Queries¶

Queries define what data to fetch from your data sources. Dataface supports six types of queries. Most projects start with SQL. MetricFlow semantic layer queries are an optional enhancement that plays nicely with a dbt setup—bonus points if you already leverage dbt!

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
Semantic layer queries - Use dbt MetricFlow metrics and dimensions (optional, advanced)
Schema queries - Introspect configured sources, schemas, tables, and columns
LookML queries - Compile a LookML explore or merged query to SQL (optional, requires dataface[lookml])

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 face level and every string query uses it automatically:

source: my_postgres
queries:
  sales: |
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      region,
      SUM(revenue) AS total_revenue,
      COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
      AND {{ filter('region', region) }}
      AND {{ filter_date_range('created_at', date_range) }}
    GROUP BY 1, 2

When you need to override the source, use description for AI search metadata, or mix query types on the same face, use the dict form instead:

queries:
  sales:
    description: "Monthly completed-order revenue by region"
    sql: |
      SELECT ...
    source: my_postgres

Query Types¶

1. SQL Queries (Baseline)¶

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

source: my_postgres
queries:
  sales: |
    SELECT
      DATE_TRUNC('month', created_at) as month,
      region,
      SUM(revenue) as total_revenue,
      COUNT(*) as order_count
    FROM orders
    WHERE status = 'completed'
      AND {{ filter('region', region) }}
      AND {{ filter_date_range('created_at', date_range) }}
    GROUP BY 1, 2
    ORDER BY 1 DESC

Dict form — use when you need to override the source per query, set a target, or add a description:

queries:
  sales:
    sql: |
      SELECT ...
    source: my_postgres  # Required when no face-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 orders
    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¶

Dataface 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 - Dataface 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:
  sales:
    sql: SELECT * FROM orders
    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

Dataface also has direct support for dbt MetricFlow semantic layer queries as an optional extra—use it when your dbt project already exposes metrics and treat it as a little reward for being a great dbt user.

To query a dbt model directly (no MetricFlow required), 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 compile (or any command that writes target/manifest.json), or commit a manifest.snapshot.json next to dataface.yml. 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 - metrics: and/or dimensions: → Semantic Layer Query - explore: or merge: → LookML 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).

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.start }}"

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. Semantic Layer Queries (MetricFlow) - Optional¶

Use dbt MetricFlow metrics and dimensions (requires dbt Semantic Layer setup):

queries:
  semantic_sales:
    metrics: [total_revenue, order_count]  # Semantic layer metrics
    dimensions: [month, region]            # Semantic layer dimensions
    time_grain: month                      # Optional time grouping

Use cases: - Teams already using dbt's semantic layer - Consistent metric definitions across dashboards - Automatic metric calculation logic - Leveraging existing MetricFlow setup

Note: - Requires dbt Semantic Layer (MetricFlow) setup - All metric names must exist in your dbt Semantic Layer - See MetricFlow for details on how it works - MetricFlow queries filter with their own where: key (a list of predicates) rather than a SQL WHERE clause — see MetricFlow: Filters and Variables

5. 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 Dataface'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.

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

6. LookML Queries - Optional¶

Compile a Looker explore or merged query into SQL and run it as a Dataface query — useful when migrating off Looker or running alongside it. Requires the lookml extra: pip install 'dataface[lookml]'.

queries:
  order_metrics:
    explore: orders
    fields: [orders.count, orders.total_revenue, orders.created_month]

Use cases: - Migrating Looker explores to Dataface incrementally - Reusing existing LookML metric definitions without a full rewrite

Note: Requires the lookml extra and a LookML project on disk. See the YAML Reference for the full field list (merge, sorts, pivots, total, subtotals, filter_expression, filters, column_limit).

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¶

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.

Semantic Layer Queries¶

queries:
  <query_name>:             # Unique identifier
    metrics: [string]       # Required
    dimensions: [string]    # Optional: for grouping
    time_grain: day | week | month | quarter | year
    limit: number           # Optional: max rows

Raw SQL Queries¶

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

source: my_postgres  # face-level default
queries:
  <query_name>: string  # bare SQL — inherits face source

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

queries:
  <query_name>:
    sql: string           # Required: SQL query (supports Jinja)
    source: string        # Required if no face-level source
    target: string        # Optional: dbt target name (defaults to 'dev')
    description: string   # Optional: metadata for AI and UI

Example:

source: my_postgres
queries:
  sales: |
    SELECT * FROM orders
    WHERE status = 'completed'
    LIMIT 100

Database Connection via Source Configuration:

Dataface 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:
  sales:
    sql: SELECT * FROM orders
    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.)

Metrics (Semantic Layer Only)¶

Metrics are the measures you want to calculate. They come from your dbt Semantic Layer:

queries:
  revenue:
    metrics: [total_revenue]       # Single metric

  multiple:
    metrics: [total_revenue, order_count, avg_order_value]  # Multiple metrics

Note: Metrics only work with semantic layer queries. All metric names must exist in your dbt Semantic Layer (MetricFlow).

Dimensions¶

Dimensions are the categories you want to group by:

queries:
  sales_by_region:
    metrics: [total_revenue]
    dimensions: [region]           # Group by region

  sales_by_time_and_region:
    metrics: [total_revenue]
    dimensions: [month, region]    # Group by month AND region

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¶

Reference variables directly in the SQL text:

variables:
  region:
    input: text
  min_revenue:
    input: text
queries:
  filtered_sales:
    sql: |
      SELECT month, region, SUM(revenue) AS total_revenue
      FROM orders
      WHERE region = '{{ region }}'
        AND revenue >= {{ min_revenue }}
      GROUP BY month, region
    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.

Note: MetricFlow semantic layer queries (metrics:) filter through their own where: key — literal predicates bake into the generated SQL at compile time, {{ }} predicates on selected dimensions apply per render. See MetricFlow: Filters and Variables.

The only query type with a declarative filters: field today is lookml (Looker-explore filter expressions) — see LookML Queries above.

Time Grain¶

Time grain automatically groups time-based dimensions:

queries:
  monthly:
    metrics: [total_revenue]
    dimensions: [order_date]
    time_grain: month              # Groups by month automatically

Available time grains: day, week, month, quarter, year

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:
  sales_long:
    sql: |
      SELECT region, quarter, SUM(revenue) AS revenue
      FROM orders
      GROUP BY region, quarter
    source: warehouse

charts:
  sales_crosstab:
    type: table
    query: queries.sales_long
    rows: [region]      # row headers down the left side
    columns: [quarter]  # distinct values become column headers (the pivot)
    values: [revenue]   # measure(s) that fill the cells

The table has one row per region and one column per distinct quarter, with revenue filling the cells. List more than one measure on values (e.g. 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.

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, e.g. _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 (region = '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 (it keeps 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:
  sales_with_totals:
    sql: |
      SELECT region, quarter, SUM(revenue) AS revenue, 'value' AS row_role
      FROM orders
      GROUP BY region, quarter
      UNION ALL
      SELECT region, 'Total' AS quarter, SUM(revenue) AS revenue, 'value' AS row_role
      FROM orders
      GROUP BY region
      UNION ALL
      SELECT 'Total' AS region, quarter, SUM(revenue) AS revenue, 'total' AS row_role
      FROM orders
      GROUP BY quarter
      UNION ALL
      SELECT 'Total' AS region, 'Total' AS quarter, SUM(revenue) AS revenue, 'total' AS row_role
      FROM orders
      ORDER BY
        CASE WHEN region = 'Total' THEN 1 ELSE 0 END, region,
        CASE WHEN quarter = 'Total' THEN 1 ELSE 0 END, quarter
    source: warehouse

charts:
  sales_crosstab_with_totals:
    type: table
    query: queries.sales_with_totals
    rows: [region]
    # Both the region rows and the quarter columns reshape in first-seen (query)
    # order, so the ORDER BY above pins both: regions then quarters, each with
    # its "Total" sorted last. The renderer never reorders — the total ROW lands
    # last only because the ORDER BY puts the 'Total' region-partition last; drop
    # that clause and the total row renders wherever the query returns it.
    columns: [quarter]
    values: [revenue]
    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 region row (it just carries a 'Total' quarter cell), and only the bottom-row partition is tagged 'total'. Tagging the row-total branch 'total' would collide it with that region'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.

Query Examples¶

Simple Revenue Query¶

queries:
  revenue:
    metrics: [total_revenue]
    dimensions: [month]

Multiple Metrics and Dimensions¶

queries:
  regional_sales:
    metrics: [total_revenue, order_count]
    dimensions: [month, region]

With Time Grain¶

queries:
  quarterly:
    metrics: [total_revenue]
    dimensions: [order_date]
    time_grain: quarter

Multiple Metrics, Dimensions, and Time Grain¶

queries:
  comprehensive:
    metrics: [total_revenue, order_count, avg_order_value]
    dimensions: [month, region, product_category]
    time_grain: month

Best Practices¶

Reuse Queries Across Charts¶

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

queries:
  sales:
    metrics: [total_revenue]
    dimensions: [month, region]

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:
  north_sales:
    sql: |
      SELECT
        DATE_TRUNC('month', created_at) as month,
        SUM(revenue) as total_revenue
      FROM orders
      WHERE region = 'North'
      GROUP BY 1
    source: my_postgres

  south_sales:
    sql: |
      SELECT
        DATE_TRUNC('month', created_at) as month,  # Repeated
        SUM(revenue) as total_revenue               # Repeated
      FROM orders
      WHERE region = 'South'                        # Only difference
      GROUP BY 1
    source: my_postgres

# ✅ Good: DRY with query references
queries:
  base_sales:
    sql: |
      SELECT
        DATE_TRUNC('month', created_at) as month,
        SUM(revenue) as total_revenue
      FROM orders
      GROUP BY 1
    source: my_postgres

  north_sales:
    sql: |
      SELECT * FROM {{ queries.base_sales }}
      WHERE region = 'North'
    source: my_postgres

  south_sales:
    sql: |
      SELECT * FROM {{ queries.base_sales }}
      WHERE region = 'South'
    source: my_postgres

Use Semantic Layer When Possible¶

Semantic layer queries provide: - Consistent metric definitions - Automatic calculation logic - Better maintainability - Reusability across dashboards

Performance Considerations¶

Use limit when you don't need all rows
Filter early in queries (use filters field)
Reuse queries across multiple charts
Use time_grain for automatic time grouping

Naming Conventions¶

Use descriptive names (e.g., 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:

dft validate faces/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:

dft render faces/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 - sales by month
  sales_by_month:
    sql: |
      SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as sales
      FROM orders
      WHERE status = 'completed'
      GROUP BY 1
    source: my_postgres

  # Reference the base query
  current_month_sales:
    sql: |
      SELECT sales as current_sales
      FROM {{ queries.sales_by_month }}
      ORDER BY month DESC
      LIMIT 1
    source: my_postgres

How It Works¶

When you use {{ queries.query_name }}, Dataface: 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 sales as current_sales
FROM (
  SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as sales
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
) AS sales_by_month
ORDER BY month DESC
LIMIT 1

Chained References¶

You can chain multiple query references:

queries:
  raw_orders:
    sql: |
      SELECT * FROM orders WHERE status = 'completed'
    source: my_postgres

  monthly_sales:
    sql: |
      SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as sales
      FROM {{ queries.raw_orders }}
      GROUP BY 1
    source: my_postgres

  current_month:
    sql: |
      SELECT sales
      FROM {{ queries.monthly_sales }}
      ORDER BY month 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 (to use a different alias, rename the query):

queries:
  base_sales:
    sql: |
      SELECT product_id, amount FROM orders
    source: my_postgres

  top_products:
    sql: |
      SELECT base_sales.product_id, SUM(base_sales.amount) as total
      FROM {{ queries.base_sales }}
      GROUP BY base_sales.product_id
      HAVING SUM(base_sales.amount) > 1000
    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, SUM(revenue) AS revenue
      FROM orders
      GROUP BY 1
    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: Dataface 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_orders:
    sql: SELECT * FROM orders WHERE created_at > now() - interval '5 minutes'
    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 dataface.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 orders
    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 face in one folder reuses queries defined in another:

queries:
  # from faces/gtm_weekly/mqls.yml, reaching faces/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: number
    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¶

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

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE region = '{{ region }}'
        AND {{ filter('status', status) }}
    source: my_postgres

Dataface converts this to a parameterized query:

-- SQL sent to database:
SELECT * FROM orders
WHERE region = $1
  AND status = $2

-- Parameters sent separately:
params = ['North', 'active']

This separation ensures that: - Malicious input cannot alter query logic - Even if a user enters '; DROP TABLE orders; -- 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('region', 'North') }} → region = $1 (with 'North' as parameter)
Filter skipped: {{ filter('region', 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('region', None, none='deny') }} → 1=0 (no rows when unset)
{{ filter('region', 'North', none='deny') }} → region = $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. Semantic Layer Queries (MetricFlow) - Optional¶

5. Schema Queries¶

6. LookML Queries - Optional¶

Field Reference¶

Values Queries¶

HTTP / API Queries¶

Semantic Layer Queries¶

Raw SQL Queries¶

Metrics (Semantic Layer Only)¶

Dimensions¶

Filters¶

Jinja in SQL¶

Time Grain¶

Pivot (Cross-Tab Tables)¶

Row and Column Totals¶

Query Examples¶

Simple Revenue Query¶

Multiple Metrics and Dimensions¶

With Time Grain¶

Multiple Metrics, Dimensions, and Time Grain¶

Best Practices¶

Reuse Queries Across Charts¶

Use Query References for DRY SQL¶

Use Semantic Layer When Possible¶

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¶