dbt Integration

Hex has two separate integrations with dbt Cloud — the dbt metadata integration, and the dbt server integration — enabling analytics engineers and business users to work closer together than ever.

dbt Metadata integration

If you use dbt Cloud, Hex can use metadata from your dbt project to enrich the Data browser with additional information.

Hex will fetch the following information from your scheduled jobs in dbt Cloud:

• Model, source and column descriptions and tests
• When the model was last updated
• Any source freshness tests
• Links to the relevant page of your deployed docs site in dbt Cloud

Once the dbt integration is configured, tables in the Data browser that are part of a model or defined as a source in dbt will display their most recent job execution date, source freshness, and the status of any tests configured on the model.

This metadata will be refreshed automatically for all tables in a data connection associated with dbt models or sources whenever you perform a schema refresh, and only needs to be configured once per connection.

tip

If a new table has been added to a data connection, you will need to refresh the Data browser in order to see the new table.

Configure the dbt Metadata integration

Each data connection can be individually configured to integrate with dbt. From the connection settings, you can toggle on the dbt Metadata integration and provide your credentials. Hex will automatically find the relevant schemas and tables as modeled in dbt.

Account ID and Project ID can be most easily found in the URL of the "Jobs" page for your project, following this structure: https://cloud.getdbt.com/#/accounts/<ACCOUNT_ID>/projects/<PROJECT_ID>/jobs/

The service token must have both the "Metadata Only" and "Job Admin" permissions. To generate a service token:

1. Head to the Account Settings view in dbt Cloud.
2. Click the Service Account tokens page and select "New Token".
3. Name the token ("Hex dbt integration" is a good starting point), and add the "Metadata Only" and "Job Admin" permissions.
4. Add the the token to the Hex connection.

Note: After a token is generated, it won't be able to be viewed again so either add it to Hex immediately, or store it somewhere very safe!

Supported connection types

• BigQuery
• Databricks
• PostgreSQL
• Redshift
• Snowflake

Troubleshooting

If you're experiencing errors, check the error message by hovering over the warning icon in the Data browser.

Error type	Troubleshooting
No jobs found for this project	The dbt Cloud integration pulls information from jobs invoked via the dbt Cloud Scheduler. Check that at least one job in dbt Cloud has the "generate docs" checkbox enabled (more info), and that it has been run successfully.
401 Unauthorized	This error message often means the token provided on the connection is invalid, or does not have the correct permissions. To fix this, follow the steps in the above section on Configuring the dbt integration to create a new token.
All other errors	For all other errors, first check the dbt Cloud status page to see if there is an active issue. Then contact dbt support for more assistance.

If you receive no errors on the dbt integration but don't see any dbt data coming through to the Data browser, check that:

• At least one job has been set up via the dbt Cloud scheduler
• At least one job meets the following criteria:
  • The "generate docs" checkbox is ticked
  • The job uses a command other than dbt docs generate (if you want to use a command that doesn't build anything in your warehouse, consider using dbt list or dbt compile)
  • The job has run successfully at least once, and docs have been produced as a result

dbt Server integration

Hex also integrates with the dbt Server to unlock the dbt Metrics cell and allow users to write SQL that references dbt macros, including ref and source macros.

tip

At present, dbt only supports dbt Server integrations for Snowflake users.

Configuring the dbt Server integration

To turn on the dbt Server integration:

1. First ensure that the dbt metadata integration is enabled (as above)
2. Generate a dbt Server URL in dbt Cloud. To do this, you'll need to enable the "Semantic Layer" option in the "Settings" page of your production environment. See dbt's docs for setting up the dbt Semantic layer here. Once enabled, you'll see a "Proxy server" URL, which you can copy.
3. In Hex, turn on the dbt Server integration on the desired connection and fill in the dbt Cloud proxy server field with the proxy URL created in dbt Cloud

tip

If your data is firewalled, you'll need to configure your Snowflake connection to allow traffic from dbt's IP addresses.

Running SQL against the dbt Server

When querying a connection which has the dbt server integration enabled, you can write queries which include any standard dbt jinja functions or custom macros defined in your production dbt environment. This includes all refs and sources!

You can mix-and-match regular query parameterization using standard Jinja with dbt-specific functions in your queries. Select "Show compiled SQL" in the top right-hand corner to see the rendered query that Hex sends to the dbt server.

Usage notes

• Keep in mind that Hex does not know what is valid Jinja in your dbt project. We pass on any unrecognized Jinja to the dbt server. As a result, if you happen to mistype a Hex variable (e.g. my_varible instead of my_variable), we'll send it along to dbt and the compilation error will happen on dbt's side.
• Hex does not currently support mixing variables from the Hex and dbt Jinja contexts within the same section of Jinja. This means you cannot use a variable defined in a Hex project as an argument to a dbt macro.
• SQL autocomplete does not work when using Jinja references instead of a table name.

FAQs

Does Hex have an integration with dbt Core?

Not currently. Both the dbt Metadata and dbt Server integrations rely on features that are part of dbt Cloud (the Metadata API and semantic layer respectively).

However, the schema browser does pick up table and column descriptions from your data warehouse. One way to have the descriptions from your dbt project appear in Hex is to:

1. Set the persist_docs config to true in your dbt project (docs). This will write your descriptions to your database as table and column comments.
2. Run your dbt project from any orchestrator.
3. Refresh the schema browser in a Hex project. Your descriptions should now appear in the schema browser.

Note that this will not populate other information about your dbt project, like tests and their statuses, and model build times.