How to - automatically generate ERDs and display in your docs site

[dpguthrie]

Background

What is an ERD?

An ERD, or entity-relationship diagram, is a graphical representation of data - showing entities, their attributes, and how they can relate to other entities.

Setup

Docs (source code)

Create a docs block containing a link to your soon-to-be-created ERD:

{% docs tpch_source %}
Check out the TPCH ERD!

![ERD](https://raw.githubusercontent.com/dpguthrie/snowflake-dbt-demo-project/main/assets/tpch_erd.png)
{% enddocs %}

You'll notice that I created an assets folder to store my ERDs - the location is up to you!

Then, add your block to a yml file:

version: 2


sources:
  - name: tpch
    description: '{{ doc("tpch_source") }}'

Python (source code)

The python script should do the following:

Identify the schemas you'd like to generate an ERD for:

SCHEMAS = ['TPCH']

Run a job specifically created for a merge to main. Think of this as a good time to run those newly merged models and anything downstream (or simply a dbt compile and dbt docs generate will suffice)

from dbtc import dbtCloudClient

# I'm using a dbt Cloud python client called dbtc - lots of other alternatives here
client = dbtCloudClient(service_token=os.getenv('DBT_CLOUD_SERVICE_TOKEN')

run_id = client.cloud.trigger_job_and_poll(account_id, job_id, {'cause': '"Cause" I want an ERD!'})

Create a database URL. I'm using the snowflake-sqlalchemy package and the URL utility to create my database URL. Other dialects can be found here.

url = URL(
    account=os.getenv('SF_ACCOUNT'),
    user=os.getenv('SF_USER'),
    password=os.getenv('SF_PASSWORD'),
    database='DOUG_DEMO_V2',
    schema=schema,
    warehouse='TRANSFORMING',
    role='TRANSFORMER',
)

Finally, create an ERD using the eralchemy package.

from eralchemy import render_er

# ensure that the path here matches what you've set in your docs block
render_er(str(url), f'assets/{schema.lower()}_erd.png')

Github Action (source code)

The action will be triggered when a pull request is merged to our main branch:

on:
  push:
    branches:
      - main

An important callout here is that we need to install graphviz and its dependencies in order for pygraphviz to work properly.

      - name: Install graphviz
        run: sudo apt install graphviz-dev graphviz

Then, our python dependencies are installed via requirements.txt and our python script from above is run. Ensure that you have the appropriate environment variables set via secrets stored within your repo.

      - name: Install dependencies
        run: pip install -r requirements.txt
      
      - name: Run Python
        run: DBT_CLOUD_JOB_ID=93955 DBT_CLOUD_ACCOUNT_ID=43786 python scripts/run_and_erd.py
        env:
          SF_ACCOUNT: ${{ secrets.SF_ACCOUNT }}
          SF_USER: ${{ secrets.SF_USER }}
          SF_PASSWORD: ${{ secrets.SF_PASSWORD }}
          DBT_CLOUD_API_KEY: ${{ secrets.DBT_CLOUD_API_KEY }}
          DBT_CLOUD_SERVICE_TOKEN: ${{ secrets.DBT_CLOUD_SERVICE_TOKEN }}

The python script will create .png files and the last step will add those newly created files directly to your main branch.

      - name: Add file
        uses: EndBug/add-and-commit@v9
        with:
          add: '*.png'
          committer_name: Github Actions
          committer_email: [email protected]
          message: 'Add ERDs'

Caveats

I'm using the dbt_constraints package to automatically create foreign keys via an on-run-end hook. Currently, this package is only compatible with Snowflake and PostgreSQL.

Output

[gwenwindflower]

hot fire flame 🔥 right here

[patkearns10]

This is so cool!
Some questions and thoughts on what to add to the devhub post:

• ERD vs DAG -- when to look at one versus the other?
• does this pull from the raw source data? could this be applied to the dbt models we end up creating?
• can this be run on a schedule to update if/when schemas change?

[dpguthrie]

Thanks, Pat!

ERD vs DAG -- when to look at one versus the other?

Would love some others thoughts here as well, but my take is this:

• DAG - This is useful in the context of a specific model - how was it built, what are its dependencies, etc
• ERD - This is useful in the context of a specific layer - how do the tables in a specific mart relate to one another, how could I create a SELECT statement that pulls data from tables X, Y, and Z.

does this pull from the raw source data? could this be applied to the dbt models we end up creating?

This can apply to dbt models as well! The only requirement is that you create those foreign key constraints on the models also. The only reason I used the source data is because it was the fastest way to get to a working solution 😅.

can this be run on a schedule to update if/when schemas change?

The script will run anytime code is pushed to the main branch (PR merged into main). So, it should always be up to date with whatever you have in production!

[b-per]

Nice!

To follow up on Pat's notes, a similar (but slightly different) approach would be to consume the dbt manifest and catalog files directly instead of querying the DW.

This repo is a python script that creates a dbml file from the dbt artifacts. This dbml file can then be loaded and accessed on https://dbdocs.io/ and/or rendered in SVG with this other repo.

The advantage of this approach is that it is compatible across Warehouses.

[dpguthrie]

Thanks for the additional solutions, Benoit.

I do like the dbml solution, and the ERD looks much better in dbdocs, but it requires human intervention, right? Or can you programmatically generate the ERD with dbdocs?

I think this solution is also compatible across warehouses. The first argument to render_er is just the database URL, so you'd just have to ensure you have the right dialect for the warehouse you're connecting to.