MSC-BQ0005

Micro-partitioning is automatically performed on all Snowflake tables.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

This warning is added to the Create table when the partition by clause is present. PARTITION BY is an optional clause that controls table partitioning but is not supported in Snowflake.

All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage. Each micro-partition contains between 50 MB and 500 MB of uncompressed data. This size and structure allows for extremely granular pruning of very large tables, which can be comprised of millions, or even hundreds of millions, of micro-partitions.

Snowflake stores metadata about all rows stored in a micro-partition, including:

• The range of values for each of the columns in the micro-partition.

• The number of distinct values.

• Additional properties used for both optimization and efficient query processing.

Also the tables are transparently partitioned using the ordering of the data as it is inserted/loaded. For more information please refer to Benefits of Micro-partitioning.

Code Example

Input Code:

BigQuery

CREATE TABLE table1(
    transaction_id INT, 
    transaction_date DATE
)
PARTITION BY transaction_date;

Output Code:

Snowflake

CREATE TABLE table1(
    transaction_id INT, 
    transaction_date DATE
)
----** MSC-WARNING - MSC-BQ0005 - MICRO-PARTITIONING IS AUTOMATICALLY PERFORMED ON ALL SNOWFLAKE TABLES. **
--PARTITION BY transaction_date
;

Recommendations

• No additional user actions are required, it is just informative.

• If you need more support, you can email us at [email protected]