🧱Create Table As Select

NEW

Also known as: CTAS

An SQL pattern that creates a new table populated by the results of a SELECT query.

DESCRIPTION

CREATE TABLE AS SELECT (CTAS) is a common SQL operation to materialize query results into a new table. It’s used for staging, snapshotting, and transforming data in ELT workflows. CTAS often improves performance by persisting intermediate results for reuse. Behavior varies by system regarding constraints, indexes, and transaction semantics.

ALTERNATIVE DEFINITIONS

PostgreSQL Documentation

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command.

Concise, canonical definition of CTAS semantics in PostgreSQL.

Source

Snowflake

CREATE TABLE AS SELECT creates a new table from the results of a query.

Vendor-specific articulation of CTAS usage in cloud data warehouse context.

Source

RELATED TERMS

Materialized View Temporary Table ELT Data Warehouse SQL

COMPETING TERMS

INSERT INTO SELECT CREATE VIEW

KNOWLEDGE AREAS

Programming Languages Data Engineering Data Storage & Operations

TARGET AUDIENCE

Data Engineer Analytics Engineer Software Developer Data Architect

EXTERNAL SOURCES

• Wikipedia - Create (SQL)
• Google Cloud BigQuery - CTAS pattern for creating tables from query results
• Oracle - CREATE TABLE including AS subqueries

TOP QUESTIONS

When should I use Create Table As Select?

Use CTAS to materialize intermediate results, create snapshots, or speed repeated queries. It’s ideal in ELT pipelines and data warehousing tasks.

Category: Use Cases

Does CTAS copy indexes and constraints?

Typically no. Most systems create a heap or clustered table without indexes or constraints unless specified separately.

Category: Behavior

How does CTAS differ from CREATE VIEW?

CTAS materializes data at creation time. Views are virtual and compute results at query time, unless materialized.

Category: Comparison

Is CTAS transactional?

Behavior varies by system. In many databases, CTAS runs atomically within a transaction; check your engine’s documentation.

Category: Standards

What are performance considerations for CTAS?

Optimize the SELECT part with proper filters and joins. Use partitioning, clustering, or file formats to align with query patterns.

Category: Performance

Can CTAS overwrite an existing table?

Some systems support CREATE OR REPLACE; others require dropping the table first. Validate semantics in your SQL dialect.

Category: Practice

How does CTAS fit in ELT workflows?

It’s a primary mechanism to persist transformation steps in modern warehouses, enabling idempotent and incremental patterns.

Category: Data Engineering

What’s a safe pattern for CTAS in production?

Write to a new table, validate row counts and checksums, then swap names atomically or via views to minimize disruption.

Category: Best Practices

Is CTAS supported in BigQuery and Snowflake?

Yes. Both support creating tables from query results, with options for partitioning, clustering, and storage optimizations.

Category: Tooling