Data Ingestion Guide

Data Engineering Data Engineering 7 min read 1472 words Beginner ExcellentWiki Editorial Team

Data ingestion is the first step in any data pipeline. Get this wrong, and everything downstream is unreliable.

Ingestion Modes

Batch Ingestion

Data is collected and loaded in scheduled intervals (hourly, daily, nightly).

Pros	Cons
Simple to implement	Data delay (not real-time)
Easy error recovery	Requires scheduling infrastructure
Cost-effective (resource-efficient)	Hard to handle late-arriving data
Well-understood tools

Best for: Historical data, aggregated reporting, non-time-critical analytics.

Streaming Ingestion

Data is ingested continuously, with sub-second latency.

Pros	Cons
Real-time insights	Complex infrastructure
No batch windows	Harder error recovery
Handles late data naturally	More expensive

Best for: Real-time dashboards, fraud detection, IoT sensors, monitoring.

Micro-batch

Small batches every few seconds to minutes (Spark Streaming, Flink).

Best for: Near-real-time without full streaming complexity.

Ingestion Sources

Databases

Technique	Latency	Complexity	Use Case
JDBC/ODBC pull	Minutes-hours	Low	Small tables, periodic sync
SQL dump + load	Hours	Low	Full database snapshots
Change Data Capture (CDC)	Seconds	High	Real-time sync, minimal load

Change Data Capture (CDC)

Captures database changes as they happen.

Methods:

Log-based (Debezium): Reads WAL (Write-Ahead Log). No performance impact.
Trigger-based: Database triggers on INSERT/UPDATE/DELETE. Adds load.
Timestamp-based: Query rows where updated_at > last_run. Simple but misses deletes.

-- Timestamp-based CDC
SELECT * FROM orders
WHERE updated_at > last_checkpoint;

Files

File Type	Best For
CSV	Simple, universal. No schema support.
JSON	Nested data, API payloads
Parquet	Columnar, analytics, best compression
Avro	Row-based, schema evolution, streaming
ORC	Columnar, Hive/Presto

Rule: For production data lakes, use Parquet or Avro. CSV is for ad-hoc analysis.

APIs

Making API calls to external or internal services:

# Example: paginated API ingestion
def fetch_all_pages(base_url, api_key):
    page = 1
    while True:
        resp = requests.get(
            f"{base_url}?page={page}",
            headers={"Authorization": f"Bearer {api_key}"}
        )
        data = resp.json()
        if not data["results"]:
            break
        yield data["results"]
        page += 1

API Type	Complexity	Rate Limits	Reliability
REST	Low	Common	Good
GraphQL	Medium	Variable	Good
Webhooks	Low	None	Event-driven
gRPC	High	Configurable	Best

Ingestion Patterns

Full Load

Copy the entire source every time.

Source → [SELECT * FROM table] → Target

Pros: Simple, ensures complete data
Cons: Slow for large tables, wastes bandwidth
When: Small dimension tables (< 100K rows)

Incremental Load

Copy only new/changed data.

Source → [WHERE updated_at > last_run] → Target

Pros: Fast, efficient
Cons: Misses deletes, requires timestamp column
When: Large fact tables, regular schedules

Upsert (Merge)

Apply changes to existing records.

Source → [INSERT OR UPDATE WHERE key matches] → Target

Pros: Handles changes, idempotent
Cons: More complex, can be slow
When: When data is updated in place

Ingestion Pipeline Architecture

Simple Pipeline (Batch)

Source DB → Python script (SQL export) → S3/GCS (CSV/Parquet) → Data warehouse

Orchestrated Pipeline

Source DB → Airflow (scheduler) → Spark/EMR (transform) → Data lake
                                       ↓
                              Incremental load to warehouse

Streaming Pipeline

DB CDC (Debezium) → Kafka → Flink/Spark Streaming → Kafka sink → Warehouse
                          ↓
                Real-time dashboard

Common Ingestion Tools

Batch vs Streaming Ingestion

Batch Ingestion

Batch ingestion processes data in discrete chunks at scheduled intervals. It is simpler to implement, easier to debug, and more cost-effective for large volumes. Common batch tools include Apache Airflow (orchestration), Spark (processing), and AWS Glue (serverless ETL).

Best for: Historical data, daily reporting, non-time-sensitive analytics, large file processing.

Streaming Ingestion

Streaming ingestion processes data in real-time as it arrives. It requires more infrastructure — message brokers (Kafka, Kinesis), stream processors (Flink, Spark Streaming), and state management.

Best for: Real-time monitoring, fraud detection, clickstream analytics, IoT data.

Change Data Capture (CDC)

CDC captures database changes in real-time without impacting source performance. Tools like Debezium (Kafka Connect) read the database transaction log (binlog, WAL) and stream changes to downstream systems:

PostgreSQL WAL → Debezium → Kafka → Flink → Data Lake

CDC enables real-time synchronization from operational databases to analytical systems with near-zero latency and minimal overhead on the source database.

Ingestion Formats

Choose data formats carefully based on use case:

Parquet — Columnar format optimized for analytics. Compresses well (3-5x reduction). Best for data lakes and analytical queries.
Avro — Row-based format with embedded schema. Best for streaming and Kafka.
JSON — Flexible but verbose. Best for semi-structured data and API responses.
CSV — Universally readable but no schema enforcement. Best for simple exports.

Schema Evolution

Data schemas change over time. Ingestion pipelines must handle new columns, deleted columns, changed data types, and renamed fields. Avro and Parquet support schema evolution with defined rules (adding nullable columns is safe, removing columns is breaking). Use schema registries (Confluent Schema Registry, AWS Glue Schema Registry) to enforce compatibility rules across producers and consumers.

FAQ

What is data engineering? Data engineering builds infrastructure and pipelines to collect, store, and process data at scale. It involves ETL/ELT processes, data warehousing, streaming systems, and ensuring data quality and accessibility for analytics and machine learning.

What is the difference between ETL and ELT? ETL (Extract-Transform-Load) transforms data before loading into the target system. ELT (Extract-Load-Transform) loads raw data first and transforms in-place. ELT leverages modern data warehouses like Snowflake and BigQuery for compute power.

What is a data pipeline? A data pipeline moves data from source systems to destinations, applying transformations along the way. Pipelines can be batch (scheduled intervals) or streaming (continuous processing). Tools include Airflow, dbt, and Spark.

Should I use a data lake or a data warehouse? Data lakes store raw data in native formats (cheap, flexible). Data warehouses store structured, curated data optimized for analytics (faster, more expensive). Modern architectures use both — lakehouse architecture combines their strengths.

What is the role of a data catalog? A data catalog indexes and documents available data assets — their schemas, lineage, quality metrics, and ownership. It helps data consumers discover, understand, and trust the data they work with.

Batch vs Streaming Ingestion

Batch Ingestion

Best for: Historical data, daily reporting, non-time-sensitive analytics, large file processing.

Streaming Ingestion

Streaming ingestion processes data in real-time as it arrives. It requires more infrastructure — message brokers (Kafka, Kinesis), stream processors (Flink, Spark Streaming), and state management.

Best for: Real-time monitoring, fraud detection, clickstream analytics, IoT data.

Change Data Capture (CDC)

PostgreSQL WAL → Debezium → Kafka → Flink → Data Lake

CDC enables real-time synchronization from operational databases to analytical systems with near-zero latency and minimal overhead on the source database.

Ingestion Formats

Choose data formats carefully based on use case:

Parquet — Columnar format optimized for analytics. Compresses well (3-5x reduction). Best for data lakes and analytical queries.
Avro — Row-based format with embedded schema. Best for streaming and Kafka.
JSON — Flexible but verbose. Best for semi-structured data and API responses.
CSV — Universally readable but no schema enforcement. Best for simple exports.

Schema Evolution

Ingestion Schema Management

Schema-on-Read

Don’t enforce schema at ingestion. Apply it when reading.

Pros: Flexible, ingests anything
Cons: Downstream queries can break

Schema-on-Write

Enforce schema during ingestion.

Pros: Data quality enforced early
Cons: Rejects messy data

Best approach: Schema evolution support (Avro, Parquet) with validation warnings, not failures.

Monitoring Ingestion

Metric	What It Tells You
Rows ingested	Volume
Ingestion latency	Timeliness
Error rate	Data quality
Duplicate rate	Reliability
Schema drift	Source changes
Freshness (max timestamp)	Pipeline health

Set up alerts: If no data arrives for 2x the expected interval, notify.

Ingestion Anti-Patterns

Anti-Pattern	Problem	Fix
Querying production DB directly	Load, risk of crash	Read replica or CDC
No error handling	Silent data loss	Dead letter queue, alerts
One pipeline for everything	Complexity, brittleness	Separate pipelines per source
Loading without validation	Garbage in, garbage out	Row counts, null checks
Hardcoded credentials	Security risk	Secrets manager