ETL and ELT

ELT loads raw data first and transforms it where it lives

ETL: transform before loadingsourcesExtractcopy outTransformon an ETL serverLoadonly the resultwarehouseraw detailnever arrivesELT: load raw, transform inside the platformsourcesExtractcopy outLoadraw, as isTransformSQL in the platformre-run any time from rawlakehouseETL transforms data before it reaches the warehouse; ELT loads it raw and transforms it inside, where compute is elastic.ETL made sense when warehouse storage and compute were scarce: only clean results were kept. Cloud platforms made storage cheap andcompute elastic, so most teams now land raw data first and transform it with SQL or Spark where it lives. Keeping the raw copy means a bugin a transformation is fixed by re-running it, not by asking the source system for the data again.

ETL transforms before loading; ELT loads raw data and transforms it inside the platform, so it can always re-run.

Medallion architecture

Bronze keeps data as it came, silver cleans it, gold shapes it

Bronze: raw, as receivedidtstotalstatus1012025-10-01T09:121.20delivered1022025-10-01T09:406.50SHIPPED1022025-10-01T09:406.50SHIPPED10301/10/2025 11:053.00delivered1042025-10-01T12:30NULLdeliveredSilver: cleaned, one row per orderiddatetotalstatus1012025-10-011.20delivered1022025-10-016.50shipped1032025-10-013.00delivered1 duplicate dropped · 1 row quarantined (no total)dates parsed to one format · status lower-casedGold: ready for businessdateordersrevenue2025-10-01310.70daily revenue, a star,features for MLThe medallion layout refines data in three steps: bronze keeps it as it arrived, silver cleans it, gold shapes it for use.Bronze is append-only and untouched, so anything can be rebuilt from it. Silver applies the rules: here 5 raw records become 3 orders(one duplicate dropped, one date fixed, one row with no total set aside for review). Gold holds the tables people query: aggregates,star schemas, features. Each layer is a set of Delta tables, and each step is a job that reads one layer and writes the next.

The medallion architecture refines data through bronze (raw), silver (clean) and gold (business-ready) layers.

Incremental loads

Load only what changed since the last run, tracked by a watermark

Source table, by last changeorder_idupdated_at1012025-09-30 18:021022025-09-30 23:411032025-10-01 08:151042025-10-01 09:30872025-10-01 10:02watermark: 2025-09-30 23:59WHERE updated_at > :watermarkthen save the new maximum as the next watermarkorder 87 changed today, so it is picked up againRows read each nightFull reload1,000,000 rows, every nightIncrementalabout 2,740 rows: one day’s changesAn incremental load copies only what changed since the last run, tracked by a watermark: here 365 times fewer rows.Each run reads rows whose updated_at is after the saved watermark, loads them, and moves the watermark forward. It needs a reliable changecolumn, and it cannot see deletes: a deleted row has no new timestamp. Change data capture, which reads the database’s own log, sees inserts,updates and deletes (lesson 11). A full reload is simplest and still right for small tables.

An incremental load reads rows changed after the saved watermark: about 2,740 rows a night instead of 1,000,000.

Idempotent loads

A step that gives the same result when re-run makes retries safe

A load fails after writing, the scheduler retries it, and it succeedsINSERT INTO silver.orders SELECT …idtotal1011.201026.501011.201026.50retry added thesame rows againrevenue: 15.40: doubledMERGE INTO silver.orders t USING batch s ON t.id = s.id WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT …idtotal1011.201026.50run it once or five times:the same two rowsor replace a whole partition:INSERT OVERWRITE … PARTITION (date = '2025-10-01')revenue: 7.70: correctA pipeline step is idempotent when running it twice leaves the same result as running it once, so retries are safe.Jobs fail halfway and get retried; schedulers re-run days; files arrive twice. A plain INSERT appends again and doubles the numbers. Upsert withMERGE on a key, or overwrite exactly the partition the run is responsible for, and a repeat changes nothing. COPY INTO and Auto Loaderremember which files they have loaded for the same reason. Idempotent steps also make backfills (re-running past dates) routine.

An idempotent step leaves the same result however many times it runs: use MERGE on a key or overwrite a partition.

Orchestration

A pipeline runs as a graph: each task starts when its inputs succeed

ex_ordersex_customersbronzesilvergold_salesgold_customersrefresh_bisilver waits for bronze;a failure stops what depends on it[ex_orders, ex_customers] >> bronze >> silver >> [gold_sales, gold_customers] >> refresh_bi # daily 02:00An orchestrator runs a pipeline as a graph of tasks: each starts when what it depends on has succeeded.The graph is a DAG (directed acyclic graph): arrows point one way and never loop. Independent tasks run in parallel; a failed task is retried, and ifit still fails, everything downstream waits instead of building on bad data. The orchestrator also schedules runs, sends alerts, and backfills(re-runs past dates). Airflow writes the graph in Python, as above; Lakeflow Jobs, Data Factory and Step Functions define it as a workflow.

An orchestrator runs a DAG of tasks on a schedule, in dependency order, with retries, alerts and backfills.

An Airflow DAG · Going deeper

An Airflow DAG as written: schedule, catchup, retries, run date

with DAG( dag_id="orders_daily", schedule="0 2 * * *", start_date=datetime(2025, 10, 1), catchup=False, default_args={"retries": 2, "retry_delay": timedelta(minutes=5)},) as dag: bronze = BashOperator(task_id="bronze", bash_command="python load.py --date {{ ds }}") silver = BashOperator(task_id="silver", bash_command="dbt build --select silver") bronze >> silver123451schedulecron: minute hour day month weekday,so 02:00 daily. Also "@daily", atimedelta, or an upstream dataset.2catchupTrue creates a run for every intervalmissed since start_date. Default: Truein Airflow 2, False in Airflow 3.3retriesper task: here twice, 5 minutes apart.Then the task fails and everythingdownstream is marked upstream_failed.4{{ ds }}the run’s logical date as YYYY-MM-DD,filled in by Jinja. Never use now().5>>silver starts only when bronze succeeds.Pass the run’s date into every task, so re-running 1 October reprocesses 1 October: safe backfills.Airflow 2 imports shown (from airflow import DAG; airflow.operators.bash); Airflow 3 moves them to airflow.sdk and thestandard provider. Tasks run on workers through an executor; a sensor waits for a file or partition; XCom passes smallvalues between tasks, not data sets.

A DAG sets a schedule, catchup and retries; each task receives the run’s logical date as {{ ds }}.

dbt

dbt builds SQL models in dependency order and tests what they produce

models/marts/fct_daily_revenue.sqlselect order_date, count(*) as orders, sum(total) as revenuefrom {{ ref('stg_orders') }}where status <> 'cancelled'group by order_datemodels/staging/schema.yml- name: stg_orders columns: - name: order_id tests: [unique, not_null] - name: status tests: [accepted_values: …]dbt reads the ref() calls and builds the graph itself:raw.ordersstg_ordersfct_daily_revenuedbt build: run in order, test eachdbt turns transformations into version-controlled SELECT statements, works out their order, and tests the results.Each model is one SELECT; ref() names the models it reads, so dbt knows the dependency graph and runs it in order inside the warehouse (ELT).A model can materialise as a view, a table, or incrementally (only new rows, using is_incremental()). Tests such as unique, not_null andaccepted_values run after each build and stop bad data reaching gold. Docs and lineage are generated from the same files.

dbt models are SELECT statements; ref() links them into a graph, and tests check each result.

dbt incremental models · Going deeper

An incremental model adds one filter that applies after the first run

models/marts/fct_orders.sql{{ config(materialized="incremental", unique_key="order_id", incremental_strategy="merge") }}select order_id, customer_id, total, updated_atfrom {{ ref("stg_orders") }}{% if is_incremental() %}where updated_at > (select max(updated_at) from {{ this }}){% endif %}First run: no table yet, so the filter is left out and everything loads.Later runs: only newer rows are selected, then merged on order_id.dbt run --full-refresh drops the table and rebuilds it from scratch.Materialisationsviewthe default: a saved querytablerebuilt in full on every runincrementalnew or changed rows added to a tableephemeralnot built: inlined as a CTEsnapshottype 2 history of a source tableCommandsdbt runbuild the modelsdbt testrun the testsdbt buildboth, in graph order, test by test-s fct_orders+a model and all that depends on it-s +fct_ordersa model and all it depends onAn incremental model is a plain SELECT plus one filter that only applies once the table exists.is_incremental() is true when the model is incremental, its table already exists and --full-refresh was not given; {{ this}} is that table. Strategies: append, merge on unique_key, delete+insert, or insert_overwrite of whole partitions; whichexist depends on the warehouse. The four built-in tests are unique, not_null, accepted_values and relationships (every valueexists in another model); a singular test is a SQL file that must return no rows. Sources declare raw tables, and dbt sourcefreshness fails when one has stopped arriving.

is_incremental() is false on the first build and true afterwards; unique_key makes the load a merge.

File ingestion

Ingestion tools remember which files they have loaded

s3://landing/orders/orders_0901.jsonloadedorders_0902.jsonloadedorders_0903.jsonnewCOPY INTO: SQL, run on a scheduleCOPY INTO bronze.orders FROM 's3://landing/orders/' FILEFORMAT = JSONremembers loaded files: a second run loads only orders_0903.jsongood for thousands of files; Snowflake’s COPY INTO keeps 64 days of load historyAuto Loader: a stream that discovers new filesspark.readStream.format('cloudFiles') .option('cloudFiles.format', 'json').load('s3://landing/orders/')checkpoint records progress · infers and evolves the schema · bad fields → _rescued_dataFile ingestion tools record which files they have already loaded, so each new file is loaded exactly once.On Databricks, COPY INTO is a SQL command that skips files it has loaded before; Auto Loader is a streaming source that finds new files as theyland, scales to millions of files, and copes with new columns. Run it continuously or as a scheduled job that processes what arrived and stops.Lakeflow Connect adds managed connectors for databases and SaaS apps; Snowflake loads files with COPY INTO and continuously with Snowpipe.

COPY INTO skips files it already loaded; Auto Loader streams new files in, tracking them in a checkpoint.

Pipeline services

Each platform has connectors, transform engines and an orchestrator

JobAzure / FabricAWSDatabricks · SnowflakeCopy data inData Factory copy activityGlue, DMS, AppFlowLakeflow Connect · SnowpipeTransform at scaleFabric notebooks, DatabricksGlue ETL (Spark), EMRSpark, SQL · SQL, SnowparkDeclarative pipelinesDataflow Gen2Glue StudioDeclarative Pipelines · Dynamic tablesOrchestrateData Factory pipelinesStep Functions, MWAALakeflow Jobs · TasksIncremental statetumbling window triggersGlue job bookmarksAuto Loader checkpoints · StreamsEvery platform splits the work the same way: connectors copy, engines transform, an orchestrator runs it all.Azure Data Factory (and Data Factory in Fabric) builds pipelines of activities with triggers. AWS Glue runs serverless Spark jobs, andits job bookmarks remember what was processed; Step Functions and Managed Workflows for Apache Airflow (MWAA) orchestrate. LakeflowJobs run tasks with dependencies, retries and repair runs; Lakeflow Declarative Pipelines (formerly Delta Live Tables) declare tableswith quality expectations and manage the order themselves. Snowflake chains tasks and tracks changes with streams.

Azure Data Factory, AWS Glue and Step Functions, Databricks Lakeflow Jobs and Snowflake tasks run pipelines.

AWS Glue · Going deeper

AWS Glue is a catalogue, crawlers and serverless Spark jobs

A crawler fills the Data Catalog; a Glue job is serverless Spark billed per DPU-hour; a job bookmark remembers what was processed.

1 DPU4 vCPU and 16 GB of memory. A G.1X worker is 1 DPU, a G.2X worker 2.
Per secondSpark jobs bill by the DPU-second with a 1-minute minimum. Flex execution is cheaper for jobs that can wait.
15 minutesthe most a Lambda function may run, which is why long transforms go to Glue or EMR.

The parts

  • Data Catalog databases and tables as metadata only: schema, format, S3 location, partitions. Hive-compatible, and shared by Athena, Redshift Spectrum, EMR and Lake Formation.
  • Crawlers scan S3 paths or JDBC sources, infer the schema with classifiers, and create or update tables and partitions, on a schedule or on demand.
  • Jobs Spark (batch or streaming), Python shell for small scripts, or Ray. Written in code, or drawn in Glue Studio.
  • Job bookmarks state kept between runs, by S3 object timestamp or by a JDBC key column, so a re-run reads only new data.
  • DynamicFrame Glue’s DataFrame in which each record describes itself, so a column may hold mixed types until resolveChoice settles it.
  • Data Quality and DataBrew rules written in DQDL that score a data set or stop a job; DataBrew cleans data visually, with no code.

Choosing the orchestrator on AWS

  • Glue triggers and workflows scheduled, on demand or on completion of another job. Glue jobs and crawlers only.
  • Step Functions a state machine across any AWS service, with branches, retries and catches. A Standard workflow can run for up to a year.
  • Amazon MWAA managed Apache Airflow, when the team already writes DAGs in Python.
  • EventBridge starts any of these on a schedule or on an event, such as a file landing in S3.

Lesson complete

You finished “Moving data”

Nicely done.

Open in a new tab