page269
357
Please refer to the feature list tables and overview of the Snowflake core
platform capabilities by edition found here:
https://docs.snowflake.com/en/
user-guide/intro-editions?utm_source=snowscope&utm_medium=serp&utm_
term=edition#feature-edition-matrix
.
Data Model Approach
Snowflake is “model agnostic.” Third normal form, Data Vault 2.0, and star schema all
work very well. You must understand your data volume, velocity, and variety in order to
decide upon a data modeling approach. Choosing the wrong model for your data profile
can adversely affect performance and increase costs. In general, you know from earlier
investigations conducted in this book that both
UPDATE
and
MERGE
operations are expensive,
whereas
INSERT
and many (but not all)
DELETE
operations are relatively inexpensive.
You must balance your approach with the need to query data. Because reading data is
performed far more often than writing data, tuning
SELECT
statements is always worthwhile.
Where your application requires historized or bi-temporal data, adopting an insert-
only pattern such as Data Vault 2.0 will provide optimal performance throughout the
application life cycle.
Snowflake performs best with high-volume, low-frequency data operations and
is less performant with low-volume, high-frequency data operations. Transactional
workloads should be avoided when not using hybrid tables. Note that any use of hybrid
tables should be limited to low millions of records according to conversation with the
Snowflake Sales Support engineering staff. Mitigation by partitioning workloads as
described earlier in this book can be very effective.
Regardless of the data modeling approach, avoid forcing object and attribute names
to either mixed or lowercase. I prefer most objects and attributes names to remain in
uppercase, preventing objects and attributes from being referenced in double quotes in
SQL statements. Procedure and function names may benefit from mixed-case naming
according to preference.
Platform Differences
A full treatise on migrating from disparate legacy platforms to Snowflake is beyond
the scope of this book. Various guides exist to begin the migration process, though not
all the details are covered. You can find a good starting point at
https://community.
snowflake.com/s/article/So-You-Want-to-Migrate-to-Snowflake-Part-One
.
ChapTer 10
OpTimizing perfOrmanCe