ERP Data Quality Checklist

A profiler will tell you a column is 3% null, that a string column holds four distinct lengths, and that a number is normally distributed. None of those findings is the reason a supply chain report disagrees with the ERP. The reasons are structural, and they are specific to how ERPs store things: a declared key that stopped holding when a CDC batch landed twice, a company column dropped from a join, a date that is still a six-digit number, a feed that never hears about a deleted row.

So the checks worth writing are not profiles. They are assertions against something the ERP already documents — the key on the table page, the relationship in the reference, the watermark in the extraction guide — and each one is a query that returns a number you can trend.

Which means every check on this page is the same shape. It is a Σ: a count, or a sum, at a stated grain. It is scheduled, not run by hand. And it lands a row in a check-results table with its measured value and its verdict, because a check you ran once during the build is an anecdote, and the failure you care about happens in week eleven.

Two of the checks below have a page of their own in this library and are only summarized here. Delete drift — the failure where a feed can’t see a removed row — is the whole subject of deletes & change tracking. Reconciling accumulated copies against the flows that produced them is the standing rule of inventory snapshot patterns.

Shorthand on this page: CDC is change data capture.

The check. For every silver table, group by the documented primary key and keep the groups with more than one row. Expect zero. Run it after every load, on every table, and record the count — this is the check every other number on the platform inherits, because a duplicate at the key inflates every join, every sum, and every ratio built above it.

The key is not something you infer from the data. Every table page in this library documents it, which is what makes this assertion cheap to write and worth trusting.

Two causes worth naming. The first is the tenant or company column left out of the key — the subject of check 3, the partition column, and the reason a key can hold in the source and fail in your copy. The second is the staged CDC batch: several change rows for one key arrive in one file, and inserting them all duplicates the key even though the source never did. The fix is the landing pattern in each reference’s extraction or quirks guide — dedupe by full key keeping the latest watermark, then MERGE (the SAP instance is in that reference’s landing section).

-- Check 1: does the declared primary key still hold? One row per key, or the
-- table is not at the grain its documentation claims. The key columns below
-- are not guessed — every table page in this library documents them.
-- Include the tenant / company column: it is part of the key in most of
-- them, and leaving it out is the commonest reason this check passes on a
-- table that is in fact duplicated.
-- Placeholders only — the real column names are on that table's own page.
SELECT
  <key_column_1>,
  <key_column_2>,
  COUNT(*) AS row_count
FROM <catalog>.<silver_schema>.<table>
GROUP BY <key_column_1>, <key_column_2>
HAVING COUNT(*) > 1
ORDER BY row_count DESC;

-- When it fails on a freshly landed table the cause is usually the staged
-- batch rather than the source: several change rows for one key arrived in one
-- file. Dedupe by full key keeping the latest watermark before the MERGE --
-- the landing pattern in each reference's extraction guide already does this.
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY <key_column_1>, <key_column_2>
    ORDER BY <watermark_column> DESC
  ) AS rn
  FROM <catalog>.<staging_schema>.<table>
) WHERE rn = 1;

The check

For each relationship the reference documents, anti-join the child to the parent, count the rows that find no parent, and trend the count. Lines without headers, movements pointing at items that aren’t in your item table, orders against customers you never landed — these are the failures that make a dashboard quietly incomplete rather than visibly broken.

Blanks and zeros are not orphans

They mean “no reference”. JD Edwards and Dynamics 365 both write empty strings and zeros where other systems write NULL (JDE, D365), and M3 stores 0 for “no date”. Exclude the sentinel on the child side before counting, or the orphan number is mostly rows that were never supposed to point anywhere.

Decode both sides first

An anti-join compares encodings, not meanings. Mismatched GUID forms in EWM and untrimmed padded business units in JD Edwards both produce a 100% orphan rate that has nothing to do with integrity — the details are in those references’ own quirks guides.

Alert on the change, not the level

An orphan count that is stable and explained — a legacy code range never migrated, a document type you deliberately don’t land — is a documented fact about your platform. Write down the expected number and alert when it moves.

-- Check 2: orphans against one documented relationship. LEFT ANTI JOIN keeps
-- the child rows that find no parent, which is exactly the orphan set.
-- Two guards, or the number means nothing:
--   1. Blanks and zeros are "no reference", not a broken one. JDE and D365
--      write '' and 0 where other systems write NULL, and M3 writes 0 for
--      "no date" — exclude the sentinel on the child side before counting.
--   2. Both sides have to be decoded and trimmed to the same encoding first.
-- Then trend it. A stable, explained orphan count is a documented fact, so
-- alert on the change rather than on the level.
SELECT COUNT(*) AS orphan_rows
FROM (
  SELECT *
  FROM <catalog>.<silver_schema>.<child_table>
  WHERE NULLIF(TRIM(<child_key_column>), '') IS NOT NULL
    -- and the numeric sentinel: JDE and D365 write 0, not NULL, for
    -- "no reference" on a numeric key column.
    AND <child_key_column> <> <no_reference_sentinel>
) AS c
LEFT ANTI JOIN <catalog>.<silver_schema>.<parent_table> AS p
  ON  p.<partition_column> = c.<partition_column>
  AND p.<parent_key_column> = c.<child_key_column>;

This is the strongest synthesis on the page. Every ERP in this library ships a tenant, company, or organization column; every one of them produces plausible-but-wrong numbers when that column is dropped from a join or a group-by; and not one of them raises an error when it happens. The failure is always the same shape — two entities summed into one figure that trends nicely and reconciles to nothing.

The check, in two parts. First, row counts by partition value, asserted against the short list of values you expect — a value you don’t recognize is either a new legal entity or a landing mistake, and both are worth an alert. Second, and less mechanical: the partition column appears in every join, every group-by, and in every other check on this page, including the key check and the orphan check.

The column, by reference: SAP MANDT, which is part of the key rather than merely a filter; Dynamics 365 DATAAREAID; Infor M3 CONO; Oracle EBS both ORG_ID and ORGANIZATION_ID, which are two different organizations and the single most common first-extract bug; Oracle Fusion’s ORG_ID, which carries the same name as the EBS one and means business unit instead; NetSuite subsidiary under OneWorld; SAP EWM MANDT plus LGNUM, four characters wide against classic WM’s three; and JD Edwards company keys, alongside the branch / business-unit keys (MCU) that are right-justified (space-padded on the left) and need trimming on both sides before the comparison is even valid.

The rule is inherited from the rest of this library: conversions happen once, in silver, never in the query. Which makes the check an output check — you are not validating the raw column, you are asserting that what came out of the conversion is inside a plausible domain.

Dates

Min and max inside a plausible business range, and no surviving raw artifact. A surviving artifact has a signature that names its source: a “date” of 126236 is an unconverted JD Edwards Julian value, a numeric 0 is an M3 no-date, '00000000' is an SAP DATS unset, and 1900-01-01 is a Dynamics 365 sentinel.

Quantities and amounts

Magnitude-check one converted figure against a number someone can read off the ERP screen. Off by a power of ten is the JD Edwards implied-decimal conversion not being applied — and it is invisible in a profile, because the distribution is perfectly well-behaved.

Booleans

No 'T' / 'F' strings survive past silver. That is a NetSuite trap specifically, and the reason it earns a check is that a naive truthiness test passes for both values.

Coded columns

Assert every value resolves in the decode table the reference documents — the UDC table for JD Edwards, the generic code table for M3, the lookup table for Oracle EBS, the enum metadata for Dynamics 365. Two of them change the check rather than skipping it, because the set they decode against isn’t closed. NetSuite status values are real but unpublished, and Dynamics 365 enums are extensible per deployment, so an ISV can add a member your CASE has never seen. For both, inventory the values you observe and alert on new ones instead of asserting a closed set.

The check, in two parts. Freshness is now minus the maximum watermark in the table, per partition value, compared against the cadence the feed promised — not against a feeling about how recent the data looks. Completeness is row count per posting-date bucket against a trailing window, so a load that landed empty shows up as a hole rather than as a quiet dip in a chart three weeks later.

A watermark is a claim, not a guarantee. That is the caveat that makes this check honest. Each reference’s extraction or quirks guide documents what its watermark can’t see, and the answers differ: Oracle EBS’s LAST_UPDATE_DATE gets bulk-stamped by batch jobs and never stamps a hard delete; JD Edwards splits its update time across two column aliases and omits audit columns entirely on some constants tables; Infor M3’s LMDT is date-grain, so ties need CHNO to break them; and NetSuite’s modification stamp sees inserts and updates only. The questions to put to a watermark before you trust it as a completeness check are in the seven-question source-history test, which asks of each source object whether updates can arrive without moving the cursor, how deletes are represented, and which of its four timestamps the check is actually reading.

-- Check 5a: freshness. Now minus the newest watermark in the table, per
-- partition value, against the cadence the feed promised. The watermark column
-- is whichever one that reference's extraction guide names — it is not always
-- a timestamp, and on JD Edwards it is a Julian number.
-- On the JDE case TIMESTAMPDIFF can't read UPMJ directly: convert the CYYDDD
-- number to a DATE first (the canonical expression is julianDateExpr in
-- src/lib/jde/julian.ts, rendered on the JDE quirks page).
SELECT
  <partition_column>,
  MAX(<watermark_column>) AS newest_row,
  TIMESTAMPDIFF(HOUR, MAX(<watermark_column>), CURRENT_TIMESTAMP()) AS hours_behind
FROM <catalog>.<bronze_schema>.<table>
GROUP BY <partition_column>
ORDER BY hours_behind DESC;

-- Check 5b: completeness. Rows per posting-date bucket against the trailing
-- average of the buckets before it. A hole is a load that quietly did not land;
-- a spike is usually a batch job bulk-stamping rows, not a busy day.
-- Grouping the fact table alone would hide the failure this check exists for:
-- a day with no rows is an absent group, not a visible zero, and the trailing
-- window would then count 28 rows rather than 28 days. So drive it off a date
-- spine and LEFT JOIN the facts onto it.
WITH spine AS (
  SELECT EXPLODE(SEQUENCE(
    CURRENT_DATE() - INTERVAL 90 DAYS,
    CURRENT_DATE(),
    INTERVAL 1 DAY
  )) AS bucket
),
daily AS (
  SELECT
    s.bucket,
    COUNT(f.<posting_date_column>) AS row_count
  FROM spine AS s
  LEFT JOIN <catalog>.<silver_schema>.<table> AS f
    ON f.<posting_date_column> = s.bucket
  GROUP BY s.bucket
)
SELECT
  bucket,
  row_count,
  AVG(row_count) OVER (
    ORDER BY bucket
    ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
  ) AS trailing_avg
FROM daily
ORDER BY bucket;

The check. Capture a control total on the source side at extract time — a row count and one summed measure, grouped by the partition column and a coarse date bucket — and run the same query over your landed copy. Compare. It is one scheduled query on each side, and it is the only check here that can fail when everything else passes.

It catches two drifts nothing else does. The first is delete drift: a feed that can’t see a removed row leaves it in bronze forever, so counts run high and no other check notices, because every row present is individually valid. Which feeds are exposed and what to do about each is the subject of deletes & change tracking. The second is value drift: a correction lands in the ERP against a date you already loaded, and your copy of that date is a photograph of a number that has since changed — the restatement problem worked through in inventory snapshot patterns.

Two practical notes. Group by a coarse bucket — month, or week — so the comparison survives timezone and posting-time differences that would make a day-level compare noisy without being wrong. And run it against a trailing window rather than all of history, so the cost stays flat as the platform grows.

Bronze: structural only

Did the key hold after dedupe, did the watermark advance, are the partition values the set you expected? Nothing semantic belongs here, because bronze is still shaped the way the source publishes it. When a batch fails one of these, quarantine it — write it somewhere you can inspect rather than dropping it silently, because a batch that vanishes is indistinguishable from a batch that never arrived.

Silver: semantic

This is where the conversions have happened, so this is where they get checked: dates, quantities, booleans and codes domain-checked on their outputs, referential integrity run against the documented relationships, statuses and coded columns resolving in their decode tables.

Gold: business

Control-total reconciliation to source, and the components of every published ratio — never a stored ratio, which is the standing rule of inventory snapshot patterns.

The implementation

Scheduled Databricks SQL, writing one row per check per run into a single results table: check name, table, partition value, run timestamp, measured value, and the verdict. The same plain raw + MERGE register the extraction guides use — nothing on this page needs a framework, and a results table you can query is worth more than a dashboard you can’t.

Every check above produces a number. None of the published standards will tell you which number is bad, and they decline on purpose.

The standards decline the job

ISO 8000-8 requires that a business-pertinent threshold be established, and says in the same breath that it does not set one; it likewise calls for the scales a measurement is made against to be stated rather than defining them. ISO/IEC 25024 says in its scope that it does not define ranges of values to rate levels or grades. One caveat about where those sentences live: both ISO 8000-8 sentences sit in its Introduction, which is informative rather than normative under ISO drafting rules, so read them as that standard declining the job and not as a normative prohibition on setting a threshold. The 25024 sentence is a scope statement rather than introductory text, and it declines the same job for its own reason — it defines measures and leaves the ranges that would rate them to you. The consequence is the same either way. A threshold is an authored decision that needs a provenance, exactly like the decision switches every definition in the supply chain KPI dictionary makes explicit.

What is standardized is a counting record, not a ratio

The nearest thing to a standardized metric in this area is a count: per rule, how many checks were performed and how many occurrences complied, plus a list of the rules that were defined and not checked. That is the results table above — one row per check per run, carrying the measured value and the verdict. The standard specifies the record. The threshold is what you add to it.

Provenance one: the check’s own history

The first threshold for any check is no threshold. Alert on a change in the measured value rather than on its level — the same rule the orphan check above states, generalized to every check on this page. The platform’s built-in anomaly monitoring is exactly this mechanism, and it is one-sided on both metrics. Freshness predicts the next commit time from the table’s own commit history and marks the table stale when a commit arrives unusually late; completeness predicts an expected row-count range and marks the table incomplete when a 24-hour count falls below the lower bound — neither flags the other direction. Three caveats travel with it. It records rather than enforces; it is a preview feature, so it is not something to build a control on yet; and what it calls “completeness” is a question about row counts rather than completeness in the sense the standards use the word.

Provenance two: a declared consumer tolerance

Once a consumer has promised something to somebody, the threshold is that promise. A freshness check with a warn-after and an error-after stated against the cadence the feed was promised at is a declared target, and it is the one number on this page that does not come out of the data. Be careful with the vocabulary around it: service level indicators, objectives and agreements come from service reliability engineering, where the subject is a service, and no located standard defines a data SLA or a data SLO. That is a confirmed absence across the standards surfaces checked here, not a claim that the idea is wrong — borrow the construct if it helps, and do not cite it as a standard. And it is bounded rather than closed: the two ISO cloud service-level texts, the SLA framework and its metric model, were identified by title but could not be opened, so they are unread here rather than checked and found silent.

Provenance three: enforcement

A small number of rules are load-bearing enough that a violating row is worse than a stopped pipeline. Those stop being thresholds and become constraints — NOT NULL and CHECK on the write, or a fail-mode pipeline expectation — which reject the write rather than counting it. A drop-mode expectation sits between the two: it discards the invalid records and lets the rest of the write through, and it counts what it dropped.

The asymmetry: the strictest setting records nothing

A pipeline expectation set to fail aborts the update on the first invalid record, so there are no violation metrics to trend; a warn-mode expectation writes the invalid records through and counts them, which is worth far more when the question is whether quality is getting worse. Enforcement strength and measurement coverage pull in opposite directions. Enforce the few rules that must never pass, and record the many.

Two vocabulary cautions

The data quality “dimensions” taxonomy — accuracy, completeness, consistency, timeliness and the rest — is not settled, and the survey work on it says so: there is no standard for the dimensions of data quality or for their definitions — qualified in the same sentence, since one does exist in the statistical institutions domain, unpublished as a separate product — and the major published lists agree on only a handful of names, with the same territory occupied under different names on either side. And the “five pillars of data observability” is a vendor’s description of a category it says it named, carrying no numerator, denominator, threshold or unit for any of the five. Neither is wrong. Neither is a specification, and neither will settle a threshold argument for you.

The dimension nobody can compute

Accuracy is the one everybody asks for and nobody defines computably. Across every surface checked here — the standards previews as far as each was readable, the survey’s own accuracy material, a national quality framework, the platform’s monitoring metrics and the transformation tool’s tests — no definition of accuracy carries a numerator and a denominator. They state closeness or correspondence to the real value, which is a meaning rather than a measurement procedure. Bounded honestly: the one standards clause that might carry one is paywalled, so that part is unread rather than absent. The practical consequence is that an accuracy threshold, when somebody asks for one, is really a threshold on the six check families above — each of which does have a numerator and a denominator.

The rule, in order

A threshold starts as change detection against the check’s own history, because that needs nobody’s agreement. It becomes a declared target when a named consumer signs one, and the consumer’s name is part of the threshold. It becomes enforcement only when a violated row is genuinely worse than a stopped pipeline. Run those stages backwards — a hard constraint on a number nobody agreed to — and the first month of a quality program is spent failing loads over a rule that turns out to be wrong. Which gold objects are written at all, and so can carry a constraint, is a promotion question, answered in when a gold view becomes a table.