The problem I’m having
In my set-up, i have 4 layers. RAW (exact copy of the source), STAGING (here the data is incrementally loaded and history is stored, easy transformations here), INTERMEDIATE (here the more heavy transformation happen, not every table goes through this layer) and MART (here finished data product is stored).
I’m wondering which materialization strategy is best-practice when implementing in the silver/intermediate layer for large fact tables.
I would like to implement a materialization strategy where in my intermediata layer all data is deleted/truncated each load and only the new incremental data of that load, is loaded in my table. After that the i can ‘merge’ or ‘delete + insert’ that data in my mart.
But this does not seem to be a standard option in dbt? I don’t want to use ‘delete + insert’ in my intermediate layer because I don’t want to keep a full history of all my records in my intermediate layer.
Why is this not a standard option in dbt? Is this not something that commonly used? What is the best-practice here?