Production Data Modeling: Scale, Loading & Behavior

A model can be perfectly correct on a whiteboard and still fall over in production. The earlier chapters made this model right; this chapter runs it — one retail sales star, under load, retries, backfills and a budget.

Step through the six questions below. Each is a topic in this chapter, and each has a failure attached to skipping it.

partitioning

Splitting a table by a key (usually date) so queries scan only relevant slices.

append-only fact

A fact table written by inserts only, never in-place updates, the key to fast ingest.

idempotent load

A load that can be re-run without double-counting, via natural-key dedupe.

Chapters 8 and 9 cover both shapes in full. Here is the model this chapter loads, partitions and scales: a retail sales star at the grain of one product line per sale, plus the snowflake variant.

In production it is 10.8bn rows and about 1.04 TB. Price a few edits to it below: the same ALTER is free or a 69-minute rewrite, depending on a decision taken at CREATE TABLE time.

star schema

Fact + wide denormalized dimensions; one join per attribute.

snowflake schema

Dimensions normalized into linked sub-tables; more joins, less redundancy.

metadata-only change

A schema change applied by editing table metadata, leaving every data file untouched.

field ID

A numeric identifier a format assigns to a column so files are read by ID rather than by name.

fact_sales grows without bound. Partitioning splits it physically by a key — almost always date — so a date-filtered query reads only the relevant partitions and skips the rest. Clustering sorts rows within a partition so the engine can skip blocks that cannot match.

A scheme is only right or wrong relative to a workload, so pick one below and watch three real queries run against it. The right granularity is set by the query that runs 400 times a day, not by the one that looks tidiest.

partition pruning

Skipping partitions that cannot match a query's partition-key filter.

clustering

Sorting data within a partition so non-matching blocks can be skipped.

partition key

The column a table is physically split on, usually date for facts.

-- fact_sales is partitioned by date_key (one partition per month).
-- Filtering on the partition key lets the engine scan ONLY Q2 partitions.
select d.month, sum(f.amount) as revenue
from fact_sales f
join dim_date d on d.date_key = f.date_key
where f.date_key between 20260401 and 20260630   -- prunes to 3 partitions
group by d.month;

Reads and writes scale differently. Reads are served by columnar storage, pruning, fewer joins and pre-aggregation — the ladder in the table below. Writes are served by append-only facts and bulk loading.

Neither helps with the failure that actually stops distributed work: one key holding a disproportionate share of the rows. Run the skewed join below, then fix it three ways — two in the engine, one in the model.

pre-aggregation

Storing rolled-up totals (daily/monthly) so hot metrics avoid scanning raw facts.

data skew

One key holding a disproportionate share of rows, so one task does most of the work.

append-only ingest

Loading facts by inserts only, the cheapest, most scalable write path.

A new sale is a two-step path: resolve the surrogate keys of the dimensions involved (the current customer, product, store, and date), then append one row to fact_sales. No fact is ever updated in place.

At scale that becomes a batch — and batches get retried. Run the same batch twice below under each write mode. Only one of the three is safe in both, and the mode that looks most obviously idempotent is not it.

surrogate key lookup

Resolving a fact's dimension references to the current surrogate keys at load time.

append-only

Facts are inserted, never updated in place; corrections are new rows.

idempotent load

A load that dedupes on the natural key so re-runs do not double-count.

bounded dedup window

Limiting the deduplication scan to a recent range (the replay window) to avoid scanning all history.

-- Resolve the CURRENT dimension surrogate keys, then insert one fact row.
insert into fact_sales
  (date_key, customer_key, product_key, store_key, order_number, quantity, amount)
select
  d.date_key, c.customer_key, p.product_key, s.store_key, 'SO-5001', 1, 40.00
from dim_date     d
join dim_customer c on c.customer_id = 'C-7'  and c.is_current
join dim_product  p on p.product_id  = 'P-A1'
join dim_store    s on s.store_id    = 'ST-9'
where d.full_date = date '2026-06-20';
-- Append-only: there is no UPDATE of an existing fact, ever.

-- At-least-once delivery can replay duplicate events in staging_sales.
-- row_number picks one copy per natural key, ordered by load_ts (most recent).
-- BOUND the window to the replay period so the CTE never scans all history.
with deduped as (
  select *,
    row_number() over (
      partition by order_number, sku
      order by load_ts desc
    ) as rn
  from staging_sales
  where load_ts >= current_date - interval '3' day  -- bounded to replay window
)
insert into fact_sales (order_number, sku, /* keys... */ amount)
select d.order_number, d.sku, /* resolved keys... */ d.amount
from deduped d
left join fact_sales f
  on f.order_number = d.order_number and f.sku = d.sku
where d.rn = 1
  and f.order_number is null;  -- only rows absent from fact_sales

Change Data Capture (CDC) captures row-level changes from an operational database as an ordered stream of events, each carrying an operation type (insert, update, delete), before and after images of the row, and a log position (LSN or offset) that establishes order. The data-pipeline KB covers the capture mechanics — binlog/WAL readers, Debezium-style tooling — in depth. This section owns the modeling side: what you do with a CDC stream once it arrives.

The one constraint that matters is apply in log-position order per key. Run the stream below both ways: arrival order does not merely land on the wrong current value, it leaves two overlapping open intervals, so one sale resolves to two dimension rows and is counted twice.

CDC (Change Data Capture)

A stream of row-level change events (insert/update/delete) with log positions from an operational database.

log position

A monotonically increasing offset (LSN, offset) that establishes the true order of changes, regardless of arrival time.

before/after image

The row state immediately before and after a change event; the after image is the new dimension attribute value.

-- Deduplicate the source FIRST; then merge to avoid nondeterminism.
-- As of current documentation: Snowflake raises an error by default when
-- multiple source rows match one target row (ERROR_ON_NONDETERMINISTIC_MERGE).
-- BigQuery and Delta Lake MERGE INTO likewise fail
-- on multi-source-row matches.
-- The fix is always: deduplicate the source before merging.
with deduped_src as (
  select *,
    row_number() over (
      partition by order_number, sku
      order by load_ts desc
    ) as rn
  from staging_sales
  where load_ts >= current_date - interval '3' day
)
merge into fact_sales tgt
using (select * from deduped_src where rn = 1) src
  on tgt.order_number = src.order_number
  and tgt.sku = src.sku
when matched then
  update set amount = src.amount
when not matched then
  insert (order_number, sku, amount)
  values (src.order_number, src.sku, src.amount);

Dimensions change and data arrives late. Chapter 11 covers SCD types 0 through 7 in full; here the focus is the load pattern — close the old row, insert a new version with a new surrogate key, load dimensions before facts, and stamp every timestamp in UTC.

At scale the hard part is not the SCD, it is that rewriting history and loading today happen at the same time. Run the collision below: a March restatement against an hourly loader, judged on whether a reader sees a gap, whether the late row survives, and what it costs.

SCD2 load

Close the current dimension row and insert a new version with a new surrogate key.

replay window

Reprocessing a recent range of partitions to absorb late or corrected data.

event date

The timestamp of when the business event happened, as opposed to when data arrived in the pipeline.

-- 1) Close the current version.
update dim_customer
set valid_to = date '2026-06-19', is_current = false
where customer_id = 'C-7' and is_current;

-- 2) Insert the new version with a NEW surrogate key.
insert into dim_customer
  (customer_id, name, city, segment, valid_from, valid_to, is_current)
values
  ('C-7', 'Mara', 'San Francisco', 'gold', date '2026-06-20', date '9999-12-31', true);
-- Facts before Jun 20 keep the old customer_key, so they still report the old city.

-- A sale from 2026-03-10 arrives on 2026-03-14.
-- WRONG: stamp it to today (load date) -> March totals are understated forever.
-- RIGHT: stamp it to the EVENT date and reprocess that partition idempotently.
insert into fact_sales (date_key, /* keys... */ amount)
select 20260310, /* resolved keys... */ stg.amount   -- event date, not load date
from staging_sales stg
where stg.event_date = date '2026-03-10'
  and not exists (select 1 from fact_sales f
                  where f.order_number = stg.order_number and f.sku = stg.sku);

Validate the model the way Chapter 22 insisted: run the access patterns as sample queries and confirm they answer correctly and cheaply. The two below also make the star-vs-snowflake tradeoff concrete — same answer, one more join.

Then size the design against the constraints you actually have. Everything in this chapter costs something, and the ledger below is willing to tell you that the answer is none of it.

access-pattern validation

Running the real queries to confirm the model answers them correctly and cheaply.

small-file problem

Partitions far below the compaction target, so metadata and file listing cost more than the data.

key-join history

Correct historical attribution via the stored surrogate key, no as-of date logic.

-- STAR: category is a column on dim_product -> one join.
select p.category, sum(f.amount) as revenue
from fact_sales f
join dim_product p on p.product_key = f.product_key
group by p.category;

-- SNOWFLAKE: category lives in dim_category -> an extra hop.
select c.category_name, sum(f.amount) as revenue
from fact_sales f
join dim_product  p on p.product_key  = f.product_key
join dim_category c on c.category_key = p.category_key
group by c.category_name;

-- The fact stored the customer_key current at sale time, so a plain
-- join reports each sale under the city the customer had THEN.
select cu.city, sum(f.amount) as revenue
from fact_sales f
join dim_customer cu on cu.customer_key = f.customer_key   -- version-specific key
group by cu.city;
-- A pre-move sale rolls up under the old city; a post-move sale under the new one.

This chapter asked every production question of one retail sales model: what it reads, how it distributes, what a retry does to it, how a change stream lands, what a backfill costs, and whether the scale is real. Three of those answers are decisions taken before the first row loads.

So take them. Build the spec below, then work five nights on it — the same five this chapter ran one at a time.

Six design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.