Ollama Python Tutorial: Run Local LLMs with Chat, Streaming, Structured Outputs, Tools and Embeddings (2026) Python sqlite3 Tutorial: Tables, Transactions, JSON and Upserts with the Built-in Database (2026) Convert Python to EXE with PyInstaller: One-File Apps, Data Files and Hidden Imports (2026) Two-Factor Authentication in Python with pyotp: TOTP, QR Codes and FastAPI (2026) Hash Passwords in Python with Argon2: pwdlib, argon2-cffi and bcrypt Migration (2026) Encrypt and Decrypt in Python with Fernet: Strings, Files and Passwords (2026) psutil Tutorial: Monitor CPU, Memory, Disk and Processes in Python (2026) PyMuPDF Tutorial: PDF to Image, Extract Text and Images in Python (2026) MoviePy 2 Tutorial: Edit Videos with Python (Trim, Text, Merge, Audio) 2026 FastAPI Dependency Injection with Depends: Annotated, Yield and Overrides (2026) HTML to PDF in Python with WeasyPrint and Jinja2: Invoices and Reports (2026) Extract Tables from PDF in Python with pdfplumber: CSV, Excel and pandas (2026) OCR in Python: Extract Text from Images and Scanned PDFs with RapidOCR (2026) Text to Speech in Python with Piper: Offline Natural Voices, WAV Files and a TTS API (2026) Speech to Text in Python with faster-whisper: Transcribe Audio and Make Subtitles (2026) Send Email with Python: smtplib, HTML, Attachments and Bulk Mail (2026) Python QR Code Generator: Styled QR Codes with qrcode (2026) Pillow 13: Resize, Crop and Watermark Images in Python (2026) Parallel Programming with Dask in Python 2026: Intro Guide MarkItDown: Convert Files to Markdown for LLMs in Python (2026) Plotly Express: Interactive Charts in Python (2026) pandera: Validate pandas DataFrames in Python (2026) jsonschema: Validate JSON Data in Python (2026) Faker: Generate Fake Test Data in Python (2026) RapidFuzz: Fuzzy String Matching in Python (2026) sqlglot: Parse and Transpile SQL in Python (2026) structlog: Structured Logging in Python (2026) python-pptx in 2026: Automate PowerPoint Reports with Python python-docx: Create and Edit Word Documents with Python (2026) pypdf: Merge, Split and Edit PDFs with Python (2026) View all in this category →

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

Watch on YouTube

Chapters

  1. 0:00:0001. Welcome: why SQL, and setting up PostgreSQL and DuckDB
  2. 0:06:3902. Generating a practice shop database in SQL
  3. 0:15:1803. SELECT, WHERE, ORDER BY and LIMIT
  4. 0:22:4704. Aggregates, GROUP BY and HAVING
  5. 0:30:3805. JOINs: inner, left and anti joins
  6. 0:39:4206. CTEs and subqueries
  7. 0:48:1007. Window functions: ranking, LAG, running totals, dedup
  8. 0:57:0208. Data types, NULLs, casting, dates and timestamps
  9. 1:07:0309. DDL, constraints, keys, indexes and EXPLAIN
  10. 1:16:4710. Transactions
  11. 1:25:4411. Staging tables, idempotent loads and upserts
  12. 1:39:1512. Incremental loads with watermarks
  13. 1:47:5813. Slowly changing dimensions, type 2
  14. 1:59:2114. Star schema: facts and dimensions
  15. 2:06:3515. Data quality checks in SQL
  16. 2:13:3916. Querying CSV and Parquet files with DuckDB
  17. 2:19:2817. SQL from Python: a small ELT pipeline with psycopg
  18. 2:26:4618. Final project 1: the warehouse and the daily load
  19. 2:42:0619. Final project 2: quality gates, reports and reruns
  20. 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.sql

The 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).

Download source code

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.