In dbt, how do you generate model columns from values a query returns at run time?

## The problem You want a pivoted model: one column per payment type, where the set of payment types lives in the data rather than in your head. SQL alone cannot do this — the column list is fixed at query-parse time. Jinja can, because it runs before the query exists. The mechanism is an *introspective query*: dbt asks the warehouse a question during compilation and templates the answer into the SQL it is about to run. ## run_query and the agate table `run_query(sql)` executes a statement against the target and returns an [agate](https://agate.readthedocs.io) table — a lightweight in-memory table object. The two accessors you need in practice: - `results.columns[0].values()` — the first column as a sequence. - `results.rows` — row-wise iteration when the query returns several columns. `run_query` is a thin wrapper around the `statement` block. The long form is equivalent and still seen in older code: ```sql {% call statement('get_types', fetch_result=True) %} select distinct payment_type from {{ ref('stg_payments') }} {% endcall %} {% set results = load_result('get_types')['table'] %} ``` ## Why the execute guard exists dbt runs in two passes. In the **parse** pass it renders every model far enough to find `ref()`, `source()` and `config()` and build the graph. Executing arbitrary queries then would be wrong and slow — the upstream models may not even exist yet — so dbt sets the context variable `execute` to `False` and makes `run_query()` return `None`. In the **execute** pass, during an actual `dbt run` or `dbt compile`, `execute` is `True` and the query really runs. Without the guard, parsing reaches `results.columns[0]` on a `None` and the whole project fails to parse — often with an error that names the model but not the cause. The canonical shape: ```sql {% set results = run_query(payment_types_query) %} {% if execute %} {% set payment_types = results.columns[0].values() %} {% else %} {% set payment_types = [] %} {% endif %} ``` The `else` branch matters as much as the `if`: the loop below still needs a variable to iterate, and an empty list renders a syntactically odd but harmless stub during parsing. ## The loop ```sql select order_id {% for pt in payment_types %} , sum(case when payment_type = '{{ pt }}' then amount end) as {{ pt }}_amount {% endfor %} from {{ ref('stg_payments') }} group by 1 ``` Leading commas sidestep the classic trailing-comma bug. If you prefer trailing commas, Jinja's loop object gives you `{{ "," if not loop.last }}`; `loop.index`, `loop.first` and `loop.last` are all available inside a `{% for %}`. Watch the quoting: `'{{ pt }}'` puts the value inside a SQL string literal, while `{{ pt }}_amount` uses it as a bare identifier. Values that contain spaces, quotes or reserved words will produce broken SQL — sanitise or alias them explicitly if the source is untrusted. ## Real costs This pattern is genuinely useful and genuinely dangerous: - **The output schema now depends on data.** A new payment type appears upstream and your model silently grows a column; one disappears and a downstream model referencing it breaks. Table materializations absorb this; incremental models with `on_schema_change` set to `ignore` will not. - **It runs at compile time, on every compile.** `dbt compile`, `dbt run`, `dbt docs generate` and CI all pay for the introspective query, and the model cannot be compiled at all if the upstream relation does not yet exist — which bites in a fresh environment or a slim CI run. - **Diffs stop being meaningful.** The git diff shows a template; the SQL that ran is only in `target/compiled/`. Because of that, the mature answer to "how do I pivot on a dynamic value set" often is: don't. Keep the value set in a seed file or a small dimension model, `dbt_utils.get_column_values()` it (which is the packaged version of exactly this pattern), and accept an explicit pull request when the list changes. Reserve run-time introspection for cases where the list truly is unbounded and the model is a table that downstream consumers query loosely. ## Debugging it Add `{{ log("types: " ~ payment_types, info=True) }}` after the guard to see what the query actually returned, and read `target/compiled/` for the generated SQL. Most bugs here are an empty list (query returned nothing, or you read the wrong column index) or a comma in the wrong place.