Welcome To TechBrothersIT: Snowflake SQL Tutorial 118 - The DELETE That Emptied The Whole Table, And How I Got It Back

Snowflake SQL Tutorial 118 - The DELETE That Emptied The Whole Table, And How I Got It Back

Snowflake SQL · Episode 118

In this post we are going to cover the DELETE statement in Snowflake.

DELETE removes rows from a table. You name the table, you describe the rows you want gone, and Snowflake tells you how many it removed. One number. That is the whole report.

We will start with the simplest form and build it up step by step. Along the way we will write a DELETE that empties the whole table by accident, and then get every row back with Time Travel.

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

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

On this page

Let's create some sample data
Let's start with the simplest DELETE
What happens when a DELETE matches nothing?
Now let's bring in a second table
The habit that will save you: write it as a SELECT first
Now let's do the DELETE with USING
What happens when the join predicate is not there?
Can we get them back?
Now let's try a source that names the same customer twice
Are there other ways to write this?
Does DELETE reset an AUTOINCREMENT counter?
One last thing: DELETE will not go through a view

Let's create some sample data

Before we delete anything we need something to delete from. The script below creates an ORDERS_T118 table and puts eight orders into it. Each order has a customer, a region, an amount and a status.

Sample data

-- Sample data for this post. Run it once in a worksheet.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE ORDERS_T118 (
    ORDER_ID  INT,
    CUSTOMER  VARCHAR(20),
    REGION    VARCHAR(10),
    AMOUNT    NUMBER(10,2),
    STATUS    VARCHAR(12)
);

INSERT INTO ORDERS_T118 (ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS) VALUES
    (1, 'acme',    'north', 100.00, 'open'),
    (2, 'acme',    'north', 250.50, 'shipped'),
    (3, 'borealis','south',  75.25, 'open'),
    (4, 'borealis','south', 310.00, 'cancelled'),
    (5, 'cobalt',  'east',   90.00, 'open'),
    (6, 'cobalt',  'east',  180.75, 'shipped'),
    (7, 'delta',   'west',  420.00, 'open'),
    (8, 'delta',   'west',   55.00, 'cancelled');

Let's look at what we just created.

Query

SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS
FROM ORDERS_T118 ORDER BY ORDER_ID;

Result8 rows · 5 columns

ORDER_ID	CUSTOMER	REGION	AMOUNT	STATUS
1	acme	north	100.00	open
2	acme	north	250.50	shipped
3	borealis	south	75.25	open
4	borealis	south	310.00	cancelled
5	cobalt	east	90.00	open
6	cobalt	east	180.75	shipped
7	delta	west	420.00	open
8	delta	west	55.00	cancelled

Eight rows. Two of them are cancelled, which is where we are going to start.

Let's start with the simplest DELETE

The simplest DELETE is the word itself, then FROM and the table, then a WHERE clause describing the rows we want gone.

Let's remove the cancelled orders.

Query

DELETE FROM ORDERS_T118 WHERE STATUS = 'cancelled';

Result1 row · 1 column

number of rows deleted
2

One column, one number. In the last post UPDATE gave us two columns to read. DELETE gives us one, because there is nothing to choose. A row either goes or it stays.

Let's see what is left.

Query

SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS
FROM ORDERS_T118 ORDER BY ORDER_ID;

Result6 rows · 5 columns

ORDER_ID	CUSTOMER	REGION	AMOUNT	STATUS
1	acme	north	100.00	open
2	acme	north	250.50	shipped
3	borealis	south	75.25	open
5	cobalt	east	90.00	open
6	cobalt	east	180.75	shipped
7	delta	west	420.00	open

Six rows, and the two cancelled ones are gone. Hold on to that six, because it matters later.

What happens when a DELETE matches nothing?

Let's ask for a region that does not exist in the table at all.

Query

DELETE FROM ORDERS_T118 WHERE REGION = 'antarctica';

Result1 row · 1 column

number of rows deleted
0

Zero rows deleted, and that is not an error. The statement was perfectly valid. It simply described a set of rows that happens to be empty.

And the table

SELECT COUNT(*) AS ROWS_PRESENT FROM ORDERS_T118;

Result1 row · 1 column

ROWS_PRESENT
6

Still six. Nothing happened, and nothing needed to.

Now let's bring in a second table

Deleting by a value we type ourselves is the easy case. The real case is a list of keys in another table: a feed, an export, a list somebody sent you.

Let's make one. Two customers, both for fraud.

A list of customers to remove

-- A second table: the customers somebody wants removed.
CREATE OR REPLACE TABLE BADLIST_T118 (
    CUSTOMER  VARCHAR(20),
    REASON    VARCHAR(20)
);

INSERT INTO BADLIST_T118 (CUSTOMER, REASON) VALUES
    ('acme',   'fraud'),
    ('cobalt', 'fraud');

Query

SELECT CUSTOMER, REASON FROM BADLIST_T118 ORDER BY CUSTOMER;

Result2 rows · 2 columns

CUSTOMER	REASON
acme	fraud
cobalt	fraud

Two names. Between them they own four of the six orders still in the table.

The habit that will save you: write it as a SELECT first

Before deleting anything against another table, write the same thing as a SELECT. The identical FROM, the identical WHERE.

Query

SELECT O.ORDER_ID, O.CUSTOMER, O.STATUS, B.REASON
FROM ORDERS_T118 O, BADLIST_T118 B
WHERE O.CUSTOMER = B.CUSTOMER
ORDER BY O.ORDER_ID;

Result4 rows · 4 columns

ORDER_ID	CUSTOMER	STATUS	REASON
1	acme	open	fraud
2	acme	shipped	fraud
5	cobalt	open	fraud
6	cobalt	shipped	fraud

Four rows. Orders 1, 2, 5 and 6 — two customers with two orders each. That is exactly what the DELETE is about to remove, and we are looking at it before it removes it.

Let's count it rather than glance at it.

Counted

SELECT COUNT(*) AS ROWS_THE_DELETE_WILL_TOUCH
FROM ORDERS_T118 O, BADLIST_T118 B
WHERE O.CUSTOMER = B.CUSTOMER;

Result1 row · 1 column

ROWS_THE_DELETE_WILL_TOUCH
4

Four. Now we have a number to compare the DELETE against.

Now let's do the DELETE with USING

USING is how a DELETE brings in another table. We name the source after USING, and we tie it to the target in the WHERE clause.

Query

DELETE FROM ORDERS_T118
USING BADLIST_T118
WHERE ORDERS_T118.CUSTOMER = BADLIST_T118.CUSTOMER;

Result1 row · 1 column

number of rows deleted
4

Four rows deleted, matching the SELECT exactly. That is the whole point of running the SELECT: the two numbers agree, so nothing surprising happened.

What is left

SELECT ORDER_ID, CUSTOMER, REGION, STATUS
FROM ORDERS_T118 ORDER BY ORDER_ID;

Result2 rows · 4 columns

ORDER_ID	CUSTOMER	REGION	STATUS
3	borealis	south	open
7	delta	west	open

Two orders survived, borealis and delta, who are on nobody's list.

Now let's put those four back, so the next statement starts from the same place.

Restore

INSERT INTO ORDERS_T118 (ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS)
VALUES (1, 'acme',    'north', 100.00, 'open'),
       (2, 'acme',    'north', 250.50, 'shipped'),
       (5, 'cobalt',  'east',   90.00, 'open'),
       (6, 'cobalt',  'east',  180.75, 'shipped');

Result1 row · 1 column

number of rows inserted
4

And the count

SELECT COUNT(*) AS ROWS_PRESENT FROM ORDERS_T118;

Result1 row · 1 column

ROWS_PRESENT
6

Six again, exactly as before the DELETE.

Worth saying out loud

DELETE ... USING reads like a join because it is one. The rows it removes are the target rows the join matched. Everything in this post that goes wrong, goes wrong because of that one sentence.

What happens when the join predicate is not there?

Here is the same statement again, with one line changed. We still delete from ORDERS_T118 using BADLIST_T118. But the WHERE clause now filters on the reason instead of matching the customer.

Read that WHERE clause before you read the result. It looks like a sensible filter. It is a sensible filter. It filters the badlist.

Query

DELETE FROM ORDERS_T118
USING BADLIST_T118
WHERE BADLIST_T118.REASON = 'fraud';

Result1 row · 1 column

number of rows deleted
6

Six rows deleted. Not four.

And the table

SELECT COUNT(*) AS ROWS_PRESENT FROM ORDERS_T118;

Result1 row · 1 column

ROWS_PRESENT
0

Zero. Every order in the table is gone. The table held six at that moment, because two of the original eight went in the first DELETE of this post, and all six of the rest went here.

Nothing is tying the badlist to the orders. There is no join predicate at all. So every row of one pairs with every row of the other, every pair satisfies REASON = 'fraud', and every target row is matched.

The most expensive typo in SQL is a missing line

Snowflake did not object, because nothing about that statement is invalid. It is not a syntax error and it is not a warning. It is a valid statement that means something you did not intend. This is why the SELECT first is a habit and not a nicety — the identical FROM and WHERE would have returned every row in the table, on screen, before anything was removed.

Can we get them back?

Time Travel can read a table as it was at a point in the past. What is less widely known is that it can name a statement rather than a clock time, with BEFORE(STATEMENT => ...).

LAST_QUERY_ID(-2) gives us the id of the statement two back in this session. Let's count the rows the table had just before it ran.

Query

SELECT COUNT(*) AS ROWS_BEFORE_THAT_STATEMENT
FROM ORDERS_T118 BEFORE(STATEMENT => LAST_QUERY_ID(-2));

Result1 row · 1 column

ROWS_BEFORE_THAT_STATEMENT
6

Six. Six rows existed before that statement, and Snowflake can still see them.

If we can read them, we can insert them. Same clause, this time inside an INSERT.

Query

INSERT INTO ORDERS_T118 (ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS)
SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS
FROM ORDERS_T118 BEFORE(STATEMENT => LAST_QUERY_ID(-3));

Result1 row · 1 column

number of rows inserted
6

And the table

SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS
FROM ORDERS_T118 ORDER BY ORDER_ID;

Result6 rows · 5 columns

ORDER_ID	CUSTOMER	REGION	AMOUNT	STATUS
1	acme	north	100.00	open
2	acme	north	250.50	shipped
3	borealis	south	75.25	open
5	cobalt	east	90.00	open
6	cobalt	east	180.75	shipped
7	delta	west	420.00	open

Six rows inserted, and there they all are. Same ids, same customers, same amounts. Nothing lost.

One condition

How far back you can go depends on the table's data retention period, and the statement has to be one your account can still see. Read the Snowflake Time Travel documentation before you rely on it. But this is the query worth keeping from this episode.

Now let's try a source that names the same customer twice

In the last post a duplicated source was a real problem for UPDATE. There were two candidate values for one row, Snowflake had to pick one, and there is a whole session parameter about it.

Let's ask DELETE the same question. Here is a list that names acme twice, once for fraud and once for a chargeback.

A list with a duplicate in it

-- The same idea, but this list names one customer twice.
CREATE OR REPLACE TABLE DUPLIST_T118 (
    CUSTOMER  VARCHAR(20),
    REASON    VARCHAR(20)
);

INSERT INTO DUPLIST_T118 (CUSTOMER, REASON) VALUES
    ('acme', 'fraud'),
    ('acme', 'chargeback');

Orders acme has

SELECT COUNT(*) AS ACME_ORDERS FROM ORDERS_T118 WHERE CUSTOMER = 'acme';

Result1 row · 1 column

ACME_ORDERS
2

Pairs the join makes

SELECT COUNT(*) AS PAIRS_THE_JOIN_MAKES
FROM ORDERS_T118 O, DUPLIST_T118 D
WHERE O.CUSTOMER = D.CUSTOMER;

Result1 row · 1 column

PAIRS_THE_JOIN_MAKES
4

Two orders, and two list rows naming them, so the join makes four pairs. Now let's delete using that list and watch the number.

Query

DELETE FROM ORDERS_T118
USING DUPLIST_T118
WHERE ORDERS_T118.CUSTOMER = DUPLIST_T118.CUSTOMER;

Result1 row · 1 column

number of rows deleted
2

Two. Not four. It counts target rows, not pairs.

And acme now

SELECT COUNT(*) AS ACME_ORDERS_REMAINING
FROM ORDERS_T118 WHERE CUSTOMER = 'acme';

Result1 row · 1 column

ACME_ORDERS_REMAINING
0

Zero. Both acme orders are gone, once each.

Where DELETE and UPDATE part company

UPDATE has to choose a value when two source rows match the same target row, which is why it needs a parameter to tell you about it.
DELETE has nothing to choose. The row either goes or it stays.
So a duplicated source is simply not a problem for DELETE.

Are there other ways to write this?

Yes. A subquery does the same job, and some people find it easier to read.

Query

DELETE FROM ORDERS_T118
WHERE CUSTOMER IN (SELECT CUSTOMER FROM BADLIST_T118);

Result1 row · 1 column

number of rows deleted
2

Two rows deleted. Both acme orders had already gone in the section above, so the two this one removes are the cobalt ones.

What is left

SELECT ORDER_ID, CUSTOMER, REGION, STATUS
FROM ORDERS_T118 ORDER BY ORDER_ID;

Result2 rows · 4 columns

ORDER_ID	CUSTOMER	REGION	STATUS
3	borealis	south	open
7	delta	west	open

Borealis and delta again. The same two rows we saw survive earlier, arrived at a different way.

And USING accepts more than one source table, which I did not know until I tried it.

Query

DELETE FROM ORDERS_T118
USING BADLIST_T118, DUPLIST_T118
WHERE ORDERS_T118.CUSTOMER = BADLIST_T118.CUSTOMER
  AND ORDERS_T118.CUSTOMER = DUPLIST_T118.CUSTOMER;

Result1 row · 1 column

number of rows deleted
0

Accepted, and zero rows deleted. By this point nothing in the table matched both lists, so there was nothing to remove. The thing being shown here is that Snowflake took the statement at all.

Does DELETE reset an AUTOINCREMENT counter?

There are two ways to empty a table. DELETE with no WHERE clause, and TRUNCATE. Let's find out whether either of them resets a counter.

Two identical tables, each with its own AUTOINCREMENT column, and three rows in each.

Two counter tables

-- Two identical tables, each with its own counter.
CREATE OR REPLACE TABLE COUNTED_D_T118 (
    ID     INT AUTOINCREMENT START 1 INCREMENT 1,
    LABEL  VARCHAR(10)
);

CREATE OR REPLACE TABLE COUNTED_T_T118 (
    ID     INT AUTOINCREMENT START 1 INCREMENT 1,
    LABEL  VARCHAR(10)
);

INSERT INTO COUNTED_D_T118 (LABEL) VALUES ('a'), ('b'), ('c');
INSERT INTO COUNTED_T_T118 (LABEL) VALUES ('a'), ('b'), ('c');

The ids they handed out

SELECT 'delete side' AS WHICH_TABLE, ID, LABEL FROM COUNTED_D_T118
UNION ALL
SELECT 'truncate side', ID, LABEL FROM COUNTED_T_T118
ORDER BY WHICH_TABLE, ID;

Result6 rows · 3 columns

WHICH_TABLE	ID	LABEL
delete side	1	a
delete side	2	b
delete side	3	c
truncate side	1	a
truncate side	2	b
truncate side	3	c

Both counters started at 1 and both handed out 1, 2 and 3. A fair comparison.

Now let's empty one each way.

DELETE

DELETE FROM COUNTED_D_T118;

Result1 row · 1 column

number of rows deleted
3

TRUNCATE

TRUNCATE TABLE COUNTED_T_T118;

Result1 row · 1 column

status
Statement executed successfully.

Three rows deleted on one side. On the other, TRUNCATE does not report a row count at all — just that it worked.

Now one more row in each, and let's look at the id each table hands out.

Insert one

INSERT INTO COUNTED_D_T118 (LABEL) VALUES ('after');

Result1 row · 1 column

number of rows inserted
1

And one

INSERT INTO COUNTED_T_T118 (LABEL) VALUES ('after');

Result1 row · 1 column

number of rows inserted
1

The id each one gave

SELECT 'emptied with DELETE'   AS HOW_IT_WAS_EMPTIED, ID, LABEL FROM COUNTED_D_T118
UNION ALL
SELECT 'emptied with TRUNCATE', ID, LABEL FROM COUNTED_T_T118
ORDER BY HOW_IT_WAS_EMPTIED;

Result2 rows · 3 columns

HOW_IT_WAS_EMPTIED	ID	LABEL
emptied with DELETE	4	after
emptied with TRUNCATE	4	after

Identical. Both tables handed out 4, which is the next number after the 3 the counter had already reached. Neither DELETE nor TRUNCATE sent the counter back to the start.

That is worth knowing if you have ever assumed an empty table means a fresh sequence. The rows are gone. The counter is not.

One last thing: DELETE will not go through a view

Let's make a view over the open orders and try to delete a row through it.

A view

CREATE OR REPLACE VIEW OPEN_ORDERS_T118 AS
SELECT ORDER_ID, CUSTOMER, REGION, AMOUNT, STATUS
FROM ORDERS_T118 WHERE STATUS = 'open';

Result1 row · 1 column

status
View OPEN_ORDERS_T118 successfully created.

Query

DELETE FROM OPEN_ORDERS_T118 WHERE ORDER_ID = 7;

Query failed — Snowflake returned an error

001759 (42601): SQL compilation error:
DELETE statement's target must be a table

Refused, and the message says the rule out loud. A DELETE has to name a table.

Then everything this post made gets dropped, and the last query counts what was left behind.

Cleanup

-- Put the schema back the way we found it.
DROP VIEW  IF EXISTS OPEN_ORDERS_T118;
DROP TABLE IF EXISTS ORDERS_T118;
DROP TABLE IF EXISTS BADLIST_T118;
DROP TABLE IF EXISTS DUPLIST_T118;
DROP TABLE IF EXISTS COUNTED_D_T118;
DROP TABLE IF EXISTS COUNTED_T_T118;

The last check

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

Result1 row · 5 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	PROBE_VIEWS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	10

No probe tables, no probe views, and the rest of the database still readable. A script that creates objects should count them back out again.

Key points

DELETE reports one number, the rows it removed, where UPDATE reports two.
A DELETE that matches nothing removes zero rows and is not an error.
DELETE ... USING is a join. The rows it removes are the target rows the join matched.
A missing join predicate is not a syntax error. It is a valid statement that pairs every row with every row, and nothing warns you.
Write the DELETE as a SELECT first, with the identical FROM and WHERE. That is the habit.
Time Travel can name the statement that did it, with BEFORE(STATEMENT => LAST_QUERY_ID(...)), and the same clause inside an INSERT puts the rows back.
A duplicated source is not a problem for DELETE, because there is no value to choose. It counts target rows, not pairs.
Neither DELETE nor TRUNCATE resets an AUTOINCREMENT counter. The sequence carries straight on.
DELETE will not go through a view. Its target must be a table.

Next in this series

Episode 119 — MERGE, part one. Everybody learns MERGE as update if it exists, insert if it does not. We will look at what decides whether it exists — and at a condition in the ON clause that quietly duplicated a row.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The ORDERS_T118 data is made up for teaching. The refusal near the end is a real captured error and is shown as it came back. All the _T118 objects are created and dropped by the script. Nothing here is timed and no performance claims are made anywhere in this series. Clause behaviour follows the Snowflake DELETE documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 118 - The DELETE That Emptied The Whole Table, And How I Got It Back

Let's create some sample data

Let's start with the simplest DELETE

What happens when a DELETE matches nothing?

Now let's bring in a second table

The habit that will save you: write it as a SELECT first

Now let's do the DELETE with USING

What happens when the join predicate is not there?

Can we get them back?

Now let's try a source that names the same customer twice

Are there other ways to write this?

Does DELETE reset an AUTOINCREMENT counter?

One last thing: DELETE will not go through a view

Key points

No comments:

Post a Comment