One in Five

An easy AI Coding interview practice problem on DataDriven.

Domain
AI Coding
Difficulty
easy
Seniority
L4
Asked in
Stripe

Problem

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.

Green, green, red, green.

Worked solution and explanation

The flake lives in the ORDER BY

This is a non-deterministic dedup dressed up as a flaky CI test. The model's job is 'keep the latest row per natural key', and row_number() over (partition by provider, external_payment_id order by event_ts desc) looks like it does exactly that. The trap: event_ts is not unique inside a partition. Both (stripe, p_42) rows carry event_ts = '2026-04-28 10:05', so rn = 1 is a coin flip decided by whichever row the scan reads first. Locally the scan order is stable, so it always lands the same way and the test is green. CI shuffles scan order on partitioned sources, so ~1 run in 5 the other row wins. Nothing about the data or the seed changed. The winner did.

models/`fct_payments`.sql (fixed)
/* models/fct_payments.sql */
/* One row per (provider, external_payment_id): the latest by event_ts,
   ties broken deterministically so scan order can't change the winner. */

{{ 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, status 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

One extra column in the order by turns a coin flip into a total order.

Before
order by event_ts desc. On (stripe, p_42) the two rows tie, so the survivor's status flips between 'captured' and 'refunded' depending on scan order. The unique test on payment_id can even pass while the row you kept is wrong.
After
order by event_ts desc, status desc. event_ts still picks the latest; when it ties, status decides. Reverse the input rows and the same row wins every time, so ascending and descending scans agree.

Common follow-up questions