# Working with dynamic schemas

**URL:** <https://community.omni.co/t/working-with-dynamic-schemas/99>\
**Category:** Patterns\
**Tags:** dynamic-schemas\
**Created:** [June 7, 2024, 8:09am UTC](https://community.omni.co/t/working-with-dynamic-schemas/99 "2024-06-07T08:09:09Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conor](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conor/32/45_2.png) [@conor](https://community.omni.co/u/conor)\
**Post date:** [June 7, 2024, 8:09am UTC](https://community.omni.co/t/working-with-dynamic-schemas/99/1 "2024-06-07T08:09:09Z")

</div>

## Dynamic schemas

[Dynamic schemas](https://docs.omni.co/docs/modeling/model-files#dynamic_schemas) are a useful concept to implement when your data is partitioned into separate, but identical schemas. In industry, this is referred to as a single tenant database architecture.

Suppose we have two orders schemas for different stores, `store_dublin` and `store_san_francisco` and the table structure inside both is identical. Ignoring table relationships and focusing on the schema and table relationship both schemas would look something like this:

```bash
store_<name>
├── order_items
├── orders
├── products
├── inventory_items
└── store_users

```

In this case we want to build a single semantic layer (topics, view files, relationships etc) once but _dynamically_ swap which schema we are working in so that it can apply to separate but identical schemas.

Depending on the user attributes, they will see different data. For example the Dublin store manager will see the data for their store, the SF manager will see the data for their store and the data team and management can choose what store they want to look at. We can manage this all through user attributes.

Let’s do a walk through of how we can build out a dynamic schema in Omni:

## Steps

1. Create a `dynamic_schema` in the `model` file.

```auto
# in the model file
dynamic_schemas:
  store_ecom:
    from_schema: store_dublin
    user_attribute: store

```

1. Create the relevant user attribute. First go to the **admin** tab in only, click on **attributes** , create a new attribute, set a default value and manage user’s values. In the below example I’ve created a `store` attribute and set the default value to store\_dublin.

 ![Screenshot 2025-10-20 at 2.02.53 PM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/f3b0ac545c59740e0611f8a87e1a95496926491e.png)

1. Update the relationships file to prefix relationships that should be scoped under this dynamic schema name with the name \<dynamic\_schema\_name\>\_\_  
For the above example:

```auto
# relationships file
- join_from_view: orders
  join_to_view: order_items
  join_type: always_left
  on_sql: ${orders.id} = ${order_items.order_id}
  relationship_type: one_to_many

```

Becomes:

```auto
# relationships file
- join_from_view: store_ecom__orders
  join_to_view: store_ecom__order_items
  join_type: always_left
  on_sql: ${store_ecom __orders.id} = ${store_ecom__ order_items.order_id}

  relationship_type: one_to_many

```

1. Any topics that reference the dynamic schema should replace the table references with the table name with the `<dynamic_schema_name> __` , which in this case is `store_ecom__ `. So a topic which previously looked like:

```auto
# in the model file
topics:
  store_order_data:
    base_view: orders

    joins:
      order_items: {}

```

Becomes:

```auto
topics:
  store_order_data:
    base_view: store_ecom__orders

    joins:
     store_ecom__order_items: {}

```

### Side note: Using dynamic schemas with your dbt developer workflow

If you’re using dbt, dynamic schemas are used automatically as part of [Omni’s branching mode](https://www.youtube.com/watch?v=0hD_KTFeRw8&t=117s) with our [dbt integration](https://docs.omni.co/docs/integrations/dbt/). Switching schemas between production and feature branches to look at different schemas and is handled automatically in the workbook interface. We have a demo of this [here](https://www.youtube.com/watch?v=0hD_KTFeRw8&t=117s).

If you’ve any questions, please follow up in the comments.