In dbt, how does adapter.dispatch let one macro work across Snowflake and BigQuery?

## The problem it solves SQL dialects diverge on exactly the things utility macros wrap: date arithmetic, string aggregation, safe casting, hashing, type names. A package that wants to run on Snowflake, BigQuery, Redshift, Postgres and Databricks needs one call site and several implementations. The naive answer is a conditional on `target.type`, which produces an ever-growing `{% elif %}` ladder that no third party can extend. Dispatch replaces it with a naming convention plus a resolution order. ## The naming convention An implementation is a macro whose name is `<adapter_prefix>__<macro_name>`, with a **double** underscore separating them: ```sql {% macro default__datediff(first_date, second_date, datepart) %} datediff({{ datepart }}, {{ first_date }}, {{ second_date }}) {% endmacro %} {% macro bigquery__datediff(first_date, second_date, datepart) %} date_diff(cast({{ second_date }} as date), cast({{ first_date }} as date), {{ datepart }}) {% endmacro %} ``` The prefix is the adapter name in lowercase — `snowflake`, `bigquery`, `redshift`, `postgres`, `databricks`, `spark`. `default__` is the fallback used when no adapter-specific implementation exists. ## The entry point The macro that models call is a one-liner that hands off to dispatch: ```sql {% macro datediff(first_date, second_date, datepart) %} {{ return(adapter.dispatch('datediff', macro_namespace='my_package')(first_date, second_date, datepart)) }} {% endmacro %} ``` Read the shape carefully: `adapter.dispatch(...)` **returns a macro**, and the trailing `(...)` calls it with the real arguments. Forgetting the second parentheses is the classic mistake, and it renders the macro object rather than its output. The `macro_namespace` argument tells dbt which package the macro conceptually belongs to. That is what makes the search order configurable by the *consuming* project rather than fixed by the package author. ## Overriding a package's implementation Suppose dbt_utils' Snowflake implementation of some macro does not do what your warehouse setup needs. You do not fork the package. You write your own `snowflake__that_macro` in your project's `macros/` directory and then tell dbt to look in your project first: ```yaml # dbt_project.yml dispatch: - macro_namespace: dbt_utils search_order: ['my_project', 'dbt_utils'] ``` Now every dispatched call in that namespace searches `my_project` for `snowflake__<name>`, then `dbt_utils`. Models keep calling `{{ dbt_utils.that_macro(...) }}`; the implementation underneath changed. This is genuinely powerful and genuinely a footgun: the override applies to every call in the project including calls made from *inside* the package's other macros, so a subtly different implementation can change behaviour far from where you edited. ## Resolution, step by step On a Snowflake target, `adapter.dispatch('datediff', macro_namespace='dbt_utils')` will: 1. Walk the configured `search_order` for the `dbt_utils` namespace, defaulting to the package itself if nothing is configured. 2. In each candidate project, look for `snowflake__datediff`. 3. If none is found anywhere, look for `default__datediff` in the same order. 4. Raise a compilation error if neither exists. All of this happens during compilation, so a dispatch mistake surfaces as a compile error naming the macro, not as a warehouse error. ## When you actually need it Most analytics teams run on one warehouse and never need dispatch for their own macros — a plain macro is simpler and clearer. It becomes worth the ceremony when: - you are **publishing a package** meant to run on more than one adapter; - you maintain an internal macro library shared by projects on different warehouses, for example a Snowflake production project and a DuckDB or Postgres project used for local development and testing; - you need to **patch a package's behaviour** for your adapter without forking it, which is the search-order case; - you are **migrating** between warehouses and want models to compile against both during the transition. One related fact worth knowing: since dbt Core 1.2 a set of cross-database macros — `datediff`, `dateadd`, `safe_cast`, `hash`, `split_part`, `listagg` and others — ships in dbt Core itself and is called *without* a namespace. They were previously in dbt_utils, and old projects still carry `dbt_utils.datediff` calls that now shadow or duplicate the built-in. When you meet dispatch in an interview, mentioning that migration shows you have actually maintained a project across versions rather than read the docs page.