Explain how partitioning and bucketing in Hive/Spark optimize queries. What are the trade-offs in bucket count, partition cardinality, and small-file problem? When does over-partitioning or over-bucketing become counterproductive?

Partitioning: Splits by column (e.g., dt, region); pruning skips non-matching partitions. Bucketing: Hashes rows into N files by key; enables co-located joins when both tables bucketed on same key. Why combined: PARTITIONED BY (dt) CLUSTERED BY (user_id) INTO 32 BUCKETS—prune by date, efficient join on user_id. Trade-offs: Partition cardinality too high (e.g., by hour for years) → small-file problem, metadata overload; too low → coarse pruning. Bucket count: Too many → tiny files, task overhead; too few → skew, large shuffles. Over-partitioning: Hundreds of thousands of partitions stress metastore and listing. Over-bucketing: Files smaller than block size waste I/O. Cost: Small files increase task count, S3 LIST calls; co-location reduces shuffle. Best practice: Partition by high-filter columns; bucket by join keys; target ~128MB–256MB per file.