Top 100 Data Architect Interview Questions and Answers

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.

Consumer	Assumed grain	Rows counted	Revenue reported
finance	order header	612,000	$41.2 million
product	order line	1,840,000	$118.7 million
declared unique keys	none	—	—

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.

I would start degenerate keys from the contract and the consumer, not from the warehouse diagram.

A degenerate dimension is a business identifier stored on a fact when it has no useful dimension record of its own. Related status or timestamp columns do not automatically change the fact grain or require a dimension; the decision depends on their semantics, reuse, and history requirements.

Concretely, state the fact grain, identify attributes reused across facts or requiring independent history, keep the identifier degenerate when no separate dimension adds value, and model reusable or independently changing attributes in the appropriate dimension or fact lifecycle.

The reason for that specificity is a failure I have seen: Order status history was appended as new fact rows without changing the declared order-line grain. 2.1 million facts gained 3 versions each over 9 days, and uniqueness on (order_number, line) broke for 47,800 rows; adding columns alone would not have caused that fan-out.

When the identifier stopped being degenerate.

State	Non-key attributes on the identifier	Fact versions per order	Broken unique rows
designed	0	1	0
after status landed on the fact	4	3	47,800

I would not consider it settled without evidence: apply three status changes to one order line and require the chosen design to preserve the declared fact grain while retaining exactly the history consumers need.

Degenerate means no leftover attributes, not "we skipped the dimension."

This is an area where a green dbt test and a held meaning are different observations.

A natural key is what the source or business uses to recognise an entity. A warehouse surrogate gives each dimension version a stable join key when natural keys change, collide across sources, or participate in type-2 history. A durable source UUID can still be a valid fact key when those risks do not apply.

Concretely, keep the natural key as an alternate key, enforce uniqueness on the current row or on (natural_key, effective_start) for type-2 history, use a warehouse surrogate where versioned or cross-source identity requires it, and refuse a fact whose natural key resolves to anything other than one effective dimension row.

The reason for that specificity is a failure I have seen: Facts joined customer on a CRM UUID. After a merge of 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.

Natural-key merge without a surrogate.

Stage	People	Facts still on retired UUIDs	Double-counted LTV
before merge	18,400	0	$0
11 days after merge	9,200	6,300,000	$4.8 million

I would not consider it settled without evidence: replay a natural-key merge and require every fact to follow the surviving surrogate while the retired natural key remains queryable as history.

Choose the join key from identity and history requirements, not habit.

My answer to SCD type 2 versus type 1 begins with which semantic claim is being made, not which physical table exists.

Type 1 overwrites the current attribute and rewrites history for every prior fact. Type 2 inserts a new version with effective dates so a fact keeps the attribute that was true when the event happened. The choice is whether yesterday's report is allowed to change when the source edits a name.

Concretely, type-2 any attribute that a historical metric must freeze, keep a current-row flag and closed-open effective dates, join the fact to the version whose range contains the event timestamp, and type-1 only corrections that were never true.

The reason for that specificity is a failure I have seen: Category was type-1. A rebrand on 3 March rewrote 14 months of facts. Year-over-year category mix shifted 9.4 percentage points overnight with 0 new orders, and 3 finance closes had to be restated.

One rebrand, two SCD choices.

Handling	Facts restated	Mix shift with 0 new orders	Closes reopened
type 1 overwrite	14 months	9.4 points	3
type 2 version	0	0	0

I would not consider 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.

I would treat SCD type 3 current and previous as a claim about every producer, region, and load window, not about the one that rendered.

Type 3 stores a previous value beside the current value. It answers one prior state, not an arbitrary history. If a third change arrives, the first prior value is gone unless you also version.

Concretely, use type 3 only when the business named exactly two states it will query, keep the change timestamp, and promote to type 2 the moment a third state must be recovered.

The reason for that specificity is a failure I have seen: Region used type 3. After 3 reorganisations in 22 months, 41,200 customers had lost the original region. A 2019 cohort study recovered the first region for only 18 percent of the file.

How many prior regions survived.

Reorganisation	Prior regions stored	2019 cohort recoverable
1	1	100 percent
3 in 22 months	1	18 percent
customers affected	41,200	—

I would not consider it settled without evidence: apply three successive region changes and require the first value to remain queryable; if it does not, reject type 3 for that attribute.

Two columns remember one change, not a timeline.

The useful question for conformed dimensions is what a second consumer would compute from the same rows.

Conformance is shared keys, shared grain, and shared attribute definitions that produce the same member for the same entity. Two tables named customer that use different identifiers or different "active" rules are not conformed; they are a silent join tax.

Concretely, publish one member identifier and one set of type-2 attributes as the contract, map every producer onto that identifier before facts load, and fail any dashboard that joins on name, email, or a local surrogate.

The reason for that specificity is a failure I have seen: Sales keyed customer on CRM id; support keyed on ticket email. 12 percent of emails were shared mailboxes. Cross-domain "customers with open tickets" under-counted 8,640 accounts for 6 weeks.

Two customer tables, one mailbox.

Domain	Customer key	Shared-mailbox emails	Under-counted accounts
sales	CRM id	not used	—
support	email	12 percent	8,640 for 6 weeks

I would not consider 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.

Same noun is not the same member.

I would settle junk dimensions by exercising the failed path against the contract, not against the catalog screenshot.

A junk dimension packs low-cardinality flags and codes that are not a business entity into one surrogate, so the fact does not grow a column per checkbox. It is not a dumping ground for attributes that belong on a real dimension.

Concretely, materialise observed valid combinations rather than every theoretical Cartesian product, assign a surrogate when the resulting cardinality and storage tradeoff beat separate fact columns, and move any attribute with independent history or entity semantics to a proper dimension.

The reason for that specificity is a failure I have seen: Eight boolean flags stayed on the fact. After 3 more flags, the table widened by 11 columns and scan cost on 2.4 billion facts rose 37 percent. A 48-member junk dimension would have covered the same combinations.

Flags on the fact versus a junk dimension.

Design	Extra fact columns	Distinct combinations	Scan cost change
flags on fact	11	48	+37 percent
junk dimension	1 surrogate	48	0

I would not consider it settled without evidence: count observed combinations, compare fact width and join cost under both designs, and require that any junk-dimension member represent only valid low-cardinality combinations with no independent history.

Pack flags that are not entities; do not hide entities in the pack.

The judgement in late-arriving facts is which identifier and which time axis the fact is keyed on.

A late fact is still attributed to its event time, not its load time. It must join the dimension version effective at the event. Physical partitioning may use event time or ingestion time, but the query contract must still put the fact in the correct business period.

Concretely, store event and ingestion timestamps, resolve type-2 dimensions as of event time, choose partitioning from overwrite, retention, and late-write costs, and publish a lateness watermark so consumers know when an event-time period is provisionally closed.

The reason for that specificity is a failure I have seen: Late shipments were partitioned by load date and dashboards also filtered only that physical partition. 190,000 facts for 2 March landed under 8 March, so fill-rate for 2 March stayed 4.1 points low for 11 days even after the events arrived.

Where the 2 March shipments were stored.

Placement	Facts	2 March fill-rate error	Days the error lasted
load-date partition 8 March	190,000	4.1 points	11
event-date partition 2 March	190,000	0	0

I would not consider it settled without evidence: inject a fact whose event time is 6 days old and require it to join the 6-day-old dimension version and appear in the 2 March business metric regardless of its physical partition.

Lateness is a delivery property, not a new grain.

Where candidates lose the interview on late-arriving dimensions is calling the lakehouse layout the model.

A fact cannot wait forever, and it cannot join a future customer version that did not exist at event time. The architecture uses an inferred member with the natural key, a warehouse surrogate, and unknown attributes, then upgrades that member when the dimension arrives.

Concretely, mint an inferred dimension row on first unseen natural key, attach the fact to that surrogate, queue the natural key for repair, and overwrite unknown attributes without creating a second member when the real row arrives.

The reason for that specificity is a failure I have seen: Facts with missing customers were dropped. 27,400 first-time buyers vanished from day-1 revenue for 8 days, then appeared as a spike when the dimension finally loaded, moving $2.1 million between weeks.

Dropped facts versus inferred members.

Handling	Day-1 facts missing	Revenue moved between weeks	Distinct customers after repair
drop the fact	27,400	$2.1 million	correct later
inferred then upgraded	0	$0	1 per natural key

I would not consider it settled without evidence: land a fact with an unknown customer, then land the customer, and require 1 member, 1 fact, and attributes filled in rather than a second surrogate.

Infer the member; do not delete the fact or invent a later person.

I would answer MDM golden records by separating storage, schema, and the meaning a consumer is allowed to assume.

A golden record is a survivorship decision with a published rule, a durable enterprise identifier, and an audit of which source won each attribute. A nightly "winner" table without rules is another duplicate, not a master.

Concretely, assign an enterprise id, apply attribute-level survivorship with source rank and recency, persist the losing records as aliases, and refuse warehouse loads that still key facts on a local CRM id.

The reason for that specificity is a failure I have seen: A "golden" table picked the latest-updated CRM row. A batch job that touched 410,000 stale rows overnight stole gold from the billing system. 19,200 billing addresses flipped, and 1,140 invoices mailed to former offices.

Last-write-wins versus ranked survivorship.

Rule	Rows flipped overnight	Invoices to former offices	Aliases kept
latest updated_at	19,200	1,140	0
billing ranked over CRM	0	0	19,200

I would not consider it settled without evidence: run two sources with conflicting addresses through the rule and require the published winner, the losing alias, and a fact still keyed on the enterprise id.

Gold is a ruled identifier, not the row that was touched last.

The engineering content of survivorship rules is the contract and the incompatible write it would reject, not the file format.

Survivorship is per attribute and per purpose, not a single row winner. The warehouse can store both ruled values, but it must name which value a given data product is allowed to use.

Concretely, publish an attribute matrix of source rank by purpose, store surviving and alternate values, and bind each data product to one column so a marketing export cannot silently take the legal address.

The reason for that specificity is a failure I have seen: One winner column fed every product. Marketing's recency rule overwrote 6,800 contract addresses. 420 dunning letters used a campaign click address, and 38 days of collections paused.

One winner column versus purpose-bound attributes.

Product	Address rule	Wrong letters	Collections paused
all products, one column	recency	420	38 days
legal versus marketing columns	contract versus recency	0	0

I would not consider it settled without evidence: export the legal product and the marketing product from the same golden record and require two different address columns with two different rules in the contract.

One person can have two surviving addresses; one product cannot.

Before calling Avro schema evolution done I would write down the consumer, region, or batch window nobody loaded.

Avro fills a field the writer never stored from the reader schema's default, not from the writer's. A new reader that adds a field with no default cannot decode historical files that lack that field; those files have no writer default to consult.

Concretely, put the default on the reader schema for every newly added field, run the newest reader against retained historical files before publishing, forbid a required reader field with no default, and reject the registry version when that decode fails.

The reason for that specificity is a failure I have seen: A required channel field landed on the new reader with no default. 14 days of historical files, 2.8 million records, failed to deserialize. The pipeline retried for 6 hours and duplicated 410,000 facts that had already landed from a parallel JSON path.

Adding channel to an Avro topic.

Change	Registry result	Historical files readable	Duplicate facts
new reader field, no reader default	accepted by misconfiguration	0 of 2.8 million	410,000
reader field with default ""	accepted	2.8 million	0

I would not consider it settled without evidence: publish a reader field without a default against pinned historical files and require the registry to reject the subject version.

The default that saves history lives on the reader, not on a writer who never saw the field.

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

Protobuf identifies fields by number. Reusing a number at the same wire type silently reinterprets old bytes. An incompatible wire type does not convert the value; those bytes become unknown fields. Reserve retired numbers so they cannot return.

Concretely, treat field numbers as immutable, reserve deleted numbers, add new meaning only under a new number, and refuse a decoder that binds a column to a number previously used at the same wire type.

The reason for that specificity is a failure I have seen: Field 7 stayed varint but changed from amount_cents to item_count. 9.1 million lake files decoded silently as counts. Revenue for 3 days printed as 0 while 7 dashboards stayed green on "row count passed." A later string sku on field 7 would have left amounts as unknown fields, not as SKUs.

Reusing field 7.

Decoder	Field 7	Files	What the bytes did
old	amount_cents varint	9.1 million	correct revenue
new, same number, same wire type	item_count varint	9.1 million	$0 for 3 days, silent
new, same number, string sku	length-delimited	9.1 million	amounts unknown, sku empty

I would not consider it settled without evidence: decode a file written under the old number with a same-wire-type reuse and require a compatibility gate to fail before the warehouse job is allowed to run.

Same-wire-type reuse is corruption; a different wire type is an unknown field. Reserve the old number.

I would start Iceberg column evolution from the contract and the consumer, not from the warehouse diagram.

Iceberg tracks columns by id, so add, rename, and drop are id-safe at the table-format layer. That is not automatic consumer compatibility. SELECT *, cached Spark names, JDBC ordinals, and positional writers still bind the SQL name or position they were compiled with.

Concretely, add columns as optional, rename only through a published mapping with consumer notice, never reuse a dropped column id for a new meaning, and test both name-bound and position-bound consumers against the evolved schema rather than assuming field ids protect their projections.

The reason for that specificity is a failure I have seen: amount was renamed to amount_usd. The Iceberg metadata stayed id-safe, but a Spark job cached the old name for 36 hours and failed with an unresolved-column error before writing 1.2 million expected downstream rows. A JDBC client reading by the old column label failed too; the rename itself did not shift column positions.

Rename without consumer notice.

Layer	amount visible	Expected rows not written	Hours until noticed
Iceberg catalog (id-safe)	as amount_usd	0	—
cached Spark / JDBC name lookup	unresolved old label	1,200,000	36

I would not consider it settled without evidence: rename a column and run every listed consumer against the new snapshot, requiring name-bound consumers to adopt the new label and position-bound consumers to preserve the same field mapping.

Format-level column ids do not update a frozen projection.

This is an area where a green dbt test and a held meaning are different observations.

Iceberg can add a partition spec for new files while old files retain the old spec. Overwrite behavior then depends on the engine and API: a static predicate overwrite may match files across specs, while dynamic overwrite identifies partitions under the current spec and can leave old-spec files untouched.

Concretely, add the hour spec for new writes, leave historical files on day, choose an explicit predicate overwrite or MERGE for corrections, and test both old- and new-spec files because a dynamic overwrite that was safe under the old spec may no longer replace the intended slice.

The reason for that specificity is a failure I have seen: After hour partitioning, a job reused dynamic overwrite for a single hour. It replaced the current-spec hour but left 18 old day-spec files containing overlapping rows, duplicating 6.8 million facts. A static day predicate with complete input or a keyed MERGE would have made the scope explicit.

Day overwrite after hour evolution.

Action	Old-spec files left	Duplicate facts	Result
dynamic overwrite after spec change	18	6,800,000	overlapping day/hour files
explicit predicate or keyed MERGE	0 unintended	0	scope tested

I would not consider it settled without evidence: evolve the spec, write overlapping old- and new-spec files, then require the selected overwrite or MERGE API to produce exactly one row per grain key across the boundary.

Overwrite is legal when the input is complete; incomplete input is the delete.

My answer to backward versus forward compatibility begins with which semantic claim is being made, not which physical table exists.

Compatibility names a direction, a history depth, and a format. BACKWARD checks the newest reader against the immediately preceding writer schema; BACKWARD_TRANSITIVE checks it against every registered version. A warehouse that rereads the oldest retained data needs the transitive mode or an explicit all-retained-versions gate.

Concretely, set warehouse subjects to BACKWARD_TRANSITIVE or FULL_TRANSITIVE for retained history, prove the newest job reads sampled files from the oldest retained version, and do not treat an Avro reader default as portable across formats or directions.

The reason for that specificity is a failure I have seen: The subject was BACKWARD but not transitive. Each adjacent upgrade passed, yet a new warehouse job could not read 11 months of files whose writer schemas lacked a field the newest reader required. Backfill stalled 17 days and a regulatory extract missed 22 million rows.

Registry mode versus a history replay.

Mode	Newest reader vs 11-month-old files	Rows missing from extract	Backfill stall
BACKWARD (latest version only)	not guaranteed	22,000,000	17 days
BACKWARD_TRANSITIVE	checked against all versions	0	0

I would not consider it settled without evidence: compile the newest reader against the oldest retained schema and require a successful decode of 1,000 sampled files.

The warehouse is the newest reader of the oldest bytes; that requires a transitive backward guarantee.

I would treat additive versus breaking schema as a claim about every producer, region, and load window, not about the one that rendered.

Additive changes extend the schema without changing the meaning of existing columns: a new optional field, a new table. A type or unit change reinterprets old values and is a break, even when SQL still compiles.

Concretely, version the column or introduce amount_usd beside amount_cents with a dual-read window, backfill with an explicit conversion, and drop the old column only after every listed consumer has switched.

The reason for that specificity is a failure I have seen: The column type flipped in place. 840 million historical cents were read as dollars. One dashboard showed $12.4 trillion of GMV for 4 hours, and 3 downstream ML features trained on the spike.

Cents read as dollars.

Representation	Stored values	Dashboard GMV	Hours live
int cents	840,000,000 rows	correct	—
decimal dollars, same column	same bytes misread	$12.4 trillion	4

I would not consider it settled without evidence: apply the type change in a shadow column, compare 10,000 converted rows to the source cents, and refuse an in-place type change on a column that already has files.

Same name plus new type is a break.

The useful question for PII classification in the warehouse is what a second consumer would compute from the same rows.

Classification is a label. Architecture is which roles can select the column, which jobs can write it to a less-controlled zone, and how long each copy lives. Forty tags with SELECT * still granted is interior decoration.

Concretely, bind each PII class to column masks, allowed processors, and egress zones, deny SELECT * on classified schemas, and test that a BI role cannot project the raw column.

The reason for that specificity is a failure I have seen: email was tagged PII while a reporting view used SELECT *. 2,400 analysts kept raw emails. A spreadsheet export of 1.1 million addresses left the warehouse, and the tag in the catalog stayed green.

Tagged PII that still exported.

Control	Analysts with raw email	Addresses exported	Catalog tag
tag only	2,400	1,100,000	green
tag plus mask plus deny SELECT *	0	0	green

I would not consider it settled without evidence: connect as the BI role, project the classified column, and require a mask or a deny; then export to a sandbox and require the raw value to be absent.

A tag does not mask a column.

I would settle column-level access by exercising the failed path against the contract, not against the catalog screenshot.

Row policy answers whose rows. Column policy answers which attributes. A support analyst who can see every column of their customers still sees national identifiers that the ticket system is not allowed to store.

Concretely, combine row filters with column masks per role, list the columns each role may see in the data product contract, and test a query that projects a forbidden column even on an allowed row.

The reason for that specificity is a failure I have seen: Row filters were correct for 98 percent of queries. A support role still selected tax_id. 6,200 identifiers landed in ticket comments over 21 days.

Row filter without column mask.

Policy	Rows visible	tax_id visible	Identifiers in tickets
row filter only	own customers	yes	6,200 in 21 days
row filter plus column mask	own customers	no	0

I would not consider it settled without evidence: as the support role, select tax_id on an allowed customer and require a masked or denied result.

The right row with the wrong column is still a leak.

The judgement in GDPR erasure versus warehouse history is which identifier and which time axis the fact is keyed on.

Erasure is every copy of the identifying attributes: current rows, type-2 history, late-arriving repairs, downstream marts, and time-travel snapshots whose files still exist. Expiring a snapshot in the catalog while retaining its data files is not erasure. After those snapshots are expired, AS OF that snapshot id must fail because the snapshot is gone.

Concretely, resolve the enterprise id to every table and snapshot that stores identifying attributes, rewrite identifying attributes, expire the snapshots that held them, remove the unreferenced files, and prove AS OF the pre-erasure snapshot id errors with snapshot expired rather than returning the email.

The reason for that specificity is a failure I have seen: Current customer was deleted. Type-2 history kept 11 versions. Snapshot 2209 was expired in the catalog while its files were retained. AS OF 2209 should have failed as snapshot expired; a file-path scan still returned the email for 4 days. A restored sandbox from those files reintroduced 1 person into 3 marts.

Where the person remained.

Copy	Identifying attributes after DELETE current	AS OF snapshot 2209
current dimension	0	—
type-2 history	11 versions	until rewritten
snapshot expired, files retained	1 email via file path	should have failed expired; did not

I would not consider it settled without evidence: after erasure, query current and history for 0 identifying attributes, then AS OF the pre-erasure snapshot id and require the engine to fail because that snapshot expired, not because it returned a masked row while files still exist.

History and time travel are copies; erasure expires the snapshot so AS OF cannot resurrect it.

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.

Clock	Days	Subjects still readable after SLA
erasure SLA	30	0 intended
metadata expiry only, files kept	90	14,800 for 60 extra days
rewrite plus expire plus remove files	30	0

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.

I would answer data mesh versus central warehouse by separating storage, schema, and the meaning a consumer is allowed to assume.

Mesh federates product ownership, SLAs, and pipelines. Cross-domain identifiers, security classes, and conformed meanings still need governed interoperability, but that can come from shared standards, mappings, or identity data products rather than one centrally minted key.

Concretely, give domains ownership of product schemas, govern identity mappings and classification through federated contracts, and refuse a cross-domain customer product whose local key has no tested mapping or declared semantics.

The reason for that specificity is a failure I have seen: Four domains published customer. Matching across them required 3 weeks of email heuristics. 7.2 percent of enterprise revenue could not be attributed to a single person, $18 million in a quarter.

Federated products without a shared identifier.

Domain customer keys	Matching method	Unattributed quarterly revenue
4 local keys	email heuristics for 3 weeks	$18 million (7.2 percent)
governed mappings or identity product	tested keys	$0

I would not consider it settled without evidence: list every domain product that claims customer and require either a governed enterprise id or a tested mapping from its local key into the cross-domain identity product.

Own the pipeline; do not own a private person.

The engineering content of data product SLAs is the contract and the incompatible write it would reject, not the file format.

A data-product SLA names freshness, completeness, uniqueness, schema compatibility, and the exception path when a check fails. "Daily" is a wish. Consumers need the time by which the day is closed and what they must do if it is not.

Concretely, publish closed-by time in a named timezone, completeness against a source count, uniqueness on the grain keys, a compatibility mode, and a pager owner; block downstream jobs when any check fails.

The reason for that specificity is a failure I have seen: The page said daily. Completeness was unmeasured. 12 percent of source rows were missing for 9 consecutive days. A pricing consumer trained on the holes and mispriced 64,000 SKUs.

A daily product without a close.

Check	Stated	Measured	Downstream effect
freshness	daily	none	partials loaded
completeness	none	12 percent missing for 9 days	64,000 SKUs mispriced

I would not consider it settled without evidence: fail a completeness check at 06:00 and require downstream jobs to skip rather than to load a silent partial.

Daily without a close time is a rumor.

Before calling OpenLineage as evidence done I would write down the consumer, region, or batch window nobody loaded.

Lineage evidence is OpenLineage events emitted by the jobs that actually read and wrote, with dataset and column facets. A hand-drawn or crawled graph is a hypothesis; it drifts the moment a job bypasses the scheduler.

Concretely, require every production job to emit run events, compare the graph to the scheduler inventory, and fail a release whose job has no event for the datasets it claims.

The reason for that specificity is a failure I have seen: A crawled graph showed warehouse.customer from crm.customer. A weekend Python notebook wrote 380,000 rows from a spreadsheet. The graph stayed unchanged for 11 days while 380,000 people had no CRM id.

Crawled graph versus emitted events.

Writer	Lineage events	Rows with no CRM id	Days the graph looked fine
scheduled dbt	yes	0	—
weekend notebook	0	380,000	11

I would not consider it settled without evidence: run a job that writes without emitting lineage and require the release gate to fail; then emit events and require the column facet to name the source field.

A picture of jobs is not an event from a job.

The first thing I would establish about quality contracts before load is which grain and which keys the number actually uses.

Schema compatibility should fail in the producer pipeline before publication. Grain and uniqueness may require warehouse history, so validate them in an isolated landing or staging area and refuse promotion to the certified table. Treating every check as producer-side misses cross-batch duplicates; checking only after promotion is an autopsy.

Concretely, publish the wire contract as a schema-registry subject, validate it on the producer, stage the accepted bytes, run grain and uniqueness assertions against staged plus existing keys, and promote only when both layers pass.

The reason for that specificity is a failure I have seen: Tests ran in dbt after load. A producer widened an enum on Friday. 6.4 million incompatible rows sat in the table until Monday. 14 consumers failed; 2 had already published numbers.

Where the incompatible enum was caught.

Gate	Rows in the certified table	Consumers that published numbers
dbt after load	6,400,000	2
producer schema plus staging grain checks	0	0

I would not consider it settled without evidence: send an incompatible record from a producer harness and require a schema reject, then send a schema-valid duplicate of an existing grain key and require staging to block its promotion.

Reject schema at the producer and global grain before certified promotion.

I would start CDC versus batch from the contract and the consumer, not from the warehouse diagram.

Batch is a consistent cut of a day. CDC is a stream of changes with an ordering key. Mixing them in one table without a change-sequence produces double counts and lost deletes.

Concretely, pick one primary for a given fact, carry a change sequence on CDC, materialise a daily snapshot for close, and forbid a union of CDC and batch that lacks a dedupe key.

The reason for that specificity is a failure I have seen: Orders mixed a nightly dump with CDC. Inserts duplicated 3.1 percent of rows. Deletes from CDC arrived 14 hours late. Closed revenue was $2.6 million high for 8 days.

A union without a change sequence.

Path	Duplicate inserts	Late deletes	Closed revenue error
dump plus CDC, no merge key	3.1 percent	14 hours	+$2.6 million for 8 days
CDC sequenced plus daily snapshot	0	0 in the close	$0

I would not consider it settled without evidence: run a source delete through both paths and require the closed snapshot to drop the row once, not twice or never.

Two ingest styles are two grains until you name the merge.

This is an area where a green dbt test and a held meaning are different observations.

Lakehouse tables on object storage can provide governed grain, transactions, access control, and SLAs; raw files cannot. Choose by engine support, workload, latency, governance, and ownership, not by "files versus tables."

Concretely, declare the authoritative governed table for each product, enforce schema and column policy across its engines, measure workload isolation, and refuse production BI credentials on ungoverned raw prefixes.

The reason for that specificity is a failure I have seen: Analysts queried the raw bucket because it was 40 minutes fresher. 22 jobs scanned 9.4 TB/day ungoverned. A PII column that was masked in the warehouse was free in the lake for 16 weeks.

Fresher lake, weaker control.

Layer	Freshness	PII masked	Unplanned scan
warehouse product	T+40 minutes	yes	—
raw lake bucket	T+0	no for 16 weeks	9.4 TB/day

I would not consider it settled without evidence: attempt a BI query against the raw prefix and require a deny, then show the same grain through the warehouse product.

Cheap files are not a second warehouse.

My answer to serving store versus analytical store begins with which semantic claim is being made, not which physical table exists.

A strict 20 ms point-read SLO usually favors a keyed serving store with workload isolation, but analytical and HTAP systems are not inherently eventual or batch-only. The decision depends on measured p99, concurrency isolation, availability, and freshness under production analytical load.

Concretely, load-test the candidate analytical or HTAP path under compaction and ETL; if it misses the SLO, project a keyed serving table with a documented lag SLA and idempotent synchronization, then keep analytical queries isolated from that serving workload.

The reason for that specificity is a failure I have seen: The app queried the warehouse for profile. p99 rose from 18 ms to 940 ms during the 02:00 merge. Checkout timeouts hit 2.4 percent of sessions for 47 minutes, 11 nights in a row.

Profile reads during merge.

Store	p99 profile read	Checkout timeouts	Nights repeated
warehouse during 02:00 merge	940 ms	2.4 percent for 47 minutes	11
serving key-value, 90 s lag	18 ms	0	0

I would not consider it settled without evidence: load-test 5,000 profile reads during a warehouse merge and require p99 under the serving SLA with workload isolation; use a separate serving store only if the analytical or HTAP path fails.

The golden record is a meaning; serving is a latency budget.

I would treat slowly changing reference data as a claim about every producer, region, and load window, not about the one that rendered.

Reference data that looks static still splits, renames, and retires. Facts that stored only the code without effective dates cannot reconstruct which country a shipment belonged to on the event day after a split.

Concretely, version reference members with effective dates, freeze the code that was valid at event time on the fact or join as-of, and treat an in-place code overwrite as a type-1 correction only when the old code was never valid.

The reason for that specificity is a failure I have seen: A country split reused a code. 61,000 historical shipments flipped country in reports. Duty calculations for 14 months were restated, $3.4 million.

In-place country code reuse.

Handling	Historical shipments restated	Duty restatement
overwrite code	61,000	$3.4 million over 14 months
type-2 reference	0	$0

I would not consider it settled without evidence: split a reference member and require historical facts to stay on the pre-split member when queried as-of event time.

Rarely changing is not never versioned.

The useful question for event time versus processing time is what a second consumer would compute from the same rows.

Event time is when the business action happened. Processing time is when the warehouse saw it. Hourly business metrics that bucket on processing time move late events into the wrong hour and cannot be replayed.

Concretely, store both timestamps, aggregate business metrics on event time, use processing time only for freshness SLOs, and window with a watermark on event time.

The reason for that specificity is a failure I have seen: A collector outage delayed 2.2 million events by 3 hours. Processing-time hours showed a 3-hour hole then a spike. Marketing paused a campaign for a "drop" that never happened in event time.

A 3-hour collector delay.

Clock	Hole in the hourly metric	Spike after catch-up	Campaign paused
processing time	3 hours	yes	yes
event time	0	0	no

I would not consider it settled without evidence: delay a stream by 3 hours and require the event-time hourly metric to match the undelayed run within the stated tolerance.

The business hour is the event's hour.

I would settle timezone storage by exercising the failed path against the contract, not against the catalog screenshot.

Instants belong in UTC. Civil dates for a local close belong with a named timezone. A naive timestamp has no offset, so a DST spring-forward has no local 02:00-03:00 to store, and a fall-back repeats that hour.

Concretely, store event_ts_utc as timestamptz, store local_close_date with a timezone identifier, and forbid naive timestamp columns on facts.

The reason for that specificity is a failure I have seen: Naive timestamps were treated as UTC in one job and as Europe/Paris in another. On the DST spring-forward, 18,400 rows carried the nonexistent local hour 02:00-03:00 and were dropped or shifted; they were not 18,400 valid facts in that hour. On the fall-back, 18,400 facts duplicated into the repeated hour. Two closes disagreed by $1.1 million.

Spring-forward gap versus fall-back duplicate.

Event	Local clock	What happened	Close disagreement
spring-forward	02:00-03:00 does not exist	18,400 invalid naive values dropped or shifted	$1.1 million
fall-back	02:00-03:00 happens twice	18,400 duplicated	included above
timestamptz UTC	instants preserved	0 gap, 0 duplicate	$0

I would not consider it settled without evidence: load events through a DST transition and require identical UTC instants and a single local close date per civil timezone policy.

A timestamp without a zone is not a time.

The judgement in unit and currency is which identifier and which time axis the fact is keyed on.

A measure without a unit and a currency is not additive across rows. Warehouse architecture stores the original amount, the ISO currency, a unit, and a converted amount with the rate date used.

Concretely, require currency_code and unit on every money or quantity measure, convert through a dated rate table, and refuse a SUM across mixed currencies in a certified metric.

The reason for that specificity is a failure I have seen: Yen and dollars were summed as "amount." Fourteen days of JP volume at ¥81 million sat beside $81 million, producing a dimensionless raw SUM of 162 million. At 109.4 JPY per USD, ¥81 million is about $740,402, so the valid combined total is about $81.740 million.

A SUM of mixed currencies.

Currency	Rows	Raw SUM	Converted SUM
USD	40,000	81,000,000	$81 million
JPY	40,000	81,000,000	~$740,000 at 109.4
mixed certified	80,000	162,000,000 raw	~$81.74 million

I would not consider it settled without evidence: insert two rows in different currencies with the same numeric amount and require the certified metric to convert before adding.

The number is not the money until the unit is named.

Where candidates lose the interview on JSON-in-SQL is calling the lakehouse layout the model.

JSON in a column is a bypass of schema, grain, and classification. A certified measure must be projected into typed columns with a contract; path extracts in every dashboard are 50 undeclared schemas.

Concretely, shred documented JSON paths into typed columns at load, classify those columns, and deny certified metrics that call JSON extract functions.

The reason for that specificity is a failure I have seen: price lived at $.amount then $.price.amount after a producer change. 9 dashboards kept the old path and summed nulls for 6 days, $0 revenue, while 2 new dashboards summed the nested path.

Two JSON paths, two revenues.

Dashboard path	Days	Revenue shown
$.amount	6	$0
$.price.amount	6	correct
shredded typed column	—	one number

I would not consider it settled without evidence: change a JSON path in a fixture and require the load to fail until the shredder is updated, rather than to load nulls into certified columns.

If a dashboard has to parse JSON, the contract is missing.

I would answer dual-write migrations by separating storage, schema, and the meaning a consumer is allowed to assume.

Dual writes without a single commit produce divergent sources. The warehouse then has two truths and no grain. An outbox or change stream from one authoritative write is the ingest contract.

Concretely, take facts from one change stream, keep a reconciliation count between A and B during dual-run, and refuse to union A and B dumps as the warehouse fact.

The reason for that specificity is a failure I have seen: The app dual-wrote. 0.8 percent of orders landed in only one system. The warehouse unioned both dumps and counted 1.6 percent extras from retries. Close was $740,000 high for 5 days.

Union of dual-written dumps.

Source	Missing orders	Extra from retries	Close error
A only	0.4 percent	0	low
B only	0.4 percent	0	low
warehouse union	0	1.6 percent	+$740,000 for 5 days

I would not consider it settled without evidence: kill one write path in a test and require the warehouse to follow the surviving outbox, not to union both databases.

Two writes are two sources; pick the stream.

The engineering content of dataset ownership is the contract and the incompatible write it would reject, not the file format.

Ownership is the named group that can reject a breaking schema, that pages on the SLA, and that performs erasure. Shared write without an owner is how grain dies. Readers do not become owners by depending.

Concretely, put one owning group on the catalog record, require their approval on schema changes, route freshness pages to them, and convert extra writers to producers behind the owner's contract.

The reason for that specificity is a failure I have seen: Nine writers shared a "core.events" table. A type change by team 7 broke 11 jobs. The page had 0 owners. Mean time to a rollback was 16 hours, 2.1 billion rows of mixed types.

A table with nine writers.

Writers	Catalog owner	Hours to rollback	Rows of mixed type
9	0	16	2,100,000,000
1 owner, 8 contracted producers	1	0 in CI	0

I would not consider it settled without evidence: submit a breaking schema change and require the owning group's contract test to fail in CI before merge.

If everyone can write, no one can refuse.

Before calling exception process for failed contracts done I would write down the consumer, region, or batch window nobody loaded.

A failed contract is a refuse-or-quarantine decision with an owner, a clock, and a consumer notification. Loading anyway teaches producers that the contract is documentation.

Concretely, quarantine the bad partition, page the owner, notify downstream with a skip, and allow a signed exception with an expiry only when the incompleteness is accepted for a named close.

The reason for that specificity is a failure I have seen: Uniqueness failed on 4 percent of rows. The job loaded anyway. 3 days later a replay "fixed" them and duplicated 1.9 million facts. Refunds were understated $410,000.

Load-anyway after a uniqueness fail.

Action	Rows promoted	Duplicate facts after replay	Refunds understated
load anyway	all, including 4 percent bad	1,900,000	$410,000
quarantine	0	0	$0

I would not consider it settled without evidence: fail uniqueness in a staging load and require 0 rows promoted, plus a notification to listed consumers, unless a named officer signs an expiring exception.

A ticket is not a quarantine.

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

Residency is about identifying attributes and derived profiles, not about "the primary is in Frankfurt." A global replica that projects email or device id is an extra processing location.

Concretely, split identifying columns into a regional vault, replicate only non-identifying aggregates or tokenised keys, and test that the global replica cannot join back to a natural identifier.

The reason for that specificity is a failure I have seen: A "hashed" email used a 6-character prefix. The global replica still re-identified 31 percent of EU users. 4.2 million records sat in us-east-1 for 11 months.

Global replica of EU identifiers.

Replica contents	Re-identifiable EU users	Location	Months
6-char email prefix	31 percent (4.2 million)	us-east-1	11
regional vault plus token	0	EU	—

I would not consider it settled without evidence: query the global replica for an EU subject and require no identifying attribute and no join key that maps 1:1 to one.

A replica is a processing location.

I would start retention versus legal hold from the contract and the consumer, not from the warehouse diagram.

Retention is a delete clock per class. Legal hold is an exception that freezes specific enterprise ids or case keys. A global "keep forever because legal might ask" is not a hold.

Concretely, tag held ids, exclude them from retention jobs, expire unheld data on schedule, and review holds on a calendar so they cannot become unbounded.

The reason for that specificity is a failure I have seen: Retention was disabled estate-wide "for legal." 6.1 PB accumulated. When a hold for 2,400 ids was actually issued, nobody could prove the other 99.96 percent should have been deleted 3 years earlier.

Estate-wide pause versus keyed holds.

Policy	Data kept	Held ids	Provable timely delete
keep forever	6.1 PB	unknown	no
keyed hold	2,400 ids	2,400	yes for the rest

I would not consider it settled without evidence: run retention on a table with 10 held ids and 10,000 unheld, and require only the held ids to remain.

Hold is a set of keys, not a platform pause.

This is an area where a green dbt test and a held meaning are different observations.

Point-in-time correctness is a join against history as-of the prediction time. A feature computed from current warehouse tables leaks future type-1 updates and late facts into training.

Concretely, build features from event-time snapshots or type-2 as-of joins, freeze a training as-of timestamp, and reject features that read current_flag = true for historical labels.

The reason for that specificity is a failure I have seen: Fraud labels used current customer risk_score, a type-1 column. Training AUC was 0.94; production was 0.61. The score had been updated 12 days after the transaction for 18 percent of labels.

Type-1 risk_score in training.

Feature time	AUC	Labels with post-event updates
current table	0.94	18 percent
as-of transaction time	0.61 in replay	0 used

I would not consider it settled without evidence: compute the feature as-of label time and as-of today; if they differ on more than the allowed fraction, refuse the current-table feature.

Current tables are not a time machine.

My answer to reverse ETL keys begins with which semantic claim is being made, not which physical table exists.

Reverse ETL must use the destination's durable identifier or a mapped enterprise id the destination already stores. A warehouse surrogate is meaningless in the CRM and will create duplicates or silent no-ops.

Concretely, map enterprise id to CRM id in a tested crosswalk, send only mapped ids, and quarantine segment members without a mapping rather than inserting new CRM records from a surrogate.

The reason for that specificity is a failure I have seen: A job wrote warehouse_customer_sk into a CRM external id. 220,000 new CRM records were created. Outreach doubled for 9 days, 44,000 unsubscribes.

Surrogate pushed to CRM.

Identifier sent	New CRM records	Extra outreach days	Unsubscribes
warehouse_customer_sk	220,000	9	44,000
mapped CRM id	0	0	0

I would not consider it settled without evidence: run reverse ETL with 3 unmapped members and require quarantine, 0 new CRM inserts, and 0 use of the warehouse surrogate as an external id.

The destination does not speak surrogate.

I would treat streaming watermarks as a claim about every producer, region, and load window, not about the one that rendered.

A watermark estimates event-time progress: the engine believes most relevant events before T have arrived. It does not universally forbid processing older events. Whether they are dropped, side-output, or used to update prior results depends on the engine, trigger, and allowed-lateness policy.

Concretely, derive watermark progress from observed event time and an explicit out-of-orderness bound, configure the operator's late-data behavior, and merge accepted updates or repair events into the warehouse with the same grain keys.

The reason for that specificity is a failure I have seen: Watermark was wall-clock now minus 10 seconds. A 12-minute producer stall dropped 840,000 events. The hourly metric under-counted 6.2 percent with no repair path.

Wall-clock watermark versus event-time lateness.

Watermark	Events dropped in a 12-minute stall	Hourly under-count
now minus 10 seconds	840,000	6.2 percent
max event time minus 20 minutes plus repair	0	0

I would not consider it settled without evidence: delay events by the stall you actually see in production and require either a watermark that waits or a repair merge that restores the grain.

A watermark is an event-time progress estimate plus an operator policy, not "now."

The useful question for exactly-once versus at-least-once is what a second consumer would compute from the same rows.

A broker's exactly-once guarantee has a defined scope; it does not automatically cover an external warehouse sink. End-to-end exactly-once can coordinate transactional sources, checkpoints, and sinks, while at-least-once delivery can reach the same table outcome through idempotent writes.

Concretely, name the guarantee boundary, then either include the warehouse sink in coordinated transactional checkpoints or give each event a deterministic key and use an idempotent insert, upsert, or MERGE that survives retries.

The reason for that specificity is a failure I have seen: Loads appended on retry. A 3-hour incident retried 14 times. 11.2 million extra facts landed. COUNT(*) moved with the retries; COUNT(DISTINCT user_id) stayed 2.1 million people.

Retries without an idempotent sink.

Sink	Retries	Extra facts	Distinct users
append	14	11,200,000	2.1 million, unchanged
merge on event id	14	0	2.1 million

I would not consider it settled without evidence: replay the same event 14 times and require 1 warehouse row for that grain key.

Exactly-once is an end-to-end boundary, not an adjective inherited from the bus.

I would settle idempotent loads by exercising the failed path against the contract, not against the catalog screenshot.

Idempotence is replace-or-merge by the grain and the partition, not "delete the table and hope." A load is idempotent when a second run of the same input produces the same row set.

Concretely, partition by event date, merge on grain keys, or replace the whole partition from a complete input, and never append a dated dump on top of itself.

The reason for that specificity is a failure I have seen: A backfill appended 2 March again. Row count doubled to 48 million and SUM(order_value) doubled. AVG(order_value) did not halve; exact duplication leaves the average unchanged. The catalog still showed a green unique test because the test used a sample of 1,000 rows.

Append backfill of one day.

Second run	Partition rows	SUM / AVG	Unique test
append	48,000,000	SUM doubled, AVG unchanged	green on 1,000-row sample
replace partition	24,000,000	SUM and AVG unchanged	full partition unique

I would not consider it settled without evidence: run the 2 March load twice in staging and require identical counts, identical checksums, and a unique test over the full partition.

Run it twice; the table must not notice.

The judgement in snapshot versus incremental is which identifier and which time axis the fact is keyed on.

A snapshot is a complete set at a cut. Incremental is a delta. Applying a snapshot as if it were a delta duplicates everything; applying a delta as if it were a snapshot deletes everything not in the batch.

Concretely, tag each ingest with a mode, use snapshot to replace the as-of table, use CDC to apply sequenced changes, and never run both into the same table in the same transaction without a mode check.

The reason for that specificity is a failure I have seen: A full dump was merged as CDC inserts. Row count doubled. Distinct customers stayed 4.1 million because every key already existed. The next day's unique test sampled 500 rows and still passed.

Full dump merged as inserts.

Mode used	Row count	Distinct customers	Unique test sample
dump as CDC insert	doubled	4.1 million	500 rows, passed
dump as snapshot replace	4.1 million rows	4.1 million	full table

I would not consider it settled without evidence: feed a full dump tagged snapshot and a CDC file tagged incremental into the loader and require two different operators.

The file's shape is not the load mode.

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.

Design	Lines with 3 promotions	Phantom GMV
fact row repeated per promotion	revenue ×3	$12.4 million
second FK, still one row per line	promotions truncated, grain held	$0 from fan-out
bridge with allocation	revenue ×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.

I would answer bus matrix by separating storage, schema, and the meaning a consumer is allowed to assume.

A bus matrix states which facts use which conformed dimensions. It is the conformance plan. An ERD can show tables that never share a member, which is how two "customer" columns fail to join.

Concretely, list facts on rows and conformed dimensions on columns, fill only where a foreign key and a member contract exist, and refuse a new fact that ticks a dimension without the mapping test.

The reason for that specificity is a failure I have seen: The ERD showed customer on 8 facts. Only 3 used the enterprise id. A "company dashboard" inner-joined the other 5 and dropped 22 percent of revenue, $31 million in a quarter.

ERD ticks versus actual keys.

Facts drawing customer	Using enterprise id	Revenue dropped by inner join
8 in the ERD	3	22 percent ($31 million)
8 with matrix tests	8	0

I would not consider it settled without evidence: for each ticked cell, join a sample of facts to the dimension on the published key and require a match rate the SLA named.

The matrix is the join contract; the ERD is furniture.

The engineering content of star versus snowflake is the contract and the incompatible write it would reject, not the file format.

A star denormalises conformed attributes onto the dimension for a stable grain. A snowflake is justified when a large, independently changing hierarchy would otherwise type-2 the entire customer row on every org-chart edit.

Concretely, snowflake only the independently versioned hierarchy, keep query marts denormalised from a snapshot of that hierarchy, and measure whether a type-2 explosion actually happened.

The reason for that specificity is a failure I have seen: Customer snowflaked 6 address tables. A 4-hop join for 2.2 billion facts timed out. Analysts built a shadow denormalised table with no SCD, and 14 months of region history vanished.

Six-hop snowflake versus a mart.

Shape	Joins for a region metric	History kept	Query result
6-table snowflake	4 hops on 2.2 billion	intended	timeout
shadow denormalised, no SCD	1	0 for 14 months	fast and wrong

I would not consider it settled without evidence: count type-2 customer versions per month; snowflake only if versions exceed the capacity budget, and still publish a denormalised mart with as-of dates.

Normalise to control versions, then denormalise to query.

Before calling role-playing dimensions done I would write down the consumer, region, or batch window nobody loaded.

Role-playing is one date dimension viewed through several foreign keys. Three physical date tables that drift calendars are a conformance failure.

Concretely, build one date dimension, expose roles as views or renamed keys on the fact, and test that the same calendar_day_id means the same fiscal week in every role.

The reason for that specificity is a failure I have seen: Three date tables were loaded from three spreadsheets. Fiscal week 12 differed by 3 days across roles. A ship-versus-order report was 9 percent off for a quarter, $6.1 million of "late" that was a calendar bug.

Three date tables, three fiscal weeks.

Role table	Fiscal week 12 start	"Late" GMV from the mismatch
order_date_dim	Monday	—
ship_date_dim	Thursday	$6.1 million
single date_dim, three keys	Monday	$0

I would not consider it settled without evidence: pick one calendar_day_id and require identical fiscal attributes through every role view.

Roles are aliases; the calendar is one table.

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

An accumulated snapshot is one row per pipeline instance, with a timestamp and lag for each milestone. The grain is the instance, not the milestone event. Event facts still exist for the milestones; they are not a substitute for the snapshot if the question is "where is this order now."

Concretely, key on the pipeline instance, update the same row as milestones arrive, store milestone timestamps, and keep a separate transaction fact if you need to count events.

The reason for that specificity is a failure I have seen: Milestones were only in an event fact. "Orders stuck in packing" required a 5-way self-join. The query used MAX(event_time) and classified 12 percent of delivered orders as stuck for 3 weeks.

Event-only versus accumulated snapshot.

Design	Query	Delivered orders marked stuck
event fact only	5-way self-join	12 percent for 3 weeks
accumulated snapshot	1 row per instance	0

I would not consider it settled without evidence: advance a milestone and require the snapshot row to show the new timestamp without duplicating the instance.

Pipeline state is a row you update, not a join you hope.

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.

Source	Missing receipt	Units oversold	Days
replay of 11 months of movements	1	8,400	6
daily snapshot plus reconciliation	caught at close	0	0

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.

This is an area where a green dbt test and a held meaning are different observations.

A tombstone is an explicit delete with the grain key and a change sequence. Inferring deletes from a missing row in a partial dump turns a failed extract into a mass erasure.

Concretely, require a delete event or a full-snapshot reconcile job that is tagged complete, and refuse to treat a truncated extract as a set of tombstones.

The reason for that specificity is a failure I have seen: A 40-percent extract was applied as "missing means deleted." 1.6 million customers vanished for 2 days. Reactivation CDC then reinserted them as new surrogates, splitting 1.6 million histories.

Partial dump treated as deletes.

Input	Rows treated as deleted	Histories split on reinsert
40-percent dump	1,600,000	1,600,000
tombstone plus complete snapshot	1 intended	0

I would not consider it settled without evidence: feed a partial dump without a completeness flag and require 0 deletes; then feed a tombstone and require the current row to close.

Missing is not deleted until the extract is complete or the tombstone arrives.

My answer to Iceberg hidden partitioning begins with which semantic claim is being made, not which physical table exists.

Hidden partitioning lets a compatible engine project predicates on order_ts onto days(order_ts). Pruning may fail when an engine cannot translate a different expression or when timezone conversion no longer maps cleanly to the UTC day transform; that behavior must be measured per engine.

Concretely, partition on the timestamp consumers filter, document timezone semantics, and compare files scanned for the native timestamp range with the dashboard's converted expression in every production engine.

The reason for that specificity is a failure I have seen: In the deployed engine, dashboards used DATE(order_ts AT TIME ZONE 'US/Pacific') and predicate projection failed. Each 1-day query scanned 18,400 files, 6.2 TB, and cost $1,140; at 14 runs a day that was $15,960 daily.

Two predicates, one partition spec.

Predicate	Files scanned per run	Data scanned per run	Cost at 14 runs/day
days(order_ts) match	24	42 GB	$80
DATE at US/Pacific in this engine	18,400	6.2 TB	$15,960

I would not consider it settled without evidence: run the documented filter and the dashboard's expression, and require the dashboard to be rewritten until files scanned match the documented filter within 10 percent.

Partition prune follows the expression, not the intent.

I would treat compaction versus time travel as a claim about every producer, region, and load window, not about the one that rendered.

Compaction rewrites small files into new files but does not itself expire Iceberg snapshots. A separate snapshot-expiry action controls whether old snapshots and their files remain recoverable. Bundling aggressive expiry into the same maintenance workflow is a silent retention change.

Concretely, set snapshot expiry explicitly, run compaction without expiring snapshots still inside the recovery window, and re-run erasure proofs after compaction and orphan-file cleanup.

The reason for that specificity is a failure I have seen: An hourly maintenance workflow compacted files and then retained only 2 snapshots. A bad load was discovered 6 hours later. Rollback had nothing to apply. 90 million rows of a wrong schema stayed live for 3 extra days.

Aggressive snapshot expiry.

Snapshots kept	Hours until bad load found	Rollback possible	Wrong-schema rows remaining
2 (hourly)	6	no	90,000,000 for 3 days
48 (hourly, 48-hour SLA)	6	yes	0 after rollback

I would not consider it settled without evidence: compact, then attempt rollback to a snapshot 6 hours old, and require that snapshot to still exist if 6 hours is inside the recovery SLA.

Compaction is a file rewrite; expiry is a policy.

The useful question for Parquet versus Avro in the lake is what a second consumer would compute from the same rows.

Avro is row-oriented and honest about evolving records. Parquet is columnar and honest about scan cost. Nested evolution is not inherently lossy in Parquet. A frozen projection, writer schema, or SELECT list drops nested fields in any format.

Concretely, keep the raw landing in a row format with a registry, write curated Parquet/Iceberg for certified products, and refuse BI on the raw landing as if it were a mart.

The reason for that specificity is a failure I have seen: Raw landing used Parquet with a frozen projection. Nested fields outside that projection were dropped at write. 11 percent of events lost a discount object for 7 weeks, $4.6 million of unattributable promotions. The same freeze on Avro would have dropped them too.

Dropped nested fields from a frozen projection.

Landing	Nested discount retained	Unattributable promotions
Parquet, frozen projection	0 for 11 percent of events	$4.6 million over 7 weeks
Parquet or Avro with evolving projection	100 percent in raw	$0 once projected

I would not consider it settled without evidence: add a nested field in the producer and require the landing format to retain it, then require the curated Parquet job to project or explicitly drop it in a contract.

Blame the frozen projection, not Parquet as a format.

I would settle table format choice by exercising the failed path against the contract, not against the catalog screenshot.

The choice is catalog integration, partition evolution, concurrent writers, and time-travel retention that matches erasure. A format that the query engine cannot evolve will freeze the grain in files you cannot rewrite.

Concretely, score the engines that must read and write, test concurrent MERGE, test partition evolution, and test snapshot expiry against the erasure SLA before standardising.

The reason for that specificity is a failure I have seen: Hudi was picked from a talk. The warehouse engine could not prune on the hoodie keys. 2.8 billion rows were scanned for a 1-day filter. The team copied data into Iceberg, doubling storage 1.4 PB for 4 months.

A format the warehouse could not prune.

Format in the actual engine	1-day files pruned	Extra copies	Extra storage
Hudi, weak prune	no	1	1.4 PB for 4 months
Iceberg, tested MERGE	yes	0	0

I would not consider it settled without evidence: run the 1-day filter and the concurrent MERGE on a 100-million-row fixture in each candidate format with the actual engines.

The engine you have is the format you can operate.

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 grain	Duplicate customer_id rows	Consumers that trusted the text
customer_id	6.1 percent	all 9 certified dashboards
tested unique (customer_id, as_of_date)	0	9

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.

Where candidates lose the interview on dbt tests versus declared grain is calling the lakehouse layout the model.

not_null on a surrogate always passes after the warehouse minted it. unique on a surrogate always passes. Grain is uniqueness of the business keys. Green tests on surrogates are a tautology.

Concretely, test uniqueness on the published business keys, test that additive measures do not mix grains, and never treat surrogate uniqueness as grain.

The reason for that specificity is a failure I have seen: unique(order_sk) was green on 12 million rows. Business key (order_id, line_id) had 410,000 duplicates. Line revenue was double-counted $9.2 million.

Green unique on the surrogate.

Test	Result	Duplicate business keys	Extra revenue
unique(order_sk)	green	410,000	$9.2 million
unique(order_id, line_id)	would fail	410,000	$0 if blocked

I would not consider it settled without evidence: insert two rows with the same business key and different surrogates, and require the grain test to fail while unique(sk) still passes.

The surrogate is unique by construction; the grain is not.

I would answer metric store versus warehouse table by separating storage, schema, and the meaning a consumer is allowed to assume.

The metric store is a query API over a documented grain. It is not a second fact. If it computes a different join or filter than the certified fact, you now have two revenues.

Concretely, bind each metric to one fact grain and one filter set, test the metric SQL against a fixture of that fact, and refuse a metric that joins a second grain to "fix" a number.

The reason for that specificity is a failure I have seen: The metric joined fct_order and fct_refund with an inner join. 8 percent of orders had no refund row. Metric revenue was 8 percent low versus the fact, $6.7 million, for 5 weeks.

Inner join in the metric layer.

Definition	Orders dropped	Revenue versus fact
inner join to refunds	8 percent	-$6.7 million for 5 weeks
fact grain plus optional refund	0	$0

I would not consider it settled without evidence: compare the metric to the certified fact on a 30-day fixture and require 0 difference, or a documented exclusion list.

A metric that disagrees with its fact is another fact.

The engineering content of slowly changing FX rates is the contract and the incompatible write it would reject, not the file format.

Transaction conversion uses the rate on the event date (or the contracted rate). Latest-rate conversion rewrites history every time the rate table updates.

Concretely, store original amount and currency, join rates on event date, snapshot the rate used, and forbid a view that joins MAX(rate_date).

The reason for that specificity is a failure I have seen: A view used the latest USDJPY. A 6-yen move restated 11 months of JP revenue by 5.4 percent, $14 million, overnight, with 0 new invoices.

Latest rate versus event-date rate.

Rate used	New invoices	JP revenue move	Yen move
latest	0	$14 million (5.4 percent)	6
event date	0	$0	6

I would not consider it settled without evidence: change today's rate and require last year's converted amounts to stay fixed.

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

Before calling fiscal calendar done I would write down the consumer, region, or batch window nobody loaded.

Fiscal attributes and comparable-period mappings must live on the conformed date dimension. Subtracting 365 days shifts weekdays by one day in an ordinary year and two across a leap day; a 53rd fiscal week also needs an explicit finance-approved comparison rule.

Concretely, load fiscal year, week, period-close flags, and prior-comparable-day keys from finance, encode how week 53 rolls or compares, and compute YOY from those mappings rather than calendar arithmetic.

The reason for that specificity is a failure I have seen: YOY used date - 365. The shifted weekdays crossed 4-5-4 boundaries, and week 53 had no declared comparator. Booked-versus-prior was 11 percent off, $8.3 million, in the first affected period.

Gregorian YOY on a 53-week 4-5-4 year.

Comparison	Alignment	First-period error	Dollars
date minus 365	weekday shifted; week 53 undefined	11 percent	$8.3 million
finance comparable-day key	approved fiscal comparator	0	$0

I would not consider it settled without evidence: compute YOY across ordinary, leap, and 53-week fiscal fixtures and require finance-defined comparable-day keys rather than calendar_date - 365.

Minus 365 is not a fiscal year.

The first thing I would establish about slowly changing org hierarchy is which grain and which keys the number actually uses.

Those are two products: as-was and as-is. As-was joins the hierarchy version effective at event time. As-is maps historical facts through a current rollup. One column cannot be both.

Concretely, version the hierarchy type-2, expose as_was and as_is in named metrics, and refuse a dashboard that does not say which.

The reason for that specificity is a failure I have seen: A type-1 org overwrite moved $22 million of Q1 bookings into a new division in Q2 reports. Q1 close packs no longer matched the warehouse.

Type-1 org overwrite.

Report	Q1 bookings location	Match to Q1 close
type-1 as-is only	new division	no, $22 million moved
as-was plus as-is	old plus new, named	yes for as-was

I would not consider it settled without evidence: reorg a team and require as-was Q1 to stay put while as-is Q1 under the new parent is a separate metric.

As-was and as-is are two grains of hierarchy.

I would start customer 360 identity from the contract and the consumer, not from the warehouse diagram.

360 is a graph of identifiers with a survivorship rule, not a wide table of outer joins on email. Email is not unique and not stable. Behaviour joins on email create people who never existed.

Concretely, collect identifier types with confidence, resolve to an enterprise person id with a reviewed rule, and join facts only on that id.

The reason for that specificity is a failure I have seen: 360 joined on email. 9 percent of rows were shared inboxes. One "person" accumulated 14 employees' purchases, $1.8 million, and was targeted as a VIP.

Email as the 360 key.

Key	Shared inboxes	Employees collapsed	VIP spend assigned
email	9 percent	14 into 1	$1.8 million
enterprise person id	0 as people	0	$0 false VIP

I would not consider it settled without evidence: include a shared inbox in the fixture and require it not to become a person that owns employee purchases.

A 360 join on email is a group inbox with a credit card.

This is an area where a green dbt test and a held meaning are different observations.

A hash of a low-entropy identifier is reversible by guessing. A token from a vault is random and only the vault maps back. Tokens remain personal data while they are linkable across records, systems, or the vault; deleting only the vault row is insufficient when downstream tokenised rows still single out the person.

Concretely, forbid raw and unsalted hashes of email or phone in the lake, issue scoped tokens from a vault, and make erasure delete the vault mapping plus delete downstream tokens or unlink them only after testing that remaining attributes cannot reasonably identify or single out the person.

The reason for that specificity is a failure I have seen: SHA-256 email hashes were joined to a 180-million-email breach list. 62 percent had dictionary matches in 4 hours. The catalog still said "pseudonymised." Vault tokens had 0 dictionary matches and were still personal data through the vault map.

SHA-256 email versus vault token.

Method	Dictionary matches from a 180M list	Still personal data
unsalted SHA-256	62 percent in 4 hours	yes
vault token	0	yes, via vault or joins

I would not consider it settled without evidence: run a common-email rainbow against the lake column and require 0 dictionary matches for a vault token, versus documenting residual risk for a hash; do not call 0 matches "0 re-identified."

A guessable hash is still the email; a linkable token is still personal data.

My answer to erasure versus aggregates begins with which semantic claim is being made, not which physical table exists.

Aggregates that cannot be inverted to a person can remain if the DPIA says so. Small cells and residual identifiers cannot. Architecture is a cell-suppression rule, not a hope that COUNT(*) is anonymous.

Concretely, set a minimum cell size, suppress or noise small cells, store aggregates without keys, and test that an erased id cannot be recovered by subtracting successive aggregates.

The reason for that specificity is a failure I have seen: Daily counts by company_name remained. 1,140 companies had a count of 1. Erased employees were still named by the company cell for 9 months.

Company cells of size 1.

Aggregate	Cells of size 1	Erased people still pointed at
count by company_name	1,140	1,140 for 9 months
suppress below 20	0 published	0

I would not consider it settled without evidence: erase one person in a cell of size 1 and require the published aggregate to suppress that cell.

A count of one is a name.

I would treat cross-border replication as a claim about every producer, region, and load window, not about the one that rendered.

Encryption at rest is a credential away from plaintext in that country. Residency and transfer rules care about where the bytes can be decrypted, by whom, and whether identifiers are in the replica.

Concretely, keep identifying payloads regional, replicate tokens or aggregates, and document the legal transfer for any identifier that does cross.

The reason for that specificity is a failure I have seen: A US replica held EU emails encrypted with a key the US ops role could use. 3.6 million emails were decryptable in the US. The transfer impact assessment had said "encrypted replica, no access."

Encrypted replica with a usable key.

Replica	Operator can decrypt	EU emails in the US
at-rest encryption, US key	yes	3,600,000
regional vault, tokens only	no	0

I would not consider it settled without evidence: attempt decrypt as the replica region's operator and require denial for EU identifiers, or a named transfer record.

A key in the same region as the replica is access.

The useful question for freshness SLO is what a second consumer would compute from the same rows.

Freshness is when the last source event in the closed interval is visible to consumers, not when the orchestrator marked success. A job that succeeded on a partial extract meets the clock and misses the data.

Concretely, measure max(event_time) in the loaded partition against the close, plus source_count versus loaded_count, and fail the SLO if either misses.

The reason for that specificity is a failure I have seen: Airflow succeeded at 05:58. The extract had stopped at 22:10. 8 hours of orders, 96,000 rows, were missing. The freshness tile stayed green for 12 days.

Success at 05:58 on a 22:10 extract.

Signal	Value	SLO
Airflow success	05:58	green
max(event_time)	22:10 prior day	miss, 96,000 rows

I would not consider it settled without evidence: succeed a job on a truncated extract and require the freshness SLO to fail on max(event_time) and on counts.

A green orchestrator is not a closed day.

I would settle completeness contracts by exercising the failed path against the contract, not against the catalog screenshot.

Completeness needs source control totals with a documented filter. Equal row counts alone are insufficient because duplicates can replace omissions; reconcile stable key sets, keyed checksums, or equivalent domain totals as well as counts.

Concretely, publish source and loaded counts plus a stable-key checksum or key-set reconciliation, define allowed variance by control, and fail promotion when any required control differs.

The reason for that specificity is a failure I have seen: A timezone filter dropped 4.8 percent of APAC rows while retries duplicated the same number elsewhere, so total row counts matched. APAC revenue was quiet for 21 days, $3.1 million, until keyed reconciliation exposed the substitution.

APAC filter with a stable warehouse count.

Check	Count gap	Key-set gap	Revenue in the hole
source versus loaded count	0	unmeasured	$3.1 million
count plus keyed reconciliation	0	4.8 percent	$0 published

I would not consider it settled without evidence: drop 5 percent of source keys and duplicate another 5 percent in a fixture, then require the completeness contract to fail despite equal row counts.

Stable warehouse volume can be a stable hole.

The judgement in uniqueness contracts is which identifier and which time axis the fact is keyed on.

Duplicates cluster. A sample that misses the cluster passes. Certified uniqueness is a full-key count versus distinct count on the load's grain.

Concretely, compute count and count-distinct on the business keys for the partition, fail if they differ, and never sample the uniqueness test.

The reason for that specificity is a failure I have seen: A 1-percent sample unique test was green. Duplicates sat in 2 of 400 files, 180,000 rows. A join to payments exploded to 14 billion intermediate rows and timed out the close.

Duplicates in 2 of 400 files.

Test	Result	Duplicate rows	Close
1-percent sample unique	green	180,000	timeout
full partition count vs distinct	fail	180,000	blocked

I would not consider it settled without evidence: place duplicates in 1 file of 400 and require a sampled test to be illegal in CI, with the full-partition test failing.

Uniqueness that is sampled is a rumour.

Where candidates lose the interview on referential integrity across domains is calling the lakehouse layout the model.

Cross-domain foreign keys are a contract: either the parent is present, or the child uses an inferred member. Silent null keys create orphan facts that every inner join drops.

Concretely, check parent existence or infer, publish orphan rates, and fail when orphans exceed the SLA.

The reason for that specificity is a failure I have seen: Orders loaded with 0 customer_sk when the customer job lagged. Inner-join dashboards dropped 7.4 percent of GMV, $5.9 million, every Monday for 8 weeks.

Monday customer lag.

Child handling	Orphan orders	GMV dropped Mondays
null customer_sk	7.4 percent	$5.9 million for 8 weeks
inferred member	0 inner-join drops	$0

I would not consider it settled without evidence: lag the parent product and require either inferred members or a failed child load, not null keys.

A null foreign key is an inner-join tax.

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.

Dashboard	Cancel rate	Dollar gap
A	2.1 percent	—
B	6.8 percent	$11 million
certified fact, one reducer	one 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.

The engineering content of transactional outbox is the contract and the incompatible write it would reject, not the file format.

Without an outbox, the database commit and the bus publish can diverge. Retries can duplicate messages; skipped publishes lose facts. The outbox commits both intents in one transaction.

Concretely, write the fact change and the outbox row together, publish from the outbox with an idempotency key, and ingest that key in the warehouse.

The reason for that specificity is a failure I have seen: A process crashed after commit and before publish. 27,000 orders existed in OLTP and not in the warehouse for 16 hours. A later retry duplicated 4,100 of them when a second path fired.

Crash between commit and publish.

Path	OLTP orders missing in warehouse	Later duplicates
publish after commit	27,000 for 16 hours	4,100
transactional outbox	0 after poll	0

I would not consider it settled without evidence: crash between commit and publish in a test and require the outbox poller to emit exactly the missing events with keys the warehouse merges.

Retry without a transactional outbox is a second writer.

Before calling schema registry subjects done I would write down the consumer, region, or batch window nobody loaded.

Schema ids identify schema bytes globally, and wire-format consumers normally resolve the writer schema from the id embedded in each payload. Subjects scope registration versions and compatibility checks. Use separate subjects for independent evolution, or intentionally share one only when the topics must obey the same compatibility lifecycle.

Concretely, choose the subject-name strategy from the intended evolution boundary, resolve each payload's embedded writer-schema id, configure the consumer's reader schema explicitly, and keep registration-time compatibility checks separate from runtime reader selection.

The reason for that specificity is a failure I have seen: An orders consumer was configured to ignore the payload's writer schema and use the latest shipments subject version. After shipments evolved, 3.4 million order records stopped decoding for 9 hours. The embedded schema id still correctly identified the orders writer schema.

Global ids, subject-scoped compatibility.

Orders consumer strategy	After shipments evolves	Records failing
force latest shipments reader	decode fail	3,400,000 for 9 hours
embedded writer id plus orders reader policy	still decodes	0

I would not consider it settled without evidence: evolve shipments independently and require either separate subject histories or a deliberate shared-subject rejection, while the orders consumer keeps resolving its embedded writer-schema id.

Ids identify writer schemas; subjects govern registration histories.

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

Avro supplies a missing field from the reader schema's default, not from a default the writer stored. The warehouse column nullability is a second contract. If the warehouse forbids null but the reader default is null, every historical replay fails or every new field is silently backfilled with a lie.

Concretely, align the reader-schema default with warehouse nullability, or land null and have an explicit backfill; never declare NOT NULL on a field whose reader default is null.

The reason for that specificity is a failure I have seen: channel's reader default was null while the table was NOT NULL. Replay of 14 months aborted. A hotfix coerced null to "web", mis-attributing 22 percent of historical orders.

Null reader default into a NOT NULL column.

Handling	Replay	Historical orders coerced to "web"
NOT NULL plus null reader default	abort then hotfix	22 percent
nullable plus explicit backfill	completes	0 silent lies

I would not consider it settled without evidence: replay old files through the new reader schema and require either nullable columns or a non-null reader default that the business signed.

The reader default is the historical fill; the writer never had the field to default.

I would start merge-on-read versus copy-on-write from the contract and the consumer, not from the warehouse diagram.

Copy-on-write rewrites files on write so reads are cheap. Merge-on-read writes deltas cheaply and pays at read. A dashboard farm on MOR without compaction pays the CDC cost on every query.

Concretely, use COW for dashboard-heavy tables, MOR for write-heavy CDC with a compaction SLO, and measure read amplification after 24 hours of uncompacted deltas.

The reason for that specificity is a failure I have seen: MOR sat 36 hours without compact. A 12-dashboard pack scanned 4,800 delete files per query. Slot-hours rose 9× and the pack missed the 07:00 board.

Uncompacted MOR under dashboards.

Mode	Delete files per query	Slot-hours	Board pack
MOR, 36 hours	4,800	9×	missed 07:00
COW or compacted MOR	tens	1×	on time

I would not consider it settled without evidence: apply 24 hours of CDC, query the certified dashboard, and require compaction before the pack if read amplification exceeds the budget.

You pay on write or on read; unpaid is still a bill.

This is an area where a green dbt test and a held meaning are different observations.

Partitioning is a coarse prune on a low-cardinality clock. Clustering orders files on a high-cardinality key so a point lookup does not read the whole day. Partitioning on customer_id is usually a file explosion.

Concretely, partition on the time grain of retention and overwrite, cluster on the selective equality key, and reject customer_id as a partition column once distinct values exceed file-budget.

The reason for that specificity is a failure I have seen: Partitioned by customer_id, 4.1 million partitions. Planning took 11 minutes. A 1-day query opened 4.1 million directory listings. The job was killed, 0 rows.

Partition by customer_id.

Layout	Partitions	Planning	1-day 1-customer query
partition customer_id	4,100,000	11 minutes	killed
partition day, cluster customer_id	~400 days	seconds	files in one day

I would not consider it settled without evidence: compare planning time and files opened for a 1-customer 1-day query under day-partition plus cluster versus customer partitions.

Partition time; cluster the selective key.

My answer to slowly changing geography begins with which semantic claim is being made, not which physical table exists.

Store location is type-2 if region metrics must freeze. Overwriting lat/long restates every prior sale into the new region, which is a lie about where the sale happened.

Concretely, version store location, join sales as-of sale time, and offer a current-location mapping as a separate product.

The reason for that specificity is a failure I have seen: A store moved 8 km across a region boundary. Type-1 overwrite shifted $6.4 million of prior-year sales. The losing region missed a bonus threshold by 0.3 percent.

Store move across a region line.

Handling	Prior-year sales moved	Bonus impact
type-1 location	$6.4 million	missed by 0.3 percent
type-2 as-was	$0	none

I would not consider it settled without evidence: move a store and require prior-year as-was region to stay, with as-is as a named alternative.

The sale happened at the old postcode.

I would treat GDPR versus backups as a claim about every producer, region, and load window, not about the one that rendered.

Backups are copies. Erasure SLAs either wait for backup expiry, use encrypted backups whose keys can be dropped per subject (rare), or restore-and-re-erase. "We deleted prod" is not an answer while a 35-day backup exists.

Concretely, set backup retention to the erasure SLA or document the restore-and-erase procedure with a drill date, and refuse unbounded warehouse dumps to object storage.

The reason for that specificity is a failure I have seen: Prod was erased in 7 days. Dumps sat 90 days. A restore for a bug 40 days later reintroduced 12,400 erased subjects into a sandbox that analysts could query.

Erase in prod, restore from dump.

Copy	Erased subjects present	Days
prod after erasure	0	7
90-day dump restored at day 40	12,400	until re-erased

I would not consider it settled without evidence: erase, restore from the oldest backup still in retention, and require the restored copy to apply erasure before any analyst role can select.

A backup is a warehouse with a delay.

The useful question for federated governance is what a second consumer would compute from the same rows.

Federated governance distributes the work of classifying and contracting. It centralises the vocabulary of classes, the enforcement hooks, and the audit. Local labels with no shared meaning are 12 privacy programs.

Concretely, publish a shared class list and enforcement APIs, let domains apply classes, and reject a domain class that is not in the list.

The reason for that specificity is a failure I have seen: One domain used "confidential" to mean salary, another to mean anything logged in. 2,200 salary columns were queryable by a role that was allowed "confidential" in the second domain.

Two meanings of confidential.

Domain	confidential means	Salary columns exposed
HR	salary	intended
logs	anything in logs	2,200 via the logs role

I would not consider it settled without evidence: attempt to register a local class name and require a mapping to the central list before enforcement can attach.

Federation without a vocabulary is a thesaurus of leaks.

I would settle platform versus domain pipelines by exercising the failed path against the contract, not against the catalog screenshot.

The platform owns identity, classification enforcement, registry, and lineage emission. Domains own product logic. Custom Spark that skips the loader skips the contract.

Concretely, require every writer to call the loader or to emit the same contract checks, and block cluster credentials that can write to certified schemas without those checks.

The reason for that specificity is a failure I have seen: A domain Spark job wrote with raw S3 permissions. It skipped uniqueness and PII masks. 640,000 raw emails landed in a certified schema for 5 days.

Raw S3 into a certified schema.

Writer	Uniqueness	Masks	Raw emails
platform loader	enforced	enforced	0
domain Spark, raw S3	skipped	skipped	640,000 for 5 days

I would not consider it settled without evidence: attempt a write to a certified schema with raw credentials and require a deny.

Bypass of the loader is bypass of the contract.

The judgement in job lineage versus dataset lineage is which identifier and which time axis the fact is keyed on.

Job lineage names processes. Dataset and column lineage name fields. A job that reads 80 columns and writes 80 can hide that one output column was a literal.

Concretely, require column facets on certified products, and fail a steward query that can only name a job.

The reason for that specificity is a failure I have seen: The graph showed dbt_orders reading src_orders. revenue_usd was a hardcoded 0 after a refactor. Job lineage stayed identical. $0 GMV published for 2 days.

Job graph after a literal refactor.

Artefact	What it showed	GMV
job lineage	dbt_orders <- src_orders	looked fine
column lineage	revenue_usd <- literal 0	$0 for 2 days

I would not consider it settled without evidence: replace a column with a literal in a fixture and require column lineage to show no source field.

A job edge does not name a column.

Where candidates lose the interview on column lineage completeness is calling the lakehouse layout the model.

The uncovered 30 percent is where silent literals, UDFs, and notebook writes live. Completeness is measured on certified columns, and the missing set is a backlog with owners, not a coverage trophy.

Concretely, list certified columns without a source field, page owners, and block new certified columns that ship without a facet.

The reason for that specificity is a failure I have seen: Coverage was 70 percent and reported as a win. The missing 30 percent included iban. A UDF logged 18,000 IBANs to stdout for 11 days.

Coverage trophy versus IBAN.

Certified columns with facets	IBAN source known	IBANs in logs
70 percent	no	18,000 for 11 days
100 percent of certified	yes	0

I would not consider it settled without evidence: add a certified column without a column facet and require the release to fail.

70 percent lineage is 30 percent unaccountable columns.

I would answer quality SLA versus quality dashboard by separating storage, schema, and the meaning a consumer is allowed to assume.

Dashboards average tests. SLAs are per product, per check, per window. An average of 99 percent pass can hide one certified product at 0 percent.

Concretely, page on the certified product's named checks, not on an estate average, and refuse a single green tile as evidence.

The reason for that specificity is a failure I have seen: Estate pass rate was 99.2 percent. The payments product's uniqueness was red for 8 days. The tile stayed green. $2.2 million duplicate payouts shipped.

99.2 percent estate, red payments.

Tile	Payments uniqueness	Duplicate payouts
estate 99.2 percent	red 8 days	$2.2 million
per-product SLA	pages day 0	$0

I would not consider it settled without evidence: fail one certified product's uniqueness while 99 percent of other tests pass, and require a page and a downstream skip.

An estate average is not a product SLA.

The engineering content of late data after watermark close is the contract and the incompatible write it would reject, not the file format.

Closed windows need a versioned repair: a new snapshot or a compensating fact with the same grain keys. Dropping is a documented loss; silent drop is a hole; reopening without versioning duplicates.

Concretely, write late events to a repair partition, merge by grain, bump a data version, and notify consumers of the revision.

The reason for that specificity is a failure I have seen: Late events appended into the closed hour. Unique keys duplicated. The hour's revenue rose 4 percent on a restatement nobody versioned, and 3 downstream cubes double-counted.

Append into a closed hour.

Handling	Duplicate keys	Unversioned restatement
append	20,000	+4 percent, 3 cubes
merge plus version	0	named revision

I would not consider it settled without evidence: land 20,000 late events and require a version bump plus merge, not an append into the closed files.

A closed hour can be revised; it cannot be silently appended.

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.

Window	Visits split at clock	Users glued	Conversion error
30-minute tumble keyed by user	22 percent	0 from tumbling	6 points from splits
30-minute tumble keyed by IP	also splits	9 percent NAT merge	mixed
30-minute inactivity session keyed by user	0	0	0

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.

The first thing I would establish about idle-source watermarks is which grain and which keys the number actually uses.

Windows stall on the minimum of per-partition watermark progress, not on a global max event time. A silent partition holds that min. A global max would advance with the busy partitions and ignore the idle one. Idle sources need a timeout that advances the lagging partition with a recorded gap.

Concretely, set idle timeout, emit a gap metric, and advance watermarks per partition so a silent region does not stall a busy one.

The reason for that specificity is a failure I have seen: A canary producer died. The watermark was the min of per-partition progress, so 14 busy partitions delayed 2.5 hours. The freshness SLO on those products failed even though their events were on time. A global-max watermark would have closed the busy partitions.

Dead canary, min-of-partitions watermark.

Watermark	Busy partitions delayed	Events actually late
min of per-partition progress	14 for 2.5 hours	0
global max event time	0	0
per-partition plus idle timeout	0	0

I would not consider it settled without evidence: silence one partition for 40 minutes and require other partitions' windows to close on their own event times once idle timeout fires.

An idle source must not freeze the estate.

I would start sink idempotency keys from the contract and the consumer, not from the warehouse diagram.

A sink key can be a hash of (topic, partition, offset) or a hash of payload fields the producer guarantees stable. Without a key, retries are new facts. Kafka log compaction does not renumber remaining offsets; republication to a new topic or repartitioning does. Offset keys fail after those operations, not after compaction.

Concretely, choose a key that survives the retry path you actually have, persist it, and merge; do not hash a timestamp the producer refreshes.

The reason for that specificity is a failure I have seen: Keys hashed produced_at, which the retry client refreshed. 3 retries made 3 facts. 8.4 million extras on a Monday incident. A later compact of the topic left offsets unchanged; a repartition to 24 partitions did not.

Hash of refreshed produced_at.

Key	Retries	Extra facts	Offsets after compact
hash(produced_at)	3	8,400,000	unchanged
hash(topic, partition, offset)	3	0 until republish/repartition	unchanged

I would not consider it settled without evidence: retry with a refreshed timestamp and require 1 row if the business payload is the same.

A key that changes on retry is not a key; compaction is not a new offset.

This is an area where a green dbt test and a held meaning are different observations.

At-least-once is a delivery adjective. The fact table still owes uniqueness on the grain. Consumers are not the dedupe layer.

Concretely, enforce unique grain keys, make loads merge, and publish a duplicate-arrival metric so transport noise is visible.

The reason for that specificity is a failure I have seen: Dedupe was "the dashboard uses DISTINCT." Two dashboards did not. User counts differed by 13 percent, 2.4 million people.

Dedupe left to DISTINCT.

Consumer	Dedupe	User count
dashboard A	DISTINCT	baseline
dashboard B	none	+13 percent (2.4 million)
certified fact unique	merge	1 number

I would not consider it settled without evidence: deliver the same event twice and require 1 certified fact row, even if a raw landing table has 2.

DISTINCT in a dashboard is not a grain.

My answer to partition overwrite versus merge begins with which semantic claim is being made, not which physical table exists.

Overwrite replaces the whole partition from a complete snapshot. MERGE upserts keys. Overwriting a partition that also received midday CDC deletes the CDC unless the overwrite input includes it.

Concretely, use overwrite only with a complete input for that partition, use MERGE for partials, and lock mixed-mode partitions.

The reason for that specificity is a failure I have seen: A snapshot overwrite at 20:00 replaced 2 March and dropped 6 hours of CDC, 140,000 orders. The snapshot's max time was 14:00.

20:00 overwrite from a 14:00 dump.

Operator	CDC after 14:00	Orders dropped
overwrite	deleted	140,000
MERGE of dump plus CDC	kept	0

I would not consider it settled without evidence: apply CDC after 14:00 then overwrite from a 14:00 snapshot, and require the job to fail because the input is not complete.

Overwrite means the input is the whole day.

I would treat backfill versus current state as a claim about every producer, region, and load window, not about the one that rendered.

Backfill is a bounded rewrite of historical partitions. Current loads are a different writer. Two writers on today's partition without a lock duplicate or delete now.

Concretely, lock partitions being backfilled, exclude "today" from historical rewrites, and run current incremental as the only writer on the open partition.

The reason for that specificity is a failure I have seen: A backfill included CURRENT_DATE. It raced the incremental job. 31 percent of today's keys duplicated, 69 percent were overwritten with yesterday's attributes for 4 hours.

Backfill racing today's incremental.

Writers on today	Duplicate keys	Overwritten with yesterday
backfill plus incremental	31 percent	69 percent for 4 hours
locked historical partitions only	0	0

I would not consider it settled without evidence: start a backfill and an incremental on the same partition and require a lock that serialises or rejects one.

History rewrite does not own today.

The useful question for restore versus erasure is what a second consumer would compute from the same rows.

A restore reintroduces every row in that snapshot, including people erased after it. Restore is a replay of old personal data unless erasure is applied again.

Concretely, after restore, re-run erasure for subjects erased between the snapshot and now, and block analyst access until that job succeeds.

The reason for that specificity is a failure I have seen: Rollback to snapshot 1802 undid a bad schema and brought back 3,100 erased subjects for 2 days of analyst queries.

Time-travel rollback after erasures.

Step	Erased subjects queryable
after erasure	0
restore snapshot 1802	3,100 for 2 days
restore then re-erase	0

I would not consider it settled without evidence: erase, snapshot, restore, and require 0 identifying rows for the erased ids before SELECT is granted.

Rollback is not a privacy no-op.

I would settle train/serve feature skew by exercising the failed path against the contract, not against the catalog screenshot.

Skew is different grain, different clock, or different default. Matching names are not matching contracts. Architecture publishes one feature definition computed the same way, or it documents the allowed lag.

Concretely, generate serving features from the same code or SQL as training, pin versions, and require each feature value to match within a stated tolerance for a production id, not merely that top-decile sets overlap.

The reason for that specificity is a failure I have seen: Training used 30-day trailing orders including today; serving used 30 days excluding an unclosed day. Top-decile overlap was 54 percent, which still hid per-feature drift: order_count_30d differed on 71 percent of ids. Ranked campaigns missed 46 percent of the intended people.

30-day window, two clocks.

Recipe	Includes unclosed day	Top-decile overlap	Per-feature match
training	yes	—	—
serving	no	54 percent	29 percent of ids
shared definition	one clock	not the gate	99 percent within tolerance

I would not consider it settled without evidence: compute both recipes for 1,000 ids and require exact equality or a named tolerance on every feature; refuse to ship on top-decile overlap alone.

Same name, different clock, different feature.

The judgement in reverse ETL of PII is which identifier and which time axis the fact is keyed on.

Reverse ETL is an egress. Classification, lawful basis, and identifier minimisation apply. A warehouse role that can SELECT a segment can still be forbidden from sending emails to a processor.

Concretely, egress only allowed identifiers (often a platform token), record the processor and purpose, and block raw email in the reverse-ETL connector.

The reason for that specificity is a failure I have seen: The connector sent email plus spend. The ad platform became a new copy of 1.9 million EU emails. No transfer record existed.

Email plus spend to an ad platform.

Payload	EU emails copied	Transfer record
email plus spend	1,900,000	none
platform token only	0 emails	purpose logged

I would not consider it settled without evidence: run the connector in a dry run and require the payload schema to exclude raw PII columns.

A segment export is a processor copy.

Where candidates lose the interview on OLTP versus OLAP models is calling the lakehouse layout the model.

Normalization does not itself multiply rows; incorrect joins across one-to-many relationships do. Dimensional marts often simplify analytical consumption, while normalized enterprise models or Data Vault can remain valid behind governed semantic views that declare grain and measures.

Concretely, retain the source-aligned or normalized integration model where useful, publish dimensional marts or governed semantic views for consumers, and certify joins only when their cardinality preserves the declared analytical grain.

The reason for that specificity is a failure I have seen: Analysts joined 12 normalized tables without controlling a one-to-many relationship. The join exploded orders × 4.6, and revenue was 4.6× for 3 weeks, $44 million high on a board slide; 3NF did not require that join error.

12-table OLTP join.

Model	Join explosion	Board revenue
copied 3NF	4.6×	$44 million high for 3 weeks
line-grain fact	1×	certified

I would not consider it settled without evidence: compare a governed grain-preserving view with an uncontrolled one-to-many join on the same normalized fixture and require the latter to fail its cardinality assertion.

Normalization is not fan-out; an uncertified join is.

I would answer JSON shredding contracts by separating storage, schema, and the meaning a consumer is allowed to assume.

Best-effort shredding drops unknown paths. That is a silent schema break. The contract either fails the load on unknown fields (in closed schemas) or lands them in a residual map that is classified.

Concretely, choose closed or open, fail closed loads on unknown paths, and classify residual maps as unknown PII until reviewed.

The reason for that specificity is a failure I have seen: A new path held national ids. Best-effort shredding dropped it from columns but the raw JSON stayed in a string. 44,000 ids sat in an unclassified string column for 6 weeks.

Unknown path with national ids.

Shredding	Column	Raw string	Ids lingering
best effort	dropped	unclassified	44,000 for 6 weeks
closed fail or classified residual	explicit	reviewed	0 unmarked

I would not consider it settled without evidence: add an unknown path containing a national id and require either a failed closed load or a classified residual.

Dropped paths still live in the raw string.

The engineering content of schema-on-read versus schema-on-write is the contract and the incompatible write it would reject, not the file format.

Schema-on-read can be production when the read schema is governed: declared, versioned, and failing on mismatch. Ungoverned JSON files with no owner are the incident. Reading 40 undeclared JSON files in a certified dashboard is 40 schemas with no contract.

Concretely, 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.

The reason for that specificity is a failure I have seen: A production dashboard pointed at ungoverned raw JSON. A field type flipped from number to string. 0 rows returned for 3 days while exploration notebooks still "worked" with a cast.

Certified dashboard on ungoverned JSON.

Path	Type flip	Production rows
dashboard on ungoverned JSON	number to string	0 for 3 days
governed read or write-time contract	rejected at schema check	prior good partition

I would not consider it settled without evidence: flip a field type in raw files and require the certified path to fail at the governed schema, not at dashboard time.

Governed schema-on-read is a contract; ungoverned JSON is not.

Before calling SCD type 0 durable attributes done I would write down the consumer, region, or batch window nobody loaded.

Type 0 rejects business changes and still allows a named correction with an audit. Treating type 0 as "the column is immutable in the database" blocks legal fixes and pushes edits into shadow tables.

Concretely, reject ordinary updates to type-0 attributes, allow a correction ticket with before/after and officer, and record the correction without creating a type-2 history that implies the person used to be a different age for analytics.

The reason for that specificity is a failure I have seen: DOB was type-2. 18,400 "corrections" from a form glitch created 18,400 versions. Age-banded metrics moved 4 points. Type 0 with no correction path had previously forced 2,200 paper files.

DOB as type 2 versus type 0.

Handling	Spurious versions	Age-band shift
type 2	18,400	4 points
type 0 plus signed correction	0 spurious	0

I would not consider it settled without evidence: attempt a normal update and a signed correction; require the first to fail and the second to audit without restating unrelated history.

Immutable for business change; auditable for correction.

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

A mini-dimension extracts rapidly changing, low-cardinality attributes so the main customer row does not version every day. Leaving the score on customer explodes type-2 history.

Concretely, move the volatile attributes to a mini-dimension with its own surrogate on the fact, keep stable attributes on customer, and test customer version counts stay within budget.

The reason for that specificity is a failure I have seen: risk_band on customer versioned 1.1 million customers daily. Customer dim grew 400 million rows in 12 months. Joins for a 30-day fact scan spilled, 2.4 TB.

Daily risk_band on customer.

Placement	Customer rows after 12 months	Spill
on customer type 2	400,000,000	2.4 TB
mini-dimension	~1.1 million current	0

I would not consider it settled without evidence: change the volatile attribute 30 days running and require customer versions to stay flat while the mini-dimension absorbs the changes.

Daily attributes do not belong on a slowly changing person.

I would start conformed facts from the contract and the consumer, not from the warehouse diagram.

Conformed facts share additive measures at compatible grains, or they declare an allocation. Summing order-header amount with order-line amount is mixed grain. Summing shipments and orders without a bridge double-counts.

Concretely, publish measure compatibility, forbid certified SUM across incompatible grains, and provide a documented allocation if the business insists on a combined number.

The reason for that specificity is a failure I have seen: A "total commercial value" summed order amount and shipment amount. Dual-counted 31 percent of GMV, $27 million, because 31 percent of orders had shipped in-period.

Orders plus shipments in one SUM.

Metric	Overlap	Dual-counted GMV
SUM(order.amount)+SUM(ship.amount)	31 percent	$27 million
one grain or allocated bridge	0 extra	$0

I would not consider it settled without evidence: SUM two facts in a fixture with overlapping business events and require the certified metric to use one grain or an allocation that sums to 100 percent.

Same column name is not a conformed measure.

This is an area where a green dbt test and a held meaning are different observations.

Type 6 stores the historical attribute for that version and the current attribute on every version (or a current pointer on every version). It is two answers on each row. Writing current only on the latest row is not type 6; as-is then cannot see history.

Concretely, keep effective-dated versions for as-was, maintain a current_region on every version or a current-row pointer for as-is, and test both queries.

The reason for that specificity is a failure I have seen: Engineers "type-6'd" by writing current_region only on the latest row. Historical versions had null current. As-is reports inner-joined to current=true and dropped 14 percent of facts, $9.1 million.

Current attribute only on the latest version.

Type-6 implementation	Facts reachable as-is	Dollars dropped
current only on latest row	86 percent	$9.1 million
historical plus current on all versions	100 percent	$0

I would not consider it settled without evidence: reorg, then run as-was and as-is; require as-is to reach 100 percent of facts using the current attribute present on each version.

Historical plus current on each version; current-only-on-latest is a truncated type 2.

My answer to slowly changing type 4 history table begins with which semantic claim is being made, not which physical table exists.

Type 4 splits current (small, unique on natural key) from history (append-only versions). It helps when most joins need current and only audits need history. It fails if facts must join as-of without a documented as-of path.

Concretely, join facts that need current to the current table, join as-of queries to history with effective dates, and forbid a fact that stores no event time from pretending to be as-was.

The reason for that specificity is a failure I have seen: Facts joined only to current. A name correction restated 8 years of "customer name at order" in the app. 2.2 million order PDFs no longer matched the warehouse.

Facts pinned to the current table.

Join	Name at order after a correction	PDFs matching
current only	restated 8 years	0 of 2.2 million
history as-of event time	frozen	2.2 million

I would not consider it settled without evidence: correct a current attribute and require as-of-order names to stay on the history join.

Current plus history still needs an as-of join.