Star Schemas

A star schema places 1 fact table at the centre and a ring of dimension tables around it. At Ridgeline Outfitters the centre is fact_sales, and its grain is 1 row per order line item: 1 product on 1 order, sold to 1 customer on 1 date through 1 channel. Each row carries 4 foreign keys, 2 identifiers and 4 measures: quantity, unit_price, discount_amount and line_amount. Everything descriptive about the customer, the product, the date and the channel lives in the dimension the key points to.

The 2 kinds of table have opposite shapes. fact_sales is tall and narrow: 840 million rows over 3 years and 10 columns, every column a key or a numeric measure. dim_customer is short and wide: 4.2 million rows and 31 columns, among them city, region, loyalty_tier and acquisition_channel. dim_product holds 38,000 rows, dim_channel 16, and dim_date 36,525, 1 per calendar day for 100 years. Facts hold the numbers and dimensions hold the words.

Every analytical query walks the same path: start at the fact table, filter and group through the dimensions, aggregate the measures. Units sold by brand and month during 2025 reads the 2025 partitions of fact_sales, joins dim_product for brand and dim_date for month_name, and sums quantity. The warehouse returns 1,100 rows in 1.8 seconds.

A snowflake schema keeps dimensions normalized: dim_product carries a category_id pointing to dim_category, which carries a department_id pointing to dim_department. A star copies category_name and department_name onto every product row, storing 'Footwear' 6,400 times instead of once for about 50 KB. The snowflake makes any department filter 2 joins deeper, and every analyst has to remember which of 3 tables holds the category name.

Snowflaking earns its place in 2 situations: a fact table that lives at a hierarchy level, such as marketing spend per category, which needs a category table to own category_sk; and a dimension where a repeated 2 KB description column would dominate storage. A 38,000-row product table with a 12-character category name is neither.

A transaction fact holds 1 row per discrete event, written once and never updated. fact_sales is 1. A return is a new row with a negative quantity rather than an update, so the table stays append-only and reloading any day's partition reproduces the same rows. The measures are fully additive: quantity and line_amount sum across customers, products, days and channels, and the total always means something.

The last row returns the tent from order 50218 as its own event. What a transaction fact cannot answer is a question about state. fact_inventory_movements records every receipt, shipment and adjustment as a row with a signed quantity_delta, so units on hand for a product on 3 March is the sum of every movement since the warehouse opened, and a dashboard of 60 days of stock levels replays 3 years of movements 60 times.

A periodic snapshot holds 1 row per entity per period, capturing the state at the close of the period. fact_inventory_daily has the grain of 1 row per product per warehouse per day: 38,000 products across 6 warehouses produce 228,000 rows every night, whether or not anything moved. The 3 March question becomes 1 lookup. Rows are never updated; the next night writes a new set.

The measure units_on_hand is semi-additive: it sums across some dimensions and not others. Adding the 6 warehouses for a product on 1 day gives company-wide stock that day, a number a buyer can act on. Adding the same product across 30 days describes nothing, because each day's row already includes the units carried over from the day before.

An accumulating snapshot holds 1 row per instance of a process with a defined start and end. fact_order_fulfilment has 1 row per order, created at placement and updated as each milestone arrives. Each milestone is a date key, NULL until it happens, and the row carries the carrier_sk that shipped the order. Lag columns such as days_to_deliver, counted from placement, are computed in the load, so a lifecycle report never redoes date arithmetic across 325 million orders. This is the 1 fact type whose rows change after they are written, so its load is a merge rather than an append.

A factless fact table has keys and no measures. fact_promotion_coverage holds 1 row per product per promotion per day on promotion, and the row itself is the fact. COUNT(*) is the metric. It answers questions about what did not happen: promoted products that sold nothing, found by left-joining coverage to fact_sales and keeping the rows with no match.

A fact_sales row says customer_sk = 8827414, quantity = 2, line_amount = 189.98. dim_customer turns the key into Priya Natarajan in Denver, Gold tier, acquired through a referral in 2023. Every WHERE clause and every GROUP BY an analyst writes lands on a dimension column, so the set of dimensions and their attributes decides which questions the warehouse answers without a rebuild.

A conformed dimension is 1 physical table shared by several fact tables, with 1 set of keys and 1 definition per attribute. dim_customer is referenced by fact_sales, fact_returns and fact_support_tickets, so a cross-process question, such as customers who bought the Cascade 65 pack and opened a ticket within 30 days, is a join through the shared key. Before Ridgeline conformed it, support derived region from the ticket's time zone and sales from the shipping address, and the 2 regional dashboards disagreed by 11 percent.

A role-playing dimension is 1 physical table referenced more than once by the same fact, each reference in a different role. fact_order_fulfilment carries placed_date_sk, shipped_date_sk and delivered_date_sk, all pointing at dim_date. The table is loaded once; the query aliases it once per role, and the alias is the only thing that differs between orders by placed month and orders by delivered month.

An order carries flags that belong to no customer or product: is_gift, is_expedited, gift_wrap and a payment_method with 4 values. Left on the fact they are 4 columns across 840 million rows, and every new flag is an ALTER TABLE on the largest table in the warehouse. A junk dimension collects them: dim_order_flags holds every combination, 2 x 2 x 2 x 4 = 32 rows, and fact_sales carries 1 order_flags_sk as a fifth key. A new boolean flag doubles it to 64 rows and leaves the fact untouched.

order_id sits on fact_sales with no dimension table behind it. A dim_order would hold 1 column, the id, and joining 840 million rows to it to fetch a value they already carry gains nothing. An identifier with no attributes of its own stays on the fact as a degenerate dimension, used to group line items back into their orders.

dim_date has 1 row per calendar day, 36,525 of them, generated once for 100 years. Its columns are everything a report groups or filters by: day_of_week, month_name, quarter, fiscal_year, fiscal_quarter, is_weekend, is_holiday. Ridgeline's fiscal year begins on 1 February, so a calendar EXTRACT(QUARTER ...) is wrong for every finance report, and dim_date.fiscal_quarter holds the answer once. Before the table existed, 4 teams wrote their own fiscal quarter logic and 2 disagreed about which year 31 January belongs to.

The grain of a fact table states what 1 row means, and it is decided before any column is added. Ridgeline's fact_sales grain reads: 1 row per order line item, unique on (order_id, line_seq). Naming the columns that enforce the unit is what lets another engineer write a test against it.

At the order grain, product_sk for order 50218 has no honest value: 3 products, 1 cell. Whatever the load writes there, a units-by-product report attributes the whole order to 1 product or drops it. SUM(line_amount) is 489.97 at both grains, so revenue agrees with finance and nobody looks further, until merchandising asks for revenue by category and the stove's 79.99 has been reported under tents for months. A wrong grain produces no error message, only numbers that look right.

The second grain error puts 2 grains in 1 table. The 12.00 shipping fee on order 50218 is charged once per order, and the first place a developer reaches for is a shipping_fee column on each line row. Now 3 rows carry 12.00 and SUM(shipping_fee) returns 36.00: the query and the column are both correct, and the number is 3 times too large. The fix is to allocate the fee across lines in proportion to line_amount, or to store it in a separate fact at the order grain.

The story below makes the grain choice for fact_sales under the 2 requests that landed in its first quarter.

A surrogate key is an integer the warehouse assigns to a dimension row at load time. It means nothing to the business, has no relation to any source system, and never changes. The source's customer_id sits beside it as a natural key, kept for traceability and never referenced by a fact table, and fact_sales joins to dim_customer on customer_sk alone.

Natural keys come from source systems built for their own needs, and Ridgeline's fail in 3 ways. Emails change: 4 percent of customers update theirs each year, and a fact keyed on email loses its history at every update. SKUs get reused: the supplier retired RL-2208, a 2-person tent, in 2023 and reissued it for wool socks in 2025, so 2 years of tent sales keyed on SKU now join to socks. And a type 2 dimension, which adds a new row for each change to a tracked attribute instead of overwriting the old one, holds several rows per customer_id, which a fact row cannot point at without a date to pick the version.

Priya Natarajan moved from Portland to Denver on 14 August 2024. The dimension keeps both versions: the Portland row, customer_sk 3310562, closed on the move date, and the current Denver row, 8827414. A June 2024 sale references 3310562 and an October 2024 sale references 8827414, so revenue by city puts each sale where Priya lived when she bought, with no date logic in the query.

In the second query the engine hashes on customer_id, then checks each fact row against every version of that customer with a residual range filter, which costs more than a single equality probe. It is also fragile: a sale stamped at exactly valid_to matches 2 versions or none depending on which boundary convention the dimension and the query assumed. The surrogate key resolved the version once, at load time, and stored the answer in the fact row.

The load assigns the key. A customer_id the dimension has never seen gets a new row and the next value from a sequence. A change to a tracked attribute closes the current row and inserts a new one with a new key. The alternative to a sequence is a hash, such as the MD5 of customer_id and valid_from, which produces the same key on every rerun of the same input, so a failed load replays without consulting the previous run. A row with key -1 stands for 'unknown customer', so a fact whose customer has not arrived yet still joins and its amount stays in the totals.

A BIGINT key is 8 bytes and a UUID string is 36, a difference of 28 bytes per row for each foreign key column, and fact_sales carries 5 of them. dim_date is the conventional exception to meaningless keys: its key is the date written as 20260902, because the table is never versioned and a readable key makes partition filters legible.