Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Approach: Use ROW_NUMBER() OVER (PARTITION BY dedup_keys ORDER BY tie_breaker) to define which row to keep; filter rn = 1. Preferred pattern: CREATE OR REPLACE TABLE ... AS SELECT * EXCEPT(rn) FROM (SELECT *, ROW_NUMBER() OVER (...) AS rn ...) WHERE rn = 1. Why CREATE OR REPLACE...
Red Flag: Suggesting DELETE for large tables without acknowledging BigQuery's storage model. Pro-Move: 'We use CREATE OR REPLACE with partition expiry; dedup runs daily, cost stays O(n) with no historical DELETE churn'—shows cost-conscious design.
This medium-level SQL question appears frequently in data engineering interviews at companies like EY, Incedo, Tech Mahindra. While less common, it tests deeper understanding that distinguishes strong candidates. Mastering the underlying concepts (bigquery, partition) will help you answer variations of this question confidently.
Break this problem into components. Identify the core trade-offs involved, then walk the interviewer through your reasoning step by step. Demonstrate awareness of edge cases and production considerations - this is what separates good answers from great ones.
Approach: Use ROW_NUMBER() OVER (PARTITION BY dedup_keys ORDER BY tie_breaker) to define which row to keep; filter rn = 1. Preferred pattern: CREATE OR REPLACE TABLE ... AS SELECT EXCEPT(rn) FROM (SELECT , ROW_NUMBER() OVER (...) AS rn ...) WHERE rn = 1. Why CREATE OR REPLACE over DELETE: BigQuery is columnar; DELETE is a rewrite under the hood. For large tables, CREATE OR REPLACE is a single scan+write vs DELETE's read-modify-write. Cost: Full table scan; partition pruning helps if partitioning exists. Scalability: Define duplicate semantics clearly—same key + latest updated_at? Same key + first inserted? Tie-breaker drives ORDER BY. Best practice: Document dedup logic; consider MERGE for incremental upsert patterns.
Red Flag: Suggesting DELETE for large tables without acknowledging BigQuery's storage model. Pro-Move: 'We use CREATE OR REPLACE with partition expiry; dedup runs daily, cost stays O(n) with no historical DELETE churn'—shows cost-conscious design.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 3 companies. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.