Requirements & goals
- Keep raw events 7 years; fast interactive access to recent 90 days (sub-second to few seconds for typical queries).
- Minimize cost for older data while allowing occasional deep-history queries (hours latency acceptable).
- Maintain discoverability and predictable retrieval SLOs.
High-level approach
- Use a columnar file format (Parquet/ORC) managed via a table format (Apache Iceberg / Delta Lake / Hudi) on object storage (S3/GCS/Azure Blob).
- Implement a two/three-tier storage lifecycle: Hot (clickhouse/OLAP/memory + recent parquet on SSD), Warm (S3 Standard or Standard-IA), Cold (S3 Glacier / Archive cold tiers).
Partitioning strategy
- Time-first partitioning: daily partitions by event_date for recent 90 days; larger coarser partitions (monthly/quarterly) for older data to reduce object count and metadata overhead.
- Compound partition keys: event_date + shard_id or region if common query filters include them. Keep partition granularity aligned with query patterns.
- Maintain partition evolution with the table format to allow safe compaction/rewrites.
Compaction & file layout
- For recent partitions: small ingestion files (from streaming jobs) are compacted continuously (e.g., micro-batches) into optimal Parquet file sizes (256MB–1GB) for read performance.
- For warm/cold transitions: run incremental compaction/large-file rewrite jobs to merge daily files into monthly/quarterly files to reduce number of objects and per-file overhead.
- Generate and store column statistics, min/max, bloom filters, and zone maps (via Iceberg/Parquet metadata) to enable fast predicate pruning.
Lifecycle policies
- 0–90 days (Hot): Keep in S3 Standard and/or cached in an interactive engine (Presto/Trino, Dremio, BigQuery BI Engine). Enable low-latency query path and aggressive compaction.
- 90–365 days (Warm): Transition to S3 Standard-IA or equivalent; keep partition-level metadata active; less frequent compaction (monthly).
- 1–6.9 years (Cold): Repartition/compact into monthly/quarterly files and move to Glacier Deep Archive / Archive; keep a lightweight manifest/catalog entry pointing to archived objects and minimal statistics in the metadata store.
- 7 years: Delete per retention policy, optionally run final exports for compliance if required.
Metadata & discovery
- Use a centralized table/catalog (AWS Glue/Apache Hive Metastore/BigQuery dataset) backed by Iceberg/Delta/Hudi so metadata captures partition manifests, snapshots, file locations, and statistics.
- Maintain separate “archive index” that records: logical table, partition range, object URIs, min/max timestamps, key statistics, and retrieval path/estimated rehydrate time. Keep this index in a fast DB (DynamoDB/Cloud Bigtable/Postgres) for quick lookups.
- For very old cold objects, store lightweight metadata in both the catalog and an inverted/indexed search (Elasticsearch/OpenSearch) keyed by common query attributes (user_id, device_id, event_type) to support selective rehydration.
Query & retrieval patterns
- Interactive queries hit Hot/ Warm layers using partition pruning + column pruning + statistics (no scan of cold objects).
- When query touches archived partitions:
- Planner consults catalog; if partition is archived it returns a “rehydrate candidate” plan.
- Option A (selective): Use metadata to identify specific object URIs to rehydrate (only needed files), rehydrate to Warm storage tier, then run the query.
- Option B (bulk): For large historical analyses, schedule an async job to copy required monthly files into Warm storage/temporary table and notify users.
- Provide APIs/SQL functions to trigger on-demand restore with estimated cost and latency visible to user.
Performance, cost & trade-offs
- Storing metadata and using compaction reduces object count and cost, and speeds pruning.
- Using table formats ensures ACID-like snapshots and safe rewrites.
- Trade-off: more aggressive compaction and maintained statistics cost compute at transition time but reduce long-term query and retrieval cost.
- Provide SLAs: e.g., <5s interactive for 90-day data; 1–24 hours for selective archive restore; 24–72 hours for large historical restores.
Operational considerations
- Automate lifecycle via data pipelines (Airflow, Step Functions) with monitoring/alerts for failed compactions and restore ops.
- Implement cost guardrails / approval workflows for large restores.
- Periodic audits to validate archived data integrity (checksums) and retention compliance.
Example technologies
- Storage: S3/GCS/Azure Blob + Glacier/Archive tiers
- Table format: Apache Iceberg or Delta Lake
- Catalog: AWS Glue / Hive Metastore / BigQuery
- Query: Trino/Presto, Spark, BigQuery
- Index/metadata DB: DynamoDB or Postgres + OpenSearch for attribute search
This design balances low-cost long-term retention with fast access for recent data by combining time-aware partitioning, compaction, tiered storage, rich metadata, and controlled restore paths.
