The problem I’m having
Hi All I am newbie to dbt cloud and i am using Snowflake as my datawarehouse.
I have been tasked to create incremental materialize strategy for cube(You can consider this as wide table design approach for very large dataset of Fact Tables Joined with dimension table and the wide data materialized as table ).
My Cube is Fact table of SAP ERP tables which is joined with dimensional SAP ERP Tables.
The underline dimension & fact tables are following incremental materialize strategy with merge approach and materialized as tables.
These underline models are materialized every 3 hours.
For Initial Full Load to Cube, all the Fact data which is left joined with Dimension table data is materialized.
Now my question is when I start seeing the new incremental data in underline Fact & dimension data tables , i am lost how to perform this materialization.
The Changed data would consist of change in Fact table and might or might not have changes in dimension tables.
I have no option to use Dynamic table feature of Snowflake due to limitations that brings in.
Has anyone tried this before. Please suggest the direction to this problem.