AlgoCademy
Start learning free

CoursesData & AIData Engineering

Build a shop’s data warehouse, from raw files to reports.

For people who know Python and SQL and want to work on data pipelines. You load the shop’s daily order files, model them as a star schema, and build pipelines that give the same table when you run them twice. Then you do it with dbt and Spark.

Start the first lesson See the plans
You type every line A real checker runs your code Nothing to install
Lessons
107
Chapters
12
Language
Python
Level
Advanced
Interview questions
16
Free
The first chapter
Before you start
Python, SQL with joins and GROUP BY, and a little pandas.

What you build

A daily orders pipeline

Run dates, backfills, late rows and retries.

A star schema for the shop

Facts, dimensions, surrogate keys, and history that is kept.

The shop in dbt

Staging and marts, sources, tests and an incremental model.

Data quality checks

Row counts, nulls, orphan rows and freshness. A failed check stops the run.

Tools and topics

Syllabus

01Files and Formats9 lessons
  1. CSV's Trapsfree
  2. JSON Linesfree
  3. Parquet: Write and Readfree
  4. Read Only the Columns You Needfree
  5. The Schema Travels with the Filefree
  6. Folders by Datefree
  7. Compression and Sizefree
  8. Many Files at Oncefree
  9. A Column Appearsfree
02The Warehouse9 lessons
  1. Query Files Where They Are
  2. Load Files into Tables
  3. Raw, Then Clean: Staging
  4. Casts and Bad Values
  5. Remove Duplicates
  6. Upsert: Insert or Update
  7. Load Only What's New
  8. Run It Twice, Get the Same Table
  9. Export a Table
03Data Modeling10 lessons
  1. Facts and Dimensions
  2. The Grain of a Table
  3. A Star Schema for Orders
  4. Surrogate Keys
  5. A Date Dimension
  6. Changing Dimensions: Overwrite (Type 1)
  7. Changing Dimensions: Keep History (Type 2)
  8. One Wide Table or a Star?
  9. Snapshots and Events
  10. Project: Model the Shop
04Pipelines in Python10 lessons
  1. Extract, Transform, Load
  2. The Run Date Is a Parameter
  3. Replace One Day's Data
  4. Backfill a Range of Days
  5. Late Rows
  6. Log What Each Step Did
  7. Retry a Flaky Source
  8. Remember Where You Stopped
  9. Write, Then Rename
  10. Project: The Daily Orders Pipeline
05Data Quality8 lessons
  1. Count the Rows
  2. Not Null and Unique
  3. Accepted Values and Ranges
  4. Orphan Rows
  5. Freshness: Is the Data Late?
  6. Source and Target Agree
  7. Set Bad Rows Aside
  8. Checks That Stop the Run
06dbt10 lessons
  1. A Model Is a SELECT in a File
  2. ref(): Models Built on Models
  3. Staging and Marts
  4. Sources
  5. Built-in Tests
  6. Your Own Test
  7. View or Table?
  8. An Incremental Model
  9. A Variable and a Little Jinja
  10. Project: The Shop in dbt
07Orchestration8 lessons
  1. Tasks and What They Wait For
  2. The Order to Run In
  3. Run It: A Status per Task
  4. A Failure Stops What Depends on It
  5. Retries
  6. Schedules and Run Dates
  7. Backfills
  8. What Airflow Adds
08Streaming Basics7 lessons
  1. Events, Not Batches
  2. Read from a Queue
  3. The Same Event Twice
  4. Counts per Minute: Windows
  5. Late Events and Watermarks
  6. Offsets: Where Was I?
  7. The Same Answer as the Batch Job
09Table Formats6 lessons
  1. A Table Is Files Plus a Log
  2. Append and Versions
  3. Time Travel
  4. A Schema the Table Enforces
  5. Merge: Upsert into a Table
  6. Small Files and Compaction
10Spark8 lessons
  1. A DataFrame That Isn't in Memory
  2. Lazy Steps and Actions
  3. select, where, withColumn
  4. groupBy and agg
  5. Joins
  6. Partitions and Shuffles
  7. Parquet In and Out
  8. Spark SQL
11Interview Questions16 lessons
  1. An Idempotent Daily Load
  2. Slowly Changing Dimension, Type 2, in SQL
  3. Dedupe, Keeping the Latest
  4. Sessions from Clicks
  5. Top N per Group
  6. An Incremental Aggregate
  7. A Skewed Join
  8. Late Data
  9. Apply a Change Log
  10. Which Days Are Missing?
  11. A Conversion Funnel
  12. 7-Day Active Users
  13. Process Each Event Once
  14. Why Does It Read Every File?
  15. Do Source and Target Agree?
  16. First and Last Event per User
12Capstone6 lessons
  1. Capstone: Bring the Files In
  2. Capstone: Clean It
  3. Capstone: The Star
  4. Capstone: Check It
  5. Capstone: A Daily Load You Can Re-run
  6. Capstone: The Report

Questions

Something else?
team@algocademy.com

Is it real Spark?

Yes. Your PySpark code runs on Spark in the checker.

Is it real dbt?

Yes. dbt builds your models on DuckDB in the checker.

Do I use Airflow or Kafka?

No. The orchestration and streaming chapters teach the ideas in plain Python: tasks, retries, schedules, queues and windows. The orchestration chapter ends with what Airflow adds.

More in Data & AIAI EngineeringMachine LearningSQL & DatabasesAll courses

LESSON 1

CSV's Traps

The first chapter is free, no card needed. The rest comes with PRO.

Start the lesson See the plans