Chapter 12 · Source System Profiling, Data Contracts, Lineage, and Ingestion Readiness
Profile Nulls, Uniqueness, Ranges, Distributions, Referential Integrity, Duplicates, and Drift
Profile AtlasMart ERP, CRM, and inventory extracts for nulls, uniqueness, ranges, distributions, relationships, duplicates, and drift, and distinguish a profiling signal from an automatic cleansing rule.
Learning outcomes
The source inventory says ERP order lines have key
(order_id,line_no) and positive quantities. That is
a claim. Before designing transformations, AtlasMart needs
evidence that the current extract actually satisfies those
assumptions—and a controlled failure that proves the profiler
would detect violations.
Distinguish structural profiling from business validation and explain what each can and cannot prove.
Compute row counts, null counts, key uniqueness, numeric ranges, categorical distributions, and relationship/orphan checks.
Use source/control totals to reconcile the corrected Chapter 11 sales fixture before ingestion.
Inject duplicate and orphan rows, reject them explicitly, and avoid silent coercion.
Treat drift as a signal requiring context rather than a universal automatic failure threshold.
Chapter 12 preserves the accepted AtlasMart warehouse contracts from Chapters 01–11. The current published sales state after Chapter 11 contains eight current paid order-line facts, five paid orders, ten units, 690 paid GMV, 425 cost-at-sale, and 265 gross profit. Customer history still uses half-open business-effective intervals, source-qualified durable identity, governed unknown members, and auditable corrections. This chapter moves upstream: it defines what evidence a source must provide before new ingestion or transformation code is trusted. The synthetic source extracts therefore reflect the accepted Chapter 11 corrections rather than silently reverting to the original 625-GMV fixture.
The mandatory exercises are free, local, synthetic, and deterministic. They were validated with Python 3.13.5 using only standard-library data structures/JSON/hash functions. The examples prove the stated fixture, contract, and gate behavior; they do not prove production-source correctness, network extraction behavior, organizational ownership, legal compliance, or the behavior of a managed ingestion service. Treat every source statement as a contract that must be verified against the actual producer.
Tiny synthetic distributions are pedagogical evidence, not production baselines. A shift in counts or category mix can be legitimate seasonality, a source incident, or a semantic change; production thresholds require historical evidence and ownership.
1. Profiling asks “what is present?” before transformation asks “what should it become?”
Profiling computes descriptive evidence about
source data: row counts, null rates, uniqueness, ranges, value
frequencies, relationships, and changes over time. It is not the
same as cleansing. If quantity='two', profiling
should expose that violation; it should not quietly convert it
to zero and allow downstream totals to reconcile to the wrong
business state.
| Dataset | Rows | Duplicate business keys | Key observations |
|---|---|---|---|
| ERP orders | 6 | 0 | 5 paid / 1 pending; channels web/mobile/sales |
| ERP order lines | 9 | 0 | quantity 1–2; unit_price 25–200; all-line value 1090 |
| CRM customers | 4 | 0 | 4 unique IDs; one erased synthetic identity marker |
| Inventory snapshot | 4 | 0 | on_hand min 12 / max 60 / sum 137 |
The current fixture has no duplicate declared keys. That does not prove the source key will remain unique forever; it gives a deterministic baseline against which the injected failure can be compared.
2. Reconcile source evidence to the accepted warehouse control
The ERP extract includes one pending order (O1004). Therefore
all line value is not the same metric as paid GMV. A
profiler should preserve both observations instead of “fixing”
the pending row away. Applying the metric contract—parent order
status must be paid—produces the Chapter 11 current
control:
| Control | Accepted value | Meaning |
|---|---|---|
| Current paid order-line rows | 8 | Chapter 11 current-revision facts |
| Paid orders | 5 | distinct paid order_id |
| Paid units | 10 | sum quantity on paid lines |
| Paid GMV | 690 | sum quantity × unit_price on paid lines, USD |
| All order-line value | 1090 | includes pending O1004; not paid GMV |
| Inventory on-hand units | 137 | single 2026-09-20 snapshot; not additive over time |
This distinction is critical: a source-control total can be correct while a KPI is wrong if the KPI filter, unit, or grain is wrong.
3. Keys and relationships must be profiled, not assumed from DDL
Operational primary keys can be missing from exports, disabled, reused, or only unique within a source partition. AtlasMart checks the composite order-line key and three relationships: every line has an order, every order has a CRM customer representation, and every line product exists in the inventory/product reference fixture.
| Check | Observed violations |
|---|---|
| orphan_order_lines | 0 |
| orders_without_customer_reference | 0 |
| order_lines_without_product_reference | 0 |
Zero violations is an observation for this fixture. The contract still needs an explicit policy for late-arriving or deleted dimension/reference records because a future extract can legitimately violate present-time referential integrity.
4. Deliberately wrong approach: assume keys are clean and repair joins with DISTINCT
An engineer duplicates O1002 line 1, adds an O9999/P999 line,
joins everything downstream, and applies
DISTINCT to make the row count “look normal.”
DISTINCT can hide duplicates while also collapsing genuinely
separate rows if selected columns happen to match. It does not
establish the intended business key or explain the orphan.
The safer mechanism is to profile declared keys before transformation, quarantine violations with reason codes, preserve raw evidence, and require the owner to decide whether the source contract or the data is wrong.
5. Local lab — baseline profile and controlled failure
from collections import Counterorders={"O1000":"paid","O1001":"paid","O1002":"paid","O1003":"paid","O1004":"pending","O1005":"paid"}lines=[ ("O1000",1,"P300",1,75.0),("O1001",1,"P100",2,50.0),("O1001",2,"P200",1,25.0), ("O1002",1,"P300",1,190.0),("O1003",1,"P400",1,100.0),("O1003",2,"P200",2,25.0), ("O1004",1,"P300",2,200.0),("O1005",1,"P100",1,50.0),("O1005",2,"P400",1,100.0)]products={"P100","P200","P300","P400"}def report(rows): keys=[(r[0],r[1]) for r in rows] dup=len(keys)-len(set(keys)) orphan_orders=sum(r[0] not in orders for r in rows) orphan_products=sum(r[2] not in products for r in rows) paid=[r for r in rows if orders.get(r[0])=="paid"] gmv=sum(r[3]*r[4] for r in paid) units=sum(r[3] for r in paid) return len(rows),dup,orphan_orders,orphan_products,len(paid),units,gmvprint("baseline",report(lines))bad=lines+[lines[3],("O9999",1,"P999",1,10.0)]print("injected",report(bad))Expected output:
baseline (9, 0, 0, 0, 8, 10, 690.0)injected (11, 1, 1, 1, 9, 11, 880.0)The injected paid GMV jumps to 880 because the duplicate O1002 line is counted again; the orphan row has no parent status so it is not paid. The profiler exposes the mechanism instead of masking it.
6. Distribution and drift: useful signal, dangerous shortcut
AtlasMart records status/channel/segment/category frequencies and numeric ranges because they make unexpected change observable. But “mobile share changed by 20%” is not automatically a schema error. Drift can reflect a campaign, holiday, customer mix, partial extract, source code change, or a legitimate business shift. The pipeline should attach context—source version, batch ID, time window, and owner—before deciding whether a distribution shift blocks certification.
Similarly, a range check must come from semantics.
quantity > 0 is justified by this sales-line
contract; a universal “quantity must be below 100” rule would be
invented folklore unless the business owner defines it.
7. Reject, preserve, explain
When a row violates a blocking contract, keep the original
representation in a local quarantine artifact, record the
batch/source/reason, and exclude it from certified outputs until
resolved. Do not silently parse "19.99USD" as 19.99
or replace a missing key with an empty string unless a versioned
mapping contract explicitly defines that behavior. Chapter 13
will expand this into full quality/quarantine engineering.
8. Verification and cleanup
- Baseline ERP line key duplicates = 0; relationship violations = 0.
- Baseline paid controls = 8 lines, 5 orders, 10 units, 690 USD GMV.
- Injected fixture exposes exactly one duplicate key, one missing order, and one missing product.
- The lesson does not use DISTINCT as a generic duplicate repair.
- Cleanup: delete the local script; all data is embedded synthetic data.
Knowledge check
Check your understanding
- What does a zero duplicate-key count prove?
- Why is 1090 all-line value not the paid-GMV control?
- Why is DISTINCT not a universal duplicate repair?
- Can a distribution shift be legitimate?
- What should happen to malformed rows?
Review the answers
1. Only that the observed fixture has no duplicates for the tested key; it does not guarantee future source behavior.
2. It includes the pending O1004 line; paid GMV applies the parent-order status contract.
3. It operates on selected row values, not on the intended business identity or duplicate cause.
4. Yes. Drift is a signal that needs source/time/business context, not automatically a data defect.
5. Preserve raw evidence, attach a reason, quarantine/block according to policy, and resolve semantics explicitly.
Summary and next step
Profiling converts source assumptions into observable evidence and controlled failures. Lesson 3 turns those observations into an explicit source-to-target mapping so types, units, time zones, codes, key resolution, and reject behavior cannot hide inside transformation code.
Authoritative references
- W3C — PROV Overview — Official W3C overview of provenance concepts used to reason about entities, activities, agents, and lineage relationships.
- OpenLineage — Specification — Open specification for dataset/job/run lineage events; referenced as a later operationalization option, not a prerequisite for this local lab.
- JSON Schema — Specification — Official JSON Schema specification; useful when a source contract is represented as JSON, while the business semantics still require explicit agreement beyond structure.
- RFC 3339 — Date and Time on the Internet — Authoritative timestamp format reference used for the UTC timestamp contract in the synthetic fixture.
- Python documentation — csv — Standard-library CSV support suitable for free/local deterministic source fixtures.
- Python documentation — json — Standard-library JSON support used for contract and readiness artifacts.
- Python documentation — hashlib — Standard-library hashing API used only for reproducibility/evidence fingerprints, not as a semantic validation substitute.