dbt + StarRocks Performance Tuning
When to Use
- Incremental dbt models that run slowly due to poor partition filtering
- dbt DAG with wide fan-out causing excessive table rebuilds
- Need to run ANALYZE TABLE after dbt model refreshes
- Optimizing join order and query plans for complex dbt models
- Tuning thread count and model concurrency for large dbt projects
Partition-Aware Incremental Filters
The most impactful optimization: ensure incremental models only scan new partitions.
-- BAD: scans entire source table even in incremental mode
{{ config(
materialized='incremental',
incremental_strategy='append',
partition_by={"field": "created_at", "data_type": "date", "granularity": "day"}
) }}
SELECT * FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }})
{% endif %}
The MAX(event_ts) subquery scans this — fine for small tables but expensive for billions of rows.
-- GOOD: use partition column for both filter and watermark
{{ config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={"field": "event_date", "data_type": "date", "granularity": "day"}
) }}
{% set lookback_days = 3 %}
SELECT
DATE(event_ts) AS event_date,
user_id,
event_type,
event_ts
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
-- Reprocess last N days to handle late-arriving data
WHERE DATE(event_ts) >= DATE_SUB(CURDATE(), INTERVAL {{ lookback_days }} DAY)
{% endif %}
With insert_overwrite, dbt replaces only the matching partitions — no full scan of this.
Late-Arriving Data Window
Balance completeness vs performance:
{% set incremental_lookback_days = var('lookback_days', 3) %}
{% if is_incremental() %}
WHERE DATE(created_at) >= DATE_SUB(
-- Use a fixed reference point, not NOW(), for reproducibility
DATE('{{ var("ds", modules.datetime.date.today().isoformat()) }}'),
INTERVAL {{ incremental_lookback_days }} DAY
)
{% endif %}
Run with override:
dbt run --vars '{"lookback_days": 7, "ds": "2024-01-15"}'
ANALYZE TABLE via Post-Hook
Always refresh CBO statistics after materializing a table:
-- models/gold/orders_daily.sql
{{ config(
materialized='table',
post_hook=[
"ANALYZE TABLE {{ this }} WITH ASYNC MODE",
]
) }}
SELECT ...
For incremental models with partition granularity:
{{ config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={"field": "dt", "data_type": "date", "granularity": "day"},
post_hook=[
-- Analyze only today's partition (much faster than full table)
"ANALYZE TABLE {{ this }} PARTITION (p{{ run_started_at.strftime('%Y%m%d') }}) WITH ASYNC MODE",
]
) }}
Bulk ANALYZE in post-hook for large projects:
# dbt_project.yml
models:
my_project:
gold:
+post-hook: "ANALYZE TABLE {{ this }} WITH ASYNC MODE"
Query Hints in dbt Models
Embed StarRocks query hints directly in model SQL for complex join optimization:
-- models/gold/revenue_report.sql
{{ config(materialized='table') }}
-- Hint: force join order (orders first, then products)
SELECT /*+ LEADING(o p c) JOIN_METHOD(o, p, BROADCAST) */
p.category,
c.region,
COUNT(*) AS order_count,
SUM(o.amount) AS revenue
FROM {{ ref('orders') }} o
JOIN {{ ref('dim_products') }} p USING (product_id)
JOIN {{ ref('dim_customers') }} c USING (customer_id)
WHERE o.dt = CURDATE()
GROUP BY p.category, c.region
Pre-Hook: Ensure Partition Exists
For models that INSERT OVERWRITE into manually managed partitions:
{{ config(
materialized='incremental',
incremental_strategy='insert_overwrite',
pre_hook=[
"""
ALTER TABLE {{ this }}
ADD PARTITION IF NOT EXISTS p{{ run_started_at.strftime('%Y%m%d') }}
VALUES [("{{ run_started_at.strftime('%Y-%m-%d') }}"),
("{{ (run_started_at + modules.datetime.timedelta(days=1)).strftime('%Y-%m-%d') }}"))
"""
]
) }}
Materialized View as dbt Model
Create a StarRocks async MV using a dbt custom materialization or post-hook:
-- Approach 1: Create MV in a post-hook on the base table
{{ config(
materialized='table',
post_hook=[
"""
CREATE MATERIALIZED VIEW IF NOT EXISTS {{ this }}_mv
DISTRIBUTED BY HASH(region) BUCKETS 8
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM {{ this }}
GROUP BY region
"""
]
) }}
Thread and Concurrency Tuning
profiles.yml Thread Config
starrocks_project:
outputs:
prod:
type: starrocks
threads: 8 # concurrent model builds (default: 1)
# ...
Which Models Can Run in Parallel
dbt parallelizes models with no dependencies between them. Ensure your DAG is as wide as possible:
staging/stg_orders ─┐
staging/stg_customers ─┼──► silver/orders (sequential join)
staging/stg_products ─┘
silver/orders_by_region ─┐
silver/orders_by_product ─┼──► gold/revenue_report
silver/customer_segments ─┘
Avoid chains where each model depends on the previous — this prevents parallelism.
Model Selection for Minimal Rebuilds
# Only run models that changed + their downstream dependents
dbt run --select state:modified+
# Run only changed models, no downstream
dbt run --select state:modified
# Run only a specific model and its upstream dependencies
dbt run --select +orders
# Run a specific tag group
dbt run --select tag:daily
# Exclude expensive models from CI
dbt run --exclude tag:expensive
var() for Environment-Specific Config
{{ config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "dt",
"data_type": "date",
"granularity": var('partition_granularity', 'day')
},
properties={
"replication_num": var('replication_num', '3')
}
) }}
# dbt_project.yml
vars:
partition_granularity: "day"
replication_num: "3" # override to "1" in dev
dbt run --vars '{"replication_num": "1"}' --target dev
Materialization Choice vs Performance
| Model Type | Materialization | Rebuild Frequency | Performance |
|---|
| Staging (raw cleanup) | table | Daily | Full rebuild is cheap |
| Silver (CDC upsert) | incremental (unique_key) | Continuous | Only processes delta |
| Intermediate heavy joins | table | Daily | Avoid ephemeral for >100M rows |
| Gold partitions | incremental (insert_overwrite) | Daily | Replace partitions, not full table |
| Real-time metrics | MV via post-hook | Auto (async) | Near-real-time without DAG run |
| Rarely queried reports | view | On query | No storage cost |
Profiling Slow Models
# Show timing for each model
dbt run --select orders_daily --log-level debug 2>&1 | grep "Completed"
# Use dbt artifacts for timing analysis
cat target/run_results.json | python3 -c "
import json, sys
r = json.load(sys.stdin)
for n in sorted(r['results'], key=lambda x: x.get('execution_time', 0), reverse=True)[:10]:
print(f\"{n['execution_time']:.1f}s {n['unique_id']}\")
"
Anti-Patterns
incremental_strategy='append' on a growing table without watermark — re-runs append all historical rows repeatedly; always add an is_incremental() filter.MAX(event_ts) FROM {{ this }} for watermark on billion-row tables — scans the entire previous model; use partition column bounds instead.threads: 1 — serial execution for large dbt projects can take hours; set to 4-16 based on BE count.- No post-hook ANALYZE — CBO uses stale stats after model rebuild, producing bad plans for downstream queries; always ANALYZE after table materialization.
- Using
view for heavy aggregations — view recomputes the aggregation on every BI query; materialize as table or MV.
on_schema_change='sync_all_columns' on large tables — triggers full table rebuild every time a column is added; use append_new_columns instead.
References
- dbt-starrocks incremental:
github.com/StarRocks/starrocks/tree/main/contrib/dbt-connector
- ANALYZE TABLE:
docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/ANALYZE_TABLE/
- dbt model selection:
docs.getdbt.com/reference/node-selection/syntax
- Related skills:
[[dbt-starrocks-models]], [[starrocks-cbo]], [[starrocks-query-optimizer]], [[dbt-core]]