Data Quality Checks Every Beginner Team Should Add — A Practical Guide

In this article, let us go through the data quality checks that every beginner data team should add to their pipelines. These are not fancy checks that need a dedicated data observability platform. These are the ones you can set up with SQL, a bit of Python, or even the built-in features of your orchestration tool.

When I started building data pipelines, I used to think that if the pipeline ran without errors, the data must be correct. It took a few angry Slack messages from downstream users to realize that a pipeline can succeed and still produce garbage. A JOIN that silently drops rows, a source table that did not refresh, a CSV with an extra column that shifts everything to the right — the pipeline will happily run through all of that.

The checks below are what I now add to every pipeline before calling it done. They will not catch everything, but they will catch the problems that happen most often. And you can set them up in an afternoon.

1. Row Count Validation

This is the simplest check and the one I reach for first. After loading data from source to destination, compare the row counts.

-- After loading a batch, compare source and destination counts
SELECT 
    (SELECT COUNT(*) FROM source_sales WHERE batch_date = CURRENT_DATE()) AS source_count,
    (SELECT COUNT(*) FROM staging.sales WHERE batch_date = CURRENT_DATE()) AS dest_count;

If the counts do not match, something went wrong during the load. Maybe the extraction timed out halfway, maybe a file was partially ingested, or maybe a filter you added in the pipeline is more aggressive than you thought.

What to watch out for: Row count alone is not enough. I have seen pipelines where the counts matched perfectly but every row had the wrong values because a JOIN condition was off. Count checks tell you that you got the right number of rows, not the right rows. Think of it as the first line of defense, not the only one.

In practice: I usually set a tolerance rather than an exact match. If the source is an OLTP database that is still accepting writes while you extract, a small difference might be expected. A 0.1% drift is usually fine. A 20% drift is not. For batch loads from static files or snapshots, though, I expect an exact match.

If you are using dbt, you can wrap this into a simple test. But honestly, a plain SQL query in your orchestrator’s error hook works just as well when you are getting started. Do not overthink the tooling.

2. NULL Checks on Critical Columns

Some columns should never be NULL. Primary keys, foreign keys used in JOINs, timestamps that power downstream aggregations — if these are NULL, things will break, often silently.

-- Check for NULLs in columns that must be populated
SELECT
    SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS null_order_ids,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_ids,
    SUM(CASE WHEN created_at IS NULL THEN 1 ELSE 0 END) AS null_timestamps
FROM staging.orders
WHERE batch_date = CURRENT_DATE();

I run this check after every load. If any of these counts come back greater than zero, the pipeline should halt or at least raise an alert. No exceptions for critical columns.

A trap I fell into: I once checked for NULLs on a column that allowed NULLs about 5% of the time in production. I set the threshold to 10% and called it a day. A month later, the source system changed and the NULL rate went to 30%. By the time anyone noticed, downstream reports had been undercounting revenue for two weeks. If a column is important enough to check, check it properly — do not hide behind loose thresholds. If you truly need a threshold, store the historical NULL rate and alert on deviations from the norm, not on an arbitrary number you picked once.

3. Duplicate Detection

Duplicates are sneaky. They do not break your pipeline. They do not make your numbers look obviously wrong. They just quietly inflate everything. A weekly revenue report that is 8% higher than expected might not even raise eyebrows until someone reconciles it against the source system.

A deduplication check depends on what makes a row unique in your model:

-- Check for duplicate primary keys
SELECT order_id, COUNT(*) AS cnt
FROM staging.orders
WHERE batch_date = CURRENT_DATE()
GROUP BY order_id
HAVING COUNT(*) > 1;

If this returns any rows, you have a problem. Maybe the extraction ran twice. Maybe the source had an upsert that your pipeline treated as an insert. Maybe someone manually re-triggered a backfill without clearing the previous run.

For event data where there is no clean primary key, I check for exact duplicate rows across a sensible subset of columns. It is a blunt check but catches the most common issue — the same file being ingested twice.

-- Check for exact duplicate rows on key event columns
SELECT event_type, event_timestamp, user_id, COUNT(*) AS cnt
FROM staging.events
WHERE event_date = CURRENT_DATE()
GROUP BY event_type, event_timestamp, user_id
HAVING COUNT(*) > 1;

One thing I have learned: if you are dealing with a streaming source or at-least-once delivery semantics, some level of duplication might be expected. In that case, the check is not “are there duplicates” but “are there more duplicates than usual.” Again, trending over time matters more than a single run.

4. Freshness Checks

A pipeline can run successfully on an empty source. I learned this the hard way when a daily export job on the source side failed silently for three days. Our pipeline kept running, loading zero rows each time, and our dashboards slowly trended toward zero. The pipeline status was green the entire time.

A freshness check answers one question: “Does the data in the destination reflect the latest available data from the source?”

-- Check when data was last updated in the destination
SELECT MAX(updated_at) AS latest_record, 
       MAX(ingested_at) AS latest_ingestion
FROM staging.sales;

Compare MAX(updated_at) against the expected freshness window. If your source updates every hour and the latest record is from six hours ago, something is wrong.

A simpler approach that works surprisingly well: just check that the row count for the current partition is greater than zero (or greater than some reasonable minimum).

SELECT COUNT(*) FROM staging.sales WHERE batch_date = CURRENT_DATE();

If this returns 0 on a business day, raise an alert. It is not sophisticated, but it catches the most common failure mode — the source did not produce data and nobody noticed.

In a production setup, I also check that the latest timestamp is within a reasonable range. If your data typically arrives with a 15-minute lag and suddenly the freshest record is 4 hours old, something is almost certainly wrong. You can store the expected lag as a config value per pipeline and compare at runtime.

5. Value Range and Domain Checks

This is where you encode your business knowledge into checks. These are not generic — they are specific to your data and your domain. Examples:

• An order amount should never be negative
• A discount percentage should be between 0 and 100
• A country code should be exactly two uppercase letters
• An email field should at least contain an “@” sign
• A delivery date should not be earlier than the order date

-- Range and domain checks on a recent batch
SELECT COUNT(*) AS invalid_rows
FROM staging.orders
WHERE batch_date = CURRENT_DATE()
  AND (order_amount < 0 
       OR discount_pct < 0 
       OR discount_pct > 100
       OR LENGTH(country_code) != 2
       OR email NOT LIKE '%@%'
       OR delivery_date < order_date);

These checks are specific to your domain, so you will build them up over time. Start with the obvious ones — negative amounts, impossible dates — and add more as you discover what actually breaks in practice. Every time a downstream user reports bad data, ask yourself: could a simple check have caught this? If yes, add it.

Quick Comparison

Here is a summary of the five check types and what they actually buy you:

Check Type	Effort to Set Up	What It Catches	What It Misses
Row Count	Low	Incomplete loads, extraction failures, filter bugs	Silent data corruption, wrong values in correct row counts
NULL Checks	Low	Missing required fields, source schema changes	Fields with wrong-but-not-NULL values
Duplicate Detection	Low to Medium	Re-ingested data, upsert bugs, backfill mistakes	Near-duplicates, slowly changing dimension edge cases
Freshness	Low	Stale sources, stuck upstream jobs, silent source failures	Fresh but incorrect data
Value Range	Medium	Business rule violations, source application bugs	New edge cases you have not defined yet

Things to Keep in Mind

Do not build a framework before you need it. When I first got serious about data quality, I spent two weeks building a fancy framework with YAML configs, a metadata database, and a dashboard. It was a waste of time at that stage. A few SQL queries wired into your orchestration DAG will get you 80% of the value. Add the framework later when you actually know what you need and have enough pipelines to justify the overhead.

Decide what happens when a check fails. Failing silently — just logging a warning somewhere nobody looks — is worse than not having the check at all. It creates a false sense of security. For critical pipelines, a failed quality check should stop downstream jobs from running. For less critical ones, at least send a notification to a Slack channel that people actually monitor. A log line in a forgotten table does not count.

Alert fatigue is real. If you set up twenty checks that fire off alerts every day for things that are “mostly fine,” people will start ignoring them. Start with five or six checks that really matter and tune them until they fire only when there is a genuine problem. A noisy alert is an ignored alert.

Track results over time. In a production environment, you will want to store check results in a table and look at them periodically. A one-off NULL spike might be a source glitch. NULLs creeping up over three weeks probably mean the source team changed something and did not tell you. Trends tell you more than point-in-time results.

Wrapping Up

The checks above are nothing groundbreaking. But if you add them to every pipeline you build, you will catch 80 to 90 percent of the data quality issues that beginner teams run into. The goal is not to build a perfect system on day one. It is to catch the embarrassing problems before someone else does — and before they make it into a report that someone important is looking at.