Skip to main content

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 dbtTestDocsConnection
    • 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:dbtExpectedProjectPage
    • 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. dbtSubmitDocsConnection

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, with the format being target_schema_name.model_name (where target_schema_name is the target designated in your dbt profiles.yml configuration). You must still specify a value in the sql_table or sql_query parameter, following the format of target_schema_name.output_table_name.

    ---
    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_dbt.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 the Transform App

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 dbtLineageGraph

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

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 the Transform App

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.dbtDocsButton

Alternatively:

  • Click the “dbt Source Freshness” button.
  • Click on the data source name of any of the Data Source cards. dbtSourceFreshnessArrow
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.