Rule-Driven Data Quality Framework for Customer Data

Challenge

A healthcare nutrition company manages its healthcare-professional customer data across four systems: a digital identity platform, a CRM, a marketing automation platform and an e-commerce shop. Each system holds its own version of the customer, so names, emails, addresses and consents can drift apart.

The existing data quality report had grown organically. Its rules were spread across report formulas and data transformations, and its scores were calculated once per refresh, so they did not respond to filters. Some records were filtered out before they were measured, which made the scores look better than they were. The report could also say that a percentage was low, but not which records were wrong or why.

Approach

We started by reverse-engineering the existing report: every source, transformation and measure was documented, and each issue in the logic was listed. That gave a clear baseline and a list of what had to change.

We then rebuilt data quality as a framework instead of a report:

• Business data entities. One SQL view per business entity, such as customers, accounts, addresses, email events and orders, combining the underlying source tables. Each view has a defined grain and business-friendly column names. Values are passed through untouched, including placeholders, gaps and duplicates, so the checks see the data as it really is. A dedicated cross-system entity links each customer's digital identity to their CRM record through a shared key, and compares names and postcodes after normalisation.
• A check catalogue. More than 120 rules, each a single SQL expression that returns true for a good record and false for a bad one. The rules cover five dimensions: completeness, validity, uniqueness, consistency and referential integrity. Every rule is tested automatically against good, bad and empty records before it is deployed. Each problem is reported once: a missing value fails completeness, not also format and uniqueness.
• A rule-driven engine. A stored procedure reads the catalogue, evaluates every rule on every row, and separates errors from warnings. It writes run statistics per dimension, field, country and customer segment. For each failing record it logs only the fields that matter for that rule, so the evidence is immediately readable.
• Reporting on top. A single statistics view feeds the report through DirectQuery, so the report shows the latest run without a separate refresh. No data is stored in Power BI: every query goes to the governed layer in Snowflake, so access rules and data stay in one place.
• Data retention. Old and unused run results and error details are purged on a regular schedule, so only the data the report needs is kept.