Build an Event-Driven Azure Data Lake

Build an event-driven Azure sales lake with ETL, Parquet, and SQL.

Introduction

30 Second Summary

A sales report can look trustworthy even when one malformed row has slipped into the source file. If that row reaches the totals, every decision starts from the wrong answer.

In this project, you will build an event-driven sales data lake on Azure that validates each batch before it reaches analytics. Azure Synapse Analytics will quarantine malformed rows before publishing accepted data as Parquet for serverless SQL.

What You'll Build

The finished demo reacts to one replaced sales file by producing a fresh analytics result with the malformed row preserved as rejected evidence.

By the end of this project, you'll have:

A six-row sales batch generated in Azure Cloud Shell with five numeric quantities plus one deliberately malformed quantity.
A data-quality proof that visibly stops on the malformed row before the corrected pipeline writes five accepted records plus rejected-row evidence.
An event-driven analytics demo that calculates line revenue from curated data before Azure Event Grid launches a monitored pipeline run when the source file changes.
Secret Mission: Add a uniqueness assertion that rejects duplicate sale_id values automatically.

Are there any prerequisites?

You need an active Azure for Students subscription with remaining credit. This project consumes a small amount of that credit.

Everything runs in your browser, so you do not need a local Python installation or code editor.

Before We Start

Before any Azure resources exist, this is your moment to define the portfolio story behind the project. You will connect what you build to the cloud data engineering skills expected in an AWS Data Engineer role.

Set Up Azure Guardrails and the Data Lake

Cloud ETL workloads can consume your Azure for Students credit after a test run leaves your mind. Your first job is to put a visible cost boundary around this project.

You will use Microsoft Cost Management to track spending. You will also create an Azure Data Lake Storage Gen2 account that keeps the sales data private.

In this step, get ready to:

Put a $5 monthly cost alert around a new project resource group.
Build a hierarchical data lake with two private file systems.
Verify your subscription from an ephemeral Azure Cloud Shell Bash session.

Create the resource group and cost budget

A resource group keeps the project resources inside one management boundary. That boundary gives your budget a precise scope.

Sign in to the Azure portal with the account linked to your Azure for Students subscription.
Search for Resource groups in the portal search bar.
Select Resource groups from the search results.

The Resource groups page lists every management boundary available to your account.

Select Create.
Choose your Azure for Students subscription in Subscription.
Enter <resource-group-name> in Resource group.

The form now ties <resource-group-name> to your student subscription.

Choose a subscription-supported location in Region.
Select Review + Create.

Azure checks the resource group settings. A successful validation confirms that the selected region is available to your subscription.

Select Create after validation succeeds.
Select the new resource group from the resource group list.

Your resource group is ready. It now gives every project resource a shared cost and cleanup boundary.

This $5 budget sends a warning email while resources continue running. It gives you time to investigate unexpected usage.

A Budget Is an Alert

A Microsoft Cost Management budget does not stop resources or spending. Cost data typically updates after usage occurs.

Treat the email as a signal to inspect your resources. Cleanup remains the control that stops future project usage.

Select Budgets from the resource group menu.
Select Add from the top menu.
Confirm that the displayed scope is <resource-group-name>.

The resource group scope limits this budget view to costs associated with your project boundary.

Enter sales-lake-budget as the budget name.
Set Reset period to Monthly.
Enter 5 as the budget amount.

The monthly cost graph updates around the $5 budget amount. This shows the spending window that Azure evaluates each month.

Select Next.
Set the actual-cost alert threshold to 90%.

The alert threshold means Azure evaluates actual project costs against 90% of the $5 budget.

Enter your email address in Alert recipients (email).
Select Create.

Your budget now appears on the resource group's Budgets page. You have a monthly warning signal for this project's Azure usage.

Cannot Create the Budget?

Confirm that the scope shows <resource-group-name>. Budget creation requires permission to manage costs at that scope.

Check that the alert has an email recipient. A budget requires at least one threshold with a recipient.

Help me troubleshoot my resource-group budget.

Create the private data lake

The pipeline needs storage that supports real directories for raw files and analytical outputs. A hierarchical namespace adds those file-system semantics to the storage account.

Why Use a Hierarchical Namespace?

A hierarchical namespace organizes objects into directories that analytics engines can manipulate directly. This supports the raw and curated lake paths used later in the project.

The setting must be enabled when this account is created. Your storage account becomes the foundation for every sales file in the pipeline.

Search for Storage accounts in the Azure portal search bar.
Select Storage accounts from the search results.
Select Create.

The Create a storage account form opens on the Basics tab.

Choose your Azure for Students subscription in Subscription.
Choose <resource-group-name> in Resource group.

The storage account is now scoped to the project resource group.

Storage account names are a little fiddly because they must be globally unique. Use only lowercase letters with numbers in a name between 3 and 24 characters.

Enter <storage-account-name> in Storage account name.
Choose the same location as the resource group in Region.

Azure checks the storage account name as you type. A green availability result confirms that the name can be used.

Choose Blob storage or Azure Data Lake Storage (ADLS) Gen 2 in Preferred storage type.
Choose Standard in Performance.

These choices configure the account as Standard general-purpose v2 storage.

Select the Advanced tab.
Enable Enable hierarchical namespace.

The Data Lake Storage setting is now part of the account configuration. The next setting prevents anonymous access to its data.

Disable Allow enabling anonymous access on individual containers.
Select Review + create.

Azure validates the storage configuration. The summary should show Standard performance with hierarchical namespace enabled.

Select Create after validation succeeds.
Select Go to resource after deployment completes.

The storage account is live with Data Lake Storage capabilities. Anonymous access is disabled across the account.

Storage Account Name Unavailable?

Add a short lowercase suffix to <storage-account-name>. Keep the final name within the displayed character limit.

Help me choose a valid storage account name.

A hierarchical storage container acts as a file system. You need one file system for Synapse workspace storage and another for sales data.

Select Containers under Data storage.
Select + Container.

The New container pane opens with fields for the file-system name and access level.

Enter workspace in Name.
Choose Private (no anonymous access) in Anonymous access level.
Select Create.

You will see workspace in the container list. This file system becomes the primary storage location for the Synapse workspace.

Select + Container again.

A fresh New container pane opens for the sales file system.

Enter sales in Name.
Choose Private (no anonymous access) in Anonymous access level.
Select Create.

You will see workspace plus sales in the container list. Your private lake now has separate homes for workspace metadata and sales data.

Cannot See Both File Systems?

Refresh the Containers view. Confirm that both names use lowercase letters.

Help me create the missing file system.

Start Cloud Shell and verify the setup

Cloud Shell provides an authenticated browser terminal with Azure tools already installed. The ephemeral option keeps this setup quick because it does not create another storage account.

What Does Ephemeral Mean?

An ephemeral Cloud Shell session deletes saved files after the session ends or restarts. Keep this browser session open when later steps create the sales generator and CSV file.

Select the Cloud Shell icon in the Azure portal toolbar.

The Cloud Shell setup pane opens inside the portal.

Choose Bash when prompted to choose a shell.
Select No storage account required in the Getting started pane.

The subscription selector now controls which Azure account context Cloud Shell uses.

Choose your Azure for Students subscription from the subscription dropdown.
Select Apply.

You will see a Bash prompt when the session is ready. Keep this Cloud Shell session open for the next step.

Before you run the final check, which subscription do you expect to find in the account list?

List the subscriptions available to Cloud Shell by running this command:

az account list

The command returns a JSON array of subscriptions available to your signed-in identity. You should see your Azure for Students subscription in that output.

That is the foundation in place. Your guarded subscription now has a private data lake plus an authenticated browser terminal.

Azure for Students Missing?

Confirm that Cloud Shell started with the same account used to create the resource group. Check the selected subscription in the Cloud Shell setup pane.

Help me find my Azure for Students subscription.

Your cost boundary and private lake are ready. Next, you will connect a Synapse workspace to this storage foundation with identity-based access.

Create Synapse and Grant Storage Access

Your Azure Data Lake Storage Gen2 account is ready with secure file systems. The lake still needs a managed pipeline service plus a SQL engine.

Azure Synapse Analytics provides both capabilities in one workspace. You will connect that workspace through a managed identity before opening Synapse Studio.

In this step, get ready to:

Create a Synapse workspace that uses <storage-account-name> for primary storage.
Grant storage access to your Microsoft Entra identity plus the workspace managed identity.
Open Synapse Studio and confirm the default serverless SQL pool is available.

Create the Synapse workspace

The workspace needs the same project region as your existing resources. Its primary storage points to the workspace file system from the previous step.

Why Synapse for this project?

Synapse keeps managed ETL pipelines plus serverless SQL in one workspace. A separate Azure Data Factory resource would add another service to this small data-lake demo.

Switch back to the Azure portal tab from earlier.
Enter Azure Synapse Analytics in the search box at the top.

The search results show the Synapse service under the services category.

Select Azure Synapse Analytics under Services.
Select Create.

The Basics tab opens with the workspace settings.

Select your Azure for Students subscription in Subscription.
Select <resource-group-name> in Resource group.

The workspace now sits inside the same cost boundary as the storage account.

Enter a globally unique <synapse-workspace-name> in Workspace name.
Choose the same subscription-supported region as <resource-group-name> in Region.

The workspace name plus region now identify where Synapse creates the service.

Set Account name to <storage-account-name> under Select Data Lake Storage Gen 2.
Set File system name to workspace.

What does primary storage do?

Primary storage gives the workspace a known location for workspace data plus service logs. Your later pipeline can use the separate sales file system for the retail dataset.

Select Review + create.

Azure checks the workspace configuration before provisioning begins. Expect the deployment to take a few minutes after creation starts.

Confirm that the configuration validation passes.
Select Create.

You will see a successful deployment status when provisioning finishes. That is the workspace created with your existing data lake as primary storage.

Return to the resource list for <resource-group-name>.
Select <synapse-workspace-name> from the resource list.

The workspace Overview page shows its region plus storage details. Confirm that the primary storage points to <storage-account-name>.

Assign data-plane roles

Storage access uses Azure RBAC at the storage-account scope. The Storage Blob Data Contributor role grants read-write data access across the private file systems.

Your identity needs this role for Cloud Shell plus serverless SQL. The workspace identity needs the same role for Synapse pipelines.

Return to the <storage-account-name> storage account from earlier.
Select Access control (IAM) from the resource menu.

The access-control page shows every role assignment scoped to the storage account.

Select Add.
Select Add role assignment.

The role-assignment wizard opens on the Role tab.

Search for Storage Blob Data Contributor.
Select Storage Blob Data Contributor from the results.

The selected role permits storage data operations at this scope.

Select Next.
Select User, group, or service principal on the Members tab.

The wizard is ready to attach the role to your Microsoft Entra account.

Select Select members.
Search for the email address you use to sign in to Azure.

Your Microsoft Entra identity appears in the member search results.

Select your account from the search results.
Select Select.

Your account now appears in the members list for this assignment.

Select Review + assign.
Select Review + assign again to create the assignment.

Good progress. Your Microsoft Entra identity now has read-write access to the lake.

Synapse attempts to grant the same role to its managed identity during workspace creation. You will verify the assignment before adding another one.

Return to the Role assignments tab.
Find <synapse-workspace-name> under the Storage Blob Data Contributor role.

The workspace assignment is complete if its managed identity appears there. Use the next instructions only when that identity is absent.

Select Add if the workspace identity is absent.
Select Add role assignment.

A new role-assignment wizard opens for the workspace identity.

Search for Storage Blob Data Contributor.
Select Storage Blob Data Contributor from the results.

The storage data role is selected again. This time the member is the workspace identity.

Select Next.
Select Managed identity on the Members tab.

The member picker now searches Azure service identities instead of user accounts.

Select Select members.
Choose the Azure Synapse workspace identity type.

The picker now lists Synapse workspace identities from your subscription.

Select your Azure for Students subscription.
Select <synapse-workspace-name>.

The selected member represents Synapse when a pipeline accesses storage.

Select Select.
Select Review + assign.

The review page shows the role plus the workspace managed identity.

Select Review + assign again to create the assignment.

The two security paths are now in place. Cloud Shell uses your identity while Synapse pipelines use the workspace identity.

Return to the Role assignments tab.
Confirm that your account plus <synapse-workspace-name> appear with Storage Blob Data Contributor.

Cannot add the role assignment?

A disabled role-assignment option means your current account cannot grant Azure roles at this scope. Confirm that you are using the Azure for Students subscription owner account.

If the workspace is missing from the managed identity picker, confirm that workspace deployment finished successfully. Also confirm that the picker is using the correct subscription.

Help me troubleshoot the storage role assignment.

Launch Synapse Studio and check the workspace

Every Synapse workspace includes a default serverless SQL endpoint. Synapse Studio exposes that endpoint beside the pipeline plus monitoring tools you use later.

Return to the Overview page for <synapse-workspace-name>.
Select Launch Synapse Studio.

Synapse Studio opens in a new browser tab with your workspace selected. You are now inside the browser interface for pipelines plus SQL.

Select Integrate in the left navigation.

You will see the area used to create pipelines plus data flows.

Select Develop in the left navigation.

You will see the area used for SQL scripts plus other workspace code.

Select Data in the left navigation.

You will see the browser for connected workspace data.

Select Monitor in the left navigation.

You will see the area that records pipeline activity plus trigger runs.

Before you check the SQL connection, what name do you think Azure gives its preconfigured serverless pool?

Return to Develop in the left navigation.
Select the plus sign for Add new resource.

The resource menu shows the authoring options available in this workspace.

Select SQL script.
Open the Connect to dropdown in the SQL script toolbar.

You will see Built-in as the available serverless SQL pool. That confirms the workspace is ready for lake queries later in the project.

Cannot see the serverless pool?

Select Refresh in the SQL script tab if the serverless pool is unavailable. Confirm that Synapse Studio shows <synapse-workspace-name> as the current workspace.

Help me restore the serverless SQL connection.

Your Synapse workspace can now reach the private data lake through the correct identities. Next up, you will generate a controlled sales batch and land it in the raw lake path.

Generate and Land the Raw Sales Batch

Your secure data lake is ready. Your Azure Synapse Analytics workspace has permission to use it.

A pipeline needs a realistic batch with a known defect before its validation can be trusted. You will create that batch in Azure Cloud Shell before landing it in raw storage.

In this step, get ready to:

Create a six-row CSV batch with one malformed quantity.
Upload the batch to the raw data-lake path.
Inspect the landed file to locate the known defect.

Generate the sales batch

The generator gives the pipeline a predictable input. It deliberately places not-a-number in the quantity field for one sale.

Switch back to the Azure Cloud Shell session from earlier.
Select Editor in the Cloud Shell toolbar.

You should see the browser editor beside your existing Bash terminal.

Create generate_sales.py in the Cloud Shell editor.
Paste the generator into generate_sales.py using the code below:

sales_rows = [
    ("S-1001", "2026-10-20", "North", "Notebook", "2", "12.50"),
    ("S-1002", "2026-10-20", "South", "Keyboard", "1", "45.00"),
    ("S-1003", "2026-10-21", "East", "Monitor", "2", "199.99"),
    ("S-1004", "2026-10-21", "West", "Mouse", "3", "24.50"),
    ("S-1005", "2026-10-22", "North", "Headset", "2", "79.99"),
    ("S-1006", "2026-10-22", "South", "Webcam", "not-a-number", "89.00"),
]

header = "sale_id,sale_date,region,product,quantity,unit_price"
csv_lines = [header]
csv_lines.extend(",".join(row) for row in sales_rows)

with open("sales.csv", "w", encoding="utf-8", newline="") as sales_file:
    sales_file.write("\n".join(csv_lines) + "\n")

numeric_quantity_count = sum(1 for row in sales_rows if row[4].isdigit())
malformed_quantity_count = len(sales_rows) - numeric_quantity_count

print(f"Created sales.csv with {len(sales_rows)} rows.")
print(
    f"Numeric quantities: {numeric_quantity_count}; "
    f"malformed quantities: {malformed_quantity_count}."
)

How does the generator work?

The sales_rows list holds six controlled sales records.
The header value names the six columns in the output.
The csv_lines list converts each sales tuple into one comma-separated row.
The sales_file block writes the completed batch to sales.csv.
The two count variables measure numeric quantities plus malformed quantities.

✔️ Awesome, I've got everything!

Your generator now contains the complete six-row test batch. Keep this tab selected if your file matches the code above.

ⓧ I'd like to double check the full code

Compare generate_sales.py with this complete reference:

sales_rows = [
    ("S-1001", "2026-10-20", "North", "Notebook", "2", "12.50"),
    ("S-1002", "2026-10-20", "South", "Keyboard", "1", "45.00"),
    ("S-1003", "2026-10-21", "East", "Monitor", "2", "199.99"),
    ("S-1004", "2026-10-21", "West", "Mouse", "3", "24.50"),
    ("S-1005", "2026-10-22", "North", "Headset", "2", "79.99"),
    ("S-1006", "2026-10-22", "South", "Webcam", "not-a-number", "89.00"),
]

header = "sale_id,sale_date,region,product,quantity,unit_price"
csv_lines = [header]
csv_lines.extend(",".join(row) for row in sales_rows)

with open("sales.csv", "w", encoding="utf-8", newline="") as sales_file:
    sales_file.write("\n".join(csv_lines) + "\n")

numeric_quantity_count = sum(1 for row in sales_rows if row[4].isdigit())
malformed_quantity_count = len(sales_rows) - numeric_quantity_count

print(f"Created sales.csv with {len(sales_rows)} rows.")
print(
    f"Numeric quantities: {numeric_quantity_count}; "
    f"malformed quantities: {malformed_quantity_count}."
)

What should match?

This reference is the complete generator file. Your row values plus variable names should match it exactly.

Save generate_sales.py in the Cloud Shell editor.

Before you run the generator, how many numeric quantities do you expect it to count?

Create sales.csv by running this command in Cloud Shell:

python generate_sales.py

What does this command do?

The python command runs the saved generator. The script writes sales.csv into the current Cloud Shell session.

You should see Created sales.csv with 6 rows. The next line reports five numeric quantities plus one malformed quantity.

That gives you a controlled test batch whose bad row has a known cause.

Did the generator stop early?

Confirm that you saved generate_sales.py before running it.
Compare the punctuation in your file with the complete reference above.

help me troubleshoot why generate_sales.py does not create sales.csv in Azure Cloud Shell.

Upload the batch to the raw zone

The raw zone preserves the source batch before any transformation changes it. Microsoft Entra authentication lets your existing Cloud Shell identity write to Azure Data Lake Storage Gen2 without an account key.

Replace your-storage-account in the command below with the storage account name from earlier.
Upload sales.csv to the raw data-lake path by running the updated command:

az storage fs file upload --source sales.csv -p raw/sales.csv -f sales --account-name your-storage-account --auth-mode login --overwrite true

What does this upload command do?

The --source sales.csv option selects the local file created in Cloud Shell.
The -p raw/sales.csv option sets the destination path inside the file system.
The -f sales option targets the existing sales file system.
The --auth-mode login option authorizes the upload with your signed-in identity.
The --overwrite true option allows later batches to replace the same file.

You should see the upload complete without an authentication failure. Your raw landing zone now has its pipeline input.

Did the upload fail?

Confirm that the account name in the command matches the storage account from earlier.
Confirm that sales.csv exists in the current Cloud Shell session.
Confirm that your identity still has the Storage Blob Data Contributor role on the storage account.

help me troubleshoot an Azure Data Lake Storage upload failure from Cloud Shell.

Inspect the known defect

The next pipeline failure only means something if you can point to the exact bad source value. Storage browser lets you confirm the defect at its landed path.

Switch back to the Azure portal from earlier.
Return to the storage account from earlier.
Select Storage browser from the storage account menu.
Open the sales file system.
Open the raw folder.
Open sales.csv.

Before you inspect the sixth row, which quantity value do you expect the lake to contain?

Inspect the quantity value for sale ID S-1006.

You should see not-a-number in quantity for S-1006. The browser path confirms that the file is stored at sales/raw/sales.csv.

Cannot find the raw file?

Return to the Cloud Shell session from earlier.
Rerun the upload after checking the storage account name.
Refresh Storage browser after the upload completes.

help me find sales.csv in the Azure Storage browser after uploading it.

Your raw batch is landed with a defect you can trace back to its source. Next, you will send it through validation to see whether that malformed value gets caught.

Build the Data Flow and Expose Bad Data

Your six-row sales batch now sits at sales/raw/sales.csv with the malformed quantity ready for a real pipeline test.

A pipeline that silently accepts malformed numeric fields cannot be trusted. In this step, you will use Azure Synapse Analytics to cast each value through a Mapping Data Flow with strict quality checks.

In this step, get ready to:

Create pipeline pl_sales_curate with mapping data flow df_sales_curate.
Cast the sales columns to analytical data types.
Run the strict pipeline to expose the malformed sales row.

Create the pipeline and source

A pipeline controls when work runs. Its Data Flow activity points to the transformation logic that reads the raw file.

Return to Synapse Studio from earlier.
Select Integrate from the left navigation.
Create a new pipeline.
Name the pipeline pl_sales_curate.

You should see a pipeline tab named pl_sales_curate above an empty pipeline canvas.

Add a Data Flow activity to the pipeline canvas.
Create a new mapping data flow for the activity.

You should see the new data flow canvas connected to the pipeline activity.

Name the mapping data flow df_sales_curate.
Set the Data Flow activity to run df_sales_curate.

The activity settings should now reference df_sales_curate.

Return to the df_sales_curate canvas.
Start Data flow debug from the data flow toolbar.

This startup is the slow part. The Spark cluster usually takes 5 to 7 minutes to warm up.

Add a source transformation to the data flow canvas.
Set the source format to DelimitedText.

The source transformation should now show the DelimitedText format.

Point the source to sales/raw/sales.csv in the existing storage account.
Enable First row as header.

The first CSV line now supplies column names instead of becoming a sales record.

Import the source projection.

You should see the six projected columns from sale_id through unit_price.

Source columns missing?

Confirm the source path is sales/raw/sales.csv. Check that First row as header is enabled before importing the projection.

If the source still has no columns, help me troubleshoot a missing DelimitedText projection in Synapse Mapping Data Flow.

Cast and validate the sales fields

CSV values arrive as text. A Cast transformation gives the date, quantity, and price the types required for analytics.

Add a Cast transformation after the source.
Set sale_date to the date type.

The Cast transformation should show a type rule for sale_date.

Set the sale_date format to yyyy-MM-dd.
Set quantity to the integer type.

The cast rules should now cover the date field plus the quantity field.

Set unit_price to the double type.
Enable Assert type check.

Conversion failures can now mark a row as an assertion error.

Add an Assert transformation after the Cast transformation.
Set the Assert ID to valid_sale.

The transformation should now show an assertion rule named valid_sale.

Set the assertion type to Expect true.
Enter not(isError()) && quantity > 0 && unit_price > 0 as the expression.

The rule now requires a clean cast plus positive quantity and price values.

Select Fail data flow for the assertion.

How Does the Quality Rule Work?

The isError() check identifies a row already marked by the failed quantity cast. The remaining checks reject zero or negative business values.

Fail data flow turns any rejected row into a failed activity. This makes bad data visible before it reaches analytics storage.

Add the sink and expose the failure

A sink defines where accepted rows should land. The pipeline will target columnar Parquet files in the curated lake zone.

Add a sink transformation after the Assert transformation.
Set the sink format to Parquet.

The data flow canvas should now end with a Parquet sink.

Select the sales file system for the sink.
Set the destination folder to curated.

The sink should now target sales/curated.

Before you select Debug, do you expect all six rows to reach the Parquet sink?

Return to pipeline pl_sales_curate.
Run the pipeline by selecting Debug.

In the Output pane, you should see a failed Data Flow activity. The malformed S-1006 quantity cannot satisfy the cast and assertion checks.

This Failure Is Intentional

The strict pipeline has exposed the known defect instead of allowing it into the curated dataset. The failed run proves your quality gate is active.

Pipeline succeeded unexpectedly?

Confirm that Assert type check is enabled in the Cast transformation. Check that the assertion has Fail data flow selected.

Confirm the source still points to the batch containing not-a-number for S-1006.

If the activity still succeeds, help me find why my Synapse assertion is not failing the malformed sales row.

You have proved that malformed data cannot pass silently into the curated lake. Next, you will quarantine the rejected row while allowing the five valid sales to reach analytics.

Quarantine Rows, Query Parquet, and Automate Runs

The failed Azure Synapse Analytics run proved that your mapping data flow catches malformed sales data. A useful production pipeline must preserve that evidence without blocking every valid row.

You will quarantine the bad row before publishing the five accepted sales for analytics. You will then connect the pipeline to storage events so each matching file replacement starts a monitored run.

In this step, get ready to:

Redirect assertion failures while writing accepted rows as partitioned Parquet.
Query the curated sales through the built-in serverless SQL pool.
Publish a storage-events trigger that starts the pipeline automatically.

Redirect rejected rows

A quarantine path separates records that fail validation from records that are safe to analyze. The accepted sink will write Parquet files while the malformed row remains available for investigation.

Switch back to df_sales_curate in Synapse Studio.
Select the Assert transformation with the Assert ID valid_sale.

The assertion settings still show Fail data flow selected from the deliberate failure.

Clear Fail data flow.
Select the Parquet sink that targets sales/curated.

The sink tabs now control how accepted output is written.

Select the sink's Errors tab.
Configure failed assertion rows to bypass the accepted output with rejected-row evidence written to sales/rejected.

The accepted sink now excludes rows marked by valid_sale. The rejected path preserves the reason that each excluded row failed.

Set partitioning to Key on sale_date in the Optimize tab.
Set the Parquet file pattern to sales[n].parquet in the Settings tab.

This date partitioning groups accepted records under folders derived from sale_date. The file pattern gives each Parquet file an enumerated name.

Publish the updated data flow by selecting Publish.

Before you run the corrected pipeline, how many rows do you expect in the curated output?

Run pl_sales_curate by selecting Debug.
Confirm the Data Flow activity reaches a successful result in the output pane.
Switch to the Data area to inspect the sales file system.

You should see curated date partitions containing five accepted sales. You should also see rejected-row evidence for S-1006 under sales/rejected.

That is the recovery path working. One malformed quantity is isolated while five usable records continue into the analytics layer.

Pipeline still failing?

Return to the valid_sale assertion to confirm Fail data flow is cleared.
Check the sink's Errors tab to confirm assertion-failure rows bypass the accepted output.
Confirm the rejected output targets sales/rejected.

Help me diagnose the failed data flow run.

Query the curated Parquet data

The built-in serverless SQL pool can query Parquet directly from the data lake. This proves that the curated output is ready for analytics without loading it into a separate database.

Switch to the Develop area in Synapse Studio.
Create a SQL script connected to the built-in serverless SQL pool.

You should see a blank SQL editor connected to the workspace's built-in pool.

Save the script as query_sales.sql.
Add the curated sales query by copying this code into query_sales.sql:

SELECT
    sale_id,
    sale_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS line_revenue
FROM OPENROWSET(
    BULK 'https://your-storage-account.dfs.core.windows.net/sales/curated/sale_date=*/*.parquet',
    FORMAT = 'PARQUET'
) AS sales
ORDER BY sale_id;

What does this query do?

The OPENROWSET call reads every Parquet file under the partitioned sales/curated path.
The multiplication expression calculates line_revenue from each accepted row's quantity and unit price.
The final ordering makes the five accepted sale IDs easy to compare with the original batch.

Replace your-storage-account in the data-lake URL with your storage account name from earlier.
Save query_sales.sql.

Before you select Run, do you expect the malformed sale to appear in the result?

Execute the query by selecting Run.

You should see five accepted sales ordered by sale_id. Each result includes a calculated line_revenue value.

Your curated lake is now answering an analytics question. The missing S-1006 row confirms that rejected data stayed outside the query result.

Query not returning five rows?

Check that the URL contains your actual storage account name.
Confirm the path ends with sales/curated/sale_date=*/*.parquet.
Confirm the corrected pipeline created Parquet files before running the query.

Help me troubleshoot the Parquet query.

Use these tabs to compare your saved query with the project reference.

✔️ Awesome, I've got everything!

Your query returns five accepted sales with a calculated revenue column. Keep your script saved in Synapse Studio.

ⓧ I'd like to double check the full code

SELECT
    sale_id,
    sale_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS line_revenue
FROM OPENROWSET(
    BULK 'https://your-storage-account.dfs.core.windows.net/sales/curated/sale_date=*/*.parquet',
    FORMAT = 'PARQUET'
) AS sales
ORDER BY sale_id;

What should match?

Compare the selected columns with your saved script. Confirm that the data-lake path includes the date-partition wildcard before you substitute your actual storage account name.

Start runs from storage events

A storage-events trigger connects file activity to your pipeline through Azure Event Grid. Replacing the watched source file raises a Blob created event that starts pl_sales_curate automatically.

Switch to the Integrate area in Synapse Studio.
Select the pipeline pl_sales_curate.

The pipeline designer shows the Data Flow activity that runs df_sales_curate.

Select Trigger in the pipeline toolbar.
Select New/Edit.

The trigger pane now lets you define which storage event starts this pipeline.

Choose Storage events as the trigger type.
Select the sales file system in your storage account.

The event source now points at the file system containing your raw batch.

Set Blob path begins with to raw/.
Set Blob path ends with to sales.csv.

The two filters limit automatic runs to the fixed demo path sales/raw/sales.csv.

Publishing this trigger means every matching file replacement can start billable pipeline work. The narrow path filters prevent unrelated files from launching this pipeline.

Select Blob created as the event.
Publish the storage-events trigger by selecting Publish.

The published trigger now listens for a creation or replacement event at the filtered path.

The source file already exists, so overwriting it creates the event needed for the final end-to-end test. You will reuse the authenticated Azure Cloud Shell session from earlier.

Return to Azure Cloud Shell Bash.
Replace your-storage-account in the command below with your storage account name from earlier.

Before you upload the replacement, do you expect the next pipeline run to require Debug?

Replace sales/raw/sales.csv by running this verified upload command:

az storage fs file upload --source sales.csv -p raw/sales.csv -f sales --account-name your-storage-account --auth-mode login --overwrite true

What does this command prove?

The command uploads the existing sales.csv batch to raw/sales.csv in the sales file system.
The --auth-mode login option uses your Microsoft Entra sign-in for the storage operation.
The --overwrite true option replaces the watched file. That replacement raises the event used by the published trigger.

Cloud Shell should confirm that the file upload completed.

Switch back to Synapse Studio.
Select the Monitor area.
Open the newest run for pl_sales_curate.

You should see a new Succeeded run that started without selecting Debug. This proves that replacing sales/raw/sales.csv launched the pipeline automatically.

The full event-driven path now works. A storage replacement starts validation before publishing five query-ready sales and preserving the malformed row.

Automatic run not appearing?

Confirm the storage-events trigger was published.
Check that Blob path begins with contains raw/.
Check that Blob path ends with contains sales.csv.
Confirm the upload command used --overwrite true.

Help me trace the missing automatic run.

Return to df_sales_curate after capturing the successful run.
Turn off Data flow debug so the debug cluster is not left running.

Your accepted data is partitioned and queryable. Your rejected evidence is preserved while the published trigger keeps the pipeline event-driven.

Secret mission

Reject Duplicate Sale IDs

Add a uniqueness rule for sale IDs. Prove duplicate sales are quarantined before they reach the curated analytics data.

Clean Up Your Resources

Choose whether to keep these resources available, pause the active pieces, or delete the project. Your Azure resources can continue consuming student credit through stored data, pipeline runs, data flow compute, and SQL queries.

Cost warning

Azure Data Lake Storage Gen2 continues consuming credit while your files remain stored. Replacing sales/raw/sales.csv starts another pipeline run through Azure Event Grid.

Mapping Data Flow debug compute can consume credit while it is running. Serverless SQL queries also consume credit based on the data processed.

Your $5 Microsoft Cost Management budget sends alerts at its thresholds. It cannot stop resources or spending.

Resources you used:

Project resource group <resource-group-name>.
Azure Data Lake Storage Gen2 account <storage-account-name> with private workspace and sales file systems.
Raw data in sales/raw, accepted Parquet data in sales/curated, and rejected-row evidence in sales/rejected.
Azure Synapse Analytics workspace <synapse-workspace-name> with its built-in serverless SQL pool.
Pipeline pl_sales_curate, mapping data flow df_sales_curate, datasets, linked services, SQL scripts, and the published Blob created storage-events trigger.
Storage Blob Data Contributor assignments for your Microsoft Entra identity and the workspace managed identity.
A $5 monthly Microsoft Cost Management budget with an email alert.

Keep everything running

No resource deletion is needed. Choose this if you still want to demonstrate the event-driven pipeline.

Return to df_sales_curate in Synapse Studio.
Turn off Data flow debug.
Keep the published storage-events trigger enabled only while you want file replacements to launch pipeline runs.
Avoid replacing sales/raw/sales.csv when you do not want another credit-consuming run.
Retain <resource-group-name> for future pipeline demonstrations and SQL queries.

Your stored data remains billable while the resource group exists. The $5 budget continues monitoring the selected cost scope.

Pause - I'll come back to this later

Shut down active compute and automation while preserving the project for later. Stored data continues consuming credit.

Return to df_sales_curate in Synapse Studio.
Turn off Data flow debug.
Return to pl_sales_curate in the Integrate area.
Stop the published Blob created storage-events trigger.
Confirm that the trigger is no longer active.

The workspace remains available for later SQL queries and manual pipeline runs. Replacing sales/raw/sales.csv no longer starts an automatic run while the trigger is stopped.

Files in your ephemeral Azure Cloud Shell session disappear when that session ends or restarts. Your cloud resources remain intact because they are stored in the project resource group.

Delete - I don't want to use this again

Remove the complete project when you no longer need the working demonstration. This stops the project's storage, pipeline, data flow, trigger, and query resources from consuming more credit.

Deletion removes the demo

Deleting the resource group removes the Synapse workspace, storage account, file systems, pipeline resources, trigger, stored data, and scoped role assignments. The removal is permanent for these project resources.

Save your evidence first:

Save the project screenshots you want to keep to your Windows device.
Save copies of generate_sales.py, query_sales.sql, and check_duplicates.sql to your Windows device.

Delete the project resource group:

Return to the Azure portal search.
Find the resource group <resource-group-name>.
Open <resource-group-name> from the search results.
Delete <resource-group-name> from its resource group page.
Complete Azure's confirmation prompt for the resource-group deletion.

When Azure finishes, <resource-group-name> no longer appears in your resource group list. This confirms that <storage-account-name> and <synapse-workspace-name> have been removed with the project.

Delete the separate budget alert:

Return to Microsoft Cost Management in the Azure portal.
Open the budget list for the scope used when you created the project budget.
Delete the $5 monthly budget associated with <resource-group-name>.

The budget list should no longer include the project's $5 alert. Your Azure for Students subscription remains available for future projects.

Nice Work!

Excellent work! You've built a monitored sales data lake that reacts to new files and turns them into trusted analytics data.

You've learned how to:

Build a monitored event-driven sales pipeline on Azure Data Lake Storage Gen2. Use Azure Synapse Analytics with Azure Event Grid to process replaced sales files automatically.
Apply schema casting and assertions to catch malformed sales rows. Preserve rejected-row evidence while writing accepted records as date-partitioned Parquet.
Query curated sales data with serverless SQL to calculate line revenue. Secure storage access with managed identity and Azure RBAC. Monitor pipeline runs while controlling spend with a cost budget.
(Secret Mission) Add a uniqueness assertion for sale_id. Prove that duplicate sale IDs stay out of curated Parquet.

Ready to quiz yourself?