Add Macro support to SQL cells with templating

[cdavietei]

I find Macros super helpful when writing SQL queries, especially with tables that have a wide range of values.

An example of a simple query I was writing in querybook was

SELECT 
   COUNT_IF(category_field IS NOT NULL AND category_field NOT IN ('"",' '0','[]', /*.... (other cases)*/) AS category_valid,
   COUNT_IF(product_field IS NOT NULL AND product_field NOT IN ('"",' '0','[]',  /*.... (other cases)*/) AS product_valid,
   # More examples of the exact same pattern and a combination of the two fields.
FROM some.table;

It would have been super helpful to be able to have defined a macro like:

DEFINE MACRO is_arr_str return $1 IS NOT NULL AND $1 NOT IN ('"",' '0','[]', /*.... (other cases)*/);

SELECT
   COUNT_IF({{ is_arr_str(category_field) }}) AS category_valid,
   COUNT_IF({{ is_arr_str(product_field) }}) AS product_valid,
   ....
FROM some.table;

There's already some support for macros but within the source code only . However this requires commits to the repo and it's super flexible.

I worked with Claude Code to investigate options and came up with the following proposal to add cell level macros that are user defined and work with the Jinja templating engine. Adding that as an attachment here

2026-03-09-querybook-custom-macros-design.md

[cdavietei]

Custom Macros for SQL Cells

Overview

Add support for user-defined macros in SQL query cells. Macros allow users to define reusable text substitutions within their queries, reducing repetition and improving maintainability.

Scope

Phase 1 (this PR): Cell-level macros - defined and used within a single query cell.

Future: DataDoc-level macros - defined once, usable across all cells in a DataDoc.

Syntax

Macros are defined using SQL block comments with the @macro directive:

/* @macro name(arg1, arg2) =
   macro body using {{ arg1 }} and {{ arg2 }}
*/

Examples

Simple macro:

/* @macro add_prefix(val) = CONCAT('prefix-', {{ val }}) */

SELECT {{ add_prefix(name) }} FROM users

Renders to:

SELECT CONCAT('prefix-', name) FROM users

Multiline macro:

/* @macro active_user_filter(date_col) =
    status = 'active'
    AND {{ date_col }} >= '{{ yesterday }}'
    AND deleted_at IS NULL
*/

SELECT * FROM users WHERE {{ active_user_filter(created_at) }}

Renders to:

SELECT * FROM users WHERE status = 'active'
    AND created_at >= '2026-03-08'
    AND deleted_at IS NULL

Multiple macros:

/* @macro safe_divide(a, b) = CASE WHEN {{ b }} = 0 THEN NULL ELSE {{ a }} / {{ b }} END */
/* @macro pct(num, denom) = ROUND({{ safe_divide(num, denom) }} * 100, 2) */

SELECT {{ pct(completed, total) }} as completion_rate FROM tasks

Technical Design

Parsing

Macro definitions are extracted using regex before Jinja templating:

Pattern: \/\*\s*@macro\s+(\w+)\s*\(([^)]*)\)\s*=\s*([\s\S]*?)\*\/

Captures:

• Group 1: Macro name (e.g., add_prefix)
• Group 2: Arguments (e.g., val or arg1, arg2)
• Group 3: Body (e.g., CONCAT('prefix-', {{ val }}))

Execution Flow

Current flow:

query → _escape_sql_comments() → get_templated_variables() → render_templated_query()

New flow:

query → extract_macros() → _escape_sql_comments() → register_macros() → render_templated_query()

Implementation

New Function: extract_macros(query: str)

import re
from typing import Dict, List, Tuple, TypedDict

class MacroDefinition(TypedDict):
    args: List[str]
    body: str

MACRO_PATTERN = re.compile(
    r'\/\*\s*@macro\s+(\w+)\s*\(([^)]*)\)\s*=\s*([\s\S]*?)\*\/',
    re.MULTILINE
)

def extract_macros(query: str) -> Tuple[str, Dict[str, MacroDefinition]]:
    """
    Extract macro definitions from query and return cleaned query.

    Returns:
        Tuple of (query_without_macros, macros_dict)
    """
    macros = {}

    for match in MACRO_PATTERN.finditer(query):
        name = match.group(1)
        args = [arg.strip() for arg in match.group(2).split(',') if arg.strip()]
        body = match.group(3).strip()

        if name in macros:
            raise QueryTemplatingError(f"Duplicate macro definition: '{name}'")

        # Validate macro name
        if not re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', name):
            raise QueryTemplatingError(f"Invalid macro name: '{name}'")

        macros[name] = {'args': args, 'body': body}

    # Remove macro definitions from query
    cleaned_query = MACRO_PATTERN.sub('', query)

    return cleaned_query, macros

Modified Function: get_templated_query_env()

def get_templated_query_env(engine_id: int, macros: Dict[str, MacroDefinition] = None, session=None):
    jinja_env = SandboxedEnvironment()

    # Existing globals
    jinja_env.globals.update(
        latest_partition=create_get_latest_partition(engine_id, session=session),
        macros={'ds_add': ds_add},
    )

    # Register custom macros
    if macros:
        for name, macro_def in macros.items():
            args = macro_def['args']
            body = macro_def['body']

            # Create Jinja macro string
            jinja_macro = f"{{% macro {name}({', '.join(args)}) %}}{body}{{% endmacro %}}"

            # Parse and add to environment
            macro_template = jinja_env.from_string(jinja_macro)
            jinja_env.globals[name] = getattr(macro_template.module, name)

    jinja_env.trim_blocks = True
    jinja_env.lstrip_blocks = True

    return jinja_env

Modified Function: render_templated_query()

def render_templated_query(
    query: str, variables: Dict[str, str], engine_id: int, session=None
) -> str:
    # Extract custom macros first
    query, custom_macros = extract_macros(query)

    jinja_env = get_templated_query_env(engine_id, macros=custom_macros, session=session)

    try:
        escaped_query = _escape_sql_comments(query)
        variables_in_query = get_templated_variables_in_string(escaped_query, jinja_env)

        all_variables = get_templated_query_variables(variables, jinja_env)
        verify_all_variables_are_defined(variables_in_query, all_variables)

        return render_query_with_variables(escaped_query, all_variables, jinja_env)
    except TemplateSyntaxError as e:
        raise QueryJinjaSyntaxException(f"Line {e.lineno}: {e.message}")

Error Handling

Error	Cause	Message
Invalid syntax	/* @macro foo = */	"Invalid macro definition: expected format @macro name(args) = body"
Duplicate name	Two @macro add_prefix	"Duplicate macro definition: 'add_prefix'"
Invalid name	@macro 123invalid()	"Invalid macro name: '123invalid'"
Undefined macro	{{ unknown() }}	Jinja's "UndefinedError"
Wrong arg count	{{ add_prefix() }}	Jinja's "'add_prefix' takes 1 argument, 0 given"

Security

• All macro bodies are rendered through Jinja2's SandboxedEnvironment
• No new attack surface - macros produce text substitution only
• Macro names validated against [a-zA-Z_][a-zA-Z0-9_]*
• Existing cycle detection in flatten_recursive_variables() prevents infinite recursion

Files Changed

File	Changes
querybook/server/lib/query_analysis/templating.py	Add extract_macros(), modify get_templated_query_env() and render_templated_query()
querybook/tests/test_lib/test_query_analysis/test_templating.py	Add tests for macro extraction, rendering, and error cases
querybook/webapp/components/TemplateGuide/guides/syntax.md	Add macro syntax documentation
docs_website/docs/user_guide/faq.mdx	Add macro FAQ entry (optional)

Test Cases

1. Basic macro - Single argument, simple substitution
2. Multiline macro - Body spans multiple lines
3. Multiple macros - Several macros in one query
4. Macro calling macro - Nested macro invocation
5. Macro with variables - Body uses {{ today }} etc.
6. Error: duplicate name - Two macros with same name
7. Error: invalid syntax - Malformed macro definition
8. Error: wrong arg count - Macro called with wrong number of args

Future Work (DataDoc-level)

Phase 2 would add DataDoc-level macros:

1. New field DataDoc.meta.macros storing macro definitions
2. UI component similar to DataDocTemplateVarForm for managing macros
3. Resolution order: cell macros override DataDoc macros
4. Migration: no DB changes needed (uses existing JSON meta field)

Alternatives Considered

Native Jinja2 Macro Syntax

{% macro add_prefix(val) %}CONCAT('prefix-', {{ val }}){% endmacro %}

Rejected: Verbose, unfamiliar to SQL users, clutters the query.

Custom SQL-like Syntax

DEFINE MACRO add_prefix($1) AS CONCAT('prefix-', $1)

Rejected: Requires complex parsing, breaks SQL syntax highlighting, ambiguous with actual SQL keywords.

Comment with End Marker

-- @macro add_prefix(val)
CONCAT('prefix-', val)
-- @endmacro

Rejected: Macro body would execute as SQL if @endmacro is forgotten; more complex parsing.