snowflake create table as select example

OP Authors 2025

2 min read

·

16-03-2025

23

0

Share

Snowflake CREATE TABLE AS SELECT (CTAS) Examples: A Deep Dive

Snowflake's CREATE TABLE AS SELECT (CTAS) statement is a powerful tool for creating new tables by selecting data from existing ones. It's efficient, flexible, and a cornerstone of data warehousing and ETL processes. This article will explore various CTAS examples, demonstrating its versatility and highlighting best practices.

Basic CTAS:

The simplest form creates a new table with the same schema as the source table:

CREATE OR REPLACE TABLE new_table AS
SELECT *
FROM existing_table;

This copies all columns and data from existing_table into new_table. Note the OR REPLACE clause; this will replace new_table if it already exists. Omitting it will result in an error if the table already exists.

Selecting Specific Columns:

Often, you only need a subset of columns from the source table. CTAS allows for precise selection:

CREATE OR REPLACE TABLE customer_summary AS
SELECT customer_id, order_total, order_date
FROM orders;

This creates customer_summary containing only customer_id, order_total, and order_date from the orders table.

Data Transformation with CTAS:

CTAS isn't limited to simple data copying. You can incorporate data transformations within the SELECT statement:

CREATE OR REPLACE TABLE sales_with_tax AS
SELECT 
    product_id,
    quantity,
    price,
    price * quantity * (1 + tax_rate) AS total_price_with_tax
FROM sales;

Here, total_price_with_tax is calculated on the fly and added as a new column to sales_with_tax.

Filtering Data with WHERE Clause:

You can filter the data selected using a WHERE clause:

CREATE OR REPLACE TABLE high_value_customers AS
SELECT *
FROM customers
WHERE total_spent > 10000;

This creates a table containing only customers who have spent more than $10,000.

Using Aggregations with CTAS:

CTAS works seamlessly with aggregate functions, enabling the creation of summary tables:

CREATE OR REPLACE TABLE monthly_sales AS
SELECT
    MONTH(order_date) AS sales_month,
    SUM(order_total) AS total_monthly_sales
FROM orders
GROUP BY sales_month
ORDER BY sales_month;

This creates a table summarizing monthly sales totals. Note the use of MONTH() for extracting the month and SUM() for aggregation.

Clustering and Partitioning:

For optimal performance, consider adding clustering and partitioning keys during CTAS:

CREATE OR REPLACE TABLE partitioned_sales_data
  CLUSTER BY product_id
  PARTITION BY (YEAR(order_date))
AS
SELECT *
FROM sales;

This creates a partitioned and clustered table, improving query performance significantly. The choice of clustering and partitioning keys depends heavily on your query patterns.

Best Practices:

• Clearly name your tables: Choose descriptive names that reflect the table's content.
• Use OR REPLACE cautiously: Understand the implications of overwriting existing tables.
• Optimize your SELECT statement: Use appropriate filters and aggregations to reduce data volume.
• Consider clustering and partitioning: These features significantly enhance query performance.
• Monitor performance: After creating a large table using CTAS, check the execution time and resource usage.

By mastering CTAS, you gain a powerful tool for managing and transforming data within Snowflake, facilitating efficient data warehousing and ETL processes. Remember to adapt these examples to your specific needs and data schema for optimal results.