If you’re using dbt as your source of truth for database schemas and metabase as your analytical powerhouse, the dbt-metabase integration makes your life easier by propagating table relationships, model descriptions, and more into Metabase. This article walks you through the essentials of setting up this integration smoothly.

Requirements

Before diving in, ensure you have Python 3.8 or above installed on your machine. For development purposes, you need to have uv installed as well.

Installation

To install dbt-metabase, simply run the following command:

pip install dbt-metabase

Getting Started: Manifest File

Before you can run dbt-metabase, a compiled manifest.json file is required, which consists of artifacts generated during the dbt compilation process. This file is typically found in the target directory of your dbt project after a successful compile.

For more details, refer to the dbt documentation.

Connecting to Metabase API

All commands require authentication against the Metabase API. You can authenticate using:

• API Key: Use --metabase-api-key for automation.
• Username and Password: Use --metabase-username and --metabase-password for older versions.

Exporting Models

To export your models to Metabase, define a simple schema.yml like this:

yaml
models:
  - name: stg_users
    description: User records.
    columns:
      - name: id
        description: Primary key.
        data_tests:
          - not_null
          - unique
      - name: email
        description: Users email address.
      - name: group_id
        description: Foreign key to user group.
        data_tests:
          - not_null
          - relationships:
              to: ref(groups)
              field: id
  - name: stg_groups
    description: User groups.
    columns:
      - name: id
        description: Primary key.
        data_tests:
          - not_null
          - unique
      - name: name
        description: Group name. 

This setup is sufficient to propagate the primary keys, foreign keys, and descriptions to Metabase. Use the command:

dbt-metabase models \
  --manifest-path target/manifest.json \
  --metabase-url https://metabase.example.com \
  --metabase-api-key mb_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX= \
  --metabase-database business \
  --include-schemas public

Fleshing Out Foreign Keys and Semantic Types

To add foreign keys and semantic types, use meta fields. Think of this like labeling items in an organized warehouse. You label the primary key as “ID” and foreign key as “Group ID” so that everyone knows how to relate different boxes easily. For instance:

yaml
- name: country_id
  description: FK to Users country in the dim_countries table.
  meta:
    metabase.fk_target_table: analytics_dims.dim_countries
    metabase.fk_target_field: id

Further, you can mark semantic types like:

yaml
- name: email
  description: Users email address.
  meta:
    metabase.semantic_type: typeEmail

Visibility Types

Control the display of tables and columns in Metabase. For example, to keep email addresses hidden for sensitive data, you would set:

yaml
- name: email
  description: Users email address.
  meta:
    metabase.semantic_type: typeEmail
    metabase.visibility_type: sensitive

Exporting Exposures

You can extract questions and dashboards from Metabase as dbt exposures using:

dbt-metabase exposures \
  --manifest-path target/manifest.json \
  --metabase-url https://metabase.example.com \
  --metabase-api-key mb_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX= \
  --output-path models \
  --exclude-collections temp*

Upon completion, check the output path for YAML files containing enriched insights from your Metabase dashboards.

Troubleshooting

If you encounter issues, ensure your manifest.json file is correctly compiled and that you have the right credentials for the Metabase API. You may also want to check for any discrepancies between your dbt models and those in Metabase.

For additional support, stay connected with fxis.ai for further insights, updates, or to collaborate on AI development projects. Keep an eye on potential errors that may arise if synchronization is not set correctly. If you have column discrepancies, consider setting the sync timeout to zero, but proceed with caution.

Conclusion

Integrating dbt and Metabase can significantly enhance your analytics workflow. By following these steps, you can achieve a seamless connection, ensuring your data remains a valuable asset.

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.