How do you choose partitioning and clustering for a very large table? What can go wrong?

What the interviewer wants to hear

• Base it on the most common query filters
• Choose low-to-medium cardinality partition keys like date
• Avoid too many small partitions
• Use clustering or sort keys for high-cardinality filters
• Watch for skewed partitions and functions that block pruning
• Re-evaluate as query patterns change

Model answer

I start from how the table is queried: the filters and joins that appear in most queries and in the most expensive ones. Usually that is a date, so I partition by day or month. The partition key should have a moderate number of values; partitioning by user_id or by hour over many years creates millions of tiny partitions, which slows planning and creates small files. For high-cardinality filters like customer_id, I use clustering in BigQuery or Snowflake, Z-ordering or liquid clustering in Delta, or sort keys in Redshift, so the engine can skip data blocks within partitions. Things that go wrong: skewed partitions where one day is huge, queries that wrap the partition column in a function and lose pruning, and choosing a key nobody filters on. I check pruning in query profiles and revisit the design as query patterns change.

More hard Data Engineer questions

• Design a data platform for a fast-growing startup with 20 data sources and a small team. What would you choose and why?
• Explain at-most-once, at-least-once and exactly-once processing, and how you achieve effectively-once results in practice.
• A user asks to delete all their personal data. Your data is spread across a data lake, warehouse and backups. How do you handle it?
• The CEO's revenue dashboard showed wrong numbers for two days before anyone noticed. How do you fix this and prevent it from happening again?
• Design a pipeline that ingests 200,000 clickstream events per second and powers both a real-time dashboard and daily analytics.
• You must migrate a company from an on-premise warehouse to Snowflake or BigQuery without breaking hundreds of reports. How would you plan it?
• Leadership asks you to cut the cloud warehouse bill by 40 percent without hurting the business. What do you do?
• A metric definition changed and you must backfill two years of a large fact table while the daily pipeline keeps running. How do you do it safely?
• Design a CDC pipeline that replicates a busy PostgreSQL database into Iceberg or Delta tables, including updates and deletes. What are the tricky parts?