Picking a Compression Codec for Two Very Different Tables

Your data platform has two Parquet tables:

• hot_metrics: queried continuously by an internal service with a strict p95 latency SLO, backed by local NVMe-attached storage on the query cluster (not remote object storage), and re-scanned by dashboards roughly every 30 seconds.
• cold_archive: a compliance-retention table written once and queried perhaps a few times a year during an audit, stored on S3, currently costing a non-trivial amount in storage.

Both are currently compressed with the same default codec, snappy.

1. For each table, decide whether snappy is the right choice, a different codec would serve better, or the current choice is already appropriate — and justify each answer by identifying where the bottleneck actually is for that table.
2. hot_metrics' query cluster CPU utilization is currently running very high during peak dashboard load. Does that change your answer for that table?
3. Explain why "just always use zstd" would not be a fully rigorous answer to give in an interview, even though it's a reasonable default.