Applies toDatabases (SQL Server, Oracle, PostgreSQL, MySQL), SaaS applications, files (SFTP, object storage), event streams; tools such as Debezium, Fivetran/Airbyte-style connectors, ADF, Auto Loader, Snowpipe
Pattern by source
| Source | Preferred pattern | Notes |
|---|---|---|
| OLTP database, large tables, frequent changes | Log-based CDC (Debezium, native CDC, replication tools) | Captures deletes, low source load, near real time |
| Database without CDC access or small tables | Incremental extract by watermark (updated_at, rowversion) | Misses hard deletes unless soft-deleted or reconciled |
| Small reference tables | Full extract | Simple; compare snapshots to find deletes |
| SaaS application | API connector (managed or custom) | Rate limits, pagination, API changes |
| Partner/vendor files | File drop with event-driven pickup | Contracts on naming, schema, completeness markers |
| Application events | Event streaming (Kafka, Event Hubs, Kinesis) | Producer owns schema; contracts needed |
| Another analytics platform | Data sharing (Delta Sharing, Snowflake shares, shortcuts) | No copy where possible |
Design questions per source
- How much load can the source take, and when? (Production OLTP often allows only replicas or log reading.)
- Do we need deletes? Hard deletes break watermark-based extracts.
- How fresh must data be? (See the batch vs streaming guide.)
- How will schema changes be communicated and handled? (See the schema drift Sev1 post.)
- How do we replay or backfill a period?
- Who owns the source, and how do we get notified of changes and outages?
Things every pattern needs
| Concern | Design |
|---|---|
| Idempotency | Re-running a load produces the same result (MERGE on keys, partition overwrite) |
| Completeness | Row counts/checksums vs source; end-of-file markers; CDC lag monitoring |
| Raw landing | Keep raw data (bronze) to reprocess without hitting the source again |
| Metadata | Load timestamps, source system, batch/offset IDs on every row |
| Secrets | Managed identities / secret stores, never in pipeline code |
| Source protection | Read replicas, off-peak windows, throttling |
Watermark pitfalls
updated_atnot updated by every code path (bulk fixes, triggers disabled).- Clock differences and long transactions committing older timestamps. Use a lookback window.
- Time zone mismatches.
When these are common, it's a strong signal to move to CDC (see the polling to CDC transformation post).
Choosing build vs managed connectors
| Managed connectors | Custom pipelines |
|---|---|
| Fast to start, maintained against API changes | Full control, no per-row pricing |
| Cost scales with volume/rows | Your team maintains them |
| Limited customisation | Any logic, any source |
Many teams mix: managed connectors for SaaS APIs, CDC tools for databases, custom code for unusual sources.
Tip
Write a one-page "source contract" for each important source: owner, pattern, schedule/latency, schema change process, contact for outages. It prevents most ingestion incidents from becoming mysteries.
Checklist
- Each source classified and pattern chosen
- Source load limits and windows agreed with owners
- Deletes, schema changes and replays designed
- Idempotency, completeness checks and raw landing in place
- Build vs managed connector decision recorded
- Source contracts written