Catch Any Migration Drift in Databricks

Compare Databricks tables to catch migration drift, duplicates, and NULLs.

Introduction

30 Second Summary

Moving data between systems can look successful when both sides contain the same number of rows. One altered value or one missing copy can still leave those totals perfectly matched.

In this project, you will build a Databricks SQL reconciliation notebook that returns an exact match result for two migration tables without relying on row order or a unique key. Its duplicate-aware comparison exposes the complete unmatched rows behind any drift while handling NULL values safely.

What You'll Build

You'll watch two equal row counts conceal a changed amount before the notebook reveals the unmatched rows behind the migration drift.

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

  • A reusable reconciliation notebook where you enter each fully qualified table name once for the whole comparison.
  • A schema preflight that helps you stop before an incompatible full-table scan.
  • An exact migration verdict backed by complete directional mismatch rows, duplicate multiplicity, plus NULL-safe matching.
  • Secret Mission: Create a duplicate-only defect to prove why EXCEPT ALL catches drift that plain EXCEPT misses.

Are there any prerequisites?

Your Databricks workspace must allow SQL notebook creation on an available serverless or pro SQL warehouse.

Both tables must be readable with identical column order, compatible data types, plus no MAP-typed comparison columns.

Before We Start

Before any full-table scan, this checkpoint makes your exact reconciliation target explicit. You will record both fully qualified table names alongside the workspace access needed to verify that no transformation occurred.

Set Up the Reconciliation Notebook

Exact reconciliation needs one active Databricks SQL session that can reach both migration tables. Later session variables and temporary views depend on that shared session.

This step creates a notebook on approved SQL warehouse compute. A small readiness query proves the session works before you query either production table.

In this step, get ready to:
  • Create a SQL notebook named Migration Reconciliation.
  • Attach an available serverless or pro SQL warehouse.
  • Prove the notebook can execute SQL with a readiness query.
Create the SQL notebook

A notebook keeps each reconciliation check in its own cell. Setting SQL as the default language prepares every new cell for the queries ahead.

  • Open your organization's Databricks workspace link in a browser.
  • Complete your organization's sign-in flow.
  • Click + New in the left sidebar.
  • Select Notebook from the menu.
  • Replace the notebook title with Migration Reconciliation.
  • Click the language button below the notebook name.
  • Select SQL as the notebook language.

You should now see a blank notebook named Migration Reconciliation with SQL shown as its default language.

Attach approved compute

An SQL warehouse supplies the compute that executes each notebook cell. Using an existing approved warehouse keeps this step inside your team's operating policy.

Compute choices can affect workspace costs. You will attach existing approved capacity without creating anything new.

  • Open the compute drop-down at the top of Migration Reconciliation.
  • Select Serverless when your workspace offers that approved option.
  • Select your team's approved pro SQL warehouse when Serverless is unavailable.

The notebook header should now show the selected compute resource. This confirms that the notebook is attached to an available warehouse.

No approved warehouse available?

Stop before running SQL if the compute menu has no approved option. Creating unapproved compute can violate your team's workspace policy.

  • Record that you need access to a serverless or pro SQL warehouse.
  • Send the access blocker to your workspace administrator.
  • Resume this step after an approved warehouse becomes available.

Need help describing the blocker? Help me write a Databricks SQL warehouse access request.

Prove the SQL session works

A readiness query isolates notebook setup from production table access. It returns a constant value without reading migration data.

  • Paste this readiness query into the first cell of Migration Reconciliation:
-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

What does this query do?

  • The literal 1 gives the SQL engine a simple value to return.
  • The alias notebook_ready gives the result column a clear name.
  • The query avoids both migration tables while you test the notebook session.

A stopped warehouse starts automatically when you run a query. The first result can take longer while that compute starts.

Before you run the cell, do you expect the notebook to return a value or report a compute blocker?

  • Run the cell with the cell's run control.

You'll see one result row under the notebook_ready column. That row contains 1.

That is the setup hurdle cleared. Your notebook now has a working SQL session.

Readiness query not running?

  • Confirm that the compute drop-down shows an available serverless or pro SQL warehouse.
  • Ask your workspace administrator for access if the selected warehouse cannot start.
  • Confirm that the first cell uses SQL as its language.

Still blocked? Help me diagnose the readiness query.

✔️ Awesome, I've got everything!

Your Migration Reconciliation notebook contains one SQL cell. Its result confirms that the attached compute can execute SQL.

ⓧ I'd like to double check the full code

  • Compare your first notebook cell with the complete migration_reconciliation.sql content below.
-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

Your notebook now has a working SQL session backed by approved compute. Next, you will create a safe four-row demo that shows how equal row counts can hide a changed value.

Demonstrate the Limits of Row Counts

Your Migration Reconciliation notebook can already run SQL on the attached Databricks compute. You now have a safe place to test a migration check before accessing the real tables.

A migration can preserve the total number of rows while changing a value inside one row. This step creates that exact defect so you can see how a row-count check produces false confidence.

In this step, get ready to:
  • Create two temporary views with equal row counts.
  • Run a row-count comparison against both views.
  • Trace the changed value that the counts fail to reveal.
Create the matching-count demo

A temporary view stores query-backed rows inside your current SQL session. These demo views let you reproduce a migration defect without changing either production table.

  • Return to the Migration Reconciliation notebook from earlier.
  • Add a new SQL cell below Cell 1.
  • Create the pre-migration demo view by pasting this code into Cell 2:
-- Cell 2: Create a safe demonstration where counts match but one cell changed.
CREATE OR REPLACE TEMPORARY VIEW demo_pre(region, amount) AS
VALUES
  ('north', 100),
  ('south', 200),
  ('south', 200),
  (NULL, 50);

What Does This SQL Create?

  • The demo_pre view represents the data before migration.
  • The two copies of ('south', 200) model a duplicate row.
  • The (NULL, 50) row keeps a missing region value inside the test data.
  • Run Cell 2.

The cell completes without an error. The demo_pre temporary view now contains four rows in this notebook session.

Could Not Create demo_pre?

  • Confirm that Cell 2 uses the SQL language.
  • Check that every row except the last ends with a comma.
  • Check that the statement ends with a semicolon.

Still stuck? Help me debug the demo_pre temporary view.

The post-migration side keeps the same four-row total. It changes the row order while replacing one 200 amount with 201.

  • Place your cursor after the final semicolon in Cell 2.
  • Add the post-migration demo view by pasting this code below demo_pre:
CREATE OR REPLACE TEMPORARY VIEW demo_post(region, amount) AS
VALUES
  (NULL, 50),
  ('south', 200),
  ('north', 100),
  ('south', 201);

What Changed in the Post-Migration Data?

  • The demo_post view uses the same two columns as demo_pre.
  • Its rows appear in a different order.
  • Its final row contains ('south', 201).
  • It contains one copy of ('south', 200).
  • Run the complete Cell 2.

The cell completes successfully. Both temporary views now exist in the same active notebook session.

Could Not Create demo_post?

  • Confirm that demo_post appears after the semicolon that closes demo_pre.
  • Check that both view definitions contain the columns region and amount.
  • Check that every text value uses matching single quotes.

Need another pair of eyes? Help me debug the two demo views.

Compare the two row counts

A row count measures how many rows each view contains. It does not inspect whether the values inside those rows match.

  • Add a new SQL cell below Cell 2.
  • Prepare the row-count comparison by pasting this query into Cell 3:

Before you run this check, what row count do you expect for each side?

-- Cell 3: This naive check shows 4 rows on both sides.
SELECT 'pre' AS table_side, count(*) AS row_count
FROM demo_pre
UNION ALL
SELECT 'post' AS table_side, count(*) AS row_count
FROM demo_post;

What Does This Query Measure?

  • The first count(*) counts every row in demo_pre.
  • The second count(*) counts every row in demo_post.
  • The UNION ALL operator places the two count results into one output.
  • The result answers whether both sides contain the same quantity of rows.
  • Run Cell 3.

You will see a pre row with a count of 4. You will also see a post row with a count of 4.

You have reproduced the intended false success. The counts agree even though one amount changed during the simulated migration.

Did the Count Query Fail?

  • Return to Cell 2.
  • Run Cell 2 again in the current notebook session.
  • Confirm that Cell 3 references demo_pre and demo_post exactly.

Still seeing a query error? Help me troubleshoot the row-count comparison.

Trace the hidden mismatch

The equal counts describe table size. The rows inside Cell 2 reveal the missing detail.

  • Find the two copies of ('south', 200) inside demo_pre.
  • Find the single copy of ('south', 200) inside demo_post.
  • Locate ('south', 201) inside demo_post.
  • Confirm that both views still contain four rows.

The source has an additional complete row containing 200. The target has a complete row containing 201 in its place.

Why Equal Counts Fall Short

Row counts detect added or missing rows when the total changes. They cannot certify that every complete row survived a migration unchanged.

This demo also includes a duplicate and a NULL value. An exact reconciliation must preserve both while ignoring physical row order.

✔️ Awesome, I've got everything!

Your notebook now contains both demo views and a row-count query that returns 4 for each side.

ⓧ I'd like to double check the full code

Here is the complete migration_reconciliation.sql notebook content for this checkpoint. Compare it with your three cells line by line.

-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

-- Cell 2: Create a safe demonstration where counts match but one cell changed.
CREATE OR REPLACE TEMPORARY VIEW demo_pre(region, amount) AS
VALUES
  ('north', 100),
  ('south', 200),
  ('south', 200),
  (NULL, 50);

CREATE OR REPLACE TEMPORARY VIEW demo_post(region, amount) AS
VALUES
  (NULL, 50),
  ('south', 200),
  ('north', 100),
  ('south', 201);

-- Cell 3: This naive check shows 4 rows on both sides.
SELECT 'pre' AS table_side, count(*) AS row_count
FROM demo_pre
UNION ALL
SELECT 'post' AS table_side, count(*) AS row_count
FROM demo_post;

Your demo has exposed the weakness in a count-only migration check. Next, you will use bidirectional EXCEPT ALL to reveal the complete unmatched rows.

Detect the Hidden Change with EXCEPT ALL

The demo in your Databricks SQL notebook has exposed the trap: both tables contain four rows while one amount has changed. The next check must compare complete row values without treating physical row order as a defect.

EXCEPT ALL treats each table as a multiset of complete rows. Running the difference in both directions exposes rows unique to either side.

In this step, get ready to:
  • Create bidirectional mismatch views using EXCEPT ALL.
  • Inspect unmatched complete rows in each direction.
  • Calculate directional mismatch counts plus exact_match.
Create both directional differences

A one-way difference answers a directional question: which complete rows have occurrences left in the source after matching target occurrences are removed? The reverse difference asks the same question from the target side.

  • In the Migration Reconciliation notebook from earlier, add a SQL cell beneath the row-count cell by pasting this code:
-- Cell 4: Compare the demo in both directions and preserve duplicates.
CREATE OR REPLACE TEMPORARY VIEW demo_source_only AS
SELECT * FROM demo_pre
EXCEPT ALL
(SELECT * FROM demo_post);

How Does the Source-Only View Work?

  • demo_pre supplies the left side of the difference.
  • EXCEPT ALL removes one matching occurrence from the left for each occurrence on the right.
  • The complete region plus amount row forms the comparison unit.
  • NULL values use null-safe set semantics during the comparison.
  • Run the source-only SQL cell.

The cell should complete successfully. The active session now contains demo_source_only.

Source-Only View Failed?

  • Return to the demo cells from earlier.
  • Run the cell that creates demo_pre plus demo_post.
  • Run the source-only cell again in the same SQL warehouse session.

Still stuck? Help me restore the demo views in my current Databricks SQL session.

  • Add the reverse difference in the next SQL cell by pasting this code:
CREATE OR REPLACE TEMPORARY VIEW demo_target_only AS
SELECT * FROM demo_post
EXCEPT ALL
(SELECT * FROM demo_pre);

Why Reverse the Difference?

  • demo_post becomes the left side of this difference.
  • demo_target_only retains target occurrences without matching source occurrences.
  • The two views cover both possible directions of migration drift.
  • Run the target-only SQL cell.

Both difference views now exist in the same session. The comparison is ready to reveal the hidden change.

Target-Only View Failed?

  • Run the demo creation cell from earlier if the SQL warehouse session changed.
  • Check that the reverse query starts with demo_post.
  • Check that the parenthesized query reads from demo_pre.

Need another pair of eyes? Help me debug the reverse EXCEPT ALL view.

Inspect the unmatched rows

The difference views contain complete unmatched row occurrences. Reading both outputs shows which values remain on each side after matching rows cancel out.

  • Add both inspection queries in the next SQL cell by pasting this code:
SELECT * FROM demo_source_only;
SELECT * FROM demo_target_only;

What Do These Queries Inspect?

  • The first query returns unmatched source occurrences from demo_source_only.
  • The second query returns unmatched target occurrences from demo_target_only.
  • Rows with equal complete values cancel regardless of their physical order.

Before you run these queries, which complete row do you expect each direction to return?

  • Run the inspection SQL cell.

There it is: the hidden change is visible despite matching row counts. demo_source_only shows south | 200, while demo_target_only shows south | 201.

The reordered north row does not appear. The matching NULL row also does not appear.

Seeing Extra Mismatch Rows?

  • Return to the demo creation cell from the previous step.
  • Confirm that demo_pre contains two copies of south | 200.
  • Confirm that demo_post contains one south | 200 row plus one south | 201 row.

Still seeing unexpected rows? Help me compare my demo data with the expected mismatch results.

Summarize the demo result

Individual rows explain the defect. A compact summary turns those rows into a reusable exact-match decision.

  • Add the exact-match summary in the next SQL cell by pasting this code:
SELECT
  (SELECT count(*) FROM demo_source_only) AS source_only_row_count,
  (SELECT count(*) FROM demo_target_only) AS target_only_row_count,
  (SELECT count(*) FROM demo_source_only) = 0
    AND (SELECT count(*) FROM demo_target_only) = 0 AS exact_match;

How Does the Summary Decide?

  • source_only_row_count counts unmatched source occurrences.
  • target_only_row_count counts unmatched target occurrences.
  • exact_match becomes true only when both directional counts equal 0.

Before you run this summary, can exact_match be true while either directional count is above 0?

  • Run the summary SQL cell.

You should see 1 for source_only_row_count. You should also see 1 for target_only_row_count.

The exact_match result is false. The two nonzero counts prove that equal table sizes did not guarantee equal complete rows.

Summary Counts Look Wrong?

  • Run both temporary-view creation cells again.
  • Run both inspection queries again.
  • Check that the summary reads from demo_source_only plus demo_target_only.

Need help tracing the counts? Help me debug the demo exact-match summary.

✔️ Awesome, I've got everything!

Your notebook now contains both directional views, both inspection queries, and the failed exact-match summary.

ⓧ I'd like to double check the full code

  • Compare your notebook with the complete SQL below:
-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

-- Cell 2: Create a safe demonstration where counts match but one cell changed.
CREATE OR REPLACE TEMPORARY VIEW demo_pre(region, amount) AS
VALUES
  ('north', 100),
  ('south', 200),
  ('south', 200),
  (NULL, 50);

CREATE OR REPLACE TEMPORARY VIEW demo_post(region, amount) AS
VALUES
  (NULL, 50),
  ('south', 200),
  ('north', 100),
  ('south', 201);

-- Cell 3: This naive check shows 4 rows on both sides.
SELECT 'pre' AS table_side, count(*) AS row_count
FROM demo_pre
UNION ALL
SELECT 'post' AS table_side, count(*) AS row_count
FROM demo_post;

-- Cell 4: Compare the demo in both directions and preserve duplicates.
CREATE OR REPLACE TEMPORARY VIEW demo_source_only AS
SELECT * FROM demo_pre
EXCEPT ALL
(SELECT * FROM demo_post);

CREATE OR REPLACE TEMPORARY VIEW demo_target_only AS
SELECT * FROM demo_post
EXCEPT ALL
(SELECT * FROM demo_pre);

SELECT * FROM demo_source_only;
SELECT * FROM demo_target_only;

SELECT
  (SELECT count(*) FROM demo_source_only) AS source_only_row_count,
  (SELECT count(*) FROM demo_target_only) AS target_only_row_count,
  (SELECT count(*) FROM demo_source_only) = 0
    AND (SELECT count(*) FROM demo_target_only) = 0 AS exact_match;

The demo has now caught the changed amount without flagging reordered rows. Next, you will connect the same comparison pattern to your real migration tables.

Connect the Migration Tables

Your Databricks demo already exposed the changed amount that matching row counts missed. The next goal is to connect that proven comparison pattern to your real migration tables.

Exact set comparison depends on schema alignment. A mismatched column position can turn the result into misleading migration evidence.

In this step, get ready to:
  • Set the real migration table names with session variables.
  • Confirm both schemas are safe to compare.
  • Record baseline row counts through temporary aliases.
Declare the real table names

Session variables let you enter each fully qualified table name once. Later cells can reuse those names without repeating environment-specific values.

  • In the Migration Reconciliation notebook from earlier, add a new SQL cell beneath the demo summary.
  • Paste this declaration template into the cell:
-- Cell 5: Replace these example values with the real fully qualified names.
DECLARE OR REPLACE VARIABLE source_table STRING
  DEFAULT 'catalog.schema.pre_migration';
DECLARE OR REPLACE VARIABLE target_table STRING
  DEFAULT 'catalog.schema.post_migration';

What do these variables do?

  • The DECLARE OR REPLACE VARIABLE statement stores each table name in the current SQL session.
  • The source_table variable points to the pre-migration table.
  • The target_table variable points to the post-migration table.
  • Replace catalog.schema.pre_migration with the fully qualified pre-migration table name you identified earlier.
  • Replace catalog.schema.post_migration with the fully qualified post-migration table name you identified earlier.
  • Run the cell in the active SQL warehouse session.

Good progress. Your current session now holds both real table references for the remaining notebook cells.

Variable cell not running?

  • Check that each fully qualified table name remains inside single quotes.
  • Confirm the notebook is still attached to the SQL warehouse from earlier.

Still stuck? Help me troubleshoot my Databricks SQL session variable declarations.

Check schema alignment

Set operators compare corresponding columns by position. Each corresponding pair needs a compatible data type.

  • Add a new SQL cell beneath the variable declarations.
  • Paste both schema inspection statements into the cell:
-- Cell 6: Compare these outputs before running the full scan.
DESCRIBE TABLE IDENTIFIER(source_table);
DESCRIBE TABLE IDENTIFIER(target_table);

What does this preflight check do?

  • The DESCRIBE TABLE statements return metadata for the referenced tables.
  • The IDENTIFIER clause resolves each session variable as a table reference.
  • This preflight catches schema problems before the full-table comparison consumes compute.
  • Run the schema inspection cell.

You will see schema metadata for both migration tables. These results are your checkpoint before the full scan.

  • Compare the returned column names in order.
  • Confirm each corresponding position uses a compatible data type.
  • Confirm neither schema contains a MAP-typed comparison column.
  • Stop the reconciliation if any schema check fails.

Cannot describe a table?

  • Check that the catalog name matches the table location in your workspace.
  • Check that the schema name matches the table location in your workspace.
  • Ask your workspace administrator to confirm your read access if the table reference is correct.

Need help narrowing it down? Help me diagnose why my Databricks notebook cannot describe one of my migration tables.

Create aliases and record row counts

The real tables now pass the schema preflight. Temporary views give the remaining comparison cells short session-scoped names.

  • Add a new SQL cell beneath the schema inspection cell.
  • Paste the temporary view definitions into the cell:
-- Cell 7: Create session-scoped aliases for the real tables.
CREATE OR REPLACE TEMPORARY VIEW source_rows AS
SELECT * FROM IDENTIFIER(source_table);

CREATE OR REPLACE TEMPORARY VIEW target_rows AS
SELECT * FROM IDENTIFIER(target_table);

What do these aliases provide?

  • The source_rows view exposes every complete row from the pre-migration table.
  • The target_rows view exposes every complete row from the post-migration table.
  • Both views remain scoped to the current SQL session.
  • Run the temporary view cell.

Both statements should complete successfully. The short aliases now point to the real migration data.

Temporary view creation failed?

  • Re-run the variable declaration cell if the SQL session changed.
  • Re-run the schema inspection cell to confirm both table references still resolve.

Still blocked? Help me troubleshoot the source_rows and target_rows temporary views.

Baseline counts show how many complete rows each alias exposes. They provide context for the exact reconciliation without proving equality on their own.

  • Add a new SQL cell beneath the temporary view cell.
  • Paste the baseline row-count query into the cell:
-- Cell 8: Record the baseline row counts.
SELECT 'source' AS table_side, count(*) AS row_count
FROM source_rows
UNION ALL
SELECT 'target' AS table_side, count(*) AS row_count
FROM target_rows;

What does this baseline measure?

  • The count(*) expressions count every row exposed by each temporary view.
  • The UNION ALL operator returns both counts in one result.
  • The output establishes table size before the duplicate-aware comparison.

Before you run this, pause to predict whether the source count will match the target count.

  • Run the baseline row-count cell.

You will see one row labelled source plus one row labelled target. Each row includes the count returned from its real migration table.

That is the production connection complete. Your notebook can now read both migration tables through aligned session-scoped aliases.

Counts not returning?

  • Re-run the temporary view cell if the SQL session restarted.
  • Confirm the SQL warehouse remains available to the notebook.
  • Ask your workspace administrator to verify read access if one table cannot be scanned.

Need another pair of eyes? Help me troubleshoot why my Databricks baseline row-count query is not returning both tables.

✔️ Awesome, I've got everything!

Great. Double check that both schemas passed the preflight and that the baseline query returned a count for each migration table.

ⓧ I'd like to double check the full code

Here is the complete migration_reconciliation.sql notebook content you should have so far. Compare it with yours cell by cell.

-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

-- Cell 2: Create a safe demonstration where counts match but one cell changed.
CREATE OR REPLACE TEMPORARY VIEW demo_pre(region, amount) AS
VALUES
  ('north', 100),
  ('south', 200),
  ('south', 200),
  (NULL, 50);

CREATE OR REPLACE TEMPORARY VIEW demo_post(region, amount) AS
VALUES
  (NULL, 50),
  ('south', 200),
  ('north', 100),
  ('south', 201);

-- Cell 3: This naive check shows 4 rows on both sides.
SELECT 'pre' AS table_side, count(*) AS row_count
FROM demo_pre
UNION ALL
SELECT 'post' AS table_side, count(*) AS row_count
FROM demo_post;

-- Cell 4: Compare the demo in both directions and preserve duplicates.
CREATE OR REPLACE TEMPORARY VIEW demo_source_only AS
SELECT * FROM demo_pre
EXCEPT ALL
(SELECT * FROM demo_post);

CREATE OR REPLACE TEMPORARY VIEW demo_target_only AS
SELECT * FROM demo_post
EXCEPT ALL
(SELECT * FROM demo_pre);

SELECT * FROM demo_source_only;
SELECT * FROM demo_target_only;

SELECT
  (SELECT count(*) FROM demo_source_only) AS source_only_row_count,
  (SELECT count(*) FROM demo_target_only) AS target_only_row_count,
  (SELECT count(*) FROM demo_source_only) = 0
    AND (SELECT count(*) FROM demo_target_only) = 0 AS exact_match;

-- Cell 5: Replace these example values with the real fully qualified names.
DECLARE OR REPLACE VARIABLE source_table STRING
  DEFAULT 'catalog.schema.pre_migration';
DECLARE OR REPLACE VARIABLE target_table STRING
  DEFAULT 'catalog.schema.post_migration';

-- Cell 6: Compare these outputs before running the full scan.
DESCRIBE TABLE IDENTIFIER(source_table);
DESCRIBE TABLE IDENTIFIER(target_table);

-- Cell 7: Create session-scoped aliases for the real tables.
CREATE OR REPLACE TEMPORARY VIEW source_rows AS
SELECT * FROM IDENTIFIER(source_table);

CREATE OR REPLACE TEMPORARY VIEW target_rows AS
SELECT * FROM IDENTIFIER(target_table);

-- Cell 8: Record the baseline row counts.
SELECT 'source' AS table_side, count(*) AS row_count
FROM source_rows
UNION ALL
SELECT 'target' AS table_side, count(*) AS row_count
FROM target_rows;

Your real tables are connected through schema-checked aliases. Next up, you will run the bidirectional production comparison and turn those tables into an exact PASS or FAIL result.

Run the Exact Production Reconciliation

Your real-table aliases are ready in the Migration Reconciliation notebook. The earlier schema check cleared both tables for an exact comparison in Databricks SQL.

A production verdict needs evidence in both directions. This step treats each table as a multiset of complete rows so every duplicate occurrence contributes to the result.

In this step, get ready to:
  • Create duplicate-aware mismatch views in both directions.
  • Run one summary query for the exact verdict.
  • Inspect complete unmatched rows when the verdict is false.
Create the bidirectional mismatch views

A one-way difference finds source rows without matching target occurrences. Exact equality also requires the reverse comparison.

  • In the Migration Reconciliation notebook from earlier, add a SQL cell beneath Cell 8.
  • Paste the source-only definition into Cell 9:
-- Cell 9: Perform the exact comparison in both directions.
CREATE OR REPLACE TEMPORARY VIEW source_only_rows AS
SELECT * FROM source_rows
EXCEPT ALL
(SELECT * FROM target_rows);

What does the source-only view contain?

  • The left side reads every complete row occurrence from source_rows.
  • The right side removes matching occurrences found in target_rows.
  • The ALL behavior preserves duplicate multiplicity during the difference.
  • The resulting source_only_rows view holds source occurrences that remain unmatched.
  • Run the source-only statement in Cell 9.

You should see the statement complete successfully. The active session now contains the source-side mismatch evidence.

Source-only view did not run?

  • Confirm that the notebook remains attached to the same active SQL warehouse session.
  • Rerun Cell 7 if source_rows or target_rows is unavailable.
  • Confirm that your read access to both migration tables remains active.

Still blocked? Help me diagnose why the source-only temporary view cannot be created..

The reverse difference finds target rows without matching source occurrences. Together, the two views test equality without assuming a unique key.

  • Paste the target-only definition beneath the source-only statement in Cell 9:
CREATE OR REPLACE TEMPORARY VIEW target_only_rows AS
SELECT * FROM target_rows
EXCEPT ALL
(SELECT * FROM source_rows);

Why reverse the comparison?

  • The reversed query begins with target_rows.
  • It removes matching source occurrences while preserving duplicate counts.
  • The resulting target_only_rows view captures unmatched target occurrences.
  • Run the target-only statement in Cell 9.

You should see the second statement complete successfully. Both directional mismatch views now exist in the active session.

Target-only view did not run?

  • Check that the target statement sits below the closing parenthesis of the source statement.
  • Confirm that target_rows appears on the left side of the reverse comparison.
  • Rerun the schema inspection cells if the underlying tables changed after your earlier check.

Need another pair of eyes? Help me check the direction of both EXCEPT ALL views..

Run the exact-match summary

The mismatch views contain the detailed evidence. A single summary row turns that evidence into a result you can record for the migration.

  • Add a SQL cell beneath Cell 9.
  • Paste the final summary query into Cell 10:
-- Cell 10: Produce the final reconciliation result.
SELECT
  (SELECT count(*) FROM source_rows) AS source_row_count,
  (SELECT count(*) FROM target_rows) AS target_row_count,
  (SELECT count(*) FROM source_only_rows) AS source_only_row_count,
  (SELECT count(*) FROM target_only_rows) AS target_only_row_count,
  (SELECT count(*) FROM source_only_rows) = 0
    AND (SELECT count(*) FROM target_only_rows) = 0 AS exact_match;

How is the verdict calculated?

  • The source_row_count field records every row in the source alias.
  • The target_row_count field records every row in the target alias.
  • The source_only_row_count field measures unmatched source occurrences.
  • The target_only_row_count field measures unmatched target occurrences.
  • The exact_match field becomes true only when both mismatch counts equal 0.

Production-scale comparisons can take longer than the demo because the warehouse processes the complete tables. Before you run Cell 10, do you expect both directional mismatch counts to be zero?

  • Run Cell 10.

You should see one row with five named fields. The exact_match field shows the production reconciliation verdict.

That is the central proof complete. Your notebook now produces an exact result that preserves duplicate multiplicity.

Summary query did not finish?

  • Confirm that both Cell 9 statements completed before Cell 10.
  • Return to the same active SQL warehouse session if the notebook became detached.
  • Ask your workspace administrator to check compute access if the warehouse is unavailable.

Need help isolating the blocker? Help me troubleshoot the production reconciliation summary..

Inspect mismatches and preserve cleanup

A false verdict needs inspectable evidence. The mismatch views expose complete unmatched rows while preserving the diagnostic limit created by having no unique key.

  • Add a SQL cell beneath Cell 10.
  • Paste the mismatch sample queries into Cell 11:
-- Cell 11: Run these only when exact_match is false.
SELECT * FROM source_only_rows LIMIT 20;
SELECT * FROM target_only_rows LIMIT 20;

How should you read these samples?

The first query samples complete source rows that remain unmatched. The second query samples complete target rows that remain unmatched.

A changed cell can create evidence in both outputs. Without a unique key, the evidence cannot pair a particular source row with a particular target row.

✔️ exact_match is true

Both directional mismatch counts are 0. The tables contain the same complete rows with the same duplicate multiplicities.

  • Keep Cell 11 unrun because there are no mismatch rows to inspect.

ⓧ exact_match is false

At least one directional mismatch count is positive. Cell 11 reveals the complete rows behind that result.

Before you run Cell 11, which mismatch direction do you expect to contain rows?

  • Run Cell 11.

You should see up to 20 source-only rows in the first result. You should see up to 20 target-only rows in the second result.

Treat each result as directional evidence. Rows at the same displayed position do not establish an old-to-new pairing.

Mismatch samples are unavailable?

  • Rerun Cell 9 if the mismatch views expired with an earlier session.
  • Confirm that the first Cell 11 statement references source_only_rows.
  • Confirm that the second Cell 11 statement references target_only_rows.

Still missing the evidence? Help me inspect the directional mismatch samples..

The cleanup cell only targets temporary views created by this notebook. Your production tables remain unchanged.

  • Add a final SQL cell beneath Cell 11.
  • Paste the optional cleanup statements into Cell 12:
-- Cell 12: Optional explicit cleanup for the temporary views.
DROP VIEW IF EXISTS source_only_rows;
DROP VIEW IF EXISTS target_only_rows;
DROP VIEW IF EXISTS source_rows;
DROP VIEW IF EXISTS target_rows;
DROP VIEW IF EXISTS demo_source_only;
DROP VIEW IF EXISTS demo_target_only;
DROP VIEW IF EXISTS demo_pre;
DROP VIEW IF EXISTS demo_post;

What does the cleanup cell remove?

Each statement removes one temporary view from the current session. The IF EXISTS clause suppresses a missing-view error.

The cell leaves both migration tables untouched. Keep it in the notebook for explicit cleanup after a future reconciliation run.

  • Keep Cell 12 unrun while you want the temporary views available.

✔️ Awesome, I've got everything!

Great. Your notebook contains the production mismatch views, final summary, diagnostic samples, and optional cleanup statements.

ⓧ I'd like to double check the full code

Compare your migration_reconciliation.sql notebook with this complete reference. Keep your own fully qualified migration table names in Cell 5.

-- Cell 1: Prove that the notebook can run SQL.
SELECT 1 AS notebook_ready;

-- Cell 2: Create a safe demonstration where counts match but one cell changed.
CREATE OR REPLACE TEMPORARY VIEW demo_pre(region, amount) AS
VALUES
  ('north', 100),
  ('south', 200),
  ('south', 200),
  (NULL, 50);

CREATE OR REPLACE TEMPORARY VIEW demo_post(region, amount) AS
VALUES
  (NULL, 50),
  ('south', 200),
  ('north', 100),
  ('south', 201);

-- Cell 3: This naive check shows 4 rows on both sides.
SELECT 'pre' AS table_side, count(*) AS row_count
FROM demo_pre
UNION ALL
SELECT 'post' AS table_side, count(*) AS row_count
FROM demo_post;

-- Cell 4: Compare the demo in both directions and preserve duplicates.
CREATE OR REPLACE TEMPORARY VIEW demo_source_only AS
SELECT * FROM demo_pre
EXCEPT ALL
(SELECT * FROM demo_post);

CREATE OR REPLACE TEMPORARY VIEW demo_target_only AS
SELECT * FROM demo_post
EXCEPT ALL
(SELECT * FROM demo_pre);

SELECT * FROM demo_source_only;
SELECT * FROM demo_target_only;

SELECT
  (SELECT count(*) FROM demo_source_only) AS source_only_row_count,
  (SELECT count(*) FROM demo_target_only) AS target_only_row_count,
  (SELECT count(*) FROM demo_source_only) = 0
    AND (SELECT count(*) FROM demo_target_only) = 0 AS exact_match;

-- Cell 5: Replace these example values with the real fully qualified names.
DECLARE OR REPLACE VARIABLE source_table STRING
  DEFAULT 'catalog.schema.pre_migration';
DECLARE OR REPLACE VARIABLE target_table STRING
  DEFAULT 'catalog.schema.post_migration';

-- Cell 6: Compare these outputs before running the full scan.
DESCRIBE TABLE IDENTIFIER(source_table);
DESCRIBE TABLE IDENTIFIER(target_table);

-- Cell 7: Create session-scoped aliases for the real tables.
CREATE OR REPLACE TEMPORARY VIEW source_rows AS
SELECT * FROM IDENTIFIER(source_table);

CREATE OR REPLACE TEMPORARY VIEW target_rows AS
SELECT * FROM IDENTIFIER(target_table);

-- Cell 8: Record the baseline row counts.
SELECT 'source' AS table_side, count(*) AS row_count
FROM source_rows
UNION ALL
SELECT 'target' AS table_side, count(*) AS row_count
FROM target_rows;

-- Cell 9: Perform the exact comparison in both directions.
CREATE OR REPLACE TEMPORARY VIEW source_only_rows AS
SELECT * FROM source_rows
EXCEPT ALL
(SELECT * FROM target_rows);

CREATE OR REPLACE TEMPORARY VIEW target_only_rows AS
SELECT * FROM target_rows
EXCEPT ALL
(SELECT * FROM source_rows);

-- Cell 10: Produce the final reconciliation result.
SELECT
  (SELECT count(*) FROM source_rows) AS source_row_count,
  (SELECT count(*) FROM target_rows) AS target_row_count,
  (SELECT count(*) FROM source_only_rows) AS source_only_row_count,
  (SELECT count(*) FROM target_only_rows) AS target_only_row_count,
  (SELECT count(*) FROM source_only_rows) = 0
    AND (SELECT count(*) FROM target_only_rows) = 0 AS exact_match;

-- Cell 11: Run these only when exact_match is false.
SELECT * FROM source_only_rows LIMIT 20;
SELECT * FROM target_only_rows LIMIT 20;

-- Cell 12: Optional explicit cleanup for the temporary views.
DROP VIEW IF EXISTS source_only_rows;
DROP VIEW IF EXISTS target_only_rows;
DROP VIEW IF EXISTS source_rows;
DROP VIEW IF EXISTS target_rows;
DROP VIEW IF EXISTS demo_source_only;
DROP VIEW IF EXISTS demo_target_only;
DROP VIEW IF EXISTS demo_pre;
DROP VIEW IF EXISTS demo_post;
  • Return to the Cell 10 result.
  • Confirm that the result still displays the five reconciliation fields.
  • Confirm that Cell 11 displays directional samples when exact_match is false.

You have finished the production reconciliation. The notebook now gives you an exact verdict plus complete evidence for every unmatched row occurrence.

Secret mission

Expose a Duplicate-Only Defect

Can two tables contain the same distinct values while still holding different data? Build a controlled duplicate-only defect to prove why exact migration reconciliation must preserve every repeated row.

Clean Up Your Resources

Clean Up Your Resources

Your notebook uses existing Databricks compute, so there are no external tools to cancel. Any additional cost follows your workspace plan plus its SQL warehouse policy.

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

Cost warning

The full bidirectional comparison scans and redistributes table data. Re-running it against million-row tables consumes the Databricks compute assigned to the notebook.

Actual cost depends on your workspace contract. Your cloud environment also affects the total.

Warehouse configuration affects the cost. Runtime affects it too.

Resources you used:

  • The Migration Reconciliation SQL notebook in your Databricks workspace.
  • Demo temporary views named demo_pre, demo_post, demo_source_only, and demo_target_only.
  • Real-table temporary views named source_rows, target_rows, source_only_rows, and target_only_rows.
  • Duplicate-only temporary views named duplicate_pre and duplicate_post.
  • Session variables named source_table and target_table.

Keep everything running

No action is needed. Choose this option if you want to reuse the reconciliation notebook for another migration run.

  • Keep the Migration Reconciliation notebook in its current workspace folder.
  • Keep both migration tables unchanged.
  • Reattach the notebook to approved compute when you return.
  • Run the notebook cells again to recreate the session variables plus temporary views.

Pause - I'll come back to this later

Pausing ends this active work session while keeping the notebook ready for another migration. Your production tables remain unchanged.

  • Detach the Migration Reconciliation notebook from compute.
  • Leave a shared SQL warehouse running unless your team's operating policy permits you to stop it.
  • Reattach the notebook to approved compute when you are ready to resume.
  • Run the required notebook cells again to restore the session-scoped views plus variables.

Delete - I don't want to use this again

Deleting workspace artifacts can feel risky. This path leaves both migration tables untouched.

  • Remove the reconciliation temporary views by running the final cleanup cell:
DROP VIEW IF EXISTS source_only_rows;
DROP VIEW IF EXISTS target_only_rows;
DROP VIEW IF EXISTS source_rows;
DROP VIEW IF EXISTS target_rows;
DROP VIEW IF EXISTS demo_source_only;
DROP VIEW IF EXISTS demo_target_only;
DROP VIEW IF EXISTS demo_pre;
DROP VIEW IF EXISTS demo_post;

What does this cleanup remove?

These statements remove only the eight named temporary views. The IF EXISTS clause lets cleanup continue when a view has already disappeared.

The cleanup does not modify either migration table. Your source data plus target data remain available.

After the cell finishes, the eight named views are no longer available in the active session.

The notebook deletion may require a confirmation in the workspace.

  • End the active notebook session by detaching the notebook from compute.
  • Return to the workspace folder containing Migration Reconciliation to locate the notebook.
  • Open the context menu beside Migration Reconciliation.
  • Choose the delete action for the notebook.
  • Approve the confirmation for Migration Reconciliation.

Ending the session clears the remaining session-scoped objects, including duplicate_pre, duplicate_post, source_table, and target_table.

Cleanup is complete. Your pre-migration plus post-migration tables remain untouched.

Nice Work!

Nice Work!

You did it! Your Databricks SQL notebook now catches migration drift that matching row counts can hide.

You've learned how to:

  • Built an order-independent reconciliation notebook for same-schema Databricks tables. The demo proves that matching row counts can hide a changed cell.
  • Used EXCEPT ALL in both directions to preserve duplicate multiplicity. The comparison also handles NULL values safely. The two mismatch views expose complete unmatched rows.
  • Produced an exact migration verdict from both directional mismatch counts. Interpreted failed samples as unmatched complete rows without inventing unsupported old-to-new pairings.
  • Secret Mission: Proved that plain EXCEPT can miss duplicate-only drift. Confirmed that EXCEPT ALL returns the extra B occurrence.

Ready to quiz yourself?