Skip to main content

Data quality

MDMongoose checks data at four points. The first one — expectations on the source tables — stops bad rows before they reach master data; the others keep the hierarchy itself consistent.

WhereCheckWhat happens to bad data
Load (pipeline)Expectations of the Table nodeThe row is dropped and counted
Load (merge)Column mapping and transformsValues are trimmed / cased consistently
Golden recordsEligible if on property rules, PropertyType of the golden propertyThe value does not compete / is reported as a conflict
Every editPropertyTypes, Allowed Child Type, Max Child DepthThe save is refused, the cell turns red

Expectations on source tables​

Each Table node has an Expectations list (Configure Load): SQL conditions that every source row must meet. The load pipeline turns them into a Spark Declarative Pipelines expectation with the DROP action (expect_all_or_drop) on the table's change feed:

@dp.temporary_view(name="changes_kna1_2021")
@dp.expect_all_or_drop({"rule_1": "KUNNR IS NOT NULL", "rule_2": "length(LAND1) = 2"})
def changes():
...
  • A row that fails any expectation is dropped: it never reaches rec_<table>, so it never becomes or changes a record in the hierarchy. The rest of the batch loads normally — one bad row does not stop the load.
  • The pipeline counts passed and failed rows per expectation, for every update.
  • Expectations are checked on the trimmed values (empty strings are already NULL).

Writing expectations​

Any SQL boolean expression over the source columns works. Some useful ones:

RuleExpectation
Key presentKUNNR IS NOT NULL
Country codeLAND1 RLIKE '^[A-Z]{2}$'
VAT ID format (if present)STCEG IS NULL OR STCEG RLIKE '^[A-Z]{2}[0-9A-Z]{2,12}$'
Plausible e-mailEmail__c IS NULL OR Email__c LIKE '%@%.%'
Value from a liststatus IN ('active', 'blocked')
Not a test recordNOT lower(NAME1) LIKE 'test%'

Add them in Configure Load → Expectations, one per line, and Save & update pipeline. They apply from the next load; to re-check rows that were already loaded, run one full reload.

Dropped means dropped

A row that fails an expectation is not loaded, and if it was an update of a record that is already in the hierarchy, the record keeps its previous values. If you would rather see bad rows than lose them, keep the rule out of the expectations and check it with SQL on rec_<table> instead (see below).

Checking data quality​

In the pipeline​

Open Jobs & Pipelines → mdm_load, select an update and click the table's dataset (rec_<table>, fed by the changes_<table> view that carries the expectations): the Data quality tab lists each expectation (rule_1, rule_2, …) with the number of passing and failing rows.

In SQL — the event log​

SELECT timestamp,
details:flow_progress.metrics.num_output_rows AS rows_written,
details:flow_progress.data_quality.dropped_records AS rows_dropped,
details:flow_progress.data_quality.expectations AS expectations
FROM event_log('<mdm_load pipeline id>')
WHERE event_type = 'flow_progress'
AND details:flow_progress.status = 'COMPLETED'
AND details:flow_progress.data_quality IS NOT NULL
ORDER BY timestamp DESC;

expectations is a JSON array with name, dataset, passed_records and failed_records for each rule. To see failures per rule over time:

SELECT date_trunc('day', timestamp) AS day, e.dataset, e.name AS rule,
sum(e.passed_records) AS passed, sum(e.failed_records) AS failed
FROM event_log('<mdm_load pipeline id>'),
LATERAL explode(from_json(details:flow_progress.data_quality.expectations::string,
'array<struct<name string, dataset string, passed_records bigint, failed_records bigint>>')) AS t(e)
WHERE event_type = 'flow_progress'
GROUP BY ALL ORDER BY day DESC, failed DESC;

Finding the dropped rows​

Dropped rows are not stored anywhere by the pipeline, but they are still in the staging table. Compare it with the cleaned table:

-- source rows that did not make it into rec_kna1_2021 (failed an expectation)
SELECT s.*
FROM workspace.mdm_staging.src_sap_kna1 s
LEFT ANTI JOIN workspace.mdm_staging.rec_kna1_2021 r ON r.KUNNR = s.KUNNR;

The dataset is named rec_<table name>_<UID number>; Configure Load shows it in its header (dataset kna1_2021), and the pipeline lists it.

Data quality after loading​

  • Survivorship eligibility — a property rule's Eligible if (e.g. value RLIKE '^[A-Z]{2,3}[0-9]+$') keeps malformed values out of golden records even when they are in the source. See Survivorship rules.
  • Typed golden properties — a surviving value that does not fit the golden property's PropertyType (a country that is not in the Countries enum) is not stored, and the golden record keeps its previous value; the Refresh Golden Records dry run lists it as a conflict. Loads do not check Enum values at all. See Values that do not resolve.
  • Matching evidence — every golden record stores its Match Score; low scores and near misses in the dry run point at records to check. See Matching.
  • Validation on save — the app parses every value by its PropertyType (numbers, dates, booleans, Enum and Link names) and checks Allowed Child Type and Max Child Depth. Invalid cells in the grid turn red and block Save. See Enum, Link and Array.

For dashboards and alerts on these checks, see Monitoring.