Design a data pipeline from end to end - describe how data would be ingested, processed, stored, and queried.

Section 1 — The Context (The 'Why')
End-to-end data pipelines must reconcile batch (S3, databases) and streaming (Kafka, Kinesis) sources into a unified lakehouse or warehouse. The primary challenge is orchestrating ingestion, transformation, and serving while handling schema evolution, late-arriving data, and maintaining lineage for compliance. A naive monolithic pipeline breaks when a single transformation fails and blocks the entire DAG; incremental processing and dependency management become critical at scale.

Section 2 — The Diagram

[S3/RDS/API] --> [Glue/Kafka] --> [Bronze] --> [Silver]
      |               |                  |             |
      v               v                  v             v
[CDC Connector]  [Schema Reg]    [Delta/Parquet] [Aggregations]
                                                    |
                                                    v
                                          [Gold] --> [Redshift/Dash]

Section 3 — Component Logic
Ingestion uses Glue for batch S3/RDS sync and Kafka Connect or Kinesis for streaming. The bronze layer stores raw data with schema-on-read; idempotency is ensured via upsert keys or merge operations. The silver layer applies transformations, deduplication, and type coercion; partitioning strategies (e.g., by date, region) optimize query performance. The gold layer holds business-level aggregations. Fan-out patterns allow one bronze table to feed multiple silver/gold consumers. CDC connectors enable low-latency replication from OLTP databases. TTL policies on raw data reduce storage costs while retaining hot tiers. In production, monitor consumer lag, checkpoint success rate, and sink write latency as primary SLOs. Partitioning strategies should align with query patterns; bucketing within partitions mitigates join skew. TTL policies on raw and intermediate data control storage cost while preserving replay capability for debugging and backfill. Data skew mitigation via salting or secondary hashing prevents single partitions from becoming bottlenecks. Exactly-once semantics require transactional commits at the sink; at-least-once delivery demands idempotent write logic to avoid duplicates. Fan-out patterns allow one source topic to feed multiple downstream consumers without re-ingestion. Backpressure handling ensures that slow processors do not cause unbounded buffer growth; Kafka consumer lag is a key metric. Schema evolution should follow additive-only rules where possible to avoid breaking consumer compatibility. The CAP trade-off should be documented per component: analytics typically favors AP, while financial reconciliation requires CP. Blast radius from component failure is bounded by replication and checkpointing; design for graceful degradation during partial outages. Cost optimization: use Spot instances for batch workloads and tier cold data to lower storage classes. Dead-letter queues preserve failed records for replay rather than dropping them.

Section 4 — The Trade-offs (The 'Senior' part)