There Are 5 Data Ingestion Patterns — And Choosing Wrong Will Cost You Months

Most engineers don’t think about data ingestion until something breaks.

A dashboard loads slowly. A report shows yesterday’s numbers. A real-time fraud alert fires 20 minutes too late.

And then someone asks:

“How is our data actually getting into the system?”

**Read for free here**

That question — how data moves from where it’s born to where it’s useful — is data ingestion. And the pattern you choose decides almost everything downstream: cost, freshness, complexity, reliability.

The tricky part? There’s no single right answer.

Let’s walk through five patterns using one system that grows from a garage startup to a scaled platform.

Data Ingestion Patterns

Our Example: A Food Delivery Startup

Imagine you’re building BiteRunner — a food delivery app.

Your core data looks like this:

orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    restaurant_id INT,
    status TEXT,
    total_amount DECIMAL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
)

This single table feeds:

• revenue dashboards
• driver dispatch systems
• restaurant analytics
• customer notifications
• fraud detection

As BiteRunner grows, the way you move this data will evolve — sometimes painfully.

Pattern #1 — Full Load Ingestion

The “copy everything” approach

BiteRunner just launched. Three restaurants. Forty orders a day. One engineer doing everything.

The simplest possible ingestion:

import pandas as pd
from sqlalchemy import create_engine

source = create_engine("postgresql://biterunner_prod")
warehouse = create_engine("postgresql://biterunner_warehouse")
# Just grab everything
df = pd.read_sql("SELECT * FROM orders", source)
# Overwrite the warehouse table
df.to_sql("orders", warehouse, if_exists="replace", index=False)
print(f"Loaded {len(df)} orders")

Schedule this with cron. Run it every hour. Done.

Why engineers start here

Because it’s:

• dead simple
• impossible to miss a record
• easy to debug (“just look at the table”)
• no state to track, no watermarks, no offsets

When your table has 500 rows, this takes two seconds.

Then BiteRunner gets popular

Six months later. 200 restaurants. 50,000 orders per day. Millions of historical rows.

Now this same script:

• takes 45 minutes to run
• hammers the production database
• costs real money in warehouse compute
• overwrites fresh data with a stale snapshot every cycle

The query that used to take 2 seconds now takes 2,700 seconds.

When full load still makes sense

Don’t dismiss it entirely. Full load is the right choice when:

+-----------------------------------------------------------------------+
| Scenario                       | Why full load works                  |
| ------------------------------ | ------------------------------------ |
| Small reference tables         | hundreds of rows                     |
| Lookup data (country codes)    | rarely changes                       |
| Weekly compliance snapshots    | completeness matters more than speed |
| Source has no timestamp column | no way to track changes              |
+-----------------------------------------------------------------------+

The rule: If the entire table fits in memory comfortably and correctness matters more than speed — full load is fine. Stop optimizing what doesn’t need it.

Pattern #2 — Incremental Ingestion

The “only grab what changed” approach

BiteRunner’s engineer realizes: most orders don’t change between runs. Why copy a million rows when only 200 are new?

import pandas as pd
from sqlalchemy import create_engine
import json
from pathlib import Path

source = create_engine("postgresql://biterunner_prod")
warehouse = create_engine("postgresql://biterunner_warehouse")
# Load the last bookmark
state_file = Path("ingestion_state.json")
if state_file.exists():
    state = json.loads(state_file.read_text())
    last_loaded = state["last_updated_at"]
else:
    last_loaded = "1970-01-01"
# Only fetch what changed
query = f"""
    SELECT * FROM orders
    WHERE updated_at > '{last_loaded}'
    ORDER BY updated_at
"""
df = pd.read_sql(query, source)
if not df.empty:
    # Append new/changed rows
    df.to_sql("orders_staging", warehouse, if_exists="replace", index=False)
    # Merge into final table
    merge_query = """
        INSERT INTO orders
        SELECT * FROM orders_staging
        ON CONFLICT (order_id)
        DO UPDATE SET
            status = EXCLUDED.status,
            total_amount = EXCLUDED.total_amount,
            updated_at = EXCLUDED.updated_at
    """
    with warehouse.connect() as conn:
        conn.execute(merge_query)
    # Save new bookmark
    new_bookmark = str(df["updated_at"].max())
    state_file.write_text(json.dumps({"last_updated_at": new_bookmark}))
    print(f"Incrementally loaded {len(df)} changed orders")
else:
    print("No new changes")

Why this is a massive improvement

What used to scan millions of rows now touches hundreds.

Full load:    SELECT * FROM orders          → 2,000,000 rows
Incremental:  SELECT * WHERE updated > X    → 347 rows

The production database barely notices. Warehouse costs drop. Runs in seconds instead of minutes.

Where incremental ingestion breaks

Problem 1: Deletes are invisible

A customer requests data deletion (GDPR). The row disappears from the source. Your incremental query never sees it. The warehouse keeps the ghost forever.

Problem 2: Your bookmark is fragile

# What happens if this file gets deleted?
state_file = Path("ingestion_state.json")

Lost bookmark = you either re-process everything or miss a gap of data.

Problem 3: Late-arriving data

An order is created at 10:08 inside a slow database transaction. It commits at 10:13. But updated_at says 10:08.

Your job ran at 10:10 and saved the watermark as 10:10.

Next run: WHERE updated_at > '10:10'

That order — with updated_at = 10:08 — is gone forever. No error. No warning. Just silently missing revenue.

When to use incremental

• Your table has millions of rows
• You have a reliable updated_at or auto-incrementing column
• Deletions are rare or handled separately
• Near-real-time freshness isn’t critical (every 5–15 minutes is fine)

Most batch data pipelines in production use this pattern. It’s the workhorse.

Pattern #3 — Streaming Ingestion

The “push as it happens” approach

BiteRunner is now in 50 cities. Drivers need real-time dispatch. Customers want live order tracking. Fraud detection can’t wait 15 minutes.

Batch won’t cut it anymore.

                  ┌──────────────┐
                  │  Order Event │
                  └──────┬───────┘
                         │
                         ▼
               ┌─────────────────┐
               │   Kafka Topic   │
               │  "order-events" │
               └────────┬────────┘
                        │
          ┌─────────────┼─────────────┐
          │             │             │
          ▼             ▼             ▼
    ┌──────────┐   ┌───────────┐   ┌──────────┐
    │ Warehouse│   │  Fraud    │   │  Driver  │
    │  Sink    │   │  Detector │   │ Dispatch │
    └──────────┘   └───────────┘   └──────────┘

The producer — your order service:

from kafka import KafkaProducer
import json
from datetime import datetime

producer = KafkaProducer(
    bootstrap_servers="localhost:9092",
    value_serializer=lambda v: json.dumps(v).encode("utf-8")
)
def publish_order_event(order_id, status, amount):
    event = {
        "order_id": order_id,
        "status": status,
        "total_amount": float(amount),
        "event_time": datetime.utcnow().isoformat(),
        "event_type": "ORDER_UPDATED"
    }
    producer.send("order-events", value=event)
    producer.flush()
# Called from your order service
publish_order_event(order_id=42, status="CONFIRMED", amount=28.50)

The consumer — writing to your warehouse:

from kafka import KafkaConsumer
import json

consumer = KafkaConsumer(
    "order-events",
    bootstrap_servers="localhost:9092",
    group_id="warehouse-consumer",
    auto_offset_reset="earliest",
    value_deserializer=lambda v: json.loads(v.decode("utf-8"))
)
for message in consumer:
    event = message.value
    if event["event_type"] == "ORDER_UPDATED":
        upsert_to_warehouse(event)
        print(f"Ingested order {event['order_id']} - {event['status']}")

What changes with streaming

| Aspect                  | Batch Processing                     | Streaming Processing                 |
| ----------------------- | ------------------------------------ | ------------------------------------ |
| Data Freshness          | Minutes to hours                     | Seconds or near real-time            |
| Risk of Missing Records | Possible if jobs fail                | Very unlikely with proper guarantees |
| Infrastructure          | Scheduled jobs (Cron, Airflow, etc.) | Kafka, Kinesis, Pulsar, consumers    |
| Operational Complexity  | Relatively low                       | Significantly higher                 |
| Cost Model              | Compute consumed during runs         | Infrastructure runs continuously     |

Where streaming ingestion breaks

Problem 1: Duplicates are guaranteed

Kafka provides at-least-once delivery. Your consumer will see events more than once.

ORDER_CONFIRMED → ✓
ORDER_CONFIRMED → ✓ (duplicate — network hiccup)
ORDER_CONFIRMED → ✓ (duplicate — consumer rebalance)

Without deduplication, your revenue dashboard says $85.50 instead of $28.50.

Fix: make your consumer idempotent.

def upsert_to_warehouse(event):
    """
    Uses order_id as key + event_time for ordering.
    Safe to call multiple times with the same event.
    """
    query = """
        INSERT INTO orders (order_id, status, total_amount, updated_at)
        VALUES (%s, %s, %s, %s)
        ON CONFLICT (order_id) DO UPDATE
        SET status = EXCLUDED.status,
            total_amount = EXCLUDED.total_amount,
            updated_at = EXCLUDED.updated_at
        WHERE EXCLUDED.updated_at > orders.updated_at
    """
    execute(query, (
        event["order_id"],
        event["status"],
        event["total_amount"],
        event["event_time"]
    ))

Problem 2: Out-of-order events

Network delays can shuffle events. A DELIVERED event might arrive before CONFIRMED.

Without timestamp guards, your warehouse thinks the order went backwards.

Problem 3: Backfill is painful

Streaming captures future events. What about the 2 million historical orders that existed before you set up Kafka?

You still need a one-time batch load. Then you stitch streaming on top. Getting that cutover right — without duplicates or gaps — is where most teams spend weeks.

When to use streaming

• Sub-second or sub-minute freshness is a real requirement
• Multiple downstream systems need the same events
• You already have (or are ready to invest in) streaming infrastructure
• The data is naturally event-shaped (clicks, transactions, status changes)

Pattern #4 — Event-Driven Ingestion

The “react to signals” approach

Not everything fits neatly into “poll on a schedule” or “stream continuously.”

BiteRunner now receives:

• restaurant menu updates as CSV uploads to S3
• partner invoices as PDFs dropped into a shared folder
• promotional pricing files from a third-party API on unpredictable schedules

These don’t arrive at fixed intervals. They arrive when they arrive.

Restaurant uploads CSV
            │
            ▼
   ┌─────────────────┐
   │   S3 Bucket     │
   │  /menu-uploads/ │
   └────────┬────────┘
            │ (S3 event notification)
            ▼
   ┌──────────────────┐
   │  Lambda / Cloud  │
   │  Function        │
   └────────┬─────────┘
            │
            ▼
   ┌──────────────────┐
   │  Parse + Load    │
   │  into Warehouse  │
   └──────────────────┘

import boto3
import pandas as pd
from io import StringIO

def handle_s3_event(event, context):
    """Triggered automatically when a file lands in S3."""
    s3 = boto3.client("s3")
    bucket = event["Records"][0]["s3"]["bucket"]["name"]
    key = event["Records"][0]["s3"]["object"]["key"]
    print(f"New file detected: s3://{bucket}/{key}")
    # Download and parse
    response = s3.get_object(Bucket=bucket, Key=key)
    content = response["Body"].read().decode("utf-8")
    df = pd.read_csv(StringIO(content))
    # Validate before loading
    required_columns = {"item_id", "restaurant_id", "price", "item_name"}
    if not required_columns.issubset(df.columns):
        print(f"REJECTED: {key} - missing columns: {required_columns - set(df.columns)}")
        move_to_quarantine(bucket, key)
        return
    # Clean and load
    df["ingested_at"] = pd.Timestamp.utcnow()
    df["source_file"] = key
    load_to_warehouse(df, table="menu_items")
    print(f"Loaded {len(df)} menu items from {key}")

Why event-driven is different from streaming

People confuse these constantly. Here’s the distinction:

Streaming:      continuous flow of small events
                → Kafka consumer running 24/7

Event-driven:   triggered by an external signal
                → function wakes up, processes, goes back to sleep

Streaming is a firehose. Event-driven is a doorbell.

Where event-driven ingestion breaks

Problem 1: Exactly-once is nearly impossible

S3 might send duplicate notifications. Your function might time out and retry. Now the same CSV gets loaded twice.

Fix: track processed files.

def already_processed(file_key):
    """Check if we've already ingested this file."""
    result = query("SELECT 1 FROM ingestion_log WHERE source_file = %s", (file_key,))
    return len(result) > 0

def mark_processed(file_key, row_count):
    execute(
        "INSERT INTO ingestion_log (source_file, rows_loaded, processed_at) VALUES (%s, %s, %s)",
        (file_key, row_count, datetime.utcnow())
    )

Problem 2: Poison files

One malformed CSV crashes your function. The event retries. The function crashes again. Infinite loop.

Fix: dead-letter queues and quarantine buckets. After 3 failures, move the file aside and alert someone.

Problem 3: No built-in ordering

Two menu update files arrive one minute apart. Cloud functions spin up in parallel. The older file finishes after the newer one — overwriting fresh prices with stale ones.

When to use event-driven

• Data arrives as files (CSV, JSON, Parquet) on unpredictable schedules
• External systems push to you (webhooks, S3 uploads, FTP drops)
• You want to pay only when data actually arrives (serverless cost model)
• Processing is independent per file — no cross-file dependencies

Pattern #5 — Hybrid Ingestion (Lambda Architecture)

The “why not both” approach

BiteRunner is now a serious company. Millions of orders. Hundreds of restaurants. Real-time fraud detection and month-end financial reports.

Here’s the uncomfortable truth:

No single ingestion pattern handles everything.

Real-time streaming gives you speed but fights with completeness. Batch gives you completeness but can’t deliver speed.

Hybrid says: use both, then reconcile.

              ┌─────────────────────┐
              │   Order Service     │
              └──────────┬──────────┘
                         │
              ┌──────────┴──────────┐
              │                     │
              ▼                     ▼
    ┌──────────────────┐  ┌───────────────────┐
    │  Kafka Stream    │  │  Database Table   │
    │  (real-time)     │  │  (source of truth)│
    └────────┬─────────┘  └────────┬──────────┘
             │                     │
             ▼                     ▼
    ┌──────────────────┐  ┌──────────────────┐
    │  Speed Layer     │  │  Batch Layer     │
    │  (seconds-old)   │  │  (nightly)       │
    └────────┬─────────┘  └────────┬─────────┘
             │                     │
             └──────────┬──────────┘
                        │
                        ▼
              ┌──────────────────┐
              │  Serving Layer   │
              │  (reconciled)    │
              └──────────────────┘

The speed layer — handles real-time:

from kafka import KafkaConsumer
import json

consumer = KafkaConsumer(
    "order-events",
    bootstrap_servers="localhost:9092",
    group_id="speed-layer",
    value_deserializer=lambda v: json.loads(v.decode("utf-8"))
)
for message in consumer:
    event = message.value
    # Write to a "fast" table - approximate but fresh
    upsert_to_realtime_table(event)

The batch layer — runs nightly, ensures completeness:

import pandas as pd
from sqlalchemy import create_engine

source = create_engine("postgresql://biterunner_prod")
warehouse = create_engine("postgresql://biterunner_warehouse")
def nightly_batch_load():
    """Full authoritative load - corrects any streaming inconsistencies."""
    df = pd.read_sql("""
        SELECT * FROM orders
        WHERE updated_at >= CURRENT_DATE - INTERVAL '2 days'
    """, source)
    # Load into batch table
    df.to_sql("orders_batch_staging", warehouse, if_exists="replace", index=False)
    # Reconcile: batch layer wins on conflicts
    reconcile_query = """
        MERGE INTO orders_final t
        USING orders_batch_staging s
        ON t.order_id = s.order_id
        WHEN MATCHED AND s.updated_at >= t.updated_at THEN
            UPDATE SET
                status = s.status,
                total_amount = s.total_amount,
                updated_at = s.updated_at
        WHEN NOT MATCHED THEN
            INSERT (order_id, status, total_amount, updated_at)
            VALUES (s.order_id, s.status, s.total_amount, s.updated_at)
    """
    execute(reconcile_query)
    print(f"Batch reconciled {len(df)} orders")
nightly_batch_load()

The serving logic — decides which layer to trust:

def get_order(order_id):
    """
    Check real-time table first for freshness.
    Fall back to batch table for completeness.
    """
    realtime = query("SELECT * FROM orders_realtime WHERE order_id = %s", (order_id,))
    batch = query("SELECT * FROM orders_batch WHERE order_id = %s", (order_id,))

    if realtime and batch:
            # Trust whichever is newer
            if realtime["updated_at"] > batch["updated_at"]:
                return realtime
            return batch

        return realtime or batch

Why hybrid exists

Because business requirements conflict:

| Team             | What They Care About Most                           |
| ---------------- | --------------------------------------------------- |
| Fraud Detection  | Detecting suspicious activity in near real-time     |
| Finance          | Perfectly accurate month-end reporting              |
| Customer Support | Access to the latest customer and order information |
| Data Science     | A complete and reliable historical dataset          |

No single pattern satisfies all four.

Where hybrid breaks

Problem 1: You’re running two pipelines now.

Double the infrastructure. Double the monitoring. Double the on-call pages at 3 AM.

Problem 2: Reconciliation logic gets complicated fast.

Which layer wins? What about events that exist in streaming but not in batch? What about the reverse?

Every edge case becomes a judgment call.

Problem 3: Teams get confused about which table to query.

“Is orders_realtime the one I should use, or orders_final?"

Without clear documentation, people query the wrong table and get wrong answers.

When to use hybrid

• You genuinely need both real-time and batch correctness
• Different consumers have fundamentally different freshness requirements
• You have the engineering capacity to maintain two parallel paths
• The cost of being wrong (in either direction) justifies the complexity

Choosing the Right Pattern

Most engineers overthink this. Start with the simplest pattern that meets your actual requirements — not the requirements you imagine you’ll have in two years.

                Do you need real-time?
                         │
                ┌────────┴───────┐
                │                │
               Yes               No
                │                │
                ▼                ▼
           Multiple         Is the source
           consumers?       table small?
              │                  │
        ┌─────┴─────┐      ┌─────┴─────┐
        │           │      │           │
       Yes         No     Yes          No
        │           │      │           │
        ▼           ▼      ▼           ▼
    Streaming   Event     Full     Incremental
               Driven     Load      Load

And if you need both real-time and batch guarantees? That’s when hybrid earns its complexity.

+------------------------------------------------+
| Pattern       | Freshness | Complexity | Cost   |
| ------------- | --------- | ---------- | ------ |
| Full Load     | Hours     | Very Low   | High*  |
| Incremental   | Minutes   | Low        | Low    |
| Streaming     | Seconds   | High       | Medium |
| Event-Driven  | On signal | Medium     | Low**  |
| Hybrid        | Seconds   | Very High  | High   |
+------------------------------------------------+

*  at scale   
** serverless = pay per invocation

What Senior Engineers Eventually Learn

Data ingestion is not a technology choice. It’s a tradeoff negotiation.

Every pattern optimises for something and sacrifices something else:

• Full load optimizes for simplicity — sacrifices efficiency
• Incremental optimizes for efficiency — sacrifices completeness
• Streaming optimizes for freshness — sacrifices simplicity
• Event-driven optimizes for responsiveness — sacrifices ordering
• Hybrid optimizes for coverage — sacrifices your weekends

The best data engineers don’t pick the “best” pattern.

They pick the cheapest pattern that won’t wake them up at night.

And then – only when that pattern actually breaks under real production pressure — they evolve to the next one.

References:

https://airbyte.com/data-engineering-resources/data-ingestion-architecture

https://estuary.dev/blog/data-ingestion-pipeline