Data Modeling Projects for Data Engineers

The 11 projects sit in 4 tiers. Grain and the star comes first: a star schema, a role-playing dimension and a bridge table, which together teach the grain decision and the fan-outs that break it. Time and history adds a periodic snapshot and a Type 2 dimension, 2 of the ways a model records time. Events and change starts with nested event data and an activity stream, then handles a source whose schema moves. Integration and serving uses a bus matrix and a data vault to show how many models fit together, and ends with a semantic layer.

Each tier assumes the one before it. A Type 2 dimension is only useful to a fact whose grain you trust, an activity stream is easiest to judge against a star you've built, and a semantic layer compiles wrong numbers from any model with an unclear grain. Finishing 2 or 3 projects is worth more than starting 6: a reviewer reads a finished model with tests and a README faster than a folder of half-built ones, and each finished project is a set of decisions you can defend in an interview.

A dataset makes a good modeling project when it has more than 1 business process, which forces dimensions to be shared. It also needs a published data dictionary that documents what the codes and measures mean, and it needs change, so the model has to handle rows that arrive or change and fields that appear. The NYC TLC trip records have all 3. A single static CSV has none, which is why most tutorial star schemas teach so little.

Look for change first. A source that publishes monthly (the TLC trip files, bikeshare trip files), one that republishes its full population on a schedule, or a live feed (GBFS, Wikimedia EventStreams) forces the decisions that matter: how the model loads incrementally, how history is kept, and what happens when a field appears. Then read the licence. Olist's CC BY-NC-SA 4.0 is fine for a portfolio and wrong for a product.

Benchmark generators fill the remaining gap. TPC-DS and TPC-H produce a retailer's data at any scale factor inside DuckDB, with schemas large enough to need a bus matrix and queries you can check your model against. Once you've finished a project from this list, point the same model at a second dataset of your choice; the second build is where you find out which decisions were the dataset's and which were yours.

A staging layer that renames columns, followed by a mart that is the source schema again, isn't a model: nothing in it declares a grain or conforms a dimension, and it answers no question faster than the source did. Start every model from the business process and the question, and let the source schema be an input.

A fact table whose grain nobody wrote down gets a second grain the first time someone joins a table at a different level, as order payments do when joined onto order lines. Write the grain as a sentence in the model's documentation and enforce it with a uniqueness test on the columns the sentence names.

Balances and inventory levels are semi-additive across time, as is bikes available, and ratios are non-additive everywhere. A model that exposes them without saying how they aggregate invites the wrong dashboard. Joining 2 fact tables row by row before aggregating fans out and double counts; drill across through conformed dimensions instead, as the TPC-DS mart does.

A Type 2 dimension with valid_from and valid_to that no fact joins by date proves nothing. Ship the point-in-time join, and a test that every fact row finds exactly 1 version. And a cloned repository with no change of your own is a tutorial, not a project: reviewers recognise the popular ones, so the extension is the part they ask about.

Management schemas for libraries and schools or for hospitals and hotels are normalized designs for software that writes rows. A reviewer looks in them for a declared grain or kept history, and for someone who consumes the model. Finding none, the reviewer reads them as a signal for the wrong job.

Write the grain of every table as 1 sentence, and learn the sentences. The first question about any model you present will be what 1 row means, and a sentence you can say without looking beats a diagram you have to read aloud.

Keep a decisions log of 3 to 5 entries per project: the decision, the alternative you rejected and why. For the taxi project, 1 fact with a service type against 2 facts; for the Type 2 project, the check strategy against the timestamp strategy; for the data vault, a vault against a star loaded directly. Interviewers probe decisions more than syntax, and a log turns each entry into a prepared answer.

Break the model on purpose and keep the evidence: a duplicate row that fails the uniqueness test, a late fact that finds no dimension version, a new field that fails the schema version test. Describing what failed and how the model caught it is the most convincing thing you can say about a model you built.

Then say it out loud under time pressure, which is a separate skill from building. The data modeling interview round guide explains how the round runs and what interviewers score, so you can map each project to the questions it answers.

A project teaches the decisions; an interview asks you to make them in 45 minutes on a blank canvas for a business you have never seen. Close that gap by drilling the same patterns on fresh prompts: work through the data modeling interview questions that cover grain and keys along with slowly changing dimensions and fact table types, then design full models under a clock in the data modeling practice problems, which you draw on the same canvas as the diagrams in the project tiers.