Azure Data Engineering Projects for Your Portfolio

A portfolio is not judged on how many services it touches. It is judged on whether each project answers a question an interviewer would ask: can you load many tables without copying a pipeline forty times, can you load only what changed, what do you do with bad rows, can you model data, will anyone know when it fails? These five Azure data engineering projects are built around those five questions.

Each uses public data, names the technique it demonstrates, and links to the article that explains that technique in detail. Build them in order; each reuses what the one before it left in the lake.

Before you start: cost and habits

Know what the free account gives you. Microsoft's documentation says eligible new users get a 200 dollar credit for the first 30 days, and limited quantities of some services free for 12 months. When the credit runs out or expires, the subscription is disabled unless you upgrade (Microsoft Learn: avoid charges with a free account). A free account has a spending limit equal to the credit, on by default (Microsoft Learn: spending limit). Thirty days is enough for all five projects if you plan them first.

After upgrading, set a budget on day one. A budget with an alert at a small threshold tells you when something is running that should not be (Microsoft Learn: create and manage budgets).

Know which things bill while idle. These are the usual surprises:

• A dedicated SQL pool that is not paused.
• A data flow debug session left on.
• A Spark or Databricks cluster with no auto-termination.
• An Azure-SSIS integration runtime left started.

Pipelines and storage, in contrast, cost little at this scale.

Habits from the first day:

• One resource group per project, deleted when you finish.
• Git connected to the data factory before you build anything.
• A README started on day one, not written at the end.
• Managed identity for every connection that supports it. A repository with a storage key in it is worse than no repository.

Project 1: metadata-driven ingestion into a lake

The question it answers: can you ingest many objects with one pipeline?

Data. Monthly yellow taxi trip files, published as Parquet with a zone lookup table (NYC TLC: trip record data). Each month is one file of a few million rows.

Build.

1. A storage account with a hierarchical namespace, and a raw container.
2. A control table listing the months to load, each with a status.
3. A pipeline with a Lookup over the control table, a ForEach, and one parameterized Copy activity that downloads a month's file over HTTP into a folder named for that year and month.
4. A log table recording, per file, rows copied, duration and the pipeline run ID.

Prove it. Add a row for a new month and run the pipeline without changing it. Then run it twice for the same month and show that the folder holds one copy, not two.

What it demonstrates: parameters, Lookup and ForEach, a dated folder layout, safe reruns and logging. See metadata-driven pipelines.

Project 2: an incremental load, and its limits

The question it answers: can you load only what changed, and do you know what that misses?

Data. An Azure SQL Database created with the AdventureWorksLT sample, which the creation screen offers as a data source (Microsoft Learn: create a single database). Azure SQL Database has a free offer with a monthly allowance of compute and storage, and an option to pause when the allowance is used up (Microsoft Learn: Azure SQL Database free offer).

Build.

1. A watermark table, and a pipeline with two Lookups, a Copy of the rows between the old and new watermark, and a step that moves the watermark only after the copy succeeds.
2. An upsert into a target table on the key.
3. Then the interesting part: change the source yourself. Update some rows, insert some, delete one, and insert one with an old modified date.

Prove it. Show a table of what the pipeline caught and what it missed. The delete and the backdated row will be missing, and the row counts may still match.

What it demonstrates: that you understand the pattern well enough to state its failure modes, which is rarer than being able to build it. Incremental data loading in Azure Data Factory covers both the pattern and the gaps.

Project 3: cleaning with Spark, and rules that reject rows

The question it answers: what do you do when the data is wrong?

Data. The taxi files from project 1. They are real, so they are imperfect. The January 2024 file, for instance, contains a handful of pickup timestamps from other years. Find such things yourself; do not assume.

Build.

1. A notebook, in Azure Databricks or a Synapse Spark pool, that reads one month from the raw zone.
2. Explicit rules: pickup time inside the file's month, dropoff after pickup, non-negative fare, a pickup zone that exists in the lookup table.
3. Rows that pass go to a cleaned zone as Parquet or Delta, partitioned by date. Rows that fail go to a quarantine folder with the name of the rule they broke.
4. The notebook returns the two counts, and the pipeline from project 1 calls it for each month and logs them.

Prove it. A small table per month: rows read, rows kept, rows rejected by each rule. The numbers must add up.

What it demonstrates: data quality as code, quarantine instead of silent deletion, and a pipeline orchestrating Spark with parameters in and results out. See Azure Databricks with ADF.

Project 4: a dimensional model with history

The question it answers: can you model data, and keep history when it changes?

Data. The cleaned trips and the zone table.

Build.

1. A fact table of trips at a daily grain per pickup zone, and dimensions for zone and date.
2. Load them into Azure SQL Database, or define them as views over the lake with a serverless SQL pool. Avoid a dedicated SQL pool unless you are certain you will pause it.
3. Make the zone dimension Type 2: a surrogate key, effective and expiry dates, a current flag. Then change a zone's borough in your source copy and reload.

Prove it. Show the dimension with two versions of the changed zone, a query that reports trips against the borough that applied at the time, and a second reload with no changes that adds no rows.

What it demonstrates: star schema design, surrogate keys, slowly changing dimensions, and idempotent loads. See slowly changing dimensions in ADF data flows.

Project 5: event-driven files with alerts

The question it answers: will anyone know when it breaks?

Data. The U.S. Geological Survey publishes earthquake feeds as GeoJSON for programmatic use (USGS: GeoJSON summary format). The feeds are small, change continuously, and are free to read. Poll them gently, once an hour is plenty.

Build.

1. A scheduled pipeline that fetches the hourly feed with the REST connector and writes it to a landing folder, one file per run.
2. A second pipeline started by a storage event trigger when a file lands, taking the file name as a parameter. It validates the file, flattens the features into rows, and writes them to a table with the earthquake ID as the key, so repeated events do not create duplicates.
3. A failure path that moves a bad file to a rejected folder and fails the pipeline.
4. Diagnostic settings sending run logs to Log Analytics, and an alert rule on failed pipeline runs.

Prove it. Drop a deliberately broken file into the landing folder. Show the rejected file, the failed run, the alert that arrived, and the log query that finds it.

What it demonstrates: API ingestion, event triggers, idempotent loading, failure handling and monitoring. See event-based triggers and monitoring and alerts.

Presenting the work

An interviewer will spend minutes, not hours, on your repository. Make those minutes count.

Leave out secrets, account names you would not want public, and any claim you cannot demonstrate. If a project is torn down to save money, say so, and keep the screenshots and the code.

Write the numbers down as you go. A sentence that gives the months loaded, the rows and the minutes taken is a different statement from "built a scalable pipeline", and only one of them can be checked. Use your own measured figures; never borrow someone else's.

A note on Fabric

Microsoft now points people new to data integration towards Microsoft Fabric. All five projects translate: pipelines, a lakehouse, notebooks, a warehouse and scheduled or event-driven runs exist there too. If you have access to a Fabric trial, rebuilding one of these projects in it and writing up the differences is a strong sixth item.

Mistakes to avoid

• Tutorial copies. A walkthrough followed step by step demonstrates that you can follow steps.
• Tiny toy data. Ten rows hide every problem worth discussing.
• No reruns. A pipeline shown only on its first run has not been shown.
• Everything in one giant pipeline.
• A bill you did not expect, from something left running.
• Results you cannot reproduce because the resources are gone and nothing was recorded.

For projects that are not tied to one cloud, see data engineering projects for your portfolio.

Azure Data Factory, Synapse and Databricks are covered in the Data Engineering course, which includes ETL pipeline, data warehouse and data lake projects.

Sources

• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• GeoJSON Summary Format (U.S. Geological Survey) — U.S. Geological Survey
• Quickstart: Create a single database in Azure SQL Database (Microsoft Learn) — Microsoft Learn
• Try Azure SQL Database for free (Microsoft Learn) — Microsoft Learn
• Avoid charges with your Azure free account (Microsoft Learn) — Microsoft Learn
• Azure spending limit (Microsoft Learn) — Microsoft Learn
• Tutorial: Create and manage budgets (Microsoft Learn) — Microsoft Learn