Databricks - Stage JDBC

Applies to
Platform: Databricks
Architecture: Stage

Databricks - Stage JDBC generates a Databricks Stage layer that reads other databases over JDBC, as notebooks with SQL statements on Delta tables. Select the Generator Configuration Databricks Stage Jdbc when you add a project whose sources are MariaDB, Microsoft SQL Server, MySQL, Oracle or PostgreSQL databases.

Another project, for example a Data Vault project, uses its Stage model objects as a linked project. For tables of a Databricks catalog use Databricks - Stage.

Scope and output

Platform: Databricks. Each layer is a schema of a Unity Catalog catalog or, with Use Unity Catalog off, a Hive metastore database.
Layers: Source and Stage. Dataflow Modeling shows Stage in the Bronze column. See Understand the Layering concept.
Output: notebooks in Jupyter and LoadControl, and deploy.ps1, described in Databricks - Artifacts.

Prerequisites

A Databricks workspace with a catalog for the project, or with the Hive metastore a storage location per layer. The deployment creates the schemas or databases.
An all-purpose cluster and a user who can create tables and run jobs on it.
PowerShell 7 and the Databricks CLI for deploy.ps1.
A network path from the cluster to the source database, and a database user that can read the source tables.

The setup is described in Databricks - Target environment with Unity Catalog and Databricks - Target environment with the Hive metastore, the placeholders in Databricks - Placeholder values.

Sources and linked projects

The source system has the type MariaDB, Microsoft SQL Server, MySQL, Oracle or PostgreSQL. Its placeholders jdbc_url, jdbc_driver, jdbc_user, jdbc_password and schema_name hold the connection and the source schema. Source Systems per Generator Configurations lists the discovery types.

You can export this project as a linked project. The configuration cannot import linked projects.

Supported model objects

Source
Stage

Capabilities and limitations

Generated objects per Stage: the table stg_st_<model object name>, the view _result and a loader notebook. There is no source view; the loader reads the source table into a temporary view.
Load: the loader replaces the rows of the table with INSERT OVERWRITE.
Loaders: one notebook per loader in the folder Jupyter, for example STG_ST_CUSTOMER_Loader, with the load timestamp as the parameter bg_loadtimestamp.
Tables: Delta tables. File Format offers only delta, and Optimization Method offers Partitioned By and Cluster By.
Keys: Stage tables have no hash columns and no key on the business key terms. Indexes: index management is not available.
Deployment: with the default Deployment Options, each deployment replaces the tables and the load control log tables with empty ones, see Redeploy and keep data in Databricks - Deployment.
Governance: Apply Sensitivity Level Tags adds a tags notebook that the deployment does not run.

Project properties

The configuration offers no Hash Algorithm and no End of Universe. It offers these project properties, with their defaults:

Hash Casing: None; also Upper and Lower
Hash Concatenation Separator: _
Hash Null Placeholder: _NULL_
Hash Trim: off
Begin of Universe: 1900-01-01
Persist Validity: on
Create Result Views: on
Target Object Comment Type: None; also Tables and Tables and Views
Use Unity Catalog: on
Use Unique Constraints: on
Deployment Options: Generate Drop Table Statements and Generate Create Schema If Not Exists Statements; also Generate Alter View Body Statements
Notebook Deployment Objects Limit: 100
Apply Sensitivity Level Tags: off
Sensitivity Level Tag Name: sensitivity_level
Configure LoadControl: on
Deploy LoadControl: on
LoadControl Options: Multi-Threading Notebook; also Airflow
Logging Method: Delta Table; also Python Logger

Hash Casing, Hash Concatenation Separator, Hash Null Placeholder, Hash Trim and Persist Validity have no effect on Stage objects.

Data types

The Default Terms get these data types:

Load timestamp, Timestamp: BG_LoadTimestamp
Text, String: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_IncrementLoadStatus
Number, Long: BG_ID

Platform differences

The table compares the configuration with Microsoft SQL Server - Stage of the same release:

Item	Databricks	Microsoft SQL Server
Timestamp Default Terms	`Timestamp`	`datetimeoffset`
Text Default Terms	`String`	`nvarchar`
Persist Validity default	on	off
Deployment Options default	drop tables, create schemas	drop tables
Index management	not available	available

Both platforms offer the LoadControl Option Airflow. The native load control is Multi-Threading Notebook on Databricks.

Example

A Stage CUSTOMER reads the table CUSTOMER of a Microsoft SQL Server source system. Its loader STG_ST_CUSTOMER_Loader starts with this statement, shown with line breaks:

df = spark.read.jdbc(
    '<jdbc_url>',
    '<schema_name>.CUSTOMER',
    properties={'user': <jdbc_user>,
                'password': <jdbc_password>,
                'driver': '<jdbc_driver>'})

The names in angle brackets are the placeholders of the source system. <jdbc_user> and <jdbc_password> have no quotes in the notebook, so their values are Python expressions, for example 'loader' or a dbutils.secrets.get call; see Databricks - Placeholder values. The loader then writes the rows into stg_st_customer with INSERT OVERWRITE and sets bg_loadtimestamp to its parameter.

Release differences

Project Settings shows the Generator Version of your project.

Generator 2.0: Use Unity Catalog is on by default, off up to Generator 1.10. Target Object Comment Type replaced Target Platform with Tables and Tables and Views, default None. Deployment Options added Generate Alter View Body Statements.
Generator 2.2: added the project properties Persist Validity, Create Result Views, the four Hash properties and the two Sensitivity Level properties, and the model object property Increment Management Type on Stage. Deployment Options added Generate Create Schema If Not Exists Statements, selected by default.
Generator 2.4: added the project property Use Unique Constraints.