How to Generate ERDs from a Snowflake Model
Only a few days ago I presented the proper way to reverse-engineer a Snowflake database schema with a simple Python script, without going at all through INFORMATION_SCHEMA:
- SHOW TABLES — to get all the tables
- SHOW COLUMNS — to get all the columns for these tables
- SHOW PRIMARY KEYS — for all PRIMARY KEY constraints
- SHOW UNIQUE KEYS — for all UNIQUE constraints
- SHOW IMPORTED KEYS — for all FOREIGN KEY constraints
Here is another simple Python script that generates ERDs (Entity-Relationship Diagrams) from the loaded metadata in DOT Graphviz notation. The generated code can be pasted on free online GraphViz viewers like viz-js, to instantly render ERDs. Or you may get them previewed automatically in VSCode, through a great plugin. The full open-source code is now on GitHub, free for you to use!
The Entity-Relationship Diagrams
For a free Chinook sample database, we’ll generate three DOT files, as follows:
(a) The -full.dot version will be used to render the table columns with their data types (which displays the most amount of information):
