Marketing Data Modeling (Star Schema)
Facts and dimensions
A star schema organizes a warehouse mart around a central fact table — one row per business event (an ad impression, an order, an email send) holding measures (spend, revenue, quantity) and foreign keys — surrounded by dimension tables holding descriptive attributes about the entities those keys reference (campaign name and objective, customer segment and acquisition channel, product category). The 'star' shape comes from drawing the fact table in the center with each dimension joined directly to it by a single foreign key, rather than dimensions joining to other dimensions (which would make it a snowflake schema instead).
-- Fact table: one row per order, foreign keys out to dimensions, numeric measures
CREATE TABLE fct_orders (
order_id STRING,
order_date DATE,
customer_key STRING, -- FK -> dim_customers
campaign_key STRING, -- FK -> dim_campaigns
product_key STRING, -- FK -> dim_products
order_revenue NUMERIC,
discount_amount NUMERIC,
quantity INT64
);
-- Dimension table: descriptive attributes, slowly changing over time
CREATE TABLE dim_customers (
customer_key STRING,
customer_id STRING,
acquisition_channel STRING,
lifecycle_stage STRING,
signup_date DATE,
valid_from TIMESTAMP,
valid_to TIMESTAMP
);Why marketing analytics specifically benefits from this structure
Marketing questions are almost always 'slice this measure (spend, revenue, conversions) by this attribute (channel, campaign, segment, region) over this time grain.' A star schema makes every such question a single join plus a GROUP BY — the fact table stays lean and fast to scan (it's usually the largest table by row count), while attribute changes (a campaign gets renamed, a customer's segment gets reclassified) only require updating the small dimension table, not rewriting history in the fact table.
Slowly Changing Dimensions (SCDs) — the part marketers usually don't know they need
Customer segments, lifecycle stages, and even campaign metadata change over time, and a naive dimension table that just overwrites old values in place (a 'Type 1' SCD) destroys your ability to answer 'what segment was this customer in when they made this purchase.' A 'Type 2' SCD instead inserts a new row with valid_from/valid_to timestamps whenever an attribute changes, preserving full history — so a fact table join using order_date BETWEEN valid_from AND valid_to always resolves to the dimension values that were actually true at the time the event happened, not today's current values.
Type 1 vs Type 2 is a business decision, not just a technical one
Star schema vs wide denormalized table vs raw normalized source replica
| Approach | Query simplicity | Storage/compute cost | Flexibility for new questions |
|---|---|---|---|
| Star schema (fact + dims) | One join per dimension needed | Moderate — dimensions are small, facts are lean | High — new dimensions attach without reshaping facts |
| Single wide denormalized table | No joins needed at query time | High — attribute values repeated on every fact row | Low — adding a new attribute requires a full rebuild |
| Raw normalized source replica | Requires many joins mirroring source system's own schema | Low storage, but expensive joins at query time | High for engineers, low for analysts unfamiliar with source schema |
Grain: the decision that determines everything else
Before building any fact table, explicitly decide its grain — the thing one row represents. 'One row per order' and 'one row per order line item' are both valid grains, but mixing them in a single table (some rows representing orders, others representing items) makes every aggregate ambiguous. State the grain in the model's documentation and enforce it with a unique dbt test on whatever column(s) define that grain, so a join bug that silently fans out the grain gets caught immediately rather than surfacing as a slightly-wrong dashboard number weeks later.
What's Next?
A clean star schema is only as good as the identity keys joining facts to dimensions — which raises the much harder problem of how you decide two records, often from entirely different source systems, actually represent the same customer.
I build these systems professionally.
Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.