How to Remove Duplicates in ELT: Step by Step

After loading raw data into the staging layer, it is common to find duplicate records: reprocessed files, repeated ingestion attempts, or duplicates coming from the source itself. These duplicates inflate metrics (one sale counted twice), break joins, and can duplicate rows in Power BI reports, so it pays to handle them early. Below is a step-by-step guide on how to remove duplicates in ELT in the transformation layer, using standard SQL that works in SQL Server, Snowflake, BigQuery, Databricks, and Microsoft Fabric.

Prerequisites

• A staging table already loaded — the "L" (Load) of ELT — for example staging.customers.
• Permissions to run SQL and create tables or views in your data warehouse.
• Knowing the business key that identifies a unique record (e.g., customer_id).
• A date/time column to break ties (e.g., updated_at).

Step 1: Confirm that duplicates exist

Before deleting anything, confirm the problem. Group by the business key and count the rows per key: if any key has more than one row, you have duplicates to handle. If the table is very large, run this check first to size the problem before rewriting any data.

SELECT customer_id, COUNT(*) AS n_rows
FROM staging.customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n_rows DESC;

Step 2: Define the key and the tie-breaker

To decide which of the repeated rows to keep, you need a rule. The most common one is to keep the most recent record. So define two things: the business key (what makes a record unique) and the column that orders from newest to oldest, usually an update timestamp such as updated_at.

Note: SELECT DISTINCT only removes rows that are identical in every column. It does not solve business duplicates, where the same key appears with different values. That is why we use ROW_NUMBER().

Step 3: Number the rows with ROW_NUMBER()

The ROW_NUMBER() window function assigns a sequential number to each row within a group. PARTITION BY defines the group (the business key) and ORDER BY defines who comes first. The row we want to keep always gets number 1.

Unlike GROUP BY, which collapses rows and forces you to aggregate every column, a window function keeps all the original columns intact. That is why ROW_NUMBER() is ideal for deduplication: it picks one whole row per key without losing information.

WITH ranked AS (
  SELECT
    customer_id, name, email, updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM staging.customers
)
SELECT customer_id, name, email, updated_at
FROM ranked
WHERE rn = 1;

Step 4: Materialize the deduplicated table

Now save the result into a clean table, ready for the next layers. Use the CREATE TABLE ... AS SELECT pattern (CTAS) and list the final columns so you do not carry over the helper column rn.

CREATE TABLE clean.customers AS
WITH ranked AS (
  SELECT
    customer_id, name, email, updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM staging.customers
)
SELECT customer_id, name, email, updated_at
FROM ranked
WHERE rn = 1;

Some warehouses — Snowflake, BigQuery, and Databricks — offer the QUALIFY clause, which filters on the window function without needing the CTE and makes the code shorter:

SELECT customer_id, name, email, updated_at
FROM staging.customers
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY updated_at DESC
) = 1;

Verify the result

Confirm that the final table has no duplicates by repeating the count from Step 1, now against the clean table: the result should be empty. Also compare the total number of rows before and after — the difference matches exactly the duplicates you removed.

-- Should return no rows
SELECT customer_id, COUNT(*)
FROM clean.customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

Conclusion

With a window function and a clear tie-breaking rule, deduplication becomes a reliable, repeatable step in your ELT transformation. The next step is to plug this query into your orchestrator — for example Azure Data Factory or dbt — so it runs on every load and keeps your silver and gold layers clean. One question to take with you: in your case, what should the tie-breaker be — the most recent date, or a source-priority rule?