{"id":667,"date":"2025-05-09T06:58:53","date_gmt":"2025-05-09T06:58:53","guid":{"rendered":"https:\/\/datadandies.nl\/?p=667"},"modified":"2025-05-09T06:58:53","modified_gmt":"2025-05-09T06:58:53","slug":"commit-en-rollback-in-snowflake-sql","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2025\/05\/09\/commit-en-rollback-in-snowflake-sql\/","title":{"rendered":"COMMIT en ROLLBACK in Snowflake SQL"},"content":{"rendered":"\n<p>Ever wondered what COMMIT and ROLLBACK do in Snowflake SQL?<\/p>\n\n\n\n<p>I know I have.<\/p>\n\n\n\n<p>Let me try to explain it using Snowflake SQL.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro padding-bottom-disabled cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1.3rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * 1.3rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:16px 0 0 16px;width:100%;text-align:left;background-color:#1e1e1e\"><span style=\"background:#c7c7c7;padding:0.3rem 0.5rem 0.2rem;border-radius:1rem;font-size:0.8em;line-height:1;height:1.25rem;text-align:center;display:inline-flex;align-items:center;justify-content:center;color:#1e1e1e\">SQL<\/span><\/span><span role=\"button\" tabindex=\"0\" data-code=\"--Creating prerequisites.\nCREATE DATABASE TEST;\nCREATE TABLE T_D_PEOPLE (NAME VARCHAR);\nINSERT INTO T_D_PEOPLE VALUES ('Bruce');\n\n--Check out the table.\nSELECT * FROM T_D_PEOPLE;\n\n--AUTOCOMMIT is set to true. This means that statements are commited and cannot be rolled back. \nSHOW PARAMETERS LIKE '%COMMIT%' IN SESSION;\n\n--Let's check if this is indeed true. \nINSERT INTO T_D_PEOPLE VALUES ('Clark');\n\n--Both Bruce and Clark are in the table now.\nSELECT * FROM T_D_PEOPLE;\n\n--When I try to rollback, nothing happens: Clark is still in the table. \nROLLBACK;\n\n--Let's set AUTOCOMMIT to false\nALTER SESSION SET AUTOCOMMIT = false;\n\n--Let's insert another name.\nINSERT INTO T_D_PEOPLE VALUES ('Diana');\n\n--Bruce, Clark and Diana are in the table now.\nSELECT * FROM T_D_PEOPLE;\n\n--However, when I rollback, Diana will dissappear, because the INSERT statement has not been committed yet.\nROLLBACK;\n\n--If I do the same insert and commit right after however, the rollback will not roll the insert back because the INSERT statement has been committed.\nINSERT INTO T_D_PEOPLE VALUES ('Diana');\nCOMMIT;\n\n--Trying to rollback will not remove the inserted record because it has been committed. \nROLLBACK;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Kopieer\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #6A9955\">--Creating prerequisites.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">DATABASE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">TEST<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">T_D_PEOPLE<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">NAME<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VARCHAR<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">INSERT INTO<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE <\/span><span style=\"color: #569CD6\">VALUES<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;Bruce&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Check out the table.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--AUTOCOMMIT is set to true. This means that statements are commited and cannot be rolled back. <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">SHOW PARAMETERS <\/span><span style=\"color: #569CD6\">LIKE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;%COMMIT%&#39;<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SESSION<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Let&#39;s check if this is indeed true. <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">INSERT INTO<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE <\/span><span style=\"color: #569CD6\">VALUES<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;Clark&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Both Bruce and Clark are in the table now.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--When I try to rollback, nothing happens: Clark is still in the table. <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ROLLBACK<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Let&#39;s set AUTOCOMMIT to false<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ALTER<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SESSION<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOCOMMIT = false;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Let&#39;s insert another name.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">INSERT INTO<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE <\/span><span style=\"color: #569CD6\">VALUES<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;Diana&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Bruce, Clark and Diana are in the table now.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--However, when I rollback, Diana will dissappear, because the INSERT statement has not been committed yet.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ROLLBACK<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--If I do the same insert and commit right after however, the rollback will not roll the insert back because the INSERT statement has been committed.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">INSERT INTO<\/span><span style=\"color: #D4D4D4\"> T_D_PEOPLE <\/span><span style=\"color: #569CD6\">VALUES<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;Diana&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">COMMIT<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--Trying to rollback will not remove the inserted record because it has been committed. <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">ROLLBACK<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><span style=\"display:flex;align-items:flex-end;padding:10px;width:100%;justify-content:flex-end;background-color:#1E1E1E;color:#c7c7c7;font-size:12px;line-height:1;position:relative\">SQL<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Ever wondered what COMMIT and ROLLBACK do in Snowflake SQL? I know I have. Let me try to explain it using Snowflake SQL.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[40,4],"class_list":["post-667","post","type-post","status-publish","format-standard","hentry","category-blog","tag-snowflake","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/667","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=667"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/667\/revisions"}],"predecessor-version":[{"id":668,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/667\/revisions\/668"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}