Module 2 – Sources & Ingestion

Understanding Source Systems

Data exists to run the business first

Types of source systems

▼

WhatHow

Common sources

OLTP databases: PostgreSQL, MySQL, SQL Server, Oracle (the primary operational backbone)
SaaS applications: Salesforce, HubSpot, Stripe, Shopify (accessed via REST APIs)
Files and object storage: CSV, JSON, XML, Parquet files dropped in S3/ADLS/GCS
Event streams: Kafka topics, webhooks, IoT sensor data, application logs
Third-party APIs: Weather data, financial feeds, social media, geolocation services
Legacy systems: Mainframes, FTP drops, Excel files, email attachments

Key questions about any source

Ownership: Who owns this data? Who approves access?
Change pattern: Append-only, updates, deletes, or full snapshots?
Volume and frequency: How much data, how often?
Schema stability: How often does the schema change? Is there a contract?
Extraction impact: Can the source handle extraction load?

Ingestion Patterns

Batch, incremental, CDC, and streaming

Full load (snapshot)

▼

What it is

Extract the entire table on every run. Simple but expensive at scale. Common for initial loads, reference/lookup tables, or when no reliable change tracking exists.

When to use

Initial migration or bootstrap load
Small reference tables (countries, currencies, product categories)
Sources with no reliable updated_at or change tracking
When simplicity outweighs efficiency

Incremental load

▼

What it is

Extract only new or changed rows since the last extraction, typically using a watermark column (updated_at, created_at, or a sequence ID). Efficient but may miss deletes.

How it works

Store a high-watermark (the max timestamp from the last successful run)
Query: WHERE updated_at > last_watermark
Append or merge the results into the target
Limitation: Does not capture deletes unless soft-delete flags exist

Change Data Capture (CDC)

▼

WhatWhyHow

What it is

CDC captures every insert, update, and delete as it happens in the source database, typically by reading the database transaction log (Write-Ahead Log in PostgreSQL, Binary Log in MySQL). This gives you a complete, ordered stream of all changes.

Why it matters

CDC is the most accurate way to replicate data because it captures deletes (which incremental loads miss), preserves the order of changes, and has minimal impact on the source database since it reads the log rather than running queries.

Tools and implementation

Debezium: open-source CDC connector for PostgreSQL, MySQL, SQL Server, MongoDB
AWS DMS: managed database migration and replication service
Fivetran / Airbyte: managed CDC connectors for many databases
Flow: Source DB log → Debezium → Kafka topic → Consumer (Spark, Flink, etc.)

Event-driven / streaming ingestion

▼

What it is

Data arrives as a continuous, unbounded stream of events: clickstream data, IoT sensors, application logs, transaction events. There is no "batch" boundary; events flow continuously and must be processed with awareness of time, ordering, and state.

Key components

Event producers: applications, sensors, user interactions
Event broker: Kafka, Kinesis, Pub/Sub, Event Hubs (durable, replayable log)
Stream processor: Flink, Kafka Streams, Spark Structured Streaming
Sink: Data lake, warehouse, feature store, real-time dashboard

Pattern	Captures deletes?	Latency	Source impact	Complexity
Full load	Yes (implicit)	Hours	High (full scan)	Low
Incremental	No	Minutes to hours	Moderate	Low
CDC	Yes	Seconds to minutes	Low (reads log)	Moderate
Streaming	N/A (events)	Seconds	Low	High

Schema Drift & Data Contracts

When the source changes without warning

Schema drift: the silent pipeline killer

▼

What it is

Schema drift occurs when the source system changes its schema (a column is renamed, a new column is added, a data type changes, or a column is dropped) without coordination with the data team. This is one of the most common causes of pipeline failures in production.

Strategies for handling it

Schema evolution: Table formats like Iceberg and Delta support adding, renaming, and reordering columns without breaking existing readers
Schema contracts: Formal agreements between producers and consumers about what changes are allowed
Schema registry: Centralized service (Confluent Schema Registry) that validates event schemas before they reach the pipeline
Defensive ingestion: Land raw data first (schema-on-read), then validate during transformation

Data contracts: the solution

▼

What it is

A data contract is a formal agreement between a data producer and consumer that specifies the schema, semantics, quality expectations, SLAs, ownership, and change management process. It is like an API contract for data.

What a contract includes

Schema: column names, types, nullable constraints
Semantics: what each field means (business glossary)
Quality expectations: freshness, completeness, uniqueness
SLA: delivery schedule, latency, availability
Owner: who is responsible for maintaining the contract
Change policy: how breaking changes are communicated and versioned

Ingestion Tools

The modern connector ecosystem

Interactive: Compare ingestion tools

Select a tool to see its strengths, limitations, and best use case.

Airbyte: Open-source EL(T) platform

Type: Open-source, self-hosted or cloud. Strengths: 300+ pre-built connectors, UI-based configuration, supports CDC for databases, community-driven. Best for: Teams that want managed connectors without vendor lock-in. Limitation: Some connectors are community-maintained with varying quality. Not designed for real-time streaming.

Typical ingestion architecture

OLTP DB

SaaS API

Files

Events

↓

Ingestion Layer
Airbyte / Fivetran / dlt / Kafka Connect

↓

Bronze / Raw Landing Zone
S3 / ADLS / GCS (Parquet, JSON)

Knowledge Check: Module 2

1. Which ingestion pattern captures deletes with minimal impact on the source database?

CDC reads the database transaction log to capture all inserts, updates, and deletes. It has minimal source impact because it reads the log rather than running queries against the production database.

2. What is the main limitation of incremental loading with a watermark?

Incremental loads query WHERE updated_at > last_watermark. If a row is physically deleted from the source, there is no update to the watermark column, so the delete goes undetected unless the source uses soft deletes.

3. A source team renames a column without telling the data team. What problem did this cause?

An uncoordinated schema change is schema drift. It can cause pipeline failures (column not found), silent data loss (null values where data should exist), or incorrect transformations.

4. What is the purpose of a data contract?

A data contract is a formal agreement about what the data looks like, what quality it must meet, how often it arrives, who owns it, and how changes are communicated. It prevents schema drift and quality surprises.

5. When is streaming ingestion the right choice over batch?

Streaming is warranted when data arrives continuously without a natural batch boundary and the use case demands low-latency processing (fraud detection, real-time dashboards, IoT). Monthly reports and reference data are perfectly served by batch.