building-ingestion-pipelineslisted

# Building Ingestion Pipelines ## When to use - Extracting from databases, APIs, files, or SaaS into a warehouse/lake. - Designing incremental extraction, watermarks, or CDC. - Handling API pagination, rate limits, and retries. - Deciding managed EL (Fivetran/Airbyte) vs custom code. - Do NOT use for transforming already-landed data (use dbt/Spark skills). ## Workflow ``` - [ ] Decide extraction mode: full snapshot vs incremental vs CDC - [ ] Pick a reliable high-water mark (updated_at, LSN/binlog, sequence) - [ ] Land raw immutably (append), then transform downstream - [ ] Make the load idempotent (upsert/partition overwrite by key) - [ ] Handle pagination, rate limits, retries, and late data ``` 1. **Choose the mode.** Full reload (small/dimension tables), incremental by a high-water mark (most fact tables), or CDC (high-volume OLTP where you need deletes and every change). 2. **Pick a trustworthy watermark.** `updated_at` only works if the source always updates it; otherwise use DB log positions (LSN/binlog/SCN) or a monotonic sequence. Store the last watermark and resume from it. 3. **Land raw immutably.** Append raw extracts (bronze) with load metadata; do transformations downstream so you can replay without re-pulling the source. 4. **Idempotent load.** Upsert by natural key or overwrite the partition, so retries and overlaps don't duplicate (see `writing-idempotent-transformations`). 5. **Be robust** to pagination, rate limits, and late data.