Module 6: Marts: Facts and Dimensions
Create the business-facing layer: facts, dimensions, and star schemas.
110 minutes. 1 inline exercise. Free course module.
Learning Objectives
- Design simple fact and dimension tables
- Understand star schema basics
- Choose the right mart grain for reporting
Why This Matters
Marts are the tables business users and dashboards should trust. Facts store measurable events; dimensions store descriptive context.
Lesson Content
The Mental Model
Marts are the tables business users and dashboards should trust. Facts store measurable events; dimensions store descriptive context.
If staging is clean ingredients and intermediate is prep work, marts are the served plates. This is the layer people actually consume.
Tiny Example
We will use a small ecommerce dataset throughout the course. Think of these as the only tables in your first warehouse:
| Table | Grain | Example columns |
|---|---|---|
raw_orders | one row per order event | order_id, customer_id, amount, status, created_at |
raw_order_items | one row per item inside an order | order_id, product_id, quantity, item_price |
raw_customers | one row per customer | customer_id, email, country, created_at |
Interactive Check
Question: Should customer country live in fct_orders or dim_customers?
Reveal the answer
Usually dim_customers owns customer country. fct_orders can join to it for analysis, but the descriptive customer attributes belong in the customer dimension.
Inline Practice Lab
This lab is intentionally small. You can solve it by reading the table, writing the SQL/YAML mentally, or pasting the snippet into any SQL scratchpad later.
-- Example starter table
select
order_id,
customer_id,
amount,
status,
created_at
from raw_orders;The goal is not tooling setup. The goal is learning the production habit: state the grain, clean one thing, test one assumption, and explain the downstream impact.
Self-Check Quiz
- What is the grain of the table you are building?
- Which downstream metric or dashboard would be wrong if this model broke?
- What test would catch the most likely beginner mistake here?
Real-World Use Cases
- Reliable executive dashboards that do not disagree across teams
- AI analytics agents that query governed metrics instead of guessing SQL
- Auditable metric changes where owners can see downstream impact before merge
Production Notes
- Name marts based on business concepts, not source systems. Business users do not care which app emitted the raw table.
Common Mistakes
- Creating one giant flat table for every question
- Putting measures in dimensions
- Ignoring slowly changing attributes
Think Like an Engineer
- Can you explain the grain of this model in one sentence?
- What breaks downstream if this field becomes null tomorrow?
- Where should this logic live so it is reused instead of copied?
Career Relevance
Analytics engineering is the bridge between SQL skill and production data ownership. Freshers who learn tests, lineage, metrics, and semantic modeling early stand out because they can reason about trust, not just queries.
Key Terms
- Mart
- A business-facing model intended for analytics consumption.
- Dimension
- A descriptive table such as customers, products, or accounts.
Inline Exercises
-
Design an Ecommerce Mart
Create a simple star schema with fct_orders, dim_customers, and dim_products.
30-45 minutes - Beginner to Intermediate
- List each table grain
- Choose primary keys
- Choose foreign keys
- Choose three measures on fct_orders
- Choose five descriptive columns for dimensions
Inline lab: complete the exercise directly in the course page.
Key Takeaways
- Marts should be easy and safe for downstream consumers
- Facts and dimensions make reporting grain explicit
- Star schemas remain useful even in modern warehouses