Prerequisites

Before studying ETL and streaming, you should have a basic understanding of the following concepts:

What Is a Data Pipeline?

A data pipeline is an automated sequence of steps that moves data from one or more source systems to one or more destinations. During this journey, the pipeline may validate, clean, filter, enrich, join, aggregate, or restructure the data.

Sources may include operational databases, application events, APIs, log files, sensors, third-party systems, and object storage. Consumers may include dashboards, search indexes, recommendation engines, machine-learning models, reports, APIs, and alerting systems.

What Is ETL?

ETL stands for Extract, Transform, and Load. It is a data-integration process in which data is extracted from source systems, transformed into the required format, and then loaded into a destination.

Batch Processing in ETL

ETL commonly operates as a batch process. Instead of processing every record immediately, the system collects records during a defined interval and processes them together.

A batch may run every hour, every day, at the end of a business period, after files arrive, or on demand. Each run normally has a clear beginning, a bounded input, and a completion state.

Here, \(B_k\) is the batch for interval \(k\), \(e_i\) is an input event or record, and \(t_i\) is the time associated with that record. The batch contains the records that fall within the selected processing boundary.

Common ETL Use Cases

What Is Streaming?

Streaming is a processing model in which data is continuously consumed and processed as events become available. Instead of waiting for a large scheduled batch, the system continuously reacts to new information.

Streaming does not necessarily mean that the system processes exactly one event at a time. A streaming engine may group events into very small internal batches for efficiency while still presenting a continuous processing model.

Common Streaming Use Cases

ETL vs Streaming: Core Comparison

ETL vs ELT

ETL should not be confused with ELT. Both patterns extract data, but they differ in where transformation takes place.

Processing Time and Event Time

Streaming systems must distinguish between the time an event occurred and the time the processing system received or processed it.

Measuring only processing duration can hide time spent waiting in a producer, network, queue, or event log. End-to-end latency gives a more complete view of data freshness.

Windows in Stream Processing

A stream is unbounded, but many calculations require a bounded set of events. Windows divide the stream into manageable groups for aggregation.

SELECT
    product_id,
    window_start,
    window_end,
    COUNT(*) AS click_count
FROM TABLE(
    TUMBLE(
        TABLE product_clicks,
        DESCRIPTOR(event_time),
        INTERVAL '5' MINUTE
    )
)
GROUP BY
    product_id,
    window_start,
    window_end;

This conceptual streaming SQL query counts product clicks in non-overlapping five-minute event-time windows. Exact syntax depends on the selected stream-processing platform.

Late and Out-of-Order Events

Events do not always arrive in the same order in which they occurred. Mobile disconnection, network delay, retries, queue partitions, and producer failures can cause older events to arrive after newer events.

A stream processor therefore needs a policy that decides how long to wait before treating an event-time window as sufficiently complete.

The watermark \(W\) estimates how far event-time processing has progressed. Events older than the watermark may be treated as late, depending on the pipeline policy.

Delivery Semantics

Because consumers and networks can fail, a pipeline must define how event redelivery affects its output.

Idempotent Stream Processing

An idempotent consumer can receive the same event multiple times without producing multiple unintended effects. This is essential when the processing platform uses retries.

BEGIN;

INSERT INTO processed_events (
    event_id,
    processed_at
)
VALUES (
    'event-98341',
    CURRENT_TIMESTAMP
)
ON CONFLICT (event_id) DO NOTHING;

UPDATE product_metrics
SET click_count = click_count + 1
WHERE product_id = 501
  AND EXISTS (
      SELECT 1
      FROM processed_events
      WHERE event_id = 'event-98341'
  )
  AND NOT EXISTS (
      SELECT 1
      FROM applied_events
      WHERE event_id = 'event-98341'
  );

INSERT INTO applied_events (
    event_id,
    applied_at
)
VALUES (
    'event-98341',
    CURRENT_TIMESTAMP
)
ON CONFLICT (event_id) DO NOTHING;

COMMIT;

This example illustrates the need to connect deduplication and the business update within one safe transactional boundary. The exact implementation depends on the database and consistency model.

Checkpoints and Offsets

Streaming consumers need to remember their progress. An offset identifies a position in an ordered log partition, while a checkpoint records enough processing state to resume after a failure.

{
    "consumerGroup": "search-ranking-consumers",
    "checkpointId": "checkpoint-2841",
    "partitions": {
        "0": 193450,
        "1": 201782,
        "2": 188945
    },
    "stateVersion": "ranking-state-v7",
    "status": "COMPLETED"
}

Batch ETL Example

The following JavaScript example demonstrates the structure of an incremental ETL job. It extracts records after a stored checkpoint, transforms them, writes to staging, validates the result, and then updates the checkpoint.

async function runOrderEtl() {
    const checkpoint = await getCheckpoint("orders-etl");
    const runId = createRunId();

    try {
        const orders = await extractOrders({
            afterId: checkpoint.lastOrderId,
            limit: 10000
        });

        const transformedOrders = orders.map(function (order) {
            return {
                orderId: order.id,
                customerId: order.customer_id,
                totalAmount: Number(order.total_amount),
                orderDate: order.created_at.substring(0, 10),
                status: normalizeStatus(order.status)
            };
        });

        await writeToStaging(runId, transformedOrders);
        await validateStagingData(runId);
        await mergeIntoWarehouse(runId);

        const lastOrder = orders[orders.length - 1];

        if (lastOrder) {
            await saveCheckpoint("orders-etl", {
                lastOrderId: lastOrder.id,
                completedRunId: runId
            });
        }

        await markRunComplete(runId);
    } catch (error) {
        await markRunFailed(runId, error.message);
        throw error;
    }
}

In a production implementation, extraction should use a stable boundary. Depending only on an increasing identifier may be insufficient when old records can be updated or deleted.

Streaming Consumer Example

async function processProductEvent(event) {
    validateEventSchema(event);

    const alreadyProcessed = await eventStore.exists(event.eventId);

    if (alreadyProcessed) {
        return;
    }

    const normalizedEvent = {
        eventId: event.eventId,
        productId: event.productId,
        eventType: event.eventType,
        eventTime: new Date(event.eventTime),
        sourceVersion: event.schemaVersion
    };

    await database.transaction(async function (transaction) {
        await updateProductProjection(
            transaction,
            normalizedEvent
        );

        await eventStore.markProcessed(
            transaction,
            normalizedEvent.eventId
        );
    });
}

The consumer validates the event, checks for prior processing, updates the derived product view, and records the processed event within a controlled transactional operation.

ETL and Streaming in a Search System

A search platform may use both processing models because different parts of the index have different freshness and correctness requirements.

ETL and Streaming in Ranking

Ranking systems combine signals with very different update frequencies. Some features can be recomputed periodically, while others lose value quickly if delayed.

Here, \(B\) represents batch-generated historical features, \(S\) represents continuously updated streaming features, and \(R\) represents request-time signals such as query relevance or user context.

Micro-Batching

Micro-batching groups records over short intervals and processes each group as a small batch. It sits between traditional scheduled batches and event-by-event processing.

Hybrid Architecture

Many production systems combine batch and streaming rather than selecting only one. The streaming path supplies fresh updates, while the batch path supports historical processing, correction, reconciliation, and rebuilding.

Replay and Backfill

A pipeline may need to process historical data again after a bug fix, schema change, new business rule, or destination recovery. Batch systems usually describe this as a backfill. Streaming systems often describe it as replay.

Common Failure Scenarios

Monitoring and Observability

Batch and streaming pipelines require different operational metrics because their processing lifecycles are different.

Throughput and Capacity

A stable streaming system must process events at least as quickly as they arrive over a sustained period. Otherwise, the backlog continues to grow.

Here, \(\lambda_{\text{arrival}}\) is the arrival rate and \(\mu_{\text{processing}}\) is the sustainable processing rate. Capacity planning must also consider bursts, retries, maintenance, failed workers, and partition skew.

For a batch pipeline, the primary condition is whether the job can complete within its processing window.

Data Quality and Security

Fast data is not useful when it is incorrect or unsafe. Both ETL and streaming pipelines should enforce data contracts, access controls, encryption, auditing, and validation.

Cost and Complexity Trade-offs

How to Choose ETL or Streaming

Begin with the business freshness requirement. Do not choose streaming only because it appears more modern. Continuous processing introduces state, ordering, replay, duplicate, checkpoint, and operational concerns.

Decision Questions

Example System Design

Consider an e-commerce search platform that needs searchable products, accurate inventory, updated prices, and useful ranking signals.

Proposed Architecture

1. Product, order, click, price, and inventory services create authoritative records and events.
2. A durable event log stores events for independent consumers and controlled replay.
3. A streaming processor validates events and updates price, availability, recent popularity, and operational views.
4. Raw events are also retained in durable storage for auditing and historical processing.
5. A scheduled ETL pipeline joins catalog information with historical interactions and computes expensive ranking features.
6. The batch pipeline builds a validated version of the complete search index.
7. The serving layer combines textual relevance, batch features, streaming features, and request-time context.
8. A reconciliation job compares derived systems with authoritative records and repairs inconsistencies.

System Design Interview Discussion

In a system design interview, do not select ETL or streaming without explaining the requirements and operational trade-offs.

Common Design Mistakes

Production Readiness Checklist

Knowledge Check

Summary

ETL extracts data from source systems, transforms it into the required structure, and loads it into a destination. It is commonly executed as a bounded batch and works well for reporting, historical analysis, migrations, backfills, and periodic recomputation.

Streaming continuously processes events and is appropriate when systems must react to changes with low delay. It requires careful handling of partitions, ordering, duplicates, checkpoints, state, event time, windows, late data, replay, and continuous operations.

ETL versus ELT determines where transformation occurs, while batch versus streaming determines how data is processed over time. These decisions should be evaluated separately.

Many search and ranking platforms use a hybrid architecture. Streaming maintains fresh prices, inventory, clicks, and recent activity, while batch pipelines compute historical features, rebuild indexes, perform backfills, and repair drift.