dbt Connector

Install the plug-in

# we use conda and python 3.9 for this example
conda create --name dbt-maxcompute-example python=3.9
conda activate dbt-maxcompute-example

# current dbt-maxcompute version is 1.9 
pip install dbt-core==1.9.4
pip install dbt-maxcompute

Configure parameters

Run dbt models

New dbt users can refer to the dbt-maxcompute repository or the official dbt documentation, but additional adaptation is required for MaxCompute.

Standard dbt capabilities

dbt provides the following features to help users write models that reflect core business logic. For more information, see dbt introduction.

Model

dbt provides multiple features to help developers write SELECT query models that adapt to changing business requirements and improve query efficiency. When dbt run is executed, the model transforms data directly within the data warehouse without moving the data out. dbt also supports languages other than SQL. Starting from v1.3, dbt Core and dbt Cloud support Python models. Python models are suitable for the following scenarios:

• Training or deploying data science models, such as machine learning models.

• Complex data transformations, such as text processing and graph computing.

• Using specific Python libraries, such as the dateutil library for parsing dates.

In modern workflows, the top-level structure in dbt is a project. A project contains:

• Project configuration files (.yml files), which define project metadata and global settings.

• Model files (.sql or .py files), which define data transformation logic.

Project configuration files tell dbt how to build datasets, while models specify the data transformation rules. Models can also reference each other to improve data transformation efficiency. For more information, see Models documentation.

Seed

Seeds are CSV files in a dbt project, typically stored in the seeds directory, that can be loaded into the data warehouse by running the dbt seed command. Seed files can be referenced in downstream models by using the same ref function as models. Because these CSV files are stored in the dbt repository, they can be version-controlled and code-reviewed. Seeds are best suited for static data that rarely changes, such as enumeration values and mapping tables. For more information, see Seeds documentation.

Source

Sources declare and describe raw data tables in a dbt project. Extract and load tools write data to these tables in the data warehouse. Declaring these tables as sources allows users to:

• Use the source() function to reference source tables in queries and build data lineage between source tables and downstream models.

• Check whether source data meets expectations by using data tests.

• Check source data freshness to determine whether the data is updated in time.

For more information, see Sources documentation.

version: 2
sources:  
  - name: jaffle_shop    
    database: raw      
    schema: jaffle_shop      
    tables:      
      - name: orders      
      - name: customers  
      
  - name: stripe    
    tables:      
      - name: payments

SELECT
  ...
  FROM {{ source('jaffle_shop', 'orders') }}
  LEFT JOIN {{ source('jaffle_shop', 'customers') }} USING (customer_id)

SELECT
  ...
  FROM raw.jaffle_shop.orders
  LEFT JOIN raw.jaffle_shop.customers USING (customer_id)

version: 2

sources:
  - name: jaffle_shop
    description: This is a replica of the Postgres database used by our app
    tables:
      - name: orders
        description: >
          One record per order. Includes cancelled and deleted orders.
        columns:
          - name: id
            description: Primary key of the orders table
            tests:
              - unique
              - not_null
          - name: status
            description: Note that the status can change over time

      - name: ...

  - name: ...

version: 2

sources:  
   - name: jaffle_shop    
     database: raw    
     freshness: # default freshness threshold    
       warn_after: {count: 12, period: hour}      
       error_after: {count: 24, period: hour}    
     loaded_at_field: _etl_loaded_at    
     
     tables:      
       - name: orders        
         freshness: # stricter time threshold      
           warn_after: {count: 6, period: hour}          
           error_after: {count: 12, period: hour}      
        
       - name: customers # inherits the parent configuration      
       
       
       - name: product_skus        
         freshness: null # do not check freshness for this table

$ dbt source freshness

SELECT
  MAX(_etl_loaded_at) AS max_loaded_at,
  CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()) AS calculated_at
FROM raw.jaffle_shop.orders

Snapshots

When a source table overwrites existing field values through updates, the table retains only the current state, and historical states cannot be queried directly. dbt snapshots preserve historical versions of records in source tables to support historical data queries and analysis. For more information, see Snapshots documentation.

dbt snapshots record changes by using Slowly Changing Dimension Type 2 (SCD Type 2). When a record change is detected, the old version is retained and a new version is added. The dbt_valid_from and dbt_valid_to fields identify the validity period of each version.

For example, in the orders table, the status field indicates the order status. Before the order ships, the status is pending:

After the order ships, status is updated to shipped, which overwrites the original pending value:

If a snapshot captures both states before and after the update, the snapshot table retains both record versions and their validity periods:

snapshots:
  - name: orders_snapshot
    relation: source('jaffle_shop', 'orders')  # reference to a source table or model
    description: "Snapshot of order status changes"
    config:
      database: analytics
      schema: snapshots
      alias: your_custom_snapshot
      strategy: timestamp  # strategy type: timestamp or check
      unique_key: id       # unique key column or expression
      check_cols: ["status"]  # configure only for the check strategy
      updated_at: updated_at  # configure only for the timestamp strategy
      dbt_valid_to_current: "to_date('9999-12-31')"  # custom dbt_valid_to value for current records
      snapshot_meta_column_names: {}  # custom names for meta columns
      hard_deletes: ignore  # hard delete handling: ignore, invalidate, or new_record

Macro

Macro properties can be declared in any properties.yml file. However, macro properties cannot be configured in the dbt_project.yml file or through a config() block. For macro paths, see macro-paths.

Example configuration file (macros/<filename>.yml):

version: 2

macros:
  - name: <macro name>
    description: <Markdown description>
    docs:
      show: true | false  # whether to show the macro in the documentation
    meta: {<dictionary>}        # custom metadata (optional)
    arguments:
      - name: <argument name>
        type: <data type string>
        description: <Markdown description>
      - ...  # declare properties of other arguments

  - name: ...  # declare properties of other macros

Docs

dbt docs includes two supported subcommands: generate and serve. For more information, see dbt docs command documentation.

dbt docs generate

dbt docs serve [--profiles-dir PROFILES_DIR]
               [--profile PROFILE] [--target TARGET]
               [--host HOST]
               [--port PORT]
               [--no-browser]