Count everything twice: the UNION that would have double counted a travel warehouse

A master table holds the travel reservations. Four smaller tables hold direct supplier bookings. The requirement was to fold the four into the same fact tables, and the obvious way is four more staging views and a UNION ALL under the master rows. It builds, and every test in the project passes.

It is also wrong. About 90% of the rows in the four channel tables describe bookings already in the master table. Added naively, they are counted twice, in a financial mart. This post covers how we found that before shipping, why the primary-key test cannot see it, the dedup union we built, the count that checked it, and the test that should exist and, in the project today, does not yet exist.

This is from a data platform we built for a travel and fintech group: an ERP and a booking platform loaded into Postgres, modelled in dbt, and read by an AI analyst. Everything below ran on a staging environment. We have left out business names and every money figure, so the evidence is row counts and ratios.

Constraints

The union sat inside a larger set of constraints, and each one made the double count more expensive.

Constraint	Consequence
The marts feed revenue and booking-count metrics	An extra row is an extra sale. There is no “close enough” for a duplicate
An AI analyst reads the marts directly	Nobody necessarily reviews the figure before it is quoted. A wrong total reads as a confident answer
One person operates the platform	Checks have to be mechanical and run on every build, not depend on someone remembering to look
Grain is one row per booking	Anything that adds a second row for the same booking breaks every count and sum
The four channel tables are narrow and differently shaped	Each branch of the union needs explicit padding, and the branches are easy to get subtly wrong
The warehouse is Postgres, under 1 GB	We can count anything, any time. The cost of checking is minutes, not infrastructure

The data we had

The master table is the main reservation system’s record of every booking. It has about five thousand rows and 83 columns, one row per reservation. A booking_type column splits it cleanly into flights (about 82% of rows) and hotels (about 18%), with no third value. A separate column distinguishes a booking from a refund.

Next to it sit four direct supplier channel tables: three flight suppliers and one hotel supplier. They are much narrower than the master table. Each stores what that supplier knows about a booking, in its own shape, and each carries a column that can point at a master reservation id. We will call that column master_id.

Staging follows a strict rule: rename and cast, nothing else. The master table became one staging view and each channel table one more. The flight and hotel facts each filter the master view by booking_type.

The symptom we did not get

Nothing failed here. The naive union builds, the facts fill, the dashboards draw, and the totals are too large by an amount nobody can see without already knowing the answer. What stopped us was a counting query run before the union shipped. For each channel table we asked how many rows had a master_id that resolved to a row in the master table, and how many did not.

Channel	Linked to a master row	Direct-only
Flights, channel A	82%	18%
Flights, channel B	95%	5%
Flights, channel C	95%	5%
Hotels, channel D	81%	19%

Across all four tables, 92% of channel rows are linked, and per channel the share runs from 81% to 95%. Channel rows are roughly 10% of the size of the master table. We quote it as “about 90%” in the title and summary, and 92% is the exact aggregate.

The channel tables are mostly supplier-side detail for bookings that also flowed through the master system. For roughly nine in ten channel rows, the master table already has the sale.

A naive UNION ALL would have added about 11% more flight rows and about 2% more hotel rows, 9% too many overall. These are row counts. We did not measure the revenue effect, because a linked channel row may carry a different amount from its master row, but each extra row would have added its own gross amount to a revenue sum.

The wrong first guess

The first guess was a design, not a theory about a bug: stack the sources, tag each row with where it came from, and let the tests tell us if anything is off. This is the standard way to combine sources, and on the earlier marts in this project, each with one source and one grain, it was right. Two assumptions made the design look safe.

First assumption: the channel tables are separate sales. A direct supplier booking sounds like one that did not go through the master system. For about 8% of rows it is. For the rest, it is a second description of a sale the master table already has.

Second assumption: unique on the primary key is a duplicate detector. It is the first test most people add to a fact table, and it is a real guard against one kind of duplicate: two rows with the same key. It says nothing about two rows with different keys that describe the same booking. That is exactly what overlap between sources produces. The master row has its id. The channel row has its own id from its own table. Nothing collides.

A plain UNION in place of UNION ALL would not help either. It removes rows identical across every column, and a master row and its channel twin are not, because the channel row is narrower and padded with NULLs. This is reasoning from the SQL, not something we ran.

Root cause

The fact claims to be a list of bookings. The channel tables are lists of supplier records, and a supplier record is either a booking nobody else knows about or a second record of one the master system has.

A union combines rows and does not know that two rows refer to the same real-world thing. That knowledge lives in the master_id column, and only a join or a NOT EXISTS against the master table can use it.

The failure also hides behind its own test. The mart’s key is source_channel || '-' || source_booking_id, so a booking that appears once as a master row and once as a channel row gets two keys that differ in their prefix. A unique test on that key passes in the naive version and the correct version alike. A test that cannot tell the correct build from the broken one is not testing the thing you care about.

The fix: a dedup union

Each channel branch of the union keeps only rows whose master_id does not resolve to a row in the master table. The rows that survive are the genuinely direct bookings. (One branch, abridged. Table, column and channel names are generalised.)

from {{ ref('stg_channel_a') }} a
left join {{ ref('stg_currency') }} cur on cur.currency_id = a.currency_id
left join {{ ref('int_fx_to_usd') }} fx
       on fx.company_id  = a.company_id
      and fx.currency_id = a.currency_id
      and coalesce(a.sale_date, a.created_at_utc::date) >= fx.valid_from
      and coalesce(a.sale_date, a.created_at_utc::date) <  fx.valid_to
where not exists (
    select 1 from {{ ref('stg_master_reservations') }} t
    where t.master_booking_id = a.master_id
)

NOT EXISTS is safer than NOT IN here. NOT IN (subquery) returns no rows at all if the subquery produces a single NULL. And a channel row with a NULL master_id has no link by definition, so it should be kept: under NOT EXISTS the correlated condition matches nothing, so the row survives as a direct booking.

The branches are then stacked (abridged; the real branch names differ):

unioned as (
    select * from master
    union all select * from channel_a
    union all select * from channel_b
    union all select * from channel_c
)

select
    source_channel || '-' || source_booking_id as booking_key,
    *
from unioned

The hotel fact has the same shape with one channel branch. Every row carries a source_channel value, so channel-level analysis survives the union.

The net effect is that fewer than 1% of the combined fact rows are direct-only channel rows. Every flight and hotel fact row from the master table is kept, and only the direct-only channel rows are added.

The unglamorous part: aligning columns

Most of the union SQL is column alignment. The channel tables are far narrower than the master table’s 83 columns, so each channel branch pads the gaps with typed NULLs such as null::integer as customer_id and null::date as departure_date. Each one is a recorded decision that the channel lacks that attribute. Semantic differences are resolved at the same place: one hotel channel records only a net amount, so the fact uses it as both gross and net, with a comment saying so; one flight channel has no refund distinction, so its transaction type is the constant 'booking'.

Channel rows have a NULL customer_id, so the relationships test on that column still passes, since dbt ignores NULLs there. The column description tells the analyst agent the value is NULL for channel rows.

Two things the union broke

The primary key. The master id means nothing for channel rows, and ids from different channel tables can collide. The fact mints a surrogate key from the channel and the source id, and the unique and not_null tests moved onto it.

The type check. The first deploy failed in dbt run with a message that bigint and character varying cannot be matched. The master table stores its reservation number as an integer, and the channel tables store supplier reference strings. Postgres will not guess a common type across branches. The fix was a cast at the edge of the master branch, so every branch agrees on text. We now audit column types across every branch of a union before shipping, with an information_schema.columns query joined by ordinal position. A build error is the good outcome. With implicitly castable types, the same mismatch is a silent change of meaning.

Verification: never trust a dedup you have not counted

One flipped NOT or one join on the wrong id column, and the fact is confidently incorrect while every test stays green. So the build was not done until the arithmetic matched the overlap analysis.

• Flight fact: expected master flight rows plus the direct-only rows of each channel. Built: exact match, with per-channel counts matching.
• Hotel fact: expected master hotel rows plus the direct-only hotel channel rows. Built: exact match.
• booking_key unique across both facts, by the dbt test and by a manual GROUP BY ... HAVING count(*) > 1. Zero duplicates.

They matched on the first build. The general rule we took is that any transformation which drops or merges rows must ship with an independent expected count, derived from the source before the transformation was written. If you compute the expectation from the mart, you have checked your code against itself.

The test that catches it

Today the flight and hotel facts carry unique and not_null on booking_key, plus the relationships test on customer_id. The singular tests in the project cover the exchange-rate model. No test targets the overlap. The verification above was a one-time count.

It is tempting to assume the nightly unique test would flag a broken overlap assumption, and an earlier internal write-up of this work did say so. Having read the model, we do not think it would, because the key is prefixed by channel: delete the NOT EXISTS and the key test stays green. A toy reproduction of the same shape (a naive union and a dedup) passes the key test in both cases, and the overlap test below returns rows only on the naive build. It is cheap to confirm on the real model: break it on purpose and watch the test fail.

What would catch it is a test about the relationship between sources, not about keys. A dbt singular test returns the rows that violate a rule, and the test fails if it returns any. The rule is that no row the fact took from a channel may resolve to a master row.

-- tests/assert_channel_rows_not_in_master.sql
-- Fails if any channel row in the flights fact also exists in the master table.
select
    m.booking_key,
    c.master_id
from {{ ref('fct_flight_bookings') }} m
join {{ ref('stg_channel_a') }} c
  on m.source_channel     = 'channel_a'
 and m.source_booking_id  = c.channel_a_booking_id::text
join {{ ref('stg_master_reservations') }} t
  on t.master_booking_id  = c.master_id

union all

select m.booking_key, c.master_id
from {{ ref('fct_flight_bookings') }} m
join {{ ref('stg_channel_b') }} c
  on m.source_channel     = 'channel_b'
 and m.source_booking_id  = c.channel_b_booking_id::text
join {{ ref('stg_master_reservations') }} t
  on t.master_booking_id  = c.master_id
-- repeat for channel C, and in a second test for the hotel fact

Three properties make this the right shape. It joins the fact to the staging layer, because the fact has already nulled out the master id on channel rows. It returns the offending ids, so a failure names the double-counted bookings. And it asserts an invariant, not a number: a hard-coded row count would fail the first time the data grew.

We have not run this test against the real model, and it is not in the project today. We ran it on a toy reproduction of the same shape: it returns rows on the naive union and none on the dedup. Like the count, it uses the same link column as the model, so it proves the model does what it says, not that the links are right.

A cheaper companion is a row-count test against the sum of the staging views, with the channel side written as an anti-join rather than NOT EXISTS. Two formulations of one rule that disagree make a useful alarm, though it is a smoke test, since it shares the rule with the model.

Excluded on purpose: the detail tables

The source also has per-segment detail tables. The master flight segment table holds roughly two rows per booking. Unioning them into the bookings fact would shift the grain from one row per booking to one row per sometimes-booking-sometimes-segment, and every count and sum would drift. They are not loaded at all, under a rule from the load stage: no consumer, no load. Passenger-level analysis would get its own fact at its own grain.

Where it fails

The dedup union is better than the naive one. It is not a proof, and these are the places we know it can be wrong.

It trusts the link column. A channel row is dropped only if its master_id resolves. If a supplier row describes a master booking but carries no id, or the wrong one, it survives as a direct booking and is counted twice. The 8% direct-only share is therefore an upper bound on genuinely independent bookings, and we did not verify that the surviving channel rows are all real. We checked that the arithmetic matches the link analysis, not that the links are complete.

A dangling id looks like a missing id. A channel row whose master_id points at nothing is kept as direct-only. That may be correct, or the master row may never have loaded.

It does not compare amounts. We dropped linked channel rows without checking whether their amounts agreed with the master row, so a price disagreement is now invisible. We counted rows, not money.

It loses supplier-side detail. The master branch sets route and passenger name to NULL, while some channels carry them. For linked bookings that detail never reaches the fact.

It compares against the whole master table. The NOT EXISTS checks every master reservation, flights and hotels alike. A flight channel row whose link resolved to a hotel reservation would be dropped from the flights fact, and the hotel reservation would still be counted once in the hotel fact, so the channel-side detail is lost. We did not check whether any such row exists.

The test above has not run. Its first run may fail for reasons unrelated to duplicates, and the hotel version needs its own copy.

The count shares the link column. The expected numbers came from the column the model filters on, so if that column is wrong the count agrees and the mart is still off. A supplier statement for one month would test the links independently. We did not have one.

Deletes are not captured. The warehouse loads incrementally each day, and hard deletes at the source are not captured today. We have not checked whether that can leave a channel twin looking direct-only.

What we took from it

1. Count the overlap before shipping the union. Ten minutes of queries turned a design we were about to ship into a different one.
2. A uniqueness test is a test about keys. It cannot see two rows that describe the same thing under different keys. When a fact is built from several sources, test the relationship between them.
3. Ship an independent expected count with any row-dropping transform, derived from the source before the transformation exists.
4. Break the model on purpose. A test you have never seen fail is a guess.
5. Write down what you did not verify. We dropped rows on one column’s word. That is a reasonable bet, and a stated one.

The flight and hotel facts each count a booking once, as far as the link column can tell. A naive union would have produced about 9% more rows, and every dbt test we had would have passed.

---

CloudGuys builds systems like this for startups and teams. Tell us what you are building.