dbt
dbt with TD Trino. Covers profiles.yml setup (method:none, user:TD_API_KEY), required override macros (no CREATE VIEW), TD_INTERVAL in models, and TD Workflow deployment.
When & Why to Use This Skill
This Claude skill provides a comprehensive integration guide and technical boilerplate for using dbt (data build tool) with Treasure Data Trino. It streamlines the setup of data transformation pipelines by providing optimized profiles.yml configurations, essential macro overrides for Trino compatibility (such as handling the lack of CREATE VIEW support), and specialized model patterns for Treasure Data's time-based partitioning. It also covers end-to-end deployment strategies using Treasure Data Workflows.
Use Cases
- Setting up a robust dbt environment for Treasure Data Trino using API key authentication and secure connection settings.
- Implementing custom dbt macros to bypass Trino-specific limitations, ensuring seamless table materialization and metadata caching.
- Developing high-performance incremental data models utilizing TD_INTERVAL and JST timezone handling for efficient data processing.
- Automating production-grade data pipelines by deploying dbt projects into Treasure Data Workflows (Digdag) using custom Docker images.
- Troubleshooting common dbt-trino integration errors such as connector support issues and variable definition conflicts.
| name | dbt |
|---|---|
| description | dbt with TD Trino. Covers profiles.yml setup (method:none, user:TD_API_KEY), required override macros (no CREATE VIEW), TD_INTERVAL in models, and TD Workflow deployment. |
dbt with Treasure Data Trino
Installation
uv venv && source .venv/bin/activate
uv pip install dbt-core dbt-trino==1.9.3
profiles.yml
td:
target: dev
outputs:
dev:
type: trino
method: none # Not 'ldap'
user: "{{ env_var('TD_API_KEY') }}"
password: dummy # Not used
host: api-presto.treasuredata.com
port: 443
database: td # Always 'td'
schema: your_dev_database # Your TD database name
threads: 4
http_scheme: https
session_properties:
query_max_run_time: 1h
Key TD settings:
method: nonefor API key authdatabase: td(always)schema: your_td_database(what you see in TD Console)
Required Override Macros
TD doesn't support CREATE VIEW. Create macros/override_dbt_trino.sql:
{% macro trino__create_view_as(relation, sql) -%}
create or replace table {{ relation }} as (
{{ sql }}
);
{%- endmacro %}
{% macro trino__list_relations_without_caching(schema_relation) %}
{% call statement('list_relations_without_caching', fetch_result=True) %}
select
table_catalog as "database",
table_schema as "schema",
table_name as "name",
table_type as "type"
from {{ schema_relation }}.information_schema.tables
where table_schema = '{{ schema_relation.schema }}'
{% endcall %}
{{ return(load_result('list_relations_without_caching').table) }}
{% endmacro %}
dbt_project.yml
name: 'my_td_project'
version: '1.0.0'
config-version: 2
profile: 'td'
flags:
require_certificate_validation: true
vars:
target_range: '-3M/now'
models:
my_td_project:
+materialized: table
+on_schema_change: "append_new_columns"
Model Patterns
Basic model:
{{
config(materialized='table')
}}
SELECT
TD_TIME_STRING(time, 'd!', 'JST') as date,
COUNT(*) as event_count
FROM {{ source('raw', 'events') }}
WHERE TD_INTERVAL(time, '{{ var("target_range", "-7d") }}', 'JST')
GROUP BY 1
Incremental model:
{{
config(
materialized='incremental',
unique_key='event_id'
)
}}
SELECT *
FROM {{ source('raw', 'events') }}
WHERE TD_INTERVAL(time, '{{ var("target_range", "-1d") }}', 'JST')
{% if is_incremental() %}
AND time > (SELECT MAX(time) FROM {{ this }})
{% endif %}
Commands
dbt debug # Test connection
dbt run # Run all
dbt run --select daily_events # Run specific
dbt run --vars '{"target_range": "-1d"}' # Override variable
dbt run --full-refresh # Rebuild incremental
dbt test # Run tests
TD Workflow Deployment
# dbt_workflow.dig
timezone: Asia/Tokyo
schedule:
daily>: 03:00:00
_export:
docker:
image: "treasuredata/customscript-python:3.12.11-td1"
_env:
TD_API_KEY: ${secret:td.apikey}
+setup:
py>: tasks.InstallPackages
+dbt_run:
py>: dbt_wrapper.run_dbt
command_args: ['run', '--target', 'prod']
tasks.py:
def InstallPackages():
import subprocess, sys
subprocess.check_call([sys.executable, '-m', 'pip', 'install', 'dbt-core==1.10.9', 'dbt-trino==1.9.3'])
Common Errors
| Error | Fix |
|---|---|
| connector does not support creating views | Add override macro above |
| Table ownership information not available | Add override macro for list_relations |
| Var 'target_range' is undefined | Add default: {{ var('target_range', '-1d') }} |