How to Reverse Engineer a Snowflake Database Schema

To extract an existing database model, the typical approach is to query the INFORMATION_SCHEMA views. However, with Snowflake you will soon discover that there are some limitations. Column names, for instance, cannot be detected when you look for the foreign key references! Compare the queries Dataedo offers for Snowflake and for Postgres: there are no column names extracted from Snowflake! A product like DbSchema will also not be able to extract foreign keys from a Snowflake database.

The Snowflake-specific get_ddl() function will not work on direct shares. And the free sample schemas provided by Snowflake come through direct shares: you’ll get a “SQL compilation error: This operation is not supported on shared database ‘SNOWFLAKE_SAMPLE_DATA’” error…

But there is a better way to do it! All using the powerful Snowflake-specific SHOW statement! Except that the most powerful of all — which also solves the foreign keys problem — is not even documented yet: at the present moment, SHOW IMPORTED KEYS does not appear in Snowflake’s documentation.

The ORM Model

We’ll write a simple script in Python, extracting the metadata from one single database schema. There will be no cross-schema relationships, but the script could be easily extended to reverse engineer a whole database…