Simple rules that prevent costly disasters
A logistics mid-market company discovers it has double-billed 120,000 euros over one quarter. The cause: duplicates in the ERP's orders table, created by a misconfigured import 4 months earlier. A simple uniqueness rule on the order number — checked automatically at each import — would have detected the problem on the very first day. A quality control plan is a set of automated rules that monitor your data around the clock: completeness, uniqueness, format, freshness and consistency. Setup cost: 2 to 4 weeks. Cost of an avoided incident: 10,000 to 500,000 euros.
The problem
In a typical SMB, data quality is managed "by feel". Someone notices an anomaly in a report, launches an investigation, fixes it manually and moves on. No formal rules, no automated control, no tracking over time. The same problems come back month after month.
The most costly types of errors:
- Missing completeness — 15% of the customer records in the CRM have no email address. The marketing department sends a campaign to the whole base and gets a disastrous 72% deliverability rate. The 3,500 customers without an email never receive the communications — and no one knows. The result: 45,000 euros of revenue lost on "forgotten" customers.
- Undetected duplicates — The same supplier is created 3 times in the ERP with slightly different spellings ("Dupont SAS", "DUPONT S.A.S.", "dupont sas"). Orders are spread across 3 accounts. The result: volume discount terms don't apply, and the company pays 12% more than negotiated. Estimated annual loss: 35,000 euros.
- Outdated data — The product price table hasn't been updated for 6 months for 200 references. The e-commerce site shows the old prices. Customers order at outdated prices. The company has to honor the orders or risk a dispute. Loss: 8,000 euros over a quarter.
- Cross-system inconsistencies — A product is marked "in stock" in the ERP but "out of stock" on the website. Customers order an unavailable product. Customer service handles 150 complaints per month related to this problem. Cost in processing time and brand image: incalculable.
All these incidents would have been detected by basic quality rules — but no one had defined or automated them. See our observability and traceability offer to take action.
The AI solution
A quality control plan is organized around 5 dimensions. AI reinforces each dimension by detecting anomalies that static rules don't cover.
Completeness and uniqueness rules
Automatic verification that mandatory fields are filled (customer email, order number, product code) and that primary keys are unique. AI goes further: it detects "near-duplicates" by fuzzy matching ("Dupont SAS" vs "DUPONT S.A.S.") and suspicious default values (e.g. "[email protected]" in 200 customer records). Examples: email NOT NULL, UNIQUE(order_id), fuzzy_match(supplier_name) > 0.85.
Format and consistency rules
Verification that the data respects the expected formats: valid email (regex), 5-digit postal code, 14-digit SIRET, positive amounts, future dates for deliveries. AI adds cross-consistency rules: "if the country is France, the postal code must start with 0-9 and have 5 digits", "the gross amount = net amount × (1 + VAT rate) ± 0.01 euro".
Freshness and volume rules
Verification that the data is updated within the expected timeframes: the orders table must receive new rows every business day, product prices must be updated at least once a month, HR data must be synchronized within 24h of a personnel change. AI learns the historical volume patterns and alerts if the day's volume deviates by more than 2 standard deviations.
Implementation
The quality control plan is rolled out in three phases over 2 to 4 weeks.
Identifying critical tables and rules (week 1)
Select your 5 to 10 most critical tables (customers, orders, products, invoices, suppliers). For each, define 3 to 5 rules covering the 5 dimensions: completeness (NOT NULL fields), uniqueness (primary keys), format (regex), freshness (timestamp) and consistency (foreign keys, business rules). Document each rule with its acceptability threshold and its criticality (critical/major/minor).
Implementation and testing (weeks 2-3)
Code the rules in the chosen tool (dbt tests, Great Expectations or Soda). Run them a first time to measure the initial state of quality. Don't be surprised: the first run often reveals 5 to 15% of anomalies on the critical tables. Fix the most serious problems. Adjust the thresholds to avoid false positives (e.g. going from 100% completeness to 98% if some null values are legitimate).
Automation and tracking ritual (week 4)
Schedule the automatic execution of the rules (daily or at each data load). Configure Slack/email alerts by criticality level. Create a quality dashboard with the key metrics: compliance rate by table, 30-day trend, number of open incidents. Establish a 15-minute weekly ritual with the Data Steward to review the alerts and prioritize the fixes.
Results
Here are the results observed at our clients after 3 months of operational quality control plan.
Frequently asked questions
What is a data quality control plan?
It's a document (or a set of automated tests) that defines for each table or dataset the quality rules to respect: completeness (no null values on mandatory fields), uniqueness (no duplicates on primary keys), format compliance (valid emails, 5-digit postal codes), freshness (data updated within 24h) and cross-table consistency (each order has an existing customer). Each rule has an acceptability threshold and an action in case of breach.
How many quality rules should you define per table?
Start with 3 to 5 rules per critical table, covering the essential dimensions: one completeness rule, one uniqueness, one format and one freshness. Add specific business rules if necessary (e.g. "an order amount must be positive"). In total, aim for 30 to 50 rules to cover your 10 most important tables. Don't exceed 100 rules at the start — the risk is generating too many alerts and losing the ability to react.
How do you prioritize quality rules?
Prioritize by business impact. Wrong data that affects billing (direct financial impact) is more critical than missing data in a comment field. Classify your rules into 3 levels: critical (block the pipeline on violation), major (immediate alert) and minor (weekly report). Start with the critical rules and add the others gradually.
Which tools should you use to automate quality controls?
The three most used tools: Great Expectations (Python, open source, very flexible), dbt tests (integrated into the SQL pipeline, ideal if you already use dbt) and Soda (SodaCL declarative language, no-code interface available). For non-technical teams, Soda Cloud or Atlan offer a graphical interface to define and monitor the rules without coding. All integrate with Slack, Teams and PagerDuty for alerts.
For tech profiles
Example rules by tool
Rules integrated into the SQL pipeline
Native tests: unique, not_null, accepted_values, relationships (foreign key). Custom tests in SQL: SELECT COUNT(*) FROM orders WHERE amount < 0 must return 0. dbt-expectations package for advanced rules: expect_column_values_to_match_regex, expect_table_row_count_to_be_between. Execution at each dbt build, results in the terminal and in Elementary.
Python validation framework
Library of 300+ predefined expectations: expect_column_values_to_not_be_null, expect_column_values_to_be_unique, expect_column_values_to_match_regex. Automatic generation of HTML reports (Data Docs). Automatic profiling that suggests expectations based on your data. Integrates with Airflow, Prefect and Dagster.
20 essential rules
Comparison of quality control tools
| Criterion | dbt tests + Elementary | Great Expectations | Soda Cloud |
|---|---|---|---|
| Cost | Free (open source) | Free (open source) | 500-2,000 €/month |
| Language | SQL + YAML | Python | SodaCL (declarative) |
| No-code interface | Via Elementary Cloud | No | Yes (native) |
| Pipeline integration | Native (dbt build) | Airflow, Prefect, Dagster | CI/CD, Airflow, dbt |