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.

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 the eight references 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, 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 the eight references documents them.
-- Include the tenant / company column: it is part of the key in most of the
-- eight, 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 two sections below.

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 one of the eight references 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 — F0005 for JD Edwards UDCs, CSYTAB for M3, FND_LOOKUP_VALUES 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. Each ERP section below names its own. 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.

Two of SAP's three checks are about a column and a string that are easy to forget and expensive to forget: the client key that belongs in every key and every filter, and the DATS zero date that is not a date. The third is a release question — on S/4HANA a balance table can freeze without anything failing.

• Key check includes MANDT. MSEG is keyed on MANDT, MBLNR, MJAHR and ZEILE — all four — and the landing MERGE in the extraction guide dedupes staged batches on exactly that key, keeping the latest watermark row.
• Pin the client in every query and every check. The boilerplate across this library carries WHERE mandt = '100'for a reason: a system with more than one client will otherwise hand you two companies' rows under one number.
• DATS zero-date check. '00000000' means unset, not a date — NULLIF before casting, then domain-check that no zero-date artifact survived into silver. It is the trap that fakes a real date on delivery goods-issue dates and on MKPF material-document posting dates alike.
• S/4 stock-freeze check. On S/4HANA the quantities on MARD are computed through NSDM proxy views over MATDOC rather than persisted, so a replicated physical table simply stops moving. Check the balance columns still change between loads — nothing else will tell you (what is on hand right now).
• Orphan check on the documented header/line joins. Every MSEG line should find its MKPF header, and every EKPO line its EKKO header — the relationships this reference documents on the table pages are the check list.

Landing in Databricks: raw + MERGE →Table-level CDC & extraction tools →The zero-date trap on shipped dates →

EWM is the one reference where the encoding check has to run before the referential check, not beside it. Get the GUID form wrong on one side of a join and every row is an orphan, which looks like a catastrophic data problem and is a formatting one.

• GUID encoding check before any join or orphan count. Stock, handling units and deliveries key on RAW16 GUIDs, and the product master carries the same identity in a CHAR22 form — treat the crossing as a conversion, never an equality, or the anti-join returns everything (two key models in one system).
• Open-task completeness. A row leaving /SCWM/ORDIM_O is a confirmation — it lands in /SCWM/ORDIM_C, whose key carries one column more, TAPOS — or a logged cancellation. Check that disappeared opens reappear confirmed, and that your feed's delete branch is actually honored (confirming a task deletes it).
• Stock reconciliation guard. Never sum /LIME/NQUANwithout resolving the handling-unit tree, and don't raise a defect when /SCWM/AQUA disagrees with it: AQUA is available quantity, not on-hand, so the gap is by construction.
• Rename-map check. Slashes are not legal Databricks identifiers, so namespaced tables land renamed — /SCWM/… as scwm_…. Assert the map is one-to-one, because a collision here silently merges two tables (namespaced names in a lakehouse).
• Timestamp domain check. Execution stamps are DEC(15) yyyymmddhhmmss numbers in UTC, sitting beside warehouse-local twins — convert once, then range-check the output (timestamps are UTC numbers).

Raw table CDC and the open-task problem →GUID keys, and the two encodings →The LIME stock model →

JD Edwards has the cleanest movement history in the library and the two encodings most likely to corrupt it without raising anything. Both conversions belong in silver, once; the checks here are on what came out of them.

• Julian date domain check. After the CYYDDD conversion the dates should fall in a plausible business range. A surviving value like 126236 is not an outlier — it is an unconverted Julian date, and it will sort and filter as a number all the way into a dashboard.
• Implied-decimal magnitude check. Compare one converted amount on F4211 against a figure someone can read off the screen in JDE. Off by a factor of ten to the n means the display decimals were never applied.
• Blank/zero orphan guard. JDE stores blanks and zeros rather than NULL, so ' ' and 0mean “no reference” and have to be excluded before an orphan count means anything (no nulls — blanks and zeros).
• Join-key normalization. Business units (MCU) are CHAR(12) right-justified, so an untrimmed join drops every row; and items carry three numbers, ITM, LITM and AITM. Check both sides of every join trim, and use the same identifier (business unit padding, item numbers).
• UDC decode coverage. Every coded value should resolve in F0005 for its own system/type pair — and DRKY is right-justified too, so trim it before comparing (UDC decode).
• Freshness against a moving target. The watermark is UPMJ — a Julian date — plus an update time whose alias changes by table family: TDAY on distribution and manufacturing tables like F4111 and F41021, UPMT across financial and foundation ones, and RPUPMT on F0411. Constants files such as F0002 and F40205 carry no audit columns at all, so full-refresh those instead of watermarking them (audit columns).

Julian dates (CYYDDD) →Implied decimals →Poor-man's CDC: audit columns →

Dynamics 365 F&O is the reference where a missing column, not a missing row, is the failure mode. Company partitioning runs through every application table, and the checks below are mostly about carrying it everywhere.

• DATAAREAID in every key, join, group-by, and check. Leave it out and two legal entities sum into one number that looks entirely reasonable.
• Referential integrity on RecId. Joins ride the 64-bit per-table surrogate — including InventTransto its origin record. One caveat for the check design: polymorphic TableId + RecId references can't be validated with a single anti-join, because the parent table varies by row (RecId, polymorphic refs).
• Enum domain check. Enums land as raw integers, and the set is not closed — an ISV or a customization can add members per deployment. So check each value against the documented set for its column, keep the ELSE CAST fallback so an unmapped value surfaces its raw integer rather than collapsing to NULL, and alert on the unmapped values instead of failing the batch. Resolve the labels through the metadata rather than a hand-written CASE (enums (option sets)).
• Sentinel-date check. 1900-01-01means “never”. Exclude it before any MIN, AVG, aging bucket or freshness calculation, and convert the UTC datetimes first so the date boundary lands where your business thinks it does (UTC datetimes).
• InventDimId resolution. Every dimension id on InventSum or SalesLine should resolve through InventDim — the site, warehouse, batch and serial grain you report at is that join (InventDim).
• Watermark and soft deletes. The documented incremental dedupes on RecId keeping the latest sysrowversion; check that the IsDelete rows are actually being applied as physical deletes downstream, not merged in as ordinary updates.

DataAreaId (company partitioning) →Sentinel dates (1900-01-01) →Incremental & soft deletes →

M3's checks divide neatly: three about reading the columns correctly, and one about the landed Data Lake objects carrying far more rows than you asked for.

• Pin CONO before anything else. The numeric company key sits on essentially every table, and it belongs in the join as well as the WHERE clause so a join never fans out across companies.
• Numeric YYYYMMDD date check. 0is “no date”, not a date: a raw MIN returns 0 and a numeric BETWEEN quietly scoops up every unset row. NULL-wrap, convert, then domain-check. And entry and change dates are in the server's timezone rather than UTC, so don't run a UTC conversion over them.
• Prefix-aware RI. Every column is a two-char table prefix plus a four-char field alias, so MMITNO on MITMAS and MBITNO on MITBAL are the same field. Orphan checks join the documented aliased pairs, not the literal column names (column prefixes & field aliases).
• Status-ladder domain check. Two-char statuses run on ladders, and orders carry a lowest/highest pair — check values against the ladder documented for that table rather than a general list (status ladders).
• CSYTAB decode coverage. Hundreds of logical code tables live inside CSYTAB, discriminated by STCO — assert every coded value resolves for its own STCO, with the language filter applied (CSYTAB).
• Variation dedupe, then freshness. Raw Data Lake objects carry every variation of each record, delete and archive indicators included: keep the highest variation per key and honor the delete indicator, or every updated MITTRA row counts several times. The watermark alternative, LMDT, is date-grain — tie-break it with CHNO (Data Lake variations, audit columns).

CONO (company partitioning) →Numeric YYYYMMDD dates →Incremental & deletes →

Oracle EBS fails quietly at the org column. Two differently-named org ids do different jobs, look interchangeable, and both return rows — so the first check here is not “are there rows?” but “are they the right ones?”

• The right org column per table. ORGANIZATION_ID is the inventory organization — MTL_MATERIAL_TRANSACTIONS and MTL_ONHAND_QUANTITIES_DETAIL stripe on it — while ORG_ID is the operating unit that stripes OE_ORDER_LINES_ALL. Count by both and compare to the org master before trusting either.
• _ALL striping check. Outside the applications there is no implicit org filter: MOAC security never applies to a lakehouse extract, so a landed _ALL table holds every operating unit's rows. Count by ORG_ID against the operating units you expected (MOAC striping in practice).
• Watermark honesty. LAST_UPDATE_DATE is the obvious incremental watermark and it is a filter, not a guarantee — batch jobs bulk-stamp rows and a hard delete never stamps anything. Measure freshness against it, and pair it with a periodic full refresh.
• Referential integrity on surrogate ids. Order numbers, PO numbers and receipt numbers all repeat, so an orphan check written against document numbers reports nonsense. Join the surrogate-key spine (join on ids, not document numbers).
• Lookup decode coverage. Status and type codes decode through FND_LOOKUP_VALUES — assert every coded value resolves for its lookup type, with LANGUAGE filtered, rather than shipping an unverified CASE ladder (decoding coded columns).
• On-hand reconciliation is a SUM. The on-hand rows are FIFO receipt slices, several per item per bin, so a reconciliation that looks up a row instead of summing at the grain will disagree with the ERP forever (on-hand is receipt slices).

ORG_ID vs ORGANIZATION_ID →WHO columns →Incremental & deletes →

Fusion is the only reference on this page where the first check is not on the data at all. BICC hands you files whose headers are view-object attribute names, so a drifted mapping breaks every check downstream while each individual file still looks perfectly well-formed.

• Header-to-column mapping check, first. BICC CSV headers are PVO attribute names — InventoryItemId, not INVENTORY_ITEM_ID — so validate the rename against the .mdcsv and lineage artifacts before anything else runs (BICC headers are PVO attributes, mapping headers into bronze/silver).
• Increment completeness. Incrementals key on each data store's own incremental key column against the stored last-extract date, and the prune window re-delivers rows — so the MERGE has to be idempotent, and the counts on INV_MATERIAL_TXNS want reconciling against a periodic full extract.
• _ALL now means business unit. ORG_ID on a Fusion table such as DOO_LINES_ALL is the business unit, not the EBS operating unit — the same column name changed meaning in the migration, so stripe checks by BU (_ALL now means business unit).
• Date-effective _F dedupe. HR_ALL_ORGANIZATION_UNITS_F carries one row per effectivity window, so assert exactly one effective row per key per as-of date and check for overlapping ranges — otherwise every join through it multiplies.
• Item-org striping and the master org. Items repeat per inventory organization on EGP_SYSTEM_ITEMS_B, with a master org defined in setup — run item attribute checks at the organization the attribute is actually mastered in (item-org striping).
• Counters-versus-ledgers reconciliation. Maintained quantity counters and insert-only event ledgers hold the same truth twice. Pick one as the source, check that the two tie, and never sum both into one measure (counters vs event ledgers).

BICC mechanics: offerings, stores, increments →Incremental & deletes →Date-effective _F tables →

NetSuite sets three traps that no generic profiler is built to see: a boolean that is a string, one table family holding every document at two grains, and a watermark that deletes never reach.

• Boolean cast check. Check-box columns come back as 'T' / 'F' VARCHAR strings, so a naive truthiness test passes for both values and a WHERE flag = truematches nothing — silently. Assert no 'T'/'F' string survives past silver.
• Mainline grain check. Sales orders, purchase orders, fulfillments and journals all live in transaction and transactionline, separated by a type value and the mainline header row. Count documents on mainline rows and measures on line rows, and duplicate-check the spine at both grains.
• Delete-drift check. The modification stamp catches inserts and updates only, and the deleted-record ledger is blind to line-level deletes — so schedule a key-level compare against source, hardest and most necessary at line grain on transactionline.
• Status domain drift. Status is a populated column with no published value list, and its meaning is scoped to the document type. Inventory the observed values and alert when a new one appears, instead of asserting a closed set you can't source (statuses are real, their values aren't published).
• Subsidiary scoping. OneWorld partitions the data by subsidiary, so the stripe is pinned in every check the same way MANDT or DATAAREAID is elsewhere (OneWorld subsidiary scoping).
• Ids-not-text referential integrity. Select columns hold internal ids, so joins run on ids and display text is a decode join against the landed list records — including on item. And on-hand value is a costing output you copy, never a figure you re-derive by summing transactions (internal ids, not display text, on-hand value is a costing output).

Deletes never reach your watermark →Booleans arrive as 'T' and 'F' strings →One table family holds every document →

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.

Nothing new here — every cell restates a section above. Read the second and third columns together: the column you pin decides whether a number is for the right entity, and the watermark caveat decides whether it is for the right day.

Every column of this table is a claim the ERP makes and the lakehouse has to verify.

Six rules survive every ERP on this page, and they are the whole page in six lines.

Check the declared key first. Every other number inherits a duplicate.

Anti-join the documented relationships, and alert on the change, not the level. A stable, explained orphan count is a fact about your platform.

Pin the partition column in every join, group-by, and check. All eight have one, and none of them complains when you drop it.

Convert encodings once in silver. Then domain-check the outputs, not the inputs.

A watermark is a claim. Freshness is measured against it, never assumed from it.

Reconcile counts and sums to source on a schedule. Deletes and reversals don't announce themselves.