4 New Table Types in 2022 by Snowflake - A Summary - by Somen Swain - Snowflake - Dec, 2022 - Medium

Published in Snowflake
Somen Swain
Dec 17, 2022 · 6 min read · Listen
Snowflake New Table Types
4 new table types in 2022 by Snowflake — A

summary Somen Swain
105 Followers
This year i.e., 2022 there has been a series of new features which were Solution Architect at Tech Mahindra by
launched by Snowflake to enhance its platform. In this blog we are going to Profession, Student of Data Modernization by
Passion
discuss about 4 new category of tables that were launched by this product.
Follow
We all by now have become very familiar with Snowflake’s table types which
are namely “Standard Tables”, “Temporary Tables”, “Transient Tables” & More from Medium
“External Tables”. Let us discuss on the
9 newly
3 introduced table types, their
John Ryan in Snowflake
features and what problems do they solve.
Top 14 Snowflake Data
Engineering Best Practices
The four new type of tables that were announced by Snowflake this year are
Gonzalo Fernandez Plaza
as follows:
Snowflake SnowPro
Advanced: Architect —
1. Iceberg Tables. Practice Exam Questions
2. Dynamic Tables. Rajiv Gupta in Dev Genius
When To Use Which Table In

3. Hybrid Tables.
Snowflake?
4. Event Tables.
Rajiv Gupta in Dev Genius
Database Role V/S Account

Role in Snowflake
1. ICEBERG TABLES
Iceberg tables are brand new table type on Snowflake that uses open format
and customer supplied storage. Over here both metadata and data both are
stored in customer supplied storage. Over here data must be stored in
parquet format and Table Metadata would be stored in “table format”.
There are primarily 3 design criteria which makes iceberg table unique
considering it is powered by “Open Source → Table Format”.
Pic Courtesy → Snowflake
As iceberg table would be using customer supplied storage hence no longer

we have to pay Snowflake the storage cost associated for maintaing and
creating tables.
Iceberg tables are capable of doing most of the activities as a standard table
does i.e., full DML operations, Dynamic data masking, row level security,
etc.. Now this is something which is unique if we try relate it with external Help Status Writers Blog Careers Privacy Terms About
Text to speech
tables on Snowflake which are primarily just “read only”. Personally I really
like a diagram which Snowflake has given that clearly portrays when Iceberg
tables can be used, please refer it as below:
Syntax:
Syntax
Over here you have to give the keyword as “iceberg” to make it as iceberg
table. External volume are needed to define this table which are kind of
similar to external stage syntax i.e.,
Syntax for external volume.
You can also go through one of my blog to know more about Iceberg tables &
table format, link shared as below:
Table Format-> Powering Snowflake with Apache Iceberg

Current Challenges:
medium.com
2. DYNAMIC TABLES
Dynamic Tables are a new table type in Snowflake that lets teams use simple
SQL statements to declaratively define the result of your data pipelines. They
automatically refreshes as the data changes. This was also announced under
the name as “Materialized tables” on SnowSummit at Vegas, but later they
have renamed as “Dynamic Tables”.
Now what is a declarative way of defining the pipelines ? See the snapshot
below:
Dynamic table(Syntax)
If we see this image closely we would understand that it is more of a CTAS

statement by using multiple table as JOINs and materializing the result
inside a dynamic table.
To understand dynamic table we have to try relate it with Task & Stream.
Previously, a data engineer would use Streams and Tasks along with
manually managing the database objects (tables, streams, tasks, SQL DML
code) to build a data pipeline in Snowflake. But with Dynamic Tables, data
pipelines get much easier. Check out this diagram:
What challenges does “dynamic table” solves ?
1. Designed to solve the data engineering challenges for incrementally

processing the data be it streaming OR batch loads.
2. Managing dependencies and scheduling.
3. Cost effective data pipelines.
Dynamic Tables automatically process data incrementally as it changes. All

of the database objects and DML management is automated by Snowflake,
enabling data engineers to easily build resilient and cost effective pipelines.
When to use dynamic tables ?
1. SQL based transformational pipelines.
2. Transformation requires complex SQL, including Joins, Aggregates,

Window Functions, etc.
3. Building a pipeline of transformations vs. aggregations on a single table.
Syntax:
Syntax of dynamic table
More read:
https://www.snowflake.com/blog/dynamic-tables-delivering-declarative-
streaming-data-pipelines/
3. HYBRID TABLES
This was launched around June 2022, there was a major announcement done
in the Snowflake Summit at Vegas i.e., about “UNISTORE workload” that is
going to address the OLTP and OLAP needs. Now “Hybrid Tables” are
designed for this Unistore workload. So whenever you think “Unistore” then
think “Hybrid Tables”
Hybrid tables are a new Snowflake table type powering Unistore. A key
design principle is to have this table support all the transactional capabilities
need. These are highly performant which is a need of any transactional
application & support fast single row operations. They work on entirely new
row-based storage engine. This is unlike other tables in Snowflake where data
is stored in columnar way. Below is the design diagram of it:
The design of Hybrid tables(Pic Courtesy: Snowflake)
What challenges are Hybrid tables going to solve primarily ?
Hybrid tables (unistore workload) is important which is primarily because of

3 reasons i.e.,
1. To go away from too many disparate systems.
2. Data movement challenges for OLTP and OLAP needs.
3. Delayed access
Properties of Hybrid tabes
a. Primary keys are defined & Snowflake enforces the uniqueness of it.
b. Relationships between tables can be defined using referential integrity

constraints, use of foreign key.
c. Secondary Indexes can be defined.
d. Join tables, Hybrid tables can be joined with standard tables in Snowflake.
Syntax:
The Syntax
Do check the ones marked in yellow i.e., keyword “HYBRID” for distinction
on table type, “CONSTRAINT, INDEXES” that would be supported and
imposed by Snowflake once it is defined along with this table.
4. EVENT TABLES
This is launched very recently and below is what I know for now(limited
information).
Event tables makes logging in Snowflake much easier. This enables us to do

logging around Snowflake stored procedures and in future this capability
would also be used to enhance in Snowpark, Python, etc..
Below screenshot defines on how to create and use an “event table”.
Pic courtesy → Snowflake
SUMMARY :
All the 4 new table type which we discussed in this blog are in PrPr and is not
yet GA. Having some initial understanding of each of this category of table
and knowing in which use case what kind of table fits in is always helpful.
Below diagram would help in getting some clarifications on the feature to
use case details.
Use case to Table type mapping
THANK YOU !!!!

Disclaimer: The views expressed here are mine alone and do not necessarily reflect
the view of my current, former, or future employers.
Data Superhero Snowflake
About Help Terms Privacy

4 New Table Types in 2022 by Snowflake - A Summary - by Somen Swain - Snowflake - Dec, 2022 - Medium

Uploaded by

Copyright:

Available Formats

4 New Table Types in 2022 by Snowflake - A Summary - by Somen Swain - Snowflake - Dec, 2022 - Medium

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

4 New Table Types in 2022 by Snowflake - A Summary - by Somen Swain - Snowflake - Dec, 2022 - Medium

Uploaded by

Copyright:

Available Formats

Published in Snowflake

Snowflake New Table Types

4 new table types in 2022 by Snowflake — A

2. Dynamic Tables. Rajiv Gupta in Dev Genius

When To Use Which Table In

Database Role V/S Account

As iceberg table would be using customer supplied storage hence no longer

Pic Courtesy → Snowflake

Table Format-> Powering Snowflake with Apache Iceberg

If we see this image closely we would understand that it is more of a CTAS

What challenges does “dynamic table” solves ?

1. Designed to solve the data engineering challenges for incrementally

2. Managing dependencies and scheduling.

3. Cost effective data pipelines.

Dynamic Tables automatically process data incrementally as it changes. All

When to use dynamic tables ?

1. SQL based transformational pipelines.

2. Transformation requires complex SQL, including Joins, Aggregates,

3. Building a pipeline of transformations vs. aggregations on a single table.

Syntax of dynamic table

The design of Hybrid tables(Pic Courtesy: Snowflake)

What challenges are Hybrid tables going to solve primarily ?

Hybrid tables (unistore workload) is important which is primarily because of

1. To go away from too many disparate systems.

2. Data movement challenges for OLTP and OLAP needs.

Properties of Hybrid tabes

b. Relationships between tables can be defined using referential integrity

c. Secondary Indexes can be defined.

Event tables makes logging in Snowflake much easier. This enables us to do

Below screenshot defines on how to create and use an “event table”.

Pic courtesy → Snowflake

THANK YOU !!!!

Data Superhero Snowflake

About Help Terms Privacy

You might also like