dbt_big_query_dfs_nested_table_creation.sql

generate_nested_table.sql

-- EXAMPLE HIERARCHY (the macro works with infinite depth and width of child tables).
-- {% set mailchimp_lists_hierarchy = {
--     'stg_lists': {
--         '_hashid': '_lists_hashid',
--         'child_tables': {
--             'stg_lists_campaign_defaults': {
--                 '_hashid': '_lists_hashid'
--             },
--             'stg_lists_contact': {
--                 '_hashid': '_lists_hashid'
--             },
--             'stg_lists_stats': {
--                 '_hashid': '_lists_hashid'
--             }
--         }
--     }
-- } %}

{% macro generate_nested_table(hierarchy) %}
    {% for table_name, table_properties in hierarchy.items() %}
        {% set hashid = table_properties['_hashid'] %}
        {% set child_tables = table_properties['child_tables'] if 'child_tables' in table_properties else {} %}

        -- Depth First Search approach
        {% if child_tables %}
            {{ generate_nested_table(child_tables) }}
        {% endif %}

        {{ table_name }} AS (
            SELECT
                {{ table_name }}.{{ hashid }},
                ARRAY_AGG(STRUCT(
                    {{ dbt_utils.star(
                        from=ref(table_name),
                        except=[
                            hashid
                        ],
                        relation_alias=table_name
                    ) }}
                    {% for child_table in child_tables %}
                        ,{{ child_table }}.{{ '_' ~ child_table }}
                    {% endfor %}
                )) AS {{ '_' ~ table_name }}
            FROM
                {{ ref(table_name) }} AS {{ table_name }}
                {% for child_table, child_properties in child_tables.items() %}
                    {% set child_hashid = child_properties['_hashid'] %}
                    LEFT JOIN {{ child_table }}
                        ON {{ child_table }}.{{ child_hashid }} = {{ table_name }}.{{ child_hashid }}
                {% endfor %}
            GROUP BY
                {{ table_name }}.{{ hashid }}
        ),
    {% endfor %}
{% endmacro %}