Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Partition effectiveness metrics: (1) Partition pruning—% of partitions scanned vs total; aim for high pruning on typical queries. (2) Data skew—partition sizes; avoid huge variance. (3) Small files—count of files < threshold; need compaction. (4) Query latency—p50, p99. (5) Scan...
Partition effectiveness metrics: (1) Partition pruning—% of partitions scanned vs total; aim for high pruning on typical queries. (2) Data skew—partition sizes; avoid huge variance. (3) Small files—count of files < threshold; need compaction. (4) Query latency—p50, p99. (5) Scan size—bytes read per query. (6) Compaction frequency. Tools: Spark UI, query explain, table stats. Best practice: Monitor over time; adjust partition keys based on query patterns. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.
Red Flag: Generic textbook answers. Pro-Move: 'At scale we measured X, implemented Y, achieved Z%—validated and iterated.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.