Course Introduction

This course teaches the skills that published data engineering interview guides name. You will write SQL that ranks, groups, and compares rows. You will write Python that reads messy files and gives the same result when it runs twice. You will design tables for analytics, pipelines that load them, and checks that show the numbers are right.

A data engineer builds the systems that move data from where it is created, such as an app or a payment service, to the tables that analysts, reports, and models read. Unless a lesson labels code as not run, every query and script shown was run, and its real output is printed below it.

What the Interviews Test

Only some companies publish how they interview data engineers. The points below come from the companies that do, and from prep companies, which sell interview preparation and are labeled as such.

Meta. Meta's published preparation guide for its Data Engineer, Product Analytics role describes a 60-minute first screen with a 25-minute SQL part and a 25-minute Python part. The SQL part runs on PostgreSQL, but the guide says standard ANSI SQL is enough. Its SQL topics include joins, UNION against UNION ALL, correlated subqueries, and aggregations. For Python, it lists common data structures such as lists, dictionaries, and sets, and it says advanced techniques like dynamic programming are not required.

Meta's guide for the full loop names three 1-hour technical interviews and one 30-minute ownership interview. Each technical interview is a case study of a product problem, and together they cover product sense, data modeling, SQL, ETL, and Python. Product sense is judging what a product needs and which numbers show whether it works. ETL (extract, transform, load) is the work of reading data from a source, reshaping it, and writing it to a target table. In the data modeling part, the candidate designs a data mart, a set of analytics tables for one subject, and writes queries against it. The ownership interview asks about past work where the candidate took initiative and influenced others. The guide gives two different counts of technical interviews in different places, so confirm the format with your recruiter.

Amazon. Amazon's interview page for university hiring into data roles says its data engineers know SQL, ETL, and data modeling, plus at least one scripting or programming language. The same page says its Data Engineer interviews combine verbal and coding exercises. Its preparation notes ask candidates to explain the trade-offs between types of databases and to know the styles of schema design and when each one fits. They also list joins, grouping, window functions, and aggregations.

Take-home tasks. Some companies publish a take-home step. Monzo's analytics engineering loop includes a one-week take-home task and a case study. phData, a data consultancy, gives data engineer candidates a technical challenge with a coding task and a SQL task.

Prep-company descriptions. Prep companies describe data engineering loops as rounds of SQL, coding, data modeling, pipeline or system design, and behavioral questions. These are prep-company descriptions, not published company policy, and the round counts differ between them.

Job postings show the same two languages at the top. A study of 1,000 data engineer postings by 365 Data Science found SQL in 79.4 percent of them and Python in 73.7 percent. Huntr's sample of 110,933 postings found Python in 74.3 percent and SQL in 67.4 percent.

The table maps each published topic to the chapters that teach it.

None of the published topics above names streaming or privacy. The course covers both because a pipeline that reads a stream or stores personal data needs them. The ad metrics design and the change data capture design in Chapter 10 both read their events from a stream.

How the Course Is Arranged

The first nine chapters fall into three groups, and the tenth chapter combines them.

• Write the code. Advanced SQL (Chapter 1) covers window functions, ranking, running totals, recursive queries, sessions, duplicates, cohorts, funnels, and query plans. Coding for Data Work (Chapter 2) uses the Python standard library to parse files, group records, process files larger than memory, and write jobs that can run again safely.
• Design tables and pipelines. Data Modeling (Chapter 3) covers facts, dimensions, grain, and records that change over time. Pipeline Design (Chapter 4) covers batch against streaming, idempotent loads, backfills, late data, change data capture, and Airflow. A load is idempotent when running it twice gives the same table as running it once, and change data capture copies each insert, update, and delete from a source database into another system. Storage and Processing at Scale (Chapter 5) covers column storage, Parquet files, table formats, Spark, and warehouse costs. Streaming for Data Engineers (Chapter 7) covers reading a log, event-time windows, watermarks, and writing a stream to a table exactly once.
• Keep data correct and useful. Data Quality and Reliability (Chapter 6) covers data tests, data contracts, reconciliation, and recovering a failed pipeline. Privacy and Governance (Chapter 8) covers personal data, deleting one user everywhere, masking, and retention. Metrics and Product Sense (Chapter 9) covers metric definitions, investigating a drop, and experiment data.
• Complete Designs (Chapter 10). Four systems, each designed across two lessons: a clickstream pipeline, change data capture into a warehouse, an ad metrics dashboard, and a daily reporting pipeline.

The chapters build in order, and a lesson that uses an earlier technique links to the lesson that teaches it instead of teaching it again.

How a Lesson Works

Each lesson teaches the subject first. A concept starts with a one-sentence definition in bold, then works through an example with numbers from one running table. When two methods compete, the lesson states what each one costs and when the other one is the better choice.

Near the end, a short section named Using This in an Interview says what to explain while you work and answers two follow-up questions. Key Takeaways, a short closing paragraph, and four or five practice questions with folded answers end the lesson.

The last lesson of each chapter then ends with a chapter review: a deck of twenty flashcards and an adaptive assessment that draws twenty-five questions from a bank of forty, each with an explanation for every option.

Running the Code

Each lesson names where its output came from.

• SQL. The queries ran on MySQL 8.4. Most lessons include a graded exercise. You write one query against small tables, run it on the example data, and submit it. Submitting also checks the query against two more sets of tables with different data that you do not see, so a query that only fits the example does not pass.
• Python. In Chapter 2 and a few other lessons, you can run the Python scripts in the player. These scripts use only the Python standard library. They ran on Python 3.12, and the player runs Python 3.12.3.
• Linux samples. Spark, Airflow, Delta Lake, Iceberg, and Parquet file code ran on Linux. The lesson shows this code as read-only text with its recorded output and names the versions used.
• Engines that were not run. BigQuery, Snowflake, Redshift, and Debezium were not run for this course. Their syntax comes from their documentation and is labeled as not run, and their costs are arithmetic on their published prices.

Most sample tables follow two rules. Days are integer day numbers, such as day_no 9 and 12, instead of calendar dates, so the gap between them is a subtraction: 12 - 9 = 3 days. Money is stored in integer cents, so 1,250 cents is 12.50, and every sum is an exact integer. Lessons whose subject is dates, time zones, or schedules use real times instead.

Where to Start

Advanced SQL starts with the feature that most of its other lessons build on. The first lesson, Window Functions, shows how to compute a value from a group of rows while keeping every row in the result.