erdgen

DBT YML ERD Generator

Overview

This Python program generates Database Markup Language (DBML) Entity Relationship Diagram's (ERD) from the relationships node in your dbt YML files. The script parses the YML files, extracts relationships and columns, and outputs a DBML schema.

The program is pretty opinionated. It requires each YML file to only contain one model. Further, the "relationships" node in dbt yml is a made-up construct.

This program is useful for automated ERD generation if your dbt project doesn't have referential integrity or explicit SQL relationships. If your SQL models have defined SQL relationships there are better tools for automated ERD generation.

Usage

python -m erdgen --directory <directory> --include_non_join_keys <True/False>

Args

• --directory: Directory to search for YAML files. The default value is the current directory ('.').
• --include_non_join_keys: Boolean flag to indicate whether to include non-join keys in the DBML. The default value is False.

The DBML will be printed to the console. You can redirect this output to a file if desired.

YML File Structure

The YML files are expected to have the following structure:

version: 2

models:
    - name: Computer
      description: beep boop beep
      columns:
          - name: computerId
            description: The unique identifier of computer
          # other non-join key columns as necessary
      relationships:
          - name: files
            description: The files are in the computer!?
            type: one_to_many
            table: computer_files
            join:
                - local: computerId
                  remote: computerId

note: Each YML file should contain only one model under the models node.

Relationships

The relationships node in the YML files represents the relationship between the current model and other models. It is composed of several sub-nodes:

• name: The name of the relationship.
• description: A brief description of the relationship.
• type: The type of the relationship. It can be one_to_one, one_to_many, many_to_one, or many_to_many.
• table: The name of the other model involved in the relationship.
• join: A list of the columns that are used for the join between the current model and the other model. Each item in the list is composed of local and - remote nodes, representing the column in the current model and the column in the other model, respectively.

Output

The output is a DBML schema that includes the tables, columns, and references based on the relationships defined in the YML files. The output is printed to the console.

Notes

• If a YML model file has no relationships, and include_non_join_keys is False, all columns from the YML are included in the DBML table. This is helpful as other models may have a relationship to this model, and there is no way to know which column is being referenced (well there is but I didn't bother figuring this out)
• Regardless of whether include_non_join_keys is True or False, columns that contain Id or id in them are always included. These are likely join keys that do not have a relationship yet.

Improvements

• All data types are int, account for the actual data type via metadata in the YML file
• All relationships are 1:1, account for the cardinality via the relationship type
• What about .yaml files lol

DEV

Setup

make install

This will setup a virtualenv, upgrade pip, install dependencies, and install pre-commit

note: This does not activate your virtualenv! You must run source env/bin/activate to use the venv.

Test

make test

Format

make format

make lint

Version & Release

make version-<major/minor/patch>

make release

note Don't forget to git push with --tags

pre-commit

Setup

make install-pre-commit

Run all

make pre-commit