Any database be it a transactional or a data warehouse will always have records that need to be deleted, either to clean up the complete table or specifically to delete a few records.
The Snowflake Data Cloud, like other databases, has SQL commands to delete specific records or complete tables. In addition, the deleted records can also be recovered quickly using the Time Travel feature.
The syntax of deleting records in Snowflake is very similar to ANSI SQL, however, delete commands in Snowflake can be combined with other features thereby providing greater control for the developers.
In this blog, we’ll look at the various ways to delete records and also to recover them in Snowflake. This post will summarize the different options for deleting records in Snowflake including:
- Delete Records Using Subqueries
- Delete Data Using Filters
- Delete Records Using Correlated Subquery
- How to Delete Duplicate Records
- Delete Semi-Structured/Variant Data Type Data
- Comparison of In-Clause/Using-Clause/Exist-clause Delete types
- Delete Records as a Transaction
- Soft Deletes
Why Delete Records in a Data Warehouse
There are several reasons why an organization would need to delete records in a data warehouse. One of the basic use cases is the regular ETL job to delete and reload historical data to insert new incremental data or completely wipe out older data to insert new data. But there are specific business use cases where an organization will need to delete records in a data warehouse.
One of the major use cases is compliance. Any organization might have to delete very old historical records and retain only the last few years of data to satisfy compliance requirements. Some organizations will have a scheduled process that will scan historical data on a regular basis and delete those records over a specific threshold.
In a more specific use case, EU GDPR allows any individual to force an organization to delete personal records under the “Right to be forgotten/Right to erasure”. If an organization receives such a request, they need to comply by deleting any or all records of specific individuals from all databases.
How to Delete Records in Snowflake
First, let’s delete some records in Snowflake! For this blog, let’s consider Snowflake’s sample data SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER. This is cloned into a new schema with the same name CUSTOMER with the following columns:
Sample data in this table will look like this:
The basic syntax to delete records from Snowflake is below:
Delete from
E.g. delete from customer;
Truncate table
E.g. truncate table customer;
Both these commands will completely wipe out all the records in the table. Even though they are used for the same purpose, there is a slight difference. While Delete and Truncate maintain the deleted data (micro partitions) for recovery purposes (using Time Travel), Truncate removes load metadata as well.
Snowflake usually maintains load metadata (copy/Snowpipe) for each of the tables which includes file name, eTag, size, etc. and the Truncate command removes these load metadata. If this metadata is required, then it is better to use the Delete command.
Truncate is usually faster and it is a metadata-only operation. While Truncate and Delete seem to perform the same type of deletion, Delete statements will bring up a virtual warehouse whereas Truncate does not require a virtual warehouse to be on.
In the screenshot below, we can see Truncate did not use a warehouse (warehouse_size is null), whereas Delete used the warehouse for the operation. We can also see that Truncate is 50% faster (total_elapsed_time) than Delete operation for the same sized table (15M).
How to Delete Records Using Filters
Specific records can be deleted from tables using simple filter conditions or from another table or using complex subqueries. Simple delete using a filter condition will just include where condition in the delete statement.
The syntax looks like this:
delete from where condition;
E.g. delete from customer where c_custkey = 123
Now let’s consider some special cases for deletion.
How to Delete Records Based on Another Table
While most of the simple use cases have delete statements with filters, there will always be cases where we need to delete records based on another table.
Let’s create another table with some customer keys for deletion.
create table invalid_customers (c_custkey string, reason string);
insert into invalid_customers values
(11761864,'No longer a customer'),
(11761910,'No longer a customer'),
(11761864,'Invalid entry');
The table will look like this:
Now let’s write a query to delete the records from the customer table based on this invalid_customers tables. There are different ways to write queries in this case.
Using “In” Clause
delete from customer where c_custkey in (select c_custkey from invalid_customers);
Using “Using” (inner join) Clause
delete from customer as cust using invalid_customers as inactiv_c where cust.c_custkey = inactiv_c.c_custkey;
Using “Exists” Clause
delete from customer as cust where exists(select * from invalid_customers as inactiv_c where cust.c_custkey = inactiv_c.c_custkey);
Performance Considerations:
The performance of these three queries are very similar, but looking at the query profile, “in” and “exists” clause adds additional aggregate where “using” clause doesn’t generate any aggregate.
While looking at this query profile, we may think that the performance of delete queries may depend on how the query is written. While it is partially correct, the actual performance of the delete query depends on the number of micro partitions where deleted records resize.
As you may know, Snowflake organizes data in the form of micro partitions. For high-volume tables, the number of micro partitions may span up to 1000s or even millions (TB/PB sized tables).
Snowflake usually maintains metadata information about each of the micro partitions. Micro partitions are immutable, so Snowflake needs to rewrite the micro partitions into newer micro partitions where the delete action is performed.
In a simple case of deleting a couple of million records in a 100-200 million records table may result in rewriting 90 percent or almost all micro partitions. In some scenarios, clustering may be helpful as it will bring down the number of micro partitions to scan and rewrite. But this may not always be feasible when delete is performed on unique keys/ids.
In a high-volume table spanning multi TB size, it is recommended to cluster tables based on low cardinality keys and redesign the ETL process to delete and reload based on clustering keys. There will still be overhead but it may not be as high as rewriting all micro partitions.
How to Delete Records with Semi-Structured Data
Snowflake has special data types like Variant, Object, and Array to store semi-structured data and map/array type data. While the basic syntax of delete is similar, the filters will require specifying fields with dot/bracket notation.
Let’s create a table with some semi-structured data in a variant column.
create or replace table users(record variant) as
select to_variant(parse_json($1)) from values
('{"name":"John", "age":30, "car":"Honda"}'),
('{"name":"Alex", "age":40, "car":"Toyota"}'),
('{"name":"Tim", "age":20, "car":"Lexus"}');
The data in the table will look like this:
Let’s write a delete query to delete all users where age > 20. The query will look like this:
delete from users where record:age::number > 20;
In this query, the field age is represented as a record:age with an additional typecast specification number using :: notation. Running the above query will delete two records from the table.
The following section has queries to handle some use cases like deleting duplicate records, top 10 records, etc.
Deleting Exact Duplicates From Table
Let’s consider a table customer which has exact duplicates inserted in all the fields.
To remove these exact duplicates, the query will look like the table below. Even though this query does not have “delete” or “truncate”, the “overwrite” clause will implicitly run “truncate” and then distinct records. This will be visible in the query profile. Snowflake runs this as a two-step process with step 1 running truncate and step 2 being the insert.
Insert overwrite into customer select distinct * from customer;
After deleting records, the table will look like this:
How Do We Ensure We Delete Records as a Transaction
Snowflake has auto-commit enabled, so any delete will be committed automatically. But there might be cases where we want to ensure that deletes from multiple tables run as a transaction. Consider the same example where we used a child table (invalid_customer) to delete records from the customer table.
Now if we need to delete records from both tables, then enclose them within a transaction. This will ensure that the delete will be committed only if both deletes succeed.
begin transaction;
delete from customer where c_custkey in (select c_custkey from invalid_customers);
delete from invalid_customers;
Commit;
What About Soft Deletes?
When deleting records from a data warehouse, there is always a case for soft deletes. Soft delete means we do not actually delete records, but we add additional columns (say delete_ind) in the table so that we can update the column which will mark the record as deleted rather than physically deleting it from the table.
We use soft delete records for different reasons. The main reason is to retain the history of data without actually losing them. In Snowflake, we can always use Time Travel to recover deleted records but if a business use case requires deleted records for some sort of historical analysis, then it is better to soft delete them rather than actually delete records.
For example, in the customer table we used above, we can add additional columns so that the table will look like this:
alter table customer add column
delete_ind varchar(1),
delete_ts timestamp ;
Once the column is available and when a record needs to be deleted, we will instead update and mark the record as deleted. For example,
update customer set delete_ind=’Y’, delete_ts=current_timestamp() where c_custkey=238129;
An important point to consider is that Snowflake is designed for data warehouses and it will not perform well for record by record updates. Even if records are updated in batches, it still needs to rewrite micro partitions (remember micro partitions are immutable), so the performance of delete and update will be very similar.
Closing
There are different ways to delete records in Snowflake using queries. Most of the select statement style filters can be applied to delete as well (multi-column filter condition). The type of delete may depend on the use case and requirement.
While there is more than one way to achieve the same results as seen above, choosing the right solution will require a good understanding of Snowflake architecture and business use case.
At phData, we have experts who excel in both technology and business that would be happy to help out. We’ve also been awarded the 2022 Snowflake Partner of the Year. Reach out to us today with your toughest Snowflake questions.
More to explore
Data Engineering
Migrating PySpark to Snowflake with CoCo
Andy Bouts
·
November 17, 2026
Read article
Read article
Anthropic
phData Raises New Funding to Turn Enterprise Data Into Trusted AI Answers and Reliable Agents with Claude
phData
·
October 28, 2026
Read article
Read article
AWS
The road to the AWS Golden Jacket: 13 exams, one foundation
Ryan Leonard
·
October 26, 2026
Read article
Read article
Cookie Consent
We use cookies to improve your experience on our site. By using our site, you consent to cookies.
Cookie Preferences
Manage your cookie preferences below:
Essential cookies enable basic functions and are necessary for the proper function of the website.
NameDescriptionDurationCookie PreferencesThis cookie is used to store the user's cookie consent preferences.30 daysGoogle Tag Manager simplifies the management of marketing tags on your website without code changes.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDurationcookiePreferencesRegisters cookie preferences of a user2 yearstdRegisters statistical data on users' behaviour on the website. Used for internal analytics by the website operator.sessionStatistics cookies collect information anonymously. This information helps us understand how visitors use our website.
Clarity is a web analytics service that tracks and reports website traffic.
Service URL: clarity.microsoft.com (opens in a new window)
NameDescriptionDurationANONCHKIndicates whether MUID is transferred to ANID, a cookie used for advertising. Clarity doesn't use ANID and so this is always set to 0.Session_clckPersists the Clarity User ID and preferences, unique to that site is attributed to the same user ID.12 months_clskConnects multiple page views by a user into a single Clarity session recording.12 monthsCLIDIdentifies the first-time Clarity saw this user on any site using Clarity.12 monthsGoogle Analytics is a powerful tool that tracks and analyzes website traffic for informed marketing decisions.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDuration_gatUsed to monitor number of Google Analytics server requests when using Google Tag Manager1 minute_gidID used to identify users for 24 hours after last activity24 hours_ga_ID used to identify users2 years_galiUsed by Google Analytics to determine which links on a page are being clicked30 seconds_gaID used to identify users2 years__utmxUsed to determine whether a user is included in an A / B or Multivariate test.18 months__utmvContains custom information set by the web developer via the _setCustomVar method in Google Analytics. This cookie is updated every time new data is sent to the Google Analytics server.2 years after last activity__utmcUsed only with old Urchin versions of Google Analytics and not with GA.js. Was used to distinguish between new sessions and visits at the end of a session.End of session (browser)__utmzContains information about the traffic source or campaign that directed user to the website. The cookie is set when the GA.js javascript is loaded and updated when data is sent to the Google Anaytics server6 months after last activity__utmbUsed to distinguish new sessions and visits. This cookie is set when the GA.js javascript library is loaded and there is no existing __utmb cookie. The cookie is updated every time data is sent to the Google Analytics server.30 minutes after last activity__utmtUsed to monitor number of Google Analytics server requests10 minutes__utmaID used to identify users and sessions2 years after last activity_gac_Contains information related to marketing campaigns of the user. These are shared with Google AdWords / Google Ads when the Google Ads and Google Analytics accounts are linked together.90 daysMarketing cookies are used to follow visitors to websites. The intention is to show ads that are relevant and engaging to the individual user.
Facebook Pixel is a web analytics service that tracks and reports website traffic.
Service URL: www.facebook.com (opens in a new window)
NameDescriptionDuration_fbpUsed to track the user across multiple sessions.3 months_fbcUsed to track the last session visit.12 monthsfrUsed to track the user across multiple sessions.3 monthsGoogle Ads is an online advertising platform that enables businesses to create targeted ads displayed on Google search results and partner sites.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDurationFPGCLAWGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days__Secure-3PSIDCCTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-3PSIDTSTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PSIDTSTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PAPISIDTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-3PSIDTargeting cookie. Used to profile the interests of website visitors and display relevant and personalised Google ads.2 yearsConversionGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 days_gcl_awGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gcl_gsGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gcl_gbGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gac_gb_Google uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 DaysFPGCLGBGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 DaysADS_VISITOR_IDCookie required to use the options and on-site web services2 monthsAECAEC cookies ensure that requests within a browsing session are made by the user, and not by other sites. These cookies prevent malicious sites from acting on behalf of a user without that user's knowledge.6 months__Secure-3PAPISIDProfiles the interests of website visitors to serve relevant and personalised ads through retargeting.2 years__Secure-1PSIDCCTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PSIDTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 yearsHubSpot is an all-in-one marketing, sales, and customer service platform that streamlines business growth.
Service URL: legal.hubspot.com (opens in a new window)
NameDescriptionDurationmessagesUtkThis cookie is used to recognize visitors who chat with you via the chatflows tool. If the visitor leaves your site before they're added as a contact, they will have this cookie associated with their browser.13 months__hssrcWhenever HubSpot changes the session cookie, this cookie is also set to determine if the visitor has restarted their browser.session__hsscThis cookie keeps track of sessions.30 minuteshubspotapiThis cookie allows the user to access the app with the correct permissions.7 dayshubspotapi-prefsThis is used with the hubspotapi cookie to remember whether the user checked the 'remember me' box (controls the expiration of the main cookie's authentication).1 Year__hs_opt_outThis cookie is used by the opt-in privacy policy to remember not to ask the visitor to accept cookies again.13 monthshubspotapi-csrfThis is used for CSRF prevention - preventing third party websites from accessing your data. Expires after a year.1 year__hs_do_not_trackThis cookie can be set to prevent the tracking code from sending any information to HubSpot.13 months__hs_cookie_cat_prefThis cookie is used to record the categories a visitor consented to.6 months__hs_initial_opt_inThis cookie is used to prevent the banner from always displaying when visitors are browsing in strict mode.7 days__hs_gpc_banner_dismissThis cookie is used when the Global Privacy Control banner is dismissed.180 dayshs_ab_testThis cookie is used to consistently serve visitors the same version of an A/B test page they’ve seen before.session__hs_notify_banner_dismissThis cookie is used when the website uses a Notify consent banner type.180 dayshs-messages-is-openThis cookie is used to determine and save whether the chat widget is open for future visits.30 minuteshs-messages-hide-welcome-messageThis cookie is used to prevent the chat widget welcome message from appearing again for one day after it is dismissed.1 day__hsmemThis cookie is set when visitors log in to a HubSpot-hosted site.1 yearhs-membership-csrfThis cookie is used to ensure that content membership logins cannot be forged.sessionhs_langswitcher_choiceThis cookie is used to save the visitor's selected language choice when viewing pages in multiple languages.2 years__hstcThe main cookie for tracking visitors.13 monthshubspotutkThis cookie keeps track of a visitor's identity. It is passed to HubSpot on form submission and used when deduplicating contacts.13 monthsLinkedIn Insight is a web analytics service that tracks and reports website traffic.
Service URL: www.linkedin.com (opens in a new window)
NameDescriptionDurationlangUsed to remember language settings.Sessionli_fat_idUsed for member indirect identifier for conversion tracking.30 daysli_sugrUsed to make a probabilistic match of a user's identity.90 daysli_oatmlUsed for identifying LinkedIn Members for advertising and analytics.30 daysliapUsed for authentication of LinkedIn members.90 dayslidcUsed for routing and session management.24 hoursGPC Signal Honored