dbt-maxcompute

Welcome to the dbt-maxCompute repository! This project aims to extend the capabilities of dbt (data build tool) for users of Alibaba MaxCompute, a cutting-edge data processing platform.

What is dbt?

dbt empowers data analysts and engineers to transform their data using software engineering best practices. It serves as the T in the ELT (Extract, Load, Transform) process, allowing users to organize, cleanse, denormalize, filter, rename, and pre-aggregate raw data, making it analysis-ready.

About MaxCompute

MaxCompute is Alibaba Group's cloud data warehouse and big data processing platform, supporting massive data storage and computation, widely used for data analysis and business intelligence. With MaxCompute, users can efficiently manage and analyze large volumes of data and gain real-time business insights.

This repository contains the foundational code for the dbt-maxcompute adapter plugin. For guidance on developing the adapter, please refer to the official documentation.

Adapter Versioning

This adapter follows semantic versioning and the dbt Core 1.11 compatibility line. Individual capabilities can have a narrower maturity level than the adapter package:

Production Preview and Beta limitations are documented in their respective guides. Pre-release package versions such as 1.11.3b2 do not replace the latest stable release.

Getting Started

Install the plugin

# Python 3.11 is a reproducible baseline for MaxFrame custom-UDF workloads.
conda create --name dbt-maxcompute-example python=3.11
conda activate dbt-maxcompute-example

pip install dbt-core
pip install dbt-maxcompute

To run Python models on MaxFrame, install the optional MaxFrame runtime:

pip install "dbt-maxcompute[maxframe]"

Configure dbt profile:

1. Create a file in the ~/.dbt/ directory named profiles.yml.
2. Copy the following and paste into the new profiles.yml file. Make sure you update the values where noted.

jaffle_shop: # this needs to match the profile in your dbt_project.yml file
  target: dev
  outputs:
    dev:
      type: maxcompute
      project: dbt-example # Replace this with your project name
      schema: default # Replace this with schema name, e.g. dbt_bilbo
      endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint
      auth_type: access_key
      access_key_id: XXX # Replace this with your accessId(ak)
      access_key_secret: XXX # Replace this with your accessKey(sk)

Currently we support the following parameters：

Note: Fields marked with "Required" must be explicitly specified in your configuration.

Run your dbt models

If you are new to DBT, we have prepared a Tutorial document for your reference. Of course, you can also access the official documentation provided by DBT (but some additional adaptations may be required for MaxCompute)

Configure Your dbt Models

You can customize dbt materialization behavior through model configurations. For general dbt configuration reference, see the official documentation: dbt Model Configs.

While dbt core provides native configurations like materialized and sql_header, this section focuses on dbt-maxcompute specific configurations that control table creation behavior during materialization.

dbt-maxcompute Specific Configurations

Default SQL Hints

MaxCompute supports global SQL hints to control query behavior and optimize performance. The following are the default global hints used by our system:

odps.sql.type.system.odps2: "true"
odps.sql.decimal.odps2: "true"
odps.sql.allow.fullscan: "true"
odps.sql.select.output.format: "csv"
odps.sql.submit.mode: "script"
odps.sql.allow.cartesian: "true"
odps.sql.allow.schema.evolution: "true"
odps.table.append2.enable": "true"

You can override these defaults by specifying your own sql_hints use model config. Your custom hints will be merged with the defaults — you do not need to repeat the entire list unless you want to change specific values.

MaxQA (Interactive Query Acceleration)

MaxQA (MCQA V2) is MaxCompute's interactive query acceleration engine. It provides significantly faster execution for suitable workloads — queries that take 30+ seconds in offline mode can often complete in under 5 seconds with MaxQA.

Enable MaxQA in your profile

my_profile:
  target: dev
  outputs:
    dev:
      type: maxcompute
      project: my_project
      schema: default
      endpoint: http://service.cn-hangzhou.maxcompute.aliyun.com/api
      access_key_id: "{{ env_var('ODPS_ACCESS_ID') }}"
      access_key_secret: "{{ env_var('ODPS_SECRET_ACCESS_KEY') }}"
      execution_mode: maxqa
      quota_name: my_interactive_quota   # optional

When execution_mode is set to maxqa, all SQL is submitted through the MaxQA endpoint. By default, server-side fallback is enabled (maxqa_fallback: true), so DDL and complex queries that MaxQA cannot handle are automatically routed to the offline engine.

Fallback configuration

Per-model override

You can override the execution mode on individual models using sql_hints:

-- Force a heavy model to use offline, even when the profile default is maxqa
{{ config(
    materialized='table',
    sql_hints={'dbt.execution_mode': 'offline'}
) }}
SELECT ...

-- Use MaxQA for a specific model when the profile default is offline
{{ config(
    materialized='table',
    sql_hints={'dbt.execution_mode': 'maxqa', 'dbt.quota_name': 'my_quota'}
) }}
SELECT ...

The dbt.execution_mode and dbt.quota_name hints are consumed by the adapter and never sent to MaxCompute.

MaxFrame Python Models

Python models can use MaxFrame DataFrames with the standard dbt ref, source, config, and this objects. The adapter creates an isolated MaxFrame session, writes to a dbt intermediate table, and then uses the normal table swap flow so an existing target is not replaced until the MaxFrame job succeeds.

Start with the MaxFrame Python user guide, then use the MaxFrame Python Models reference for complete installation, partitioning, incremental, operations, and migration details. Run the production readiness checklist before promoting a project workload.

def model(dbt, session):
    dbt.config(
        materialized="table",
        submission_method="maxframe",
        timeout=3600,
        lifecycle=7,
    )

    orders = dbt.ref("stg_orders")
    return orders[orders.amount > 0][["order_id", "amount"]]

Run it like any other dbt model:

dbt run --select path:models/my_maxframe_model.py

The dbt output includes the MaxFrame session ID and reports whether LogView is available. Signed LogView URLs are not written to logs or artifacts because they contain temporary access tokens. Model-level sql_hints are forwarded to MaxFrame, and maxframe_quota_name can be set in the model or profile.

Table and incremental materializations support regular MaxCompute partitions. Both the MaxCompute plural form and the BigQuery-style singular form are accepted, which makes migrated Python models easier to reuse:

def model(dbt, session):
    dbt.config(
        materialized="incremental",
        incremental_strategy="merge",
        unique_key="order_id",
        partition_by={"field": "ds", "data_type": "string"},
        on_schema_change="sync_all_columns",
    )

    orders = dbt.ref("stg_orders")
    if dbt.is_incremental:
        # Replace this predicate with the project's watermark policy.
        orders = orders[orders["order_id"] > 1_000_000]
    return orders

Time columns create MaxCompute automatic partitions. granularity and generate_column_name control the generated partition column:

partition_by={
    "field": "event_time",
    "data_type": "timestamp",
    "granularity": "day",
    "generate_column_name": "ds",
}

Supported incremental strategies are merge, append, delete+insert, insert_overwrite, and microbatch. Incremental targets are created as transactional MaxCompute tables because MaxCompute MERGE requires that property. Python microbatch models apply dbt's half-open event-time window to the returned DataFrame before writing each batch.

Writes are failure-safe: table models and Python full refreshes build an intermediate relation and swap it only after the MaxFrame DAG succeeds. Failed DAG output relations are removed with bounded retries. Automatic-partition staging tables use lifecycle 1 and are cleaned before reuse and after a successful run. Transient DAG transport errors are retried in a new MaxFrame session (maxframe_retries, default 2). The compiled Python model is re-executed against the new session after its failed staging relation is cleaned. Successful remote dependency builds use MaxFrame's production PythonPack cache by default; set maxframe_pythonpack_production: false only when short-lived dependency builds should not be retained.

Current intentional differences from dbt-bigquery Python models:

• cluster_by is rejected because MaxCompute partitioning and transactional tables do not provide BigQuery's clustering contract.
• Enforced dbt model contracts are rejected for Python models. Schema changes on incremental models should use on_schema_change.
• packages is not installed dynamically into the dbt process. Install graph-building dependencies in the dbt runtime. For dependencies used by a remote MaxFrame UDF, decorate the function with maxframe.udf.with_python_requirements(...).

Python UDFs

dbt functions: resources can create persistent MaxCompute Python scalar and aggregate functions. Scalar functions keep the standard dbt plain-function authoring experience; aggregate functions use MaxCompute's bounded BaseUDAF buffer lifecycle.

See the Python UDF guide for deployment safety, dependencies, data types, UDAF examples, and BigQuery migration differences.

# functions/double_value.py
def main(value):
    return None if value is None else value * 2

# functions/double_value.yml
functions:
  - name: double_value
    config:
      entry_point: main
      runtime_version: "3.11"
    arguments:
      - name: value
        data_type: bigint
    returns:
      data_type: bigint

Build and reference it through dbt's normal function DAG:

dbt build --select double_value

select {{ function('double_value') }}(quantity) from {{ ref('orders') }}

dbt-maxcompute defaults SQL UDF execution to MaxCompute CPython 3.11 (cp311). Third-party libraries must be uploaded as compatible MaxCompute resources; dynamic dbt packages installation is not available on this runtime. To avoid repeating runtime_version on every Python function, set functions: {+runtime_version: "3.11"} once in dbt_project.yml.

Compatible dbt Packages for MaxCompute

The following community-maintained dbt packages have been verified to work with dbt-maxcompute:

1. dbt-date (MaxCompute Edition)
2. dbt-utils (MaxCompute Edition)
3. dbt-expectations (MaxCompute Edition)
4. elementary (MaxCompute Edition)
5. dbt-project-evaluator (MaxCompute Edition)

Known Limitations

Due to MaxCompute engine characteristics, the following limitations apply:

Developers Guide

If you want to contribute or develop the adapter, use the following command to set up your environment:

pip install -r dev-requirements.txt

Reporting Bugs and Contributing

Your feedback helps improve the project:

• To report bugs or request features, please open a new issue on GitHub.

Code of Conduct

We are committed to fostering a welcoming and inclusive environment. All community members are expected to adhere to the dbt Code of Conduct.