Data Storage and Integration
Data Storage and Integration tests whether you can choose and defend a storage and movement pattern: which engine, which file format, which load strategy, and how the pipeline behaves when something fails. Expect OLTP versus analytical storage, partitioning and indexing, ACID versus BASE, and the difference between ETL, ELT, CDC and streaming ingestion.
Choosing storage
- Row-oriented stores suit transactional lookups touching whole records; columnar stores suit scans and aggregation over few columns.
- Partitioning prunes the data read; clustering or sort keys order data within a partition. Indexes accelerate point lookups but cost write throughput.
- ACID guarantees atomicity, consistency, isolation and durability and is the expectation for regulated financial data; BASE trades immediate consistency for availability and eventual convergence.
- CAP states that during a network partition you choose consistency or availability - it is about partitions, not a permanent pick-one.
Moving data
ETL transforms before loading, which suits expensive warehouse compute; ELT loads raw and transforms in-warehouse, keeping lineage and reprocessing ability. CDC reads the source log so only changes move, cutting load. Streaming gives low latency but requires ordering, watermarking and idempotent sinks, because retries duplicate messages.
Worked example
A source ERP emits order updates continuously; the warehouse must reflect them within five minutes without duplicates. Reasoning: nightly batch fails the latency requirement, so use log-based CDC into an immutable landing zone. The sink merge must be idempotent - keyed on order_id, applying the highest sequence number - because at-least-once delivery will replay messages after a failure. A curated layer then deduplicates and casts types. Daily reconciliation compares source and target row counts, and a variance above tolerance halts downstream jobs rather than publishing suspect numbers.
Traps that cost marks
- Claiming exactly-once end to end when the sink is only at-least-once plus an idempotent merge.
- Using columnar storage for high-frequency single-row updates.
- Loading straight to curated tables, losing the ability to replay.
- Ignoring late-arriving data and watermark handling in streaming designs.
- Assuming more indexes always improve performance.