Recap — Picking the Right Ingestion Pattern

# Picking the Right Ingestion Pattern Data ingestion is the first technical layer of any pipeline. Pick the wrong pattern and you build operational pain forever. ## Pattern 1 — Full table snapshot (batch) Every run: read the entire source table; load into the warehouse. ``` Run nightly: SELECT * FROM source.orders → load to warehouse.raw.orders (full replace) ``` Pros: - Simple. No state to track. - Works for any source that can do SELECT *. - Self-healing (each load is a full picture). Cons: - Expensive for large tables (re-reads everything every run). - No history of changes (every load overwrites). - Latency = batch interval. Best for: - Small tables ( → append to warehouse.raw.events ``` Pros: - Much cheaper than full snapshot. - Scales to large tables. - Preserves history (rows accumulate). Cons: - Requires a reliable watermark column (`updated_at` or `created_at`). - Late-arriving data (events synced after the watermark) get dropped. - Watermark management is stateful. Best for: - Append-mostly tables (events, transactions, logs). - Large tables where full snapshot is expensive. Use sync time (`_loaded_at`) rather than event time when possible to avoid late-arrival drops. ## Pattern 3 — CDC (Change Data Capture) The source's transaction log is streamed; every INSERT, UPDATE, DELETE is captured and replicated. ``` Postgres WAL or MySQL binlog → CDC tool → warehouse ``` Pros: - Near-real-time (seconds to minutes lag). - Captures DELETEs and UPDATEs correctly. - No load on source DB (reads the log, not the tables). - Catches every change, including very late updates. Cons: - Requires DB-level access (log reader). - More operational complexity. - CDC tools cost money (Fivetran, Debezium, etc.). - Tricky to backfill historical state (you typically snapshot + start CDC from a point). Best for: - OLTP databases where you need close-to-real-time and accurate. - Tables with frequent UPDATEs (incremental batch misses them). - Compliance / audit use cases. Major CDC tools: - **Fivetran** — managed, expensive. - **Airbyte CDC** — open source, self-hosted. - **Debezium** — open source, requires Kafka infrastructure. - **AWS DMS** — AWS-native. ## Pattern 4 — Streaming (event-time) Application emits events to a stream (Kafka, Kinesis, Pub/Sub); consumer reads stream and loads warehouse. ``` App → Kafka topic → stream consumer → warehouse table ``` Pros: - True real-time when needed. - Decouples producer from consumer. - Replayable (events stay in stream). Cons: - Infrastructure complexity (Kafka cluster or managed equivalent). - Stream processor needed (Flink, Spark Streaming, Materialize, RisingWave). - Out-of-order events, exactly-once semantics — operational concerns. Best for: - Genuinely real-time use cases (fraud, alerting, live dashboards). - High-volume event data where batch struggles. - Decoupled architectures with many consumers. ## Pattern 5 — File-based Source produces files (CSV, JSON, Parquet) on S3/GCS/SFTP; pipeline picks them up. ``` External vendor uploads daily file to s3://incoming/ → Lambda / Airflow detects new file → loads to warehouse ``` Pros: - Simple. No source DB access needed. - Works with vendors who don't offer API. - Easy backfill (re-process files). Cons: - Latency depends on file drop schedule. - Schema can drift unexpectedly. Best for: - B2B vendor data feeds. - Marketing platform exports. - Compliance/audit logs. ## Decision tree ``` Where does the data live? ├─ External SaaS (Salesforce, Stripe, etc.) → Fivetran/Airbyte (uses their API; CDC where supported) ├─ Your OLTP database (Postgres/MySQL) │ ├─ Small + slow-changing → full snapshot batch │ ├─ Large + append-mostly → incremental batch │ └─ Need correctness on UPDATEs/DELETEs OR near-real-time → CDC ├─ Application events → Kafka/streaming (if real-time) OR batch event load └─ Files from a vendor → file-watch pipeline ``` ## Cost reality For 100K rows/hour: | Pattern | Cost | Latency | |---|---|---| | Full snapshot (hourly) | Modest, scales with table size | 1h | | Incremental batch | Low | 1h | | CDC | Modest (tool cost) | 1-15 min | | Streaming (own infra) | High (Kafka + ops) | Seconds | | Managed connector (Fivetran) | High (per-row fees) | 5-30 min | ## Build vs buy For each source, choose: - **Managed (Fivetran/Airbyte Cloud)** — pay per row; saves engineer hours. - **Self-hosted (Airbyte OSS, Meltano)** — operate yourself; saves money at scale. - **Custom Python script** — build for unusual sources. Most teams use Fivetran for 5-10 critical sources and skip it for one-off custom ones. Threshold: Fivetran costs 5-10x what it costs to operate Airbyte O