Chapter 4: Batch Ingestion and ETL/ELT Pipelines

ETL vs ELT and AWS Glue

For three decades "ETL" was synonymous with data integration: a dedicated server pulled rows out of source systems, reshaped them in flight, and wrote the polished result into the warehouse. Cloud warehouses inverted that flow. Today most pipelines load raw data first and transform it inside the warehouse, an arrangement called ELT. Knowing when to use each pattern, and where the boundary lies, is the most consequential architectural decision in a batch pipeline.

Why ELT dominates in cloud warehouses

Cloud warehouses favor ELT for three reinforcing reasons. First, compute elasticity: Snowflake, BigQuery, and Redshift can scale compute up to handle a transform and back down when it finishes; a dedicated ETL server costs the same whether processing 100 GB or sitting idle. Second, pay-per-use billing: Snowflake charges per second of warehouse compute, BigQuery per byte scanned, Redshift via reserved or serverless capacity — all of which reward "transform when needed" rather than "transform always." Third, schema agility: loading raw data first means new fields land automatically and historical reprocessing is a query, not a redeployment.

The performance gap can be dramatic. Loading 100 GB of clickstream data with traditional ETL means an intermediate server has to extract, join, aggregate, and then push the result into the warehouse — often over hours. The ELT version loads the raw 100 GB into Snowflake in minutes and applies the same transformations using massively parallel warehouse compute.

ETL vs ELT comparison

Analogy. ETL is a meal-kit company that chops your vegetables in a central kitchen and ships pre-prepped boxes. ELT is a grocery delivery service: raw ingredients arrive in your kitchen and you decide what to make tonight. The grocery model wastes nothing if your menu changes; the meal kit forces an upstream change every time you want a new dish.

When ETL still applies

ELT is the default but not the universal answer. ETL keeps a role wherever raw data must not land in the warehouse in its original form:

1. PII redaction and minimization. GDPR/HIPAA require that PII be processed only for legitimate purposes; if your warehouse cannot guarantee that no one will query a raw email or ssn, you must hash, tokenize, or mask before the row arrives.
2. Strict schema enforcement. Regulatory feeds, financial reports, and ML feature stores cannot tolerate optional fields, type coercion, or schema drift. ETL's schema-on-write rejects malformed records at the boundary instead of corrupting downstream tables.
3. Cross-warehouse or air-gapped destinations. When data must traverse trust boundaries or move between clouds, transformation has to happen in a neutral compute layer (Glue, Spark on EMR, Fivetran).

The transformation layer: dbt and SQL Mesh

If raw data lands in the warehouse, transformations have to live somewhere. dbt treats SQL as software: each model is a SELECT in a .sql file plus a YAML descriptor; dbt compiles the files into a DAG, runs them in dependency order, and reports test failures. SQL Mesh adds virtual data environments, semantic versioning of models, and explicit handling of breaking changes via preview environments. Both treat business logic as code in version control, tested before deployment, with the warehouse as the runtime.

AWS Glue overview

AWS Glue is Amazon's serverless data integration service. It bundles four capabilities you will use repeatedly: a managed metadata catalog, automatic schema crawlers, a visual ETL builder (Glue Studio), and a serverless Spark runtime. Together they cover the lifecycle from "we just got a folder of CSVs in S3" to "production-grade Parquet tables refreshing every hour."

Glue crawlers and the Data Catalog

The Glue Data Catalog is the central metadata repository: table definitions, schemas, partition info, and pointers to underlying data, queryable by Athena, Redshift Spectrum, EMR, and Glue jobs. A crawler populates it. Point a crawler at an S3 prefix or a JDBC source and it will (1) classify objects (JSON, Parquet, CSV, Avro, ORC), (2) infer schema and partition structure from prefixes like year=2026/month=05/day=07/, and (3) register or update a table in a target Glue database.

Figure 4.2: Glue crawler-to-catalog-to-consumer pipeline

Glue Studio visual ETL

Not every transformation deserves hand-written Spark. Glue Studio is a drag-and-drop interface; you wire source nodes (Catalog, S3, JDBC, Kinesis), transformation nodes (apply mapping, filter, join, drop fields, aggregate, custom SQL), and sink nodes, and Glue Studio generates PySpark or Scala. It is appropriate for column projections, type casts, joins of a fact to a small dimension, and review-friendly artifacts. Hand-written Spark is the right tool when logic exceeds simple flow-graph transformations.

Glue Spark jobs and DynamicFrames

Under the hood, Glue jobs run on managed Apache Spark. AWS provisions executors, scales them within configured limits, and tears them down when the job finishes; you pay per second of DPU consumption. The Glue API introduces an abstraction on top of Spark called the DynamicFrame: similar to a DataFrame but tolerant of schema variance per record. If half your JSON has address.zip as a string and half as an integer, a DataFrame would fail to infer a single schema; a DynamicFrame stores both possibilities using a "choice type" and lets you resolve later with ResolveChoice.

The Relationalize transform is the killer feature: given a deeply nested JSON structure, it walks the tree and produces a set of relational tables joined by surrogate keys — exactly what you need to load nested data into a relational warehouse. Once data is clean, convert a DynamicFrame to a DataFrame with .toDF() and use Spark SQL.

Three tuning levers matter before the first production deploy:

• File grouping. S3 prefixes with thousands of small files create one Spark task per file, putting pressure on the driver. groupFiles: 'inPartition' collapses many small files into a single task.
• Adaptive Query Execution (AQE). Default in Glue 4.0+. Converts sort-merge to broadcast joins at runtime when one side is small, and rebalances skewed partitions. Almost always a net win.
• Worker sizing. Default G.1X has 4 vCPU / 16 GB. Move to G.2X or G.4X on executor OOMs — but check whether the OOM is caused by skew (one key dominates) before throwing memory at it.

The job.commit() call is the signal that tells Glue to advance the job bookmark and mark the run successful. Without it, the next run reprocesses the same data.

Incremental Ingestion: CDC and Watermarking

Full reloads do not scale. A 10-billion-row source table cannot be re-extracted nightly without saturating the source database, the network, and the warehouse. Incremental ingestion is the practice of moving only what has changed since the last run. There are three families of approaches — log-based CDC, query-based watermarks, and S3-side Glue bookmarks — and you will likely use all three across a single platform.

Change Data Capture (CDC) sources

CDC identifies and captures INSERT, UPDATE, and DELETE operations in source databases, enabling incremental synchronization rather than full reloads. Instead of asking "what is the current state of the table?" CDC asks "what events changed the table?" That difference, state vs. event, is what makes incremental ingestion correct.

Three CDC strategies, in descending order of fidelity:

1. Log-based CDC reads the database's transaction log directly: PostgreSQL's WAL, MySQL's binlog, Oracle's redo logs, SQL Server's change tracking. Connectors like Debezium or AWS DMS decode the log and emit a stream of change events with full INSERT/UPDATE/DELETE semantics, primary keys, before-and-after values, and transaction order.
2. Trigger-based CDC installs DB triggers that fire on writes and write change rows to a side table; consumers poll. Universal but slow and lossy on failure.
3. Query-based CDC asks the source for rows changed since a known point in time. Simplest pattern; covered as watermarking below.

Figure 4.3: Log-based CDC reference architecture

AWS DMS in particular supports three modes: full load only (one-time snapshot), CDC only (changes after a known LSN), and full-load-plus-CDC (snapshot then continuous stream) — the third is what you want for migrations and ongoing replication into a warehouse.

Watermarking and high-water-mark queries

When log-based CDC is impractical — analytics-only stores, third-party APIs without a binlog, small infrequently changing tables — query-based CDC with a watermark is the pragmatic choice. A watermark is a monotonically increasing column on the source: a last_modified_at timestamp, an auto-incrementing id, or a version field. The pipeline records the highest value it saw last run (the high-water mark, HWM) and asks the source for everything beyond that mark.

The query template:

SELECT *
FROM   users
WHERE  last_modified_at >  :prev_hwm
  AND  last_modified_at <= :current_hwm
ORDER  BY last_modified_at;

prev_hwm is loaded from the pipeline's metadata store. current_hwm is captured at the start of the run, often now() - 5 minutes to give in-flight transactions time to commit. After the run succeeds, the metadata store advances prev_hwm to current_hwm. If the run fails, prev_hwm is left unchanged so the retry covers the same window.

Figure 4.4: Watermark advancement state machine

Watermark column choices

The right answer is often a combination: insert-only fact tables keyed on id, mutable dimension tables keyed on last_modified_at plus a soft-delete column, and a periodic full-compare to catch drift.

Glue job bookmarks

When the source is S3 rather than a database, Glue offers a built-in incremental mechanism called job bookmarks. A bookmark is metadata Glue persists between runs — which files (by path), timestamps, and row counts have already been processed — and the next run automatically skips them. A canonical use case:

Run 1 (Mon):  Process files 1 to 100.   Bookmark stores last_path=file_100.
Run 2 (Tue):  Process files 101 to 150. Files 1 to 100 are skipped automatically.
Run 3 (Wed):  No new files. Job runs but processes zero rows.

Bookmarks require two cooperating elements in your script: a bookmark-aware source (create_dynamic_frame.from_catalog or from_options with transformation_ctx set) and a final job.commit(). Without job.commit() the bookmark does not advance and the next run reprocesses the same files.

Operational notes: bookmark scope is per transformation_ctx, so multi-source jobs must give each source its own context name; bookmarks survive job edits as long as the context name is stable; resetting requires the deliberate "Reset bookmark" or "Run with bookmark disabled" Glue console actions, useful for backfills.

Reliability: Idempotency and Schema Evolution

Incremental ingestion is necessary but not sufficient. The same job will be retried after a network blip, replayed after a bad upstream change, and rerun after a DDL evolution that broke its assumptions. The reliability of a batch pipeline is determined less by its happy-path code than by what happens on the second, third, and fourth runs. Three patterns make those reruns safe: idempotent writes, replayability via raw zone retention, and disciplined schema evolution.

Idempotent writes and exactly-once semantics

A write is idempotent when applying it twice produces the same result as applying it once. Idempotency is the single most important property of a reliable batch job, because partial failures, retries, and replays are not edge cases — they are the norm. Three implementation patterns deliver idempotency in practice.

1. Upsert with version guards

The strongest pattern for mutable rows is a MERGE keyed on the primary key, with a guard clause that ignores out-of-order updates:

MERGE INTO target_table t
USING staged_changes  s
ON    t.id = s.id
WHEN MATCHED AND (s.version > t.version OR t.version IS NULL) THEN
    UPDATE SET t.value = s.value,
               t.version = s.version,
               t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (id, value, version, updated_at)
    VALUES (s.id, s.value, s.version, s.updated_at);

The s.version > t.version clause is load-bearing. It means a stale event arriving after a fresher one is silently discarded. Replays are safe; out-of-order CDC streams are safe.

Figure 4.5: Idempotent upsert decision flow with version guard

2. Deduplication tables

When MERGE is unavailable (large append-only Parquet partitions, for example), maintain a small ledger of processed event IDs and filter incoming events against it:

INSERT INTO target_table
SELECT s.*
FROM   staged_changes s
WHERE  NOT EXISTS (
    SELECT 1
    FROM   cdc_processed p
    WHERE  p.source_id = s.id
      AND  p.operation_sequence = s.seq
);

Pair the insert with an insert into cdc_processed inside the same transaction (or atomic write). Replays that find the row already in the ledger become no-ops.

3. Atomic partition swaps

For partition-grained idempotency, write the new data to a staging path, validate it, and then swap the staging path into the table catalog atomically. If the run fails midway, the staging path is orphaned (and a janitor cleans it up); the live table is untouched.

"Exactly-once" is a marketing term; "exactly-once-effective" is the engineering reality. Your job may execute twice, but the observable result is the same as if it had executed once. That is what idempotency buys you, and it is the strongest guarantee you should claim.

Replayability via raw zone retention

A pipeline is replayable if you can reconstruct any past state of any downstream table by re-running transformations against retained raw inputs. Replayability is what saves you when (not if) a transformation has a bug, a business rule changes retroactively, or a regulator asks for a six-month-old report computed under the rules in force then.

The architectural foundation is the raw zone: an immutable, retention-tagged S3 prefix where every input file lands in its original form, with metadata recording when it arrived. The raw zone is append-only by policy; nothing in it is ever rewritten. Transformations read from raw and write to a curated zone; the curated zone can be wiped and rebuilt from raw at any time.

s3://lake/raw/                      ← immutable, partitioned by ingest date
  orders/ingest_date=2026-05-07/...
  events/ingest_date=2026-05-07/...

s3://lake/curated/                  ← rebuildable, partitioned by business date
  orders_clean/order_date=2026-05-07/...

s3://lake/marts/                    ← presentation, used by BI tools
  fact_orders/...

Three operational practices keep replayability honest:

• Retention as policy, not afterthought. Use S3 Lifecycle rules to move raw partitions to Glacier after 90 days but never delete; or set Object Lock to enforce immutability for compliance.
• Capture ingestion metadata. Stamp each raw file with ingest_timestamp, source_version, and pipeline_run_id. Replays can then ask "what would today's transformation produce against the raw data we had on 2026-05-01?"
• Idempotent transformations. Replay only works if rerunning a transform on the same input produces the same output. Random IDs, now() timestamps, and external API calls inside transforms all break replayability.

Schema evolution handling

Schema evolution is the inevitable change in the structure of incoming data: a new column appears, a type widens from int to bigint, an old column is renamed, an enum gains a value. A pipeline that breaks every time a producer changes is a pipeline that owns the producer's roadmap. The goal is to absorb common changes automatically and surface only the changes that genuinely need engineering attention.

The schema-change taxonomy

Three mechanisms handle most of these automatically:

• Glue crawlers detect new fields on each run and update the catalog. Combined with Parquet's column-level metadata, downstream queries can ignore unknown columns or pick them up immediately.
• DynamicFrame ResolveChoice lets you declare a strategy when a column has multiple inferred types in the same data: make_struct, cast, project, or match_catalog. A common pattern is to cast numeric ambiguity to the wider type and project JSON ambiguity to a string.
• Schema registries (AWS Glue Schema Registry, Confluent Schema Registry) version the contract between producers and consumers. Producers register a new version; consumers check compatibility before reading. Backward-compatible changes propagate silently; incompatible changes fail at registration time, not in production.

For producer-facing pipelines (events from microservices into a lake), a registry is the right answer. For analyst-facing pipelines (curated tables consumed by dashboards), versioned dbt or SQL Mesh models give you the same control with deployment gates. The unsexy but critical practice is monitoring schema drift: a weekly diff of today's catalog schema against last week's catches the slow accretion of fields that no one announced.