SQL for Data Engineering - Full Course (2.5 Hours): PostgreSQL, DuckDB, MERGE, SCD2, Star Schema – Source Code
Learn SQL the way data engineers use it, from zero with a local PostgreSQL server and DuckDB: SELECT, aggregates, joins, CTEs, window functions, data types, DDL, indexes, EXPLAIN and transactions. Then the data engineering part: staging tables, idempotent loads and upserts, incremental loads with watermarks and MERGE, slowly changing dimensions type 2, a star schema, data quality gates, querying CSV and Parquet with DuckDB, and SQL from Python with psycopg. The final project is a small warehouse with staging, core and mart layers, a daily incremental load, quality gates and safe reruns.
- Duration
- 2:56:17 (20 chapters)
- Tools
- PostgreSQL 17, DuckDB 2.0.0, Python 3.13.5, psycopg 3.3.6, VS Code, bash
Watch the course
Chapters
- 0:00:0001. Welcome: why SQL, and setting up PostgreSQL and DuckDB
- 0:06:3902. Generating a practice shop database in SQL
- 0:15:1803. SELECT, WHERE, ORDER BY and LIMIT
- 0:22:4704. Aggregates, GROUP BY and HAVING
- 0:30:3805. JOINs: inner, left and anti joins
- 0:39:4206. CTEs and subqueries
- 0:48:1007. Window functions: ranking, LAG, running totals, dedup
- 0:57:0208. Data types, NULLs, casting, dates and timestamps
- 1:07:0309. DDL, constraints, keys, indexes and EXPLAIN
- 1:16:4710. Transactions
- 1:25:4411. Staging tables, idempotent loads and upserts
- 1:39:1512. Incremental loads with watermarks
- 1:47:5813. Slowly changing dimensions, type 2
- 1:59:2114. Star schema: facts and dimensions
- 2:06:3515. Data quality checks in SQL
- 2:13:3916. Querying CSV and Parquet files with DuckDB
- 2:19:2817. SQL from Python: a small ELT pipeline with psycopg
- 2:26:4618. Final project 1: the warehouse and the daily load
- 2:42:0619. Final project 2: quality gates, reports and reruns
- 2:51:4420. What's next: an honest map
What's in the zip
pg.env,shop/– local PostgreSQL setup and practice shop database (ch 1-2)basics/,aggregates/,joins/,ctes/,windows/– querying (ch 3-7)types/,ddl/,transactions/– types, DDL, indexes, transactions (ch 8-10)make_orders.py,loads/– staging, upserts and incremental loads (ch 11-12)make_customers.py,scd/,star/,quality/– SCD2, star schema, data quality (ch 13-15)duck/– DuckDB on CSV and Parquet (ch 16)elt.py,requirements.txt– SQL from Python with psycopg (ch 17)warehouse/,run_warehouse.py– final project (ch 18-19)README.md,HOW_TO_RUN.txt,LICENSE,MANIFEST.sha256
How to run
PostgreSQL 17 and the DuckDB CLI; Python 3.11+ (tested with 3.13.5) for chapters 11-19. Linux or macOS; on Windows use WSL. Run every command from the course folder in a bash terminal.
initdb -D pgdata -U postgres --auth=trust
echo "port = 5433" >> pgdata/postgresql.conf
echo "unix_socket_directories = '/tmp'" >> pgdata/postgresql.conf
pg_ctl -D pgdata -l pg.log start
source pg.env && createdb shop
psql -q -f shop/schema.sql ; psql -q -f shop/seed.sqlThe course server is local only and uses trust auth with no password. See HOW_TO_RUN.txt for every chapter's exact commands.
Data
No third-party datasets are bundled or downloaded. All data is synthetic, generated by the course code itself (shop/seed.sql, make_orders.py, make_customers.py).
File: sql-for-data-engineering-code.zip
Size: 25.6 KB (26,189 bytes)
SHA-256:112c01814f3fa672a790cf90304dd76606143a5f9ae275fe185a372792f279d8
Licence: code is released under the MIT licence, copyright PyInns. See LICENSE and README inside the zip. Free learning code, not production code.