CTAS (Create Table As Select) Patterns
The CTAS (CREATE TABLE AS SELECT) statement is one of the most fundamental and powerful commands in Snowflake's ELT toolkit. It allows you to create a new table and populate it with data from a SELECT query in a single, atomic operation.
This module explores how to use CTAS for data transformation, optimization, and table recreation.
Topics Covered
- Basic CTAS Syntax
- CTAS for Data Transformation
- Advanced CTAS (Clustering and Cloning)
- Performance Considerations
Basic CTAS Syntax
The core syntax combines a DDL (Data Definition Language) statement with a DML (Data Manipulation Language) statement.
-- Conceptual Syntax
CREATE [OR REPLACE] TABLE new_table_name AS
SELECT column1, column2
FROM source_table
WHERE condition = true;
By default, Snowflake infers the data types of the new table based on the expressions in the SELECT clause.
Hands-On Demonstration: CTAS for Data Transformation
Let's assume we have a raw staging table containing messy retail data for stores across India. We want to clean this data and immediately save the cleaned version as a new table for analysts to use.
1. Database & Raw Data Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_ELT_DB;
USE DATABASE BEEMS_ELT_DB;
CREATE SCHEMA IF NOT EXISTS CTAS_LAB;
USE SCHEMA CTAS_LAB;
-- Create Raw Table
CREATE OR REPLACE TABLE RAW_STORE_DATA (
RAW_JSON VARIANT
);
-- Insert messy JSON data
INSERT INTO RAW_STORE_DATA (RAW_JSON)
SELECT PARSE_JSON(column1) FROM VALUES
('{"store_id": "101", "city": "mumbai", "revenue": "4500.50", "is_active": "true"}'),
('{"store_id": "102", "city": "DELHI", "revenue": "null", "is_active": "false"}'),
('{"store_id": "103", "city": "chennai", "revenue": "12000.00", "is_active": "true"}');
2. The CTAS Transformation
Instead of creating a target table structure manually and then running an INSERT statement, we do it all at once with CTAS. We will parse the JSON, cast data types, and standardize strings.
(To verify your active role before creating tables, run SELECT CURRENT_ROLE();)
CREATE OR REPLACE TABLE CLEAN_STORE_DATA AS
SELECT
RAW_JSON:store_id::INT AS STORE_ID,
INITCAP(RAW_JSON:city::STRING) AS CITY_NAME,
COALESCE(RAW_JSON:revenue::NUMBER(10,2), 0) AS REVENUE_INR,
RAW_JSON:is_active::BOOLEAN AS IS_ACTIVE
FROM RAW_STORE_DATA
WHERE RAW_JSON:is_active::BOOLEAN = TRUE;
-- Verify the result
SELECT * FROM CLEAN_STORE_DATA;
Notice that Delhi was filtered out (inactive), and the cities are properly capitalized.
Advanced CTAS Patterns
1. CTAS with Clustering Keys
If the table you are creating is massive (billions of rows), you might want to establish a clustering key right at creation. You can append clustering logic before the AS keyword.
-- Create a new table and cluster it by CITY_NAME
CREATE OR REPLACE TABLE CLUSTERED_STORE_DATA
CLUSTER BY (CITY_NAME)
AS
SELECT * FROM CLEAN_STORE_DATA;
2. Table Recreation (The "Deep Copy" Pattern)
Over time, a heavily updated table in Snowflake might suffer from fragmented micro-partitions (even if auto-clustering is on). Sometimes, the fastest way to "defragment" and optimize a massive table is to do a deep copy using CTAS, completely rewriting the micro-partitions.
-- Recreate a massive table to optimize storage
CREATE OR REPLACE TABLE MASSIVE_FACT_TABLE_OPTIMIZED AS
SELECT * FROM MASSIVE_FACT_TABLE
ORDER BY DATE_COLUMN; -- Optional: forcefully sort data during creation
-- Swap the names
ALTER TABLE MASSIVE_FACT_TABLE RENAME TO MASSIVE_FACT_TABLE_OLD;
ALTER TABLE MASSIVE_FACT_TABLE_OPTIMIZED RENAME TO MASSIVE_FACT_TABLE;
Performance Considerations
While CTAS is highly optimized, there are a few things to keep in mind:
- Warehouse Sizing: CTAS requires compute. If you are running a CTAS on 5 Terabytes of data, do not use an
X-Smallwarehouse. Scale up to anX-Largebefore running the command, and scale back down after. - Metadata vs Compute: If you only want to copy the structure of a table without moving any data, do not use a CTAS with
WHERE 1=0. It still requires compute to parse. Instead, useCREATE TABLE new_table LIKE old_table;which is a pure metadata operation. - Cloning: If you want an exact, instant copy of a table for testing, do not use CTAS. Use
CREATE TABLE new_table CLONE old_table;. Cloning is a zero-copy metadata operation that takes seconds and consumes no extra storage until changes are made.
Summary
- CTAS combines table creation and data insertion into a single statement.
- It is the standard way to materialize heavy ELT transformations (e.g., parsing raw JSON into a structured table).
- You can add Clustering Keys directly in the CTAS statement.
- Do not use CTAS if you just want to copy a table for testing; use Zero-Copy Cloning instead.