dbt-trino

Introduction

dbt is a data transformation workflow tool that lets teams quickly and collaboratively deploy analytics code, following software engineering best practices like modularity, CI/CD, testing, and documentation. It enables anyone who knows SQL to build production-grade data pipelines.

One frequently asked question in the context of using dbt tool is:

Can I connect my dbt project to two databases?

(see the answered question on the dbt website).

TL;DR dbt stands for transformation as in T within ELT pipelines, it doesn't move data from source to a warehouse.

dbt-trino adapter uses Trino as a underlying query engine to perform query federation across disperse data sources. Trino connects to multiple and diverse data sources (available connectors) via one dbt connection and process SQL queries at scale. Transformations defined in dbt are passed to Trino which handles these SQL transformation queries and translates them to queries specific to the systems it connects to create tables or views and manipulate data.

This repository represents a fork of the dbt-presto with adaptations to make it work with Trino.

Compatibility

This dbt plugin has been tested against Trino version 478, Starburst Enterprise version 482-e.1 and Starburst Galaxy.

Setup & Configuration

For information on installing and configuring your profile to authenticate to Trino or Starburst, please refer to Starburst and Trino Setup in the dbt docs.

Trino- and Starburst-specific configuration

For Trino- and Starburst-specific configuration, you can refer to Starburst (Trino) configurations on the dbt docs site.

Query routing

client_tags and http_headers can be set per model, for the statements that model runs: client_tags replaces the profile's list entirely, while http_headers is merged on top of the profile's, with the model's values winning on a name collision.

{{ config(materialized='table', client_tags=['fault-tolerant']) }}

Trino routers pick a cluster per statement from the request headers, so this lets a single dbt run send individual models to different clusters - a large incremental model to a fault-tolerant cluster, say, while the rest of the project stays on the default one. With Starburst Galaxy the routing decision is made from the client tags and the user's role.

Two things to keep in mind:

• A query whose tags match no routing rule is rejected, not sent to a default cluster (unless a default routing rule is configured). Give your profile tags that match a rule, since statements dbt runs outside a model - metadata queries, for instance - use the profile's. This includes dbt's own pre-run bookkeeping, such as listing and creating schemas and populating the relation cache for every database and schema touched by the run: those queries run once up front, before any model executes and without any per-model context, so they can never pick up a model's client_tags/http_headers - only your profile's.
• X-Trino-Client-Tags cannot be set through http_headers; Trino builds that header itself from client_tags.
• A model's tests are separate nodes with their own config, so they do not inherit the model's client_tags/http_headers and run with the profile's instead. Configure routing on the tests themselves - inline on each test's config, or path-scoped under tests:/data_tests: in dbt_project.yml - if they need to reach the same cluster as the model.

User-defined functions (SQL UDFs)

dbt-trino supports scalar SQL user-defined functions: put the function body in functions/<name>.sql and its signature in a YAML file. Python, JavaScript and aggregate functions are not supported.

-- functions/price_for_xlarge.sql
price * 2

# functions/price_for_xlarge.yml
functions:
  - name: price_for_xlarge
    description: Calculate the price for the xlarge version of a standard item
    config:
      database: memory
      schema: functions
      volatility: deterministic
    arguments:
      - name: price
        data_type: double
    returns:
      data_type: double

The function is called with {{ function('price_for_xlarge') }}(price). Things to keep in mind:

• Where a function can live: Only catalogs whose connector stores functions work.
• The body is a single expression: dbt-trino generates RETURN <body>, and Trino does not allow queries in functions, so SELECT ... is rejected by Trino when the function is created.
• Types are passed to Trino as written: Use double or real rather than float.
• Volatility: deterministic and non-deterministic become DETERMINISTIC and NOT DETERMINISTIC. stable is not supported and is ignored with a warning. Trino assumes DETERMINISTIC when it is unset and rejects a declaration that contradicts the body, so a body that calls random() needs volatility: non-deterministic.
• Not supported by Trino: argument default_value, grants (a warning is logged) and persist_docs. The function description is stored as the function's COMMENT.
• Overloads: Trino identifies a function by its name and argument types. Changing an argument type creates a second function and leaves the old one in place. The old function must be dropped manually.
• Starburst Galaxy: Functions can only be created in the built-in galaxy.functions schema. Any other catalog fails with functions can only be created in the built-in 'galaxy.functions' catalog and schema. Set database: galaxy and schema: functions on the function.

Contributing

• Want to report a bug or request a feature? Let us know on Slack in the #db-starburst-and-trino channel, or on Trino slack in the #python channel, or open an issue
• Want to help us build dbt-trino? Check out the Contributing Guide

Release process

First 5 steps are ONLY relevant for bumping minor version:

1. Create 1.x.latest branch from the latest tag corresponding to current minor version, e.g. git checkout -b 1.6.latest v1.6.2 (when bumping to 1.7). Push branch to remote. This branch will be used for potential backports.
2. Create new branch (Do not push below commits to 1.x.latest). Add a new entry in .changes/0.0.0.md that points to the newly created latest branch.
3. Run changie merge to update README.md. After that, remove changie files and folders related to current minor version. Commit.
4. Bump version of dbt-tests-adapter. Commit.
5. Merge these 2 commits into the master branch. Add a Skip Changlelog label to the PR.

Continue with the next steps for a minor version bump. Start from this point for a patch version bump:

1. Run Version Bump workflow. The major and minor part of the dbt version are used to associate dbt-trino's version with the dbt version.
2. Merge the bump PR. Make sure that test suite pass.
3. Run dbt-trino release workflow to release dbt-trino to PyPi and GitHub.

Backport process

Sometimes it is necessary to backport some changes to some older versions. In that case, create branch from x.x.latest branch. There is a x.x.latest for each minor version, e.g. 1.3.latest. Make a fix and open PR back to x.x.latest. Create changelog by changie new as ususal, as separate changlog for each minor version is kept on every x.x.latest branch. After merging, to make a release of that version, just follow instructions from Release process section, but run every workflow on x.x.latest branch.

Code of Conduct

Everyone interacting in the dbt project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the PyPA Code of Conduct.