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