# Create models dynamically

**URL:** <https://discourse.getdbt.com/t/create-models-dynamically/7031>\
**Category:** Help\
**Tags:** best-practice, snowflake, dbt-cloud\
**Created:** [February 28, 2023, 2:04pm UTC](https://discourse.getdbt.com/t/create-models-dynamically/7031 "2023-02-28T14:04:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![adi.levhar](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/adi.levhar/32/1951_2.png) [@adi.levhar](https://discourse.getdbt.com/u/adi.levhar)\
**Post date:** [February 28, 2023, 2:04pm UTC](https://discourse.getdbt.com/t/create-models-dynamically/7031/1 "2023-02-28T14:04:16Z")

</div>

Hello,  
I need to create a job that generates views based on data from Snowflake information schema.  
First step is to pull all relevant tables for the process based information\_schema tables.  
Second step is to iterate over the results from the 1st step, and for each table, create a view over the table in another data base.  
What is the best why to implement this in dbt?

example:  
1st step  
select table\_name from old\_db.information\_schema.tables  
where table\_schema = ‘FINANCE’

2nd step  
for each table in tables:  
run query : create or replace view new\_db.finance.table as select \* from old\_db.finance.table

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [March 3, 2023, 6:57am UTC](https://discourse.getdbt.com/t/create-models-dynamically/7031/4 "2023-03-03T06:57:02Z")

</div>

You should avoid writing custom DDL to create tables - this is the sort of thing that dbt abstracts away for you and ensures you don’t have to worry about things like running nodes in the right order.

Further, you won’t be able to ref these different models if they’re not created as individual dbt model files as dbt believes in a 1 model file = 1 database object paradigm.

If you need help bootstrapping a lot of these models, you could consider using the codegen package which will help generate your staging models: [GitHub - dbt-labs/dbt-codegen: Macros that generate dbt code](https://github.com/dbt-labs/dbt-codegen)

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [March 3, 2023, 10:16pm UTC](https://discourse.getdbt.com/t/create-models-dynamically/7031/5 "2023-03-03T22:16:28Z")

</div>

Thanks @joellabes, I will remove my answer in order not to induce bad practices!

Always learning

---

<div class="post-metadata">

**Author:** ![razlinky](https://avatars.discourse-cdn.com/v4/letter/r/f6c823/32.png) [@razlinky](https://discourse.getdbt.com/u/razlinky)\
**Post date:** [October 30, 2024, 12:54pm UTC](https://discourse.getdbt.com/t/create-models-dynamically/7031/6 "2024-10-30T12:54:59Z")

</div>

> [@adi.levhar](#):
>
> cond step is to iterate over the results from the 1st step, and for each table, create a view over the table in another data base.  
> What is the best why to implement this in dbt?

you can write a macro with for loop . running on all the objects .