dbt

dbt is a data transformation tool where data analysts and engineers can transform, test, and document data in the data warehouse. Transform’s integration with dbt gives you the power to define Transform data sources using dbt models. Transform then surfaces useful metadata from your dbt models—like the time of the last successful run—and links to model documentation directly in the Transform user interface (UI).

Requirements

• An active dbt cloud account or dbt core setup
• An active Transform account

Setup Instructions

You’ll first need to do the following setup in your dbt project and Transform in order to use the integration:

1. (In dbt) In your dbt project’s dbt_project.yml file, add the following “post-hook” line under the models parameter:

   models:
     test_dbt_model:
       post-hook: "{{ transform_dbt_sync.log_model_end_event() }}"
2. (In dbt) In your dbt project’s packages.yml file, add the transform-dbt-sync package:

   - git: https://github.com/transform-data/transform-dbt-sync
     revision: 0.1.1
3. (In dbt) Commit the changes from Steps 1 and 2 to your project.
4. (In dbt) If you have a dbt cloud account, our integration uses the dbt models’ docs links associated with your account. Please make sure that you’ve selected the setting to generate the docs on run for your project.
5. (In dbt) Kick off a fresh run of your dbt project to ensure that the installed package has logged the requisite metadata.
   • If you are using dbt cloud, manually kick off a new dbt cloud run of your project.
   • If you are not using dbt cloud, follow your manual process for kicking off a dbt run of your project.
6. (In Transform) In the Transform UI, navigate to Settings → Integrations → Click “Get Started” on dbt card:
   • Fill in the input fields on the modal
     • If you are a dbt cloud user, enter your account ID in the respective input field
     • If you self-host your dbt docs, enter your self-hosted docs URL in the respective input field
   • Click the “Test Connection” button. You should be taken to a new tab that takes you directly to the following blank page on dbt with a url path to your projects directory:
   • Once you’ve verified that "Test Connection" does in fact take you to the dbt project docs site you expect, mark the verification checkbox and click the “Submit” button.

Using dbt models for your Transform Metrics

Once you complete the setup instructions, you’re ready to add your dbt models as a data source in your Transform source configuration files.

1. Add your dbt model in the dbt_model parameter. The format for the dbt_model value is schema_name.model_name. You must still specify a value in the sql_table or sql_query parameter.

   ---
   data_source:
     name: calls
     description: This data source is sourced from the demo.calls table. Each row in this table represents a call. Each call has a user_id representing the user making that call.
     owners:
       - larry+david@transformdata.io
     sql_table: demo.calls
     dbt_model: demo_dbt.calls_base
   
     identifiers:
       - name: call
         type: primary
         expr: call_id
       - name: user_id
         type: foreign
   
     measures:
       - name: calls
         description: The total number of calls made.
         expr: 1
         agg: sum
   
     dimensions:
       - name: ds
         type: time
         is_partition: false
         type_params:
           is_primary: True
           time_granularity: day
   
     mutability:
       type: immutable
   ---
   metric:
     name: calls
     description: Calls made by users.
     owners:
       - larry+david@transformdata.io
     display_name: Calls 📞
     type: measure_proxy
     type_params:
       measure: calls
     tier: 1

2. Run mql validate-configs to verify that your data source file which uses a dbt model passes all of the validation checks.

3. Commit your configs, either by opening and merging a PR or by running mql commit-configs --force-primary.

Viewing dbt source freshness in Transform Metric Store

You’ll now be able to see the source freshness for the dbt models you use as data sources in the Transform UI. In the UI, navigate to a metric page where you used a dbt model as a data source.

note

Your dbt job(s) will need to run before you are able to see dbt source freshness in the Transform UI for the first time. It can take up to 10 minutes for the dbt metadata to sync with Transform and get surfaced to the UI.

There are two places in the UI where you can view dbt source freshness information:

1. Click the “Metric Lineage” button in the Metric Definition section

2. Click the “dbt Source Freshness” button in the Metric Definition section

In both sections above, the dbt source freshness timestamp indicates the last time that particular dbt model completed a run.

note

At the moment, source freshness information will only be displayed for metrics that use dbt models as a data source.

Viewing dbt lineage docs in Transform Metric Store

If you have completed Step 3 in the Setup Instructions section, then you’ll also be able to access the lineage docs link for every dbt model you use as a data source in Transform.

Follow these steps:

• Click the “Metric Lineage” button.
• Click on any Data Source card in the lineage graph with a dbt icon.
• Scroll down and click the “dbt Source File” button. You should be taken to a new tab that takes you directly to the docs page for that particular dbt model.

Alternatively:

• Click the “dbt Source Freshness” button.
• Click on the data source name of any of the Data Source cards.

note

We generate dbt lineage docs links based on the dbt run documentation. For the docs links to work, make sure that you’ve completed Step 4 (i.e. enabling the setting to generate the docs on run for your dbt project) in the Setup Instructions.