Investigate Data Quality on Databricks

Find and fix data quality issues in Azure Databricks using SQL and pipelines.

Introduction

30 Second Summary

Every report depends on the data behind it. When that data has missing values, duplicates, or inconsistent formats, the numbers come out wrong and nobody trusts the results.

In this project, you will set up an Azure Databricks workspace, load a dataset with hidden quality problems, investigate the issues using SQL, and build a quality-enforced view that automatically rejects bad rows.

What You'll Build

Picture this: you run a revenue query, get the right number on the first try, and know for certain that every bad row was caught and dropped before it could corrupt the result.

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

  • A data quality investigation notebook that queries a messy dataset and immediately surfaces rows with missing values, duplicates, and type inconsistencies.
  • A set of systematic SQL checks that identify exactly which rows are problematic and why, producing a single quality summary report.
  • A materialized view powered by Databricks expectations that automatically drops bad rows, so downstream queries always return correct results.
  • Secret Mission: Schedule an automated job that re-runs your quality checks on a daily basis.

Are there any prerequisites?

You need an Azure account with a pay-as-you-go subscription. The project uses the Trial (Premium) tier, which gives you free Premium DBUs for 14 days.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your career as a data engineer.

Set Up Your Azure Databricks Workspace

Before you can investigate any data quality issues, you need a workspace to run queries in. Azure Databricks provides a cloud-based environment where you can write SQL, build notebooks, and create data pipelines.

In this step, you will create your Azure Databricks workspace from scratch and confirm it is ready to use. This workspace will be your home base for the entire project.

In this step, get ready to:
  • Create an Azure Databricks workspace in the Azure Portal.
  • Launch the workspace in your browser.
  • Confirm that Unity Catalog is enabled and ready.
Create the workspace in Azure Portal

The Azure Portal is your control panel for all Azure resources. You will create the Databricks workspace here, then launch it as a separate environment in your browser.

  • Open your browser and go to portal.azure.com.
  • Sign in with the Microsoft account attached to your pay-as-you-go subscription.
  • Type Azure Databricks in the search bar at the top of the page.
  • Select Azure Databricks from the search results.
  • Click + Create.
  • For Subscription, select your pay-as-you-go subscription from the dropdown.
  • For Resource group, click Create new and enter databricks-learning-rg.
  • For Workspace name, enter databricks-learning-ws.

What is a resource group?

A resource group is a container that holds related Azure resources together. Think of it as a folder for your cloud services. Putting everything for this project in one resource group makes it easy to find, manage, and delete later.

  • For Region, select the region closest to your physical location.
  • For Pricing Tier, select Trial (Premium - 14-Days Free DBUs).
  • Click Review + create.
  • Click Create.

Why the Trial tier?

The Trial (Premium - 14-Days Free DBUs) tier gives you full access to all Premium features at no cost for 14 days. This includes Unity Catalog, SQL warehouses, and Lakeflow pipelines. You have plenty of time to complete this project and the next one within the free window.

The deployment takes 2-3 minutes. You can watch the progress in the Notifications bell icon at the top of the Azure Portal.

Deployment failed?

If the deployment fails, check that your subscription is a pay-as-you-go subscription (not a Free Trial Subscription). Azure Databricks requires a pay-as-you-go subscription. Also confirm the region you selected supports Azure Databricks by trying a different region.

help me fix my Azure Databricks deployment failure

Launch the workspace

Once the deployment completes, you will open the Databricks workspace itself. This is a separate web application that runs in your browser.

  • When the deployment notification says Deployment succeeded, click Go to resource.
  • On the workspace overview page, click Launch Workspace.

What do you think you will see when the workspace opens? Take a moment to predict before the page loads.

You are now inside your Azure Databricks workspace. The left sidebar shows options including New, Workspace, Catalog, and SQL Editor. This is where you will do all your data work.

Workspace not opening?

If clicking Launch Workspace does nothing, check whether your browser is blocking pop-ups for this site. Allow pop-ups and try again. If you see a blank page, wait 30 seconds and refresh.

help me open my Azure Databricks workspace

Verify Unity Catalog is enabled

Unity Catalog is the governance layer that organizes all your data into catalogs, schemas, and tables. New workspaces automatically get Unity Catalog enabled, but you should confirm it is attached before moving on.

  • Click Catalog in the left sidebar.
  • Look for a workspace catalog already listed (it is named after your workspace).

You should see a catalog entry and a default schema inside it. This confirms Unity Catalog is attached and your tables will have a home.

No catalog visible?

If the Catalog page is empty or shows an error about no metastore, your workspace may not have been auto-provisioned with Unity Catalog. This can happen if your Azure region does not have an automatic metastore. Wait a few minutes and refresh the page. If it still does not appear, check the Databricks documentation for manual metastore assignment in your region.

help me enable Unity Catalog on my Azure Databricks workspace

Your workspace is ready. Next up, you will load a dataset with hidden quality problems and run your first query to see the data.

Load Sample Data and Run Your First Query

Your Azure Databricks workspace is up and running. Now it's time to get data into it.

Before you can investigate data quality issues, you need a dataset to work with. In this step, you'll create a notebook, load a realistic orders dataset, and run your first query to see the raw data.

In this step, get ready to:
  • Create a Databricks notebook for your investigation.
  • Load a sample orders table with hidden quality issues.
  • Run your first SQL query to view the raw data.
Create a notebook

A notebook in Databricks is an interactive document where you write and run code one cell at a time. You'll use this notebook for the rest of the project.

  • In the left sidebar of your Databricks workspace, click + New.
  • Select Notebook from the menu.
  • A blank notebook opens. Click the notebook name at the top (it will say something like Untitled Notebook) and rename it to data_quality_investigation.
  • Leave the default language as Python. You'll use both Python and SQL cells in this notebook.

What about compute?

In Unity Catalog-enabled workspaces, new notebooks automatically connect to serverless compute when you run your first cell. You do not need to create or attach a cluster manually.

You should see an empty notebook with a single blank cell, ready for code.

Create the sample orders table with intentional quality issues

The dataset you're loading simulates a real-world orders table. It has intentional quality issues hidden inside it that you won't spot just by looking at the data.

  • In the first cell of your notebook, paste the following code to define the table structure:
spark.sql("""
CREATE OR REPLACE TABLE orders_raw (
    order_id STRING,
    customer_name STRING,
    order_date STRING,
    product STRING,
    quantity STRING,
    unit_price DOUBLE,
    status STRING
)
""")

What does this code do?

This creates a table called orders_raw in your workspace catalog's default schema. The columns use STRING types for most fields. That's intentional.

In real-world datasets, upstream systems often export numbers as text. Storing quantity as a STRING simulates this common issue, which hides type problems until you try to aggregate the data.

  • Directly below the code you just pasted (in the same cell), add the following INSERT statement to load 20 rows of order data:
spark.sql("""
INSERT INTO orders_raw VALUES
    ('ORD001', 'Alice Chen', '2024-03-15', 'Laptop', '2', 999.99, 'completed'),
    ('ORD002', 'Bob Smith', '2024-03-16', 'Mouse', '5', 24.99, 'completed'),
    ('ORD003', 'Alice Chen', '2024-03-15', 'Laptop', '2', 999.99, 'completed'),
    ('ORD004', NULL, '2024-03-17', 'Keyboard', '1', 79.99, 'pending'),
    ('ORD005', 'Diana Ross', '2024-03-18', 'Monitor', 'three', 349.99, 'completed'),
    ('ORD006', 'Eve Wilson', '2024/03/19', 'Headphones', '1', 149.99, 'shipped'),
    ('ORD007', 'Frank Lee', '2024-03-20', 'Webcam', '2', 89.99, 'completed'),
    ('ORD008', 'Grace Kim', '2024-03-21', NULL, '1', 59.99, 'pending'),
    ('ORD009', 'Henry Park', '2024-03-22', 'Cable', '10', 9.99, 'completed'),
    ('ORD010', 'Ivy Zhang', '2024-03-23', 'Laptop', '1', -999.99, 'completed'),
    ('ORD011', 'Jack Brown', '2024-03-24', 'Mouse', '0', 24.99, 'completed'),
    ('ORD012', 'Kate Davis', 'March 25 2024', 'Keyboard', '2', 79.99, 'shipped'),
    ('ORD013', 'Leo Garcia', '2024-03-26', 'Monitor', '1', 349.99, 'COMPLETED'),
    ('ORD014', NULL, '2024-03-27', 'Headphones', '3', 149.99, 'pending'),
    ('ORD015', 'Nina Patel', '2024-03-28', 'Webcam', '1', 89.99, 'completed'),
    ('ORD016', 'Oscar Liu', '2024-03-29', 'Cable', '5', 9.99, 'completed'),
    ('ORD017', 'Paula White', NULL, 'Laptop', '1', 999.99, 'pending'),
    ('ORD018', 'Quinn Adams', '2024-03-31', 'Mouse', '2', 24.99, 'cancelled'),
    ('ORD019', 'Rosa Martinez', '2024-04-01', 'Keyboard', '1', 79.99, 'completed'),
    ('ORD020', 'Sam Taylor', '2024-04-02', 'Monitor', '1', 349.99, 'shipped')
""")

What does this data contain?

These 20 rows look like a normal orders dataset at first glance. But several rows contain hidden quality issues: NULL values in critical columns, a duplicate order, non-numeric quantities, negative prices, and inconsistent date formats.

You'll discover each of these problems systematically in the next step.

  • Click inside the cell and press Shift+Enter to run it.

First run may take a moment

The first time you run a cell, Databricks connects to serverless compute. This may take 10-20 seconds. Subsequent cells run much faster.

Once the cell finishes, you should see a small status message below the cell confirming the command completed successfully.

Cell not running?

If you see a message about compute not being available, click the compute drop-down at the top of the notebook and select Serverless. Then try running the cell again.

If you see a permissions error, confirm your workspace is using the Trial (Premium) pricing tier from Step 1.

Help me troubleshoot this issue

Query the data to see what you are working with

The table is created and loaded. Time to look at it.

  • Click the + button below the first cell to add a new cell.
  • Type %sql at the top of the new cell to switch it to SQL mode.
  • Below %sql, paste the following query:
%sql
SELECT * FROM orders_raw LIMIT 20

What does this query do?

The %sql magic command tells Databricks to interpret this cell as SQL instead of the default Python. The query retrieves all columns from orders_raw and limits the output to 20 rows.

Before you run this, what do you expect to see? Think about how many rows you inserted and what columns should appear.

  • Press Shift+Enter to run the cell.

You should see a table of all 20 order rows displayed inline below the cell. Each row shows the order ID, customer name, date, product, quantity, price, and status.

Not seeing results?

Make sure you ran Cell 1 (the Python cell) before this SQL cell. The table must exist before you can query it.

Check that you typed %sql on its own line at the very top of the cell. If it's on the same line as the SELECT statement, it won't work.

Help me fix my SQL query

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved. Databricks auto-saves, but you can confirm by checking that the notebook title shows no unsaved indicator.

ⓧ I'd like to double check the full code

Your notebook should have exactly two cells at this point:

  • Cell 1 (Python): Contains the CREATE OR REPLACE TABLE statement followed by the INSERT INTO orders_raw VALUES statement with all 20 rows.
  • Cell 2 (SQL): Contains %sql on line 1, followed by SELECT * FROM orders_raw LIMIT 20 on line 2.

Scroll up through your notebook and confirm both cells are present and have been run successfully (green checkmarks or completed status).

The data is loaded and you can see it. At first glance, it looks like a normal orders table. But those downstream reports are getting wrong numbers for a reason. In the next step, you'll try to calculate revenue and discover exactly why this data is broken.

Investigate Data Quality Issues

In the last step, you loaded 20 order rows into your notebook and confirmed they all appeared in the table.

Now imagine your manager asks: what was last month's total revenue from completed orders? Sounds like a simple calculation. But when you try it, the numbers come back wrong. Something is broken in the data, and your job is to find out what.

In this step, get ready to:
  • Attempt a revenue calculation and watch it fail.
  • Run systematic SQL checks to identify six types of data quality issues.
  • Produce a single quality summary report that counts every problem in the dataset.
Try to calculate total revenue

The most natural first question for an orders dataset is total revenue. This query multiplies each order's quantity by its unit price and sums the results for completed orders.

Before you run it, do you expect this query to succeed on 20 rows of order data? Think about what could go wrong with a column stored as STRING being cast to a number.

  • Add a new cell below Cell 2 by clicking the + button at the bottom of the cell.
  • Type %sql at the top of the cell to switch it to SQL mode.
  • Paste the following revenue calculation and press Shift+Enter to run it:
%sql
SELECT
    SUM(CAST(quantity AS INT) * unit_price) AS total_revenue,
    COUNT(*) AS total_orders
FROM orders_raw
WHERE status = 'completed'

You see an error. The value 'three' in the quantity column cannot be cast to an integer, so the entire query fails. This is the pain of dirty data.

Why did this fail?

The quantity column is stored as STRING. This is a common real-world scenario where upstream systems export numbers as text. Most values like '2' or '5' cast to INT fine, but row ORD005 contains the word 'three' instead of the digit '3'.

One bad row breaks an entire report. Your next job is to find every issue hiding in this dataset.

Not seeing an error?

If your workspace has ANSI mode disabled, the query may return a result instead of erroring. The result will still be wrong because the 'three' row gets cast to NULL and the negative price in ORD010 pulls the total down. Either way, the revenue number is unreliable.

Help me understand why my revenue query returned a result instead of an error.

Run systematic quality checks

Now you know the data is broken. A data engineer does not guess at what is wrong. You run a series of targeted SQL checks, each designed to surface a specific category of issue.

  • Add a new SQL cell (Cell 4) and run this query to find NULL values in critical columns:
%sql
-- Check 1: Find NULL values in critical columns
SELECT
    SUM(CASE WHEN customer_name IS NULL THEN 1 ELSE 0 END) AS null_customers,
    SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_dates,
    SUM(CASE WHEN product IS NULL THEN 1 ELSE 0 END) AS null_products
FROM orders_raw

You should see: 2 null customers, 1 null date, and 1 null product.

What does this query do?

The CASE WHEN ... IS NULL pattern counts how many rows have missing values in each column. This is the first check any data engineer runs because NULLs silently drop rows from aggregations.

  • Add a new SQL cell (Cell 5) and run this query to find duplicate orders:
%sql
-- Check 2: Find duplicate orders (same customer, date, product, quantity)
SELECT customer_name, order_date, product, quantity, COUNT(*) as duplicate_count
FROM orders_raw
GROUP BY customer_name, order_date, product, quantity
HAVING COUNT(*) > 1

You should see one row showing Alice Chen's laptop order appears twice. ORD001 and ORD003 are duplicates.

What does this query do?

Grouping by multiple columns and filtering with HAVING COUNT(*) > 1 surfaces any rows that share identical values across all key fields. Duplicates inflate revenue counts and skew reports.

  • Add a new SQL cell (Cell 6) and run this query to find non-numeric quantities:
%sql
-- Check 3: Find non-numeric quantities
SELECT order_id, customer_name, product, quantity
FROM orders_raw
WHERE TRY_CAST(quantity AS INT) IS NULL AND quantity IS NOT NULL

You should see ORD005 with the quantity value 'three'. This is the row that broke your revenue query.

Why TRY_CAST instead of CAST?

TRY_CAST returns NULL when a value cannot be converted, instead of throwing an error. By checking where TRY_CAST is NULL but the original value is NOT NULL, you find rows where the data looks filled in but is actually the wrong type.

  • Add a new SQL cell (Cell 7) and run this query to find invalid prices:
%sql
-- Check 4: Find invalid prices (negative or zero)
SELECT order_id, customer_name, product, unit_price
FROM orders_raw
WHERE unit_price <= 0

You should see ORD010 with a price of -999.99. A negative price makes no business sense and would corrupt any revenue total.

  • Add a new SQL cell (Cell 8) and run this query to find inconsistent date formats:
%sql
-- Check 5: Find inconsistent date formats
SELECT order_id, order_date
FROM orders_raw
WHERE TRY_CAST(order_date AS DATE) IS NULL AND order_date IS NOT NULL

You should see ORD006 ('2024/03/19' uses slashes) and ORD012 ('March 25 2024' uses a written month). Both are valid dates to a human but not parseable by the default date format.

  • Add a new SQL cell (Cell 9) and run this query to find inconsistent status values:
%sql
-- Check 6: Find inconsistent status values
SELECT status, COUNT(*) as count
FROM orders_raw
GROUP BY status
ORDER BY count DESC

You should see both 'completed' and 'COMPLETED' listed as separate values. Case inconsistency means your earlier WHERE status = 'completed' filter missed ORD013 entirely.

Why does case matter?

SQL string comparisons are case-sensitive by default. 'COMPLETED' and 'completed' are treated as different values. One row slipping through with uppercase silently drops it from filtered aggregations.

Seeing a syntax error on any check?

Make sure each cell starts with %sql on its own line. The notebook defaults to Python, so without that magic command the cell tries to run your SQL as Python code.

Also confirm the table name is exactly orders_raw with an underscore. A typo like ordersraw or orders-raw returns a table-not-found error.

Help me fix a syntax error in my Databricks SQL quality check cell.

Summarize your findings in a single report

You have identified six categories of issues across six separate queries. Now consolidate them into one summary so you have a single view of how many problems exist.

  • Add a new SQL cell (Cell 10) and run this full quality summary report:
%sql
SELECT
    COUNT(*) as total_rows,
    SUM(CASE WHEN customer_name IS NULL THEN 1 ELSE 0 END) AS null_customer_names,
    SUM(CASE WHEN product IS NULL THEN 1 ELSE 0 END) AS null_products,
    SUM(CASE WHEN order_date IS NULL OR TRY_CAST(order_date AS DATE) IS NULL THEN 1 ELSE 0 END) AS invalid_dates,
    SUM(CASE WHEN TRY_CAST(quantity AS INT) IS NULL THEN 1 ELSE 0 END) AS non_numeric_quantities,
    SUM(CASE WHEN unit_price <= 0 THEN 1 ELSE 0 END) AS invalid_prices
FROM orders_raw

What does this summary tell you?

This single query counts every issue type across all 20 rows. You should see: total_rows = 20, null_customer_names = 2, null_products = 1, invalid_dates = 3, non_numeric_quantities = 1, invalid_prices = 1.

The invalid_dates count is 3 because it includes the 1 NULL date (ORD017) plus the 2 format mismatches (ORD006 and ORD012). Both conditions make the date unusable for date-based analysis.

You now have a complete picture of every quality problem in this dataset. Multiple rows would silently corrupt any report built on this raw data.

✔️ Awesome, I've got everything!

Great. Confirm you have 10 cells total in your notebook (Cells 1 and 2 from the previous step, plus the 8 new cells you just added).

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm each cell is present in this order:

  • Cell 1 (Python): Creates and populates orders_raw table.
  • Cell 2 (%sql): SELECT * FROM orders_raw LIMIT 20.
  • Cell 3 (%sql): Revenue calculation with CAST(quantity AS INT).
  • Cell 4 (%sql): NULL check on customer_name, order_date, product.
  • Cell 5 (%sql): Duplicate check with GROUP BY and HAVING COUNT(*) > 1.
  • Cell 6 (%sql): Non-numeric quantities using TRY_CAST.
  • Cell 7 (%sql): Invalid prices with unit_price <= 0.
  • Cell 8 (%sql): Invalid date formats using TRY_CAST(order_date AS DATE).
  • Cell 9 (%sql): Inconsistent status values with GROUP BY status.
  • Cell 10 (%sql): Full quality summary combining all checks into one output.

You have diagnosed every quality problem hiding in this dataset. Next up, you will build a pipeline that automatically rejects bad rows so downstream reports always get clean data.

Enforce Data Quality with Expectations

You now have a complete map of every quality issue hiding in your data. But finding problems is only half the job.

A data engineer needs to prevent bad data from reaching downstream reports. In this step, you will build a Lakeflow pipeline with expectations that automatically reject bad rows. The result is a clean materialized view that downstream queries can always trust.

In this step, get ready to:
  • Create a Lakeflow ETL pipeline with data quality expectations.
  • Run the pipeline and observe which rows get dropped.
  • Verify the clean data produces correct revenue calculations.
Create a Lakeflow pipeline with expectations

Databricks expectations are quality rules you attach directly to a pipeline. Each rule defines a condition every row must pass. Rows that violate a rule are automatically dropped before they reach the output table.

  • Click New in the left sidebar of your Databricks workspace.
  • Select ETL Pipeline.

The pipeline editor opens with a default pipeline name and a blank my_transformation file.

  • Click the default pipeline name at the top and rename it to orders_quality_pipeline.
  • In the my_transformation file, select SQL from the language dropdown.

What is my catalog name?

The SQL code below references workspace_catalog.default.orders_raw. You need to replace workspace_catalog with your actual workspace catalog name.

To find it, click Catalog in the left sidebar. The catalog named after your workspace (e.g. databricks_learning_ws) is the one you need.

  • Replace the sample code in the my_transformation file with the following SQL:
CREATE OR REFRESH MATERIALIZED VIEW orders_clean (
    -- Drop rows where customer_name is missing
    CONSTRAINT valid_customer EXPECT (customer_name IS NOT NULL) ON VIOLATION DROP ROW,
    -- Drop rows where product is missing
    CONSTRAINT valid_product EXPECT (product IS NOT NULL) ON VIOLATION DROP ROW,
    -- Drop rows where order_date cannot be parsed as a DATE
    CONSTRAINT valid_date EXPECT (TRY_CAST(order_date AS DATE) IS NOT NULL) ON VIOLATION DROP ROW,
    -- Drop rows where quantity is not a positive integer
    CONSTRAINT valid_quantity EXPECT (TRY_CAST(quantity AS INT) IS NOT NULL AND CAST(quantity AS INT) > 0) ON VIOLATION DROP ROW,
    -- Drop rows where price is zero or negative
    CONSTRAINT valid_price EXPECT (unit_price > 0) ON VIOLATION DROP ROW
)
AS SELECT
    order_id,
    customer_name,
    TRY_CAST(order_date AS DATE) AS order_date,
    product,
    CAST(quantity AS INT) AS quantity,
    unit_price,
    LOWER(status) AS status
FROM [[WORKSPACE_CATALOG="your workspace catalog name"]].default.orders_raw

What does this code do?

This creates a materialized view called orders_clean with five quality constraints attached to it.

  • Each CONSTRAINT line defines a rule. If a row fails the EXPECT condition, the ON VIOLATION DROP ROW clause removes it from the output.
  • The SELECT statement also cleans up types: it casts order_date to DATE, quantity to INT, and lowercases status so values like COMPLETED become completed.

Why a pipeline instead of a notebook filter?

Expectations are a Lakeflow pipeline feature. They enforce rules automatically every time the pipeline runs, without anyone needing to remember to apply filters manually. This is the production-grade approach to data quality.

Run the pipeline

Before you run this, predict: will all 20 rows make it into orders_clean, or will some be dropped?

  • Click Run pipeline in the upper right of the pipeline editor.
  • Watch the pipeline graph update as it processes.
  • When the run completes, click on the orders_clean table in the pipeline graph to see the results.

You should see fewer than 20 rows in the output. The pipeline dropped every row that violated at least one constraint.

Which rows were dropped?

  • ORD004 and ORD014: NULL customer names.
  • ORD008: NULL product.
  • ORD017: NULL order date.
  • ORD005: quantity is the word 'three' (fails TRY_CAST to INT).
  • ORD011: quantity is 0 (fails the > 0 check).
  • ORD010: negative price of -999.99.
  • ORD006 and ORD012: date formats that TRY_CAST cannot parse as DATE.

Pipeline failed or showed errors?

  • Check that you replaced workspace_catalog with your actual catalog name from the Catalog sidebar.
  • Confirm the table name is orders_raw under the default schema. You can verify this in Catalog by expanding your workspace catalog.
  • Make sure you selected SQL (not Python) from the language dropdown in the transformation file.

help me fix my pipeline error

Verify the clean data works

Remember the broken revenue calculation from Step 3? Time to see if your clean data fixes it.

  • Switch back to your data_quality_investigation notebook.
  • Add a new cell below Cell 10 by clicking the + button.
  • Run the following query to calculate revenue from the clean data:
%sql
SELECT
    SUM(quantity * unit_price) AS total_revenue,
    COUNT(*) AS clean_order_count
FROM orders_clean
WHERE status = 'completed'

What does this query do?

This is the same revenue calculation that failed in Step 3. The difference is that orders_clean only contains rows where quantity is already a valid integer and unit_price is positive. No more type errors or negative prices corrupting the result.

This time the query succeeds and returns a correct revenue number along with the count of clean completed orders.

Table not found error?

  • Confirm your pipeline ran successfully by checking the pipeline graph shows orders_clean in green.
  • If you see a schema error, the materialized view may have been created in a different catalog or schema. Check your pipeline's default catalog and schema settings match where orders_raw lives.

help me fix my orders_clean table not found error

✔️ Awesome, I've got everything!

Great. Double check you saved your notebook with Cell 11 included.

ⓧ I'd like to double check the full code

Your my_transformation.sql file in the pipeline should contain:

CREATE OR REFRESH MATERIALIZED VIEW orders_clean (
    CONSTRAINT valid_customer EXPECT (customer_name IS NOT NULL) ON VIOLATION DROP ROW,
    CONSTRAINT valid_product EXPECT (product IS NOT NULL) ON VIOLATION DROP ROW,
    CONSTRAINT valid_date EXPECT (TRY_CAST(order_date AS DATE) IS NOT NULL) ON VIOLATION DROP ROW,
    CONSTRAINT valid_quantity EXPECT (TRY_CAST(quantity AS INT) IS NOT NULL AND CAST(quantity AS INT) > 0) ON VIOLATION DROP ROW,
    CONSTRAINT valid_price EXPECT (unit_price > 0) ON VIOLATION DROP ROW
)
AS SELECT
    order_id,
    customer_name,
    TRY_CAST(order_date AS DATE) AS order_date,
    product,
    CAST(quantity AS INT) AS quantity,
    unit_price,
    LOWER(status) AS status
FROM workspace_catalog.default.orders_raw

Your notebook Cell 11 should contain:

%sql
SELECT
    SUM(quantity * unit_price) AS total_revenue,
    COUNT(*) AS clean_order_count
FROM orders_clean
WHERE status = 'completed'

Your data quality rules are now enforced automatically. Every time the pipeline runs, only clean rows make it through. Next up, you will build a quality report that shows stakeholders the before-and-after impact of your work.

Build a Quality Report Dashboard

Your pipeline is now enforcing quality rules and automatically rejecting bad rows. The next challenge is proving to stakeholders that the fixes actually work.

In a real job, you need to show a clear before-and-after comparison. In this step, you will build a query that contrasts the raw data against the clean view, visualize it as a chart, and document your findings.

In this step, get ready to:
  • Create a comparison query showing raw vs. clean data side by side.
  • Visualize the comparison as a bar chart.
  • Document your investigation findings in a Markdown summary.
Create a before-and-after comparison

You have the raw data with 20 rows and the clean materialized view with fewer rows. A single query using UNION ALL can place both results side by side so you can compare them at a glance.

  • Switch back to your data_quality_investigation notebook.
  • Add a new cell below Cell 11 by clicking the + button.
  • Change the cell language to SQL by typing %sql at the top of the cell.

Before you run this query, think about what you expect. The raw table has 20 rows. How many rows do you think survived the quality filters in the clean view?

  • Paste the following comparison query into your new cell:
%sql
SELECT
    'Raw (Before)' AS dataset,
    COUNT(*) AS row_count,
    SUM(CASE WHEN customer_name IS NULL OR product IS NULL THEN 1 ELSE 0 END) AS null_issues,
    SUM(CASE WHEN TRY_CAST(quantity AS INT) IS NULL OR CAST(quantity AS INT) <= 0 THEN 1 ELSE 0 END) AS quantity_issues,
    SUM(CASE WHEN unit_price <= 0 THEN 1 ELSE 0 END) AS price_issues
FROM orders_raw

UNION ALL

SELECT
    'Clean (After)' AS dataset,
    COUNT(*) AS row_count,
    SUM(CASE WHEN customer_name IS NULL OR product IS NULL THEN 1 ELSE 0 END) AS null_issues,
    SUM(CASE WHEN quantity IS NULL OR quantity <= 0 THEN 1 ELSE 0 END) AS quantity_issues,
    SUM(CASE WHEN unit_price <= 0 THEN 1 ELSE 0 END) AS price_issues
FROM orders_clean
  • Run the cell by pressing Shift+Enter.

You should see a two-row table. The Raw (Before) row shows 20 total rows with non-zero issue counts. The Clean (After) row shows fewer rows and zero for every issue column.

What does this query do?

The first SELECT counts issues in the raw data. The second SELECT counts issues in the clean view. UNION ALL stacks them into a single result so you can compare them in one table.

Because the pipeline dropped all bad rows, the clean view should show zero for every issue category. That proves your expectations are working.

Seeing an error about orders_clean?

If the query fails with a table-not-found error, your pipeline may not have completed successfully. Return to the orders_quality_pipeline and confirm it ran without errors.

Also check that your catalog name matches. Run SHOW TABLES to confirm orders_clean appears in your default schema.

Still stuck? Why can't my notebook find the orders_clean table?

Visualize the results

A table with two rows is clear enough for you, but a chart makes the difference obvious at a glance for anyone you present to.

  • In the Cell 12 results area, click the + next to the Table tab.
  • Click Visualization to open the visualization editor.
  • In the Visualization Type drop-down, select Bar.
  • Set X column to dataset.
  • Set Y column to row_count.
  • Click Save.

You now see a bar chart with two bars. The Raw (Before) bar is taller (20 rows) and the Clean (After) bar is shorter, showing exactly how many rows your quality rules removed.

Try other Y columns

You can change the Y column to null_issues, quantity_issues, or price_issues to see bar charts that highlight specific issue categories dropping to zero in the clean view.

Don't see the + button next to Table?

Make sure Cell 12 has been run and shows query results. The visualization option only appears after a cell produces output.

If you see only a legacy chart icon, click it and look for the bar chart option in the toolbar. Help me create a bar chart visualization in my Databricks notebook

Document what you found

A good investigation notebook ends with a human-readable summary. This is what you would send to a manager or paste into a ticket to explain what you found and fixed.

  • Add a new cell below Cell 12.
  • Change the cell type to Markdown using the language selector at the top of the cell.
  • Write a short summary that includes:
  • Run the Markdown cell to render your formatted text.

Why write this summary?

In a data engineering role, your notebook is your deliverable. Stakeholders read the summary cell to understand what happened without scrolling through every query. It also helps future-you remember what the investigation found when you revisit the notebook months later.

Your notebook now has 13 cells: the original data creation, six quality checks, a summary report, the clean data verification, a before-and-after comparison with a chart, and your written summary.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook. You should have 13 cells total with the bar chart visualization visible on Cell 12.

ⓧ I'd like to double check the full code

Scroll through your data_quality_investigation notebook and confirm these cells exist in order:

  • Cell 1 (Python): Creates and populates orders_raw table.
  • Cell 2 (SQL): SELECT * FROM orders_raw LIMIT 20.
  • Cell 3 (SQL): Revenue calculation attempt with CAST(quantity AS INT).
  • Cell 4 (SQL): NULL value check.
  • Cell 5 (SQL): Duplicate check with HAVING COUNT(*) > 1.
  • Cell 6 (SQL): Non-numeric quantity check using TRY_CAST.
  • Cell 7 (SQL): Invalid price check (unit_price <= 0).
  • Cell 8 (SQL): Invalid date format check.
  • Cell 9 (SQL): Status inconsistency check.
  • Cell 10 (SQL): Full quality summary report.
  • Cell 11 (SQL): Clean revenue verification query against orders_clean.
  • Cell 12 (SQL): UNION ALL before-and-after comparison with bar chart visualization.
  • Cell 13 (Markdown): Summary of findings, rules applied, rows dropped, and intended use.

Secret mission

Schedule Automated Quality Checks

Data arrives continuously. A one-time investigation is not enough. Real data engineers automate their quality checks to run daily so new issues are caught immediately. Can you schedule your pipeline to run automatically?

Clean Up Your Resources

Clean Up Your Resources

This project runs on the Azure Databricks Trial tier, which provides free Premium DBUs for 14 days. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

After 14 days, the Trial tier expires. If you do not change or delete the workspace, you will be billed for any compute usage (DBUs plus VM cost). Serverless compute is billed per second only when actively running.

If you are past the 14-day trial window, choose Pause or Delete below to avoid unexpected charges.

Resources you used:

  • Azure Databricks workspace (databricks-learning-ws) in resource group databricks-learning-rg.
  • Scheduled job daily_quality_check (runs orders_quality_pipeline daily at 06:00 AM).
  • Notebook data_quality_investigation, pipeline orders_quality_pipeline, tables orders_raw and orders_clean (all inside the workspace).

Keep everything running

No action needed. Choose this if you are still actively building or plan to do another Databricks project within the 14-day trial window.

  • Your workspace, notebook, pipeline, tables, and scheduled job all remain intact.
  • No charges apply while you are within the trial period and no compute is actively running.
  • The daily_quality_check job will trigger daily at 06:00 AM, consuming DBUs each time it runs. This is covered by the trial.

Pause - I'll come back to this later

Stop running processes to avoid consuming DBUs while you are away. Your workspace, notebooks, and tables stay intact.

  • In your Databricks workspace, click Workflows in the left sidebar.
  • Click on the daily_quality_check job to open it.
  • In the Job details pane, scroll to Schedules & Triggers and click Pause. This stops future scheduled runs while keeping your job configuration intact.
  • Confirm no SQL warehouses or clusters are running by clicking Compute in the left sidebar. Terminate any active clusters.
  • The workspace itself does not cost money when idle. Only active compute usage generates charges.

Delete - I don't want to use this again

Remove all project resources permanently. This deletes the workspace, notebook, pipeline, tables, and scheduled job.

  • Sign in to the Azure Portal at portal.azure.com.
  • Click Resource groups in the left menu.
  • Find and select databricks-learning-rg.
  • Click Delete resource group in the top menu.
  • Type databricks-learning-rg to confirm, then click Delete.
  • Wait for the deletion to complete. This removes the workspace and all resources inside it.

Nice Work!

Nice Work!

You just went from a blank Azure subscription to a fully working data quality system. You diagnosed a broken dataset, built automated quality enforcement, and proved the fix with a before-and-after report.

You've learned how to:

  • Set up an Azure Databricks workspace from scratch and load a dataset with realistic quality issues into Unity Catalog.
  • Investigate and identify 6 types of data quality problems (NULLs, duplicates, type mismatches, invalid values, inconsistent formats, and case inconsistencies) using systematic SQL checks.
  • Build a Lakeflow pipeline with expectations that automatically reject bad rows, producing a clean materialized view that downstream queries can trust.
  • Secret Mission: Scheduled the quality pipeline to run automatically every day so new data issues are caught without manual effort.

Ready to quiz yourself?