What optimizations would you apply for partitioning strategies?

Partition optimization is a critical lever for cost and performance at scale. Why it matters: Misaligned partitions cause full-table scans; over-partitioning creates small-file hell (each file = metadata overhead + slower I/O). Strategies: (1) Align partition key with query filters—date, region—so the engine prunes at scan time. (2) Target 100MB–1GB per partition; under that, compaction merges small files. (3) Z-ordering (Delta/Iceberg) or liquid clustering (Snowflake) for multi-column filters where single-column partitioning falls short. (4) Drop stale partitions to reduce catalog and scan cost. Scalability trade-offs: Partitioning by high-cardinality key (e.g., user_id) leads to millions of tiny partitions—use bucketing instead. Cost implications: Over-partitioning multiplies S3 LIST/GET operations; under-partitioning forces full scans. Best practice: Profile query patterns, iterate based on access, and balance write cost vs read performance.