Stopping bad data inside Airflow data quality checks
Pipelines that load data downstream teams trust need a way to stop a run before bad rows reach dashboards, models or regulatory reports. The practical answer for many Airflow estates is to put SQL checks in the DAG itself, so that a failed check fails the task, blocks the tasks that depend on it and leaves the bad data in staging rather than in the consumer tables. The common SQL provider offers four operators for this: SQLColumnCheckOperator for per-column null, distinct, unique, minimum and maximum checks, SQLTableCheckOperator for boolean statements about a whole table, SQLValueCheckOperator for comparing a single result to an expected pass_value, and SQLThresholdCheckOperator for checking a result against minimum and maximum bounds that can themselves be queries. Because they run as tasks, they use the same connections, scheduling, retries and logs as the rest of the pipeline, which is the main reason teams keep checks here instead of in a separate tool.
Teams usually need these operators when a pipeline has a small number of well-understood invariants: row counts within an expected range, keys that must be unique, amounts that must not be negative, or a daily load that must cover a partition. Tolerances and partition clauses matter here. A value check with a tolerance percentage lets a daily count drift without failing on normal variation, and a partition clause keeps the check focused on the slice just loaded instead of the full history. Confirm the exact parameter names, tolerance semantics and supported database backends against the common SQL provider operator reference and your provider version before you design thresholds. If your warehouse is BigQuery, the Google provider also documents BigQuery-specific check operators.
A dedicated data quality framework may fit better when you need a large catalog of rules across many datasets, profiling, rule libraries shared by non-engineers, or a quality dashboard with history and ownership. In that case, Airflow can still trigger the framework and wait for its result, so the DAG gates downstream tasks without becoming the only place rules live. Decide early which checks must block a run and which should only raise an alert, because that choice drives where each check sits in the DAG and who gets paged. A partner can help map those decisions to your pipelines, but the thresholds and ownership must come from your data owners.
Which SQL check operator fits which check
Use SQLColumnCheckOperator when the question is about the shape of individual columns in a table, such as whether a key is populated, unique or within an expected range. You give it a table and a mapping of column names to the checks you want, and the operator generates and runs one aggregate query for the columns you name. The checks it supports are null checks, distinct checks, unique checks, and minimum and maximum checks, each compared with a condition such as equal_to or greater_than. Optional tolerance percentages and partition clauses let you scope the check to a load date or allow a small known deviation.
- Null checks: Count nulls in a column and compare the count against a condition, which suits required identifiers and timestamps.
- Unique checks: Confirm that a key has no duplicates after a join or merge step.
- Min and max checks: Catch values outside a plausible range, such as negative amounts or dates in the future.
- Partition clauses: Limit the check to the partition the run just loaded so that old data does not keep failing new runs.
- Failure output: The task fails and the log lists which column check did not meet its condition, so the failing column is visible without opening the warehouse.
Before relying on a column check, confirm the exact check names and condition keywords for your provider version in the operator documentation.
Use SQLTableCheckOperator when the rule spans rows or columns together and does not fit a single column check. You write boolean SQL statements for the table, and each one must evaluate to true for the check to pass. This is the natural place for relationships between columns, such as a settled amount never being greater than the authorized amount, or for row count rules that combine several conditions. Because you write the statement yourself, the operator is as expressive as your SQL, which also makes the rule easier to review with a data owner.
- Boolean statements: Each named check is a condition that must hold for the whole table, for example that a status column only contains allowed values.
- Cross-column rules: Compare columns within the same row, which column checks cannot express.
- Named checks: Give each statement a clear name so the failure message tells a reviewer which business rule broke.
- Partition scope: Apply the same partition filter to the statements as you would to column checks, so one bad historical load does not block every run.
- Failure output: The task fails and the log reports which named statement evaluated to false.
Review each statement with the data owner before the first production run, because a rule written against the wrong grain will fail for reasons the pipeline team cannot resolve alone.
Use SQLValueCheckOperator when one query returns a single value and you want it to match an expected value. The operator runs the query, takes the result, and compares it with pass_value. You can allow an optional tolerance, which is useful when a count or sum can legitimately drift by a small amount between source and target. The check is most natural for reconciliation, such as comparing a row count in the staging table with the count the source system reported, or confirming that a control total matches the previous day's figure within an agreed margin.
- Single-value query: The query must return one value, so aggregate it in SQL before the check runs.
- Pass value: The expected result, which can be a fixed number or a value computed by a prior task passed in through the DAG.
- Tolerance: An optional allowance for expected drift. Agree the tolerance with the business owner and record it in code review.
- Reconciliation use: Strong fit for comparing totals between two systems or two runs.
- Failure output: The task fails and the log shows the returned value next to the expected value, which makes the gap easy to read.
Confirm how the operator handles a query that returns no rows or more than one row in your provider version, since that case should fail loudly rather than pass silently.
Use SQLThresholdCheckOperator when a result must fall between a minimum and a maximum, and those bounds may change over time. The operator runs a query for the metric and compares it with a lower and an upper threshold. Either threshold can itself be a query, so the bound can be derived from history, such as a rolling average, instead of a number that someone must update by hand. This suits volume and freshness checks where a fixed number would go stale as the business grows.
- Metric query: Returns the value under test, such as the number of rows loaded for the day.
- Minimum and maximum: Each bound can be a fixed value or a query, so the allowed band can follow recent history.
- Freshness and volume use: Strong fit for detecting an empty load or an unexpected spike.
- Maintenance: Query-based bounds need a review of their lookback window, because a bad week of data can widen the band and hide a real problem.
- Failure output: The task fails and the log reports the measured value and the bounds it violated.
Ask the team who owns each bound to confirm the lookback logic and the behavior when history is short, such as after a new source starts.
Configuration options and when each fits
This table compares the main options on the SQL check operators, what each one checks, when it fits and the trade-off to weigh before you set it. Read each row as a decision: the option is useful when the business rule matches it, and it becomes a blind spot when the tolerance, partition or condition is looser than the data really allows.
| Option | What it controls | When it fits | Trade-off to weigh |
|---|---|---|---|
| conn_id | The Airflow connection the check runs against | Keeping checks on the same warehouse as the load, with credentials managed in Airflow | Checks inherit that connection's permissions, so a read-only role must still see the tables under test |
| sql (check statements) | The SQL that returns the value or boolean being checked | Business rules you can express as a single query, such as row counts or reconciliation totals | Long or costly queries run on every DAG run, so keep statements narrow and indexed where possible |
| equal_to, greater_than, less_than | The comparison a column or value check applies to its result | Exact expectations, such as a fixed reference count, or a lower bound such as a minimum daily volume | Bounds that are set too loosely pass bad loads silently, and bounds set too tightly fail on normal seasonal change |
| tolerance | The allowed percentage difference from the expected value | Metrics that drift naturally, such as sums that move with exchange rates or daily volumes | A generous tolerance hides real drift, so document the reason for the chosen percentage and review it after incidents |
| partition_clause | A filter that limits the check to one slice of the table, such as the run date | Large tables where scanning all history on every run is too slow or too costly | Scoping to one partition limits coverage, so late-arriving or back-filled rows in other partitions go unchecked |
| Column checks (null, distinct, unique, min, max) | Per-column rules applied through SQLColumnCheckOperator | Keys, mandatory fields and value ranges that should hold on every load | Each added column adds query work, so prioritize keys and fields that downstream reports depend on |
| Table checks (boolean statements) | Row-level or cross-column rules applied through SQLTableCheckOperator | Rules that span columns, such as an end date never preceding a start date | Every statement must return a true or false result, so complex logic is harder to read and debug |
| BigQuery column and table check operators | BigQuery-specific versions of the column and table checks in the Google provider | Pipelines that already run on BigQuery and need the same checks in that dialect | Option names and behavior differ by provider version, so confirm each option in the Google provider documentation before you rely on it |
Where checks sit in the DAG and why placement matters
Where a check sits decides what it protects. A check placed right after a load stops bad rows from reaching consumers, while a check on a staging table stops them from ever reaching production. The cards below cover the placement decisions that matter most and the effect a failing check has on the tasks around it.
Checks after each load
Place a column or table check directly after the task that writes each table, before any task that reads it. This keeps the failure close to its cause, so the on-call engineer sees which load produced the bad rows. Keep the check in the same DAG run as the load so its result is tied to the partition that was just written. Confirm that the upstream task's success state is what triggers the check in your environment.
Checks on staging tables before promotion
When loads write to a staging table and a later task swaps or merges it into the production table, run the checks against staging. A failure then blocks promotion, and production keeps the last good data. This pattern works well for regulated pipelines where a bad publish is hard to reverse. Confirm how your promotion step handles a skipped or failed upstream check.
Checks gating publish steps
Make the publish task depend on every check that protects the data it publishes. Downstream consumers such as dashboards, exports and reverse ETL jobs then wait for the gate to pass. Be deliberate about trigger rules: the default rule skips a task when an upstream task fails, which usually keeps the publish from running, but any task set to run regardless of upstream state will publish anyway. Verify the trigger rule on every publish task.
Checks scoped to partitions
Run checks against the partition the run just loaded, using a partition clause, rather than the whole table. Table-wide checks grow slower as history accumulates and can fail on old data that a current run did not touch. Partition-scoped checks also make a retry or backfill for one date independent of the others. Confirm that the partition filter is parameterized with the logical date for each run.
Failures and retry behavior
A failing check raises an error, so the task is marked failed and retries follow the task's retry settings. A retry reruns the check against the same data, so it will fail again unless the upstream data changed or the failure was transient, such as a dropped connection. Set retries on the check tasks with that in mind and avoid retrying a deterministic data failure many times. Confirm the retry count and delay in your Airflow 3.3.2 environment.
How to set up SQL checks in an Airflow 3 pipeline
This sequence takes a single table's load task from no checks to a tested, alerting data quality gate in staging. Each phase ends with a check you can run, so the team knows the checks fail when they should before they reach production.
Confirm the provider version against Airflow constraints
The platform engineer installs the Common SQL provider package into the same environment the scheduler, DAG processor and workers use, pinned to a version that resolves against the Airflow constraints file for your Airflow 3 release. Do not install it into a separate image or virtualenv that the scheduler cannot see. Verify by running a DAG-parse check in the scheduler environment and confirming that the SQL check operators import with no errors and that no dependency conflicts appear in the install log.
Define the database connection for checks
The data engineer creates an Airflow connection to the warehouse or database that holds the target table, using a service account that has read-only access to the tables being checked. Store the password in your configured secrets backend, not in the DAG file. Verify by running a trivial query such as a row count through the connection from a test task, and confirm that the query returns the expected result and that the account cannot write to the table.
Write check definitions for one table
The data engineer writes the expected conditions for one target table before wiring any operator: which columns must not be null, which must be unique, the minimum and maximum allowed values, the expected row count range, and any partition clause that limits the check to the load window. Record the tolerance for each check and who approved it. Verify by reviewing the definitions with the data owner and confirming each one maps to a stated business rule rather than a guess about current data.
Place check operators after the load task
The data engineer adds the column, table, value or threshold check operators as downstream tasks of the load task, so they run only after data lands and before any task that publishes or consumes it. Make the publish task depend on every check, so a failed check stops promotion. Verify by viewing the DAG graph in the Airflow UI and confirming that the checks sit between the load and publish tasks, and that no downstream task can run while a check is still pending.
Run the DAG in staging with known-good data
The data engineer triggers the DAG in the staging environment against a dataset whose contents are known to be correct, such as a snapshot of a previous clean load. Each check task should finish in the success state. Verify by reading the task logs and confirming that each check reports the expected measured value, so the thresholds and tolerances are being applied as written.
Run the DAG in staging with known-bad data
The data engineer loads a deliberately faulty dataset into staging, such as one with nulls in a required column, duplicate keys, or a row count outside the agreed range. The affected check should fail, the publish task should not run, and the failure should appear in the task logs with the measured value. Verify by confirming that each planted defect fails the intended check and that no downstream publish task executes. If a defect passes, adjust the check definition before going further.
Confirm failure behavior and alert routing
The platform engineer confirms that a failed check sends a notification to the owning team through the alerting channel your organization uses, such as email, chat or an incident tool, configured through Airflow callbacks or notifications on the failed task. Check that the alert names the DAG, task and table, and that it reaches a person who can act on it. Verify by triggering a known-bad run and confirming the alert arrives, then recording the result as the test evidence for the change.
How tolerances and partitions change check results
A tolerance or partition clause can turn a failing check into a passing one, so both settings need to be tested before the check guards a production load. The SQL check operators accept an optional tolerance on value and column checks, so a result that differs from the expected value by a small percentage still passes. Whether that is acceptable depends on what the metric means to the business. A row count that should match a source extract exactly rarely deserves tolerance, while a daily count of events that naturally fluctuates may need one. Record the reason for each tolerance in the DAG code or in a review note, and ask the data owner to confirm it. A tolerance chosen to stop a noisy alert usually hides a real upstream problem.
Partition clauses limit a check to a slice of the table, most often the newest load, so the check does not rescan years of history on every run. That is useful for cost and runtime, but the clause is written as SQL, and it must match how the pipeline actually lands data. If the load date is stored in a different column, uses a different time zone, or is templated with the wrong logical date, the check runs against yesterday's partition or an empty one. An empty slice can also pass checks that count nulls or duplicates, because there is nothing to fail on. Confirm the partition expression against the table's load pattern for backfills and reruns, not only for the normal daily run.
The most serious risk is a check that passes because it tests the wrong slice, the wrong table, or a result that was never compared to anything meaningful. Before relying on any setting, run each check against deliberately bad fixtures: a small test table with a known null in a required column, a duplicate key, a count outside the tolerance band, and a partition with zero rows. Confirm that each bad fixture produces a failure with the expected message, and that a good fixture passes. Run the same tests with a partition clause pointed at an older load to confirm the check notices a stale slice. Keep these fixtures in version control with the DAG, so a change to a tolerance or partition expression triggers them again.
Security and operational considerations for check queries
Data quality checks run queries against production sources, so they inherit the access, code and load risks of any pipeline task. The cards below cover the decisions that keep checks from exposing data, slowing source systems or running untracked SQL. Each one connects to the check operators described above.
Use read-scoped check credentials
Check operators execute their SQL with the privileges of the Airflow connection they reference, so a connection built for loading data can also drop or overwrite tables. Create a separate read-only connection for checks, grant it select access only to the schemas being tested, and confirm that the database role cannot write. Review this grant whenever a new check is added to a DAG that uses a different schema.
Treat check SQL as reviewed code
Each column, table and threshold check is SQL that decides whether a pipeline passes, so a wrong condition can silently approve bad data. Keep check definitions in the same version-controlled repository as the DAG, require review for changes, and test them against a known sample of good and bad rows. A check that was never tested against a failing dataset has not been shown to fail.
Mask values in failure messages
When a check fails, the operator reports the expected and observed values, and a failing null or distinct count can echo sensitive identifiers into task logs, notifications and chat alerts. Decide which fields in regulated tables may appear in messages, keep row-level values out of the check output, and confirm that log retention and alert channels match the data classification. Masking rules belong in the security hardening work described in the related topic.
Load connections from the secrets backend
Check connections hold database hosts and passwords, and storing them as plain metadata in the Airflow database spreads them to every user who can view connections. Point the secrets backend at your vault or cloud secrets manager and keep the check connection ID as the only reference inside DAG code. Confirm that rotation of the read-only credential does not require a DAG change.
Plan check load on source databases
Column checks scan every listed column, and partition clauses only limit the work if the filter matches an indexed or partitioned column in the source. Checks that run on a large table at the same time as a load or a reporting peak can slow production queries. Ask the source owner for a window, schedule checks after the load task that produces the partition, and measure runtime in staging before enabling them on production tables.
Questions teams ask before adding SQL checks
What drives the effort of a data quality check suite?
The effort scales with how many tables, columns, partitions and SQL dialects the checks cover, and with how many of those checks need business rules agreed first. A suite that covers a handful of tables with row counts and null checks is a small job. A suite that spans dozens of tables, several warehouses and partition-aware thresholds takes longer because each check needs an agreed expected value, a tolerance and a decision about what happens on failure. Ask the partner to break the effort down by table and by dialect in the written scope, and to name which checks depend on information only your data owners can provide.
What does the work need from our team?
Four things need to be in place before the work starts:
- SQL knowledge of the warehouses being checked, including how partitions and date columns are stored
- A staging Airflow environment where checks can run against copies or samples of production data
- Connection access through Airflow connections, with credentials that follow your secrets policy
- Alert routing agreed with the people who respond to failures, such as a channel, an on-call rotation or a ticket queue
Data owners also need to confirm what a valid value looks like for key columns. Without those answers, checks either fire constantly or never fire.
Can our internal team build SQL checks alone?
Yes, for a small set of checks. Operators such as the common SQL column and table check operators are plain Airflow tasks, so a team that already writes DAGs can add null, uniqueness and row-count checks without outside help. The risk grows with scale. Thresholds that depend on seasonality, partitions and tolerances are easy to get wrong, and a check that passes when it should fail gives false confidence. Treat the first set of checks as a pilot, have a second engineer review the SQL and the expected values, and run the checks in staging before any alert reaches an on-call rotation.
How long does a SQL check rollout take?
The duration depends on the scope: the number of pipelines, tables and warehouses, how much business logic must be agreed, and how many alert destinations need to be wired up. No duration is fixed by the product itself. Ask for a written plan that lists the tables in order of priority, the checks for each one, the staging validation steps, the review gates and the cutover to production alerting. A plan that cannot name its dependencies on data owners and on access approvals is not yet ready to be scheduled.
How do SQL check operators differ from a full data quality platform?
SQL check operators run inside an Airflow DAG. They execute queries you define, compare the results to expected values and fail the task when a check does not pass. A full data quality platform adds a catalog of checks, profiling, lineage, ownership workflows, incident history and dashboards across many tools. Checks in Airflow are a good fit when the pipeline itself should stop bad data before it moves downstream. If you need organization-wide governance of quality rules, ownership and lineage, plan that as a separate layer. The two can work together, but they solve different problems and need different owners.
Does a failed check stop the pipeline?
It can, and that is usually the point. A check operator raises an error when its condition is not met, so downstream tasks that depend on it do not run unless you configure a different trigger rule. Decide per check whether a failure should block publication, mark data as suspect, or only alert. Blocking is safer for financial or regulated tables. Alert-only is often better for noisy metrics while thresholds are still being tuned. Write this decision into the check design so that on-call engineers know what a red task means.
What should we confirm about BigQuery and other warehouses?
Confirm which check operators your installed provider versions support for each warehouse. The common SQL provider covers generic checks, and the Google provider documents BigQuery-specific column and table check operators. Check the provider versions against your Airflow version, because Airflow 3 requires provider packages to match its supported releases. Ask the partner to test each dialect in your staging environment, since null handling, partition syntax and tolerance behavior can differ between engines.
What runs after the checks are delivered?
Checks need upkeep. Expected values drift as the business changes, new columns arrive, partitions change format and provider packages are upgraded. Decide who owns each check after handover, how threshold changes are reviewed and how check code is versioned with the DAGs. Ongoing support, upgrades and continuous testing are available from DeployOpen as retainers, and the scope of any retainer is agreed in writing before work starts.
How DeployOpen scopes and delivers data quality checks
DeployOpen plans and builds SQL-based data quality checks for Apache Airflow inside infrastructure you control. We start from the tables and rules your pipelines depend on, place each check where it protects downstream work, and hand over tested DAG code, documentation and alert routing that your team can run.
How we scope
- Engagements are fixed-scope, with the list of pipelines, tables and checks agreed in writing before work starts.
- We map which columns need null, distinct, unique, minimum and maximum checks, and which need value or threshold rules.
- The scope records tolerances, partition clauses and the owner who decides what counts as an acceptable deviation.
How we build
- We write check tasks with the common SQL and provider operators, placed so bad data stops before it reaches loads or reports.
- Each check runs against a staging environment first, with test data that includes deliberate failures to confirm alerts fire.
- Where warehouse-specific operators apply, such as BigQuery, we confirm the provider version in your environment before relying on them.
How we hand over
- You receive DAG code, check definitions and configuration in your own repository, with the failure paths documented.
- The handover includes a runbook that explains each check, its threshold, its owner and the steps when a check fails.
- Alert routing to your chosen channels is configured and tested, and we walk your team through a failed-check drill.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- Retainer work can cover threshold reviews as source volumes change and adjustments when Airflow versions move forward.
- Ask any quote to name who maintains the check definitions after the engagement ends and how changes are approved.
