Answering Natural Language Questions with dbt

Overview

Answer data questions using the best available method: semantic layer first, then SQL modification, then model discovery, then manifest analysis. Always exhaust options before saying "cannot answer."

Use for: Business questions from users that need data answers

• "What were total sales last month?"
• "How many active customers do we have?"
• "Show me revenue by region"

Not for:

• Validating model logic during development
• Testing dbt models or semantic layer definitions
• Building or modifying dbt models
• dbt run, dbt test, or dbt build workflows

Decision Flow

mermaid
1flowchart TD
2    start([Business question received])
3    check_sl{Semantic layer tools available?}
4    list_metrics[list_metrics]
5    metric_exists{Relevant metric exists?}
6    get_dims[get_dimensions]
7    sl_sufficient{SL can answer directly?}
8    query_metrics[query_metrics]
9    answer([Return answer])
10    try_compiled[get_metrics_compiled_sql<br/>Modify SQL, execute_sql]
11    check_discovery{Model discovery tools available?}
12    try_discovery[get_mart_models<br/>get_model_details<br/>Write SQL, execute]
13    check_manifest{In dbt project?}
14    try_manifest[Analyze manifest/catalog<br/>Write SQL]
15    cannot([Cannot answer])
16    suggest{In dbt project?}
17    improvements[Suggest semantic layer changes]
18    done([Done])
19
20    start --> check_sl
21    check_sl -->|yes| list_metrics
22    check_sl -->|no| check_discovery
23    list_metrics --> metric_exists
24    metric_exists -->|yes| get_dims
25    metric_exists -->|no| check_discovery
26    get_dims --> sl_sufficient
27    sl_sufficient -->|yes| query_metrics
28    sl_sufficient -->|no| try_compiled
29    query_metrics --> answer
30    try_compiled -->|success| answer
31    try_compiled -->|fail| check_discovery
32    check_discovery -->|yes| try_discovery
33    check_discovery -->|no| check_manifest
34    try_discovery -->|success| answer
35    try_discovery -->|fail| check_manifest
36    check_manifest -->|yes| try_manifest
37    check_manifest -->|no| cannot
38    try_manifest -->|SQL ready| answer
39    answer --> suggest
40    cannot --> done
41    suggest -->|yes| improvements
42    suggest -->|no| done
43    improvements --> done

Quick Reference

Approach 1: Semantic Layer Query

When list_metrics and query_metrics are available:

1. list_metrics - find relevant metric
2. get_dimensions - verify required dimensions exist
3. query_metrics - execute with appropriate filters

If semantic layer can't answer directly (missing dimension, need custom logic) → go to Approach 2.

Approach 2: Modified Compiled SQL

When semantic layer has the metric but needs minor modifications:

• Missing dimension (join + group by)
• Custom filter not available as a dimension
• Case when logic for custom categorization
• Different aggregation than what's defined

1. get_metrics_compiled_sql - get the SQL that would run (returns raw SQL, not Jinja)
2. Modify SQL to add what's needed
3. execute_sql to run the raw SQL
4. Always suggest updating the semantic model if the modification would be reusable

sql
1-- Example: Adding sales_rep dimension
2WITH base AS (
3    -- ... compiled metric logic (already resolved to table names) ...
4)
5SELECT base.*, reps.sales_rep_name
6FROM base
7JOIN analytics.dim_sales_reps reps ON base.rep_id = reps.id
8GROUP BY ...
9
10-- Example: Custom filter
11SELECT * FROM (compiled_metric_sql) WHERE region = 'EMEA'
12
13-- Example: Case when categorization
14SELECT
15    CASE WHEN amount > 1000 THEN 'large' ELSE 'small' END as deal_size,
16    SUM(amount)
17FROM (compiled_metric_sql)
18GROUP BY 1

Note: The compiled SQL contains resolved table names, not {{ ref() }}. Work with the raw SQL as returned.

Approach 3: Model Discovery

When no semantic layer but get_all_models/get_model_details available:

1. get_mart_models - start with marts, not staging
2. get_model_details for relevant models - understand schema
3. Write SQL using {{ ref('model_name') }}
4. show --inline "..." or execute_sql

Prefer marts over staging - marts have business logic applied.

Approach 4: Manifest/Catalog Analysis

When in a dbt project but no MCP server:

1. Check for target/manifest.json and target/catalog.json
2. Filter before reading - these files can be large

bash
1# Find mart models in manifest
2jq '.nodes | to_entries | map(select(.key | startswith("model.") and contains("mart"))) | .[].value | {name: .name, schema: .schema, columns: .columns}' target/manifest.json
3
4# Get column info from catalog
5jq '.nodes["model.project_name.model_name"].columns' target/catalog.json

3. Write SQL based on discovered schema
4. Explain: "This SQL should run in your warehouse. I cannot execute it without database access."

Suggesting Improvements

When in a dbt project, suggest semantic layer changes after answering (or when cannot answer):

Stay at semantic layer level. Do NOT suggest:

• Database schema changes
• ETL pipeline modifications
• "Ask your data engineering team to..."

Rationalizations to Resist

Red Flags - STOP

• Writing SQL without checking if semantic layer can answer
• Saying "cannot answer" without trying all 4 approaches
• Suggesting database-level fixes for semantic layer gaps
• Reading entire manifest.json without filtering
• Using staging models when mart models exist
• Using this to validate model correctness rather than answer business questions

Common Mistakes