Skip to content
Tech Interview Prep

Top 100 Data Architect Interview Questions and Answers

The questions most likely to come up in your Data Architect interview, ranked by likelihood, with senior-level answers that cover what the interviewer is listening for.

Curated: · Written: · Reviewed:

Reviewed 100Review pending 8

108 questions

Q&A-1Finance and product both query orders. Why is "one row per order" still an unfinished architecture?

The first thing I would establish about fact grain is which grain and which keys the number actually uses.

Grain is the statement of what one row means: one order, one order line, one shipment, or one payment. Until that statement is written and tested, two consumers can join the same table and produce irreconcilable totals because they counted different things.

Concretely, write the grain as a uniqueness constraint on the business keys that identify one row, publish the additive measures that are legal at that grain, and refuse loads that insert a second row with the same keys or that mix line and header amounts on one row.

The reason for that specificity is a failure I have seen: Orders sat at mixed grain: 612,000 header rows and 1.84 million line rows in the same table. Finance summed revenue to $41.2 million while product summed $118.7 million for the same 14-day window, and both dashboards stayed green.

Two consumers, one table, two grains.

ConsumerAssumed grainRows countedRevenue reported
financeorder header612,000$41.2 million
productorder line1,840,000$118.7 million
declared unique keysnone——

I would not consider it settled without evidence: load a duplicate key pair and a header amount on a line-grain table, and require both writes to fail before any consumer is pointed at the table.

A table without a stated grain is two tables wearing one name.

Curated: · Written: · Reviewed:

Q&A-2The order number lives only on the fact. When is that a degenerate key, and when is it a missing dimension?

It's a degenerate dimension when the order number carries no attributes of its own: the only things you ever do with it are GROUP BY order_number, COUNT(DISTINCT order_number), and drill across to another fact table. It's a missing dimension the moment it owns attributes that consumers filter or group on, or needs history that changes independently of the fact grain.

The mechanism is what makes the distinction concrete. A degenerate dimension is a dimension key with no dimension table: it sits on the fact row so you can group and drill across facts without a join. That's the whole value: no join, no extra table, no load dependency. The moment you need to filter on order channel, sales rep, or promised ship date, you're either storing those on every fact row or you need a table to join to. That's the fork.

Deciding criteria:

QuestionDegeneratePromote to order dimension
Does the identifier own attributes consumers filter on?NoYes
Is it reused across fact tables (orders, shipments, returns)?RarelyYes: conformed dimension
Do its attributes change independently of the fact grain?NoYes: SCD
Is it only used for grouping / distinct counts?YesNo

Costs, both real. Degenerate: zero join, but order-level attributes repeat on every line. At 10 lines per order, a channel attribute is stored 10x and updated 10x. Promoted: one row per order, one place to update, conformed across facts, but every query joins, and the load must sequence dimension before fact.

The failure mode is grain fan-out. Appending order status changes as new fact rows without changing the declared order-line grain: 2.1M order-line facts, 3 status versions each over 9 days, and uniqueness on (order_number, line) breaks for 47,800 rows. Detect it with a grain test:

SELECT order_number, line_number, COUNT(*) AS versions
FROM fact_order_line
GROUP BY order_number, line_number
HAVING COUNT(*) > 1;

Any row returned means the declared grain is a lie. Adding columns to the fact would not have caused this; the extra rows did.

Promote to a real order dimension when order-level attributes are filtered on, reused across multiple fact tables, or need history. Keep it degenerate when the identifier is only a grouping handle. If you need status as of the fact event, that's a status dimension FK on the fact, not a new fact row: the fact grain stays order-line.

So: degenerate means no leftover attributes, not "we skipped the dimension." If attributes exist, you have a missing dimension.

Curated: · Written: · Reviewed:

Q&A-45An order has several promotions. Why does putting promotion_id on the fact explode the grain?

Where candidates lose the interview on many-to-many bridge tables is calling the lakehouse layout the model.

A many-to-many relationship needs a bridge at the relationship grain. Repeating the fact row once per promotion multiplies every additive measure by the number of promotions. Adding a second foreign key on the same grain, without repeating the row, does not by itself explode the grain; it usually cannot represent several promotions.

Concretely, keep the fact at order-line grain, add a bridge of (order_line_sk, promotion_sk, allocation), and allocate additive measures only through that allocation.

The reason for that specificity is a failure I have seen: Each line was repeated once per promotion_id. Lines with 3 promotions tripled revenue. 6.8 percent of lines had multiple promotions, adding $12.4 million of phantom GMV for a month. A nullable second FK on the original grain would have under-counted promotions, not multiplied GMV.

Promotions on the fact versus a bridge.

DesignLines with 3 promotionsPhantom GMV
fact row repeated per promotionrevenue ×3$12.4 million
second FK, still one row per linepromotions truncated, grain held$0 from fan-out
bridge with allocationrevenue ×1$0

I would not consider it settled without evidence: attach 3 promotions to one line and require revenue to stay the line amount, with bridge rows carrying allocations that sum to 1.

Fan-out of the fact is the grain change; a second FK without extra rows is a different defect.

Curated: · Written: · Reviewed:

Q&A-48An order has order_date, ship_date, and deliver_date. How many date dimensions do you build?

One physical date dimension, three role-playing keys on the fact. Assumptions: all three dates are day-grain, and the business runs one calendar (Gregorian plus one fiscal calendar). If either breaks, see the exceptions below.

What I'd say in the first minute: order_date, ship_date and deliver_date are three roles the same calendar plays. Build dim_date once, put three foreign keys on the fact (order_date_key, ship_date_key, deliver_date_key) and expose each as a view or an aliased key. The calendar is defined once, so fiscal week 12 means the same seven days no matter which role you reach it through.

CREATE TABLE dim_date (
  date_key          INT PRIMARY KEY,   -- 20240321
  calendar_date     DATE NOT NULL,
  fiscal_year       SMALLINT,
  fiscal_week       SMALLINT,
  fiscal_week_start DATE
);

CREATE TABLE f_order (
  order_id         BIGINT PRIMARY KEY,
  order_date_key   INT REFERENCES dim_date,
  ship_date_key    INT REFERENCES dim_date,
  deliver_date_key INT REFERENCES dim_date
);

CREATE VIEW dim_order_date   AS SELECT * FROM dim_date;
CREATE VIEW dim_ship_date    AS SELECT * FROM dim_date;
CREATE VIEW dim_deliver_date AS SELECT * FROM dim_date;

Trace: order placed Mon 2024-03-18, shipped Thu 2024-03-21, delivered Mon 2024-03-25. With Monday-start fiscal weeks, FW12 = Mar 18–24. So order → FW12, ship → FW12, deliver → FW13, and ship-to-deliver is 4 days. Every one of those values comes from one row per date in one table; there is no second copy to disagree with.

When separate physical date dimensions are legitimate. Three cases:

  1. Different grain. If deliver_date is a timestamp (dock scan at 14:32:07) and order_date is a day, one day-grain table can't serve both. Keep the timestamp on the fact, join dim_date for calendar attributes, and build a second table only if you need time-of-day attributes such as shift or carrier cutoff window.
  2. Genuinely different calendars. A 4-4-5 retail calendar for merchandising and ISO weeks for finance disagree by design. One table can carry both as separate column sets; two tables are justified only when the calendars are owned by different teams on different reload schedules.
  3. Different attribute sets. Ship_date wants carrier cutoff and holiday-shipping flags; order_date wants promo period. Put both in one wide table with nullable columns: cheaper than two tables and it keeps conformance.

Cost of the views. Each role view is a join. A query joining all three roles to a 2B-row fact does three joins against the same table; modern optimizers (Snowflake, BigQuery, Postgres) reuse the scan, older ones don't. The table itself is small, 30 years of days is about 11k rows, so storage is not the argument. The argument is that a copy is a second thing to reload, and the moment one copy reloads alone you have drift.

Failure mode. Say the ship-date copy was built from a spreadsheet whose week started Thursday (hypothetical). Then 2024-03-21 is FW12 under both calendars, but 2024-03-25 is FW13 under Monday-start and FW12 under Thursday-start. Every order shipped Thursday and delivered the following Monday flips from "next fiscal week" to "same fiscal week": a ship-to-deliver SLA report silently changes its denominator. No error, no failed load, just a different number.

Detect it with a conformance test that runs on every load:

SELECT a.date_key
FROM dim_date a
JOIN dim_ship_date b USING (date_key)
WHERE a.fiscal_week IS DISTINCT FROM b.fiscal_week;  -- must return zero rows

With one table the test is trivially true; with copies it is the only thing standing between you and a quarter of wrong numbers.

Curated: · Written: · Reviewed:

Q&A-46What is a bus matrix for, if you already have an ERD?

The 90-second answer. A bus matrix is the conformance plan: it records which facts share which dimension instances, not which tables happen to have a column named customer. An ERD answers "what tables exist and what references what." The matrix answers "can I put revenue and support tickets on the same axis and have the numbers mean the same thing." Those are different questions, and the ERD has nowhere to record the answer to the second one.

Mechanism. Facts on rows, conformed dimensions on columns. A cell is ticked only when two things hold: a foreign key exists, and a member contract exists. Same grain, same business key, same SCD policy, same treatment of unknowns and late-arriving members. The first condition is what the ERD already shows. The second is the one it cannot express, and it is the one that decides whether a join is safe.

Worked example (hypothetical, but the shape is common). An ERD showed customer_id on 8 fact tables. Only 3 of them used the enterprise customer id; the other 5 carried a source-system id that happened to share the column name. A company-wide dashboard inner-joined all 8 on customer_id and silently dropped rows.

Facts drawing customerUsing enterprise idRevenue lost to the inner join
8 in the ERD322% ($31M in a quarter)
8 with matrix tests80

The ERD was correct. It just never claimed the ids were the same id.

When the matrix is the wrong tool. If you have one fact table and no shared dimensions, the matrix is a single cell and buys nothing: skip it. It is also a Kimball artefact: on Data Vault, Inmon, or a lakehouse with a semantic layer, the conformance contract belongs in the semantic model, in dbt contracts, or in metric definitions, and a separate spreadsheet becomes a second source of truth that drifts from the code. And the matrix is design-time documentation: nothing enforces it. If you already have referential integrity constraints plus dbt tests on the join keys, those enforce a subset of it automatically, and the matrix's remaining job is the part tests can't see: grain, SCD policy, and unknown-member handling.

Failure modes. The matrix drifts: a cell stays ticked after the dimension's business key changes, and the tick becomes a lie. It gets filled in by whoever owns the fact table, so two teams tick the same cell against different definitions of "customer." And it is often treated as a one-time design artefact, so nobody re-runs it when a new source lands.

Verification. For each ticked cell, join a sample of fact rows to the dimension on the published key and require a match rate the SLA names: 99.5% of fact rows resolving to a dimension member is a reasonable starting bar. Untick anything that fails, and re-tick only after the mapping is fixed. That test is what turns the matrix from a diagram into a contract.

Curated: · Written: · Reviewed:

Q&A-7Flags and codes are proliferating on the fact. When do they become a junk dimension?

Answer in the first minute. Flags and codes become a junk dimension when four things hold at once: they are low-cardinality, they are not entities (no natural key, no attributes of their own, no history), they are queried together as a group, and the combinations that actually occur are bounded and far below the Cartesian product. The trigger is proliferation: you are adding the Nth flag to a fact that is already wide, and every scan now reads N columns to answer a question about the group.

If a flag has independent history, its own attributes, or high cardinality, it is not junk. It is a dimension, and folding it into the junk dimension freezes it.

Mechanism. A junk dimension is one row per observed combination of the flags, with a surrogate key; the fact carries that single key instead of N columns. The dimension is tiny, tens to low thousands of rows, so it broadcasts to every node and the join is cheap. The fact gets narrower, and the query writer gets one attribute to group by instead of eleven.

Worked example (hypothetical figures). A 2.4-billion-row columnar fact has 11 low-cardinality flags on it. Only 48 combinations occur, not 2^11 = 2048, because the flags are correlated: a cancelled order is never also shipped.

  • As columns: a query grouping by all 11 reads 11 columns. At 1 byte per boolean, 11 × 2.4e9 = 26.4 GB uncompressed per full scan.
  • As a junk dimension: the fact carries one 4-byte surrogate, 4 × 2.4e9 = 9.6 GB, and the dimension is 48 rows. Well under a kilobyte of payload, broadcast to every node. The added cost is one hash join against 48 rows.

Fact-side bytes fall by roughly 17 GB per scan; the join is the price. Whether that nets out positive depends on how often the flags are queried together: measure both plans before committing.

Failure modes.

  • Cartesian explosion. Materialising all 2^11 rows is harmless; materialising 2^20 for 20 flags is a million-row "junk" dimension that is no longer small, and you cannot enumerate combinations that have not occurred. Detect by comparing dimension row count to COUNT(DISTINCT ...) over the flags in the fact.
  • Hiding an entity. A status code with its own attributes and SCD2 history belongs in its own dimension; inside the junk dimension it is frozen and you lose the ability to track changes. Detect by asking whether the attribute has a natural key and changes over time.
  • Missing members. Late-arriving facts need their combination to already exist, plus rows for unknown and not-applicable. Detect by null surrogate keys or dropped rows in the load.
  • Decorrelation. Flags that were correlated can decorrelate; new combinations then need new dimension rows, and a load path that does not insert them drops facts.

When not to. Two or three independent flags: leave them on the fact, the join is not worth it. High cardinality, a code with thousands of values, gets its own dimension. Flags never queried together are probably separate dimensions, or stay put.

Pack flags that are not entities and are queried together. Do not hide an entity in the pack.

Curated: · Written: · Reviewed:

Q&A-102You have a fact table with no measures on it. What is it recording, when is that the right grain, and how do you aggregate over it?

There are two things people mean by "factless", and the design answer differs for each. Get the interviewer to say which one before you draw anything.

Event fact: one row per occurrence: a student attended a session, a patient checked in, a user logged in. The measure is implicit and it is COUNT(*). Grain is the entire design: (student_sk, session_sk, date_sk), with a unique constraint on that tuple.

Coverage fact: one row per relationship that was in force: this SKU was on promotion in this store on this day. It exists to answer the question a sales fact structurally cannot: what was eligible and did not sell.

Event factCoverage fact
A row meanssomething happenedsomething was available or true
MeasureCOUNT(*)COUNT(*)
Answershow manywhat didn't happen (anti-join)
Row-count driveractual eventscross product of the dimensions in scope

Aggregation. Count, never sum, and count at the grain you were asked about, which is usually coarser than the table:

-- table grain: (student_sk, session_sk)
-- "how many students attended at least one session this term?"
SELECT COUNT(DISTINCT student_sk)
FROM fct_attendance a
JOIN dim_date d USING (date_sk)
WHERE d.term = '2025-Autumn';

COUNT(*) there returns session-attendances, not students. That is the single most common wrong answer on this question.

The coverage anti-join, which is the reason the table exists:

-- SKUs on promotion that sold nothing in that store/day
SELECT c.store_sk, c.product_sk, c.date_sk
FROM fct_promo_coverage c
LEFT JOIN fct_sales s
  ON  s.store_sk   = c.store_sk
  AND s.product_sk = c.product_sk
  AND s.date_sk    = c.date_sk
WHERE s.store_sk IS NULL;

Failure modes.

  1. Fan-out on join. Joining a factless fact to a measured fact at a finer grain multiplies rows. Aggregate the measured side down to the factless grain first, or use EXISTS. SUM(amount) after a naive join is silently inflated and nothing in the pipeline will flag it.
  2. Materialising the full cross product. 500 stores × 20,000 SKUs × 365 days ≈ 3.65 billion rows (illustrative). Don't. Store only rows where coverage actually existed; generate the missing side at query time from the dimensions, or snapshot it monthly.
  3. Unknown members. A factless fact with a null FK still counts. Either the load rejects it, or you carry a real -1 unknown row in the dimension: otherwise the count comes up short and no test catches it.
  4. Idempotency. Factless facts are still facts. Re-running a load without a unique key on the natural event key doubles the count, and there is no measure to sanity-check against afterwards.

When not to use it. If the business will ever ask "how much", you have a measured fact and should model it that way. And don't add a constant 1 measure column just to have one: it invites SUM(1) after a join, which is the fan-out bug with a friendlier name.

Curated: · Written: · Reviewed: —

Q&A-50Inventory is a balance. Why is a transaction fact not enough, and what is the periodic snapshot's grain?

I would start periodic snapshot grain from the contract and the consumer, not from the warehouse diagram.

Balances need a picture at a clock: on-hand by SKU by warehouse by day. Transactions explain the movement; they do not give the stock unless you replay every day from origin.

Concretely, emit a daily snapshot at a named close time, grain (day, warehouse, sku), and reconcile snapshot[t] = snapshot[t-1] + receipts - issues.

The reason for that specificity is a failure I have seen: Only movements were stored. A missing receipt 11 months ago made every later stock query wrong. Ops oversold 8,400 units across 6 days before anyone replayed the chain.

Stock from movements only.

SourceMissing receiptUnits oversoldDays
replay of 11 months of movements18,4006
daily snapshot plus reconciliationcaught at close00

I would not consider it settled without evidence: drop one historical receipt in a test replay and require the daily snapshot job to fail reconciliation rather than to publish stock.

A balance is a snapshot; movements are the audit.

Curated: · Written: · Reviewed:

Q&A-49A fulfilment pipeline has 5 milestones. When is an accumulated snapshot the right fact, and what is the grain?

Short answer. Use an accumulating snapshot when the pipeline has a fixed, known set of milestones and the questions are "where is each instance right now" and "how long did each stage take". The grain is one row per pipeline instance, one row per order, keyed by order_id, with one timestamp column per milestone and lag columns between them. It is not the grain of the milestone event, and it is not one row per instance per day.

How the row is maintained. Insert the row when the instance is created (order placed), with every milestone timestamp null. As each milestone lands, update that same row in place: set the milestone's timestamp, recompute the lags, advance a status column. Two guards make it safe: write only forward (never overwrite a milestone timestamp with a later-arriving but earlier-stamped event), and derive status from the milestone timestamps present rather than from the last message received.

When it is the wrong fact. If milestones are unbounded or vary per instance, a support ticket with arbitrary states, there is no fixed column set and the snapshot degenerates. If you need the history of intermediate states, every scan and every rework, the overwrite destroys it; keep the event fact alongside. If the question is "how many packing scans happened", that is a count of events, not a state. And if you need to replay or audit, the event fact is the source of truth; the snapshot is derived from it.

Failure modes.

  • Instance never inserted. If the row is created on first milestone completion, orders that stall before that milestone are invisible and "stuck before packing" reads as zero. Detect by reconciling snapshot row count against distinct instance IDs in the event fact.
  • Out-of-order arrival. A delayed feed delivers a packing event after dispatch. Writing status on arrival regresses the order. Detect with a check that no row has a later milestone timestamp earlier than an earlier one.
  • Null means two things. A null dispatch timestamp can mean "not yet dispatched" or "cancelled / not applicable". Without a status column you cannot tell them apart, and your in-flight count is wrong.

Worked trace (hypothetical, one order, 4471):

timeeventsnapshot row after
09:12order placedinserted; all milestone ts null; status = placed
11:40packedpacking_ts = 11:40; status = packed
16:05dispatcheddispatch_ts = 16:05; status = dispatched
18:20duplicate packed scan, delayed feedunchanged. packing_ts already set
19:05packed event stamped 11:52, delayed feedunchanged. 11:52 < stored 16:05, write-forward guard rejects it

Without the guard, the last row flips status back to packed and the order shows as in flight after it shipped.

Cost. For 1,000 orders a day with 5 milestones, the event fact holds up to 5,000 rows a day; the snapshot holds 1,000 rows total and grows only with new orders. "How many are in packing now" is packing_ts IS NOT NULL AND dispatch_ts IS NULL: one row per order, no self-join. The price is update contention on hot rows and the loss of intermediate history.

Curated: · Written: · Reviewed:

Q&A-101When do you choose a star schema, when a snowflake, and when a normalized Inmon or Data Vault model?

Short answer: decide the layer before the shape. Star and snowflake are mart-layer choices; Inmon 3NF and Data Vault are integration-layer choices. Almost every estate above a handful of sources needs one integration layer and star marts on top of it. Default the mart to star. Snowflake a dimension only when it is too wide to scan or its sub-parts change at different rates. Pick 3NF vs Data Vault on source count, audit requirements and load concurrency: never on query speed, because neither is built to be queried by a BI tool.

The two questions that decide it

Who writes the SQL? If a human or a BI tool does, star. In a columnar MPP engine every join is a shuffle or a broadcast; a dashboard that joins nine snowflaked tables pays nine of them per refresh. If the consumer is another pipeline, the integration layer is fine.

How many sources, and how much do they drift? Three stable sources with a shared key: a 3NF EDW is enough and cheaper to build. Ten sources with overlapping business keys, attributes arriving monthly, and a need to prove what the warehouse knew on a given date: Data Vault's insert-only hubs, links and satellites absorb that without rewriting history.

ModelLayerOptimizes forCostsChoose when
StarMartOne join from fact to a wide dimensionRedundant attributes, wider dimension scansDefault for BI and analyst SQL
SnowflakeMartNarrow dimensions, sub-parts changing at different ratesOne join per level, per queryA dimension is too wide to scan, or a sub-entity is genuinely shared
3NF (Inmon)IntegrationSingle integrated source of truth, auditabilityAnalysts rebuild the star badly in every queryFew sources, stable model, marts downstream
Data VaultIntegrationInsert-only history, parallel loads, schema drift5–9 joins per question, hash keys, load metadataMany sources, audit and replay requirements

The trap: snowflaking a type-2 dimension

-- dim_customer and dim_customer_address are both type 2, joined on customer_sk
SELECT g.region, sum(f.amount)
FROM fct_order f
JOIN dim_customer c         ON c.customer_sk = f.customer_sk
JOIN dim_customer_address a ON a.customer_sk = c.customer_sk
JOIN dim_geography g        ON g.geo_sk = a.geo_sk
GROUP BY 1;

Nothing in that join says "the address version that was current when the order happened." A customer with 3 customer versions and 2 address versions produces 6 rows per fact row. On a 2B-row fact where 4% of customers have multiple versions in both, that is roughly 80M duplicated rows and a revenue figure inflated by whatever those rows carry: silently, because the query runs and returns a number. Fix it by carrying the version key on the fact (that is the star), or by putting the version key on the address sub-table so the join is on (customer_sk, customer_version_key).

Why the vault is not the serving layer

"Revenue by the customer's region as of the order date" from a raw vault means hub_order → link_order_customer → sat_order_amount, plus hub_customer → sat_customer_region, each satellite with its own load_date and its own effectivity predicate. Six joins and a per-satellite as-of rule. That is why you materialize a business vault or a star mart; nobody puts that query behind a dashboard.

Tooling constraints that settle it

  • Spark's default spark.sql.autoBroadcastJoinThreshold is 10 MB. A sub-dimension under that is free to join; above it, every query shuffles it. That threshold, not taste, decides whether snowflaking is affordable.
  • Power BI DirectQuery and Tableau live connections push joins to the source on every visual. Import/extract mode hides the cost and moves it to refresh.
  • Row-level security on a snowflaked dimension has to propagate through the join chain; on a star it is one predicate on one table.

When not to: a 50k-row dimension is not worth snowflaking. It broadcasts. Data Vault with one source and no audit requirement buys hash keys and joins for nothing. And a star built straight on raw sources with no integration layer becomes a different star per team, which is the problem the 3NF or vault layer existed to solve.

Curated: · Written: · Reviewed: —

Q&A-47When do you snowflake a dimension, and when is that a query-time tax the warehouse should not collect?

Short answer: snowflake a dimension when the hierarchy is large, changes on its own schedule, and is shared by more than one fact table, because denormalising it means rewriting every parent row on every child edit. Don't snowflake when the hierarchy is small and stable, or when the only consumer is a high-volume fact scan that would pay the join on every query. In that case keep the normalised hierarchy as the system of record and publish a denormalised mart built from a dated snapshot of it.

Assumptions I'd state before answering: columnar MPP warehouse, SCD2 on dimensions, fact tables in the billions of rows, BI queries that group by one or two hierarchy levels.

Why snowflaking exists: write amplification. Take 200,000 customers carrying 12 org attributes (region, district, territory, manager, cost centre…). The org restructures three times a month. Denormalised, each restructure versions all 200,000 customer rows: 600,000 new SCD2 rows a month. Snowflaked, the same restructure versions 12 hierarchy rows: 36 a month. That is roughly 16,000× less write, and it is the entire argument for snowflaking. Nothing about storage or elegance enters into it.

The tax you pay for it. A query for revenue by region now walks fact → customer → address → city → region. The first hop joins on customer_sk, which is usually the fact's distribution key, so it is local. The next three join on keys the fact does not carry, so the intermediate result has to be shuffled each time. Illustrative numbers: 2.2 billion facts, ~40 bytes of key and payload per row, ~88 GB moved per hop, three extra hops. Call it 260 GB of shuffle traffic per query that the star shape does not pay. On a cluster with 5 GB/s of aggregate shuffle throughput that is ~50 s of pure data movement before any aggregation. That is the tax, and it is collected on every scan, not once.

Failure modes, and how you'd catch them.

  • Shadow denormalisation without SCD. Analysts get tired of the join, build a flat table with no versioning, and history silently disappears. Detection: reconcile mart row counts against the SCD2 dimension, if the mart has one row per natural key while the dimension has many versions, you have already lost history.
  • Type-2 explosion. If the parent row is versioned on every child change, the dimension grows multiplicatively. Detection: count distinct versions per natural key per month; a number that tracks child-edit frequency rather than parent-edit frequency means the grain is wrong.
  • Snowflaking a stable dimension. A 200-row country table joined through three hops is pure tax with zero write benefit. Detection: if the dimension is under a few thousand rows and changes less than once a quarter, the write saving is nil.
Denormalised starSnowflaked hierarchy
Write per org restructure200k customer rows versioned12 hierarchy rows versioned
Joins for revenue-by-region14
Extra shuffle per querynone if fact is distributed on customer_sk~88 GB/hop × 3 hops
Historywhatever the mart snapshot keptfull SCD2 on the hierarchy

Decision rule. Snowflake when a restructure would rewrite more than a few percent of the parent table, and when at least two fact tables consume the hierarchy. Otherwise denormalise. Either way, publish a mart built from a dated snapshot of the hierarchy so the query path stays one join and the history stays in the normalised table. Normalise to control versions; denormalise to query them.

Curated: · Written: · Reviewed:

Q&A-106The marts layer could be a star schema, a snowflake, or a normalized Data Vault underneath. How do you choose, and what does each cost the analysts who write the SQL?

The first thing I'd say is that this is not a binary. The marts layer and the integration layer have different jobs. A star schema is the right default for the marts layer because it minimizes joins for the people writing SQL: usually analysts and BI tools. A snowflake is a star with dimensions normalized into sub-dimensions; it saves storage and makes dimension maintenance easier, but it pushes joins back onto every query. A normalized or Data Vault model belongs underneath, as the integration layer, not as the surface analysts query.

Here's how I'd frame the decision:

CriterionStarSnowflakeNormalized / Data Vault
Joins per typical query1 per dimension2–4 per dimension5–15+
StorageRedundantLowerLowest
Dimension maintenanceUpdate in one placeUpdate in sub-dimensionInsert-only, auditable
Self-service friendlinessHighMediumLow
BI tool supportNativeWorks but needs modelPoor without a semantic layer

The real cost driver is who writes the SQL. If 50 analysts and 200 dashboard authors query the marts, every extra join is a tax paid thousands of times a day. If a small data engineering team writes all the SQL and the dimensions are huge, say a product dimension with 40 million rows and 200 attributes, snowflaking can cut storage and make it easier to add a new attribute without rewriting the whole dimension.

Let me make that concrete. Suppose a sales fact joins to a product dimension. In a star, the product dimension has product_key, category, subcategory, brand, and 50 other attributes. A query grouping by category does one join. In a snowflake, product points to category, category points to department. Grouping by department now needs three joins. If that dashboard runs 20 queries and is refreshed 100 times a day, that's 2,000 extra joins daily. On a warehouse with 100 concurrent users, that adds up in CPU and queue time. The storage saving might be 10–20 GB on a 5 TB warehouse, not worth it.

But snowflaking is not always wrong. If the dimension is shared across many facts and changes frequently, normalizing it means you update one sub-dimension instead of rebuilding a wide dimension. That's a maintenance win. The failure mode is when a self-service user joins the snowflaked tables incorrectly and gets fan-out: for example, joining product to category and then to a fact that already has category, doubling the rows. That's a correctness bug, not just a performance one.

Data Vault is a different animal. It's an insert-only, auditable integration model with hubs, links, and satellites. It's warranted when you have many source systems, frequent schema changes, and a need for full history and lineage. But it is not a marts layer. If you expose Data Vault directly to analysts, they write 7-way joins and get it wrong. The pattern is Data Vault in the integration layer, then a star schema in the marts layer, built from the Data Vault.

So my answer: default to star for marts. Snowflake only when the dimension is large, shared, and the SQL writers are few and expert. Data Vault underneath when source integration and auditability dominate. And always measure: if a snowflaked query is slower than the star equivalent by more than the storage saving is worth, you've made the wrong call.

Curated: · Written: · Reviewed: —

Q&A-3Why does a warehouse still mint integer surrogates when the source already has a UUID?

Short answer: a surrogate key identifies the dimension row, not the customer. If the source UUID is immutable, unique across every source you load, and never versioned, use it as the fact key. Most warehouses mint an integer surrogate because at least one of those three fails, and the failure stays silent until a merge.

Assumptions that decide it: one source system, the UUID assigned once and never reused, no type-2 history on the dimension. Under those, the UUID is a fine fact key and an extra surrogate is overhead.

Where it breaks:

  • Type-2 history. One customer UUID maps to N dimension rows over time. A fact has to join to the version effective at event time, so the join key is (natural_key, effective_start) or a surrogate that encodes the version. A bare UUID cannot say which version.
  • Cross-source identity. Two systems assign different UUIDs to the same person, or a merge retires one. The UUID is unique per system, not per entity.
  • Merge. Deduplication retires UUIDs. Facts written before the merge still carry them.

The failure I have seen: facts joined customer on a CRM UUID. After merging 18,400 duplicate people into 9,200, 6.3 million order facts still pointed at retired UUIDs for 11 days, and lifetime value double-counted $4.8 million.

StagePeopleFacts on retired UUIDsDouble-counted LTV
before merge18,4000$0
11 days after merge9,2006,300,000$4.8 million

Why the tests stayed green: a uniqueness test on the dimension passes, because the surviving rows are unique. Nothing checks that facts resolve to exactly one effective dimension row. That check is the one that catches it:

select f.order_id
from fct_orders f
left join dim_customer d
  on f.customer_natural_key = d.customer_natural_key
 and f.ordered_at >= d.effective_start
 and f.ordered_at <  d.effective_end
group by f.order_id
having count(d.customer_sk) <> 1;

That returns facts with zero matches (retired UUID) or more than one (overlapping versions).

What I would do: keep the natural key as an alternate key on the dimension, enforce uniqueness on the current row or on (natural_key, effective_start) for type-2, mint the surrogate where versioned or cross-source identity needs it, and reject any fact whose natural key does not resolve to exactly one effective row.

To settle it: replay a natural-key merge and require every fact to follow the surviving surrogate while the retired natural key stays queryable as history.

Curated: · Written: · Reviewed:

Q&A-6Sales and support both have a customer table. What makes them conformed rather than merely similar?

Conformance is a claim about members, not about names. Two customer tables are conformed when the same real-world customer resolves to the same member key in both, at the same grain, with attribute definitions that agree on what each value means. The mechanical test is a drill-across: revenue by support ticket count returns correct totals with no reconciliation step inside the query. If you have to join on email and hope, you have two dimensions that happen to share a noun.

That is the bar. It is not always the bar worth paying for, and the choice between regimes is the part interviewers push on.

Fully conformed dimension. One customer dimension, both fact tables reference it. Every producer maps onto the published member key before facts load. Cost: the key and the type-2 attributes become a cross-team release. A new segment value is a change to a shared object. Right when the two domains must be compared in the same query and the entity is genuinely the same customer.

Conformed core plus satellite. Conform only what gets compared across domains (member key, name, segment, region) and leave domain-specific attributes (support tier, CRM owner, renewal date) in satellites keyed by the same member key. Deciding criterion: how many attributes are actually compared across domains. If it is 3 of 40, conforming all 40 buys nothing and makes every change a negotiation.

Domain-specific dimensions with a bridge. Sales keeps CRM id, support keeps email, a mapping table resolves them at query time. Right when the domains are only loosely related (a reseller's end users versus your own support accounts), when ownership of the key is contested, or when the mapping is genuinely many-to-many and unstable. Cost: every query pays the join, and unmatched rows fail silently. They drop out of the result rather than raising an error.

The deciding criteria, in order: does any query need both domains in one result; is the entity the same entity; how volatile is the mapping; who owns the key. Answer those and the regime picks itself.

Where the email-keyed bridge goes wrong, with illustrative figures: sales keyed customer on CRM id, support keyed on ticket email, and 12 percent of those emails were shared mailboxes. Cross-domain "customers with open tickets" under-counted 8,640 accounts for 6 weeks before anyone noticed.

DomainCustomer keyShared-mailbox emailsUnder-counted accounts
salesCRM idnot used—
supportemail12 percent8,640 for 6 weeks

Two customer tables, one mailbox. The bridge was the right regime for the mapping's volatility; it was the wrong regime for a metric that product read weekly.

Detection is the part that keeps this honest. Publish the unmatched rate per load, members each side claims that the other does not, and alert when it moves, not when it crosses zero. A bridge that is 2 percent unmatched and stable is a known cost; one that goes 2 to 9 percent in a week is a broken producer.

I would not call it settled without evidence: join the two dimensions on the published member key and require 0 unmatched members among entities both domains claim to know. Then look at the members only one side claims: that number, not the unmatched rate, is what the business is actually missing.

Same noun is not the same member. Same member is the contract.

Curated: · Written: · Reviewed:

Q&A-62Marketing wants a 360 view. 5 systems have people. What is the minimum identity architecture before you join behaviours?

The 60-second answer. The minimum is an identifier table, a resolution step that produces an enterprise person id, and a rule that facts join only on that id. Everything else (golden records, an MDM hub, a graph database) is optional. The rule that matters: no source system's native key is the person key, email included.

Assumptions I'd state out loud: five systems, each with its own primary key, no shared identifier today, and marketing wants behaviour joins this quarter. If two of those systems already share a verified unique key, the problem is smaller than it looks.

What "minimum" contains

  1. identifier(source_system, type, value_hash, confidence, first_seen, last_seen)
  2. person(enterprise_person_id, survivorship_rule_version)
  3. link(identifier_id, enterprise_person_id, rule_version, decided_by)
  4. a review queue for merges the matcher is unsure about
  5. every fact table joins on enterprise_person_id and nothing else

That is enough to join behaviour correctly. A golden record and a full MDM hub are later problems.

Deterministic vs probabilistic matching. Deterministic matching is exact match on normalised, verified identifiers: hashed email, E.164 phone, loyalty id. Precision is near 1 and every merge is explainable, which matters when someone asks why two customers became one. Its weakness is recall: the same human with three emails and two phones and no overlap stays three people. Probabilistic matching scores on name, address and date of birth, with a threshold. It recovers that recall, but precision is a function of the threshold and the blocking keys, and you need labelled pairs to measure it. Most production systems run deterministic first, probabilistic as a second pass, and route the band between the two thresholds to human review. Choosing only one is a real choice, not a default.

Build vs buy. A commercial identity-resolution product sells you the matcher, the review UI and the connector maintenance. You still own the survivorship rule and the definition of a person: that is the part vendors cannot decide for you. Building is justified when identity is the product, or when your identifier set is unusual (B2B domains, device graphs). Buying is justified when the identifier set is ordinary and the team is small.

When email-keying is acceptable. In a single-tenant B2C product where email is verified at signup and is the login credential, and the join is scoped to authenticated sessions, email is a fine key: it is an account key. It is also fine as a blocking key inside probabilistic matching. It stops being fine the moment it is promoted to the person key across systems that contain shared or role inboxes.

The failure mode. A 360 joined on email, illustrative figures from one such pipeline:

KeyRows sharing a keyPeople collapsedSpend misattributed
email9% of rows14 employees → 1 person$1.8M
enterprise person id00$0

The 9% were shared inboxes. One "person" accumulated fourteen employees' purchases and was targeted as a VIP. A 360 join on email is a group inbox with a credit card.

How you'd know it's broken. Watch the distribution of identifiers per person id: a person carrying fourteen keys across five systems is a merge bug, not a super-user. Put a shared inbox in the test fixture and require that it does not become a person owning employee purchases. Alert when a merge moves spend above a threshold between person ids.

Curated: · Written: · Reviewed:

Q&A-10Three CRMs each claim a golden customer. What does a golden record have to be for the warehouse to trust it?

What a strong candidate says first

A golden record is trustworthy to a warehouse when four properties hold together: a stable enterprise identifier that never changes, attribute-level survivorship governed by a published and versioned rule, provenance on every attribute (which source won, and when), and effective-dated history so a fact can be re-pointed without rewriting it. A nightly "winner" table with none of those is another duplicate with better branding.

State the assumptions that matter: several CRMs write independently, loads are batch, and warehouse facts currently key on local CRM ids. If exactly one consumer reads the data and nothing writes back, you may not need a physical golden table at all: a conformance layer in the warehouse can be enough.

Mechanism

Identity resolution runs first: deterministic keys (normalized email, tax id, national id) before probabilistic matching, with a human review queue for scores in the ambiguous band. Survivorship then runs per attribute, not per row. The row-level winner is what breaks.

AttributeRuleWhy not recency
legal_nameCRM rank 1, else billingBulk CRM jobs rewrite timestamps without changing names
billing_addressBilling always winsBilling owns the invoice; CRM is a copy
phoneMost recent verified valueVerification date, not row update date
enterprise_idAssigned once, never reusedRe-pointing facts is the whole point

A worked trace

Enterprise id E-4471, three sources: CRM-A (row updated 2026-02-03), Billing (2025-11-14), Support (2026-01-20).

  • legal_name → CRM-A, "Northwind Logistics LLC" (rank 1)
  • billing_address → Billing, "400 Harbor St" (billing owns it)
  • phone → Support (most recent verified)

Now a nightly CRM-A job touches every row and stamps updated_at = 2026-02-04 without changing a single value. Under last-write-wins, CRM-A now wins billing_address too. Under ranked survivorship, nothing moves, because rank decides and the timestamp is metadata. That is the entire difference, and it is why the rule has to be published rather than implied by a column.

Failure modes and how you detect them

  • Mass flip from a bulk job. In one illustrative incident, a CRM batch touched 410,000 stale rows and stole gold from billing: 19,200 addresses flipped and 1,140 invoices went to former offices. Detect it by monitoring per-source attribute churn per load and alerting when one source flips a large share of a high-cardinality attribute in a single run.
  • Unversioned rules. You cannot explain why a record looked a certain way last quarter. Detect it by replaying a past effective date and comparing output.
  • Broken alias table. Merges become unmergeable and facts drift back to source ids. Detect it by counting facts still keyed on a non-enterprise id; that count should trend to zero.
  • Over-merging. A shared household email fuses two real customers. Detect it with a precision audit on a labelled sample of merges, not on volume.

Alternatives, and when to skip the hub

Warehouse-side conformance (a modeled layer that resolves identity at query time) is cheaper and fine for read-only analytics. A source-of-truth-per-attribute registry without a physical golden table works when write-back is not required. A full MDM hub earns its cost only when systems must write the merged result back.

What the warehouse is allowed to assume

Join on enterprise_id, never on a source id. Every attribute carries its winning source and effective date. The rule version is queryable. If a load violates any of those, reject it: that contract, not the table, is what makes the record golden.

Curated: · Written: · Reviewed:

Q&A-11Legal says the contract address wins; marketing says the most recently clicked address wins. How does survivorship get decided?

The 90-second answer

Survivorship isn't one winner per record. It's a decision per attribute per consuming purpose, encoded as an ordered rule set that the consuming product owns. Legal and marketing are both right: for different attributes. The golden record carries billing_address (contract wins) and last_clicked_address (recency wins) as separate columns, each with its own rule. "Which wins" is only answerable as "for which product".

Engineering's job is to make that rule explicit, versioned and testable, not to pick a side.

How the rule set is evaluated

Rules are ordered; first match wins; ties break deterministically so the same input produces the same output on every run. A typical set for billing_address:

  1. Source is the contract system and the contract is active → take it.
  2. Otherwise the most recent non-null value by source_updated_at.
  3. Tie → higher source rank, then lower source record id.

Trace on one golden record, party P-4471:

sourcevaluesource_updated_atrank
contract_system12 Elm St2023-04-021
crm9 Oak Ave2024-11-183
web_clickstream44 Pine Rd2025-01-075

Rule 1 fires: billing_address = 12 Elm St. The same three candidates feed last_clicked_address, whose rule is "most recent by event time": 44 Pine Rd. Both survive; neither overwrites the other. Store the losing candidates and their provenance next to the winner, when the rule changes you re-run survivorship over the candidate list instead of having thrown the data away.

Trade-offs

  • One winner column: cheapest, one rule, but couples every consumer to one purpose's priority.
  • Per-purpose columns: extra storage and a contract per product, but no cross-purpose contamination.
  • No survivorship at all (expose candidates plus provenance, consumer picks at read time): maximum flexibility, but every consumer re-implements the rule and none can be guaranteed to avoid the wrong value.
  • Build-time vs read-time: read-time lets rules change without a rebuild, but makes results non-reproducible across queries and costs at scale.

Failure mode

Worked example, figures illustrative: one winner column fed every product, with recency as the rule. Marketing's clickstream overwrote 6,800 contract addresses. 420 dunning letters went to a campaign click address and collections paused 38 days. Detection is a reconciliation job that counts records where the surviving value differs from that attribute's system of record, alerting when the count crosses a threshold: plus a CI contract test per data product asserting its column resolves to the expected rule.

How to settle it

Export the legal product and the marketing product from the same golden record. Require two different address columns, two different rules in the contract, and a test that fails if the marketing export ever reads the legal column. One person can hold two surviving addresses; one product cannot.

Curated: · Written: · Reviewed:

Q&A-40You push warehouse segments back to the CRM. Which identifier do you send, and what happens if you send the warehouse surrogate?

What I send

The CRM's own durable identifier for that person: the CRM record id, or an enterprise/canonical id the CRM already stores and indexes as an external key. The warehouse surrogate (warehouse_customer_sk, dim_customer_id, whatever the load allocated) never leaves the warehouse.

Reverse ETL is an upsert against the destination's key, and the destination resolves identity by the key it knows. A surrogate is a warehouse-local integer: allocated by load order, reassigned when the dimension is rebuilt, unique only inside that warehouse. The CRM has no row for it and no way to derive one.

What happens if you send the surrogate

Two outcomes, both bad, and which one you get depends on the CRM's upsert semantics:

  • Upsert-on-external-id (Salesforce, HubSpot, most CRMs): unknown external id means insert. Every segment member becomes a new CRM record. The person now exists twice, and the segment push has manufactured the duplicates it was supposed to enrich.
  • Validate-against-known-ids: the CRM rejects or drops the row. The job reports success, the segment silently no-ops, and nobody notices until someone asks why the campaign never went out.

The first is loud and expensive; the second is quiet and also expensive. Neither is visible in the job's exit code.

Worked example (illustrative figures)

A nightly job wrote warehouse_customer_sk into the CRM's external id field for a 220,000-member "high-intent" segment. The CRM upserted on external id.

Identifier sentNew CRM recordsExtra outreach daysUnsubscribes
warehouse_customer_sk220,000944,000
mapped CRM id000

The 220,000 are duplicates of people already in the CRM. Outreach dedupes on email, so each person received the sequence twice; the unsubscribe spike is the tell. Detection: a daily count of CRM records created by the reverse-ETL integration user. For a segment push that count should be ~0, because a push should only update existing records.

The alternatives, and when each applies

KeyWhen it worksWhere it breaks
CRM-native id via crosswalkDefault. The CRM is the system of record for the person; you maintain a mapping table keyed on a stable enterprise idCrosswalk goes stale; unmapped members need a quarantine path, not an insert
Email as natural keySmall shops, single source, CRM already enforces unique emailEmail changes, role and shared inboxes, one person with several addresses, and case/whitespace normalization that differs between systems
Hashed identifier (e.g. SHA-256 of normalized email)You want a deterministic join key without moving PII, and both sides normalize identicallyHash of what, normalized how: any drift silently breaks the join; salt rotation breaks it wholesale; it is still not the CRM's id, so the CRM still needs a mapping
Let the CRM own the mappingThe CRM has mature identity resolution or an external-id API; you send source system + source idRequires CRM-side integration work and a resolution step; if the CRM cannot resolve, some CRMs insert anyway

The crosswalk is the default because the CRM is the authority on who a person is. Email-as-key is the tempting shortcut and the one that breaks first at scale.

How I'd verify before trusting it

Run the push against a test segment containing 3 members with no crosswalk entry. Require: 3 quarantined, 0 new CRM records, and 0 rows where the external id equals a warehouse surrogate. Then confirm in production that the integration user's record-creation count stays flat across a push.

Curated: · Written: · Reviewed:

Q&A-69Orders reference customer_sk. The customer product is owned by another domain. When do you fail the order load?

What a strong candidate says in the first two minutes

Clarify two things before answering: what "fail" means here (abort the whole batch, or reject only the offending rows) and whether the order fact needs real customer attributes downstream or just the key. Then the rule:

Fail the order load when the parent customer row is missing and the fact needs real customer attributes and the orphan rate is above the threshold you agreed with the consumer. Otherwise don't fail the batch: either infer a member or quarantine the rows. A null customer_sk is the one option to rule out.

Why a null FK is the wrong default

If customer_sk is null, every inner join to dim_customer drops the order. Revenue, order counts, and anything built on that join understate silently: no error, just a smaller number. That is the failure mode to design against.

Two ways to keep the join intact:

  • Inferred member: a placeholder row in dim_customer with a sentinel key (commonly -1 or 0) and 'Unknown' attributes. The FK is never null, so joins preserve the order. Cost: it is a fake customer. You must exclude the sentinel from customer counts and never use it for customer-level metrics like region or segment.
  • Quarantine: write the orphan rows to a side table, load the rest, reprocess when the parent arrives.

Deciding criteria

SituationActionWhy
Parent presentLoadNormal path
Parent missing, fact needs only the keyInferred memberPreserves joins; exclude sentinel from counts
Parent missing, fact needs customer attributesQuarantine rowsDon't corrupt customer-level metrics
Orphan rate above thresholdFail the loadA partial batch is worse than none for the consumer

Computing the threshold

Orphan rate = orphan rows / total rows, per run. Set the fail threshold from the baseline, not from zero: if normal runs sit at 0.05% and a lagging parent pushes it to 3%, that is a signal, not noise. Hypothetical: 2M orders/day, baseline 1,000 orphans, lag day 60,000. Fail above, say, 1% sustained across two runs; alert below it.

Failure modes to watch

  • Sentinel collision: if you pick 0 and a real customer_sk can be 0, you merge two entities. Pick a value outside the key space.
  • The inferred member inflates dim_customer row count: any dashboard counting customers goes up by one.
  • The quarantine table grows and nobody reprocesses it. Alert on its row count.
  • Threshold too tight and the load flaps on normal variance.

The check itself (dbt relationships test pattern):

select o.order_id
from {{ ref('fct_orders') }} o
left join {{ ref('dim_customer') }} c
  on o.customer_sk = c.customer_sk
where c.customer_sk is null

Run it with severity: error to fail the load, or severity: warn to alert and let the quarantine path handle the rows.

Curated: · Written: · Reviewed:

Q&A-9An order fact arrives before the customer dimension row exists. What do you put on the fact?

Put an inferred member on the fact. The first time a fact arrives with an unseen customer natural key, mint a dimension row: new surrogate key, the natural key, placeholder attributes, and a flag marking it inferred. The fact joins to that surrogate. When the real customer row lands, update the inferred row in place, same surrogate, and fill in the attributes.

Assumptions that decide this: the fact carries the customer's natural key, the dimension has a warehouse surrogate, and the fact load can't wait for the dimension. If the fact carries no customer identifier at all, that's a different case: one shared unknown member is the honest answer, and you should say so rather than pretend the row is attributable.

Why not the alternatives:

  • Drop the fact. Day-1 revenue is wrong and stays wrong until you replay the source.
  • Null surrogate. Every downstream inner join silently drops the row: the same loss, discovered later.
  • One shared -1 unknown member. Keeps the row but collapses every unknown customer into one, so distinct-customer counts and per-customer aggregates are wrong until repair.
  • Queue the fact until the dimension arrives. Fine when the dimension is minutes behind; it makes fact freshness depend on the slowest dimension, and a dimension that never arrives strands the facts.

Mechanism. The inferred row is a promise that the surrogate is stable. Repair is an update keyed on the natural key, not an insert:

-- Postgres
update dim_customer d
set name = s.name, segment = s.segment, is_inferred = false
from stage_customer s
where d.customer_nk = s.customer_nk
  and d.is_inferred;

If the dimension is Type 2, the inferred row's effective-from is the earliest fact date, so the real attributes backdate onto the facts that already reference it. History stays consistent without rewriting the fact table.

Failure modes worth naming:

  • Insert instead of update on repair → two members for one customer, and the facts split across them. Detect with select customer_nk, count(*) from dim_customer group by 1 having count(*) > 1.
  • Inferred rows that never get repaired. Detect with select count(*) from dim_customer where is_inferred and inferred_at < now() - interval '24 hours' and alert on it; a silent backlog of inferred members is how "unknown" becomes permanent.
  • Key normalization. C-9001, c-9001 and C-9001 are three natural keys to a naive loader. Normalize case and whitespace before the lookup, or you mint duplicates that repair never merges.

Worked example (illustrative figures). A retailer loads orders hourly and the customer dimension nightly. Orders for 27,400 first-time buyers land before their dimension rows.

HandlingDay-1 facts missingRevenue moved between weeksDistinct customers after repair
drop the fact27,400$2.1Mcorrect, but late
inferred, then upgraded0$01 per natural key

The drop path doesn't lose the money, it moves it: eight days of first-time-buyer revenue lands in one spike when the dimension finally loads, and week-over-week comparisons are wrong in both weeks.

How I'd verify it. Land one fact with customer C-9001, which the dimension doesn't have. Assert one inferred row exists and the fact points at it. Land C-9001 in the dimension. Assert: one dimension row for that natural key, one fact still pointing at the same surrogate, and no placeholder attributes left. Then assert no orphan facts: select count(*) from fact_order f left join dim_customer d on f.customer_sk = d.customer_sk where d.customer_sk is null returns 0.

Infer the member, keep the surrogate stable, and repair by update.

Curated: · Written: · Reviewed:

Q&A-4Product names change. How do you choose type 2 over type 1, and what must the fact still join?

The decision is whether the old value was ever true.

Type 1 is for corrections: the value was wrong, no fact should ever have referenced it, so overwriting is right and cheap. Type 2 is for state changes: the old value was true for a period, and any fact that happened in that period must keep reporting it. The test is one question, if I overwrite this attribute, does a number someone already published change? If yes, type 2. A product rename is a state change, not a correction, so it is type 2.

What the fact must still join. Not the natural key. The fact must resolve to the dimension's version key, the surrogate for the specific version, and store it. The join predicate is the event timestamp against the version's closed-open range: event_ts >= valid_from AND event_ts < valid_to. Two timestamps are in play and they are not interchangeable: event time (when the order happened) and load time (when the row landed). A late-arriving fact must join the version valid at event time, which may be several versions back.

-- dim_product: product_sk, product_nk, name, valid_from, valid_to, is_current
-- fact_order_line: order_line_id, product_sk, ordered_at, qty
INSERT INTO fact_order_line (order_line_id, product_sk, ordered_at, qty)
SELECT o.order_line_id, d.product_sk, o.ordered_at, o.qty
FROM   staging_order_line o
JOIN   dim_product d
  ON   d.product_nk = o.product_nk
 AND   o.ordered_at >= d.valid_from
 AND   o.ordered_at <  d.valid_to;

Trace it. dim_product holds two versions of SKU-7: (101, 'Widget', 2024-01-01, 2025-03-03, false) and (102, 'Widget Pro', 2025-03-03, 9999-12-31, true). Staging has (9001, 'SKU-7', 2025-02-14, 3) and (9002, 'SKU-7', 2025-03-10, 1).

  • 9001 against 101: 2025-02-14 >= 2024-01-01 true, 2025-02-14 < 2025-03-03 true → match. Against 102: 2025-02-14 >= 2025-03-03 false → no match. One match, product_sk = 101.
  • 9002 against 101: 2025-03-10 < 2025-03-03 false → no match. Against 102: both true → match, product_sk = 102.

Fact rows land as (9001, 101, 2025-02-14, 3) and (9002, 102, 2025-03-10, 1). The February order reports Widget forever, because it stored the version key, not the name. Swap the predicate to AND d.is_current and both rows get 102: the February order silently reports Widget Pro. That is the whole failure, in one line of SQL.

Worked example, illustrative figures: a category attribute was type 1. A rebrand on 3 March rewrote 14 months of facts. Year-over-year category mix moved 9.4 percentage points overnight with zero new orders, and three finance closes were restated.

HandlingFacts restatedMix shift, 0 new ordersCloses reopened
Type 1 overwrite14 months9.4 points3
Type 2 version000

Alternatives and when not to use type 2. Type 3 keeps one previous-value column: fine for a single was/now comparison, useless for arbitrary history. Type 6 keeps a type-1 current attribute alongside type-2 history, so one row answers both as-was and as-is; use it when analysts keep asking for the current name on a historical fact. A periodic snapshot fact stores the version key on the fact at snapshot time and removes the range join at query time, at the cost of multiplying fact rows. Type 2's real cost is not storage, a 200k-product dimension changing 5% a month adds 10k rows, it is the range join and the discipline of event time versus load time.

Failure modes and how to catch them.

  • Type 1 on a history-bearing attribute: retroactive restatement. Detect by rerunning last month's report against the current dimension and diffing; any change with zero new facts is the bug.
  • Natural-key-only join to the current row: the same restatement, silently. Detect by asserting the fact's stored version key is not always the current one.
  • Overlapping effective ranges: a fact matches two versions, fans out, and inflates sums. Detect by counting matches per fact row: anything above one is a broken range.
  • Late fact before the first version: no match, null attribute or dropped row. Detect by counting unmatched staging rows before the insert.
  • Two current rows: detect with a partial unique index on product_nk where is_current.

I would not call it settled without evidence: change a type-2 attribute after 1,000 facts have landed, and require those facts to still report the old value while new facts report the new one.

Overwrite is a correction; a version is a history.

Curated: · Written: · Reviewed:

Q&A-5Leadership wants last year's region and this year's region on the same row. When is type 3 enough?

Short answer: Type 3 is enough when the requirement is literally "the current value and the one value it replaced". Two states, one hop back, and the business has agreed the state before that is never queried. The moment someone asks for "the region as of last year's close" for an arbitrary year, or for the value before the previous one, type 3 has already lost the data and no query recovers it.

What the columns actually hold. Type 3 adds a previous_<attr> column beside the current one. Each write moves the outgoing value into the previous column and overwrites the current one. The row is a two-slot window that slides forward on every change; it is not a history.

Worked trace, one customer row (hypothetical figures):

Eventregion_currentregion_previousregion_changed_at
initial loadEMEANULL—
2023-03-01APACEMEA2023-03-01
2023-11-14AMERAPAC2023-11-14
2024-06-02EMEAAMER2024-06-02

After the third change the row offers only EMEA (current) and AMER (previous). The true region on 2023-06-01 was APAC, and the row cannot produce it: both values it can return are wrong. The original EMEA assignment is gone too: not archived, not derivable from the timestamp.

When it is enough. All of these hold:

  • The business names exactly two states it will query, and they are "current" and "immediately previous."
  • The change timestamp is kept, so you can at least tell when the previous value stopped being current.
  • The attribute carries no audit or regulatory lookback beyond one hop.

When it is not. Any of: a third state must be recoverable; the question is "as of date X" for arbitrary X; the attribute feeds a cohort or retention analysis that pins an entity to its state at signup. Then use type 2, a new row per version with valid_from/valid_to, or a separate full-history table.

Failure mode and how to detect it. The failure is silent. The columns stay populated and look healthy, so nothing alerts; you find out when an analyst asks for a value that was overwritten. Detect it before that with an acceptance test: apply three successive changes to a test row and require the first value to still be queryable. If it is not, type 3 is the wrong tool for that attribute. Also check the write path: an update that overwrites current without first copying it into previous loses the prior value one change earlier than the schema suggests.

Cost of the alternative. Type 2 costs rows, not columns: N changes per entity means N rows, plus an is_current flag or a valid_to IS NULL predicate on every read. For a customer dimension of a few million rows with a handful of changes a year that is cheap. For a high-churn attribute it is the reason people reach for type 3 in the first place, and the reason they later regret it.

Curated: · Written: · Reviewed:

Q&A-76A store moves postcode. Do historical sales change region?

No: historical sales keep the region they were sold in. A postcode is a property of the store now; the region on a sale is a property of the sale then. Overwriting the store's location restates history, and that restatement is a bug unless someone asked for it.

Three things to pin down before answering:

  • Which entity does region come from? If region is derived from the customer's address, a store move is irrelevant to it. The rest of this assumes region comes from the store.
  • Which "region"? The region the store sits in today (as-is) or the region it sat in when the sale happened (as-was). Both are real reports. Name them explicitly so one never silently becomes the other.
  • Did the postcode actually cross a boundary? A move within the same region changes nothing for region reporting. Only a boundary crossing does.

Mechanism. Keep store location as a type-2 dimension: one row per (store, region, validity interval). Join sales to the row whose interval contains the sale timestamp. The store's current region is then just the row with valid_to = 'infinity': a separate, explicitly named product.

-- Postgres 16
CREATE TABLE store_location (
  store_id   int         NOT NULL,
  region     text        NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_to   timestamptz NOT NULL DEFAULT 'infinity',
  PRIMARY KEY (store_id, valid_from)
);

-- as-was: the region in force when the sale happened
SELECT s.sale_id, s.amount, l.region
FROM   sale s
JOIN   store_location l
  ON   l.store_id = s.store_id
 AND   s.sold_at >= l.valid_from
 AND   s.sold_at <  l.valid_to;

Trace. Store 42 is North from 2019-01-01; it moves to South on 2024-06-01. A sale on 2024-03-15 for $120 joins the North row, because 2024-03-15 falls in [2019-01-01, 2024-06-01). Run the same query after the move and it still returns North. Under a type-1 overwrite, UPDATE store_location SET region = 'South', the same sale reports South, and nothing in the data records that it changed.

QueryRegion for the 2024-03-15 sale
as-was (type-2 join)North
as-is (current store row)South

Failure modes.

  • Type-1 overwrite. History is restated silently. Detect it by reconciling a closed historical period before and after any location change: the totals must not move. If they do, the change was type-1.
  • Late-arriving sales. A sale loaded after the move but with sold_at before it. Join on sold_at, never on load time, or it lands in the wrong region.
  • Overlapping intervals. A store that moves twice, or moves back. Close the old row (valid_to = new valid_from) in the same transaction as the insert; overlapping intervals double-count in the join.
  • Boundary redraw. Different problem: the store didn't move, the definition of the region did. That legitimately changes historical region under the new definition. Version the boundary and report both, rather than restating one in place.

Trade-off. Type-2 costs a join and grows the dimension with every move: trivial at store counts, and worth it. The as-is view is not wrong; it answers "which stores are in the North today", which is what logistics and staffing want. Build it as a view over the current rows, not by overwriting.

How I'd settle it: move a store across a boundary in a test, then assert that prior-year as-was totals are unchanged and that the as-is view reflects the new region.

Curated: · Written: · Reviewed:

Q&A-29Country codes rarely change. Why do they still need an effective dating policy?

The 90-second answer

A country code is not an identifier for a country. It is a label with a validity period. ISO 3166-1 alpha-2 codes get retired (AN, Netherlands Antilles, 2010), replaced (ZR → CD, 1997) and, the case that breaks systems, reused: CS meant Czechoslovakia until 1993, then Serbia and Montenegro from 2003 to 2006. ISO added an entire part, ISO 3166-3, purely to give retired codes a stable identity (CSHH for Czechoslovakia, CSXX for Serbia and Montenegro).

So the question is not whether the code changes. It is whether a fact row that stored the code can still say which country it belonged to. If the fact stores the code and the reference table is overwritten in place, a 1991 shipment labelled CS now reads Serbia and Montenegro, and no query can recover the truth. Effective dating: valid_from/valid_to on the reference member, plus an as-of join or a frozen surrogate key on the fact: is what preserves it. The cost is two columns and one extra join predicate.

Assumption worth stating: the code is being used as the business key on facts. If facts already carry a stable surrogate country ID, the pressure is lower, but you still need dating to answer "what was this called then" for invoices and customs documents.

Why "rarely" is the trap

Low change frequency is exactly what makes this dangerous. Nobody builds versioning machinery for data that never moves, so the one change arrives as a manual UPDATE, and the blast radius is every historical row that shares the code. The policy is cheap insurance against a rare, unrecoverable event.

Mechanism

The root failure is conflating the business key with entity identity. Fix it in two places:

  • Facts reference a surrogate country ID, not the code. A code reuse then cannot touch history at all.
  • The code-to-entity mapping is a type-2 (versioned) attribute: valid_from, valid_to, exclusive end. A type-1 overwrite is correct only when the old value was never valid: a typo, not a change.

Add transaction time (bitemporal) only if you must reproduce what a report said last quarter, not just what was true then.

Real changes, and why each one bites

alpha-2EntityPeriodWhat happened
CSCzechoslovakia–1993retired; code later reused
CSSerbia and Montenegro2003–2006reuse of a retired code
ANNetherlands Antilles–2010split into BQ, CW, SX
ZRZaire–1997renamed, code changed to CD
SZSwaziland2018–renamed Eswatini, code unchanged
MKMacedonia2019–renamed North Macedonia, code unchanged

The last two matter because the code is stable while the name is not, so the dating policy covers the mapping, not just the code.

The join, traced

Year granularity for readability; production uses dates.

-- country_ref: code, name, valid_from, valid_to (valid_to exclusive)
SELECT f.shipment_id, f.event_date, r.name
FROM   shipment f
JOIN   country_ref r
  ON   r.code = f.country_code
 AND   f.event_date >= r.valid_from
 AND   f.event_date <  r.valid_to;

Reference rows: ('CS','Czechoslovakia',1945,1993) and ('CS','Serbia and Montenegro',2003,2006). Fact: (8817, 1991-06-14, 'CS').

Row 1: 1991 ≥ 1945 true, 1991 < 1993 true → match. Row 2: 1991 ≥ 2003 false → excluded. One row, Czechoslovakia. Drop the two date predicates and the same fact matches both rows: double-counted in any GROUP BY country, or, if code is the primary key, silently relabelled Serbia and Montenegro.

Failure modes and detection

  • Code reuse: history silently reassigned. Detect by running the as-of join nightly and alerting on facts whose match count is 0 (code never valid at that date) or >1 (overlapping validity ranges).
  • Split: AN facts must stay on AN, not be redistributed to BQ/CW/SX. Same check catches it.
  • Late-arriving facts: a 2009 event loaded in 2012 must resolve against the 2009 version, which the as-of join does and a "current value" lookup does not.

When not to bother

If the attribute is purely cosmetic and never appears on a legal or financial document, a type-1 overwrite is fine. The moment a fact, invoice or duty calculation depends on it, version it.

Curated: · Written: · Reviewed:

Q&A-97Customer has 80 type-2 attributes. A volatile score changes daily. Why does that belong in a mini-dimension?

Short answer: a type-2 customer dimension versions the whole row. Put a daily-changing attribute on that row and every customer gets a new version every day, with all 80 attributes copied along. A mini-dimension moves the volatile attribute into its own small dimension with its own surrogate key on the fact, so the customer dimension only versions when a stable attribute actually changes.

First, check it is a dimension attribute at all. A score that changes daily and is consumed as a measurement is a fact: it belongs on the fact table as a measure, keyed by the customer version and the date. A mini-dimension is the right home only when the value is used for grouping and filtering (banding, tiering), where the distinct set of values is small. If a downstream model needs the exact score, keep it as a fact measure and let the mini-dimension hold only the band.

Why type-2 explodes. Type-2 keeps history by writing a new row per change, carrying every attribute. With 80 attributes and 1.1M customers, one change per customer per day is 1.1M new rows/day, roughly 400M rows/year, each row repeating all 80 columns. The fact's FK then has to resolve to the customer version valid at event time, so a 30-day scan touches 30x the customer rows it needs.

Worked example (illustrative figures):

PlacementCustomer rows after 12 monthsFact-to-customer join for a 30-day scan
on customer (type 2)~400M30 versions per customer, 2.4 TB spill
mini-dimension~1.1M (only stable changes)one small FK join, no spill

How the mini-dimension is built. Take the volatile attributes, band them, and load the observed combinations, not the full cross product. A score banded into 10 buckets plus two other volatile flags gives a few dozen rows, not millions. The fact carries two keys: customer_sk (the version valid at event time) and score_band_sk (the band valid at event time). Both are small integers, and the join is a hash lookup against a tiny table.

Trade-offs and when not to use it.

  • Banding loses the exact value. If a model needs the raw score, keep it as a fact measure and use the mini-dimension only for the band.
  • If you load raw daily scores instead of bands, cardinality approaches customers x days and you have moved the problem rather than solved it.
  • If the volatile attributes are many and independent, the cross product of bands can itself grow large; load only observed combinations and re-band when the distribution drifts.

Failure modes and detection.

  • Fact rows joined to the current band instead of the band at event time: history silently rewrites. Detect by re-running a closed period and diffing totals.
  • Mini-dimension cardinality creeping up: alert on row-count growth; if it grows with the fact, the banding is too fine.
  • Customer dimension still versioning daily: count distinct customer_sk per day; it should be flat.

Verification: change the volatile attribute for 30 consecutive days and require the customer version count to stay flat while the mini-dimension absorbs the changes.

Curated: · Written: · Reviewed:

Q&A-96Date of birth should never change. How do you implement type 0 without blocking legitimate corrections?

What I'd say first

Type 0 means the attribute never changes for a business reason: a person's date of birth is the same fact forever. It does not mean the column is frozen. A correction is a different event class: a wrong value was stored, and fixing it restores the truth instead of recording a change. So I'd build two paths: ordinary updates fail loudly, and a signed correction writes an audit row and updates the value in place, with no new dimension version.

Assumptions I'd state out loud: DOB sits on a dimension table that is the system of record; corrections are rare (tens a year, not tens a day); each correction carries a ticket and a named officer; downstream consumers read the current row, not a history.

Mechanism

Enforce with a BEFORE UPDATE trigger that rejects any DOB change unless a transaction-local flag is set. The flag is what separates the two paths, and set_config(..., true) makes it transaction-scoped so it cannot leak into the next statement.

-- PostgreSQL 16
CREATE TABLE dob_correction (
  ticket_id   text PRIMARY KEY,
  customer_sk bigint NOT NULL,
  old_dob     date   NOT NULL,
  new_dob     date   NOT NULL,
  officer     text   NOT NULL,
  signed_at   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION reject_dob_change() RETURNS trigger AS $$
BEGIN
  IF NEW.dob IS DISTINCT FROM OLD.dob
     AND coalesce(current_setting('app.dob_correction', true), 'off') <> 'on' THEN
    RAISE EXCEPTION 'dob is type 0; use correct_dob(ticket, officer)';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER dob_type0 BEFORE UPDATE ON dim_customer
FOR EACH ROW EXECUTE FUNCTION reject_dob_change();

CREATE OR REPLACE FUNCTION correct_dob(p_ticket text, p_officer text,
                                      p_sk bigint, p_new date) RETURNS void AS $$
DECLARE v_old date;
BEGIN
  SELECT dob INTO v_old FROM dim_customer WHERE customer_sk = p_sk FOR UPDATE;
  IF v_old IS NULL THEN RAISE EXCEPTION 'no such customer %', p_sk; END IF;
  IF v_old = p_new THEN RETURN; END IF;
  INSERT INTO dob_correction(ticket_id, customer_sk, old_dob, new_dob, officer)
    VALUES (p_ticket, p_sk, v_old, p_new, p_officer);
  PERFORM set_config('app.dob_correction', 'on', true);
  UPDATE dim_customer SET dob = p_new, dob_ticket_id = p_ticket
   WHERE customer_sk = p_sk;
END $$ LANGUAGE plpgsql;

Trace of the two paths against customer 42 whose stored DOB is 1988-03-02:

  1. UPDATE dim_customer SET dob = '1990-01-01' WHERE customer_sk = 42;: trigger fires, setting is off, exception raised, statement aborts, row unchanged.
  2. SELECT correct_dob('TCK-8812','officer.lee', 42, '1988-03-02');: v_old = p_new, returns without writing. Idempotent.
  3. SELECT correct_dob('TCK-8812','officer.lee', 42, '1988-03-05');: audit row inserted, flag set, update passes the trigger, commit clears the flag.
  4. Re-running step 3: primary-key violation on dob_correction.ticket_id, so a ticket cannot be replayed.

Failure modes and detection

  • Bulk ETL using INSERT ... ON CONFLICT DO UPDATE still fires the trigger, so it is rejected too: good, but it means a legitimate reload job will fail loudly rather than silently overwrite. Detect by watching for the exception in load logs.
  • TRUNCATE plus reload bypasses row triggers entirely. Revoke TRUNCATE from loader roles, and reconcile nightly: any dim_customer.dob whose dob_ticket_id is null but differs from the prior snapshot is a violation.
  • A writer that updates dob directly with the flag already set. Revoke column-level UPDATE (dob) from application roles and grant only EXECUTE on correct_dob; then the procedure is the sole writer.
  • A correction with no audit row. Alert on any DOB change where no matching dob_correction row exists for that customer_sk and timestamp.

Alternatives and when not to use this

Type 2 on DOB is the wrong tool: it records a correction as a change, so age-banded metrics see a person who used to be a different age. I've seen that: a form glitch produced 18,400 "corrections" and 18,400 versions, and age-band metrics moved 4 points. A shadow table splits the truth and leaves consumers reading the stale value. Database-level immutability with no correction path is worse than either: it forced 2,200 paper files in the same system. If corrections are frequent and genuinely business-driven, the attribute is not type 0 and should be modelled as type 2 or type 1 instead.

Verification

Run both statements in a test transaction: assert the plain update raises, assert the signed correction writes exactly one audit row and leaves dim_customer with one row for that customer, and assert the age-band aggregate is unchanged by the correction.

Curated: · Written: · Reviewed:

Q&A-99You need current region on every historical fact and the historical region too. What does type 6 actually store?

Type 6 stores two region values on every version row: the historical region for that version and the current region. The current region is a type 1 attribute: when region changes, you insert a new type 2 row for the new historical value, then update current_region on all rows for that customer to the new value. The latest row also gets is_current = true.

Assume a natural key customer_id, effective-dated versions, and a region change from East to West on 2024-03-01. Customer 42 has two rows:

customer_idregion_histeffective_fromeffective_tois_currentcurrent_region
42East2023-01-012024-03-01falseWest
42West2024-03-01nulltrueWest

Both rows carry current_region = West. The first row still answers as-was: East. The second row answers as-was and as-is: West. That is the mechanism: type 6 is type 2 history plus a type 1 current column denormalized onto every version.

Queries read the same dimension:

-- as-was: region at the time of the fact
select f.fact_id, d.region_hist
from fact f
join dim_customer d
  on f.customer_id = d.customer_id
 and f.event_date >= d.effective_from
 and (f.event_date < d.effective_to or d.effective_to is null);

-- as-is: current region for every fact
select f.fact_id, d.current_region
from fact f
join dim_customer d
  on f.customer_id = d.customer_id
 and f.event_date >= d.effective_from
 and (f.event_date < d.effective_to or d.effective_to is null)
where d.current_region is not null;

Trace two facts. Fact F1 has event_date = 2023-06-01, customer 42. It joins to the first row: region_hist = East, current_region = West. As-was returns East; as-is returns West. Fact F2 has event_date = 2024-04-01, customer 42. It joins to the second row: region_hist = West, current_region = West. Both return West.

If you write current_region only on the latest row, F1 joins to a row with current_region = null. The as-is query above drops F1. In one incident, that pattern dropped 14% of facts and $9.1M. The fix is not a second join to the current row; it is updating current_region across all versions when the type 1 attribute changes.

Test both directions. As-was: count facts by region_hist; the total must equal the fact count. As-is: count facts by current_region; the total must also equal the fact count. Add a dbt test that for each customer_id, count(distinct current_region) = 1 and count(*) where current_region is null = 0. If either fails, the dimension is a truncated type 2, not type 6.

Type 6 stores historical plus current on every version. Current-only-on-latest is a type 2 that cannot answer as-is without losing historical facts.

Curated: · Written: · Reviewed:

Q&A-100When do you keep a current table plus a history table instead of one type-2 dimension?

Short answer: default to type 2. Split into a current table plus a history table only when nearly every query wants the current row, the versioned rows are read rarely (audit, regulatory replay, backfill), and the fact table does not already carry the version key.

(The numbering is inconsistent across sources: Kimball's type 4 is a mini-dimension; the current-plus-history split is what many references call type 4. The decision below is the same either way.)

The question that decides it

Does the fact table store the dimension's version key at load time?

If yes, type 2 is nearly free. The fact row points at the version that was current when the event happened, so as-of is an equality join and no query reasons about dates:

-- type 2: as-of comes from the key already on the fact
SELECT f.order_id, d.customer_name
FROM fact_order f
JOIN dim_customer d ON d.customer_version_key = f.customer_version_key;

Splitting then buys you a smaller current table and nothing else.

If no: facts carry only the natural key, or facts arrive late and the version must be resolved at query time: you need a range join on effective dates whichever shape you pick. That is when the split earns its keep: the joins that want current hit a small table with a unique constraint on the natural key and a plain equality join, and only the as-of queries touch the versioned history.

-- current + history: the common query, no current-row filter needed
SELECT f.order_id, c.customer_name
FROM fact_order f
JOIN dim_customer_current c ON c.customer_key = f.customer_key;

-- as-of: must hit history, not current
SELECT f.order_id, h.customer_name
FROM fact_order f
JOIN dim_customer_history h
  ON h.customer_key = f.customer_key
 AND f.ordered_at >= h.valid_from
 AND f.ordered_at <  h.valid_to;

The two shapes side by side

Type 2 (one versioned dimension)Current + history
Rows per customerone per versioncurrent: 1; history: one per version
Fact joinequality on version key stored on the factcurrent: equality; as-of: range on effective dates
As-of correctnessfree at query timedepends on the as-of join being written correctly
Current-row queryneeds is_current filter or a current-row viewplain join, no filter
Unique constraint on natural keyimpossibleenforced on the current table
Growthdimension grows with every changehistory grows; current stays flat
Characteristic failureforgotten current filter double-countsas-of query written against current restates history

Failure modes, and how you catch them

Type 2's failure is a missing predicate: a report joins the versioned dimension without WHERE is_current, and a customer with four versions contributes four rows. Detect it with a row-count assertion per fact table against the natural-key count, and by exposing a dim_customer_current view so nobody writes the filter by hand.

The split's failure is a query that looks right and answers the wrong question. Facts joined only to current will restate history the moment someone corrects an attribute. Hypothetical example: 40M order facts, 2M customers, 6% of customers change an attribute per year. After eight years the type-2 dimension holds roughly 3M rows and every current-only join scans it; the split keeps current at 2M and history at about 1M. A name correction then rewrites the reported "customer name at order" for eight years of orders, and every order document regenerated from the warehouse disagrees with the one that was issued. Catch it with a regression test that corrects a current attribute and asserts as-of-order names are unchanged, plus an alert on any query joining a fact with an event time to the current table.

When type 2 is the better choice

Pick type 2 when as-of is the common case rather than the exception: point-in-time reporting is the product, facts are event-sourced, or the dimension is small enough that the extra rows cost nothing. Also pick it when you cannot trust the query layer to choose the right table: one join path means nobody can pick the wrong one. The split is a performance and constraint decision, not a correctness one; it never removes the need for an as-of join, it just moves that join off the hot path.

Curated: · Written: · Reviewed:

Q&A-107A customer's credit limit changes a few times a year. Finance must rebuild last quarter's exposure as it was booked; sales wants the current limit on every order. Which SCD type, and what does each choice cost?

First I'd pin down what "as it was booked" means, because it decides the type. If finance means the limit in force on the order date, that is point-in-time and only type 2 answers it. If they mean today's limit applied backwards, type 1 is enough, but that is a restatement, and I'd make them say so out loud before I build it.

Assuming point-in-time, the choice is type 2 on credit_limit alone, with a current-value access path for sales.

ChoicePoint-in-time accuracyRow growthDownstream join
Type 1none: overwrite loses the prior valueflatsimple, current state only
Type 2exact at any date+1 row per version of the tracked attributefact must carry the version's surrogate key
Type 3one prior value only+1 columnsimple, two columns
Hybrid (2 + 1)exact where it mattersonly tracked attributes growcurrent view for everyone else

The join is where this breaks. The fact should carry customer_sk resolved at load time, not a natural-key lookup at query time:

-- only if the fact carries the natural key, not the surrogate
select f.order_id, f.amount, d.credit_limit
from   fct_order f
join   dim_customer d
  on   d.customer_nk = f.customer_nk
 and   f.order_ts >= d.eff_from
 and   f.order_ts <  d.eff_to;

Drop the two date predicates and one order matches every version of that customer. A 40M-row order fact against a customer with four versions returns four rows per order and finance's exposure is 4x. You catch it with a uniqueness test on order_id after the join, or a row-count delta against the pre-join fact, not by eyeballing the dashboard.

Growth is the second cost, and it is arithmetic, not vibes. 40M customers, 8% change their limit in a year: 3.2M new rows, about 8% growth, tolerable. Type-2 all twelve tracked attributes and the union of change rates is what bites: if each changes independently at 8%, roughly 1 − 0.92¹² ≈ 63% of customers change at least one attribute, so ~25M new rows a year and the dimension doubles in about two years. That is the argument for tracking only the attributes the business actually reconstructs.

Two failure modes I'd design for. A late-arriving order dated before the customer's first version drops out of the join: you need an inferred row with eff_from at the earliest known date or negative infinity. And a backdated limit correction must insert a new version without renumbering existing surrogate keys, or every fact already loaded points at the wrong version.

Type 3 earns its place only when the question is literally "this year versus last year" and the business accepts losing the third change. Type 1 is right for corrections (a misspelled city, a casing fix) where the prior value was never true. The rule I'd state: pick by whether the business must reconstruct past state; if yes, type 2 on that attribute alone, and give consumers who only want today a current view over it.

Curated: · Written: · Reviewed: —

Q&A-108A customer dimension carries region, credit band, and legal name. Region changes twice a year, credit band changes weekly, and legal name is a correction. How do you apply SCD types 1, 2, and 3 per attribute, and what must the fact carry so a 2023 order still reports its 2023 region?

Two questions decide each attribute, not the dimension: does anyone need the value as it was when the event happened, and does the attribute change on its own schedule? Region and legal name answer differently, so they get different types on the same dimension.

Type 1 overwrites. The old value is gone. Correct for corrections (a misspelled legal name, a postcode fixed at source) and for attributes nobody reports historically. No new rows, no change to the fact join.

Type 2 inserts a new row with a new surrogate key and a validity range. Correct when history is a business requirement: region, because "sales by region for FY2023" must use the 2023 region, not today's.

Type 3 keeps one previous-value column on the same row. Only correct when the question is literally "did it change?" It cannot answer a 2021 question if the attribute changed twice since.

TypeRows addedAnswersFails when
10current value, correctionsaudit asks what it was
21 per changepoint-in-time, any number of changesstorage and join cost
30one before/after comparisona second change overwrites the prior

Worked trace: customer 4471 moves from EMEA to APAC on 2024-03-01:

customer_skcustomer_idregioncredit_bandvalid_fromvalid_tois_current
88124471EMEAB2023-01-012024-02-29false
99304471APACB2024-03-019999-12-31true

An order placed 2024-01-15 stores customer_sk = 8812. That is the whole point of type 2: the fact joins to the version, not to the customer. If the fact carries only customer_id and order_ts, you range-join instead, f.customer_id = d.customer_id AND f.order_ts >= d.valid_from AND f.order_ts < d.valid_to, which is correct but costs a range predicate on every query and silently picks the wrong row when the dimension arrives after the fact.

Storage is where the per-attribute call pays off. 10M customers, region changing twice a year: 20M new rows a year, roughly 2x the base. Credit band changing weekly: 520M rows a year, 52x. That is the signal to pull the volatile attribute into its own dimension so the stable attributes are not versioned 52 times over.

Failure modes and how they show up:

  • Two current rows for one customer_id: test count(*) where is_current group by customer_id having count(*) > 1.
  • Overlapping or gapped ranges: assert valid_to of row n equals valid_from of row n+1 minus one day, per natural key.
  • Late-arriving fact resolved against is_current at load time gets today's region. Resolve at event time, or range-join.
  • Type 3 quietly loses the second change; nobody notices until a restatement.

Default to type 1 for corrections, type 2 only where a named report needs point-in-time, type 3 only for a single before/after. The fact's key choice is what makes the type-2 decision actually work.

Curated: · Written: · Reviewed: —

Q&A-59You convert last year's invoices to USD. Which rate date do you use, and why is "latest rate" a restatement machine?

Use the rate as of the event date: the date the revenue was earned or the invoice was issued, whichever your accounting policy defines as the transaction date, or the contracted rate if the contract fixes one. Not the latest rate.

Two assumptions to state before answering: which date counts as "the event" (invoice date vs. delivery date) is a policy choice, and it has to be the same date every time; and the rate source plus quote direction (USD per JPY, not JPY per USD) are pinned, because inverting the pair silently changes the answer by four orders of magnitude.

Why latest-rate is a restatement machine: conversion is a pure function of (amount, currency, rate at a point in time). Replace the third argument with "whatever the rate table says right now" and the output becomes a function of when you run the query. Every rate-table update then retroactively rewrites closed periods. That is a restatement: prior-period figures move with no new transactions, reconciliation against the ledger breaks, and period-over-period comparison stops meaning anything.

The design that makes it impossible:

  • Store amount_minor and currency on the invoice row; never a pre-converted USD amount alone.
  • Join the rate table on the event date, with an explicit fallback for non-business days (prior business day), not "nearest".
  • Snapshot the rate and rate_date onto the converted row at write time, so the booked figure is immutable.
  • Forbid any view that joins MAX(rate_date). A current-value view is a different metric: label it as such, and never let it overwrite the booked amount.

The failure I've seen: a revenue view joined the latest USDJPY. A 6-yen move restated 11 months of JP revenue by 5.4%, $14 million, overnight, with zero new invoices.

Rate usedNew invoicesJP revenue moveYen move
latest0$14 million (5.4%)6
event date0$06

The test that settles it: change today's rate, then assert last year's converted amounts are identical. If they move, you have a restatement machine.

The rate is as-of the event, not as-of the query.

Curated: · Written: · Reviewed:

Q&A-60The business close is 4-5-4. The date dimension is Gregorian. What breaks in a year-over-year metric?

Assumptions. NRF-style 4-5-4: the fiscal year ends on the Saturday nearest 31 January, each quarter is 13 weeks split 4-5-4, and the metric is weekday-sensitive. Sales, traffic, units. If the metric is a point-in-time snapshot (inventory on hand, subscriber count), weekday drift matters far less and only the period mapping breaks.

What breaks. The Gregorian date dimension carries no fiscal year, period, week, quarter or close flag, so any YOY built from calendar arithmetic compares the wrong weeks. Two distinct breaks:

  1. Weekday drift. 52 weeks is 364 days, so date - 364 lands on the same weekday and the same fiscal week. date - 365 shifts the weekday by exactly one day. For weekday-sensitive metrics that shift alone moves the number.
  2. No 1:1 week in a 53-week year. Every five to six years the Saturday-nearest rule pushes the year end out by 371 days instead of 364, producing a 53rd week. The prior year has 52 weeks, so week 53 has no counterpart. Finance must declare the rule: repeat week 52, compare against week 1 of the following year, or exclude it.

Options and when each is right.

ComparisonAlignmentBreaks whenUse for
date - 365weekday shifts by exactly 1 dayany fiscal grainnothing fiscal
date - 364weekday and fiscal week preserved53-week years; date-level attributes across a leap dayquick day-grain checks in 52-week years
finance comparable-day keyexplicit prior week/period per fiscal week, week-53 rule encodedneeds re-approval each fiscal yearweek- and period-grain YOY, comps
restated prior yearprior year restated onto the current 52-week calendarno longer ties to reported figurestrend and comp metrics
as-reportedcompare to originally reported prior-year figuresnot comparable across a 53-week yearexternal reporting, guidance

Deciding criteria: the grain you compare at (day, week, period), the audience (external reporting versus internal comp), and whether the metric is weekday-sensitive.

Worked trace. Take a 52-week year ending Saturday 2028-01-29 and the 53-week year ending Saturday 2029-02-03: 371 days later. Week 53 runs Sunday 2029-01-28 to Saturday 2029-02-03.

  • 2029-01-28 - 365 = 2028-01-29, a Saturday: the last day of the prior fiscal year, week 52. Wrong weekday, wrong fiscal week.
  • 2029-01-28 - 364 = 2028-01-30, a Sunday: week 1 of the next fiscal year, not the prior one.

Neither arithmetic has a prior-year week to land in. Only a declared mapping does.

Failure modes and detection. Week 53 double-counted or silently dropped shows up as a Q4 period total that does not equal the sum of its weeks. Period-boundary drift shows as a date whose fiscal period differs between the fact table and the dimension: reconcile against the period-close flags. A leap day inside a 52-week fiscal year means -364 lands on the right fiscal week but the wrong Gregorian date, so any date-level attribute is off by a day. The signature of all three is a YOY variance concentrated in the final period of a 53-week year.

What I would require before signing off. Fiscal year, period, week, quarter and close flags on the conformed date dimension; a finance-approved comparable-day key with the week-53 rule encoded; and test fixtures covering a 52-week year, a 53-week year and a leap-spanning year, asserting that period totals equal the sum of their weeks.

Curated: · Written: · Reviewed:

Q&A-61Teams reorg every quarter. How do you report last quarter's bookings by this quarter's org without silently restating last quarter's bookings by last quarter's org?

Two numbers, not one. "Q1 bookings by Q1's org" is as-was; "Q1 bookings by today's org" is as-is. Both are legitimate and they answer different questions. The failure is publishing one under the other's label, so the first move is to name which one a report is and refuse to ship a metric that doesn't say.

Mechanically, version the org dimension type-2: one row per entity per version, with (entity_id, parent_id, valid_from, valid_to). Facts carry entity_id and event_date. As-was joins event_date BETWEEN valid_from AND valid_to. As-is joins the open row (valid_to = '9999-12-31'). Same fact table, two joins, two metrics.

When is as-is alone acceptable? When the org hasn't changed inside the reporting window, or when the consumer's question really is "who owns this now": a rep's current book, capacity planning, current P&L ownership. It's also cheaper: one equi-join to open rows, no range predicate, no version history to store. Type-2 costs roughly one row per entity per change; a 5,000-entity org reorged quarterly for three years is ~65k rows, which is nothing. The real cost is the join. A range join can't use a plain equi-index, so you index (entity_id, valid_from) and the planner does an index range scan per fact row: on a billion-row fact table that dominates the query. If that bites, snapshot the mapping into a bridge table keyed by (entity_id, period) so the join is equi again.

The deciding criterion is who consumes the number and what they do with it. Close packs, commissions and regulatory reporting must tie to the number as booked → as-was. Planning and current-org rollups → as-is. When both audiences exist, publish both as named metrics rather than picking one.

The failure mode is a type-1 overwrite. Hypothetical: a 5,000-entity org, Q1 bookings of $22M sitting in Division A. In Q2 a reorg moves that team to Division B and someone updates the dimension in place. The signed Q1 close pack says Division A $22M; the warehouse now says Division B $22M, and nobody changed a fact row.

ReportQ1 bookings land inTies to Q1 close pack
as-is only, type-1 overwriteDivision Bno: $22M moved
as-wasDivision Ayes
as-isDivision Bn/a: different question

Detection: reconcile the as-was rollup against the signed close pack every period. A nonzero delta on a closed period is the alarm. The upstream tell is any dimension update that changes a row's parent without closing the old row and opening a new one.

To prove it rather than assert it: reorg a team, then require as-was Q1 to stay put while as-is Q1 under the new parent moves. If as-was moved, the dimension isn't actually versioned.

Curated: · Written: · Reviewed:

Q&A-98Two facts both have amount. When are they conformed, and when is a SUM across them a crime?

The 90-second answer

Two facts are conformed when their measures are the same thing: same definition, same grain, same additivity, same units and currency, same time semantics. When all five match, SUM(a) + SUM(b) is arithmetic over one population and it is fine. When any one differs, the sum is a crime: it adds two overlapping populations, or adds a stock to a flow.

The check I run first is not the column name, it is the grain and the business event. order.amount and shipment.amount are both money, both additive, both USD, and summing them is still wrong, because an order that shipped appears in both facts.

What "conformed" actually requires

Five checks; all must pass:

  1. Definition: same business event, or a declared rollup of it.
  2. Grain: same grain, or one is a strict rollup of the other with no fan-out.
  3. Additivity: both fully additive over the same dimensions. A balance (inventory on hand) is semi-additive over time; adding it to a flow (units sold) is meaningless.
  4. Units and currency: same currency, same conversion date.
  5. Time semantics: booked, billed and shipped are different events.

Two teams both naming a column amount passes none of these. That is the most common trap.

When the SUM is a crime

Three shapes, each with a detection:

  • Mixed grain: header amount vs line amount. Detect: SUM(header.amount) != SUM(line.amount) over the same orders.
  • Overlapping events: orders vs shipments. Detect: join on order_id and count orders present in both.
  • Mixed additivity: balance plus flow. Detect: does the measure survive re-aggregation over time?

Worked example (hypothetical fixture)

Orders and shipments, one row per shipment, partial shipments allowed:

order_idorder_amountshipped_amount
O1100100
O2200200
O33000
O4400150

SUM(order_amount) = 1000. SUM(shipped_amount) = 450. The naive SUM(order_amount) + SUM(shipped_amount) = 1450, which is 45% above the ordered total: the 450 shipped is already inside the 1000 ordered. The correct output is two metrics (ordered 1000, shipped-to-date 450) or one metric at one grain.

SQL that makes the overlap visible:

-- the double-counted set: shipped amount whose order is also in the orders fact
SELECT COUNT(DISTINCT s.order_id) AS overlapping_orders,
       SUM(s.amount)               AS double_counted
FROM shipments s
JOIN orders o ON o.order_id = s.order_id
WHERE s.ship_date BETWEEN :start AND :end;

On the fixture this returns overlapping_orders = 3 (O1, O2, O4) and double_counted = 450: exactly the amount the naive sum counts twice.

What to do instead

  • Publish one certified metric per grain and forbid certified SUM across grains.
  • If the business wants a single number, build a bridge or allocation: allocate order amount to shipments by shipped quantity, so the allocation sums to 100% of the order and never exceeds it.
  • Aggregate to the coarser grain first (order), then carry shipment status as an attribute, not as a second amount.

Failure modes

The failure is silent: the number is plausible, so nobody checks it. Detection is a fixture with overlapping business events: assert that the certified metric equals the single-grain total, and that any allocation sums to 100%. A metric that cannot pass that is not conformed, whatever its column is called.

Curated: · Written: · Reviewed:

Q&A-103A claim carries several diagnoses, and finance wants the cost split across them. How do you model that so the split still sums to the claim total?

Keep the claim amount on the fact, once. Put the many-to-many in a bridge table that carries an allocation weight, and make that weight sum to exactly 1.0 per claim. Then every allocated number is measure * weight, and the sum over any set of diagnoses reconciles to the unallocated total.

The shape

fct_claim            claim_sk, member_sk, service_date_sk, claim_amount
bridge_claim_diag    claim_sk, diagnosis_sk, diagnosis_role, weight
                     PK (claim_sk, diagnosis_sk, diagnosis_role)
dim_diagnosis        diagnosis_sk, icd10_code, description

Claim C1 is $1,000 with three diagnoses:

claim_skdiagnosis_skroleweight
C1I10primary0.50
C1E11secondary0.30
C1J45secondary0.20
SELECT d.icd10_code,
       SUM(f.claim_amount * b.weight) AS allocated_amount
FROM fct_claim f
JOIN bridge_claim_diag b ON b.claim_sk = f.claim_sk
JOIN dim_diagnosis d    ON d.diagnosis_sk = b.diagnosis_sk
GROUP BY d.icd10_code;

Drop the * b.weight and C1 contributes $3,000 to a $1,000 claim. That is the whole reason the bridge exists rather than a diagnosis_sk column on the fact: three diagnoses on the fact means three rows, the claim amount repeated three times, and every SUM(claim_amount) wrong unless someone remembers to dedupe.

Where the weight comes from

Equal split (1/n), a primary/secondary rule (0.7/0.2/0.1), or a cost-based split from the source system. Whichever it is, store it in the bridge and version it: do not compute it in the query. A weight that lives in a report is unauditable and drifts between dashboards. If the source has no defensible split, say so and use equal split; a made-up clinical weighting is worse than an honest one.

When not to weight

If nobody allocates, you do not need weights at all. A factless fact table, (claim_sk, diagnosis_sk) with no measure, answers "how many claims had this diagnosis" and cohort questions correctly and cheaply. Add weights only when a measure has to be divided. And if exactly one diagnosis drives a metric, put a role-playing primary_diagnosis_sk on the fact and keep the bridge for the rest; the common case then avoids the join.

Failure modes

  • Weights that do not sum to 1.0. Three-way equal splits round to 0.333333 and lose a millionth. Use decimal(9,6), let the largest weight absorb the remainder in the ETL, and test sum(weight) per claim within 1e-6.
  • A claim with no diagnosis. An inner join silently drops it, so allocated totals never reconcile to the claim total. Add an "unknown" diagnosis member with weight 1.0, or left-join and report the unallocated bucket explicitly.
  • Duplicate bridge rows after a re-run. Each row passes a per-row weight test while the per-claim sum is 2.0. Test the sum per claim, not per row, and make the load idempotent on the bridge's primary key.
  • The bridge changes. A corrected diagnosis assignment restates history unless the bridge is effective-dated or snapshotted per load date. The fact total never moves; the allocation does. Put that in the contract so finance is not surprised.
  • Late-arriving diagnosis. The claim loads Monday, the bridge row lands Tuesday. Yesterday's allocated report changes. That is correct behaviour, but only if the bridge is loaded before the report runs: otherwise you are publishing a number you will contradict.

The test that catches all of this: for every claim, SUM(claim_amount * weight) over the bridge must equal claim_amount on the fact. Run it as a data-quality check, not a reconciliation someone does by hand at close.

Curated: · Written: · Reviewed: —

Q&A-30A metric is "orders per hour." Which clock is the hour, and what breaks if you pick the processor's clock?

The hour is the event's hour: the timestamp the order was placed, as recorded by the system of record, truncated to the hour in the reporting timezone. Say which zone out loud (UTC, or the business's local zone with a stated DST rule), because that choice moves orders across the boundary twice a year.

Processing time is when the pipeline saw the row. It is a property of your collector, not of the order. A metric bucketed on it measures the pipeline's health, not the business.

Five orders, one collector outage:

orderplaced (event)arrived (processing)
A10:0510:06
B10:5010:51
C11:1011:12
D10:2013:40
E11:3013:41

Hourly counts under each clock:

hourevent timeprocessing time
10:003 (A, B, D)2 (A, B)
11:002 (C, E)1 (C)
12:0000
13:0002 (D, E)

Processing time reports a dead hour at 12:00 and a doubling at 13:00. Neither happened. Event time reports 3 and 2, which is what the business did.

What breaks, concretely:

  1. Wrong buckets, permanently. Late events land in the hour they arrived, and nothing moves them back.
  2. No replay. Processing time is not reproducible: re-running the job tomorrow assigns different hours. Event time is a function of the data, so a backfill reproduces the same series. This is the one that kills you: a processing-time metric can only be restated, never corrected.
  3. Downstream systems act on the artefact. The 13:00 spike trips a threshold alert, autoscales the consumer, and can pause a campaign; the 12:00 hole trips a no-data alert.
  4. Watermarks. Event-time windows need a watermark and an allowed-lateness bound, or windows never close under unbounded lateness. Processing time needs neither, which is exactly why it is tempting.

When processing time is right: freshness and ops metrics. Consumer lag, end-to-end latency, "did the pipeline stall". Those are genuinely about the pipeline. Also when the source has no trustworthy event timestamp at all.

The trap inside "event time": it is only as good as the producer's clock. A client-side timestamp from a phone can be minutes off or user-set. Prefer the timestamp assigned by the system of record (server receipt at the order service) over a device clock, and treat anything from the client as suspect.

Failure modes and how to detect them:

  • Silent misbucketing. The event-time and processing-time hourly series diverge by more than the known late-arrival rate. Alert on the divergence, not on either series alone.
  • Watermark too tight. Events dropped as too late. Count dropped-late events as a metric; nonzero means the allowed lateness is wrong.
  • Watermark too loose. State grows and windows stay open. Watch state size per key.

Validation: replay one day of events with a 3-hour delay injected into arrival times, and diff the hourly event-time series against the undelayed run. It should match exactly; the processing-time series will not.

Curated: · Written: · Reviewed:

Q&A-31Events arrive from 14 countries. What do you store, and why is a naive timestamp a warehouse defect?

Store the instant, and store the zone that produced the local reading.

Two columns on the fact table: event_ts timestamptz (the instant) and source_tz text (IANA id, e.g. Europe/Paris). Derive local civil time at query time with event_ts AT TIME ZONE source_tz. Never put a naive timestamp on a fact table.

Why: in Postgres (checked against 16), timestamptz stores an instant (microseconds since 2000-01-01 UTC, an int64) and nothing else. It does not remember a zone. So if you need "the local business date this event belongs to", the zone has to live in a column, not in the type. A naive timestamp is a wall-clock reading with no offset: it is not an instant, and it cannot be turned into one without guessing.

The 14 countries matter because a country is not a timezone. The US, Australia and Brazil span multiple zones, and not every country observes DST. Store the IANA id per source, not a country code and not a fixed offset.

Worked example, Europe/Paris 2024 (real transitions):

  • Spring forward, 2024-03-31: local 02:00 jumps to 03:00. Local 02:30 does not exist. A source emitting naive local wall clock produces a value that maps to no instant.
  • Fall back, 2024-10-27: local 02:00-03:00 occurs twice. Once at +02:00 (00:00-01:00 UTC) and once at +01:00 (01:00-02:00 UTC). Two distinct instants, one identical naive value.

Trace: two events, both naive 2024-10-27 02:30. Event A is 00:30 UTC, event B is 01:30 UTC. Stored naive, they are byte-identical, so any dedup key, uniqueness constraint or ORDER BY on that column treats them as the same point in time. Stored as timestamptz with source_tz = 'Europe/Paris', they are 3600 seconds apart and both render as 02:30 local.

CaseLocal clockNaive storagetimestamptz + zone
Spring forward 2024-03-3102:00-03:00 absentvalue with no instant; dropped or silently shiftedno such instant exists; reject at ingest
Fall back 2024-10-2702:00-03:00 twicetwo instants collapse to one valuetwo instants, 3600 s apart, both 02:30 local
Ordinary dayone reading per instantworksworks

Failure modes this creates downstream:

  • Daily close by local date: events in the repeated hour land in the wrong bucket or double-count; a GROUP BY local_date across the transition is off by up to an hour of volume.
  • Dedup and idempotency: a unique key on the naive timestamp silently drops the second occurrence.
  • Cross-job joins: one job reads naive as UTC, another as Paris, so the same row joins to different instants.

Detection: assert every fact has a non-null source_tz; assert no local wall-clock value falls in a known gap for its zone; count distinct instants against distinct naive values across a transition. They must differ by exactly the overlap volume.

When a fixed offset is enough: if you only ever need the instant and never local civil time, timestamptz alone is fine. The moment a report says "close of business in Paris", you need the zone.

Verify by loading a day of events across both 2024 transitions and requiring identical UTC instants and one local date per event.

Curated: · Written: · Reviewed:

Q&A-32Amounts from JP and US land in one fact. What must be on the row besides the number?

The 90-second answer

Besides the number, the row needs the currency code and the unit of measure, and if it also stores a converted amount, the rate and the date that rate applies to. Currency is part of the fact's grain, not a descriptive attribute. That is what makes the column non-additive across currencies instead of silently wrong.

Why the row needs it

A measure is only a measure if it is additive within its grain. A column of amounts in mixed currencies is not: the sum has no unit. Two designs resolve this, and you pick one on purpose.

  • Original only. Store amount + currency_code, keep currency in the grain, and convert in the semantic layer against a dated rate table. The raw column is never summed across currencies; the certified metric converts first.
  • Dual amount. Store amount_original + currency_code alongside amount_base + rate + rate_date. amount_base is additive, but only valid for the rate_date stamped on the row.

Most warehouses take the second, because it turns the certified metric into a plain SUM over amount_base.

ColumnExampleWhy it is there
amount_original81000000the number as booked
currency_codeJPYISO 4217; joins to the rate table
unitcurrencywhat the number counts (currency, kg, each)
amount_base740402converted; the additive column
rate109.4JPY per USD
rate_date2019-04-01which rate produced amount_base

Failure modes

  • Mixed-currency SUM. Yen and dollars added as "amount." 40,000 JP rows at ¥81,000,000 and 40,000 US rows at $81,000,000 produce a raw SUM of 162,000,000 with no unit. At an illustrative 109.4 JPY/USD, ¥81,000,000 is $740,402, so the valid total is $81,740,402: the raw 162 million is off by roughly 2x.
  • Rate drift. Converting a 2019 transaction at today's rate. The converted amount moves every day and stops reconciling to the ledger. Pin rate_date.
  • Free-text currency. "Yen", "JPY", "¥" as three distinct values. Nothing joins to the rate table and the grain silently splits.
  • Missing unit on non-money measures. kg vs lb, each vs case: same failure, different column.

How I'd check it

Insert two rows with the same numeric amount and different currency_code. The certified metric must convert before adding; the raw column must refuse or be excluded from the metric. If the raw SUM returns 2x, currency is not in the grain.

Curated: · Written: · Reviewed:

Q&A-12A producer wants to add a field to an Avro topic the warehouse consumes. What is compatible, and what is a break?

Core answer. Adding a field to an Avro record is compatible when the field carries a default. The producer's new schema has the field; the warehouse's existing reader schema doesn't know it, and Avro resolution ignores writer fields the reader lacks, so the current consumer keeps decoding new data unchanged. A reader that does know the field reads old data by filling it from its own default. One default covers both directions, which is why the registry reports this as FULL compatible.

Adding a field without a default is a break in the direction the registry checks: a new schema that adds a required field cannot read data written by the previous schema, so Confluent Schema Registry in BACKWARD (its default) or FULL mode rejects the version. The warehouse's current reader still works, it ignores the extra field, but the first consumer that upgrades to the new schema and touches historical data fails resolution.

The mechanism, concretely. Writer v1 is {id}. Reader v2 adds channel with a default:

{"type":"record","name":"Order","fields":[
  {"name":"id","type":"string"},
  {"name":"channel","type":"string","default":""}
]}

Resolution runs field by field against the writer schema:

  • v1 record, v2 reader: id copied; channel absent in the writer, so the reader's default "" is used.
  • v2 record, v1 reader: id copied; channel present in the writer but not the reader, so it is dropped.
  • v1 record, v2 reader where channel has no default: resolution fails, missing required field channel, before any record is emitted.

Compatible vs breaking.

ChangeBACKWARD (default)FULLOld reader on new dataNew reader on old data
add field with defaultacceptedacceptedfine (ignored)fine (default fills)
add field, no defaultrejectedrejectedfinefails resolution
add field, no default, mode FORWARD or NONEaccepted—finefails at read time
remove fieldrejectedrejectedfails if it had no defaultfine
change field typerejectedrejectedfailsfails
rename field without aliasesrejectedrejectedfailsfails

So the safe producer change is: add the field with a default (use null in a ["null","string"] union for genuinely optional data), and let the registry's BACKWARD check gate the publish. Renames need aliases; type changes and removals are breaks.

When a break is acceptable. A breaking change is fine when nothing reads across the boundary: publish to a new topic (subjects are per-topic, so a new subject starts clean), or run a dual-schema migration where the producer writes both shapes and consumers move before the old shape is dropped. It is also fine when every consumer upgrades in lockstep and no retained data is ever re-read: rare for a warehouse, which is exactly why the default is BACKWARD.

What the registry does and does not do. The registry validates schema resolution rules; it does not decode your retained files. So the check you own is separate: replay the new reader against a sample of historical files before publishing. That catches the case the registry can't: a field that resolves on paper but whose default is wrong for the data already on disk.

Failure mode to watch. A required field added with no default passes in a FORWARD or NONE subject and fails only when a consumer upgrades and reads old data: a delayed, consumer-side break. Detect it by pinning the compatibility mode to BACKWARD or FULL and by running the replay above in CI, not by trusting the publish to succeed.

Curated: · Written: · Reviewed:

Q&A-17A producer changes an amount from integer cents to decimal dollars. Why is that not an additive change even if the column name is the same?

What a strong candidate says first

"Additive" is a claim about consumers, not about whether the DDL succeeds. A change is additive when every existing reader keeps interpreting existing data the same way. What a column carries is (name, type, unit, nullability, semantics); the name is one of five. Cents to dollars keeps the name and changes the unit, so every reader that was correct yesterday is wrong today, with nothing raised to tell them.

So: not additive. It is a breaking change the database will happily apply.

Why it is silent

The bytes do not move. Only the reader's interpretation changes. Take an order of $19.99, stored as 1999 cents. After the change, a reader that assumes dollars renders 1999 as $1999.00: a 100x inflation on every row, no exception. The error is asymmetric. Read paths inflate 100x; write paths still emitting cents into a column the new code reads as dollars deflate 100x. A dashboard and an invoice then disagree by 10,000x and both look plausible.

Columnar formats make it worse. Parquet and Iceberg files carry their own physical type, so historical files still say int32 while new code assumes decimal(18,2). One logical column now holds two physical meanings, and which one you get depends on the file's age. Aggregations spanning both are meaningless.

Why the tooling does not catch it

ALTER TABLE ... ALTER COLUMN TYPE succeeds. If you have a schema registry, Avro's resolution rules permit int->long, int->float, int->double, long->float, long->double, float->double; int->decimal is not on that list, so a BACKWARD-compatible registry rejects the change. That check is what saves you. Without a registry, nothing fires: no compile error, no diff alert, because the column name is unchanged.

What to do instead

Add amount_usd as a new nullable column, dual-read (prefer usd, fall back to cents), backfill with an explicit conversion, verify, then drop amount_cents once every consumer has switched.

ApproachReader impactCostUse when
Add amount_usd, dual-readnone during windowtwo columns, backfill, dual-writedefault for a live column with readers
Version the name (amount_cents_v2)none, but consumers must migraterename churnyou want the break to be loud
New table or topic versionnonefull migrationstreaming, or schema already versioned
In-place type changesilent 100x errornoneno historical files and no readers

Failure modes and how you would see them

  • 100x inflation on read paths: dashboard GMV, ML features, invoices. Detect with a shadow column: write both, compare, alert when the ratio leaves [0.999, 1.001].
  • Mixed-unit aggregation: sums over rows written before and after the flip. Detect by partitioning the check by write timestamp.
  • Rounding loss on write: dollars to cents truncating fractional cents, or float/double instead of decimal reintroducing binary representation error. Detect with a round-trip test: cents -> dollars -> cents must be identity.

When in-place is fine

A fresh table with no files and no consumers, or a widening promotion the format actually supports (int->long). Otherwise the name being the same is exactly what makes it dangerous.

Curated: · Written: · Reviewed:

Q&A-13A team renumbers a Protobuf field because the name was ugly. What did they just do to every stored payload?

Nothing on disk changed, and nothing is corrupted, but every stored payload now reads that field as unset. Protobuf puts field numbers on the wire and never puts names there. Renaming amount_cents to amount in the .proto is free: the bytes are identical and every old payload still decodes. Renumbering is a different act. Old payloads still carry the value under the old number; the new schema doesn't know that number, so the decoder files those bytes as an unknown field and reports the new number as absent, which for a scalar means the default, 0 or "".

Mechanism: each field is written as a tag varint (field_number << 3) | wire_type, then the value. A payload written with amount_cents = 1500 at field 3, varint:

tag   = (3 << 3) | 0 = 24 = 0x18
value = 1500 -> varint 0xDC 0x0B
bytes = 18 DC 0B

Decode that with a schema where the field is now number 9. The reader sees tag 0x18, resolves field 3, finds no field 3 in the schema, and stores 18 DC 0B in the unknown field set. Field 9 is not present, so amount returns 0. The 1500 is still in the buffer; it just isn't reachable by name anymore. If the new field 3 were a string, the tag's wire type (0) wouldn't match the declared type (2), and the parser skips it as unknown rather than converting it.

MoveEffect on stored payloadsMigration cost
Rename only, same numbernone; bytes identicalnone on the binary wire; breaks proto3 JSON and name-keyed sinks
Renumberold number becomes unknown field; new number reads defaultbackfill or dual-read every stored payload
Add new number, reserve oldboth decode; old readers keep workinglazy; write new, read both until drained
New message typeclean breaknew topic/table, dual-write window

Deciding criteria: if the complaint is the name, rename. It costs nothing on the binary wire. Renumber only if the number itself is wrong (collision, reserved range), and then treat it as a data migration, not a schema edit. Note that a rename is not free everywhere: proto3 JSON uses the field name, and columnar sinks keyed on names (Parquet, BigQuery) see the rename as a dropped and added column.

Failure modes: silent zeros in aggregates. A revenue column summing to 0 while row counts stay green, because the rows still arrive, just without the value. Unknown fields survive re-serialization, so a proxy that decodes and re-encodes carries the orphaned bytes forward and can mask the break. Detection: decode a payload written under the old schema with the new one and assert the field is present; a schema-registry BACKWARD compatibility check fails on a renumber precisely because the new schema can't read old data.

Hypothetical scale: 40M rows/day, three days before anyone notices, is 120M rows with amount = 0.

Curated: · Written: · Reviewed:

Q&A-16The registry offers BACKWARD, FORWARD, and FULL. Which one does a warehouse that must reread last year's files actually need?

BACKWARD_TRANSITIVE, or FULL_TRANSITIVE if the warehouse also writes to a subject other teams read. Plain BACKWARD is the wrong setting for a reader that touches last year's bytes, and the reason is mechanical.

What the modes actually check. Compatibility is a direction plus a history depth. BACKWARD means the new schema, used as a reader, can decode data written by the previous schema. FORWARD means the previous schema can decode data written by the new one. FULL is both. The _TRANSITIVE suffix changes the depth: the check runs against every registered version in the subject, not just version N-1. Confluent Schema Registry's default is BACKWARD, which is how the gap gets inherited without anyone choosing it.

Why BACKWARD is not enough. Adjacent compatibility is not transitive. Each upgrade can pass against its immediate predecessor while the newest reader has drifted arbitrarily far from version 1. A field added six months ago with a default satisfies BACKWARD for every registration after it; a reader that later requires that field passes BACKWARD against the recent version and fails on the older files.

Worked trace, Avro, three versions of subject orders-value:

// v1, registered 11 months ago
{"type":"record","name":"Order","fields":[
  {"name":"order_id","type":"string"},
  {"name":"amount_cents","type":"long"}]}

// v2, registered 6 months ago
{"type":"record","name":"Order","fields":[
  {"name":"order_id","type":"string"},
  {"name":"amount_cents","type":"long"},
  {"name":"region","type":"string","default":"US"}]}

// v3, the new warehouse reader
{"type":"record","name":"Order","fields":[
  {"name":"order_id","type":"string"},
  {"name":"amount_cents","type":"long"},
  {"name":"region","type":"string"}]}

Under BACKWARD, v3 is checked only against v2. v2 carries region, so the reader's required region resolves and registration succeeds. Under BACKWARD_TRANSITIVE, v3 is also checked against v1. v1 has no region and the reader supplies no default, so Avro resolution fails on a missing required field. The registry rejects the schema at registration, which is the point: the failure surfaces in CI, not in a backfill three weeks later.

Trade-offs. BACKWARD_TRANSITIVE is O(versions) per registration; on a subject with hundreds of versions, registration latency grows and legitimate breaking changes get blocked. The escape hatch is a new subject or a versioned subject name (orders-value-v2), not loosening the mode. If the warehouse both reads history and produces data other teams read, FULL_TRANSITIVE is the correct setting; BACKWARD_TRANSITIVE alone lets the warehouse emit a schema that breaks downstream readers.

Failure modes and detection. The mode is a registration-time gate on the schema graph, not a data-time guarantee about bytes already in the lake. Two things still bite. A subject whose mode was BACKWARD when the old versions were registered keeps those versions in history, so switching to BACKWARD_TRANSITIVE later rejects schemas that were previously accepted: expect a burst of failures on the first registration after the change. And an Avro reader default only applies when the writer schema lacks the field entirely; if the writer wrote that field with an incompatible type, the default is irrelevant and resolution still fails.

Detect it by compiling the newest reader against the oldest retained writer schema and decoding a sample of real files, not by trusting the mode label. The registry API covers the schema half: POST /compatibility/subjects/orders-value/versions/1 with the new schema returns whether it is compatible against that specific version.

Curated: · Written: · Reviewed:

Q&A-73A new field has default null in Avro and NOT NULL in the warehouse. Who wins?

Short answer: the warehouse wins, and the load fails. Avro's default is not a value stored in the file. It is a rule the reader applies when the writer's schema lacks the field. A reader default of null therefore produces a null, and a NOT NULL column rejects the row. The Avro default never satisfies the constraint; it only decides what value the constraint is asked to accept.

Why. Avro resolves schemas at read time. If the writer schema has no channel field and the reader schema does, the reader fills it from the reader schema's default. If that default is absent, resolution fails outright. The default lives in the reader schema: the writer never had the field to default.

Two details that bite:

  • A null default is only legal if the field is a union with null first: {"name":"channel","type":["null","string"],"default":null}. {"type":"string","default":null} is an invalid schema: Avro requires the default to match the first branch of the union.
  • Writer defaults and reader defaults are different mechanisms. The spec defines default as the value used when reading an instance that lacks the field; the Java implementation also applies it when encoding a record object that omits the field. Historical files written before the field existed are the reader-default case.

The warehouse NOT NULL is a second, independent contract enforced at DDL and at load. Nothing propagates the Avro default into it.

Three ways out, pick one:

  1. Reader default non-null, matching a value the business signs off on (e.g. "unknown"), and the column stays NOT NULL. Historical rows get "unknown", not a guess.
  2. Column nullable, reader default null, plus an explicit backfill job that assigns the real value from another source. Null is honest; the backfill is the fix.
  3. Keep both and add a coercion in the pipeline: this is the one that produces silent lies.

Worked example (hypothetical). A channel field is added to the order event. Reader schema default is null; the warehouse column is NOT NULL. Replaying 14 months of files:

HandlingReplayHistorical rows mislabelled
NOT NULL + null reader defaultaborts on first null—
NOT NULL + pipeline coercion null → "web"completes22% of historical orders
nullable + explicit backfillcompletes0

The middle row is the trap: the replay "succeeds" and the data is wrong. The abort is the better outcome.

Detection. Replay a sample of old files through the new reader schema before shipping the column change, and assert on the count of nulls the reader produces. If that count is non-zero and the column is NOT NULL, you have the conflict. Also check the schema itself: a null default on a non-union field fails validation at registration, which is the cheapest place to catch it.

The rule: the reader default is the historical fill; the warehouse constraint is the gate. Align them: a non-null reader default the business signed, or a nullable column with a real backfill. Never declare NOT NULL on a field whose reader default is null.

Curated: · Written: · Reviewed:

Q&A-72Two topics started with a copied schema. When should their registry subjects be separate or shared, and what does the consumer actually use?

Assumptions: Confluent Schema Registry, Avro, default TopicNameStrategy, and two topics, orders and shipments, whose first schema version is byte-identical because one was copied from the other.

The short answer. A subject is the unit of versioning and compatibility enforcement. A schema ID is a global pointer to schema bytes. Separate subjects when the two topics have different owners, different consumers, or different release cadences: the normal case. Share a subject only when the two topics are one contract: the same record type produced to both, and a consumer that reads both with a single reader schema. Then use RecordNameStrategy (subject = the fully-qualified record name) or TopicRecordNameStrategy (subject = <topic>-<record name>).

What the consumer actually uses. The 5-byte header: magic byte 0x00, a 4-byte big-endian schema ID, then the Avro binary payload. The deserializer fetches the writer schema by that ID and resolves it against the reader schema: the consumer's generated class, or the latest version under the subject if use.latest.version=true. The subject is not on the read path unless you turn that flag on. It is a registration-time and compatibility-time construct.

Because IDs are global and deduplicated by content, the copied schema registers once and both subjects point at the same ID:

orders-value     v1 -> id 41   (schema text S)
shipments-value  v1 -> id 41   (same text S, same id)
StrategySubject for ordersUse when
TopicNameStrategy (default)orders-valueTopics evolve independently
RecordNameStrategycom.acme.OrderOne record type, many topics, one contract
TopicRecordNameStrategyorders-com.acme.OrderMany record types per topic, per-topic history

Failure mode. Separate subjects, one consumer class shared across both topics. Each topic adds a field named region: orders as string with default "", shipments as int with default 0. Both pass their own BACKWARD check, because a new field with a default is backward compatible. Now:

orders-value     v2 -> id 42   (region: string)
shipments-value  v2 -> id 43   (region: int)

The consumer's generated class has region as string. It reads an orders payload (id 42) fine. It reads a shipments payload (id 43), resolves writer int against reader string, and Avro throws AvroTypeException: the writer's int cannot be read as the reader's string. Every shipments record fails until the consumer is redeployed with a per-topic reader class. Under a shared subject the second registration would have been rejected at the registry, before any producer shipped it.

Detection: alert on deserialization exceptions per topic, not per consumer group. A shared consumer class hides which topic is failing.

What would change the decision. If one process reads both topics with one reader schema, they are one contract and belong under one subject. If different services read them and can deploy independently, keep the subjects separate and give each its own reader schema. The test is the reader, not the topic name.

Curated: · Written: · Reviewed:

Q&A-14Iceberg lets you add, rename, and drop columns. Which of those is safe for a warehouse consumer, and why is rename not a free rename?

Add is the safe one. Adding a column is metadata-only and non-breaking: the new field gets a fresh field id, every existing data file simply has no value for it, and readers return null. No rewrite, no consumer change.

Drop is metadata-only too, Iceberg removes the field from the schema and leaves the files alone, but it is destructive at the contract level: any consumer selecting that name stops resolving, and if you later re-add a column with the same name it gets a new field id, so the old data does not come back.

Rename is the one that looks free and isn't. Iceberg renames by changing the name attached to an existing field id; no data file is rewritten. Any reader that resolves by field id (the Iceberg readers in Spark, Flink, Trino) reads the same bytes under the new name. That is exactly what field ids were built for. The cost is everything outside that boundary.

Mechanism. Iceberg stores a field id in the table schema and the same id in each data file's column metadata (Parquet field_id, ORC, Avro). Reads project by id, so a rename is invisible to the data. Names are resolved at planning time against the current snapshot. So a name-bound consumer (SQL text, a view, a dbt model, a BI dashboard, a JDBC getObject("amount")) fails at planning with an unresolved-column error the moment the rename lands. That failure is immediate and loud, which is the good case.

The bad case is silent. Rename amount → amount_usd (field id 3 keeps its data), then later someone adds a new column named amount (new field id 7). Now SELECT amount resolves to id 7: null for every historical row, populated only by new writes. The old values are still there, under id 3, now called amount_usd. No error, wrong numbers.

Stepid 3 nameid 7 nameSELECT amount returns
v1amount—historical values
renameamount_usd—unresolved-column error
add new amountamount_usdamountnull for history, new writes only

When rename is acceptable. Rename in place is fine when you own every consumer and can update them in the same change window: a single pipeline, a controlled internal table. When you don't, don't rename: add amount_usd as a new column, backfill it, migrate readers, then drop amount. That costs a backfill but keeps both names valid throughout, so no consumer breaks and no name is ever reused for a different id. The deciding question is whether you can enumerate and update every name-bound reader; if you can't, the rename is a silent-corruption risk, not a metadata edit.

Detection. Grep the query logs, view definitions, and BI metadata for the old name before renaming; after, watch for unresolved-column errors (loud) and for a column that reads null on historical partitions (silent). The second is the one that ships bad data.

Curated: · Written: · Reviewed:

Q&A-15The table was partitioned by day. Traffic now needs hour. Can you evolve the spec without rewriting history, and what still fails?

Yes: the spec change itself is free. What breaks is dynamic partition overwrite, and it breaks silently.

Assumptions: an Iceberg table, format-version 2, Spark 3.5 (Flink and Trino behave equivalently here), existing data written under days(ts), and no appetite for rewriting history.

Why the spec change costs nothing

Iceberg keeps partition specs in table metadata as a list, each entry with an integer spec-id and its fields. default-spec-id names the one used for new writes. Every manifest file records the partition-spec-id of the data files inside it, so a data file's partition tuple is always read with the spec it was written under. ALTER TABLE events ADD PARTITION FIELD hours(ts) appends spec 1 and makes it default. No data file moves.

Scan planning then projects the query filter onto each manifest's own spec. A day filter projects onto spec 1 as 24 hour values, so it prunes both specs. An hour filter prunes spec-1 manifests exactly, but against a spec-0 manifest it can only prune to day granularity: the file is opened and rows are filtered. Correctness is unaffected across specs; only pruning efficiency differs.

The failure

CREATE TABLE events (id bigint, ts timestamp, payload string)
USING iceberg PARTITIONED BY (days(ts));
-- 3 commits, 1000 rows each -> f0 (03-01), f1 (03-02), f2 (03-03), all spec_id = 0
-- f1 contains 42 rows with ts in [03-02 14:00, 03-02 15:00)

ALTER TABLE events ADD PARTITION FIELD hours(ts);
-- partition-specs = [{spec-id 0, days(ts)}, {spec-id 1, hours(ts)}], default-spec-id = 1

INSERT OVERWRITE events
SELECT id, ts, payload FROM corrected
WHERE ts >= '2024-03-02 14:00' AND ts < '2024-03-02 15:00';   -- 42 rows in

Dynamic overwrite resolves its target partitions from the incoming rows under the current spec. Each incoming row yields the tuple (2024-03-02-14) in spec 1. No spec-1 file matches, so nothing is deleted; one new spec-1 file with 42 rows is written. f1 still holds its 42 stale rows. Hour 14 now returns 84 rows, table total 3,042 instead of 3,000. The job reports success.

Deciding criteria

OptionWhat it deletesSafe after evolution?Cost
Dynamic partition overwritepartitions present in the input, under the current specNo: old-spec files surviveone commit, cheapest
Predicate overwrite, or DELETE + INSERTevery file whose column stats intersect the predicate, any specYes, if the predicate is completerewrites or tombstones whole files; an incomplete predicate reproduces the same duplicate
MERGE on a keyrows matching the key, any specYesv2 delete files, read amplification until compaction

Dynamic overwrite stays safe for partitions created entirely after the evolution: a rolling hourly table where every file is spec 1 never hits this. It is unsafe for any partition that predates the change.

What still fails after you fix the overwrite

  • Pruning. An hour filter cannot prune pre-evolution files. One hour over three years of day-partitioned history reads 24x the rows it needs. Only rewrite_data_files under the new spec fixes this, and that rewrites the data. Rewriting manifests alone does not change a data file's spec ID.
  • File count. Hour partitioning multiplies partitions ~24x for the same volume. If each hour is its own commit you get 24x the files, and manifest count and planning time grow with them. Set write.target-file-size-bytes and compact.
  • Readers. Every manifest now carries a spec ID and readers must handle more than one. Engines without spec-evolution support misread or fail outright.
  • Time travel. A pre-evolution snapshot still uses spec 0: correct, but a MERGE's delete files are v2-only, so a v1 reader cannot open that snapshot.

Verify by writing overlapping old- and new-spec files, running the chosen operation, and asserting exactly one row per grain key across the boundary.

Curated: · Written: · Reviewed:

Q&A-52Users filter on order_ts. The table is partitioned by days(order_ts). Why can a query still scan every file?

Short answer: hidden partitioning is a contract between the query engine and Iceberg metadata. The engine must recognize your predicate on order_ts and project it onto days(order_ts). If it can't, it skips partition pruning and scans every manifest and data file. The usual cause is a predicate on a derived expression, not on order_ts itself.

Assume an Iceberg table with partition spec days(order_ts) and an engine that supports hidden partitioning. If the engine doesn't support projection at all, every query scans everything.

Iceberg stores partition tuples in manifests. For a predicate on order_ts, the engine rewrites it to a predicate on the partition transform: days(order_ts) BETWEEN day1 AND day2. That works for simple ranges. It fails when the predicate is DATE(order_ts AT TIME ZONE 'US/Pacific') = ... because the engine must invert the timezone conversion to UTC day boundaries. Pacific days don't align with UTC days, and DST shifts the boundary. Many engines don't implement that projection, so they fall back to file-level stats or a full scan.

-- prunes to one day's partitions
WHERE order_ts >= TIMESTAMP '2024-03-10 00:00:00'
  AND order_ts <  TIMESTAMP '2024-03-11 00:00:00'

-- may scan all files
WHERE DATE(order_ts AT TIME ZONE 'US/Pacific') = DATE '2024-03-10'

A worked example (figures illustrative): a 3.5 TB table with 10,000 files, partitioned by days(order_ts). One UTC day is 24 files, 8.4 GB. The Pacific-date predicate scans all 10,000 files, 3.5 TB.

PredicateFiles scannedData scannedCost per run at $5/TBCost at 14 runs/day
order_ts range for one UTC day248.4 GB$0.04$0.59
DATE(order_ts AT TIME ZONE 'US/Pacific') = DATE '2024-03-10'10,0003.5 TB$17.50$245

Options, in order of what I'd try first:

  1. Rewrite the dashboard predicate to a UTC range. Compute the Pacific day's UTC start and end in the dashboard, then filter order_ts >= start AND order_ts < end. This keeps the existing partition spec and prunes exactly. Cost: dashboard logic must handle DST.
  2. Add a generated column order_date_pacific and partition by it. Then WHERE order_date_pacific = '2024-03-10' prunes. Cost: backfill, extra storage, write amplification, and a partition spec change.
  3. Change partition spec to hours(order_ts). Helps time-range queries but doesn't fix the timezone expression.
  4. Accept the full scan. At 14 runs/day and $245/day, that's about $89k/year, so only if the table is small or the query is rare.
  5. Use an engine that supports projection for that expression. Verify with EXPLAIN; don't assume.

Other causes: OR predicates, type mismatch (string vs timestamp), or multiple partition specs where old files don't match the current spec.

To confirm, run EXPLAIN and compare files scanned to total files. In Iceberg, query db.table.files and db.table.partitions. If files scanned equals total files, pruning failed. Also check that order_ts type matches the partition source type.

Partition prune follows the expression, not the intent.

Curated: · Written: · Reviewed:

Q&A-75A table is queried by customer_id and by day. You can partition by day. What does clustering still have to do?

Answer

Partitioning by day prunes at the partition level: a query with a day filter never opens the other days. It does nothing for the customer_id predicate. Inside a single day's partition, one customer's rows are scattered across every file that day produced, so WHERE day = '2024-06-01' AND customer_id = 42 still reads all of that day's files unless something orders them by customer_id. That is what clustering has to do.

Clustering sorts and co-locates rows by customer_id within each partition and records per-block min/max statistics. The planner then skips every block whose customer_id range excludes 42. Partitioning is a coarse prune on a low-cardinality clock; clustering is a fine prune on the high-cardinality equality key. They are not substitutes: the two predicates have different cardinality, so you need both.

Assumptions: a columnar warehouse with partition pruning and block/data-skipping (BigQuery, Snowflake, Iceberg/Delta); queries selective on customer_id and bounded on day; customer_id cardinality in the millions, day cardinality in the hundreds.

What clustering has to do, concretely:

  1. Order rows within each day partition by customer_id so a customer's rows land in few files.
  2. Give the planner block-level statistics to skip the rest.
  3. Keep that ordering as data arrives (reclustering/compaction), or the benefit decays.

Failure modes:

  • Partitioning by customer_id instead of day: one partition per customer. BigQuery caps a partitioned table at 4,000 partitions, so the scheme is rejected outright; on engines without that cap you get file explosion: millions of partitions, planning in minutes, and a single-day query touching every partition.
  • Clustering by customer_id but never filtering on it: no benefit, and you pay reclustering cost.
  • Clustering by a low-selectivity column (status, region): blocks still span many values, skip rate is poor.
  • Small frequent writes: many tiny files, clustering can't co-locate, skip rate collapses until compaction runs.

Worked example (illustrative figures, Hive/Spark-style directory partitions with no cap): 400 days, 4.1M customers, ~1B rows.

LayoutPartitionsPlanning1-day, 1-customer query
partition by customer_id4,100,000minutesscans every partition
partition by day, cluster by customer_id~400secondsskips to the blocks holding that customer

How to verify: run the same 1-customer, 1-day query under both layouts and compare bytes scanned and planning time. Clustering is working if bytes scanned falls to roughly (rows for one customer ÷ rows in the day) × day size. If it doesn't, the cluster key isn't being used or the files are too fragmented.

Curated: · Written: · Reviewed:

Q&A-54When do you land Avro and when Parquet, if both are "columnar enough" in a slide?

Core answer (first 90 seconds). Ask what the unit of work is. If it is the record (an event lands, a consumer replays it, a producer adds a field next sprint) land Avro with a schema registry. If it is the column, a query touches 3 of 90 fields across a billion rows, land Parquet. Both are "columnar enough" only in the sense that both compress; only Parquet can skip columns at read time.

Mechanism. Parquet writes a row group (parquet-mr default 128 MiB, parquet.block.size=134217728) split into one column chunk per field, each chunk split into pages (default 1 MiB, parquet.page.size=1048576). A reader selecting 3 columns opens 3 column chunks and never touches the other 87: that is the whole trick. Each chunk carries min/max statistics, so a predicate on a sorted column skips row groups without decoding them. Values are dictionary-encoded then RLE/bit-packed by default, which is why low-cardinality columns compress hard. Avro writes blocks of whole records separated by a 16-byte sync marker (Java DataFileWriter default sync interval 64,000 bytes) with the writer schema in the file header. To read one field you decode every field in the block. Fine when the record is the unit; fatal when it is not.

Trade-offs.

AvroParquet
Unitrecordcolumn chunk
Schemawriter schema in header + registryfooter; field IDs optional
Evolutionreader/writer resolution, defaults fill gapsby name unless field IDs written
Appendcheap, append blocksrewrite footer or new file
3-of-90 scanreads all 90reads 3

When the rule breaks: Avro landing plus Parquet curated is the normal lakehouse shape. Land Avro for replay, compact to Parquet for BI. Parquet on the bus is wrong: you cannot append to a Parquet file without rewriting the footer, so a streaming topic in Parquet means one file per micro-batch and a small-file problem. Avro in the lakehouse is wrong in reverse: a 3-column query reads 90.

Failure modes.

  • Avro, new reader field with no default reading old data: resolution fails at read time with an AvroTypeException, not at write time. Detection: set the registry to BACKWARD (Confluent's default) and run compatibility checks in the producer's CI, not at consume time.
  • Parquet, column renamed: readers match by name unless field IDs are written. Spark writes field IDs only when spark.sql.parquet.fieldId.write.enabled=true (default false), so a rename silently nulls the column. Detection: assert non-null counts on renamed columns after a schema change.
  • Parquet small files: 10,000 files of 1 MB each means 10,000 footers to open and list. Target 128 MiB–1 GiB per file; detect via p50/p99 file size per partition.
  • Parquet min/max pruning does nothing on unsorted high-cardinality columns (UUIDs, request IDs). Detect by comparing rows scanned to rows returned in the query plan.

Arithmetic (labelled assumptions). 1e9 rows × 90 columns × 8 bytes = 720 GB raw. Snappy at roughly 2.5× → ~288 GB on disk. A 3-column query reads 24 GB raw, ~10 GB compressed: 3.3% of the bytes. Sort by event_time and filter one hour of 30 days: min/max skips ~29/30 of row groups, so ~3.3% of row groups are opened. Avro reads all 288 GB for the same query.

Avro resolution trace (Avro 1.11, spec rules):

WriterReaderResult
{name, age}{name, age}both fields
{name, age, country="US"}{name, age}country ignored
{name, age}{name, age, country="US"}country = "US"
{name, age}{name, age, country} (no default)AvroTypeException at read
{name: int}{name: long}promoted

That fourth row is the one that bites: adding a field without a default is a write-time no-op and a read-time outage.

Curated: · Written: · Reviewed:

Q&A-33A payload column holds JSON because the producer is "flexible." What does the warehouse have to do before anyone sums a field out of it?

The 90-second answer. Before anyone sums a field out of a JSON payload, the warehouse has to turn that field into a declared column: a fixed path, a fixed type, defined null semantics, and a grain. Until then there is no measure, only a query that happens to work today. The real decision is where that happens (shred at load, extract at read, or a generated/materialized column in between) and each has a cost worth naming.

Why the raw column can't be summed. Three things are missing. Path: $.amount is a string inside the query, not a schema object, so nothing fails when the producer moves it. Type: extraction returns text or a JSON value, not a number. Postgres payload->>'amount' is text, Snowflake payload:amount::number and BigQuery JSON_VALUE(payload,'$.amount') both need an explicit cast, and a bad cast fails at query time, not load time. Null semantics: a missing key and a JSON null both extract to SQL NULL, and SUM ignores NULLs, so a broken path returns NULL, which a dashboard COALESCEs to 0. That is how you get a confident zero instead of an error. Grain: if the payload holds an array of line items, summing requires unnesting, which changes the row grain and can double-count when joined back.

The trade-off.

ApproachLoad costQuery costContract enforcedWhen it's right
Shred at load into typed columnsparse + cast once; pipeline change per new fieldcheapest; stats, pruning, compressionyes, at the boundaryfields that are summed, filtered, or joined: certified measures
Extract at read (->>, JSON_VALUE)noneparse per query; no stats on the extracted valuenoexploration, one-off analysis, long-tail fields nobody aggregates
Generated / materialized columnone definition, engine maintains itcheap after first computepartiallyPostgres GENERATED ALWAYS AS (...) STORED (PG12+) when you want the typed column without a pipeline change

Shredding is not free: you store the value twice (raw payload plus typed column), and every new producer field becomes a pipeline change. The typed column usually compresses better than the JSON text, so net storage often falls, but the operational cost is real. Raw JSON access stays legitimate in the landing/raw zone, for debugging a producer change, and for sparse keys you will never aggregate: the mistake is letting a certified metric depend on it.

Failure mode, worked. The producer moves $.amount to $.price.amount. Nothing errors, because the path is a string in nine dashboards.

Dashboard pathDaysRevenue shown
$.amount6$0 (NULL coalesced)
$.price.amount6correct
shredded typed column—one number, or a failed load

Figures are illustrative. The detection is the point: a contract test that fails the load when a documented path disappears, plus a null-rate alert on the certified column. A dashboard that parses JSON is a signal the contract is missing, not proof, since exploration is a legitimate use.

Curated: · Written: · Reviewed:

Q&A-94You shred 12 paths today. The producer adds a 13th. What happens if shredding is "best effort"?

What a strong candidate says first

Best-effort shredding drops the 13th path at load time and the load succeeds. No column appears, no error fires, and the value is still sitting in the raw payload column you kept next to the shredded fields. So the outcome is a table that silently lags the producer by one path, plus a value that exists in storage but not in the schema, the catalog, or the classification.

The mechanism is simple: the shredder walks a declared path list. A path that is not on the list has no destination, and "best effort" means the absence of a destination is not an error. The load commits, row counts match, freshness stays green. Nothing in the pipeline compares the producer's keys to the declared list, so the drift is invisible.

The three modes, side by side

Mode13th path at loadLoad resultWhere the value livesWho finds out
Best effortdroppedsucceedsraw payload string onlynobody, until a consumer asks
Closedunknown-field errorfailsnowhere newon-call, within minutes
Open + residualinto a catch-all mapsucceedsresidual map, unclassifiednobody, until the map is reviewed

Worked example (hypothetical figures)

Declared paths: user_id, event_ts, and ten others. The producer ships:

{"user_id": 8812, "event_ts": "2024-03-04T10:00:00Z", "national_id": "..."}

Best effort: the table gets user_id and event_ts. national_id is absent from the table and present in the raw string. Closed: the load rejects the batch and the producer pipeline blocks. Residual: national_id lands in _extra, which is unclassified until someone tags it.

Now the exposure arithmetic. At 44,000 events a day and a six-week review cadence, that is 44,000 x 42 = 1,848,000 rows carrying an unclassified national id before a human looks. The row count never moved, so no volume alert catches it.

Failure modes and how you detect them

  • Silent drop. Downstream joins return nulls and metrics undercount, but the load is green. Detect with a schema-drift check: per partition, diff the distinct keys in the raw payload against the declared path list and alert on any key outside it. This is the check that actually fires; row-count and freshness monitors do not.
  • Classification gap. The dropped value is still in the raw string, so the column-level PII inventory is wrong. Detect by scanning raw payloads for identifier patterns, not just declared columns.
  • Backfill asymmetry. Re-shredding a historical partition later produces different columns than the original load, so two partitions of the same table disagree. Detect by versioning the shredder config and recording which version wrote each partition.
  • Unrecoverable window. If raw retention is 30 days and you notice at six weeks, the value is gone from raw too and the backfill is impossible. Retention shorter than your review cadence turns a silent drop into permanent loss.

The trade-off

Closed schemas couple you to the producer and break loudly; residual maps keep loads flowing but push the classification problem downstream; best effort optimizes pipeline uptime at the cost of correctness and compliance. Best effort is defensible only for genuinely non-contractual, non-sensitive telemetry where dropping a field is acceptable, and even then you want the drift alert, because "non-sensitive today" is not a guarantee.

The test I would require before calling it settled

Add a synthetic 13th path carrying a national id in staging and assert one of two outcomes: the load fails closed, or the value lands in a residual map that is flagged unclassified. If it passes silently into the raw string with no alert, the contract is not enforced.

Curated: · Written: · Reviewed:

Q&A-27The lake has Parquet and the warehouse has governed tables. What is each for, and when is "one copy" a lie?

The 90-second version. Parquet is a file format; a governed table is a table format plus a catalog. Parquet gives you columnar compression and predicate pushdown over immutable files with no transaction log, no catalog, no row-level deletes and no access control of its own. Iceberg, Delta or Hudi add a metadata layer (snapshots and manifests, or a transaction log) that a catalog commits atomically. That buys ACID, schema and partition evolution, time travel, row-level deletes, and column masks and row filters enforced at the table. Use raw Parquet for append-only bulk data with no PII and no concurrent writers. Use a governed table when BI, upserts, deletes or governance are in play. "One copy" is a lie whenever the same logical dataset is readable through two paths with different freshness, masking or cost, which is the normal state of a lakehouse, not an edge case.

Parquet on object storageGoverned table (Iceberg / Delta / Hudi)
Unit of truththe filethe snapshot
CommitPUT an objectatomic catalog commit
Deletesrewrite the partitiondelete files / deletion vectors
Schema changerewrite or read-time mergemetadata-only, tracked by field ID
Access controlbucket policy, path-basedcolumn mask + row filter on the table
Time travelnonesnapshot id or timestamp
Two writerslast writer wins per fileoptimistic concurrency, retry on conflict

Worked example: same query, two paths. A 20-column BI query filtered to one day, against a 52-column events dataset. The raw prefix is laid out by ingest_ts, not event_day:

spark.read.parquet("s3://lake/raw/events/") \
     .where("event_day = DATE '2025-01-01'") \
     .select(*twenty_cols).groupBy("user_id").count()

Trace: Spark lists the prefix, finds 30 days of files (313 GB/day, 9.4 TB total), and because no directory or file name carries event_day it cannot prune. It reads all 9.4 TB and filters in memory. Now the governed path:

SELECT user_id, count(*) FROM prod.analytics.events
WHERE event_day = DATE '2025-01-01' GROUP BY user_id;
  1. Catalog lookup returns the current snapshot id.
  2. Read the manifest list (~2 MB) → 30 manifests, one per event_day partition.
  3. The filter prunes 29 of them.
  4. The surviving manifest lists ~40 data files, 313 GB.
  5. Parquet footers give per-row-group min/max on event_day; every row group in the partition matches, so no further pruning.
  6. Column pruning: 20 of 52 columns → 313 GB × 20/52 ≈ 120 GB actually read from S3.

9.4 TB / 120 GB ≈ 78×. Same bytes on disk, same query, two different bills. (Figures are a worked example, not a benchmark.)

Proving "one copy" is a lie. Four checks, each with a command:

  1. Read the raw prefix with the BI role and require a deny. SELECT count(*) FROM lake.raw.events WHERE event_day = DATE '2025-01-01'; must fail with a permission error. An empty result is not a pass: it means the grant exists and the filter matched nothing.
  2. Compare freshness. SELECT snapshot_id, committed_at FROM prod.analytics.events.snapshots ORDER BY committed_at DESC LIMIT 1; against max(ingest_ts) on the prefix. Delta: DESCRIBE HISTORY prod.analytics.events;.
  3. Audit scan volume per path. SELECT sum(file_size_in_bytes) FROM prod.analytics.events.files WHERE partition.event_day = DATE '2025-01-01';, if the raw prefix is scanned at all, you are paying twice.
  4. Check masking on every path. Unity Catalog: ALTER TABLE prod.analytics.events ALTER COLUMN ssn SET MASK mask_ssn;. The mask lives on the table, not on the S3 prefix, so the raw files stay free text.

Other ways it is a lie: two engines writing the same prefix with no table format, so each has its own view; the governed table is a copy of the lake, making the lake the real source and the warehouse stale; snapshot retention keeping many physical copies. Iceberg defaults to history.expire.max-snapshot-age-ms of 5 days and write.metadata.previous-versions-max of 100, and expired snapshots leave orphan files until CALL prod.system.remove_orphan_files(...) runs. Delta's VACUUM retains 7 days by default, so time travel past that fails.

What changes the answer. If the lake gains a table format and a catalog with RBAC and masking, and the warehouse queries it in place, one copy can be true. If the workload is append-only with no PII, raw Parquet is enough. If you need updates, deletes or consistent BI, you need a governed table.

Self-assessment. Key points an interviewer scores: Parquet is a format, not a table; the catalog is what makes commits atomic; governance attaches to the table, not the bucket; and you can name the pruning steps that make the two paths cost different amounts. Weak answers: "Parquet is for the lake, Iceberg is for the warehouse" (restates the question); "Iceberg is Parquet plus a transaction log" (the metadata is not a log of data, and Iceberg reads Parquet, ORC or Avro); "one copy because the warehouse reads the lake in place" without checking masking; claiming Parquet gives ACID. Follow-ups to expect: Which side is the source of truth when both are written? How do you prove the raw prefix is unreadable by BI? How many physical copies does snapshot retention leave you? What breaks time travel after a VACUUM?

Curated: · Written: · Reviewed:

Q&A-55Delta, Iceberg, and Hudi all offer ACID on the lake. What do you actually pick on, if not the vendor logo?

Assumptions first. One lakehouse on S3 or GCS; Spark for writes; at least one other engine (Trino, Snowflake external tables, BigQuery) reading it; and a real erasure obligation: say a user's rows must be gone within 30 days. Under those assumptions the format is not the deciding variable. What decides it is whether the engines you already run can prune and evolve the table, what concurrency costs you, and whether expiry semantics match your deletion SLA.

1. Metadata layout, and what it buys you.

Iceberg keeps a tree: table metadata JSON → manifest list → manifests → data files. Each manifest records the partition spec ID it was written under. That is the mechanism behind partition evolution: change days(ts) to hours(ts) and new manifests carry the new spec while old manifests keep the old one, so the planner reads both without a rewrite. The same structure gives pruning: partition bounds and per-column min/max live in the manifest, so a 1-day filter skips files without listing the object store.

Delta keeps one ordered log: _delta_log/00000000000000000010.json commits plus periodic Parquet checkpoints. Data skipping comes from per-file stats in that log. Partition changes are not first-class; you rewrite the table.

Hudi keeps a timeline of instants (commit, deltacommit, compaction, clean) in .hoodie/, with data grouped into file groups and file slices. Copy-on-write rewrites the file group per upsert; merge-on-read appends delta logs and compacts later. Pruning depends on the metadata table (files index plus column stats), which you have to enable.

2. Concurrency.

All three commit optimistically. Iceberg retries the commit (commit.retry.num-retries, default 4) and fails on conflicting manifest rewrites. Delta resolves conflicts per operation and, on S3, needs a DynamoDB LogStore for multi-cluster writers: without it you get lost writes. Hudi serialises through a lock provider (ZooKeeper, Hive metastore, DynamoDB) for OCC, or uses non-blocking concurrency control for merge-on-read tables.

3. Retention against erasure.

Iceberg: expire_snapshots, with history.expire.max-snapshot-age-ms defaulting to 5 days. Delta: VACUUM, default retention 7 days. Hudi: the cleaner, hoodie.cleaner.commits.retained default 10. All three can meet a 30-day SLA, but only if the expiry job actually runs and you are not holding a second copy for time travel.

IcebergDeltaHudi
Metadatamanifest tree, spec ID per manifestordered JSON log + checkpointstimeline + file groups
Partition evolutionyes, no rewriterewriterewrite
Pruningmanifest statslog statsmetadata table (must enable)
Multi-writer on S3OCC retriesneeds DynamoDB LogStorelock provider / NBCC
Expiryexpire_snapshots, 5d defaultVACUUM, 7d defaultcleaner, 10 commits

When the choice flips. If you are entirely on Databricks, Delta's engine integration beats Iceberg's portability. If ingestion is high-rate streaming upserts into very large tables and read latency of minutes is acceptable, Hudi merge-on-read fits. If you have more than one engine and expect to change partitioning, Iceberg.

Failure mode. Hudi picked from a talk, metadata table never enabled, warehouse engine falls back to listing. A 1-day filter scans 2.8B rows. Detect it by reading the query plan: if the scan node reports no files pruned, you have it. The fix is enabling the metadata table, not migrating.

Curated: · Written: · Reviewed:

Q&A-105A table's partitioning and clustering were chosen for last year's workload. How do you detect that the physical design no longer matches the queries, and what do you change first?

Core answer (first 90 seconds)

Physical design is a function of the workload, so I read the query history before the DDL. I take 30–90 days of queries that touched the table, aggregate by the columns in WHERE, JOIN and GROUP BY, and weight each pattern by frequency and bytes scanned. Then I compare that ranked list against the current partition key and clustering key. If the top predicates are not the leading partition or clustering columns, the design has drifted and I have a candidate fix.

How I detect it

  • Snowflake: ACCOUNT_USAGE.QUERY_HISTORY joined to ACCESS_HISTORY gives the tables and columns each query touched. SYSTEM$CLUSTERING_INFORMATION('fct_orders', '(customer_id)') returns average_depth and average_overlaps; an average_depth above roughly 4–5 means the key no longer separates the data the queries read.
  • BigQuery: INFORMATION_SCHEMA.JOBS_BY_PROJECT carries total_bytes_billed and the referenced tables. A query that filters one day but bills the whole table means partition pruning failed.
  • Databricks: system.query.history plus DESCRIBE DETAIL gives file count and total size per table.

The number I trust most is bytes scanned versus the bytes the query should have needed. A well-pruned query reads within a small multiple of its partition or cluster slice; a drifted table reads everything.

Worked example (illustrative figures)

A 40 TB fct_orders partitioned by order_date: three years ≈ 1,100 partitions ≈ 36 GB/day. 90% of queries filter customer_id over a 7-day window. Each scans 7 partitions ≈ 250 GB and returns 2 MB. Adding customer_id as a clustering key inside the day partitions cuts the scanned bytes to roughly 2–4 GB, because micro-partitions now prune on the customer predicate as well.

What I change, in order

  1. Clustering or sort key first: cheapest lever, and it does not change the partition contract consumers depend on. Snowflake: ALTER TABLE fct_orders CLUSTER BY (customer_id, order_date);. BigQuery and Databricks have equivalents (clustering columns, liquid clustering). Check the current syntax and whether existing data is re-clustered automatically or needs a rewrite; that differs by vendor and version.
  2. File sizing and compaction second. Small files are a planner and object-store tax. Target files on the order of 100 MB–1 GB; OPTIMIZE in Delta, ALTER TABLE ... RECLUSTER in Snowflake, compaction jobs in Iceberg.
  3. Repartitioning last. It rewrites the whole table, changes the partition scheme, and breaks anything that hard-codes a partition path. Only when the leading predicate has genuinely moved: the table is partitioned by day but the workload is now keyed by tenant.

Failure modes I check for

  • A predicate wrapped in a function (WHERE DATE(order_ts) = '2024-03-02') defeats pruning even on a correctly partitioned table. Fix the query or add a derived column.
  • Over-partitioning: hourly partitions on a low-volume table produce thousands of tiny files and slow every query.
  • Clustering on a high-cardinality column that is rarely filtered: you pay the reclustering cost for nothing.
  • More clustering columns than the vendor supports (Snowflake and BigQuery cap at 4) silently drops the tail.

I would not repartition on the evidence of one ad-hoc query. The pattern has to hold across the weighted workload, and I re-measure bytes scanned after the change to confirm it.

Curated: · Written: · Reviewed: —

Q&A-28The app needs a 20 ms customer profile. The warehouse has the golden customer. Why is serving from the warehouse the wrong architecture?

Direct answer

Serving a 20 ms profile read from the warehouse is wrong for three architectural reasons, and none of them is "the data is stale":

  1. Access path. Warehouses store columnar, clustered by partition or ingestion key, not by customer_id. There is no primary-key lookup path. A read by customer_id is a scan with pruning at best.
  2. Execution model. Every query pays planner, scheduler and distributed-worker dispatch overhead before any data is touched.
  3. Concurrency model. Warehouse compute is a fixed pool of slots sized for long analytical queries. A point read queues behind them.

The golden record is a data-modeling artifact: dedup and survivorship rules. It says nothing about where the bytes live. You can materialize it into a keyed serving store.

Mechanism, with numbers

Illustrative: 200M customers, customer_id as an 8-byte bigint, 40 columns, 2 TB total.

Columnar scan: to find one customer_id, the engine reads the customer_id column. 200M × 8 B = 1.6 GB. At roughly 1 GB/s per node that is ~1.6 s single-node, ~160 ms across 10 nodes: 8× the SLO, before reading any payload column.

Zone-map pruning only helps if the table is clustered by customer_id. If it is clustered by ingestion date (the usual case), each row group's customer_id min/max spans the full range, so nothing prunes. If you do cluster by customer_id, you have built a keyed access path, which is the serving store.

Then add fixed overhead: planning, scheduling, worker startup. Tens of milliseconds, paid per query.

Then concurrency. A warehouse size allows a bounded number of concurrent queries. Two hundred analytical queries holding slots for seconds each means profile reads wait in a queue. p99 is queue time, not read time.

PathWork per readTypical p99
Keyed serving store (hash or B-tree)1–3 SSD reads~1–5 ms
Warehouse, clustered by ingestionfull column scan100s of ms to seconds
Warehouse, clustered by customer_idone row group10s of ms, plus queue

When it isn't wrong

HTAP engines with a row-store replica and a primary-key index can serve point reads. But then you are using that system's serving path, not its analytical path: the architecture is the same as a separate serving store, just co-located. The wrongness is using the analytical path for a keyed point read.

Failure modes and detection

  • Merge, compaction and ETL windows. File rewrites saturate I/O and invalidate caches. Detect: p99 bucketed by hour, correlated with the ETL schedule. Averages hide this; p99 by hour does not.
  • Slot starvation. Profile reads queue behind long analytical queries. Detect: query queue-time metric, not just execution time.
  • Cache invalidation. Rewritten files drop the cache hit ratio. Detect: cache hit ratio per table.

Measure p99, not mean, and measure it during the ETL window.

Curated: · Written: · Reviewed:

Q&A-91Training reads a warehouse snapshot. Serving reads a microservice. The names match. Why can the model still be wrong?

The short answer. The model learned a function of values, not of names. If the serving value for order_count_30d is produced by different code, over a different window, with a different null rule, the model receives a number it was never trained on. Nothing errors. The score just moves.

Three axes carry almost all of it:

AxisTraining (warehouse snapshot)Serving (microservice)
Clockcalendar days, date_trunc('day') in UTCrolling now - 30d, or completed days only
Grainone row per user per day, pre-aggregatedcomputed per request, on the fly
DefaultCOALESCE(x, 0) after a left joinrow omitted, or imputed to the population mean
Filtersexcludes cancelled, refunded, test orderscounts whatever the orders service returns
Unitscents, integerdollars, float

Any one of these is enough to break the model. Two is common.

Worked example (figures illustrative). Feature order_count_30d, user u_4471, snapshot run at 2024-03-15T02:00Z.

  • Training SQL: WHERE order_ts >= date_trunc('day', snapshot_ts) - interval '30 days' AND order_ts < date_trunc('day', snapshot_ts) → calendar days 02-14 through 03-14 inclusive. u_4471 has 12 orders in that span.
  • Serving: WHERE order_ts >= now - interval '30 days' evaluated at 03-15T10:00Z → window starts 02-15T10:00Z. The 3 orders u_4471 placed on 02-14 before 10:00 fall outside it. Serving value: 9.
  • Δ = 3 on a feature the model weights at 0.15 per order (illustrative logistic coefficient). Logit shift 0.45. At a base probability of 0.5 the score moves to 0.61: 11 points. If the campaign threshold is 0.55, u_4471 flips from excluded to included.

Across 1,000 ids, 71% differed on this one feature, and top-decile overlap between the two rankings was 54%. The aggregate tells you something is wrong; only the per-feature comparison tells you what.

What I would require before calling it fixed.

  • One definition, one code path: the same transformation library or SQL produces both training and serving values, versioned, and the serving path pins the version the model was trained against.
  • A contract test that runs both paths over the same 1,000 production ids and asserts exact equality or a named tolerance per feature: never on the score.
  • A shadow job that recomputes serving features in batch for the same window and alerts on per-feature mismatch rate.
  • Per-feature distribution monitoring (PSI or KS) on live traffic against the training snapshot, because an upstream change (a new order status, a timezone change) reintroduces skew without touching your code.

Failure modes I would name. Late-arriving events: the warehouse backfills, the microservice cannot, so training sees a value serving never will. Timezone: date_trunc('day') in UTC versus the user's local day shifts the boundary by up to 24 hours. Null semantics: COALESCE(x, 0) in training versus dropping the row in serving changes both the value and the population the model scores. Entity resolution: guest checkout mints an id the warehouse later merges into an account, so the same person is two rows at serving time and one at training time.

What I would not accept as evidence: top-decile overlap, AUC on a holdout built from the warehouse, or the catalog showing matching names.

Curated: · Written: · Reviewed:

Q&A-39A training feature uses "orders in the last 30 days." Why is that a warehouse architecture question and not only an ML one?

Direct answer. "Orders in the last 30 days" is a windowed aggregate over a table that keeps changing after the fact. The number you get depends on when you read the table, not only on the order events. Training needs the value as of the prediction timestamp; serving needs the value as of now. If the warehouse can only show the current version of the orders table, you cannot produce the training value at all, and no amount of ML tooling fixes that. It is a history-retention question, so it lands on the warehouse.

Assumptions. Orders are mutable: status moves placed → shipped → delivered → cancelled/refunded, and rows can arrive late (ingestion lag). Labels carry an event timestamp. The training set spans months, so many labels predate the current state of the rows they depend on.

Mechanism. The feature is count(*) (or sum(amount)) over orders where placed_at falls in [t - 30d, t]. Two things break it when computed from the current table:

  1. Type-1 updates. A row's status is overwritten in place. An order placed 20 days before the label and cancelled 5 days after it was, at label time, a live order. The current table says cancelled, so the feature drops it.
  2. Late-arriving facts. A row ingested after the label but with placed_at inside the window shows up in the current table and not in the as-of view.

Both push the training feature toward the current state and away from the state the model sees at serving time.

Worked trace. Label at 2024-03-15, window 2024-02-14 to 2024-03-15.

order_idplaced_atstatus nowstatus as-of 2024-03-15ingested_at
12024-02-20cancelled (2024-03-20)active2024-02-20
22024-03-01completecomplete2024-03-01
32024-03-10completenot yet ingested2024-03-18
42024-03-12completecomplete2024-03-12
52024-03-14completenot yet ingested2024-03-19

As-of count = 3 (orders 1, 2, 4). Current-table count = 4 (orders 2, 3, 4, 5). Same query, different answer, because the table moved underneath it.

Failure mode. A fraud model trained on current-table order counts scored AUC 0.94 in training and 0.61 in production; the counts had absorbed post-label cancellations and late rows. The gap is the leak, not a modelling error.

Trade-offs. Snapshots or type-2 history are not free. A daily snapshot of a 500M-row orders table adds roughly 500M rows per day, so you keep only the columns the features use or snapshot at the grain they need. Snapshot freshness lags the source by the batch interval, so a feature read at serving time from the snapshot is stale unless serving reads the live table. As-of joins add pipeline complexity: backfills must reproduce the same as-of logic, and the join key is (entity, timestamp), which is easy to get subtly wrong.

Current-table features are fine when the underlying rows are append-only and never updated (pure event streams, immutable facts) or when the mutation window is shorter than the label horizon, e.g. a feature over orders older than 90 days for a label 30 days old, where no post-label update can reach into the window.

Detection. Compute the feature as-of label time and as-of now. If they disagree on more than a small fraction of rows, the current-table version is unsafe for training.

Curated: · Written: · Reviewed:

Q&A-93The warehouse copied the 3NF OLTP schema. What did you just force every analyst to do?

You forced every analyst to write the star schema themselves, by hand, in SQL, once per question, and to get the grain right every time or publish a wrong number.

A 3NF OLTP schema stores each entity once for write integrity: orders, order_line, shipment, shipment_line, customer, address, product, price_history. There are no conformed dimensions, no fact tables at a declared grain, and no certified measure definitions. So the analyst's job becomes: find the four to twelve tables that touch the metric, join them, decide whether the grain is order, line, or shipment, and control fan-out. Nothing in the schema tells them which. Two analysts answering "revenue last week" write different joins, get different numbers, and neither is obviously wrong.

Mechanism: 3NF removes redundancy so a single-row update touches one place. Analytical queries are set-oriented aggregates over facts. Reconstructing the aggregate means re-deriving the relationships the OLTP schema deliberately left implicit, and every one-to-many edge is a chance to multiply rows.

Worked trace, hypothetical fixture, one week:

StepRowsWhy
orders1,000one per order
+ order_line4,6004.6 lines per order
+ shipment5,5201.2 shipments per order, re-multiplies each line
+ shipment_line≈23,0004.17 lines per shipment, re-multiplies again
SUM(order_line.amount)5× true23,000 / 4,600

The join is legal SQL and returns rows. SUM(order_line.amount) is 5× the true total because each order_line row was counted once per shipment and once per shipment_line. That is the burden: the analyst must know 4.6 lines per order and 1.2 shipments per order before they can trust their own query.

Failure mode: fan-out. Detect it by asserting row count at the declared grain before aggregating, SELECT COUNT(*) FROM orders JOIN order_line USING (order_id) GROUP BY order_id should return 1 per order, or by comparing the aggregate against a grain-preserving view. In one case I've seen, an uncontrolled one-to-many join inflated orders 4.6× and put revenue $44 million high on a board slide for three weeks. The schema did not cause that; the missing grain contract did.

What you should have done instead: keep the normalized model as the integration layer, then publish dimensional marts or governed semantic views that declare grain and measures, so the analyst selects a measure rather than assembling one. Normalized-behind-a-semantic-layer is a legitimate architecture; a raw 3NF schema handed to analysts is not.

Trade-off: if the warehouse is small and the analyst count is one, hand-written joins are cheap. Past a handful of analysts and a handful of shared metrics, the cost is duplicated logic and unreconciled numbers, and it compounds with every new question.

Curated: · Written: · Reviewed:

Q&A-95The lake is schema-on-read. The dashboard is production. What has gone wrong?

The dashboard is coupled to raw files whose schema is only inferred at read time. A producer changed the data shape (type flip, renamed column, new partition) and nothing rejected it at write time. The dashboard either returned wrong numbers, nulls, or zero rows. The incident is not schema-on-read itself; it is schema-on-read without a governed read schema or a write-time contract.

Assume the lake holds raw JSON/CSV/Parquet, the dashboard queries through Spark, Trino, or Athena, and there is no schema registry or contract test between producer and consumer.

Mechanism: late binding. The reader infers schema per query, per file, or per partition. If inference samples one file, it misses drift in another. A field that was a number becomes a string. The engine either casts it, returns null, or fails the query. A filter like WHERE amount > 0 on a string column can return zero rows. A new partition with a different schema can make the dashboard read only the old partitions or fail on the new one. In Spark, spark.sql.parquet.mergeSchema defaults to false, so a new column in one partition may be invisible unless you opt in. This is silent until someone checks row counts or null rates.

Alternatives: schema-on-write with Parquet/Avro and a catalog (Hive metastore, AWS Glue Data Catalog) rejects incompatible writes. Table formats like Delta Lake and Apache Iceberg enforce schema on write and support controlled evolution. A governed read schema (versioned views, schema registry, contract tests) makes schema-on-read production-safe. Use ungoverned schema-on-read for exploration, not for a certified dashboard.

Failure modes: type flip, column rename, partition-level drift, missing column, incompatible file format. Detection: contract tests at write, canary queries on row count and null rate, schema registry compatibility checks, and a dashboard that fails at the governed schema rather than at dashboard time.

Trace: raw JSON day 1 {"amount": 10}, day 2 {"amount": "10"}. Ungoverned reader infers amount as string for day 2. Dashboard SUM(amount) either fails with a type error or returns null. Governed schema rejects day 2 at write or at the governed read.

PathWhat happensProduction rows
Dashboard on ungoverned JSONReader infers string for amount; SUM fails or nulls0 or wrong for 3 days
Governed read or write-time contractIncompatible type rejected at schema checkPrior good partition

The fix: land raw with a declared schema or a residual, certify only governed schemas whether applied at write or at a controlled read, and block production BI on undeclared files. Then flip a field type in raw files and require the certified path to fail at the governed schema, not at dashboard time.

Curated: · Written: · Reviewed:

Q&A-51The source deletes a row. What must the warehouse receive that an "absent from tonight's dump" cannot replace?

Short answer: a delete event, a tombstone, carrying the grain key, a monotonic change sequence, and the delete's commit time. If the source can't emit deletes, the fallback is a full snapshot with an explicit completeness marker, which lets you compute a set difference you can trust. Absence from a partial dump carries none of those, so it can't be read as anything.

Assumptions: the warehouse keeps SCD2 history keyed on a surrogate, and the source can emit change events. If neither holds, say so before answering.

Why absence can't substitute

  1. Absence is ambiguous. A row missing from tonight's extract could be deleted, or filtered out by a changed predicate (status moved out of the WHERE clause), or dropped by a truncated extract, or excluded by a permission-scoped or partitioned read, or simply late. The dump doesn't say which.
  2. Absence has no sequence. Delete-then-reinsert with the same key between two snapshots is invisible: the key is present in both, so a diff reports no change and the warehouse keeps one row where the source had two versions.
  3. Absence isn't replay-safe. Replaying the same dump produces the same "missing" set with no way to tell whether you already applied it. A tombstone with a sequence is: apply if seq > stored_seq.
  4. Absence can't distinguish hard delete from soft delete, or from a key that was re-keyed.
SignalGrain keyOrdering sequenceCommit timeReplay-safeDelete vs filter-drop
Tombstoneyesyesyesyesyes
Full snapshot + completeness markeryesno (one instant)noyes (idempotent set diff)yes, at that instant
Row absent from partial dumpnonononono

The case a snapshot diff silently loses

Source key K=42, version 1. At 09:00 the source deletes K=42. At 09:05 it inserts a new record that happens to reuse the natural key K=42.

  • Snapshots at 08:00 and 10:00 both contain K=42. Snapshot diff: zero changes. The warehouse keeps one row and merges two distinct customers into one history.
  • Tombstone stream: delete@09:00 seq 900, insert@09:05 seq 905. The warehouse closes version 1 at 09:00 and opens version 2 at 09:05.

Applying it

-- close current rows from tombstones; never from absence
update dim_customer
set valid_to = d.commit_ts, is_current = false
from cdc_events d
where d.op = 'D'
  and d.seq > (select max(seq) from applied_watermark)
  and dim_customer.key = d.key
  and dim_customer.is_current;

The snapshot-diff job runs only when extract_complete = true and the extract's row count matches the source's reported count; otherwise it emits zero deletes and raises.

Failure mode and detection

Worked example, hypothetical figures: a 40% extract (1.6M of 4M rows) applied as "missing means deleted" closes 1.6M current rows. The next full extract reinserts them under new surrogates, splitting 1.6M histories and breaking every downstream join keyed on the surrogate. Detect it before the merge commits: alert when the current-row count drops by more than the source's delete rate for the period, or when the extract's row count differs from the source's reported count.

Verification

Feed a partial dump with no completeness flag and assert zero deletes. Feed a tombstone and assert the current row closes at the tombstone's commit time and a reinsert opens a new version.

Curated: · Written: · Reviewed:

Q&A-70The app stores an event log. Why is that log not already the warehouse fact table?

I would answer event sourcing versus warehouse facts by separating storage, schema, and the meaning a consumer is allowed to assume.

An event-sourced log is an immutable sequence of accepted domain events, not an audit of commands that may have been rejected. A warehouse fact has an analytical grain, conformed keys, and measures. Replaying domain events in every dashboard repeats projection logic 40 times.

Concretely, project certified facts from the log with documented reducers, version the reducer, and keep the log as a source, not as the BI table.

The reason for that specificity is a failure I have seen: BI queried the raw log. Two dashboards reduced "OrderCancelled" differently. Cancellations were 2.1 percent versus 6.8 percent, $11 million apart, for a quarter.

Two reductions of the same log.

DashboardCancel rateDollar gap
A2.1 percent—
B6.8 percent$11 million
certified fact, one reducerone rate$0

I would not consider it settled without evidence: apply two reducer versions to the same log fixture and require certified consumers to pin one version.

The log is the source; the fact is the reducer.

Curated: · Written: · Reviewed:

Q&A-34You are moving orders from system A to B. Why is writing to both without an outbox a warehouse problem, not only an application problem?

The application problem is a lost or duplicated order. The warehouse problem is worse: the warehouse is the layer that's supposed to detect divergence, and dual writes make it inherit the divergence instead. It ends up reporting two truths as one fact table.

Why divergence happens: A and B are separate databases, so there is no transaction spanning them. The app writes A, then writes B. Between those two writes the process can crash, the network can drop the second call, or B can commit and the response can be lost. After the migration each order is in one of four states: both, A only, B only, neither. Retries make it worse: both systems deliver at-least-once, so a retried write can land twice.

Why the warehouse amplifies it rather than just mirroring it:

  • Grain. The fact table's grain is one row per order. Two sources with no shared key means the union has no grain: you cannot say what a row is, so you cannot sum it.
  • Dedup. Each system dedupes on its own key. If B re-keys orders, the warehouse cannot join A and B at all; if it can join, retries still produce duplicate rows the union will not collapse.
  • Reconciliation. The warehouse is where you would normally catch this. Ingesting both sources puts the error inside the number you would use to check.

Worked example, illustrative figures: 1,000,000 orders, $50 average.

After dual writeOrdersWarehouse union
both A and B996,0001 row
A only (B write failed)1,0001 row, B downstream never saw it
B only (A write failed)1,0001 row
B duplicated by retry2,0002 rows → double count

Union sums 1,002,000 rows × $50 = $50.1M against a true $50M: $100,000 high, and it stays high until someone reconciles by hand. The 1,000 A-only orders are also missing from B's fulfillment feed: the warehouse is right about the count and wrong about the state.

The fix is one authoritative write plus a stream. Two options:

  • Outbox. Write the order and an outbox row in one local transaction; a relay publishes and marks sent. At-least-once, replayable, works on any database, payload under your control. Costs an extra table, relay lag, and an idempotent consumer.
  • Change stream (CDC). Read the database's replication log: Postgres logical replication, MySQL binlog via Debezium. No extra table, no app change, catches writes the app forgot. Couples you to DB internals, leaks schema changes into the stream, and needs log retention to cover consumer downtime.

Do not reach for an outbox when the second system is a rebuildable projection: a search index, cache, or analytics mart. There a lost event is lag, not correctness, because you can rebuild from the source of truth. The outbox earns its cost when B is a system of record that cannot be rebuilt.

Failure modes to watch: relay lag (alert on oldest-unsent age in the outbox), duplicate delivery (idempotent consumer keyed on the event id, count duplicate keys), ordering (partition by aggregate key, check for version gaps), outbox growth (monitor table size. A dead relay fills the disk).

The test I would run before calling it done: kill one write path in staging and require the warehouse to follow the surviving outbox. If it still unions both databases, the migration is not finished.

Curated: · Written: · Reviewed:

Q&A-71The app writes a row and publishes a message. What warehouse failure does an outbox prevent that "we'll retry the message" does not?

The failure: a committed fact that never reaches the warehouse.

Retry covers exactly one case: the publish call returned an error, so you call it again. It does not cover the process dying between COMMIT and the publish. At that instant the row is durable in OLTP, the publish never happened, and nothing durable records that a publish is owed. The retry loop died with the process. The warehouse is now permanently missing a fact the source of truth says exists: a gap, not a delay.

That is the distinction: retry fixes a failed publish; an outbox fixes a never-attempted publish.

Mechanism. In the same transaction as the business write, insert a row into an outbox table. Both commit or neither does. A separate relay, a poller or CDC reading that table, publishes each row to the bus and marks it sent. Delivery is at-least-once: the relay can crash after publishing and before marking, so it republishes. The warehouse therefore dedups on a stable key carried in the message (the outbox row id, or a business key like order_id plus a version), which makes the load effectively-once.

The idempotency key does its work on the warehouse side, not the outbox side. The outbox guarantees the message is eventually sent at least once; the key guarantees the warehouse applies it once. Drop the key and at-least-once becomes duplicate rows downstream.

Trade-offs. The outbox is not free:

  • Poller lag: the warehouse trails OLTP by the poll interval plus relay time. Sub-second to a few seconds, but a real window.
  • Table growth: the outbox table grows with write volume; without a pruning job or partition drop it becomes the largest table in the database.
  • CDC overhead: reading the table via logical replication adds WAL volume and a connector to operate.
  • Ordering: one poller preserves order; parallel relays do not, so consumers must tolerate reordering.

When something simpler suffices: if the warehouse is loaded by a periodic batch reconcile from a source-of-truth snapshot, or the downstream can re-derive from the OLTP table itself, you do not need per-write outbox rows. The outbox earns its keep when the warehouse must reflect each fact promptly and the publish is not otherwise recoverable.

Failure modes to detect:

  • Relay stuck: watch the age of the oldest unsent outbox row, not the error rate.
  • Duplicate delivery: warehouse counts exceed OLTP for a key; the dedup key catches it.
  • Unbounded table: row count and disk on the outbox table.

Worked example (hypothetical figures). A process crashes after commit, before publish. 27,000 orders sit in OLTP and not in the warehouse for 16 hours until someone notices. A manual re-publish then duplicates 4,100 of them because the warehouse had no dedup key. With an outbox, the relay picks up the 27,000 unsent rows on restart and the warehouse merges on key: 0 missing, 0 duplicates.

PathMissing in warehouseDuplicatesCost paid
publish after commit + retry27,000 until noticed4,100 on re-publishnone until it breaks
outbox + dedup key0 after relay catches up0poller lag, table growth, relay to operate

What I would verify: kill the process between commit and publish in a test; require the relay to emit exactly the missing events, and the warehouse to merge them on key with no duplicates.

Curated: · Written: · Reviewed:

Q&A-26Finance wants yesterday's close. Operations wants the row as it is now. How do you choose CDC or batch without pretending they are the same fact?

The 90-second answer

Ask which fact each consumer needs. Finance needs the state of the book at a cut: every order as it stood at 23:59:59, deletes included as absences. Operations needs the current state of a row and cares about the last write, not the day boundary. Those are two different facts, and one pipeline cannot be the system of record for both.

So name one primary per fact. If the ledger is the fact, batch (or a snapshot read) is primary and CDC is a change feed for downstream caches. If the current row is the fact, CDC is primary and the daily snapshot is derived from it. If you must serve both from one table, you need a merge key (a change sequence (binlog position, LSN, or commit timestamp plus a tiebreaker)) and a defined grain. Without that, a union of the two is a union of two grains.

Why they are not the same fact

Batch is a cut, and only if it reads at one point in time. A nightly SELECT * that takes minutes to scan is not a cut: rows written during the scan land in it, rows deleted during it vanish, and you get a smear. A consistent snapshot (a repeatable-read transaction, a time-travel AS OF, or a flashback query) is a cut.

CDC is a log. Log-based CDC reads the binlog or WAL and emits per-key ordered events, typically at-least-once. Two consequences follow. Deletes are events (tombstones), not absences: batch can only express a delete by the row not being there. And ordering is per key, not global, so "latest" needs a sequence, not an arrival time.

Trade-offs

Batch cutCDC stream
Latency to consumerhours (T+1)seconds
Delete semanticsabsencetombstone event
Orderingnone across rowsper key, by log position
Replay / backfillre-run the dumpre-read the log from an offset, bounded by retention
Cost shapelarge periodic scancontinuous, plus log retention
Schema changedump and stream drift silentlyDDL event or schema registry

Worked example (hypothetical figures)

One order, key 4471: insert 09:12 amount 120; update 11:40 amount 150; cancel 16:05 (status cancelled, amount 150).

Batch dump for D-1, cut at 00:05 on D: one row. 4471, 150, cancelled. CDC for D-1: three events. I(4471, 120), U(4471, 150), U(4471, 150, cancelled).

UNION ALL, no merge key: four rows for one key. Row count inflated 4x; summed amount 150 + 120 + 150 + 150 = 570 against a true close value of 0, because the order is cancelled. That is the double count, and it is arithmetic, not opinion.

Merge on (order_id, change_seq), keep the max sequence per key: one row, cancelled, amount 150 → close value 0. Correct.

Now make the cancel event arrive at 06:00 on D, after the 00:05 cut. The snapshot was already taken with the row live. The close is wrong by 150 for that key and stays wrong until the next cut. That is the late delete.

Failure modes and how I would detect them

  • Double count: the merged view has more than one row per key. Detect with a per-key count assertion plus a sum reconciliation against the source.
  • Lost delete: keys present in the snapshot and absent from the source. Detect with an anti-join on key.
  • Late CDC: stream lag exceeds the snapshot watermark. Detect by comparing the max event time in the stream against the cut time before publishing the close.
  • Schema drift: a column added to the source appears in one path only. Detect by diffing schemas at ingest.

Before calling it settled

Run a delete through both paths and require the closed snapshot to drop the row exactly once. Then write the grain into the table's contract: one row per key per change sequence, or one row per key per day. Two ingest styles are two grains until you name the merge.

Curated: · Written: · Reviewed:

Q&A-104A source can be read nightly, every five minutes, or as a change stream. How do you choose, and what changes in the warehouse when you do?

Start with three facts, not with the word "real-time." What is the tightest consumer freshness SLO? Can the source emit changes (binlog, outbox, CDC) or only a snapshot? And what does a wrong row cost: a restatement, a duplicate, or a late fact that never lands?

Then pick the slowest cadence that clears the SLO. Every step toward streaming buys latency and pays in correctness surface area: ordering, dedup, watermarks, compaction, and a merge path that a dashboard reads through.

CadenceSource must provideWarehouse loadFact/dim change
Batch (nightly)full snapshot or high-water-mark extractpartition overwriteinsert-only fact, batch-built dims
Micro-batch (5 min)incremental extract with a change keyidempotent MERGE or partition swapupsert key + load_ts, late-arrival window
Streamingdurable ordered log (CDC/outbox)merge-on-read or copy-on-writewatermark, allowed lateness, compaction, snapshot retention

Arithmetic that decides it: a 5-minute micro-batch is 288 loads a day against 1 nightly load. If the consumer is a 06:00 dashboard, that is 287 extra merges buying nothing. If the consumer is an on-call ops board, the nightly load is 24 hours stale and fails the SLO.

What changes in the warehouse, concretely:

  • Batch: the fact is insert-only, the partition is the load unit, and reprocessing 2 March is a partition overwrite. No watermark, no dedup beyond the natural key.
  • Micro-batch: the fact needs a stable merge key and a load timestamp. Retries must be idempotent: a MERGE on (order_id, updated_at) or a partition swap, not an append. Late rows land in the next run; you need a bounded lookback, e.g. re-read the last 3 days each run.
  • Streaming: the table becomes an upsert target. On Iceberg that is merge-on-read (delete files, read amplification) or copy-on-write (rewrite per merge, write amplification). You need a watermark and an allowed-lateness policy, or late events are dropped or double-counted. Compaction and snapshot retention become load-bearing: the erasure and rollback stories both depend on them.

Failure modes to name: micro-batch without an idempotent key duplicates on every retry; streaming without a watermark silently drops the 2-hour-late events; batch full dumps with no delete signal keep rows the source deleted. Detect them with a row-count reconciliation against the source per partition, a duplicate check on the merge key, and a freshness lag metric per source.

The trap: choosing streaming because the word is in the requirements, then paying merge-on-read read amplification on a table that a daily dashboard scans. Match the cadence to the consumer, not to the source's marketing.

Curated: · Written: · Reviewed: —

Q&A-44Nightly you get a full dump. Midday you get CDC. How do you keep one fact table?

Core answer

Keep the two feeds in separate staging tables and merge them into the fact table through one keyed path, with a watermark that decides which side wins. The nightly dump is authoritative for which rows exist; the CDC stream is authoritative for what changed, in log order. Never load either straight into the fact table.

  1. Land the dump in stg_snapshot, stamped with the log position it was taken at (a consistent export gives you binlog coordinates or an LSN).
  2. Land CDC in stg_cdc, each row carrying its log position and op (I/U/D).
  3. Merge the snapshot first: upsert every key, and delete fact rows whose key is absent from the dump. That is how deletes that happened before the cut get through.
  4. Apply CDC in position order per key, dropping any change whose position is ≤ the snapshot's captured position, because it is already reflected.

The watermark is the whole trick. Without it, CDC that happened before the dump arrives after the dump load and either resurrects a deleted row or overwrites a newer value with an older one.

Merge

-- fact: (customer_id PK, plan, mrr, last_pos, deleted_at)
MERGE INTO fact AS f
USING stg_cdc AS c
  ON f.customer_id = c.customer_id
WHEN MATCHED AND c.pos > f.last_pos THEN UPDATE SET
  plan = c.plan, mrr = c.mrr, last_pos = c.pos, deleted_at = NULL
WHEN NOT MATCHED AND c.op <> 'D' THEN INSERT
  (customer_id, plan, mrr, last_pos) VALUES (c.customer_id, c.plan, c.mrr, c.pos);

c.pos > f.last_pos makes the merge idempotent: at-least-once CDC redelivery is a no-op. Deletes are soft (deleted_at) so a late insert cannot silently resurrect a row without passing the position check.

Trace of the ordering hazard

Snapshot cut at pos 1000. CDC buffer holds pos 998 (customer 7, mrr 50→60) and pos 1002 (customer 7, mrr 60→70). The snapshot at 1000 has mrr=60.

  • Apply snapshot: customer 7 mrr=60, last_pos=1000.
  • Apply CDC in order: pos 998 ≤ 1000 → drop, already reflected. pos 1002 > 1000 → mrr=70.
  • Result: mrr=70. Correct.

Apply CDC first and the snapshot second and you get mrr=60: the 1002 update is lost.

Trade-offs

ApproachCostWhen it is right
Truncate + reload from dumpfull rewrite nightlytable under a few million rows, no history needed
Snapshot diff against current tablefull scan + compareno trustworthy CDC; but cannot see intermediate states
Snapshot + CDC merge (above)incrementalCDC is reliable and ordered

A full reload midday is the right call when CDC has been backfilled or is known-lossy: reload from a fresh dump, then resume CDC from that dump's captured position. It is also right when the table is small enough that the rewrite is cheaper than reconciling.

Failure modes

  • Dump merged as inserts: row count doubles, distinct keys unchanged. Detect with count(*) / count(distinct key): a ratio near 2 means you inserted a snapshot.
  • Late CDC after the snapshot: per-key last_pos goes backwards. Detect by asserting last_pos is monotonic per key.
  • Lost deletes: rows absent from the dump are never removed. Detect by diffing dump keys against fact keys nightly.

Curated: · Written: · Reviewed:

Q&A-41A streaming aggregation closes an hour. What is the watermark doing, and what is still allowed to arrive?

Core answer

A watermark is the engine's event-time clock: a monotonically advancing timestamp W that means "I believe no event with timestamp ≤ W will arrive again." When W crosses the end of the hour window, say 10:00:00, the window fires and emits its aggregate. That firing is the close.

What is still allowed to arrive: events whose event timestamp falls inside the hour can still show up after the close, and whether they change the result depends on allowed lateness. In Flink, allowedLateness keeps the window state alive; a late event with a timestamp inside the window re-triggers it and emits an updated aggregate. Once the watermark passes windowEnd + allowedLateness, the state is purged and any further late event is dropped, or routed to a side output if you set sideOutputLateData. The default allowedLateness is 0, so the window fires and purges the instant the watermark crosses the boundary, and everything after is late.

Mechanism

The watermark advances as maxObservedEventTime - outOfOrdernessBound (Flink's forBoundedOutOfOrderness). In a parallel job the operator's watermark is the minimum across all input channels, so one slow or idle partition holds the whole watermark back. The close is therefore a claim about the slowest producer, not the average.

Trace: window [09:00, 10:00), out-of-orderness 5 min, allowedLateness 5 min.

EventEvent timeWatermark afterEffect
e109:5809:53buffered
e210:0309:58buffered
e310:0710:02WM crosses 10:00 → window fires, emits sum
e409:5910:02late, within lateness → re-fires, updated sum
e509:4510:02late, within lateness → re-fires
e609:5010:10WM 10:10 > 10:05 → state purged, e6 dropped or side-output

Trade-offs

A larger out-of-orderness bound drops fewer late events but buffers more state and delays every result. A larger allowedLateness means more re-fires and longer state retention, since each open window holds its accumulator. Kafka Streams calls the same knob a grace period: after window end + grace, late records are dropped. Beam exposes allowed lateness plus an accumulation mode (discarding vs accumulating) and triggers.

Deciding criteria: set the bound from the p99 of observed event-time lag per source, not the mean. Set allowedLateness from how much downstream can tolerate re-emitted results, an idempotent sink keyed on the window grain absorbs them cheaply, versus how long you can afford to hold state.

Failure modes

  • Wall-clock watermark (now - 10s): a producer stall of minutes makes the watermark race ahead of event time and purge windows that never received their data. Detect it by comparing watermark lag against source lag; fix it by deriving the watermark from event time.
  • Idle partition: a source with no data stops advancing its watermark, holding the min back and stalling the close. Flink's withIdleness handles this.
  • Skew: one hot key's window holds state far longer than the rest, so purge timing and memory diverge across keys.

Curated: · Written: · Reviewed:

Q&A-85A partition has no events for 40 minutes. Why does a min-of-partitions watermark stall the others, and why would a global max not?

A window fires when the watermark passes its end time. With min-of-partitions, the watermark is the slowest partition's progress. One silent partition pins that min at its last event time, so every window ending after that time stays open across all partitions. A global max takes the fastest partition's progress, so busy partitions close their windows; the idle partition's later events are then late.

Mechanism: suppose four partitions and a window [10:00, 10:05). P0-P2 have events through 10:40, so their watermarks are 10:40. P3 has been silent since 10:00, so its watermark is 10:00. Min = 10:00. The window end 10:05 is greater than 10:00, so no window closes. Global max = 10:40, so the window closes. If P3 later emits an event at 10:03, the current watermark 10:40 is already past 10:03, so that event is late. With allowed lateness 0 it is dropped; with a side output it is routed there. That is the cost global max hides.

Trade-off: min-of-partitions preserves correctness for the idle partition at the cost of stall. Global max preserves progress at the cost of silently marking idle-partition data late. The deciding criterion is whether the idle partition is truly dead or just quiet. If dead, global max or idle timeout is fine. If quiet and its data matters, min plus idle timeout with bounded lateness is safer.

Idle timeout: Flink's withIdleness(Duration) marks a source idle after no events for the duration; downstream min ignores it. When it resumes, its watermark may be behind, so it can stall again until it catches up; use watermark alignment or allowed lateness. Emit a gap metric when idle fires.

Failure mode: a canary producer died. 14 busy partitions were delayed 2.5 hours because 2 idle partitions pinned the min. The freshness SLO failed even though the busy partitions' events were on time. Global max would have closed the busy partitions, but any late canary events would have been dropped. Detection: per-partition watermark lag, idle-timeout count, late-event side-output rate.

PolicyBusy windowsIdle partition's late data
min of per-partitionstall until idle advances or timeoutnone dropped, but stall
global maxclose on busy progressevents with timestamp < max are late/dropped
min + idle timeoutclose after timeoutevents after timeout may be late if timestamp < watermark

Curated: · Written: · Reviewed:

Q&A-83The hour closed. 20,000 events arrive 2 hours late. What is the warehouse path that is not "drop them"?

Assumptions decide this, so take them first. The closed hour is a materialized table at one row per (account_id, hour_utc); dashboards have already read it; every event carries event_id plus those two grain keys; the raw events for that hour are still retained. Twenty thousand late events two hours after the close is a bounded correction, not a stream that is still flowing, if it were still flowing, the watermark policy is the bug, not the repair.

The answer: land the batch, fold it into the hour at the same grain, and republish the hour as a new revision consumers can see. Appending is the option that fails. Dropping is defensible only when the hour is contractually frozen, and then the correction posts in the next period, never into the closed one.

PathClosed hour afterwardsIdempotent on retryConsumer work
Append the batchTotals inflatedNo, every retry adds againNone, while the number is wrong
Recompute the hour from raw events, snapshot-replaceCorrectedYes, by constructionRefresh that hour
Merge the deduped batch at grainCorrectedOnly if the dedupe bookkeeping commits with the mergeRefresh that hour
Compensating delta rowOriginal rows untouched, signed delta addedYes, if the delta carries a revision idEvery reader must sum a net column

Recompute-and-replace is the default when the transformation is deterministic and raw events are retained: idempotency for free, since a retried job writes the same snapshot twice. Merge-at-grain wins when the hour is expensive to rebuild or the raw layer is gone. Compensating deltas are for write-once facts whose readers already sum a net column. A legally closed hour takes none of these.

The merge, in the SQL form Delta and Iceberg both accept, assuming the target stores the pre-aggregate and processed_events holds every event_id already folded in:

MERGE INTO hourly_metrics AS t USING ( SELECT l.account_id, l.hour_utc, sum(l.amount) AS amount FROM late_landing AS l WHERE NOT EXISTS (SELECT 1 FROM processed_events AS p WHERE p.event_id = l.event_id) GROUP BY l.account_id, l.hour_utc ) AS s ON t.account_id = s.account_id AND t.hour_utc = s.hour_utc WHEN MATCHED THEN UPDATE SET amount = t.amount + s.amount WHEN NOT MATCHED THEN INSERT (account_id, hour_utc, amount) VALUES (s.account_id, s.hour_utc, s.amount);

Where this goes wrong:

  • Duplicates inside the batch. A producer that retried around the watermark puts the same event_id in the original delivery and in the late batch. The merge's real row count is the anti-join result, not 20,000, so emit that count and alert when it drops well below the batch size.
  • Retry of the merge itself. If processed_events commits separately from the merge, a retried job passes the anti-join a second time and adds the same amounts again. Commit them together.
  • Order within the batch. Two late events for one grain key, one additive field and one last-write-wins field. Aggregate to grain with max(event_time) before merging; an unordered merge does not fix the second field.
  • Two writers on one hour. Snapshot isolation makes the second commit fail, so retry it. Rewriting the hour's files in place instead of snapshot-replacing is worse: a reader mid-rewrite sees half the old hour and half the new one.
  • No unique key on the grain. With one, the append of a second aggregate row is rejected outright and the batch fails loudly. Without one, it succeeds silently, which is how the hole gets made.

Arithmetic, hypothetical: the hour holds 1,000,000 events and 250,000 USD across 50,000 accounts. The 20,000 late events span 1,200 accounts and carry 30,000 USD; 4,000 of them, carrying 6,400 USD, are duplicates already folded in. Appending the raw batch reports 256,400 instead of 250,000: 2.56 percent high, repeated identically at every rollup layer, so three layers cost three reconciliations. Appending aggregate rows without a unique key is worse: those 1,200 accounts each hold two rows for the same hour and any sum() doubles their contribution.

I would not call it settled without one check: after the repair, the hour recomputed from raw events matches the hour in the table row for row at grain, and the published revision id is higher than the one the dashboards cached.

Curated: · Written: · Reviewed:

Q&A-84You need "sessions per user." Why is a 30-minute tumbling window the wrong grain?

Before calling session versus tumbling windows done I would write down the consumer, region, or batch window nobody loaded.

A tumbling window cuts time at clock boundaries. A session window cuts on inactivity. Clock cuts split one visit across two boxes. They do not glue two users behind NAT if the window is keyed by user; that merge is an IP-key defect.

Concretely, define session gap, key on user plus session_id minted by the gap, and store session facts at that grain rather than at 30-minute clock slices.

The reason for that specificity is a failure I have seen: 30-minute tumbles split 22 percent of visits at clock boundaries. A separate job keyed by IP glued 9 percent of adjacent users on shared NATs. Session conversion was 6 points off. The glue was not tumbling's fault.

Tumbling 30 minutes versus session gap.

WindowVisits split at clockUsers gluedConversion error
30-minute tumble keyed by user22 percent0 from tumbling6 points from splits
30-minute tumble keyed by IPalso splits9 percent NAT mergemixed
30-minute inactivity session keyed by user000

I would not consider it settled without evidence: send a 40-minute visit and two users behind one NAT, and require 1 session for the visit when keyed by user, and 2 people unless the key is IP.

A clock box is not a visit; an IP key is not a user.

Curated: · Written: · Reviewed:

Q&A-8A shipment event arrives 6 days after the warehouse closed that day. What must still be true of the fact?

What a strong candidate says first. Four invariants hold, whatever the physical layout.

  1. Event time is preserved and is the attribution key. The fact belongs to 2 March. Ingestion time is metadata, not the business period.
  2. Dimension resolution is as-of event time. It joins the type-2 version where valid_from <= event_time < valid_to, not the version current on 8 March.
  3. The fact is idempotent under re-delivery. A 6-day-late row is usually a retry or a backfill, so the write is a keyed upsert on a deterministic event key, not an append.
  4. The affected aggregate is restatable. Either the pipeline can write into the closed 2 March period, or the query unions a late-arrivals buffer with the historical partition. A published metric that is never invalidated stays wrong.

Why. A fact carries two clocks: event time (when the shipment closed) and ingestion time (when the row landed). Business metrics are defined on event time; processing time is a physical choice that must not leak into the query contract. If the partition key is load date, every query must still filter the event-time column, and the partition key must never stand in for the business period. Two couplings bite: allowed lateness must be ≤ the retention of the historical partition, or the partition is dropped before the fact can land; and the watermark that declares a period final must be published, or consumers cannot tell a provisional 2 March from a settled one.

Physical layoutLate writeQueryFailure mode
event-time partitionmerge/overwrite into an old partitionpruning workspartition already compacted or past retention → write rejected or dropped
ingestion-time partitioncheap appendmust filter event time; no pruningquery filters the partition key only → fact invisible
event-time partition + late bufferappend to buffer, periodic idempotent mergeunion of two scansbuffer never merged → unbounded growth; non-idempotent merge → double count

Worked trace (illustrative figures). Shipment S-4471, event_time = 2024-03-02T18:40Z, ingested_at = 2024-03-08T09:12Z; the lane's carrier contract changed on 2024-03-05.

  1. Dedupe on (S-4471, 'departed', 2024-03-02T18:40Z) → no existing row → insert.
  2. Dimension join → carrier version valid 2024-01-01 .. 2024-03-05 → Northwind. Joining the current version returns Acme (valid from 2024-03-05): wrong carrier, right row count.
  3. Metric recompute for 2024-03-02 reads partition + late buffer.
  4. Watermark for 2024-03-02 is 2024-03-09T00:00Z (allowed lateness 7 days), so 6 days is inside the window and the fact is accepted; at 8 days it routes to the dead-letter path instead.
MERGE INTO fact_shipment AS t
USING staged_shipment AS s
   ON t.shipment_id = s.shipment_id
  AND t.event_type  = s.event_type
  AND t.event_time  = s.event_time
WHEN MATCHED THEN UPDATE SET qty = s.qty, ingested_at = s.ingested_at
WHEN NOT MATCHED THEN INSERT VALUES (...);

Failure modes and how they are detected.

  • Streaming window with allowed lateness 0 fires and discards the row. Detect: count rows where ingested_at - event_time exceeds the window; alert on nonzero.
  • Load-date partition plus a partition-key filter hides the fact. Detect: reconcile per-day fact counts against the source event feed.
  • As-of-load-time dimension join mis-attributes carrier or region. Detect: sample late facts and compare attributes to the source system's as-of values.
  • Append instead of upsert double-counts on retry. Detect: fact count vs distinct source event count per day.
  • Retention shorter than allowed lateness. Detect: assert allowed_lateness <= partition_retention at deploy time.

Lateness is a delivery property, not a new grain.

Curated: · Written: · Reviewed:

Q&A-66The SLO says 06:00 UTC. The job finished at 06:00. Why can the product still be stale?

What I'd say first

The 06:00 SLO is a promise about data. The job's finish time is a fact about the scheduler. They coincide only if the job read everything it was supposed to read. A job that succeeds on a truncated extract finishes at 06:00 and leaves the product stale, because success means "the process exited 0", not "the interval is closed".

So the freshness SLO has to be measured on the consumer-visible table: max(event_time) in the partition the product reads, compared against the close of the interval. If that max is 22:10 from the prior day, the SLO is missed no matter what the orchestrator says.

Why they diverge

Three causes, none of which raise an exception:

  • Partial extract. The source API paginates and stops early, or a WHERE clause on a timestamp column hits a timezone bug and cuts the window short.
  • Source-side lateness. The upstream batch had not landed when the extract ran, so the extract is complete for what existed.
  • Silent filtering. A join or dedup step drops rows without failing.

The signals, side by side

SignalValueSLO
Airflow task statesuccess at 06:00green
max(event_time) in loaded partition22:10 prior daymiss
source_count vs loaded_count for 22:10–06:0096,000 vs 0miss

Trade-offs

Orchestrator-time SLO. Cheap, needs no source access, catches crashes and timeouts. Blind to silent truncation: exactly the failure that matters. Use it as an operational alert, not as the freshness SLO.

Event-time SLO on max(event_time). Catches truncation and source lateness. Wrong when the source is legitimately quiet: a B2B order system with no overnight traffic has a max of 22:10 every night and would page forever. Fix by comparing against a source-side watermark (the source's own max event_time) rather than wall clock, or by setting the threshold from the source's diurnal baseline.

max() is also fragile in the other direction. One event with a skewed producer clock, a mobile client with the wrong timezone, makes the partition look fresh while the bulk is stale. A p50/p95 of event_time, or a count of rows in the last hour, is harder to fool.

Count checks (source_count vs loaded_count). Catch drops that max() misses, such as a filter that removes 5% of rows but keeps the newest. Wrong when the pipeline legitimately dedups or upserts, loaded_count is below source_count by design, or when the source itself is late, in which case you page on an upstream problem. Compare against the source's own count for the same closed interval, not a fixed expectation.

Failure modes and detection

  • Silent truncation: max(event_time) lags the close. Alert on lag above threshold.
  • Source lateness: source_count for the window is 0 or low. Compare to the source watermark; mark the partition incomplete and re-run rather than failing the pipeline.
  • Clock skew: max(event_time) ahead of wall clock. Bound it with event_time <= now + 5 min.
  • Backfill: old event_time rows written now. Freshness on max(event_time) is unaffected; a "rows loaded this run" check would false-alarm.

Verification

Succeed a job on a truncated extract and require the freshness check to fail on max(event_time) and on counts. Then run it against a quiet window and require it to pass.

Curated: · Written: · Reviewed:

Q&A-23A product page says "daily." What has to be in the SLA before a consumer is allowed to depend on it?

"Daily" is a cadence, not a contract. Before a consumer can depend on it, the SLA has to pin five things: when the day closes, which day it is, what "complete" means, what the consumer sees when a check fails, and whether a closed day can change. A page that says only "daily" answers none of them, so the consumer is really depending on the producer's good intentions.

The fields that have to be there

FieldExample valueWhat it decides for the consumer
Close time06:00 UTC, for the previous UTC dayEarliest safe read; before it the partition is partial
Day boundaryUTC calendar day, event time"Daily" for a US business is not a local day
Completeness≥99.5% of source rows, denominator = upstream manifest countSeparates "closed" from "closed and correct"
Freshnessqueryable by 06:30 UTCClose and publish are different deadlines
Grain / uniquenessone row per (sku, day); duplicates rejectedWhat a join will do
Schemaadditive changes anytime; breaking changes 30 days' noticeWhether tomorrow's load breaks the consumer
Failure signalpartition carries status=incomplete; downstream must not loadThe trigger to skip, machine-readable
Restatementclosed days immutable; corrections land as a new version with a notificationWhether yesterday's number can move
Ownernamed team, pager, escalation pathWho to wake at 06:05

The completeness number is meaningless without its denominator. "99.5% complete" against a row count the producer also computes is circular; it has to be measured against something independent: an upstream manifest, a source-system count, or a control total.

What the failure path looks like

Hypothetical, but this is the shape:

expected = manifest.count(day)        # 1,000,000
observed = count(distinct source_id)  #   880,000
ratio    = observed / expected        #      0.88
if ratio < 0.995:
    publish(day, status="incomplete")
    page(owner)
else:
    publish(day, status="complete")

At 06:00 the check runs, ratio 0.88 fails the 0.995 floor, the partition is published as incomplete and the owner is paged. A downstream job reads status before it loads, sees incomplete, skips the day and alerts its own owner. The consumer gets a missing day, which it can reason about, instead of a plausible-looking partial it cannot.

Failure modes and how they show up

  • No close time: consumers poll, read a partial, and it looks complete because the row count is merely low, not zero. Detect with a consumer-side assertion that the partition it read was status=complete.
  • Completeness unmeasured: a hole passes every check. Detect with a day-over-day row-count delta alert and a control-total comparison against the source.
  • Silent restatement: a closed day is rewritten and the consumer's number changes under it. Detect by versioning partitions and alerting on writes to closed days.

The contract is two-sided. The consumer has to hold up its end: don't read before close, check the status flag, decide in advance what a skipped day means (carry the previous partition, or fail loudly), and pin the schema version it was built against. An SLA the consumer ignores is not a dependency, it is a hope.

When "daily" is enough. Exploratory dashboards and internal analysis can live with a best-effort cadence. Anything feeding pricing, billing, or a customer-facing number cannot: those need the close time, the completeness floor, and the failure signal, or the consumer is guessing.

Curated: · Written: · Reviewed:

Q&A-67How do you know a daily fact has all of the source day's rows?

Short answer: row counts alone can't tell you. A daily fact is complete when it reconciles to source control totals taken with the same filter the load uses, and when the set of business keys loaded equals the set of keys in the source for that day. Publish both, define the allowed variance per control, and fail the promotion when any control is out.

Why counts alone fail. A count is a scalar; it can't distinguish a missing row from a duplicated one. If the extract drops 4.8% of APAC rows and a retry path inserts the same number of duplicate rows from another region, source count and loaded count match exactly and the fact table is still wrong. The only way to catch substitution is to compare something keyed: the key set itself, or a checksum over the keys.

What to publish. For each daily partition, three controls:

  • source count and loaded count, both computed with the same documented filter (e.g. order_ts >= '2024-03-01' AND order_ts < '2024-03-02' AND region IS NOT NULL);
  • key-set reconciliation or keyed checksum over the business key;
  • one independent domain total, e.g. SUM(net_revenue), which catches errors neither of the first two sees (wrong column mapped, currency conversion applied twice).

Key-set reconciliation at daily grain:

-- one row per business key per side, same day, same filter
SELECT
  (SELECT COUNT(*) FROM source_keys)                                   AS src_rows,
  (SELECT COUNT(*) FROM loaded_keys)                                   AS ld_rows,
  (SELECT COUNT(*) FROM source_keys s
     LEFT JOIN loaded_keys l ON s.order_id = l.order_id
   WHERE l.order_id IS NULL)                                           AS missing_keys,
  (SELECT COUNT(*) FROM loaded_keys l
     LEFT JOIN source_keys s ON s.order_id = l.order_id
   WHERE s.order_id IS NULL)                                           AS extra_keys;

missing_keys = 0 AND extra_keys = 0 is the completeness condition; the counts are reported but are not the gate. At billions of rows a full anti-join per day is expensive, so use SUM(HASH(order_id)) on each side instead: order-independent, catches substitution, and a 64-bit hash collision is not a practical concern at daily volumes.

Failure mode. A timezone filter dropped 4.8% of APAC rows while a retry path duplicated the same number of rows elsewhere, so total row counts matched. APAC revenue was understated by $3.1M for 21 days until keyed reconciliation exposed the substitution.

CheckCount gapKey-set gapRevenue impact
source vs loaded count only0not measured$3.1M understated for 21 days
count + keyed reconciliation04.8% of APAC keys missingcaught on day 1

Test the check, not just the data. Drop 5% of source keys and duplicate another 5% in a fixture, then require the completeness contract to fail despite equal row counts. A control that has never failed on a known-bad fixture is not evidence.

Trade-offs. Key-set reconciliation is exact but costs a join or a full scan per partition; checksums are cheaper but only tell you that something differs, not which keys. Run the exact reconciliation on the day's partition and the checksum across full history. If the source has no stable business key, you cannot do this: get one, or accept that completeness is unverifiable.

Curated: · Written: · Reviewed:

Q&A-68Why must uniqueness be tested on the full partition rather than on a 1-percent sample?

The short answer. Uniqueness is a property of the key set, not of the rows you happened to look at. A sample certifies the sample. Duplicates arrive in clusters (one retried file, one re-run batch, one bad partition boundary) so a sample that misses the cluster returns green and tells you nothing about the partition. The gate that certifies the partition has to be count(*) = count(distinct key) over the whole partition.

Why clusters break sampling. A sampled test gives a usable error bound only if duplicates are spread uniformly at random across the partition. They never are. They come from a single writer retry, a single file loaded twice, a single upstream backfill. The sample's pass rate is then evidence about 1% of the data, not about the partition.

Worked example, illustrative figures: 400 files, 2 of them loaded twice, 180,000 duplicate rows. A 1% file sample draws 4 files; the chance it draws at least one of the 2 bad files is 1 − (396·395)/(400·399) ≈ 2%. The test is green 98% of the time. Downstream, a join to payments fans each duplicated key out to every matching payment row: 180,000 duplicates × ~78,000 matches ≈ 14 billion intermediate rows, and the close times out.

TestResultDuplicate rowsClose
1-percent sample uniquegreen180,000timeout
full partition count vs distinctfail180,000blocked

What full-partition testing costs. count(*) versus count(distinct key) is a full scan plus a hash aggregate and shuffle. On a multi-terabyte partition that is real compute and minutes to hours, and it can block the very close window it exists to protect. Three ways to pay less without weakening the gate:

  • Certify the delta, not the history. Run the full-partition check on new and changed partitions and keep prior partitions certified. Full-partition semantics, incremental cost.
  • Screen cheaply first. approx_distinct (HyperLogLog, roughly 0.8% standard error at 2^14 registers) is one-sided: if the approximate distinct count is below count(*), duplicates exist and you can fail immediately without the exact pass. If it is at or above count(*), it proves nothing and you still need the exact count.
  • Where sampling is legitimate: a smoke check in dev or CI on small fixtures, or an early signal on a partition you are going to fully certify anyway. It is a first-pass filter, never the gate.

Failure modes, and how each is detected.

  • Duplicate cluster from a retried load. Detect by alerting on the count-minus-distinct delta itself, not just on pass/fail, so a small cluster is visible before it reaches a join.
  • Join fan-out. Detect by tracking the output-to-input row ratio of the join and alerting when it exceeds the expected cardinality for the key.
  • The full-partition test blocking the close. Detect by comparing test runtime against the window; move it to the delta or run it before the window opens.

The worst ordering is a green sample followed by a red full test in production, because by then the duplicates have already been joined. Uniqueness that is sampled is a rumour.

Curated: · Written: · Reviewed:

Q&A-57not_null and unique tests are green. How can the grain still be wrong?

Grain is uniqueness of the business key, not of the surrogate. not_null and unique on order_sk test the warehouse's key generator: the surrogate is minted inside the model, so it is distinct by construction and both tests pass whatever the rows contain. They confirm the generator ran. They say nothing about whether one row means one order line.

The test that answers the question is unique on the published business key: (order_id, line_id), not order_sk. Pair it with a row-count reconciliation against the source at the same grain, and a sum check on an additive measure: if line revenue summed from the fact table exceeds the header total, the model fanned out.

Why the surrogate test cannot fail

A surrogate key comes from row_number(), a hash, or an identity column evaluated inside the model. Uniqueness is a property of that expression, not of the input. Any fan-out that happens before the key is minted: a join to a table with more than one matching row per order, a source that changed grain from header to line, a UNION ALL of two sources at different grains: produces more rows, and each gets its own fresh surrogate. The test stays green while the grain is wrong.

TestWhat it assertsGrain failure it catches
unique(order_sk)the key generator produced distinct valuesa broken generator, a merge that re-mints keys
unique(order_id, line_id)one row per order linefan-out joins, a source that changed grain, mixed-grain UNION ALL
sum(line_revenue) vs header totalmeasures reconcile to the declared graindouble-counting that survives a passing key test

Worked example

A 12M-row order line model. unique(order_sk) green. unique(order_id, line_id) returns 410,000 rows: 410,000 order lines present more than once. Those rows carried an average of $22.44 of line revenue each, so 410,000 × $22.44 = $9.2M of revenue reported that the orders never contained. Every dashboard on top of the model was overstated by that amount, and the test suite was green.

When the surrogate test is still worth keeping

It is nearly free, no sort or hash over wide business-key columns, and it catches a real class of bug: a hash collision, a non-deterministic row_number() over an unordered window, a merge that re-mints keys on update. Keep it as a canary, not as the grain test. On an event fact where the grain genuinely is one row per event, unique(event_sk) is a legitimate grain test, because the surrogate and the business key are the same thing. And when the business key is late-arriving or still under negotiation, the surrogate test is the only one you can write today: write it, and add the business-key test the moment the key is agreed. The business-key test costs a hash or sort over the key columns; on 12M rows that is seconds. dbt-utils ships unique_combination_of_columns for exactly this.

How to check it

Insert two rows with the same (order_id, line_id) and different surrogates. unique(order_sk) stays green; the business-key test fails. If it does not fail, the test is not testing the grain.

Curated: · Written: · Reviewed:

Q&A-36A uniqueness test fails at 05:10. What is the architecture if the team "loads anyway and files a ticket"?

The architecture is a validation gate with three enforcement modes, plus an exception service the promotion job cannot bypass. "Load anyway and file a ticket" is not a missing control: it is a fourth path through the gate, and the architecture's job is to make the ticket a machine-checkable precondition rather than a promise someone remembers to keep.

Assumptions: batch ingestion on a scheduler, a contract registry keyed by dataset, and a warehouse fact table with no enforced unique constraint (the common case. Most columnar warehouses do not enforce one).

Components

  • Scheduler: triggers the DAG at 05:00; owns the run id.
  • Ingest: lands raw files/CDC into staging.raw_orders, partitioned by business date.
  • Contract registry: versioned uniqueness/not-null/referential contracts per dataset, each with an owner and an enforcement mode.
  • Validator: runs the contract against staging, writes a row to validation_results (run id, contract id, pass/fail, failing key set, bad-row count).
  • Gate: reads the contract's mode and routes: block, quarantine, or load_anyway.
  • Quarantine store: bad partition written aside with the validation run id.
  • Exception service: ticket integration (Jira/ServiceNow); issues a signed exception with approver identity, scope (dataset, partition, date range) and expiry.
  • Promotion job: writes rows to fact_orders and stamps load_manifest with run id, contract id, exception id, row counts.
  • Consumer registry + notifier: publishes a partial-load event to named downstream consumers.
  • Reconciliation job: re-runs the contract over landed data and repairs; must be idempotent.
05:00 scheduler -> ingest -> staging.raw_orders
05:10 validate(orders.unique_order_id) -> validation_results
        |
        +-- pass ----------------> promote -> fact_orders
        +-- fail --> gate(policy)
                      +-- block       -> halt, page owner
                      +-- quarantine  -> quarantine_orders + promote good rows
                      +-- load_anyway -> requires exception_id
                                         -> promote all rows + ticket + notify

Trace of the 05:10 event

Validator finds 4% of rows sharing order_id with an earlier row in the same partition. Gate reads mode load_anyway. Promotion job queries the exception service for an unexpired exception covering this dataset and partition; with none, it refuses and the run halts. With one, it writes every row, stamps load_manifest.exception_id, and the notifier emits a partial-quality event to the consumer registry. The bad rows are still in fact_orders: the exception bought time, not correctness.

Where this architecture breaks

Worked example, hypothetical figures: the replay path. Reconciliation re-reads staging and writes back to fact_orders. If that write is an insert rather than an upsert on order_id, the 4% bad rows get a second copy.

PathRows promotedDuplicate facts after replayRefunds understated
load_anyway, insert-based replayall, incl. 4% bad1,900,000$410,000
load_anyway, upsert on order_idall, incl. 4% bad0$0
quarantinegood rows only0$0

The duplicate count is the failure mode to design against, and it lives in the reconciliation job, not the gate. A ticket is not a quarantine: it is a pointer to an approval, and only the promotion job's exception check makes it binding.

What to verify before trusting it

Fail uniqueness in a staging load and assert 0 rows promoted unless a valid exception_id is present; assert the notifier emitted to every registered consumer; run reconciliation twice and assert the fact-table row count is unchanged.

Curated: · Written: · Reviewed:

Q&A-25When should schema and grain tests run: after the warehouse table exists, or before the producer is allowed to write?

Split the gate. Schema compatibility is a property of a single record, so it fails in the producer pipeline before publication. Grain and uniqueness are properties of the set, so they need the warehouse's existing keys and run in an isolated landing area that refuses promotion to the certified table. Either gate alone leaves a hole.

Why the split falls where it does:

  • A schema check needs no history. Given the registry subject and the record, you can accept or reject inside the producer's process, before anything is written anywhere. That is the cheapest place to fail and the only place that stops bad bytes from landing at all.
  • A grain check needs history. "Is this key already present?" is unanswerable from the incoming batch alone. Two producers can each emit a key that is unique within their own stream and collides with the other's, and one producer can re-emit a key from last week. Neither is visible until you compare staged rows against the certified table's existing keys.

So the pipeline is: publish the wire contract as a schema-registry subject; validate each record against it in the producer; stage the accepted bytes; run grain and uniqueness assertions against staged ∪ existing keys; promote only when both layers pass. The staging area is not a second certified table: it is a quarantine that holds rows until the set-level assertions clear.

Trade-offs. Producer-side schema validation costs a registry lookup and a serialization pass per record, which is cheap next to a backfill. Staging adds a copy and a promotion step, so there is a gap between the producer writing and a consumer reading; that gap needs its own alerting, because a stuck promotion looks exactly like a stalled producer from the consumer's side. If a table has one writer and an append-only natural key, the grain check is nearly free and can run inline. The moment a second writer or a re-run exists, you need the set-level check against history.

Failure mode I have seen. Tests ran in dbt after load. A producer widened an enum on a Friday. 6.4 million incompatible rows sat in the certified table until Monday. 14 consumers failed; 2 had already published numbers downstream.

GateRows in the certified tableConsumers that published numbers
dbt after load6,400,0002
producer schema + staging grain checks00

The second row is the same incident replayed with the split gate: the enum widening is rejected at the producer, so nothing lands.

How I would verify the gate rather than assume it: send an incompatible record from a producer harness and require a schema reject; then send a schema-valid record that duplicates an existing grain key and require staging to block its promotion. If either one passes through, the gate is decorative.

Curated: · Written: · Reviewed:

Q&A-86The event has no natural id. How do you still load exactly once?

Exactly-once delivery doesn't exist end to end. What you build is at-least-once delivery plus an idempotent sink: the event may arrive twice, but the second write is a no-op. So the real question is what the sink keys on, and that is decided by the retry path, not by the warehouse schema.

Assume the broker gives at-least-once (Kafka's default without transactions) and the sink is a database that can enforce a unique constraint. Then: derive a deterministic key, store it in the row, and write with an upsert. Three sources for that key, best first.

Key sourceSurvives retrySurvives repartition / republishCollapses legitimate duplicatesProducer change
Producer-assigned id (aggregate_id + sequence)yesyesnoyes
hash(stable payload fields)yesyesyes: two identical events become oneno
hash(topic, partition, offset)yesnonono
hash(produced_at) or any per-attempt valuenononono

Transport coordinates are the cheapest key because the consumer already holds them: record.topic(), record.partition(), record.offset(). They are stable across retries of the same record: a retry re-delivers the same offset. They break the moment the event is copied: republishing to a new topic, repartitioning a topic from 8 to 24 partitions, or a backfill that re-emits from a source system all mint new coordinates for the same fact. Log compaction is not one of those cases: it drops superseded records but leaves the offsets of surviving records unchanged, so an offset key still holds after a compact.

If the producer can be changed, get an id into the contract: (aggregate_id, sequence_number) or a UUIDv7 minted once where the event is created, not per publish attempt. That is the only key that survives both retries and repartitioning without collapsing distinct events.

The sink side is the same in all three cases:

CREATE TABLE events (
  event_key   bytea PRIMARY KEY,          -- sha256 of the chosen identity
  payload     jsonb NOT NULL,
  ingested_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (event_key, payload)
VALUES ($1, $2)
ON CONFLICT (event_key) DO NOTHING;       -- rows affected 0 == duplicate

ON CONFLICT DO NOTHING makes the retry a no-op and gives you a duplicate counter for free: rows affected is 0 on a replay, 1 on first insert. If the sink is a warehouse without unique constraints, the equivalent is a MERGE on the key, or a staging table plus ROW_NUMBER() OVER (PARTITION BY event_key ORDER BY ingested_at) = 1.

Failure modes worth naming:

  • Key built from a field the producer refreshes per attempt. produced_at, an ingest timestamp, or a UUID generated inside the retry client all change on retry, so every attempt is a new fact. Detect it by replaying one known event through the retry path and asserting the sink row count stays at 1.
  • Content hash collapsing real duplicates. Two genuine events with identical payloads (same user, same amount, same second) hash the same and one is lost. Detect by comparing sink row count against source count for a batch you know contains a legitimate repeat.
  • Unbounded dedup table. If keys live in a side table rather than on the row, size it: 5k events/s at ~60 bytes per key row (16-byte key, 8-byte timestamp, index overhead) is ~26 GB/day, so a 7-day window is ~180 GB. Set the TTL longer than the maximum retry or backfill horizon, and accept that a replay older than the window duplicates.
  • Kafka transactions only cover Kafka. An idempotent producer plus isolation.level=read_committed gives exactly-once from topic to topic; the sink write is still at-least-once unless the sink participates in the transaction, as Flink's two-phase-commit sink does with a transactional store.

Verify before shipping: replay a batch with the retry client's timestamp refreshed and require the sink row count to be unchanged.

Curated: · Written: · Reviewed:

Q&A-87You accepted at-least-once. What must the fact table still guarantee?

The 90-second answer. At-least-once is a property of the transport, not of the table. The fact table's contract is: exactly one row per business event, however many times that event was delivered. That means the load must be idempotent: a second arrival of the same event key updates or no-ops, it never inserts. Consumers then get to assume the grain is unique, and any consumer that adds DISTINCT is paying for a guarantee it should already have.

Why duplicates arrive. Three common causes: the producer times out waiting for an ack and retries, but the first write landed; a Kafka consumer commits offsets after processing, so a crash between process and commit replays the batch; a batch job reruns after a partial failure and re-reads the same source rows. In all three the same event_id shows up N times in the landing table. Nothing upstream fixes this for you.

How the table guarantees it. Merge on the event key, never append.

-- Snowflake syntax; QUALIFY also exists in BigQuery and DuckDB
MERGE INTO fact_orders AS t
USING (
  SELECT * FROM stg_orders
  QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1
) AS s
ON t.event_id = s.event_id
WHEN MATCHED THEN UPDATE SET amount = s.amount, updated_at = s.ingested_at
WHEN NOT MATCHED THEN INSERT (event_id, order_id, customer_id, amount, occurred_at)
VALUES (s.event_id, s.order_id, s.customer_id, s.amount, s.occurred_at);

Two preconditions. The event key must be in the payload and stable across retries, if the producer mints a fresh UUID per attempt, no downstream merge can help and you fix the producer. And the key must be deterministic; a key that includes ingested_at never matches and always inserts.

The backstop. Snowflake and BigQuery don't enforce PRIMARY KEY, so the merge is the guarantee, not the DDL. Add a dbt unique test on the grain and run it over the whole table, not just today's partition: a late duplicate arriving after the merge window closed is exactly what a partition-scoped test misses.

Metric. Duplicate-arrival rate = (rows landed − distinct event keys) / distinct event keys, per load window, computed on staging before the merge. If it sits at 0 while retry counts are nonzero, the metric is reading the wrong table.

Alternatives, and when they win.

Where you dedupeCostWhen it's right
MERGE into the factReads and rewrites target partitions; expensive on a 1 TB factDefault. Merge only the affected occurred_at partitions to bound it
Streaming keyed state (Flink, Kafka Streams)State store size, bounded by retentionHigh-volume streams where duplicates should never land; a retry after state expiry still lands
Dedupe view over append-only rawEvery query pays the window functionYou need raw history and query volume is low
Consumer-side DISTINCTEvery consumer pays, and one that forgets is silently wrongAd-hoc queries only

Failure mode worth naming. Dedupe left to consumers. Worked example: 18.5M events, 2.4M duplicate arrivals. 13.0% inflation. Dashboard A used DISTINCT and showed the baseline; dashboard B didn't and showed +2.4M users. The certified fact, merged on event_id, showed one number. The bug wasn't the duplicate. It was that the grain guarantee lived in a query instead of in the table.

Curated: · Written: · Reviewed:

Q&A-42The bus promises exactly-once. The warehouse load retries. What did you actually get?

What you got: at-least-once end to end, and duplicate rows in the warehouse. The bus's exactly-once is real but scoped to the bus. It covers producer → broker → consumer and the offset commit; it stops at the socket where your loader writes to the warehouse.

Why the guarantee stops there. Kafka's exactly-once is two mechanisms. Producer idempotence (enable.idempotence=true, the default since Kafka 3.0) tags each record with a producer ID and a per-partition sequence number, so the broker dedups a retried produce request. Transactions (transactional.id, consumers on isolation.level=read_committed) make writes across partitions plus the consumer offset commit atomic. Both live inside Kafka. A JDBC insert into Snowflake, BigQuery or Postgres is not a participant in that transaction, so there is no point at which the broker can roll back your warehouse write. The loader's own retry loop, the thing that fires when the warehouse connection drops, sits outside the guarantee entirely.

The two ways out. Either bring the sink into the transaction, or make the write idempotent.

  • Transactional sink. Flink's Kafka sink uses two-phase commit: records are staged in the transaction and committed on checkpoint, so the offset and the sink write move together. Kafka Connect has exactly-once support for source connectors (KIP-618, Kafka 3.3); sink connectors are at-least-once unless the sink itself supports transactions. A warehouse that speaks XA or exposes a native transaction API can be a 2PC participant; most columnar warehouses cannot, and the ones that can pay for it in commit latency.
  • Idempotent sink. Give every event a deterministic key derived from the source, not a UUID minted at load time, and write with insert-if-absent or an upsert. Flink's JDBC connector exposes this as sink.delivery-guarantee=exactly-once, which requires a primary key on the target table and compiles to an upsert.
MERGE INTO fact_events AS t
USING (SELECT ? AS event_id, ? AS user_id, ? AS amount) AS s
ON t.event_id = s.event_id
WHEN NOT MATCHED THEN INSERT (event_id, user_id, amount)
VALUES (s.event_id, s.user_id, s.amount);

WHEN NOT MATCHED only: no WHEN MATCHED THEN UPDATE. An update makes the write last-write-wins, which is correct for a state table and wrong for additive facts: a retry carrying a corrected amount would silently rewrite history instead of being ignored.

Failure modes, and how you see them.

  • Offset committed before the sink flush. A crash between the two loses the batch. Silent: row counts come in low, no error anywhere. That is at-most-once, and it is worse than duplicates.
  • Offset committed after the sink flush. A crash between the two replays the batch. This is the common case, and the one you are living with.
  • MERGE without a unique index on the key. Two concurrent retries both evaluate NOT MATCHED and both insert. Dedup looks correct in testing and fails under exactly the concurrency that caused the retry.
  • Non-deterministic key. event_id = uuid() or a load timestamp in the key means every retry is a new event, and duplicates survive every dedup you build downstream.
  • Detection. SELECT grain_key, COUNT(*) c FROM fact_events GROUP BY grain_key HAVING c > 1 LIMIT 10; If that returns rows, you are at-least-once. Run it after every incident, not just this one.

The arithmetic. A 3-hour incident, loader retried 14 times, each attempt inserting the full 800,000-row batch: 14 × 800,000 = 11,200,000 rows landed. The batch covered 640,000 distinct users, and COUNT(DISTINCT user_id) still reads 640,000, which is why the dashboard looked fine while the revenue SUM was 14× high.

SinkAttemptsRows landedDistinct usersSUM(amount)
append1411,200,000640,00014× true
MERGE on event_id14800,000640,0001× true

What I'd say in the room. Exactly-once is a property of a pipeline boundary you can name, not an adjective the bus hands down. Ask where the transaction ends; if the answer is "at the broker," you have at-least-once and a dedup key to design.

Curated: · Written: · Reviewed:

Q&A-43How do you reload 2 March without doubling 2 March?

What I'd say first

Two things decide the approach: what the grain is (the key that makes a row unique), and whether the 2 March source is a complete snapshot of that day or a delta of changed rows.

  • Complete snapshot → replace the partition. INSERT OVERWRITE scoped to event_date = '2026-03-02' removes that partition's rows and writes the new ones in the same commit. A second run leaves the same row set.
  • Delta of changes → MERGE on the grain keys, with the source deduplicated first. A second run converges, because it matches rows the first run inserted.

Append is the only option that can double, so it's the one to remove. "Reload" means the partition's contents are a function of the source, not of how many times the job ran.

Why append doubles the sum but not the average

Exact duplication adds a copy of every row. COUNT(*) doubles, SUM(v) doubles, AVG(v) does not move: the mean of a multiset equals the mean of its duplicate. So the average is a useless alarm and the count is a good one. COUNT(DISTINCT grain_key) stays flat under append, which is the cleanest tell that you appended instead of merged.

Trace on a three-row partition:

operationrowsSUM(v)AVG(v)COUNT(DISTINCT k)
before360203
append rerun6120203
MERGE on k360203
INSERT OVERWRITE partition360203

At 24M rows the append run leaves 48M rows and a doubled revenue figure that no dashboard flags, because the average still looks right.

The write

Spark 3.5 against a Hive-style or Iceberg table:

-- static partition spec: only 2026-03-02 is touched
INSERT OVERWRITE TABLE orders PARTITION (event_date = DATE '2026-03-02')
SELECT order_id, line_id, order_value, event_date
FROM   staging_orders_2026_03_02;

Naming the partition is the safe form. If you omit it and let the SELECT produce dates, spark.sql.sources.partitionOverwriteMode decides: STATIC (the default) overwrites the whole table when no partition clause is given, DYNAMIC overwrites only the partitions present in the incoming rows. Set it deliberately rather than inheriting it.

For a delta source:

MERGE INTO orders t
USING (
  SELECT * FROM (
    SELECT s.*, ROW_NUMBER() OVER (
             PARTITION BY order_id, line_id
             ORDER BY updated_at DESC, ingest_seq DESC) rn
    FROM staging_orders_2026_03_02 s) x
  WHERE rn = 1
) s
ON  t.event_date = DATE '2026-03-02'
AND t.order_id = s.order_id AND t.line_id = s.line_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

The ROW_NUMBER is not optional. Delta rejects a MERGE where two source rows match one target row; Iceberg and Postgres behave differently but none gives you the result you want. The tie-break must be deterministic (updated_at, then an ingest sequence), or the surviving row changes between runs and the checksum drifts while the count stays stable.

On Postgres the same shape is DELETE FROM orders WHERE event_date = '2026-03-02'; INSERT ... in one transaction, or INSERT ... ON CONFLICT (order_id, line_id) DO UPDATE backed by a unique index on the grain.

Failure modes

  • Source holds duplicate grain keys. MERGE multiplies or errors. Detect: COUNT(*) - COUNT(DISTINCT (order_id, line_id)) on staging must be 0 before the merge runs.
  • Source extract is incomplete. Replace-partition silently deletes the missing rows: the failure is a drop, not a double. Detect: staging row count against the previous partition count, plus a source watermark check that the extract covers the whole day.
  • Late-arriving rows for 2 March after the reload. The next run must target the same partition again; if the scheduler appends, you are back to doubling. Detect: partition row count compared with the previous run.
  • Two writers on the same partition. Last commit wins and one day's work vanishes. Iceberg and Delta detect conflicting concurrent commits; a plain Hive table does not.

Verification

Run the 2 March load twice in staging and require identical results:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT (order_id, line_id)) AS keys,
       SUM(order_value) AS total,
       SUM(CAST(CRC32(CONCAT_WS('|', order_id, line_id,
           CAST(order_value AS STRING))) AS BIGINT)) AS fingerprint
FROM orders WHERE event_date = DATE '2026-03-02';

Same rows, keys, total and fingerprint on both runs, with keys = rows. A unique test over a 1,000-row sample passes on a doubled partition; run it over the full partition or not at all.

Curated: · Written: · Reviewed:

Q&A-88You need to correct 2 March. When is overwrite the right operator, and when does it delete the CDC you already applied?

Start with the assumption that decides it: is the input a complete snapshot of 2 March, or a delta? Overwrite is correct only for the first. A partition overwrite is a metadata swap: the engine replaces the partition's data files with the files the query produced and commits atomically. It never reads the existing partition. The output is exactly the input, so anything in the partition that is not in the input is deleted, not merged. MERGE is the operator for a delta: it reads the target partition, matches on key, updates matched rows, inserts unmatched source rows, and leaves target rows with no source match untouched.

That single difference, read the target or not, is the whole decision.

When overwrite is right. You have a full extract for the day: a nightly snapshot, a re-run of the source's own full export, a backfill from a system that can produce the complete partition. Overwrite is then cheaper than MERGE (no target read, no shuffle on the key), idempotent (re-running produces the same partition), and it handles deletes for free: a key that disappeared upstream is simply absent from the new file set. That last property is why teams reach for it: MERGE cannot delete a row unless you write an explicit delete branch.

When it deletes your CDC. The input is a snapshot whose cutoff is earlier than the partition's current state, and the partition already holds CDC applied after that cutoff. Overwrite replaces the file set with the snapshot, and the CDC is gone.

Worked example, figures illustrative:

  • The 2 March partition takes a 14:00 full snapshot, then CDC from 14:00 to 20:00. 140,000 order updates applied by MERGE.
  • At 20:00 a job overwrites the partition from the same 14:00 snapshot.
  • Result: the 140,000 CDC rows are deleted. The partition's max(updated_at) drops from 20:00 back to 14:00.
OperatorInputCDC after 14:00Rows lost
INSERT OVERWRITE14:00 snapshotdeleted140,000
MERGE14:00 snapshot + CDCkept0

Detecting it before it ships. Compare the partition's watermark to the input's: max(updated_at) in the partition must not exceed max(updated_at) in the overwrite input. If it does, the input is stale and the job must fail, not commit. Row-count reconciliation per partition against the source catches the same thing after the fact. Both are cheap; neither is a load test.

-- Guard: refuse the overwrite if the input is behind the partition.
-- Spark 3.5 SQL
SELECT
  (SELECT max(updated_at) FROM orders WHERE dt = '2024-03-02') AS partition_wm,
  (SELECT max(updated_at) FROM snapshot_2024_03_02)          AS input_wm;
-- input_wm < partition_wm  =>  do not overwrite

Mixed-mode partitions are the real hazard. If one writer MERGEs CDC into 2 March and another overwrites it from a snapshot, the outcome depends on commit order, and the overwrite wins whenever it lands last. Pick one writer per partition, or make the overwrite input include the CDC. Locking the partition during the overwrite window is the cheap version of that.

When not to use MERGE. If the source cannot produce a complete partition and you need deletes, MERGE alone will not remove rows; you need a delete branch or a separate tombstone pass. And if the input is complete, MERGE is strictly more expensive for the same result.

Curated: · Written: · Reviewed:

Q&A-89A backfill rewrites 14 months. What must not happen to today's incremental load?

First 60–120 seconds

Clarify the range before anything else: does the backfill's upper bound include today, and is today's partition still open for incremental writes? The invariant I'd state up front is that today's partition has exactly one writer at a time. A 14-month backfill is a bounded rewrite of closed history; the incremental load is an append or upsert into an open partition. If the backfill's range reaches into today, two writers touch the same rows with no shared transaction, and the result is decided by interleaving, not by intent.

Assumptions worth stating: the backfill is a batch job with its own commit cadence, the incremental commits every few minutes, both write the same table, and the table has a primary key but no cross-job lock.

Why it breaks

A backfill that recomputes a partition usually writes with overwrite semantics: it assumes it owns the whole range. The incremental writes with append or upsert semantics and assumes it owns the tail. Neither knows about the other. Overwrite deletes rows the incremental just committed; append duplicates rows the backfill already wrote. There is no ordering that saves you, because the two jobs commit independently.

The options, and when each is right

OptionWhat it guaranteesCostFails when
Lock the backfilled range, exclude todayone writer on the open partitionbackfill can't repair today; needs a lock every writer honoursad-hoc SQL or a replay bypasses the lock
Idempotent MERGE on PK with a monotonic versionconverges regardless of commit orderneeds a version/updated_at column, and delete handlingsource has no monotonic column, so a late older row wins
Copy-and-swap (rebuild range, atomic partition replace)readers see old or new, never partial~2x storage for the rebuilt range, plus a swap windowreaders hold open handles, or the engine has no atomic replace
Watermark gating (freeze a high-watermark, backfill below it, replay the gap)backfill and incremental never overlap in rangeneeds a frozen watermark and a gap replaythe watermark moves while the backfill runs

Criteria for choosing: if the engine supports atomic partition replace, copy-and-swap is strongest because it removes the race instead of serialising it. If deletes are in scope, MERGE alone won't remove rows that vanished from the source: you need the swap or explicit tombstones. If you cannot pause the incremental, watermark gating plus an idempotent MERGE is the fallback, and it is the weakest of the four because correctness now depends on the watermark never moving.

Worked example (hypothetical figures)

Backfill range computed as event_date >= current_date - 425, upper bound open, so it includes today. Incremental commits every 5 minutes. At 02:00 the backfill reads today's rows as of 02:00. Say 1.2M, and writes them back at 02:40. Between those times the incremental appended 180k rows. If the backfill writes with partition overwrite, those 180k rows are gone; if it appends, they are duplicated. Detection is cheap: compare count(*) against count(distinct pk) per partition per hour. A gap between them is duplication; a fall in row count on an open partition is an overwrite.

Failure modes to name

Backfill bound computed from current_date at job start rather than frozen at submission: the range grows while the job runs. MERGE keyed on PK without a monotonic version: a late-arriving older row overwrites a newer one, and the table looks correct until someone diffs against the source. Copy-and-swap without reader coordination: consumers querying mid-swap see an empty or missing partition. Watermark gating with a watermark that advances: a gap opens between the backfill's ceiling and the incremental's floor, and nothing detects it because both jobs report success.

How I'd settle it

Run a backfill and an incremental concurrently against a scratch partition and assert one of two things: the lock rejects the second writer, or the MERGE converges to the same final state under both commit orders. Until one of those holds, the answer is a hope, not a guarantee.

Curated: · Written: · Reviewed:

Q&A-74CDC MERGE runs all day. Do you pick merge-on-read or copy-on-write, and what does a dashboard pay for?

The 90-second answer. Ask two things before choosing: how many MERGE commits per hour, and how many queries read the table. Continuous CDC with a small read fan-out → merge-on-read, with a compaction job you actually schedule and alert on. Read-heavy table with a bounded write rate → copy-on-write. A dashboard on MOR pays read amplification: every query scans the base data files plus the delete files accumulated since the last compaction, and that second term grows with every MERGE.

What each mode actually does. COW applies the MERGE by rewriting every data file containing a matched row: write cost scales with files touched, read cost is a plain scan. MOR writes new rows as data files and matched rows as delete files; the reader applies deletes at scan time. In Iceberg v2 those are position deletes (row pointers, cheap to apply) or equality deletes (key match, which can invalidate rows across many data files: much more expensive). Delta Lake does the same with deletion vectors; Hudi names the two table types COW and MOR and requires compaction to fold deltas into new base files.

What the dashboard pays. Not rows: files. Cost per query ≈ (data files in the scan) + (delete files whose partition overlaps the scan). Position deletes are applied by reading the delete file and skipping the referenced rows; equality deletes force a join against the data. The bill is driven by delete-file count and by how many of them partition pruning can skip.

Worked example (hypothetical numbers, one table). 200 GB table, ~2,000 data files (~100 MB each), CDC MERGE every 5 minutes (288 commits/day), each MERGE touching ~10 data files and writing ~10 delete files, dashboard pack of 12 queries over the last 7 days.

COWMOR + hourly compactionMOR, no compaction for 36 h
Write per MERGErewrite ~10 files (~1 GB)~10 small data files + ~10 delete filessame
Rewritten per day~288 GB~0~0
Delete files a query applies0≤120 (one hour's worth)~4,320
Compaction jobnonehourlynone

COW rewrites more than the table's own size every day here. The middle column is the one to run. The right column is the failure: the dashboard's cost is set by a background job nobody owns.

Failure modes and how you see them.

  • MOR with no compaction: delete-file count grows linearly with commit rate, and query planning time and bytes read grow with it. Track delete files per partition and the age of the oldest uncompacted delete file: alert on age, not count, because count depends on table size.
  • Equality deletes: one delete file can touch every data file holding that key. If your MERGE emits equality deletes, compaction is not optional.
  • COW under high-frequency CDC: write amplification and commit contention. Two MERGEs on the same partition serialize or retry; long commits hold snapshots and block expiry. Watch commit duration p99 and retry rate.
  • Compaction that can't keep up: if a compaction run takes longer than the interval between MERGEs, the backlog grows without bound. Size the job against commit rate, not table size.

Decision rule. MOR when commit rate × files-per-commit is high and you can hold a compaction SLO: compaction lag under one dashboard refresh interval. COW when reads dominate, the write rate is low, or the table is small enough that rewriting files is cheap. If you can't staff the compaction job, COW is the honest choice: you pay at write, but dashboard latency stays predictable.

Measure it before you argue. Iceberg's files metadata table has a content column (0 = data, 1 = position deletes, 2 = equality deletes), so you can count delete files per partition directly:

SELECT partition, count(*) AS delete_files
FROM prod.orders.files
WHERE content IN (1, 2)
GROUP BY partition
ORDER BY delete_files DESC;

Run that after a day of CDC. If the top partition shows hundreds of delete files, the dashboard is paying for them.

Curated: · Written: · Reviewed:

Q&A-35Who owns a warehouse table that 9 teams write and 40 teams read?

One owning group owns it. The other eight teams write to it as producers under that owner's contract: they get write access, not a veto over the schema. Ownership means the ability to reject a change.

Assumptions worth stating up front: the table is a shared dataset with a stable grain, the nine writers are internal teams, and "own" means accountable for schema, freshness and erasure, not merely listed in a catalog field. If the table is really nine independent datasets that happen to share a name, the fix is to split it, not to appoint an owner.

What the owner actually holds:

  • The catalog record names one group, not a distribution list.
  • Schema changes require that group's approval; CI runs their contract tests against every producer's write path.
  • Freshness and quality alerts page the owner, not the writers.
  • Erasure and retention requests land on the owner, who coordinates with producers.

Why not shared write: with nine equal writers, any one can change the grain or a column type and the other eight find out from a broken job. Nobody has standing to reject a change, because rejecting it means blocking a peer's roadmap with no authority behind the refusal. Readers don't become owners by depending on the table: 40 read teams add pressure, not accountability.

ModelWho can reject a changeFits whenBreaks when
Single owner + contracted producersthe ownershared grain, many readersowner has no review capacity
Schema councilquorumfew writers, rare changeswriters past ~5, changes frequent
Producer-owned viewseach producergrains genuinely differ40 readers each need the same join

Failure modes and how you'd see them:

  • Owner exists on paper, nobody reviews: schema PRs merge with zero approvals. Detect by counting approvals per schema change, not by reading the catalog.
  • Owner is a team that no longer exists: pages bounce. Detect by alert acknowledgement rate.
  • Producers bypass the contract by writing directly: detect by diffing write grants against the producer list.

Worked example (hypothetical, to make the numbers concrete). A core.events table has nine writers and no catalog owner. Team 7 changes a column from string to a struct. Eleven downstream jobs break. The page routes to an empty on-call rotation, so the first human to notice is a consumer. Rollback takes 16 hours, and the table holds 2.1 billion rows written under two incompatible types, so the backfill is a full rewrite. The same table with one owner and eight contracted producers: the type change fails the owner's contract test in CI, before merge, and rollback time is zero because nothing shipped.

How I'd verify ownership is real rather than nominal: open a PR that makes a breaking schema change and confirm the owning group's contract test fails the build. If it merges, the owner is decorative.

Curated: · Written: · Reviewed:

Q&A-56The catalog has descriptions on 90 percent of tables. Why is that not a contract?

The judgement in catalog as contract is which identifier and which time axis the fact is keyed on.

A contract is enforceable: schema, grain keys, compatibility, and a failing load. A description is prose. Coverage percentages measure documentation, not whether an incompatible write is refused.

Concretely, bind catalog entries to registry subjects and warehouse assertions, and fail a publish when the catalog text and the tested schema disagree.

The reason for that specificity is a failure I have seen: Descriptions said grain was customer_id. The table allowed duplicates. 6.1 percent of rows shared a customer_id. Every tool that trusted the catalog under-counted those people as 1.

Catalog text versus unique keys.

Catalog grainDuplicate customer_id rowsConsumers that trusted the text
customer_id6.1 percentall 9 certified dashboards
tested unique (customer_id, as_of_date)09

I would not consider it settled without evidence: change the tested unique keys without changing the catalog text and require the publish to fail.

Prose coverage is not enforcement.

Curated: · Written: · Reviewed:

Q&A-78Domains classify their own columns. What must still be central, or you do not have governance?

The 90-second answer. Domains can own the labelling, which column is what. They cannot own the vocabulary, the binding from class to control, or the audit. If a domain can mint a class name and attach it to a policy, you have N independent privacy programs sharing a database, not governance.

Four things stay central:

  1. The class vocabulary: a closed, versioned set of class names with written definitions, each mapped to a canonical sensitivity tier. Domains propose additions; they do not mint them.
  2. The class → control binding: what pii.email does at query time (mask, row-filter, deny) is defined once and applied by the platform. A domain that can bind its own class to "no control" has no governance.
  3. The principal model: the roles and groups the policies reference. If each domain has its own role namespace, the same class resolves to different people.
  4. The audit: one log of who classified what and who read what, so "who can read salary" is answerable across domains.

Federated: the labelling decision, the domain's review of its own labels, and any stricter controls layered on top.

ConcernCentralFederated
Class vocabularyclosed, versioned set + definitionsdomains propose new classes
Class → controlplatform binds class to mask/filter/denydomain may add stricter controls
Principalsone role namespace policies referencedomain role membership
Labelling—which column gets which class
Auditone log of class + accessdomain review of its own labels

The failure this prevents is label collision. In one incident, HR defined confidential as salary; the logs team defined confidential as anything in a log line. A role scoped to logs was granted confidential. The grant matched on the class name, and the name was not namespaced, so it also matched HR's columns. 2,200 salary columns became readable by a role meant to see request logs.

Trace:

  1. HR labels hr.employees.salary → class confidential (local meaning: salary).
  2. Logs labels logs.request.body → class confidential (local meaning: any log line).
  3. Grant: role logs_reader may read columns of class confidential.
  4. logs_reader runs SELECT salary FROM hr.employees.
  5. The policy resolves the class name confidential → match → rows returned.
  6. The audit records a legitimate class match; nothing flags it.

The fix is namespaced class names (hr.confidential, logs.confidential) plus a central registry that requires each to map to a canonical tier before enforcement attaches. Grants then reference the tier or the namespaced class, never a bare name. The gate is what makes it hold:

# central registry gate (Python 3); a domain cannot attach an unregistered class
TIERS = {"public", "internal", "confidential", "restricted"}

def attach(column, class_name, tier):
    if class_name not in REGISTRY:        # REGISTRY is central and versioned
        raise UnmappedClass(class_name)   # fail closed, not open
    if tier not in TIERS:
        raise UnknownTier(tier)
    REGISTRY[class_name].bind(column, tier)

Detection and trade-offs. A nightly job joins the registry to the policy bindings and alerts on two things: classes with zero bindings (a class that enforces nothing is fail-open), and grants that reference a bare class name defined in more than one domain. The cost of the central registry is a bottleneck: every new class needs a review. Mitigate with a proposal workflow and a default-deny for unmapped classes, so the slow path is safe. The alternative, one central team labelling every column, does not scale and does not know the data; pure federation is the incident above. The workable middle is a central vocabulary with federated labelling.

Curated: · Written: · Reviewed:

Q&A-24The catalog draws a lineage graph. Why is that not proof that a column still comes from the system of record?

The 90-second answer

A lineage graph is metadata about jobs: which job declared a read of crm.customer and a write to warehouse.customer. It is not evidence about the bytes currently in that column. Three gaps separate the two, and any one of them breaks the claim.

  1. Coverage. Lineage only knows about jobs that emit events or whose SQL was parsed. A notebook, a manual INSERT, a vendor load, a restore from backup: none of them appear. The graph is unchanged and wrong.
  2. Resolution. Column-level lineage is usually inferred by parsing SQL. SELECT *, SQL built at runtime, UDFs, temp tables and dialects the parser doesn't handle produce a missing or wrong column facet. A missing facet is not "no dependency"; it is "unknown".
  3. Time. The graph records the last successful parse, not the last write. A backfill or a manual update after that parse moves the data without moving the graph.

So the graph answers "is there a plausible path?" It does not answer "does this column's value still equal the system of record's value?" Only reconciliation does.

What actually proves it

Reconciliation keyed on the join key, run against the system of record. Figures below are hypothetical:

SELECT count(*) AS rows_total,
       count(c.crm_id) AS rows_with_id,
       count(*) FILTER (WHERE c.crm_id IS NULL) AS rows_missing_id
FROM warehouse.customer c;
-- rows_total 4,120,000 | rows_with_id 3,740,000 | rows_missing_id 380,000

Then a value check against the source for a sample:

SELECT count(*)
FROM warehouse.customer c
JOIN crm.customer s ON s.id = c.crm_id
WHERE c.email IS DISTINCT FROM s.email;

Zero on both, over a sample, and the column is still sourced from the system of record. Lineage tells you where to look; the check tells you whether it is true.

QuestionLineage graphReconciliation
Does a path exist from crm.customer?yes, if the job was parsed—
Does every row carry a crm_id?noyes
Are the values current as of this run?noyes

Where lineage still earns its place

Use it as a coverage gate, not as proof: fail a release when a job that writes a system-of-record-derived column has no lineage event for it. That catches the bypassing notebook at deploy time rather than weeks later. The trade-off is cost: row-level provenance for every column is expensive, so you pick the reconciliation by blast radius. A customer id column gets a full check on every load; a marketing tag gets a weekly sample.

Failure modes to name

  • Silent bypass: a job outside the scheduler writes the table; the graph does not move.
  • Parser gap: SELECT * or runtime SQL; the column facet is missing and read as "no dependency".
  • Stale graph: a backfill lands after the last parse.
  • False confidence: the graph is green and the data is wrong. The worst case, because nobody looks.

A picture of jobs is not an event from a job, and neither is proof about the values.

Curated: · Written: · Reviewed:

Q&A-80OpenLineage shows jobs. A steward asks which table feeds a column. Why can a job graph still be the wrong artefact?

What a strong candidate says first

The job graph answers "what ran, and which datasets did it touch". The steward asked "which field produced this field". Those are different keys, so the job graph is not a worse version of the answer: it is a different artefact.

OpenLineage's primary event is a RunEvent identified by job namespace, job name and run id; its inputs and outputs are datasets. That yields a bipartite job↔dataset graph. Column-to-column edges exist only if the producer emits the optional columnLineage facet. When it doesn't, no query over the job graph will name the source column, because the edge was never recorded. Check whether the facet is present before promising the steward anything.

What each artefact is keyed on

QuestionArtefactKey
Which jobs read or write this table?job lineagejob namespace + name, run id
Which datasets feed this dataset?dataset lineagedataset namespace + name
Which field feeds this field, and how?column lineage (columnLineage facet)dataset + field, plus transformation type

The spec distinguishes DIRECT from INDIRECT transformations, which matters below.

Worked example (hypothetical)

Model analytics.orders_enriched reads raw.orders and raw.fx_rates and writes order_id, revenue_usd. Originally revenue_usd = amount * fx_rate. A refactor replaces that with revenue_usd = 0.

BeforeAfter
job lineagereads raw.orders, raw.fx_rates; writes analytics.orders_enrichedidentical
column lineagerevenue_usd ← raw.orders.amount, raw.fx_rates.raterevenue_usd has no input fields

The job graph cannot tell these two states apart. The column graph can, and that is the whole point.

Failure modes and how you detect them

  • Literal or constant output. The output field has no input field. Job lineage still shows the dependency. Detect with a CI assertion: every output field in the facet has at least one input field, or is explicitly marked as a literal.
  • Facet absent, read as "no dependency". Absent means unknown, not none. Track the fraction of runs emitting columnLineage and alert when it drops; a broken integration looks exactly like a clean pipeline otherwise.
  • Filter-only reads. A job reads a table only in a WHERE clause. The facet marks that edge INDIRECT; job lineage shows the same edge as a data dependency. Dropping the table breaks the job, but no column flows through it.
  • Renames. An output field is renamed and the facet is not regenerated, leaving stale edges. Compare the facet against the dataset schema facet.
  • Aggregations and joins. Many-to-one. The job graph has no way to say which input column fed the output.

Trade-offs

Column lineage is expensive and fragile: it needs SQL parsing or engine-level instrumentation, and it degrades on dynamic SQL, UDFs, stored procedures and templated queries. Not every integration emits the facet. So don't demand it everywhere: require it on certified or contracted datasets, where the steward's question is column-shaped. Job lineage remains the right artefact for scheduling, cost attribution and job-level impact analysis.

What to do

Require the columnLineage facet on certified products, assert in CI that every output field resolves to an input field or an explicit literal, and fail a steward query that can only name a job.

Curated: · Written: · Reviewed:

Q&A-81Column lineage covers 70 percent of certified columns. Is that a passing architecture?

Verdict first. 70% is not a passing architecture on its own, and it is not a failing one either: the number is meaningless until you know what the missing 30% is. The decision rule: it passes if every uncovered certified column is (a) non-sensitive, (b) not derived from a sensitive source, and (c) detectable at read time by something other than lineage. It fails if any uncovered column carries regulated data or feeds a downstream certified column, because then you have no path to mask, audit, or contract it.

Why the gap is dangerous: the mechanism. Column-level masking, access policy, and audit are usually driven by lineage metadata. The policy engine attaches a mask to curated.accounts.iban because it can see that column derives from raw.payments.iban. When the derivation is invisible to the parser, the engine sees a brand-new column with no upstream, so no policy attaches, and the audit log records the read of the source table but not the propagation into the new column. Schema enforcement checks types and nullability, not provenance, so a column can be added without a facet and nothing fails.

Three things produce invisible derivations:

  • literals and expressions the parser does not model ('GB' || sort_code || account_number)
  • UDFs: the parser cannot see inside the function body
  • notebook/DataFrame writes, where there is no SQL text to parse at all

Trace. Take this statement:

CREATE TABLE curated.accounts AS
SELECT
  a.account_id,
  'GB' || a.sort_code || a.account_number AS iban,
  mask_iban(a.account_number) AS iban_masked
FROM raw.accounts a;

A parser that resolves SELECT/JOIN/WHERE records curated.accounts.account_id -> raw.accounts.account_id. For iban it records nothing, because the value is built from a literal plus two columns through an expression it does not model. For iban_masked it records nothing, because mask_iban is opaque. Result: two of three output columns have no column lineage, and the table is still certified.

Arithmetic. If the certified set is 4,000 columns, 70% coverage leaves 1,200 uncovered. If 2% of those carry PII, a labelled hypothetical, that is 24 columns with no masking path and no audit trail. That is the number to put in front of a reviewer, not "70%".

Alternatives, and when blocking is too expensive.

  • Fail the build when a certified column ships without a facet. Strongest; correct when producers are internal and instrumented. Too expensive when the uncovered set is dominated by genuinely sourceless columns (constants, config) or when producers are external and you cannot gate their writes.
  • Tag at write time: require sensitivity and owner declared at creation. Cheaper, but drifts as people copy tables.
  • Sampling lineage: parse a sample of queries, or audit the query log for unlineaged columns. Catches drift without blocking, but is probabilistic and lags by a batch.
  • Accept residual risk with monitoring: allow the 30%, alert on reads of uncovered columns matching PII patterns. Cheapest, weakest: fine only if the uncovered set is provably non-sensitive.

What changes the decision. If the 30% is all non-sensitive and read-time detection covers it, 70% is passing. If any uncovered column is regulated or feeds a certified downstream column, it is not, and the fix is to gate at the consumption boundary (mask on read by pattern) until lineage catches up.

Curated: · Written: · Reviewed:

Q&A-58A metric layer defines revenue. The warehouse also has fct_revenue. Who is the authority when they disagree?

Neither is the authority by default. They are authorities over different things, and the disagreement is a bug in one of them.

  • The certified fact (fct_revenue) is the authority for the number. It is the artefact with tests, a freshness SLA, and lineage back to source events.
  • The metric layer is the authority for the definition: grain, filters, time semantics, currency. It is a projection over a fact, not a second fact.

So the rule I would state in the first minute: a metric that does not reproduce its fact's total is wrong, unless the difference is a named, documented exclusion. If the metric genuinely needs a different number (recognized versus booked revenue, excluding internal orders) that is a different metric and it gets a different name. Two things called revenue is the actual defect.

How I would make that enforceable:

  1. Bind each metric to exactly one fact grain and one filter set. revenue = sum of fct_revenue.amount over the certified filter. No joins to other grains inside the metric.
  2. Ship a reconciliation test in CI: metric output versus fact total on a fixed window, failing on any delta not in the exclusion list.
  3. One owner for the definition, with the exclusion list versioned alongside it.
-- runs nightly; fails the build on any unexplained delta
select m.metric_value - f.expected as delta
from (select sum(amount) as metric_value
      from metric_revenue
      where ds between '2024-01-01' and '2024-01-31') m
cross join (select sum(amount) as expected
            from fct_revenue
            where ds between '2024-01-01' and '2024-01-31'
              and not is_internal) f
-- expected: 0

Failure modes I have actually hit:

  • Inner join to a second grain. The metric joined fct_order to fct_refund with an inner join to net out refunds. Orders with no refund row vanished.
  • Fan-out. Joining a second grain without pre-aggregating multiplies rows; revenue inflates instead of deflating.
  • Time semantics. The metric truncates in UTC, the fact in local time; month boundaries disagree by a day's revenue.
  • Restatement. Late refunds rewrite history in the fact; the metric's cached aggregate does not move.

Worked example, hypothetical figures: fct_order holds 1.2M orders and $96.0M revenue. 90,000 orders (7.5%) have no refund row; those orders are small and carry $2.4M, which is 2.5% of revenue. The inner join drops them, so the metric reports $93.6M: a 2.5% understatement that reads as noise and survived five weeks. The error is revenue-weighted: counting dropped orders (7.5%) overstates the damage threefold.

DefinitionRows droppedRevenue reportedDelta vs fact
inner join to refunds90,000 (7.5%)$93.6M-$2.4M (-2.5%)
fact grain, refunds pre-aggregated to order grain then left-joined0$96.0M$0

Detection: the reconciliation test above catches it on the first run. A row-count check on the metric's input would not, because the metric's row count is fine: the join is what is wrong.

So the fact is the authority for the number and the metric layer for the definition, and the reconciliation test is what makes that a fact rather than a convention.

Curated: · Written: · Reviewed:

Q&A-82A quality dashboard is green. Freshness SLO is met. Why might a consumer still be wrong?

Freshness measures arrival, not content. A green freshness SLO says rows landed inside the window; a green quality dashboard says the checks you wrote passed. Neither says the consumer's assumptions about the data still hold. Three things break while both stay green: schema drift, semantic drift, and silent partial or duplicate loads.

Schema drift. Upstream renames, retypes, or reorders a column. Consumers that read by position, or by name with a default, get the wrong value or NULL, and row-count and not-null-on-id checks don't notice.

Worked example: a consumer computes daily revenue.

-- Postgres 16
SELECT sum(amount_cents) AS revenue
FROM orders
WHERE order_date = CURRENT_DATE;

Upstream switched amount_cents (integer cents) to amount (numeric dollars) and left amount_cents in place, now writing NULL. Trace today's 1,000 orders: every amount_cents is NULL, so sum(amount_cents) returns NULL, not 0, NULL. Freshness: rows landed within 2 minutes, green. Dashboard: row_count > 0 passes, order_id IS NOT NULL passes, no duplicate order_id passes. Revenue is NULL and the tile is green.

Semantic drift. Same schema, different meaning. Units change, a timezone flips from local to UTC, an enum gains a value, or an upstream join changes cardinality from inner to left. Nothing in the schema moves, so schema checks pass; only a reconciliation against an independent source catches it.

Silent partial or duplicate loads. The job succeeds and writes a subset of partitions, or a retry double-writes. Row count stays above zero and freshness stays green. A duplicate-key check only helps if the key is still unique: a new idempotency key upstream makes duplicates look like new rows.

Late arrivals are the freshness-specific trap. If the SLO is max(event_time), a late event with an old timestamp never moves it, so the window looks complete while it isn't. If it's max(ingested_at), a trickle of junk rows keeps it green while the real partition is missing.

What to check instead. Four cheap additions, in order of return:

  • Column-set diff against yesterday (information_schema.columns): catches schema drift in one query.
  • Null-rate delta on columns consumers depend on: amount_cents going 0% to 100% is the signal above.
  • Row-count band vs the trailing 7-day median, per partition: catches partial loads.
  • A business reconciliation (revenue vs the billing system): the only thing that catches semantic drift.

Trade-offs. Freshness is one number, cheap, and catches stalls. Content checks are per-column, need baselines, and fire false positives on legitimate change. You can't check everything, so check what a consumer would act on. Freshness-only is fine for internal, non-decision data, or where a downstream reconciliation already exists. For anything feeding money, paging, or a customer-facing number, freshness is necessary and not sufficient.

Curated: · Written: · Reviewed:

Q&A-22Domains want to own their data products. The platform team wants one warehouse. What actually has to be federated, and what must stay shared?

Federate ownership, not storage. What has to be federated is who defines and ships a data product: its schema, freshness SLA, pipeline code, and the on-call rotation that answers when it breaks. What stays shared is the small set of things that only work if everyone agrees: the cross-domain identifier, security classification, and the conformed meaning of shared entities. Customer, account, order. One warehouse can host all of it: a single storage and query engine with domain-owned schemas. That is the right default for most organisations, and it is not the same thing as a central team owning every table.

Deciding criteria, in the order I would apply them:

  1. Does the join cross a domain boundary? If most queries stay inside one domain, federation buys nothing and costs coordination. If the product is the cross-domain join, the shared layer is the product.
  2. Is the cross-domain key stable and testable? A key with a coverage number and a reconciliation job can be federated. A key that only exists as a convention cannot.
  3. Do domains have genuinely incompatible constraints? Streaming fraud detection and nightly finance close have different latency and retention needs; two jurisdictions may have different data-residency rules. That is a real reason to federate storage, not just ownership.
  4. Can you staff the governance? Federated contracts need owners, a registry, and someone who rejects a product that fails its mapping test. Without that, federation is just decentralised drift.

When full federation is the wrong call. Under roughly five domains, one regulatory regime, and one analytics consumer, a central warehouse with domain-owned schemas and a shared identity service is cheaper and faster. Mesh pays off when a central team is fielding more schema-change requests than it can ship in a sprint: that bottleneck, not ideology, is the trigger.

When a central warehouse is the wrong call. When domains have incompatible latency or residency requirements, or when a single central team cannot be on call for every domain's freshness SLA.

The failure mode. Four domains each published a customer table. Matching across them took three weeks of email heuristics, and 7.2% of quarterly revenue, $18M, could not be attributed to a single person. The mesh did not fail at ownership. It failed at the one thing that has to stay shared.

OptionFederatedSharedWins when
Central warehouse, central teamnothingstorage, schema, identity, pipelinesfew domains, one regime, low schema churn
Central warehouse, domain-owned schemasschema, SLA, pipelinesstorage, query engine, identity, classificationmost orgs: one engine, many owners
Full meshschema, SLA, pipelines, storageidentity, classification, conformed dimensions via contractsmany domains, incompatible latency or residency, central team is the bottleneck

Detection. Enumerate every product that claims customer and require either a governed enterprise id or a tested mapping from its local key into the identity product. Tested means a coverage figure and a reconciliation job that fails loudly, not a wiki page. Until that list is clean, the mesh is not done.

Curated: · Written: · Reviewed:

Q&A-79The platform team offers a loader. Domains want custom Spark. What is the architectural split?

Assumptions: one lakehouse, Spark on a shared cluster, "certified" schemas are the tables other teams are allowed to depend on, and domains own their product logic end to end.

The split is at the certified-schema write, not at compute. Domains run whatever Spark they want inside their own zone. The platform owns the boundary that turns a domain's output into a certified table: identity, classification, schema registry, lineage, and the write into certified schemas. Custom Spark is fine; custom Spark that writes certified schemas directly is what the architecture forbids. The line sits there because that is where blast radius changes: a bad row in a domain's staging zone costs that domain, a bad row in a certified schema costs every consumer.

LayerOwnerConcretely
Spark jobs, DAGs, schedulingDomainAny version, any transform
Write to domain staging zoneDomainDomain role has write
Contract checks: uniqueness, PII mask, registry compat, lineagePlatformRuns at promotion
Write to certified schemaPlatformOnly the loader role

Two enforcement points make that real.

  1. IAM. Certified schemas grant write to the loader service account only. Domain roles get write to their staging prefix and read everywhere. A domain job cannot write certified even if it wants to: it writes staging, and the loader promotes.
  2. The loader as a library. The platform ships a thin write wrapper domains call at the end of their job:
# domain Spark job, checked against PySpark 3.5
from platform_loader import write_certified

write_certified(
    df=result,
    table="certified.billing.invoices",
    key=["invoice_id"],          # uniqueness
    pii=["customer_email"],      # mask before write
)
# emits lineage, validates registry compat, then writes as the loader role

Domains keep their own Spark; the last mile goes through the wrapper.

Alternatives, and when each wins.

  • Loader-as-service: domain writes staging, a platform job promotes. Strongest isolation, domains never hold certified credentials, at the cost of a scheduling hop and promotion lag. Pick this when the domain is untrusted or the table is high-blast-radius.
  • Loader-as-library (above): lower latency, domain keeps control of the job. Risk is version drift: a domain pins an old wrapper and skips a new check. Mitigate by failing promotion on an unknown wrapper version.
  • Federated compute: the domain owns its certified schema and its own enforcement. Legitimate when the domain is the system of record: billing owns certified.billing.*. The platform's job shrinks to registry and lineage.

Failure mode. The one that bites is a job with raw S3 credentials that never touches IAM or the wrapper. It skips uniqueness and PII masks and lands raw rows in a certified schema. Detect it by auditing certified tables for writes with no matching loader run in lineage, plus a PII scan and a uniqueness check on the certified table itself. The IAM deny is the prevention; the lineage audit is the detection.

Curated: · Written: · Reviewed:

Q&A-18The catalog marks 40 columns as PII. Why is that not yet a warehouse privacy architecture?

Core answer (first 60–120 seconds)

A tag is a fact about a column stored in the catalog. Privacy architecture is what the platform does when someone queries, copies or exports that column. Those are different layers, and only the second one stops a leak. Forty tags with SELECT * still granted to the analyst role means the catalog knows the column is PII and the query engine does not care.

Assumptions worth stating out loud: a warehouse with column-level security and dynamic masking available (Snowflake, BigQuery and Databricks Unity Catalog all ship both), and an analyst role that currently holds SELECT on the classified schemas.

Three questions decide whether you have architecture:

  1. Read: can a role project the raw column, or does it get a mask or a denial?
  2. Write and copy: does the classification survive a CTAS, a view, an unload to a stage, a BI extract?
  3. Time: how long does each copy live, and who deletes it?

If the answer to any of them is "the tag says so", you have a label, not a control.

Why the tag does not bind

The catalog is not in the query path. SELECT * expands to every column before any policy is consulted, so a tag with no attached policy changes nothing about what comes back. Derived objects are the second hole: CREATE TABLE scratch AS SELECT * FROM customers produces a new table whose columns carry no tag unless the platform propagates tags to derived columns, and propagation is not universal. Verify it per platform rather than assume it. Third, egress: a BI extract or a CSV unload leaves the warehouse's enforcement boundary entirely, and the tag cannot follow it out.

Two enforcement models, and the trade-off

ModelSELECT * behaviourCost you accept
Deny (column-level security)query fails with access deniedbreaks dashboards that use SELECT *; column lists need maintenance
Mask (dynamic masking / masking policy)query succeeds, raw value replacedanalysts get plausible-but-wrong values; joins and aggregates on masked columns can mislead

Deny is right for the small set of columns where any exposure is a breach: government ID, card PAN. Mask is right where the column is needed for joins or counts but not for reading. Most warehouses need both, chosen per PII class, not per table.

Worked example (hypothetical figures)

A retail warehouse tags 40 columns PII across 12 tables. The analyst role has SELECT on all 12.

StateAnalysts who can read raw emailAddresses exported to sandbox in 30 daysCatalog tag
tag only2,4001,100,000green
tag + masking policy bound to the tag + SELECT * revoked on classified schemas00green

The tag column is identical in both rows. That is the point.

Failure modes and how you detect them

  • Tag exists, no policy attached. Detect: join the tag inventory against the masking-policy inventory; every tagged column with no policy is a gap.
  • Policy on the table, but an older view selects the raw column and is granted to analysts. Detect: walk view definitions for references to tagged columns.
  • Service account with broad grants bypasses masks. Detect: audit log for classified-column reads by non-human principals.
  • Copy in a sandbox with no TTL. Detect: scan for tables whose column names match tagged ones but carry no tag.

The test that settles it

Run as the BI role, not as the owner:

SELECT email FROM analytics.customers LIMIT 1;  -- expect: denied, or a masked value
SELECT *     FROM analytics.customers LIMIT 1;  -- expect: denied under deny model; masked column under mask model

Then export the result to a sandbox and assert the raw value is absent. If either step returns raw PII, what you have is the tag.

Curated: · Written: · Reviewed:

Q&A-19Row filters hide other customers. Why can a support analyst still fail a privacy review?

The 90-second answer

Row filters answer which rows. They say nothing about which columns, whose data sits inside those rows, or whether the access serves the purpose. A support analyst scoped to their own customers still reads every column of those customers' records (national IDs, card tokens, health flags) and still reads the personal data of the individuals inside that customer's tenant. A privacy review fails on any of those three, and a row filter fixes none of them.

Why the two policies are orthogonal

Row-level security is a predicate evaluated per query against the session identity: tenant_id = current_tenant(). Column masking is a separate object attached to a column, evaluated when that column is projected. Snowflake ships them as row access policies and masking policies; BigQuery as row access policies and policy tags; Databricks Unity Catalog as row filters and column masks. They compose, but neither implies the other, and a table can carry one without the other.

Worked example (hypothetical figures)

Table customers(tenant_id, customer_name, tax_id, card_token). The support role has a row access policy tenant_id = current_tenant() and no masking policy.

SELECT tenant_id, customer_name, tax_id
FROM customers
WHERE tenant_id = 'T-4471';

Trace: the row access policy is evaluated first. T-4471 matches the session tenant, so the row survives. tax_id carries no masking policy, so the engine returns the stored value. The analyst pastes it into a ticket comment. Add a masking policy on tax_id returning '***' for the support role and the same query returns ***; the row filter is unchanged.

Policy on customersRows returnedtax_id returned
row access policy onlyown tenantraw value
row access policy + masking policyown tenant***

Failure modes and how to detect them

  • Column over-exposure. The row is legitimately the analyst's; the column is not. Detect by running SELECT * as each role and diffing the returned column set against the data product contract.
  • Data-subject scope. The row filter is keyed on tenant_id, but the tenant is a company and the rows describe its employees or end users: people who never had a relationship with your support org. A tenant-scoped filter is not a data-subject-scoped filter. Detect by asking, per table, who the person in the row is and whether they consented to support access.
  • Identity substitution. Row filters evaluate against the querying principal. A shared BI service account, a scheduled dbt job, or one support login used by the whole team collapses the filter to the union of all tenants. Detect by running the query as the service account, not as yourself.
  • Derived objects and copies. Clones, materialized views, exports and shares do not always carry the policy. Verify per platform rather than assuming inheritance.
  • Inference. A masked tax_id plus COUNT(DISTINCT) or a join to a table without the mask reconstructs it. Detect with a minimum-group-size threshold on any aggregate touching a masked column.
  • Purpose and retention. Even correctly scoped, copying an identifier into a ticket system with no lawful basis to store it fails the review. Detect by checking what the downstream system is allowed to retain.

What closes it

Not the catalog screenshot. As the support role, project the forbidden column on an allowed row and require a masked or denied result; then run the same query as the service account. The right row with the wrong column is still a leak.

Curated: · Written: · Reviewed:

Q&A-63The lake hashes emails with SHA-256. Why can that still be a personal-data copy, and when is a token vault the architecture?

Short answer. SHA-256 of an email is not anonymisation. The input space is small and enumerable, so the hash is reversible by guessing, and the result still singles out a person, which is what makes it personal data. A token vault is the architecture when the mapping has to live in one place you can delete, and when the token must not be derivable from the email at all.

Why the hash fails. SHA-256 is deterministic, unsalted and fast. Email addresses are not high-entropy: local parts come from a few thousand names and words, domains from a few thousand providers. A candidate list of 10^9 addresses is cheap to build. A single high-end GPU does roughly 2×10^10 SHA-256/s, so that list is exhausted in well under a second. A per-row salt stops a precomputed rainbow table but not a per-row guess, and a global salt is just a secret you now have to protect.

Worked example (hypothetical figures): take a lake column of unsalted SHA-256 email hashes and join it against a 180M-address breach corpus, hashing each corpus address and matching. In a run of that shape, 62% of the lake column resolves to a plaintext address within 4 hours on one machine. The catalog label "pseudonymised" is unchanged by that result: the label describes the transform, not the residual risk.

Why a token is different. A vault issues a random token (128 bits is plenty) and stores token → identifier in a table with access control, audit and a delete path. The token is not a function of the email, so a leaked lake column yields nothing without the vault. 128 bits is 3.4×10^38 values; there is no dictionary to run.

But the token is still personal data, for two reasons:

  1. The vault map exists. Anyone with vault read access resolves it, so the token is pseudonymous, not anonymous.
  2. Linkability. If the same token appears across datasets, or is stable per person globally, joins single the person out even without the vault. Per-system or per-purpose tokens break that; one global token recreates the identifier you were trying to remove.
MethodRecoverable from the lake column alone?Still personal data?
unsalted SHA-256yes: dictionary/rainbowyes
HMAC-SHA256, key in KMSno, without the keyyes: key holder resolves it; deterministic, so it still joins across datasets
vault token (128-bit)noyes: via the vault map or cross-dataset joins

When the vault is the architecture. Choose it when at least one of these holds:

  • You must honour erasure requests and prove deletion. Deleting the vault row is the deletion; you then delete or unlink downstream tokens.
  • You need unlinkability across systems, so each consumer gets a scoped token.
  • You need revocation or rotation without re-hashing the lake.
  • Analysts must work on the lake with no path back to identifiers.

Don't reach for it when you only need to stop casual reading and you control a KMS key: HMAC-SHA256 with the key in KMS is cheaper, adds no lookup on the hot path, and is still pseudonymisation. It is weaker on erasure (you must re-key or re-tokenise) and it is deterministic, so it still joins across datasets.

Failure modes to watch.

  • Vault as join hub: one global token per person. Detect by counting distinct tokens shared across datasets; high overlap means the token is an identifier.
  • Vault availability: ingestion blocks when the vault is down. Cache mappings with a bounded TTL and a documented staleness window.
  • Key rotation without re-tokenising: joins silently break, or two tokens appear for one person and the erasure path misses one.

How to check it. Run a dictionary/rainbow attack against the lake column and require zero matches before calling a token safe; for a hash, document the residual match rate instead of claiming zero. Zero matches is not zero re-identification: it means your dictionary was too small.

Curated: · Written: · Reviewed:

Q&A-64After erasure, can you keep "1,204 customers in France bought SKU 9"?

Short answer: usually yes, but only if the count is genuinely anonymous, and "1,204" is not automatically anonymous. The number names no one by itself; the risk is that it can be subtracted against another number to name someone.

Start with the semantic claim, not the table. GDPR Art. 17 erases personal data; Recital 26 puts anonymous data outside scope. So the question is whether "1,204 customers in France bought SKU 9" is personal data. A count of 1,204 is not, provided no one can single out, link, or infer an individual from it. That proviso is where the work is.

Why a count can still be personal data. Two attacks matter.

Differencing. Publish two overlapping aggregates that differ by one person and subtract. If you publish count(country, sku) and count(country, sku, loyalty_member), the difference is the non-member count. If that difference is 1, you have named them.

The erasure request is itself a probe. If the dashboard shows 1,204 on Monday and 1,203 on Tuesday, the day you honoured an erasure, anyone watching learns that the erased person bought SKU 9 in France. Recomputing and republishing after an erasure leaks membership.

Small cells. A cell of size 1 is a name with a count attached.

So can you keep it? Yes if the cell is large (a common floor is k=5 or k=20), the aggregate carries no key back to a person, and no second aggregate lets someone subtract down to a cell of 1. No if the cell is small, or if the only reason it is small is that you filtered to one person.

Trade-offs: suppress, noise, or delete.

ApproachRare cellsCostChoose when
Min cell size k, suppress belowLost: the long tail is exactly what merchandising wantsNone; no budget to trackPublished dashboards, low query volume, most cells large
Laplace/Gaussian noise (differential privacy)Kept, usablePrivacy budget ε; utility drops with each queryRepeated or adversarial queries, need a formal guarantee
Delete the aggregaten/aMetric goneAggregate only meaningful per-person, or cell of 1 with no signal

Deciding criteria: how many times the same aggregate can be queried (a one-off report versus a live dashboard), the cell-size distribution, whether raw data still exists (if raw is deleted, the aggregate is the last copy and residual risk is higher), and whether it is published externally or internal-only. Suppression is cheap and loses the long tail; noising keeps the long tail and costs budget and accuracy. If neither is defensible for a given cell, delete the row.

Worked example. Table purchases_by_day(country, sku, n).

Monday: (FR, SKU9) = 1,204. Tuesday, after erasing customer X: (FR, SKU9) = 1,203.

Delta = 1. Anyone with the dashboard now knows X bought SKU 9 in France. Fix: apply k=20 suppression to the published view so both days show the cell suppressed; or freeze the published number and do not recompute per erasure; or add noise with a fixed seed so the delta is not exactly 1.

Failure mode I have seen. A count by company_name table shipped with no floor. 1,140 companies had a count of 1. Erased employees were still named by the company cell for 9 months: a company cell of size 1 pointed at exactly one person. Detection: a nightly job that counts cells below k in every published aggregate and alerts, plus a differencing test that recomputes after each erasure and checks the delta is suppressed.

What I would say in the room: keep the 1,204 if the cell is large, the aggregate is keyless, and no second aggregate lets you subtract to a cell of 1. Otherwise suppress, noise, or delete, and never let the erasure itself change a published number by exactly one.

Curated: · Written: · Reviewed:

Q&A-65A replica in another country is "encrypted at rest." Why is that not a residency answer?

The 90-second answer

No. "Encrypted at rest" describes how bytes sit on the disk, not who can turn them back into data or where that happens. Residency and cross-border transfer rules ask two questions: where is the data stored, and who can reach the plaintext. A replica in Frankfurt whose key a US operator role can call is, functionally, EU data readable from the US. Encryption changed the threat model from "someone steals the disk" to "someone with the key reads it", and the second threat is the one residency law is about.

So encryption at rest is a control, not a location. It becomes part of a residency answer only when the key sits outside the third country and the importer has no technical path to plaintext. Some national and sectoral regimes require local storage outright, where encryption is not a factor at all.

Why the distinction holds

At-rest encryption protects against media compromise: a decommissioned disk, a stolen backup tape, a snapshot leaked from object storage. It does not constrain the operator who holds the key. If the KMS key lives in the same region and the same cloud account as the replica, then the replica region's operator, its support engineers, and anyone who can assume that role can decrypt. Legal compulsion aimed at that operator reaches the plaintext. That is the gap.

Three things decide whether the replica is actually out of scope:

  • Key custody. Where the key material lives and which principals can call Decrypt.
  • Identifiers. Whether the replica holds direct identifiers or only tokens and aggregates. Pseudonymised rows with the re-identification key back in the source region are a different transfer question from raw email addresses.
  • Transfer mechanism. The documented legal basis for anything that does cross.

Worked example (hypothetical figures)

ReplicaOperator can decryptEU emails in the US
At-rest encryption, key in the US accountyes3,600,000
Regional vault, tokens only, key held in the EUno0

Both rows are "encrypted at rest." Only the second is a residency answer.

Failure modes and how to detect them

  • Key in the replica's region or account. Read the KMS key policy and list its principals; then attempt Decrypt as the replica-region operator role and require AccessDenied.
  • Break-glass or admin role that bypasses the policy. Look for wildcard kms:* grants and emergency roles, and test them rather than trusting the policy document.
  • Plaintext leaking around the encryption. Index files, WAL segments, temp spill from a query engine, replication logs, and backups are frequently unencrypted or encrypted with a different key. Sample the replica's filesystem and object storage for identifier patterns.
  • Key rotation or DR copying the key into the third country. Audit key replication configuration and rotation jobs.
  • Provider-side support access. Provider access logs plus contractual terms; the operator's own IAM policy does not cover this.

What I would accept as evidence

A decrypt attempt from the replica region's operator identity that fails for EU identifiers, plus a named transfer record for anything that does cross. Without that, "encrypted at rest" is a statement about the disk, not about residency.

Curated: · Written: · Reviewed:

Q&A-37EU personal data must stay in EU regions. The warehouse has a global replica for analytics. What has to be true of that replica?

Residency follows the data, not the primary. A replica in another region is a separate processing operation and, if it holds personal data, a separate transfer. So the question is not "is the replica allowed" but "what must be true of its contents and of the pipeline that fills it" for it to be either non-personal or lawfully transferred.

Two things have to be true, and they are independent.

Contents. Either the replica holds no personal data under GDPR Art 4(1): no direct identifiers, no pseudonymous keys whose mapping the controller still holds (Art 4(5) is explicit that pseudonymised data is still personal data), and no quasi-identifier combination that singles out an individual, or it holds personal data and there is a transfer mechanism (an adequacy decision, or SCCs plus a transfer impact assessment) with the data still under equivalent protection.

Pipeline. The path that populates it must not itself move EU personal data through non-EU compute. A CDC stream landing in a US Kafka topic, a dbt model running in us-east-1 that joins the token back to the vault, a snapshot restored for debugging: each is a transfer even if the final table looks clean.

The decisive test is re-identification, not column names. Ask whether any row in the global replica can be linked to a natural person, alone or combined with anything else the recipient holds or can obtain. If yes, it is personal data and the transfer rules apply.

Aggregation is not a safe harbour. Hypothetical arithmetic: 40,000 EU users in one country, replicated at grain (postal_prefix_3, birth_year, plan). That is 200 × 80 × 5 = 80,000 cells for 40,000 users: mean 0.5 users per cell, so most cells hold 0 or 1 and a single row identifies a person. Coarsen birth_year to decade: 200 × 8 × 5 = 8,000 cells, mean 5. Coarsen postal to region: 20 × 8 × 5 = 800 cells, mean 50. Only the last is defensible, and even then you suppress cells below k and inspect the tail, not the mean.

Three credible designs, with what each requires:

DesignWhat has to be trueCost
Regional-only analytics tierNo global replica; per-region marts, cross-region queries federated with results merged in the EUDuplicated modelling, no global entity view, slower cross-region joins
Pseudonymised identifiers under SCCs + TIAMapping key stays in the EU, contract and TIA in place, recipient cannot re-identifyLegal overhead, fragile after Schrems II, key management
Non-personal aggregates onlyk-suppression, no raw grain, no join keyLoses user-level funnels, cohort retention, entity resolution

Tokenisation is not free: it breaks cross-region joins, needs a key service with its own residency, and turns every new analyst request into a "can we add this column" review. If the analytics genuinely need user-level funnels, the SCC route with the key held in the EU is often the honest answer rather than pretending aggregates suffice.

Failure modes and how to catch them:

  • Quasi-identifier re-identification: test by computing the maximum cell size and the count of singleton cells per published grain, not the mean.
  • The mapping key shipped alongside: audit the lineage of every column; a token is only non-identifying if the key is unreachable from the replica's region.
  • Metadata leakage: query logs, column statistics, error messages and backups are in scope; check the replica's own audit tables.
  • Silent re-introduction: a new model joins the token back; gate it with a CI check that fails if any model in the global project references the vault.

Verification: query the global replica for a known EU subject and require zero identifying attributes and no key that maps 1:1 to one, then repeat from the replica's region using only the replica's credentials.

Curated: · Written: · Reviewed:

Q&A-38Default retention is 400 days. Legal hold needs 7 years. How does the warehouse keep both without keeping everything forever?

Retention and legal hold are two mechanisms, and the mistake is to implement one as a switch on the other. Retention is a delete clock per data class: rows older than 400 days go, on schedule, by default. Legal hold is a set of keys exempted from that clock. Keep both by making the delete job subtract the hold set: the clock keeps running for everything else, and the held rows are the only ones that live to 7 years.

Assumptions worth stating up front: the warehouse holds the copy you are obligated to delete; holds are expressible as stable keys (enterprise id, matter id, case key); there is a hold registry with an owner and a review date; and the derived copies (backups, snapshots, search indexes) are enumerated and handled by the same key set.

Mechanism. A hold registry table keyed on the entity:

-- pseudocode, not a specific dialect
legal_hold(entity_type, entity_id, matter_id, placed_at, released_at, owner)

The retention job for each class computes candidates as rows past the clock, then deletes candidates minus held keys:

DELETE FROM events e
WHERE e.created_at < now() - interval '400 days'
  AND NOT EXISTS (
    SELECT 1 FROM legal_hold h
    WHERE h.entity_type = 'enterprise'
      AND h.entity_id   = e.enterprise_id
      AND h.released_at IS NULL
  );

The hold table is small, thousands of rows, so the anti-join is a lookup per candidate row, not a scan. The delete cost is unchanged; only the exempt set is smaller.

Trade-offs. A flag column on the fact table reads cheaply but every hold change rewrites a wide table and the flag leaks into every query. A registry table is a single source of truth but needs the join above. Partition exclusion is the cheapest delete, but only works when the hold grain matches the partition key, and a single held row in a tenant freezes that tenant's whole partition. Hold at the finest grain the obligation allows: matter or enterprise, not tenant.

Failure modes. The one I have seen: retention disabled estate-wide "for legal." Illustrative figures. 12 TB/day ingest, 400-day clock, so roughly 4.8 PB steady state, and when a hold for 2,400 of 6 million enterprise ids was issued, nobody could prove the other 99.96 percent should have been deleted three years earlier. The hold was fine; the deletion obligation was now unprovable. Detect it with a reconciliation that reports, per class, the fraction of rows past the clock still present without a hold key.

Two more. Hold key drift: holds keyed on an id that changes after a migration silently stop matching, so rows expire while the hold looks active. Reconcile the registry against the entity table and alert when a hold matches zero rows. Unreleased holds: holds with no review date become permanent and the estate drifts back to keep-everything. Report hold age and alert past the review date. And the copies outside the warehouse: the delete clears the table, but last quarter's snapshot and the search index still hold the rows. Run the same key set against each derived store.

PolicyRows past the 400-day clockHeld rowsProvable timely delete
keep everything "for legal"all retainedunknownno
keyed holddeleted except held2,400yes for the rest

I would not call it settled without evidence: run retention on a table with 10 held ids and 10,000 unheld, and require exactly the 10 to remain.

Curated: · Written: · Reviewed:

Q&A-77Warehouse rows are erased. The nightly backup still has them. What is the architecture?

The question underneath is: where do you enforce erasure so that a restore cannot resurrect a deleted subject? Erasure is a property of the whole copy graph, not of prod. So the architecture has two parts: a mechanism that makes the subject unrecoverable in every copy, and a gate that keeps any restored copy unqueryable until erasure has been replayed onto it.

Assumptions I'd state up front: nightly full snapshot to object storage, retention 30–90 days, the warehouse is the analyst query surface, access is role-based. If those differ, the decision below moves.

Direct answer. Compare the erasure SLA to backup retention. If the SLA is at least as long as retention, align retention to the SLA: cheapest, no new components, erasure completes when the last copy ages out. If the SLA is shorter than retention (the common case: 30-day SLA, 90-day backups), you need restore-and-re-erase. Crypto-shredding is a third option, but only when PII is confined to columns you can encrypt.

How each mechanism actually works.

Retention alignment. Set the object-storage lifecycle to expire snapshots at the SLA. The subject stays in old snapshots until they age out, which is legal only if the SLA permits it. Cost: your disaster-recovery window shrinks to the SLA. You cannot restore a 60-day-old state if retention is 30 days.

Crypto-shredding. Encrypt each subject's PII columns with a per-subject data key, wrapped by a KMS master key (envelope encryption). Erasure deletes the wrapped key. The snapshot still holds ciphertext, but it is undecryptable, so the copy is inert. Cost: a key store at subject scale, that store must itself be durable and backed up, and it only covers fields you encrypted. Derived tables, aggregates, and join keys leak unless you tokenize them too.

Restore-and-re-erase. Keep backups, but no restore is queryable until the erasure ledger is replayed onto it. The ledger is append-only: (subject_id, erased_at, scope). The restore job replays it, then the access gate opens. Cost: restore time grows with ledger size, and the ledger must outlive every backup it protects.

Why tombstoning fails. A delete marker in the live table never reaches the snapshot. Object-storage dumps are immutable Parquet: you cannot update a row in a file already written; you would rewrite the partition, and the prior version may still exist under versioning. So "we deleted prod" is not an answer while a 90-day dump exists.

Failure mode and detection. Prod erased in 7 days, dumps sat 90 days, a restore for a bug at day 40 reintroduced 12,400 erased subjects into an analyst-queryable sandbox. Detection is a reconciliation job that scans every restored copy for subject IDs in the ledger and blocks the access gate on any hit: before roles are granted, not after.

erasure request ──► ledger (subject_id, erased_at, scope)
prod delete ──────────────┐
                          ▼
nightly snapshot ──► object storage (retention lock)
                          │
restore job ──► replay ledger ──► access gate ──► analyst roles
OptionWorks whenMain cost
Retention alignmentSLA ≥ retentionDR window shrinks to SLA
Crypto-shreddingPII confined to encrypted columnsKey store at subject scale
Restore-and-re-eraseSLA < retentionLedger must outlive backups

I would not call it settled without evidence: erase a subject, restore from the oldest snapshot still in retention, and require the restored copy to apply the ledger before any analyst role can select.

Curated: · Written: · Reviewed:

Q&A-20A data-subject request arrives. The warehouse has 7 years of type-2 customer history. What has to happen that a DELETE FROM current will miss?

What a strong candidate says first

Clarify three things before touching anything: which identifier the request names (email, customer id, or both), which legal basis applies (erasure versus a retention obligation that overrides it), and which time axis the fact is keyed on. Then answer directly: DELETE FROM current removes one row. It misses every other copy of the identifying attributes: type-2 history versions, late-arriving repairs that re-insert the old value, downstream marts built from those, and time-travel snapshots whose data files still exist.

Why

A type-2 dimension stores the identifier once per version, so 7 years of history is N rows, not one. Snapshots are immutable file sets; expiring a snapshot in the catalog drops the pointer, not the underlying Parquet or ORC files. So AS OF <snapshot> can still resolve until the files are removed. Erasure therefore has to (1) rewrite or delete identifying attributes in current and history, (2) expire the snapshots that held them, (3) remove the now-unreferenced files, and (4) prove AS OF the pre-erasure snapshot id fails with snapshot expired rather than returning the email.

Alternatives and when not to use them

ApproachWhat it doesWhen it fitsCost / risk
Hard delete rowsremoves current and history rowsidentifier appears only in this table, no retention obligationbreaks SCD-2 joins and downstream foreign keys
Mask / pseudonymiseoverwrites identifier with a stable tokenanalytics must keep row shape and joins must survivetoken must be irreversible and not re-derivable from other columns
Tombstonemarks the row erased, keeps the keyaudit must show erasure happenedthe key itself may be personal data
Full snapshot expirydrops snapshot and filesthe snapshot contains only this subject's dataon a 7-year table a snapshot holds millions of unrelated rows

That last row is the real cost driver. If snapshots are shared, expiring one to erase one subject destroys everyone else's history. The usual fix is to partition snapshots by subject or by date so expiry is scoped, or to rewrite the snapshot's files rather than drop them.

Retention law can also override: tax, AML and employment records often carry a statutory retention that outranks erasure for the retained fields. The correct move is to erase what is not under retention and document the rest, not to delete everything.

Failure modes and detection

  • Catalog expiry without file removal: detect by scanning the storage path for the identifier after expiry, not by trusting the catalog.
  • A late-arriving repair re-inserts the old value: detect by re-running the erasure check after the next load.
  • A downstream mart rebuilt from a pre-erasure snapshot: detect by lineage: any mart whose source snapshot predates erasure.
  • A restored sandbox built from retained files: detect by checking restore sources against the erasure list.

Worked example (hypothetical)

Current row deleted, 11 type-2 versions remain, snapshot 2209 expired in the catalog but its files retained:

CopyAfter DELETE FROM currentAS OF 2209
current dimension0 rows—
type-2 history11 versionsuntil rewritten
snapshot expired, files retained1 email via file pathmust fail snapshot expired; if it returns the email, files were not removed

Verification

After erasure: query current and history for 0 identifying attributes, then run AS OF <pre-erasure snapshot id> and require the engine to fail with snapshot expired, not to return a masked row while the files still exist. That failure is the evidence; a passing query is not.

Curated: · Written: · Reviewed:

Q&A-21Iceberg time travel is the recovery story. How can it be the erasure failure?

Where candidates lose the interview on time travel versus GDPR is calling the lakehouse layout the model.

Time travel preserves snapshots so a bad load can be rolled back. Those snapshots are also personal data if they contain identifiers. Snapshot expiry removes metadata; it is not physical erase. Identifiers remain until files are rewritten without them, snapshots that pointed at the old files are expired, and the orphaned files are deleted.

Concretely, rewrite data files without the identifiers, expire snapshots that still reference the old files, remove those files, and set snapshot retention to the shorter of recovery need and erasure SLA.

The reason for that specificity is a failure I have seen: Snapshot retention was 90 days for recovery. Erasure SLA was 30 days. The team expired snapshot metadata at day 30 but did not rewrite or delete files. 14,800 erased subjects remained in orphan files for 60 extra days, which legal logged as 14,800 breaches.

Two clocks on the same table.

ClockDaysSubjects still readable after SLA
erasure SLA300 intended
metadata expiry only, files kept9014,800 for 60 extra days
rewrite plus expire plus remove files300

I would not consider it settled without evidence: erase a subject, then attempt AS OF and a file listing; require snapshot expiry to fail AS OF and require the rewritten files plus removed orphans to hold 0 identifiers.

Expiry without rewrite and file removal is a privacy clock that has not run.

Curated: · Written: · Reviewed:

Q&A-53Compaction rewrites small files. What happens to the snapshots a GDPR erasure and a bad-load rollback were counting on?

Short answer: compaction does not touch snapshots. rewrite_data_files writes new data files and commits a new snapshot; the old snapshots and the files they reference stay exactly where they were. What kills them is expire_snapshots, and compaction makes that job more dangerous, because every compaction run adds a snapshot to the same retention budget.

That is the trap. If retention is expressed as a count, hourly compaction plus hourly loads gives you roughly one hour of rollback window, not two days. The snapshots are being consumed by maintenance, not by the data.

What each operation actually does

OperationSnapshotsFiles
rewrite_data_filesadds onewrites new, leaves old ones referenced
expire_snapshotsremoves those past older_thandeletes files no retained snapshot references
remove_orphan_filesnonedeletes files no snapshot references, older than older_than (3 days by default)
rollback_to_snapshotnonenone: moves the current pointer

Two defaults decide this. history.expire.max-snapshot-age-ms is 432000000 (5 days) and history.expire.min-snapshots-to-keep is 1. And retain_last is a floor, not a ceiling: it protects the N most recent snapshots from removal, it does not cap retention at N. The ceiling is older_than, which defaults to 5 days ago unless the table property lowers it.

Trace: the rollback that was not there

09:00  load commits S100 (good)
10:00  rewrite_data_files -> S101
10:30  expire_snapshots() with history.expire.max-snapshot-age-ms = 3600000
       S100 is 90 min old -> removed; S101 retained
11:00  load commits S102 (wrong schema)
15:00  rollback_to_snapshot(100) fails: 100 is not in the snapshot list

The bad load is live for four hours before anyone looks, and the rollback target expired ninety minutes after it was created. Compaction did not cause that. The one-hour max-snapshot-age-ms did, and compaction is what kept feeding snapshots into it.

The GDPR side pulls the other way

Erasure is not complete when the DELETE commits. A merge-on-read delete leaves the rows sitting in the old data files, readable by time travel to any snapshot that predates the delete. The erasure becomes provable only after:

  1. rewrite_data_files with delete-file-threshold set low enough to force the affected files to be rewritten, so no live file contains the rows;
  2. expire_snapshots drops every snapshot that still references them;
  3. remove_orphan_files clears the now-unreferenced files.

So the rollback window and the erasure SLA are in direct conflict: the snapshot you want for rollback is the snapshot that still holds the erased rows. Resolve it with a tag, not with a long retention window. ALTER TABLE db.orders CREATE TAG good_2024_06_01 AS OF VERSION 100; pins S100 against expiry, because history.expire.max-ref-age-ms defaults to Long.MAX_VALUE. Tag the post-erasure snapshot, never one that predates it: a tag is a retention override, and it will keep erased rows on disk indefinitely.

Failure modes to check for

  • Retention set by count while compaction runs on a schedule. Convert to a time-based older_than, and alert when the oldest retained snapshot is younger than the recovery SLA.
  • A tag or branch created before an erasure. It silently defeats the erasure; audit refs against erasure timestamps.
  • Erasure proof run before expiry. SELECT count(*) FROM db.orders VERSION AS OF <id> WHERE user_id = 42 returns 0 for the current snapshot and non-zero for an older one.
  • remove_orphan_files run with a short older_than while writers are active: it can delete files belonging to an in-flight commit.

The test I would want before calling it settled: compact, then roll back to a snapshot six hours old, and require that snapshot to still exist if six hours is inside the recovery SLA.

Curated: · Written: · Reviewed:

Q&A-92A segment of "high value" customers is pushed to an ad platform. What warehouse control is still required?

What a strong candidate says first

Assume the segment is built in the warehouse and a reverse-ETL connector (Census, Hightouch, Fivetran Activations) reads it as a service account. The control still required is a data-level control bound to the PII columns themselves: a masking policy or column-level security tag, plus a row access policy scoping the segment, because the connector's SELECT is legitimate and cannot be revoked.

Why the SELECT grant doesn't govern egress

Warehouse access control is identity- and query-time. GRANT SELECT ON segment TO reverse_etl_role lets that role read every column the query names. The connector needs to read the segment, so you cannot revoke it. Nothing in the grant distinguishes "read email to compute a segment" from "read email to ship it to the ad platform." The control has to bind to the column, not the query: masking applies to the value returned regardless of which role or tool issued the SELECT.

The controls

  • Column masking / policy tag on email, phone, name. Snowflake: CREATE MASKING POLICY pii_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('PII_READER') THEN val ELSE '***' END; then ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY pii_mask;. BigQuery: a policy tag in a Data Catalog taxonomy, enforced as column-level access control. Databricks: a column mask in Unity Catalog.
  • Row access policy so the connector sees only the high-value rows it was scoped to, not the whole table.
  • A separate service account per destination, so the ad-platform connector's grant is not the same identity that reads finance tables.

Snowflake exempts the role that owns the masking policy, so the reverse-ETL service account must not hold it.

Trade-offs

Egress identifierMatch rate (typical)Still personal data?
raw emailhighestyes
SHA-256 emaillower, platform-dependentyes: pseudonymised, not anonymised
platform token / hashed IDlowest, decaysdepends on contract

Hashing is a mitigation, not a control: the ad platform holds the same hash space and can join it to its own audience. Token-only egress is insufficient when the platform has no token for the customer (no logged-in ID) and the campaign needs reach: that is the case where you accept hashed email and log the transfer.

Failure modes

  • Masking policy on the base table, but the connector reads a view or materialised copy that lacks it.
  • Policy tag created, but the service account holds a role with fine-grained reader on the taxonomy.
  • Row access policy not attached to the reverse-ETL role, so the connector exports the full table.

Detect by running the connector in dry-run and asserting the payload schema contains no raw PII columns.

Curated: · Written: · Reviewed:

Q&A-90You roll back a bad load with time travel. What must the restore procedure check that a snapshot id does not tell you?

A snapshot id is a coordinate, not a plan. It pins one object to one instant. Every decision the restore procedure has to make lives in the gap between that instant and now, and the id is silent about all of it.

What a strong candidate says first: before restoring, diff snapshot→now and classify every delta as revert, re-apply, or leave alone. The id tells you none of these.

Snapshot id gives youIt does not tell you
row state of one object at Tother tables/objects changed between T and now
a restorable point in timerows deleted for erasure after T
data already streamed or exported downstream
DDL, grants, streams, tasks created after T
whether T is still inside the retention window

The checks, in the order they bite:

  1. Retention. Time travel is bounded. Snowflake keeps 1 day on Standard and up to 90 on Enterprise; BigQuery's window is 7 days. If the incident runs long, the snapshot you planned to restore is gone. Confirm the window before you promise a rollback.

  2. Scope. In Snowflake time travel is per table, so rolling back one table can leave it inconsistent with a sibling loaded in the same window. Enumerate every object the bad load touched and restore them as a set, or fix forward.

  3. Obligations accrued after T. Erasure requests, retention expiries, legal holds. A restore replays old personal data; re-apply erasure after the restore and gate reads until it succeeds.

  4. What already left. CDC, exports, caches, search indexes, feature stores. Restore does not un-send a row. Reconcile the consumer.

  5. Idempotency. If the bad load's source re-runs on schedule, it re-applies after the restore. Pause it or make it idempotent first.

Trade-offs. Full rollback is the blunt option: it reverts good work too. The alternatives are real and the choice depends on the window.

OptionReverts good work?Exposure windowCost
Full rollback + re-eraseyesuntil re-erase completesanalyst block
Restore into clone, merge bad rowsnononebuild time, slower to ship
Fix forward (delete bad rows)nononeDDL/schema damage stays

Blocking analysts until re-erasure completes is the safe default, but it is not free. If the exposure window is short and the erased subjects are few, a documented, time-boxed exposure with a compensating control can be the right call. Say which you chose and why.

Failure modes. Partial rollback across related tables → orphaned foreign keys, caught by a referential-integrity check. Downstream replay → resurrected rows in the warehouse, caught by row-count reconciliation on the consumer. Retention expiry mid-incident → the restore fails outright.

Worked example (hypothetical): bad load at 14:00, snapshot 1802 taken 13:00. 3,100 subjects erased at 15:00. Restore at 17:00 brings all 3,100 back; analyst queries run for 2 days before re-erasure. Exposure is 3,100 subjects × 2 days. Re-erase then gate: 0 queryable. The snapshot id told you none of that.

Curated: · Written: · Reviewed: