Deduplicate Source Data

How can we deduplicate Bronze raw ingestion records retaining the latest update per order?

Advanced⏱ 3 Minutes⭐ 1920 XP

🤔 THE QUESTION

How can we deduplicate Bronze raw ingestion records retaining the latest update per order?

💡 WHAT IS IT?

Partitioning by order_id and sorting by updated_at DESC isolates the latest clean record in bronze-to-silver staging.

🎯 WHAT IS IT USED FOR?

Bronze to Silver Lakehouse ELT, removing duplicate streaming deliveries, idempotent ingestion.

💻 EXAMPLE

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM source_orders
) cleaned
WHERE rn = 1;

🎯 Mission Objectives

Practice typing production-grade SQL code for Deduplicate Source Data.

✓ Bronze-to-Silver deduplication
✓ Idempotent pipeline staging
✓ ROW_NUMBER partition
✓ Lakehouse curation

🚀 Start Mission

← Prev (Incremental Filter)Next Lesson (Data Quality Validation) →