Examining table schemas and deriving an optimal structure for a table

This recipe walks you through analyzing a table's structure in conjunction with the data it contains and provides suggestions on optimizing the table structure.

Getting ready

This recipe uses a public S3 bucket for a sample file that is loaded into an example table to demonstrate the concepts. You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe successfully.

How to do it…

We will create a new table with a not-so-optimal structure and load it with sample data. Later, we will optimize the table and load it with the same data and analyze the two tables' storage differences. The steps for this recipe are as follows:

1. We will start by creating a new database and a table that will hold the sample data:

   CREATE DATABASE C6_R1;
   CREATE TABLE CUSTOMER
   (
     CustomerID VARCHAR(100),
     FName VARCHAR(1024),
    ...