page30
10
Manual Schema Conversion
Depending upon your requirements and perceived application code complexity,
it is possible to convert schema objects to Snowflake syntax relatively easily. One
successfully used approach involves the use of the shell scripts
awk
and
sed
to refactor
Data Definition Language to Snowflake syntax. Note this approach does not address
performance tuning concerns but does provide a baseline from which to start.
Manual schema migrations are relatively straightforward; however, there are some
caveats.
Identifying source character sets can be challenging. Sometimes character set
corruption has occurred before data was ingested within the application to be ported;
therefore, reconciliation when converted to the Snowflake default UTF-8 character set is
impossible.
User-defined types must be reconciled back to their base data types, which in
most scenarios will be the supertype rather than subtype. For example, declare
FLOAT
,
DECIMAL
,
MONEY
,
NUMBER
with or without precision, etc.
Some objects do not lend themselves to direct conversion; for example, this
nonexhaustive list of Oracle to Snowflake migration challenges will require remediation:
•
Snowflake does not support
ROWID
.
•
Within tree walks, Snowflake does not explicitly support
LEVEL
.
•
Complex materialized views are not directly supported; dynamic
tables are an equivalent, but at the time of writing this feature is not
generally available.
•
Snowflake
NULL
treatment is ANSI compliant; Oracle
NULL
treatment is not.
•
Embedded documents are often encoded, encrypted, or compressed
using proprietary algorithms.
•
Snowflake doesn’t have synonyms and relies upon
search_path
.
Likewise, SQLServer to Snowflake migration challenges may be found by doing the
following:
•
Resolving user-defined types and platform-specific data types to their
equivalent Snowflake supertypes
•
Using SQL Server syntax that diverges from the ANSI standard
ChaptEr 1
tuning thE SnowFlakE Data ClouD