A monitoring dashboard tells you the allocation was wrong after the number already hit the production report. A quality gate stops the load before it gets there. That is the whole difference, and most operators only have the first one.
We see this pattern constantly. A vendor SCADA API renames a tag, or the OCC changes a fixed-width column, or somebody’s allocation input sums to 103 percent because a working interest decimal got fat-fingered. The pipeline runs green. Row counts look normal. Three weeks later the revenue analyst is staring at a number that does not tie, and now you are doing forensics on a load that happened during last cycle instead of catching it the night it landed.
This post is about the test layer that sits inside the pipeline and refuses to let bad data through. It is a companion to two others in this batch: data contracts are the agreement you make with a producer at the source boundary, and migrating legacy ETL is about replacing the plumbing itself. Quality gates are what runs between those two: the automated checks that decide, on every load, whether the data is good enough to promote.
Gate versus monitor
The words get used interchangeably and they should not be.
A monitor observes. It watches row counts, null rates, freshness, and score distributions, and it tells you when something looks off. It runs after the fact, on data that is already in the warehouse, already queryable, already feeding a dashboard. Monitoring is genuinely useful. We wrote a whole argument for it in the context of SCADA reliability. But a monitor’s job ends at the alert. By the time it fires, the bad data is already downstream.
A gate decides. It runs inline, as a step in the pipeline, and it has the authority to stop the flow. Pass, and the data promotes to the next layer. Fail, and it does not. The load halts, or the bad rows get quarantined, or an alert goes out and the pipeline continues, depending on how severe the failure is and how much you trust the check.
The mechanics overlap. A dbt freshness test can be a monitor or a gate depending on whether a failing result blocks the downstream models. What makes something a gate is that a failure has consequences for the flow, not just for someone’s inbox.
Two tools, two jobs
The stack we reach for splits the work by where the data is in its life.
Great Expectations validates raw data on arrival, before it touches your modeled tables. Its natural home is the landing zone: the JSON that came back from a vendor SCADA API, the CSV the OCC published, the file a partner dropped on SFTP. This is data you do not control and cannot trust yet, and you want to reject it before it contaminates anything.
dbt tests validate your transformation logic and the shape of your modeled output. Schema tests and generic tests assert on the data in your tables. Unit tests, added in dbt 1.8, assert on the logic itself: given these mock inputs, does the model produce exactly these outputs.[1] That last one is new enough that a lot of teams have not picked it up, and for upstream transformation logic it is the most valuable of the three.
You want both. Great Expectations catches the vendor who changed a field. dbt catches the analyst who changed a model. Neither one covers the other’s failure mode.
Great Expectations at the source boundary
Here is a raw check against production data landing from a vendor API, before it goes anywhere near a PPDM table. GX Core 1.0 (released August 2024) changed the API from the older 0.18 style, so this is the current shape.[2]
import great_expectations as gx
context = gx.get_context()
# The raw landing table, one row per well per production day
data_source = context.data_sources.add_postgres(
"landing",
connection_string="postgresql+psycopg2://etl@warehouse/landing",
)
asset = data_source.add_table_asset(
name="prod_daily_raw",
table_name="vendor_prod_daily",
)
batch_definition = asset.add_batch_definition_whole_table("nightly_load")
suite = context.suites.add(
gx.ExpectationSuite(name="prod_daily_raw_gate")
)
# API-14 well number: 14 digits, no nulls, no duplicates within a load day
suite.add_expectation(
gx.expectations.ExpectColumnValuesToMatchRegex(
column="api_number",
regex=r"^\d{14}$",
)
)
suite.add_expectation(
gx.expectations.ExpectColumnValuesToNotBeNull(column="api_number")
)
# Production date has to be inside a sane window, not 1900 and not next year
suite.add_expectation(
gx.expectations.ExpectColumnValuesToBeBetween(
column="prod_date",
min_value="2000-01-01",
max_value="{{ tomorrow }}",
)
)
# Oil, gas, water volumes are non-negative and physically plausible
suite.add_expectation(
gx.expectations.ExpectColumnValuesToBeBetween(
column="oil_bbl",
min_value=0,
max_value=50000,
severity="warning", # implausibly high is a flag, not a hard stop
)
)
validation_definition = context.validation_definitions.add(
gx.ValidationDefinition(
name="prod_daily_raw_validation",
data=batch_definition,
suite=suite,
)
)
result = validation_definition.run()
if not result.success:
raise ValueError(f"Raw production gate failed: {result}")
Note the severity on the volume check. A malformed API number is a hard failure, because everything downstream joins on it. A single well reporting 50,000 barrels in a day is probably wrong but might be real, so it gets flagged for review instead of killing the load. Encoding that judgment is most of the work.
dbt tests on the transformation logic
Once raw data passes the gate and lands in your models, the failure mode shifts. Now the risk is not bad input, it is bad logic. This is where dbt earns its place, and where unit tests matter more than the schema tests everyone already writes.
Take an allocation model. Facility-level metered volume gets distributed to the wells behind it by each well’s share. The rule that must always hold: the allocated volumes sum back to the facility total, and the shares sum to 100 percent. A schema test cannot check that. A unit test can.
# models/silver/_silver__unit_tests.yml
unit_tests:
- name: allocation_distributes_full_volume
model: silver_well_allocation
given:
- input: ref('stg_facility_volume')
rows:
- {facility_id: "F-01", prod_date: "2026-07-01", gas_mcf: 1000}
- input: ref('stg_well_allocation_factor')
rows:
- {facility_id: "F-01", api_number: "35017200010000", alloc_pct: 0.60}
- {facility_id: "F-01", api_number: "35017200020000", alloc_pct: 0.40}
expect:
rows:
- {api_number: "35017200010000", prod_date: "2026-07-01", allocated_mcf: 600}
- {api_number: "35017200020000", prod_date: "2026-07-01", allocated_mcf: 400}
This runs against mocked inputs, not the warehouse, so it is fast and deterministic. When someone edits the allocation SQL six months from now and quietly breaks the rounding, this test fails in CI before the change ever ships. dbt Labs recommends running unit tests in development and CI rather than production, which is exactly right: they validate logic, not data.[1:1]
The same pattern covers the transformations that bite upstream teams most:
- A daily-to-monthly rollup. Feed it 31 daily rows across a month boundary and assert the monthly total and the correct bucketing. Off-by-one on the month edge is a classic silent error.
- A working interest revenue calculation. Feed a known volume, price, and WI decimal, assert the exact net revenue. WI math is where fat-fingered decimals hide.
For the modeled data itself, generic and schema tests still carry weight. A unique and not_null on the well surrogate key. An accepted_range on allocation percentage. A custom singular test that the allocated volumes reconcile to the facility total within a tolerance:
-- tests/assert_allocation_reconciles.sql
select
a.facility_id,
a.prod_date,
sum(a.allocated_mcf) as allocated_total,
f.gas_mcf as facility_total
from {{ ref('silver_well_allocation') }} a
join {{ ref('stg_facility_volume') }} f
on a.facility_id = f.facility_id
and a.prod_date = f.prod_date
group by a.facility_id, a.prod_date, f.gas_mcf
having abs(sum(a.allocated_mcf) - f.gas_mcf) > 0.01
Any row this query returns is a facility whose allocation does not add up. In a well-behaved run it returns nothing.
Wiring the gate into the DAG
A gate is only a gate if a failure changes what the pipeline does. In Airflow, you have three responses, and the right one depends on severity.
Fail the task and stop. The strictest option. The check raises, the task fails, downstream tasks do not run because they depend on it. Use this for the checks where bad data must never promote: the malformed API number, the allocation that does not reconcile. The nightly load simply does not complete, and someone gets paged.
Route to a dead letter queue. The load continues on the good rows, and the failing rows get quarantined in a side table for review. Use this when a partial load is better than no load, which is common with vendor feeds where one bad well should not block the other 400. We covered the dead letter pattern in the broader pipeline patterns reference.
Alert and continue. The softest. The check runs, logs a warning, fires an alert, and the pipeline keeps going. Use this for the plausibility flags: the 50,000-barrel day, the null rate that crept up. These are things a human should look at, not things that should halt close.
Here is the branching wired into a DAG, with the gate deciding which path the load takes.
from airflow.decorators import dag, task
from airflow.exceptions import AirflowFailException
from datetime import datetime
@dag(schedule="0 3 * * *", start_date=datetime(2026, 7, 1), catchup=False)
def prod_daily_ingest():
@task
def raw_quality_gate():
result = run_ge_suite("prod_daily_raw_gate")
hard_failures = [
r for r in result.results
if not r.success and r.expectation_config.severity != "warning"
]
if hard_failures:
# hard stop: nothing promotes tonight
raise AirflowFailException(
f"{len(hard_failures)} blocking checks failed on raw load"
)
# soft failures already logged and alerted inside run_ge_suite
return "gate_passed"
@task
def quarantine_bad_rows():
# move rows failing row-level checks into landing.prod_daily_reject
route_failures_to_dlq("vendor_prod_daily", "prod_daily_reject")
@task
def load_to_ppdm(_gate):
promote_clean_rows("vendor_prod_daily", "ppdm.production")
@task
def run_dbt_build():
# dbt build runs models and their schema/generic tests together;
# a failing test fails the task and blocks downstream models
run_dbt(["build", "--select", "silver_well_allocation+"])
gate = raw_quality_gate()
quarantine_bad_rows()
loaded = load_to_ppdm(gate)
run_dbt_build().set_upstream(loaded)
prod_daily_ingest()
The important detail is dbt build rather than dbt run followed by a separate dbt test. build interleaves the tests with the models, so a model runs, its tests run immediately, and if they fail, the models depending on it never build. That is the gate behavior you want: a broken silver model does not get to poison gold.
The three checks to build first
If you are starting from nothing, do not try to test everything. Three checks catch a disproportionate share of what actually goes wrong in upstream loads, and every operator should have them before anything fancier.
API number format. The API-14 (or API-10, depending on your convention) is the join key for nearly every cross-system report. A malformed one either fails the join silently or, worse, joins to the wrong well. A regex check on arrival is cheap and catches vendor format drift immediately. We wrote about why the API number is not the clean primary key people assume in the OCC ingestion post.
Date range sanity. Production dates that land in 1900 or in next quarter are almost always a parsing bug or a timezone problem, and they wreck any time-based rollup. A bounded range check on every date column costs nothing and catches a whole class of errors.
Allocation sums to 100 percent. If the allocation factors behind a facility do not sum to one, the allocated volumes are wrong, and that error flows straight into revenue and reserves. This is the single most valuable domain check in upstream, and it is exactly the kind of rule that a schema test cannot express but a reconciliation test can.
These three are the floor, not the ceiling. But an operator who has only these three catches more real problems than one with a wall of monitoring dashboards and no gates at all.
Where this fits
Gates do not replace monitoring and they do not replace contracts. A contract sets the expectation with the producer. A gate enforces it on every load. A monitor watches what got through. You want all three, and they reinforce each other: the assertions in a data contract are frequently the same assertions you implement as a gate, and the checks a gate runs are the same ones a monitor keeps watching after promotion.
The reason to start with gates is where they act. Monitoring tells you about a problem you already have. A gate is the difference between finding the bad allocation the night it landed and finding it in the diligence room. One of those is a five-minute fix. The other is a credibility problem in front of a buyer.
dbt Labs, “Unit tests” (dbt Developer Hub). Unit tests are available from dbt v1.8. https://docs.getdbt.com/docs/build/unit-tests ↩︎ ↩︎
Great Expectations, “GX Core overview” and “Try GX Core.” GX Core 1.0 was released in August 2024 with API changes from the 0.18 series. https://docs.greatexpectations.io/docs/core/introduction/gx_overview/ ↩︎