SQL CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS for short) runs a query and stores its rows in a new table. The table is ordinary from then on: query it, join it, index it, drop it. Nothing links it back to the query that filled it.

That last sentence is the whole point. A view re-runs its query every time and always matches the current data. A CTAS table is a photograph: it shows what the query returned at the moment you ran it, and it keeps showing that until you rebuild it.

create table category_revenue as
select p.category_id,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
group by p.category_id

You do not declare column names or types. CTAS takes both from the query, so category_id arrives as BIGINT and revenue as DOUBLE without you saying so. Check with DESCRIBE category_revenue when the types matter.

Rebuilding it

A snapshot is only useful if refreshing it is easy. Two spellings, and they do different things.

create or replace table category_revenue as
select p.category_id,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
group by p.category_id

insert into category_revenue
select p.category_id,
       sum(oi.quantity * oi.unit_price) as revenue
from order_items oi
join products p on p.product_id = oi.product_id
where p.discontinued = true
group by p.category_id

CREATE OR REPLACE TABLE throws away the old rows and the old shape, so it is the safe choice when the query changed. INSERT INTO keeps the existing rows and appends, which is what you want for an accumulating log and exactly what you do not want when you meant to refresh: run it twice and every row is in there twice.

What CTAS does not carry over

CTAS copies rows and column types. It does not copy constraints. Snapshot a table with a primary key and the copy has no primary key, no NOT NULL, no defaults. DuckDB accepts a duplicate key into the copy that the original would have rejected.

create or replace table product_keys (
  product_id integer primary key,
  product_name varchar not null
);

create or replace table product_keys_copy as
select * from product_keys;

select table_name, constraint_type
from duckdb_constraints()
where table_name in ('product_keys', 'product_keys_copy')
order by table_name, constraint_type;

Only product_keys comes back. The copy has the same two columns holding the same values, and none of the rules.

This is usually fine, because a reporting snapshot does not need to enforce anything. It stops being fine when someone treats the copy as the real table and starts writing to it. If the copy needs the rules, declare the table first and INSERT INTO it.

Snapshot vs view, side by side

When to reach for it

• A report that must not move. Month-end figures should be the same tomorrow as they were when you signed off on them. A view would restate them as late data arrives.
• An expensive step you read many times. A view re-runs its query on every reference; a snapshot pays once.
• Staging a load. Land the raw rows, clean them into a second table, and check each stage separately.
• Freezing a "before" picture. Snapshot a table before a bulk UPDATE, so you can compare or restore afterwards.

And when not to: a dashboard that needs current numbers wants a view. A snapshot there is a bug that looks like working software, because the query still runs and still returns rows, only old ones.

Useful variants

create table orders_backup as
select *
from orders
where 1 = 0

A WHERE that matches nothing still creates the table with the right columns and types, giving you an empty table shaped like the original to insert into.

create or replace view customer_revenue as
select o.customer_id,
       sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id;

create or replace table customer_revenue_snapshot as
select *
from customer_revenue;

Materializing a view is CTAS over it. That is also DuckDB's answer to the materialized-view question, below.

Habits worth keeping

• Name snapshots so nobody mistakes them for live tables: daily_revenue_snapshot, not daily_revenue_2.
• Write the rebuild as CREATE OR REPLACE TABLE so rerunning the script is safe.
• Alias every computed column before it becomes a stored column name.
• Store the "as of" moment in the table itself. A strftime(now(), '%Y-%m-%d') as snapshot_date column costs nothing and answers the only question anyone asks about a snapshot.
• If the snapshot needs constraints, create the table explicitly and insert into it. CTAS will not give you them.