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.
Want feedback on your answer?
Paste your answer to this question and our AI Coach scores it, finds gaps, and shows you the FAANG-level version.
Paste your answer and get instant AI feedback with a FAANG-level improved version.
Analyze My Answer — FreeAccording to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 3 companies. DataEngPrep.tech maintains a curated database of 1,863+ real data engineering interview questions across 7 categories, verified by industry professionals.