Data Cleaning and Quality Validation in SQL Questions
Handle NULL values, duplicates, and data type issues within queries. Implement data validation checks (row counts, value distributions, date ranges). Practice identifying and documenting data quality issues that impact analysis reliability.
HardTechnical
81 practiced
Write SQL to scan logs table and detect plaintext PII leaks: identify rows where expected hashed_email column contains short non-hex strings or where free-text fields appear to contain email or SSN patterns. Return sample violating rows and counts and discuss false-positive handling.
EasyTechnical
75 practiced
For table 'events' with column 'event_type', write SQL to list the top 10 most frequent event_type values with their counts and percentage of total events. Explain how this helps triage data quality issues.
MediumTechnical
65 practiced
A frequently run analytic query repeatedly casts a varchar column 'user_id_str' to INT many times, causing CPU overhead. Propose a SQL rewrite and a long-term schema change to avoid repeated casting. Provide before-and-after SQL snippets and discuss trade-offs.
HardSystem Design
130 practiced
Design SQL-based validation for a streaming ingestion pipeline (Kafka -> staging -> BigQuery) that supports late-arriving records, replays, and idempotent reprocessing. Describe watermarking, deduplication by idempotency key, soft-failure handling, and how to run validations without blocking steady-state ingest.
MediumTechnical
77 practiced
Given a 'metrics' table (entity_id, ts, value NUMERIC), write SQL to flag per-entity rows that are statistical outliers using the IQR method: compute Q1 and Q3 per entity and mark rows where value is outside [Q1 - 1.5*IQR, Q3 + 1.5*IQR]. Provide the SQL and discuss handling few-sample entities.
Unlock Full Question Bank
Get access to hundreds of Data Cleaning and Quality Validation in SQL interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.