Data Store Management
Core concepts
You must be able to choose and physically design a store: S3 data lakes, Redshift warehouses, DynamoDB key-value tables, and the Glue Data Catalog (optionally with Lake Formation) as the shared metastore. Open table formats (Iceberg, Hudi, Delta) add ACID and upserts on S3.
S3 design: partition prefixes that match query filters (dt=2024-01-01/), target file sizes (roughly 128 MB–1 GB), compaction of small files, lifecycle transitions to IA/Glacier, versioning, and Object Lock (requires versioning). Athena partition projection avoids crawler and MSCK REPAIR overhead.
Redshift: distribution style KEY (co-locate joins), ALL (replicate small dimensions), EVEN (default round-robin), AUTO; sort keys compound (prefix order) versus interleaved (equal weight, costly to maintain); VACUUM and ANALYZE after large loads; WLM queues and concurrency scaling; Spectrum for external S3 tables; RA3 for separated compute and managed storage.
DynamoDB: high-cardinality partition keys, hot-partition avoidance, LSIs (same key, 10 max) versus GSIs (own key, eventually consistent, extra write cost), on-demand versus provisioned capacity, TTL for free expiry, Streams for change propagation.
Traps
- DISTSTYLE ALL on a fact table replicates it to every node; interleaved sort keys require
VACUUM REINDEXand degrade with heavy churn. - Athena bills per byte scanned, so partitioning, columnar formats, and compression are the cost levers.
- A DynamoDB query on a non-key attribute always scans or needs a GSI.
Rule of thumb
Dimension under ~2 million rows → DISTSTYLE ALL. Large fact table → DISTSTYLE KEY on the frequent join column. Unsure → AUTO.