Fabric Lakehouse - Stage Files

Fabric Lakehouse - Stage Files generates a Stage layer that loads Parquet, Delta or CSV files for Microsoft Fabric, as notebooks on Delta tables. Select the Generator Configuration Fabric Lakehouse Stage File when you add a project whose sources are files. With the project property Target Platform set to Azure Synapse, it generates for Azure Synapse Analytics instead.

Another project, for example a Data Vault project, uses its Stage model objects as a linked project. For lakehouse tables use Fabric Lakehouse - Stage.

Scope and output

• Platform: with Target Platform Fabric, the default, each layer is a schema of a Microsoft Fabric lakehouse. With Azure Synapse, each layer is a database in Azure Synapse Analytics, which the deployment creates with CREATE DATABASE IF NOT EXISTS ... LOCATION.
• Layers: Source and Stage. Dataflow Modeling shows Stage in the Bronze column. See Understand the Layering concept.
• Output: notebooks in the folders Jupyter, Helpers and LoadControl, the notebook <project>_deploy and, with Target Platform Fabric, deploy.ps1; described in Fabric Lakehouse - Artifacts.

Prerequisites

• Target Platform Fabric: a Fabric workspace with a lakehouse with schemas. The deployment creates a schema per layer.
• Target Platform Fabric: a service principal as Member of the workspace, and PowerShell 7 for deploy.ps1.
• Target Platform Azure Synapse: an Azure Synapse workspace and a storage location per layer.
• The source files in a folder that the notebooks can read, for example the Files folder of a lakehouse, with one folder per source model object.

The setup is described in Fabric Lakehouse - Target environment and Azure Synapse - Target environment, the placeholders in Fabric Lakehouse - 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_TEST_SALESTRANSACTION_ClusterBy_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 constraints. 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. With Target Platform Fabric, it also creates a schema per layer.
• Loading: LoadControl Options offers Multi-Threading Notebook and Airflow.

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
• Target Platform: Fabric; also Azure Synapse
• Deployment Options: Generate Drop Table Statements and Generate Create Schema If Not Exists Statements
• Notebook Deployment Objects Limit: 100
• Configure LoadControl: on
• Deploy LoadControl: on
• LoadControl Options: Multi-Threading Notebook; also Airflow
• Logging Method: Delta Table; also Python Logger

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:

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

Example

A Stage TEST_SALESTRANSACTION_ClusterBy reads the Parquet files of the source SALESTRANSACTION. Its Optimization Method is Cluster By on transaction_id and transaction_line_no. It generates this table; the excerpt shows the example lakehouse my_lakehouse with the schema stage in place of the placeholders and leaves out eight columns:

CREATE OR REPLACE TABLE `my_lakehouse`.`stage`.
    `stg_st_test_salestransaction_clusterby` (
     `bg_sourcefilepath` String NOT NULL
    ,`bg_loadtimestamp` Timestamp NOT NULL
    ,`bg_sourcesystem` String
    ,`transaction_id` Decimal(38,0) NOT NULL
    ,`transaction_line_no` Decimal(38,0) NOT NULL
    ,`transaction_time` Timestamp NOT NULL
    ,`sales_amount` Decimal(18,2) NOT NULL
)
USING delta
CLUSTER BY (
     `transaction_id`
    ,`transaction_line_no`
)

With file_path set to the ABFS path of a lakehouse folder, the loader STG_ST_TEST_SALESTRANSACTION_ClusterBy_Loader reads the files like this, shown with line breaks:

df = spark.read.format('parquet') \
    .load("abfss://<workspace>@onelake.dfs.fabric.microsoft.com/"
          "<lakehouse>.Lakehouse/Files/Parquet/SALESTRANSACTION") \
    .withColumn("bg_sourcefilepath", input_file_name())

It then writes the rows into stg_st_test_salestransaction_clusterby 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: Target Object Comment Type replaced the value Target Platform with Tables and Tables and Views, default None.
• Generator 2.2: added the project properties Persist Validity, Create Result Views and the four Hash properties, and the model object property Increment Management Type on Stage. Deployment Options added Generate Create Schema If Not Exists Statements, selected by default, and LoadControl Options added Airflow.

Related articles

• Previous:
  • Fabric Lakehouse: Target environment
  • Azure Synapse: Target environment
• Next: Generate artifacts, Replace the placeholders in the artifacts
  • Fabric Lakehouse: Artifacts, Deployment, Load data - Native Load Control, Load data - Apache Airflow Load Control
  • Azure Synapse: Deployment, Load data - Native Load Control
• Reference: CSV options (Model Object), Target Platform (Project), Deployment Options (Project), Notebook Deployment Objects Limit (Project), LoadControl Options (Project), Optimization Method (Model Object)
  • Fabric Lakehouse: Placeholder values
• See also: Available Generator Configurations, Understand the Generator Configuration, Source Systems per Generator Configurations, Microsoft SQL Server - Stage Files
  • Fabric Lakehouse: Stage, Stage JDBC, Data Store, DataVault, Dimensional