Source semantic understanding: a key to data program success

Most "the numbers don't match" problems after go-live are not pipeline bugs. They come from not knowing what the source data means and how the source system behaves. This post draws the six questions to ask about every source, who can answer them, where to write the answers down, and why "matches the trusted report" is the only real definition of done.

Ravi's team connects the new lakehouse to the sales system. Every table lands, every load is green, and the dashboard goes live on time.

Three weeks later, finance sends one line: "Your revenue for October is too high." The pipeline has no bug. It loaded exactly what the source holds, which includes orders that were later cancelled, invoices that were reversed with a minus entry, and dates the business doesn't report on.

Nobody asked what the data means. That is source semantics, and it decides whether a data program is trusted or quietly ignored.

Plumbing is not meaning

Every source needs two kinds of understanding. The first is plumbing: where the source lives, how to connect, how to pick up changes. Architects and engineers are good at this, and tools make it easier every year.

The second is meaning: what one row stands for, which records count, which date matters, what a blank means. No connector can tell you this. It lives in people's heads and in the habits of the source system.

two kinds of understanding; tools only help with onePLUMBINGSourceapp, databaseLaketables landwhere it liveshow to connecthow to pick up changesWhen broken, it shoutsthe load failsMEANING₹10,000status 079999-12-31?what one row iswhich records countwhich date mattersWhen wrong, it whispersthe load succeeds
Plumbing failures stop the load. Meaning failures let it succeed with the wrong answer.

Plumbing problems shout. A failed connection stops the load and someone fixes it that night. Meaning problems whisper. The load succeeds, the numbers look plausible, and the mistake is found weeks later by the person whose trust you most needed.

Six questions for every source

You don't need a 200-row questionnaire. Almost every meaning problem falls under one of six questions.

almost every meaning problem falls under one of theseOne sourcesix questionsWhat is one row?grain, key, master copy1What life does it live?statuses, fixes, deletes2What time is it?which date, zone, year3What do values mean?codes, money, blanks4How do records connect?header and lines, hierarchies5What can we trust?test data, trusted report6
Six questions for every source. The sixth, which report the business trusts, is the one that proves the rest.
  1. What is one row? The grain: an order, an order line, a daily snapshot, an event. Which key identifies it, and can that key change, be reused or be merged (two customer records become one)? Is this system the master copy, or a copy of something else?
  2. What life does a record live? The statuses it moves through, such as draft, approved, posted and cancelled, and which of them count for reporting. How mistakes are fixed: overwritten, reversed with a minus entry, or cancelled and re-entered. What "deleted" means: gone, flagged, or archived.
  3. What time is it? Most records carry several dates: when it happened, when it was posted, when it takes effect, when it was last changed. Which one does the business report on? Which time zone? A calendar year or a financial year? Can a closed month still change?
  4. What do the values mean? What code 03 or type ZOR stands for. Whether amounts are in one currency or many, which exchange rate applies, whether tax is included, and whether a credit is a negative number or a flag. What a blank, a zero or a date like 9999-12-31 means.
  5. How do records connect? Header and lines that must arrive together. Hierarchies such as cost centres or product groups, and how they change over time. Company codes or regions that follow different rules.
  6. What can we trust? Test and dummy records, and how to spot them. Fields used for something other than their name ("we keep the region in remark2"). And most important: which existing report does the business already trust, and what does it show for a closed month?

Two orders, two totals

Here is why question 2 matters so much. Follow two orders of ₹10,000 each through the sales system.

the same correct data, two different totalsOrder ADraftApprovedPosted₹10,000a week laterReturn−₹2,000, a row of its ownOrder BDraftCancelledamount still ₹10,000Naive sum: ₹18,000every row, every statusFinance: ₹8,000posted, less returns
Nothing is broken. The only difference is knowing which statuses count and how returns are booked.

It is created as a draft, approved, and posted. A week later a return is booked as a separate minus entry of ₹2,000. The customer's second order, also ₹10,000, is cancelled before it ships.

Load all of that naively and sum the amounts, and the lake says ₹18,000. Finance says ₹8,000: one posted order, less the return. Both numbers come from the same correct data. Only one of them is the business's meaning of revenue.

The fix is not code you can write before you know the rule. It is one question asked early: "Which statuses count, and how are returns booked?"

Who can answer

The people in the first architecture meeting usually can't answer these questions, and that's fine. Their job is to tell you who can.

three people, three kinds of answerArchitectKey userData engineerWhere it livesand how to reach itWhat it meanswhich rows count, and whyWhat the data saysa profile of real rowsshowsOne short session per sourcepeople react to real data far better than they remember rules
Ask the architect who knows. Then put a profile of real data in front of that person.

Bring the profile to a short session per important source. People are poor at recalling rules from memory and very good at reacting to real data. "What does status 07 mean? There are 4,000 of them" gets an answer in seconds.

Write it down once, use it three times

The answers belong in the dataset's own record, next to its loading settings, not in a slide deck. If you already describe each table with one row of settings, add a short meaning section to the same record.

dataset: sales.orders
grain: one row per order line
key: order_id + line_no (unique per company_code)
report_date: posting_date (India time)
counts_when: status in (POSTED, CLOSED)
returns: separate rows, negative amount
amount: INR, excluding tax
blank_ship_date: not shipped yet
trusted_report: Finance monthly sales pack
write the meaning down once, next to the loading settingssales.ordersgrainone row per order linekeyorder_id + line_noreport dateposting_datecounts whenPOSTED, CLOSEDamountINR, excl. taxtrustedFinance sales packQuality rulesstatus known, key unique, run on every loadCatalog descriptionsusers read the meaning, not guess itReconciliationlake total matches the trusted report
One semantic record, three payoffs. The last one, reconciliation, is the definition of done.

That one record pays off three times:

  1. Quality rules. "Status is one of the known values" and "the key is unique per company code" become checks that run on every load.
  2. Catalog descriptions. People using the table read what it means instead of guessing.
  3. Reconciliation. A check compares the lake's total for a closed month with the trusted report and shows any gap.

This is the meaning part of a data contract. The contract says what the producer promises. The semantic record says what the data means when it arrives.

The real definition of done

A source is not onboarded when its tables land. It is onboarded when the lake's numbers match the trusted report for one closed period, within a tolerance the business agreed.

Make that the exit test for every source. It forces the meaning questions early, because you cannot pass it without them. And it gives the business a reason to trust the new numbers that has nothing to do with how modern the platform is.

What goes wrong

Five questions to check your own program

  1. For your most important dataset, can you say in one sentence what one row is?
  2. Which statuses count for reporting, and is that rule written where a machine can check it?
  3. Which date does your main metric use, and does finance use the same one?
  4. Is there a named report the business trusts, and have you matched it for a closed month?
  5. Who is the key user for each source, and have they seen a profile of their own data?

Connecting to a source takes a week. Understanding it is what makes the next three years of numbers believable.