page270
358
Some additional considerations also apply; note that the following list is incomplete:
•
Oracle incorrectly implements
NULL
logic according to the ANSI
standard, whereas Snowflake implements
NULL
correctly.
•
Never use
SELECT *
in Snowflake; always declare every attribute even
if all attributes from the table are used in the wider query.
•
Snowflake does not support physical table partitioning, though
a similar effect can be achieved with parallelizing operations, as
described in Chapter 8.
•
Not all legacy RDBMS implement ACID transactions by default.
For some, the isolation level must be set to block writes to prevent
dirty reads.
In Chapter 1 I discussed migration guides and listed several common legacy RDBMS
platform guides. I also noted the availability of SnowConvert and detailed alternative
options. You can find more information on migration kits at
https://www.snowflake.
com/migrate-to-the-cloud/
.
Logging
All applications require process metadata to be stored to trace the inevitable feed
ingestion issues arising during the course of day-to-day operation.
A single logging table effectively serializes all concurrent parallel processing due
to the immutable nature of micro-partitions and locking operation where constant
micro-partition churn occurs. My recommendation is to use an Event table as described
previously, which removes the serialization issue. Note that a latency of a few minutes is
commonly experienced between event creation and event observability.
The forthcoming Unistore workload uniting transactional and analytical data using
hybrid tables may provide a single logging table capability without blocking parallel
transactions. We have not proven Unistore workloads in this book, but I bring this to
your attention for future reference. You can find more details on Unistore at
https://
www.snowflake.com/en/data-cloud/workloads/unistore/
.
ChapTer 10
OpTimizing perfOrmanCe