Become a Data Engineer with SQL

Where an analyst mostly reads data, an engineer is responsible for how it's structured, how safely it's written, and how fast it can be read back at scale. This path assumes you can already write a SELECT — it's built around the decisions that come before and after the query: how the tables are designed, how the writes stay consistent under concurrent load, how the pipeline keeps a warehouse up to date, and how the schema is shaped for the queries analysts will eventually run against it.

1. 1

   Schema Design with DDL

   30 min

   Every pipeline starts with a schema. CREATE TABLE decisions — column types, nullability, primary keys — made early are expensive to unwind later once a table has production data and downstream consumers depending on its shape.

   CREATE TABLE departments (
       id INT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       location VARCHAR(100),
       budget DECIMAL(12,2)
   );

   A tighter data type and a NOT NULL constraint decided at table-creation time prevents a whole category of bad data from ever landing.
   Lesson: DDL Commands
2. 2

   Constraints & Normalization

   35 min

   Foreign keys, uniqueness, and check constraints are the database enforcing your data model instead of trusting application code to get it right every time. Normalization decides how many tables a piece of information should live in — and knowing when to break those rules deliberately (denormalizing for a reporting table) is just as important as knowing the rules.

   OLTP systems favor normalization for write integrity; warehouses often denormalize on purpose to make analytical reads faster.
   Lesson: Constraints Lesson: Normalization Blog: Primary vs foreign vs unique key
3. 3

   Indexes & Reading Execution Plans

   45 min

   An index turns a full table scan into a targeted lookup — but only if it matches how the table is actually queried. Reading an execution plan is how you stop guessing and start proving whether a query is using an index or silently scanning every row.

   CREATE INDEX idx_employees_department
   ON employees (department_id);
   
   EXPLAIN SELECT * FROM employees WHERE department_id = 1;

   More indexes isn't free — every index speeds up reads but slows down every write to that table. Index for your actual query patterns, not preemptively.
   Lesson: Indexes & Performance Blog: Index scan vs table scan Blog: Reading EXPLAIN plans Blog: When not to use indexes
4. 4

   Transactions, ACID & Isolation Levels

   40 min

   Pipelines write data concurrently, and things fail mid-write. Transactions and ACID guarantees are what keep a partially-failed load from leaving a table in a half-updated state. Isolation levels decide exactly what one transaction is allowed to see of another's in-progress work — a real trade-off between consistency and throughput.

   BEGIN TRANSACTION;
   UPDATE accounts SET balance = balance - 500 WHERE id = 1;
   UPDATE accounts SET balance = balance + 500 WHERE id = 2;
   COMMIT;

   "Atomic" means both updates succeed or neither does — a transaction is what makes a multi-step write safe to run against a live pipeline.
   Lesson: Transactions Blog: Transactions & ACID explained Blog: Isolation levels explained
5. 5

   Change Data Capture & Incremental Loading

   35 min

   Reloading an entire source table on every pipeline run doesn't scale. Incremental loading — pulling only what changed since the last run — is what makes daily (or hourly) pipelines feasible, and UPSERT logic is how you apply those changes without creating duplicates.

   CDC and incremental loads are the difference between a pipeline that takes minutes and one that takes hours as the source table grows.
   Blog: Change Data Capture explained Blog: Incremental loading strategies Blog: UPSERT explained
6. 6

   Warehouse Modeling: Star Schema & Slowly Changing Dimensions

   40 min

   A warehouse isn't modeled like an application database. Fact tables (events, transactions) and dimension tables (customers, products) arranged in a star schema are what let analysts self-serve fast queries. Slowly Changing Dimensions handle the messier reality — attributes that change over time, like a customer's address or a product's category — without losing history.

   SCD Type 2 (keeping historical rows with valid-from/valid-to dates) is the default choice when "what did this look like on that date" matters for reporting.
   Blog: Star vs snowflake schema Blog: Fact table vs dimension table Blog: SCD Type 1 vs 2 vs 3 vs 4
7. 7

   Modern Data Architecture: Lakehouse & Medallion

   30 min

   Data engineering interviews and job descriptions now assume familiarity with how raw data moves through layered storage — the Medallion pattern (bronze/silver/gold) is the most common mental model, sitting on top of the broader warehouse-vs-lake-vs-lakehouse decision every modern data platform has to make.

   Medallion architecture is really just "raw, cleaned, business-ready" given formal names — useful vocabulary even before you touch a specific platform.
   Blog: Medallion architecture explained Blog: Warehouse vs lake vs lakehouse Blog: ETL vs ELT
8. 8

   Stored Procedures, Triggers & Automation

   25 min

   Stored procedures let you encapsulate pipeline logic inside the database itself; triggers fire automatically on inserts or updates. Both are powerful and both are easy to overuse — knowing when in-database automation helps versus when it makes a pipeline harder to debug is a judgment call every engineer has to make.

   CREATE TRIGGER trg_salary_audit
   AFTER UPDATE ON employees
   FOR EACH ROW
   INSERT INTO salary_audit (employee_id, old_salary, new_salary)
   VALUES (OLD.id, OLD.salary, NEW.salary);

   Triggers are great for audit trails and invariants, but logic hidden in a trigger is logic your pipeline code can't see — use them deliberately, not by default.
   Lesson: Stored Procedures Lesson: Triggers