December 23, 2021 - 6 minutes read

How to Model Snowflake External Tables in Vertabelo

Martyna is a software developer with a passion for programming, automation, and innovation. Currently working for the global corporate IT consulting sector, she is a new-generation techie who hails from Warsaw University of Technology, Poland. She has certifications in both database administration and Java development. Her interests include creating small automation devices using embedded systems and various electronic components. In her free time, she practices yoga, which helps her to center her thoughts and come up with new ideas.

Tags:

snowflake schema

There is a lot more data than stored in databases. This raises the question of how to access all the external data from the database. The external tables come to the rescue! Read along to learn more about external tables in Snowflake and how to model them in Vertabelo.

This article focuses on external tables in the Snowflake database. We will first introduce the Snowflake database and the concept of external tables. We’ll then see how to model Snowflake external tables in Vertabelo.

To find out more about Vertabelo’s support for the Snowflake database, check out this article.

Let’s get started!

External Tables in Snowflake

We will start with the basics. Let’s find out more about Snowflake and the concept of external tables.

What Is Snowflake?

Snowflake provides various services. It is a cloud computing data warehouse where you can store and analyze data. It is also an SQL database that lets you store, access, and retrieve data.

Source: https://www.snowflake.com/blog/managing-snowflakes-compute-resources/

Snowflake runs on Amazon Web Services, Microsoft Azure, and Google Cloud. It introduces a convenient separation of data storage from computing, thus allowing for seamless scaling of both the storage and the CPU independently.

What Are External Tables?

We are used to working with database tables whose data resides internally in a database. But we can also create tables with data external to the database. These are the so-called external tables.

What else should you know about external tables?

While the data in external tables resides outside the database, external tables make it feel as if it is inside.
External tables let you use their data for querying and joining operations.
External tables are read-only. So, you cannot perform any DML operations on them.
Querying data external to a database can negatively impact query performance. You can fix it by creating materialized views based on external tables and running queries on these views.

Let’s visualize external tables in a database.

External tables do what the name indicates. They take data from external files and make it available in a database. Standard tables, on the other hand, contain data that resides internally in a database.

Modeling Snowflake External Tables in Vertabelo

Now, let’s get started with Vertabelo. We’ll learn how to create external tables in Snowflake and modify their properties.

But first, let’s create a new physical data model for our Snowflake database. Then we can design external tables in ER diagrams.

Now, we are ready to start!

There are many ways you can draw ER diagrams. Learn more by visiting our articles on How to Draw an ER Diagram Online and Top 7 Entity Relationship (ER) Diagram Online Tools.

Creating External Tables in Snowflake

A Snowflake ER diagram would not be complete without external tables. Vertabelo provides you with convenient ways of creating them.

Via the Toolbar

Just click the button, and you’ll get the external table ready.

Via the Left Panel

You can also do it via the left-hand panel, like this:

Now, our external table is ready.

Let’s fix the errors and warnings by modifying the properties in the right-hand panel.

Modifying Properties of External Tables

Now that we have created an external table in Snowflake, we can fill in the properties.

Changing the Name

When an external table is created, there is a warning that says we should change its default name. We can do that in the General section of the right-hand panel, like this:

We got rid of the warning! Let’s move on.

Adding Columns

We can add columns to an external table in the same way we do for standard tables. In the Columns section of the right-hand panel, there is an Add column button. By clicking it, we can add some columns.

neral section of the right-hand panel, like this:

But there are still two errors. Let’s resolve those.

Adding the File and its Location

The remaining errors tell us the file location and file format fields are missing. We can easily add them in the Additional properties section of the right-hand panel. First, click the Set button next to the Location and File format fields. Then, you can type in the location of the external file and its format.

We have created an external table and got rid of all the errors. Now, we can explore other properties.

Other Properties for External Tables

There are many other properties in the Additional properties section of the right-hand panel. Let’s go through them one by one.

The available properties are, from the top:

The Schema property asks you for the database schema name where the external table exists.
The Location property requires the location of the external file (as mentioned in the previous section). The trace of this property in the generated SQL script is WITH LOCATION = <value of the Location field>.
The Partition by property asks you how to partition the table. The trace of this property in the generated SQL script is the PARTITION BY
The File format property requires the format of the external file. The trace of this property in the generated SQL script is FILE_FORMAT = <value of the File format field>.
The AWS SNS topic property is optional. The trace of this property in the generated SQL script is AWS_SNS_TOPIC = <value of the AWS SNS topic field>.
The Pattern property lets you filter the data that matches the given pattern. The trace of this property in the generated SQL script is PATTERN = '<value of the Pattern field>'.
The Auto refresh property is set to Yes by default (i.e., after clicking the Set button). This property ensures periodical synchronization of the data in the external table with the data in the file unless you set it to No. The trace of this property in the generated SQL script is AUTO_REFRESH = TRUE|FALSE.
The Refresh on create property is set to Yes by default (i.e., after clicking the Set button). This property ensures the data of the external table is synchronized with the data in the file data at the table creation time unless you set it to No. The trace of this property in the generated SQL script is REFRESH_ON_CREATE = TRUE|FALSE.

Other Properties for Columns of External Tables

In the Columns section of the right-hand panel, you can expand each column by clicking the arrow next to it.

There are two properties: Expression and Constraint. The Expression property defines the alias name for a column. The trace of this property in the generated SQL script is columnName AS columnAlias. The Constraint property defines the constraint set on a column. It may be NOT NULL, DEFAULT, PRIMARY KEY, etc.

These are all the properties you can use when creating ER diagrams that contain external tables in Vertabelo.

What’s Next?

External tables in Snowflake can be considered an interface that lets you view data external to your database. Not only can you view external data, but you can also query and join with other tables. And if you create a view based on an external table, there are benefits such as improved query performance. External tables are a powerful tool that pays to know.

Now, it’s time for you to create ER diagrams with external tables in Vertabelo on your own. Be sure to check out the current developments in Vertabelo by following this article. Good luck!