How to DELETE rows in Snowflake

Galaxy Glossary

How do I delete rows from a table in Snowflake?

DELETE permanently removes rows from a Snowflake table, either all rows or those that match a WHERE condition.

Welcome to Galaxy!
You'll be receiving a confirmation email.

In the meantime, follow us on Twitter

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does DELETE do in Snowflake?

DELETE removes rows from a table and returns the number of affected rows. Without a WHERE clause, the entire table is cleared; with a condition, only matching rows disappear.

What is the basic DELETE syntax?

The minimal form is DELETE FROM table_name [WHERE condition];. You can add table aliases, use sub-queries, or limit rows through JOIN-style predicates.

How to delete a single customer by ID?

Supply the primary key in the WHERE clause to ensure only one row is touched.

DELETE FROM Customers WHERE id = 42;

How to delete multiple orders before a date?

Combine DELETE with a date filter to clean historical data safely.

DELETE FROM Orders WHERE order_date < '2023-01-01';

Can you DELETE with a sub-query?

Yes—use IN or EXISTS to target related rows.

DELETE FROM OrderItems oi WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.id = oi.order_id AND o.order_date < '2023-01-01' );

What are best practices for DELETE?

Always test with SELECT first, wrap large deletes in transactions, and prefer predicate-based deletes over truncating whole tables when auditing matters.

How to reclaim space after massive deletes?

Use ALTER TABLE ... RECLUSTER or rely on automatic micro-partition pruning; Snowflake automatically manages storage but reclustering speeds queries.

Why How to DELETE rows in Snowflake is important

How to DELETE rows in Snowflake Example Usage


-- Remove all cart items for out-of-stock products
DELETE FROM OrderItems oi
USING Products p
WHERE oi.product_id = p.id
  AND p.stock = 0;

How to DELETE rows in Snowflake Syntax


DELETE [FROM] <table_name> [AS alias]
       [USING <additional_tables_or_subqueries>]
       [WHERE <condition>]
       [RETURNING * | column_list];

-- Example with ecommerce tables
DELETE FROM Orders AS o
USING Customers c
WHERE o.customer_id = c.id
  AND c.email LIKE '%@example.com%' ;

Common Mistakes

Deleting without a WHERE clause: This wipes the entire table. Always run a SELECT with the same filter first or wrap the DELETE in a transaction that you can roll back.
Forgetting table alias in multi-table DELETE: If USING is present, all column references must be qualified. Omitting the alias triggers "column is ambiguous" errors.