Data Engineering Interview: How Do You Choose a Partitioning Strategy and Prove Partition Pruning Works?

Prompt and Applicable Context

An Apache Iceberg event table contains about 1 PiB and adds 6 TiB per day. It retains 180 days. Every query has an event_time range, usually one to seven days; 35% also has an equality filter on one of 12,000 tenant_id values. Events may arrive up to 48 hours late, and historical backfills are possible. The current unpartitioned table scans too much data.

Choose an initial partition specification, show why obvious alternatives fail, and explain how you would prove pruning rather than assume it. Cover predicate shape, skew, file sizing, late data, partition evolution, rollout, and rollback.

All sizes, percentages, cardinalities, and retention periods are interview assumptions. This question targets data engineers, analytics-platform engineers, and lakehouse engineers. Its core competency is physical data layout and query planning, so the category is data.

What the Interviewer Is Evaluating

The first signal is workload-first reasoning. A partition column is useful when common predicates let the engine exclude physical data. Cardinality alone does not make a good partition key.

The second signal is granularity discipline. Coarse partitions read unnecessary bytes; fine or high-cardinality partitions create metadata, tiny files, and expensive writes. A strong answer estimates bytes and partition counts before selecting a specification.

The third signal is proof. A query containing a date condition does not guarantee pruning. The candidate checks the physical plan or dry-run estimate, planned files and partitions, bytes scanned, and result equivalence.

The fourth signal is lifecycle awareness. Event time and ingestion time serve different questions. Late data changes old event-time partitions. Partition evolution also leaves old files under old specifications until they are rewritten.

Clarifying Questions Before Answering

Which predicates are mandatory and selective? If most queries omit time, time partitioning cannot bound their scans. If nearly every query selects one tenant, bucketing by tenant becomes more valuable.
Does the business filter by event time or arrival time? Event-time partitioning matches reports but accepts late writes. Ingestion-time partitioning simplifies append operations but may scatter one business day across several arrival partitions.
What is the tenant distribution? Fixed hash buckets tolerate high cardinality, but a dominant tenant can still create skew. A dedicated path may be justified only after measuring it.
What file and metadata limits does the engine have? The goal is not the maximum number of partitions. Each active partition must receive enough bytes to create useful files.
Can the table format hide transforms and evolve the specification? If not, queries and writers may need an explicit derived partition column and a migration plan.

30-Second Answer Framework

“I would begin from the predicate log, not from column cardinality. Every query filters event_time, so I would canary day(event_time). Six TiB per day is large; for the 35% of queries with tenant equality, I would benchmark adding a fixed bucket(64, tenant_id) transform. That creates at most 64 physical groups per day instead of 12,000 tenant partitions.

I would use half-open event-time ranges, inspect the plan and planned files, and compare scanned bytes against an unpartitioned control while verifying identical results. I would reject tenant_id/hour because it can create hundreds of thousands of groups per day and starve writers. Late events update the corresponding event-day partition. I would roll out by workload class and evolve the Iceberg specification only after pruning benefit, file sizes, write cost, and metadata stay within acceptance limits.”

Step-by-Step Deep Dive

Step 1: Turn the Query Log into a Predicate Matrix

Sample production queries before choosing layout. For each workload class, record frequency, latency or cost weight, time-range width, tenant selectivity, joins, and whether the predicate is available before scanning the fact table. Weighting by frequency alone can optimize cheap dashboards while ignoring a rare daily job that reads most of the table.

The scenario gives one strong invariant: every query has an event-time range. This makes event time the first pruning dimension. Tenant equality appears in only 35% of queries, so tenant cannot be the only partition dimension. A free-form search field, metric value, or high-cardinality event ID is unsuitable because it neither matches the dominant access path nor produces stable physical groups.

Step 2: Estimate Candidate Partition Sizes and Counts

Daily event-time partitions receive about 6 TiB. A seven-day query can therefore start with roughly 42 TiB before file- and row-group-level skipping. Hourly partitions receive about 256 GiB on average:

text

6 TiB / 24 = 0.25 TiB = 256 GiB per hour

Both are large enough to fill normal columnar files. Daily partitions create about 180 active time groups; hourly partitions create about 4,320. The finer choice is justified only if many queries cover a few hours and the added metadata and write fan-out improve measured cost.

Directly partitioning by tenant_id and hour has a dangerous upper bound:

text

12,000 tenants * 24 hours = 288,000 tenant-hour groups per day

Sparse groups and skew make the realized count lower, but the upper bound exposes the failure mode. Writers may touch many groups per batch and close tiny files. A fixed bucket(64, tenant_id) transform caps the tenant dimension at 64 groups per time partition. With uniform data, each daily bucket receives about 96 GiB; actual skew must be measured.

Step 3: Choose the Simplest Specification That Meets the Workload

Start with day(event_time). It serves every query and has the smallest metadata surface. Then benchmark this alternative for tenant-heavy workloads:

sql

PARTITIONED BY (day(event_time), bucket(64, tenant_id))

The bucket count is an interview candidate, not a universal default. Compare 16, 32, 64, and 128 using actual tenant distribution, file targets, writer parallelism, and query concurrency. Adding buckets helps only when the engine can derive the bucket from a tenant equality predicate and the reduction in planned files outweighs metadata and write fan-out.

Do not encode the partition layout into business SQL when the table format supports hidden transforms. Queries should filter logical event_time and tenant_id; the table metadata maps those predicates to physical partitions. This avoids silently inconsistent derived columns and lets the specification evolve without rewriting every consumer query.

Step 4: Write Predicates the Planner Can Prune

Use an unambiguous half-open range in the business time zone converted to UTC boundaries:

sql

SELECT tenant_id, event_type, COUNT(*)
FROM analytics.events
WHERE event_time >= TIMESTAMP '2026-07-01 00:00:00 UTC'
  AND event_time <  TIMESTAMP '2026-07-08 00:00:00 UTC'
  AND tenant_id = 'tenant-42'
GROUP BY tenant_id, event_type;

The half-open interval avoids double-counting a boundary when adjacent windows run. Wrapping the partition source in unsupported functions, comparing it with another row-dependent column, hiding it behind an OR, or casting it inconsistently may prevent static elimination. Exact supported transformations are engine-specific, so verify the plan instead of memorizing one optimizer's rules.

For local-date reports, compute UTC start and end instants from the requested time zone before the query. A fixed 24-hour subtraction is wrong across daylight-saving transitions. The stored event timestamp, partition transform, and reporting boundary must also use a documented timestamp convention.

Step 5: Prove Pruning and Correctness

Build a test matrix with a one-hour range, one day, seven days, tenant and non-tenant variants, an empty range, a late event, and a daylight-saving boundary. For each query, capture:

logical and physical plans, including partition or file filters;
planned partition and file counts;
estimated and actual bytes scanned;
planning time, execution p50/p95, and task count;
output row count, aggregates, and a checksum against the control.

An unpartitioned snapshot is the control. For a one-day query over 180 uniformly sized days, a coarse expectation is that time pruning considers about 1/180 of active bytes, before metadata, skew, and file statistics. This is an order-of-magnitude check, not a promised ratio. A plan that names the filter but still scans all files has not passed.

Test negative controls too. Remove the time predicate, rewrite it into an unsupported expression, and use a tenant range rather than equality. The scan should expand in explainable ways. Negative controls prove that the measurement can detect missing pruning.

Step 6: Account for Writes, Late Data, and Maintenance

Partitioning changes the write path. Track open writers per task, files created per commit, p50/p90 file size, commit latency, metadata growth, and retry conflicts. Distribute rows by the partition transform before writing and size writer count from bytes per group. Partition pruning cannot compensate for millions of tiny files.

Because reports use event_time, an event arriving 48 hours late belongs to its historical event-day partition. Writers and compaction must permit those updates. Define when a partition becomes cold, and delay aggressive rewrite work until the lateness window closes. Backfills need a bounded partition range, idempotent writes, and the same file-size checks as streaming ingestion.

Step 7: Evolve Without a Big-Bang Rewrite

With Iceberg hidden partitioning, a new specification applies to new files while old files keep their previous specification. The planner can read both, but benefit is mixed until hot or frequently queried old data is rewritten. Record a baseline snapshot, canary a recent range, and expand only if both old- and new-spec queries remain correct.

Rollback means stopping new writers on the new specification and restoring the previous write configuration or table snapshot where supported. It does not automatically undo already deleted files. Keep snapshot retention and physical cleanup outside the canary window. Rewrite historical data only when the measured savings justify the I/O and conflict risk.

Acceptance criteria should include identical business results, expected partition elimination for each workload class, lower scan bytes or latency, healthy file-size percentiles, bounded planning time, no ingestion-SLA regression, and stable metadata growth.

High-Quality Sample Answer

“I would extract the real predicate distribution first. Here, every query has an event-time range, while only 35% selects one tenant, so time is the primary dimension. A daily partition holds about 6 TiB and gives 180 active time groups. Hourly partitions hold about 256 GiB but create 4,320 active time groups; I would use them only if narrow-hour queries justify the extra metadata.

My baseline canary is day(event_time). For tenant-heavy queries, I would benchmark day(event_time), bucket(64, tenant_id). Sixty-four is only a candidate: it caps the tenant fan-out and gives roughly 96 GiB per daily bucket under uniform distribution. I would reject direct tenant-hour partitioning because its upper bound is 288,000 groups per day, which risks small files and writer fan-out.

Queries use UTC half-open ranges and tenant equality. I would inspect the physical plan, planned files, and scan bytes, then compare results and checksums with an unpartitioned snapshot. The test matrix includes one-hour, one-day, seven-day, empty, late-event, tenant, non-tenant, and daylight-saving cases. I would also run negative controls that remove or obscure the time predicate.

Late events write to their event-day partitions, so compaction waits beyond the 48-hour lateness window. During the canary I monitor file percentiles, writers per task, planning p95, metadata, write lag, and conflicts. If Iceberg partition evolution is available, new files adopt the new spec while old files remain readable. I rewrite older data only when measured query savings exceed rewrite cost. Any correctness difference, all-file scan, tiny-file surge, or ingestion-SLA regression stops rollout.”

Common Mistakes

Choosing the highest-cardinality column → It creates sparse groups and tiny files without serving dominant predicates → Start from weighted query predicates and bound physical groups.
Partitioning by tenant and hour → The scenario permits 288,000 groups per day → Use time first and benchmark a fixed bucket transform.
Seeing a date filter and assuming pruning → Unsupported expressions can still scan every file → Inspect the physical plan, planned files, and bytes.
Validating latency only → Cache, concurrency, or extra compute can mimic improvement → Use scan bytes, plan evidence, negative controls, and equal resources.
Using ingestion time for event-time reports without analysis → Late events for one business day scatter across arrival partitions → Align the physical dimension with the query's time semantics.
Ignoring file sizes → Fine partitions starve writers and shift cost from scanning to planning → Track bytes per group, writer fan-out, and file percentiles.
Rewriting all 1 PiB immediately → Cost, conflicts, and rollback exposure become excessive → Canary recent ranges and rewrite only justified history.
Assuming evolution rewrites old files → Multiple specifications coexist → Test mixed-spec planning and schedule selective rewrites.

Follow-Up Questions and Responses

Follow-up 1: What changes if 95% of queries filter one tenant?

Tenant bucketing gains more weight. Recalculate bucket counts from tenant distribution and query concurrency, then compare time-only, time-plus-bucket, and possibly tenant-isolated layouts for dominant tenants. Direct 12,000-way partitioning still requires proof that each group produces healthy files and manageable metadata.

Follow-up 2: Why not partition hourly if 256 GiB per hour is still large?

Hourly may be correct when most queries span minutes or hours and pruning seven daily partitions is too coarse. It creates 24 times as many time groups and more write fan-out. Benchmark planning, bytes, file sizes, and ingestion cost for the actual range distribution before accepting that trade.

Follow-up 3: What if a query filters a local calendar day?

Translate the requested local day's start and the next day's start into UTC instants, then use a half-open range. This handles 23- or 25-hour daylight-saving days. Verify that the partition transform and stored timestamp convention let the engine derive the relevant physical days.

Follow-up 4: What if the plan shows pruning but bytes do not fall?

Check whether the selected partitions contain most of the table due to skew, whether old files use another specification, whether residual filters cannot use file statistics, and whether the reported bytes include unrelated stages. Compare planned file IDs and an unpartitioned control; a label in the plan is not sufficient evidence.

Follow-up 5: How do you change from daily to hourly partitions safely?

Canary the new specification on new files, keep the previous snapshot, and query ranges that cross old and new layouts. Measure correctness, planning, file sizes, and write cost. Rewrite only the historical ranges whose measured benefit pays for the I/O, and delay physical cleanup until rollback and retention windows close.