Queries¶

Queries define what data to fetch from your data sources. dbt Charts supports four types of queries. Most projects start with SQL.

SQL queries - Direct SQL against database tables (also how you query dbt models, via ref())
Values queries - Inline data embedded directly in your YAML
HTTP / API queries - Fetch JSON data from REST endpoints
Schema queries - Introspect configured sources, schemas, tables, and columns

Basic Query Structure¶

Every query has a unique name and defines what data to fetch. Query names must be unique within a file and across all imported files.

The simplest way to write a SQL query is a bare string: just the SQL itself. Set source: once at the board level and every string query uses it automatically:

source: my_postgres
variables:
  status:
    input: select
    options:
      static: ["new", "open", "solved"]
  date_range:
    input: daterange
    default: ["2024-01-01", "2024-12-31"]
queries:
  tickets: |
    SELECT
      status,
      priority,
      COUNT(*) AS ticket_count
    FROM dundersign.tickets
    WHERE {{ filter('status', status) }}
      AND {{ filter_date_range('created_at', date_range) }}
    GROUP BY 1, 2

When you need to override the source, use notes for AI search metadata (it never renders), or mix query types on the same board, use the dict form instead:

queries:
  tickets:
    notes: "Open tickets by priority"
    sql: |
      SELECT ...
    source: my_postgres

Query Types¶

1. SQL Queries (Baseline)¶

String shorthand: set source: once at the board level and write queries as bare SQL strings:

source: my_postgres
variables:
  status:
    input: select
    options:
      static: ["new", "open", "solved"]
  date_range:
    input: daterange
    default: ["2024-01-01", "2024-12-31"]
queries:
  tickets: |
    SELECT
      status,
      priority,
      COUNT(*) as ticket_count
    FROM dundersign.tickets
    WHERE {{ filter('status', status) }}
      AND {{ filter_date_range('created_at', date_range) }}
    GROUP BY 1, 2
    ORDER BY ticket_count DESC

Dict form: use when you need to override the source per query, set a target, or add a notes (never rendered; AI search metadata only):

queries:
  sales:
    sql: |
      SELECT ...
    source: my_postgres  # Required when no board-level source
    target: prod           # Optional: dbt target name (defaults to 'dev')

Use the pipe (|) after sql: (or directly after the query name for string shorthand) to start a YAML literal block. Everything you indent under it becomes one multiline string, so you can paste your SQL verbatim.

`setup_sql`: a preamble statement¶

setup_sql runs as its own statement on the same connection, immediately before sql. Use it for a warehouse-specific declaration your main query depends on; the canonical case is a BigQuery CREATE TEMP FUNCTION:

queries:
  scaled_revenue:
    setup_sql: |
      CREATE TEMP FUNCTION double_it(x FLOAT64) AS (x * 2);
    sql: SELECT month, double_it(revenue) AS revenue_x2 FROM dundersign_serving.monthly_metrics
    source: my_warehouse

setup_sql is non-nestable: it may not contain {{ queries.X }} references, and a query that references another query's SQL still picks up that query's setup_sql automatically (the engine walks the dependency chain and runs every preamble, once each, before the composed query). Only CREATE TEMP FUNCTION / CREATE TEMP TABLE / CREATE TEMP VIEW, and DuckDB's CREATE [OR REPLACE] MACRO, are accepted; anything else (including non-TEMP CREATE, DROP, INSERT) is rejected. Not supported for SQLite sources.

Database Connection via Source Configuration¶

dbt Charts uses source configuration for SQL database connections. A source name can refer to a dbt profile defined in profiles.yml. This means:

✅ Works with dbt profiles - dbt Charts reads your existing profiles.yml file
✅ Works with all dbt-supported databases - Postgres, Snowflake, BigQuery, Redshift, DuckDB, etc.
✅ Leverages dbt's adapter system - Handles database-specific quirks automatically
✅ Same credentials as dbt - Uses your existing database connections

Setting up sources:

Create or edit ~/.dbt/profiles.yml (or profiles.yml in your project directory)
Define your database connection using dbt's profile format:

# ~/.dbt/profiles.yml
my_postgres:
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: myuser
      password: mypass
      dbname: mydb
      schema: public
    prod:
      type: postgres
      host: prod.example.com
      port: 5432
      user: prod_user
      password: "{{ env_var('DB_PASSWORD') }}"
      dbname: analytics
      schema: public
  target: dev  # Default target

Reference the source in your queries using the source field (and optionally target):

queries:
  tickets:
    sql: SELECT * FROM dundersign.tickets
    source: my_postgres  # Uses 'my_postgres' source (matches dbt profile name)
    target: prod           # Uses 'prod' target (defaults to 'dev' if not specified)

For more information: - See dbt's profile documentation for complete profile configuration options - See dbt adapter documentation for database-specific connection parameters

To query a dbt model, write a plain SQL query against a dbt_profile source and resolve the model with dbt's ref() macro:

queries:
  model_sales:
    sql: SELECT month, region, revenue, order_count FROM {{ ref('fct_orders') }}
    source: my_postgres

ref() and source() are resolved from the dbt manifest, so the project needs one: run dbt parse (or any command that writes target/manifest.json). A dbt_profile source with target_path: reads <target_path>/manifest.json instead (details). target/ is gitignored dbt build output, so it must be regenerated after every fresh clone. Without a manifest the query fails with ERR-DBT-MANIFEST-MISSING.

Type Inference: Query types are automatically inferred from the keys present: - sql: → SQL Query (requires source field) - rows: or values: → Values Query (inline data) - url: → HTTP Query - Schema queries have no key-based inference; set type: schema explicitly

Use cases: - Most common use case - direct SQL queries - Full control over query logic - Works with any dbt-supported database - Requires source configuration (minimal setup - just profiles.yml)

2. Values Queries (Inline Data)¶

Values queries embed data directly in your YAML; no database or file needed. Perfect for examples, documentation, small reference datasets, and prototyping.

Dict rows syntax: each row is a key-value mapping:

queries:
  products:
    rows:
      - { product: "Widget A", revenue: 100, category: "Electronics" }
      - { product: "Widget B", revenue: 140, category: "Electronics" }
      - { product: "Gadget X", revenue: 180, category: "Accessories" }

Columns + values syntax: compact, SQL-style:

queries:
  products:
    columns: [product, revenue, category]
    values:
      - ["Widget A", 100, "Electronics"]
      - ["Widget B", 140, "Electronics"]
      - ["Gadget X", 180, "Accessories"]

Both syntaxes produce identical results. Use whichever reads better for your data.

Type Inference: Detected by the presence of rows: or values:; no explicit type: needed.

Use cases:

Examples and documentation that need to be self-contained
Small lookup/reference tables (categories, labels, thresholds)
Prototyping charts before connecting a real data source
Test fixtures and demo dashboards

3. HTTP / API Queries¶

Fetch data from any JSON API. This is useful for integrating external data sources (weather, stock prices) or calling your own services (ML models, Jupyter notebooks exposed as endpoints).

Runs under local dct only; Not supported on dbtcharts.com.

queries:
  prediction:
    type: http
    url: "https://api.model-service.com/predict"
    method: POST
    headers:
      Authorization: "Bearer {{ env.MODEL_API_KEY }}"
    body:
      features:
        region: "{{ region }}"
        date: "{{ date_range[0] }}"

Type Inference: Detected by the presence of url: or explicit type: http.

Use cases: - Calling ML inference endpoints - Fetching data from 3rd party APIs - Triggering server-side workflows (if read-only)

4. Schema Queries¶

Introspect the project's own configured sources, schemas, tables, and columns instead of fetching business data. Useful for building meta-dashboards (data catalogs, source health checks) on top of dbt Charts's own configuration:

queries:
  orders_columns:
    type: schema
    source: my_postgres
    schema: public
    table: orders

Set only the fields that narrow the introspection you want: omit table to list tables in a schema, omit schema to list schemas in a source, omit source to list configured sources, or set column (requires schema and table) to profile a single column's value distribution. Each field requires the ones above it: table needs schema, schema needs source. type: schema is always explicit; there's no key-based inference for this type.

Schema rows carry whatever metadata the source and its profiling depth provide, so their key set varies. fields: projects (and orders) the result rows to exactly the listed keys, the schema-query counterpart of a SQL SELECT list. A projected key a row lacks yields null, giving the consuming table a stable column set:

queries:
  tables:
    type: schema
    source: warehouse
    schema: analytics
    fields: [name, row_count]

Use cases: - Data-catalog / "what's in this warehouse" dashboards - Health checks over your own dbt_charts.yml sources

Field Reference¶

Values Queries¶

queries:
  <query_name>:
    # Option 1: Dict rows
    rows:                        # List of {key: value} dicts
      - { col1: val1, col2: val2 }

    # Option 2: Columns + values (compact)
    columns: [string]            # Column names
    values:                      # List of value arrays
      - [val1, val2]

HTTP / API Queries¶

Runs under local dct only; Not supported on dbtcharts.com.

queries:
  <query_name>:
    type: http                     # Required
    url: string                    # Required: URL (supports Jinja)
    method: GET | POST             # Optional: Default GET
    headers: Record<string, string> # Optional: HTTP headers
    params: Record<string, string>  # Optional: Query parameters
    body: any                      # Optional: JSON body (for POST)
    json_path: string              # Optional: dot-path to the row array in a nested response
    limit: number                  # Optional: max rows

Use json_path when the API wraps its rows inside an envelope object instead of returning a bare array. It's dot-notation ($.key.subkey), not full JSONPath; it must resolve to a list:

queries:
  orders_api:
    type: http
    url: "https://api.example.com/orders"
    json_path: "$.data.orders"   # response is { "data": { "orders": [ {...}, {...} ] } }

Omit json_path when the response is already a bare array of row objects.

Raw SQL Queries¶

String shorthand (recommended when all queries share one source):

source: my_postgres  # board-level default
queries:
  <query_name>: string  # bare SQL; inherits board source

Dict form (use when overriding source, setting target, or adding notes):

queries:
  <query_name>:
    sql: string           # Required: SQL query (supports Jinja)
    source: string        # Required if no board-level source
    target: string        # Optional: dbt target name (defaults to 'dev')
    dialect: string       # Optional: warehouse dialect the SQL is written in (see below)
    notes: string           # Optional: metadata for AI search; never rendered

Example:

source: my_postgres
queries:
  tickets: |
    SELECT * FROM dundersign.tickets
    WHERE status = 'solved'
    LIMIT 100

Database Connection via Source Configuration:

dbt Charts uses source configuration for SQL database connections. The source field must match a profile name defined in your dbt profiles.yml file (typically located at ~/.dbt/profiles.yml or in your project directory).

Source Configuration:

Create or edit ~/.dbt/profiles.yml with your database connection:

my_postgres:
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: myuser
      password: mypass
      dbname: mydb
      schema: public
    prod:
      type: postgres
      host: prod.example.com
      port: 5432
      user: prod_user
      password: "{{ env_var('DB_PASSWORD') }}"
      dbname: analytics
      schema: public
  target: dev  # Default target

Reference the source in your queries:

queries:
  tickets:
    sql: SELECT * FROM dundersign.tickets
    source: my_postgres  # Uses 'my_postgres' source
    target: prod           # Uses 'prod' target (defaults to 'dev' if not specified)

For more information: - See dbt's profile documentation for complete profile configuration options - See dbt adapter documentation for database-specific connection parameters (Postgres, Snowflake, BigQuery, Redshift, etc.)

Filters¶

SQL queries have no declarative filters: field; you filter by writing the WHERE clause yourself, using Jinja to reference variables. Reach for the filter() / filter_date_range() helpers (below) instead of hand-rolled conditionals; they handle null-checking, list-to-IN, and parameter binding for you.

Jinja in SQL¶

Board SQL is ordinary SQL with Jinja. You can use your board's variables, the filter() and filter_date_range() helpers, {{ queries.<name> }} to compose another query, and {{ ref() }} / {{ source() }} to name your dbt models and sources. Write it in one dialect and set dialect:: dbt Charts translates common SQL to the warehouse you run it on, and raises an error on constructs it can't translate. dbt Charts doesn't run dbt macros: put business logic in your models.

Reference variables directly in the SQL text:

variables:
  priority:
    input: text
  ticket_type:
    input: text
queries:
  filtered_tickets:
    sql: |
      SELECT priority, status, COUNT(*) AS ticket_count
      FROM dundersign.tickets
      WHERE priority = '{{ priority }}'
        AND ticket_type = '{{ ticket_type }}'
      GROUP BY priority, status
    source: my_warehouse

See the Expressions guide for the filter() / filter_date_range() helpers, which parameterize these Jinja-based filters against SQL injection and handle unset variables automatically.

Writing SQL once with `dialect:`¶

A board that has to run on several warehouses, such as a dashboard pack shipped inside a dbt package, would otherwise need one query per dialect. Set dialect: to the dialect you wrote the SQL in. When it differs from the source's warehouse, dbt Charts translates the query before running it:

queries:
  monthly_tickets:
    dialect: duckdb
    source: my_warehouse
    sql: |
      SELECT
        date_trunc('month', created_at) AS month,
        COUNT(*) AS tickets,
        SUM(solved) / NULLIF(COUNT(*), 0) AS solve_rate
      FROM {{ ref('fct_tickets') }}
      GROUP BY 1

Run against BigQuery, Snowflake, Redshift, Databricks, or Postgres, the same query gets each warehouse's spelling of date_trunc and integer division.

What to know:

Without dialect:, nothing is translated. The SQL runs as written.
Translation fails with ERR-SQL-DIALECT-UNSUPPORTED when the SQL does not parse, holds more than one statement, calls a function dbt Charts does not know, or uses a construct the target cannot express. It is not a guarantee: a function that exists in both dialects but behaves differently, or one the warehouse lacks, can still fail at the warehouse.
Queries joined with {{ queries.<name> }} must all set the same dialect:, or all leave it unset (ERR-QUERY-DIALECT-MIXED).
setup_sql is never translated. Write it in the source's own dialect.
dialect: is not supported on file sources, with {{ queries.<name>.cache }} (which runs on DuckDB), or with incremental:. Each raises ERR-SQL-DIALECT-UNSUPPORTED when the dialect differs from the one the query runs in.
A variable that ends up inside a string after translation, such as INTERVAL {{ n }} DAY on Postgres, is rejected with ERR-SQL-DIALECT-UNSUPPORTED. Write {{ n }} * INTERVAL '1 day' instead.
dialect: takes one spelling per warehouse: athena, bigquery, clickhouse, databricks, duckdb, mysql, postgres, presto, redshift, snowflake, spark, sqlite, sqlserver, trino.

Some behavior differs by warehouse even when the SQL translates. BigQuery weeks start on Sunday. On Postgres, datediff counts full 24-hour periods, while Snowflake counts calendar boundaries crossed.

Percentiles and medians. Percentile functions do not translate reliably. Compute an exact median in plain SQL instead: rank the rows and average the one or two middle values.

queries:
  median_resolution_by_group:
    dialect: duckdb
    source: my_warehouse
    sql: |
      WITH ranked AS (
        SELECT
          group_name,
          resolution_minutes,
          ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY resolution_minutes) AS rn,
          COUNT(*) OVER (PARTITION BY group_name) AS n
        FROM dundersign.tickets
        WHERE resolution_minutes IS NOT NULL
      )
      SELECT group_name, AVG(resolution_minutes) AS median_resolution_minutes
      FROM ranked
      WHERE rn IN (FLOOR((n + 1) / 2.0), CEIL((n + 1) / 2.0))
      GROUP BY group_name

Pivot (Cross-Tab Tables)¶

A tidy (long-form) query is pivoted into a cross-tab grid at render time by adding rows / columns / values channels to a table chart. A field on columns is the pivot: the SQL and the rows returned by the database are unchanged; only the table's layout is reshaped.

queries:
  tickets_long:
    sql: |
      SELECT priority, ticket_type, COUNT(*) AS ticket_count
      FROM dundersign.tickets
      GROUP BY priority, ticket_type
      ORDER BY priority, ticket_type
    source: warehouse

charts:
  tickets_crosstab:
    type: table
    query: queries.tickets_long
    rows: [priority]      # row headers down the left side
    columns: [ticket_type]  # distinct values become column headers (the pivot)
    values: [ticket_count]  # measure(s) that fill the cells

The table has one row per priority and one column per distinct ticket_type, with ticket_count filling the cells. List more than one measure on values (for example, values: [revenue, units]) to get a spanning column group per pivot value. If a cell maps to more than one source row the engine raises an error instead of silently aggregating; move the extra field onto rows or columns.

Column order: a pivot dimension whose values have a canonical order (dates and timestamps (chronological) or numbers (ascending)) renders its columns in that order regardless of the query's row order, the way every reference tool does. Any other dimension (plain strings, booleans, or a mix of dates and a literal like "Total") orders its columns by each value's first appearance in the result set, kept as one consistent order across the whole header when another dimension sorts, so an ORDER BY stays your lever for business-ordered categories and total-column placement.

That lever is not optional for those dimensions. First-seen order is whatever order the warehouse hands back, and a GROUP BY whose ORDER BY does not constrain the pivot dimension has no defined order at all; parallel aggregation can return a different one run to run, so the same data renders a different column order on different days. Rows are first-seen too, and unconditionally, no row dimension is ever sorted for you, so order by every field you put on rows and columns (that is why the example above orders by both priority and ticket_type), or accept an arbitrary order.

Pivot channels apply only to the table chart type; bar, line, and other charts use x / y / color and are unaffected.

Row and Column Totals¶

Totals are never computed by the render layer; a pivot table with totals needs a query that emits them. Two independent signals drive the two kinds of total:

Row totals (a trailing "Total" column): emit a literal "Total" value in the columns field alongside the real pivot values; it reshapes into a trailing column like any other value.
Column totals (a "Total" row): emit rows tagged with the same row-role column flat tables use for summary/total styling (style.table.row.role, for example, _df_row_role). Rows whose role resolves to "total" bucket by their rows value like every other row and get the total row's styling; a single grand total therefore means one consistent rows label (priority = 'Total' in the example) and merges into one row, while distinct total labels (per-group subtotals) stay distinct rows. Placement is the query's job: render never reorders rows (they keep first-seen order), so an ORDER BY decides where a total lands; put the total partition last to get a bottom total row. Render never merges or re-labels the values: the rows value on a total row is real data, not decoration.

queries:
  tickets_with_totals:
    sql: |
      SELECT priority, ticket_type, COUNT(*) AS ticket_count, 'value' AS row_role
      FROM dundersign.tickets
      GROUP BY priority, ticket_type
      UNION ALL
      SELECT priority, 'Total' AS ticket_type, COUNT(*) AS ticket_count, 'value' AS row_role
      FROM dundersign.tickets
      GROUP BY priority
      UNION ALL
      SELECT 'Total' AS priority, ticket_type, COUNT(*) AS ticket_count, 'total' AS row_role
      FROM dundersign.tickets
      GROUP BY ticket_type
      UNION ALL
      SELECT 'Total' AS priority, 'Total' AS ticket_type, COUNT(*) AS ticket_count, 'total' AS row_role
      FROM dundersign.tickets
      ORDER BY
        CASE WHEN priority = 'Total' THEN 1 ELSE 0 END, priority,
        CASE WHEN ticket_type = 'Total' THEN 1 ELSE 0 END, ticket_type
    source: warehouse

charts:
  tickets_crosstab_with_totals:
    type: table
    query: queries.tickets_with_totals
    rows: [priority]
    # Both the priority rows and the ticket_type columns reshape in first-seen
    # (query) order; rows always do, and ticket_type is a string dimension, the
    # kind whose columns follow query order (dates/numbers would sort instead),
    # so the ORDER BY above pins both: priorities then types, each with its
    # "Total" sorted last. The total ROW lands last only because the ORDER BY
    # puts the 'Total' priority-partition last; drop that clause and the total
    # row renders wherever the query returns it.
    columns: [ticket_type]
    values: [ticket_count]
    style:
      row:
        role: row_role    # tags "total" rows for styling; query order places them

The role tag marks rows, not cells: the row-total UNION branch above stays 'value' because it's still a detail priority row (it just carries a 'Total' ticket_type cell), and only the bottom-row partition is tagged 'total'. Tagging the row-total branch 'total' would collide it with that priority's detail bucket and raise a conflicting row roles error at render.

A role-tagged total row gets the same double-rule styling as a flat table's total row; pivot render only reshapes the role-tagged rows, it does not restyle or re-derive them. Nothing styles a column by value; the trailing total column is plain, it's only in the last position because of query order.

Best Practices¶

Reuse Queries Across Charts¶

Multiple charts can reference the same query, which is more efficient:

queries:
  sales:
    sql: SELECT month, region, SUM(revenue) AS total_revenue FROM dundersign.orders GROUP BY 1, 2
    source: my_postgres

rows:
  - cols:
      - chart1:
          query: queries.sales  # Same query
          type: bar
          x: month
          y: total_revenue
      - chart2:
          query: queries.sales  # Same query
          type: line
          x: month
          y: total_revenue
          color: region

Use Query References for DRY SQL¶

Instead of repeating SQL, reference other queries:

# ❌ Bad: Repetitive SQL
queries:
  open_tickets:
    sql: |
      SELECT
        priority,
        COUNT(*) as ticket_count
      FROM dundersign.tickets
      WHERE status = 'new'
      GROUP BY 1
    source: my_postgres

  resolved_tickets:
    sql: |
      SELECT
        priority,         # Repeated
        COUNT(*) as ticket_count  # Repeated
      FROM dundersign.tickets
      WHERE status = 'solved'    # Only difference
      GROUP BY 1
    source: my_postgres

# ✅ Good: DRY with query references
queries:
  base_tickets:
    sql: |
      SELECT
        priority,
        status,
        COUNT(*) as ticket_count
      FROM dundersign.tickets
      GROUP BY 1, 2
    source: my_postgres

  open_tickets:
    sql: |
      SELECT * FROM {{ queries.base_tickets }}
      WHERE status = 'new'
    source: my_postgres

  resolved_tickets:
    sql: |
      SELECT * FROM {{ queries.base_tickets }}
      WHERE status = 'solved'
    source: my_postgres

Performance Considerations¶

Use limit when you don't need all rows
Filter early, in the query's own WHERE clause (using filter()/filter_date_range()) rather than fetching all rows and filtering elsewhere
Reuse queries across multiple charts

Naming Conventions¶

Use descriptive names (for example, sales, products) and reference with explicit namespacing (queries.sales)
Use descriptive names that indicate what data the query fetches
Keep names lowercase with underscores

Testing and Validation¶

Validate Your Dashboard¶

After writing queries, validate your dashboard to catch errors early:

dct validate charts/sales.yml

See the CLI Reference for validation options.

Render to Inspect Resolved Queries¶

Render your dashboard to JSON to see the resolved query structure with executed results:

dct render charts/sales.yml --format json

See the CLI Reference for rendering options.

Query References¶

You can reference other queries within your SQL using Jinja template syntax. This enables CTE-style query composition where complex queries can be built from simpler building blocks.

queries:
  # Base query - tickets by status
  tickets_by_status:
    sql: |
      SELECT
        status,
        COUNT(*) as ticket_count
      FROM dundersign.tickets
      WHERE status = 'solved'
      GROUP BY 1
    source: my_postgres

  # Reference the base query
  top_status_tickets:
    sql: |
      SELECT ticket_count as current_ticket_count
      FROM {{ queries.tickets_by_status }}
      ORDER BY ticket_count DESC
      LIMIT 1
    source: my_postgres

How It Works¶

When you use {{ queries.query_name }}, dbt Charts: 1. Substitutes the referenced query's SQL inline as a parenthesized subquery 2. Aliases that subquery to the query name, so you reference its columns as query_name.column, and the same SQL is valid on both DuckDB and Postgres (Postgres requires the alias; DuckDB tolerates it) 3. Detects circular dependencies

You don't add the parentheses or the alias yourself. The example above becomes:

SELECT ticket_count as current_ticket_count
FROM (
  SELECT
    status,
    COUNT(*) as ticket_count
  FROM dundersign.tickets
  WHERE status = 'solved'
  GROUP BY 1
) AS tickets_by_status
ORDER BY ticket_count DESC
LIMIT 1

Chained References¶

You can chain multiple query references:

queries:
  solved_tickets:
    sql: |
      SELECT * FROM dundersign.tickets WHERE status = 'solved'
    source: my_postgres

  priority_totals:
    sql: |
      SELECT
        priority,
        COUNT(*) as ticket_count
      FROM {{ queries.solved_tickets }}
      GROUP BY 1
    source: my_postgres

  top_priority:
    sql: |
      SELECT ticket_count
      FROM {{ queries.priority_totals }}
      ORDER BY ticket_count DESC
      LIMIT 1
    source: my_postgres

Referencing Columns (the automatic alias)¶

The subquery is aliased to the query name, so qualify its columns with that name. Don't add your own AS alias; the query already has one and writing FROM {{ queries.base_tickets }} AS b produces a SQL parse error (... AS base_tickets AS b) because the alias is inserted automatically. To use a different alias, rename the query itself:

queries:
  base_tickets:
    sql: |
      SELECT status, ticket_type FROM dundersign.tickets
    source: my_postgres

  top_statuses:
    sql: |
      SELECT base_tickets.status, COUNT(*) as ticket_count
      FROM {{ queries.base_tickets }}
      GROUP BY base_tickets.status
      HAVING COUNT(*) > 10
    source: my_postgres

Referencing the same query twice in one statement collides on the alias and fails; give each its own query name instead.

Cross-Source Composition via Cache¶

{{ queries.X }} inlines query X as a SQL subquery; both queries must run against the same source. To join results from different sources, use the cache read token {{ queries.X.cache }} instead.

{{ queries.X.cache }} reads query X's cached result rows and evaluates the composing query in the local cache engine (DuckDB), making cross-source joins possible:

queries:
  # Runs against your data warehouse
  monthly_revenue:
    sql: |
      SELECT month, revenue
      FROM dundersign_serving.monthly_metrics
    source: my_warehouse

  # Runs against your CRM (different source)
  monthly_leads:
    sql: |
      SELECT month, COUNT(*) AS leads
      FROM leads
      GROUP BY 1
    source: my_crm

  # Joins the two sources; evaluated in the local cache engine.
  # Each cache reference is aliased to its query name (no AS needed).
  efficiency:
    sql: |
      SELECT
        month,
        monthly_revenue.revenue,
        monthly_leads.leads,
        monthly_revenue.revenue / NULLIF(monthly_leads.leads, 0) AS revenue_per_lead
      FROM {{ queries.monthly_revenue.cache }}
      JOIN {{ queries.monthly_leads.cache }} USING (month)
    source: my_warehouse

How it works: dbt Charts executes monthly_revenue and monthly_leads first, caches their results, then evaluates the composing query inside the cache engine (DuckDB) using the cached rows as tables.

Opt out of caching by setting cache: false on a query. Queries with caching disabled cannot be referenced via .cache:

queries:
  sensitive_data:
    sql: SELECT * FROM pii_table
    source: my_warehouse
    cache: false  # results never cached

How long results stay cached¶

cache: takes a duration: 5m, 1h, 7d, or a compound like 1h30m (m is minutes). Cached rows older than that are recomputed on the next read. cache: forever never auto-expires, and cache: false turns caching off. The default is 24 hours.

Write it once at the top of a dashboard and every query in it inherits:

title: Sales
cache: 1h        # every query on this board expires after an hour

queries:
  overview:
    sql: SELECT * FROM daily_rollup   # 1h, inherited

  live_tickets:
    sql: SELECT * FROM dundersign.tickets WHERE status = 'new'
    cache: 30s                        # this one needs to be fresher

  pii_lookup:
    sql: SELECT * FROM pii_table
    cache: false                      # never cached

The nearest setting wins: a query's own cache: beats the dashboard's, which beats the source's, which beats the project default in dbt_charts.yml. Anything you leave out is inherited, so cache: 30s above changes only the duration.

Limitations & Notes¶

Query references are powerful - they let you build complex analyses by composing simpler queries. Just two simple limitations:

1. Top-Level Only

All query references (`{{ queries.* }}`) MUST reference top-level queries in the current dashboard's queries: section:

queries:
  base_query:  # ✅ Top-level - can be referenced
    sql: SELECT * FROM dundersign.tickets
    source: my_postgres

  # Import from other files and add to top level:
  shared_sales: _shared_queries.queries.base_sales  # ✅ Top-level - can be referenced

  my_analysis:  # ✅ Top-level - can be referenced
    sql: |
      SELECT * FROM {{ queries.shared_sales }} WHERE amount > 100
      UNION ALL
      SELECT * FROM {{ queries.base_query }}
    source: my_postgres

rows:
  - queries:  # ❌ NESTED - cannot be referenced anywhere!
      nested_query:
        sql: SELECT * FROM products
        source: my_postgres

Important: Even if you import a query from another file that has internal references (like {{ queries.raw_data }}), you must import ALL referenced queries to the top level of your current dashboard for them to work.

2. SQL Only

Only SQL queries can reference other queries. Values, HTTP, CSV, and other query types cannot use {{ queries.* }}.

Importing Queries from Other Files¶

You can reuse queries across multiple dashboards by referencing queries from other files directly in your queries: section.

Import Syntax¶

Use the format: file_name.queries.query_name

queries:
  # Import queries from other files
  base_sales: _shared_queries.queries.base_sales
  customer_data: _shared_queries.queries.customer_data

  # Use imported queries like any other query
  my_analysis:
    sql: |
      SELECT * FROM {{ queries.base_sales }}
      WHERE revenue > 1000
    source: my_postgres

charts:
  sales_chart:
    query: my_analysis
    type: bar
    x: month
    y: revenue

Import Examples¶

Import from same directory:

queries:
  # _shared_queries.yml is in the same directory (underscore prefix means not rendered)
  orders: _shared_queries.queries.base_orders
  customers: _shared_queries.queries.base_customers

Import from subdirectory:

queries:
  # analytics/metrics.yml
  revenue: analytics/metrics.queries.total_revenue

  # ml/predictions.yml
  top_customers: ml/predictions.queries.predict_top_customers

Import from a sibling directory:

A reference may start with one or more ../ segments to climb out of the current directory. This is how a board in one folder reuses queries defined in another:

queries:
  # from charts/gtm_weekly/mqls.yml, reaching charts/sales/pipeline.yml
  mqls_by_week: ../sales/pipeline.queries.mqls_by_week

Paths are resolved relative to the importing file, and may not escape the project root; ../../../etc/passwd.queries.x is rejected.

Use imported queries:

queries:
  base_sales: _shared.queries.sales

  filtered_sales:
    sql: |
      SELECT *
      FROM {{ queries.base_sales }}
      WHERE region = '{{ filter("region", "US") }}'
    source: my_postgres

  summary:
    sql: |
      SELECT
        COUNT(*) as total_orders,
        SUM(amount) as total_revenue
      FROM {{ queries.filtered_sales }}
    source: my_postgres

charts:
  summary_chart:
    query: summary
    type: kpi
    value: total_revenue

Best Practices¶

Use imports for shared queries: Create _shared_queries.yml files (underscore prefix means not rendered as dashboards) for queries used across multiple dashboards
Don't overdo it: If you find yourself creating many shared query files, consider whether you need better data modeling in dbt or a proper data mart instead. Query references are for composition, not data transformation pipelines.
Use for composition: Build complex queries from simple, reusable building blocks
Consider CTEs: For simple cases or one-off queries, standard SQL CTEs might be clearer
Reference columns by query name: A reference is auto-aliased to its query name, so qualify columns as base.column; don't add your own AS alias
Test separately: Ensure referenced queries work independently before chaining them
Keep it simple: Avoid deep chains (A → B → C → D). Two levels is usually enough

Security: Parameterized Queries¶

dbt Charts uses parameterized queries to prevent SQL injection attacks. When you use variables in your SQL queries, they are automatically passed as parameters to the database driver rather than being interpolated directly into the SQL string.

How It Works¶

When you write:

variables:
  priority:
    input: text
  status:
    input: text
queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE priority = '{{ priority }}'
        AND {{ filter('status', status) }}
    source: my_postgres

dbt Charts converts this to a parameterized query:

-- SQL sent to database:
SELECT * FROM dundersign.tickets
WHERE priority = $1
  AND status = $2

-- Parameters sent separately:
params = ['normal', 'new']

This separation ensures that: - Malicious input cannot alter query logic - Even if a user enters '; DROP TABLE tickets; -- as input, it's treated as a literal string value, not SQL code - Database can cache query plans - The same parameterized SQL structure enables query plan reuse - No escaping needed - The database driver handles type conversion and escaping automatically

Filter Helper Behavior¶

The filter() and filter_date_range() helpers return 1=1 (always true) when the variable is None, undefined, or empty. This means:

Filter applied: {{ filter('priority', 'normal') }} → priority = $1 (with 'normal' as parameter)
Filter skipped: {{ filter('priority', None) }} → 1=1 (all rows match, no filtering)

This pattern allows optional filtering where unset variables don't restrict results.

Deny-on-null fallback¶

For mandatory-scope dashboards (audit views, customer-specific dashboards, anything where the unfiltered superset is the wrong default), pass none='deny' to flip the fallback from 1=1 to 1=0 (zero rows):

{{ filter('priority', None, none='deny') }} → 1=0 (no rows when unset)
{{ filter('priority', 'normal', none='deny') }} → priority = $1 (normal filter when set)

none is keyword-only; valid values are 'allow' (default) and 'deny'. Anything else raises ValueError.

Security Validations¶

The parameterized filter helpers include additional security measures:

Operator validation: Only valid SQL operators (=, !=, >, <, >=, <=, LIKE, IN, etc.) are allowed. Invalid operators raise an error.
Column name validation: Column names must contain only letters, numbers, underscores, and optionally one dot (for table.column format).

Best Practices¶

Use filter() for user-controlled values - Always use the filter helpers for variables that come from user input
Don't construct SQL from user strings - Avoid patterns like WHERE {{ user_column }} = ... where column names come from user input
Validate at the variable level - Use variable validation (data types, options) to restrict allowed values

Variables - Using variables in filters
Expressions - Variable references and Jinja expressions
Charts - Visualizing query data
CLI Reference - Validate, render, serve, and more
YAML Schema Reference - Complete field reference
Troubleshooting Guide - Common query issues

Queries¶

Basic Query Structure¶

Query Types¶

1. SQL Queries (Baseline)¶

setup_sql: a preamble statement¶

Database Connection via Source Configuration¶

2. Values Queries (Inline Data)¶

3. HTTP / API Queries¶

4. Schema Queries¶

Field Reference¶

Values Queries¶

HTTP / API Queries¶

Raw SQL Queries¶

Filters¶

Jinja in SQL¶

Writing SQL once with dialect:¶

Pivot (Cross-Tab Tables)¶

Row and Column Totals¶

Best Practices¶

Reuse Queries Across Charts¶

Use Query References for DRY SQL¶

Performance Considerations¶

Naming Conventions¶

Testing and Validation¶

Validate Your Dashboard¶

Render to Inspect Resolved Queries¶

Query References¶

How It Works¶

Chained References¶

Referencing Columns (the automatic alias)¶

Cross-Source Composition via Cache¶

How long results stay cached¶

Limitations & Notes¶

Importing Queries from Other Files¶

Import Syntax¶

Import Examples¶

Best Practices¶

Security: Parameterized Queries¶

How It Works¶

Filter Helper Behavior¶

Deny-on-null fallback¶

Security Validations¶

Best Practices¶

Related¶

`setup_sql`: a preamble statement¶

Writing SQL once with `dialect:`¶