SqlDBM partnership with Snowflake
As promised in a previous blog, I will have a look at the SqlDBM partnership with Snowflake. According to SqlDBM it “offers you an easy, convenient way to design your database absolutely anywhere on any browser, working away without need for any extra database engine or database modelling tools or apps”. With SqlDBM you can both Forward Engineer (generate SQL) as well as Reverse Engineer (import an existing Database Schema). Check out more details about the the SqlDBM partnership with Snowflake here.
Using SqlDBM is as easy as navigating to; https://sqldbm.com/Home/ and sign-in (or sign-up first).
Forward Engineer
With Forward Engineering, SqlDBM makes it possible to generate DDL statements. These DDL statements can be executed in Snowflake to create the physical data model (tables, columns, relationships, schema properties, file format options, column constraints, etc.).
It’s a relatively simple process. For the ease of this blog, I use a sample project from SqlDBM. I will Forward Engineer this to Snowflake.
SqlDBM comes with samples for various different databases. For this example we will choose Snowflake.
First we can start with a standard opening a standard sample project in SqlDBM.
This sample project will show simple star-schema with a few dimensions and a fact-table. In this sample the data modelling exercise has been executed already. This means that there are tables, with columns and columns with data types. Next to that the dimension tables are linked to the fact table. All tables have constraints defined. More details (various table properties) can be defined if necessary.
Generate SQL
When satisfied with the model as displayed above, you can generate the DDL. These are SQL-scripts which can be executed in Snowflake. Find the generated SQL on Github.
Snowflake result
Finally we can run the SQL and check the result in Snowflake.
Reverse Engineer
The opposite of Forward Engineering is Reverse Engineering. In the case of Revere Engineering the starting point is the database. In this case Snowflake. Before Reverse Engineering Snowflake in SqlDBM, you need the Snowflake DDL (Data Definition Language). This is possible via the ‘GET_DDL’-command in Snowflake. Check the code in Github.
Import Snowflake DDL
Running this code in Snowflake returns the statement to import (Reverse Engineer) into SqlDBM. Find the generated SQL on Github. This code can be imported into SqlDBM.
SqlDBM result
The end result is similar to the start of the Forward Engineer sample.
Thanks for reading this blog. In a few simple steps I showed how to use SqlDBM and Snowflake in a partnership. Create data models in SqlDBM and execute these in Snowflake. Also the other way around is an option. Create the data model in Snowflake and import this model in SqlDBM
Cheers,
Originally published at https://daanalytics.nl on November 8, 2019.
