Data Career Hub
Menu
SQLPythonPySparkApache SparkKafkaAirflow
ProjectsProject 1 of 8

Beginner project · Project 1 of 8

CSV to Data Warehouse Pipeline

A small online shop exports orders as daily CSV files. Build a pipeline that loads them into a star schema so that sales can be reported reliably, even when files are resent or contain bad rows.

  • Beginner
  • Python · SQLite or PostgreSQL · SQL · pytest
  • 2 min read
  • Updated Oct 2026

Requirements

  • Load daily order CSV files into a local database
  • Model the data as a fact table and at least two dimensions
  • Reruns must not create duplicates
  • Bad rows are logged and counted, not silently dropped
  • Automated tests prove idempotency

Technology stack

Python, SQLite or PostgreSQL, SQL, pytest

Dataset

Generate your own CSV files with a small script, or use any public sample retail dataset whose licence allows reuse.

Business context

Reports built from hand-edited spreadsheets are slow and error-prone. A small, reliable pipeline with a clear model gives the shop consistent numbers every morning and is a good first project because every Data Engineering concept appears in miniature: modelling, idempotency, quality and testing.

Architecture

  1. Daily CSV files land in a data/ folder.
  2. A Python loader writes them to a staging table with upserts and an audit record.
  3. SQL builds dim_customer, dim_product and fact_order_line.
  4. Quality checks run; failures stop the report refresh.
A single-machine batch pipeline with staging, modelling and checks.

Start from the idempotent CSV loader tutorial, then add the modelling layer described in the star schema guide.

Build steps

  1. Design the star schema and write down the grain of the fact table
  2. Write the staging loader (see the idempotent loader tutorial)
  3. Write SQL to build dimensions from staging with upserts
  4. Build the fact table by overwriting each loaded date
  5. Add data-quality checks and an audit table
  6. Write tests, including a run-twice test
  7. Write a README with a diagram and how to run it

Testing

  • Unit tests for row parsing
  • A test that loads the same file twice and compares outputs
  • A test with a corrected file that updates values

Data quality

  • Row counts match between source file and staging (minus logged bad rows)
  • Primary keys unique in every dimension
  • Every fact row joins to its dimensions

Monitoring

  • Log rows read, loaded and rejected per file
  • Record each load in an audit table with a timestamp

Cost considerations

  • Runs locally at no cost
  • Moving to a cloud database adds storage and compute cost; size accordingly

Interview questions this project prepares you for

  • What is the grain of your fact table?
  • What happens if the same file is loaded twice?
  • How do you handle a bad row?
  • How would this change for 100 times more data?

Resume bullets (add only what you measured)

Use these as structure. Replace the wording with results you actually produced and can show; never claim numbers you did not measure.

  • Built an idempotent Python and SQL pipeline that loads daily order files into a star schema, with automated tests proving reruns do not duplicate data
  • Implemented data-quality checks and an audit log that record rows read, loaded and rejected per file

Extensions

  • Schedule it with Airflow
  • Add slowly changing dimension type 2 for customers
  • Move storage to a cloud warehouse

By Data Career Hub Editorial · Last reviewed Oct 2026

Progress is saved in this browser only. No account needed.

Search
Filter by type