Skip to main content

Debugging and Testing Macros

A macro can be syntactically valid Jinja and still generate invalid or incorrect SQL. The reliable workflow is to inspect the rendered SQL and test representative inputs.

1. Start with dbt compile​

Compile only the model you are changing:

dbt compile --select stg_customers

Then inspect the matching file under target/compiled/. Search for:

  • leftover {{ ... }} or {% ... %} tokens
  • missing commas between generated columns
  • unquoted strings
  • incorrect relation names from ref() or source()
  • unexpected NULL behavior

The database receives the compiled SQL, so this is the most useful first debugging artifact.

2. Reproduce the SQL in Snowflake​

If the compiled SQL fails, copy the smallest failing query into a Snowflake worksheet. This separates a SQL problem from a Jinja problem.

For example, a loop that generates columns must avoid a trailing comma:

SELECT
order_date,
{% for state in ['MH', 'KA', 'DL'] %}
SUM(IFF(state_code = '{{ state }}', amount, 0)) AS {{ state }}_revenue
{% if not loop.last %},{% endif %}
{% endfor %}
FROM {{ ref('stg_orders') }}
GROUP BY order_date

loop.last controls punctuation in the generated SQL. Always inspect the final output rather than trusting the template by inspection.

3. Test a transformation macro with a singular test​

Create tests/assert_standardize_status.sql:

WITH inputs AS (
SELECT column1 AS raw_status, column2 AS expected_status
FROM VALUES
('ACTIVE', 'ACTIVE'),
(' active ', 'ACTIVE'),
('A', 'ACTIVE'),
('I', 'INACTIVE'),
('CANCEL', 'CANCELLED'),
('DELIVERED', 'COMPLETED'),
('PROCESSING', 'PENDING'),
(NULL, 'UNKNOWN')
),
results AS (
SELECT
raw_status,
expected_status,
{{ standardize_status('raw_status') }} AS actual_status
FROM inputs
)
SELECT *
FROM results
WHERE actual_status IS DISTINCT FROM expected_status

A singular test passes when it returns zero rows. It fails when one or more rows violate the expected result.

Run it with:

dbt test --select assert_standardize_status

The IS DISTINCT FROM comparison is useful because it treats two nulls as equal. Confirm the exact null-comparison syntax supported by your adapter.

4. Common failures​

SymptomLikely causeCheck
Macro not foundFile is outside macros/ or name is misspelledProject path and macro name
SQL has a trailing commaLoop punctuation is unconditionalloop.last in compiled SQL
Values do not matchCase, whitespace, or mapping gapRaw inputs and UPPER(TRIM())
source() failsSource name or table name differs from YAMLSource declaration and compiled relation
run-operation argument failsKey does not match the parameter--args mapping

5. A disciplined workflow​

Edit macro
|
v
Compile one model
|
v
Inspect generated SQL
|
v
Run singular test
|
v
Execute model
|
v
Validate representative warehouse rows

Do not debug by repeatedly running the full project. A narrow selection gives faster feedback and makes the failing change easier to identify.

Practice​

Introduce a deliberate bug by removing TRIM() from the status macro. Add a value with leading whitespace to the test input, run the singular test, inspect the failure, and restore the correct implementation.

Summary​

  • dbt compile shows what dbt will actually send to the warehouse.
  • Singular tests validate macro output through expected rows.
  • Test nulls, whitespace, casing, known aliases, and unexpected values.
  • Narrow compile and test commands make debugging faster and safer.