System design

Solution

OLTP CDC → raw/bronze files → typed silver (MERGE on order_id) → gold at one grain (order or line — pick one) → a serving warehouse or marts for BI. Orchestrate freshness; do not transform 2 TB in the orchestrator. Page on freshness and volume, not only “job succeeded.”

Solution

At-least-once plus an idempotent sink (MERGE on event_id / offset+partition). True EOS is a specific Kafka + sink setup. Saying “Kafka is exactly-once” without a key is a fail.

Solution

Type-2 dimension with valid_from/valid_to. Facts join as-of the event timestamp. Type-1 overwrite changes history. Say which product wants.

Solution

Dual-write CSV trees. Pick Iceberg (or UniForm) with a single writer. Two writers is how the copies lie.

Solution

Separate raw PII, tokenize or mask in silver, grant only the columns they are allowed, audit the extract. Slack SELECT * from bronze is the incident.

Solution

Bounded date chunks, a smaller pool, catchup off, and idempotent partition replace. Unpausing catchup from 2019 is the stampede.

Solution

Sub-100ms point lookups, per-user online features, or a product API that is not SQL. Then you need a keyed store or a feature/online path — and a contract with the batch grain.

Solution

Tags / chargeback on compute and storage, query tags from dbt/jobs, and an owner on every gold table. Architecture without FinOps is a slide.

Solution

Debezium/DMS → Kafka/MSK → sink that MERGEs into Iceberg on the primary key, watermark on LSN/time, dead-letter for poison rows. A nightly dump is not this design.

Solution

Tenant_id on every fact, row policies or separate schemas for large tenants, and never a shared “admin” role that can SELECT * across tenants. Compaction and clustering on tenant + date.

Solution

If the decision can wait an hour, batch is cheaper and simpler. Streaming is for operational actions (fraud, inventory, live ops). Do not stream a CFO dashboard to look modern.

Solution

Cross-region replica or object-lock, a metastore backup, and a restore drill. “S3 is durable” is not a runbook. Know RPO/RTO in hours, not adjectives.

Solution

Events → Kafka/Kinesis → cheap land (S3/Iceberg bronze) → 15–60 min silver sessionization → gold DAU table partitioned by day. Analysts hit gold, not Kafka. SLA: gold partitioned yesterday complete by 08:30 UTC. Do not stream the CFO dashboard.

Solution

One row per ledger event (or immutable entry), not a mutable balance column. Balance is a sum. Idempotency on (payment_id, event_id). Refunds are new events, not UPDATEs of the capture.

Solution

Hash rows or use a natural key you document; snapshot each dump in bronze by load_date; compute diffs in silver (except/hash). If there is truly no key, the table is insert-only snapshots — say that to the stakeholder.

Solution

Region-local bronze/silver, aggregates that are non-PII replicated to a global mart, or a viewer in-region. Do not COPY PII to us-east-1 “temporarily.” Privacy engineering is part of the sketch.

Solution

Dual-run old and new marts, reconciling on a date range, then swap the BI pointer on a freeze window. Keep the old table read-only for a month. A silent rename on Friday is how finance pages you.

Solution

Not the warehouse. Materialize features to Redis/Dynamo/Feast online, with a batch/stream publisher and a mismatch monitor. Warehouse is the offline train path.

Solution

One warehouse (or tag) per team, resource monitors, and QUERY_HISTORY grouped by warehouse/role. Shared XS for ad-hoc with a tight auto-suspend. Without tags, FinOps is a spreadsheet of villains.

Solution

Replica identity FULL (or unique key) so WAL carries the old row, a slot that does not fall behind, and a sink that applies deletes. Replica identity DEFAULT on a table without PK drops delete details.

Solution

Microbatch if 2 minutes is fine and the team already runs Spark. Flink+CH if you need event-time windows and sub-minute. Complexity is a cost. Do not pick Flink to look senior.

Solution

Separate schema, masking/row policies, approved join to a consent table, no SELECT * grants, and an access log. A hashed email is still often PII. Marketing gets a view, not bronze.