Create select * views (staging layer) from many raw data tables in different Snowflake schemas

Help
, ,
1

Hi! Would like to find a good way to create a staging layer in Snowflake based on a lot of Oracle tabels that are synced with Fivetran. The issue is that these source tables will move around a bit on different schemas in the fivetran database on Snowflake, so I would like to keep the users unaffected by the changes on the back. I would like to create views as select * from the source tables, and keep those views in the same schema

I have tried to create a macro that reads table names from account_usage in Snowflake

Macros:
'-- Macro to create a view for a specific table
{% macro create_view(schema, table_name) %}
CREATE OR REPLACE VIEW PROD.TEMPDB.{{ table_name|upper }} AS
SELECT *
FROM FIVETRAN_CORE_DATA.{{ schema }}.{{ table_name|upper }};
{% endmacro %}

– Macro to retrieve all tables from the specified schema
{% macro get_all_tables(schema) %}
{% set all_tables = run_query(
“SELECT table_name FROM snowflake.account_usage.tables WHERE table_schema = '” ~ schema ~ “'”
) %}
{{ return(all_tables) }}
{% endmacro %}

– Model code to call the create_view macro for each table in the schema
{% set schema = ‘SCD1_SCDAT’ %}
{% set all_tables = get_all_tables(schema) %}

{% for table in all_tables %}
{{ create_view(schema, table[‘TABLE_NAME’]) }}
{% endfor %}’

The dbt dun creates this query in Snowflake:

create or replace view schema_name.create_staging

as (
– create_views.sql

CREATE OR REPLACE VIEW PROD.TEMPDB.CALINKAGES AS
SELECT *
FROM FIVETRAN_CORE_DATA.SCD1_SCDAT.CALINKAGES;

Which throws the error: SQL compilation error: syntax error line 11 at position 4 unexpected ‘CREATE’.

I guess this is related to the fact that a dbt model can only handle one query and that the macro/run model is not consistent with this.

Does anyone have experience with this? Maybe tbe dbt codegen package is worth looking into?

2

dbt model accepts only select statements, it failed here because the macro is returning create statements.

if you want to create all the views at once use the below code and run it using dbt run-operation create_views

I slightly modified ur code

-- Macro to create a view for a specific table
{% macro create_view(schema, table_name) %}

    {% set query %}
        CREATE OR REPLACE VIEW PROD.TEMPDB.{{ table_name|upper }} AS
            SELECT *
        FROM FIVETRAN_CORE_DATA.{{ schema }}.{{ table_name|upper }};
    {% endset %}
    
{%do run_query(query) %}

{% endmacro %}

– Macro to retrieve all tables from the specified schema
{% macro get_all_tables(schema) %}
{% set all_tables = run_query(
“SELECT table_name FROM snowflake.account_usage.tables WHERE table_schema = '” ~ schema ~ “'”
) %}
{{ return(all_tables) }}
{% endmacro %}

– Model code to call the create_view macro for each table in the schema


{% macro create_views() %}

{% set schema = ‘SCD1_SCDAT’ %}
{% set all_tables = get_all_tables(schema) %}

{% for table in all_tables %}
{{ create_view(schema, table[‘TABLE_NAME’]) }}
{% endfor %}

{% endmacro %}
3

Thanks so much @Surya, I will test asap :slight_smile:

It worked perfectly - thanks again!

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.