Discuss the data size challenges in your previous projects. How did you optimize storage and processing?

Situation: I led data platform optimization for a 50TB+ analytical workload where queries routinely timed out (15+ min), blocking downstream reporting and analytics teams. The warehouse was partition-unaware and stored raw JSON, driving 3x storage costs and full-table scans.

Task: Reduce query latency to sub-minute P95 while cutting storage spend by at least 25%, without disrupting existing pipelines or introducing new vendor lock-in.

Action: I implemented a multi-layer optimization strategy with clear trade-offs: (1) Partitioning — Partitioned by date (day) and region; partition pruning reduced scan volume by ~80% but required schema discipline to avoid partition skew. (2) Columnar format (Parquet) — Migrated from JSON to Parquet for column pruning; chose Snappy compression for a 60/40 read/write balance vs. ZSTD. (3) Aggregation tables — Pre-computed 5 materialized views for the top 20% of queries; accepted 2-hour refresh latency for these to avoid real-time overhead. (4) Data lifecycle — Moved data older than 2 years to cold storage (S3 Glacier Deep Archive); reduced hot-tier cost by ~40% with a 12-hour restore SLA. (5) Query optimization — Tuned join order, added bloom filters for high-cardinality joins, and set Z-order for time-range predicates.

Result: Query P95 latency dropped from 15 min to 8 sec; storage cost down 30%. The trade-off: lifecycle policies required governance and stakeholder alignment on retention.

Why it scales: Partitioning and columnar format are foundational; aggregation layers are a cost-for-latency trade-off. At 100TB+, we’d revisit tiering thresholds and consider tiered aggregation (hourly vs. daily vs. monthly).