Run Raw SQL in dbt with Custom Materialization

If you need to execute complex SQL, including DDL and DML, directly in dbt without modification, you can do this by creating a custom materialization. Here’s a quick guide on setting up a raw_sql materialization in dbt for BigQuery.

Step 1: Define the Materialization

Create a new materialization named raw_sql. Place the following code in your materialization directory (typically under macros):

# materialization_raw_sql.sql
{% materialization raw_sql, adapter='bigquery' %}
    {%- set identifier = model['alias'] -%}
    {%- set target_relation = api.Relation.create(database=database, schema=schema, identifier=identifier) -%}

    {{ run_hooks(pre_hooks) }}

    {% call statement('main') -%}
        {{ sql }}
    {% endcall -%}

    {{ run_hooks(post_hooks) }}

    {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

Step 2: Use the Materialization

To use this materialization, specify materialized='raw_sql' in your model’s configuration. This will run the SQL exactly as you’ve written it, allowing you to implement any SQL logic that dbt's standard materializations would typically not support.

Conclusion

This setup lets you leverage dbt to run raw SQL directly, making it easier to handle complex SQL tasks within your dbt workflow. Try this custom materialization to enhance your data transformation processes.