Number the versions of each customer from newest to oldest and keep number 1. Then drop the customers whose latest change is a delete.
Example data
customers_cdc has one row per change event.
customer_id op email updated_at op_seq 7 I a@x.com 2025-03-01 10:00 101 7 U b@x.com 2025-03-02 09:00 230 7 U c@x.com 2025-03-02 09:00 231 8 I d@x.com 2025-03-01 12:00 102 8 D d@x.com 2025-03-03 08:00 300
Query
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC, op_seq DESC
) AS rn
FROM customers_cdc
)
SELECT customer_id, email, updated_at
FROM ranked
WHERE rn = 1 AND op <> 'D';Result: customer 7 with c@x.com. Customer 8 is gone because the latest event is a delete.
The order of steps matters. Filter op <> 'D' after picking the latest row. If you removed deletes first, customer 8's old insert would become the newest remaining row and the customer would wrongly come back.
The tie-breaker
Customer 7 has two updates with the same timestamp. Ordering on updated_at alone leaves the choice to chance, and different runs may pick different rows. Add a column that is strictly increasing per source, such as a log sequence number (op_seq). Use whatever monotonic field your CDC tool provides.
QUALIFY form
SELECT customer_id, email, updated_at FROM customers_cdc QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, op_seq DESC) = 1 AND op <> 'D';
On engines that support it, this is shorter.
Why not MAX plus a join
MAX(updated_at) per customer, joined back to the table, returns both rows for customer 7 when timestamps tie. You get duplicates and have to patch it. ROW_NUMBER always returns one row.