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).

Image for post
Image for post
SqlDBM — SQL Database Modeler

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

Finally we can run the SQL and check the result in Snowflake.

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

The end result is similar to the start of the Forward Engineer sample.

Image for post
Image for post

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,

Daan Bakboord

Originally published at https://daanalytics.nl on November 8, 2019.

Written by

Data & Analytics 📊 Consultant @daanalytics_nl & Partner @pongprof & Board Member @nl_OUG | Modern ☁️ Analytics #Oracle #Snowflake #Looker #Matillion #Fivetran

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store