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.
| Where | Check | What happens to bad data |
|---|---|---|
| Load (pipeline) | Expectations of the Table node | The row is dropped and counted |
| Load (merge) | Column mapping and transforms | Values are trimmed / cased consistently |
| Golden records | Eligible if on property rules, PropertyType of the golden property | The value does not compete / is reported as a conflict |
| Every edit | PropertyTypes, Allowed Child Type, Max Child Depth | The 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:
| Rule | Expectation |
|---|---|
| Key present | KUNNR IS NOT NULL |
| Country code | LAND1 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-mail | Email__c IS NULL OR Email__c LIKE '%@%.%' |
| Value from a list | status IN ('active', 'blocked') |
| Not a test record | NOT 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.
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.