dbt Debug: One in Five

Analytics ships fct_payments through dbt nightly. The unique test on payment_id passes locally every time, fails in CI ~20% of runs. Same input fixtures, same model SQL. Run tests/test_uniqueness.py to reproduce, then fix the model so the test is deterministic. The fix is in the SQL, not the test.

models/fct_payments.sql

/* models/fct_payments.sql */
/* One row per (provider, external_payment_id). Some providers retry the
   same external id with a different status; we keep the latest by event_ts. */

{{ config(materialized='table', unique_key='payment_id') }}

with ranked as (
    select
        provider,
        external_payment_id,
        amount_cents,
        status,
        event_ts,
        row_number() over (
            partition by provider, external_payment_id
            order by event_ts desc
        ) as rn
    from {{ ref('stg_payment_events') }}
)
select
    {{ dbt_utils.generate_surrogate_key(['provider', 'external_payment_id']) }} as payment_id,
    provider,
    external_payment_id,
    amount_cents,
    status,
    event_ts
from ranked
where rn = 1

/* models/fct_payments.sql */
/* One row per (provider, external_payment_id). Some providers retry the
   same external id with a different status; we keep the latest by event_ts. */

{{ config(materialized='table', unique_key='payment_id') }}

with ranked as (
    select
        provider,
        external_payment_id,
        amount_cents,
        status,
        event_ts,
        row_number() over (
            partition by provider, external_payment_id
            order by event_ts desc
        ) as rn
    from {{ ref('stg_payment_events') }}
)
select
    {{ dbt_utils.generate_surrogate_key(['provider', 'external_payment_id']) }} as payment_id,
    provider,
    external_payment_id,
    amount_cents,
    status,
    event_ts
from ranked
where rn = 1

Active Now|Data Engineer (L4)|||5.7k Attempts|1.9k Solves|