Azure Data Engineer Projects

Quick answer

Pick Azure data engineer projects that match your level: beginners start with Azure Data Factory, Azure SQL and simple PySpark; intermediate learners add incremental loads, Delta Lake MERGE, Lakeflow and Fabric; advanced engineers build streaming, infrastructure as code, governance and AI pipelines. This page lists 90 projects, 30 per level, and every project has a business question, a free dataset, steps, key code, a validation check and a resume line. Build two or three of them fully, put them on GitHub and practise explaining them in 2 minutes.

Key takeaways

1. All 90 Azure data engineer projects at a glance

Short answer: Use these three tables to pick projects by level and by the skill you want to show. Project numbers match the detailed sections below.

Beginner projects (1–30)

Intermediate projects (31–60)

Advanced projects (61–90)

2. Which level should you start with?

Short answer: Start at the level that matches what you can already build without a tutorial, and move up once you can explain every step of two projects at that level.

Accounts and tools you need:

How to keep cost low:

If you are completely new to Azure, follow our Azure data engineer roadmap alongside the beginner projects.

3. Beginner projects 1–5: Azure Data Factory basics

Project 1: Copy CSV from Blob Storage to Azure SQL

Level: Beginner · Time: 1 day · Tools: Azure Data Factory, Blob Storage, Azure SQL Database
Business question: How many electric vehicles are registered by county and make in Washington State? The transport team wants the data in a SQL table they can query.
Dataset: Electric Vehicle Population Data (published by the Washington State Department of Licensing on data.gov, available as CSV)

Steps:

1. Download the CSV and upload it to a Blob Storage container named raw, folder ev/.
2. In ADF, create a linked service for Blob Storage and one for Azure SQL Database, both using the default Azure integration runtime.
3. Create a DelimitedText dataset with "First row as header" and an Azure SQL dataset for the staging table below.
4. Add a Copy activity. On the Mapping tab, import schemas and map columns such as Model Year to model_year. Add the pre-copy script TRUNCATE TABLE dbo.stg_ev_population so reruns do not duplicate rows.
5. Debug the pipeline, then check rowsCopied in the Copy activity output.

CREATE TABLE dbo.stg_ev_population (
    vin_prefix NVARCHAR(10),
    county NVARCHAR(50),
    city NVARCHAR(100),
    state NVARCHAR(5),
    model_year NVARCHAR(10),
    make NVARCHAR(50),
    model NVARCHAR(100),
    ev_type NVARCHAR(100),
    electric_range NVARCHAR(10),
    dol_vehicle_id NVARCHAR(20),
    load_ts DATETIME2 DEFAULT SYSUTCDATETIME()
);

Validation check: rowsCopied in the Copy activity output must equal SELECT COUNT(*) FROM dbo.stg_ev_population. Then test whether dol_vehicle_id is unique with SELECT dol_vehicle_id FROM dbo.stg_ev_population GROUP BY dol_vehicle_id HAVING COUNT(*) > 1 and write down what you find.
What it proves: you can build the core ADF objects (linked service, dataset, pipeline, Copy activity), map columns and make a load safe to rerun.
Resume line: "Built an Azure Data Factory pipeline that loads Washington State EV registration data from Blob Storage into Azure SQL, with column mapping and rerun-safe truncate-and-load."

Project 2: Load many files with wildcard paths and Get Metadata

Level: Beginner · Time: 1 day · Tools: Azure Data Factory, ADLS Gen2, Azure SQL Database
Business question: Can we load a month of daily files in one run and still know which file every row came from? This is the normal shape of vendor and partner data.
Dataset: Johns Hopkins CSSE COVID-19 daily reports (one CSV per day named MM-DD-YYYY.csv; the repository was archived in March 2023, so the files no longer change)

Steps:

1. From the csse_covid_19_data/csse_covid_19_daily_reports folder, download the 31 files for January 2021 and upload them to raw/jhu/2021/01/. Use 2021 files, because the column names changed in early 2020.
2. Add a Get Metadata activity on the folder with the field list childItems. Get Metadata does not support wildcard filters, so add a Filter activity with the condition @endswith(item().name, '.csv').
3. Add a Copy activity with a wildcard file path and an additional column source_file set to $FILEPATH.
4. Sink to an Azure SQL staging table with all columns as text plus source_file.
5. Store the filtered file count in a variable: @string(length(activity('FilterCsv').output.value)).

"source": {
  "type": "DelimitedTextSource",
  "storeSettings": {
    "type": "AzureBlobFSReadSettings",
    "recursive": false,
    "wildcardFolderPath": "jhu/2021/01",
    "wildcardFileName": "01-*-2021.csv"
  },
  "additionalColumns": [
    { "name": "source_file", "value": "$FILEPATH" }
  ]
}

Validation check: SELECT COUNT(DISTINCT source_file) FROM dbo.stg_jhu_daily must equal the file count from the Filter activity (31 for January).
What it proves: wildcard loading, Get Metadata and Filter activities, and file-level lineage, which helps you debug bad rows later.
Resume line: "Loaded a month of daily CSV files in one ADF run using wildcard paths and Get Metadata, adding a source-file column for row-level lineage."

Project 3: Convert Excel files to Parquet

Level: Beginner · Time: 1 day · Tools: Azure Data Factory, ADLS Gen2
Business question: The finance team sends Excel workbooks. How do we turn them into Parquet files that Spark and SQL engines can read quickly?
Dataset: UCI Online Retail II (about 1,067,371 transactions from a UK online retailer, December 2009 to December 2011, in one .xlsx file)

Steps:

1. Download and unzip the file, then upload online_retail_II.xlsx to raw/online_retail/.
2. Open the workbook and note its sheet names. Create a pipeline parameter sheets (Array) with those names.
3. Create an Excel dataset with a sheet parameter, as below. ADF supports Excel as a source only, not as a sink.
4. Add a ForEach over @pipeline().parameters.sheets with a Copy activity inside. Sink: a Parquet dataset with folder bronze/online_retail/sheet=@{item()}.
5. Run the pipeline and open one Parquet file in Synapse serverless or Databricks to check the column types.

{
  "name": "ds_retail_excel",
  "properties": {
    "type": "Excel",
    "linkedServiceName": { "referenceName": "ls_adls", "type": "LinkedServiceReference" },
    "parameters": { "sheet": { "type": "string" } },
    "typeProperties": {
      "location": { "type": "AzureBlobFSLocation", "fileSystem": "raw",
                    "folderPath": "online_retail", "fileName": "online_retail_II.xlsx" },
      "sheetName": { "value": "@dataset().sheet", "type": "Expression" },
      "firstRowAsHeader": true
    }
  }
}

Validation check: add up rowsCopied across the ForEach iterations and compare it with the instance count on the UCI page (1,067,371). If the numbers differ, find out why (empty rows, a header row inside the data) before you move on.
What it proves: Excel ingestion, parameterized datasets, ForEach loops and columnar Parquet output.
Resume line: "Converted a 1M-row Excel workbook into partitioned Parquet in ADLS Gen2 with a parameterized ADF pipeline that loops over sheets."

Project 4: Storage event trigger for new files

Level: Beginner · Time: 1 day · Tools: Azure Data Factory, Event Grid, ADLS Gen2, Azure SQL Database
Business question: Can we load a file as soon as it lands, instead of waiting for a nightly schedule?
Dataset: Johns Hopkins CSSE COVID-19 daily reports (upload one day's file at a time to simulate daily arrivals)

Steps:

1. Register the Microsoft.EventGrid resource provider in your subscription. Storage event triggers depend on Azure Event Grid.
2. Create a pipeline pl_load_one_file with parameters folderPath and fileName, and a Copy activity that loads that single file into the staging table from Project 2.
3. Create a storage event trigger: blob path begins with /landing/blobs/jhu/, ends with .csv, event "Blob created", and "ignore empty blobs" set to Yes.
4. Map @triggerBody().folderPath and @triggerBody().fileName to the pipeline parameters and publish.
5. Upload three daily files one by one and watch the runs in the Monitor tab.

{
  "name": "tr_new_jhu_file",
  "properties": {
    "type": "BlobEventsTrigger",
    "typeProperties": {
      "blobPathBeginsWith": "/landing/blobs/jhu/",
      "blobPathEndsWith": ".csv",
      "ignoreEmptyBlobs": true,
      "scope": "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<storage>",
      "events": [ "Microsoft.Storage.BlobCreated" ]
    }
  }
}

Validation check: three uploads must create exactly three pipeline runs, and SELECT COUNT(DISTINCT source_file) FROM dbo.stg_jhu_daily must grow by three. A zero-byte file must not start a run.
What it proves: event-driven pipelines, trigger parameters and the link between ADF and Event Grid.
Resume line: "Built an event-driven ADF pipeline that loads each new file within minutes of arrival using a storage event trigger and trigger-to-pipeline parameters."

Project 5: Archive and clean up processed files

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (Copy, Delete, Get Metadata, ForEach), ADLS Gen2
Business question: After a file is loaded, how do we move it out of the landing folder so it is never loaded twice, while keeping a copy for audits?
Dataset: Johns Hopkins CSSE COVID-19 daily reports (the files loaded in Project 4)

Steps:

1. Turn on blob soft delete on the storage account. The ADF Delete activity cannot be undone without it.
2. Get Metadata (childItems) on landing/jhu/, then Filter to files only.
3. ForEach file: a Copy activity (Binary dataset) copies it to archive/jhu/@{formatDateTime(utcnow(),'yyyy/MM/dd')}/.
4. On Copy success only, a Delete activity removes the same file from landing, with logging turned on.
5. Add a second pipeline that deletes archive files older than 30 days using the modifiedDatetimeEnd filter.

{
  "name": "DeleteFromLanding",
  "type": "Delete",
  "dependsOn": [ { "activity": "CopyToArchive", "dependencyConditions": [ "Succeeded" ] } ],
  "typeProperties": {
    "dataset": { "referenceName": "ds_landing_file", "type": "DatasetReference",
                 "parameters": { "fileName": "@item().name" } },
    "enableLogging": true,
    "logStorageSettings": {
      "linkedServiceName": { "referenceName": "ls_adls", "type": "LinkedServiceReference" },
      "path": "logs/delete"
    }
  }
}

Validation check: after a run, landing/jhu/ holds 0 files, the archive folder holds the same number of files as the Filter output, and the delete log lists exactly those file names.
What it proves: the "move file" pattern (Copy then Delete), success dependencies and safe housekeeping.
Resume line: "Automated file archiving in ADF with Copy and Delete activities, delete logging and soft delete, so landing folders stay clean and every file is kept for audit."

4. Beginner projects 6–10: APIs and parameters

Project 6: Public holidays API for several countries

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (REST connector, ForEach), Azure SQL Database
Business question: Which days are public holidays in the countries where our support team works? Planning and SLA reports need this calendar.
Dataset: Nager.Date public holiday API (free and keyless; each holiday has date, localName, name, countryCode, global and types. India was not in the API's country list when we checked, so use countries such as GB, US, DE, AU, SG, CA and JP)

Steps:

1. Create a REST linked service with base URL https://date.nager.at/api/v3/.
2. Create a REST dataset with a relativeUrl parameter.
3. Add pipeline parameters year (String) and countries (Array, for example ["GB","US","DE","AU","SG"]).
4. ForEach country: a Copy activity with relative URL PublicHolidays/@{pipeline().parameters.year}/@{item()} and a sink to dbo.public_holiday. Map date, localName, name, countryCode and global.
5. Use the pre-copy script below, so rerunning a year replaces it instead of duplicating it.

CREATE TABLE dbo.public_holiday (
    country_code CHAR(2) NOT NULL,
    holiday_date DATE NOT NULL,
    local_name NVARCHAR(200) NULL,
    english_name NVARCHAR(200) NULL,
    is_global BIT NULL
);
-- Pre-copy script in the Copy activity sink (dynamic content):
-- DELETE FROM dbo.public_holiday
-- WHERE country_code = '@{item()}'
-- AND YEAR(holiday_date) = @{pipeline().parameters.year}

Validation check: after running the same year twice, SELECT country_code, holiday_date, english_name FROM dbo.public_holiday GROUP BY country_code, holiday_date, english_name HAVING COUNT(*) > 1 returns no rows, and every country in the parameter has at least one row.
What it proves: REST ingestion, ForEach with dynamic URLs, JSON-to-table mapping and rerun-safe loads.
Resume line: "Ingested multi-country public holiday data from a REST API into Azure SQL with a parameterized ADF ForEach pipeline that is safe to rerun."

Project 7: Daily currency exchange rates API

Level: Beginner · Time: 1 day · Tools: Azure Data Factory, ADLS Gen2, Azure SQL Database, schedule trigger
Business question: What is the daily USD to INR, EUR and GBP rate, so the finance report can convert sales into rupees?
Dataset: Frankfurter exchange rates API (free, no key; rates from central banks and official sources, updated daily)

Steps:

1. Create a REST linked service with base URL https://api.frankfurter.dev/.
2. Daily run: relative URL v2/rates?base=USD&quotes=INR,EUR,GBP. The response is a JSON array of records with date, base, quote and rate.
3. First Copy activity: save the raw response to raw/fx/load_date=@{formatDateTime(utcnow(),'yyyy-MM-dd')}/rates.json.
4. Second Copy activity: load the same JSON into dbo.stg_fx_rate (truncate first), then a Stored Procedure activity runs the MERGE below.
5. Add a schedule trigger once a day. For a backfill, use the time-series form v2/rates?base=USD&quotes=INR&from=2026-01-01.

MERGE dbo.fx_rate AS t
USING dbo.stg_fx_rate AS s
   ON t.rate_date = s.[date]
  AND t.base = s.base
  AND t.quote = s.quote
WHEN MATCHED AND t.rate <> s.rate THEN
    UPDATE SET t.rate = s.rate, t.updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (rate_date, base, quote, rate, updated_at)
    VALUES (s.[date], s.base, s.quote, s.rate, SYSUTCDATETIME());

Validation check: (rate_date, base, quote) must be unique, and running the pipeline twice on the same day must leave the row count unchanged. List the dates with no rate (for example weekends) and decide whether the report should carry the last rate forward.
What it proves: scheduled API loads, keeping raw responses, and idempotent upserts with MERGE.
Resume line: "Built a daily ADF pipeline that lands raw exchange-rate JSON in ADLS Gen2 and upserts it into Azure SQL with T-SQL MERGE, so reruns never duplicate rates."

Project 8: GitHub REST API with pagination

Level: Beginner · Time: 1–2 days · Tools: Azure Data Factory (REST connector), Key Vault, ADLS Gen2
Business question: How many issues and pull requests does an open-source project open and close each month? Engineering managers track this to plan maintenance.
Dataset: GitHub REST API pagination docs (pages are linked through the link response header; per_page is usually capped at 100). Example endpoint: https://api.github.com/repos/delta-io/delta/issues?state=all&per_page=100

Steps:

1. Create a GitHub fine-grained personal access token with read-only public access and store it in Key Vault. Unauthenticated calls have a low hourly limit.
2. Create a REST linked service with base URL https://api.github.com/. Under auth headers, add Authorization with the value Bearer <token> taken from a Key Vault secret reference, so the token never appears in the pipeline JSON.
3. Copy activity source: relative URL repos/delta-io/delta/issues?state=all&per_page=100, headers Accept: application/vnd.github+json and a User-Agent.
4. Pagination: with no rule, the ADF REST connector follows RFC 5988 link headers by default. As a controlled alternative, use a page range as below.
5. Sink: JSON files in raw/github/delta-issues/load_date=<date>/, then flatten number, state, created_at, closed_at and pull_request in a later step.

"source": {
  "type": "RestSource",
  "additionalHeaders": {
    "Accept": "application/vnd.github+json",
    "User-Agent": "brolly-adf-learning-project"
  },
  "paginationRules": {
    "QueryParameters.page": "RANGE:1:20:1"
  },
  "httpRequestTimeout": "00:01:40"
}

Validation check: the number of distinct issue number values you loaded must equal the total you count on the GitHub web page with the same filter (open plus closed), and no number may appear twice.
What it proves: REST pagination, request headers, rate-limit awareness and secret handling.
Resume line: "Ingested paginated GitHub issue data with the ADF REST connector, using pagination rules, custom headers and a token stored in Azure Key Vault."

Project 9: Parameterized pipeline per country with COVID-19 data

Level: Beginner · Time: 1–2 days · Tools: Azure Data Factory (Execute Pipeline, ForEach), Azure SQL Database, ADLS Gen2
Business question: Can analysts get a clean daily COVID-19 file for any country they choose, without a new pipeline per country?
Dataset: Our World in Data COVID-19 dataset (one row per location and date with iso_code, location, date, new_cases and new_deaths; OWID says the GitHub files have not been updated since 19 August 2024, which is fine for practice)

Steps:

1. Copy https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/owid-covid-data.csv with the HTTP connector into raw/owid/.
2. Copy the file into dbo.stg_owid_covid (truncate first), mapping only the columns you need.
3. Create a child pipeline pl_export_country with parameters iso_code and from_date. Its Copy activity uses the query below and writes Parquet to curated/covid/iso_code=@{pipeline().parameters.iso_code}/.
4. Create a parent pipeline with a parameter countries (for example ["IND","GBR","USA","BRA"]) and a ForEach that calls the child with Execute Pipeline ("Wait on completion" on).
5. Set the ForEach batch count to 4 so countries run in parallel.

SELECT iso_code,
       location,
       TRY_CAST([date] AS DATE) AS report_date,
       TRY_CAST(new_cases AS FLOAT) AS new_cases,
       TRY_CAST(new_deaths AS FLOAT) AS new_deaths
FROM dbo.stg_owid_covid
WHERE iso_code = '@{pipeline().parameters.iso_code}'
  AND TRY_CAST([date] AS DATE) >= '@{pipeline().parameters.from_date}';

Validation check: for each country, the child Copy activity's rowsCopied must equal SELECT COUNT(*) FROM dbo.stg_owid_covid WHERE iso_code = 'IND' AND TRY_CAST([date] AS DATE) >= '<from_date>' (change the code per country).
What it proves: parent and child pipelines, pipeline parameters and reusable design.
Resume line: "Designed a reusable parent-child ADF pipeline that exports country-level COVID-19 data to Parquet for any list of countries passed as a parameter."

Project 10: Export Azure SQL tables to ADLS by date folder

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (Lookup, ForEach, Copy), Azure SQL Database, ADLS Gen2
Business question: Can we keep a daily snapshot of every sales table in the lake, so analysts can see what the data looked like on any past day?
Dataset: AdventureWorksLT sample database (choose "Sample" as the data source when you create the Azure SQL Database)

Steps:

1. Create the Azure SQL Database with the AdventureWorksLT sample.
2. Add a Lookup activity (uncheck "First row only") with the table query below.
3. Add a ForEach over @activity('LookupTables').output.value.
4. Inside it, a Copy activity reads @{item().TABLE_SCHEMA}.@{item().TABLE_NAME} and writes Parquet using the folder and file expressions below.
5. Add a daily schedule trigger, and a storage lifecycle rule that deletes snapshot folders older than 90 days.

Lookup query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'SalesLT' AND TABLE_TYPE = 'BASE TABLE'

Sink dataset parameters (dynamic content):
folderPath = @concat('SalesLT/', item().TABLE_NAME, '/',
                     formatDateTime(pipeline().TriggerTime, 'yyyy/MM/dd'))
fileName = @concat(item().TABLE_NAME, '.parquet')

Validation check: every table from the Lookup has a folder for today's date, and each Copy activity's rowsCopied matches the row count from SELECT t.name, SUM(p.rows) FROM sys.tables t JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1) GROUP BY t.name.
What it proves: dynamic content, Lookup plus ForEach, date-partitioned folders and simple data retention.
Resume line: "Built a daily ADF snapshot pipeline that exports all AdventureWorksLT tables from Azure SQL to date-partitioned Parquet folders in ADLS Gen2."

5. Beginner projects 11–15: Data quality and operations

Project 11: Row-count check with Lookup and If Condition

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (Lookup, If Condition, Fail, Stored Procedure), Azure SQL Database
Business question: How do we stop a half-loaded or empty file from reaching the report table?
Dataset: Electric Vehicle Population Data (the staging load from Project 1)

Steps:

1. Reuse the Project 1 pipeline. Name the Copy activity CopyEV.
2. After it, add a Lookup activity CountStaged: SELECT COUNT(*) AS cnt FROM dbo.stg_ev_population.
3. Add an If Condition with the expression below. It passes only when the counts match and are above zero.
4. True branch: a Stored Procedure activity that loads dbo.ev_population from staging. False branch: a Fail activity with a clear message and error code.
5. In both branches, write the run ID, both counts and the result to dbo.load_audit.

If Condition expression:
@and(
    greater(activity('CopyEV').output.rowsCopied, 0),
    equals(activity('CopyEV').output.rowsCopied,
           activity('CountStaged').output.firstRow.cnt)
)

Fail activity message:
@concat('Row count mismatch: copied ', string(activity('CopyEV').output.rowsCopied),
        ', staged ', string(activity('CountStaged').output.firstRow.cnt))

Validation check: remove the pre-copy truncate and run twice. The second run must fail, because the staged count is now double the copied count, and the final table must not change.
What it proves: data quality gates, If Condition logic, the Fail activity and audit logging.
Resume line: "Added an automated row-count quality gate to an ADF pipeline that blocks partial or duplicate loads from reaching reporting tables and logs every check."

Project 12: Failure email alert with a Logic App

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (Web activity), Azure Logic Apps
Business question: When a nightly pipeline fails, how does the team find out before business users do?
Dataset: Microsoft Learn ADF email notification tutorial (no new data; reuse the pipeline from Project 1 or 11)

Steps:

1. Create a Consumption Logic App with the trigger "When an HTTP request is received" and a request body schema with dataFactoryName, pipelineName, runId, message and receiver.
2. Add a "Send an email" action (Office 365 Outlook or Outlook.com) that uses those fields in the subject and body. Save it and copy the HTTP POST URL.
3. Store that URL in Key Vault, because it contains a signature that works like a password.
4. In ADF, add a Web activity SendFailureEmail connected to the Copy activity's failure path (red arrow). Method POST, header Content-Type: application/json, body below.
5. Force a failure (rename the source file) and confirm the email arrives with the error text.

{
  "dataFactoryName": "@{pipeline().DataFactory}",
  "pipelineName": "@{pipeline().Pipeline}",
  "runId": "@{pipeline().RunId}",
  "message": "@{activity('CopyEV').error.message}",
  "receiver": "@{pipeline().parameters.alertEmail}"
}

Validation check: one forced failure must produce exactly one email that contains the run ID, and that run ID must match the failed run in the ADF Monitor tab. A successful run must send no email.
What it proves: failure paths, system variables, Web activity calls and basic incident response.
Resume line: "Implemented failure email alerts for ADF pipelines using a Logic App and Web activity, sending the pipeline name, run ID and error message."

Project 13: Store connection secrets in Key Vault with managed identity

Level: Beginner · Time: 1 day · Tools: Azure Key Vault, Azure Data Factory, managed identity, Azure SQL Database
Business question: How do we remove passwords from pipeline JSON and Git, so a leaked repository does not leak database access?
Dataset: Store credentials in Azure Key Vault (Microsoft Learn) (reuse the Azure SQL Database from Project 1)

Steps:

1. Create a Key Vault with Azure RBAC permissions and add a secret sql-admin-password.
2. Give the data factory's system-assigned managed identity the "Key Vault Secrets User" role on the vault.
3. Create a Key Vault linked service in ADF and test the connection.
4. Edit the Azure SQL linked service so the password field references the secret, as below.
5. Better still, switch the SQL linked service to managed identity authentication: in the database, run CREATE USER [<adf-name>] FROM EXTERNAL PROVIDER and add it to db_datareader and db_datawriter. Now no password is needed at all.

"password": {
  "type": "AzureKeyVaultSecret",
  "secretName": "sql-admin-password",
  "store": {
    "referenceName": "ls_keyvault",
    "type": "LinkedServiceReference"
  }
}

Validation check: search the exported ADF JSON (or your Git repo) for the password text and find zero matches. Then remove the managed identity's Key Vault role: the next run must fail at the linked service, which proves the secret really comes from Key Vault.
What it proves: secret management, managed identity, RBAC on Key Vault and secure linked services.
Resume line: "Secured ADF linked services by moving credentials to Azure Key Vault and using managed identity authentication to Azure SQL, removing all passwords from code."

Project 14: Copy only new files using last-modified filters

Level: Beginner · Time: 1 day · Tools: Azure Data Factory (tumbling window trigger), ADLS Gen2
Business question: Files keep arriving in one folder. How do we copy only the files that are new since the last run, without moving the originals?
Dataset: ADF incremental copy by LastModifiedDate tutorial (use the JHU daily files from Project 2 as test files)

Steps:

1. Create pipeline parameters windowStart and windowEnd.
2. In the Copy activity source, set the last-modified filter to these parameters, as below.
3. Create a tumbling window trigger every 15 minutes and map @trigger().outputs.windowStartTime and @trigger().outputs.windowEndTime to the parameters.
4. Upload one file, wait for the next window, then upload a second file.
5. Check which files each run copied in the Copy activity output.

"storeSettings": {
  "type": "AzureBlobFSReadSettings",
  "recursive": true,
  "wildcardFolderPath": "incoming",
  "wildcardFileName": "*.csv",
  "modifiedDatetimeStart": "@{pipeline().parameters.windowStart}",
  "modifiedDatetimeEnd": "@{pipeline().parameters.windowEnd}"
}

Validation check: each file must be copied exactly once. The total filesWritten across all windows must equal the number of files you uploaded, and a window with no uploads must copy 0 files.
What it proves: incremental file loading, tumbling windows, trigger outputs and why ADF still scans the whole folder (large folders make scans slow, so partition folders by date).
Resume line: "Built incremental file ingestion in ADF using last-modified filters and a tumbling window trigger, copying each new file exactly once."

Project 15: Access control on ADLS with RBAC and ACLs

Level: Beginner · Time: 1 day · Tools: ADLS Gen2, Azure RBAC, POSIX ACLs, Microsoft Entra groups, Azure CLI
Business question: Engineers must write to Bronze and Silver, while analysts may only read Gold. How do we enforce that in the data lake?
Dataset: Access control lists in Azure Data Lake Storage (Microsoft Learn) (use any files in bronze, silver and gold folders of a container named lake)

Steps:

1. Create Microsoft Entra security groups grp-data-engineers and grp-analysts. Always assign groups, not individual users.
2. RBAC: give engineers "Storage Blob Data Contributor" on the container. Give analysts no data role, because an RBAC role would override the ACLs.
3. ACLs: give analysts --x (execute) on the container root so they can traverse, then r-x on gold plus a default ACL so new files inherit it (CLI below).
4. Test as an analyst with Azure Storage Explorer: list and read gold, then try silver.
5. Note that default ACLs do not change files that already exist; use the recursive update for those.

# Analysts: read and list gold, now and for files created later
az storage fs access update-recursive \
  --acl "group:<analysts-object-id>:r-x,default:group:<analysts-object-id>:r-x" \
  --path gold --file-system lake \
  --account-name <storage> --auth-mode login

# Review the result
az storage fs access show --path gold --file-system lake \
  --account-name <storage> --auth-mode login

Validation check: as an analyst, reading a file in gold succeeds, while listing silver and writing to gold both fail with 403 errors. Record each test and its result in a table.
What it proves: the ADLS Gen2 security model: RBAC, access and default ACLs, the execute permission and group-based access.
Resume line: "Implemented folder-level security on ADLS Gen2 with Azure RBAC and POSIX ACLs, giving analysts read-only access to Gold data through Entra groups."

6. Beginner projects 16–20: SQL and modeling

Project 16: Star schema for Superstore sales in Azure SQL

Level: Beginner · Time: 2 days · Tools: Azure SQL Database, T-SQL, Azure Data Factory
Business question: What are sales and profit by month, region, category and customer segment? A star schema makes these questions simple and fast.
Dataset: Superstore dataset on Kaggle (one row per order line, with columns such as Order ID, Order Date, Customer ID, Segment, Region, Product ID, Category, Sales, Quantity, Discount and Profit)

Steps:

1. Load the CSV into dbo.stg_superstore with ADF (all text columns). If you see odd characters, check the file encoding in the dataset settings.
2. Create the dimension and fact tables below, plus dim_date with one row per day and date_key in yyyymmdd form.
3. Load dim_customer with SELECT DISTINCT on customer columns. For dim_product, group by product_id and take MIN(product_name), because one Product ID can appear with more than one name.
4. Load fact_sales by joining staging to the dimensions on business keys and converting dates with TRY_CONVERT(date, order_date, 101).
5. Write three report queries: sales by month, profit by region and the top 10 customers.

CREATE TABLE dbo.dim_customer (
    customer_key INT IDENTITY PRIMARY KEY, customer_id NVARCHAR(20) UNIQUE,
    customer_name NVARCHAR(100), segment NVARCHAR(30));
CREATE TABLE dbo.dim_product (
    product_key INT IDENTITY PRIMARY KEY, product_id NVARCHAR(30) UNIQUE,
    product_name NVARCHAR(255), category NVARCHAR(50), sub_category NVARCHAR(50));
CREATE TABLE dbo.fact_sales (
    order_id NVARCHAR(20), order_date_key INT, customer_key INT, product_key INT,
    region NVARCHAR(20), sales DECIMAL(12,4), quantity INT,
    discount DECIMAL(5,2), profit DECIMAL(12,4));

Validation check: the fact table must have the same row count and the same SUM(sales) as staging, and SELECT COUNT(*) FROM dbo.fact_sales WHERE customer_key IS NULL OR product_key IS NULL must return 0.
What it proves: dimensional modeling (facts, dimensions, surrogate keys, a date dimension) and handling real data problems in keys.
Resume line: "Designed and loaded a Superstore sales star schema in Azure SQL with surrogate keys and a date dimension, reconciled to the source on rows and revenue."

Project 17: SCD Type 1 upsert with T-SQL MERGE

Level: Beginner · Time: 1 day · Tools: Azure SQL Database, T-SQL MERGE, Azure Data Factory
Business question: When a customer changes their email or phone, the CRM report must show the latest value. History is not needed, so SCD Type 1 (overwrite) is the right choice.
Dataset: AdventureWorksLT sample database (SalesLT.Customer)

Steps:

1. Create schema dw and table dw.dim_customer (customer_id INT PRIMARY KEY, first_name, last_name, email, phone, updated_at).
2. Use ADF (or INSERT INTO ... SELECT) to copy SalesLT.Customer into dw.stg_customer each run.
3. Run the MERGE below in a stored procedure. EXISTS ... EXCEPT compares columns safely even when values are NULL, so unchanged rows are not updated.
4. Add OUTPUT $action to the MERGE to see how many rows were inserted or updated.
5. Change two customers' emails in SalesLT.Customer, rerun and check the output.

MERGE dw.dim_customer AS t
USING dw.stg_customer AS s
   ON t.customer_id = s.CustomerID
WHEN MATCHED AND EXISTS (SELECT s.FirstName, s.LastName, s.EmailAddress, s.Phone
                         EXCEPT
                         SELECT t.first_name, t.last_name, t.email, t.phone)
    THEN UPDATE SET t.first_name = s.FirstName, t.last_name = s.LastName,
                    t.email = s.EmailAddress, t.phone = s.Phone,
                    t.updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, first_name, last_name, email, phone, updated_at)
    VALUES (s.CustomerID, s.FirstName, s.LastName, s.EmailAddress, s.Phone, SYSUTCDATETIME());

Validation check: the first run inserts every customer; a second run with no source changes reports 0 inserts and 0 updates; after editing two emails, the next run reports exactly 2 updates.
What it proves: SCD Type 1, MERGE, NULL-safe change detection and idempotent loads.
Resume line: "Implemented an SCD Type 1 customer dimension in Azure SQL with a NULL-safe T-SQL MERGE that updates only changed rows."

Project 18: Deduplicate customers with Mapping Data Flow

Level: Beginner · Time: 1 day · Tools: ADF Mapping Data Flow (Derived Column, Window, Filter), Azure SQL Database
Business question: The sales file repeats each customer on every order line. How do we build one clean row per customer that shows their latest city and segment?
Dataset: Superstore dataset on Kaggle (customers repeat across many order lines)

Steps:

1. Turn on data flow debug (it starts a small Spark cluster; turn it off when you finish).
2. Source: the Superstore CSV in ADLS Gen2.
3. Derived Column: trim the ID, fix name casing and parse the order date.
4. Window: partition by customer_id, sort by order_dt descending, and add rn = rowNumber(). Then Filter rn == 1.
5. Select only the customer columns and sink to dbo.customer_clean with "Recreate table" or truncate.

source1 derive(customer_id = trim(toString({Customer ID})),
               customer_name = initCap(trim(toString({Customer Name}))),
               order_dt = toDate(toString({Order Date}), 'M/d/yyyy')) ~> Clean
Clean window(over(customer_id),
             desc(order_dt, true),
             rn = rowNumber()) ~> RankLatest
RankLatest filter(rn == 1) ~> KeepLatest

Validation check: the sink row count must equal SELECT COUNT(DISTINCT [Customer ID]) FROM dbo.stg_superstore, and customer_id must be unique in dbo.customer_clean.
What it proves: no-code Spark transformations, window functions and a clear "latest record wins" rule.
Resume line: "Built an ADF Mapping Data Flow that deduplicates order-level data into one customer record each, using window ranking on the latest order date."

Project 19: Flatten JSON with Mapping Data Flow

Level: Beginner · Time: 1 day · Tools: ADF REST connector, Mapping Data Flow (Flatten), Azure SQL Database
Business question: An API returns nested JSON. How do we turn it into a flat table that SQL users can query?
Dataset: Random User Generator API (free, fake user profiles; each response has a results array with nested name, location, login and dob objects. Add seed to get the same users every time)

Steps:

1. Copy https://randomuser.me/api/?results=100&seed=brolly with the REST connector into raw/users/users.json.
2. In a data flow, read the JSON source with "Single document" selected.
3. Add a Flatten transformation: unroll by results, and map nested fields to flat columns as below.
4. Do not map login.password, login.salt or the hash fields. Even fake data should be treated like real personal data.
5. Sink to dbo.app_user and query users by country.

source1 foldDown(unroll(results),
    mapColumn(
        user_id = results.login.uuid,
        first_name = results.name.first,
        last_name = results.name.last,
        email = results.email,
        city = results.location.city,
        country = results.location.country,
        birth_date = results.dob.date
    ),
    skipDuplicateMapInputs: false,
    skipDuplicateMapOutputs: false) ~> FlattenUsers

Validation check: the table must have exactly 100 rows (the results value you asked for, also shown in the response's info.results), user_id must be unique, and no password or hash column may exist in the sink.
What it proves: handling hierarchical JSON, the Flatten transformation and privacy-aware column selection.
Resume line: "Flattened nested REST API JSON into a relational Azure SQL table with an ADF Mapping Data Flow, excluding sensitive fields by design."

Project 20: Query CSV and Parquet with Synapse serverless OPENROWSET

Level: Beginner · Time: 1 day · Tools: Azure Synapse serverless SQL pool, ADLS Gen2
Business question: Can analysts query lake files with plain SQL, without loading them into a database first?
Dataset: UCI Online Retail II (the Parquet files from Project 3) and the Superstore CSV from Project 16

Steps:

1. Create a Synapse workspace linked to your ADLS Gen2 account and give yourself "Storage Blob Data Reader" on it.
2. Create a database (not master) and set a UTF-8 collation, such as Latin1_General_100_CI_AS_SC_UTF8, to avoid text conversion errors.
3. Query the CSV with HEADER_ROW = TRUE and the Parquet folder with a wildcard, as below.
4. Create views (vw_superstore, vw_online_retail) with explicit column types using a WITH clause.
5. Note the data processed per query in the Monitor hub, because serverless SQL is billed by data processed.

SELECT TOP 10 Region, SUM(CAST(Sales AS DECIMAL(12,2))) AS sales
FROM OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/raw/superstore/*.csv',
    FORMAT = 'CSV', PARSER_VERSION = '2.0', HEADER_ROW = TRUE
) AS s
GROUP BY Region;

SELECT COUNT_BIG(*) AS retail_rows
FROM OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/bronze/online_retail/**',
    FORMAT = 'PARQUET') AS r;

Validation check: retail_rows must equal the total rowsCopied from Project 3, and the Superstore sales total must match SUM(sales) in dbo.fact_sales from Project 16.
What it proves: the logical data warehouse idea, OPENROWSET, file formats, collations and cost awareness.
Resume line: "Enabled SQL access to CSV and Parquet files in ADLS Gen2 with Synapse serverless OPENROWSET views, reconciled to source row counts."

7. Beginner projects 21–25: First Databricks and PySpark

Project 21: Explore one month of NYC taxi data with PySpark

Level: Beginner · Time: 1–2 days · Tools: Azure Databricks, PySpark, Unity Catalog volume
Business question: At which hours do yellow taxi trips peak, and how do fares differ by payment type? Exploring data well is the first step of every Spark job.
Dataset: NYC TLC Trip Record Data (monthly yellow taxi Parquet files; the URL pattern is https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_YYYY-MM.parquet)

Steps:

1. Download yellow_tripdata_2026-01.parquet into a volume such as /Volumes/dev/raw/files/nyc/.
2. Read it, then run printSchema(), count() and describe() on fare and distance columns.
3. Count nulls per column and count trips whose pickup time falls outside January 2026.
4. Build two summaries: trips by pickup hour, and average fare and tip by payment_type.
5. Write a short README section on what looks wrong in the data (negative fares, zero distances, odd dates).

from pyspark.sql import functions as F

trips = spark.read.parquet("/Volumes/dev/raw/files/nyc/yellow_tripdata_2026-01.parquet")
trips.printSchema()

nulls = trips.select([F.sum(F.col(c).isNull().cast("int")).alias(c) for c in trips.columns])
display(nulls)

by_hour = (trips.groupBy(F.hour("tpep_pickup_datetime").alias("pickup_hour"))
                .agg(F.count("*").alias("trips"), F.round(F.avg("fare_amount"), 2).alias("avg_fare"))
                .orderBy("pickup_hour"))
display(by_hour)

Validation check: SUM(trips) across the 24 pickup hours must equal trips.count(), and the number of rows outside January 2026 must be reported (not silently dropped).
What it proves: reading Parquet, schema checks, null profiling, groupBy and aggregation in PySpark.
Resume line: "Profiled a month of NYC yellow taxi trips in Azure Databricks with PySpark, measuring nulls, outliers and hourly demand patterns."

Project 22: MovieLens ratings joins and aggregations

Level: Beginner · Time: 1–2 days · Tools: Azure Databricks, PySpark, Delta Lake
Business question: Which genres and movies are rated highest, counting only movies with enough ratings to be trusted?
Dataset: MovieLens latest small dataset (about 100,000 ratings of about 9,000 movies by about 600 users; files include ratings.csv and movies.csv)

Steps:

1. Download ml-latest-small.zip, unzip it and upload ratings.csv and movies.csv to a volume.
2. Read both with headers and explicit casts.
3. Split the pipe-separated genres column and explode it to one row per movie and genre.
4. Join ratings to movies and compute the average rating and number of ratings per genre and per movie.
5. Keep movies with at least 50 ratings and save dev.gold.movie_rating_summary as a Delta table.

from pyspark.sql import functions as F

base = "/Volumes/dev/raw/files/movielens/"
ratings = spark.read.option("header", True).option("inferSchema", True).csv(base + "ratings.csv")
movies = spark.read.option("header", True).option("inferSchema", True).csv(base + "movies.csv")

genres = movies.withColumn("genre", F.explode(F.split("genres", r"\|")))
by_genre = (ratings.join(genres, "movieId")
            .groupBy("genre")
            .agg(F.count("*").alias("ratings"), F.round(F.avg("rating"), 2).alias("avg_rating"))
            .orderBy(F.desc("avg_rating")))
display(by_genre)

Validation check: ratings.join(movies, "movieId", "left_anti").count() must be 0 (every rating has a movie), and every rating must be between 0.5 and 5.0.
What it proves: joins, explode, aggregation, join-key checks and saving results as Delta tables.
Resume line: "Analysed about 100K MovieLens ratings in Databricks with PySpark joins and aggregations, producing a Delta table of trusted top-rated movies and genres."

Project 23: Delta Lake time travel and RESTORE

Level: Beginner · Time: 1 day · Tools: Azure Databricks, Delta Lake, Spark SQL
Business question: Someone ran a wrong UPDATE on a production table. How do we see what changed and get the old data back in minutes?
Dataset: Delta Lake utility docs (use the MovieLens movies data from Project 22 as a Delta table)

Steps:

1. Save movies as dev.silver.movies (version 0).
2. Simulate a mistake: an UPDATE without the right WHERE clause (version 1).
3. Run DESCRIBE HISTORY to see each version, the operation and the user.
4. Compare versions with VERSION AS OF, then RESTORE the table to version 0.
5. Learn the limit: VACUUM removes old data files (default retention 7 days), and a restore fails if the files of that version are gone.

CREATE OR REPLACE TABLE dev.silver.movies AS
SELECT * FROM read_files('/Volumes/dev/raw/files/movielens/movies.csv',
                         format => 'csv', header => true); -- version 0

UPDATE dev.silver.movies SET genres = 'Unknown'; -- version 1, the "mistake"

DESCRIBE HISTORY dev.silver.movies;

SELECT COUNT(*) AS changed_rows
FROM dev.silver.movies AS cur
JOIN dev.silver.movies VERSION AS OF 0 AS old ON cur.movieId = old.movieId
WHERE cur.genres <> old.genres;

RESTORE TABLE dev.silver.movies TO VERSION AS OF 0;

Validation check: after RESTORE, SELECT COUNT(*) FROM dev.silver.movies WHERE genres = 'Unknown' returns the same number as in version 0, and DESCRIBE HISTORY shows a new RESTORE version (history is never deleted by a restore).
What it proves: the Delta transaction log, auditing with table history, time travel and recovery.
Resume line: "Demonstrated recovery from a bad update in Delta Lake using DESCRIBE HISTORY, time travel queries and RESTORE TABLE."

Project 24: Parse web server logs with regex

Level: Beginner · Time: 1 day · Tools: Azure Databricks, PySpark (regexp_extract, to_timestamp)
Business question: How many error and notice messages did the Apache server write per hour, and which messages repeat most often?
Dataset: Loghub Apache logs (Apache_2k.log, an Apache web server error log; lines look like [Sun Dec 04 04:47:44 2005] [notice] message)

Steps:

1. Download Apache_2k.log from the repository and upload it to a volume.
2. Read it with spark.read.text(), which gives one value column per line.
3. Use regexp_extract to pull out the timestamp (without the day name), the level and the message.
4. Parse the timestamp with the pattern MMM dd HH:mm:ss yyyy. Spark 3 does not allow day-of-week names when parsing, which is why the day name is removed.
5. Count messages by hour and level, and find the top 10 repeated messages.

from pyspark.sql import functions as F

raw = spark.read.text("/Volumes/dev/raw/files/logs/Apache_2k.log")
pattern = r"^\[\w{3} (\w{3} \d{2} \d{2}:\d{2}:\d{2} \d{4})\] \[(\w+)\] (.*)$"

logs = raw.select(
    F.to_timestamp(F.regexp_extract("value", pattern, 1), "MMM dd HH:mm:ss yyyy").alias("log_ts"),
    F.regexp_extract("value", pattern, 2).alias("level"),
    F.regexp_extract("value", pattern, 3).alias("message"))

display(logs.groupBy(F.date_trunc("hour", "log_ts").alias("hour"), "level").count().orderBy("hour"))

Validation check: logs.filter(F.col("level") == "").count() must be 0 (every line matched the pattern), and logs.count() must equal the line count of the raw file.
What it proves: text parsing with regular expressions, timestamp formats and log analytics, a common task in operations data.
Resume line: "Parsed Apache server logs in PySpark with regular expressions into a structured table, reporting hourly error trends with zero unparsed lines."

Project 25: Clean CO2 data into a Delta table partitioned by year

Level: Beginner · Time: 1 day · Tools: Azure Databricks, PySpark, Delta Lake
Business question: How have CO2 emissions and emissions per person changed by country over time?
Dataset: Our World in Data CO2 and greenhouse gas emissions (owid-co2-data.csv, one row per country and year, with columns such as country, year, iso_code, population, co2 and co2_per_capita)

Steps:

1. Download owid-co2-data.csv and upload it to a volume.
2. Keep only real countries: rows where iso_code is not null and does not start with OWID_ (these are regions and groups).
3. Select the columns you need and cast year to int and the measures to double.
4. Write a Delta table partitioned by year.
5. Query emissions for India, China and the United States since 2000.

from pyspark.sql import functions as F

co2 = (spark.read.option("header", True).csv("/Volumes/dev/raw/files/owid/owid-co2-data.csv")
       .filter(F.col("iso_code").isNotNull() & ~F.col("iso_code").startswith("OWID_"))
       .select("country", "iso_code",
               F.col("year").cast("int").alias("year"),
               F.col("population").cast("double").alias("population"),
               F.col("co2").cast("double").alias("co2_mt"),
               F.col("co2_per_capita").cast("double").alias("co2_per_capita")))

(co2.write.format("delta").mode("overwrite")
    .partitionBy("year").saveAsTable("dev.silver.co2_by_country"))

Validation check: SELECT iso_code, year FROM dev.silver.co2_by_country GROUP BY iso_code, year HAVING COUNT(*) > 1 returns no rows, and no iso_code starts with OWID_.
What it proves: filtering aggregate rows, type casting and Delta partitioning. Also note the trade-off: partitioning a small table by year creates many small files, so for small tables liquid clustering or no partitioning is usually better.
Resume line: "Cleaned Our World in Data CO2 data in PySpark into a year-partitioned Delta table, removing regional aggregates and enforcing one row per country and year."

8. Beginner projects 26–30: Reporting and mini end-to-end

Project 26: Power BI report on Azure SQL sales

Level: Beginner · Time: 1–2 days · Tools: Power BI Desktop, DAX, Azure SQL Database
Business question: How are sales and profit trending by month, region and category, and how does this year compare with last year?
Dataset: Superstore dataset on Kaggle (the star schema built in Project 16)

Steps:

1. In Power BI Desktop, connect to Azure SQL Database and load fact_sales, dim_customer, dim_product and dim_date in Import mode.
2. Create one-to-many relationships from each dimension to the fact table, and mark dim_date as the date table.
3. Add the DAX measures below.
4. Build one page: KPI cards, a monthly line chart with last year, a region bar chart and a category matrix with slicers for segment and year.
5. Hide key columns from report view and save the report as a .pbix file in your GitHub repo.

Total Sales = SUM ( fact_sales[sales] )
Total Profit = SUM ( fact_sales[profit] )
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( dim_date[full_date] ) )
Sales YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Validation check: the Total Sales card with no filters must equal SELECT SUM(sales) FROM dbo.fact_sales, and the total for one month must match the same SQL query filtered to that month.
What it proves: the serving layer: star schema relationships, DAX measures, time intelligence and checking numbers against the source.
Resume line: "Built a Power BI sales report on an Azure SQL star schema with DAX time-intelligence measures, reconciled to the database totals."

Project 27: Historical weather with the Open-Meteo archive API to Delta

Level: Beginner · Time: 1 day · Tools: Azure Databricks, Python requests, PySpark, Delta Lake
Business question: How have daily maximum temperatures and rainfall in Hyderabad changed over the last ten years?
Dataset: Open-Meteo Historical Weather API (endpoint https://archive-api.open-meteo.com/v1/archive; ERA5 data goes back to 1940; an API key is needed only for commercial use)

Steps:

1. Call the archive API for Hyderabad for 2015-01-01 to 2024-12-31 with daily temperature_2m_max, temperature_2m_min and precipitation_sum, and timezone set (daily values need it).
2. Save the raw JSON response to a volume before transforming it, so you can reprocess later.
3. Zip the parallel daily arrays into rows and build a DataFrame (code below).
4. Write a Delta table; add more cities with a loop over a city list.
5. Build a yearly summary: average maximum temperature and total rainfall per year.

import requests
from pyspark.sql import functions as F

url = ("https://archive-api.open-meteo.com/v1/archive?latitude=17.385&longitude=78.4867"
       "&start_date=2015-01-01&end_date=2024-12-31"
       "&daily=temperature_2m_max,temperature_2m_min,precipitation_sum&timezone=Asia%2FKolkata")
daily = requests.get(url, timeout=60).json()["daily"]

rows = list(zip(daily["time"], daily["temperature_2m_max"],
                daily["temperature_2m_min"], daily["precipitation_sum"]))
df = (spark.createDataFrame(rows, "day string, tmax double, tmin double, rain_mm double")
      .withColumn("day", F.to_date("day")).withColumn("city", F.lit("Hyderabad")))
df.write.format("delta").mode("overwrite").saveAsTable("dev.silver.weather_daily_history")

Validation check: the table must have exactly one row per day: 3,653 rows for 2015 to 2024 (ten years including three leap years), and COUNT(DISTINCT day) must equal COUNT(*).
What it proves: API ingestion from a notebook, keeping raw data, reshaping arrays into rows and completeness checks.
Resume line: "Ingested ten years of daily weather history from the Open-Meteo archive API into a Delta table, with a completeness check of one row per day."

Project 28: Azure cost export to storage and a Power BI cost report

Level: Beginner · Time: 1–2 days · Tools: Microsoft Cost Management exports, ADLS Gen2, Synapse serverless SQL or Power BI
Business question: Which services and resource groups cost the most in our learning subscription each day? Data engineers are expected to know what their pipelines cost.
Dataset: Cost Management exports tutorial and the FOCUS cost file schema (columns include BilledCost, BillingCurrency, ChargePeriodStart, ServiceName and x_ResourceGroupName)

Steps:

1. In Cost Management, create a daily export of "Cost and usage details (FOCUS)" in Parquet to a container costexports. The first run can take up to 24 hours.
2. Exports are partitioned and come with a manifest.json; always read all partition files, not one file.
3. Query the export with Synapse serverless as below, or connect Power BI to the folder and combine files.
4. Build a report: daily cost line, cost by service and cost by resource group.
5. Compare the total with Cost analysis in the portal for the same dates.

SELECT CAST(ChargePeriodStart AS DATE) AS usage_date,
       x_ResourceGroupName AS resource_group,
       ServiceName AS service_name,
       BillingCurrency AS currency,
       SUM(BilledCost) AS billed_cost
FROM OPENROWSET(
    BULK 'https://<storage>.dfs.core.windows.net/costexports/<directory>/<export-name>/**',
    FORMAT = 'PARQUET') AS c
GROUP BY CAST(ChargePeriodStart AS DATE), x_ResourceGroupName, ServiceName, BillingCurrency
ORDER BY usage_date, billed_cost DESC;

Validation check: the month-to-date total of billed_cost must match the Cost analysis view in the portal for the same scope and dates. If Power BI shows a different total, check that the cost column type is Decimal and not whole number.
What it proves: FinOps awareness, working with partitioned exports and a manifest, and reporting on a real operational dataset.
Resume line: "Automated daily Azure cost exports to ADLS Gen2 and built a cost report by service and resource group, reconciled to Azure Cost analysis."

Project 29: Simple daily batch with a Databricks job schedule

Level: Beginner · Time: 1 day · Tools: Azure Databricks Jobs, PySpark, Delta Lake MERGE
Business question: Can we keep an up-to-date table of earthquakes worldwide, refreshed by a scheduled job without anyone pressing "Run"?
Dataset: USGS earthquake GeoJSON feeds (the "all earthquakes, past day" feed is updated every minute; each feature has an id, properties such as mag, place and time, and coordinates)

Steps:

1. Write a notebook that downloads https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_day.geojson and saves it to a volume with a timestamp in the file name.
2. Read the file, explode the features array and select id, magnitude, place, event time (epoch milliseconds) and coordinates.
3. Create dev.silver.earthquakes once (same columns, empty), then MERGE into it on id. The past-day feed overlaps between runs, and USGS updates events, so MERGE prevents duplicates.
4. Create a job with this notebook as a task on a small job cluster. Add a Scheduled trigger (Advanced) every 6 hours in UTC, plus a failure email notification.
5. Let it run for two days and review the run history.

from pyspark.sql import functions as F
from delta.tables import DeltaTable

raw = spark.read.option("multiLine", True).json(landing_file) # path saved in step 1
quakes = (raw.select(F.explode("features").alias("f"))
          .select(F.col("f.id").alias("id"),
                  F.col("f.properties.mag").alias("mag"),
                  F.col("f.properties.place").alias("place"),
                  F.expr("timestamp_millis(f.properties.time)").alias("event_ts"),
                  F.col("f.geometry.coordinates")[2].alias("depth_km")))

(DeltaTable.forName(spark, "dev.silver.earthquakes").alias("t")
    .merge(quakes.alias("s"), "t.id = s.id")
    .whenMatchedUpdateAll().whenNotMatchedInsertAll().execute())

Validation check: SELECT id FROM dev.silver.earthquakes GROUP BY id HAVING COUNT(*) > 1 returns no rows, even after many overlapping runs, and the job's run history shows a run at every scheduled time.
What it proves: job scheduling, cron-style triggers, idempotent MERGE and working with GeoJSON.
Resume line: "Scheduled a Databricks job that ingests the USGS earthquake feed every 6 hours and upserts it into Delta Lake with MERGE, keeping one row per event."

Project 30: Mini end-to-end Superstore pipeline: ADF → ADLS → Databricks → Power BI

Level: Beginner · Time: 3–4 days · Tools: Azure Data Factory, ADLS Gen2, Azure Databricks, Delta Lake, Key Vault, Power BI
Business question: Can we deliver a sales dashboard that refreshes automatically every morning, from raw file to report, with checks at each step?
Dataset: Superstore dataset on Kaggle (split the file by order month to simulate daily deliveries)

Steps:

1. Ingest: ADF copies new Superstore files from landing/ to bronze/superstore/load_date=<date>/ (Projects 4, 5 and 14 patterns), with secrets in Key Vault (Project 13).
2. Clean: a Databricks notebook reads Bronze, trims text, parses dates, drops exact duplicates on Row ID and writes dev.silver.superstore.
3. Model: a second notebook builds Gold Delta tables: fact_sales, dim_customer and dim_product (Project 16 logic in PySpark).
4. Orchestrate: a master ADF pipeline runs Copy → Databricks Notebook (Silver) → Databricks Notebook (Gold), with a daily trigger, retries and the failure email from Project 12.
5. Serve: connect Power BI to the Gold tables through a Databricks SQL warehouse and reuse the Project 26 measures.

from pyspark.sql import functions as F

bronze = spark.read.option("header", True).csv("abfss://bronze@<storage>.dfs.core.windows.net/superstore/")
silver = (bronze
    .select(F.col("Row ID").cast("int").alias("row_id"), F.trim("Order ID").alias("order_id"),
            F.to_date("Order Date", "M/d/yyyy").alias("order_date"),
            F.trim("Customer ID").alias("customer_id"), F.trim("Product ID").alias("product_id"),
            "Region", "Category", "Segment",
            F.col("Sales").cast("decimal(12,4)").alias("sales"),
            F.col("Profit").cast("decimal(12,4)").alias("profit"))
    .dropDuplicates(["row_id"]))
silver.write.format("delta").mode("overwrite").saveAsTable("dev.silver.superstore")

Validation check: three numbers must match at every layer: row count and SUM(sales) in the original CSV, in dev.silver.superstore and in dev.gold.fact_sales, and the Power BI total card. Keep the comparison query and its output in the README.
What it proves: a complete data flow with ingestion, cleaning, modeling, orchestration, security, alerting and reporting, which is what interviewers mean by an end-to-end project.
Resume line: "Built an end-to-end Azure pipeline for Superstore sales: ADF ingestion to ADLS Gen2, Databricks Silver and Gold Delta tables, a daily trigger with alerts and a Power BI dashboard reconciled at every layer."

9. Intermediate projects 31–35: Incremental and change data

Short answer: These five projects teach the most common real-world requirement: load only what changed, including deletes, and make every rerun safe.

Project 31: SQL Server change tracking incremental loads

Level: Intermediate · Time: 1–2 days · Tools: Azure Data Factory, Azure SQL Database, ADLS Gen2, Lookup, Copy activity, Stored Procedure activity
Business question: Which customer rows were inserted, updated or deleted since yesterday, when the source table has no reliable ModifiedDate column?
Dataset: Wide World Importers sample database (a fictional wholesale importer with sales, purchasing and warehouse tables)

Steps:

1. Restore WideWorldImporters into Azure SQL Database, then enable change tracking on the database and on Sales.Customers.
2. Create a ct_version table that stores the last loaded SYS_CHANGE_VERSION per table, seeded with CHANGE_TRACKING_CURRENT_VERSION().
3. In ADF, add two Lookup activities: GetLastVersion and GetCurrentVersion (SELECT CHANGE_TRACKING_CURRENT_VERSION() AS v).
4. Add a Copy activity whose source query joins CHANGETABLE(CHANGES ...) to the table, and write Parquet to bronze/wwi/customers/run=@{pipeline().RunId}/.
5. After a successful copy, a Stored Procedure activity saves the current version.

SELECT ct.CustomerID, ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION,
       c.CustomerName, c.PhoneNumber, c.DeliveryCityID
FROM CHANGETABLE(CHANGES Sales.Customers,
     @{activity('GetLastVersion').output.firstRow.last_version}) AS ct
LEFT JOIN Sales.Customers AS c
       ON c.CustomerID = ct.CustomerID
WHERE ct.SYS_CHANGE_VERSION <=
      @{activity('GetCurrentVersion').output.firstRow.v};

Validation check: update two customers and delete one test customer, then run the pipeline. The output file must hold exactly 3 rows: two with operation U and one with D (its columns from Sales.Customers are NULL because the row is gone).
What it proves: you understand change tracking, version-based windows, delete detection and why the version is saved only after the copy succeeds.
Resume line: "Built an ADF incremental pipeline using SQL Server change tracking that captures inserts, updates and deletes from WideWorldImporters into ADLS Gen2 with safe reruns."

Project 32: Azure SQL change data capture (CDC) to ADLS with ADF

Level: Intermediate · Time: 2 days · Tools: Azure SQL Database, CDC, Azure Data Factory, If Condition, tumbling window trigger, ADLS Gen2
Business question: What did every order look like before and after each change, hour by hour, so finance can audit edits?
Dataset: AdventureWorksLT sample database (choose "Sample" when you create the Azure SQL Database)

Steps:

1. Enable CDC with sys.sp_cdc_enable_db, then sys.sp_cdc_enable_table on SalesLT.SalesOrderHeader with @supports_net_changes = 1. (CDC on Azure SQL Database needs a vCore or S3+ service tier.)
2. Add pipeline parameters windowStart and windowEnd, filled by a tumbling window trigger every hour.
3. A Lookup activity counts the net changes in the window; an If Condition skips the copy when the count is 0.
4. A Copy activity runs the CDC query and writes CSV or Parquet to bronze/cdc/salesorderheader/yyyy/MM/dd/HH/.
5. In Databricks, apply the changes to a Silver Delta table with MERGE, using __$operation (1 = delete, 2 = insert, 4 = update after).

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',
                '@{pipeline().parameters.windowStart}');
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than',
                '@{pipeline().parameters.windowEnd}');
SELECT *
FROM cdc.fn_cdc_get_net_changes_SalesLT_SalesOrderHeader(@from_lsn, @to_lsn, 'all');

Validation check: for any window, the Lookup count must equal the Copy activity's rowsCopied, and the sum of rows across all hourly folders must equal the total net changes for the whole day.
What it proves: log-based CDC, LSN windows, tumbling window triggers with backfill, and applying change operations downstream.
Resume line: "Implemented hourly CDC ingestion from Azure SQL to ADLS Gen2 with ADF tumbling window triggers and applied inserts, updates and deletes to Delta Lake."

Project 33: Upsert Parquet into Azure SQL with the Copy activity upsert sink

Level: Intermediate · Time: 1 day · Tools: Azure Data Factory, Copy activity, ADLS Gen2, Azure SQL Database
Business question: How do we keep a product table in Azure SQL current every day, inserting new products and updating changed prices, without truncating it?
Dataset: UCI Online Retail II (1,067,371 transaction rows from a UK online retailer, December 2009 to December 2011)

Steps:

1. In Databricks, build a daily product snapshot from the transactions: StockCode, latest Description and latest Price, and write it as Parquet to silver/retail/product_snapshot/date=<d>/.
2. In Azure SQL, create dbo.dim_product with StockCode as the primary key.
3. In ADF, add a Copy activity with a Parquet source (parameterized date folder) and an Azure SQL sink.
4. Set the sink write behaviour to Upsert, key column StockCode, and use tempdb for the interim table.
5. Run the pipeline for two consecutive dates.

"sink": {
  "type": "AzureSqlSink",
  "writeBehavior": "upsert",
  "upsertSettings": {
    "useTempDB": true,
    "keys": [ "StockCode" ]
  }
}

Validation check: after both runs, SELECT COUNT(*) - COUNT(DISTINCT StockCode) FROM dbo.dim_product must return 0, and the row count must equal the distinct product count across both snapshots.
What it proves: you can make relational loads idempotent without hand-written MERGE procedures, and you know the role of key columns and interim tables.
Resume line: "Built an ADF pipeline that upserts daily Parquet product snapshots into Azure SQL using the Copy activity upsert sink, with zero duplicate keys across reruns."

Project 34: Daily Delta MERGE of Olist orders

Level: Intermediate · Time: 1–2 days · Tools: Azure Databricks, PySpark, Delta Lake MERGE, Change Data Feed, Databricks job
Business question: What is the current status of every order (created, shipped, delivered, cancelled), updated every day as new order files arrive?
Dataset: Brazilian E-Commerce Public Dataset by Olist (real, anonymised marketplace orders split into customers, orders, items, payments, reviews, products and sellers files)

Steps:

1. Split olist_orders_dataset.csv into daily files by purchase date and drop them, one day at a time, into landing/olist/orders/.
2. Load each day into bronze.olist_orders with the file name and load time.
3. Deduplicate by order_id, keeping the latest row, then MERGE into silver.orders.
4. Enable Change Data Feed on silver.orders so Gold jobs can read only changed orders.
5. Schedule the notebook as a daily Databricks job.

from delta.tables import DeltaTable
from pyspark.sql import Window, functions as F

w = Window.partitionBy("order_id").orderBy(F.col("_load_ts").desc())
src = (spark.table("bronze.olist_orders").filter(F.col("load_date") == load_date)
       .withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn"))

(DeltaTable.forName(spark, "silver.orders").alias("t")
 .merge(src.alias("s"), "t.order_id = s.order_id")
 .whenMatchedUpdateAll(condition="t.order_status <> s.order_status")
 .whenNotMatchedInsertAll()
 .execute())

Validation check: silver.orders must have one row per order_id, and running the same day twice must leave numTargetRowsInserted and numTargetRowsUpdated at 0 in DESCRIBE HISTORY.
What it proves: Delta MERGE, deduplication before MERGE, idempotent reruns and Change Data Feed.
Resume line: "Built a daily Delta Lake MERGE pipeline for Olist orders in Databricks with deduplication, Change Data Feed and fully idempotent reruns."

Project 35: Self-hosted integration runtime: on-premises SQL Server to ADLS

Level: Intermediate · Time: 1–2 days · Tools: Azure Data Factory, self-hosted integration runtime, SQL Server, Key Vault, ADLS Gen2
Business question: How do we move data from a SQL Server that sits inside a company network, with no public endpoint, into the data lake?
Dataset: Wide World Importers sample database restored on SQL Server Developer Edition on your laptop (it plays the "on-premises" server)

Steps:

1. Create a self-hosted integration runtime in ADF and install it on your laptop or a small VM with the authentication key.
2. Store the SQL login password in Key Vault and give ADF's managed identity the "Key Vault Secrets User" role.
3. Create a SQL Server linked service that connects via the self-hosted IR and reads the password from Key Vault.
4. Copy Sales.Orders, Sales.OrderLines and Sales.Customers to bronze/wwi/<table>/load_date=<date>/ as Parquet with a ForEach.
5. Stop the IR service once and run the pipeline to see how the failure appears in monitoring.

{
  "name": "ls_onprem_sqlserver",
  "properties": {
    "type": "SqlServer",
    "typeProperties": {
      "connectionString": "Server=<laptop-name>;Database=WideWorldImporters;User ID=adf_reader;",
      "password": { "type": "AzureKeyVaultSecret",
        "store": { "referenceName": "ls_keyvault", "type": "LinkedServiceReference" },
        "secretName": "onprem-sql-password" }
    },
    "connectVia": { "referenceName": "shir-laptop", "type": "IntegrationRuntimeReference" }
  }
}

Validation check: for each table, SELECT COUNT(*) on SQL Server must equal the Copy activity rowsCopied and the row count of the Parquet folder read in Databricks.
What it proves: hybrid connectivity, integration runtime types, Key Vault-backed linked services and troubleshooting IR failures.
Resume line: "Configured a self-hosted integration runtime to ingest on-premises SQL Server tables into ADLS Gen2 with ADF, using Key Vault for credentials."

10. Intermediate projects 36–40: Databricks pipelines

Short answer: These Azure Databricks projects show the tools companies use to run pipelines every day: Auto Loader, Lakeflow Declarative Pipelines, AUTO CDC, Lakeflow Jobs and a reusable data quality pattern.

Project 36: Auto Loader with schema evolution and the rescued data column

Level: Intermediate · Time: 1–2 days · Tools: Azure Databricks, Auto Loader, Delta Lake, Unity Catalog volumes
Business question: How do we keep loading bike trip files when the provider changes the file format, without losing any data?
Dataset: Citi Bike System Data (monthly trip history files; the page lists the current fields and a separate "previously" format with trip duration, bike ID, user type, gender and birth year)

Steps:

1. Download one month in the old format and one month in the new format into /Volumes/dev/raw/files/citibike/, but upload the new-format file only after the first run.
2. Start an Auto Loader stream with cloudFiles.schemaLocation and cloudFiles.schemaEvolutionMode set to addNewColumns.
3. Run once with the old file. Then add the new file and run again: the stream stops once with an UnknownFieldException, updates the schema and succeeds on restart.
4. Inspect the _rescued_data column for values that did not match the schema.
5. In Silver, unify both formats with coalesce (for example, started_at and starttime).

(spark.readStream.format("cloudFiles")
  .option("cloudFiles.format", "csv")
  .option("cloudFiles.schemaLocation", "/Volumes/dev/raw/files/_schemas/citibike")
  .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
  .load("/Volumes/dev/raw/files/citibike/")
  .withColumn("source_file", F.col("_metadata.file_path"))
  .writeStream
  .option("checkpointLocation", "/Volumes/dev/raw/files/_checkpoints/citibike")
  .option("mergeSchema", "true")
  .trigger(availableNow=True)
  .toTable("dev.bronze.citibike_trips"))

Validation check: Bronze row count per source_file must equal the line count of each CSV minus its header, and SELECT COUNT(*) FROM dev.bronze.citibike_trips WHERE _rescued_data IS NOT NULL must be explained row by row.
What it proves: Auto Loader schema inference, schema evolution modes, the rescued data column and handling format changes without data loss.
Resume line: "Built an Auto Loader ingestion pipeline for Citi Bike trip files that survives format changes using schema evolution and the rescued data column, with no lost rows."

Project 37: Lakeflow Declarative Pipelines with expectations

Level: Intermediate · Time: 2 days · Tools: Lakeflow Declarative Pipelines, streaming tables, materialized views, expectations, Unity Catalog
Business question: How many crimes of each type happen per district per month, using only records that pass basic quality rules?
Dataset: Chicago Crimes – 2001 to Present (City of Chicago open data portal; export CSV for one or two recent years)

Steps:

1. Export recent years as CSV into a Unity Catalog volume.
2. Create a pipeline with a Bronze streaming table that reads files with read_files.
3. Create a Silver streaming table with three expectations: a warning, a drop and a fail.
4. Create a Gold materialized view: crimes by primary_type, district and month.
5. Run the pipeline and open the Data quality tab to see passed and dropped records.

CREATE OR REFRESH STREAMING TABLE silver_crimes (
  CONSTRAINT has_id EXPECT (id IS NOT NULL) ON VIOLATION FAIL UPDATE,
  CONSTRAINT valid_date EXPECT (crime_ts IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT has_location EXPECT (latitude BETWEEN 41 AND 43)
) AS
SELECT CAST(ID AS BIGINT) AS id,
       to_timestamp(Date, 'MM/dd/yyyy hh:mm:ss a') AS crime_ts,
       `Primary Type` AS primary_type, District AS district,
       CAST(Latitude AS DOUBLE) AS latitude
FROM STREAM(bronze_crimes);

Validation check: Bronze rows minus Silver rows must equal the dropped-record count for valid_date in the pipeline event log, and has_location must report its failures as warnings only.
What it proves: declarative pipelines, streaming tables vs materialized views, the three expectation actions and reading data quality metrics.
Resume line: "Built a Lakeflow Declarative Pipeline for Chicago crime data with warn, drop and fail expectations, tracking quality metrics in the pipeline event log."

Project 38: SCD Type 2 with AUTO CDC (APPLY CHANGES) in Lakeflow

Level: Intermediate · Time: 1 day · Tools: Lakeflow Declarative Pipelines, AUTO CDC, Delta Lake
Business question: Where did each customer live at any point in time, when changes arrive out of order and some customers are deleted?
Dataset: AUTO CDC sample change feed from Microsoft Learn (8 sample change records with inserts, updates, a delete and an out-of-order batch), then your own feed from Project 32

Steps:

1. Create the sample table main.cdc_tutorial.users_cdf from the documentation.
2. Create a pipeline with a streaming table users_history.
3. Add an AUTO CDC flow keyed on userId, sequenced by sequenceNum, with deletes applied and SCD Type 2 storage.
4. Run the pipeline and inspect the __START_AT and __END_AT columns.
5. Repeat the flow with TRACK HISTORY ON * EXCEPT (city) to see how untracked columns behave.

CREATE OR REFRESH STREAMING TABLE users_history;

CREATE FLOW apply_cdc AS AUTO CDC INTO users_history
FROM stream(main.cdc_tutorial.users_cdf)
KEYS (userId)
APPLY AS DELETE WHEN operation = "DELETE"
SEQUENCE BY sequenceNum
COLUMNS * EXCEPT (operation, sequenceNum)
STORED AS SCD TYPE 2;

Validation check: the documentation's expected result is 7 rows; user 125 must have 3 versions with only Guadalajara open (__END_AT NULL), and user 123 must have no open row.
What it proves: SCD Type 2 from a change feed, sequencing for out-of-order events, delete handling and the AUTO CDC API that replaces APPLY CHANGES.
Resume line: "Implemented SCD Type 2 customer history with Lakeflow AUTO CDC, correctly handling out-of-order updates and deletes from a CDC feed."

Project 39: Databricks Workflows with task parameters, retries and repair runs

Level: Intermediate · Time: 1–2 days · Tools: Lakeflow Jobs (Databricks Workflows), Databricks Asset Bundles, PySpark, REST API
Business question: How do we refresh GDP and population indicators for several countries every week, retry short API failures and rerun only the failed part?
Dataset: World Bank Indicators API (free JSON API with no key; returns paged yearly values per country and indicator)

Steps:

1. Write an ingest_indicator notebook that reads country and indicator task parameters, calls the API and writes the JSON to Bronze.
2. Write a build_silver notebook that flattens all Bronze files into silver.wb_indicators.
3. Define a job with a for_each task (or several ingest tasks) for IN, US and GB, then the Silver task that depends on them.
4. Set max_retries: 2 and min_retry_interval_millis on the ingest tasks, and email notifications on failure.
5. Break one task (use an invalid indicator), run the job, fix the parameter and use Repair run to rerun only the failed tasks.

resources:
  jobs:
    world_bank_weekly:
      name: world_bank_weekly
      tasks:
        - task_key: ingest_in
          notebook_task:
            notebook_path: ../src/ingest_indicator.py
            base_parameters: { country: IN, indicator: NY.GDP.MKTP.CD }
          max_retries: 2
          min_retry_interval_millis: 60000
        - task_key: build_silver
          depends_on: [ { task_key: ingest_in } ]
          notebook_task: { notebook_path: ../src/build_silver.py }

Validation check: for each country and indicator, the Silver row count must equal the total field in the API's paging object.
What it proves: job design, task dependencies, parameters, retries, notifications and repair runs, which are daily work in production.
Resume line: "Orchestrated a weekly multi-task Databricks job for World Bank indicators with task parameters, automatic retries and repair runs for failed tasks."

Project 40: Quarantine pattern for bad records with a data quality rules table

Level: Intermediate · Time: 2 days · Tools: PySpark, Delta Lake, Unity Catalog
Business question: Which 311 service requests are valid for reporting, and which were held back, by which rule, so the source team can fix them?
Dataset: NYC 311 Service Requests from 2020 to Present (NYC Open Data; export one month as CSV)

Steps:

1. Create a dq_rules table with rule_name, table_name, expression (a SQL condition that must be true) and is_active.
2. Insert rules such as unique_key IS NOT NULL, created_date <= closed_date OR closed_date IS NULL and borough IN ('BRONX','BROOKLYN','MANHATTAN','QUEENS','STATEN ISLAND').
3. In PySpark, read the active rules and add one Boolean column per rule.
4. Write rows that pass every rule to silver.nyc311, and the rest to quarantine.nyc311 with an array of failed rule names.
5. Build a small Gold summary: failures per rule per day.

from pyspark.sql import functions as F

rules = spark.table("dq.dq_rules").filter("table_name = 'nyc311' AND is_active").collect()
df = spark.table("bronze.nyc311")
for r in rules:
    df = df.withColumn(f"ok_{r.rule_name}", F.coalesce(F.expr(r.expression), F.lit(False)))

failed = F.array_compact(F.array(*[F.when(~F.col(f"ok_{r.rule_name}"), F.lit(r.rule_name)) for r in rules]))
df = df.withColumn("failed_rules", failed)
df.filter(F.size("failed_rules") == 0).write.mode("append").saveAsTable("silver.nyc311")
df.filter(F.size("failed_rules") > 0).write.mode("append").saveAsTable("quarantine.nyc311")

Validation check: Bronze rows must equal Silver rows plus quarantine rows for each load, and no unique_key may appear in both tables.
What it proves: metadata-driven data quality, the quarantine pattern, reconciliation and rules that analysts can change without code.
Resume line: "Built a rules-table-driven data quality framework in PySpark that quarantines invalid NYC 311 records with the failed rule names, reconciling 100% of input rows."

11. Intermediate projects 41–45: Performance

Short answer: Interviewers for 2–3 year roles ask how you made a job faster. These projects give you measured before-and-after numbers from the Spark UI and query profile. Record your own timings; never copy someone else's.

Project 41: Fix data skew with salting and AQE skew join

Level: Intermediate · Time: 2 days · Tools: Azure Databricks, PySpark, Adaptive Query Execution, Spark UI
Business question: Why does one task in the trips-by-zone-and-day join run far longer than the others, and how do we fix it?
Dataset: NYC TLC High Volume For-Hire Vehicle trip records (monthly Parquet files published separately from yellow taxi data since 2019)

Steps:

1. Load two or three months of HVFHV files into bronze.hvfhv_trips.
2. Build a medium-sized zone_day table (zone × date, with a few attributes) so the join cannot be broadcast.
3. Disable broadcast and AQE skew handling, run the join and note the slowest task time and the max vs median shuffle read in the Spark UI stage page.
4. Turn on spark.sql.adaptive.skewJoin.enabled and rerun. Note the change.
5. Turn AQE skew handling off again and apply manual salting on the busiest keys. Compare all three runs.

from pyspark.sql import functions as F

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
N = 16
trips = spark.table("bronze.hvfhv_trips").withColumn("salt", (F.rand() * N).cast("int"))
zone_day = (spark.table("silver.zone_day")
            .crossJoin(spark.range(N).withColumnRenamed("id", "salt")))

joined = trips.join(zone_day, ["PULocationID", "pickup_date", "salt"])
joined.write.mode("overwrite").saveAsTable("silver.trips_zone_day")

Validation check: all three runs must produce the same row count and the same SUM(base_passenger_fare); only the timing and task distribution may change.
What it proves: reading skew in the Spark UI, AQE skew join settings, salting and the trade-off of duplicating the smaller side.
Resume line: "Diagnosed and fixed join skew on NYC HVFHV trip data using AQE skew join and salting, reducing the slowest task from [X] to [Y] minutes."

Project 42: Benchmark partitioning vs Z-order vs liquid clustering

Level: Intermediate · Time: 2 days · Tools: Delta Lake, Databricks SQL, query profile
Business question: Which data layout makes date-range and customer queries on a large order-line table fastest, at the lowest maintenance cost?
Dataset: Databricks samples.tpch schema (TPC-H benchmark tables available in every Unity Catalog workspace)

Steps:

1. Create three copies of samples.tpch.lineitem: partitioned by ship year, Z-ordered by l_shipdate and l_partkey, and liquid clustered by the same columns.
2. Run OPTIMIZE on each copy.
3. Run the same two queries on each table three times: a one-month date filter and a single-part filter.
4. From the query profile, record duration, files read and files pruned.
5. Put the results in a table in your README with a recommendation.

CREATE TABLE perf.li_part PARTITIONED BY (ship_year) AS
SELECT *, YEAR(l_shipdate) AS ship_year FROM samples.tpch.lineitem;

CREATE TABLE perf.li_zorder AS SELECT * FROM samples.tpch.lineitem;
OPTIMIZE perf.li_zorder ZORDER BY (l_shipdate, l_partkey);

CREATE TABLE perf.li_liquid CLUSTER BY (l_shipdate, l_partkey)
AS SELECT * FROM samples.tpch.lineitem;
OPTIMIZE perf.li_liquid;

Validation check: all three tables must return identical results for both queries (compare COUNT and SUM(l_extendedprice)), so only the layout differs.
What it proves: partition pruning, data skipping, Z-order vs liquid clustering, and making layout decisions from evidence.
Resume line: "Benchmarked Hive partitioning, Z-order and liquid clustering on TPC-H line items in Databricks and cut files scanned for date queries by [X]% with the chosen layout."

Project 43: Small-file compaction with OPTIMIZE and a VACUUM housekeeping job

Level: Intermediate · Time: 1 day · Tools: Delta Lake, OPTIMIZE, VACUUM, Databricks job
Business question: Why do queries on a table that receives hourly appends get slower every week, and how do we keep it healthy?
Dataset: GH Archive (public GitHub activity, one gzipped JSON file per hour, from 2011)

Steps:

1. Download one or two days of hourly files and append each hour separately to bronze.gh_events to create many small files.
2. Run DESCRIBE DETAIL and record numFiles and sizeInBytes. Time a query that counts events by type.
3. Run OPTIMIZE and record numFiles and the query time again.
4. Run VACUUM with the default 7-day retention and explain why old files stay until then.
5. Create a weekly housekeeping job that loops over a list of tables and logs the before and after file counts to ops.housekeeping_log.

for t in ["bronze.gh_events", "silver.gh_events"]:
    before = spark.sql(f"DESCRIBE DETAIL {t}").first()["numFiles"]
    spark.sql(f"OPTIMIZE {t}")
    spark.sql(f"VACUUM {t}")
    after = spark.sql(f"DESCRIBE DETAIL {t}").first()["numFiles"]
    spark.createDataFrame([(t, before, after)], "table string, files_before long, files_after long") \
         .withColumn("run_ts", F.current_timestamp()) \
         .write.mode("append").saveAsTable("ops.housekeeping_log")

Validation check: COUNT(*) on the table must be the same before and after OPTIMIZE, while numFiles must drop.
What it proves: the small-file problem, compaction, VACUUM retention and time-travel trade-offs, and automated maintenance.
Resume line: "Automated Delta Lake housekeeping with OPTIMIZE and VACUUM for hourly GitHub event tables, reducing file count from [X] to [Y] and query time by [Z]%."

Project 44: Broadcast vs shuffle joins on a large fact table

Level: Intermediate · Time: 1 day · Tools: Spark SQL, join hints, EXPLAIN, Spark UI
Business question: Which products and departments sell most often, and which join strategy gives that answer fastest?
Dataset: Instacart Market Basket Analysis on Kaggle (orders, order lines, products, aisles and departments as separate CSV files)

Steps:

1. Load all files to Delta tables in bronze.instacart.
2. Join the large order-lines table to products and departments with a BROADCAST hint and record the time and plan.
3. Run the same query with a MERGE (sort-merge) hint and compare.
4. Join order lines to the orders table (which is much larger than products) with both hints and observe when broadcasting stops making sense.
5. Write a short rule of thumb in your README based on what you measured.

EXPLAIN FORMATTED
SELECT /*+ BROADCAST(p), BROADCAST(d) */
       d.department, COUNT(*) AS lines
FROM bronze.instacart.order_products_prior op
JOIN bronze.instacart.products p ON op.product_id = p.product_id
JOIN bronze.instacart.departments d ON p.department_id = d.department_id
GROUP BY d.department;

Validation check: the BroadcastHashJoin and SortMergeJoin versions must return identical results, and the plan text must show the join type you asked for.
What it proves: join strategies, hints, autoBroadcastJoinThreshold and reading physical plans.
Resume line: "Compared broadcast and sort-merge joins on Instacart order lines in Databricks and documented a join strategy that cut query time from [X] to [Y] seconds."

Project 45: Spark UI investigation of a slow job (spill, stages, tasks)

Level: Intermediate · Time: 1–2 days · Tools: Azure Databricks, Spark UI, PySpark
Business question: Why does a daily "top pages per wiki" job slow down, and which stage, task or setting is responsible?
Dataset: Wikimedia hourly pageviews dumps (one gzipped text file per hour; each line starts with the wiki code, the page title and the view count)

Steps:

1. Download the 24 hourly files for one day into a volume.
2. Parse lines with split and keep domain_code, page_title and count_views. Group by domain_code and page_title, then rank pages per wiki.
3. Run it badly on purpose: set spark.sql.shuffle.partitions to 4 on a small cluster. In the Spark UI, find the slow stage and note Spill (Memory), Spill (Disk), task count and max task duration.
4. Note that .gz files cannot be split, so the read stage has one task per file.
5. Fix it: enable AQE, raise shuffle partitions, write a Silver Delta table so later queries skip decompression, and compare.

from pyspark.sql import Window, functions as F

raw = spark.read.text("/Volumes/dev/raw/files/pageviews/2026-09-01/")
parts = F.split("value", " ")
pv = raw.select(parts[0].alias("wiki"), parts[1].alias("page"),
                parts[2].cast("long").alias("views"))
daily = pv.groupBy("wiki", "page").agg(F.sum("views").alias("views"))
w = Window.partitionBy("wiki").orderBy(F.desc("views"))
top = daily.withColumn("rank", F.row_number().over(w)).filter("rank <= 10")
top.write.mode("overwrite").saveAsTable("gold.top_pages_daily")

Validation check: before and after tuning, the output must have at most 10 rows per wiki and the same SUM(views) for wiki = 'en'.
What it proves: reading the Spark UI (jobs, stages, tasks, shuffle, spill), splittable vs non-splittable files and evidence-based tuning.
Resume line: "Investigated a slow PySpark job with the Spark UI, removed disk spill and reduced runtime from [X] to [Y] minutes by tuning shuffle partitions and AQE."

12. Intermediate projects 46–50: Warehousing and modeling

Short answer: These projects prove you can design tables for analytics, not only move data: distribution, serving views, Data Vault, late data and joined customer profiles.

Project 46: Synapse dedicated SQL pool star schema with distribution and indexes

Level: Intermediate · Time: 2 days · Tools: Azure Synapse dedicated SQL pool, T-SQL, COPY INTO, ADLS Gen2
Business question: What are sales by store, product category and month, served fast from an MPP data warehouse?
Dataset: Contoso Data Generator V2 by SQLBI (generates Contoso sales data as Parquet, Delta, CSV or SQL Server scripts)

Steps:

1. Generate a small Contoso dataset as CSV or Parquet and upload it to landing/contoso/.
2. Load staging tables with COPY INTO (round-robin heaps for fast loading).
3. Create dimensions as REPLICATE tables and the sales fact as HASH distributed with a clustered columnstore index.
4. Load the fact with CTAS from staging, then update statistics.
5. Check data skew across the 60 distributions with DBCC PDW_SHOWSPACEUSED.

CREATE TABLE dbo.fact_sales
WITH (DISTRIBUTION = HASH(CustomerKey), CLUSTERED COLUMNSTORE INDEX)
AS
SELECT s.OrderKey, s.LineNumber, s.OrderDate, s.CustomerKey, s.StoreKey,
       s.ProductKey, s.Quantity, s.NetPrice, s.Quantity * s.NetPrice AS net_amount
FROM stg.sales AS s;

CREATE TABLE dbo.dim_product
WITH (DISTRIBUTION = REPLICATE, CLUSTERED INDEX (ProductKey))
AS SELECT * FROM stg.product;

Validation check: SUM(net_amount) in dbo.fact_sales must equal the same sum on the staging table, and the largest distribution in DBCC PDW_SHOWSPACEUSED should not be many times larger than the smallest.
What it proves: MPP design, hash vs replicate vs round-robin, columnstore indexes and skew checks.
Resume line: "Designed a Contoso sales star schema in a Synapse dedicated SQL pool with hash-distributed columnstore facts and replicated dimensions."

Project 47: Synapse serverless logical data warehouse over Delta with views

Level: Intermediate · Time: 1 day · Tools: Synapse serverless SQL pool, Delta Lake, managed identity, Power BI
Business question: Can analysts query lake data with plain SQL and Power BI without loading it into a database?
Dataset: the Olist Silver and Gold Delta tables from Project 34 (Olist on Kaggle)

Steps:

1. Create a database lakehouse_ldw in the serverless pool (never use master for objects).
2. Create a database scoped credential with IDENTITY = 'Managed Identity' and an external data source pointing to the gold container.
3. Give the Synapse workspace identity "Storage Blob Data Reader" on the storage account.
4. Create one view per Delta table with OPENROWSET(... FORMAT = 'DELTA'), with explicit column types for strings.
5. Connect Power BI to the serverless endpoint and build a small report.

CREATE EXTERNAL DATA SOURCE gold_lake
WITH (LOCATION = 'https://<storage>.dfs.core.windows.net/gold',
      CREDENTIAL = lake_mi);
GO
CREATE VIEW dbo.orders AS
SELECT *
FROM OPENROWSET(BULK 'olist/orders/', DATA_SOURCE = 'gold_lake', FORMAT = 'DELTA')
WITH (order_id VARCHAR(32), customer_id VARCHAR(32), order_status VARCHAR(20),
      order_purchase_timestamp DATETIME2) AS o;

Validation check: SELECT COUNT(*) FROM dbo.orders in Synapse must equal SELECT COUNT(*) FROM silver.orders in Databricks.
What it proves: logical data warehouse design, credentials and identities, Delta reads from serverless SQL and pay-per-query serving.
Resume line: "Built a Synapse serverless logical data warehouse over Delta Lake with managed-identity access, serving Olist data to Power BI without data movement."

Project 48: Data Vault 2.0 hubs, links and satellites on Delta

Level: Intermediate · Time: 2–3 days · Tools: Azure Databricks, Delta Lake, Spark SQL
Business question: How do we integrate customers and orders from source systems in an auditable, insert-only model that keeps full history?
Dataset: Wide World Importers sample database (use Sales.Customers and Sales.Orders landed by Project 35)

Steps:

1. Create hub_customer (hash key, business key, load date, record source) and hub_order.
2. Create link_customer_order with a hash of both business keys.
3. Create sat_customer with descriptive columns and a hash_diff of those columns.
4. Load hubs and links insert-only (new keys only), and satellites only when hash_diff changes.
5. Build a Gold "current customer" view on top of the vault.

INSERT INTO dv.sat_customer
SELECT h.customer_hk, current_timestamp() AS load_ts, 'WWI' AS record_source,
       s.hash_diff, s.CustomerName, s.PhoneNumber, s.DeliveryCityID
FROM (SELECT *, sha2(concat_ws('||', CustomerName, PhoneNumber, DeliveryCityID), 256) AS hash_diff
      FROM bronze.wwi_customers) s
JOIN dv.hub_customer h ON h.customer_bk = CAST(s.CustomerID AS STRING)
LEFT JOIN (SELECT customer_hk, hash_diff,
                  ROW_NUMBER() OVER (PARTITION BY customer_hk ORDER BY load_ts DESC) AS rn
           FROM dv.sat_customer) cur
  ON cur.customer_hk = h.customer_hk AND cur.rn = 1
WHERE cur.hash_diff IS NULL OR cur.hash_diff <> s.hash_diff;

Validation check: rerunning the load with unchanged source data must insert 0 rows into every hub, link and satellite.
What it proves: Data Vault concepts, hash keys, hash diffs, insert-only loading and auditability.
Resume line: "Modeled WideWorldImporters customers and orders as a Data Vault 2.0 on Delta Lake with insert-only hubs, links and change-detecting satellites."

Project 49: Late-arriving dimensions with inferred members

Level: Intermediate · Time: 1 day · Tools: Azure Databricks, Delta Lake MERGE
Business question: How do we load today's sales even when a new customer's details have not arrived yet, without losing the link later?
Dataset: UCI Online Retail II (transactions with a Customer ID column); build a customer master from it and hold back a few customers to simulate late arrival

Steps:

1. Build dim_customer with a surrogate key, customer_id, country and an is_inferred flag, leaving out 5 customers.
2. Before loading facts, insert an inferred member for every fact customer_id missing from the dimension.
3. Load fact_sales with the surrogate key, so no fact row is dropped or mapped to "Unknown".
4. When the late customer details arrive, MERGE them: update the inferred row in place and set is_inferred = false.
5. Report how many inferred members exist and how long they stayed inferred.

MERGE INTO gold.dim_customer d
USING (SELECT DISTINCT customer_id FROM silver.sales_today
       WHERE customer_id IS NOT NULL) f
ON d.customer_id = f.customer_id
WHEN NOT MATCHED THEN INSERT (customer_id, country, is_inferred, inferred_at)
VALUES (f.customer_id, 'Unknown', true, current_timestamp());

Validation check: after loading, SELECT COUNT(*) FROM gold.fact_sales f LEFT ANTI JOIN gold.dim_customer d ON f.customer_sk = d.customer_sk must return 0, and after the late file, the 5 customers must have is_inferred = false with the same surrogate keys.
What it proves: referential integrity under late data, surrogate keys and inferred member handling.
Resume line: "Implemented inferred-member handling for late-arriving customers in a Delta Lake star schema, keeping 100% of fact rows linked to valid dimension keys."

Project 50: Customer 360 joining orders, support tickets and web events

Level: Intermediate · Time: 2–3 days · Tools: Azure Databricks, PySpark, Delta Lake, Power BI
Business question: For each customer, what did they view, buy and complain about, and which high-value customers have open tickets?
Dataset: eCommerce behavior data from a multi-category store (view, cart and purchase events by user_id) plus the Customer Support Ticket Dataset

Steps:

1. Load one month of events into Bronze; treat purchase events as orders.
2. Load the ticket dataset. Because the two public datasets share no customer key, map each ticket to a user_id with a fixed, documented rule (for example, a hash of the ticket's customer email modulo the list of purchasers) and state this clearly in the README.
3. Build Silver tables: customer_events, customer_orders and customer_tickets.
4. Aggregate each to one row per user_id, then join them into gold.customer_360.
5. Add segments, for example "high value with open ticket", and build a Power BI page.

from pyspark.sql import functions as F

orders = (spark.table("silver.customer_orders").groupBy("user_id")
          .agg(F.count("*").alias("orders"), F.sum("price").alias("revenue")))
views = (spark.table("silver.customer_events").filter("event_type = 'view'")
         .groupBy("user_id").agg(F.count("*").alias("views")))
tickets = (spark.table("silver.customer_tickets").groupBy("user_id")
           .agg(F.sum(F.when(F.col("ticket_status") != "Closed", 1).otherwise(0)).alias("open_tickets")))

c360 = (views.join(orders, "user_id", "left").join(tickets, "user_id", "left")
        .fillna(0, ["orders", "revenue", "open_tickets"]))
c360.write.mode("overwrite").saveAsTable("gold.customer_360")

Validation check: gold.customer_360 must have exactly one row per user_id, and SUM(revenue) must equal the sum of price over purchase events in Silver.
What it proves: entity resolution thinking, pre-aggregating before joins to avoid fan-out, and building analytics-ready profiles.
Resume line: "Built a Customer 360 Delta table in Databricks joining web events, purchases and support tickets into one row per customer for Power BI segmentation."

13. Intermediate projects 51–55: Microsoft Fabric

Short answer: These Microsoft Fabric projects cover the main Fabric data engineering items: Dataflow Gen2, OneLake shortcuts, Warehouse, Power BI models and notebooks in a Lakehouse. They also help with DP-700 preparation.

Project 51: Dataflow Gen2 ETL into a Lakehouse

Level: Intermediate · Time: 1 day · Tools: Microsoft Fabric, Dataflow Gen2, Power Query, Lakehouse
Business question: What are sales by product category and employee, refreshed daily with a low-code tool that business analysts can maintain?
Dataset: Northwind OData service (public OData feed with Customers, Orders, Order_Details, Products, Categories and Employees)

Steps:

1. In a Fabric workspace, create a Lakehouse lh_northwind and a Dataflow Gen2.
2. Connect to the OData feed and select Orders, Order_Details, Products, Categories and Employees.
3. In Power Query, set data types, remove unused columns and add a LineAmount column.
4. Set the data destination of each query to the Lakehouse, with "Replace" for dimensions.
5. Schedule a daily refresh and add the dataflow to a Fabric pipeline with a failure email.

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/"),
    Lines = Source{[Name = "Order_Details", Signature = "table"]}[Data],
    Typed = Table.TransformColumnTypes(Lines, {{"UnitPrice", type number},
                {"Quantity", Int64.Type}, {"Discount", type number}}),
    WithAmt = Table.AddColumn(Typed, "LineAmount",
                each [UnitPrice] * [Quantity] * (1 - [Discount]), type number)
in
    WithAmt

Validation check: the row count of the order_details Lakehouse table must equal the count from https://services.odata.org/V4/Northwind/Northwind.svc/Order_Details/$count.
What it proves: Dataflow Gen2, Power Query transformations, data destinations and scheduling in Fabric.
Resume line: "Built a Fabric Dataflow Gen2 that loads Northwind OData sales into a Lakehouse with typed columns and a scheduled daily refresh."

Project 52: OneLake shortcuts to ADLS Gen2 without copying

Level: Intermediate · Time: Half a day · Tools: Microsoft Fabric, OneLake, Lakehouse, ADLS Gen2, SQL analytics endpoint
Business question: Can Fabric users query Delta tables that already live in ADLS Gen2 without a second copy of the data?
Dataset: the Olist Delta tables in your ADLS Gen2 silver container from Project 34 (Olist on Kaggle)

Steps:

1. Create a Fabric cloud connection to the storage account (organizational account, service principal or workspace identity).
2. In a Lakehouse, create a shortcut in the Tables folder for each Delta folder, such as orders.
3. Query the shortcut table from the SQL analytics endpoint and from a notebook.
4. Add a new order in Databricks and confirm it appears in Fabric without any copy.
5. Optionally, create shortcuts through the REST API so they can be scripted.

POST https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/items/<lakehouseId>/shortcuts
{
  "path": "Tables",
  "name": "orders",
  "target": {
    "adlsGen2": {
      "location": "https://<storage>.dfs.core.windows.net",
      "subpath": "/silver/olist/orders",
      "connectionId": "<connectionId>"
    }
  }
}

Validation check: SELECT COUNT(*) FROM orders on the SQL analytics endpoint must equal the Databricks count of the same Delta table, before and after the new order.
What it proves: OneLake shortcuts, connections and credentials, and avoiding data duplication between Databricks and Fabric.
Resume line: "Exposed ADLS Gen2 Delta tables in Microsoft Fabric with OneLake shortcuts, giving Fabric users zero-copy SQL access to Databricks data."

Project 53: Fabric Warehouse with T-SQL stored procedures and a pipeline

Level: Intermediate · Time: 2 days · Tools: Fabric Warehouse, T-SQL, COPY INTO, Fabric pipelines
Business question: What are daily sales by store and product, loaded into a T-SQL warehouse by repeatable stored procedures?
Dataset: Contoso Data Generator V2 by SQLBI (generate CSV or Parquet files and land them in ADLS Gen2 or OneLake)

Steps:

1. Create a Warehouse wh_contoso with stg and dw schemas.
2. Load staging tables with COPY INTO from the landing files.
3. Write dw.usp_load_dim_product (update changed rows, insert new ones) and dw.usp_load_fact_sales (delete and reload the affected dates).
4. Build a Fabric pipeline: Copy or COPY INTO → Stored procedure activities → a final row-count check.
5. Add an etl_log table and write start time, end time and row counts from each procedure.

CREATE OR ALTER PROCEDURE dw.usp_load_fact_sales @load_date DATE
AS
BEGIN
    DELETE FROM dw.fact_sales WHERE OrderDate = @load_date;

    INSERT INTO dw.fact_sales (OrderKey, LineNumber, OrderDate, StoreKey, ProductKey, Quantity, NetAmount)
    SELECT OrderKey, LineNumber, OrderDate, StoreKey, ProductKey, Quantity, Quantity * NetPrice
    FROM stg.sales
    WHERE OrderDate = @load_date;

    INSERT INTO dw.etl_log (proc_name, load_date, rows_loaded, logged_at)
    SELECT 'usp_load_fact_sales', @load_date, COUNT(*), SYSUTCDATETIME()
    FROM dw.fact_sales WHERE OrderDate = @load_date;
END;

Validation check: for each load date, rows_loaded in etl_log must equal the staging row count for that date, and running the procedure twice must not change the fact count.
What it proves: Fabric Warehouse T-SQL, idempotent delete-and-insert loading, pipeline orchestration and logging.
Resume line: "Built a Fabric Warehouse for Contoso sales with idempotent T-SQL stored procedures orchestrated by a Fabric pipeline with load logging."

Project 54: Power BI incremental refresh or Direct Lake model on Gold tables

Level: Intermediate · Time: 1 day · Tools: Power BI, Microsoft Fabric, Direct Lake, incremental refresh, DAX
Business question: How do we keep a large sales report fast and fresh without re-importing all history every day?
Dataset: the Contoso Gold tables from Project 53 or a Lakehouse (Contoso Data Generator V2)

Steps:

1. Option A (Direct Lake): create a semantic model on the Lakehouse Gold tables, define relationships and measures, and confirm the model reads Delta files directly.
2. Option B (Import with incremental refresh): in Power BI Desktop, create RangeStart and RangeEnd DateTime parameters and filter the fact on them.
3. Set the policy: store 3 years, refresh the last 3 days.
4. Publish, refresh twice and inspect partitions with SQL Server Management Studio or DAX Studio through the XMLA endpoint.
5. Document which option you chose and why.

let
    Source = Sql.Database("<warehouse-endpoint>", "wh_contoso"),
    Sales = Source{[Schema = "dw", Item = "fact_sales"]}[Data],
    Filtered = Table.SelectRows(Sales,
                 each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    Filtered

Validation check: a DAX query EVALUATE ROW("total", SUM(fact_sales[NetAmount])) must match SELECT SUM(NetAmount) FROM dw.fact_sales in the Warehouse.
What it proves: Direct Lake vs Import trade-offs, incremental refresh policies, the >= and < filter rule that avoids double counting, and model validation.
Resume line: "Configured a Power BI semantic model on Fabric Gold tables using [Direct Lake / incremental refresh], reducing refresh time from [X] to [Y] minutes."

Project 55: Fabric notebooks with a medallion Lakehouse for COVID time series

Level: Intermediate · Time: 1–2 days · Tools: Fabric notebooks, PySpark, window functions, Lakehouse, Power BI
Business question: What is the 7-day average of new COVID-19 cases per country, and how does it compare between countries over time?
Dataset: Our World in Data COVID-19 data (the compact CSV at catalog.ourworldindata.org, with one row per country and date; the older GitHub repository stopped updating in August 2024)

Steps:

1. Create three Lakehouses or three schemas: bronze, silver, gold.
2. In a notebook, download the compact CSV into Files/bronze/covid/ and load it as bronze.covid_raw.
3. In Silver, keep country, date and new_cases, cast types, drop aggregate regions you do not want and remove duplicate country-date rows.
4. In Gold, calculate a 7-day rolling average with a window function.
5. Schedule the notebooks in a Fabric pipeline and build a Direct Lake report.

from pyspark.sql import Window, functions as F

silver = spark.table("silver.covid_daily")
w = Window.partitionBy("country").orderBy("date").rowsBetween(-6, 0)

gold = (silver
    .withColumn("new_cases_7d_avg", F.round(F.avg("new_cases").over(w), 2))
    .withColumn("days_in_window", F.count("new_cases").over(w)))

gold.write.mode("overwrite").format("delta").saveAsTable("gold.covid_7day")

Validation check: for rows where days_in_window = 7 and there are no missing dates, your new_cases_7d_avg should be close to the source's new_cases_smoothed column; investigate any large differences.
What it proves: Fabric notebooks, medallion design in OneLake, window frames and checking your logic against a trusted reference.
Resume line: "Built a Fabric Lakehouse medallion pipeline for Our World in Data COVID-19 time series with PySpark window functions and a Direct Lake report."

14. Intermediate projects 56–60: Governance and operations

Short answer: Mid-level engineers are trusted with sensitive data and live pipelines. These projects show security, lineage, privacy requests, monitoring and event-driven processing.

Project 56: Unity Catalog row filters and column masks for PII

Level: Intermediate · Time: 1 day · Tools: Unity Catalog, SQL UDFs, account groups
Business question: How do we let regional analysts see only their region's customers, and hide email and phone numbers from everyone except the support team?
Dataset: a synthetic customer table generated with the Faker Python library (fake names, emails, phone numbers and addresses, so no real personal data is used)

Steps:

1. Generate 10,000 fake customers with Faker (name, email, phone, region) and save them as dev.silver.customers.
2. Create account groups analysts_south, analysts_north and support_team.
3. Create a row filter function that returns true for admins, or when the user is in the group that matches the row's region.
4. Create a column mask function for email and phone.
5. Test the same query as users in different groups and record what each sees.

CREATE FUNCTION dev.sec.region_filter(region STRING) RETURNS BOOLEAN
RETURN is_account_group_member('admins')
    OR (region = 'South' AND is_account_group_member('analysts_south'))
    OR (region = 'North' AND is_account_group_member('analysts_north'));

CREATE FUNCTION dev.sec.mask_pii(v STRING) RETURNS STRING
RETURN CASE WHEN is_account_group_member('support_team') THEN v ELSE '***MASKED***' END;

ALTER TABLE dev.silver.customers SET ROW FILTER dev.sec.region_filter ON (region);
ALTER TABLE dev.silver.customers ALTER COLUMN email SET MASK dev.sec.mask_pii;

Validation check: a user in analysts_south must get a row count equal to COUNT(*) WHERE region = 'South' (checked by an admin), and every email they see must be masked.
What it proves: Unity Catalog fine-grained access control, row-level security, column masking and testing security as different users.
Resume line: "Secured PII in Databricks with Unity Catalog row filters and column masks, giving regional analysts row-level access and masking contact details."

Project 57: Lineage with Unity Catalog and Microsoft Purview

Level: Intermediate · Time: 1 day · Tools: Unity Catalog lineage, system tables, Microsoft Purview, Azure Databricks
Business question: If the silver.orders table changes, which Gold tables, dashboards and jobs are affected?
Dataset: the Olist Bronze, Silver and Gold tables from Projects 34 and 47 (Olist on Kaggle)

Steps:

1. Run the Olist pipeline end to end in a Unity Catalog workspace so lineage is captured automatically.
2. Open Catalog Explorer, select gold.revenue_by_month and view the Lineage graph, including column-level lineage.
3. Query the system.access.table_lineage system table to list every downstream table of silver.orders.
4. In Microsoft Purview, register and scan the Azure Databricks Unity Catalog source so the same assets and lineage appear in the Purview Data Map.
5. Write a short impact analysis in your README for one proposed column change.

SELECT target_table_full_name, entity_type, MAX(event_time) AS last_seen
FROM system.access.table_lineage
WHERE source_table_full_name = 'dev.silver.orders'
  AND target_table_full_name IS NOT NULL
GROUP BY target_table_full_name, entity_type
ORDER BY last_seen DESC;

Validation check: every Gold table that reads silver.orders in your code must appear in the query result, and the same chain must be visible in the Purview lineage view.
What it proves: automatic lineage, system tables, Purview integration and impact analysis before changes.
Resume line: "Implemented end-to-end lineage for an Olist lakehouse using Unity Catalog system tables and Microsoft Purview, enabling impact analysis before schema changes."

Project 58: GDPR delete requests on Delta (DELETE, VACUUM, retention)

Level: Intermediate · Time: 1 day · Tools: Delta Lake, DELETE, REORG TABLE, VACUUM, Databricks job
Business question: When a customer asks to be forgotten, how do we remove their data from every table and prove it is physically gone?
Dataset: the synthetic Faker customer table from Project 56 plus an orders table that references it (Faker on PyPI)

Steps:

1. Create privacy.erasure_requests with customer_id, requested_at and status.
2. Write a job that deletes matching rows from every table listed in a pii_tables config table.
3. If deletion vectors are enabled, run REORG TABLE ... APPLY (PURGE) so the rows are rewritten out of the data files.
4. Run VACUUM after the retention period so old files, and time-travel access to them, are removed.
5. Mark the request as done and log the Delta versions before and after.

DELETE FROM dev.silver.customers
WHERE customer_id IN (SELECT customer_id FROM privacy.erasure_requests WHERE status = 'open');

DELETE FROM dev.silver.orders
WHERE customer_id IN (SELECT customer_id FROM privacy.erasure_requests WHERE status = 'open');

REORG TABLE dev.silver.customers APPLY (PURGE);
VACUUM dev.silver.customers RETAIN 168 HOURS;

Validation check: after the job, SELECT COUNT(*) for the requested IDs must be 0 in every table in pii_tables; after VACUUM, time travel to a version before the delete must fail for the removed files.
What it proves: how Delta deletes really work (soft deletes, deletion vectors, time travel, VACUUM retention) and building auditable privacy processes.
Resume line: "Built an auditable GDPR erasure process on Delta Lake that deletes customer data across all PII tables and purges it physically with REORG and VACUUM."

Project 59: ADF monitoring dashboard with Log Analytics and KQL

Level: Intermediate · Time: 1 day · Tools: Azure Data Factory, Azure Monitor, Log Analytics, KQL, Azure Workbooks, alerts
Business question: Which pipelines fail most, how long do they take, and are we alerted before the business notices?
Dataset: your own ADF run logs, sent to Log Analytics with ADF diagnostic settings (resource-specific tables such as ADFPipelineRun, ADFActivityRun and ADFTriggerRun)

Steps:

1. Add a diagnostic setting on the data factory that sends pipeline, activity and trigger runs to a Log Analytics workspace in Resource-Specific mode.
2. Run your pipelines from earlier projects for a few days, including some planned failures.
3. Write KQL queries for failures per pipeline, average duration and the slowest activities.
4. Pin the queries to an Azure Workbook or dashboard.
5. Create a log search alert that fires when any pipeline fails, with an action group that sends an email.

ADFPipelineRun
| where TimeGenerated > ago(7d) and Status in ("Succeeded", "Failed")
| extend DurationMin = datetime_diff('minute', End, Start)
| summarize Runs = count(),
            Failures = countif(Status == "Failed"),
            AvgMinutes = avg(DurationMin)
          by PipelineName, bin(TimeGenerated, 1d)
| order by Failures desc

Validation check: for one day, the run and failure counts in your KQL result must match the counts in ADF Studio's Monitor tab for the same time range (allow for the up to 15-minute delay before logs appear).
What it proves: Azure Monitor diagnostics, KQL, workbooks and alerting, which are core operational skills.
Resume line: "Built an ADF operations dashboard with Log Analytics and KQL showing failures and run times per pipeline, with automatic failure alerts."

Project 60: Event-driven pipeline with Event Grid storage events and ADF

Level: Intermediate · Time: 1 day · Tools: Azure Data Factory, storage event trigger, Azure Event Grid, ADLS Gen2, Azure SQL Database
Business question: How do we process each trip file as soon as it lands, instead of waiting for a nightly schedule, and never process the same file twice?
Dataset: Capital Bikeshare System Data (monthly trip history files for Washington, DC)

Steps:

1. Register the Microsoft.EventGrid resource provider in your subscription.
2. Create a storage event trigger on Blob created, with "Blob path begins with" /landing/blobs/bikeshare/ and "Blob path ends with" .csv.
3. Map @triggerBody().folderPath and @triggerBody().fileName to pipeline parameters.
4. In the pipeline, a Lookup checks a file_log table; an If Condition skips files already processed.
5. A Copy activity loads the file to Bronze, and a Stored Procedure activity records the file name, row count and time in file_log.

"pipelines": [{
  "pipelineReference": { "referenceName": "pl_bikeshare_file", "type": "PipelineReference" },
  "parameters": {
    "sourceFolder": "@triggerBody().folderPath",
    "sourceFile": "@triggerBody().fileName"
  }
}],
"typeProperties": {
  "blobPathBeginsWith": "/landing/blobs/bikeshare/",
  "blobPathEndsWith": ".csv",
  "ignoreEmptyBlobs": true,
  "events": [ "Microsoft.Storage.BlobCreated" ]
}

Validation check: upload 3 files and then re-upload one of them; file_log must show exactly 3 distinct processed files, and each logged row count must equal the Copy activity rowsCopied.
What it proves: event-driven architecture, storage event triggers, trigger metadata and idempotent file processing.
Resume line: "Built an event-driven ADF pipeline with Event Grid storage triggers that loads each Capital Bikeshare file on arrival, with a file log that prevents duplicate processing."

15. Advanced projects 61–65: Streaming

These five projects cover the streaming topics that decide senior interviews: event-time windows, watermarks, stream joins, exactly-once writes and change data capture.

Project 61: IoT telemetry with stateful streaming aggregation

Level: Advanced · Time: 3–4 days · Tools: Azure Event Hubs, Azure Databricks, Structured Streaming, Delta Lake, Power BI
Business question: Which devices are overheating right now, and what is each device's average and maximum temperature every 5 minutes?
Dataset: Environmental Sensor Telemetry Data on Kaggle (about 132,000 readings from IoT sensors). Replay it with a Python producer to simulate live devices.

Steps:

1. Create an Event Hubs namespace (Standard tier, which supports the Kafka endpoint) and an event hub iot-telemetry with 4 partitions. Use the device ID as the partition key so each device's events stay in order.
2. Write a Python producer with the azure-eventhub package that reads the CSV, replaces the original timestamp with the current time and sends one JSON event per row, with a small random delay.
3. Read the stream in Databricks through the Kafka endpoint, parse the JSON with from_json and write raw events to iot.bronze.telemetry.
4. Build 5-minute tumbling-window aggregates with a 10-minute watermark and write them to iot.silver.device_5min.
5. Add a second query that writes alerts to iot.gold.overheat_alerts when max_temp crosses a threshold, and show both tables in Power BI.

schema = "device STRING, ts TIMESTAMP, temp DOUBLE, humidity DOUBLE, co DOUBLE"
events = (spark.readStream.format("kafka").options(**kafka_opts).load()
    .select(F.from_json(F.col("value").cast("string"), schema).alias("e"))
    .select("e.*"))

agg = (events.withWatermark("ts", "10 minutes")
    .groupBy(F.window("ts", "5 minutes"), "device")
    .agg(F.avg("temp").alias("avg_temp"), F.max("temp").alias("max_temp"),
         F.count("*").alias("readings")))

(agg.writeStream.outputMode("append")
    .option("checkpointLocation", "/Volumes/iot/chk/device_5min/_chk")
    .toTable("iot.silver.device_5min"))

Validation check: for one closed window, SUM(readings) in iot.silver.device_5min must equal the count of Bronze events for that device and time range. Then stop and restart the stream: no window should appear twice.
What it proves: event-time processing, watermarks, append output mode, state cleanup, checkpoint recovery and Event Hubs partitioning.
Resume line: "Built a real-time IoT pipeline with Azure Event Hubs and Structured Streaming that computes 5-minute device aggregates with watermarks and raises overheating alerts within [X] seconds."

Project 62: Stream-stream join of orders and payments with watermarks

Level: Advanced · Time: 3–4 days · Tools: Azure Event Hubs, Structured Streaming, Delta Lake
Business question: Which orders were paid within one hour, and which orders are still unpaid after one hour so the operations team can follow up?
Dataset: Brazilian E-Commerce Public Dataset by Olist (about 100,000 orders with a separate payments table). The payments file has no timestamp, so your producer adds a payment time when it replays each payment.

Steps:

1. Create two event hubs, orders and payments. Write a producer that sends each order, then sends its payment after a random delay of 0–90 minutes (scaled down to seconds for testing). Skip some payments on purpose.
2. Read both streams in Databricks and parse them into order_id, order_ts, amount and order_id, pay_ts, payment_value.
3. Add a watermark to each stream and join them with a time-range condition, so Spark knows when it can drop old state.
4. Use a left outer join. Matched rows are emitted as payments arrive; unmatched orders are emitted only after the watermark passes the one-hour limit.
5. Write results to shop.silver.order_payment_status and build a Gold view of unpaid orders.

orders = orders_raw.withWatermark("order_ts", "30 minutes").alias("o")
pays = pays_raw.withWatermark("pay_ts", "2 hours").alias("p")

joined = orders.join(
    pays,
    F.expr("""o.order_id = p.order_id
              AND p.pay_ts >= o.order_ts
              AND p.pay_ts <= o.order_ts + INTERVAL 1 HOUR"""),
    "leftOuter")

(joined.select("o.order_id", "o.order_ts", "p.pay_ts", "p.payment_value")
    .writeStream.option("checkpointLocation", "/Volumes/shop/chk/order_pay")
    .toTable("shop.silver.order_payment_status"))

Validation check: every order the producer sent must appear exactly once in the output, either with a payment or with pay_ts NULL: SELECT order_id, COUNT(*) FROM shop.silver.order_payment_status GROUP BY order_id HAVING COUNT(*) > 1 returns no rows.
What it proves: stream-stream joins, why watermarks are needed on both sides, outer-join delay, state size control and late data handling.
Resume line: "Implemented a stream-stream join of orders and payments with watermarks and time bounds, flagging unpaid orders within one hour with bounded streaming state."

Project 63: foreachBatch MERGE for idempotent streaming upserts

Level: Advanced · Time: 2–3 days · Tools: Structured Streaming, Delta Lake MERGE, Auto Loader
Business question: How do we keep an always-current order lines table from a stream that contains updates and duplicate events, without double counting after a restart?
Dataset: Online Retail II from the UCI Machine Learning Repository (1,067,371 transactions from December 2009 to December 2011). Split it into small JSON files and replay them, including some repeated files and changed quantities.

Steps:

1. Convert the Excel file to CSV, add a key line_id (invoice, stock code and row number) and an event_ts, then write the data as many small JSON files. Copy some files twice and change quantities in a few repeats.
2. Ingest the files with Auto Loader into a streaming DataFrame.
3. In foreachBatch, keep only the latest event per line_id inside the micro-batch. A MERGE fails if two source rows match one target row.
4. MERGE into retail.silver.order_lines, updating only when the incoming event is newer.
5. Stop the stream in the middle of a batch, restart it and confirm the table is unchanged by the replay.

from delta.tables import DeltaTable
from pyspark.sql import Window

def upsert(batch_df, batch_id):
    w = Window.partitionBy("line_id").orderBy(F.col("event_ts").desc())
    latest = batch_df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn")
    (DeltaTable.forName(spark, "retail.silver.order_lines").alias("t")
        .merge(latest.alias("s"), "t.line_id = s.line_id")
        .whenMatchedUpdateAll(condition="s.event_ts > t.event_ts")
        .whenNotMatchedInsertAll()
        .execute())

(stream.writeStream.foreachBatch(upsert)
    .option("checkpointLocation", "/Volumes/retail/chk/order_lines").start())

Validation check: COUNT(*) must equal COUNT(DISTINCT line_id) in retail.silver.order_lines, and the total quantity must equal the total from the latest version of each line in the source files.
What it proves: micro-batch semantics, why MERGE inside foreachBatch is idempotent, deduplication before MERGE and safe restarts.
Resume line: "Built an idempotent streaming upsert with foreachBatch and Delta MERGE over about 1M retail transactions, keeping one current row per order line across restarts and duplicate events."

Project 64: CDC streaming with Debezium, Kafka Connect and Event Hubs

Level: Advanced · Time: 4–5 days · Tools: SQL Server CDC, Debezium, Kafka Connect, Azure Event Hubs, Structured Streaming, Delta Lake
Business question: How do we copy every insert, update and delete from an operational SQL Server into the lakehouse within minutes, without heavy queries on the source?
Dataset: AdventureWorks sample databases (restore AdventureWorks2022 into SQL Server Developer edition in Docker).

Steps:

1. Restore AdventureWorks2022 and enable CDC: EXEC sys.sp_cdc_enable_db and then sys.sp_cdc_enable_table for Sales.Customer and Sales.SalesOrderHeader. SQL Server Agent must be running, because Debezium reads from the CDC tables it fills.
2. Run Kafka Connect in Docker with the Debezium SQL Server connector, and point its bootstrap servers at the Event Hubs Kafka endpoint on port 9093.
3. Register the connector below. Debezium creates one topic (event hub) per table, such as aw.AdventureWorks2022.Sales.Customer.
4. In Databricks, read the topic, parse the Debezium envelope (op, before, after, ts_ms) and land it in aw.bronze.customer_cdc.
5. Apply changes to aw.silver.customer with foreachBatch MERGE: op = 'd' deletes, c and u upsert, ordered by ts_ms.

{
  "name": "aw-sqlserver-cdc",
  "config": {
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "<sqlserver-host>",
    "database.port": "1433",
    "database.user": "<cdc-user>",
    "database.password": "${file:/secrets/sql.properties:password}",
    "database.names": "AdventureWorks2022",
    "topic.prefix": "aw",
    "table.include.list": "Sales.Customer,Sales.SalesOrderHeader",
    "schema.history.internal.kafka.bootstrap.servers": "<namespace>.servicebus.windows.net:9093",
    "schema.history.internal.kafka.topic": "aw-schema-history"
  }
}

Validation check: after you insert, update and delete one test customer in SQL Server, aw.silver.customer must show the same final state, and SELECT COUNT(*) must match SELECT COUNT(*) FROM Sales.Customer in the source.
What it proves: log-based CDC, the Debezium event format, handling deletes, ordering by source time and the Kafka compatibility of Event Hubs.
Resume line: "Built log-based CDC from SQL Server to Delta Lake with Debezium, Kafka Connect and Azure Event Hubs, applying inserts, updates and deletes with an end-to-end delay of [X] seconds."

Project 65: Fabric Real-Time Intelligence: eventstream to Eventhouse with a KQL dashboard

Level: Advanced · Time: 2–3 days · Tools: Microsoft Fabric, eventstream, Eventhouse, KQL database, Real-Time Dashboard, Activator
Business question: Which neighbourhoods are running out of bikes right now, and when should the operations team move bikes?
Dataset: Fabric eventstream sample data (built-in Bicycles stream with fields such as BikepointID, Street, Neighborhood and Latitude).

Steps:

1. In a Fabric workspace, create an Eventhouse with a KQL database named rti_bikes.
2. Create an eventstream, add the Bicycles sample data source and publish it.
3. Add the Eventhouse as a destination, with a new table BikeEvents. Check the exact column names in the data preview before you write queries.
4. Write KQL queries for bikes available by neighbourhood over time and for stations with zero bikes.
5. Pin the queries to a Real-Time Dashboard with auto refresh, and add an Activator rule that sends a Teams message when a station has had zero bikes for 15 minutes.

BikeEvents
| where ingestion_time() > ago(1h)
| summarize AvgBikes = avg(toreal(No_Bikes)),
            EmptyStations = dcountif(BikepointID, toint(No_Bikes) == 0)
            by Neighbourhood, bin(ingestion_time(), 5m)
| order by EmptyStations desc

Validation check: compare BikeEvents | count with the events-in count shown in the eventstream monitoring view for the same period; they must match.
What it proves: Fabric Real-Time Intelligence, KQL aggregation with bin(), Eventhouse table design and real-time alerting without Spark.
Resume line: "Built a Fabric Real-Time Intelligence solution that streams bike station events into an Eventhouse, with KQL dashboards and Activator alerts for empty stations."

16. Advanced projects 66–70: Real-time use cases

These projects take streaming skills into business problems that companies actually pay for: fraud, user behaviour, stock levels, unified pipelines and location analytics.

Project 66: Card fraud rules engine on synthetic transactions

Level: Advanced · Time: 4–5 days · Tools: Azure Event Hubs, Structured Streaming, Delta Lake, Unity Catalog
Business question: Which card transactions should be held for review in real time, based on rules that the fraud team can change without a code release?
Dataset: Credit Card Transactions Fraud Detection Dataset on Kaggle (simulated card transactions with merchant, amount, customer location and an is_fraud label).

Steps:

1. Replay the transactions into an event hub card-tx, ordered by transaction time, and land them in fraud.bronze.card_tx.
2. Build a static customer profile table fraud.silver.customer_profile with average amount and home location per card, refreshed daily.
3. Create a rules table fraud.config.rules (rule_id, threshold, is_active) so the fraud team can change thresholds with SQL.
4. Implement three rules: amount more than [threshold] times the customer average (stream-static join), five or more transactions in 10 minutes (sliding window) and merchant far from the home location.
5. Union rule hits into fraud.gold.alerts with rule_id, card number and event time, and compare alerts with the is_fraud label.

tx = spark.readStream.table("fraud.bronze.card_tx").withWatermark("trans_ts", "15 minutes")
limit = spark.table("fraud.config.rules").filter("rule_id = 'VELOCITY_10M'").first()["threshold"]

velocity = (tx.groupBy(F.window("trans_ts", "10 minutes", "5 minutes"), "cc_num")
    .agg(F.count("*").alias("tx_count"), F.sum("amt").alias("tx_amount"))
    .filter(F.col("tx_count") >= limit)
    .withColumn("rule_id", F.lit("VELOCITY_10M")))

(velocity.writeStream.outputMode("append")
    .option("checkpointLocation", "/Volumes/fraud/chk/velocity")
    .toTable("fraud.gold.alerts_velocity"))

Validation check: for each rule, report how many alerts were raised and how many of them have is_fraud = 1. Record your own precision and recall; do not quote a number you did not measure.
What it proves: sliding windows, stream-static joins, configurable rules, and measuring alert quality against labels.
Resume line: "Built a real-time card fraud rules engine with Structured Streaming and configurable rule tables, measured at [X]% precision against labelled transactions."

Project 67: Clickstream sessionization with session windows

Level: Advanced · Time: 3 days · Tools: Azure Databricks, Structured Streaming, session windows, Delta Lake, Power BI
Business question: How long do shopping sessions last, how many events does a session have, and what share of sessions end in a purchase?
Dataset: eCommerce behavior data from multi category store on Kaggle (view, cart and purchase events with event_time, user_id and a user_session column). Start with one month; the files are large.

Steps:

1. Land one month of CSV files in a volume and ingest them with Auto Loader into click.bronze.events.
2. Build sessions per user with session_window, using a 30-minute inactivity gap.
3. Count events, cart adds and purchases per session, and keep start time, end time and duration.
4. Write sessions to click.silver.sessions and a daily funnel table click.gold.session_funnel.
5. Compare your sessions with the dataset's own user_session IDs to see where the two definitions differ.

sessions = (events.withWatermark("event_time", "1 hour")
    .groupBy("user_id", F.session_window("event_time", "30 minutes").alias("s"))
    .agg(F.count("*").alias("events"),
         F.sum((F.col("event_type") == "cart").cast("int")).alias("cart_adds"),
         F.sum((F.col("event_type") == "purchase").cast("int")).alias("purchases"))
    .select("user_id", F.col("s.start").alias("start_ts"), F.col("s.end").alias("end_ts"),
            "events", "cart_adds", "purchases"))

(sessions.writeStream.outputMode("append")
    .option("checkpointLocation", "/Volumes/click/chk/sessions")
    .toTable("click.silver.sessions"))

Validation check: SUM(events) in click.silver.sessions must equal the number of Bronze events that arrived before the final watermark. Investigate any difference: it is late data that was dropped.
What it proves: session windows, dynamic gap logic, funnel metrics and how watermarks drop late events.
Resume line: "Sessionized [X] million clickstream events with Spark session windows and built a session funnel showing cart-to-purchase conversion by day."

Project 68: Real-time inventory with Lakeflow streaming tables

Level: Advanced · Time: 3–4 days · Tools: Lakeflow Declarative Pipelines, streaming tables, materialized views, expectations, Unity Catalog
Business question: What is the stock on hand for each part and supplier right now, and which items will run out soon?
Dataset: TPC-H tables in the Azure Databricks samples catalog (samples.tpch.partsupp gives opening stock in ps_availqty; samples.tpch.lineitem gives sales movements).

Steps:

1. Copy samples.tpch.partsupp into inv.bronze.opening_stock.
2. Write a small job that takes lineitem rows in batches and drops them as JSON movement files (part, supplier, quantity, movement time) into /Volumes/inv/landing/movements/.
3. Create a Lakeflow pipeline with a streaming table that ingests the files with read_files, and an expectation that drops rows with zero or negative quantity.
4. Create a materialized view that subtracts total movements from opening stock.
5. Schedule the pipeline in triggered mode for testing, then switch to continuous mode for the demo.

CREATE OR REFRESH STREAMING TABLE inventory_movements (
  CONSTRAINT positive_qty EXPECT (qty > 0) ON VIOLATION DROP ROW
) AS SELECT partkey, suppkey, CAST(qty AS INT) AS qty, movement_ts
FROM STREAM read_files('/Volumes/inv/landing/movements/', format => 'json');

CREATE OR REFRESH MATERIALIZED VIEW stock_on_hand AS
SELECT s.ps_partkey, s.ps_suppkey,
       s.ps_availqty - COALESCE(SUM(m.qty), 0) AS on_hand
FROM inv.bronze.opening_stock s
LEFT JOIN inventory_movements m
  ON m.partkey = s.ps_partkey AND m.suppkey = s.ps_suppkey
GROUP BY s.ps_partkey, s.ps_suppkey, s.ps_availqty;

Validation check: for 10 random part-supplier pairs, on_hand must equal ps_availqty minus the sum of valid movement quantities in the landing files. The pipeline event log must show how many rows the expectation dropped.
What it proves: declarative streaming, streaming tables vs materialized views, data quality expectations and pipeline monitoring.
Resume line: "Built a real-time inventory pipeline with Lakeflow Declarative Pipelines, streaming tables and expectations, updating stock on hand for [X] part-supplier pairs."

Project 69: Unified batch and streaming (kappa) pipeline for sales

Level: Advanced · Time: 3 days · Tools: Structured Streaming, Delta Lake, Databricks Jobs
Business question: Can one pipeline both rebuild three years of daily store revenue and keep it updated in near real time, so batch and live numbers never disagree?
Dataset: Contoso Data Generator V2 on GitHub (generates Contoso sales data in Parquet, CSV or Delta). Generate a few years of orders.

Steps:

1. Generate the sales data and load it into sales.bronze.sales_events with columns renamed to order_ts, store_key and net_amount.
2. Write one function that reads Bronze as a stream, aggregates daily revenue per store and writes to sales.gold.daily_store_revenue.
3. Run it first with availableNow to replay all history; this is your "batch" backfill.
4. Run the same function with a one-minute processing-time trigger, and keep appending new orders to Bronze.
5. Explain in your README why there is no separate batch job (the kappa idea) and what a lambda architecture would need instead.

def run(trigger):
    (spark.readStream.table("sales.bronze.sales_events")
        .withWatermark("order_ts", "1 day")
        .groupBy(F.window("order_ts", "1 day").alias("d"), "store_key")
        .agg(F.sum("net_amount").alias("revenue"), F.count("*").alias("lines"))
        .select(F.to_date("d.start").alias("sale_date"), "store_key", "revenue", "lines")
        .writeStream.outputMode("append")
        .option("checkpointLocation", "/Volumes/sales/chk/daily_store_revenue")
        .trigger(**trigger).toTable("sales.gold.daily_store_revenue"))

run({"availableNow": True}) # replay all history, then stop
run({"processingTime": "1 minute"}) # same logic, now continuous

Validation check: for any closed day, revenue in sales.gold.daily_store_revenue must equal a plain batch query: SELECT SUM(net_amount) FROM sales.bronze.sales_events WHERE to_date(order_ts) = '<day>' AND store_key = <store>.
What it proves: kappa architecture, checkpoint reuse across triggers, watermark trade-offs (a day closes only after the watermark passes) and reconciliation.
Resume line: "Designed a kappa-style sales pipeline where one Structured Streaming job both backfilled [X] years of history and served near real-time daily revenue."

Project 70: Geospatial analytics with H3 indexes on NYC taxi data

Level: Advanced · Time: 2–3 days · Tools: Azure Databricks, H3 SQL functions, Delta Lake, liquid clustering, Power BI
Business question: Which small areas of New York have the most evening pickups and the highest average fares, at a finer level than taxi zones?
Dataset: NYC TLC Trip Record Data. Use yellow taxi files from 2015; files up to mid-2016 include pickup longitude and latitude, while newer files only have zone IDs.

Steps:

1. Download a few months of 2015 yellow taxi files into a volume and load them into geo.bronze.yellow_2015. Check the schema for pickup_longitude and pickup_latitude.
2. Remove trips with coordinates outside a New York bounding box (many rows have zeros).
3. Add an H3 cell ID at resolution 9 with h3_longlatash3 (no library install is needed on Databricks Runtime).
4. Cluster the Silver table by the H3 column so area queries skip files.
5. Build Gold tables of trips and average fare per cell and hour, and map cell centres in Power BI using h3_centeraswkt.

CREATE OR REPLACE TABLE geo.silver.trips_h3 CLUSTER BY (pickup_h3) AS
SELECT tpep_pickup_datetime, total_amount, trip_distance,
       h3_longlatash3(pickup_longitude, pickup_latitude, 9) AS pickup_h3
FROM geo.bronze.yellow_2015
WHERE pickup_longitude BETWEEN -74.30 AND -73.60
  AND pickup_latitude BETWEEN 40.45 AND 41.00;

SELECT pickup_h3, h3_centeraswkt(pickup_h3) AS centre,
       COUNT(*) AS trips, ROUND(AVG(total_amount), 2) AS avg_fare
FROM geo.silver.trips_h3
WHERE HOUR(tpep_pickup_datetime) BETWEEN 17 AND 20
GROUP BY pickup_h3 ORDER BY trips DESC LIMIT 20;

Validation check: Silver rows plus rows removed by the bounding-box filter must equal Bronze rows, and h3_resolution(pickup_h3) must be 9 for every row.
What it proves: spatial indexing, data cleaning of GPS data, clustering for spatial queries and mapping results.
Resume line: "Indexed [X] million NYC taxi pickups with H3 in Databricks and built evening demand and fare hotspots at resolution 9."

17. Advanced projects 71–75: Platform engineering

Senior engineers are expected to build the platform, not only the pipelines. These projects show infrastructure as code, release pipelines and secure networking.

Project 71: Terraform for ADLS, Key Vault, ADF and Databricks

Level: Advanced · Time: 3–4 days · Tools: Terraform, azurerm provider, ADLS Gen2, Key Vault, Azure Data Factory, Azure Databricks, managed identity
Business question: How can the team create identical dev, test and prod data platforms in minutes, with permissions set correctly every time?
Dataset: no dataset; reference the Terraform azurerm provider documentation.

Steps:

1. Create a remote state backend: a storage account and container for terraform.tfstate, with state locking.
2. Write modules for a resource group, ADLS Gen2 (hierarchical namespace on), Key Vault with RBAC, Data Factory with a system-assigned managed identity, and a Databricks workspace.
3. Grant ADF's managed identity "Storage Blob Data Contributor" on the lake and "Key Vault Secrets User" on the vault.
4. Use one variables file per environment (dev.tfvars, prod.tfvars) and run terraform plan in a pull request.
5. Destroy and recreate dev to prove the code is complete.

resource "azurerm_storage_account" "lake" {
  name = "st${var.env}lake${var.suffix}"
  resource_group_name = azurerm_resource_group.data.name
  location = var.location
  account_tier = "Standard"
  account_replication_type = "ZRS"
  is_hns_enabled = true
}

resource "azurerm_role_assignment" "adf_lake" {
  scope = azurerm_storage_account.lake.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id = azurerm_data_factory.adf.identity[0].principal_id
}

Validation check: running terraform plan immediately after terraform apply must report "No changes", and ADF must connect to the lake with managed identity and no keys.
What it proves: infrastructure as code, remote state, modules, environment variables and least-privilege role assignments.
Resume line: "Codified the Azure data platform (ADLS Gen2, Key Vault, ADF, Databricks) in Terraform modules, creating a full environment in [X] minutes with managed-identity access."

Project 72: Bicep deployment of a data landing zone

Level: Advanced · Time: 2–3 days · Tools: Bicep, Azure CLI, Azure Resource Manager, ADLS Gen2, Log Analytics
Business question: How do we give each new business unit a standard, secure place to land data, with storage layers, logging and naming rules already in place?
Dataset: no dataset; reference What is Bicep? on Microsoft Learn.

Steps:

1. Write main.bicep with parameters for environment and location, and modules for storage, Key Vault and a Log Analytics workspace.
2. Create the lake with Bronze, Silver and Gold containers, public blob access disabled and TLS 1.2 minimum.
3. Add diagnostic settings that send storage logs to Log Analytics.
4. Preview every change with az deployment group what-if before deploying.
5. Deploy with az deployment group create -g rg-data-dev -f main.bicep -p env=dev, then deploy again to show the deployment is idempotent.

param env string
param location string = resourceGroup().location

resource lake 'Microsoft.Storage/storageAccounts@2023-05-01' = {
  name: 'st${env}lake${uniqueString(resourceGroup().id)}'
  location: location
  sku: { name: 'Standard_ZRS' }
  kind: 'StorageV2'
  properties: { isHnsEnabled: true, allowBlobPublicAccess: false, minimumTlsVersion: 'TLS1_2' }
}

resource layers 'Microsoft.Storage/storageAccounts/blobServices/containers@2023-05-01' = [for c in ['bronze', 'silver', 'gold']: {
  name: '${lake.name}/default/${c}'
}]

Validation check: a second deployment with the same parameters must show no changes in what-if, and az storage account show must report isHnsEnabled: true and allowBlobPublicAccess: false.
What it proves: Bicep syntax, modules, loops, what-if previews, idempotent deployments and secure storage defaults.
Resume line: "Built a reusable Bicep data landing zone with ADLS Gen2 layers, Key Vault and Log Analytics, deployed per business unit with what-if previews."

Project 73: Azure DevOps CI/CD for ADF with ARM templates and approvals

Level: Advanced · Time: 3–4 days · Tools: Azure DevOps (Repos and Pipelines), Azure Data Factory, ARM templates, PowerShell
Business question: How do we move ADF changes from dev to test to prod safely, with a review and an approval before production?
Dataset: any pipelines from your earlier projects; reference Continuous integration and delivery in Azure Data Factory.

Steps:

1. Connect only the dev data factory to Azure Repos Git. Test and prod factories are updated only by the release pipeline.
2. Add a build folder with a package.json that uses the @microsoft/azure-data-factory-utilities npm package to validate and export the ARM template.
3. Create a multi-stage YAML pipeline: build (validate and export), deploy to test, deploy to prod.
4. Put an approval check on the adf-prod environment so a lead must approve production deployments.
5. Stop triggers before deployment and start them after, using Microsoft's pre- and post-deployment script, and override linked service values (Key Vault name, storage URL) per environment.

stages:
- stage: Build
  jobs:
  - job: export_arm
    steps:
    - task: Npm@1
      inputs: { command: custom, workingDir: build, customCommand: 'run build export $(Build.Repository.LocalPath)/adf /subscriptions/<sub-id>/resourceGroups/rg-data-dev/providers/Microsoft.DataFactory/factories/adf-dev "ArmTemplate"' }
    - publish: build/ArmTemplate
      artifact: adf-arm
- stage: Prod
  dependsOn: Build
  jobs:
  - deployment: deploy_prod
    environment: adf-prod # approval check configured on this environment

Validation check: after a release, the prod factory must contain the same pipeline names and versions as the published ARM artifact, and its linked services must point to prod resources, not dev.
What it proves: ADF Git integration, ARM template export, parameter overrides, trigger handling and gated releases.
Resume line: "Set up Azure DevOps CI/CD for Azure Data Factory with automated ARM template export, environment-specific parameters and approval-gated production releases."

Project 74: Multi-environment Databricks Asset Bundles with service principals

Level: Advanced · Time: 3 days · Tools: Databricks Asset Bundles (now called Declarative Automation Bundles), Databricks CLI, service principals, GitHub Actions
Business question: How do we deploy the same Databricks jobs to dev and prod, where prod runs as a service principal and nobody deploys from a laptop?
Dataset: any job from your earlier projects; reference Declarative Automation Bundles on Microsoft Learn.

Steps:

1. Run databricks bundle init and move your notebooks and job definitions into the bundle project.
2. Define dev and prod targets. Dev uses development mode, which prefixes resources with your name; prod runs as a service principal.
3. Use variables for the catalog name, so dev writes to dev and prod writes to prod.
4. In GitHub Actions, run databricks bundle validate on every pull request and databricks bundle deploy -t prod on merge to main, authenticating as the service principal with OIDC federation instead of a stored secret.
5. Add a unit-test step with pytest for your transformation functions before deployment.

bundle:
  name: sales_pipeline
variables:
  catalog: { default: dev }
targets:
  dev:
    mode: development
    default: true
    workspace: { host: "https://adb-<dev-id>.azuredatabricks.net" }
  prod:
    mode: production
    workspace: { host: "https://adb-<prod-id>.azuredatabricks.net" }
    variables: { catalog: prod }
    run_as: { service_principal_name: "<prod-sp-application-id>" }

Validation check: in the prod workspace, the job's "Run as" must show the service principal, and the job must have no owner who is a person.
What it proves: bundle targets, variables, service principals, OIDC-based CI/CD and testing before deployment.
Resume line: "Automated Databricks deployments to dev and prod with bundles and GitHub Actions, running production jobs as a service principal with tests on every pull request."

Project 75: Private networking: private endpoints, VNet-injected Databricks, no public storage access

Level: Advanced · Time: 3–4 days · Tools: Azure Virtual Network, private endpoints, private DNS zones, VNet injection, NAT gateway, Azure Databricks, ADLS Gen2
Business question: How do we prove to the security team that lake data never travels over the public internet and the storage account rejects public access?
Dataset: no dataset; reference Deploy Azure Databricks in your Azure virtual network (VNet injection).

Steps:

1. Create a VNet with two delegated subnets for Databricks (container and host, each at least /26) and one subnet for private endpoints.
2. Deploy the Databricks workspace with VNet injection and secure cluster connectivity (no public IPs), and attach a NAT gateway for outbound traffic.
3. Create private endpoints for the lake's dfs and blob sub-resources. Microsoft recommends both, because some ADLS operations are redirected to the blob endpoint.
4. Link the private DNS zones privatelink.dfs.core.windows.net and privatelink.blob.core.windows.net to the VNet.
5. Disable public network access on the storage account. For ADF, use a managed virtual network with managed private endpoints.

LAKE_ID=$(az storage account show -g rg-data -n <storage> --query id -o tsv)

az network private-endpoint create -g rg-data -n pe-lake-dfs \
  --vnet-name vnet-data --subnet snet-pe \
  --private-connection-resource-id "$LAKE_ID" \
  --group-id dfs --connection-name lake-dfs

az network private-dns zone create -g rg-data -n privatelink.dfs.core.windows.net
az network private-endpoint dns-zone-group create -g rg-data \
  --endpoint-name pe-lake-dfs --name default \
  --private-dns-zone privatelink.dfs.core.windows.net --zone-name dfs

az storage account update -g rg-data -n <storage> --public-network-access Disabled

Validation check: from a cluster, nslookup <storage>.dfs.core.windows.net must return a private 10.x address and reads must succeed; from your laptop, the same read must fail with an authorization error.
What it proves: private endpoints, private DNS, VNet injection, secure cluster connectivity and data exfiltration controls.
Resume line: "Secured an Azure lakehouse with VNet-injected Databricks, private endpoints for ADLS Gen2 and public access disabled, verified with DNS and access tests."

18. Advanced projects 76–80: Governance and sharing

Governance projects show that you can make data safe to use at company scale: clear ownership, controlled sharing, stable schemas and trusted freshness.

Project 76: Data mesh with domain catalogs in Unity Catalog

Level: Advanced · Time: 3 days · Tools: Unity Catalog, external locations, account groups, tags, Azure Databricks
Business question: How can the sales, logistics and customer teams each own and publish their data, while other teams use only the published, documented data products?
Dataset: Brazilian E-Commerce Public Dataset by Olist (split into domains: orders and payments for sales, shipping for logistics, customers and reviews for customer).

Steps:

1. Create one storage container and external location per domain, and one catalog per domain: sales_domain, logistics_domain and customer_domain.
2. Make each domain team's account group the owner of its catalog.
3. Inside each catalog, keep internal schemas (raw, work) private and publish only a products schema.
4. Add comments and tags (owner, SLA, contains_pii) to every product table or view.
5. Grant other domains read access to products only, and show in Catalog Explorer that internal schemas are hidden from them.

CREATE CATALOG IF NOT EXISTS sales_domain
  MANAGED LOCATION 'abfss://sales@<storage>.dfs.core.windows.net/';
ALTER CATALOG sales_domain OWNER TO `sales-data-team`;
CREATE SCHEMA IF NOT EXISTS sales_domain.products COMMENT 'Published sales data products';

CREATE OR REPLACE VIEW sales_domain.products.orders_daily
  COMMENT 'Owner: sales-data-team. Refreshed daily by 06:00 IST.'
AS SELECT order_date, order_status, COUNT(*) AS orders, SUM(payment_value) AS revenue
   FROM sales_domain.work.orders_enriched GROUP BY ALL;

GRANT USE CATALOG ON CATALOG sales_domain TO `logistics-data-team`;
GRANT USE SCHEMA, SELECT ON SCHEMA sales_domain.products TO `logistics-data-team`;

Validation check: signed in as a logistics user, SELECT * FROM sales_domain.products.orders_daily succeeds and SELECT * FROM sales_domain.work.orders_enriched fails with a permission error.
What it proves: data mesh ideas in practice, catalog design, ownership, data products, tagging and least-privilege grants.
Resume line: "Designed a data mesh on Unity Catalog with domain-owned catalogs and published data product schemas for sales, logistics and customer teams."

Project 77: Delta Sharing to an external partner

Level: Advanced · Time: 2 days · Tools: Delta Sharing (now documented as OpenSharing in Azure Databricks), Unity Catalog, pandas, Power BI
Business question: How do we give a delivery partner live, read-only access to their daily order data without copying files or creating accounts in our workspace?
Dataset: Gold tables from the Olist dataset; reference What is OpenSharing? on Microsoft Learn.

Steps:

1. Build shop.gold.partner_orders_daily with only the columns the partner needs. Remove all personal data.
2. Enable sharing on the metastore (metastore admin), then create a share and add the table with history so the partner can also stream it.
3. Create an open recipient (token-based) for a partner without Databricks, and grant the share.
4. As the "partner", download the credential file and read the table with the delta-sharing Python connector and with Power BI.
5. Revoke access and show that the partner can no longer read.

CREATE SHARE IF NOT EXISTS partner_logistics COMMENT 'Daily orders for delivery partner';
ALTER SHARE partner_logistics ADD TABLE shop.gold.partner_orders_daily WITH HISTORY;

CREATE RECIPIENT IF NOT EXISTS partner_x COMMENT 'Delivery partner X (open sharing)';
GRANT SELECT ON SHARE partner_logistics TO RECIPIENT partner_x;

-- Audit what is shared and with whom
SHOW ALL IN SHARE partner_logistics;
SHOW GRANTS ON SHARE partner_logistics;

Validation check: the partner's row count from delta_sharing.load_as_pandas(...) must equal SELECT COUNT(*) FROM shop.gold.partner_orders_daily, and after REVOKE the same call must fail.
What it proves: open data sharing, recipients and tokens, sharing with history, revoking access and one key limitation: tables with row filters or column masks cannot be shared, so you share a clean table or a view.
Resume line: "Shared live Gold tables with an external partner through Delta Sharing with no data copies, including token-based access, auditing and revocation."

Project 78: Data contracts with schema enforcement and CI checks

Level: Advanced · Time: 3 days · Tools: YAML data contracts, pytest, Delta Lake constraints, GitHub Actions
Business question: How do we stop an upstream change, like a renamed or retyped column, from silently breaking reports downstream?
Dataset: Olist orders from the Kaggle Olist dataset; contract format based on the Open Data Contract Standard.

Steps:

1. Write contracts/orders.yaml with the owner, version, columns, types, required flags and a freshness promise.
2. Add Delta constraints that match the contract, for example ALTER TABLE shop.silver.orders ADD CONSTRAINT valid_status CHECK (order_status IN ('delivered','shipped','canceled','invoiced','processing','unavailable','approved','created')).
3. Write a pytest test that compares the contract with the actual table schema.
4. Run the test in GitHub Actions on every pull request, against a small test table built by the same code.
5. Make a breaking change on a branch (rename a column) and show the pull request failing.

import yaml

def test_orders_contract(spark):
    contract = yaml.safe_load(open("contracts/orders.yaml"))
    expected = {c["name"]: c["physicalType"] for c in contract["schema"][0]["properties"]}
    actual = {f.name: f.dataType.simpleString() for f in spark.table("test.silver.orders").schema}

    missing = sorted(set(expected) - set(actual))
    wrong_type = sorted(k for k in expected if k in actual and expected[k] != actual[k])
    assert not missing and not wrong_type, f"missing={missing}, wrong_type={wrong_type}"

Validation check: the CI run on the branch with the renamed column must fail with the column name in the message, and the run on main must pass.
What it proves: data contracts, schema enforcement, Delta CHECK constraints and shifting data quality left into CI.
Resume line: "Introduced data contracts for core tables with automated schema checks in GitHub Actions, blocking breaking changes before they reached production."

Project 79: Row-level security in a Fabric Warehouse and Power BI

Level: Advanced · Time: 2 days · Tools: Microsoft Fabric Warehouse, T-SQL security policies, Power BI
Business question: How can each regional sales manager see only their region's sales in the same report, with the rule enforced in the warehouse?
Dataset: AdventureWorksDW from the AdventureWorks sample databases (internet sales with sales territories).

Steps:

1. Load a wide sales fact with a SalesTerritoryRegion column into a Fabric Warehouse.
2. Create a mapping table dbo.user_region_map (user email, region), with a row value of 'ALL' for national managers.
3. Create an inline table-valued function and a security policy, as below. See Row-level security in Fabric Data Warehouse.
4. Build a Power BI report on the warehouse. Note that Direct Lake falls back to DirectQuery when warehouse RLS applies, so check performance.
5. Test with two test users from different regions.

CREATE SCHEMA sec;
GO
CREATE FUNCTION sec.fn_region_filter(@Region AS varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  SELECT 1 AS allowed
  FROM dbo.user_region_map m
  WHERE m.user_email = USER_NAME() AND (m.region = @Region OR m.region = 'ALL');
GO
CREATE SECURITY POLICY sec.RegionFilter
ADD FILTER PREDICATE sec.fn_region_filter(SalesTerritoryRegion) ON dbo.FactSalesWide
WITH (STATE = ON);

Validation check: for each test user, the row count they see must equal SELECT COUNT(*) FROM dbo.FactSalesWide WHERE SalesTerritoryRegion = '<their region>' run by an admin with the policy turned off.
What it proves: predicate-based RLS, security policies, mapping tables, Power BI behaviour with warehouse security and testing access rules.
Resume line: "Implemented row-level security in a Microsoft Fabric Warehouse so regional managers see only their region in shared Power BI reports."

Project 80: Data freshness SLAs and observability with alerts

Level: Advanced · Time: 2–3 days · Tools: Unity Catalog information_schema, Databricks SQL alerts, Azure Monitor, Log Analytics, KQL
Business question: Which tables missed their agreed refresh time today, and was the team alerted before the business noticed?
Dataset: your own Gold tables from earlier projects; reference Overview of Azure Monitor alerts.

Steps:

1. Create ops.config.freshness_sla with the full table name, owner and allowed delay in minutes.
2. Create a view that compares each table's last change time with its SLA.
3. Create a Databricks SQL alert on the view that fires when any row has status 'BREACH', and send it to email or Teams.
4. Send ADF diagnostic logs to Log Analytics and add an Azure Monitor log search alert on failed pipeline runs (ADFPipelineRun | where Status == "Failed").
5. Build one dashboard with freshness, failures and row-count trends per table.

CREATE OR REPLACE VIEW ops.monitor.freshness_status AS
SELECT s.table_full_name, s.owner, s.max_delay_minutes, t.last_altered,
       timestampdiff(MINUTE, t.last_altered, current_timestamp()) AS delay_minutes,
       CASE WHEN timestampdiff(MINUTE, t.last_altered, current_timestamp()) > s.max_delay_minutes
            THEN 'BREACH' ELSE 'OK' END AS status
FROM ops.config.freshness_sla s
JOIN system.information_schema.tables t
  ON concat_ws('.', t.table_catalog, t.table_schema, t.table_name) = s.table_full_name;

Validation check: pause the job that loads one table, wait past its SLA and confirm that exactly one 'BREACH' row appears and the alert arrives. last_altered also changes on maintenance such as OPTIMIZE, so for critical tables use the maximum of a load timestamp column instead.
What it proves: data observability, SLA thinking, Databricks SQL alerts, Azure Monitor log alerts and KQL.
Resume line: "Built freshness SLA monitoring for [X] production tables with Databricks SQL alerts and Azure Monitor, cutting time to detect late data to [Y] minutes."

19. Advanced projects 81–85: Migration and scale

Migration and scale projects are common in Indian service companies and product teams. They show judgement: what to move as-is, what to rewrite and how to prove nothing was lost.

Project 81: Migrate SSIS packages to ADF (Azure-SSIS IR vs rewrite)

Level: Advanced · Time: 4–5 days · Tools: SSIS, Azure-SSIS integration runtime, SSISDB, Azure Data Factory, Azure SQL Database
Business question: Should a company lift and shift its SSIS packages to Azure, or rewrite them as ADF pipelines, and how do we prove the migrated load gives the same result?
Dataset: Wide World Importers samples on GitHub (includes the wwi-ssis ETL project that loads WideWorldImportersDW).

Steps:

1. Restore WideWorldImporters and WideWorldImportersDW, and run the wwi-ssis package locally once to record baseline row counts.
2. Path A (lift and shift): create an Azure-SSIS IR in ADF with SSISDB in Azure SQL, deploy the project and run it with the Execute SSIS Package activity.
3. Path B (rewrite): rebuild one fact load (for example Movement) as ADF Copy activities to staging plus a stored procedure for the merge.
4. Compare both paths on run time, cost per run, effort and ease of maintenance, and write the comparison as a decision table in your README.
5. Follow Microsoft's SSIS migration guidance for assessment and scheduling options.

-- Check recent package runs on the Azure-SSIS IR (status 7 = succeeded, 4 = failed)
SELECT TOP 20 e.execution_id, e.folder_name, e.project_name, e.package_name,
       e.status, e.start_time, e.end_time
FROM SSISDB.catalog.executions AS e
ORDER BY e.start_time DESC;

-- Reconcile the Movement fact after each path
SELECT COUNT(*) AS movement_rows, SUM(Quantity) AS total_quantity
FROM Fact.Movement;

Validation check: after loading the same date range, movement_rows and total_quantity must be identical for Path A and Path B.
What it proves: SSIS migration options, Azure-SSIS IR setup, ADF rewrites, reconciliation and making a cost-and-effort recommendation.
Resume line: "Migrated the Wide World Importers SSIS ETL to Azure, compared Azure-SSIS IR with an ADF rewrite and recommended a path based on [X] run time and [Y] cost per run."

Project 82: Migrate a Synapse dedicated SQL pool to a Fabric Warehouse

Level: Advanced · Time: 4 days · Tools: Azure Synapse dedicated SQL pool, CETAS, ADLS Gen2, Microsoft Fabric Warehouse, COPY INTO, Fabric Migration Assistant
Business question: How do we move a warehouse from Synapse to Fabric with no data loss, and which code must change?
Dataset: AdventureWorksDW from the AdventureWorks sample databases, loaded into a small dedicated SQL pool. Pause the pool when you are not using it.

Steps:

1. Inventory tables, views and stored procedures, and read Microsoft's migration planning guide.
2. Convert the DDL: remove DISTRIBUTION and index options and map types (for example NVARCHAR to VARCHAR, DATETIME to DATETIME2, MONEY to DECIMAL(19,4), TINYINT to SMALLINT).
3. Export data from Synapse to Parquet in ADLS with CETAS, then load it into Fabric with COPY INTO.
4. Try the Fabric Migration Assistant on the same pool and compare its output with your manual conversion.
5. Rewrite stored procedures that use unsupported T-SQL, and repoint the Power BI model.

-- Synapse dedicated SQL pool (source)
CREATE TABLE dbo.FactInternetSales (
  SalesOrderNumber NVARCHAR(20), CustomerKey INT, OrderDate DATETIME, SalesAmount MONEY)
WITH (DISTRIBUTION = HASH(CustomerKey), CLUSTERED COLUMNSTORE INDEX);

-- Fabric Warehouse (target)
CREATE TABLE dbo.FactInternetSales (
  SalesOrderNumber VARCHAR(20), CustomerKey INT, OrderDate DATETIME2(6), SalesAmount DECIMAL(19,4));

COPY INTO dbo.FactInternetSales
FROM 'https://<storage>.dfs.core.windows.net/export/FactInternetSales/*.parquet'
WITH (FILE_TYPE = 'PARQUET');

Validation check: run SELECT YEAR(OrderDate), COUNT(*), SUM(SalesAmount) FROM dbo.FactInternetSales GROUP BY YEAR(OrderDate) on both sides; every year must match exactly.
What it proves: warehouse migration planning, data type mapping, CETAS and COPY INTO, code refactoring and reconciliation by business totals.
Resume line: "Migrated an AdventureWorksDW warehouse from a Synapse dedicated SQL pool to a Fabric Warehouse, with type mapping, CETAS export, COPY INTO loads and year-level reconciliation."

Project 83: Hive metastore tables to Databricks Unity Catalog

Level: Advanced · Time: 3 days · Tools: Unity Catalog, UCX, SYNC, DEEP CLONE, Azure Databricks
Business question: How do we move legacy Hive metastore tables and jobs into Unity Catalog without breaking downstream jobs?
Dataset: create legacy tables from the Azure Databricks sample datasets (copy a few TPC-H tables into hive_metastore as managed and external tables). Use a workspace where the Hive metastore is still available.

Steps:

1. Create the legacy setup: two external tables on ADLS and two managed tables in hive_metastore.legacy_sales, plus one job that reads them.
2. Install UCX from Databricks Labs and run its assessment to list tables, jobs and code that need changes.
3. Upgrade external tables with SYNC (metadata only, data stays where it is).
4. Copy managed tables with DEEP CLONE (or CTAS), because their data sits in workspace storage.
5. Update job code to three-level names (main.sales.orders) and grant permissions in Unity Catalog.

-- External table: register in Unity Catalog without moving data
SYNC TABLE main.sales.orders FROM hive_metastore.legacy_sales.orders DRY RUN;
SYNC TABLE main.sales.orders FROM hive_metastore.legacy_sales.orders;

-- Managed table: copy data into a Unity Catalog managed table
CREATE TABLE IF NOT EXISTS main.sales.customers
  DEEP CLONE hive_metastore.legacy_sales.customers;

-- Permissions now live in Unity Catalog
GRANT SELECT ON SCHEMA main.sales TO `analysts`;

Validation check: for every migrated table, row count and SUM of a key numeric column must match between hive_metastore and main, and the updated job must run successfully using only Unity Catalog names.
What it proves: Unity Catalog migration patterns, SYNC vs clone, external vs managed tables, assessment tooling and code updates.
Resume line: "Migrated legacy Hive metastore tables and jobs to Unity Catalog using UCX assessment, SYNC for external tables and DEEP CLONE for managed tables, with full reconciliation."

Project 84: Large backfill framework with parallel date partitions

Level: Advanced · Time: 3 days · Tools: Azure Databricks, Delta Lake replaceWhere, Python concurrency or a Databricks for-each task, control tables
Business question: How do we reload several years of history after a logic change, safely, in parallel, and with the ability to rerun only failed months?
Dataset: NYC TLC Trip Record Data (monthly Parquet files across several years).

Steps:

1. Create ops.config.backfill_control with one row per month (ym) and a target table partitioned by ym, so parallel writes touch different partitions and do not conflict.
2. Write a load_month function that reads one month, casts columns to one schema and overwrites only that month with replaceWhere.
3. Log each result (rows, status, error) to an append-only table ops.log.backfill_runs; appends from parallel tasks do not conflict.
4. Run pending months in parallel with a thread pool (or a Databricks job with a for-each task).
5. Rerun the framework; only months without a successful log row should run again.

from concurrent.futures import ThreadPoolExecutor

def load_month(ym):
    try:
        df = (spark.read.parquet(f"/Volumes/raw/nyc/yellow/yellow_tripdata_{ym}.parquet")
              .select(*[F.col(c).cast(t) for c, t in TARGET_SCHEMA]).withColumn("ym", F.lit(ym)))
        (df.write.format("delta").mode("overwrite").option("replaceWhere", f"ym = '{ym}'")
           .saveAsTable("nyc.bronze.yellow_hist"))
        log(ym, "SUCCESS", spark.table("nyc.bronze.yellow_hist").filter(f"ym = '{ym}'").count())
    except Exception as e:
        log(ym, "FAILED", 0, str(e)[:500])

with ThreadPoolExecutor(max_workers=4) as pool:
    list(pool.map(load_month, pending_months()))

Validation check: for each month, rows in nyc.bronze.yellow_hist must equal rows in the source file, and running the backfill twice must not change any count.
What it proves: idempotent partition overwrites, parallelism without write conflicts, restartability and schema drift handling.
Resume line: "Built a restartable backfill framework that reloaded [X] months of NYC taxi data in parallel with Delta replaceWhere, rerunning only failed partitions."

Project 85: Disaster recovery with Delta deep clone and geo-redundant storage

Level: Advanced · Time: 3 days · Tools: Delta deep clone, Databricks jobs, ADLS Gen2 GZRS, Unity Catalog, a second region
Business question: If our primary Azure region goes down, how quickly can we serve Gold tables from another region, and how much data could we lose?
Dataset: your Gold tables from earlier projects; reference Clone a table on Azure Databricks and Azure Storage data redundancy.

Steps:

1. Write down the targets first: recovery point objective (RPO, how much data you may lose) and recovery time objective (RTO, how fast you must recover).
2. Create a storage account and catalog in a second region (dr_catalog) and a list of critical tables.
3. Schedule a job that deep clones each critical table into dr_catalog. Re-running a deep clone copies only new or changed files.
4. Store the clone metrics (files copied, bytes copied) in a log table after each run.
5. Compare with storage-level geo-redundancy (GZRS). Storage replication is asynchronous, and Delta table history is not copied by clone, so document what each option protects.

CRITICAL = ["prod.sales.daily_store_revenue", "prod.sales.dim_customer", "prod.sales.fact_orders"]

for src in CRITICAL:
    target = src.replace("prod.", "dr_catalog.", 1)
    metrics = spark.sql(f"CREATE OR REPLACE TABLE {target} DEEP CLONE {src}").first()
    spark.createDataFrame(
        [(src, target, int(metrics["num_copied_files"]), int(metrics["copied_files_size"]))],
        "source STRING, target STRING, files_copied BIGINT, bytes_copied BIGINT"
    ).withColumn("run_ts", F.current_timestamp()).write.mode("append").saveAsTable("ops.log.dr_clone")

Validation check: run a failover drill: point a Power BI report at dr_catalog, compare row counts with production and record the actual recovery time against your RTO.
What it proves: DR planning, RPO and RTO, incremental deep clone, geo-redundant storage trade-offs and running a recovery drill.
Resume line: "Designed disaster recovery for critical Gold tables using incremental Delta deep clone to a second region, meeting an RPO of [X] hours in a tested failover drill."

20. Advanced projects 86–90: AI and cost

Data engineers now feed AI systems and are asked to control cloud bills. These projects show both, plus one deep performance project.

Project 86: RAG-ready pipeline: documents to chunks to Databricks Vector Search

Level: Advanced · Time: 3–4 days · Tools: Azure Databricks, Delta Lake, change data feed, Databricks Vector Search (now called Databricks AI Search), Unity Catalog
Business question: How do we turn a large set of documents into clean, searchable chunks that a chatbot can use, and keep them updated when documents change?
Dataset: Azure documentation source files on GitHub (Markdown articles; start with the articles/data-factory folder). Check the repository licence before you share outputs.

Steps:

1. Copy the Markdown files for one service into a volume /Volumes/rag/raw/adf_docs/.
2. Read each file as one record, remove front matter and image links, and keep the file path as the document ID.
3. Split documents into overlapping chunks (for example 1,500 characters with 200 overlap) and give each chunk a stable ID.
4. Write chunks to a Delta table and enable change data feed, which the sync index needs on standard endpoints.
5. Create a Vector Search (AI Search) endpoint and a Delta Sync index on the table with a Databricks-hosted embedding model, then query it with test questions. See Databricks AI Search.

@F.udf("array<string>")
def chunk(text):
    size, overlap = 1500, 200
    return [text[i:i + size] for i in range(0, max(len(text), 1), size - overlap)]

docs = (spark.read.option("wholetext", "true").text("/Volumes/rag/raw/adf_docs/*.md")
        .select(F.col("_metadata.file_path").alias("doc_path"),
                F.regexp_replace("value", r"(?s)^---.*?---", "").alias("body")))

chunks = (docs.select("doc_path", F.posexplode(chunk("body")).alias("chunk_no", "chunk_text"))
          .withColumn("chunk_id", F.concat_ws("#", "doc_path", "chunk_no")))
chunks.write.format("delta").mode("overwrite").saveAsTable("rag.silver.adf_doc_chunks")
spark.sql("ALTER TABLE rag.silver.adf_doc_chunks SET TBLPROPERTIES (delta.enableChangeDataFeed = true)")

Validation check: chunk_id must be unique, no chunk may be empty, and for 10 test questions the top 3 results must come from the article you expect. Record your hit rate as [X] out of 10.
What it proves: unstructured data pipelines, chunking strategy, change data feed, vector index sync and retrieval testing.
Resume line: "Built a RAG-ready pipeline that chunks Azure documentation into Delta tables and syncs them to Databricks Vector Search, with [X]/10 retrieval accuracy on test questions."

Project 87: Enrich product data with Azure OpenAI in a batch pipeline

Level: Advanced · Time: 3 days · Tools: Azure OpenAI, Azure Databricks, pandas UDF, Key Vault-backed secret scope, Delta Lake
Business question: Can we automatically give every product a clean category, key attributes and a short description, so search and reports work better?
Dataset: Flipkart Products on Kaggle (Flipkart product listings with names, category trees and text descriptions).

Steps:

1. Load the CSV into catalog.bronze.flipkart_products, keep product_name, product_category_tree and description, and add a hash of the description.
2. Deploy a chat model in Azure OpenAI and store the endpoint and key in Key Vault, read through a secret scope.
3. Write a versioned prompt that asks for JSON with category, brand, key_attributes and short_description.
4. Call the model in a pandas UDF with temperature 0, and process only rows whose hash is not already in the output table, so reruns do not pay twice.
5. Parse the JSON with from_json, send invalid responses to a quarantine table, and store the prompt version with each row. For very large volumes, consider the Azure OpenAI Batch API or Databricks ai_query.

import pandas as pd
from openai import AzureOpenAI

@F.pandas_udf("string")
def enrich(desc: pd.Series) -> pd.Series:
    client = AzureOpenAI(azure_endpoint=AOAI_ENDPOINT, api_key=AOAI_KEY, api_version="2024-10-21")
    def one(text):
        r = client.chat.completions.create(
            model="<deployment-name>", temperature=0,
            response_format={"type": "json_object"},
            messages=[{"role": "system", "content": PROMPT_V1},
                      {"role": "user", "content": (text or "")[:4000]}])
        return r.choices[0].message.content
    return desc.map(one)

Validation check: report the share of rows with valid JSON and a category from your allowed list, and manually review 50 random rows. Record both results; do not assume the model is always right.
What it proves: LLMs in batch pipelines, prompt versioning, cost control through caching, secret handling, rate limits and output validation.
Resume line: "Enriched [X] Flipkart product listings with Azure OpenAI in a Databricks batch pipeline, with versioned prompts, JSON validation and incremental processing."

Project 88: ML feature pipeline with Databricks feature tables and MLflow

Level: Advanced · Time: 3–4 days · Tools: Databricks Feature Engineering in Unity Catalog, MLflow, scikit-learn, Databricks Jobs
Business question: Which telecom customers are likely to leave, and how do we give the data science team reusable, versioned features instead of one-off notebooks?
Dataset: Telco Customer Churn on Kaggle (7,043 customers with services, charges and a churn flag).

Steps:

1. Load the CSV into ml.bronze.telco, fix TotalCharges (blank strings to NULL, then cast) and build a label table with customerID and churn_label.
2. Engineer features: tenure bands, number of services, charges per month of tenure and contract type flags.
3. Create a feature table in Unity Catalog with customerID as the primary key.
4. Create a training set with FeatureLookup, train a simple model and log it with the feature metadata in MLflow.
5. Schedule a job that refreshes features daily, and score customers in batch with fe.score_batch.

from databricks.feature_engineering import FeatureEngineeringClient, FeatureLookup
fe = FeatureEngineeringClient()

fe.create_table(name="ml.features.customer_features", primary_keys=["customerID"],
                df=features_df, description="Telco churn features v1")

training_set = fe.create_training_set(
    df=labels_df, label="churn_label", exclude_columns=["customerID"],
    feature_lookups=[FeatureLookup(table_name="ml.features.customer_features",
                                   lookup_key="customerID")])

with mlflow.start_run():
    pdf = training_set.load_df().toPandas()
    model = LogisticRegression(max_iter=1000).fit(pdf.drop(columns="churn_label"), pdf["churn_label"])
    fe.log_model(model=model, artifact_path="model", flavor=mlflow.sklearn,
                 training_set=training_set, registered_model_name="ml.models.churn")

Validation check: the feature table must have exactly one row per customerID (7,043 rows), and the training set row count must equal the label table row count.
What it proves: feature engineering as data engineering, primary keys, point-of-use lookups, MLflow logging and the hand-off between data engineers and data scientists.
Resume line: "Built a governed churn feature pipeline with Databricks feature tables and MLflow, giving data scientists reusable features and a model with [X] AUC."

Project 89: Cost optimization with cluster policies, serverless, autoscaling and a cost dashboard

Level: Advanced · Time: 2–3 days · Tools: Databricks cluster policies, serverless compute, autoscaling, system tables, AI/BI dashboards, Azure Cost Management
Business question: Where is our Databricks spend going, by team and by product, and which rules stop it from growing without control?
Dataset: your own workspace usage in the billable usage system table (system.billing.usage).

Steps:

1. Create a cluster policy that forces auto-termination, limits autoscaling workers, allows only approved node types and adds a fixed cost_center tag.
2. Move scheduled jobs from all-purpose clusters to job compute or serverless jobs, and compare the cost of the same job before and after.
3. Ask a metastore admin to enable access to the system.billing schema.
4. Build the query below into a dashboard: daily list cost by cost centre and product, and the top 10 jobs.
5. Add a monthly budget in Azure Cost Management with alerts at 50%, 80% and 100%.

SELECT u.usage_date,
       COALESCE(u.custom_tags['cost_center'], 'untagged') AS cost_center,
       u.billing_origin_product,
       ROUND(SUM(u.usage_quantity * p.pricing.effective_list.default), 2) AS list_cost_usd
FROM system.billing.usage u
JOIN system.billing.list_prices p
  ON u.cloud = p.cloud AND u.sku_name = p.sku_name AND u.usage_unit = p.usage_unit
 AND u.usage_end_time >= p.price_start_time
 AND (p.price_end_time IS NULL OR u.usage_end_time < p.price_end_time)
WHERE p.currency_code = 'USD'
  AND u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY u.usage_date, list_cost_usd DESC;

Validation check: the dashboard total for a month must be close to the Azure Databricks cost in Azure Cost Management. List price ignores your discounts, so explain any gap. The share of 'untagged' cost should fall after the policy is enforced.
What it proves: FinOps for data platforms, cluster policies, tagging, serverless trade-offs and cost reporting from system tables.
Resume line: "Reduced Databricks spend by [X]% with cluster policies, job compute and serverless jobs, and built a cost dashboard on system billing tables by team and product."

Project 90: Performance tuning a heavy join with AQE, skew hints and Photon

Level: Advanced · Time: 3 days · Tools: Azure Databricks, Adaptive Query Execution, skew join handling, Photon, Spark UI, query profile
Business question: Why does the nightly revenue join take so long, and which change actually fixes it?
Dataset: TPC-H tables in the Azure Databricks samples catalog (samples.tpch.lineitem, orders, supplier). Create skew on purpose by assigning a large share of lineitem rows to a few supplier keys.

Steps:

1. Create perf.bronze.lineitem_skewed from samples.tpch.lineitem, replacing l_suppkey with one value for a large share of rows.
2. Run the baseline join and aggregation with AQE turned off on a non-Photon cluster. Record duration, shuffle size and the longest task from the Spark UI.
3. Turn on AQE with skew join handling and run again. Look for split skewed partitions in the plan.
4. Try the Databricks SKEW hint on the skewed relation and compare with AQE alone.
5. Run the same query on a Photon-enabled cluster of the same size and compare. Write a results table with only your measured numbers.

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionFactor", "5")
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "256m")

result = spark.sql("""
  SELECT /*+ SKEW('l', 'l_suppkey') */ s.s_nationkey,
         SUM(l.l_extendedprice * (1 - l.l_discount)) AS revenue
  FROM perf.bronze.lineitem_skewed l
  JOIN samples.tpch.supplier s ON l.l_suppkey = s.s_suppkey
  GROUP BY s.s_nationkey""")
result.write.format("delta").mode("overwrite").saveAsTable("perf.gold.revenue_by_nation")

Validation check: the revenue result must be identical in every run (baseline, AQE, hint, Photon). Only time and resources may change, and the longest task should shrink after skew handling.
What it proves: reading the Spark UI, shuffle and skew diagnosis, AQE, skew hints, Photon and evidence-based tuning.
Resume line: "Cut a skewed TPC-H revenue join from [X] to [Y] minutes using AQE skew join handling and Photon, with results verified as identical across runs."

21. How to put Azure data engineer projects on GitHub

Short answer: A clean repository with a clear README makes recruiters and interviewers trust that you built the project yourself.

Suggested repository structure:

azure-adventureworks-lakehouse/
├── README.md
├── architecture/
│ └── architecture-diagram.png
├── adf/
│ ├── pipeline/
│ ├── dataset/
│ └── linkedService/
├── databricks/
│ ├── 01_bronze_to_silver.py
│ ├── 02_silver_to_gold.py
│ ├── lib/
│ └── tests/
├── sql/
│ └── validation_checks.sql
├── databricks.yml
└── docs/
    ├── results.md
    └── screenshots/

What your README should include:

Never commit secrets: use Key Vault and secret scopes, and add a .gitignore for local configuration files.

22. How to write Azure data engineer projects on your resume

Short answer: Write each project as an action, the tools and a measurable result. Two or three strong, well-explained projects are enough. For full resume samples from fresher to 10+ years, see our Azure data engineer resume guide.

Formula: Built [what] using [tools] that [result with a number you measured].

Examples:

Tips:

23. How to explain your Azure data engineer project in an interview

Short answer: Explain the business problem, then the data flow from source to report, then your own work, one challenge and the result, in about 2 minutes.

A 2-minute script:

Common follow-up questions:

Practise more with our 200 Azure data engineer interview questions with answers and our 140 Azure Data Factory interview questions, and see which skills employers ask for in our Azure data engineer jobs in Hyderabad guide.

24. How Brolly Academy helps

Frequently asked questions

Sources and further reading