The ETL Passed. The Business Meaning Didn't
4 min read

The ETL Passed. The Business Meaning Didn't


There is a particularly frustrating kind of data bug: the pipeline is green, the row count matches, the types are correct, and the numbers are plausible.

The report is still wrong.

No database exception is going to tell you that a “customer” field actually names the company that submitted the transaction rather than the company that bought the product. The SQL engine knows both are strings. It has no idea what the business intended to measure.

That is why I no longer treat schema validation and reconciliation as the end of data quality. They establish that data moved and conformed. They do not establish that the resulting model means what its labels claim.

A tiny example that passes the easy tests

Picture a fictional marketplace sending these rows:

ordersellerbuyeramount
A101S1B7$120
A102S1B8$80
A103S2B7$50

The fact table receives all three rows. The total is $250. Nothing is null. A dimension lookup finds both sellers. A report grouped by customer_key shows S1 with $200 and S2 with $50.

The number is correct; the label is not. If the report promises sales by buyer, the intended answer is B7 with $170 and B8 with $80.

The same valid rows grouped by seller or buyer produce different business answers despite equal totals.

The grand total cannot tell us which relationship was modeled.

This example is intentionally simple. Real systems carry several plausible identities and use imperfect labels such as account_id or customer_id for different relationships. Some source records genuinely lack an end consumer. The mistake is not having more than one identity. It is allowing a field name or convenient join to decide which identity a metric represents.

Four contracts, not one

I now think of correctness as several contracts that should be tested separately.

Shape. Are the required columns present? Can values be parsed? Do types and null policies hold?

Grain. What exactly is one row? An order, order line, shipment, revision, or something else? A join that accidentally multiplies rows is a grain failure even when every foreign key resolves.

Identity. Which entity does each key denote? A billing account, owner, seller, recipient, and end user can all be valid identities. “Resolved successfully” is not the same as “resolved to the intended role.”

Meaning. Which dates, statuses, currencies, effective mappings, and exclusions define the metric? Two clean datasets can answer different questions with identical totals.

Only the first contract is routinely covered by a file schema. Grain and identity need examples; meaning needs a decision from the people who own the metric.

Test the questions, not just the tables

The most useful acceptance tests I have seen start with a plain-language assertion and a small synthetic case:

When an order has a seller and a different buyer, a “sales by buyer” report must group it under the buyer. If the buyer is unknown, the report must show an explicit unknown category rather than silently substituting the seller.

Then write a test that makes those two roles differ. A fixture where seller and buyer are always identical will happily approve the wrong join. That is a common weakness of realistic-looking test data: it can reproduce ordinary rows while never exercising the ambiguity that matters.

For a metric, I want three examples: a normal row, a deliberately ambiguous row, and a row with missing context. The examples should assert both the final value and why that value was chosen. A traceable resolution reason can be more useful than another overall pass percentage.

This is complementary to finding rows excluded before a reject table and handling two legitimate customer identities. Those posts concentrate on particular failure surfaces. The broader point here is that a model can be operationally healthy and semantically broken.

Don’t make every uncertainty a hard failure

An unknown buyer does not automatically mean a bad transaction. Sometimes the source never had that information. Blocking the entire load would make the pipeline less useful without making the data more truthful.

I’d rather preserve the event, express the uncertainty, and keep the reporting contract clear: known buyer, unknown buyer, and not applicable should not collapse into a made-up identifier. Where business policy explicitly permits a fallback, the output should make that choice understandable.

There is a genuine trade-off here. Recording more semantic state complicates modeling and report design. But erasing ambiguity by choosing a convenient key moves that complexity into invisible business errors.

The question I try to ask before signing off a migration is no longer just “Did the new pipeline match the old one?” It is “What question is this dataset supposed to answer, and can our tests distinguish that question from a nearby, plausible, wrong one?”

Next in “Microsoft Fabric migration and data correctness” A Data Pipeline Is a State Machine Wearing a DAG →