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 the Galaxy, Guardian!
You'll be receiving a confirmation email

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.