Investigate Data Issues with Databricks SQL

Use SQL joins and query profiling to find and fix data discrepancies.

Introduction

30 Second Summary

Someone on the business team messages you: "the numbers don't add up." Tracing where data breaks down across multiple tables is one of the most common tasks in data engineering, and it is exactly what separates someone who can write a query from someone who can solve problems.

In this project, you will investigate a real business dataset in Databricks to find data discrepancies, diagnose a slow query, and build the same quality checks that data platform support engineers run daily.

What You'll Build

When you are done, you will run a single combined query and see a clean report showing which data quality rules pass and which catch real problems, ready to show anyone who asks how you would investigate a data issue.

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

  • A working data investigation workflow where you query across multiple related tables to trace exactly where discrepancies originate.
  • A before-and-after performance comparison showing how a single date filter dramatically reduces query execution time, with the Query Profile proving why.
  • A reusable data quality check that reports on missing records, duplicates, orphaned references, and calculation mismatches in one pass.
  • Secret Mission: Write advanced analytical queries using window functions to rank, compare, and detect data patterns.

Are there any prerequisites?

You need basic SQL knowledge (SELECT, WHERE, ORDER BY). No Databricks account is needed before you start. The project walks you through setting up Databricks Free Edition from scratch, which is forever free with no payment required.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

This step is your chance to commit to the goal: investigating data discrepancies, diagnosing slow queries, and building proactive data quality checks on a real data platform.

Set Up Databricks Free Edition

To investigate data issues, you first need a data platform to work on. Databricks Free Edition gives you a real SQL warehouse with pre-loaded business data at zero cost.

In this step, you'll create your Databricks workspace and confirm it can query data. This is the foundation for everything you'll build in this project.

In this step, get ready to:
  • Sign up for Databricks Free Edition and create your workspace.
  • Open the SQL Editor and confirm the SQL warehouse is running.
  • Run your first query against the pre-loaded sample data.
Sign up for Databricks Free Edition

Databricks Free Edition is forever free with daily usage limits. No credit card or payment method is required.

  • Open the Databricks Free Edition signup page in your browser:
  • databricks.com/learn/free-edition
  • Click Sign up for Free Edition.
  • Choose your preferred signup method: Continue with Google, Continue with Microsoft, or sign up with email.
  • Complete the authentication flow with your chosen provider.

Databricks creates your workspace automatically after signup. After a few moments, you'll land on the Databricks workspace home page.

What is Databricks Free Edition?

Free Edition is a no-cost, serverless workspace designed for learning and experimentation. It replaced the legacy Databricks Community Edition, which was retired in 2025.

Your workspace includes a SQL warehouse and pre-loaded sample datasets. If you exceed the daily usage quota, compute shuts down for the rest of the day, but your data and settings persist.

Seeing a 14-day Free Trial instead?

Some users accidentally sign up for the Free Trial instead of Free Edition. Make sure you clicked the Sign up for Free Edition button on the Free Edition page, not a trial link.

If you see references to credits or a 14-day period, you're on the wrong plan. Help me fix my Databricks signup so I get Free Edition instead of the Free Trial.

Open the SQL Editor and confirm your warehouse

The SQL Editor is where you'll write and run all your queries in this project. It connects to a SQL warehouse that processes your queries against the data.

  • In the left sidebar of your Databricks workspace, find the Lakehouse section.
  • Click SQL Editor.
  • Look at the compute dropdown near the top of the editor. You should see a SQL warehouse listed and available.

The SQL warehouse auto-starts when you open the editor. It may take a few seconds to show a Running status.

What is a SQL warehouse?

A SQL warehouse is the compute engine that processes your SQL queries. Free Edition gives you one warehouse limited to a 2X-Small cluster size.

This is more than enough for the queries in this project. The warehouse auto-suspends when idle, so it costs nothing while you're not using it.

  • Now confirm the sample data is available. In the catalog dropdown near the top of the editor, select samples.
  • In the schema dropdown next to it, select tpch.

You should see tables listed in the schema browser on the left pane, including orders, customer, and lineitem.

Don't see the samples catalog?

The samples catalog is pre-loaded in every Databricks workspace with Unity Catalog enabled. Free Edition workspaces have Unity Catalog enabled by default.

If you don't see it in the dropdown, try clicking the refresh icon near the top of the schema browser. Help me find the samples catalog in my Databricks workspace.

Run your first query to verify the connection

Time to confirm everything works end to end. You'll query the orders table and see real data come back.

  • Click in the query editor area (the large blank pane in the center).
  • Type the following query:
SELECT * FROM samples.tpch.orders LIMIT 10;
  • Before you run this, what do you expect to see? Think about what columns an "orders" table in a business dataset might have.
  • Run the query by pressing Cmd+Enter (macOS) or Ctrl+Enter (Windows).

You should see 10 rows of order data returned in the results pane below your query. The columns include o_orderkey, o_custkey, o_totalprice, and o_orderdate.

What does this query do?

This query uses the three-part naming convention from Unity Catalog: catalog.schema.table.

  • samples is the catalog (a collection of schemas).
  • tpch is the schema (a collection of tables based on the TPC-H benchmark, approximately 1 TB of business data).
  • orders is the table containing order records.
  • LIMIT 10 restricts the result to 10 rows so the query returns quickly.

Query failed or no results?

Make sure your SQL warehouse shows a Running status in the compute dropdown at the top of the editor. If it says Starting, wait a few seconds and try again.

Check that you typed the table name exactly as samples.tpch.orders with no typos. Help me troubleshoot why my SQL query isn't returning results in Databricks.

Your Databricks workspace is live and querying data. Next up, you'll explore the full business dataset to understand its structure and relationships before investigating where the data breaks down.

Explore the Business Dataset

Your workspace is connected and returning data. Now you need to understand the landscape before investigating problems.

When a support ticket arrives saying "the numbers don't add up," the first thing you do is map the data. What tables exist? How do they relate? How much data are you dealing with? This step builds that map.

In this step, get ready to:
  • Discover all available tables in the TPC-H schema and explore their column structures.
  • Measure the data scale by counting rows and finding the date range of orders.
  • Confirm how the tables connect by writing a JOIN query.
Discover the available tables

The samples.tpch schema contains pre-loaded business data organized using the Unity Catalog naming convention: catalog.schema.table.

  • In the SQL Editor, clear your previous query and run this command to see all tables in the schema:
-- See all available tables in the TPC-H schema
SHOW TABLES IN samples.tpch;

You should see a list of tables including orders, customer, lineitem, supplier, nation, region, part, and partsupp.

What are these tables?

TPC-H is a standard benchmarking dataset that models a wholesale supplier business. Orders, customers, and line items form the core transactional data. Supplier, nation, region, part, and partsupp provide the reference data around them.

  • Now explore the structure of the three key tables by running each DESCRIBE statement (highlight one statement at a time and press Ctrl/Cmd + Enter to run it):
-- Explore the orders table structure
DESCRIBE samples.tpch.orders;

-- Explore the customer table structure
DESCRIBE samples.tpch.customer;

-- Explore the lineitem table structure
DESCRIBE samples.tpch.lineitem;

Each DESCRIBE returns the column names, data types, and comments for that table. Notice the naming convention: columns are prefixed with o_ for orders, c_ for customer, and l_ for lineitem.

What do these prefixes tell you?

The prefixed column names (o_orderkey, c_custkey, l_orderkey) make it clear which table a column belongs to, even after a JOIN combines multiple tables. This is the TPC-H standard naming convention.

Not seeing results?

If you see an error, make sure you have the samples catalog selected in the catalog dropdown at the top of the editor. Also check that your SQL warehouse shows a green running status.

help me run DESCRIBE queries in the SQL Editor

Understand the data scale and shape

Knowing how many rows each table holds tells you what you're working with. A table with 1.5 million rows behaves differently from one with 150 rows.

  • Count the rows across the three key tables by running this query:
-- Count rows in key tables
SELECT 'orders' AS table_name, COUNT(*) AS row_count FROM samples.tpch.orders
UNION ALL
SELECT 'customer', COUNT(*) FROM samples.tpch.customer
UNION ALL
SELECT 'lineitem', COUNT(*) FROM samples.tpch.lineitem;

You should see three rows in the results pane, each showing a table name and its row count.

What does this query do?

UNION ALL stacks the results of three separate COUNT queries into a single result set. Each SELECT produces one row with the table name and its count. This is a common pattern for building summary reports from multiple tables.

  • Next, find the date range of orders to understand the time span of the data:
-- Find the date range of orders
SELECT
  MIN(o_orderdate) AS earliest_order,
  MAX(o_orderdate) AS latest_order,
  COUNT(*) AS total_orders
FROM samples.tpch.orders;

You should see a single row showing the earliest order date, the latest order date, and the total number of orders.

Why check the date range?

Knowing the date boundaries helps you write efficient filtered queries later. Instead of scanning every order ever placed, you can target a specific month or quarter. This becomes critical when query performance matters.

Verify table relationships with a JOIN

You now know the tables exist and how big they are. The final piece is confirming how they connect. The o_custkey column in orders should match c_custkey in customer, linking each order to the customer who placed it.

  • Before you run this next query, predict: if the foreign key relationship is correct, what should you see when you JOIN orders to customer?
  • Run this JOIN query to see customer names alongside their orders:
-- Join orders to customer to verify the relationship
SELECT
  o.o_orderkey,
  o.o_orderdate,
  o.o_totalprice,
  c.c_name,
  c.c_mktsegment
FROM samples.tpch.orders o
JOIN samples.tpch.customer c
  ON o.o_custkey = c.c_custkey
LIMIT 20;

You should see 20 rows, each showing an order's key, date, and total price alongside the customer's name and market segment. The JOIN matched successfully because o_custkey in orders corresponds to c_custkey in customer.

What does this prove?

A successful JOIN confirms the foreign key relationship works. Each order maps to exactly one customer. This same relationship is what you'll exploit in the next step to find data discrepancies: orders that DON'T match a customer, or totals that DON'T add up across tables.

The l_orderkey column in lineitem works the same way, linking individual line items back to their parent order via o_orderkey.

Seeing zero rows or an error?

Check that the ON clause uses o.o_custkey = c.c_custkey with the correct prefixes. A typo in the column name produces a resolution error rather than empty results.

help me fix my JOIN query between orders and customer

  • Rename your query tab by clicking the tab title at the top and typing 01_explore_dataset. Your queries are autosaved as you work.

✔️ Awesome, I've got everything!

Great. Confirm you have all six queries in your editor tab: SHOW TABLES, three DESCRIBE statements, the COUNT UNION ALL, the date range query, and the JOIN query.

ⓧ I'd like to double check the full code

Scroll through your 01_explore_dataset query tab and confirm the following queries appear in order:

  • SHOW TABLES IN samples.tpch at the top.
  • Three DESCRIBE statements for orders, customer, and lineitem.
  • The COUNT UNION ALL query covering all three tables.
  • The date range query using MIN(o_orderdate) and MAX(o_orderdate).
  • The JOIN query selecting from samples.tpch.orders o joined to samples.tpch.customer c with LIMIT 20.

You now have a complete map of the dataset: what tables exist, how big they are, and how they link together. Next up, you'll use these relationships to find where the data breaks down.

Investigate Data Discrepancies with JOINs

In the previous step, you explored the TPC-H dataset and confirmed how the tables connect through foreign keys.

Now a support ticket arrives: "some order totals don't match." Your job is to find where the data breaks down. You'll use LEFT JOINs and aggregations to trace discrepancies across related tables. These patterns work identically in Snowflake, BigQuery, or any SQL platform.

In this step, get ready to:
  • Write a LEFT JOIN query to find orphaned orders with no matching customer.
  • Compare stored order totals against calculated line item totals to surface mismatches.
  • Find customers who exist in the system but have never placed an order.
Find orphaned orders with LEFT JOIN

An orphaned record is a row that references another table's key, but no matching row exists on the other side. This is one of the most common data quality problems in production systems.

  • In the SQL Editor, click + at the top of the editor to open a new tab, then select Create new query.
  • Rename the query by clicking the tab title and typing 02_investigate_discrepancies.
  • Paste the following query to find orders whose o_custkey does not match any customer:
-- Find orphaned orders (orders with no matching customer)
SELECT
  o.o_orderkey,
  o.o_custkey,
  o.o_totalprice,
  o.o_orderdate
FROM samples.tpch.orders o
LEFT JOIN samples.tpch.customer c
  ON o.o_custkey = c.c_custkey
WHERE c.c_custkey IS NULL;

What does this query do?

A LEFT JOIN returns all rows from the left table (orders) even when there is no match in the right table (customer). When there is no match, the customer columns come back as NULL.

The WHERE c.c_custkey IS NULL filter keeps only the orphaned rows. If any orders reference a customer that does not exist, they appear here.

  • Highlight the query and press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run it.

You should see an empty result set (0 rows). The TPC-H dataset has referential integrity, so no orphaned orders exist here. In production, this query would surface the broken references.

Why is an empty result useful?

An empty result on a data quality check means PASS. You now have a reusable query that would catch orphaned orders the moment one appears. Support engineers run these checks proactively, not just when a ticket arrives.

Compare stored totals to calculated line item totals

The ticket says "totals don't match." Each order stores a pre-computed o_totalprice, but the actual total should equal the sum of its line items (with discounts and taxes applied). If these differ, something went wrong during data loading or transformation.

  • Add the following query below the orphaned orders query in the same tab:
-- Compare stored order totals to calculated line item totals
SELECT
  o.o_orderkey,
  o.o_totalprice AS stored_total,
  SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS calculated_total,
  o.o_totalprice - SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS difference
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
GROUP BY o.o_orderkey, o.o_totalprice
HAVING ABS(o.o_totalprice - SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax))) > 0.01
LIMIT 50;

What does this query do?

The formula l_extendedprice * (1 - l_discount) * (1 + l_tax) calculates each line item's net price after discount and tax. SUM() adds them up for each order.

The HAVING clause filters the grouped results to show only orders where the stored total differs from the calculated total by more than one cent. This is how you surface calculation mismatches without drowning in noise from floating-point rounding.

Before you run this, what do you think will happen? Will the stored totals match the calculated totals exactly, or will there be differences?

  • Highlight this second query and press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run it.

You should see rows returned where the calculated total from line items differs from the stored o_totalprice. The difference column shows exactly how much each order is off.

Why do the totals differ?

The TPC-H benchmark pre-computes o_totalprice using a slightly different formula than the line-item-level calculation with per-line tax. This is a realistic scenario. In production, pre-computed aggregates frequently drift from their source rows after partial updates, late-arriving data, or formula changes.

Finding these mismatches is the exact investigation a support engineer performs when a business user reports that their dashboard totals look wrong.

Not seeing any rows returned?

Make sure you highlighted only the second query before running. If you ran all statements, the result pane may show the output from the first query (0 rows) instead. Highlight just the comparison query and run again.

Also confirm the HAVING threshold is > 0.01. A threshold of 0 would include rounding noise and return too many rows.

Help me debug my order totals comparison query.

Find customers who never placed an order

The last piece of the investigation flips the direction. Instead of looking for orders with no customer, you'll look for customers with no orders. These "inactive" customers are important for business reporting and can indicate data loading gaps.

  • Add the following query below the comparison query in the same tab:
-- Find customers who never placed an order
SELECT
  c.c_custkey,
  c.c_name,
  c.c_mktsegment
FROM samples.tpch.customer c
LEFT JOIN samples.tpch.orders o
  ON c.c_custkey = o.o_custkey
WHERE o.o_orderkey IS NULL;

What does this query do?

This is the same LEFT JOIN pattern as the orphaned orders check, but reversed. The customer table is now on the left side, so you keep all customers. Filtering for WHERE o.o_orderkey IS NULL returns only customers that have zero matching orders.

Including c_mktsegment in the output helps you spot patterns. If all inactive customers belong to one segment, that could indicate a targeted data load failure rather than random gaps.

  • Highlight this third query and press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to run it.

You should see a list of customers with their names and market segments who have no orders in the system. These are your inactive customer records.

Seeing an error about ambiguous column names?

Make sure you are using table aliases (c. and o.) on every column reference. Without aliases, the SQL engine does not know which table's column you mean when both tables are in the query.

Help me fix my customer-to-orders LEFT JOIN query.

Your query tab now contains all three investigation queries. The content is autosaved, so your work is already preserved.

✔️ Awesome, I've got everything!

Great. Confirm your tab is named 02_investigate_discrepancies and contains all three queries.

ⓧ I'd like to double check the full code

-- Find orphaned orders (orders with no matching customer)
SELECT
  o.o_orderkey,
  o.o_custkey,
  o.o_totalprice,
  o.o_orderdate
FROM samples.tpch.orders o
LEFT JOIN samples.tpch.customer c
  ON o.o_custkey = c.c_custkey
WHERE c.c_custkey IS NULL;

-- Compare stored order totals to calculated line item totals
SELECT
  o.o_orderkey,
  o.o_totalprice AS stored_total,
  SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS calculated_total,
  o.o_totalprice - SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS difference
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
GROUP BY o.o_orderkey, o.o_totalprice
HAVING ABS(o.o_totalprice - SUM(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax))) > 0.01
LIMIT 50;

-- Find customers who never placed an order
SELECT
  c.c_custkey,
  c.c_name,
  c.c_mktsegment
FROM samples.tpch.customer c
LEFT JOIN samples.tpch.orders o
  ON c.c_custkey = o.o_custkey
WHERE o.o_orderkey IS NULL;

You've just completed the core investigation workflow. You found orphaned records, surfaced calculation mismatches, and identified inactive customers. Next up, you'll take one of these queries, run it at scale, and use the Query Profile to diagnose why it slows down.

Diagnose a Slow Query with Query Profile

In the previous step, your JOIN and aggregation queries surfaced data discrepancies across the orders, customer, and lineitem tables. Those queries ran against filtered subsets and returned quickly.

What happens when you remove the filters and run a heavy aggregation across the entire dataset? This is the exact moment a support engineer needs to stop guessing and start profiling. You will run a broad query, watch it struggle, then use Query Profile to understand exactly why.

In this step, get ready to:
  • Run an unfiltered aggregation query across all orders and line items.
  • Use Query Profile to identify which operations consumed the most time and data.
  • Rewrite the query with a date filter and compare the performance improvement.
Run the unfiltered aggregation query

Your discrepancy queries from the previous step joined orders to lineitem, but they filtered results with HAVING or LIMIT. Now you will ask the warehouse to aggregate across every order in the dataset with no date restriction.

  • In the SQL Editor, click the + button in the tab bar and select Create new query.
  • Paste the following query into the editor:
-- SLOW VERSION: Broad query with no date filter (run this first, note the elapsed time)
SELECT
  o.o_orderkey,
  o.o_totalprice,
  o.o_orderdate,
  SUM(l.l_extendedprice * (1 - l.l_discount)) AS line_total,
  COUNT(l.l_linenumber) AS line_count
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
GROUP BY o.o_orderkey, o.o_totalprice, o.o_orderdate
ORDER BY line_total DESC
LIMIT 100;

What does this query do?

This query joins every order to every matching line item, calculates the total extended price per order (after discounts), counts the line items, and sorts by the highest totals.

There is no WHERE clause filtering dates. The warehouse must scan the full lineitem table (approximately 1 TB in the TPC-H sample) and join it against all orders before it can return results.

Before you run this query, think about how long it might take compared to your filtered queries in the previous step. Do you expect it to be noticeably slower?

  • Click Run (or press Ctrl+Enter) to execute the query.
  • Watch the elapsed time counter at the bottom of the results pane as the query runs.

The query takes noticeably longer than your previous filtered queries. Note the elapsed time displayed at the bottom of the results. This is your "before" baseline.

Why is this so much slower?

You do not know yet. The elapsed time tells you something is wrong, but not what. This is exactly where a support engineer reaches for the Query Profile instead of guessing.

Open Query Profile and identify the bottleneck

The Query Profile visualizes every operation the warehouse performed, showing exactly where time and resources went. It replaces guesswork with data.

  • Click the elapsed time link near the bottom of the results pane (it displays the time elapsed and number of rows returned).
  • In the query details panel that opens, click See query profile.

The Query Profile opens, showing a visual breakdown of the query execution.

  • Look at the Top operators panel on the right side. This shows the most expensive operators used in your query.
  • Identify which operator consumed the most time. You will likely see the Scan or Sort operator at the top.
  • Note the rows processed and bytes scanned metrics on the scan operators. These numbers reveal how much data the warehouse read from disk.

What do these metrics tell you?

The rows processed count shows how many rows the warehouse had to read and join. The bytes scanned metric shows the raw data volume pulled from storage.

When there is no date filter, the warehouse reads the entire lineitem and orders tables. That is the root cause of the slowness.

Don't see the elapsed time link?

Make sure the query has finished running. The link only appears after execution completes (or while it is still running). Look near the bottom of the editor pane, below the results table.

help me find the Query Profile link in Databricks SQL Editor

Rewrite the query with a date filter and compare

Now that you know the warehouse scanned the entire dataset, the fix is straightforward. Adding a date filter lets the warehouse skip irrelevant data files entirely.

  • In the same query tab, add the following query below your slow version (leave the slow version in place for reference):
-- FAST VERSION: Same query with a date filter (run this second, compare elapsed time)
SELECT
  o.o_orderkey,
  o.o_totalprice,
  o.o_orderdate,
  SUM(l.l_extendedprice * (1 - l.l_discount)) AS line_total,
  COUNT(l.l_linenumber) AS line_count
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
WHERE o.o_orderdate BETWEEN '1995-01-01' AND '1995-01-31'
GROUP BY o.o_orderkey, o.o_totalprice, o.o_orderdate
ORDER BY line_total DESC
LIMIT 100;

What changed?

The only difference is the WHERE o.o_orderdate BETWEEN '1995-01-01' AND '1995-01-31' clause. This restricts the query to one month of data instead of the full date range.

Delta Lake tables store data in files grouped by ranges. When you filter by date, the warehouse can skip entire files that fall outside your range. This is called partition pruning.

Before you run the filtered version, predict: how much faster do you think it will be? Half the time? Ten times faster?

  • Highlight only the fast version query (the second SELECT statement) and click Run.
  • Compare the elapsed time to your baseline from the slow version.
  • Click the elapsed time link again to open the Query Profile for this fast version.
  • Compare the rows processed and bytes scanned to the slow version. Look for filter icons on scan metrics that indicate pruned data.

The filtered query returns results significantly faster. The Query Profile shows far fewer rows processed and bytes scanned. That difference is partition pruning in action.

Why does this matter for support engineers?

Partition pruning is the single most impactful query optimization technique in data warehouses. When a user reports a slow query, the first thing a support engineer checks is whether the query filters on the table's date or partition column.

This pattern works identically in Snowflake, BigQuery, or any SQL platform with columnar storage. The tool changes, but the diagnostic approach is universal.

  • Click the query title at the top of the editor (it will say something like "New query") and rename it to 03_diagnose_slow_query.
  • Click Save to store the query in your workspace.

Don't see a noticeable speed difference?

Make sure you highlighted only the second query (the one with the WHERE clause) before clicking Run. If you ran both queries together, the elapsed time reflects both.

On a Free Edition warehouse, both queries may complete within a few seconds due to caching. Try running the slow version first, then wait 30 seconds, then run the fast version. Compare the Query Profile metrics (rows processed, bytes scanned) even if the wall-clock difference is small.

help me compare slow and fast query performance in Databricks Query Profile

✔️ Awesome, I've got everything!

Great. Confirm both the slow and fast versions are saved in your 03_diagnose_slow_query query.

ⓧ I'd like to double check the full code

-- SLOW VERSION: Broad query with no date filter (run this first, note the elapsed time)
SELECT
  o.o_orderkey,
  o.o_totalprice,
  o.o_orderdate,
  SUM(l.l_extendedprice * (1 - l.l_discount)) AS line_total,
  COUNT(l.l_linenumber) AS line_count
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
GROUP BY o.o_orderkey, o.o_totalprice, o.o_orderdate
ORDER BY line_total DESC
LIMIT 100;

-- FAST VERSION: Same query with a date filter (run this second, compare elapsed time)
SELECT
  o.o_orderkey,
  o.o_totalprice,
  o.o_orderdate,
  SUM(l.l_extendedprice * (1 - l.l_discount)) AS line_total,
  COUNT(l.l_linenumber) AS line_count
FROM samples.tpch.orders o
JOIN samples.tpch.lineitem l
  ON o.o_orderkey = l.l_orderkey
WHERE o.o_orderdate BETWEEN '1995-01-01' AND '1995-01-31'
GROUP BY o.o_orderkey, o.o_totalprice, o.o_orderdate
ORDER BY line_total DESC
LIMIT 100;

You just diagnosed a performance bottleneck using real profiling data instead of guesswork. Next up, you will build proactive data quality checks that catch problems before anyone reports them.

Build Data Quality Checks

You now know how to find discrepancies and diagnose slow queries. Those are reactive skills. You used them because someone reported a problem.

Good support engineers go further. They build proactive checks that catch problems before anyone reports them. In this step, you will write the kind of monitoring queries that run on a schedule and alert the team when data quality degrades. This pattern is platform-agnostic. It works the same way in Snowflake, BigQuery, or Azure SQL.

In this step, get ready to:
  • Write individual check queries that detect NULL values, duplicates, and impossible dates.
  • Combine all checks into a single report using UNION ALL.
  • Review Query History to see your full audit trail of past queries.
Write individual data quality checks

Each check answers one question about your data. Is a required column ever NULL? Are there duplicate primary keys? Do any dates fall in the future when they should not?

  • In the SQL Editor, click + at the top of the editor tabs.
  • Select Create new query to open a fresh tab for your data quality checks.
  • Paste the following two NULL checks and run them by pressing Cmd+Enter (macOS) or Ctrl+Enter (Windows):
-- Individual check: NULL values in required columns
SELECT
  'null_orderkey' AS check_name,
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
  COUNT(*) AS affected_rows
FROM samples.tpch.orders
WHERE o_orderkey IS NULL;

-- Individual check: NULL custkey (broken foreign key)
SELECT
  'null_custkey' AS check_name,
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
  COUNT(*) AS affected_rows
FROM samples.tpch.orders
WHERE o_custkey IS NULL;

What do these checks do?

Each check filters for rows that violate a rule (e.g. WHERE o_orderkey IS NULL). If zero rows match, the data passes. If any rows match, the check reports FAIL and the count of affected rows.

The CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' pattern is reusable for any data quality rule. It turns a raw count into a clear pass/fail signal.

  • Check the results pane. You should see two result sets, each showing check_name, status, and affected_rows. Both should show PASS with 0 affected rows.

Only seeing one result set?

By default, the SQL Editor has Run all statements selected. If you only see the last statement's results, scroll through the Output pane at the bottom to find both result sets. Each statement separated by a semicolon produces its own output.

Help me see the results for both SQL statements in the Databricks SQL Editor

Now add checks for two different kinds of data problems: duplicate keys and impossible dates.

  • Add the following queries below your NULL checks and run them:
-- Individual check: Duplicate order keys
SELECT
  'duplicate_orderkey' AS check_name,
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
  COUNT(*) AS affected_rows
FROM (
  SELECT o_orderkey, COUNT(*) AS cnt
  FROM samples.tpch.orders
  GROUP BY o_orderkey
  HAVING COUNT(*) > 1
);

-- Individual check: Orders with future dates
SELECT
  'future_order_dates' AS check_name,
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
  COUNT(*) AS affected_rows
FROM samples.tpch.orders
WHERE o_orderdate > CURRENT_DATE();

What is different about the duplicate check?

The duplicate check uses a subquery. The inner query groups by o_orderkey and finds any key that appears more than once. The outer query counts how many duplicates were found and converts that into a PASS/FAIL.

The future date check uses CURRENT_DATE() so it stays accurate over time. Any order dated after today is suspicious in historical data.

  • You should see two more result sets, both showing PASS with 0 affected rows.
Combine all checks into a single report

Running checks one at a time works, but a support engineer needs a single view of all data quality results at a glance. You will combine every check into one query that produces a clean report.

  • Add the following combined query below your individual checks and run it:
-- Combined data quality report
SELECT 'null_orderkey' AS check_name,
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
  COUNT(*) AS affected_rows
FROM samples.tpch.orders WHERE o_orderkey IS NULL
UNION ALL
SELECT 'null_custkey',
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END,
  COUNT(*)
FROM samples.tpch.orders WHERE o_custkey IS NULL
UNION ALL
SELECT 'duplicate_orderkey',
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END,
  COUNT(*)
FROM (
  SELECT o_orderkey FROM samples.tpch.orders GROUP BY o_orderkey HAVING COUNT(*) > 1
)
UNION ALL
SELECT 'future_order_dates',
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END,
  COUNT(*)
FROM samples.tpch.orders WHERE o_orderdate > CURRENT_DATE()
UNION ALL
SELECT 'orphaned_orders',
  CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END,
  COUNT(*)
FROM samples.tpch.orders o
LEFT JOIN samples.tpch.customer c ON o.o_custkey = c.c_custkey
WHERE c.c_custkey IS NULL;

What does UNION ALL do here?

Each SELECT produces one row with three columns: check_name, status, and affected_rows. UNION ALL stacks all those rows into one results table. You get a complete health report in a single pass.

The fifth check (orphaned_orders) reuses the LEFT JOIN pattern you wrote in Step 3. Real monitoring queries often repackage investigation queries into automated checks.

Before you look at the results, what do you expect to see? Will all five checks pass, or will any fail?

  • Check the results pane. You should see a single table with five rows, one per check. Each row shows the check_name, a status of PASS or FAIL, and the affected_rows count.

Why might orphaned_orders show PASS?

The TPC-H dataset is well-formed benchmark data. Every order references a valid customer. In a real production system, orphaned records are common due to failed inserts, race conditions, or deleted parent records. Your check will catch them when they appear.

Review Query History

Every query you have run in this project is recorded. Query History is the same monitoring view a support engineer uses to triage reported problems, check how long queries took, and spot failures.

  • Click Query History in the left sidebar.
  • You should see a list of all your past queries with their status (Finished, Failed, Cancelled), duration, and the SQL warehouse that ran them.
  • Find your combined data quality report in the list. Note how the duration compares to the broad unfiltered query you ran in the previous step.

How would a support engineer use this?

When a user reports that a dashboard is slow, a support engineer opens Query History, finds the slow query by its duration column, and opens its Query Profile. You have already practiced exactly this workflow. Query History is the entry point for every performance investigation.

  • Return to the SQL Editor by clicking SQL Editor in the sidebar.
  • Rename your query tab to 04_data_quality_checks by clicking the tab title at the top and typing the new name.

Can't find your query in the history?

Query History shows queries for the currently selected warehouse by default. Make sure the filter at the top matches the warehouse you have been using. You can also filter by your username to narrow the list.

Help me find my queries in Databricks Query History

✔️ Awesome, I've got everything!

Great. Make sure your query tab is saved as 04_data_quality_checks. The SQL editor autosaves your content, so your individual checks and the combined report are both preserved.

ⓧ I'd like to double check the full code

Your 04_data_quality_checks file contains all the queries you built in this step. Since the full file is long, confirm each section is present in order:

  • The NULL o_orderkey check (returns null_orderkey as check_name).
  • The NULL o_custkey check (returns null_custkey as check_name).
  • The duplicate o_orderkey check using a subquery with HAVING COUNT(*) > 1.
  • The future dates check using CURRENT_DATE().
  • The combined data quality report using UNION ALL with all five checks (null_orderkey, null_custkey, duplicate_orderkey, future_order_dates, orphaned_orders).

You now have a reusable data quality toolkit. These same check patterns work on any SQL platform. Next up, the Secret Mission challenges you to go beyond GROUP BY with window functions.

Secret mission

Analyze Data Patterns with Window Functions

Window functions are the dividing line between basic SQL and production-ready analytical queries. Every data engineer job description lists them. Can you use ROW_NUMBER, LAG, and SUM OVER to rank customers, detect trends, and flag potential churn?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on Databricks Free Edition, so there are no ongoing costs or charges to worry about.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Databricks Free Edition account and workspace (free, no charges).
  • SQL queries saved in your workspace (persistent, no cost).

Keep everything running

No action needed. Choose this if you plan to continue building on these queries or start the next project.

  • Your workspace persists indefinitely on Free Edition.
  • Your saved SQL queries remain accessible anytime you log back in.
  • The SQL warehouse auto-suspends when idle, so it costs nothing while you are away.

Pause - I'll come back to this later

You do not need to do anything. The SQL warehouse automatically suspends when idle, so there is nothing to shut down or stop manually.

  • Your workspace, queries, and data all persist at no cost.
  • When you return, the warehouse restarts automatically the next time you run a query.

Delete - I don't want to use this again

Remove your saved queries and optionally cancel your Databricks plan. Since Free Edition is free, there is no financial reason to do this.

  • Delete your saved SQL queries by right-clicking each query in the workspace sidebar and selecting Move to Trash.
  • To cancel your Databricks plan entirely, open the account console sidebar, click Settings, click the Manage your plan dropdown, then select Cancel plan.
  • To fully delete your account after cancelling, file a ticket at help.databricks.com.

Should you delete your Free Edition account?

There is no reason to. Free Edition has no charges, no expiry, and no daily cost. Keeping your workspace lets you revisit your queries or start new projects without re-creating an account.

Nice Work!

Nice Work!

You just completed a full data investigation workflow, the same process data platform support engineers follow every day when someone says "the numbers don't add up." You queried, diagnosed, and validated data across a real multi-table business dataset in Databricks.

You've learned how to:

  • Used SQL JOINs and aggregations to trace data discrepancies across related tables, finding orphaned records, calculation mismatches, and missing references.
  • Diagnosed a slow query using Query Profile, identified the most expensive operator, and rewrote the query with a date filter to scan less data.
  • Built a reusable data quality check that reports pass/fail status and affected row counts for multiple validation rules in a single query.
  • Secret Mission: wrote window function queries using ROW_NUMBER, LAG, and SUM OVER to rank customers, compare orders to previous periods, and detect potential churn patterns.

Ready to quiz yourself?