Reviewed by Aditya Kumar · Last reviewed 2026-08-08
Drop partitions with ALTER TABLE ... DROP PARTITION , naming the partition values you want removed. Managed versus external tables This is the detail that decides whether your data actually…
This medium-level SQL question appears frequently in data engineering interviews at companies like Dunnhumby. While less common, it tests deeper understanding that distinguishes strong candidates. Mastering the underlying concepts (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. The expert answer includes a code example that demonstrates the implementation pattern.
Drop partitions with ALTER TABLE ... DROP PARTITION, naming the partition values you want removed.
-- Single partition
ALTER TABLE events DROP IF EXISTS PARTITION (dt='2026-01-01');
-- Several at once, in one metastore call
ALTER TABLE events DROP IF EXISTS
PARTITION (dt='2026-01-01'),
PARTITION (dt='2026-01-02');
-- A range, using comparison operators
ALTER TABLE events DROP IF EXISTS PARTITION (dt < '2026-01-01');
This is the detail that decides whether your data actually disappears. For a managed table, dropping a partition removes both the metastore entry and the underlying files. For an external table, it removes only the metastore entry and leaves the files exactly where they are. People routinely "delete" partitions from an external table, see them vanish from queries, and are then surprised by the storage bill, or by the rows reappearing after someone runs a metastore repair.
By default a managed-table drop moves files to HDFS trash, so space is reclaimed only when trash expires. Adding PURGE deletes immediately and irreversibly:
ALTER TABLE events DROP IF EXISTS PARTITION (dt='2026-01-01') PURGE;
MSCK REPAIR TABLE synchronises the metastore with what is in storage. Depending on the engine and version it typically only adds partitions it discovers; it is not a delete command. On external tables it can actively undo your work by re-registering partitions whose files still exist. To remove stale metastore entries whose files are gone, use ALTER TABLE ... DROP PARTITION explicitly, or MSCK REPAIR TABLE ... SYNC PARTITIONS where supported.
Always include IF EXISTS in automated retention jobs so a re-run does not fail on an already-dropped partition. Batch many partitions into a single statement, because each separate ALTER is a round trip to the metastore and dropping a year of daily partitions one at a time is slow.
In the interview, also mention that partition pruning is the reason partitioning exists, so choosing a low-cardinality partition key matters far more than the drop syntax.
Red Flag: Dropping partition without backup for compliance. Pro-Move: 'We archive to cold storage before DROP; retain 7 years for audit—documented in data retention policy.'
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 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.