Home

Awesome

dbt exposures crawler

PyPI version Tests Code checks codecov Apache License 2.0

Automates the creation of dbt exposures from different sources. Currently, only Tableau workbooks using Snowflake SQL are supported as a source.

This project is in an ALPHA stage. Internal and external APIs might change between minor versions.

Please reach out if you try this at your own organization. Feedback is very appreciated, and we would love to hear if you had any issues setting this up at your own.

Version compatibility

This project requires Python 3.8+. The latest published version of this package has been tested internally (at Voi) with dbt 1.x, Tableau Server 2023.1 and Snowflake SQL dialect.

Here is a summary of which versions from dbt and Tableau have been tested internally for recent versions of this package, but it doesn't mean that other dbt and Tableau versions will not work.

dbt-exposures-crawlerdbt versionTableau versionSQL dialect
0.1.4 - 0.1.51.1 - 1.4Tableau Server 2022.1 - 2023.1Snowflake

Installation

You can install the latest version of this package from PyPI by running the command below.

$ pip install dbt-exposures-crawler

Usage

Internally, we use this automation at Voi as part of our dbt docs release pipeline. We have a GitHub Action that does the following:

  1. Clone our dbt repository;
  2. Install dbt and our dependencies;
  3. Run dbt docs generate (it should be run against the production environment);
  4. Run this project (using the manifest.json generated from the previous command as input);
  5. Publish the generated documentation portal;

To run this project, we use:

$ python3 -m exposurescrawler.crawlers.tableau \
            --manifest-path=~path/to/dbt/target/manifest.json \
            --dbt-package-name="your_dbt_package_name" \
            --tableau-ignore-projects Archive \
            --verbose

Make sure you check the .env.example file to see which environment variables must be defined.

Project motivation

dbt is an open-source tool to manage data transformations in SQL. It automatically generates a documentation portal from your project which includes a dependency lineage graph. It is possible to add external downstream dependencies to this graph (such as a dashboard on a Business Intelligence tool) through a dbt feature called exposures, which are normally defined through yaml files.

This project automates the creation of exposures by implementing crawlers to parse the metadata of downstream tools. Currently, only Tableau dashboards are supported.

A few use cases on how having exposures can help:

How it works

This Python package will, in summary:

  1. Retrieve dbt models and sources from manifest.json;
  2. Extract metadata (custom SQL and table references) from Tableau workbooks using their GraphQL API;
  3. Try to find occurrences of the dbt models and sources in the Tableau SQL;
  4. Use the Tableau REST API to retrieve additional information about the workbooks (author, project, etc);
  5. Create the dbt exposures (in-memory) and write them back to the manifest.json.

More in-depth explanation:

First, you must provide the path to a dbt project manifest. The metadata and fully qualified names (database, schema and object name) are extracted from all dbt models and sources represented in the manifest. The combination of dbt models and dbt sources will from now on be referred as dbt nodes.

Next, workbook metadata is extracted from Tableau using their Metadata API, including workbooks that use custom SQL queries and workbooks that don't (which are referred in this project as "native SQL" workbooks). Note that this API is included in all Tableau licenses (i.e. it does not require the Data Management Add-on), but must be manually enabled if you host your own Tableau Server.

The SQL from the custom SQL workbooks and the table names from the native SQL workbooks are normalized through simple heuristics, such as removing quotes and converting the custom SQL to lowercase. With the normalized SQL and normalized table names from Tableau, and the fully qualified names for the dbt nodes available, this package tries to find the occurrences of the latter in the former.

The result is a mapping of workbooks and which dbt nodes they depend on. For every workbook (with mapped dependencies available), extra metadata that was not available in the Metadata API is then retrieved from Tableau using their REST API, including when the workbook was created, when it was last updated, to which folder it belongs to on Tableau and information about its author.

As a final step, the information above is written back in the provided manifest.json in the form of exposures. Note that instead of generating .yaml files on disk for each exposure, the exposures are written directly on the manifest.json, which can then be used to publish the dbt documentation portal.

Example

To better understand how this package works, let's take as an example the jaffle_shop dbt sample project. It has, among other models, a customers and an orders model.

Now suppose that you company has 4 workbooks on Tableau:

When running this project, you would get the following console output:

<p align="center"> <a href="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/cli_output.png"> <img src="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/cli_output.png?raw=true" alt="CLI example" width="600px" /> </a> </p>

The manifest.json that you provided would have 3 new exposures added to it, such as:

<p align="center"> <a href="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/modified_manifest.png"> <img src="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/modified_manifest.png?raw=true" alt="modified manifest" width="600px" /> </a> </p>

Those exposures can then be visualized through your dbt documentation portal, either by finding which exposures are downstream dependencies of a certain model:

<p align="center"> <a href="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/models.gif"> <img src="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/models.gif?raw=true" alt="models in the documentation portal" width="600px" /> </a> </p>

Or by doing the inverse. Starting from an exposure, find which models are used on it:

<p align="center"> <a href="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/exposures.gif"> <img src="https://github.com/voi-oss/dbt-exposures-crawler/blob/main/docs/exposures.gif?raw=true" alt="exposures in the documentation portal" width="600px" /> </a> </p>

This example has been taken from the integration tests available in this project. You can read more in the Testing section below.

Features, assumptions and limitations

Related projects

If you use Metabase, the dbt-metabase open-source project offers similar functionality to this project, but extracting exposures from Metabase dashboards instead of Tableau.

Development

Clone the repository and install it in editable mode:

$ pip install -e .

Before opening a pull request, make sure you run:

Architecture

The entry point for the crawlers should be on the crawlers module. For now, only Tableau is supported.

The tableau module contains all API clients (REST and GraphQL) and models.

The dbt module contains a model for representing a dbt exposure and utilities for parsing, interacting and saving dbt manifests.

Finally, the utils module has functions for logging and string parsing.

Testing

For the integration tests, we use a sample manifest.json as a fixture. It was manually generated from the jaffle_shop, an official dbt sample project. The following steps can be used to recreate it or to generate a new manifest on a more recent dbt version.

First, the dbt sample project should be cloned and dbt needs to be installed:

$ git clone https://github.com/fishtown-analytics/jaffle_shop
$ cd jaffle_shop
$ pipenv shell
$ pip install dbt-snowflake==1.4.4

Then, a new profiles.yml should be created in the same folder with the following configuration:

jaffle_shop:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: sample_db
      account: ...
      user: ...
      private_key_path: ...
      private_key_passphrase: ...
      warehouse: ...
      role: ...
      schema: ...

Afterward, the project can be compiled by running:

$ dbt compile --target dev

The generated manifest.json should then be prettified and copied to the tests/_fixtures folder in this repository. You can also manually remove the macros entries from the file since they are not needed and they make the file much larger and harder to be manually inspected

$ cat target/manifest.json | jq > $PROJECT_ROOT/tests/_fixtures/manifest.json

Future ideas

Contributing

We are open and would love to have contributions, both in Pull Requests but also in ideas and feedback. Don't hesitate to create an Issue on this repository if you are trying this project in your organization or have anything to share.

Release

There is a GitHub Action that will trigger a release of this package on PyPI based on releases created on GitHub. Steps:

License

This project is licensed under the Apache License, Version 2.0: http://www.apache.org/licenses/LICENSE-2.0.