The Best-Laid Plans of Mice and Men Often Go Awry

In today's installment of, let's macro the heck out of this SaaS system, things didn't go entirely as I had planned. I thought for sure I could figure out a way to create multiple models from a single macro, without having to create the model SQL by hand--I was wrong. Not too huge of a change, though. I simply dropped the gather_transformations macro and used a passed in parameter for transformation name. This way, I can create one model per transformation, utilizing two macros, one to get the query associated with the transformation, and one to get the tenant data based on said query.

Final answer for the design portion of the project. Note the addition of the third macro, Get Transformation Query (get_transform_query.sql)

I am still using a metadata table to store relevant transformation information. The create statement SQL for that, and the account metadata table, are as follows:

CREATE TABLE OHO_TEST2_META.ACCOUNT_META(
	DATABASE_NAME VARCHAR,
    ACCOUNT_ID VARCHAR
    );

CREATE OR REPLACE TABLE OHO_TEST2_META.AGG_QUERY_METADATA (
    ID INTEGER AUTOINCREMENT START 1 INCREMENT 1,
    AGG VARCHAR,
    QUERY VARCHAR,
    TABLE_NAME VARCHAR,
	ORDER_BY VARCHAR
    );

The macro Jinja code is as follows:

{% macro get_transform_query(transformation) -%}
    {% set schema = target.schema | replace('DBT_','') %}
    {% set results = run_query("SELECT CAST(QUERY AS TEXT) AS QUERY, CAST(ORDER_BY AS TEXT) AS ORDER_BY FROM OHO_" ~ schema ~ "_META.AGG_QUERY_META WHERE AGG = '" ~ transformation ~ "'") %}
    {%- set res_test = results.columns[0].values()[0] -%}
    {% if res_test|length == 0 %}
		{%- set results = run_query("SELECT 'DNE' AS QUERY, 'DNE' AS ORDER_BY") -%}
        {{ return(results) }}
    {% else %}
        {{ return(results) }}
    {% endif %}
{%- endmacro %}

{%- macro gather_account_data(gather_accounts, get_transform_query, transformation) -%}
    {%- if execute -%}
      	{%- set results = get_transform_query(transformation) -%
      	{% if results.columns[0].values()[0] == 'DNE' %}
            SELECT 'There is no available query for one of more transformations'
        {% else %}
            {%- set query_prep = results.columns[0].values()[0] -%}
            {%- set order_by = results.columns[1].values()[0] -%}
            {%- set accounts = gather_accounts() -%}
            {%- for account in accounts -%}
                {%- set act = account['ACCOUNT_ID'] -%}
                {%- set schema = target.schema | replace('DBT_','') -%}
                {%- set db = 'HRM_' ~ schema ~ '_' ~ act | replace('-','') | upper() -%}
                {%- set query = query_prep | replace(':database',db ) -%}
                {%- if loop.last -%}
                    {{ query }} {{ order_by }}
                {% else %}
                    {{ query }} union
                {% endif -%}
            {%- endfor -%}
        {% endif %}
    {%- endif -%}
{%- endmacro -%}

From there, we INSERT the appropriate data into the AGG_QUERY_META table and create one model per transformation within the INGEST layer. The model SQL is as simple as, the following, and of course, replacing the transformation name parameter with your own.

{{ config(
	materialized='table'
	) 
}}

{{ gather_account_data(gather_accounts, get_transform_query, "hrm_activities_by_tenant") }}

Voila, for now anyway. I am fairly green to Jinja so if you see a better way to do something, please let me know! As always, thanks for reading!