Problem
Schema drift is a source-shape change relative to what the ingestion boundary expects. A producer can add a field, rename one, change a type or remove a required field. Each change can be technically loadable while still changing the meaning of a report.
The teaching contract here is one row per order with order_id, order_date, channel and amount_cents. Every field is required and the amount is an integer number of cents. The baseline contains 3 rows totalling 18500 cents. The requested 2026-03-18 slice contains 2 rows totalling 14500 cents. The out-of-scope 2026-03-17 row contributes 1 row and 4000 cents.
That third row is deliberate. The contract gate rejects the changed batch before it can alter trusted data, while a row outside the requested date remains visible as part of the baseline. The comparison is about source shape, not event identity or replay. The flagship retail-orders pipeline covers its event contract and quarantine stage. Replay safety covers event identity, while late corrections covers a reporting-date change. This note owns the boundary between an observed source schema and an admissible trusted row.
The tools named in this problem solve related but different problems. dbt model contracts verify the shape of supported model outputs before a model builds, and the documentation says contracts do not apply to sources. Azure Data Factory's schema-drift guidance describes late-bound columns and auto-mapping in mapping data flows. Databricks' schema-evolution documentation describes when schema evolution can add fields during writes. Those are useful product behaviours, not evidence that an ingestion boundary should accept every change.
Start from the target contract rather than from the parser's favourite fallback. For every field, write down the name, type, null policy, unit and business meaning. Then decide which differences are compatible and which require a new source version. An optional field can be ignored only when its absence has no effect on the target grain or its calculations. A rename needs an explicit mapping because a name change can be either a harmless alias or a new business concept. A type change needs a coercion rule and a failure rule. A removed field needs a replacement value only when that value is part of the contract. These decisions make the diff reviewable and give operators a useful reason when the batch stops.
A schema contract guards the trusted path
The observed source shape is diffed, gated and written to the trusted table only when allowed.
Diagram, 4 stages
Select a stage to see its details. The diagram explains the pipeline and does not run it; the full explanation follows below.
Observed source
Outputs: source rows, schema manifest
A closed source case supplies rows and a schema manifest.
Why The loader must inspect the shape that arrived, not only the values it can coerce.
Inputs, outputs and schemaObserved source: inputs, outputs and schema
- Receives
- Nothing: the flow starts here
- Outputs
- source rows; schema manifest
- Hands on
- observe shape, to Contract diff
column_name- VARCHAR Observed field name
data_type- VARCHAR Observed field type
nullable- BOOLEAN Whether the field may be null
Hands on to Contract diff: observe shape
Contract diff
Outputs: diff
The observed triples are compared with the required contract.
Why A renamed, removed or retyped field must be visible before trusted data changes.
Inputs, outputs, SQL and schemaContract diff: inputs, outputs, SQL and schema
- Receives
- observe shape, from Observed source
- Outputs
- diff
- Hands on
- check compatibility, to Contract gate
sql/right.sql scroll for more-- Teaching sketch: diff the observed shape, then replace only an allowed slice.-- The application does not run this SQL.CREATE OR REPLACE TEMP TABLE contract_diff ASWITH expected(column_name, data_type, nullable) AS ( VALUES ('order_id', 'VARCHAR', false), ('order_date', 'DATE', false), ('channel', 'VARCHAR', false), ('amount_cents', 'BIGINT', false)), observed AS ( SELECT column_name, data_type, nullable FROM schema_manifest WHERE schema_case = $1), differences AS ( SELECT 'unexpected_or_changed' AS reason, column_name, data_type, nullable FROM observed EXCEPT SELECT 'unexpected_or_changed', column_name, data_type, nullable FROM expected UNION ALL SELECT 'missing_or_changed', column_name, data_type, nullable FROM expected EXCEPT SELECT 'missing_or_changed', column_name, data_type, nullable FROM observed)SELECT reason, column_name, data_type, nullableFROM differences;CREATE OR REPLACE TEMP TABLE gate ASSELECT CAST($1 AS VARCHAR) AS schema_case, CAST( COUNT(*) = 0 OR ( $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true ) AS BOOLEAN ) AS allowed, CASE WHEN COUNT(*) = 0 THEN 'none' WHEN $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true THEN 'approved_nullable_addition' ELSE string_agg(reason || ':' || column_name, ', ' ORDER BY reason, column_name) END AS diff_reasonFROM contract_diff;CREATE OR REPLACE TEMP TABLE candidate_rows ASSELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_baselineWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'baseline'UNION ALLSELECT order_id, order_date, channel, amount_cents, currencyFROM source_addedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'added'UNION ALLSELECT order_id, CAST(NULL AS DATE) AS order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_renamedWHERE sale_date = CAST('2026-03-18' AS DATE) AND $1 = 'renamed'UNION ALLSELECT order_id, order_date, channel, TRY_CAST(amount_cents AS BIGINT) AS amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_retypedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'retyped'UNION ALLSELECT order_id, order_date, 'unknown' AS channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_removedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'removed';BEGIN TRANSACTION;DELETE FROM trusted_ordersWHERE EXISTS (SELECT 1 FROM gate WHERE allowed) AND order_date = CAST('2026-03-18' AS DATE);INSERT INTO trusted_orders (order_id, order_date, channel, amount_cents, currency)SELECT candidate.order_id, candidate.order_date, candidate.channel, candidate.amount_cents, candidate.currencyFROM candidate_rows AS candidateWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);UPDATE trusted_schema_stateSET schema_version = CASE WHEN $1 = 'added' THEN 2 ELSE 1 ENDWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);COMMIT;SELECT gate.schema_case, CASE WHEN gate.allowed THEN 'loaded' ELSE 'rejected' END AS action, CAST(CASE WHEN gate.allowed THEN (SELECT COUNT(*) FROM candidate_rows) ELSE 0 END AS BIGINT) AS loaded_rows, CAST(COALESCE(SUM(CASE WHEN trusted.order_date = CAST('2026-03-18' AS DATE) AND trusted.channel IN ('web', 'store') THEN trusted.amount_cents ELSE 0 END), 0) AS BIGINT) AS known_channel_total_cents, state.schema_version, COALESCE(string_agg(COALESCE(trusted.currency, '<null>'), ',' ORDER BY trusted.order_id) FILTER (WHERE trusted.order_date = CAST('2026-03-18' AS DATE)), '') AS currency_values, gate.diff_reasonFROM gateCROSS JOIN trusted_schema_state AS stateCROSS JOIN trusted_orders AS trustedGROUP BY gate.schema_case, gate.allowed, gate.diff_reason, state.schema_version;column_name- VARCHAR Changed or missing field
data_type- VARCHAR Observed or expected type
Hands on to Contract gate: check compatibility
Contract gate
Outputs: gate decision, accepted rows
It allows the baseline shape or the approved nullable currency addition and rejects any other change before a trusted row is written.
Why A rejection writes zero trusted rows, while an accepted additive change keeps currency and sets schema_version 2 in the same transaction.
Inputs, outputs, SQL and schemaContract gate: inputs, outputs, SQL and schema
- Receives
- check compatibility, from Contract diff
- Outputs
- gate decision; accepted rows
- Hands on
- allow or reject, to Trusted table
sql/right.sql scroll for more-- Teaching sketch: diff the observed shape, then replace only an allowed slice.-- The application does not run this SQL.CREATE OR REPLACE TEMP TABLE contract_diff ASWITH expected(column_name, data_type, nullable) AS ( VALUES ('order_id', 'VARCHAR', false), ('order_date', 'DATE', false), ('channel', 'VARCHAR', false), ('amount_cents', 'BIGINT', false)), observed AS ( SELECT column_name, data_type, nullable FROM schema_manifest WHERE schema_case = $1), differences AS ( SELECT 'unexpected_or_changed' AS reason, column_name, data_type, nullable FROM observed EXCEPT SELECT 'unexpected_or_changed', column_name, data_type, nullable FROM expected UNION ALL SELECT 'missing_or_changed', column_name, data_type, nullable FROM expected EXCEPT SELECT 'missing_or_changed', column_name, data_type, nullable FROM observed)SELECT reason, column_name, data_type, nullableFROM differences;CREATE OR REPLACE TEMP TABLE gate ASSELECT CAST($1 AS VARCHAR) AS schema_case, CAST( COUNT(*) = 0 OR ( $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true ) AS BOOLEAN ) AS allowed, CASE WHEN COUNT(*) = 0 THEN 'none' WHEN $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true THEN 'approved_nullable_addition' ELSE string_agg(reason || ':' || column_name, ', ' ORDER BY reason, column_name) END AS diff_reasonFROM contract_diff;CREATE OR REPLACE TEMP TABLE candidate_rows ASSELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_baselineWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'baseline'UNION ALLSELECT order_id, order_date, channel, amount_cents, currencyFROM source_addedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'added'UNION ALLSELECT order_id, CAST(NULL AS DATE) AS order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_renamedWHERE sale_date = CAST('2026-03-18' AS DATE) AND $1 = 'renamed'UNION ALLSELECT order_id, order_date, channel, TRY_CAST(amount_cents AS BIGINT) AS amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_retypedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'retyped'UNION ALLSELECT order_id, order_date, 'unknown' AS channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_removedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'removed';BEGIN TRANSACTION;DELETE FROM trusted_ordersWHERE EXISTS (SELECT 1 FROM gate WHERE allowed) AND order_date = CAST('2026-03-18' AS DATE);INSERT INTO trusted_orders (order_id, order_date, channel, amount_cents, currency)SELECT candidate.order_id, candidate.order_date, candidate.channel, candidate.amount_cents, candidate.currencyFROM candidate_rows AS candidateWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);UPDATE trusted_schema_stateSET schema_version = CASE WHEN $1 = 'added' THEN 2 ELSE 1 ENDWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);COMMIT;SELECT gate.schema_case, CASE WHEN gate.allowed THEN 'loaded' ELSE 'rejected' END AS action, CAST(CASE WHEN gate.allowed THEN (SELECT COUNT(*) FROM candidate_rows) ELSE 0 END AS BIGINT) AS loaded_rows, CAST(COALESCE(SUM(CASE WHEN trusted.order_date = CAST('2026-03-18' AS DATE) AND trusted.channel IN ('web', 'store') THEN trusted.amount_cents ELSE 0 END), 0) AS BIGINT) AS known_channel_total_cents, state.schema_version, COALESCE(string_agg(COALESCE(trusted.currency, '<null>'), ',' ORDER BY trusted.order_id) FILTER (WHERE trusted.order_date = CAST('2026-03-18' AS DATE)), '') AS currency_values, gate.diff_reasonFROM gateCROSS JOIN trusted_schema_state AS stateCROSS JOIN trusted_orders AS trustedGROUP BY gate.schema_case, gate.allowed, gate.diff_reason, state.schema_version;schema_case- VARCHAR Closed source case under review
action- VARCHAR Loaded or rejected decision
loaded_rows- BIGINT Rows written, or zero after rejection
schema_version- BIGINT Trusted schema version after the transaction
currency_values- VARCHAR Currency values preserved for the requested slice
diff_reason- VARCHAR Approval or rejection reason
Hands on to Trusted table: allow or reject
Trusted table
Outputs: action, trusted total, schema version, currency values
Only an allowed shape can replace the requested date slice.
Why A rejection must preserve the last trusted result and name the changed column.
Inputs, outputs, SQL and schemaTrusted table: inputs, outputs, SQL and schema
- Receives
- allow or reject, from Contract gate
- Outputs
- action; trusted total; schema version; currency values
- Hands on
- Nothing: the flow ends here
sql/right.sql scroll for more-- Teaching sketch: diff the observed shape, then replace only an allowed slice.-- The application does not run this SQL.CREATE OR REPLACE TEMP TABLE contract_diff ASWITH expected(column_name, data_type, nullable) AS ( VALUES ('order_id', 'VARCHAR', false), ('order_date', 'DATE', false), ('channel', 'VARCHAR', false), ('amount_cents', 'BIGINT', false)), observed AS ( SELECT column_name, data_type, nullable FROM schema_manifest WHERE schema_case = $1), differences AS ( SELECT 'unexpected_or_changed' AS reason, column_name, data_type, nullable FROM observed EXCEPT SELECT 'unexpected_or_changed', column_name, data_type, nullable FROM expected UNION ALL SELECT 'missing_or_changed', column_name, data_type, nullable FROM expected EXCEPT SELECT 'missing_or_changed', column_name, data_type, nullable FROM observed)SELECT reason, column_name, data_type, nullableFROM differences;CREATE OR REPLACE TEMP TABLE gate ASSELECT CAST($1 AS VARCHAR) AS schema_case, CAST( COUNT(*) = 0 OR ( $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true ) AS BOOLEAN ) AS allowed, CASE WHEN COUNT(*) = 0 THEN 'none' WHEN $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true THEN 'approved_nullable_addition' ELSE string_agg(reason || ':' || column_name, ', ' ORDER BY reason, column_name) END AS diff_reasonFROM contract_diff;CREATE OR REPLACE TEMP TABLE candidate_rows ASSELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_baselineWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'baseline'UNION ALLSELECT order_id, order_date, channel, amount_cents, currencyFROM source_addedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'added'UNION ALLSELECT order_id, CAST(NULL AS DATE) AS order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_renamedWHERE sale_date = CAST('2026-03-18' AS DATE) AND $1 = 'renamed'UNION ALLSELECT order_id, order_date, channel, TRY_CAST(amount_cents AS BIGINT) AS amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_retypedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'retyped'UNION ALLSELECT order_id, order_date, 'unknown' AS channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_removedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'removed';BEGIN TRANSACTION;DELETE FROM trusted_ordersWHERE EXISTS (SELECT 1 FROM gate WHERE allowed) AND order_date = CAST('2026-03-18' AS DATE);INSERT INTO trusted_orders (order_id, order_date, channel, amount_cents, currency)SELECT candidate.order_id, candidate.order_date, candidate.channel, candidate.amount_cents, candidate.currencyFROM candidate_rows AS candidateWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);UPDATE trusted_schema_stateSET schema_version = CASE WHEN $1 = 'added' THEN 2 ELSE 1 ENDWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);COMMIT;SELECT gate.schema_case, CASE WHEN gate.allowed THEN 'loaded' ELSE 'rejected' END AS action, CAST(CASE WHEN gate.allowed THEN (SELECT COUNT(*) FROM candidate_rows) ELSE 0 END AS BIGINT) AS loaded_rows, CAST(COALESCE(SUM(CASE WHEN trusted.order_date = CAST('2026-03-18' AS DATE) AND trusted.channel IN ('web', 'store') THEN trusted.amount_cents ELSE 0 END), 0) AS BIGINT) AS known_channel_total_cents, state.schema_version, COALESCE(string_agg(COALESCE(trusted.currency, '<null>'), ',' ORDER BY trusted.order_id) FILTER (WHERE trusted.order_date = CAST('2026-03-18' AS DATE)), '') AS currency_values, gate.diff_reasonFROM gateCROSS JOIN trusted_schema_state AS stateCROSS JOIN trusted_orders AS trustedGROUP BY gate.schema_case, gate.allowed, gate.diff_reason, state.schema_version;order_id- VARCHAR Stable order key
order_date- DATE Reporting date
channel- VARCHAR Known sales channel
amount_cents- BIGINT Order value in cents
currency- VARCHAR Nullable approved currency
| order_idVARCHAR | order_dateDATE | channelVARCHAR | amount_centsBIGINT |
|---|---|---|---|
| o-101 | 2026-03-18 | web | 12000 |
| o-102 | 2026-03-18 | store | 2500 |
| o-103 | 2026-03-17 | web | 4000 |
Synthetic data, showing 3 of 3 rows from data/baseline.json
| schema_caseVARCHAR | column_nameVARCHAR | data_typeVARCHAR | nullableBOOLEAN |
|---|---|---|---|
| baseline | order_id | VARCHAR | false |
| baseline | order_date | DATE | false |
| baseline | channel | VARCHAR | false |
| baseline | amount_cents | BIGINT | false |
| added | order_id | VARCHAR | false |
| added | order_date | DATE | false |
| added | channel | VARCHAR | false |
| added | amount_cents | BIGINT | false |
| added | currency | VARCHAR | true |
| renamed | order_id | VARCHAR | false |
| renamed | sale_date | DATE | false |
| renamed | channel | VARCHAR | false |
| renamed | amount_cents | BIGINT | false |
| retyped | order_id | VARCHAR | false |
| retyped | order_date | DATE | false |
| retyped | channel | VARCHAR | false |
| retyped | amount_cents | VARCHAR | false |
| removed | order_id | VARCHAR | false |
| removed | order_date | DATE | false |
| removed | amount_cents | BIGINT | false |
Synthetic data, showing 20 of 20 rows from data/schema-manifest.csv
Wrong version
The tempting loader extracts the fields it knows by name, supplies unknown for a missing channel and uses TRY_CAST for an amount. It does not compare the observed schema with a versioned contract. The result can be a successful query with a plausible row count and an empty or partial measure.
The file shown in the next block is a teaching sketch. The application does not run this SQL. It selects the two rows for the requested date from each closed source case, projects them into the expected shape and summarises the projection. A rename leaves order_date null in the projection. A retyped value such as 12000c is not a valid integer and becomes null. A removed channel becomes unknown, which is outside the known-channel total.
The baseline and additive cases look harmless to this loader. On baseline, the permissive path loads 2 rows totalling 14500 cents. On added, it also loads 2 rows totalling 14500 cents because the extra currency field is ignored. The breaking cases are more dangerous because the loader still accepts the batch: on renamed, retyped and removed, it loads 2 rows totalling 0 cents.
That is the failure mode to avoid. A successful load says only that the projection ran. It does not say that the source and target still describe the same field. A null amount is not a zero sale, and an unknown channel is not a known channel that can safely join a channel report.
-- Teaching sketch: a permissive projection that accepts drift by name.-- The application does not run this SQL.WITH selected AS ( SELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currency FROM source_baseline WHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'baseline' UNION ALL -- The permissive projection maps only the fields it expects, so the new -- currency column is ignored. SELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currency FROM source_added WHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'added' UNION ALL SELECT order_id, CAST(NULL AS DATE) AS order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currency FROM source_renamed WHERE sale_date = CAST('2026-03-18' AS DATE) AND $1 = 'renamed' UNION ALL SELECT order_id, order_date, channel, TRY_CAST(amount_cents AS BIGINT) AS amount_cents, CAST(NULL AS VARCHAR) AS currency FROM source_retyped WHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'retyped' UNION ALL SELECT order_id, order_date, 'unknown' AS channel, amount_cents, CAST(NULL AS VARCHAR) AS currency FROM source_removed WHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'removed'), projected AS ( SELECT order_id, order_date, COALESCE(channel, 'unknown') AS channel, amount_cents, currency FROM selected)SELECT CAST($1 AS VARCHAR) AS schema_case, 'loaded' AS action, CAST(COUNT(*) AS BIGINT) AS loaded_rows, CAST(COALESCE(SUM(CASE WHEN order_date = CAST('2026-03-18' AS DATE) AND channel IN ('web', 'store') THEN COALESCE(amount_cents, 0) ELSE 0 END), 0) AS BIGINT) AS known_channel_total_cents, CAST(1 AS BIGINT) AS schema_version, COALESCE(string_agg(COALESCE(currency, '<null>'), ',' ORDER BY order_id), '') AS currency_values, 'not_checked' AS diff_reasonFROM projected;Right version
The right version compares (column_name, data_type, nullable) triples before it writes. The expected contract contains the required fields. The added case also contains a nullable currency field, which is explicitly allowed and is not used by the order total. The rename, retype and removal cases produce a diff and stop before trusted rows change.
The contract diff and gate are also a teaching sketch. The application does not run this SQL. It uses a closed schema_case choice, binds it by position and selects the matching declared source table. The harness loads those declared files into DuckDB, then drops the registered file buffers and locks external access before authored SQL runs. No query in this note reads a file or URL, and no reader can type SQL.
The gate has two outcomes. For baseline and added, it replaces only the two rows in the requested date slice and reports 2 rows totalling 14500 cents. For renamed, retyped and removed, it reports rejected with 0 rows totalling 14500 cents. After rejection, the trusted table still has 3 rows totalling 18500 cents. The accepted addition preserves its nullable currency values and records the approved schema version in the same transaction. The same connection test checks that state immediately after the rejected write, rather than opening a fresh database that could hide a mutation.
-- Teaching sketch: diff the observed shape, then replace only an allowed slice.-- The application does not run this SQL.CREATE OR REPLACE TEMP TABLE contract_diff ASWITH expected(column_name, data_type, nullable) AS ( VALUES ('order_id', 'VARCHAR', false), ('order_date', 'DATE', false), ('channel', 'VARCHAR', false), ('amount_cents', 'BIGINT', false)), observed AS ( SELECT column_name, data_type, nullable FROM schema_manifest WHERE schema_case = $1), differences AS ( SELECT 'unexpected_or_changed' AS reason, column_name, data_type, nullable FROM observed EXCEPT SELECT 'unexpected_or_changed', column_name, data_type, nullable FROM expected UNION ALL SELECT 'missing_or_changed', column_name, data_type, nullable FROM expected EXCEPT SELECT 'missing_or_changed', column_name, data_type, nullable FROM observed)SELECT reason, column_name, data_type, nullableFROM differences;CREATE OR REPLACE TEMP TABLE gate ASSELECT CAST($1 AS VARCHAR) AS schema_case, CAST( COUNT(*) = 0 OR ( $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true ) AS BOOLEAN ) AS allowed, CASE WHEN COUNT(*) = 0 THEN 'none' WHEN $1 = 'added' AND COUNT(*) = 1 AND MAX(reason) = 'unexpected_or_changed' AND MAX(column_name) = 'currency' AND MAX(data_type) = 'VARCHAR' AND MAX(nullable) = true THEN 'approved_nullable_addition' ELSE string_agg(reason || ':' || column_name, ', ' ORDER BY reason, column_name) END AS diff_reasonFROM contract_diff;CREATE OR REPLACE TEMP TABLE candidate_rows ASSELECT order_id, order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_baselineWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'baseline'UNION ALLSELECT order_id, order_date, channel, amount_cents, currencyFROM source_addedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'added'UNION ALLSELECT order_id, CAST(NULL AS DATE) AS order_date, channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_renamedWHERE sale_date = CAST('2026-03-18' AS DATE) AND $1 = 'renamed'UNION ALLSELECT order_id, order_date, channel, TRY_CAST(amount_cents AS BIGINT) AS amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_retypedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'retyped'UNION ALLSELECT order_id, order_date, 'unknown' AS channel, amount_cents, CAST(NULL AS VARCHAR) AS currencyFROM source_removedWHERE order_date = CAST('2026-03-18' AS DATE) AND $1 = 'removed';BEGIN TRANSACTION;DELETE FROM trusted_ordersWHERE EXISTS (SELECT 1 FROM gate WHERE allowed) AND order_date = CAST('2026-03-18' AS DATE);INSERT INTO trusted_orders (order_id, order_date, channel, amount_cents, currency)SELECT candidate.order_id, candidate.order_date, candidate.channel, candidate.amount_cents, candidate.currencyFROM candidate_rows AS candidateWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);UPDATE trusted_schema_stateSET schema_version = CASE WHEN $1 = 'added' THEN 2 ELSE 1 ENDWHERE EXISTS (SELECT 1 FROM gate WHERE allowed);COMMIT;SELECT gate.schema_case, CASE WHEN gate.allowed THEN 'loaded' ELSE 'rejected' END AS action, CAST(CASE WHEN gate.allowed THEN (SELECT COUNT(*) FROM candidate_rows) ELSE 0 END AS BIGINT) AS loaded_rows, CAST(COALESCE(SUM(CASE WHEN trusted.order_date = CAST('2026-03-18' AS DATE) AND trusted.channel IN ('web', 'store') THEN trusted.amount_cents ELSE 0 END), 0) AS BIGINT) AS known_channel_total_cents, state.schema_version, COALESCE(string_agg(COALESCE(trusted.currency, '<null>'), ',' ORDER BY trusted.order_id) FILTER (WHERE trusted.order_date = CAST('2026-03-18' AS DATE)), '') AS currency_values, gate.diff_reasonFROM gateCROSS JOIN trusted_schema_state AS stateCROSS JOIN trusted_orders AS trustedGROUP BY gate.schema_case, gate.allowed, gate.diff_reason, state.schema_version;A renamed date is accepted by projection but rejected by the contract.
Wrong against right, same rows
| schema_caseVARCHAR | PermissiveBIGINT | Contract gateBIGINT | difference |
|---|---|---|---|
| renamed | 0 | 14500 | -14500 |
| Total | 0 | 14500 | -14500 |
1 of 1 rows differ in known_channel_total_cents. 0 Permissive = 14500 Contract gate - 14500 difference
Saved result: computed when this page was built, by DuckDB-Wasm running the note's reviewed SQL on its synthetic data.
Step through the baseline, additive and breaking shapes.
Step through, 5 saved states
Step 1 of 5: Baseline, Known-channel total (cents) 14500
The last trusted result.
| schema_caseVARCHAR | actionVARCHAR | loaded_rowsBIGINT | known_channel_total_centsBIGINT | schema_versionBIGINT | currency_valuesVARCHAR | diff_reasonVARCHAR |
|---|---|---|---|---|---|---|
| baseline | baseline | 0 | 14500 | 1 | <null>,<null> | none |
| step | Known-channel total (cents) |
|---|---|
| 1. Baseline | 14500 |
| 2. Added currency | 14500 |
| 3. Renamed date | 14500 |
| 4. Retyped amount | 14500 |
| 5. Removed channel | 14500 |
Saved result: computed when this page was built, by DuckDB-Wasm running the note's reviewed SQL on its synthetic data.
Choose a reviewed version and one closed schema case.
Closed choices, reviewed SQL
Showing Permissive projection, Baseline: 1 row.
| schema_caseVARCHAR | actionVARCHAR | loaded_rowsBIGINT | known_channel_total_centsBIGINT | schema_versionBIGINT | currency_valuesVARCHAR | diff_reasonVARCHAR |
|---|---|---|---|---|---|---|
| baseline | loaded | 2 | 14500 | 1 | <null>,<null> | not_checked |
| Total | 2 | 14500 |
Saved result: computed when this page was built, by DuckDB-Wasm running the note's reviewed SQL on its synthetic data. Readers cannot edit or run arbitrary SQL here: every option is a fixed, reviewed choice.
The query block offers only the reviewed wrong and right SQL with the closed schema cases. Its parameters are closed choices and its values bind by position. The result exposes the action, loaded row count, known-channel total, schema version, preserved currency values and contract diff reason. The independent oracle calculates the same rows, decisions and diff reasons in plain TypeScript, without DuckDB and without importing the note runner.
Trade-off
A frozen contract gives a clear failure boundary but turns an additive change into operator work. The useful compromise is not “accept whatever arrives”. It is a named compatibility policy: permit a nullable currency addition, record the schema version and review the default for every new field.
dlt's schema-contract documentation makes this policy explicit through evolve, freeze, discard_row and discard_value. Its default evolution mode can append new columns and route values that do not coerce into a variant column. The dlt schema-evolution guide describes the operational choices around that behaviour. This note uses a stricter policy for a required order shape; it does not implement dlt or claim that dlt uses this SQL.
Fivetran's schema-management documentation describes connector handling for new, removed, retyped and renamed columns. A rename can preserve old and new columns rather than making a downstream field change silently. That protects raw data but can split downstream logic, so the contract decision still belongs at the boundary that owns the target schema.
BigQuery's table-schema documentation separates nullable additions from changes that need a different table-schema operation. A warehouse DDL capability is not the same as an ingestion acceptance policy. The same distinction appears in the dlt schema-evolution field guide and this practitioner overview of schema drift in data pipelines: absorb a change only when the downstream meaning and migration path are explicit.
The cost of this gate is coordination. Producers need a compatibility rule, the ingestion owner needs a diff that names the changed field and operators need a recovery path. The benefit is that a source rename cannot turn a known total into a quiet zero while a task reports success.
What this does not prove
This note does not prove that dbt, dlt, Fivetran, BigQuery, Databricks or Azure Data Factory behave identically. Their documented features operate at different boundaries: dbt contracts protect supported model outputs, while ingestion tools and warehouse DDL apply their own rules. The SQL here is a teaching sketch. The application does not implement dbt, dlt or Fivetran.
It also does not detect semantic drift where names and types stay the same but the unit changes from cents to dollars, or where a producer sends a different business meaning. It does not prove freshness, distributions, lineage, reconciliation to a source system or dashboard correctness. A structural diff can be green while the data is still wrong.
The evidence is narrower: the same synthetic orders are presented as the baseline, an approved nullable addition, a renamed date, a retyped amount and a removed channel. The requested slice has 2 rows and 14500 cents. The independent oracle checks every published result, including counts, actions, totals and diff reasons. A mutation that changes the derived count fails the prose claim check.
For production, version the source schema, publish a compatibility matrix and make the rejection observable. Compare the exact changed columns, preserve the last trusted result and coordinate a migration before allowing a breaking shape. For the downstream write boundary, continue to re-running a backfill without doubling the total, read the bundled pipeline SQL and use the retail-orders studio or Canvas to inspect the wider project.