Welcome To TechBrothersIT: Snowflake SQL Tutorial 101 - CTAS vs LIKE vs CLONE

Snowflake SQL Tutorial 101 - CTAS vs LIKE vs CLONE - What a CTAS Silently Throws Away

Snowflake SQL · Episode 101

In this post we are going to cover the three ways to make a table out of another table in Snowflake: CREATE TABLE AS SELECT, CREATE TABLE LIKE and CREATE TABLE CLONE.

They look like three spellings of the same idea and they are not. They copy three different things. One copies the rows, one copies the definition, and one copies both.

We will build a single table that makes three promises beyond its values — it refuses a missing id, it fills in two columns for you, and it documents one of them — and then we will chase those three promises through each copy and see which of them survive. Then we will find out whether a clone is really independent of the table it came from.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 101 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create a table worth copying badly
Let's copy it with CREATE TABLE AS SELECT
So what did the CTAS copy keep?
What does losing NOT NULL actually cost us?
The one thing a CTAS can do that the others cannot
Now let's copy it with CREATE TABLE LIKE
Do those copied defaults actually fire?
Now let's copy it with CLONE
Let's count all four tables side by side
Is a clone really independent?
Can we clone a clone?
What happens when a CTAS selects no rows at all?
One last thing: the comment that did not travel
So which one do you reach for?

Let's create a table worth copying badly

We want a table that is deliberately opinionated, so there is something to lose. Four columns. The id is NOT NULL. The name defaults to unnamed and carries a comment. The amount defaults to zero. The created date is a plain column with nothing asked of it.

Sample data

-- Sample data for this post.
-- One deliberately opinionated table: a required column, two defaults,
-- a column comment and a table comment. Then four rows.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SOURCE_T101 (
    ID      INT          NOT NULL,
    NAME    VARCHAR(50)  DEFAULT 'unnamed' COMMENT 'the display name',
    AMOUNT  NUMBER(12,2) DEFAULT 0.00,
    CREATED DATE
) COMMENT = 'the table the other three are made from';

INSERT INTO SOURCE_T101 (ID, NAME, AMOUNT, CREATED)
SELECT 1, 'north', 100.00, '2026-01-01'::DATE UNION ALL
SELECT 2, 'south', 250.50, '2026-01-02'::DATE UNION ALL
SELECT 3, 'east',  999.99, '2026-01-03'::DATE UNION ALL
SELECT 4, 'west',   10.25, '2026-01-04'::DATE;

Query

CREATE OR REPLACE TABLE SOURCE_T101 (
    ID       INT            NOT NULL,
    NAME     VARCHAR(50)    DEFAULT 'unnamed' COMMENT 'the display name',
    AMOUNT   NUMBER(12,2)   DEFAULT 0.00,
    CREATED  DATE
)
COMMENT = 'the table the other three are made from';

Result1 row · 1 column

status
Table SOURCE_T101 successfully created.

Query

INSERT INTO SOURCE_T101 (ID, NAME, AMOUNT, CREATED)
SELECT 1, 'north', 100.00, '2026-01-01'::DATE
UNION ALL SELECT 2, 'south',  250.50, '2026-01-02'::DATE
UNION ALL SELECT 3, 'east',   999.99, '2026-01-03'::DATE
UNION ALL SELECT 4, 'west',    10.25, '2026-01-04'::DATE;

Result1 row · 1 column

number of rows inserted
4

Four rows in it. Let's look at them.

Query

SELECT ID, NAME, AMOUNT, CREATED FROM SOURCE_T101 ORDER BY ID;

Result4 rows · 4 columns

ID	NAME	AMOUNT	CREATED
1	north	100.00	2026-01-01
2	south	250.50	2026-01-02
3	east	999.99	2026-01-03
4	west	10.25	2026-01-04

North, south, east and west. Now the part we are actually going to chase through three copies — the definition.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'SOURCE_T101'
ORDER BY ORDINAL_POSITION;

Result4 rows · 5 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT	COMMENT
ID	NUMBER	NO	NULL	NULL
NAME	TEXT	YES	'unnamed'	the display name
AMOUNT	NUMBER	YES	0	NULL
CREATED	DATE	YES	NULL	NULL

There are the three promises. ID has IS_NULLABLE set to NO, which is the NOT NULL. NAME has a default of 'unnamed' and a comment reading the display name. AMOUNT has a default too.

One small thing to notice while we are here. We wrote DEFAULT 0.00 and the catalog records the default as 0. Snowflake stored the expression we gave it, tidied up. The value it puts in a row is still shaped by the column, and we will see that happen later in the post.

So: it refuses a null id, it fills in two columns, and it documents one. Watch which of those survive each copy. That is the whole post.

Let's copy it with CREATE TABLE AS SELECT

CREATE TABLE ... AS SELECT, which everybody calls a CTAS, runs a query and builds a table out of the result. Let's select all four columns from the source and nothing else.

Query

CREATE OR REPLACE TABLE CTAS_T101 AS
SELECT ID, NAME, AMOUNT, CREATED FROM SOURCE_T101;

Result1 row · 1 column

status
Table CTAS_T101 successfully created.

Let's check the data came across.

Query

SELECT COUNT(*) AS ROWS_COPIED, SUM(AMOUNT) AS TOTAL_COPIED FROM CTAS_T101;

Result1 row · 2 columns

ROWS_COPIED	TOTAL_COPIED
4	1360.74

Four rows, and the amounts total 1360.74, which is what the four values in the grid above come to. The data is there, all of it. Now the definition.

So what did the CTAS copy keep?

Same question we asked of the source. Same view, same columns.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'CTAS_T101'
ORDER BY ORDINAL_POSITION;

Result4 rows · 5 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT	COMMENT
ID	NUMBER	YES	NULL	NULL
NAME	TEXT	YES	NULL	NULL
AMOUNT	NUMBER	YES	NULL	NULL
CREATED	DATE	YES	NULL	NULL

IS_NULLABLE says YES on every row. COLUMN_DEFAULT is NULL on every row. COMMENT is NULL on every row.

All three promises are gone. Let's put the two definitions side by side so there is no arguing with it.

Query

SELECT S.COLUMN_NAME,
       S.IS_NULLABLE    AS SOURCE_NULLABLE,
       C.IS_NULLABLE    AS CTAS_NULLABLE,
       S.COLUMN_DEFAULT AS SOURCE_DEFAULT,
       C.COLUMN_DEFAULT AS CTAS_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS S
JOIN INFORMATION_SCHEMA.COLUMNS C
  ON C.TABLE_NAME = 'CTAS_T101' AND C.COLUMN_NAME = S.COLUMN_NAME
WHERE S.TABLE_SCHEMA = 'PUBLIC' AND S.TABLE_NAME = 'SOURCE_T101'
ORDER BY S.ORDINAL_POSITION;

Result4 rows · 5 columns

COLUMN_NAME	SOURCE_NULLABLE	CTAS_NULLABLE	SOURCE_DEFAULT	CTAS_DEFAULT
ID	NO	YES	NULL	NULL
NAME	YES	YES	'unnamed'	NULL
AMOUNT	YES	YES	0	NULL
CREATED	YES	YES	NULL	NULL

ID was NO in the source and is YES in the copy. NAME had a default of 'unnamed' and now has none. AMOUNT had a default and now has none.

This is not a bug and it is not Snowflake being careless. A SELECT returns values. It does not return a definition. There is nowhere for NOT NULL to travel in a result set, so it does not travel.

The column types did come across, because the values have types. Everything you asked for about those values did not.

What does losing NOT NULL actually cost us?

Let's find out, rather than talk about it. Here is a row with no id at all, going into the CTAS copy.

Query

INSERT INTO CTAS_T101 (ID, NAME, AMOUNT, CREATED)
SELECT NULL, 'this should not fit', 1.00, '2026-02-01'::DATE;

Result1 row · 1 column

number of rows inserted
1

One row inserted. It took it. Now the identical row into the original.

Query

INSERT INTO SOURCE_T101 (ID, NAME, AMOUNT, CREATED)
SELECT NULL, 'this should not fit', 1.00, '2026-02-01'::DATE;

Query failed — Snowflake returned an error

100072 (22000): DML operation to table SOURCE_T101 failed on column ID with error: NULL result in a non-nullable column

Refused. NULL result in a non-nullable column. Let's count the damage.

Query

SELECT (SELECT COUNT(*) FROM CTAS_T101 WHERE ID IS NULL) AS NULLS_IN_THE_COPY,
       (SELECT COUNT(*) FROM SOURCE_T101 WHERE ID IS NULL) AS NULLS_IN_THE_ORIGINAL;

Result1 row · 2 columns

NULLS_IN_THE_COPY	NULLS_IN_THE_ORIGINAL
1	0

This is what a CTAS costs you

Two tables that look identical in a SELECT, with the same column names and the same types and the same values, now disagree about whether a row is legal. One of them holds a row the other one would have refused. Nothing in a result grid tells you which table you are looking at, which is what makes this the expensive kind of mistake.

The one thing a CTAS can do that the others cannot

A CTAS runs a query, so it can do everything a query can do. It can rename columns, calculate new ones and filter rows on the way through. LIKE and CLONE cannot do any of that.

Let's rename the id, upper-case the name, add a markup to the amount, and keep only the larger amounts.

Query

CREATE OR REPLACE TABLE CTAS_SHAPED_T101 AS
SELECT ID                      AS ORDER_ID,
       UPPER(NAME)             AS REGION,
       AMOUNT * 1.1            AS AMOUNT_WITH_MARKUP
FROM SOURCE_T101
WHERE AMOUNT > 50;

Result1 row · 1 column

status
Table CTAS_SHAPED_T101 successfully created.

Now let's see what shape Snowflake inferred for those columns.

Query

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'CTAS_SHAPED_T101'
ORDER BY ORDINAL_POSITION;

Result3 rows · 4 columns

COLUMN_NAME	DATA_TYPE	NUMERIC_PRECISION	NUMERIC_SCALE
ORDER_ID	NUMBER	38	0
REGION	TEXT	NULL	NULL
AMOUNT_WITH_MARKUP	NUMBER	14	3

Look at AMOUNT_WITH_MARKUP. The source column is NUMBER(12,2) and we multiplied it by 1.1. The new column came out as NUMBER(14,3). Snowflake widened both the precision and the scale to make room for the expression, and it did not ask us.

And the rows.

Query

SELECT ORDER_ID, REGION, AMOUNT_WITH_MARKUP
FROM CTAS_SHAPED_T101 ORDER BY ORDER_ID;

Result3 rows · 3 columns

ORDER_ID	REGION	AMOUNT_WITH_MARKUP
1	NORTH	110.000
2	SOUTH	275.550
3	EAST	1099.989

Three rows, not four. The WHERE clause in that statement kept only the amounts above fifty, so the west row at 10.25 did not make it. The names are in capitals and the amounts carry the extra decimal place the wider column gave them.

This is what a CTAS is for. A new shape, built out of an old one. It is not for making a copy of a table you want to keep behaving like the original.

Now let's copy it with CREATE TABLE LIKE

One word different from the CTAS, and a completely different result.

Query

CREATE OR REPLACE TABLE LIKE_T101 LIKE SOURCE_T101;

Result1 row · 1 column

status
Table LIKE_T101 successfully created.

First question: how many rows did it bring?

Query

SELECT COUNT(*) AS ROWS_IN_THE_LIKE_COPY FROM LIKE_T101;

Result1 row · 1 column

ROWS_IN_THE_LIKE_COPY
0

None at all. Not one. Now the definition, which is the interesting half.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'LIKE_T101'
ORDER BY ORDINAL_POSITION;

Result4 rows · 5 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT	COMMENT
ID	NUMBER	NO	NULL	NULL
NAME	TEXT	YES	'unnamed'	the display name
AMOUNT	NUMBER	YES	0	NULL
CREATED	DATE	YES	NULL	NULL

ID is back to NO. The NOT NULL survived. NAME has its 'unnamed' default and its comment. AMOUNT has its default. All three promises are here.

So LIKE is the mirror image of a CTAS. The definition is kept and the rows are gone.

Do those copied defaults actually fire?

The catalog recording a default and the default working are two different claims, and we have only checked the first one. Let's insert a row that mentions nothing but the id.

Query

INSERT INTO LIKE_T101 (ID) SELECT 99;

Result1 row · 1 column

number of rows inserted
1

Query

SELECT ID, NAME, AMOUNT, CREATED FROM LIKE_T101 ORDER BY ID;

Result1 row · 4 columns

ID	NAME	AMOUNT	CREATED
99	unnamed	0.00	NULL

NAME is unnamed and AMOUNT is 0.00. The defaults fired in the copy exactly as they would have in the original. CREATED is NULL, because that column never had a default to fire.

And there is the amount default we noticed earlier. The catalog records it as 0 and the value that landed in the row prints as 0.00, because the column is NUMBER(12,2). The default is the expression; the column decides the shape.

Now the other promise. A row with a null id, into the LIKE copy.

Query

INSERT INTO LIKE_T101 (ID, NAME) SELECT NULL, 'no id';

Query failed — Snowflake returned an error

100072 (22000): DML operation to table LIKE_T101 failed on column ID with error: NULL result in a non-nullable column

Refused, with the same error the original gave us. So LIKE kept the promise the CTAS lost. That is the choice between those two statements, and it is not a close one if what you wanted was a copy.

Now let's copy it with CLONE

The third one. CREATE TABLE ... CLONE.

Query

CREATE OR REPLACE TABLE CLONE_T101 CLONE SOURCE_T101;

Result1 row · 1 column

status
Table CLONE_T101 successfully created.

Rows first.

Query

SELECT COUNT(*) AS ROWS_IN_THE_CLONE, SUM(AMOUNT) AS TOTAL_IN_THE_CLONE
FROM CLONE_T101;

Result1 row · 2 columns

ROWS_IN_THE_CLONE	TOTAL_IN_THE_CLONE
4	1360.74

Four rows, and the same 1360.74 the CTAS copy reported. Now the definition.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'CLONE_T101'
ORDER BY ORDINAL_POSITION;

Result4 rows · 5 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT	COMMENT
ID	NUMBER	NO	NULL	NULL
NAME	TEXT	YES	'unnamed'	the display name
AMOUNT	NUMBER	YES	0	NULL
CREATED	DATE	YES	NULL	NULL

The NOT NULL on the id. Both defaults. The comment on NAME. Everything. Put that grid beside the source definition at the top of this post and they are the same grid.

So a clone keeps the values and the definition. It is the faithful copy of the three.

Let's count all four tables side by side

Four tables now, so let's count them in one query.

Query

SELECT 'SOURCE' AS WHICH_TABLE, COUNT(*) AS ROWS_PRESENT FROM SOURCE_T101
UNION ALL SELECT 'CTAS',  COUNT(*) FROM CTAS_T101
UNION ALL SELECT 'LIKE',  COUNT(*) FROM LIKE_T101
UNION ALL SELECT 'CLONE', COUNT(*) FROM CLONE_T101
ORDER BY WHICH_TABLE;

Result4 rows · 2 columns

WHICH_TABLE	ROWS_PRESENT
CLONE	4
CTAS	5
LIKE	1
SOURCE	4

The source and the clone agree. The CTAS copy is one ahead of them, because of the null id row we pushed into it. And the LIKE copy, which arrived with no rows at all, holds the single row we inserted a moment ago to test the default.

Every one of those numbers is something we did on purpose, and between them they say the whole thing: rows only, definition only, or both.

Statement	Copies the rows	Copies the definition
`CREATE TABLE ... AS SELECT`	Yes	No
`CREATE TABLE ... LIKE`	No	Yes
`CREATE TABLE ... CLONE`	Yes	Yes

Is a clone really independent?

This is the one people get wrong. Snowflake calls this feature zero-copy cloning, and zero-copy describes the storage. It does not describe the behaviour. Those are two different claims and only one of them is being made.

So let's write to the clone only.

Query

INSERT INTO CLONE_T101 (ID, NAME, AMOUNT, CREATED)
SELECT 5, 'added to the clone only', 500.00, '2026-03-01'::DATE;

Result1 row · 1 column

number of rows inserted
1

Query

SELECT (SELECT COUNT(*) FROM CLONE_T101)  AS CLONE_ROWS,
       (SELECT COUNT(*) FROM SOURCE_T101) AS SOURCE_ROWS;

Result1 row · 2 columns

CLONE_ROWS	SOURCE_ROWS
5	4

The clone went up and the source did not move. Now the other direction — a different row, into the source only.

Query

INSERT INTO SOURCE_T101 (ID, NAME, AMOUNT, CREATED)
SELECT 6, 'added to the source only', 600.00, '2026-03-02'::DATE;

Result1 row · 1 column

number of rows inserted
1

Query

SELECT (SELECT COUNT(*) FROM CLONE_T101)  AS CLONE_ROWS,
       (SELECT COUNT(*) FROM SOURCE_T101) AS SOURCE_ROWS,
       (SELECT MAX(ID) FROM CLONE_T101)   AS HIGHEST_ID_IN_CLONE,
       (SELECT MAX(ID) FROM SOURCE_T101)  AS HIGHEST_ID_IN_SOURCE;

Result1 row · 4 columns

CLONE_ROWS	SOURCE_ROWS	HIGHEST_ID_IN_CLONE	HIGHEST_ID_IN_SOURCE
5	5	5	6

Read that grid carefully, because it is the best proof on this page. The two row counts are now equal. And the highest id in the clone is not the highest id in the source.

Equal counts, different rows. If the two tables were sharing anything that mattered, that could not happen.

One more, in the other direction again. Let's delete from the clone.

Query

DELETE FROM CLONE_T101 WHERE ID <= 2;

Result1 row · 1 column

number of rows deleted
2

Query

SELECT (SELECT COUNT(*) FROM CLONE_T101)  AS CLONE_ROWS,
       (SELECT COUNT(*) FROM SOURCE_T101) AS SOURCE_ROWS,
       (SELECT SUM(AMOUNT) FROM CLONE_T101)  AS CLONE_TOTAL,
       (SELECT SUM(AMOUNT) FROM SOURCE_T101) AS SOURCE_TOTAL;

Result1 row · 4 columns

CLONE_ROWS	SOURCE_ROWS	CLONE_TOTAL	SOURCE_TOTAL
3	5	1510.24	1960.74

Different counts and different totals. Completely independent. The storage underneath is shared until something changes, and that is a billing and performance detail. The table is not shared at all.

Can we clone a clone?

Let's try the second generation.

Query

CREATE OR REPLACE TABLE CLONE_OF_CLONE_T101 CLONE CLONE_T101;

Result1 row · 1 column

status
Table CLONE_OF_CLONE_T101 successfully created.

Query

SELECT COUNT(*) AS ROWS_PRESENT, MIN(ID) AS LOWEST_ID, MAX(ID) AS HIGHEST_ID
FROM CLONE_OF_CLONE_T101;

Result1 row · 3 columns

ROWS_PRESENT	LOWEST_ID	HIGHEST_ID
3	3	5

It holds what the clone held at the moment we cloned it, ids 3 to 5. A clone of a clone is just a clone. There is no chain to break and nothing is tracking which generation anything is.

And what happens if we clone something that is not there?

Query

CREATE OR REPLACE TABLE NOWHERE_T101 CLONE TABLE_THAT_NEVER_WAS_T101;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'TABLE_THAT_NEVER_WAS_T101' does not exist or not authorized.

A clean compilation error naming the object. Nothing is created and nothing is half created.

What happens when a CTAS selects no rows at all?

Back to the CTAS for one last question. A SELECT that returns nothing still has columns. Does the table get built?

Query

CREATE OR REPLACE TABLE EMPTY_CTAS_T101 AS
SELECT ID, NAME FROM SOURCE_T101 WHERE 1 = 0;

Result1 row · 1 column

status
Table EMPTY_CTAS_T101 successfully created.

Query

SELECT (SELECT COUNT(*) FROM EMPTY_CTAS_T101) AS ROWS_PRESENT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'EMPTY_CTAS_T101')  AS COLUMNS_PRESENT;

Result1 row · 2 columns

ROWS_PRESENT	COLUMNS_PRESENT
0	2

No rows, and the two columns we selected. The table exists with a shape and no data in it. So CREATE TABLE ... AS SELECT ... WHERE 1 = 0 is a way to get the column list of a query without the rows.

It is not a way to get the definition, though. Everything we found when we looked at the first CTAS copy still applies: no NOT NULL, no defaults, no comments. If that is what you wanted, LIKE is the statement you meant.

One last thing: the comment that did not travel

There is one promise we have not followed all the way through, and it is the easiest to miss. The source has a comment on the table as well as on a column. Let's list every table this post has made, with its comment.

Query

SELECT TABLE_NAME, TABLE_TYPE, ROW_COUNT, COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T101'
ORDER BY TABLE_NAME;

Result7 rows · 4 columns

TABLE_NAME	TABLE_TYPE	ROW_COUNT	COMMENT
CLONE_OF_CLONE_T101	BASE TABLE	3	the table the other three are made from
CLONE_T101	BASE TABLE	3	the table the other three are made from
CTAS_SHAPED_T101	BASE TABLE	3	NULL
CTAS_T101	BASE TABLE	5	NULL
EMPTY_CTAS_T101	BASE TABLE	0	NULL
LIKE_T101	BASE TABLE	1	NULL
SOURCE_T101	BASE TABLE	5	the table the other three are made from

Seven tables, and three of them carry the table comment: the source, the clone, and the clone of the clone. The LIKE copy has NULL there, and so does every CTAS.

So LIKE keeps a column comment and drops the table comment. We saw the display name survive on NAME a few sections ago, and the table comment is not here. Clone keeps both.

And read the comment on those two clones. It says the table the other three are made from, which was true of the table we wrote it on and is not true of either clone. A clone copies the comment verbatim, including the part of it that has stopped being correct.

Worth doing after a clone

So if you clone a table to make a backup, the backup will describe itself as the original. Nothing updates a comment for you. It is worth an ALTER TABLE ... SET COMMENT straight after the clone, while you still remember which one is which.

So which one do you reach for?

Rebuilding a table you want to keep behaving the same way: LIKE, then INSERT. Or CLONE, if you want the rows that are in it now.

A new shape, with renamed or calculated columns: that is the CTAS, and it is the only one of the three that can do it.

A CTAS is not a copy of an old table. It is a new table that happens to start with somebody else's values, and the guarantees do not come with them.

Everything in this post was made to be thrown away, so let's throw it away.

Cleanup

-- Put the schema back the way we found it.
DROP TABLE IF EXISTS SOURCE_T101;
DROP TABLE IF EXISTS CTAS_T101;
DROP TABLE IF EXISTS CTAS_SHAPED_T101;
DROP TABLE IF EXISTS EMPTY_CTAS_T101;
DROP TABLE IF EXISTS LIKE_T101;
DROP TABLE IF EXISTS CLONE_T101;
DROP TABLE IF EXISTS CLONE_OF_CLONE_T101;

Query

DROP TABLE IF EXISTS SOURCE_T101;

Result1 row · 1 column

status
SOURCE_T101 successfully dropped.

Query

DROP TABLE IF EXISTS CTAS_T101;

Result1 row · 1 column

status
CTAS_T101 successfully dropped.

Query

DROP TABLE IF EXISTS CTAS_SHAPED_T101;

Result1 row · 1 column

status
CTAS_SHAPED_T101 successfully dropped.

Query

DROP TABLE IF EXISTS EMPTY_CTAS_T101;

Result1 row · 1 column

status
EMPTY_CTAS_T101 successfully dropped.

Query

DROP TABLE IF EXISTS LIKE_T101;

Result1 row · 1 column

status
LIKE_T101 successfully dropped.

Query

DROP TABLE IF EXISTS CLONE_T101;

Result1 row · 1 column

status
CLONE_T101 successfully dropped.

Query

DROP TABLE IF EXISTS CLONE_OF_CLONE_T101;

Result1 row · 1 column

status
CLONE_OF_CLONE_T101 successfully dropped.

And a check that the schema is as we found it.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T101') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	10

DEMO_DB and PUBLIC, no probe tables left, and the course data still readable.

Key points

A CTAS copies the values and nothing else. NOT NULL, the defaults and the column comment were all gone from the copy.
That is not a bug. A SELECT returns a result set, and a result set has no definition in it for NOT NULL to travel in.
The CTAS copy took a row with a null id that the original refused. Two tables that look identical in a SELECT can disagree about which rows are legal.
A CTAS is the only one of the three that can rename, calculate and filter — and it widens the type to fit an expression without asking. AMOUNT * 1.1 on a NUMBER(12,2) came out as NUMBER(14,3).
CREATE TABLE LIKE is the mirror image: the definition survives, and it brings no rows at all.
The defaults copied by LIKE genuinely fire, and its NOT NULL genuinely refuses.
LIKE keeps the column comments and drops the table comment. CLONE keeps both.
CLONE copies the values and the definition. It is the faithful one.
A clone is genuinely independent. Zero-copy is about the storage underneath, not about the behaviour.
Writing to the clone left the source alone, and writing to the source left the clone alone — ending with equal row counts and different rows.
A clone copies the table comment verbatim, including the sentence that was only true of the original.
Rebuilding a table? LIKE then INSERT, or CLONE. A CTAS is for a new shape, not for a copy of an old one.

Next in this series

Episode 102 — PERMANENT, TRANSIENT and TEMPORARY tables. Three words you can put in front of CREATE TABLE, what each one costs you in Time Travel, and a very good way to lose data without anything telling you that you have.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the three refusals. The _T101 tables are throwaway objects created and dropped by the scripts above. Every count in the text is read off one of the grids on this page, including the row the WHERE clause in the shaped CTAS removed. Nothing is timed and no performance claims are made anywhere in this series. Statement behaviour follows the Snowflake CREATE TABLE documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 101 - CTAS vs LIKE vs CLONE - What a CTAS Silently Throws Away

Let's create a table worth copying badly

Let's copy it with CREATE TABLE AS SELECT

So what did the CTAS copy keep?

What does losing NOT NULL actually cost us?

The one thing a CTAS can do that the others cannot

Now let's copy it with CREATE TABLE LIKE

Do those copied defaults actually fire?

Now let's copy it with CLONE

Let's count all four tables side by side

Is a clone really independent?

Can we clone a clone?

What happens when a CTAS selects no rows at all?

One last thing: the comment that did not travel

So which one do you reach for?

Key points

No comments:

Post a Comment