Dbt

---
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

```bash
uv venv && source .venv/bin/activate
uv pip install dbt-core dbt-trino==1.9.3
```

## profiles.yml

```yaml
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: none` for API key auth
- `database: 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`:

```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

```yaml
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:**
```sql
{{
  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:**
```sql
{{
  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

```bash
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

```yaml
# 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:**
```python
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') }}` |

## Resources

- https://docs.getdbt.com/
- https://github.com/starburstdata/dbt-trino