Designing a Data Pipeline: ETL vs ELT, Batch vs Streaming, and Orchestration Patterns for Production

Most data pipeline failures are not caused by bad code. They are caused by implicit assumptions: the source schema is stable, records arrive in order, the job will finish in under an hour, backfills are rare. When those assumptions break, and they always do, the pipeline either silently produces wrong numbers or stops producing anything at all.

This article covers the structural decisions behind production data pipelines: extraction strategy (ETL vs ELT), processing model (batch vs streaming vs hybrid), orchestration tooling, and the cross-cutting concerns that determine whether the system holds up under load, schema changes, and operational pressure.

---

ETL vs ELT: Not About Tools, About Where Transformation Happens

The classic framing is that ETL transforms data before loading it into the destination, while ELT loads raw data first and transforms it inside the destination. The more useful framing is about where your compute budget lives.

ETL makes sense when:

• The destination has limited compute (a relational OLTP database, a third-party SaaS system).
• You need to enforce a strict schema at the boundary between systems.
• Privacy rules require masking or dropping fields before data ever lands in a downstream store.

ELT makes sense when:

• The destination is a warehouse or lakehouse (BigQuery, Snowflake, Databricks, DuckDB) that is optimized for large transformation queries.
• Source fidelity matters: you want the raw event log preserved so you can reprocess it later when business logic changes.
• Your transformations are defined in SQL by analysts, not in application code.

The operational difference matters more than the acronym. ETL code that runs in a custom application requires versioning, deployment, and monitoring at the application layer. ELT SQL that runs in dbt runs inside your warehouse and can be versioned in Git alongside the query definitions. Reprocessing an ELT pipeline after a bug fix means running dbt run --select affected_models. Reprocessing an ETL pipeline means redeploying application code, replaying source data, and coordinating with every downstream consumer.

For greenfield pipelines feeding a modern warehouse, start with ELT. The raw layer is your insurance policy.

---

Batch Processing

Batch is the default for a reason: it is simple to reason about, cheap to operate, and tolerates failures well because jobs are idempotent by design (or should be).

A typical batch ingestion job:

1. Reads records from a source (database CDC snapshot, S3 files, API paginator).
2. Writes them to a staging table or object store partition.
3. Merges or upserts into the final table.
4. Records the watermark (last processed timestamp or offset) for the next run.

interface BatchIngestionConfig {
  sourceTable: string;
  destinationTable: string;
  watermarkColumn: string;
  batchSize: number;
}

interface WatermarkRecord {
  jobName: string;
  lastProcessedAt: Date;
  lastOffset: bigint;
}

async function runBatchIngestion(
  config: BatchIngestionConfig,
  db: DatabaseClient,
  warehouse: WarehouseClient
): Promise<void> {
  // Read the last committed watermark
  const watermark = await db.queryOne<WatermarkRecord>(
    `SELECT last_processed_at, last_offset
     FROM pipeline_watermarks
     WHERE job_name = $1`,
    [config.sourceTable]
  );

  const since = watermark?.lastProcessedAt ?? new Date(0);

  // Paginate through source records since last watermark
  let offset = 0;
  let totalIngested = 0;

  while (true) {
    const rows = await db.query(
      `SELECT * FROM ${config.sourceTable}
       WHERE updated_at > $1
       ORDER BY updated_at ASC, id ASC
       LIMIT $2 OFFSET $3`,
      [since, config.batchSize, offset]
    );

    if (rows.length === 0) break;

    await warehouse.upsert(config.destinationTable, rows, {
      conflictKey: "id",
    });

    offset += rows.length;
    totalIngested += rows.length;
  }

  // Commit watermark only after all pages succeed
  await db.execute(
    `INSERT INTO pipeline_watermarks (job_name, last_processed_at)
     VALUES ($1, NOW())
     ON CONFLICT (job_name) DO UPDATE SET last_processed_at = EXCLUDED.last_processed_at`,
    [config.sourceTable]
  );

  console.log(`Ingested ${totalIngested} rows from ${config.sourceTable}`);
}

A few things to get right from the start:

Watermarks must be committed after, not before, the write. If you update the watermark first and the load fails, you lose those records permanently. If the load succeeds and the watermark commit fails, you re-ingest and hit the upsert conflict key. The second failure mode is safe; the first is not.

The updated_at column is only reliable if your source sets it on every write. If it is missing or inconsistently populated, use change data capture (CDC) via Debezium or your database’s logical replication slot instead of polling.

Batch jobs fail at scale on large ranges. If a batch job that normally processes 50,000 rows tries to catch up after a three-day outage and needs to process 10 million, it will either time out or OOM. Build range chunking: split the watermark range into hourly or daily sub-ranges and process them sequentially.

Where batch breaks down: when the acceptable data latency drops below your batch interval. A five-minute cron job means up to five minutes of lag in the destination. For dashboards that is fine. For fraud detection or inventory management, it is not.

---

Stream Processing

Streaming processes records as they arrive. The two properties that make streaming tractable in production are partitioning and offset management.

Kafka is the standard event backbone. Each topic partition is an ordered, immutable log. A consumer group reads from partitions and commits offsets. If the consumer fails and restarts, it replays from the last committed offset. This gives you at-least-once delivery by default.

import { Kafka, Consumer, EachMessagePayload } from "kafkajs";

interface OrderEvent {
  orderId: string;
  userId: string;
  totalCents: number;
  occurredAt: string;
  receivedAt?: string; // set by consumer, not producer
}

async function startOrderConsumer(
  kafka: Kafka,
  warehouse: WarehouseClient
): Promise<void> {
  const consumer: Consumer = kafka.consumer({
    groupId: "order-pipeline-v2",
  });

  await consumer.connect();
  await consumer.subscribe({ topic: "orders", fromBeginning: false });

  await consumer.run({
    // Process one message at a time to keep offset semantics simple
    eachMessage: async ({ topic, partition, message }: EachMessagePayload) => {
      const raw = message.value?.toString();
      if (!raw) return;

      const event: OrderEvent = JSON.parse(raw);
      event.receivedAt = new Date().toISOString();

      // Upsert with idempotency key to handle redelivery
      await warehouse.upsert("orders", [event], {
        conflictKey: "orderId",
      });
    },
  });
}

At-least-once means a record may be processed more than once (network partition causes the consumer to restart before committing the offset). Your write path must be idempotent. ON CONFLICT (order_id) DO NOTHING or DO UPDATE are the standard patterns.

Exactly-once is achievable in Kafka with transactions (producer.transaction() + atomic offset commit), but the operational overhead is significant. Most teams tolerate at-least-once with idempotent writes and validate deduplication via count comparisons between the source topic and the destination table.

Out-of-order events are the second hard problem. Events from mobile clients arrive with production timestamps that reflect when the client wrote to disk, not when the server received them. You will see events with occurredAt values 30+ minutes in the past arriving on a live Kafka topic. Log the skew (receivedAt - occurredAt) and alert when it exceeds your SLA, but accept the event. Rejecting late events creates gaps that are harder to explain than late records.

For stateful stream processing (windowed aggregations, joins across streams, sessionization), Apache Flink is the standard. Flink’s watermark mechanism handles out-of-order events with configurable allowed lateness. The operational cost is high: managing Flink clusters, checkpointing state to object storage, and tuning parallelism for throughput is a non-trivial infrastructure investment. Evaluate whether a materialized view in your warehouse (refreshed every 60 seconds) covers your use case before adopting Flink.

---

Hybrid Architectures: Lambda and Kappa

The Lambda architecture arose from the observation that batch and streaming have complementary failure modes: batch is accurate but slow, streaming is fast but harder to reprocess. Lambda solves this by maintaining both paths in parallel and merging results at query time.

Sources
  |
  +---> Kafka -------> Stream layer (Flink / Spark Streaming) --> Serving layer
  |                                                                    |
  +---> S3 / HDFS ---> Batch layer (Spark / dbt) ------------------> merge at query

The serving layer combines the batch output (accurate, up to N hours old) with the streaming output (approximate, seconds old) by taking the batch result as the base and overlaying stream results for the recent window.

Lambda’s problem is operational complexity. You maintain two codebases for the same transformation logic. When business logic changes, you must update both paths and coordinate their outputs. Teams consistently underestimate this cost.

The Kappa architecture eliminates the batch layer. Everything is a stream. Historical reprocessing is handled by replaying the Kafka topic from offset zero with a new consumer group. This works when:

• Your Kafka retention is long enough to cover the reprocessing window (or you archive to S3 and replay via a tool like Apache Kafka tiered storage).
• Your stream processing framework supports at-scale replay without affecting production consumers.

For most teams, a pragmatic hybrid is more useful than either pure architecture: use batch (dbt + Airflow) for the cold layer (>1 hour old data, high accuracy) and a simple Kafka consumer for the hot layer (0-60 minutes, lower accuracy, powers operational dashboards). Reserve Flink for the handful of use cases that genuinely require stateful real-time computation.

---

Orchestration: Airflow, Dagster, and Temporal

Orchestration answers the question: which jobs run, in what order, with what dependencies, and what happens when they fail?

Apache Airflow is the most widely deployed. DAGs defined in Python, a rich UI for monitoring task status, and a large ecosystem of operators. Its weaknesses are well-known: the scheduler is a single point of failure, dynamic DAG generation is awkward, and the metadata database grows without pruning. Airflow is the right choice when your team already operates it and your pipeline graph is relatively static.

Dagster is designed around the concept of assets rather than tasks. Instead of scheduling a job that “runs the orders transform,” you declare that the orders_daily asset depends on the raw_orders asset, and Dagster materializes it when the upstream changes. This makes lineage tracking first-class, simplifies backfilling (materialize all upstream assets in topological order), and integrates cleanly with dbt. Dagster is the better default for new pipelines where asset lineage and data quality are priorities.

Temporal is not a data pipeline orchestrator in the traditional sense. It is a durable execution platform where workflows are arbitrary code with guaranteed execution semantics: a workflow function that blocks for two hours waiting on a poll result will survive process restarts. This makes Temporal useful for pipelines that involve long-running external API calls, human approval steps, or complex retry and compensation logic that DAG-based systems express awkwardly.

// Temporal workflow: ingest + validate + promote
import { defineWorkflow, proxyActivities, sleep } from "@temporalio/workflow";
import type * as activities from "./activities";

const { ingestBatch, runQualityChecks, promoteToProduction } =
  proxyActivities<typeof activities>({
    startToCloseTimeout: "30 minutes",
    retry: {
      maximumAttempts: 3,
      initialInterval: "1 minute",
      backoffCoefficient: 2,
    },
  });

export async function dailyIngestionWorkflow(date: string): Promise<void> {
  // Each activity is automatically retried on failure and survives worker restarts
  const stagedTable = await ingestBatch({ date, destination: "staging" });

  const qualityResult = await runQualityChecks({ table: stagedTable });

  if (!qualityResult.passed) {
    // Temporal handles the failure record, alerting, and compensation
    throw new Error(
      `Quality checks failed for ${date}: ${qualityResult.failures.join(", ")}`
    );
  }

  await promoteToProduction({ source: stagedTable, date });
}

The activity-level retry policy in Temporal is more expressive than Airflow’s task retry settings. You get per-activity timeout, backoff, and heartbeat configuration. The workflow state is durably stored; if the worker process dies mid-execution, Temporal replays the workflow history and resumes from the last completed activity. This is the right primitive for pipelines where the failure cost of re-running from the start is high.

---

Schema Evolution

Sources change their schema. New fields appear, types change, columns are removed. Your pipeline needs a strategy for each case.

Forward-compatible changes (adding nullable columns) should not break anything if your ingestion layer treats unknown fields with a defined policy: either drop them or write them to a catch-all JSON column. Never fail hard on an unexpected field.

Type changes are the real problem. A status column that changes from integer codes to string values will corrupt your destination table if you do not handle the transition. The patterns are:

• Maintain a raw text column alongside the typed column and parse at query time.
• Version the destination table schema (orders_v1, orders_v2) and backfill on cutover.
• Use a schema registry (Confluent Schema Registry for Kafka) to enforce compatibility before producers publish.

Column removal at the source is the hardest case. The column stops arriving and your pipeline either writes nulls or fails depending on how strict your schema validation is. Catch this with an anomaly check: if a previously non-null column is now null in >5% of records in a batch, alert before promoting to production.

---

Backfill Strategies

Backfills are necessary when you fix a bug in transformation logic, add a new field, or onboard historical data. The naive approach: drop the destination table and rerun. This works for small datasets and causes SLA violations for large ones.

Production backfill patterns:

Partition-based backfill. Write to a separate destination partition (e.g., date=2024-01-01) and swap the partition after validation. Downstream queries see the old data until the swap completes atomically.

Parallel consumer group replay. In Kafka, create a new consumer group with fromBeginning: true and a dedicated destination table suffix (orders_backfill_20240329). Run the backfill consumer in parallel with the production consumer. Merge and swap after the backfill catches up to the current offset.

Chunked historical load with priority queuing. Break the historical range into chunks (daily is usually right). Submit chunks to an orchestrator with low priority. The production incremental pipeline runs at normal priority. The backfill consumes capacity only when production is not using it.

Never run a backfill and a production incremental job against the same destination table without a locking mechanism. Concurrent upserts with overlapping keys produce non-deterministic results.

---

Data Quality Checks

Quality checks at the pipeline boundary are cheaper than quality issues discovered by a downstream analyst three days later. At minimum, verify these three properties on every batch before promoting to production:

Row count sanity. Compare the count of records loaded in this batch against a historical baseline. A 90% drop is almost certainly a pipeline bug, not a real business event.

Null rate on non-nullable fields. If user_id is null in 2% of records this batch and was null in 0.01% last week, something upstream changed.

Freshness. The maximum updated_at in the loaded batch should be within a defined window of NOW(). If the newest record is six hours old and your batch runs hourly, the source is not delivering data.

interface QualityCheck {
  name: string;
  query: string;
  threshold: number;
  comparisonOperator: "lt" | "gt" | "eq";
}

const standardChecks: QualityCheck[] = [
  {
    name: "row_count_minimum",
    query: `SELECT COUNT(*) as value FROM staging.orders WHERE batch_id = $1`,
    threshold: 100,
    comparisonOperator: "gt",
  },
  {
    name: "null_rate_user_id",
    query: `
      SELECT
        CAST(SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) as value
      FROM staging.orders
      WHERE batch_id = $1
    `,
    threshold: 0.01,
    comparisonOperator: "lt",
  },
  {
    name: "freshness_max_age_hours",
    query: `
      SELECT EXTRACT(EPOCH FROM (NOW() - MAX(updated_at))) / 3600 as value
      FROM staging.orders
      WHERE batch_id = $1
    `,
    threshold: 2,
    comparisonOperator: "lt",
  },
];

async function runQualityChecks(
  batchId: string,
  warehouse: WarehouseClient
): Promise<{ passed: boolean; failures: string[] }> {
  const failures: string[] = [];

  for (const check of standardChecks) {
    const result = await warehouse.queryOne<{ value: number }>(check.query, [
      batchId,
    ]);
    const value = result?.value ?? 0;

    const passed =
      check.comparisonOperator === "lt"
        ? value < check.threshold
        : check.comparisonOperator === "gt"
          ? value > check.threshold
          : value === check.threshold;

    if (!passed) {
      failures.push(`${check.name}: got ${value}, expected ${check.comparisonOperator} ${check.threshold}`);
    }
  }

  return { passed: failures.length === 0, failures };
}

---

Tradeoffs

Dimension	Batch (cron + SQL)	Streaming (Kafka + Flink)	Hybrid (Lambda/Kappa)
Data latency	Minutes to hours	Seconds	Seconds (hot) + hours (cold)
Operational complexity	Low	High	Very high
Reprocessing	Simple (re-run job)	Complex (replay + new consumer group)	Complex (coordinate both layers)
Cost model	Predictable (scheduled compute)	Continuous (always-on consumers)	Highest (pay for both)
Exactly-once delivery	Idempotent upserts sufficient	Requires Kafka transactions or idempotent writes	Depends on each layer
Schema flexibility	High (transform on read)	Low (schema registry enforced at publish)	Medium
Best for	BI reports, nightly aggregations, compliance exports	Fraud detection, inventory, ops dashboards	Large teams with clear SLA split between hot/cold

---

Monitoring Pipeline Health

Four signals cover most pipeline failures:

Lag. For streaming, consumer group lag (offset distance between the latest produced message and the last committed offset). Alert when lag grows without a corresponding throughput increase. For batch, the age of the last successful run watermark.

Throughput anomaly. Records processed per minute over a rolling window, compared to the historical baseline for that time of day. A 50% drop in records processed at 2pm on a Tuesday is a signal, not noise.

Error rate by stage. Track failures separately for extraction, transformation, and load. A spike in load failures often means the destination schema changed. A spike in extraction failures means the source is unavailable or has changed its API.

SLA breach rate. For each pipeline, define a freshness SLA (e.g., “orders table must be no more than 15 minutes stale”). Track and page on SLA breaches, not just job failures. A job that is running but very slowly may not fail, but it still breaches the SLA.

---

Production Considerations

Idempotency is not optional. Every write operation must produce the same result when executed multiple times with the same input. This means upserts with conflict keys, not inserts. It means watermarks committed only after a successful write. It means backfills that target isolated staging tables, not the production destination.

Schema registry adoption. For Kafka-based pipelines processing more than a handful of topics, invest in a schema registry early. Confluent Schema Registry with Avro or Protobuf prevents producer schema changes from silently breaking consumers. Consumers can specify compatibility mode (backward, forward, full) and the registry will reject incompatible schema versions at publish time.

Partition pruning. Warehouse queries against a large unpartitioned table become expensive quickly. Partition destination tables by date from the start. Backfills write to historical partitions without touching current ones. Retention policies drop old partitions rather than running expensive DELETE queries.

Dead letter queues. Records that fail processing repeatedly should not block the pipeline. Route unparseable or schema-invalid records to a dedicated dead letter topic or table, with the original payload and the failure reason. Review dead letter contents weekly; they are almost always the first signal of an upstream schema change.

Test with production-scale data. Pipelines that pass unit tests and fail on production data volumes are common. The failure modes: sorting a 50GB dataset in memory, GROUP BY on a high-cardinality column generating a query plan that does not use indexes, Flink checkpoints timing out because state grew beyond configured limits. Load tests against production-scale snapshots catch these before they cause incidents.

---

The core tension in data pipeline design is between simplicity and latency. Batch is simple; streaming is fast. The organizations that operate the most reliable pipelines are the ones that resist streaming until they have an explicit latency requirement that batch cannot meet, keep their transformation logic in one layer (ELT via dbt, not split across application code and SQL), and treat quality checks as part of the pipeline definition rather than an afterthought.

Build the simplest thing that meets your SLA. Then add complexity only where the data shows you need it.