Search Optimizations for Snowflake Macro (for use in post_hook)

add_search_optimization.sql

{% macro add_search_optimization(relation,columns) %}

    {# check if the relation has search optimization added #}
    {%- call statement('search_optimization', fetch_result=True) -%}
    describe search optimization on {{ relation }}
    {%- endcall %}

    {% if execute %}
    {% set result = load_result('search_optimization') %}
    {%- set result_data = result['data'] -%}
    {% endif %}

    {% if result_data|length > 0 %}
        {{ log("Search index already on for this table")}}
    {% else %}
        {% if execute %}
            {%- call statement('search_optimization', fetch_result=False) -%}
            alter table {{this}} add search optimization on equality({{ columns | join(",") }})
            {%- endcall %}
            {{ log("No search index found. Adding.")}}
        {% endif %}    

    {% endif %}
 

{% endmacro %}

example_use.sql

{{
    config(
        materialized = 'incremental',
        post_hook=add_search_optimization(this, ['customer_key','nation_key'])
    )
}}


    select
        c_custkey as customer_key,
        c_name as name,
        c_address as address, 
        c_nationkey as nation_key
    from {{ ref('stg_customers')

This macro looks for Snowflake search optimizations, if any are found then it won't apply them. It's pretty simplistic, and only operates on equality for now (easy to add more parameters to the macro)