How to Update Data in Snowflake

Snowflake is a powerful cloud data platform that makes managing data easy. One key operation is updating existing data to reflect new information or correct errors. In this tutorial, we’ll walk you through the steps to update data in Snowflake using SQL.

1. Understand the UPDATE Statement

The UPDATE statement in Snowflake lets you modify existing records in a table. The basic syntax looks like this:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

The WHERE clause is critical — it ensures only the intended rows are updated. Without it, all rows in the table will be updated.

2. Example: Updating a Single Record

UPDATE customers
SET status = 'Active'
WHERE customer_id = 123;

This command sets the status of the customer with ID 123 to ‘Active’.

3. Example: Updating Multiple Columns

UPDATE products
SET price = price * 1.1, last_updated = CURRENT_DATE
WHERE category = 'Electronics';

This increases the price of all electronics by 10% and updates the last modified date.

4. Using JOIN in Updates

Snowflake supports using joins in updates to bring in values from another table:

UPDATE target
SET target.column1 = source.column1
FROM target
JOIN source ON target.id = source.id
WHERE source.status = 'Approved';

5. Best Practices

• Always back up your data before large updates.
• Test your WHERE condition with a SELECT query before running the update.
• Use transactions (BEGIN / COMMIT / ROLLBACK) for critical updates.

6. Common Pitfalls

• Forgetting the WHERE clause — this updates all rows!
• Updating large tables without filtering, which can cause performance issues.
• Not checking for null values that may impact the update logic.

Conclusion

Updating data in Snowflake is straightforward when you follow best practices and understand the SQL syntax. Always test your updates on a small dataset before applying them broadly, and remember to use transactions when necessary to maintain data integrity.