SQL Server → Databricks SQL Warehouse

📖 Overview & Scope

This SOP covers a one-time full copy (schema + data) of a SQL Server on-premises or VM database into an existing Databricks SQL Warehouse, using the Endrias Bridge Assessment and Migration tabs. Databricks was added as a migration target only — it cannot currently be selected as a source engine in this tool. Because Databricks (Spark SQL / Delta Lake) is a completely different engine family from SQL Server (T-SQL), there is no "same engine, 1:1 type mapping" shortcut the way SQL Server → Azure SQL has: this tool migrates schema (as CREATE TABLE) and data (as INSERT) only — it does not port stored procedures, triggers, SQL Agent jobs, or any other T-SQL procedural code.

The examples throughout this SOP use the company's own multi-industry sample database, restricted here to its eb_retail retail-domain tables — a realistic e-commerce/point-of-sale schema with customers, catalog, orders, inventory, and event-tracking tables.

✅ Prerequisites — Source (SQL Server)

-- Verify source SQL Server version and compatibility level
SELECT @@VERSION;
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'eb_retail';

-- Grant minimum permissions on source
USE eb_retail;
CREATE USER MigrationUser FOR LOGIN MigrationUser;
ALTER ROLE db_datareader ADD MEMBER MigrationUser;
GRANT VIEW SERVER STATE TO MigrationUser;

✅ Prerequisites — Target (Databricks SQL Warehouse)

Endrias Bridge does not create the Databricks workspace or the SQL Warehouse — both must already exist before Step 1. Complete the following in the Databricks UI first.

1. Confirm the SQL Warehouse exists and is running:

In the Databricks workspace, go to SQL Warehouses and confirm a warehouse is created (Serverless or Classic — either works). Start it if it is stopped.

2. Collect connection details from the warehouse:

Open the warehouse → Connection Details tab. Copy the Server hostname (e.g. adb-1234567890123456.14.azuredatabricks.net) and the HTTP Path (e.g. /sql/1.0/warehouses/8a1b2c3d4e5f6789) — you will need both in Step 1 of this SOP.

3. Generate a Personal Access Token (PAT):

In Databricks, go to User Settings → Developer → Access Tokens → Generate New Token. Give it a description (e.g. EndriasBridge migration) and a reasonable expiry, then copy the token immediately — Databricks only shows it once.

4. Install the Databricks Python driver on the migration host:

pip install databricks-sql-connector

Endrias Bridge connects to Databricks through this package. It is checked (and can be installed from) the Tools tab, under the "NoSQL & Cloud DB Migration Drivers" card.

Open Endrias Bridge and navigate to Step 5 — Source & Target Configuration. For Databricks, the target connection fields are repurposed from their usual SQL meaning — this is the single most important thing to get right on this page, so read the field mapping below carefully before filling anything in.

Field-by-field, for the Databricks target:

Switch to the Assessment tab and click Run Assessment to discover the source tables and row counts. Then run the DMF (Data Manufacturing Factory) compatibility scan — via the Run DMF Scan button in the app header, or the DMF card on the Tools tab. With SQL Server as the source and Databricks selected as the target, the DMF scan shows both the usual SQL Server compat-level / deprecated-type / deprecated-feature checks and Databricks-specific migration notes.

Switch to the Migration tab and click Load Tables from Source. This example walks through a representative subset of eb_retail: customers, products, categories, orders, order_items, inventory, and pos_transactions. Leave out very large event-log tables (cart_events, pos_items) from this pass — see the caveat in Step 6 about handling those separately.

Click Start Migration. The tool first runs a pre-flight connectivity check against both source and target — this is where any Databricks auth/HTTP Path problems surface, since there is no earlier live Test Connection for this target. Then, for each selected table, it issues a CREATE TABLE IF NOT EXISTS (all columns as STRING) followed by batched INSERTs of 500 rows at a time.

Before migrating (recommended, once tables are loaded on the Assessment tab), go to the Tools tab and run the Data Quality Checks card. It scans the source SQL Server tables for:

• Duplicate primary key values
• NULL-heavy columns (≥ 50% NULL)
• Orphaned foreign key rows

After migrating, since this tool has no target-side scan, spot-check row counts and a few key columns directly in the Databricks SQL editor against the source counts you recorded on the Assessment tab:

-- Run in the Databricks SQL editor after migration
SELECT COUNT(*) FROM main.retail.customers;
SELECT COUNT(*) FROM main.retail.orders;
SELECT * FROM main.retail.order_items LIMIT 20;

Databricks is a fundamentally different engine from SQL Server. Review the following items after the migration completes.

1. Cast STRING columns to Delta-native types:

Every column was loaded as STRING. For any column you need as a real DATE, TIMESTAMP, INT, DECIMAL, etc., cast it after the load — either with a CREATE TABLE AS SELECT using explicit CASTs, or by altering/rebuilding the table:

-- Example: typed copy of customers
CREATE TABLE main.retail.customers_typed AS
SELECT
  CAST(customer_id AS BIGINT)     AS customer_id,
  first_name,
  last_name,
  email,
  phone,
  CAST(loyalty_member AS BOOLEAN) AS loyalty_member,
  CAST(created_at AS TIMESTAMP)   AS created_at
FROM main.retail.customers;

-- Example: typed copy of orders
CREATE TABLE main.retail.orders_typed AS
SELECT
  CAST(order_id AS BIGINT)            AS order_id,
  CAST(customer_id AS BIGINT)         AS customer_id,
  CAST(order_date AS DATE)            AS order_date,
  status,
  CAST(total_amount AS DECIMAL(12,2)) AS total_amount
FROM main.retail.orders;

2. Use COPY INTO for very large tables instead of relying on this tool's row-by-row load:

Row-by-row INSERT is not the fastest path for tables with millions of rows — in eb_retail that means cart_events (3,000,000 rows) and pos_items (600,000 rows), the two largest tables in this schema. This tool does not yet automate a staged-file bulk-load flow. As a manual best practice: export the table to Parquet/CSV first using the tool's existing Amazon S3 or Azure OneLake export targets, then load it into Databricks with a native COPY INTO from that staged location.

-- Example: run in Databricks after exporting cart_events to S3/OneLake as Parquet
COPY INTO main.retail.cart_events
FROM 's3://your-staging-bucket/eb_retail/cart_events/'
FILEFORMAT = PARQUET;

3. Reimplement stored procedures, triggers, and SQL Agent jobs separately:

Databricks does not support SQL Server Agent jobs, linked servers, CLR, Service Broker, or any other T-SQL-specific feature — it's a different engine entirely (Spark SQL / Delta Lake). This tool migrates schema and data only; any procedural logic must be reimplemented by hand as Databricks notebooks or Workflows.

🗺️ Known Issues / Caveats

All columns land as STRING: Deliberate simplification, not a bug. Every column in every migrated table is created as STRING on the Databricks target. Cast to typed columns after the load if you need DATE, TIMESTAMP, INT, etc.

No live Test Connection for Databricks: Clicking Test Connections on Step 5 shows "not yet implemented" for this target. Connectivity is actually checked as a pre-flight step when you click Start Migration.

Port field is prefilled but unused: The Port field defaults to 443 for a Databricks target, but the connector does not use it — connectivity comes from the Host/IP (server hostname) and Database (HTTP Path) fields. Don't worry if it looks "wrong" or unused.

Row-by-row INSERT is not the fastest path for huge tables: Batched 500-row INSERTs work fine for typical tables, but for multi-million-row tables (cart_events and pos_items in eb_retail), export to Parquet/CSV via the tool's S3 or Azure OneLake export targets and use Databricks' native COPY INTO instead. This tool does not automate that staged bulk-load flow yet.

No procedural code migrated: Stored procedures, triggers, SQL Server Agent jobs, linked servers, CLR, and Service Broker have no Databricks equivalent and are not migrated. Reimplement them as Databricks notebooks/Workflows.

Databricks is target-only: It cannot currently be selected as a migration source in Endrias Bridge.

📊 Field Mapping Reference (Step 5 → Databricks)

Because Step 5's connection fields are shared across every target engine, several of them mean something different for Databricks than they do for a normal SQL target. Keep this table handy while filling out Step 1 of this SOP.