ETL and master data: how to keep it continuously reliable

Even with validation in place, your master data may still be inaccurate. The pipeline is rarely the root cause.

An ETL process is the mechanism that collects, validates, and forwards data. Master data is the result: one validated set that feeds your other systems.

That set only becomes continuously reliable under two conditions. Validation has to report back to the source. And the cycle has to run often enough.

Below, we explain these conditions using data from our ETL project at AkzoNobel.

In this article

• What master data is and what an ETL process does with it
• Condition 1: validation that reports back to the source
• Condition 2: a cycle that runs often enough
• Audit and ETL: one provable source
• Frequently asked questions

What master data is and what an ETL process does with it

Master data is the shared core data that comes back everywhere in your organization: products, customers, locations, and suppliers. DAMA International treats master data management (MDM) in the DMBOK as a distinct knowledge area, aimed at establishing a single authoritative source for core data.

Extract-Transform-Load (ETL) works differently. The ETL process is the mechanism that produces master data. Extract collects the data from source systems. Transform cleans it up and harmonizes it. Load then forwards the data.

So, two things to remember. An ETL process collects data from source systems. Master data is the source of truth for the systems that work with it. The first is a process, the second a dataset.

Condition 1: validation that reports back to the source

Stricter validation alone does not improve source data. Unless errors in the source system are corrected, they will persist in each cycle.

An exception report addresses this issue. At AkzoNobel, we developed a rules engine to check each record for completeness and consistency. Records that fail validation are flagged in a report, which specifies the field and cause, enabling the data owner to correct the error at its source. The rules engine validates, while the exception report communicates anomalies to the data owner.

In QA, we know this as the 1-10-100 rule, formulated in 1992 by Labovitz and Chang: preventing an error costs 1, correcting it costs 10 times more, and fixing it once the error reaches the end user costs 100 times more. The ratio is a rule of thumb, not a hard measurement.

Want to see how ETL works at AkzoNobel? Read the case study.

Condition 2: a cycle that runs often enough

Source systems refresh at their own pace. Product systems, color systems, and SAP: each on its own schedule, and much of it entered by hand. Gartner calls inconsistency between sources the hardest data quality problem and estimates the average annual cost of poor data quality at $ 12.9 million. That figure comes from a survey of 154 reference customers who were already buying data quality software, so read it as an order of magnitude.

At AkzoNobel, we work with more than 200 million color and product combinations. The old systems took about 24 hours to collect that information. The full cycle we built runs in 2 hours.

A cycle that runs 12 times faster is impressive, but it does not guarantee fully accurate source data. Without a feedback loop, errors are simply processed more quickly. Frequency and accuracy remain distinct requirements.

Want to see how ETL works at AkzoNobel? Read the case study.

Audit and ETL: one provable source

Validated master data is only worth something once your consumers actually use it. At AkzoNobel, the dataset is distributed to webshops, dashboards, and external parties through documented APIs, ensuring all parties access the same source. API integrations make master data available to receiving systems.

The audit trail is the second part. Every change is traceable: origin, timestamp, and reason for rejection. An audit trail records the origin and changes of every record. Article 5 of the GDPR requires personal data to be accurate and kept up to date where necessary, and paragraph 2 places the accountability with the controller. Being able to prove it is a legal requirement for AkzoNobel, not an extra.

Frequently asked questions

Is an ETL process the same as master data management?

No. An ETL process is the mechanism that collects, validates, and forwards data. Master data management is the discipline that decides which data is authoritative and who owns it.

Why does our master data stay wrong despite validation?

Most likely because validation blocks bad records without reporting where the error came from. The source remains unchanged, so the error reappears on every run.

How often should an ETL cycle run?

As often as your consumers need current data. We run event-driven where we can and batch-based where we have to. At AkzoNobel, the full cycle went from about 24 hours to 2 hours.

In short

Continuously reliable master data takes two things at once: validation that reports back to the source, and a cycle that runs often enough. One without the other gives you either outdated but accurate data or current but inaccurate data.

Using ETL to grow your organization?

Schedule your free technical intake

Sascha Mendel

Geschreven door Sascha Mendel, software engineer bij this.nl. Sascha kun je bijna niet blijer maken met datastromen, hoe complexer hoe beter. Zijn kennis van masterdata, PHP en Golang zet hij onder andere in om grote bedrijven zoals AkzoNobel en Royal FrieslandCampina te helpen met ETL-processen en andere complexe backendvraagstukken.