PySpark and SQL Projects for Data Engineers
Apply what you practise to a complete piece of data work. Each project turns a realistic business scenario into an ordered set of tasks that you write and run in the browser, and the graded projects end with a summary you can copy into a resume or README.
E-commerce Sales Analytics
Report on customers, order activity and revenue for an online store using SQL.
SQL · Beginner · about 70 minutes · 7 required tasks
Skills: INNER and LEFT joins, Anti-joins, GROUP BY aggregates (COUNT, AVG, SUM, MAX), Monthly date grouping, Top-N reporting
Customer Data Cleanup
Audit a customer-data migration, remove duplicates and bad values, and keep address history using SQL.
SQL · Intermediate · about 80 minutes · 6 required tasks
Skills: Anti-joins, Duplicate detection with GROUP BY and HAVING, ROW_NUMBER() deduplication, CAST and REGEXP validation, LAG() over history tables
Incremental Customer ETL
Fix, enrich and incrementally update a customer table with PySpark DataFrames.
PySpark · Intermediate · about 85 minutes · 5 required tasks
Skills: DataFrame joins, dropDuplicates, Window functions (row_number), Broadcast joins, CDC-style incremental updates
Prefer a single long-form build? See the Log Analysis Dashboard PySpark project.
What an end-to-end data engineering project involves
A data engineering project is more than one query. Interviewers ask you to walk through the whole path the data takes, so each project here practises part of that path:
Ingest
Read raw data into a table or DataFrame with a known schema. Unstructured text needs a parser before anything else can happen.
Practise it: Log Analysis Dashboard: regex-parse Apache log lines into a typed DataFrame
Clean and validate
Remove duplicate records, reject malformed values and check that nothing went missing before anyone trusts the data.
Practise it: Customer Data Cleanup: anti-join audits, ROW_NUMBER() de-duplication, CAST and REGEXP checks; Incremental Customer ETL: fix a join that duplicates customers
Join and enrich
Combine sources without multiplying rows, and add reference data such as region names from a small lookup table.
Practise it: E-commerce Sales Analytics: INNER, LEFT and anti-joins; Incremental Customer ETL: a broadcast join against a lookup table
Load incrementally
Apply only what changed: keep the latest record per key and merge change-data-capture (CDC) updates instead of reloading everything.
Practise it: Incremental Customer ETL: latest record per customer and CDC updates
Keep history
Preserve how a record changed over time instead of overwriting it, so reports can show both the current and the previous value.
Practise it: Customer Data Cleanup: current and previous address with LAG()
Aggregate and report
Turn clean data into the numbers people ask for: counts, monthly revenue, top-N lists and error rates.
Practise it: E-commerce Sales Analytics: monthly revenue and top customers; Log Analysis Dashboard: status codes, error rate, top IPs and bytes transferred
All four projects are batch pipelines over small sample data, so they run in seconds. For event streams, read the guide to Spark Structured Streaming.
Storage formats and table history are covered in the Delta Lake guide.
Partitioning and output-file control are covered in repartition vs coalesce.
The tasks in the three graded projects are checked against hidden test data as well as the sample, so a solution has to handle edge cases. The Log Analysis Dashboard is a runnable script you edit and execute rather than a graded task.
Compare the projects
| Project | Stack | Level | Data | What you build |
|---|---|---|---|---|
| E-commerce Sales Analytics | SQL | Beginner | Two related MySQL tables, customers and orders, shared by every task, so each report builds on the one before it. | An orders-with-customer-names listing and a list of customers who have never ordered; Per-customer order counts, average order amounts and most recent order dates; A monthly revenue report and a top-5 customers-by-spend leaderboard |
| Customer Data Cleanup | SQL | Intermediate | A small MySQL practice dataset per task: customer tables before and after a migration, order and transaction-amount tables, and an address-history table. | A list of customers missing after the migration and a duplicate-email report; De-duplicated customer and order records, and a set of valid numeric transaction amounts; A current-versus-previous address view built from an address history table |
| Incremental Customer ETL | PySpark | Intermediate | A small PySpark practice DataFrame per task: customers and orders, a region lookup table, and a set of change records with update timestamps. | A corrected orders-to-customers join and a de-duplicated customer table; Customers enriched with region names using a broadcast join; A latest-record-per-customer view and an applied CDC update set |
| Log Analysis Dashboard | Python + PySpark | Intermediate | 15 Apache-format web server log lines embedded in the script; no upload needed. | A regex parser, a typed DataFrame and seven reports: status codes, error rate, top IPs, popular endpoints and bytes transferred. |
A suggested order
- E-commerce Sales Analytics: Start with joins and aggregates on two tables.
- Customer Data Cleanup: Then clean, de-duplicate and validate data, the work that comes before any report.
- Incremental Customer ETL: Move the same ideas into a PySpark pipeline with broadcast joins and CDC updates.
- Log Analysis Dashboard: Finish by parsing raw text into a DataFrame and reporting on it.
Talking about a project in an interview
- Start with the problem and the data: what came in, what was wrong with it and who needed the result.
- Explain one decision per stage, for example why a broadcast join suits a small lookup table, or why you keep only the latest change per key.
- Say how you knew it was right: the checks you ran, the edge cases you handled and what the output looked like.
Completing every required task in a graded project unlocks a factual resume bullet and a README template that describe exactly what you built.