Databricks - Stage Files

Applies to
Platform: Databricks
Architecture: Stage

Databricks - Stage Files generates a Databricks Stage layer that loads Parquet, Delta or CSV files, as notebooks with SQL statements on Delta tables. Select the Generator Configuration Databricks Stage File when you add a project whose sources are files.

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, and read the source files.
PowerShell 7 and the Databricks CLI for deploy.ps1.
The source files in a folder that the cluster can read, for example a Unity Catalog volume, with one folder per source model object.

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 Parquet, Delta or Csv. The loader of each Stage reads the folder <file_path><source name>, where file_path is a placeholder of the source system and <source name> the name of the source model object. CSV options on the Source set how CSV files are read. The discovery types are listed in Source Systems per Generator Configurations.

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 files into a temporary view.
Load: the loader replaces the rows of the table with INSERT OVERWRITE. The column bg_sourcefilepath holds the path of the file each row comes from.
Loaders: one notebook per loader in the folder Jupyter, for example STG_ST_LOYALTYCARD_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, BG_SourceFilePath, BG_CorruptedRecord
Number, Long: BG_ID

Platform differences

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

Item	Databricks	Microsoft SQL Server
Source files	Parquet, Delta, CSV	CSV
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 LOYALTYCARD of the source system bfmparquet reads Parquet files. Its Optimization Method is Partitioned By on loyaltycard_id. It generates this table; the excerpt shows the example catalog dwh and schema stage in place of the placeholders:

CREATE OR REPLACE TABLE `dwh`.`stage`.`stg_st_loyaltycard` (
     `bg_sourcefilepath` String NOT NULL
    ,`bg_loadtimestamp` Timestamp NOT NULL
    ,`bg_sourcesystem` String
    ,`loyaltycard_id` Decimal(38,0) NOT NULL
    ,`card_number` Decimal(38,0) NOT NULL
    ,`customer_id` Decimal(38,0) NOT NULL
    ,`valid_from` Date NOT NULL
    ,`valid_to` Date NOT NULL
    ,`is_active` Boolean NOT NULL
)
USING delta
PARTITIONED BY (
     `loyaltycard_id`
)

With file_path set to the example volume folder /Volumes/dwh/src/files/bfmparquet/, the loader STG_ST_LOYALTYCARD_Loader reads the files like this:

df = spark.read.format('parquet') \
    .load("/Volumes/dwh/src/files/bfmparquet/loyaltycard") \
    .withColumn("bg_sourcefilepath", col("_metadata.file_path"))

It then writes the rows into stg_st_loyaltycard with INSERT OVERWRITE, so the table holds only the rows of the last load.

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.