Reviewing query plans to modify table clustering

Snowflake provides the option to configure clustering keys for tables so that larger tables can benefit from partition pruning. This recipe will analyze query plans in conjunction with table structures and identify whether a new clustering key will improve the query performance.

Getting ready

The steps in this recipe can be run either in the Snowflake web UI or the SnowSQL command-line client.

How to do it…

Let's start by creating and populating a table in Snowflake. We will simulate data being inserted into the table regularly, resulting in an increased size on disk and an increased number of partitions. The steps for this recipe are as follows:

1. Create a new database, followed by the creation of a table that will hold the transaction data:

   CREATE DATABASE C6_R6;
   CREATE TABLE TRANSACTIONS
   (
     TXN_ID STRING,
     TXN_DATE DATE,
     CUSTOMER_ID STRING,
     QUANTITY DECIMAL...