Working with dbt models

Author new dbt models from Omni queries, and edit existing models in the dbt IDE

While not every query needs to be pushed down to dbt, when you do create a query where you want to promote the logic into dbt, you can do so within Omni.

Omni also supports editing existing models. You can run and compile dbt SQL with Jinja references and config settings, allowing you to interact with data as you build and eliminate the need for manual SQL substitutions when referencing existing models. Models authored in Omni can then be materialized through existing dbt workflows without requiring complex warehouse permissions for schema ownership.

Requirements

To follow the steps in this guide, you’ll need an Omni connection with a configured dbt integration.

Creating new dbt models from Omni queries

1. Open your query

   In a workbook, open the query you want to use to convert to a dbt model.

2. Convert to dbt model

   Click Model > Convert to dbt model. Omni will convert the query to dbt SQL.

3. Refine the model

   Use the SQL editor to refine the model. You can run the query periodically to verify the output.

4. Push to dbt repo

   Click Model > Push to dbt repo.

5. Configure the push

   In the Push to dbt dialog:

   • Model name — the filename for the new .sql file. Make sure this is unique — if it matches an existing model name, it will overwrite that file.
   • Git branch — the branch to push to
   • Commit message — a description of the change

   Click Push.

   Seeing changes to an existing file? If the diff shows modifications to a file you didn’t intend to update, the model name likely matches an existing dbt model. See Preventing overwrites to existing models for how to fix this.

6. Complete the pull request

   You’ll be prompted to create a pull request in the dbt git repository. Complete the pull request process, following your organization’s process.

After the pull request is merged and the model is built in your warehouse, bring it into Omni by triggering a schema refresh.

Editing dbt models

While the dbt IDE is read-only, you can edit existing dbt models in a workbook and push changes back to the dbt repository.

Modeler permissions are required to edit dbt models.

1. Open the dbt IDE

   Navigate to Develop, select your model, and click the dbt icon in the sidebar.

   
2. Select a model

   Use the file picker to select the dbt model you want to edit.

3. Open in a workbook

   Click the Open in workbook button near the top right corner of the page:

   
4. Edit the model

   The model will open in a new workbook. Use the SQL editor to iterate on the model.

5. Push changes

   When finished, click Model > Push to dbt repo and follow the same push flow as creating a new model.

After the model is rebuilt in your warehouse, bring in the updated version by triggering a schema refresh.

Preventing overwrites to existing models

When you convert a workbook query and push it to your dbt repository, Omni checks the Model name against your existing project. When pushing a model, Omni uses the Model name to determine whether to create a new file or update an existing one. If the name matches an existing dbt model, Omni treats the push as an update rather than a new addition.

To create a brand-new .sql file instead of overwriting:

1. Open the Push to dbt dialog.
2. Edit the Model name field to a unique name.
3. Confirm the push to generate the new file in your repository.

Check the diff. You can review exactly what will be changed or overwritten in the diff viewer before you confirm the push.

Next steps

• Trigger a schema refresh — Bring newly built or updated dbt models into Omni.