Git-Driven dbt + BI Development in Omni: Dynamic Schemas for Safe Dev/Prod Switching

Note: This is the English translation of the original Japanese article:https://dev.classmethod.jp/articles/omni-dynamic-schema-with-dbt/

Omni offers several features that work great with dbt, and one standout is Dynamic Schemas, which lets you dynamically switch references to data in your dbt development environments.

https://docs.omni.co/docs/integrations/dbt#getting-started-with-dynamic-schemas-and-dbt-environments

I tried this feature, so I’ll summarize what I did in this post.

Preparation

Prerequisites

First, assume the following are already completed.

• A Connection to Snowflake has been created
  • Within the Connection, the dbt integration setup is complete

• You have created one Model for the Connection, integrated it with GitHub, and required pull requests for changes to Shared Models (reference: official docs)

Create a new Environment for the dbt development environment

Next, create a new Environment that corresponds to your dbt development environment.

From the dbt tab of the target Connection, go to the Environments section at the bottom and click Add Environment.

Configure the Environment as shown below. The key points are to set Default Schema to the schema you use for development, and set Target Name to the dbt target name you use for your development environment.

Click Save. You’ll see a list of schemas used for the dbt development environment as shown below. If everything looks good, simply close the dialog with the X in the upper right.

Enable Virtual Schemas

Next, from the dbt tab of the target Connection, enable Enable Virtual Schemas and click Save. Enabling this creates a virtual schema named omni_dbt. When referencing each view, you can use the form omni_dbt__<view name>. When you switch the dbt environment using the Dynamic Schema feature, this virtual schema abstracts away the environment so that references dynamically switch between production and development schemas.

Once Virtual Schemas are enabled, a Migration panel should appear on the right. Before enabling Virtual Schemas, the existing Omni Semantic Layer code referenced a fixed schema for a particular environment. Running this migration automatically adds code that references the omni_dbt schema.

Click Run migration to perform the migration.

You’ll see the screen below. Check Overwrite in the lower left and click Run migration.

After this, if you look at the Shared Model code, you’ll see a VIRTUAL SCHEMAS section has been added, and each view file has been updated to reference the omni_dbt schema.

(From the created VIRTUAL SCHEMAS names, it looks like they’re created as omni_dbt_<custom schema name of dbt production environment>.)

Update existing content to reference the Virtual Schema views

If you’ve already built content in Omni, you’ll need to update existing references to point to the Virtual Schema views.

Create a new branch and perform the following steps. Once done, open a pull request and merge it.

Update the view references in topics

FYI, you can use Ctrl + F to search/replace text within each file.

• Before

• After

Update the view references in each Workbook query

This is necessary if your Workbook queries reference raw views rather than topics.

In the Content Validator, click Show all documents.

Click Replace, confirm you’re on the View tab, then configure a replacement from the raw table view names to the Virtual Schema view names as shown below, and click Replace.

After this, while still on the branch, review each affected Workbook query. Verify that charts render correctly and that clicking Go to definition on any field links to the view definition within the Virtual Schema.

Trying development with Dynamic Schemas pointing to the dbt development environment

With the setup done, let’s try how development works using Dynamic Schemas to reference dbt’s development environment.

Task

There’s a column named ordered_at in one of the dbt Models used by Omni, and we’ll change it to purchased_at.

Create a branch in dbt and develop

Create a branch in dbt and make the following changes.

Then, run dbt build in the dbt Cloud IDE; the updated column name will be created in the dbt development schema in Snowflake.

Create a branch in Omni and make fixes while referencing the dbt dev environment via Dynamic Schemas

Next, in Omni, create a branch on the affected Shared Model. (For reference, dbt and Omni are connected to separate GitHub repositories.)

First, run Refresh Schema to reflect the latest dbt schema structure into Omni’s Schema Model.

Then, click the dbt icon next to the branch name at the top and switch to the dbt development Environment.

With the Environment switched to dbt’s dev environment, open the target view file and you’ll see ordered_at is gone and purchased_at is present.

Also, when viewing the dashboard in the dbt dev Environment, you’ll get errors if those dashboards referenced the old ordered_at column. Since we’re working on a branch, published dashboards still reference the dbt production Environment and do not error.

• On a branch referencing the dbt dev environment: errors occur

• When you exit the branch (published dashboards): no errors

Switch back to the branch, open the Shared Model IDE, and launch the Content Validator. As shown below, errors are detected due to mismatched field references.

Click Replace and perform replacements for FIELD.

When you display the dashboard again, the field references have been updated and charts now render correctly—even while working on the branch. That completes the development and fixes.

Open a PR to main in dbt, merge, and run dbt build against production

Commit on the dbt side, open a pull request, merge into main, and run dbt build against the dbt production environment.

Now the purchased_at column has been added to the dbt production schema in Snowflake.

Open a PR to main in Omni and merge

Finally, open a pull request to main in Omni and merge it.

First, switch the Environment in Omni back to the dbt production environment. If you don’t do this before opening the pull request, all dashboards using this Shared Model will end up referencing the development environment, so be careful. (If you try to open a pull request while the Environment is set to development, Omni will show a warning.)

Next, run Sync dbt metadata. (Without this, the view file definitions for the production Environment were still outdated.)

Now, when you look at the target view file, ordered_at is gone and purchased_at is present.

Also, when viewing the target dashboard with the Environment set to production, charts display correctly. (If the chart looks different from the first screenshot, that’s due to the dbt logic used—please ignore it.)

With everything confirmed, open a pull request while the Environment is set to production. (If no code changes exist, you won’t be able to open a pull request, so add something minimal like a space to a view file’s description.)

After merging the pull request, switch out of the branch and check the target dashboard. It renders without errors. This completes the end-to-end flow for changing a field definition in a dbt Model already referenced by Omni.

Thoughts

I tried Omni’s “Dynamic Schemas,” which let you update Semantic Layer definitions and fix dashboards while referencing data from dbt’s development environment. When I first read the docs I thought “this could be big,” and indeed it’s a very exciting feature. Being able to do Git-driven development that accounts for not just dbt but also Omni dashboards is excellent.

On the other hand, a few things stood out at this stage. If these improve, I think we’ll get a truly top-tier dbt + BI development experience!

• You can’t inject user attributes into the dbt Environment defined in Omni. In other words, if you want to use personal dev schemas like dbt_ssagara, you need to add one Environment per person. (If you want to avoid that, you’d need to insert an intermediate branch like develop or staging between main and define the Omni Environment for that branch.)
  • Omni already has Dynamic Database Environments based on user attributes, so I’m hopeful for an update here!
• The dashboard reference definitions surfaced by Content Validator are not code-ified, so as in this post, I had to add a half-width space to a description to get the final Omni pull request/merge through.
• After running dbt build against production, you still need to issue and merge a pull request on the Omni side to update the dbt production schema and reflect Content Validator changes. If dbt-side field changes are ongoing, published Omni dashboards might not reference the correct fields, causing temporary downtime.
  • There may be a smoother approach for this…