Data Federation

Data Federation lets Databricks/Unity Catalog access and query data that remains in its original system, without first copying all of that data into Delta tables in Databricks.

Suppose I have multiple data:
on-prime: SQL Server, MySQL, Oracle;
Clould Azure SQL DB;
Cloud files on ADLS, format in Delta, Clould files on Azure Blob

My target environment is:

                    Azure Databricks
                         │
                  Unity Catalog
                         │
              ┌──────────┴──────────┐
              │   Data Federation   │
              └──────────┬──────────┘
                         │
       ┌─────────────────┼─────────────────┐
       │                 │                 │
   On-Prem             Azure             Cloud Files
       │                 │                 │
 ┌─────┼─────┐       Azure SQL DB      ADLS Gen2
 │     │     │                         Delta files
SQL  MySQL Oracle                      Blob Storage
Server

Think of two fundamentally different approaches. Traditional approach — copy the data
SQL Server
│
│ ETL / ADF / Spark
▼
ADLS
│
▼
Delta Lake
│
▼
Unity Catalog
│
▼
Databricks SQL

The data physically moves.

Federation approach, the customer data can remain inside SQL Server.

                    Unity Catalog
                         │
                 Foreign Catalog
                         │
        ┌────────────────┼────────────────┐
        │                │                │
    SQL Server        Oracle          MySQL
        │                │                │
        └────────────────┼────────────────┘
                         │
                  Query Federation

The data stays where it is. You can query data where it lives.

Databricks provides a governed way to access it.

SELECT *
FROM sqlserver_catalog.sales.customers;

The customer data can remain inside SQL Server.

The Unity Catalog mental model

Federation through this hierarchy:

Unity Catalog
│
├── Metastore
│
├── Catalog
│
│   ├── Schema
│   │
│   │   └── Table
│   │
│   └── ...
│
├── Connections
│
├── Foreign Catalogs
│
├── Storage Credentials
│
├── External Locations
│
└── Volumes

For Federation, pay particular attention to:

Connection
    ↓
Foreign Catalog
    ↓
Foreign Schema
    ↓
Foreign Table

Conceptually

SQL Server
    │
    │ connection
    ▼
Unity Catalog
    │
    ▼
Foreign Catalog
    │
    ├── schema1
    │     ├── tableA
    │     └── tableB
    │
    └── schema2
          └── tableC

Hands-on learning lab

We’ll create:

LAB
│
├── SQL Server
│
├── MySQL
│
├── Oracle
│
├── Azure SQL Database
│
├── ADLS Gen2
│   └── Delta
│
└── Azure Blob
    └── CSV/Parquet

And connect these to:

Azure Databricks
       │
       ▼
Unity Catalog
       │
       ├── sqlserver_federated
       ├── mysql_federated
       ├── oracle_federated
       ├── azuresql_federated
       │
       ├── catalog_ADLS_Internal
       │
       └── blob_external

Then we’ll run queries combining the data.

Lab scenario

Let’s create a realistic business scenario. Suppose the company has:

SQL Server

Customer master:Customer

CustomerID
CustomerName
Region
CustomerType

Oracle

Orders:Orders

OrderID
CustomerID
OrderDate
Amount

MySQL

Products:

ProductID
ProductName
Category
Price

Azure SQL

Customer transactions:

Transaction

TransactionID
CustomerID
ProductID
Amount
TransactionDate

ADLS Delta

Historical sales: sales_history

Blob

Marketing CSV: customer_campaign.csv

Now we can ask: Show total customer sales across Oracle historical orders, Azure SQL transactions and ADLS Delta, enriched with customer information from SQL Server.

Step 1 — Prepare Unity Catalog

First verify your current UC environment, ensure we have enable “Unity Catalog”, and you have grant to create catalog, schema, table, external location, Connection

Step 2 — Understand the Connection

This is one of the most important concepts. A Connection represents how Databricks reaches the external database.

# Conceptually:

Unity Catalog
     │
     ▼
Connection
     │
     ├── hostname
     ├── port
     ├── database
     ├── authentication
     └── credentials
          │
          ▼
      SQL Server

Step 3 — SQL Server Federation

For SQL Server, you create a connection to the SQL Server database. The SQL syntax follows the Unity Catalog federation model.

CREATE CONNECTION sqlserver_prod
TYPE SQLSERVER
OPTIONS (
    host '10.100.5.3',
    port '1433',
    user 'william',
    password '123456',
    database 'Customer_master_DB'
);

Step 4 — Create the Foreign Catalog

Once the connection exists:

CREATE FOREIGN CATALOG sqlserver_prod_catalog
USING CONNECTION sqlserver_prod;

SHOW CATALOGS;   

might show:
catalog_ut_internal
sqlserver_catalog
system

SHOW SCHEMAS IN sqlserver_prod_catalog;

might see
dbo
...

SHOW TABLES IN sqlserver_prod_catalog.dbo;

might see
Customer
CustomerAddress
CustomerType

You didn’t copy those tables into Delta. Databricks is exposing the external database through Unity Catalog.

Step 5 — Query the federated table

SELECT *
FROM sqlserver_prod_internal.dbo.Customer
LIMIT 10;

Step 6 — MySQL

# dummay 

# create CONNECTION 
CREATE CONNECTION mysql_prod
TYPE MYSQL
OPTIONS (
    host 'mysql-demo.company.com',
    port '3306',
    user 'dbx_federation',
    password 'YourStrongPasswordHere'
);




# create FOREIGN CATALOG
CREATE FOREIGN CATALOG mysql_sales
USING CONNECTION mysql_prod
OPTIONS (
    database 'salesdb'
);




# query 
SELECT *
FROM mysql_sales.salesdb.customers;


# different Foreign catalog
SELECT *
FROM mysql_sales.salesdb.orders o
JOIN sqlserver_prod_catalog.dbo.Customer sc
    ON o.customer_id = sc.customer_id;

Step 7 — Oracle

#CREATE CONNECTION
CREATE CONNECTION oracle_prod
TYPE ORACLE
OPTIONS (
    host 'oracle-demo.company.com',
    port '1521',
    user 'federation_user',
    password 'YourStrongPasswordHere',
    service_name 'ORCL'
);

#Oracle Foreign Catalog
CREATE FOREIGN CATALOG oracle_sales
USING CONNECTION oracle_prod;




#query
SELECT
    c.customer_id,
    c.customer_name,

    SUM(m.amount) AS mysql_sales,

    SUM(o.amount) AS oracle_sales

FROM sqlserver_federated.dbo.customers c

LEFT JOIN mysql_sales.salesdb.orders m
    ON c.customer_id = m.customer_id

LEFT JOIN oracle_sales.federation_user.orders o
    ON c.customer_id = o.customer_id

GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY
    c.customer_id;

Step 8 — Azure SQL Database

# Create connection
CREATE CONNECTION azure_sql_prod
TYPE sqlserver
OPTIONS (
    host 'myazuresqlserver.database.windows.net',
    port '1433',
    user 'dbx_federation',
    password secret('my-secret-scope', 'azure-sql-password')   -- password 'YourPassword'
);


# pay attention
I used --> secret('my-secret-scope', 'azure-sql-password')

# Create catalog
CREATE FOREIGN CATALOG azure_sql_sales
USING CONNECTION azure_sql_prod
OPTIONS (
    database 'federation_lab'
);

# Query Azure SQL
SELECT *
FROM azure_sql_sales.dbo.customers;

ADLS Gen2 + Delta

Does not “connection” , “Foreign Catalog” !

Create Storage Credential

Create External Location

# Create external location 
CREATE EXTERNAL LOCATION adls_federation_location
URL 'abfss://data@mystoragelab.dfs.core.windows.net/federation/'
WITH (STORAGE CREDENTIAL adls_federation_cred);


# Create catallog



# query
SELECT *
FROM catalog_ADLS_Internal.federation_lab.sales_demo;

Blob

Does not “connection” , “Foreign Catalog” !

Create Blob External Location

# create EXTERNAL LOCATION
CREATE EXTERNAL LOCATION blob_federation_location
URL 'abfss://files@mybloblab.dfs.core.windows.net/federation/'
WITH (STORAGE CREDENTIAL blob_storage_cred);



# Create External Table for CSV
CREATE TABLE catalog_ADLS_Internal.federation_lab.marketing_campaigns
USING CSV
OPTIONS (
    header = 'true',
    inferSchema = 'true'
)
LOCATION
'abfss://files@mybloblab.dfs.core.windows.net/federation/marketing/';

# query
SELECT *
FROM catalog_ADLS_Internal.federation_lab.marketing_campaigns;

Remember this:

Source	UC Object	关键代码
SQL Server	Foreign Catalog	`CREATE CONNECTION`
MySQL	Foreign Catalog	`CREATE CONNECTION`
Oracle	Foreign Catalog	`CREATE CONNECTION`
Azure SQL DB	Foreign Catalog	`CREATE CONNECTION`
ADLS Gen2 + Delta	External Table	`CREATE EXTERNAL LOCATION`
Azure Blob	External Table	`CREATE EXTERNAL LOCATION`