Integrate Databricks SQL with External Data Sources

Contact: Bluesky @PowerBIDude | Blog: thomas-leblanc.com

Table of Contents

1. General Introduction
2. Module 1 — Understand how Databricks SQL connects to and queries external data sources (23m 6s)

• 1.1 Unity Catalog Configuration
• 1.2 Demonstration: Creation of the Unity Catalog
• 1.3 Federated Queries
• 1.4 Differentiate data access methods
• 1.5 Understanding connection types: JDBC vs ODBC
• 1.6 Demo: Create an external connection to Azure SQL Database
• 1.7 Authentication and authorization
• 1.8 Difference between external table and managed table

3. Configure and manage connections to common data sources

• 2.1 Connecting Databricks SQL to external data
• 2.2 Configure cloud data sources
• 2.3 Demonstration: Unmanaged table with external location
• 2.4 Registering objects in the Unity Catalog
• 2.5 Demonstration: Join an external table and a managed table
• 2.6 Validation of connections
• 2.7 Demo: Common Connection Issues

4. Integrate external data into analytical workflows

• 3.1 Aggregations in external queries
• 3.2 Demonstration: Simple aggregation
• 3.3 Ingestion patterns via SQL — COPY INTO and INSERT INTO
• 3.4 Demonstration: INSERT INTO
• 3.5 Transformations on federated data
• 3.6 Demonstration: GROUP BY and HAVING
• 3.7 Caching and materialization of results
• 3.8 Demonstration: Materialized Views

5. Summary and key points

1. General Introduction

This training covers the integration of Databricks SQL with external data sources, avoiding data duplication via classic ETL. It is organized into three modules:

Main objectives:

• Configure Unity Catalog in Azure and enable it in Databricks
• Create connections (connections) to relational databases and storage accounts
• Eliminate data duplication between source systems and Databricks
• Use source types such as relational databases and blob storage accounts (Parquet files)
• Understand the advantages and disadvantages of external data versus ingestion via ETL

2. Understand how Databricks SQL connects to and queries external data sources

1.1 Configuring the Unity Catalog

The Unity Catalog in Databricks is a centralized data and AI governance solution. It provides centralized access control, including auditing and lineage across all data assets.

Prerequisites

Before you begin, the following items are required:

Unity Catalog configuration steps

1. Have an active Azure subscription
2. Have a Databricks workspace on Premium capacity
3. Have the necessary administrator permissions in Azure AD and Databricks
4. Create an Azure storage account for data (metadata store)
5. Create a managed identity for secure access
6. Optionally use an Azure Key Vault for secrets

Role of the Unity Catalog

The Unity Catalog is used to:

• Govern and centralize access to data and AI
• Provide centralized access control with auditing and lineage
• Create connections to external data sources
• Enable multi-level governance: catalog → schema → table

1.2 Demonstration: Creating the Unity Catalog

Creation steps in the Azure portal

1. Create a Resource Group

• Suggested name: Unity Catalog
• Region: US West 3

2. Create a Storage Account

• Example name: storageaccountunitycatalogwest3
• Region: US West 3
• Important: Choose Gen2 and enable the hierarchical namespace (Enable hierarchy namespace)

3. Create the Azure Databricks workspace

• Name: Databricks workspace for Unity Catalog West 3
• Place in the created resource group
• Choose the Premium tier (or Premium trial)

4. Create a Container in the Storage Account

• Navigate to Storage Account → Containers
• Create a container dedicated to the Unity Catalog

5. Configure Metastore in Databricks

• In the Azure portal, enable Azure Databricks Unity Catalog via Microsoft Entra ID
• Access the Databricks admin portal to configure the Unity Catalog metastore
• Connect the metastore to the created workspace

Note: Configuring the Unity Catalog requires Global Admin access in Azure Entra ID and administrative rights in the Databricks workspace.

1.3 Federated Queries

Definition

A federated query allows you to query data across multiple systems without moving or copying it. Federated queries provide seamless access to data stored in different systems, running SQL queries that combine internal and external datasets in real time.

Prerequisites for federated queries

To use federated queries, you must perform these steps in order:

1. Create a foreign connection: Registers the federated server in the Unity Catalog. Defines the communication parameters (URL, port, credentials).
2. Register a foreign catalog: References the federated server in the Unity Catalog.
3. Grant user access to the foreign catalog: Can be done at the catalog, schema or table level, as for any secure data source.

Federated query example

This example illustrates a delta table from Databricks joined with a table from an external relational database, using a standard SQL JOIN — without moving any data.

-- Federated query : jointure entre une Delta table interne
-- et une table externe dans une base de données relationnelle
SELECT 
    dt.*,
    r.order_date,
    r.order_total
FROM delta_table dt
INNER JOIN external_db.dbo.orders r 
    ON dt.customer_id = r.customer_id;

Advantages of federated queries

1.4 Differentiate data access methods

There are two main approaches to accessing data in Databricks SQL:

Approach 1: Direct connection to a relational database

Databricks SQL connects directly to an external relational database (MySQL, PostgreSQL, SQL Server) via a JDBC or ODBC driver. Queries are run on the source system without moving data to Databricks.

Advantages:

• Real-time access to the most recent data
• No duplication → reduced storage costs
• Best for up-to-date transactional data

Disadvantages:

• Performance dependent on external database and network latency
• Less scalable for large analytical workloads
• External database may not be optimized for analytics (normalized vs dimensional schema)

Ideal use case: Scenarios requiring up-to-date transactional data, but not wanting to manage movement or storage in Databricks.

Approach 2: Ingestion into Delta tables

Data is copied from the source system to Databricks and stored in Delta Lake format (Parquet files with transaction log).

Advantages:

• Performance optimized for analytics (Columnar Parquet files with compression)
• Support for ACID transactions (reliability and consistency)
• Scalability for very large datasets
• Advanced features: time travel, schema enforcement, ACID

Disadvantages:

• Requires ETL process to move data
• Latency: data is not in real time
• Additional storage cost for copying

Ideal use case: Analytical workloads requiring high performance, scalability, and data reliability.

Comparison table

1.5 Understanding connection types: JDBC vs ODBC

The choice of driver is essential because it determines how the system communicates with the database. This choice mainly depends on the type of external system and the application environment.

JDBC (Java Database Connectivity)

• Java-based API allowing Databricks SQL to connect to relational databases
• Works best in environments using Java or systems with strong JDBC support
• Uses a standardized interface to send SQL queries and retrieve results
• Widely used for MySQL, PostgreSQL, and Oracle
• Recommended when working with Java applications or tools

ODBC (Open Database Connectivity)

• Standard language independent, connects many databases
• Commonly used in cross-platform environments
• Easily integrates with BI tools like Power BI, Tableau, etc.
• Supports a wide variety of technologies and stacks
• Mature standard, almost obsolete at one point, returned to the forefront
• Recommended for cross-platform integrations and BI tools

Decision factors

Tip: Consider performance requirements, integration with existing tools and organizational standards to choose the right driver.

1.6 Demo: Create an external connection to Azure SQL Database

Steps in the Databricks Portal

1. Navigate to Databricks service → Launch Workspace
2. In the Unity Catalog → Connections → Create a Connection
3. Configure the connection:

• Name: ContosoDW-Dev
• Data type: SQL Server
• Authentication: Username / Password

4. Enter the hostname of the SQL Azure server
5. Enter the connection credentials
6. Specify database name in external source
7. Create the associated catalog
8. Optionally add additional permissions on the connector

Result in Catalog Explorer

After creation, the Catalog Explorer displays:

• The list of schemas of the external database
• The tables accessible in each schema
• The ability to preview data (Sample Data) via a serverless SQL Warehouse

Note: A Serverless Starter Warehouse must be started for the data to be accessible and previewable in the Catalog Explorer.

1.7 Authentication and authorization

Databricks supports multiple authentication methods to secure access to resources.

OAuth

• Industry standard protocol for secure authorization
• Allows users to grant access to Databricks without directly sharing credentials
• Commonly used with identity providers such as Azure Active Directory / Entra ID
• Allows Single Sign-On (SSO) and delegated permissions
• Ideal for scenarios where user consent and security token exchange are critical

Token-based authentication (Personal Access Tokens)

• Rely on personal access tokens generated in Databricks
• These tokens serve as credentials for API calls and SQL connections
• Ideal for automation scripts and programmatic workflows
• Do not require user interaction once created → simplifies service integration

Managed Identities and Service Principals

These two approaches allow secure non-interactive authentication without storing user credentials.

Managed Identities:

• Azure automatically managed identities
• Eliminate the need to manage credentials manually
• Recommended for connections to Azure services (Storage, SQL, etc.)

Main Services:

• Identities created for applications or services, typically in Azure Entra ID
• Allow Databricks to authenticate without using a user account
• Perfect for scheduled jobs and secure automation
• Ideal for enterprise integrations requiring non-interactive authentication
• Suitable for compliance environments where centralized credential management is required

1.8 Difference between external table and managed table

External Table

An external table in Databricks is a table that references data stored in a location existing outside of Databricks-managed storage. Data files remain in their original location (Azure Data Lake Storage, AWS S3, Google Cloud Storage). Databricks only manages the metadata of the table.

Behavior during DROP:

• DROP TABLE → Only metadata is deleted
• Underlying files remain intact in their original location

Use case:

• Access large, previously processed datasets that must remain in their original location
• Other applications use the same files (compliance, cost efficiency)
• Integration with other systems requiring direct file access

Note: Management of data files remains the responsibility of the user, apart from Databricks.

Managed Table

A managed table in Databricks is an object entirely controlled by the platform. Upon creation, Databricks stores data in its managed storage location and automatically manages lifecycle operations.

Storage format: Delta Lake (Parquet files + transaction log)

Behavior during DROP:

• DROP TABLE → Metadata AND underlying files are deleted

Advanced features:

• ACID Transactions → reliability and consistency
• Schema enforcement → data validation on writing
• Time travel → access to historical versions of data

Use case:

• Analytical workloads requiring reliability, governance and performance
• Scenarios where automatic storage management is desired

Comparison table

3. Configure and manage connections to common data sources

2.1 Connecting Databricks SQL to external data

Why connect external sources directly?

Modern organizations seek to minimize data movement to reduce costs, latency, and operational complexity. Direct connection to external sources allows you to:

• Reduce duplication between systems and lower the risk of inconsistencies
• Directly access operational or warehouse systems without creating additional pipelines
• Use a common SQL interface to access various data sources
• Benefit from pushdown processing: the external system processes heavy workloads, reducing the need for preprocessing in Databricks

Configuration points to consider

Creating a SQL Server connection (CREATE CONNECTION)

This statement creates a connection object in Databricks that defines how to reach a SQL Server database. It specifies the host, port (1433 for SQL Server), and authentication credentials.

-- Créer une connexion vers un serveur SQL Server externe
CREATE CONNECTION sqlserver_connection
TYPE sqlserver
OPTIONS (
    host '10.0.0.12',
    port '1433',
    user '<username>',
    password secret('<secret_scope>', '<secret_key>')
);

Why query external data?

• Unification of SQL access across this source and many others
• Centralized permissions and roles in the Unity Catalog → centralized governance
• Reduction of complexity: fewer integrations to write
• insights are obtained without copying data

2.2 Configure cloud data sources

Objective

This section covers secure connection to cloud storage (object stores) and access governance via the Unity Catalog, using storage credentials and external locations.

Role of Unity Catalog for cloud sources

The Unity Catalog serves as a control plane for authorizing access to cloud storage. storage credentials, external locations and access policies create a consistent governance model for reading data across different cloud environments.

Supported cloud sources

All these sources are managed consistently via the Unity Catalog, eliminating ad hoc configurations at the workspace level.

Create an External Location

An external location points to a folder in cloud storage (ADLS, S3, GCS) that Databricks can use to access files via previously defined credentials.

-- Créer un external location pointant vers ADLS
-- ext_sales_loc : nom de l'external location
-- sc_sales       : credential de stockage préalablement créé
CREATE EXTERNAL LOCATION ext_sales_loc
    URL 'abfss://raw@datalake.dfs.core.windows.net/sales'
    WITH (CREDENTIAL sc_sales)
    COMMENT 'External location for sales Parquet files';

Note: The CREDENTIAL parameter references a storage credential previously created in the Unity Catalog. This credential corresponds to the managed identity of the Azure Databricks Access Connector.

2.3 Demonstration: Unmanaged table with external location

Scenario

Accessing Parquet files hosted in Azure Data Lake Storage via an external location and credential configured in the Unity Catalog.

Demonstration steps

1. Check that the Databricks cluster is running (Compute → check status)
2. List existing external locations in the notebook
3. List files in an external location
4. Create or recreate an external location via SQL script
5. Create an unmanaged table from a Parquet file
6. Query the table and describe its structure
7. Demonstrate that the DROP TABLE only deletes the metadata, not the file

SQL Commands Shown

-- 1. Lister les external locations configurés
SHOW EXTERNAL LOCATIONS;

-- 2. Lister les fichiers dans un chemin ADLS
LIST 'abfss://raw@datalake.dfs.core.windows.net/parquet/';

-- 3. Supprimer un external location existant (pour le recréer)
DROP EXTERNAL LOCATION IF EXISTS ext_parquet_loc;

-- 4. Créer l'external location avec credentials
CREATE EXTERNAL LOCATION ext_parquet_loc
    URL 'abfss://raw@datalake.dfs.core.windows.net/parquet/'
    WITH (CREDENTIAL azure_managed_identity)
    COMMENT 'External location for Parquet dimension files';

-- 5. Créer une unmanaged table à partir d'un fichier Parquet
--    La table est non gérée (external) : les données restent dans ADLS
CREATE TABLE IF NOT EXISTS test.default.DimAccountUnmanaged
    USING PARQUET
    LOCATION 'abfss://raw@datalake.dfs.core.windows.net/parquet/DimAccount.parquet';

-- 6. Requêter la table externe
SELECT * FROM test.default.DimAccountUnmanaged;

-- 7. Décrire la structure étendue de la table
DESCRIBE EXTENDED test.default.DimAccountUnmanaged;

-- 8. Supprimer la table (SEULEMENT les métadonnées — le fichier Parquet reste intact)
DROP TABLE IF EXISTS test.default.DimAccountUnmanaged;

-- 9. Vérifier que le fichier existe encore après le DROP
LIST 'abfss://raw@datalake.dfs.core.windows.net/parquet/';
-- DimAccount.parquet est toujours présent

Equivalent PySpark code (notebook)

# Lire un fichier Parquet et créer une managed Delta table
from pyspark.sql import SparkSession

# Lecture du fichier Parquet depuis ADLS
df = spark.read.parquet(
    'abfss://raw@datalake.dfs.core.windows.net/parquet/DimAccount.parquet'
)

# Vérifier que la table n'existe pas avant de la créer
spark.sql("DROP TABLE IF EXISTS test.default.dimaccount")

# Sauvegarder comme managed Delta table (overwrite)
df.write \
    .format('delta') \
    .mode('overwrite') \
    .saveAsTable('test.default.dimaccount')

# Créer des unmanaged tables pour tous les fichiers Parquet dans un dossier
file_list = dbutils.fs.ls(
    'abfss://raw@datalake.dfs.core.windows.net/parquet/'
)
file_names = [f.name for f in file_list]

for file_name in file_names:
    table_name = file_name.replace('.parquet', '').lower()
    parquet_path = (
        f'abfss://raw@datalake.dfs.core.windows.net/parquet/{file_name}'
    )
    # Créer la Delta table managed à partir du Parquet
    df_tmp = spark.read.parquet(parquet_path)
    df_tmp.write \
        .format('delta') \
        .mode('overwrite') \
        .saveAsTable(f'test.default.{table_name}')

Note: SQL syntax and PySpark produce the same result. unmanaged tables (created with LOCATION) retain data in ADLS even after a DROP TABLE. managed tables (created without LOCATION or via saveAsTable) store data in Databricks managed storage.

2.4 Saving objects in the Unity Catalog

External tables saved in the Unity Catalog

The Unity Catalog stores the metadata of the table and governs access. Data resides in cloud storage (ADLS) outside of Databricks managed storage.

-- Enregistrer une external table dans le Unity Catalog
-- Les données restent dans ADLS, Unity Catalog gère les métadonnées
CREATE TABLE catalog_name.schema_name.table_name
    LOCATION 'abfss://container@storageaccount.dfs.core.windows.net/path/to/data';

Unmanaged workspace tables (Hive Metastore legacy)

unmanaged workspace tables are legacy tables created outside of the Unity Catalog, governed only at the workspace level. They are defined in the workspace Hive Metastore and point to a user-specified storage path.

-- Table non gérée dans le Hive Metastore (legacy, sans Unity Catalog)
-- Les métadonnées sont stockées localement dans le workspace
-- Les fichiers restent dans un répertoire externe choisi par l'utilisateur
CREATE TABLE hive_metastore.schema_name.legacy_table
    USING PARQUET
    LOCATION '/user/data/external/path/';

Limitations of Hive Metastore tables (non-Unity Catalog):

• No centralized governance
• No lineage tracking
• No audit logging
• Permissions managed manually at workspace level

Audit and access control in the Unity Catalog

The Unity Catalog provides built-in auditing and granular access control for all registered datasets.

• Audit logs allow administrators to trace activities on catalogs, schemas and tables without additional configuration
• All operations are traced: table creation, reads, writes, metadata changes, permissions changes
• These logs can be exported to SIEM tools for compliance

2.5 Demonstration: Join an external table and a managed table

Scenario

This demo shows how to mix managed and external tables in a single Databricks SQL query, proving the interoperability of the two table types.

Tables used

Code for creating the managed table (PySpark)

# Lire le fichier Parquet et créer une managed Delta table
spark.read \
    .parquet('abfss://raw@datalake.dfs.core.windows.net/parquet/DimAccount.parquet') \
    .write \
    .format('delta') \
    .mode('overwrite') \
    .saveAsTable('test.default.dimaccount')

# Décrire les propriétés étendues (confirme le type "managed")
spark.sql("DESCRIBE EXTENDED test.default.dimaccount").show(50, False)

SQL query: Join managed + external

-- Jointure d'une managed table avec une external (unmanaged) table
-- factonlinesales = managed Delta table
-- dimstoreunmanaged = external table (Parquet dans ADLS)
SELECT 
    f.SalesAmount,
    f.OrderDate,
    f.ProductKey,
    s.StoreName,
    s.StoreType
FROM test.default.factonlinesales f
INNER JOIN test.default.dimstoreunmanaged s 
    ON f.StoreKey = s.StoreKey;

Conclusion: Databricks SQL allows you to mix managed and external tables in the same query without any particular configuration. The Unity Catalog seamlessly manages both types of tables.

2.6 Validating connections

Importance of validation

Connection validation must be performed before any integration work. Connection tests confirm that:

1. Databricks SQL can reach external data source (network)
2. Databricks SQL can authenticate successfully (credentials)
3. Configuration parameters are correctly established

Best practice: Establish a consistent and repeatable process for testing new and updated connections.

Common Connectivity Issues

Good testing and diagnostic practices

-- 1. Test simple : valider la connectivité de base (ne pas interroger de table)
SELECT 1;

-- 2. Test de connexion basique vers une table externe
SELECT TOP 1 * 
FROM external_connection.schema.table_name;

-- 3. Utiliser les logs de diagnostic du SQL Warehouse pour inspecter
--    les tentatives de connexion et les codes d'erreur
-- (Accessible via : Databricks UI → SQL Warehouses → Diagnostics)

Additional recommendations:

• Store credentials in a secret scope and validate access before executing queries
• Collaborate with the infrastructure team to check network logs, firewall rules, and outbound IP allowlists
• The SQL Warehouse logs allow you to precisely pinpoint the source of the error

2.7 Demo: Common Connection Issues

Scenario: Network access to Storage Account blocked

This demo illustrates how Azure Storage Account network settings affect Databricks’ ability to access files.

Situation 1: Selected networks only → Access blocked

Storage Account → Networking → 
Public network access: "Enabled from selected networks and IP addresses"
(aucune règle configurée)

Résultat : ERREUR "Failed to access cloud storage"

Situation 2: All networks enabled → Access allowed (not secure)

Storage Account → Networking → 
Public network access: "Enabled from all networks"

Résultat : Accès autorisé (mais configuration non sécurisée)

Situation 3: Recommended secure configuration

Storage Account → Networking →
"Allow trusted Microsoft services" + 
"Select specific resources: All in current tenant"

Résultat : Accès restreint aux ressources Azure de confiance du tenant

Option 1: Configure a Virtual Network around the Storage Account and Azure Data Factory Option 2: Enable Allow trusted Microsoft services → select Databricks Access Connectors

Checking IAM permissions

To confirm that the managed identity has the correct permissions on the Storage Account:

Storage Account → Access control (IAM) → 
Check access → Managed identity → 
Access Connector for Azure Databricks
→ Vérifier le rôle assigné (Storage Blob Data Contributor ou Reader)

4. Integrate external data into analytical workflows

3.1 Aggregations in external queries

Role of aggregations

Aggregations allow summarizing large datasets from both managed and external sources. They condense large volumes of raw data into meaningful, easy-to-interpret metrics, particularly useful when working with both delta tables and external relational systems.

Aggregation pushdown

Databricks SQL can push some aggregations to the external system (pushdown processing). When possible, aggregation calculations are sent directly to the external source, where the data resides:

• Accelerates results
• Minimizes data movement
• Significantly improves overall query performance

Main aggregation functions

These aggregation functions behave consistently between delta tables and most federated sources.

Window Analytical Functions

window functions allow richer analyzes without writing complex self-joins:

Performance Considerations

• Aggregations on large external tables may require a lot of data movement in the SQL Warehouse
• Applying filters and column pruning early reduces the amount of data scanned from the external source
• Caching materialized results in delta tables improves performance for large recurring datasets
• Evaluate pushdown support to push the request to the source
• For heavy and recurring analytical loads, consider ingesting data into managed delta tables for more efficient processing

Example: Multi-source CTE with aggregation

-- CTE combinant trois sources de données différentes :
-- 1. test.default.dimstoreunmanaged   → external (Parquet ADLS)
-- 2. `contoso-sql_catalog`.dbo.dimdate → federated (Azure SQL Server)
-- 3. test.default.factonlinesales     → managed (Delta Lake)

WITH cteStore AS (
    -- Source 1 : table externe (unmanaged) — dimension magasins
    SELECT 
        StoreKey, 
        StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount) AS TotalSales
FROM test.default.factonlinesales s                        -- Source 3 : managed
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d             -- Source 2 : fédérée
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st                                     -- Source 1 : CTE externe
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear
ORDER BY 
    TotalSales DESC, 
    d.CalendarYear;

3.2 Demonstration: Simple aggregation

Scenario

Build an aggregation query in the Databricks SQL Editor that combines three separate data sources.

Query construction steps

1. Open the SQL Editor in Databricks
2. Connect the serverless pool (SQL Warehouse serverless)
3. Start with a simple query to validate data
4. Gradually add joins and aggregation

-- Étape 1 : Requête de base pour valider les données
SELECT 
    s.StoreKey, 
    s.SalesAmount
FROM test.default.factonlinesales s
LIMIT 100;

-- Étape 2 : Ajouter la jointure avec la dimension date (source fédérée Azure SQL)
SELECT 
    s.StoreKey, 
    s.SalesAmount,
    d.CalendarYear
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
LIMIT 100;

-- Étape 3 : Requête complète avec CTE, agrégation et tri
WITH cteStore AS (
    SELECT StoreKey, StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount) AS TotalSales
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st 
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear
ORDER BY 
    TotalSales DESC, 
    d.CalendarYear;

Note: The first run may take longer due to data size and cache startup. The ORDER BY can use the same columns as the GROUP BY.

3.3 Ingestion patterns via SQL — COPY INTO and INSERT INTO

When to ingest rather than federate?

Ingestion is preferred over federated access in the following cases:

• Federated access is too slow for analytical workloads
• Some operations are not supported on federated sources
• downstream pipelines require data to be in Databricks
• Source has concurrent connection limitations

COPY INTO

COPY INTO ingests files from cloud storage to a delta table using the schema inferred from Parquet files (file-based loading).

Key benefit: incremental behavior

• Tracks files already loaded successfully
• Automatically skips already processed files on subsequent runs
• Perfect for repeated and planned ingestions

Available options:

• Filtering file patterns
• Column mapping
• Input validation

-- Ingestion de fichiers Parquet depuis ADLS vers une Delta table
-- Les fichiers déjà chargés sont automatiquement ignorés (incrémental)
COPY INTO catalog.schema.target_delta_table
FROM 'abfss://container@storageaccount.dfs.core.windows.net/path/'
FILEFORMAT = PARQUET
FILES = ('DimDate.parquet', 'DimProduct.parquet')
FORMAT_OPTIONS (
    'mergeSchema' = 'true'
);

INSERT INTO

INSERT INTO writes data directly to managed delta tables from an SQL source (SELECT). This pattern supports selective ingestion — choosing exactly which lines to load.

Features:

• Operates at line level → suitable for small ingestions or filtered slices
• Uses a SELECT as source → can reference external data sources
• Allows transformations during ingestion (shaping data before landing)
• Complementary to COPY INTO: manages SQL-driven ingestion, while COPY INTO manages bulk file loading

-- INSERT INTO depuis une source externe avec filtrage
-- Ingestion sélective des données depuis une table fédérée
INSERT INTO catalog.schema.managed_delta_table
SELECT 
    column1,
    column2,
    UPPER(column3) AS column3_transformed,  -- transformation pendant l'ingestion
    CURRENT_TIMESTAMP() AS load_timestamp
FROM external_source_table
WHERE 
    date_column >= '2024-01-01'             -- filtre sur les données à ingérer
    AND status = 'ACTIVE';

Comparison COPY INTO vs INSERT INTO

3.4 Demonstration: INSERT INTO

Scenario

Create a hardened table from a multi-source aggregation query, using INSERT INTO.

Demonstration steps

-- Étape 1 : Supprimer la table si elle existe (option 1 : DROP TABLE)
DROP TABLE IF EXISTS test.default.onlinesalesforstore;

-- Étape 2 : Créer la table cible
CREATE OR REPLACE TABLE test.default.onlinesalesforstore (
    StoreName    STRING,
    CalendarYear INT,
    TotalSales   DECIMAL(18, 2)
);

-- Étape 3 : Insérer les données agrégées depuis trois sources différentes
INSERT INTO test.default.onlinesalesforstore (StoreName, CalendarYear, TotalSales)
WITH cteStore AS (
    SELECT StoreKey, StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount) AS TotalSales
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st 
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear;

-- Étape 4 : Vérifier les données insérées
SELECT * FROM test.default.onlinesalesforstore;

Alternative with CREATE OR REPLACE:

-- Alternative : CREATE OR REPLACE TABLE remplace la table et ses données
CREATE OR REPLACE TABLE test.default.onlinesalesforstore AS
WITH cteStore AS (
    SELECT StoreKey, StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount) AS TotalSales
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st 
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear;

Limitation: Data in onlinesalesforstore represents a runtime snapshot. If new data is inserted into all three source tables, it will not automatically reflect in the hardened table. For automatic updating, use a Materialized View (see section 3.7).

3.5 Transformations on federated data

Transformations without data movement

Databricks SQL allows you to run analytical transformations directly on federated sources without loading the data into Delta. Databricks is push-down aware: it performs as many transformations as possible on the external system, reducing data movement.

GROUP BY with HAVING

HAVING allows you to perform advanced analytical transformations by filtering groups based on aggregated results — unlike WHERE which filters individual rows.

Use case: Compare an aggregate to a threshold (ex: total sales > $1M, count > 1500)

Transformations in the SELECT clause

Common transformations in SELECT to make data more usable:

-- Exemple de transformations dans SELECT :

-- 1. Extraire le composant année d'une date
YEAR(date_column) AS sale_year,

-- 2. Utiliser CASE pour convertir des codes en libellés lisibles
CASE 
    WHEN status_code = 1 THEN 'Active'
    WHEN status_code = 2 THEN 'Inactive'
    WHEN status_code = 3 THEN 'Pending'
    ELSE 'Unknown'
END AS status_label,

-- 3. Formater ou convertir des valeurs
CAST(amount AS DECIMAL(18,2)) AS formatted_amount,

-- 4. Concaténer des champs
CONCAT(first_name, ' ', last_name) AS full_name

3.6 Demonstration: GROUP BY and HAVING

Scenario

Add a HAVING clause to the aggregation query to only return groups exceeding a sales threshold.

-- Requête avec GROUP BY et HAVING
-- Filtre : seulement les groupes avec plus de 1,5 million de ventes
WITH cteStore AS (
    SELECT StoreKey, StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount) AS TotalSales,
    COUNT(1)           AS NoOfSales        -- Compteur de transactions
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st 
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear
HAVING COUNT(1) > 1500000                  -- Filtre APRÈS le GROUP BY
ORDER BY 
    TotalSales DESC, 
    d.CalendarYear;

Explanation: Without HAVING, all groups would be returned (including those with less than 1.5 million transactions). The HAVING COUNT(1) > 1500000 clause eliminates groups that do not exceed this threshold. This is different from WHERE which filters rows before grouping.

3.7 Caching and materialization of results

The two acceleration mechanisms

Databricks SQL provides two mechanisms to accelerate workloads that execute the same logic multiple times:

These mechanisms are particularly valuable for dashboards, BI workloads, and scheduled queries which rely on the same transformation logic.

Result Caching

• Does not modify underlying query plans
• Lightweight (light) but dependent on data refreshes and warehouse state
• Instantly returns results for future identical queries

Materialized View

A materialized view saves the output of a query as a managed object:

• Future queries read stored results without recalculating all logic
• The warehouse manages the incremental refresh automatically → the materialized view always reflects the current data
• Heavy calculation is done in advance → individual queries become lightweight
• Transformation logic resides in a single managed object instead of being duplicated across multiple reports
• Only refresh operation performs full calculation → individual queries become very lightweight
• Significantly reduces total warehouse consumption over time

-- Créer une Materialized View
-- Syntaxe générique
CREATE OR REPLACE MATERIALIZED VIEW catalog_name.schema_name.view_name AS
SELECT 
    column1,
    column2,
    SUM(amount) AS total_amount
FROM source_table
WHERE condition
GROUP BY column1, column2;

3.8 Demo: Materialized Views

Scenario

Create a Materialized View that hardens the multi-source aggregation query, letting Databricks automatically handle updates.

Advantage over INSERT INTO

Creation SQL code

-- Créer ou remplacer une Materialized View
-- Databricks gère automatiquement le refresh incrémental
CREATE OR REPLACE MATERIALIZED VIEW test.default.LargeOnlineSales AS
WITH cteStore AS (
    SELECT StoreKey, StoreName
    FROM test.default.dimstoreunmanaged
)
SELECT 
    st.StoreName,
    d.CalendarYear,
    SUM(s.SalesAmount)  AS TotalSales,
    COUNT(1)            AS NoOfSales
FROM test.default.factonlinesales s
INNER JOIN `contoso-sql_catalog`.dbo.dimdate d 
    ON s.DateKey = d.DateKey
INNER JOIN cteStore st 
    ON s.StoreKey = st.StoreKey
GROUP BY 
    st.StoreName, 
    d.CalendarYear
HAVING COUNT(1) > 1500000;

Performance Insights

When creating a Materialized View, observe in the Performance tab:

Warning: Creating a Materialized View is not instantaneous. It takes significantly longer than a simple INSERT INTO because it must create the entire metadata infrastructure and perform the initial refresh. This is especially true when the logic contains a HAVING clause, because Databricks must do the entire job in the background.

Request Materialized View

-- Requêter la materialized view (rapide — lit les résultats pré-calculés)
SELECT * 
FROM test.default.LargeOnlineSales
ORDER BY TotalSales DESC;

5. Summary and key points

General architecture

Azure Data Lake Storage (ADLS)        Azure SQL Database
  [Parquet Files]                      [Relational Tables]
        |                                      |
        | External Location +                 | CREATE CONNECTION
        | Storage Credential                  | (JDBC, port 1433)
        |                                      |
        +----------+  Unity Catalog  +---------+
                   |                 |
                   v                 v
         [External Tables]    [Foreign Catalog]
               (Metadata only)    (Federated)
                   |                 |
                   +--------+--------+
                            |
                   Databricks SQL Warehouse
                            |
                 +----------+----------+
                 |          |          |
           Managed      Materialized  Result
           Delta Table   Views        Cache

Key SQL Command Summary

Deployment Checklist

• Azure subscription available with required permissions
• Workspace Databricks on Premium tier
• Storage Account Gen2 with hierarchical namespace enabled
• Managed Identity configured for Azure Databricks Access Connector
• Unity Catalog created and activated in the workspace
• Storage Credentials created in the Unity Catalog
• External Locations defined and tested
• Connections (connections) to relational databases created
• IAM permissions checked (Storage Blob Data Contributor or Reader)
• Firewall/network rules configured to allow access from Databricks SQL
• Connectivity tests validated before integration into production

Search Terms

integrate · databricks · sql · external · data · sources · azure · spark · engineering · analytics · catalog · demonstration · unity · scenario · access · managed · connection · federated · insert · queries · aggregation · database · query · tables