We come across one scenario where data is coming from multiple sources and each source data having different format and having different frequencies i.e weekly ,daily and monthly.
Here we need to rollup/aggregate data and need to store that result in final table.
Here the data transformation scripts are different for different source of data.
So let say there 4 sources from where we are receiving raw data and having different number of columns and different information; so we have built 4 DBT model and the final result after data transformation having consistent format and hence need to store same table.
Any suggestion how we can have different (in this case 4 dbt models) which will insert data in same table(one table).
Hi there!
It sounds like you want to union your four tables into a single dataset. Is that right?
You can do that natively if your four input tables have exactly the same schema (the same columns with the same types in the same order). Or it might be easier to use the dbt-utils macro called union_relations, which will help if some of the input tables are missing fields.
Docs are here: dbt-labs/dbt-utils: Utility functions for dbt projects. (github.com)
Thanks! Union is one option.
But is there any way in DBT by which different models can insert data in one table.
Example - DBT Model A - > table1
DBT Model B - > table1
DBT Model C - > table1
DBT Model D - > table1
Multiple DBT model inserting data in one table.
is it possible in DBT?
1 Like
inserts are an anti-pattern in dbt. Everything is a select statement, that then dbt wraps in something like create table as ()
If you don’t want to rebuild the entire table every time you build a model, incremental materializations are a great option. You could, for example, union many models together in a single incremental model; if you configure it properly, dbt will essentially insert the new rows from any of the source models. See the docs on incremental models: Configuring incremental models | dbt Docs (getdbt.com)
1 Like