Databricks Data Mart

Databricks Data Mart generates a Mart layer from the objects of linked projects for Databricks as notebooks with SQL statements on Delta tables. Select the Generator Configuration Databricks DataMart when you add a project whose data marts run on Databricks.

For dbt, select Databricks Dbt DataMart instead, see Databricks dbt Data Mart.

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: Mart, and the layers of the imported linked projects. Dataflow Modeling shows the imported objects in the Linked column in Silver and the Mart model objects in the Datamart column, which is Gold. 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 schemas of the linked projects.
• PowerShell 7 and the Databricks CLI for deploy.ps1.

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 configuration reads only model objects of linked projects, for example of a Data Vault or Data Store project. It has no discovery of its own. Import a linked project before you model the Mart layer. You can also export this project as a linked project.

Mart source views read the layers of the linked project with their own catalog and schema placeholders.

Supported model objects

• Mart Dimension
• Mart Fact
• Mart Composite
• Mart Hierarchy Flatten
• Mart Out, since Generator 2.2

Capabilities and limitations

• Loaders: one notebook per loader in the folder Jupyter, for example DM_MD_bk_multipart_not_nullable_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, with Unity Catalog only: a Mart Dimension has its identity column <model object name>_sk as primary key and, with Use Unique Constraints, a UNIQUE constraint on its business key terms. Databricks does not enforce them, see Constraints on Databricks. Databricks Runtime 17.3 LTS rejects UNIQUE constraints, see Databricks - Deployment.
• Foreign keys: Foreign Key Mode offers None and Create Disabled (default), not Create Enforced. Create Disabled adds the constraint as NOT ENFORCED.
• Unknown member: the deployment adds a row with the surrogate key -1 to each Mart Dimension. Custom Singleton Values sets its values.
• Hashing: row hashes are UNHEX(SHA2(..., 256)) in Binary columns, see How biGENIUS-X builds hash values.
• Validity: with Persist Validity on (default), historized tables store bg_validtotimestamp; off, the result view computes it.
• 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 these project properties, with their defaults:

• Hash Algorithm: SHA-256, the only value
• Hash Casing: None; also Upper and Lower
• Hash Concatenation Separator: _
• Hash Null Placeholder: _NULL_
• Hash Trim: off
• Begin of Universe: 1900-01-01
• End of Universe: 9999-12-31
• 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

Data types

The Default Terms get these data types:

• Timestamps, Timestamp: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp
• Row hashes, Binary: BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
• Text, String: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus
• Numbers, Long: BG_ID, BG_DataQuality_ID, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity; Integer: BG_ErrorCount
• Flags, Integer: BG_IsDeleted

Platform differences

The table compares the configuration with Microsoft SQL Server Data Mart of the same release:

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

Example

A Mart Dimension bk_multipart_not_nullable with the business key terms bdc_shop_id and bdc_customer_id generates this table. The excerpt shows the example catalog dwh and schema mart in place of the placeholders:

CREATE OR REPLACE TABLE `dwh`.`mart`.`dm_md_bk_multipart_not_nullable` (
     `bg_sourcesystem` String
    ,`bg_loadtimestamp` Timestamp NOT NULL
    ,`bk_multipart_not_nullable_sk` Long NOT NULL
        GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)
    ,`bdc_shop_id` String NOT NULL
    ,`bdc_customer_id` String NOT NULL
    ,`first_name` String
    ,`last_name` String
    ,`date_of_birth` Date
    ,`location` String
    ,`bg_rowhashscd1` Binary NOT NULL
    ,CONSTRAINT `pk_dm_md_bk_multipart_not_nullable`
        PRIMARY KEY (`bk_multipart_not_nullable_sk`)
    ,CONSTRAINT `uc_dm_md_bk_multipart_not_nullable`
        UNIQUE (`bdc_shop_id`, `bdc_customer_id`)
)
USING delta

The deployment then inserts the unknown-member row with bk_multipart_not_nullable_sk -1, the text Unknown and the date 1900-01-01. Its source view reads bdv_current_bdc_customer of the linked Data Vault project.

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 Mart Out, 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 Mart Fact. Deployment Options added Generate Create Schema If Not Exists Statements, selected by default.
• Generator 2.3: added the model object property Custom Singleton Values on Mart Dimension.
• Generator 2.4: added the project property Use Unique Constraints.
• Generator 1.10: the project property Delta Detection. Since Generator 2.0, Delta Detection is a property of the Mart Dimension.

Related articles

• Previous:
  • Databricks: Target environment with Unity Catalog, Target environment with the Hive metastore
• Next: Generate artifacts, Replace the placeholders in the artifacts
  • Databricks: Artifacts, Deployment, Load data - Native Load Control, Load data - Apache Airflow Load Control
• Reference: Foreign Key Mode (Model Object), Custom Singleton Values (Model Object), Delta Detection (Model Object), Use Unity Catalog (Project), Use Unique Constraints (Project), Deployment Options (Project), Notebook Deployment Objects Limit (Project), LoadControl Options (Project), Optimization Method (Model Object), BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity
  • Databricks: Placeholder values
• See also: Available Generator Configurations, Understand the Generator Configuration, Source systems per generator configuration, Use Linked Projects, Microsoft SQL Server Data Mart
  • Databricks: Data Vault, Data Vault and Mart, Data Store, Dimensional and Mart
  • Databricks dbt: Data Mart, Target environment