Cross-Database Macros: Writing Once, Running Anywhere

You’ve built a solid dbt project on BigQuery. The macros work, the models run, and your team is productive. Then your company acquires a startup running on Snowflake, or you decide to publish your macros as a package for the community. Suddenly, everything breaks.

SQL dialects look similar until they aren’t. Date functions take arguments in different orders. Array operations use completely different syntax. What compiled perfectly on one database throws errors on another.

This guide builds on dbt macro fundamentals to show you how to write macros that adapt automatically to different warehouses. You’ll learn the dispatch pattern, understand where the major databases diverge, and know when to use dbt’s built-in cross-database functions instead of writing your own.

SQL Dialects Aren’t as Similar as They Look

Most analytics engineers start on a single database and learn its quirks. BigQuery’s DATE_ADD feels natural. Snowflake’s DATEADD makes sense. You might assume switching between them is just a matter of minor syntax tweaks.

That assumption holds for basic queries (SELECT, WHERE, GROUP BY work everywhere). But the moment you write macros that generate SQL dynamically, three categories of functions will trip you up:

Date and time operations have different argument orders and function names across databases. BigQuery puts the datepart last; Snowflake and Databricks put it first. A macro that works on one will generate invalid SQL on another.

Array handling varies dramatically. BigQuery uses UNNEST, Snowflake uses LATERAL FLATTEN, and Databricks uses EXPLODE. There’s no common syntax, so you need different SQL for each.

Type casting and JSON parsing use different function names and sometimes different approaches entirely. Safe casting is SAFE_CAST on BigQuery but TRY_CAST elsewhere.

One broken macro doesn’t just fail in isolation. If it’s used by multiple models, the entire downstream DAG fails during compilation. Understanding these differences matters even if you only work on one database today.

How dbt’s Dispatch Pattern Works

The dispatch pattern lets you write a single macro that automatically uses the right implementation for whatever database you’re targeting. When you call the macro, dbt checks which adapter you’re using and routes to the appropriate version.

A dispatch macro has two parts:

{% macro my_dateadd(datepart, interval, from_date) %}
    {{ return(adapter.dispatch('my_dateadd')(datepart, interval, from_date)) }}
{% endmacro %}

{% macro default__my_dateadd(datepart, interval, from_date) %}
    dateadd({{ datepart }}, {{ interval }}, {{ from_date }})
{% endmacro %}

{% macro bigquery__my_dateadd(datepart, interval, from_date) %}
    date_add({{ from_date }}, interval {{ interval }} {{ datepart }})
{% endmacro %}

The main macro acts as a router, calling adapter.dispatch() with the macro name to get the appropriate implementation. The implementations are separate macros with prefixes indicating which adapter they target.

dbt searches for implementations in a specific order:

1. {adapter}__macro_name (e.g., bigquery__my_dateadd)
2. {parent_adapter}__macro_name (for adapters that inherit from others)
3. default__macro_name

If you’re running against BigQuery, dbt uses bigquery__my_dateadd. If you’re on Redshift and haven’t written a Redshift-specific version, dbt falls back to default__my_dateadd.

This means you can start with a default implementation that works for most databases, then add adapter-specific versions only where needed.

Date and Time Functions: The First Stumbling Block

Date functions are usually the first place cross-database issues appear. The core problem is argument order. BigQuery does things differently from everyone else.

Notice the pattern: BigQuery puts the datepart at the end, while Snowflake and Databricks put it first. BigQuery also uses the INTERVAL keyword where others don’t.

For date truncation, Snowflake and Databricks agree with each other but disagree with BigQuery on argument order. These small differences compound when you’re generating SQL dynamically.

Use the built-in macros. Since dbt 1.8, cross-database date functions live in the dbt namespace:

{{ dbt.dateadd(datepart="day", interval=7, from_date_or_timestamp="order_date") }}
{{ dbt.datediff("start_date", "end_date", "day") }}

These handle the argument reordering automatically. If you were using dbt_utils.dateadd() before, switch to dbt.dateadd() (the functionality moved to core).

When you need custom date logic that goes beyond what the built-ins provide, use dispatch:

{% macro get_fiscal_quarter_start(date_column) %}
    {{ return(adapter.dispatch('get_fiscal_quarter_start')(date_column)) }}
{% endmacro %}

{% macro default__get_fiscal_quarter_start(date_column) %}
    date_trunc('quarter', {{ date_column }})
{% endmacro %}

{% macro bigquery__get_fiscal_quarter_start(date_column) %}
    date_trunc({{ date_column }}, quarter)
{% endmacro %}

Array Operations: Where Databases Really Diverge

Array handling is where the databases truly part ways. There’s no syntactic similarity to lean on, so you need completely different SQL for each platform.

Even ARRAY_CONTAINS can’t agree on argument order between Snowflake and Databricks.

Here’s a dispatch macro for flattening arrays in a FROM clause:

{% macro flatten_array(table_alias, array_column, element_alias) %}
    {{ return(adapter.dispatch('flatten_array', 'my_project')(table_alias, array_column, element_alias)) }}
{% endmacro %}

{% macro bigquery__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, UNNEST({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro snowflake__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, LATERAL FLATTEN(input => {{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro databricks__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }} LATERAL VIEW EXPLODE({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

Usage in a model:

SELECT
    e.event_id,
    param.value AS param_value
FROM {{ ref('base__app__events') }} e
{{ flatten_array('e', 'e.event_params', 'param') }}

This compiles to UNNEST on BigQuery, LATERAL FLATTEN on Snowflake, or LATERAL VIEW EXPLODE on Databricks.

Safe Casting and Type Handling

Type casting seems simple until a value can’t be converted. Regular CAST throws an error; safe casting returns NULL instead. The function names differ:

Type names also vary. What BigQuery calls STRING, Snowflake calls VARCHAR, and some contexts use TEXT. The length specifications differ too.

dbt provides dbt.safe_cast() combined with api.Column.translate_type() to handle both issues:

{{ dbt.safe_cast("user_input", api.Column.translate_type("string")) }}

The translate_type() function converts generic type names to the adapter-specific equivalent. Use it whenever you’re generating DDL or type-aware SQL.

For the type itself, dbt.type_string() returns the correct string type for your current adapter:

CAST({{ column }} AS {{ dbt.type_string() }})

Configuring Dispatch for Packages

When you use packages like dbt-utils, the dispatch system can be customized to change where dbt looks for implementations.

In your dbt_project.yml:

dispatch:
  - macro_namespace: dbt_utils
    search_order:
      - my_project      # Check your project first
      - spark_utils     # Compatibility shim for Spark/Databricks
      - dbt_utils       # Original package

The search_order controls where dbt looks for adapter-specific implementations. This is useful in two scenarios:

Overriding package behavior. If a dbt-utils macro doesn’t work quite right for your setup, you can write your own version in your project. Put your project first in the search order, and dbt will use your implementation instead.

Adding Databricks support via spark_utils. Many packages were written before Databricks was common. The spark_utils package provides Spark-compatible implementations of common macros. Adding it to the search order fills in the gaps.

When writing your own dispatched macros, include the project name as the second argument to adapter.dispatch():

{{ adapter.dispatch('my_macro', 'my_project')(args) }}

This ensures dbt looks in the right namespace when resolving the implementation.

Testing Across Multiple Adapters

If your macros need to work on multiple databases, test them on multiple databases. A CI/CD matrix strategy makes this manageable:

jobs:
  test:
    strategy:
      matrix:
        adapter: [bigquery, snowflake, databricks]
    steps:
      - run: pip install dbt-${{ matrix.adapter }}
      - run: dbt build --target ${{ matrix.adapter }}

This runs your full test suite against each adapter in parallel. You’ll need separate connection profiles in your profiles.yml for each target.

What should you test? Two things matter:

Syntax correctness. Does the generated SQL compile without errors? The CI pipeline catches this automatically. If dbt build succeeds, the SQL is syntactically valid.

Semantic equivalence. Does the macro produce the same logical result across databases? This requires test models with known inputs and expected outputs. Create a simple model that uses your macro, then add a test asserting the output matches expectations.

For complex macros, consider creating a dedicated test model:

-- models/tests/test_flatten_array.sql
{{ config(materialized='table', tags=['test']) }}

WITH test_data AS (
    SELECT 1 AS id, {{ array_construct([1, 2, 3]) }} AS values
)

SELECT
    id,
    element
FROM test_data
{{ flatten_array('test_data', 'test_data.values', 'element') }}

Then add schema tests verifying the output has three rows with the expected values.

Built-in Cross-Database Macros You Should Use

Before writing custom dispatch macros, check whether dbt already provides what you need. Since version 1.8, these live in the dbt namespace:

Migration note: Many of these used to live in dbt_utils. If you’re upgrading an older project, update your references from dbt_utils.dateadd() to dbt.dateadd(). The dbt-utils versions still work but are deprecated. For a complete list of recommended macros, see essential macros every project needs.

The dbt.concat() macro is particularly useful because it handles the differences between CONCAT() function calls and || operators across databases:

{{ dbt.concat(["first_name", "' '", "last_name"]) }} AS full_name

For anything not covered by built-ins (array operations, JSON parsing, database-specific optimizations), write your own dispatch macros following the patterns in this guide.

Making Your Macros Portable

Cross-database macro development requires knowing where SQL dialects diverge and having a strategy for handling those differences. The dispatch pattern gives you that strategy.

Start by auditing your existing macros for hardcoded date functions, array operations, and type casts. Replace them with dbt.* built-ins where available. For everything else, create dispatch wrappers with adapter-specific implementations, following DRY principles for reusable macros.

The investment pays off in several ways: easier migrations when your company changes databases, the ability to publish packages that work for the broader community, and cleaner code that separates database-specific concerns from business logic.

Even if you only use BigQuery today, writing portable macros is a form of documentation. The dispatch implementations make explicit what’s unique about each database’s SQL dialect, which helps anyone maintaining the code later.