Snowflake Data Modeling

Snowflake’s architecture is a hybrid of traditional shared-disk database architectures and shared-nothing database architectures. It supports the most common standardized version of SQL: ANSI.

Hackolade Studio was specially adapted to support the data modeling of Snowflake, including schemas, tables and views, indexes and constraints, plus the generation of DDL Create Table syntax as the model is created via the application. In particular, Hackolade Studio has the unique ability to model complex semi-structured objects stored in columns of the VARIANT data type. The reverse-engineering function, if it detects JSON documents, will sample records and infer the schema to supplement the DDL table definitions.

The data model in the picture below results from the data modeling of the sample weather data available in some regions of the trial sandbox:

It provides a simple overview of the data structures featured in this Snowflake environment in the Hackolade Studio, so that users can do all kinds of useful actions with the data model.

Schema visualization

Hackolade Studio helps users to better understand and work with their Snowflake data by providing them with a visual representation of the structure of the data that is stored inside their Snowflake environment.

Structural components of a Snowflake model: Table structure

All data in Snowflake is structured in database tables, logically structured as collections of columns and rows – optionally with single-column or multi-column constraints. Snowflake does not use indexes, which is one of the things that makes it scale for arbitrary queries. Instead, Snowflake calculates statistics about columns and records in files that you load, and uses those statistics to figure out what parts of what tables/records to actually load to execute a query.

Technical components of a Snowflake model: keys, clustering, constraints

On top of the structural components of a Snowflake model, Hackolade Studio allows users to manage some of the more technical components of the model. Unique, Primary, and Foreign keys can be highlighted and indicated in the model, as well as certain Clustering Keys that allow for better performance management of the Snowflake environment by using micro-partitions. Hackolade Studio also allows users to manage the details of your data types of the Snowflake data, including the NOT NULL constraints. Lastly, Hackolade also supports the concept of a (Materialized) View, so that users can access important queries as if they were a table.

The simplest way to visualize a Snowflake model is to present it in an Entity-Relationship Diagram, a sort of blueprint of the foundations of an application, showing a map of the data. The future queries and data representation in the application screens are the main drivers for the design of a data model.

Entity Relationship Diagram (ERD)

The ER diagram displays the tables and data elements of the tables, as well as the potential relationships between the data elements.

Hierarchical view of all data elements

A Snowflake data model is described with definitions of properties and constraints for each table and field. Hackolade Studio represents these fields in a convenient and easy to read hierarchy, with a “Properties” pane to the right that immediately allows the user to understand the details of a particular data element. This can be supplemented with detailed descriptions and a log of team comments gathered as the model adapts over time for the schema evolution.

This is particularly useful when storing JSON in Variant columns.

Outputs of a Schema Design tool for Snowflake

In addition to the DDL script that is generated for the specific Snowflake data model that you are working on, Hackolade Studio also provides the possibility to generate the HTML, PDF, or Markdown documentation that includes all the diagrams, tables, relationships, and metadata of the Snowflake data model.

A data model provides a blueprint for applications that closely represent the understanding of complex data-centric enterprises. Hackolade increases data agility by making data structures transparent and facilitating its evolution. The benefits of data modeling for Snowflake are widespread and measurable.

Schema design is a best practice to ensure that applications evolve, scale, and perform well. A good data model helps reduce development time, increase application quality, and lower execution risks across the enterprise.

Free trial

To experience the first Snowflake data modeling tool and try the full experience of Hackolade Studio free for 14 days, download the latest version of Hackolade Studio and install it on your desktop. There's no risk, no obligation, and no credit card required! The software runs on Windows, Mac, and Linux, plus it supports several other leading NoSQL databases. Or you can run the Community edition in the browser.