Test AtlasMart business invariants, ranges, freshness, duplicates, and referential integrity using negative fixtures that distinguish measurable quality from syntactic validity.
Data Quality/Business Tests: Invariants, Ranges, Freshness, Duplicates, and Referential Integrity
Build an identity and access model for AtlasMart that separates humans from services, eliminates shared credentials, enforces environment boundaries, and proves least privilege with allow/deny evidence.
Learning outcomes
Turn completeness/validity/uniqueness/freshness/relationship expectations into observable tests.
Distinguish invariants from anomaly expectations and business tolerances.
Quarantine severe row-level defects while preserving raw evidence.
Diagnose duplicate event delivery versus conflicting rows at the same business grain.
Use freshness thresholds as explicit fixture/SLO assumptions rather than universal constants.
Chapter 24 begins from the governed AtlasMart state produced by Chapters 01–23: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. The current fact grain remains one accepted current paid order line; Chapter 20 metric definitions remain authoritative; Chapter 21 certified marts remain dependent on conformed assets; Chapter 22 security controls remain in force; and Chapter 23 ownership/lineage metadata supplies the change context. A test may block, quarantine, alert, or document a failure, but it must not silently change business semantics to make the suite green.
Runtime:
Python 3.13.5 + SQLite 3.46.1.
Storage: in-memory SQLite.
Time zone: UTC. Currency: USD.
Source/warehouse grain: one paid order line
identified by (order_id, line_id), with a separate
immutable delivery/event ID.
Security: synthetic customer IDs only.
Cost: free/local, no managed service.
Non-guarantee: passing these fixtures proves
the encoded contracts for this dataset/runtime; it does not
prove unknown business rules, external source truth, or
vendor-specific behavior not represented by the fixture.
1. The realistic problem: 13 rows arrived; should 13 become facts?
The staging batch contains the 10 canonical AtlasMart rows plus
three deliberate defects. Row 11 repeats
SALE-O1008-1; row 12 claims the already-used grain
(O1003,2) with a different event/product; row 13
has customer C999, quantity -1, negative amounts,
and channel fax. A naive “loaded row count = source
row count” target would certify 13 rows and corrupt both grain
and metrics.
2. Invariants, ranges, freshness, duplicates, and relationships
| Test class | AtlasMart rule | Why it matters |
|---|---|---|
| Invariant |
One accepted row per (order_id,line_id)
|
Protect declared fact grain |
| Range | quantity > 0; amount/cost ≥ 0 | Reject impossible measure signs in this contract |
| Freshness | latest source event lag ≤ 60 min for this fixture SLO | Consumer availability expectation |
| Duplicate delivery | one accepted occurrence per event_id |
At-least-once delivery safety |
| Referential integrity | customer exists in known history | Prevent unresolved analytical identity |
The 60-minute freshness target is an explicit AtlasMart fixture SLO, not a universal recommendation. Production thresholds come from consumer needs, source guarantees, and cost/recovery tradeoffs.
3. Negative fixture results are part of the test design
event_id_unique FAIL observed=1 expected=0 action: quarantine affected rows and stop certificationdeclared_grain_unique FAIL observed=2 expected=0accepted_channel_values FAIL observed=1 expected=0business_measure_ranges FAIL observed=1 expected=0customer_relationship FAIL observed=1 expected=0freshness_under_60m FAIL observed=70.0 expected<=60 severity: warningA test suite without negative fixtures may be accidentally incapable of detecting the failure it claims to protect. Here the suite proves each detector actually fires on controlled defects.
4. Duplicates are not all the same
| Row | Observed issue | Safe interpretation |
|---|---|---|
| 11 |
Same event_id and same business grain as
O1008/1
|
Redelivery/replay candidate; deduplicate by stable event identity |
| 12 |
New event ID but same (O1003,2) as an
existing fact
|
Grain conflict/correction candidate; do not append blindly |
| 13 | Unique keys but invalid channel/ranges/orphan customer | Business-quality failure; quarantine pending owner repair |
Calling all three “duplicates” loses the mechanism. Row 12 might represent an authorized correction, but Chapter 11 already established that corrections require explicit revision/restatement policy. It cannot become a second current fact row.
5. Quarantine preserves evidence instead of dropping rows
STAGING_ROWS 13QUARANTINE 11 SALE-O1008-1 DUPLICATE_EVENT+GRAIN_CONFLICT 12 SALE-CONFLICT-O1003-2 GRAIN_CONFLICT 13 SALE-BAD-1 INVALID_CHANNEL+INVALID_RANGE+ORPHAN_CUSTOMERACCEPTED_ROWS 10ACCEPTED_CONTROLS (10, 8, 12, 820.0, 495.0, 325.0)
Each quarantined record retains its raw payload and reason code.
Silent WHERE valid = 1 filtering without evidence
would make source-to-target reconciliation impossible and erase
the context needed for repair/reprocess.
6. Row count alone can lie
If a malformed +100 USD row enters while a valid +100 USD row disappears, the source and target can both report 10 rows while revenue and customer coverage are wrong. Conversely, deduplication can intentionally make target row count lower than delivered-message count. Reconciliation must compare metrics at the declared accepted grain and account explicitly for quarantined/rejected/replayed events.
7. Referential integrity can be logical before it is physical
SELECT s.ingest_seq, s.customer_idFROM staging_sales sWHERE NOT EXISTS ( SELECT 1 FROM dim_customer_history d WHERE d.customer_id = s.customer_id);Many warehouse engines do not enforce all relationships physically, and some cloud analytical systems treat constraints as informational. The logical test remains required whenever orphaned dimensions would alter analytical meaning. Physical enforcement is engine-specific; the business invariant is not.
8. Freshness is a time-relative production test
The fixture evaluates at 2026-09-21T08:20:00Z. The
latest staging event is 07:10Z, yielding a
70-minute lag against the declared 60-minute objective. That is
a warning in this exercise because a planned ERP maintenance
window is documented. A CI fixture should not depend on “now”
from the wall clock; it should inject a fixed reference time so
results are deterministic.
9. False positives, tolerances, and ownership
Ranges and anomaly thresholds should come from business contracts, not arbitrary statistical folklore. A genuine return could be negative in a returns fact but invalid in this paid-sales fact. A zero amount may be valid for a free promotion but not for booked revenue. Every rule needs scope, grain, severity, owner/steward, and an action. Otherwise a growing list of alerts becomes noise rather than a control system.
10. Repair and reprocess
- Keep all 13 delivered rows in immutable raw/staging evidence.
- Classify rows 11–13 with deterministic reason codes.
- Certify only the 10 accepted rows.
- Route the grain conflict to the correction/restatement owner rather than append it.
- Repair source data or approve a governed mapping change.
- Reprocess the quarantined row with the same stable identity; prove rerun does not duplicate accepted facts.
11. Production judgment and bridge
Correctness: quality tests are scoped to the declared process and grain. History: a current customer existence test is weaker than event-time SCD resolution. Retry/replay: delivery duplicates are expected in at-least-once systems and must converge. Security: quarantine payloads may be more sensitive than curated marts; apply tighter access/retention. Observability: trend reject counts and reason distributions. Cost: large relationship scans may need incremental/partition-aware strategies, but correctness comes first. Lesson 3 now tests the historical and incremental mechanisms themselves.
12. Verification checklist
- Confirm 13 staging rows produce exactly 3 quarantined records and 10 accepted facts.
- Confirm accepted controls reconcile to 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit.
- Confirm the duplicate event and grain conflict have different diagnostic meanings.
- Confirm freshness uses a fixed fixture time and explicit 60-minute SLO.
- Confirm no invalid record is silently defaulted or dropped without evidence.
Knowledge check
Acceptance questions
- Why can row-count equality still hide corruption?
- Why is a new event ID at an existing business grain dangerous?
- Why is quarantine preferable to silent deletion?
- When can a negative value be valid in one fact and invalid in another?
Review the answers
1. Equal counts do not prove the same rows, measures, relationships, or business meaning.
2. It can create two current facts for one declared grain unless handled as an explicit correction/version.
3. It preserves evidence, ownership, repair/replay, and reconciliation.
4. Validity depends on the business process/metric contract—for example returns versus paid sales.
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.
13. Lab cleanup/reset
Close the in-memory database and rerun the fixture to reproduce all three quarantined records deterministically. No production quarantine or source system is touched.