Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Databricks - Dimensional

Applies to
Platform: Databricks
Architecture: Dimensional

Databricks - Dimensional generates a Stage and a dimensional Core for Databricks as notebooks with SQL statements on Delta tables. Select the Generator Configuration Databricks Dimensional when you add a project whose dimensional model runs on Databricks.

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.
  • Dataflow Modeling columns: Source; Stage in the Bronze column; Entity, Fact and Core Composite in the Silver column Dimensional. See Understand the Layering concept.
  • Deployment layers: Stage, Cleanse with the source and data quality views, and Core with the tables.
  • 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 tables.
  • 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

Stage source views read each source table as <catalog>.<schema>.<table> with placeholders of the source system. Discover a Databricks Unity Catalog creates the source model objects; Source Systems per Generator Configurations lists the discovery types.

You can import a linked project, for example a Databricks - Stage Files or Databricks - Stage JDBC project, and export this project as a linked project.

Supported model objects

Capabilities and limitations

  • Loaders: one notebook per loader in the folder Jupyter, for example COR_EN_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: an Entity table 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: each Entity table gets a row with the surrogate key -1, and a Fact row without a matching Entity row references it. Custom Singleton Values sets its values.
  • Data quality: Entity and Fact support data quality rules.
  • 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:

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_Entity_Identity, BG_Entity_ForeignKey_Identity, BG_Fact_ForeignKey_Identity; Integer: BG_ErrorCount
  • Flags, Integer: BG_IsDeleted

Platform differences

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

ItemDatabricksMicrosoft SQL Server
Timestamp Default TermsTimestampdatetimeoffset
Hash Default TermsBinarybinary(20)
Text Default TermsStringnvarchar
Hash Algorithm defaultSHA-256SHA1
Persist Validity defaultonoff
Deployment Options defaultdrop tables, create schemasdrop tables
Enforced foreign keysnot availableavailable
Index managementnot availableavailable

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

Example

An Entity bk_multipart_not_nullable with the business key terms bdc_shop_id and bdc_customer_id1 generates this table and unknown-member row. The excerpt shows the example catalog dwh and schema core in place of the placeholders and leaves out the four descriptive columns:

CREATE OR REPLACE TABLE `dwh`.`core`.`cor_en_bk_multipart_not_nullable` (
     `bg_sourcesystem` String
    ,`bg_loadtimestamp` Timestamp NOT NULL
    ,`bg_updatetimestamp` 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_id1` String NOT NULL
    ,`bg_rowhashscd1` Binary NOT NULL
    ,CONSTRAINT `pk_cor_en_bk_multipart_not_nullable`
        PRIMARY KEY (`bk_multipart_not_nullable_sk`)
    ,CONSTRAINT `uc_cor_en_bk_multipart_not_nullable`
        UNIQUE (`bdc_shop_id`, `bdc_customer_id1`)
)
USING delta

INSERT
INTO `dwh`.`core`.`cor_en_bk_multipart_not_nullable` (
     `bg_sourcesystem`
    ,`bg_loadtimestamp`
    ,`bg_updatetimestamp`
    ,`bk_multipart_not_nullable_sk`
    ,`bdc_shop_id`
    ,`bdc_customer_id1`
    ,`bg_rowhashscd1`
)
SELECT
     CAST('Unknown' AS String) AS `bg_sourcesystem`
    ,CAST('1900-01-01' AS Timestamp) AS `bg_loadtimestamp`
    ,CAST('1900-01-01' AS Timestamp) AS `bg_updatetimestamp`
    ,CAST(-1 AS Long) AS `bk_multipart_not_nullable_sk`
    ,CAST('Unknown' AS String) AS `bdc_shop_id`
    ,CAST('Unknown' AS String) AS `bdc_customer_id1`
    ,CAST(X'00' AS Binary) AS `bg_rowhashscd1`
WHERE NOT EXISTS (
          SELECT
               1
          FROM `dwh`.`core`.`cor_en_bk_multipart_not_nullable`
          WHERE `bk_multipart_not_nullable_sk` = CAST(-1 AS Long)
      )

The unknown-member row uses the Begin of Universe 1900-01-01 and the text Unknown.

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 and 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 Entity. The Optimization Method of Entity, Fact and Core Composite applies to the table since Generator 2.3.0; up to Generator 2.2.1 it was ignored. Tables of these types that were deployed with Generator 2.2.1 or earlier get the method only when they are created again. Redeploy with Generate Drop Table Statements selected, or recreate only the affected tables yourself. Then reload their data.
  • 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 Entity.

Related articles