Migrate SQL Server Procs to dbt on Databricks

Migrate legacy SQL Server stored procedures to dbt models on Databricks.

Introduction

30 Second Summary

Every organization has legacy SQL pipelines that were built years ago and still power critical business decisions. When it is time to modernize, someone has to prove the new system produces the exact same data as the old one.

In this project, you will migrate SQL Server stored procedures into modular dbt models on Databricks, then use Datafold Data Diff to prove value-level parity between your legacy outputs and the new models.

What You'll Build

A complete data platform migration from SQL Server stored procedures to modular dbt models on Unity Catalog, validated with automated cross-table data diffing through Datafold Cloud.

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

  • A set of legacy stored procedures in SQL Server containing real business logic like fiscal calendar calculations, NULL handling, and revenue aggregations.
  • Modular dbt models (staging and mart layers) deployed to Databricks Unity Catalog that replicate the same logic.
  • Datafold Data Diff reports proving 100% parity between legacy outputs and new models, packaged as a professional GitHub PR for stakeholder sign-off.
  • Secret Mission: Add incremental processing and a dbt test suite that catches data quality regressions post-migration.

Are there any prerequisites?

This project assumes you have a local SQL Server instance running with SQL Server Management Studio (SSMS) installed, Python 3.13.2 ready to go, and a GitHub account.

You should also be comfortable writing SQL queries. No prior experience with dbt or Databricks is required.

Set Up Your Environment

Before you can migrate stored procedures, you need a modern data platform to migrate to. This step connects all the pieces: a local Python environment with dbt, a Databricks workspace as your target platform, and Datafold for validating the migration.

By the end of this step, you will have a fully connected toolchain ready to translate and validate SQL Server logic on Databricks.

In this step, get ready to:
  • Install dbt-databricks in a Python virtual environment.
  • Sign up for a Databricks free trial and configure your workspace.
  • Connect Datafold to Databricks as a data source.
Install dbt-databricks in a Python virtual environment

The dbt adapter for Databricks bundles everything you need into a single package. Installing dbt-databricks pulls in dbt-core automatically, so you only need one install command.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Windsurf and press Enter to open it.
  • Open a terminal in Windsurf by pressing Ctrl+` (backtick key, top-left of your keyboard).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project workspace folder and move into it by running these commands:
mkdir datafold-migration
cd datafold-migration
  • Create a Python virtual environment by running:
python -m venv .venv

What is a virtual environment?

A virtual environment is an isolated Python installation that keeps your project's packages separate from your system Python. This prevents version conflicts between projects.

  • Activate the virtual environment by running the activation command for your operating system:

macOS

  • Run this activation command:
source .venv/bin/activate

Windows

  • Run this activation command:
.venv\Scripts\activate

You should see (.venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

  • Install the dbt-databricks adapter by running:
python -m pip install dbt-databricks==1.12.1

What does this install?

The dbt-databricks package is the dbt adapter for Databricks. It automatically installs dbt-core as a dependency, so you get both dbt and the Databricks connector in one command.

  • Verify dbt installed correctly by running:
dbt --version

You should see dbt-databricks listed with version 1.12.1 in the output.

Not seeing dbt-databricks 1.12.1?

  • Make sure your virtual environment is active. You should see (.venv) in your terminal prompt. If not, re-run the activation command above.
  • If the install failed, check your Python version with python --version. You need Python 3.13.

Help me troubleshoot my dbt-databricks installation.

Sign up for Databricks free trial and configure workspace

Databricks will be the target platform for your migrated dbt models. The free trial gives you 14 days of full platform access with up to $400 in credits.

  • Open your browser and navigate to the Databricks trial page.
  • Sign up using your email address and select the Trial option (the "For Work" path).
  • Choose Start trial with express setup.
  • Give your account a name (e.g., migration-project).
  • Select your region and click Continue.

Databricks creates a serverless workspace for you automatically. Unity Catalog is auto-enabled for all workspaces created after November 2023.

What is Unity Catalog?

Unity Catalog is Databricks' governance layer that organizes data using a three-level namespace: catalog.schema.table. Think of it like a filing cabinet where catalogs are the drawers, schemas are the folders, and tables are the documents inside.

Now you need to note down two connection details and generate an access token.

  • In your Databricks workspace, click SQL Warehouses in the left sidebar under the SQL section.
  • You should see a Serverless SQL Warehouse already created by the express setup. Click on it.
  • Click the Connection Details tab.
  • Copy the Server hostname value (it looks like dbc-xxxxx.cloud.databricks.com) and paste it somewhere safe (e.g., a notes file).
  • Copy the HTTP path value and save it alongside the hostname.

Keep these values handy

You will use the Server hostname and HTTP path multiple times throughout this project. Keep them in a scratch file or pinned note for quick access.

  • Generate a Personal Access Token by clicking your username in the top bar and selecting Settings.
  • Click Developer.
  • Next to Access tokens, click Manage.
  • Click Generate new token.
  • Enter a name like dbt-migration and click Generate.
  • Copy the token value immediately and store it securely. You will not be able to see it again.

Save your token now

The Personal Access Token is only displayed once. If you close the dialog without copying it, you will need to generate a new one. Paste it into the same notes file with your hostname and HTTP path.

Don't see SQL Warehouses in the sidebar?

  • Make sure you selected the Databricks Trial ("For Work") option during signup, not Free Edition. Free Edition has limited features.
  • Your workspace may still be provisioning. Wait 1-2 minutes and refresh the page.

My Databricks workspace doesn't show SQL Warehouses. I signed up for the free trial using express setup.

Sign up for Datafold and connect Databricks as a data source

Datafold is the validation tool you will use to prove your migration is correct. It performs value-level comparison between tables, catching discrepancies that row counts alone would miss.

  • Open the Datafold signup page in your browser.
  • Create your account with your email. This starts a 14-day free trial.
  • Once logged in, navigate to Settings in the left sidebar.
  • Click Integrations, then Data connections.
  • Click Add new integration.
  • Select Databricks from the list of data sources.

Fill in the connection form using the values you saved from Databricks.

  • Enter a descriptive name in the Connection name field (e.g., databricks-migration).
  • Paste your Server hostname into the Host field.
  • Paste your HTTP path into the HTTP path field.
  • Paste your Personal Access Token into the authentication field.
  • Click Create to save the connection.

Datafold tests the connection automatically. You should see a success indicator confirming your Databricks data source is connected.

Connection failed?

  • Double-check that you copied the full hostname without any trailing spaces.
  • Verify your Personal Access Token has not expired. Tokens generated with the default settings last 90 days.
  • Ensure your SQL Warehouse is running (not paused). Go back to Databricks and check its status.

Help me troubleshoot my Datafold connection to Databricks.

Your toolchain is fully connected. Next up, you will build a realistic legacy pipeline in SQL Server that simulates the kind of stored procedures Datafold's Migration Agent handles.

Create the Legacy SQL Server Pipeline

Before you can migrate anything, you need something to migrate from. Real-world migration projects start by documenting and understanding the legacy system's behavior.

In this step, you'll build a realistic legacy ETL pipeline in SQL Server with stored procedures that contain non-trivial business logic. Then you'll freeze the outputs and upload everything to Databricks so you have a stable baseline to diff against later.

In this step, get ready to:
  • Create a source database with realistic e-commerce seed data.
  • Build two stored procedures containing business logic that a migration must preserve.
  • Export all tables to Databricks Unity Catalog as frozen baseline data.
Create the source database and seed data

Every migration starts with a source system. You'll create a small e-commerce database that mirrors what you'd find in a real legacy environment: orders, customers, and payments.

  • Switch to SSMS and connect to your local SQL Server instance.
  • Open a new query window by clicking New Query in the toolbar.
  • Create the database and source tables by pasting this SQL and clicking Execute:
-- Create the legacy orders database
CREATE DATABASE legacy_orders;
GO

USE legacy_orders;
GO

-- Customers table
CREATE TABLE raw_customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

-- Orders table
CREATE TABLE raw_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    status VARCHAR(20),
    updated_at DATETIME
);

-- Payments table
CREATE TABLE raw_payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    payment_method VARCHAR(30),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME
);

What does this code do?

This creates a fresh legacy_orders database with three tables that form a typical e-commerce data model.

raw_customers holds customer profiles. raw_orders tracks order transactions with their status. raw_payments records individual payment attempts against each order.

  • Verify the tables were created by expanding Databases > legacy_orders > Tables in the Object Explorer pane.
  • You should see dbo.raw_customers, dbo.raw_orders, and dbo.raw_payments listed.
  • Now populate the tables with seed data by running this SQL in the same query window:
USE legacy_orders;
GO

-- Seed customers (15 rows)
INSERT INTO raw_customers (customer_id, first_name, last_name, email, created_at) VALUES
(1, 'Alice', 'Morgan', 'alice.morgan@example.com', '2024-01-05 09:00:00'),
(2, 'Bob', 'Chen', 'bob.chen@example.com', '2024-01-12 10:30:00'),
(3, 'Carla', 'Diaz', 'carla.diaz@example.com', '2024-02-01 14:00:00'),
(4, 'David', 'Kim', 'david.kim@example.com', '2024-02-10 08:15:00'),
(5, 'Eva', 'Novak', 'eva.novak@example.com', '2024-02-20 11:45:00'),
(6, 'Frank', 'Patel', 'frank.patel@example.com', '2024-03-01 16:00:00'),
(7, 'Grace', 'Wu', 'grace.wu@example.com', '2024-03-05 13:20:00'),
(8, 'Henry', 'Lopez', 'henry.lopez@example.com', '2024-03-14 09:30:00'),
(9, 'Iris', 'Tanaka', 'iris.tanaka@example.com', '2024-03-22 15:00:00'),
(10, 'Jake', 'Smith', 'jake.smith@example.com', '2024-04-01 10:00:00'),
(11, 'Karen', 'Jones', 'karen.jones@example.com', '2024-04-08 12:00:00'),
(12, 'Leo', 'Martinez', 'leo.martinez@example.com', '2024-04-15 08:45:00'),
(13, 'Mia', 'Anderson', 'mia.anderson@example.com', '2024-05-01 14:30:00'),
(14, 'Noah', 'Taylor', 'noah.taylor@example.com', '2024-05-10 09:15:00'),
(15, 'Olivia', 'Brown', 'olivia.brown@example.com', '2024-05-20 11:00:00');

-- Seed orders (50 rows across multiple customers and statuses)
INSERT INTO raw_orders (order_id, customer_id, order_date, status, updated_at) VALUES
(101, 1, '2024-01-10 10:00:00', 'completed', '2024-01-12 10:00:00'),
(102, 1, '2024-01-20 14:00:00', 'completed', '2024-01-22 14:00:00'),
(103, 2, '2024-01-14 09:30:00', 'completed', '2024-01-16 09:30:00'),
(104, 2, '2024-02-05 11:00:00', 'shipped', '2024-02-06 11:00:00'),
(105, 3, '2024-02-10 15:00:00', 'completed', '2024-02-12 15:00:00'),
(106, 3, '2024-02-18 08:00:00', 'returned', '2024-02-25 08:00:00'),
(107, 4, '2024-02-14 10:30:00', 'completed', '2024-02-16 10:30:00'),
(108, 4, '2024-02-28 13:00:00', 'completed', '2024-03-02 13:00:00'),
(109, 5, '2024-02-22 09:00:00', 'pending', '2024-02-22 09:00:00'),
(110, 5, '2024-03-01 16:00:00', 'completed', '2024-03-03 16:00:00'),
(111, 6, '2024-03-05 10:00:00', 'completed', '2024-03-07 10:00:00'),
(112, 6, '2024-03-12 14:30:00', 'shipped', '2024-03-13 14:30:00'),
(113, 7, '2024-03-08 11:00:00', 'completed', '2024-03-10 11:00:00'),
(114, 7, '2024-03-20 09:45:00', 'completed', '2024-03-22 09:45:00'),
(115, 8, '2024-03-15 15:00:00', 'returned', '2024-03-22 15:00:00'),
(116, 8, '2024-03-25 08:30:00', 'completed', '2024-03-27 08:30:00'),
(117, 9, '2024-03-28 12:00:00', 'completed', '2024-03-30 12:00:00'),
(118, 9, '2024-04-02 10:00:00', 'pending', '2024-04-02 10:00:00'),
(119, 10, '2024-04-05 14:00:00', 'completed', '2024-04-07 14:00:00'),
(120, 10, '2024-04-10 09:00:00', 'completed', '2024-04-12 09:00:00'),
(121, 11, '2024-04-12 11:30:00', 'shipped', '2024-04-13 11:30:00'),
(122, 11, '2024-04-18 15:00:00', 'completed', '2024-04-20 15:00:00'),
(123, 12, '2024-04-16 08:00:00', 'completed', '2024-04-18 08:00:00'),
(124, 12, '2024-04-22 13:00:00', 'returned', '2024-04-29 13:00:00'),
(125, 13, '2024-05-03 10:30:00', 'completed', '2024-05-05 10:30:00'),
(126, 13, '2024-05-08 14:00:00', 'completed', '2024-05-10 14:00:00'),
(127, 14, '2024-05-12 09:00:00', 'pending', '2024-05-12 09:00:00'),
(128, 14, '2024-05-18 16:00:00', 'completed', '2024-05-20 16:00:00'),
(129, 15, '2024-05-22 11:00:00', 'completed', '2024-05-24 11:00:00'),
(130, 15, '2024-05-28 08:45:00', 'shipped', '2024-05-29 08:45:00'),
(131, 1, '2024-03-10 10:00:00', 'completed', '2024-03-12 10:00:00'),
(132, 2, '2024-03-18 14:00:00', 'completed', '2024-03-20 14:00:00'),
(133, 3, '2024-04-05 09:30:00', 'completed', '2024-04-07 09:30:00'),
(134, 4, '2024-04-12 11:00:00', 'shipped', '2024-04-13 11:00:00'),
(135, 5, '2024-04-20 15:00:00', 'completed', '2024-04-22 15:00:00'),
(136, 6, '2024-04-25 08:00:00', 'completed', '2024-04-27 08:00:00'),
(137, 7, '2024-05-01 10:30:00', 'returned', '2024-05-08 10:30:00'),
(138, 8, '2024-05-05 13:00:00', 'completed', '2024-05-07 13:00:00'),
(139, 9, '2024-05-10 09:00:00', 'completed', '2024-05-12 09:00:00'),
(140, 10, '2024-05-15 16:00:00', 'pending', '2024-05-15 16:00:00'),
(141, 1, '2024-05-20 10:00:00', 'completed', '2024-05-22 10:00:00'),
(142, 3, '2024-05-25 14:30:00', 'completed', '2024-05-27 14:30:00'),
(143, 5, '2024-06-01 11:00:00', 'shipped', '2024-06-02 11:00:00'),
(144, 7, '2024-06-05 09:45:00', 'completed', '2024-06-07 09:45:00'),
(145, 9, '2024-06-10 15:00:00', 'completed', '2024-06-12 15:00:00'),
(146, 11, '2024-06-12 08:30:00', 'completed', '2024-06-14 08:30:00'),
(147, 13, '2024-06-15 12:00:00', 'pending', '2024-06-15 12:00:00'),
(148, 2, '2024-06-18 10:00:00', 'completed', '2024-06-20 10:00:00'),
(149, 4, '2024-06-20 14:00:00', 'completed', '2024-06-22 14:00:00'),
(150, 6, '2024-06-25 09:00:00', 'completed', '2024-06-27 09:00:00');

-- Seed payments (55 rows with varied methods and statuses including NULLs)
INSERT INTO raw_payments (payment_id, order_id, payment_method, amount, status, created_at) VALUES
(1001, 101, 'credit_card', 49.99, 'completed', '2024-01-10 10:05:00'),
(1002, 102, 'credit_card', 89.50, 'completed', '2024-01-20 14:05:00'),
(1003, 103, 'bank_transfer', 120.00, 'completed', '2024-01-14 09:35:00'),
(1004, 104, 'credit_card', 34.99, 'pending', '2024-02-05 11:05:00'),
(1005, 105, 'gift_card', 75.00, 'completed', '2024-02-10 15:05:00'),
(1006, 106, 'credit_card', 200.00, 'completed', '2024-02-18 08:05:00'),
(1007, 107, 'bank_transfer', 55.00, 'completed', '2024-02-14 10:35:00'),
(1008, 108, 'credit_card', 149.99, 'completed', '2024-02-28 13:05:00'),
(1009, 109, 'coupon', 25.00, NULL, '2024-02-22 09:05:00'),
(1010, 110, 'credit_card', 67.50, 'completed', '2024-03-01 16:05:00'),
(1011, 111, 'bank_transfer', 180.00, 'completed', '2024-03-05 10:05:00'),
(1012, 112, 'credit_card', 42.00, 'pending', '2024-03-12 14:35:00'),
(1013, 113, 'gift_card', 95.00, 'completed', '2024-03-08 11:05:00'),
(1014, 114, 'credit_card', 110.00, 'completed', '2024-03-20 09:50:00'),
(1015, 115, 'bank_transfer', 65.00, 'failed', '2024-03-15 15:05:00'),
(1016, 116, 'credit_card', 88.00, 'completed', '2024-03-25 08:35:00'),
(1017, 117, 'credit_card', 155.00, 'completed', '2024-03-28 12:05:00'),
(1018, 118, 'gift_card', 30.00, NULL, '2024-04-02 10:05:00'),
(1019, 119, 'credit_card', 72.50, 'completed', '2024-04-05 14:05:00'),
(1020, 120, 'bank_transfer', 195.00, 'completed', '2024-04-10 09:05:00'),
(1021, 121, 'credit_card', 58.00, 'pending', '2024-04-12 11:35:00'),
(1022, 122, 'credit_card', 130.00, 'completed', '2024-04-18 15:05:00'),
(1023, 123, 'coupon', 45.00, 'completed', '2024-04-16 08:05:00'),
(1024, 124, 'credit_card', 210.00, 'completed', '2024-04-22 13:05:00'),
(1025, 125, 'bank_transfer', 85.00, 'completed', '2024-05-03 10:35:00'),
(1026, 126, 'credit_card', 62.00, 'completed', '2024-05-08 14:05:00'),
(1027, 127, 'credit_card', 99.99, NULL, '2024-05-12 09:05:00'),
(1028, 128, 'gift_card', 140.00, 'completed', '2024-05-18 16:05:00'),
(1029, 129, 'credit_card', 77.50, 'completed', '2024-05-22 11:05:00'),
(1030, 130, 'bank_transfer', 53.00, 'pending', '2024-05-28 08:50:00'),
(1031, 131, 'credit_card', 165.00, 'completed', '2024-03-10 10:05:00'),
(1032, 132, 'credit_card', 48.00, 'completed', '2024-03-18 14:05:00'),
(1033, 133, 'bank_transfer', 92.00, 'completed', '2024-04-05 09:35:00'),
(1034, 134, 'credit_card', 38.50, 'pending', '2024-04-12 11:05:00'),
(1035, 135, 'gift_card', 110.00, 'completed', '2024-04-20 15:05:00'),
(1036, 136, 'credit_card', 73.00, 'completed', '2024-04-25 08:05:00'),
(1037, 137, 'bank_transfer', 225.00, 'failed', '2024-05-01 10:35:00'),
(1038, 138, 'credit_card', 56.00, 'completed', '2024-05-05 13:05:00'),
(1039, 139, 'credit_card', 82.50, 'completed', '2024-05-10 09:05:00'),
(1040, 140, 'coupon', 15.00, NULL, '2024-05-15 16:05:00'),
(1041, 141, 'credit_card', 198.00, 'completed', '2024-05-20 10:05:00'),
(1042, 142, 'bank_transfer', 67.00, 'completed', '2024-05-25 14:35:00'),
(1043, 143, 'credit_card', 44.50, 'pending', '2024-06-01 11:05:00'),
(1044, 144, 'gift_card', 125.00, 'completed', '2024-06-05 09:50:00'),
(1045, 145, 'credit_card', 93.00, 'completed', '2024-06-10 15:05:00'),
(1046, 146, 'bank_transfer', 158.00, 'completed', '2024-06-12 08:35:00'),
(1047, 147, 'credit_card', 71.00, NULL, '2024-06-15 12:05:00'),
(1048, 148, 'credit_card', 105.00, 'completed', '2024-06-18 10:05:00'),
(1049, 149, 'gift_card', 87.50, 'completed', '2024-06-20 14:05:00'),
(1050, 150, 'bank_transfer', 142.00, 'completed', '2024-06-25 09:05:00'),
(1051, 101, 'bank_transfer', 25.00, 'failed', '2024-01-10 10:03:00'),
(1052, 106, 'credit_card', 200.00, 'failed', '2024-02-18 08:02:00'),
(1053, 115, 'credit_card', 65.00, 'completed', '2024-03-15 15:10:00'),
(1054, 124, 'bank_transfer', 210.00, 'failed', '2024-04-22 13:02:00'),
(1055, 137, 'credit_card', 225.00, 'completed', '2024-05-01 10:40:00');

What does this seed data include?

The dataset is designed to exercise the stored procedures' business logic.

  • Orders span multiple statuses: completed, shipped, pending, returned.
  • Payments use four methods: credit_card, bank_transfer, gift_card, coupon.
  • Some payments have NULL statuses, which the stored procedures must handle.
  • Some orders have multiple payment attempts (failed then succeeded), testing aggregation logic.
  • Dates span January through June 2024, crossing the 15th of each month to test fiscal calendar logic.
  • Verify the row counts by running:
SELECT 'raw_customers' AS table_name, COUNT(*) AS row_count FROM raw_customers
UNION ALL
SELECT 'raw_orders', COUNT(*) FROM raw_orders
UNION ALL
SELECT 'raw_payments', COUNT(*) FROM raw_payments;
  • You should see 15 customers, 50 orders, and 55 payments in the results grid.

Not seeing the tables or getting an error?

  • Make sure you clicked Execute (or pressed F5) after pasting each script.
  • If you see "Database already exists", drop it first with DROP DATABASE legacy_orders; and re-run.
  • Check that USE legacy_orders; appears before the INSERT statements. Without it, the data goes into the wrong database.

Help me troubleshoot my SQL Server database creation

Create two stored procedures that transform the data

Stored procedures are where legacy business logic lives. These two procedures contain the exact logic your dbt models will need to replicate later: NULL handling, conditional aggregation, and fiscal calendar calculations.

  • Create the first stored procedure by running this SQL:
USE legacy_orders;
GO

CREATE PROCEDURE sp_customer_orders
AS
BEGIN
    -- Join customers with their orders and payments
    -- Only completed payments count toward lifetime value
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        COUNT(DISTINCT o.order_id) AS total_orders,
        SUM(
            CASE
                WHEN COALESCE(p.status, 'unknown') = 'completed'
                THEN p.amount
                ELSE 0
            END
        ) AS lifetime_value
    INTO customer_orders_output
    FROM raw_customers c
    LEFT JOIN raw_orders o ON c.customer_id = o.customer_id
    LEFT JOIN raw_payments p ON o.order_id = p.order_id
    GROUP BY c.customer_id, c.first_name, c.last_name;
END;
GO

What business logic does this procedure contain?

  • COALESCE(p.status, 'unknown') handles NULL payment statuses by treating them as 'unknown' rather than letting them silently pass the comparison.
  • The CASE expression ensures only payments with status completed count toward lifetime value. Pending, failed, and NULL payments contribute zero.
  • COUNT(DISTINCT o.order_id) avoids inflating order counts when an order has multiple payment attempts.
  • SELECT INTO creates the output table automatically. This is typical of legacy ETL: the procedure creates its own output.
  • Create the second stored procedure by running this SQL:
USE legacy_orders;
GO

CREATE PROCEDURE sp_order_summary
AS
BEGIN
    -- Aggregate orders by status and payment method
    -- Calculate daily revenue with running totals
    -- Apply fiscal calendar logic: fiscal month starts on the 15th
    SELECT
        o.status AS order_status,
        p.payment_method,
        CAST(o.order_date AS DATE) AS order_day,
        CASE
            WHEN DAY(o.order_date) >= 15
            THEN MONTH(o.order_date)
            ELSE CASE
                WHEN MONTH(o.order_date) = 1 THEN 12
                ELSE MONTH(o.order_date) - 1
            END
        END AS fiscal_month,
        SUM(p.amount) AS daily_revenue,
        SUM(SUM(p.amount)) OVER (
            PARTITION BY o.status, p.payment_method
            ORDER BY CAST(o.order_date AS DATE)
        ) AS running_total
    INTO order_summary_output
    FROM raw_orders o
    LEFT JOIN raw_payments p ON o.order_id = p.order_id
    GROUP BY
        o.status,
        p.payment_method,
        CAST(o.order_date AS DATE),
        CASE
            WHEN DAY(o.order_date) >= 15
            THEN MONTH(o.order_date)
            ELSE CASE
                WHEN MONTH(o.order_date) = 1 THEN 12
                ELSE MONTH(o.order_date) - 1
            END
        END;
END;
GO

What makes the fiscal calendar logic tricky?

The business rule is: a fiscal month starts on the 15th and ends on the 14th of the following calendar month. So January 14th belongs to fiscal month 12 (December), while January 15th belongs to fiscal month 1 (January).

This is the kind of non-trivial business logic that AI translation tools sometimes get wrong. Later, your Datafold diff will catch any mismatch in the fiscal month assignment.

Why these two procedures specifically?

Together they cover the common challenges in real stored procedure migrations: NULL handling, conditional aggregation, window functions, and custom business date logic. These are exactly the patterns that Datafold's Migration Agent decomposes when translating stored procedures into modular dbt models.

  • Verify both procedures exist by running:
SELECT name, type_desc
FROM sys.procedures
WHERE name IN ('sp_customer_orders', 'sp_order_summary');
  • You should see two rows, both with type SQL_STORED_PROCEDURE.

Seeing a syntax error?

  • Make sure you have GO on its own line after each CREATE PROCEDURE block. GO is a batch separator that SSMS requires between certain statements.
  • If you see "There is already an object named 'customer_orders_output'", the procedure was already executed once. Run DROP TABLE IF EXISTS customer_orders_output; before re-executing.

Help me fix my stored procedure creation error

Run the stored procedures and export results to Databricks

Now you'll execute the procedures, export everything as CSVs, and upload to Databricks Unity Catalog. This simulates what Datafold's Source Aligner does: it freezes the legacy outputs so you can compare them against the new system later.

  • Execute both stored procedures by running:
USE legacy_orders;
GO

-- Execute the stored procedures to generate output tables
EXEC sp_customer_orders;
GO

EXEC sp_order_summary;
GO

What does this do?

Each procedure creates its own output table using SELECT INTO. After execution, you'll have customer_orders_output and order_summary_output as new tables in the legacy_orders database.

  • Verify the output tables have data by running:
SELECT 'customer_orders_output' AS table_name, COUNT(*) AS row_count FROM customer_orders_output
UNION ALL
SELECT 'order_summary_output', COUNT(*) FROM order_summary_output;
  • You should see 15 rows in customer_orders_output (one per customer) and multiple rows in order_summary_output (one per status/method/day combination).

Next, you'll export each table as a CSV file so you can upload them to Databricks.

  • First, ensure column headers are included in exports. Go to Tools > Options > Query Results > SQL Server > Results to Grid.
  • Check the box for Include column headers when copying or saving the result. Click OK.
  • Open a new query window (the setting only takes effect in new windows).
  • Run SELECT * FROM legacy_orders.dbo.raw_customers; to display results in the grid.
  • Right-click anywhere in the results grid and select Save Results As....
  • Choose a folder on your Desktop, name the file raw_customers.csv, set Save as type to CSV, and click Save.
  • Repeat this process for all five tables, saving each as a separate CSV file:
  • raw_customers.csv (from SELECT * FROM raw_customers).
  • raw_orders.csv (from SELECT * FROM raw_orders).
  • raw_payments.csv (from SELECT * FROM raw_payments).
  • customer_orders_output.csv (from SELECT * FROM customer_orders_output).
  • order_summary_output.csv (from SELECT * FROM order_summary_output).

Speed up the export

You can run all five SELECT statements in one query window separated by GO. SSMS will display each result set in its own tab within the results pane. Right-click each tab's grid to save individually.

Now upload these CSV files to Databricks as your frozen baseline tables.

  • Switch to your Databricks workspace in the browser.
  • Open the SQL Editor by clicking SQL Editor in the left sidebar.
  • Create the legacy catalog and schemas by running this SQL:
-- Create the legacy catalog with two schemas
CREATE CATALOG IF NOT EXISTS legacy;

CREATE SCHEMA IF NOT EXISTS legacy.baseline;
CREATE SCHEMA IF NOT EXISTS legacy.raw;

What is the three-level namespace?

Unity Catalog organizes data in three levels: catalog.schema.table. Think of a catalog as a database, a schema as a folder within that database, and tables as the objects inside.

You're creating two schemas: baseline for frozen stored procedure outputs (used for diffing) and raw for the source tables (used as dbt sources).

  • Now upload the raw source tables. Click New > Add or upload data in the top navigation.
  • Click Create or modify a table.
  • Drag and drop (or browse to) raw_customers.csv from your Desktop.
  • In the catalog dropdown, select legacy. In the schema dropdown, select raw.
  • Set the table name to customers.
  • Verify the preview looks correct (columns and data types detected), then click Create.
  • Repeat this upload process for raw_orders.csv (table name: orders, schema: raw) and raw_payments.csv (table name: payments, schema: raw).
  • Upload customer_orders_output.csv to catalog legacy, schema baseline, table name customer_orders.
  • Upload order_summary_output.csv to catalog legacy, schema baseline, table name order_summary.

Why upload to two different schemas?

The legacy.raw tables are your dbt source data. Your dbt models will read from these tables and transform them.

The legacy.baseline tables are your "frozen legacy outputs." These are what your stored procedures produced. Datafold will diff your dbt model output against these baselines to prove parity.

  • Verify all five tables are visible by clicking Catalog in the left sidebar and expanding legacy > raw and legacy > baseline.
  • You should see customers, orders, and payments under raw, and customer_orders and order_summary under baseline.

Tables not appearing after upload?

  • Make sure your SQL Warehouse is running. The upload requires an active compute resource to preview and create tables.
  • If the catalog legacy doesn't appear in the dropdown, run the CREATE CATALOG SQL first in the SQL Editor, then try the upload again.
  • Check that you selected the correct schema (raw for source tables, baseline for outputs) during upload.

Help me upload CSV files to Databricks Unity Catalog

Your legacy pipeline is locked and loaded. Next up, you'll use AI to translate these stored procedures into modular dbt models and deploy them to Databricks.

Translate Stored Procedures to dbt Models

In the previous step, you built a legacy ETL pipeline in SQL Server and exported its outputs to Databricks as frozen baseline tables.

Now comes the core of any migration delivery: translating that legacy logic into clean, modular dbt models that produce identical results on a modern platform. You will initialize a dbt project, connect it to Databricks, and use AI-assisted translation to convert your stored procedures into staging and mart models following best-practice conventions.

In this step, get ready to:
  • Initialize a dbt project and configure the Databricks connection.
  • Use Claude to translate stored procedures into modular dbt models (staging + mart layers).
  • Deploy the models to Databricks Unity Catalog and verify row count parity.
Initialize the dbt project and configure the Databricks connection

Before you can write any models, dbt needs a project scaffold and a working connection to your Databricks warehouse. The dbt init command creates the project folder, and profiles.yml tells dbt how to reach your Unity Catalog workspace.

  • Initialize your dbt project by running this command in the Windsurf terminal:
dbt init migration_project

What will dbt init ask me?

The dbt init command is interactive. It will ask you which database adapter to use. Select databricks from the list.

It will also prompt for connection details like hostname and token. You can skip these for now because you will configure profiles.yml manually in the next chunk. Use the --skip-profile-setup flag if you prefer, or simply provide placeholder values during the prompts.

  • Move into the new project directory:
cd migration_project

dbt stores connection credentials in a file called profiles.yml at ~/.dbt/profiles.yml by default. You need to configure this file with your Databricks workspace hostname, SQL Warehouse HTTP path, and Personal Access Token.

  • Open (or create) ~/.dbt/profiles.yml and replace its contents with the following configuration:
migration_project:
  target: dev
  outputs:
    dev:
      type: databricks
      host: [[DATABRICKS_HOST="your-workspace.cloud.databricks.com"]]
      http_path: [[HTTP_PATH="/sql/1.0/warehouses/your-warehouse-id"]]
      token: your-databricks-personal-access-token
      catalog: migration
      schema: marts
      threads: 4

What does this configuration do?

  • type: databricks tells dbt to use the dbt-databricks adapter.
  • host is your Databricks workspace hostname (e.g., dbc-xxxxx.cloud.databricks.com) from your workspace URL.
  • http_path is the SQL Warehouse connection path you noted in Step 1 from the warehouse connection details.
  • token is the Personal Access Token you generated earlier.
  • catalog: migration and schema: marts set the three-level namespace where dbt will materialize tables: migration.marts.<model_name>.
  • Save profiles.yml.

The migration catalog does not exist yet in your Databricks workspace. You need to create it before dbt can write tables there.

  • Switch to your Databricks workspace in the browser.
  • Open the SQL Editor from the left sidebar.
  • Run this SQL statement to create the catalog:
CREATE CATALOG IF NOT EXISTS migration

What is a catalog in Unity Catalog?

Unity Catalog uses a three-level namespace: catalog.schema.table. A catalog is the top-level container. You already have a legacy catalog holding your baseline and raw data. The new migration catalog will hold the dbt-produced tables, keeping legacy and new outputs cleanly separated.

  • Confirm the catalog was created by checking the **Catalog** panel in the left sidebar of your Databricks workspace.

Now verify that dbt can reach Databricks with your credentials.

  • Return to the Windsurf terminal (still inside the migration_project folder) and run:
dbt debug
  • You should see All checks passed! at the bottom of the output, confirming dbt can connect to your Databricks SQL Warehouse.

dbt debug showing connection errors?

  • Double-check that your host value does not include https:// at the start. It should be the bare hostname only.
  • Verify that your SQL Warehouse is in a Running state in the Databricks workspace. It may have auto-stopped.
  • Confirm your Personal Access Token has not expired.

Help me debug my dbt connection to Databricks

Use Claude to translate stored procedures into dbt models

With your dbt project connected to Databricks, it is time to translate your legacy stored procedures into modular dbt models. You will use AI-assisted translation to convert each stored procedure, then organize the output into staging and mart layers following dbt conventions.

The staging layer selects and lightly transforms raw data from source tables. The mart layer contains the business logic that mirrors your stored procedures.

Why staging and mart layers?

Datafold's Migration Agent (DMA) outputs translated code in this pattern: staging models handle source casting and renaming, while mart models contain the business logic. This separation makes each transformation testable and reusable.

Instead of a single monolithic query, you get modular pieces that are easier to validate independently with Data Diff.

First, define where dbt should read source data from. This schema.yml file tells dbt about the raw tables in your legacy catalog.

  • Create a file at models/schema.yml inside your migration_project folder with the following content:
version: 2

sources:
  - name: legacy_raw
    database: legacy
    schema: raw
    tables:
      - name: orders
      - name: customers
      - name: payments

What does this file do?

The sources block defines external tables that dbt can reference using the {{ source() }} function. Setting database: legacy and schema: raw points dbt at your legacy.raw.orders, legacy.raw.customers, and legacy.raw.payments tables in Unity Catalog.

  • Save models/schema.yml.

Now create the staging models. These are thin select statements that pull from your raw sources and apply light transformations like renaming or NULL handling.

  • Create a models/staging/ directory inside your project:
mkdir -p models/staging
  • Create models/staging/stg_orders.sql with the following content:
-- Staging model: lightly transform raw order data
with source as (
    select * from {{ source('legacy_raw', 'orders') }}
)

select
    order_id,
    customer_id,
    order_date,
    status,
    updated_at
from source
  • Create models/staging/stg_customers.sql with:
-- Staging model: select and rename raw customer fields
with source as (
    select * from {{ source('legacy_raw', 'customers') }}
)

select
    customer_id,
    first_name,
    last_name,
    email,
    created_at
from source
  • Create models/staging/stg_payments.sql with:
-- Staging model: handle NULL payment statuses
with source as (
    select * from {{ source('legacy_raw', 'payments') }}
)

select
    payment_id,
    order_id,
    payment_method,
    amount,
    coalesce(status, 'unknown') as status,
    created_at
from source

What do these staging models do?

  • stg_orders.sql selects core order fields (ID, customer, date, status) from the raw source.
  • stg_customers.sql selects customer identity fields.
  • stg_payments.sql handles NULL payment statuses by replacing them with 'unknown' using coalesce(). This mirrors the NULL handling in your original sp_customer_orders stored procedure.
  • Save all three staging files.

Now for the mart layer. This is where the real business logic lives. You will use Claude to translate your stored procedures into dbt SQL.

  • Open Windsurf's AI chat (or use Claude directly) and paste your sp_customer_orders stored procedure with this prompt:
Translate this SQL Server stored procedure into modular dbt models
following staging/mart conventions. Use CTEs, ref() functions, and
Databricks SQL dialect. Preserve all business logic exactly.

Iterate if needed

AI translation rarely produces a perfect result on the first attempt. Compare the logic in Claude's output against your original stored procedure. If the NULL handling or lifetime value calculation looks different, ask Claude to fix those specific sections.

  • Create a models/marts/ directory:
mkdir -p models/marts
  • Create models/marts/mart_customer_orders.sql with the translated logic. The model should match this structure (adjust based on Claude's output):
-- Mart model: replicate sp_customer_orders logic
-- Joins customers with orders, calculates lifetime value (completed payments only)
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

-- Only completed payments count toward lifetime value
completed_payments as (
    select
        orders.order_id,
        orders.customer_id,
        payments.amount
    from orders
    left join payments on orders.order_id = payments.order_id
    where payments.status = 'completed'
),

-- Aggregate order count and lifetime value per customer
customer_metrics as (
    select
        customer_id,
        count(distinct order_id) as total_orders,
        coalesce(sum(amount), 0) as lifetime_value
    from completed_payments
    group by customer_id
)

select
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    customers.email,
    coalesce(customer_metrics.total_orders, 0) as total_orders,
    coalesce(customer_metrics.lifetime_value, 0) as lifetime_value
from customers
left join customer_metrics
    on customers.customer_id = customer_metrics.customer_id

What does this model do?

  • ref('stg_customers') and ref('stg_orders') reference the staging models you just created. dbt automatically resolves these to the correct tables.
  • The completed_payments CTE filters for only status = 'completed', matching the business rule in sp_customer_orders.
  • The final select left-joins customer metrics so customers with zero orders still appear with 0 values (matching the stored procedure's NULL handling).
  • Save models/marts/mart_customer_orders.sql.

Now repeat the AI translation for sp_order_summary. This stored procedure is more complex because it includes fiscal calendar logic (fiscal month starts on the 15th) and running totals.

  • Feed your sp_order_summary stored procedure to Claude with the same translation prompt.
  • Create models/marts/mart_order_summary.sql with the translated logic. The model should match this structure:
-- Mart model: replicate sp_order_summary logic
-- Aggregates orders by status/payment method, daily revenue with running totals
-- Fiscal calendar: fiscal month starts on the 15th
with orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

-- Apply fiscal calendar logic (fiscal month starts on the 15th)
orders_with_fiscal as (
    select
        order_id,
        order_date,
        status,
        case
            when day(order_date) >= 15
            then date_trunc('month', order_date)
            else date_trunc('month', add_months(order_date, -1))
        end as fiscal_month
    from orders
),

-- Join with payments for revenue calculation
order_payments as (
    select
        orders_with_fiscal.order_id,
        orders_with_fiscal.order_date,
        orders_with_fiscal.status,
        orders_with_fiscal.fiscal_month,
        payments.payment_method,
        payments.amount
    from orders_with_fiscal
    left join payments
        on orders_with_fiscal.order_id = payments.order_id
),

-- Aggregate by status and payment method with daily revenue
daily_summary as (
    select
        order_date,
        fiscal_month,
        status,
        payment_method,
        count(distinct order_id) as order_count,
        coalesce(sum(amount), 0) as daily_revenue
    from order_payments
    group by order_date, fiscal_month, status, payment_method
)

select
    order_date,
    fiscal_month,
    status,
    payment_method,
    order_count,
    daily_revenue,
    sum(daily_revenue) over (
        partition by fiscal_month
        order by order_date
        rows between unbounded preceding and current row
    ) as running_total
from daily_summary
order by order_date, status, payment_method

What does the fiscal calendar logic do?

The case expression assigns each order to a fiscal month. If the order date is on or after the 15th, it belongs to the current calendar month's fiscal period. If it is before the 15th, it belongs to the previous month's fiscal period.

The running_total window function calculates cumulative revenue within each fiscal month, ordered by date. This mirrors the running total logic in your original sp_order_summary stored procedure.

  • Save models/marts/mart_order_summary.sql.

Claude's output looks different from the reference?

AI-generated SQL may use slightly different CTE names, aliases, or function syntax. The critical points to verify are:

  • Only completed payments count toward lifetime value in mart_customer_orders.
  • Fiscal month calculation uses day >= 15 boundary in mart_order_summary.
  • All models use {{ ref() }} and {{ source() }} functions (not hardcoded table names).
  • SQL functions use Databricks dialect (day(), add_months(), date_trunc()).

Help me compare my AI-translated dbt model against the original stored procedure

You can also delete the sample model files that dbt init created in the models/example/ directory. They are not needed for this project.

  • Remove the example models by running:
rm -rf models/example

✔️ Awesome, I've got everything!

Great. Make sure all files are saved before moving on to deployment.

ⓧ I'd like to double check the full code

Here is the complete file structure you should have at this point.

version: 2

sources:
  - name: legacy_raw
    database: legacy
    schema: raw
    tables:
      - name: orders
      - name: customers
      - name: payments
-- Staging model: lightly transform raw order data
with source as (
    select * from {{ source('legacy_raw', 'orders') }}
)

select
    order_id,
    customer_id,
    order_date,
    status,
    updated_at
from source
-- Staging model: select and rename raw customer fields
with source as (
    select * from {{ source('legacy_raw', 'customers') }}
)

select
    customer_id,
    first_name,
    last_name,
    email,
    created_at
from source
-- Staging model: handle NULL payment statuses
with source as (
    select * from {{ source('legacy_raw', 'payments') }}
)

select
    payment_id,
    order_id,
    payment_method,
    amount,
    coalesce(status, 'unknown') as status,
    created_at
from source
-- Mart model: replicate sp_customer_orders logic
-- Joins customers with orders, calculates lifetime value (completed payments only)
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

-- Only completed payments count toward lifetime value
completed_payments as (
    select
        orders.order_id,
        orders.customer_id,
        payments.amount
    from orders
    left join payments on orders.order_id = payments.order_id
    where payments.status = 'completed'
),

-- Aggregate order count and lifetime value per customer
customer_metrics as (
    select
        customer_id,
        count(distinct order_id) as total_orders,
        coalesce(sum(amount), 0) as lifetime_value
    from completed_payments
    group by customer_id
)

select
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    customers.email,
    coalesce(customer_metrics.total_orders, 0) as total_orders,
    coalesce(customer_metrics.lifetime_value, 0) as lifetime_value
from customers
left join customer_metrics
    on customers.customer_id = customer_metrics.customer_id
-- Mart model: replicate sp_order_summary logic
-- Aggregates orders by status/payment method, daily revenue with running totals
-- Fiscal calendar: fiscal month starts on the 15th
with orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

-- Apply fiscal calendar logic (fiscal month starts on the 15th)
orders_with_fiscal as (
    select
        order_id,
        order_date,
        status,
        case
            when day(order_date) >= 15
            then date_trunc('month', order_date)
            else date_trunc('month', add_months(order_date, -1))
        end as fiscal_month
    from orders
),

-- Join with payments for revenue calculation
order_payments as (
    select
        orders_with_fiscal.order_id,
        orders_with_fiscal.order_date,
        orders_with_fiscal.status,
        orders_with_fiscal.fiscal_month,
        payments.payment_method,
        payments.amount
    from orders_with_fiscal
    left join payments
        on orders_with_fiscal.order_id = payments.order_id
),

-- Aggregate by status and payment method with daily revenue
daily_summary as (
    select
        order_date,
        fiscal_month,
        status,
        payment_method,
        count(distinct order_id) as order_count,
        coalesce(sum(amount), 0) as daily_revenue
    from order_payments
    group by order_date, fiscal_month, status, payment_method
)

select
    order_date,
    fiscal_month,
    status,
    payment_method,
    order_count,
    daily_revenue,
    sum(daily_revenue) over (
        partition by fiscal_month
        order by order_date
        rows between unbounded preceding and current row
    ) as running_total
from daily_summary
order by order_date, status, payment_method
Deploy models to Databricks

Your models are written. Now materialize them as tables in Databricks by running the full dbt pipeline.

  • Run dbt to compile and execute all models:
dbt run
  • You should see output showing each model being created successfully, ending with a summary like Completed successfully. Done. PASS=5 (five models: three staging plus two marts).

What does dbt run do?

dbt compiles each .sql model file into executable SQL, resolves {{ ref() }} and {{ source() }} functions into fully qualified table names, and runs the resulting queries against your Databricks SQL Warehouse. The output tables land in migration.marts because that is what your profiles.yml specifies.

dbt run failing with SQL errors?

  • If you see a column-not-found error, check that the column names in your staging models match the actual columns in your legacy.raw tables. Open the Databricks SQL Editor and run DESCRIBE legacy.raw.orders to verify column names.
  • If you see a permission error, confirm the migration catalog was created successfully and your token has write access.
  • If a function like day() or add_months() is not recognized, ensure your model uses Databricks SQL syntax (not SQL Server syntax like DATEPART or DATEADD).

Help me fix SQL errors in my dbt models running on Databricks

  • Switch to your Databricks workspace and open the Catalog panel in the left sidebar.
  • Expand migration > marts and verify you see mart_customer_orders and mart_order_summary listed as tables.

Finally, do a quick sanity check on row counts to confirm the translation preserved all records.

  • In the Databricks SQL Editor, run these queries to compare row counts:
-- Compare row counts between legacy baseline and new dbt models
SELECT 'legacy_customer_orders' as source, COUNT(*) as row_count
FROM legacy.baseline.customer_orders
UNION ALL
SELECT 'migration_customer_orders', COUNT(*)
FROM migration.marts.mart_customer_orders
UNION ALL
SELECT 'legacy_order_summary', COUNT(*)
FROM legacy.baseline.order_summary
UNION ALL
SELECT 'migration_order_summary', COUNT(*)
FROM migration.marts.mart_order_summary
  • The row counts for each pair should match. If the legacy baseline has 50 rows in customer_orders, your mart_customer_orders should also have 50 rows.

Row counts don't match?

  • If mart_customer_orders has fewer rows than the baseline, your join logic may be using an inner join instead of a left join. Verify the final select uses left join.
  • If mart_order_summary has more rows than the baseline, check for duplicate join keys. Ensure the group by clause includes all necessary dimensions.
  • Small differences (1-2 rows) may indicate edge cases in your fiscal calendar logic. Compare a few sample rows to identify the discrepancy.

Help me debug why my dbt model row count differs from the legacy baseline

Your stored procedures are now running as modular dbt models on Databricks. Row counts match, but that is just a surface-level check. Next up, you will use Datafold's Data Diff to perform value-level comparison across every row and column to prove true parity.

Validate Parity with Datafold Data Diff

Your dbt models are deployed and row counts line up. But row counts alone are a weak signal. Two tables can have the same number of rows while every value inside is wrong.

In this step, you will use Datafold's Data Diff to perform a value-level comparison between your legacy baseline tables and the new dbt models. This is the core of the translate-diff-repeat loop that powers real migration validation.

In this step, get ready to:
  • Create a cross-table data diff between your legacy baseline and new dbt model.
  • Analyze discrepancies and fix your dbt models until parity is achieved.
  • Export diff evidence as stakeholder sign-off artifacts.
Create a cross-table data diff in Datafold

A data diff is a value-level comparison between two tables. It checks every row and column, surfacing exactly where the data diverges.

  • Return to Datafold Cloud in your browser.
  • Navigate to the Data Diff section in the left sidebar.
  • Click to create a new data diff.
  • For the diff type, select Table to compare data directly from database tables.
  • Set the first dataset (Main) to legacy.baseline.customer_orders in your Databricks connection.
  • Set the second dataset (Test) to migration.marts.mart_customer_orders.
  • Under Primary key, enter customer_id as the column that uniquely identifies each row.
  • Under Columns to compare, select all columns.
  • Run the diff.

What is happening under the hood?

Because both tables live in the same Databricks workspace, Datafold runs an in-database diff. It executes SQL JOIN queries and aggregate queries directly inside your warehouse to compare rows, values, and columns without moving data out.

After a few seconds, Datafold will display the diff results across several tabs.

  • Check the Overview tab for the high-level diff status and runtime.
  • Check the Columns tab to see per-column status indicators showing whether each column is identical or has a percentage of differing values.
  • Check the Values tab to see specific rows where at least one column value differs between the two datasets.

What do the results tabs tell me?

The Overview gives you a pass/fail at a glance. The Columns tab pinpoints which specific columns have discrepancies. The Values tab shows the actual differing values side by side so you can diagnose root causes.

The Primary keys tab highlights rows that exist in one table but not the other, which would indicate missing or extra rows.

Diff won't run or shows a connection error?

Confirm your Databricks SQL Warehouse is running. If it timed out due to inactivity, restart it from the Databricks workspace.

Also verify that the Datafold data source connection uses the same hostname, HTTP path, and token you configured earlier.

Help me troubleshoot why my Datafold diff won't connect to Databricks.

Analyze and fix discrepancies (the translate-diff-repeat loop)

If your diff shows 100% parity on the first run, congratulations. But in most real migrations, the first diff reveals discrepancies. This is expected and exactly how the translate-diff-repeat workflow is designed to work.

  • In the diff results, click the Columns tab to identify which columns have differences.
  • Click the Values tab to inspect specific row-level mismatches.
  • Note the pattern of discrepancies. Common issues in SQL Server to Databricks translations include:

Why do discrepancies happen even with AI translation?

AI translation gets 80-90% of the logic right on the first pass. The remaining discrepancies come from subtle dialect differences between SQL Server and Databricks SQL. This is why Datafold's Migration Agent uses the translate-diff-repeat loop: each iteration narrows the gap until parity is confirmed.

  • Switch back to Windsurf and open the dbt model file that corresponds to the failing column (e.g., mart_customer_orders.sql or mart_order_summary.sql).
  • Fix the translation issue based on what the diff revealed. For example, if lifetime_value is off due to NULL handling, adjust your COALESCE logic to match the original stored procedure's behavior.
  • Save your changes.
  • Re-deploy the fixed model by running this command:
dbt run

Why a full dbt run instead of a targeted one?

With a small project like this, dbt run re-materializes all models in seconds. In larger projects, you would use dbt run --select mart_customer_orders to target only the changed model.

  • Return to Datafold and re-run the data diff (you can clone the previous diff or create a new one).
  • Check whether the discrepancy is resolved in the new results.
  • Repeat this cycle (fix model, run dbt, re-diff) until you achieve 100% parity or can document any remaining differences as accepted.

Once your customer_orders diff shows parity, repeat the entire process for the second model.

  • Create a new data diff with legacy.baseline.order_summary as Main and migration.marts.mart_order_summary as Test.
  • Set the primary key to the appropriate column for this table (the composite key or unique identifier used by your order summary).
  • Run the diff and iterate on fixes until parity is achieved.

Set tolerance for floating-point columns

If your revenue or lifetime value columns use FLOAT types, you may see tiny differences (e.g., 0.0000001) due to floating-point arithmetic. Set a tolerance level in Datafold's diff configuration to treat these as equal. This avoids flagging inconsequential precision noise.

Still seeing mismatches after fixing?

Make sure you saved the file in Windsurf before running dbt run. Unsaved edits are not picked up.

Also confirm the SQL Warehouse finished processing the new run before re-diffing. Check the dbt output for a successful completion message.

Help me debug why my dbt model still shows value mismatches after I fixed the SQL logic.

Export diff evidence for delivery

Once both diffs show 100% parity (or documented accepted discrepancies), you need to preserve this evidence. These reports become the auditable proof that your migration is correct.

  • In Datafold, open your completed customer_orders diff results.
  • Take a screenshot of the Overview tab showing the parity status. Save this as your first delivery artifact.
  • Copy the URL of the diff from your browser's address bar. You will include this link in your migration PR later.
  • Repeat for your order_summary diff: screenshot the Overview and copy the URL.

Why screenshots and URLs?

Datafold persists diff history, so the URL is a permanent link to your results that stakeholders can review anytime. The screenshot provides a quick visual confirmation for your PR description. In production migrations, Datafold generates these links automatically in CI/CD PR comments.

  • If any discrepancies were accepted (e.g., timestamp precision differences between DATETIME and TIMESTAMP), document them now in a text file or note. Record:

Download CSV for offline evidence

You can also download diff results as a CSV directly from the Datafold app. This gives you an offline artifact to attach to your migration documentation if stakeholders prefer spreadsheet-format evidence.

You now have auditable proof that your dbt models produce identical output to the legacy stored procedures. Next up, you will package everything into a professional GitHub PR that mirrors Datafold's customer delivery workflow.

Deliver as a GitHub PR

Your dbt models are deployed and validated. Datafold confirms 100% parity between the legacy stored procedures and the new models.

The final piece of a professional migration delivery is packaging everything into an auditable artifact that stakeholders can review and approve. In this step, you will initialize a Git repository, push your dbt project to GitHub, and open a pull request with Datafold diff evidence attached.

In this step, get ready to:
  • Initialize a Git repository and push to GitHub.
  • Create a migration PR with validation evidence.
  • Review and merge the PR as a delivery milestone.
Initialize the Git repository and push to GitHub

Version control turns your local dbt project into a shareable, reviewable artifact. You will create a .gitignore to keep credentials out of the repository, then push everything to GitHub.

  • Initialize a Git repository inside your dbt project by running this command:
git init
  • Create a .gitignore file to exclude sensitive and generated files by running:
echo "target/
logs/
.venv/
profiles.yml" > .gitignore

Why exclude profiles.yml?

Your profiles.yml contains your Databricks personal access token. Committing it to a public repository would expose your credentials. The .gitignore ensures Git never tracks it.

  • Stage all project files and create your initial commit by running:
git add .
git commit -m "Initial commit: dbt migration project"
  • Open your browser and go to GitHub.
  • Click the + icon in the top right, then select New repository.
  • Enter sql-server-to-databricks-migration as the repository name.
  • Leave all initialization options unchecked (no README, no .gitignore, no license).
  • Click Create repository.
  • Connect your local repository to GitHub and push by running these commands:
git remote add origin [[YOUR_REPO_URL="https://github.com/YOUR_USERNAME/sql-server-to-databricks-migration.git"]]
git branch -M main
git push -u origin main

What do these commands do?

  • git remote add origin links your local repo to the GitHub remote.
  • git branch -M main renames the default branch to main.
  • git push -u origin main uploads all committed files and sets the upstream tracking branch.
  • Refresh your GitHub repository page in the browser.
  • You should see your dbt project files (models/, dbt_project.yml, .gitignore) listed in the repository. Confirm that profiles.yml is NOT visible.

Push rejected or authentication error?

  • If you see a permission denied error, make sure you are authenticated with GitHub. Run gh auth login if you have GitHub CLI installed, or configure a personal access token for HTTPS.
  • If the push is rejected because the remote already has commits, you may have initialized the repo with a README. Delete the GitHub repo, recreate it without initialization, and try again.

Why can't I push to GitHub?

Create the migration PR with validation evidence

Datafold delivers translated code to customers as pull requests with attached data diff links. You will mirror this workflow by creating a feature branch, adding a migration report, and opening a PR with your validation evidence.

  • Create and switch to a feature branch by running:
git checkout -b migration/customer-orders-pipeline
  • Create a MIGRATION_REPORT.md file at the project root. This documents the entire migration for stakeholder review.
  • Add the following content to MIGRATION_REPORT.md:
# Migration Report: SQL Server to Databricks

## Source System
- **Platform**: SQL Server (on-premises)
- **Objects**: 2 stored procedures (`sp_customer_orders`, `sp_order_summary`)
- **Database**: `legacy_orders`

## Target System
- **Platform**: Databricks Unity Catalog
- **Catalog**: `migration`
- **Schema**: `marts`
- **Framework**: dbt Core with dbt-databricks adapter

## Objects Migrated
| Legacy Object | dbt Model(s) | Layer |
|---|---|---|
| `sp_customer_orders` | `stg_orders`, `stg_customers`, `stg_payments`, `mart_customer_orders` | Staging + Mart |
| `sp_order_summary` | `stg_orders`, `stg_payments`, `mart_order_summary` | Staging + Mart |

## Validation Results
- **Tool**: Datafold Data Diff
- **customer_orders diff**: 100% parity confirmed ([link to diff])
- **order_summary diff**: 100% parity confirmed ([link to diff])

## Accepted Discrepancies
- None (or document any timestamp precision differences here)

## Cutover Recommendation
All migrated objects pass value-level validation. Recommend proceeding with cutover to dbt models on Databricks.

What does this report do?

This report mirrors what Datafold's Migration Agent delivers to customers. It provides a non-technical stakeholder with everything they need to approve the migration: what moved, where it moved, and proof that the data matches.

Replace the [link to diff] placeholders with the actual URLs from your Datafold Data Diff results. You can copy these from the Datafold Cloud UI.

  • Replace the [link to diff] placeholders in your MIGRATION_REPORT.md with the actual Datafold diff URLs from your browser.
  • Save MIGRATION_REPORT.md.
  • Stage, commit, and push the branch by running:
git add MIGRATION_REPORT.md
git commit -m "Add migration report with Datafold validation evidence"
git push -u origin migration/customer-orders-pipeline
  • Open your GitHub repository in the browser.
  • You should see a banner saying your branch had a recent push. Click Compare & pull request.
  • In the PR title, enter: Migration: Customer Orders Pipeline (SQL Server → Databricks).
  • In the PR description, paste a summary of your parity status and include links to both Datafold diff results.
  • Click Create pull request.

What should the PR description include?

A strong migration PR description states: what was migrated (2 stored procedures to 5 dbt models), the validation method (Datafold Data Diff), the result (100% parity), and links to the evidence. Think of it as a one-paragraph executive summary for a stakeholder who will never read the code.

Don't see the Compare & pull request banner?

  • Navigate to the Pull requests tab in your repository and click New pull request.
  • Set the base branch to main and the compare branch to migration/customer-orders-pipeline.

Help me open a pull request on GitHub.

Review the PR as a stakeholder would

In a real migration delivery, stakeholders and engineers review the PR before approving cutover. You will simulate this by adding review comments that document key translation decisions and the AI-assisted workflow.

  • On your open PR page, click the Files changed tab.
  • Find mart_customer_orders.sql in the file list. Click the + icon next to a key line (such as the NULL handling logic or the lifetime value calculation).
  • Add a comment explaining the translation decision. For example: "Translated ISNULL() to COALESCE() for Databricks compatibility. Validated with Datafold that NULL handling produces identical results."
  • Click Add single comment.
  • Return to the Conversation tab of the PR.
  • Add a comment documenting your AI-assisted workflow. Include: which AI tool you used (Claude), how many translate-diff-repeat iterations were needed, and what required manual fixes.
  • Click Comment.

Why document the AI workflow?

Datafold's Migration Agent produces translation artifacts alongside code. Documenting which parts of the translation were automated and which needed human correction provides transparency for future maintenance. It also demonstrates your ability to evaluate and iterate on AI-generated code.

  • Click Merge pull request at the bottom of the PR conversation.
  • Click Confirm merge.
  • You should see the PR status change to Merged with a purple icon. This is your "migration delivery complete" milestone.

Your migration is officially delivered. You have a GitHub PR with code, a migration report, Datafold diff evidence, and review comments documenting the workflow. Next up, the Secret Mission challenges you to add incremental processing and automated data quality tests.

Secret mission

Add Incremental Processing and Data Quality Tests

Your migration is proven correct, but production pipelines need more. Real-world data keeps flowing, and you need confidence that new data won't break what you just built. In this secret mission, you'll upgrade your mart model to process data incrementally and add a dbt test suite that catches regressions automatically.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. Both Databricks and Datafold are on 14-day free trials with no charges during that period, and your GitHub repository is free.

Cost warning

Your Databricks free trial includes up to $400 in credits and expires after 14 days. Your Datafold trial also expires after 14 days. If you do nothing, both trials will expire on their own with no charges.

However, if you convert to a paid plan after the trial, running SQL Warehouses and storing data will incur costs.

Resources you used:

  • Databricks workspace with Unity Catalog (catalogs: legacy and migration).
  • Datafold Cloud account with Databricks data source connection.
  • GitHub repository (sql-server-to-databricks-migration).
  • Local dbt project (migration_project/) and Python virtual environment (.venv/).
  • SQL Server database (legacy_orders).

Keep everything running

No action needed. Choose this if you are still actively building or experimenting with more complex migrations.

  • Both trials remain active for 14 days. Continue experimenting with dbt features or additional stored procedure translations.
  • Your local migration_project/ folder and .venv/ virtual environment stay on your machine.
  • Your SQL Server legacy_orders database remains available for further testing.

Pause - I'll come back to this later

No action needed for cloud resources. Both trials will expire on their own after 14 days if you do not return.

  • Your Databricks workspace, Datafold account, and GitHub repository will remain accessible until the trials end.
  • Your local files and SQL Server database persist until you manually remove them.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

  • Drop the Databricks catalogs by running the following SQL in a Databricks SQL editor or notebook:
DROP CATALOG legacy CASCADE;
DROP CATALOG migration CASCADE;
  • In Datafold, navigate to Settings, then Data Connection. Select your Databricks data source and remove it.
  • Delete the GitHub repository. Go to your sql-server-to-databricks-migration repository on GitHub.
  • Click Settings.
  • Scroll down to the Danger Zone section and click Delete this repository.
  • Confirm by typing the repository name and completing the deletion prompts.
  • Remove your local dbt project and virtual environment. On macOS or Linux:
rm -rf migration_project/
rm -rf .venv/
  • On Windows, run these commands in your terminal:
Remove-Item -Recurse -Force migration_project
Remove-Item -Recurse -Force .venv
  • Drop the SQL Server database by running this in SSMS:
USE master;
GO
DROP DATABASE legacy_orders;
GO
  • Delete the profiles.yml file that contains your Databricks credentials. This is typically stored at ~/.dbt/profiles.yml.

That's a Wrap!

That's a Wrap!

Nice work! You just completed a full data platform migration from legacy SQL Server stored procedures to validated dbt models on Databricks.

You've learned how to:

  • Translate SQL Server stored procedures into modular dbt models (staging and mart layers) using AI-assisted translation with Claude.
  • Deploy dbt models to Databricks Unity Catalog using the three-level namespace and the dbt-databricks adapter.
  • Validate migration parity using Datafold Data Diff with value-level comparison, then deliver the migration as a professional GitHub PR with auditable diff evidence.
  • Secret Mission: Implemented incremental processing and automated data quality tests for post-migration regression detection.

Ready to quiz yourself?