Snowflake DML Commands and Examples

Snowflake is a data warehouse platform that efficiently stores and manages data. This platform eliminates the need to use physical servers and perform software installations.

You can use Snowflake SQL commands to store and manage data stored in Snowflake tables. Among all of them, Snowflake DML commands help you manipulate data efficiently.

Snowflake Data manipulation commands enable you to organize and modify data within Snowflake tables. In this beginner-friendly Snowflake DML tutorial, you will explore various Snowflake DML commands and how to use them in practical scenarios.

Table of content

• Snowflake DML Commands – Overview
• Snowflake DML Commands with Examples
  • Insert Data
  • Update Data
  • Delete Data
  • Avoid Gaps in Data
  • Truncate a Table
• Frequently Asked Questions and Answers

Snowflake DML Commands – Overview

Snowflake administrators use DML commands to insert, update, delete, and truncate data in tables. They also use DML commands to copy data into and out of tables in bulk.

At a glance, you can use the INSERT command to add new rows in a table, and use the DELETE command to delete records in a table. Besides, you can use the UPDATE command to modify existing records in a table.

Snowflake DML Commands Explained with Examples

This section walks you through the Snowflake DML commands with practical examples. It will help you gain a better understanding of the commands.

How to Insert Data in Snowflake

In Snowflake, you can insert data into a newly created table or an existing table.

Let’s uncover how to insert data with and without columns with examples.

The Snowflake INSERT operation adds multiple rows to a table. You can insert values into every column in the table, either predefined values or results from a query.

You can insert data with or without parameters.

You can insert data into Snowflake tables by specifying the values. The important aspect is that you need to pass every value in order. For example, if you have 15 columns, you must specify 15 columns.

Insert into sessions1 values (2, ‘2022-03-10 10:15:19.400’, ‘2022-04-10 10:35:19.400’, 1);

You need to list columns before values for clarity.

Insert into sessions1 (id, start_date, end_date, category)
Values (13, ‘2022-03-09 16:15:19.500’, ‘2022-03-11 17:53:57.753’, 2);

After specifying the column list, you don’t need to remember the table's column order.

Insert into sessions1 (category, id, start_date, end_date)
values(2,3, ‘2022-06-08 15:05:15.500’, ‘2022:08:10  17:07:53.600’);

Below are the benefits of including the column list before values.

• You don’t need to specify every column’s value.
• 'INSERT' statements without column lists may break when columns are removed or added to a table. You must update the query to incorporate the change.
• If you have many columns, it’s easier to match each value to its column when viewing the statement.

For example, you can choose to specify only selected columns.

Insert into sessions1(start_date, id) values (5, ‘2022-05-03 15:17:35.600’);

• Inserting Multiple Rows

A single 'INSERT' statement can add multiple rows by grouping sets of values within parentheses.

Insert into sessions1 (category, start_date, end_date, id)
values
(7, ‘2022-05-03  16:10:25.500’, ‘2022-03-04 17:15:45.553’, 5),
(6, ‘2022-03-05 17:20:25.300’, ‘2022-09-05 18:25:10.500’, 6),
(8, ‘2022-05-07 19:15:20.200’, ‘2022-07-09 21:05:09.600’, 7);

You can also use 'CREATE TABLE' with the 'SELECT' command to copy data from an existing table.

Create table sessions_dm_2 as
Select *
from sessions1
Where id <=6

• Inserting JSON Values

To insert data into a JSON column, use valid JSON within a quoted string.

Insert into sessions1(dates) values( ‘ {“start_date”: “2022-05-07 17:10:20.600”, “end_date”: “2022-08-09 16:12:25.180”}’);

 

Want to enhance your skills to become a master in Snowflake Certification, Enroll in our Snowflake Training Certification Course

How to Update Data in Snowflake?

This section covers updating data in Snowflake tables, with an example.

The UPDATE statement updates specific rows in a Snowflake table with new values.

The syntax for the UPDATE command is given below.

UPDATE <target_table>
SET <col_name> = <value> [ , <col_name> = <value> , ... ]
[ FROM <additional_tables> ]
[ WHERE <condition> ]

You update rows in a table that meet a specific condition using a 'WHERE' clause.

• Snowflake Update Table for Single Row

Below is an example for updating a single row of a table.

update sessions1
set start_date = ‘2022-05-03 11:15:13.430’
      end_date  = ‘2022-07-25 12:30:20.321’
where id = 2;

• For Multiple Rows

You can use the following code to update multiple rows.

update sessions1
set end_date = null
where category = 2;

To update all the rows in the Snowflake table, you use the ‘UPDATE’ statement without the ‘WHERE’ clause.

update sessions1
set end_date = ‘2022-08-09 15:45:57.753’

To update multiple rows at once, use the code below.

update sessions1
set start_date = ‘2022-09-09 15:50:57.625’
      end_date  = ‘2022-07-06 17:50:55.737’ 

Now you understand how to update data in Snowflake tables.

How to Delete Data in Snowflake

With Snowflake, you can efficiently organize and delete data. Here, we will cover how to delete data from Snowflake tables with an example.

In Snowflake, use the DELETE command to remove unwanted data. Importantly, you must specify the conditions for deleting rows from tables, either individually or in bulk.

For deleting the rows from the Snowflake table, you can use the ‘DELETE’ statement.

• Example

delete from sessions1 where id =8;

While the 'WHERE' clause is optional, it is required to delete specific records.

delete from sessions1;

You can use the DELETE command with a WHERE clause to delete rows that match specific conditions.

[Also Read: Snowflake Tutorial]

How to Avoid Gaps in Data in Snowflake

While storing data, you may face gaps. By using Common Table Expressions (CTEs), you can create a series of date/time values to overcome this setback.

Let’s discover how to create a continuous series of time or date values.

In Snowflake, you can group data by time and use CTEs to create a continuous time/date series.

set start_date = ‘2022-06-02’
set end_date = ‘2022-06-30’

with cte_data (data_rec) as (

select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec))
from cte_date1
where date_rec <  $end_date
)
select date_rec1
from cte_date1

date_rec1

2022-06-02
2022-06-03
2022-06-04
2022-06-05
2022-06-06
….
2022-06-30

Below is the code to count sessions for each day.

set start_date = ‘2022-06-02’
set end_date = ‘2022-06-30’
with cte_date (date_rec) as (
select to_date($start_date)
union all
select to_date(dateadd(day, 1, date_rec))
from cte_date1
where date_rec < $end_date
)

select
cte_date.date_rec,
count(s.id) as session_ct
from cte_date
left outer join sessions s on to_date(s. start_date) = cte_date.date_rec
group by date_rec;

In summary, we can avoid data gaps by generating a series of time or date values using common table expressions.

How to Truncate a Table in Snowflake

You can use the 'TRUNCATE' command to remove all content from a table at once. 

• The syntax for the TRUNCATE

 

truncate table name

If the table contains an 'AUTO_INCREMENT' column, its counter does not reset. This behaviour differs from that of other databases, such as SQL Server, where you do not have to reset the counter manually.

You can completely remove all data from a table using the 'TRUNCATE' command.

The Top 40+ Best BigQuery Interview Questions & Answers 2025 article can help you understand key concepts and prepare for interviews.

Frequently Asked Questions and Answers

1. Can I learn Snowflake easily?

Ans: Yes, you can learn Snowflake easily. Before starting, gain some basic exposure to data engineering and data warehousing concepts to help you learn it quickly.

2. Is Snowflake a database or a data warehouse?

Ans: At its core, Snowflake is a cloud-native, fully managed data warehousing platform. It unifies data storage, processing, and analytics.

3. How long will it take to learn Snowflake?

Ans: You can learn Snowflake in 3–4 weeks. Practising through more labs and projects after training will accelerate your progress toward becoming a skilled administrator.

4. How to ensure data quality while using Snowflake DML commands?

Ans: Use these methods to ensure quality data in Snowflake.

• Data metric functions (DMF)
• Anomaly detection
• Expectations
• DMF schedule

5. Can I get any additional learning resources to learn Snowflake?

Ans: You can use the following e-learning resources to boost your skills in Snowflake.

• Snowflake Tutorials
• Snowflake Interview Questions
• Snowflake Sample Resumes
• Snowflake Quiz

6. Does Snowflake support transactions for DML operations?

Ans: Yes, Snowflake supports transactions. A DML statement starts a transaction after a DDL statement. You can roll back or commit DML operations to maintain consistency.

Conclusion

We hope this tutorial helped you learn to use INSERT, DELETE, UPDATE, and TRUNCATE commands in real-world Snowflake scenarios. You should now feel confident managing data stored in Snowflake tables.

To learn more about Snowflake DML commands, enroll in a Snowflake course with MindMajix. After training, you will be able to manage data in Snowflake warehouses competently.