1. Direct Confirmation & Core Rule In the Medallion Architecture, data deduplication and entity resolution should always be executed in the transition from Bronze to Silver using atomic `MERGE INTO` or windowed deduplication.

2. Why This Answer Is Correct Bronze tables must remain immutable, append-only historical records of raw source ingestion. Deduplicating raw files before or inside Bronze violates auditability. In the Silver layer, incoming raw transactions are deduplicated against existing target records based on natural business keys (e.g. `store_id` + `transaction_id`) using Delta Lake's atomic `MERGE INTO` statement, ensuring duplicate events are discarded or updated idempotently.

3. Why Other Options Are Incorrect - **A. Reject raw files immediately in the Bronze layer**: Bronze is an append-only raw archive; dropping files before Bronze destroys the raw audit trail. - **B. Deduplicate during final BI dashboard rendering**: Pushing deduplication to reporting queries imposes heavy computational costs on every BI query and invites inconsistent metrics across tools. - **D. Delete historical records using VACUUM**: VACUUM deletes unreferenced storage files older than the retention threshold; it does not deduplicate table rows.

4. Architectural & Execution Context Silver layer deduplication is typically implemented via: 1. Micro-batch windowing: `QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY ingest_timestamp DESC) = 1` 2. Delta upsert: `MERGE INTO silver_sales USING deduplicated_batch ON silver_sales.id = deduplicated_batch.id ...`

5. Practical Code Example

-- Deduplicating incoming Bronze batch before merging into Silver
WITH deduped_incoming AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id, receipt_id ORDER BY ingest_time DESC) as rn
  FROM bronze_sales
  WHERE ingest_date = CURRENT_DATE()
)
MERGE INTO silver_sales AS target
USING (SELECT * FROM deduped_incoming WHERE rn = 1) AS source
ON target.store_id = source.store_id AND target.receipt_id = source.receipt_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

6. Real-World Engineering Scenario A POS terminal in a remote store lost connectivity, queueing 5,000 transactions and transmitting them three times when the link restored. Because the Silver ingestion job executes an idempotent `MERGE INTO`, exactly 5,000 records were written into `silver_sales`, preventing $1.2M in phantom duplicate revenue.

7. Common Exam Trap & Misconception Remember: **Bronze stores raw duplicates** for full auditability; **Silver cleans and deduplicates** for the enterprise single source of truth.

8. Best Practice & Production Recommendation Always define primary and natural keys in the Silver layer and enforce deduplication logic in scheduled pipelines or Delta Live Tables using `APPLY CHANGES INTO`.

9. Key Concept to Remember Deduplication belongs in the Silver layer, preserving Bronze as an immutable raw source of truth.

10. Related InsightfulSaga Tutorial 👉 [Bronze / Silver / Gold Layers — Lakehouse Medallion Model](/tutorials/databricks/lakehouse-medallion-model)