Schema/Contract Tests: Types, Nullability, Keys, Accepted Values, and Relationships

Learning outcomes

1. The realistic problem: the pipeline runs, but the shape changed

AtlasMart receives an ERP extract that still parses as CSV and still loads into a permissive staging table, but line_id is missing for some records and a new channel code fax appears. A SQL syntax check passes. The business decision is whether the data can enter the certified sales fact. The source event is a paid order line; the declared target grain is one row per (order_id, line_id); downstream consumers include finance, marketing, and executive reports. A syntactically valid load that violates the grain or code contract is still a failed pipeline.

2. A layered test strategy starts before row-level quality

No single layer replaces another. A NOT NULL declaration can prevent SQL NULL, but it cannot tell whether '', 'UNKNOWN', 0, or a fabricated default is business-valid.

3. Executable structural contract

CREATE TABLE fact_sales(  event_id TEXT PRIMARY KEY,  order_id TEXT NOT NULL,  line_id INTEGER NOT NULL,  event_time TEXT NOT NULL,  customer_id TEXT NOT NULL,  product_id TEXT NOT NULL,  quantity INTEGER NOT NULL CHECK(quantity > 0),  line_amount_usd REAL NOT NULL CHECK(line_amount_usd >= 0),  cost_amount_usd REAL NOT NULL CHECK(cost_amount_usd >= 0),  channel TEXT NOT NULL CHECK(channel IN ('web','mobile','sales')),  UNIQUE(order_id, line_id));

The DDL is one enforcement point, not the whole testing system. Raw/staging data is intentionally more permissive so defects can be preserved, diagnosed, quarantined, and replayed rather than coerced away before evidence exists.

4. Schema tests should assert the declared contract, not “whatever exists today”

required = {    "event_id", "order_id", "line_id", "event_time",    "customer_id", "product_id", "quantity",    "line_amount_usd", "cost_amount_usd", "channel"}actual = {row[1] for row in conn.execute("PRAGMA table_info(fact_sales)")}assert actual == requirednulls = conn.execute("""  SELECT COUNT(*) FROM fact_sales  WHERE event_id IS NULL OR order_id IS NULL     OR line_id IS NULL OR customer_id IS NULL""").fetchone()[0]assert nulls == 0

A flaky anti-pattern is to generate the expected schema dynamically from the production table under test. Then an accidental schema change changes both “actual” and “expected,” and the test reports success. Expected contracts must be version-controlled or otherwise independently governed.

5. Keys: event identity is not the same as business grain

SELECT event_id, COUNT(*) AS nFROM staging_salesGROUP BY event_idHAVING COUNT(*) > 1;SELECT order_id, line_id, COUNT(*) AS nFROM staging_salesGROUP BY order_id, line_idHAVING COUNT(*) > 1;

An exact replay may violate event uniqueness and grain uniqueness simultaneously. A conflicting correction can have a new event ID yet still violate the declared fact grain. Tests must express both semantics.

6. Accepted values and relationships are contract tests too

SELECT COUNT(*) AS invalid_channel_rowsFROM staging_salesWHERE channel IS NULL OR channel NOT IN ('web','mobile','sales');SELECT COUNT(*) AS orphan_customer_rowsFROM staging_sales sWHERE NOT EXISTS (  SELECT 1 FROM dim_customer_history d  WHERE d.customer_id = s.customer_id);

The relationship test proves a customer identity is known somewhere in history. It does not yet prove that the fact resolved to the correct SCD version at event time; that stronger historical test belongs in Lesson 3.

7. Controlled failure: “NOT NULL means quality”

Suppose an engineer replaces missing customer_id with 'UNKNOWN' just to satisfy NOT NULL. The row now passes the structural constraint but may still violate the source contract, identity policy, reconciliation totals, or unknown-member rules. The safe repair is explicit: preserve the raw value/evidence, classify the failure, route through the documented unknown-member or quarantine policy, and test the downstream consequence.

8. Executed baseline evidence

RUNTIME 3.13.5 SQLITE 3.46.1BASE_CONTROLS (10, 8, 12, 820.0, 495.0, 325.0)CI baseline checks:  schema_required_columns       PASS  required_not_null            PASS  declared_grain_unique        PASS  accepted_channel_values      PASS  business_measure_ranges      PASS  customer_relationship        PASS  golden_controls              PASS

These passes prove the encoded contract for the deterministic fixture. They do not prove the source is accurate in the real world, nor that an untested column is safe.

9. Unit tests keep transformations small and diagnosable

A unit test should isolate a rule such as canonicalizing channel case, parsing a timestamp, selecting an SCD row for a boundary instant, or calculating gross profit from revenue minus cost. It should not require the entire warehouse to rebuild just to test one mapping. Small fixtures improve signal and CI speed; integration/reconciliation tests then prove the pieces compose correctly.

10. Production judgment and bridge

Correctness: schema tests protect declared structure but not all business semantics. Freshness/history: schema validity says nothing about timeliness or SCD interval correctness. Idempotency: duplicate delivery needs a separate event identity test. Security: test fixtures must use synthetic/minimized data; failure logs should not leak PII. Compatibility: exact DDL introspection differs by engine, while the logical contract is portable. Migration/rollback: version contract changes and run old/new compatibility tests before destructive cutover. Lesson 2 now tests observed values and business invariants.

11. Verification checklist

1. Confirm the good fixture retains 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit.
2. Confirm event identity and business-grain uniqueness are separate tests.
3. Confirm code-domain and relationship failures block certified ingestion.
4. Confirm raw/staging remains evidence-preserving rather than silently defaulting invalid values.
5. Confirm expected schema is independent of the live object being tested.

Knowledge check

Authoritative references

• Python — unittestStandard-library support for deterministic fixtures, assertions, setup/teardown, and automated test suites.
• SQLite — CREATE TABLE / constraintsAuthoritative syntax and semantics for NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and table constraints used in the local lab.
• SQLite — Foreign Key SupportExplains foreign-key behavior and the need to enable enforcement explicitly in SQLite connections.
• SQLite — TransactionsUsed to demonstrate that target writes, delivery ledger, and watermark advancement must commit atomically for safe restart.
• SQLite — UPSERTReference for conflict-aware idempotent target application in the CDC fixture.
• Python — hashlibUsed to fingerprint deterministic test evidence rather than compare unstable timestamps or environment-specific logs.

12. Lab cleanup/reset

The lab uses an in-memory SQLite database. Closing the Python process discards it. Rerun the deterministic script to rebuild the good contract fixture; no production source or warehouse is modified.