How do I deduplicate records in an ELT workflow?

Deduplicating records in an ELT workflow usually means deciding which row is the “winner” for each duplicate group, then enforcing that rule either:

1. before loading into the warehouse,
2. during transformation in the warehouse, or
3. continuously as part of downstream models/views.

Common patterns

1) Use a business key + tie-breaker

Identify a stable key that defines duplicates, for example:

• customer_id
• email
• order_id
• or a composite like (source_system, external_id)

Then choose a rule to pick the “best” record, such as:

• latest updated_at
• earliest created_at
• highest version
• non-null fields preferred
• source priority (crm over csv)

Example rule:

For each customer_id, keep the row with the latest updated_at; if tied, keep the one with the highest ingested_at.

---

2) Deduplicate with SQL window functions

A very common ELT approach is to use ROW_NUMBER():

with ranked as (
  select
    *,
    row_number() over (
      partition by customer_id
      order by updated_at desc, ingested_at desc
    ) as rn
  from raw_customers
)
select *
from ranked
where rn = 1;

This keeps one row per customer_id.

If you want to deduplicate on multiple columns:

partition by email, country

---

3) Incremental deduplication

If data arrives in batches, deduplicate only the new batch against the existing table:

• stage new data
• compare against target on business key
• upsert/merge
• handle late-arriving updates

Example idea:

merge into dim_customers t
using staged_customers s
on t.customer_id = s.customer_id
when matched and s.updated_at > t.updated_at then update set ...
when not matched then insert ...

This is often better than reprocessing the entire dataset every time.

---

4) Use MERGE / UPSERT semantics

In warehouses that support it, MERGE helps keep a deduplicated table.

You define:

• match condition = your unique key
• update logic = newest/better record wins
• insert logic = new key gets added

This is useful for dimensional tables or current-state tables.

---

5) Keep raw data and dedupe in a clean layer

A robust ELT design is:

• raw/landing layer: store everything, no dedupe
• staging layer: standardize types, trim, normalize
• deduped model layer: apply dedupe rules
• final marts: use only clean deduped tables

This preserves auditability and makes debugging easier.

---

Important considerations

Define duplicate logic clearly

Duplicates aren’t always exact matches. Decide whether duplicates mean:

• exact row equality
• same business key
• same key with conflicting attributes
• near-duplicates (fuzzy matching)

Handle nulls carefully

If key fields can be null, you may need rules like:

• exclude null keys from dedupe
• coalesce multiple fields into a composite key
• send ambiguous records to a quarantine table

Make the rule deterministic

If two records tie, the winner should still be predictable. Add a stable final sort column like:

• ingested_at
• source_file_name
• load_id

Audit what was removed

Often it’s good to keep:

• the winning record
• a dedupe log table
• counts of removed records
• a reason code

---

Practical ELT example

Suppose you load customer events into raw_customers, and want one row per customer:

create or replace table customers_deduped as
select *
from (
  select
    *,
    row_number() over (
      partition by customer_id
      order by updated_at desc, load_timestamp desc
    ) as rn
  from raw_customers
)
where rn = 1;

---

If you use dbt

In dbt, this is commonly implemented in a model using row_number() or a package like:

• dbt_utils.deduplicate
• custom incremental model with unique_key

---

Rule of thumb

• Raw layer: keep all records
• Staging: clean/standardize
• Transform: deduplicate with a deterministic rule
• Incremental: use MERGE/upsert when possible

If you want, I can show you:

• a generic SQL dedupe template
• a dbt incremental dedupe example
• or a warehouse-specific example for Snowflake/BigQuery/Redshift/Postgres.