What data quality checks should run in an ETL pipeline?

The standard six: freshness (newest record is recent enough), volume (row counts within expected range), completeness (nulls in required columns), uniqueness (no duplicate keys), referential integrity (foreign keys resolve), and business rules (totals reconcile, values within valid ranges). Each one is expressible as a plain SQL query.

How do I automate data quality checks without a testing framework?

Write each check as a SQL query that returns failing rows (zero rows means the check passes), then schedule it. In SaturnSQL you save the check queries, schedule them hourly or daily, and append results to a Google Sheet that acts as the audit log. A sheet that stays empty is a pipeline that is healthy.

Should I use dbt tests or scheduled SQL checks?

If your team already runs dbt, use dbt tests as the first line: they live next to the models and run with every build. Scheduled SQL checks fit teams without a transformation layer, checks against production databases dbt does not touch, and business-rule checks owned by analysts rather than data engineers. Many teams run both.

ETL testing verifies that data survives the pipeline intact: everything extracted was loaded, transformations produced correct values, and nothing was duplicated or dropped. In practice it means comparing source and target row counts, spot-checking transformed values, and running the standard data quality checks on the target after each load.

Automated Data Quality Checks with Scheduled SQL

Q: What are data quality checks?

Data quality checks are automated tests that verify data is fresh, complete, and consistent: did last night's load arrive, are row counts in the expected range, are required fields populated, are keys unique, do totals reconcile. They catch broken pipelines before stakeholders find wrong numbers in a report.

Last updated July 23, 2026 · By the SaturnSQL team

Every broken data pipeline is discovered twice: once by the pipeline, silently, and once by a stakeholder, loudly, in a meeting. Data quality checks close that gap. They are small SQL queries that verify data is fresh, complete, and consistent, and the moment you put them on a schedule they become an early-warning system that costs almost nothing to run.

You do not need a testing framework to start. This guide gives you the six standard checks as copy-paste SQL, shows how to automate them with scheduled queries, and is honest about when to reach for dbt tests, Great Expectations, or Soda instead. If ETL terminology is new to you, start with our plain-language ETL explainer.

The pattern: checks that return failing rows

Write every check so that it returns rows only when something is wrong. A healthy pipeline produces empty results; anything else is a finding. This convention makes the checks trivially automatable: schedule the query, append results to a Google Sheet, and the sheet becomes your audit log. Empty sheet, healthy pipeline.

The six data quality checks every pipeline needs

1. Freshness — did the load actually run?

The most valuable check per line of SQL. If the newest record is older than the pipeline cadence allows, the load failed silently.

SELECT MAX(created_at) AS newest_row
FROM orders
HAVING MAX(created_at) < NOW() - INTERVAL '25 hours';

2. Volume — is the row count sane?

A daily load that usually brings 10,000 rows and suddenly brings 200 (or 2 million) is broken even if it "succeeded". Alert on the range, not the exact number.

SELECT COUNT(*) AS rows_loaded_today
FROM orders
WHERE created_at::date = CURRENT_DATE
HAVING COUNT(*) NOT BETWEEN 5000 AND 50000;

3. Completeness — are required fields populated?

Upstream schema changes love to turn a required field into a sea of NULLs without failing anything.

SELECT COUNT(*) AS missing_emails
FROM customers
WHERE email IS NULL
  AND created_at > NOW() - INTERVAL '1 day'
HAVING COUNT(*) > 0;

4. Uniqueness — any duplicate keys?

Retried loads and at-least-once delivery produce duplicates that quietly double your revenue numbers.

SELECT order_id, COUNT(*)
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

5. Referential integrity — do the joins still hold?

Orders pointing at customers that do not exist means a partial load or an out-of-order sync.

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

6. Business rules — do the numbers reconcile?

The check only a human who knows the business can write: refunds never exceed the original charge, line items sum to the invoice total, a discount is never negative.

SELECT invoice_id, total, SUM(line_amount) AS line_sum
FROM invoice_lines
GROUP BY invoice_id, total
HAVING SUM(line_amount) <> total;

The examples use Postgres syntax; the same checks translate directly to MySQL, SQL Server, and Redshift. Point them at whichever table your pipeline loads. If you connect with a read-only database user, the checks can never touch the data they are checking.

Automating the checks in SaturnSQL

1. Save each check as a named query

One query per check, named so the team understands the finding without opening the SQL: "DQ: orders freshness", "DQ: duplicate order ids". The shared library keeps the whole suite discoverable, so the checks survive the person who wrote them.

2. Schedule them at pipeline cadence

Run freshness and volume checks shortly after each expected load (daily loads get a daily 7am check). Uniqueness and reconciliation checks can run hourly on busy tables; they are cheap.

3. Append findings to a Google Sheet

Use append mode so every finding lands with its run date, building an audit log for free. The morning routine becomes: open one sheet, see zero new rows, move on. Sheets-native notification rules can email you when new rows appear.

When to use a dedicated data quality tool instead

Scheduled SQL checks are the right starting point, not the end state for every team. An honest map of the territory:

dbt tests — if you already run dbt, start here. Tests live next to the models and run with every build. Free in dbt Core. The gap: they only cover what dbt builds, on dbt's schedule.
Great Expectations / Soda — declarative check suites with profiling and anomaly detection. Worth the setup when the check count outgrows what a shared query library can organize. Both have open source cores.
Monte Carlo and other observability platforms — ML-driven anomaly detection across the whole warehouse, priced for data teams at enterprise scale. Overkill until broken data is a weekly incident category.
Scheduled SQL checks (SaturnSQL) — no framework, no YAML, no deployment. The right fit for teams without a transformation layer, for checks against production databases dbt never touches, and for business-rule checks that analysts own end to end.

Frequently asked questions

What are data quality checks?

Automated tests that verify data is fresh, complete, and consistent: the load arrived, row counts are in range, required fields are populated, keys are unique, and totals reconcile. Their job is to find broken pipelines before your stakeholders do.

Which checks should run in an ETL pipeline?

Freshness, volume, completeness, uniqueness, referential integrity, and business rules. The six above cover the failure modes behind almost every "the dashboard looks wrong" incident.

How do I automate checks without a testing framework?

Write each check to return failing rows, save it, schedule it, and append results to a Google Sheet as the audit log. That is the entire setup; there is nothing to deploy.

dbt tests or scheduled SQL checks?

Both, usually. dbt tests guard the models dbt builds. Scheduled SQL checks cover everything else: production databases, third-party loads, and the business-rule checks analysts want to own without touching the dbt repo.

What is ETL testing?

Verifying that data survives the pipeline intact: source and target row counts match, transformations produced correct values, nothing was dropped or duplicated. The six checks on this page are the recurring, automatable core of ETL testing.

Schedule your first data quality check today

Get Started View Pricing