When dbt goes to execute the model, the sequence is queried, and therefore incremented, but the model failure prevents changes to the target table model. dbt Run errors - If an incremental model that has surrogate keys maintained in this way fails due to some SQL error, we may end up with gaps in our surrogate key.Using this, we can programmatically apply a surrogate key configuration for each model that needs one, and reference that configuration in a macro to properly create and update surrogate keys when necessary.ĭespite the relative simplicity of this strategy, there are a handful of drawbacks with regard to making sure these sequences work the way we want them to. This configuration allows you to define any metadata dictionary that you want. In order to do this at scale, we’ll make use of the meta config of dbt model. In order to properly maintain the sequence of the surrogate keys in your data models, we’ll need to build and maintain a sequence for each table that needs one. We’ll use Snowflake as the example here, but this approach can likely be adapted for other warehouses as well. If this is your preferred modeling approach, dbt can absolutely support this workflow! This will likely require you to take advantage of built-in warehouse functionality to generate these MIISKs - in Snowflake, we can use sequences, which are objects built exactly for this purpose. Imagine trying to rebuild these relationships from scratch! MIISKs in dbt This data is stateful, making it rigid and more difficult to work with should there be any losses to this data. Additionally, you need to know about the exact state of the data before making any updates. You may have heard of the phrase “load your dims before your facts” - this refers to the careful work required to maintain this referential integrity. Making updates to, say, your products table will require some careful surgical work to ensure the association of cheddar to id 2 is never accidentally changed. However, there are also some clear maintenance issues here.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |