# Dynamically create models using schema files

**URL:** <https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019>\
**Category:** Help\
**Created:** [October 6, 2022, 12:14pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019 "2022-10-06T12:14:41Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![vidyaB](https://avatars.discourse-cdn.com/v4/letter/v/ad7895/32.png) [@vidyaB](https://discourse.getdbt.com/u/vidyaB)\
**Post date:** [October 6, 2022, 12:14pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/1 "2022-10-06T12:14:41Z")

</div>

Hi,  
We have a requirement to create models (sql) dynamically using schema files. We have like 10 schema files and want to generate models using those schema files. These schema files keep changing. I tried using generate\_base\_model but it works for analyses and it look for DB tables. Any suggestion to handle this scenario.

---

<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:** [October 6, 2022, 9:03pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/2 "2022-10-06T21:03:17Z")

</div>

Can you post an example of the schema file you’re trying to input, and the sort of output you would like to see?

Note: `@joellabes (dbt Labs)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CCKAGDE0M/p1665090191585469?thread_ts=1665058505.567239&cid=CCKAGDE0M). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![vidyaB](https://avatars.discourse-cdn.com/v4/letter/v/ad7895/32.png) [@vidyaB](https://discourse.getdbt.com/u/vidyaB)\
**Post date:** [October 7, 2022, 7:12am UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/3 "2022-10-07T07:12:32Z")

</div>

Hi,  
Below is the example version 1 _person.json_ schema file in aws s3 location (assuming in some location where we maintain all schemas)

```auto
{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "type": "object",
  "properties": {
    "firstName": {
      "type": "string"
    },
    "lastName": {
      "type": "string"
    },
    "age": {
      "type": "integer"
    }
  },
  "required": [
    "firstName",
    "lastName",
    "age"
  ]
}

```

I want to create a model dynamically person.sql like below

```auto
with source as 
(
 select * from person
)

select   
    firstName,
    lastName,
    age
from source

```

In future schema may change to version v2 like below (adding dob column)

```auto
{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "type": "object",
  "properties": {
    "firstName": {
      "type": "string"
    },
    "lastName": {
      "type": "string"
    },
    "age": {
      "type": "integer"
    },
    "dob": {
      "type": "string"
    }
  },
  "required": [
    "firstName",
    "lastName",
    "age",
    "dob"
  ]
}

```

I want to recreate a model dynamically person.sql like below (with dob column)

```auto
with source as 
(
 select * from person
)

select   
    firstName,
    lastName,
    age, 
    dob
from source

```

---

<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:** [October 7, 2022, 8:21am UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/4 "2022-10-07T08:21:12Z")

</div>

OK that makes sense! The first problem you’re going to have is that dbt runs inside your warehouse, and can only do things that you could do in straight SQL.

Which warehouse are you using? You’re going to need some sort of function that can ingest the file from S3 and make it available in SQL. Perhaps a UDF that returns a string of json?

If you can do that, then you should be able to parse a json blob relatively easily and create a model using jinja templating. You’d need to make a file for each model you want to generate.

Once you’ve done that, you will still have the problem that you won’t be able to automatically attach tests to the models because yaml files can’t be dynamically generated.

Honestly if you only have 10 files then it sounds like a lot more effort than it’s worth for it to be 100% dynamic. How often do they change that the additional functionality is worth the overhead in complexity?

If you really want to go down this path though, an alternative approach might be to template the sql and yaml files in Python and then run dbt as normal on the results of that work.

---

<div class="post-metadata">

**Author:** ![vidyaB](https://avatars.discourse-cdn.com/v4/letter/v/ad7895/32.png) [@vidyaB](https://discourse.getdbt.com/u/vidyaB)\
**Post date:** [October 7, 2022, 10:04am UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/5 "2022-10-07T10:04:58Z")

</div>

Thank you for your input.  
We are using snowflake warehouse. We have multiple customers and Schema files are slightly different for each, roughly 10 - 12 schema files for each customers. These schemas will change once in six months or even more depend on customers. So thought of taking dynamic model creation approach.

---

<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:** [October 9, 2022, 5:29pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/6 "2022-10-09T17:29:20Z")

</div>

OK - dbt requires a `.sql` file for each model that you want to have materialized in your database, so you have two options:

• If each customer needs their own set of tables (`customer_1/person.sql`, `customer_2/person.sql`…), you’d want some sort of codegen tool to make the template files that each reference a macro. Up to you whether you dynamically generate those from scratch at the start of each run, or just re-trigger them when you get a new customer or their schema changes. This can’t be done directly in dbt.  
• If your requirements allow a single table with each customer’s data unioned together (`person.sql`, `'address.sql`…), you could check out [https://discourse.getdbt.com/t/unioning-identically-structured-data-sources/921/1](https://discourse.getdbt.com/t/unioning-identically-structured-data-sources/921/1)

Note: `@joellabes (dbt Labs)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CCKAGDE0M/p1665336555490129?thread_ts=1665126780.782259&cid=CCKAGDE0M). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![vidyaB](https://avatars.discourse-cdn.com/v4/letter/v/ad7895/32.png) [@vidyaB](https://discourse.getdbt.com/u/vidyaB)\
**Post date:** [October 10, 2022, 2:19pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/7 "2022-10-10T14:19:05Z")

</div>

We have thousands of customers and planning to load data to in separate databases.

We are seeing below options now:

- Have one project for each customer with their own schemas and loading data to separate DB and tables.
- Have one project for all customers with similar schema

---

<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:** [October 10, 2022, 6:11pm UTC](https://discourse.getdbt.com/t/dynamically-create-models-using-schema-files/5019/8 "2022-10-10T18:11:46Z")

</div>

I’ll be interested to hear what you ultimately decide on!