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

Databricks dbt Data Mart

Applies to
Platform: Databricks dbt
Architecture: Data Mart

Databricks dbt Data Mart generates a Mart layer for Databricks as a dbt project, from the objects of linked projects. Select it in Project Settings, where Generator Configuration shows Databricks Dbt DataMart, when you load your data mart with dbt on Databricks. This article lists what the configuration supports and where it differs from Databricks Data Mart.

The configuration offers no load control, no deployment scripts and no data quality checks. The full list of features that are not available is under Capabilities and limitations.

Scope and output

  • Platform: Databricks with Unity Catalog. The configuration is available since Generator 2.2; Generator 2.1 contained a preview.
  • Layers: Mart, and the layers of the linked projects. See Understand the Layering concept.
  • Output: one dbt project with the model folder models/mart, described in dbt - Artifacts. dbt creates and loads the tables, see dbt - Deployment.

Prerequisites

Sources and linked projects

The configuration reads only model objects of linked projects: Raw Vault and Business Vault objects of a Data Vault project, Entity and Fact of a Dimensional project, and Store Entity of a Data Store project. When you import, select the source system type Spark.

Databricks dbt Data Vault and Mart builds the Data Vault and the Mart in one project, without linked projects.

dbt reads the linked objects as dbt sources. Each linked source system gets the variables <source system>_catalog and <source system>_schema in dbt_project.yml for the location of its tables. The table names come from the export: the result view when the exporting project has Create Result Views on, otherwise the table.

A project with the configuration Databricks dbt Data Vault writes the Raw Vault and the Business Vault into separate schemas. Export the Raw Vault objects and the Business Vault objects separately, and import each export as its own linked source system. Each import needs its own export file, because the client refuses a file that was already uploaded on the branch.

Supported model objects

Capabilities and limitations

  • Loading: dbt runs the models in the order of their references. A Mart Fact with more than one dataflow gets one loader model per dataflow. With the Fact Load Strategy Truncate Insert, each loader replaces the rows of the previous one, so the table holds only the rows of the last dataflow. Validation reports the warning Model object has unsupported fact load strategy defined for multiple dataflows.
  • Incremental reads: a Mart Fact with the Fact Load Strategy Insert reads only new rows when BG_IncrementalFilter is mapped to the linked object's BG_LoadTimestamp, see Configure an incremental load. New rows are rows whose BG_LoadTimestamp is later than the latest BG_LoadTimestamp in the Mart Fact table.
    • Without an incremental read filter, a Mart Fact with the Fact Load Strategy Insert inserts all source rows again on every run.
  • Surrogate keys: a Mart Dimension with Implementation Type Permanent numbers new rows in <model object name>_SK with ROW_NUMBER() above the highest existing key. The unknown-member row has the key -1. With Implementation Type Cached, the dimension numbers its rows from 1 again on every run, so a row can get another key in each run. For the effect of a full refresh on the keys, see Full refresh in dbt - Deployment.
  • Lookups: a Mart Fact reads the surrogate key from the Mart Dimension table, or from its result view with Create Result Views on. A source row without a matching dimension row gets -1.
  • Historization: Mart Dimensions with SCD2 terms allow one dataflow.
  • Validity: Mart Dimensions with SCD2 terms need Persist Validity or Create Result Views.
  • Keys: the model YAML files declare a primary key on the surrogate key and, with Use Unique Constraints on, a unique constraint on the business key. Only Mart Facts get foreign keys, with Foreign Key Mode Create Disabled (default). Foreign Key Mode offers None and Create Disabled.
  • Hashes: Hash Algorithm offers only SHA-256. Row hashes have the data type Binary.
  • Data quality: data quality checks are not available. The menu of the model objects has no Manage Data Quality entry.

Implementation Type offers these values per model object type:

  • Mart Dimension: Permanent (default) or Cached (materialization bg_overwrite)
  • Mart Fact: FactLoadStrategy, with the Fact Load Strategy Truncate Insert (default) or Insert
  • Mart Composite and Mart Out: Cached or Virtual (default)
  • Mart Hierarchy Flatten: Cached

These properties of Databricks Data Mart are not available:

Project properties

Project Settings shows these properties in the groups Governance, Hashing, Historization and Implementation, with their defaults:

Model object properties: Custom Singleton Values, Delta Detection, Version Valid From Term, Fact Load Strategy, Deduplication Method, Sort Order within Partition, Foreign Key Mode, Implementation Type, File Format (delta), Optimization Method and Optimization Method Columns.

Platform differences

Compared with Databricks Data Mart of the same release:

  • Output: a dbt project with SQL models instead of PySpark notebooks and deployment scripts.
  • Loading: dbt runs the models; there is no load control and no Apache Airflow option.
  • Create Result Views: off by default; on by default in Databricks Data Mart.
  • Target Object Comment Type: no value Tables.
  • Unity Catalog: always used; Databricks Data Mart has the property Use Unity Catalog.

Example

A Mart Fact load_insert_with_effective_timestamp with the Fact Load Strategy Insert references the Mart Dimension common_pos through the business key term POS_ID. With Create Result Views off, the model dm_mf_load_insert_with_effective_timestamp looks up the surrogate key in the dimension table. Excerpt of the model SQL:

    ,COALESCE(`r1`.`common_pos_sk`, -1) AS `common_pos_common_pos_sk`
FROM {{ ref('dm_mf_load_insert_with_effective_timestamp_source') }}
    AS `bg_source`
LEFT OUTER JOIN {{ ref('dm_md_common_pos') }} AS `r1`
   ON (`r1`.`pos_id` =
       `bg_source`.`fk_common_pos_common_pos_pos_id`)

Excerpt of models/mart/models_mart.yml with the foreign key that Foreign Key Mode Create Disabled adds:

  - name: dm_mf_load_insert_with_effective_timestamp
    constraints:
      - type: foreign_key
        columns: [common_pos_common_pos_sk]
        to: ref('dm_md_common_pos')
        to_columns: [common_pos_sk]

The excerpt leaves out the constraint name and warn_unsupported: false. With Create Result Views on, the lookup reads ref('dm_md_common_pos_result') instead.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: first release after the preview in Generator 2.1. Compared with the preview: Create Result Views, default off; the Foreign Key Mode value Create Disabled with primary key and foreign key constraints; the incremental read filter on BG_LoadTimestamp.
  • Generator 2.3: the catalog and schema of each linked source system are variables in dbt_project.yml; the model object property Custom Singleton Values; a fix of the Mart Out deduplication. See Generators Release - 2.3.0.
  • Generator 2.4: Non-Historized Satellite and Non-Historized Business Satellite as linked objects; Use Unique Constraints; the materializations apply the constraints; table columns use the modeled data types. See Generators Release - 2.4.0.

Related articles