A Fresh dbt Source Can Still Be Missing a Partition

A source can be fresh and still be unfit to publish.

Consider a batch expected to contain deliveries for three positive-count tenant/date partitions. Tenants A and B arrive two minutes ago; tenant C never arrives. A dbt source-freshness check sees the recent maximum loaded_at from A or B and can correctly report that the selected source population is fresh. Ordinary not_null and unique tests can also pass because every row that did arrive is structurally valid. Neither result establishes that tenant C exists.

That distinction matters operationally. dbt documents source freshness in terms of the most recent loaded timestamp, with an optional filter restricting the population scanned. The conceptual query is a max(loaded_at_field) over the selected source rows, compared with a snapshot time and configured thresholds. That is a recency measurement, not an inference about absent logical deliveries.

This playbook builds a deterministic acceptance gate around that boundary. An independently authored manifest declares what must arrive for batch 2026-09-23; PostgreSQL contains the synthetic landing rows; singular dbt tests reconcile expected and observed tenant/date units; a separate producer-completion relation proves that an expected zero-row delivery is legitimately empty; and publication remains blocked until freshness, completeness, manifest validity and evidence identity agree.

The laboratory is intentionally narrow. It does not claim to discover unknown missing records, perform statistical anomaly detection, diagnose incremental MERGE predicates or substitute for upstream truth. It answers one release question: did the deliveries that this batch contract explicitly declared actually arrive, in the declared counts, with acceptable recency and auditable evidence?

Define the delivery contract before freshness

The acceptance unit is a logical partition: (tenant_id, business_date) within one batch_id. No PostgreSQL table partitioning is required. Physical storage layout is irrelevant to the proof.

For batch 2026-09-23, use this contract:

The contract owner is the producer-side data owner: the party capable of stating which tenant/date units should exist and whether a zero-row delivery is intentional. The analytics platform should store and validate that declaration, but it should not manufacture the expectation from whatever happened to land.

That independence is the essential control. SELECT DISTINCT tenant_id, business_date FROM source_events cannot tell you that tenant C is absent, because tenant C contributes no source row. Deriving the expected set from observed rows collapses the oracle into the thing being tested.

The same principle explains why a single recent record and complete batch delivery answer different questions:

• max(loaded_at) asks whether at least one qualifying row is recent enough to satisfy the configured freshness threshold.

• partition reconciliation asks whether every independently declared delivery unit exists with the expected effect.

• publication readiness additionally asks whether the evidence belongs to this batch, this manifest revision and this gate run.

dbt’s freshness resource property explicitly documents loaded_at_field, warn_after, error_after and filter; its current reference also shows the conceptual max(loaded_at_field) query. A freshness filter limits the rows considered by that freshness calculation and does not redefine every downstream use of the source. That is why “freshness versus completeness” should remain two fields in the acceptance result rather than be compressed into one green icon.

This article is therefore narrower than the broader dbt and Snowflake analytics stack. The concern here begins at one declared inbound batch and ends at its release decision, rather than surveying an end-to-end analytics architecture. The same is true of data lake and warehouse quality foundations: those broader practices are useful context, but this gate must prove one concrete expected-delivery set.

The manifest needs enough information to be a release contract rather than a row-count afterthought:

batch_id
tenant_id
business_date
expected_row_count
allow_empty
manifest_revision
manifest_owner
manifest_created_at
delivery_deadline

expected_row_count must be nonnegative. In this fixture, allow_empty=true is permitted only when expected_row_count=0. Keys must be unique at (batch_id, tenant_id, business_date). manifest_revision must equal the revision authorized for the gate. Missing manifests, multiple revisions, duplicate keys or an obsolete revision are not “no failures”; they are invalid evidence.

The zero-row case also requires a second signal. Observing zero facts cannot distinguish “the producer intentionally completed an empty partition” from “nothing ever arrived.” An arrival/completion record therefore states that tenant_zero completed, declared zero rows and belongs to manifest revision r1. That record is evidence of delivery completion; it is not the fact data itself.

This design has a deliberate limitation. A false or incomplete producer manifest can still make the reconciliation look complete. The gate proves conformance to an authorized expectation, not omniscient completeness. That limitation is why manifest ownership and revision validity belong in the release decision.

Pin and isolate the reproducible Core lab

This laboratory deliberately targets the dbt Core 1.10 configuration line rather than whatever happens to be newest on the publication date. The reference configuration uses the placement documented for 1.10: loaded_at_field under config, while the freshness block had already moved into configuration in the 1.9 line. The current dbt reference records both compatibility changes.

Use these reference pins:

The PostgreSQL project lists 17.11 among the releases published on August 13, 2026. These pins are a controlled lab baseline, not a claim that Core 1.10 is the current dbt release line. Current dbt documentation already exposes later v1 and v2 documentation, and now describes dbt source freshness as a legacy command for v2 while retaining it for backward compatibility and sources.json production. That later documentation should not be silently mixed into a Core 1.10 exercise.

Execution status for this publication: the fixture below is reproducible but was not executed against an installed dbt/PostgreSQL stack in the article-production environment. Therefore the pass/fail results described below are expected results derived from the fixture, not invented command observations. An operator should not promote the batch until the exact installed versions, command exits and artifacts have been captured from the authorized runtime.

Pin the Python packages explicitly:

# requirements.txt
dbt-core==1.10.23
dbt-postgres==1.10.2

Install them into a dedicated virtual environment, then capture what the resolver actually installed rather than assuming the requested versions became the runtime:

python -m venv .venv
. .venv/bin/activate

python -m pip install --upgrade pip
python -m pip install -r requirements.txt

python --version
python -m pip show dbt-core dbt-postgres
dbt --version

For a controlled release environment, also retain the resolver’s complete installed set:

python -m pip freeze --all > python-packages.lock.txt

The gate should compare those observed values with its approved baseline. A request for 1.10.23 is not evidence that 1.10.23 actually ran.

Configure one isolated PostgreSQL schema. The profile below assumes connection secrets are supplied as environment variables:

# ~/.dbt/profiles.yml
partition_gate:
  target: lab
  outputs:
    lab:
      type: postgres
      host: "{{ env_var('PGHOST') }}"
      port: 5432
      user: "{{ env_var('PGUSER') }}"
      password: "{{ env_var('PGPASSWORD') }}"
      dbname: "{{ env_var('PGDATABASE') }}"
      schema: ae_acceptance_20260923
      threads: 1

Use a small project:

# dbt_project.yml
name: partition_gate
version: "1.0.0"
config-version: 2
profile: partition_gate

model-paths: ["models"]
test-paths: ["tests"]

vars:
  gate_batch_id: "2026-09-23"
  manifest_revision: "r1"

Create the synthetic relations. The manifest is inserted before the fact rows to emphasize that it is an authored expectation, not a summary reconstructed from arrival:

-- sql/setup.sql
begin;

drop schema if exists ae_acceptance_20260923 cascade;
create schema ae_acceptance_20260923;
set search_path to ae_acceptance_20260923;

create table source_events (
    tenant_id       text        not null,
    business_date   date        not null,
    batch_id        text        not null,
    record_id       text        not null,
    loaded_at       timestamptz not null
);

create table expected_partitions (
    batch_id            text        not null,
    tenant_id           text        not null,
    business_date       date        not null,
    expected_row_count  integer     not null,
    allow_empty         boolean     not null,
    manifest_revision   text        not null,
    manifest_owner      text        not null,
    manifest_created_at timestamptz not null,
    delivery_deadline   timestamptz not null
);

create table delivery_completion (
    batch_id           text        not null,
    tenant_id          text        not null,
    business_date      date        not null,
    manifest_revision  text        not null,
    producer_complete  boolean     not null,
    declared_row_count integer     not null,
    completed_at       timestamptz not null
);

insert into expected_partitions values
('2026-09-23','tenant_a',   date '2026-09-22',2,false,'r1','producer_contract_owner',
 clock_timestamp(), clock_timestamp() + interval '1 hour'),
('2026-09-23','tenant_b',   date '2026-09-22',2,false,'r1','producer_contract_owner',
 clock_timestamp(), clock_timestamp() + interval '1 hour'),
('2026-09-23','tenant_c',   date '2026-09-22',2,false,'r1','producer_contract_owner',
 clock_timestamp(), clock_timestamp() + interval '1 hour'),
('2026-09-23','tenant_zero',date '2026-09-22',0,true, 'r1','producer_contract_owner',
 clock_timestamp(), clock_timestamp() + interval '1 hour');

insert into delivery_completion values
('2026-09-23','tenant_a',   date '2026-09-22','r1',true,2,clock_timestamp()),
('2026-09-23','tenant_b',   date '2026-09-22','r1',true,2,clock_timestamp()),
('2026-09-23','tenant_zero',date '2026-09-22','r1',true,0,clock_timestamp());

insert into source_events values
('tenant_a',date '2026-09-22','2026-09-23','A-001',clock_timestamp() - interval '3 minutes'),
('tenant_a',date '2026-09-22','2026-09-23','A-002',clock_timestamp() - interval '2 minutes'),
('tenant_b',date '2026-09-22','2026-09-23','B-001',clock_timestamp() - interval '3 minutes'),
('tenant_b',date '2026-09-22','2026-09-23','B-002',clock_timestamp() - interval '2 minutes');

commit;

tenant_c deliberately has no rows and no completion record. tenant_zero deliberately has no fact rows but does have an independently recorded zero completion.

Record the server evidence immediately:

psql "$PGDATABASE" -Atc "
select version();
show TimeZone;
"

The gate should require UTC for this fixture, or explicitly normalize timestamps before comparison.

Define the dbt source with an explicit loaded_at_field:

# models/sources.yml
version: 2

sources:
  - name: acceptance_lab
    schema: ae_acceptance_20260923
    tables:
      - name: source_events
        config:
          loaded_at_field: loaded_at
          freshness:
            warn_after: {count: 6, period: hour}
            error_after: {count: 8, period: hour}
            filter: "batch_id = '2026-09-23'"
        columns:
          - name: tenant_id
            data_tests:
              - not_null:
                  config:
                    tags: ["present_row"]
          - name: business_date
            data_tests:
              - not_null:
                  config:
                    tags: ["present_row"]
          - name: batch_id
            data_tests:
              - not_null:
                  config:
                    tags: ["present_row"]
          - name: record_id
            data_tests:
              - not_null:
                  config:
                    tags: ["present_row"]
              - unique:
                  config:
                    tags: ["present_row"]
          - name: loaded_at
            data_tests:
              - not_null:
                  config:
                    tags: ["present_row"]

      - name: expected_partitions

      - name: delivery_completion

business_date, loaded_at and batch_id are intentionally not interchangeable. business_date defines the logical consumer period. loaded_at answers the recency question. batch_id freezes the acceptance run. A row loaded today does not silently redefine which business date was required.

Immediately after setup, the newest loaded row is expected to be roughly two minutes old. With a six-hour warning threshold, freshness should pass if the fixture is executed promptly. The large interval prevents a normal local test run from becoming stale for an unrelated reason, while the artifact still records the actual measurement clock.

Prove why freshness can be green while coverage fails

Run the explicit Core source-freshness command:

dbt source freshness \
  --select source:acceptance_lab.source_events

Do not substitute dbt build for this step. dbt’s source-freshness guide states that dbt build does not include source freshness checks. The dbt build reference likewise describes build as operating over selected DAG resources and their associated execution order; resource selection therefore has to be treated as part of the evidence rather than assuming that a generally green build exercised a separate freshness gate.

For this fixture, the predicted source-freshness result is pass, provided it runs before the configured six-hour warning boundary. That expected result is not a dbt defect. The selected batch contains recent rows for tenants A and B, so its maximum loaded_at is recent. The configured recency question is answered correctly. Tenant C’s absence contributes no timestamp capable of lowering or nullifying that maximum.

Archive target/sources.json immediately. dbt’s sources.json artifact reference documents result fields including unique_id, max_loaded_at, snapshotted_at, freshness criteria, execution information and status. Do not report “freshness passed” from console color alone. Preserve the exact source result and its measurement time.

Next run the ordinary present-row tests:

dbt test --select tag:present_row

Their predicted result is also pass. Four rows exist. Their required fields are populated, and their record_id values are unique. This demonstrates the blind spot cleanly: tests whose universe consists only of present records have no row representing the missing tenant_c delivery.

The acceptance test must instead create that missing row by comparing observed facts to the external expectation. dbt’s data-tests documentation defines a singular data test as a SQL query whose returned rows are failures; zero returned failures means the test passes. Singular tests belong in the tests directory, and dbt advises omitting a trailing semicolon.

First make the manifest incapable of disappearing silently:

-- tests/assert_manifest_valid_and_current.sql
{{ config(severity='error', tags=['acceptance_gate', 'manifest_gate']) }}

with manifest as (
    select 
    from {{ source('acceptance_lab', 'expected_partitions') }}
    where batch_id = '{{ var("gate_batch_id") }}'
),

summary as (
    select
        count() as row_count,
        count(distinct manifest_revision) as revision_count,
        min(manifest_revision) as actual_revision
    from manifest
),

duplicate_keys as (
    select
        tenant_id,
        business_date,
        'DUPLICATE_MANIFEST_KEY'::text as reason
    from manifest
    group by tenant_id, business_date
    having count(*) > 1
),

invalid_counts as (
    select
        tenant_id,
        business_date,
        case
            when expected_row_count < 0 then 'NEGATIVE_EXPECTED_COUNT'
            when expected_row_count = 0 and not allow_empty
                then 'ZERO_NOT_AUTHORIZED'
            when expected_row_count > 0 and allow_empty
                then 'POSITIVE_COUNT_MARKED_EMPTY'
        end as reason
    from manifest
    where expected_row_count < 0
       or (expected_row_count = 0 and not allow_empty)
       or (expected_row_count > 0 and allow_empty)
),

header_failures as (
    select
        null::text as tenant_id,
        null::date as business_date,
        case
            when row_count = 0 then 'MANIFEST_MISSING'
            when revision_count <> 1 then 'MULTIPLE_MANIFEST_REVISIONS'
            when actual_revision <> '{{ var("manifest_revision") }}'
                then 'MANIFEST_REVISION_MISMATCH'
        end as reason
    from summary
    where row_count = 0
       or revision_count <> 1
       or actual_revision <> '{{ var("manifest_revision") }}'
)

select tenant_id, business_date, reason from header_failures
union all
select tenant_id, business_date, reason from duplicate_keys
union all
select tenant_id, business_date, reason from invalid_counts

Now compare the declared partition grain rather than a grand total:

-- tests/assert_expected_partition_coverage.sql
{{ config(severity='error', tags=['acceptance_gate', 'completeness_gate']) }}

with expected as (
    select
        batch_id,
        tenant_id,
        business_date,
        expected_row_count,
        allow_empty,
        manifest_revision
    from {{ source('acceptance_lab', 'expected_partitions') }}
    where batch_id = '{{ var("gate_batch_id") }}'
),

observed as (
    select
        batch_id,
        tenant_id,
        business_date,
        count(*)::bigint as observed_row_count
    from {{ source('acceptance_lab', 'source_events') }}
    where batch_id = '{{ var("gate_batch_id") }}'
    group by batch_id, tenant_id, business_date
),

reconciled as (
    select
        coalesce(e.batch_id, o.batch_id) as batch_id,
        coalesce(e.tenant_id, o.tenant_id) as tenant_id,
        coalesce(e.business_date, o.business_date) as business_date,
        e.expected_row_count,
        coalesce(o.observed_row_count, 0) as observed_row_count,
        e.manifest_revision,
        case
            when e.batch_id is null
                then 'UNEXPECTED_PARTITION'
            when o.batch_id is null and e.expected_row_count > 0
                then 'MISSING_DELIVERY'
            when e.expected_row_count = 0
                 and coalesce(o.observed_row_count, 0) > 0
                then 'DECLARED_ZERO_HAS_ROWS'
            when coalesce(o.observed_row_count, 0) <> e.expected_row_count
                then 'ROW_COUNT_MISMATCH'
        end as reason
    from expected e
    full outer join observed o
      on e.batch_id = o.batch_id
     and e.tenant_id = o.tenant_id
     and e.business_date = o.business_date
)

select
    batch_id,
    tenant_id,
    business_date,
    expected_row_count,
    observed_row_count,
    reason,
    manifest_revision
from reconciled
where reason is not null

The predicted failing record is:

That row is reasoned from the supplied fixture, not observed command output.

The expected-zero partition still needs completion proof. Otherwise tenant_zero and the missing tenant_c would both be represented by “no facts,” differing only because the manifest count happens to be zero.

-- tests/assert_zero_partition_completion.sql
{{ config(severity='error', tags=['acceptance_gate', 'zero_gate']) }}

with expected_zero as (
    select 
    from {{ source('acceptance_lab', 'expected_partitions') }}
    where batch_id = '{{ var("gate_batch_id") }}'
      and expected_row_count = 0
      and allow_empty
),

observed as (
    select
        batch_id,
        tenant_id,
        business_date,
        count()::bigint as observed_row_count
    from {{ source('acceptance_lab', 'source_events') }}
    where batch_id = '{{ var("gate_batch_id") }}'
    group by 1, 2, 3
),

checked as (
    select
        e.batch_id,
        e.tenant_id,
        e.business_date,
        e.manifest_revision,
        coalesce(o.observed_row_count, 0) as observed_row_count,
        c.declared_row_count,
        c.producer_complete,
        case
            when c.batch_id is null then 'ZERO_COMPLETION_MISSING'
            when not c.producer_complete then 'ZERO_NOT_COMPLETE'
            when c.manifest_revision <> e.manifest_revision
                then 'ZERO_COMPLETION_WRONG_REVISION'
            when c.declared_row_count <> 0
                then 'ZERO_COMPLETION_NONZERO_COUNT'
            when coalesce(o.observed_row_count, 0) <> 0
                then 'DECLARED_ZERO_HAS_ROWS'
        end as reason
    from expected_zero e
    left join {{ source('acceptance_lab', 'delivery_completion') }} c
      on e.batch_id = c.batch_id
     and e.tenant_id = c.tenant_id
     and e.business_date = c.business_date
    left join observed o
      on e.batch_id = o.batch_id
     and e.tenant_id = o.tenant_id
     and e.business_date = o.business_date
)

select *
from checked
where reason is not null

For the fixture, that test is expected to pass: tenant_zero has no facts, an authorized zero expectation and an independent producer completion declaring zero.

The evidence now distinguishes three propositions instead of conflating them: A/B show recent positive deliveries; C is a missing positive delivery; tenant_zero is an acknowledged legitimate empty delivery.

Add negative controls so the gate cannot pass vacuously

A useful acceptance lab needs controls that prove the test fails for the intended reasons, not merely for the happy-path counterexample.

Run each mutation separately, then recreate the baseline with sql/setup.sql. These are test designs, not observed executions.

For example, this mutation produces a duplicate without changing the logical tenant:

insert into ae_acceptance_20260923.source_events
    (tenant_id, business_date, batch_id, record_id, loaded_at)
values
    ('tenant_a', date '2026-09-22', '2026-09-23',
     'A-001', clock_timestamp());

A source-level uniqueness test should expose A-001; the partition reconciliation should independently report that A now has three rows instead of two. Those are related but different pieces of evidence: record uniqueness and declared delivery count.

An unexpected current-batch tenant should be visible because the coverage test uses a full outer join, not merely expected left join observed:

insert into ae_acceptance_20260923.source_events
values
    ('tenant_x', date '2026-09-22', '2026-09-23',
     'X-001', clock_timestamp());

That row has no expected-side match, so the test predicts UNEXPECTED_PARTITION.

The compensating-count control is particularly important. Suppose A is incorrectly declared as one row while B is incorrectly declared as three. The manifest total remains four and the source total remains four. A global reconciliation reports equality:

expected total = 1 + 3 = 4
observed total = 2 + 2 = 4

Yet the declared grain is wrong twice:

tenant_a: expected 1, observed 2
tenant_b: expected 3, observed 2

The gate therefore reconciles before aggregation. Equal grand totals are insufficient evidence when the publication contract is tenant/date delivery coverage.

A stale or missing manifest needs its own branch because SQL tests have an important acceptance property: a singular test that returns no rows passes. dbt explicitly defines returned rows as failures. If a badly written coverage query starts from expected_partitions and that relation contains no rows, it can return nothing and appear successful. assert_manifest_valid_and_current.sql prevents that vacuous pass by manufacturing a failure row when the expected set is absent or revision-invalid.

In the isolated laboratory, also assert that all fact rows carry the active batch identifier:

-- tests/assert_fixture_batch_scope.sql
{{ config(severity='error', tags=['acceptance_gate', 'fixture_only']) }}

select
    record_id,
    batch_id,
    'WRONG_BATCH_ID' as reason
from {{ source('acceptance_lab', 'source_events') }}
where batch_id <> '{{ var("gate_batch_id") }}'

That test is appropriate because this schema is intentionally isolated to one acceptance batch. It should not be copied unchanged into a production landing table that legitimately stores many historical batches.

Finally, keep a tiny exact-row control for the synthetic fixture so that a typo in the setup does not masquerade as the intended completeness failure:

-- tests/assert_fixture_present_rows_exact.sql
{{ config(severity='error', tags=['fixture_only']) }}

with expected(record_id, tenant_id) as (
    values
        ('A-001','tenant_a'),
        ('A-002','tenant_a'),
        ('B-001','tenant_b'),
        ('B-002','tenant_b')
),

actual as (
    select record_id, tenant_id
    from {{ source('acceptance_lab', 'source_events') }}
    where batch_id = '{{ var("gate_batch_id") }}'
),

diff as (
    select
        coalesce(e.record_id, a.record_id) as record_id,
        e.tenant_id as expected_tenant,
        a.tenant_id as actual_tenant
    from expected e
    full outer join actual a using (record_id)
)

select *
from diff
where expected_tenant is distinct from actual_tenant

This control verifies only the deliberately tiny fixture. It should not be confused with general value-quality validation. Partition row counts cannot prove that every business value inside those rows is correct; they prove the deterministic delivery claims that were explicitly authored.

Block publication and bind evidence to one gate run

A reliability gate is not complete until its execution order prevents downstream publication.

The required sequence is:

1. Capture runtime and database identity.

2. Freeze or isolate the input batch.

3. Run explicit source freshness.

4. Validate the manifest.

5. Run completeness and zero-delivery tests.

6. Reconcile the archived artifacts.

7. Only after all required verdicts pass, execute the consuming build/publication step.

This is deliberately smaller than cloud-native pipeline architecture. The purpose is not to choose an orchestration platform. It is to make the blocking semantics visible.

Before execution, list exactly which tests will be selected:

dbt ls --select tag:manifest_gate --resource-type test
dbt ls --select "tag:completeness_gate tag:zero_gate" --resource-type test

That selection evidence matters because a green invocation cannot establish coverage if the coverage test was never selected. dbt’s build documentation emphasizes selection and selected-DAG behavior, and its run artifacts describe what actually executed rather than every resource merely present in the project.

Use a gate driver that removes stale target artifacts before every sub-invocation, archives new artifacts immediately and records nonzero exit codes rather than swallowing them:

#!/usr/bin/env bash
set -u -o pipefail

BATCH_ID="2026-09-23"
REVISION="r1"
GATE_RUN_ID="$(date -u +%Y%m%dT%H%M%SZ)-$$"
EVIDENCE="evidence/${BATCH_ID}/${GATE_RUN_ID}"

mkdir -p \
  "${EVIDENCE}/runtime" \
  "${EVIDENCE}/freshness" \
  "${EVIDENCE}/manifest" \
  "${EVIDENCE}/completeness"

python --version > "${EVIDENCE}/runtime/python.txt" 2>&1
python -m pip show dbt-core dbt-postgres \
  > "${EVIDENCE}/runtime/python-packages.txt"
dbt --version > "${EVIDENCE}/runtime/dbt-version.txt" 2>&1

psql "$PGDATABASE" -Atc "select version(); show TimeZone;" \
  > "${EVIDENCE}/runtime/postgres.txt"

dbt ls --select tag:manifest_gate --resource-type test \
  > "${EVIDENCE}/manifest/selected-resources.txt"

dbt ls --select "tag:completeness_gate tag:zero_gate" \
  --resource-type test \
  > "${EVIDENCE}/completeness/selected-resources.txt"

# Freshness
rm -f target/sources.json
dbt source freshness \
  --select source:acceptance_lab.source_events
fresh_rc=$?

if [[ -f target/sources.json ]]; then
  cp target/sources.json "${EVIDENCE}/freshness/sources.json"
else
  fresh_rc=70
fi
printf '%s\n' "$fresh_rc" > "${EVIDENCE}/freshness/exit-code.txt"

# Manifest validity
rm -f target/run_results.json
dbt test \
  --select tag:manifest_gate \
  --vars "{\"gate_batch_id\":\"${BATCH_ID}\",\"manifest_revision\":\"${REVISION}\"}"
manifest_rc=$?

if [[ -f target/run_results.json ]]; then
  cp target/run_results.json "${EVIDENCE}/manifest/run_results.json"
else
  manifest_rc=71
fi
printf '%s\n' "$manifest_rc" > "${EVIDENCE}/manifest/exit-code.txt"

# Coverage and confirmed-zero logic
rm -f target/run_results.json
dbt test \
  --select "tag:completeness_gate tag:zero_gate" \
  --vars "{\"gate_batch_id\":\"${BATCH_ID}\",\"manifest_revision\":\"${REVISION}\"}"
coverage_rc=$?

if [[ -f target/run_results.json ]]; then
  cp target/run_results.json "${EVIDENCE}/completeness/run_results.json"
else
  coverage_rc=72
fi
printf '%s\n' "$coverage_rc" > "${EVIDENCE}/completeness/exit-code.txt"

printf '%s\n' "$BATCH_ID" > "${EVIDENCE}/batch-id.txt"
printf '%s\n' "$REVISION" > "${EVIDENCE}/manifest-revision.txt"
printf '%s\n' "$GATE_RUN_ID" > "${EVIDENCE}/gate-run-id.txt"

exit $(( fresh_rc != 0 || manifest_rc != 0 || coverage_rc != 0 ))

For the deliberately incomplete core fixture, the final command is expected to be nonzero because the completeness test should fail on tenant_c. Therefore do not run the publication build after this fixture unless you first perform the recovery described below.

A successful wrapper can call the consuming model only after an artifact verifier approves all evidence:

-- models/published_batch.sql
{{ config(materialized='table', tags=['publication']) }}

select
    tenant_id,
    business_date,
    batch_id,
    record_id,
    loaded_at
from {{ source('acceptance_lab', 'source_events') }}
where batch_id = '{{ var("gate_batch_id") }}'

Then:

dbt build \
  --select published_batch \
  --vars '{"gate_batch_id":"2026-09-23","manifest_revision":"r1"}'

Using a table rather than a live view makes the lab’s accepted output a fixed result at publication time, but it does not remove the time-of-check/time-of-use problem by itself. The landing batch must remain immutable or isolated from the beginning of the gate until that table is built. Otherwise rows can change between validation and consumption.

The evidence bundle should contain at least:

The current sources.json documentation identifies the freshness measurement fields; run artifacts separately identify executed resources. Because freshness and completeness are separate dbt commands, they will normally have separate dbt invocation IDs. The enclosing gate_run_id is what binds those sub-invocations into one release decision.

This is where version control for data engineering work becomes operational evidence rather than a development convention. Capture the SQL revision:

git rev-parse HEAD > "${EVIDENCE}/sql-revision.txt"

Also hash the completed bundle:

find "${EVIDENCE}" -type f -print0 \
  | sort -z \
  | xargs -0 sha256sum \
  > "${EVIDENCE}/SHA256SUMS"

A verifier should be fail-closed about missing fields. A minimal Python implementation can require one exact source and the named acceptance tests:

#!/usr/bin/env python3
import json
import sys
from pathlib import Path

root = Path(sys.argv[1])

def read_json(path: Path):
    if not path.exists():
        raise RuntimeError(f"missing artifact: {path}")
    with path.open(encoding="utf-8") as f:
        return json.load(f)

sources = read_json(root / "freshness" / "sources.json")
manifest_run = read_json(root / "manifest" / "run_results.json")
coverage_run = read_json(root / "completeness" / "run_results.json")

source_id = "source.partition_gate.acceptance_lab.source_events"
source_results = [
    r for r in sources.get("results", [])
    if r.get("unique_id") == source_id
]

if len(source_results) != 1:
    raise RuntimeError("freshness evidence is absent or ambiguous")

fresh = source_results[0]
for field in ("max_loaded_at", "snapshotted_at", "criteria", "status"):
    if fresh.get(field) is None:
        raise RuntimeError(f"freshness field is unknown: {field}")

def result_for_suffix(doc, suffix):
    matches = [
        r for r in doc.get("results", [])
        if str(r.get("unique_id", "")).endswith("." + suffix)
    ]
    if len(matches) != 1:
        raise RuntimeError(f"missing or ambiguous test result: {suffix}")
    return matches[0]

manifest = result_for_suffix(
    manifest_run, "assert_manifest_valid_and_current"
)
coverage = result_for_suffix(
    coverage_run, "assert_expected_partition_coverage"
)
zero = result_for_suffix(
    coverage_run, "assert_zero_partition_completion"
)

statuses = {
    "freshness": fresh.get("status", "unknown"),
    "manifest": manifest.get("status", "unknown"),
    "coverage": coverage.get("status", "unknown"),
    "zero": zero.get("status", "unknown"),
}

if statuses["manifest"] != "pass":
    decision = "REJECT_MANIFEST"
elif statuses["freshness"] != "pass":
    decision = "HOLD"
elif statuses["coverage"] != "pass" or statuses["zero"] != "pass":
    decision = "RECOVER"
else:
    decision = "PUBLISH"

print(json.dumps({"statuses": statuses, "decision": decision}, indent=2))

if decision != "PUBLISH":
    sys.exit(20)

In production, extend that verifier to compare archived batch/revision values, resource-selection files, invocation metadata, timestamps and hashes. The important property is fail-closed semantics: a missing artifact produces unknown/nonpublication, never an inferred pass.

Freshness and completeness must remain separate verdicts. fresh + incomplete, stale + complete, fresh + complete, and unknown evidence are materially different operational states.

Recover precisely and make the release decision

The core fixture produces the most instructive state: fresh but incomplete.

The correct immediate action is HOLD, followed by a targeted RECOVER request for:

batch_id      = 2026-09-23
tenant_id     = tenant_c
business_date = 2026-09-22
expected rows = 2
manifest rev  = r1

Do not reload tenants A and B merely because the aggregate batch failed. Their evidence already reconciles. Ask the producer for the missing delivery unit and preserve its source provenance.

A controlled recovery should follow this sequence:

1. Keep the candidate batch unpublished.

2. Preserve the failed gate bundle.

3. Request the exact missing tenant/date partition from the producer owner.

4. Load the correction into the controlled batch landing area.

5. Do not alter expected_partitions merely to make the failure disappear.

6. Do not rewrite loaded_at to an artificially recent value solely to force freshness green.

7. Re-run source freshness.

8. Re-run manifest, coverage and zero-completion gates against the unchanged authorized revision.

9. Archive a new gate run; never overwrite the failed evidence.

10. Publish only from the newly approved run.

If loaded_at represents a source-provided or ingestion event timestamp, preserve its agreed semantics. A late recovery may make the batch complete yet stale. That is a legitimate outcome: completeness can become PASS while freshness remains WARN or ERROR. The correct response is to hold unless the consumer contract explicitly permits stale fallback.

The decision table should be explicit:

A legitimate expected-zero partition does not enter recovery merely because it contributes no facts. It is publishable when three statements all agree: the manifest says zero is allowed, the observed count is zero, and the producer independently records completion with zero declared rows.

An invalid manifest is different from a missing delivery. If the producer sent revision r0 while the gate expects authorized revision r1, changing fact data cannot fix the evidentiary problem. The outcome is REJECT_MANIFEST, with publication held until the expectation source is corrected or explicitly reauthorized.

A last-known-complete dataset may be retained as a rollback/fallback target, but only when the consumer contract permits that behavior. Its age must remain visible. An earlier complete batch cannot be represented as the current batch merely because the current delivery failed acceptance.

The same rule applies to gate-code rollback. Suppose a new test revision contains a defect. Operators may roll the gate implementation back to a previously approved revision and rerun acceptance. They may not infer from “test code is broken” that the candidate batch is acceptable. A code rollback restores a trusted test; it does not supply missing delivery evidence.

Version the gate implementation and manifest expectations together in the evidence receipt:

{
  "batch_id": "2026-09-23",
  "manifest_revision": "r1",
  "gate_sql_revision": "<git-commit>",
  "freshness_verdict": "<observed>",
  "completeness_verdict": "<observed>",
  "final_decision": "<observed>"
}

Placeholders above are intentional. The publication process must populate them from the actual run rather than copy fabricated example statuses.

For the unmodified synthetic fixture in this article, the predicted matrix is:

That is the “dbt freshness false green” in precise terms: freshness is green for the recency question it was configured to answer, while an independent completeness contract blocks release for a different reason.

Assign ownership and retain the acceptance artifact

The final reliability control is organizational: every evidence field needs an owner capable of correcting it.

The producer owner certifies the expected delivery set, manifest revision, expected counts and completion of legitimate zero deliveries. A data-platform team should reject a manifest it knows is stale or unauthorized rather than quietly editing it to match landed facts.

The data engineer or ingestion operator owns the isolated landing path, version capture, input freeze, command execution, recovery load and artifact preservation. That operator should be able to show which exact source, tests, batch and SQL revision were executed.

The analytics or data-product owner owns the consumer publication decision. That role decides whether fresh and complete evidence satisfies the consumer contract and, where explicitly allowed, whether an older complete fallback is acceptable.

A reliability/platform owner should control the gate implementation itself: fail-closed artifact reconciliation, version upgrades, selection validation and exception expiry.

Revalidation is warranted whenever the assumptions behind the contract change: a tenant is onboarded or removed, the logical partition grain changes, zero-delivery rules change, the source’s loaded_at semantics change, or dbt Core/adapter versions are upgraded. Those are not automatically defects; they are contract changes that can invalidate the old acceptance proof.

Exceptions should be time-bounded and evidenced. “Publish despite missing C until source owner responds” is not a stable policy unless a named consumer owner has explicitly accepted that degradation, specified an expiry and defined how downstream consumers will distinguish incomplete data. The normal state remains hold.

Evidence retention should follow the organization’s own retention and security policy. The minimum useful artifact is not a screenshot of a green job. It is a versioned bundle that ties batch identity, expected-manifest revision, selected resources, actual dbt artifacts, runtime versions, SQL revision and the final decision together. That complements broader operational observability and evidence without requiring an anomaly platform or vendor-specific monitoring layer.

The boundary remains important. This deterministic gate can say:

“For authorized manifest revision r1, every declared tenant/date partition was reconciled to its expected count, every declared empty partition had an independent completion acknowledgment, the configured freshness check had an acceptable result, and the evidence belongs to this gate run.”

It cannot honestly say:

“No record that should exist anywhere is missing.”

That stronger claim would require an authoritative expectation for those records. A manifest can itself be wrong, incomplete or stale. Treating its provenance as part of the gate is more reliable than marketing row-count reconciliation as universal completeness.

For engineers building the underlying pipeline, transformation and governance skills, Refonte Learning’s Data Engineering programme currently describes a three-month, 12–14-hour-per-week route covering areas including pipeline work, batch and streaming processing, transformations, storage, security and governance, with practical project and guidance elements; those published foundations should not be interpreted as a promise that this specific dbt acceptance lab is part of the curriculum.

The operational artifact worth retaining is simpler than a dashboard: a versioned expected-delivery manifest joined to an immutable evidence bundle and an explicit batch decision. Freshness tells you whether recent data arrived. The independent manifest tells you whether the deliveries you promised to wait for actually did. Publication should require both.