Question

A data engineer is creating a Silver Delta table for web clickstream events. The table will hold billions of rows, and analysts almost always filter queries by event_date. A colleague suggests partitioning the table by user_id because most joins reference that column. What is the best partitioning strategy, and why?

Accepted Answer

Partition by event_date, because it is a low-cardinality column frequently used in filter predicates, enabling effective partition pruning.

Answer

Partition by user_id, because partitioning on the most-joined column always improves join performance the most.

Answer

Partition by both event_date and user_id together to satisfy both filtering and joining patterns.

Answer

Do not partition at all; Delta tables cannot benefit from partitioning on any column.