Designing dimensions
Define the attributes users slice by — including the subtle trick of one logical dimension mapping to different columns per fact.
A dimension is anything you group or filter by. For Percolate there are nine: date, product category, product name, channel, country, region, segment, plan, platform. Each maps to a physical column — usually. The interesting cases are where it doesn't map so simply.
The straightforward ones
product_category:
sql: products.category
region:
sql: customers.region
segment:
sql: customers.segmentThe subtle one: a dimension that means different columns in different facts
"Date" is not one column. For revenue it's orders.order_date; for marketing spend it's marketing_spend.spend_date; for new customers it's customers.signup_date; for subscriptions it's subscriptions.start_date. They're all "date" to a business user, but they live in different tables. The layer handles this with a per-model override:
date:
type: time
sql: orders.order_date # default
per_model:
subscriptions: subscriptions.start_date
marketing_spend: marketing_spend.spend_date
customers: customers.signup_dateThe same idea rescues "channel": it's customers.channel (a customer's acquisition source) for revenue and new-customer metrics, but marketing_spend.channel when you're measuring spend. One logical name, resolved to the right physical column depending on which metric you're computing.
Because "date" and "channel" resolve consistently across facts, you can compute a ratio like CAC = spend ÷ new-customers grouped by channel — even though the numerator and denominator come from entirely different tables. The per-model mapping is what makes cross-table metrics line up.
Time dimensions also need grain: the same date column can be bucketed by day, week, month, quarter, or year. You'll wire that in Module 3 — for now, just mark date as type: time so the layer knows it supports grains.