Data Quality Checks as a Fixed Part of the Data Pipeline

1. Why data quality must be a continuous process

A one time data quality audit only delivers a snapshot. The very next import, the next schema change, or a faulty piece of application logic can introduce new quality problems that the last audit no longer captures. Systems relying on occasional manual checks usually discover such problems in practice only once a business team reports an obviously wrong report, which is often weeks after the actual root cause.

Automated, continuously running checks push that discovery point as close as possible to the actual root cause, ideally before faulty data even reaches a production system. That not only reduces the damage, it also significantly cuts the effort of the subsequent investigation, because the timing and business context of the cause is still directly traceable.

2. Categories of SQL based data quality checks

In practice, most relevant checks fall into a handful of categories: null ratios in columns that should always be filled, value range checks for numeric and date fields, referential consistency between dependent tables, uniqueness where duplicates are not allowed by business logic, and freshness, whether a table has received new data at all within the expected time window.

Every one of these categories can be expressed in plain SQL, without additional specialized tools, which makes getting started considerably easier. The decisive difference from a one off ad hoc query is that each of these checks is formulated as a standalone, repeatedly runnable query with a clearly defined expected result that can be evaluated automatically.

3. Practical example: null ratio as a threshold check

A null ratio check computes the percentage of empty values in a column that is effectively a required field and compares that percentage against a defined threshold. Such a check is considerably more robust than a plain existence check, because it also catches gradual degradation, for example when a new integration slowly starts leaving a required field empty more often, without any single failure standing out immediately.

The threshold itself should be justified on business grounds rather than chosen arbitrarily: a historically stable null ratio below one percent justifies a considerably stricter threshold than a field that has always been incompletely populated by occasional import sources.

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_rows,
    ROUND(
        100.0 * SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS null_percentage
FROM customers
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day';

4. Value range checks for numeric and date fields

Value range checks cover cases where a value is technically valid but obviously wrong from a business standpoint, for example a negative order amount, a discount above one hundred percent, or a birth date in the future. Such errors typically arise from faulty application logic, unvalidated external input, or incorrect sign handling during a currency conversion.

The advantage of a database side check over pure application validation lies in the fact that it applies regardless of which path the data ultimately took into the table, whether through the actual application, a direct import, or a manual correction by an administrator, all paths that would otherwise easily bypass application level validation.

5. Threshold based alerting instead of binary pass/fail

A binary pass/fail check that fires a critical alert on every single faulty record quickly leads to alert fatigue in practice, because individual outliers in large data sets are nearly unavoidable. A multi level threshold model is more sensible, with a warning level that flags gradual degradation and a critical level that actually intervenes.

Such a check becomes even more meaningful when it takes into account not just the current value but the trend across several past runs: a single outlier of two percentage points is less concerning than a value that has moved continuously in the same direction over the last ten runs, even if the absolute value still sits within the threshold.

6. Integration into CI/CD and ETL pipelines

The concrete technical integration depends on the tool in use but follows the same basic pattern everywhere: after every relevant pipeline step, such as an ETL load run or a schema migration, a set of defined SQL checks runs, whose result controls the further course of the pipeline. Transformation pipeline tools often offer their own testing layer for this, while the same logic works just as well in classic CI/CD systems with plain SQL scripts and a simple exit code.

What matters is that a failed check does not merely generate a notification but is actually able to stop the further pipeline run once the critical threshold has been exceeded. Without that blocking effect, a quality check remains a purely informational offering instead of a genuine protection mechanism for downstream systems.

7. Practical example: a check that warns before faulty deployments

A concrete example is a check that runs before every deployment, verifying whether referential consistency between two central tables sits within the expected bounds. If the check returns a value above the critical threshold, the deployment script is configured to abort with a corresponding error code, instead of promoting a potentially already faulty data set into production.

This pattern extends to any number of checks, with each individual check embedded as its own query with a clearly defined threshold in a shared verification script that returns an overall status at the end of all individual checks. That keeps each check individually traceable and maintainable, while the pipeline overall only needs to evaluate a single, clear success or failure status.

-- Pipeline gate: check referential consistency before deployment
SELECT COUNT(*) AS broken_references
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.id
WHERE p.id IS NULL;
-- Deployment script aborts when broken_references > 0

8. Historizing check results for trend analysis

A single check run without historical context only answers whether the current state sits within the bounds, not whether data quality is improving or degrading overall. A dedicated table that stores every check run with a timestamp, the checked metric, and the result makes that development over time visible and provides the foundation for the trend based alerting described earlier.

This history also serves excellently as the foundation for a dedicated data quality dashboard that business teams and technical teams can share, to watch the development of central quality metrics over weeks and months instead of relying exclusively on point in time alerts.

9. Organizational ownership: data contracts and owners

Technical checks alone do not solve the problem if nobody is responsible for the result. Every table with defined quality checks should therefore have a clearly named business owner who gets informed of degradations and has the authority to decide on next steps, instead of an alert getting lost in general notification noise.

A formalized data contract that explicitly defines, for every central table, which quality requirements apply, who gets informed on a violation, and which escalation levels kick in, makes that responsibility not only traceable but auditable. Without such a contract, data quality remains organizationally unanchored despite technically perfect checks.

10. Summary

11. FAQ: Data Quality Checks: Key Takeaways