ETL and ELT
ELT loads raw data first and transforms it where it lives
ETL transforms before loading; ELT loads raw data and transforms it inside the platform, so it can always re-run.
Data has to get from where it is created to where it is analysed. The classic way is ETL: extract it, transform it on a separate server, and load only the clean result into the warehouse. The raw detail never arrives. The modern way is ELT: extract it, load it as it is into the lake or warehouse, and transform it there with SQL or Spark. Storage is cheap and compute stretches, and because the raw copy is kept, a mistake in a transformation is fixed by simply running it again.
Medallion architecture
Bronze keeps data as it came, silver cleans it, gold shapes it
The medallion architecture refines data through bronze (raw), silver (clean) and gold (business-ready) layers.
Lakehouses usually organise this as three layers. Bronze holds exactly what arrived: here five records, with a duplicate, a date in another format, a status in capitals and a missing total. Nothing is fixed, so anything can be rebuilt. Silver applies the rules: drop the duplicate, parse the dates, standardise the status, and set the record with no total aside for review. 3 clean orders. Gold shapes the data for its users: here, one row of daily revenue. Gold is what dashboards and models read.
Incremental loads
Load only what changed since the last run, tracked by a watermark
An incremental load reads rows changed after the saved watermark: about 2,740 rows a night instead of 1,000,000.
Reloading everything every night is simple, and wasteful. Instead, remember a watermark: the latest change time you have loaded. Tonight, read only rows changed after it, then move the watermark forward. New orders come through, and so does order 87, which changed today. On our million-order table that is about 2,740 rows a night instead of a million. The catch: a deleted row leaves no new timestamp, so watermarks cannot see deletes. Change data capture can.
Idempotent loads
A step that gives the same result when re-run makes retries safe
An idempotent step leaves the same result however many times it runs: use MERGE on a key or overwrite a partition.
Pipelines fail halfway, and schedulers retry them. If the load is a plain INSERT, the retry appends the same rows again, and revenue for that day doubles. Make the step idempotent instead: MERGE on the order id, so a repeat updates rows that are already there, or overwrite exactly the date partition this run is responsible for. Run it once or five times, the table ends the same. That is also what makes it safe to re-run past days.
Orchestration
A pipeline runs as a graph: each task starts when its inputs succeed
An orchestrator runs a DAG of tasks on a schedule, in dependency order, with retries, alerts and backfills.
A real pipeline is many steps, and they must run in the right order. Draw them as a graph: extract orders and customers, in parallel, then bronze, then silver, then the two gold tables, then refresh the dashboards. Silver starts only when bronze has succeeded. If bronze fails after its retries, nothing downstream runs on bad data, and someone gets an alert. In Apache Airflow the whole graph is one line of Python, run every night at two.
An Airflow DAG · Going deeper
An Airflow DAG as written: schedule, catchup, retries, run date
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
dbt models are SELECT statements; ref() links them into a graph, and tests check each result.
Much of the transform step in ELT is now written with dbt. Each model is a file holding one SELECT. Instead of naming a table directly, it says ref stg_orders. Beside it, a file declares tests: order id must be unique and not null, status must be one of the known values. From the ref calls dbt works out the graph itself, runs the models in order inside the warehouse, and tests each one, so bad data stops before it reaches gold.
dbt incremental models · Going deeper
An incremental model adds one filter that applies after the first run
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
COPY INTO skips files it already loaded; Auto Loader streams new files in, tracking them in a checkpoint.
Much data arrives as files in a landing folder. Two are loaded; one is new. COPY INTO is a SQL command. Run it on a schedule and it loads only files it has not loaded before, so it is safe to repeat. Auto Loader is a stream. It discovers new files as they land, records its progress in a checkpoint, infers the schema and adds new columns when they appear, and puts anything it cannot parse in a rescued data column instead of failing.
Pipeline services
Each platform has connectors, transform engines and an orchestrator
Azure Data Factory, AWS Glue and Step Functions, Databricks Lakeflow Jobs and Snowflake tasks run pipelines.
Every platform divides the work the same way. Connectors copy data in: Data Factory's copy activity, AWS Glue and DMS, Lakeflow Connect, Snowpipe. Engines transform it: Spark and SQL. Orchestrators run the graph: Data Factory pipelines, Step Functions or managed Airflow on AWS, Lakeflow Jobs on Databricks, tasks on Snowflake. Declarative tools, such as Lakeflow Declarative Pipelines and Snowflake dynamic tables, let you state what each table should contain and work out the order themselves.
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.
A crawler fills the Data Catalog; a Glue job is serverless Spark billed per DPU-hour; a job bookmark remembers what was processed. 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.
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.