# dbt

> Paste a dbt model and the editor works on the SQL underneath the Jinja. Completions, checks, hover, and cost estimates all see the resolved query, and refs, sources, and variables resolve through mappings you keep in the file as comments or in a side panel, with no dbt CLI and no compile step.

Source: https://docs.querylab.io/writing-sql/dbt-support/

**stg_orders.sql · dbt**

```sql
-- dbt:ref('orders') = ace-analytics.warehouse.orders
-- dbt:var('start_date') = '2024-01-01'

SELECT order_id, user_id, amount, order_date
FROM {{ ref('orders') }}
WHERE order_date >= {{ var('start_date') }}
{% if is_incremental() %}
AND order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
```

## How Jinja becomes SQL

dbt mode switches on for a tab as soon as the text contains `{{`, `{%`, or `{#`. A **dbt** button appears in the status bar, and Querylab.io resolves the file to plain BigQuery SQL. Completions, checks, hover, and the cost footer all work on that SQL:

| In the file                                                             | Becomes                                                                    |
| ----------------------------------------------------------------------- | -------------------------------------------------------------------------- |
| `{{ ref('orders') }}`, `{{ source('raw', 'events') }}`                  | The mapped table, or a placeholder identifier if unmapped                  |
| `{{ var('start_date') }}`                                               | The mapped value as a quoted literal; `var('x', default)` uses the default |
| `{{ this }}`                                                            | The table built from the `project`, `dataset`, `model` directives          |
| `{{ my_macro(…) }}`                                                     | The macro body with parameters substituted, expanded before anything else  |
| `{{ config(…) }}`, `{# … #}`, `{% set %}`, `{% macro %}…{% endmacro %}` | Removed                                                                    |
| `{% if … %}…{% endif %}`, `{% for %}…{% endfor %}`                      | The first branch is kept                                                   |
| Any other `{{ … }}`                                                     | A placeholder identifier                                                   |

An unresolved reference has no columns, so completions and checks go quiet for it.

> The `is_incremental()` branch is always kept. The **Incremental mode** switch   in the panel decides whether that branch is in the SQL that actually runs.

## Mapping refs to tables

### Directive comments

The simplest way to map is a comment. Directives travel with the SQL, so a shared query resolves the same for everyone:

```sql
-- dbt:ref('orders') = ace-analytics.warehouse.orders
-- dbt:source('raw', 'events') = ace-analytics.raw.events
-- dbt:var('start_date') = '2024-01-01'
-- dbt:project = ace-analytics
-- dbt:dataset = warehouse
-- dbt:model = stg_orders
```

Six directive types: `ref`, `source`, `var`, and the three `this` parts `project`, `dataset`, `model`. A directive without `= value` declares the reference so it shows up in the panel as unresolved. `source('raw.events')` is accepted as shorthand for the two-argument form. Directives may sit on any line, and are case-insensitive.

### The dbt panel

Click **dbt** in the status bar (or press `⇧ ⌘ B`; `Ctrl Shift B` on Windows and Linux) to open the panel. The quick fix on an unresolved reference opens it with the add form prefilled:

```sql
SELECT *
FROM {{ ref('«warn:customers»') }}
```

The panel's sections:

- **Active Directives**: every ref, source, var and `this` in the tab with its resolution, or _Unresolved — add mapping_. Hover a row to edit or remove it.
- **Detected Tables**: plain table references in FROM/JOIN. **Convert to dbt ref** rewrites one to `ref()` and adds the directive.
- **Detected Values**: string literals. **Convert to dbt var** replaces one with `var()`.
- **Table Mappings**: saved `ref`/`source` → table mappings. **Remove unused** drops the ones the current SQL doesn't reference.
- **Variable Mappings**: saved variable → value.
- **Macro Definitions**: macros added by hand or imported; a green dot marks the ones the current SQL uses.
- **Options**: **Incremental mode** and **Auto-apply saved mappings**.

Panel mappings are global and shared by every tab. When a directive in the file and a saved mapping name the same key, the saved mapping is the one that wins.

### Import manifest.json

**Import** in the panel header opens **Import dbt Manifest**. Upload or paste `target/manifest.json`; the preview counts models, sources, variables and macros, then **Merge** adds to your mappings or **Replace All** overwrites them. Macros from `dbt` and `dbt_utils` are skipped; only your project's own macros come in.

## Expanding macros

Define a macro inline, in the panel (**Add macro**: name, parameters like `col, precision=2`, body), or import it. Calls expand before refs resolve, so a macro that emits `ref()` still resolves:

```sql
{% macro cents_to_dollars(column_name, precision=2) %}
  ROUND({{ column_name }} / 100, {{ precision }})
{% endmacro %}

SELECT
{{ cents_to_dollars('amount') }} AS amount_dollars,
{{ cents_to_dollars('tax', 4) }} AS tax_dollars
FROM {{ ref('transactions') }}
```

Compiled, the SELECT becomes `ROUND(amount / 100, 2) AS amount_dollars, ROUND(tax / 100, 4) AS tax_dollars`. A call with too many or too few arguments gets a warning naming the expected count.

## Completions, hover, and checks

Inside `{{ }}` the completion list is the dbt vocabulary plus your macros; inside `ref('…')`, `source('…')` and `var('…')` it is the keys from your saved mappings. Inside `{% %}` it is `if`, `endif`, `for`, `endfor`, `set`, `macro`, `endmacro`.

```sql
FROM {{
```

Hover any expression for what it resolves to. With **Inlay Hints** on (Settings → SQL Frameworks → dbt), the resolved table is drawn right after the closing braces.

```sql
FROM {{ ref('«hl:orders»') }}
```

Warnings you may see:

| Squiggle                                                         | Meaning                                                |
| ---------------------------------------------------------------- | ------------------------------------------------------ |
| Unresolved ref / source                                          | Warning; quick fix adds the mapping                    |
| Unresolved var                                                   | Info; silent when the call has a default               |
| `ref()` requires 1 or 2 arguments, `source()` requires exactly 2 | Warning                                                |
| Unknown Jinja function 'x'. Did you mean 'y'?                    | Warning; quick fix replaces the name                   |
| Macro 'm' expects at most N argument(s)                          | Warning                                                |
| Duplicate directive                                              | Warning; quick fix removes it                          |
| `{{ this }}` requires project, dataset, model directive(s)       | Warning; quick fix inserts the missing directive lines |
| Unclosed Jinja delimiter                                         | Error; quick fix inserts `}}`                          |

## Compiled view and running

The icon in the panel header (**View compiled SQL**) swaps the tab for the fully resolved query: macros expanded, refs and vars substituted, the incremental branch kept or dropped per **Incremental mode**, comments stripped. The editor is read-only until **View source SQL**.

**Run Query** compiles the same way first and runs the compiled SQL. It refuses to run while any ref is unresolved.

**Export dbt Model** at the bottom of the panel generates a model from the tab: pick a name and a materialization (`view`, `table`, `incremental`, `ephemeral`), then download the `.sql` and the `.yml` with inferred column types, or copy both.

## Turning dbt mode off

**Enable dbt Mode** and its **Inlay Hints** are both on by default, under Settings → SQL Frameworks → dbt (the full section is on the [Settings](https://docs.querylab.io/settings/#sql-frameworks) page). To turn the mode off for one tab only, right-click the **dbt** status-bar button → **Deactivate dbt mode**; the button stays, dimmed, to reactivate. The mode also drops out on its own when the last Jinja expression is deleted.

## dbt and Dataform in one file

A tab runs in one mode. Whichever syntax is detected first wins; if the other appears too, the status-bar button turns amber with _Mixed syntax detected_. For SQLX see [Dataform](https://docs.querylab.io/writing-sql/dataform-support/).