Working with dynamic schemas

[conor (Conor O'Mara)]

Dynamic schemas

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:

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.

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

2. 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 dublin.

3. 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:

# 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:

# 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

4. 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:

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

    joins:
      order_items: {}

Becomes:

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 with our dbt integration. 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.

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