SQL Projects for Data Engineers

12 Public Repos to Build

The best SQL projects for data engineers are pipelines where SQL does the engineering: loads that can run twice without duplicating a row, dimensions that keep history, tests that stop a bad build, and queries tuned from their plans. These 12 are real public projects, each a repository you clone or a tutorial that hosts it, in 4 tiers that build on each other, and 10 of them run on a laptop for $0. Each dossier says what you build, what it teaches, the signal it sends in an interview, and what to change so the finished project is yours.

Last updated: September 27, 2026Proudly published by: Jeff Wahl26 min read

What makes a SQL project a data engineering project

A data engineering project in SQL moves and models data that other people and systems use, or serves and checks it. The SQL in it has to survive being run again tomorrow, on more data, after something upstream changed. That rules out most of what ranks for "SQL projects". A sales dashboard or a churn analysis shows that you can query a table, and so does a set of puzzles, but none of them shows that you can be trusted to build one.

4 properties separate the 2 kinds. The work runs more than once, so a rerun must leave the same result, and the project shows that it does. It keeps state: what was loaded, which version of a row was true when, which checks passed. It meets data at a size where the naive query fails, so the design has to change. And it proves its own correctness instead of asking the reader to trust the numbers: tests do that, and so does a reconciliation or a query plan.

Each of the 12 projects on this page has all 4, and each is a real public project: a repository you clone, or the tutorial that hosts it. They come in 4 tiers, and each reuses skills from the one before it. Loads you can run twice come first, then models that keep history. Volume and speed follow, and production habits come last. Every source was opened on September 27, 2026, and each star count is the one its repository showed that day.

The 12 SQL projects at a glance

#	Project	Difficulty	Time	Core stack	Cost
01	Idempotent taxi loads with a hashed key and MERGE	Beginner	1 weekend	PostgreSQL · Kestra · Docker	$0
02	Deduplicate taxi trips and load the fact table incrementally in dbt	Beginner+	1 to 2 weekends	dbt · DuckDB · SQL	$0
03	A bronze, silver and gold warehouse in pure SQL	Beginner+	2 to 3 weekends	SQL Server · SQL	$0
04	A Kimball star schema from AdventureWorks in dbt	Intermediate	1 to 2 weekends	dbt · DuckDB · PostgreSQL	$0
05	A type 2 dimension kept by a stream, a task and MERGE	Intermediate	1 weekend	Snowflake · SQL	Cloud billing
06	Sessionize GA4 events with the dbt-GA4 package	Intermediate	1 to 2 weekends	dbt · BigQuery · SQL	$0 (free tier)
07	Interval-based incremental models with SQLMesh	Intermediate+	1 weekend	SQLMesh · DuckDB · SQL	$0
08	Tune TPC-H queries in PostgreSQL from their plans	Intermediate+	2 to 3 weekends	PostgreSQL · SQL	$0
09	Find the bad estimates in the Join Order Benchmark	Advanced	3 weekends	PostgreSQL · SQL	$0
10	Change data capture from PostgreSQL with wal2json, applied with MERGE	Advanced	2 to 3 weekends	PostgreSQL · SQL	$0
11	Data tests, unit tests and contracts in CI on jaffle-shop	Intermediate	1 to 2 weekends	dbt · PostgreSQL · GitHub Actions	$0
12	Reconcile a rebuilt model with dbt-audit-helper	Intermediate	1 weekend	dbt · PostgreSQL · SQL	$0

Star counts as each repository's page showed them on September 27, 2026.

How to turn a cloned SQL project into your own

A public project is a starting point. Reviewers recognise the popular templates, and the first question about a cloned repository is what you changed. Every dossier on this page ends its lesson with an extension for that reason: the extension is the part an interviewer will ask about.

To turn a clone into your project, swap in different data or a different load strategy, so the design decisions become yours to defend. Break the pipeline on purpose with a rerun and a late file, then with a duplicate and a changed column, and keep what you learned. Then add proof, such as a rerun that changes nothing or a test that failed. A reconciliation counts too, and so does a plan before and after a fix.

Star counts measure attention, not fit. A tutorial with a few hundred stars and a clear design can teach more than a famous repository you only skim, so pick by the skill each project teaches, and credit the original repository at the top of your README.

Which SQL project to start with

If your situation is

Pick

Why

You have never written a load that runs on a schedule

Projects 1 → 2 → 11

A small pipeline you can explain end to end, built from a MERGE loader and a deduplicated incremental fact, with tests that fail the build

You write SQL daily but have never modelled a warehouse

Projects 3 → 4 → 5

Layers that clean the data, a star at a declared grain, then history that a type 2 dimension keeps

You want an analytics engineering role

Projects 4 → 6 → 11

Dimensional modelling and sessions built in dbt on real traffic, with contracts and unit tests added in CI

You want to prove you can make SQL fast

Projects 8 → 9

Plan reading on generated TPC-H data, then the estimates that break on real, correlated data

You are aiming at senior or platform roles

Projects 7 → 10 → 12

Interval bookkeeping for late data, change capture applied with MERGE, then a reconciliation that proves the result

Loads you can run twice

Tier 1 · Projects 1-3

The first 3 projects answer the question every load is asked first: what happens when it runs again. Each gives a different answer, a merge on a manufactured key, a deduplicated incremental model and a truncate and reload, so together they cover the 3 designs an interviewer expects you to compare.

Project 01 · Idempotent loads

Idempotent taxi loads with a hashed key and MERGE

Beginner1 weekendLocal & free

Module 2 of the Data Engineering Zoomcamp loads 1 month of yellow or green taxi trips per run into PostgreSQL, orchestrated by Kestra. Each run truncates a staging table, copies the month's CSV into it, adds a unique_row_id that is an md5 hash of 7 trip columns, and merges into the main table with MERGE ... WHEN NOT MATCHED THEN INSERT, so only rows the table has not seen go in. A scheduled version runs on the 1st of each month with a concurrency limit of 1 and can backfill the course's 2019 and 2020 files.

TLC trip records carry no trip id, so the flow builds one from the columns that describe a trip, and the merge on that hash turns a rerun of any month into a no-op. State the trade-off out loud: 2 different trips with identical values in all 7 columns collapse into 1 row. The concurrency limit matters as much, because 2 runs at once would truncate each other's staging table in the middle of a load.

To make it yours, run a month twice and diff the row count and a checksum of total_amount, then count how many rows the hash collapses. Replace the merge with a delete and reinsert of the month in 1 transaction and compare the 2 designs on the case that separates them: a corrected file for a month you already loaded.

Interview signal

"What happens if it runs twice?" is the first question a reviewer asks about any load. You can name the key, the statement that makes the rerun safe, the rows the hash cannot tell apart and the reason the flow refuses to run twice at once.

PostgreSQLKestraDocker

SQL

DataTalksClub/data-engineering-zoomcamp, module 2~45.8k★DataNYC TLC trip records, 2019 and 2020

Project 02 · Deduplication

Deduplicate taxi trips and load the fact table incrementally in dbt

Beginner+1 to 2 weekendsLocal & free

Module 4 builds the taxi_rides_ny dbt project over the same 2 years of trips and runs locally on DuckDB or in BigQuery. int_trips unions yellow and green and keeps 1 row per trip with qualify row_number() over (partition by vendor_id, pickup_datetime, pickup_location_id, service_type order by dropoff_datetime) = 1, then derives trip_id as a surrogate key of the same 4 columns. fct_trips is an incremental model with unique_key='trip_id' and the merge strategy, and it joins dim_zones twice, once for the pickup zone and once for the dropoff.

Deduplication is a decision about which row survives, and here it is written down: trips that match on vendor and service and share a pickup time and zone count as 1 trip, and the earliest dropoff wins. The incremental filter is the part to question. It selects trips by comparing their pickup time with the latest one already in the table, so a trip that arrives late with an earlier pickup time is never selected, and a merge on trip_id cannot rescue a row the filter skipped.

To make it yours, publish how many rows the qualify drops per month, then change the filter to reprocess a window of recent days and show that a late trip lands. dim_zones joined twice is the role-playing dimension pattern; name it when you explain the model.

Interview signal

ROW_NUMBER deduplication appears in most SQL rounds. You can name the columns you partitioned by and defend the row that survives. You can also say what an incremental model does with a row that arrives after its watermark has passed.

dbtDuckDB

SQL

DataTalksClub/data-engineering-zoomcamp, module 4~45.8k★DataNYC TLC yellow and green trips, 2019 and 2020

Project 03 · Medallion warehouse

A bronze, silver and gold warehouse in pure SQL

Beginner+2 to 3 weekendsLocal & free

A SQL Server warehouse built from 6 CSV extracts of a CRM and an ERP, in 3 layers loaded by stored procedures. Bronze copies the files unchanged. Silver cleans them, and gold exposes a star schema as 3 views named dim_customers, dim_products and fact_sales. Every silver table is truncated and reloaded on each run, so the procedure can run any number of times, and it logs how long each table took inside a TRY ... CATCH.

The silver procedure is a catalogue of cleaning patterns worth knowing by heart. Customers are deduplicated with ROW_NUMBER() OVER (PARTITION BY cst_id ORDER BY cst_create_date DESC). Product versions get end dates from LEAD(prd_start_dt) OVER (PARTITION BY prd_key ORDER BY prd_start_dt) - 1, so each one ends the day before the next begins. Order dates stored as 0 or with the wrong number of digits become NULL, and a sales amount that disagrees with quantity times price is recomputed. The quality checks are queries whose expected result is no rows.

To make it yours, notice that dim_products keeps only rows whose end date is NULL, so the history silver built is thrown away at gold. Keep it: expose the validity range and join each sale to the product version that was current on its order date. Then replace the truncate with an incremental load and write down what the rerun guarantee costs you.

Interview signal

Interviewers ask how raw data becomes something a dashboard can trust. You can walk through 3 layers with a cleaning rule from each, explain why a full reload is safe to rerun, and say when it stops being affordable.

SQL Server

SQL

DataWithBaraa/sql-data-warehouse-project963★DataCRM and ERP CSV extracts (bundled)

The shape all 3 loads share: stage, key, merge, check

Raw

Stage

Load

Serve

Source

monthly csv files

PostgreSQL

staging table

SQL

key and merge

IDEMPOTENCYMERGE on a row key

PostgreSQL

trips

custom

row checks

ERRORFail the run

Consumer

reporting

The staging table is emptied at the start of every run, the merge inserts only keys the target has not seen, and checks run before anyone reads the result.

The unit of workA rerun of a month must leave the table as 1 run left it. Either a row key decides how, or the load replaces a whole partition or truncates the table, and each approach has a case it gets wrong.

1 run at a timeA shared staging table makes 2 concurrent runs corrupt each other, which is why project 1's scheduled flow sets a concurrency limit of 1.

Prepare for the interview

01 / Open invite

02min.

Know SQL projects the way the interviewer who asks it knows it.

a SQL projects query, the same shape a screen would give you.

The diff against expected. Where ties broke. What you missed.

sandbox

1SELECT user_id,

2 COUNT(*) AS sessions

3FROM events

4WHERE ts >= NOW() - INTERVAL '7 day'

Execute your solution0.4s avg.

ShopifyInterview question

Solve a SQL projects problem

What happens when a SQL load runs twice

A load is idempotent when running it a second time on the same input leaves the same table. An INSERT of a month's rows fails that test the moment a job retries: the month is in the table twice, and every total built on it doubles without an error anywhere.

2 designs pass it. A MERGE on a row key inserts only the keys the table lacks, so a rerun inserts nothing; it needs a key, and when the source has none, you manufacture one from its columns and accept that identical rows collapse. A delete and reinsert of a whole month or day, or of the full table, replaces what the last run wrote; it needs no key but must run in 1 transaction, or a reader can see the unit half gone.

3 loaders each run twice on the same month of rows: after run 2 the INSERT only loader holds the month twice, duplicated, while the MERGE on a row key and the DELETE + INSERT loader each hold it once, unchanged

To prove it, run the same month twice and compare the row count and a checksum of 1 numeric column after each run; then backfill several months in a random order and check that the result matches a load in calendar order. Both checks fit in a README table, and they answer the question before a reviewer asks it.

Models that keep history

Tier 2 · Projects 4-6

Loading rows is half the work; the other half is shaping them so a question has 1 right answer. These 3 projects cover the grain of a star schema, a dimension that remembers what was true when, and sessions built from raw events.

Project 04 · Dimensional modelling

A Kimball star schema from AdventureWorks in dbt

Intermediate1 to 2 weekendsLocal & free

A step by step tutorial, published on the dbt Developer Blog, that turns the AdventureWorks sample database into a Kimball star on DuckDB or PostgreSQL. fct_sales holds 1 row per sales order line with unitprice and orderqty, plus a derived revenue. 6 dimensions surround it, covering the product and customer, their address and credit card, the order status and the date. Keys are hashes from dbt_utils.generate_surrogate_key. unique and not_null tests guard them, and the project also builds obt_sales, the same data as 1 wide table.

Grain first. The tutorial states the grain of fct_sales before it writes a join, and every later decision follows from it: which measures add up, which keys belong on the fact, why order status is a dimension of its own. Because the star and the wide table come from the same staging models, you can compare them on the questions each answers easily.

To make it yours, add a second fact at a different grain, 1 row per order, and show why it cannot share a table with order lines. Then give dim_customer history with a dbt snapshot, which puts the type 2 pattern of project 5 into a model the star already uses.

Interview signal

Data modelling rounds open with grain and keys. You can state the grain of every table you built, defend a hashed surrogate key over the source id, and draw the star from memory.

dbtDuckDBPostgreSQL

SQL

Data-Engineer-Camp/dbt-dimensional-modelling181★DataAdventureWorks sample tables (seeded by dbt)dbt Developer Blog walkthrough

Project 05 · SCD type 2

A type 2 dimension kept by a stream, a task and MERGE

Intermediate1 weekendCloud account

Snowflake's own 2-part tutorial builds a type 2 history of the TPC-H NATION table. A stream, NATION_TABLE_CHANGES, records every change on the source, deletes included, and a view, NATION_CHANGE_DATA, turns those changes into rows tagged with a dml_type of I, U or D. From there 1 MERGE into NATION_HISTORY, matched on the key and the start time, keeps start_time and end_time current along with current_flag. The second part schedules the merge as a task, populate_nation_history, that runs every minute only when system$stream_has_data('nation_table_changes').

An update is 2 writes in a type 2 dimension: the current version gets an end time and a new version is inserted, and both must land in 1 statement or 1 transaction, or the history gets a gap. The view prepares what the merge needs for each kind of change, so 1 MERGE both closes versions and opens them. Handling deletes as their own dml_type is the case most hand-written versions forget.

To make it yours, rebuild it in PostgreSQL, where there is no stream: compare a hash of the tracked columns with the current version's hash to find the rows that changed. Then prove that each key's ranges leave no gaps or overlaps, and answer a point-in-time question with valid_from <= t AND t < valid_to.

Interview signal

Type 2 is one of the most asked data modelling topics, and most candidates can only describe it. You can say how a change is detected, how 1 statement closes a version and opens the next, and what a delete does to the history.

Snowflake

SQL

Snowflake: a type 2 SCD with streams and tasks, part 1DataTPC-H NATION table from Snowflake's sample dataPart 2: scheduling the merge as a task

Project 06 · Sessionization

Sessionize GA4 events with the dbt-GA4 package

Intermediate1 to 2 weekendsFree cloud tier

dbt-GA4 models the raw event export that Google Analytics 4 writes to BigQuery. It turns the daily sharded events_* tables into 1 partitioned table, builds a staging model per event type, and derives session models such as dim_ga4__sessions and fct_ga4__sessions, keyed by a session_key built from user_pseudo_id and ga_session_id. The public data to run it on is Google's obfuscated sample of the Google Merchandise Store, 3 months of events from 2020-11-01 to 2021-01-31; the package reads its source from its source_project and property_ids settings, so start from its README when you aim it at a dataset that is not your own property's export.

The package is incremental with a reprocessing window: static_incremental_days sets how many days each run scans again, so events that land after a day was first loaded still count. Its session key shows that you have to construct a session identity yourself. Then write your own from user_pseudo_id and event_timestamp alone. Use LAG and a running SUM to start a new session after 30 minutes without an event, which is GA4's default timeout.

Reconcile your sessions with the package's: count the ones that match, the ones split differently and the ones only 1 side has, and explain each class. Google warns that obfuscation limits the sample's internal consistency, so the deliverable is an explained difference, not a forced match. The BigQuery sandbox gives 10 GiB of storage and 1 TiB of queries a month but allows no DML, which an incremental merge needs, so build full refreshes there or enable billing and stay inside the free tier's 1 TiB.

Interview signal

Sessionization is a classic product analytics question, and it usually comes with a follow-up about late events or a changed timeout. You have run it on real traffic, written it by hand, and can say where your definition and the package's disagreed.

dbtBigQuery

SQL

Velir/dbt-ga4401★DataGA4 sample ecommerce dataset

The star project 4 builds, at order-line grain

dim_product

product_keyPKVARCHAR

productidINT

product_nameVARCHAR

product_subcategory_nameVARCHAR

product_category_nameVARCHAR

dim_customer

customer_keyPKVARCHAR

customeridINT

fullnameVARCHAR

storenameVARCHAR

fct_sales

sales_keyPKVARCHAR

product_keyFKVARCHAR

customer_keyFKVARCHAR

order_date_keyFKVARCHAR

ship_address_keyFKVARCHAR

order_status_keyFKVARCHAR

salesorderidINT

salesorderdetailidINT

unitpriceDECIMAL

orderqtyINT

revenueDECIMAL

dim_date

date_keyPKVARCHAR

date_dayDATE

fct_sales holds 1 row per sales order line. Every key is an md5 hash from dbt_utils.generate_surrogate_key. The address and credit card dimensions follow the same pattern and are left out here, as is order status.

Grain first1 row per order line decides which measures add up and which keys belong on the fact. A per-order fact is a second table, never extra rows in this one.

History is a choiceEach dimension here reflects the source as it is today. Project 5's type 2 pattern is what lets a sale join the customer as they were on the order date.

How a type 2 dimension keeps history

A slowly changing dimension of type 2 never overwrites a changed attribute. It closes the current version by setting its end, and inserts a new version that starts at the same instant. Each row carries valid_from and valid_to, usually with an is_current flag as well, and the current row's valid_to is empty or a far-future date.

A customer whose address changes in week 7 and again in week 15 ends with 3 rows, and their ranges must tile: each version ends exactly where the next begins, so no moment is missing or covered twice. Treat valid_from as inclusive and valid_to as exclusive, and a point-in-time join is simply valid_from <= t AND t < valid_to, which finds exactly 1 version for any moment.

3 versions of 1 customer in a type 2 dimension on an axis from week 0 to week 20: address A until week 7, address B until week 15, and address C, the current version, from week 15 on, with no gaps or overlaps

Detect a change by comparing a hash of the tracked columns with the current version's hash, so an unchanged row costs 1 comparison. MERGE in PostgreSQL has decided per row between insert and update since version 15, and version 17 added WHEN NOT MATCHED BY SOURCE for rows missing from the source; use that clause only when absence really means deletion. The SCD interview questions go through the variations interviewers ask next, from type 1 overwrites to late-arriving changes.

Both Ends of the Pipe

> The release engineering team wants the authors who carry a build all the way from dev into production, treating the same environment name in different casing as one. List each qualifying author with how many of those two environments they deployed to, alphabetically.

How sessionization turns events into sessions

Sessionization is the gaps and islands pattern, the most reused window function technique in data engineering. Order each user's events, measure the gap to the previous event with LAG, flag an event that starts a session when there is no previous event or the gap exceeds the timeout, and number the sessions with a running SUM of those flags.

For visitor u1, 6 events between 10:02 and 11:24 make 2 sessions, because the 42 minute gap between 10:21 and 11:03 is longer than the 30 minute timeout; every other gap is shorter.

Visitor u1's 6 events from 10:02 to 11:24 on a time axis: the 42 minute gap between 10:21 and 11:03 is longer than the 30 minute timeout, so the events split into session 1 and session 2

At volume, the hard parts are the ones the toy example hides. A session that crosses midnight belongs to 2 daily partitions, an event that arrives late can merge 2 sessions you already published, and a changed timeout rewrites history. Decide the partition a session belongs to, reprocess a window of recent days on every run, and version the timeout with the model.

Run the sessionization query on 2 visitors

-- A new session starts at a user's first event and after any gap over 30 minutes
WITH gaps AS (
  SELECT
    user_id,
    event_ts,
    page,
    (CAST(strftime('%s', event_ts) AS INTEGER)
      - CAST(strftime('%s', LAG(event_ts) OVER (
          PARTITION BY user_id ORDER BY event_ts)) AS INTEGER)) / 60 AS gap_min
  FROM page_events
),
flagged AS (
  SELECT
    *,
    CASE WHEN gap_min IS NULL OR gap_min > 30 THEN 1 ELSE 0 END AS starts_session
  FROM gaps
)
SELECT
  user_id,
  event_ts,
  page,
  gap_min,
  SUM(starts_session) OVER (
    PARTITION BY user_id ORDER BY event_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM flagged
ORDER BY user_id, event_ts;

10 events for 2 visitors make 4 sessions. Change the 30 in the CASE to 60 and run it again to see the sessions merge.

Volume and speed

Tier 3 · Projects 7-9

Correct SQL that is too slow or reprocesses too much is still a failed pipeline. These 3 projects cover incremental work done by interval, reading a query plan, and the estimates that go wrong when data stops being uniform.

Project 07 · Incremental processing

Interval-based incremental models with SQLMesh

Intermediate+1 weekendLocal & free

sqlmesh init duckdb creates a project with 2 models, 1 INCREMENTAL_BY_TIME_RANGE and 1 full, fed by a seed, and sqlmesh plan shows the date range each model must backfill before it runs anything. From there, work through the repository's sushi example, whose customer_revenue_by_day model processes days in batches of 10 and filters its source with WHERE o.event_date BETWEEN @start_date AND @end_date. Every change goes through a dev environment first, where SQLMesh classifies it as breaking or non-breaking.

SQLMesh records which time intervals each model has processed and runs only the missing ones, the bookkeeping a hand-rolled watermark gets wrong. Restating an interval deletes and reinserts exactly that range, so a rerun of last Tuesday leaves every other day alone, and a lookback setting reprocesses the most recent intervals on every run to catch rows that arrive late.

To make it yours, feed it data that arrives late: write an hourly file, then deliver 1 hour a day after the rest. Show the day's total before and after the lookback picks it up, time a full rebuild against an incremental run, and put both numbers in the README.

Interview signal

Late data and incremental models come up in every pipeline design round. You can explain how SQLMesh tracks intervals and restates them, and why the lookback exists. You can also give the cost difference between rebuilding and appending from numbers you measured.

SQLMeshDuckDB

SQL

SQLMesh/sqlmesh~3.3k★DataThe quickstart's seed data and the bundled sushi exampleSQLMesh CLI quickstart

Project 08 · Query tuning

Tune TPC-H queries in PostgreSQL from their plans

Intermediate+2 to 3 weekendsLocal & free

tpch-kit is the TPC-H generator with PostgreSQL support added. Build it with make MACHINE=LINUX DATABASE=POSTGRESQL (or MACHINE=MACOS), generate data with dbgen -s, and write all 22 queries with qgen. Load scale factor 10 into PostgreSQL, run every query under EXPLAIN (ANALYZE, BUFFERS), save the plans, pick the 3 slowest and tune them 1 change at a time.

Read a plan the way the PostgreSQL documentation on EXPLAIN describes it. Compare estimated rows with actual rows on every node, since a large mismatch means the planner chose on bad statistics. Multiply a node's actual time by its loops before deciding where the time goes, and read the buffer counts to tell a cached run from a cold one. EXPLAIN ANALYZE executes the statement, so wrap any write in BEGIN and ROLLBACK.

The changes to try, 1 at a time: an index on a join key, fresh statistics with ANALYZE, and partitioning lineitem by ship date so a date filter prunes partitions. Keep a table of query, change, the plan node that moved and the runtime before and after. The SQL query optimization guide covers the rewrites to try when an index is not the answer.

Interview signal

"Tell me about a slow query you fixed" needs a reason as well as a before and an after. This project gives you 3, each with the plan that proves it.

PostgreSQL

SQL

gregrahn/tpch-kit219★DataTPC-H tables generated by dbgen

Project 09 · Cardinality estimation

Find the bad estimates in the Join Order Benchmark

Advanced3 weekendsLocal & free

The repository holds the benchmark's 113 queries, schema.sql, optional foreign key indexes in fkindexes.sql, and where to download the IMDb snapshot they run on: 21 tables, 3.6 GB as CSV, with 36 million rows in cast_info alone. The queries come in 33 shapes with 3 to 16 joins each, 8 on average. Load it into PostgreSQL, find the first plan node of every slow query where estimated and actual rows diverge, and trace how the error grows up the join tree.

The benchmark's paper found that all the estimators it tested routinely produce large errors on this data, which is full of correlations and non-uniform distributions, while PostgreSQL's estimator works unrealistically well on generated TPC-H data. It also found that the more indexes are available, the harder the optimiser's problem becomes.

Try the fixes a working engineer has: extended statistics with CREATE STATISTICS on correlated columns, a rewritten predicate, a different index set. Record which errors each one removed and which it could not touch, and give the second list as much space in the report as the first.

Interview signal

This is senior material. Few candidates can explain how a cardinality error compounds across joins, and you can show it on a named benchmark with plans you captured.

PostgreSQL

SQL

gregrahn/join-order-benchmark371★DataIMDb snapshot from May 2013, 3.6 GB of CSV

Production habits

Tier 4 · Projects 10-12

The last 3 projects are the habits that keep SQL correct after it ships: changes captured from a live database, tests that fail a pull request, and a reconciliation that proves the result matches something outside the pipeline.

Project 10 · Change data capture

Change data capture from PostgreSQL with wal2json, applied with MERGE

Advanced2 to 3 weekendsLocal & free

wal2json is the PostgreSQL output plugin that turns the write-ahead log into JSON. Set wal_level = logical, create a slot with pg_create_logical_replication_slot('cdc', 'wal2json'), and with format-version 2 each change arrives as 1 JSON object. It carries its action (I, U or D) and its columns, and a delete also carries the key that identifies the row. Parse the JSON with PostgreSQL's own JSON functions into a staging table where each row records the change's log position and table, plus its operation with the key and values. Then apply each batch to a replica table with MERGE.

The order of operations is what makes it safe. pg_logical_slot_peek_changes reads without consuming and pg_logical_slot_get_changes consumes, so peek, apply the batch in a transaction, then consume only up to the position you applied. A crash then replays changes instead of losing them, and because the apply is a merge keyed on the primary key, a replayed change lands on the same row. Collapse each key to its latest change by log position with ROW_NUMBER first, because PostgreSQL raises an error when 2 source rows reach the same target row in 1 MERGE.

To make it yours, drive the source with pgbench. Once you pause it, compare row counts and a column sum between source and replica. Then leave the slot unread for an hour and measure what it does to the source's disk, since a slot keeps every WAL segment it has not consumed. The SQL upsert guide compares MERGE with INSERT ... ON CONFLICT for the apply step.

Interview signal

Change data capture is on most data platform job descriptions, and the hard questions are about ordering and replays, and about deletes. You have answers that come from a running system.

PostgreSQL

SQL

eulerto/wal2json~1.5k★DataYour own inserts, updates and deletes (pgbench generates them)PostgreSQL logical decoding example

Project 11 · Tests and contracts

Data tests, unit tests and contracts in CI on jaffle-shop

Intermediate1 to 2 weekendsLocal & free

dbt Labs' sandbox project for a fictional restaurant that sells jaffles, runnable on PostgreSQL as well as the cloud warehouses. Its orders model already carries 2 of the 3 kinds of check. Its keys have unique and not_null tests as well as relationships tests, and 2 dbt_utils.expression_is_true tests check that the totals add up. A unit test feeds fixed rows into the model and checks the food and drink flags it computes. Add a model contract to each mart and a GitHub Actions workflow that runs dbt build against PostgreSQL on every pull request.

The 3 layers answer different questions. A contract checks the names and types of a model's output in a preflight step, before anything is built. A unit test checks the logic on a small fixed input, and dbt recommends running unit tests only in development and CI. A data test checks the real data after the build, and with store_failures the failing rows land in a schema suffixed _dbt_test__audit, so "which rows failed and why" has an answer the next morning.

To make it yours, give each check a severity: blocking tests stop the publish, warnings record a count and let it through. Open 1 pull request that renames a column and 1 that breaks the order total, and link both failed runs in the README. For an incremental model, a unit test checks what the model will merge or insert, not the table after, so keep a data test on the result as well.

Interview signal

Analytics engineering loops ask how you stop a bad change from reaching production. You can walk through the 3 layers and the severity rule, then show the 2 pull requests they caught.

dbtPostgreSQLGitHub Actions

SQL

dbt-labs/jaffle-shop365★DataJaffle Shop seeds, or years of synthetic orders from jafgendbt unit tests documentation

Project 12 · Reconciliation

Reconcile a rebuilt model with dbt-audit-helper

Intermediate1 weekendLocal & free

dbt-audit-helper is dbt Labs' package of SQL macros for proving that 2 relations hold the same data. Rebuild a model from an earlier project a second way, such as fct_trips without its incremental filter, and compare the 2: compare_row_counts first, then compare_relation_columns to check that column names and types match in the same order, compare_which_relation_columns_differ to find the columns that disagree, and compare_and_classify_relation_rows for a row by row comparison with summary counts.

Correctness is measured against something outside the model. Tests prove the data obeys its rules; a reconciliation proves it matches another version of the truth, whether that is the legacy model you are replacing or the figures a publisher reports about its own data. Start with quick_are_relations_identical, which compares a hash of all rows, and drill down only when it says no.

To make it yours, reconcile against a source you do not control. NYC TLC publishes aggregated monthly reports of trips and fares next to its trip files: compute the same measures from your trips and produce 1 row per month that shows both values and their difference, and flags whether it falls inside a tolerance you can justify. Expect differences on the first run, and write down how each published measure is defined before you set the tolerance.

Interview signal

Reconciliation is the answer to "the finance number and your number disagree, what do you do?", a common design and behavioural prompt. You can describe a reconciliation you ran and the differences you explained.

dbtPostgreSQL

SQL

dbt-labs/dbt-audit-helper424★Data2 versions of a model from projects 2 to 4

Capture, apply, verify: the design projects 10 to 12 build together

Source

Apply

Verify

Serve

PostgreSQL

app database

CDC

wal2json slot

PostgreSQL

change log

SQL

latest per key

IDEMPOTENCYMERGE on the primary key

PostgreSQL

replica

dbt tests

model tests

ERRORFail the pull request

custom

reconciliation

Consumer

analysts

Changes are consumed from the slot only after the MERGE commits, so a crash replays them. Tests guard the shape and rules of the replica; the reconciliation compares it with the source.

Consume lastPeek, apply in a transaction, then consume up to the position applied. Any other order either loses changes or needs a merge that tolerates replays.

2 kinds of proofTests prove the data obeys its rules. A reconciliation proves it matches another source, and it catches the errors every test agrees with.

Common mistakes in SQL portfolio projects

The most common mistake is an analysis project presented as engineering. A notebook of queries over a static CSV answers a business question once; nothing in it has to run again, so nothing in it shows the judgment a data engineering reviewer is looking for. To turn it into a pipeline, schedule a load that can safely rerun and put a check in front of the output.

The unmodified clone comes next. Every project on this page is public, which is why it is worth building and also why it proves nothing on its own. The repository shows what its author decided; your README has to show what you decided and what you changed, including what broke.

Sample-sized data hides the problems a project exists to teach. Tuning a query on 10,000 rows teaches nothing, because every plan is fast, and deduplication on 1 file never meets the duplicate that spans 2. Generated data has a limit of its own: the Join Order Benchmark paper, How Good Are Query Optimizers, Really?, found that PostgreSQL's estimator works unrealistically well on TPC-H, whose generated columns are uniform and independent, so test a planner on real data before you draw conclusions about it.

Many projects also claim correctness without proof, with a README that says "the pipeline works" and shows nothing behind it. A rerun diff is proof, and so is a test that failed; a reconciliation or a before and after plan works too. Each fits in 1 screenshot or 1 table.

How to turn a finished SQL project into interview answers

01
Write down the numbers
Record rows per run and runtime, and what a rerun changes. Keep the pass and fail counts of your tests too, and the before and after of any tuning.
02
Prepare 3 stories from the build
Pick 1 design decision along with the alternative you rejected, and 1 failure with how you found it. The third story is 1 trade-off you would make differently at 10 times the volume.
03
Rewrite the core query from a blank editor
The live SQL round will not let you open your repository, so rewrite the deduplication, the type 2 close-out or the sessionization in under 20 minutes without notes.
04
Rehearse the follow-ups
What if it runs twice, what if a file is late, what if the key is not unique, what if the data is 100 times bigger: answer each out loud before someone else asks.

How SQL projects show up in the SQL interview round

A project gets you the interview; the rounds decide the offer, and they test the same patterns under a clock. These projects are built on deduplication with ROW_NUMBER and gaps and islands for sessions, plus a type 2 join on a date range and a merge keyed on a unique column. Together those are the core of most SQL rounds too.

Practise writing them from scratch with the SQL interview questions and the SQL practice problems, which run your query against test data with the edge cases these projects surface, duplicates and ties as well as gaps and NULLs. The window functions guide covers the frame clauses the sessionization query depends on, and the SQL round guide explains how the round is run and scored.

SQL projects FAQ

What SQL projects should a data engineer build?+

Build projects where SQL does engineering work for someone else. A load should run twice without duplicating rows, a dimension should keep history, and tests should stop a bad build before anyone reads it. Sessionization or deduplication belongs at real volumes, and a slow query should be fixed from its plan. 3 finished projects that cover loading and modelling and prove their own correctness say more than 10 analysis notebooks.

Are SQL projects enough for a data engineering portfolio?+

They cover most of the job, because in a warehouse the loading and modelling are written in SQL, and so are the tests and the tuning. Pair them with 1 project that adds orchestration or a stream, and with practice for the live coding rounds, since a portfolio gets you the interview and the rounds decide the offer.

Is it fine to put a cloned tutorial project on my resume?+

Only after you change it. Reviewers recognise the popular templates, so an unmodified clone says nothing about you. Change the data or the load strategy, break the pipeline on purpose and fix it, add a proof such as a rerun diff or a failed test, and list what you changed from the original repository at the top of the README.

Can I build these SQL projects for free?+

Yes, nearly all of them. 10 of the 12 run on a laptop with DuckDB or PostgreSQL, or with SQL Server Express for the medallion warehouse. The GA4 sessionization project runs in BigQuery, whose sandbox needs no billing account and gives 10 GiB of storage and 1 TiB of queries a month, with tables that expire after 60 days. The Snowflake type 2 tutorial needs a Snowflake account.

Which database should I use for SQL projects?+

DuckDB fits loads and transformations over files, because it runs anywhere and reads Parquet and CSV directly. PostgreSQL is the choice where the project is about the database itself, such as reading query plans or using MERGE and logical decoding. Use the engine a tutorial was written for on the first pass, SQL Server for the medallion warehouse or BigQuery for GA4, and port it to a second engine as your extension.

How big should the data be in a SQL project?+

Big enough that the naive approach visibly fails: a full reload that takes too long, a query whose plan changes with the data, or duplicates that only appear across many files. The 2019 and 2020 taxi trips the Zoomcamp loads, TPC-H at scale factor 10 or the 3.6 GB IMDb snapshot behind the Join Order Benchmark are all enough on a laptop.

How do I present a SQL project on GitHub?+

Open the README with the problem and a diagram, followed by what you changed from the original repository. Under them go 3 numbers. Rows processed and runtime are 2 of them, and the third is what a rerun changes. Then show the proof, such as check results or a reconciliation against the source, and a before and after query plan if you tuned anything. Keep the SQL in files a reviewer can read in order, not inside a notebook.

02 / Why practice

The candidate who gets the offer

01
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
03
5 problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition

Start a mock interview

Related guides

SQL interview questions

The SQL questions data engineering interviews ask, with answers

SQL practice problems

Graded SQL problems to practise the patterns these projects use

The SQL interview round

How the SQL round runs and how to prepare for what it tests

SCD interview questions

Slowly changing dimension questions, from type 1 to type 2 and beyond

SQL upsert: INSERT ON CONFLICT and MERGE

The statements behind idempotent loads and change data capture

The data engineering challenge

Dirty, production-shaped data, scored blind each week.

SQL Projects for Data Engineers

What makes a SQL project a data engineering project

The 12 SQL projects at a glance

How to turn a cloned SQL project into your own

Which SQL project to start with

Loads you can run twice

Idempotent taxi loads with a hashed key and MERGE

Deduplicate taxi trips and load the fact table incrementally in dbt

A bronze, silver and gold warehouse in pure SQL

Know SQL projects the way the interviewer who asks it knows it.

What happens when a SQL load runs twice

Models that keep history

A Kimball star schema from AdventureWorks in dbt

A type 2 dimension kept by a stream, a task and MERGE

Sessionize GA4 events with the dbt-GA4 package

How a type 2 dimension keeps history

Both Ends of the Pipe

How sessionization turns events into sessions

Run the sessionization query on 2 visitors

Volume and speed

Interval-based incremental models with SQLMesh

Tune TPC-H queries in PostgreSQL from their plans

Find the bad estimates in the Join Order Benchmark

Production habits

Change data capture from PostgreSQL with wal2json, applied with MERGE

Data tests, unit tests and contracts in CI on jaffle-shop

Reconcile a rebuilt model with dbt-audit-helper

Common mistakes in SQL portfolio projects

How to turn a finished SQL project into interview answers

Write down the numbers

Prepare 3 stories from the build

Rewrite the core query from a blank editor

Rehearse the follow-ups

How SQL projects show up in the SQL interview round

SQL projects FAQ

The candidate who gets the offer

Related guides