COALESCE doesn't care what your columns mean

coalesce(a, b) is a type check, not a meaning check.

COALESCE is one of the first functions anyone learns in SQL. It returns the first non-null argument. It’s cheap, portable, and it looks harmless.

The problem is that the database only checks that the arguments can be cast to a common type. It never checks that they mean the same thing. If you coalesce two columns with different semantics, the query runs, the tests pass, and the output is wrong.

This post shows the pattern with a synthetic example, the two failure modes that usually come with it, and the guardrails I now use.

The setup

Two sources describe the same customer:

• Source A ships loyalty_tier_score: the fraction of the year the customer spent in the premium tier. Values like 0.4167 or 1.0000.
• Source B ships loyalty_tier_code: a categorical code. 'G' = gold, 'S' = silver, 'N' = none.

Both are “loyalty tier”. Only one of them is a code.

-- synthetic data
with source_a as (
    select 'c1' as customer_id, cast(1.0000 as numeric(10,4)) as loyalty_tier_score
    union all
    select 'c2', cast(0.4167 as numeric(10,4))
    union all
    select 'c3', cast(null as numeric(10,4))
)

, source_b as (
    select 'c1' as customer_id, 'G' as loyalty_tier_code
    union all
    select 'c2', 'S'
    union all
    select 'c3', 'G'
)

select
      a.customer_id
    , cast(coalesce(a.loyalty_tier_score, b.loyalty_tier_code) as varchar) as tier_code
from source_a as a
inner join source_b as b
    on a.customer_id = b.customer_id

Depending on the engine, the coalesce may need an explicit cast to compile. Once it’s there, nothing complains. Two of three rows now carry a number in a column that downstream code reads as a code.

Failure mode 1: the downstream else

Somewhere downstream, someone maps the code:

select
      customer_id
    , case
        when tier_code = 'G' then 'gold'
        when tier_code = 'S' then 'silver'
        else 'none'
      end as tier
from tiers

1.0000 and 0.4167 never match, so both customers become none. Customer c1 was gold all year.

The else branch is doing two jobs at once: it handles the legitimate “none” value and anything unexpected. Wrong input becomes a valid-looking output.

Failure mode 2: “take the latest row”

The second pattern usually shows up next to the first one. You have monthly snapshots and you want one row per span, so you pick the latest month:

row_number() over (
    partition by customer_id, span_id
    order by snapshot_month desc
) = 1

The latest row wins, the code is null, and the downstream else turns it into none. The source knew the answer two months earlier. We threw it away.

Missing data by itself is fine. Missing data plus a silent default is what produces wrong data.

Failure mode 3: the default flag that only watches NULL

A common mitigation is a flag that says “we applied a default here”:

case when tier_code is null then 1 else 0 end as tier_defaulted

That catches failure mode 2. It does not catch failure mode 1: 1.0000 isn’t null, so the row is classified as none and the flag stays at 0. That’s the worst combination: wrong and marked as trustworthy.

Guardrails

1. Coalesce only columns with the same domain

If two columns don’t share a value set, they don’t go in the same coalesce. Pick the authoritative source for that attribute and keep the other as a separate column:

select
      customer_id
    , b.loyalty_tier_code      as tier_code            -- categorical, authoritative
    , a.loyalty_tier_score     as tier_premium_fraction -- numeric, kept as its own column
from ...

2. Latest non-null, not latest row

Pick the latest row for the span, but take each attribute from its latest non-null value:

-- Snowflake / Redshift / Databricks syntax
last_value(tier_code) ignore nulls over (
    partition by customer_id, span_id
    order by snapshot_month
    rows between unbounded preceding and unbounded following
) as tier_code

BigQuery uses last_value(tier_code ignore nulls) over (...). If you need something portable across adapters, rank only the non-null rows in a separate CTE and join back.

3. Validate the value set, not only nullness

In dbt, an accepted_values test on the column would have failed on 1.0000 immediately:

models:
  - name: customer_tiers
    columns:
      - name: tier_code
        data_tests:
          - accepted_values:
              arguments:
                values: ['G', 'S', 'N']

If the value set lives in a seed, use a relationships test against it instead of hardcoding the list.

4. Make the default flag check validity

case
    when tier_code is null then 1
    when tier_code not in ('G', 'S', 'N') then 1
    else 0
end as tier_defaulted

5. Prefer unknown over a business default

If the model downstream must have a value, fine, but keep the distinction visible. unknown is honest. none is a claim.

Takeaway

Data lineage is usually drawn as boxes and arrows: this column comes from that table. That’s technical lineage. What bit me here is semantic lineage: does the value mean the same thing at every hop?

COALESCE is where the two most often diverge, because it’s the one place where SQL lets you merge two columns without asking what they are.

$ grep -rn "coalesce(" models/ | wc -l

Worth reviewing that list once.

References

• dbt docs — Data tests (accepted_values, relationships): https://docs.getdbt.com/docs/build/data-tests
• dbt docs — accepted_values reference: https://docs.getdbt.com/reference/resource-properties/data-tests
• Snowflake docs — LAST_VALUE (IGNORE NULLS): https://docs.snowflake.com/en/sql-reference/functions/last_value
• BigQuery docs — Navigation functions (LAST_VALUE): https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions