Welcome to the Powered by Tasty Bytes - Zero to Snowflake Quickstart focused on Transformation!
Within this Quickstart we will walk through a large set of Snowflake functionality covering key features like Zero Copy Cloning and Time-Travel to deliver on a Tasty Bytes business requirement.
Within this Quickstart we will follow a Tasty Bytes themed story via a Snowsight SQL Worksheet with this page serving as a side by side guide complete with additional commentary, images and documentation links.
This section will walk you through logging into Snowflake, Creating a New Worksheet, Renaming the Worksheet, Copying SQL from GitHub, and Pasting the SQL we will be leveraging within this Quickstart.
As part of Tasty Bytes truck fleet analysis, our developer has been tasked with adding a calculated Truck Age Column to our Truck table.
Being a great developer, we know we cannot develop against a Production table, so we first need to create a Development environment that mimics Production.
Thanks to Snowflake's unique architecture, we can instantly create a snapshot of our production
raw_pos.truck using CLONE functionality and name it
Let's now run our next set our queries to set our
tasty_dev role context and create the table clone noting here that we do not need to set Warehouse context since cloning does not require one.
USE ROLE tasty_dev; CREATE OR REPLACE TABLE frostbyte_tasty_bytes.raw_pos.truck_dev CLONE frostbyte_tasty_bytes.raw_pos.truck;
With our Zero Copy Clone instantly available we can now begin to develop against it without any fear of impacting production. However, before we make any changes let's first run some simple queries against it and test out Snowflake's Result Set Cache.
Now that we are going to query our Table, we will need to use our
Let's kick off the next two queries with the second statement producing an result set consisting of our trucks, their years, make and models while making sure we ORDER BY our
USE WAREHOUSE tasty_dev_wh; SELECT t.truck_id, t.year, t.make, t.model FROM frostbyte_tasty_bytes.raw_pos.truck_dev t ORDER BY t.truck_id;
To test Snowflake's Result Set Cache, the next query we run will be identical to what we just ran. However, we will now take things a step further and access the Query Profile showcasing this query returned results instantly as the the results came from our Result Set Cache.
After executing the below query, follow the steps in the screenshot to access the Query Profile. From the Query Profile we will see a single node noting
QUERY RESULT REUSE.
SELECT t.truck_id, t.year, t.make, t.model FROM frostbyte_tasty_bytes.raw_pos.truck_dev t ORDER BY t.truck_id;
Based on our output above we first need to address the typo in those Ford_ records we saw in our
make Column. From there, we can begin to work on our calculation that will provide us with the age of each truck.
To begin this section, let's make sure we correct the typo by executing our next query which leverages UPDATE to change rows in our
truck_dev WHERE the make is equal to Ford_.
UPDATE frostbyte_tasty_bytes.raw_pos.truck_dev SET make = 'Ford' WHERE make = 'Ford_';
With the typo handled, we can now calculate the age in years of the trucks within our fleet. Please execute the next query where we will see YEAR and CURRENT_DATE leveraged to assist in this calculation.
SELECT t.truck_id, t.year, t.make, t.model, (YEAR(CURRENT_DATE()) - t.year) AS truck_age_year FROM frostbyte_tasty_bytes.raw_pos.truck_dev t;
With our Truck Age in Years calculation done and dusted, let's now add a new Column to our Cloned Table to support it and finish things off by updating the Column to reflect the calculated values.
To start, please execute the next query which uses ALTER TABLE... ADD COLUMN to create an empty
truck_age column of Data Type NUMBER to our
This query will provide a
Statement executed successfully result.
ALTER TABLE frostbyte_tasty_bytes.raw_pos.truck_dev ADD COLUMN truck_age NUMBER(4);
With the column in place, we can kick off the next query which will UPDATE the new, empty
truck_age column using the Truck Age calculation we built in the previous section.
UPDATE frostbyte_tasty_bytes.raw_pos.truck_dev t SET truck_age = (YEAR(CURRENT_DATE()) / t.year);
After successfully updating the data, let's now run a quick query against the table to see how things look in our
SELECT t.truck_id, t.year, t.truck_age FROM frostbyte_tasty_bytes.raw_pos.truck_dev t;
Uh oh! Thank goodness we were smart developers and didn't do this sort of thing blindly in production.
It looks like we messed up the
truck_age calculation and had it doing division instead of subtraction. We will need to resolve this in our next section.
Although we made an mistake, Snowflake has many features that can help get us out of trouble here. The process we will take will leverage Query History, SQL Variables and Time Travel to revert our
truck_dev Table back to what it looked like prior to that incorrect pdate statement.
To start our recovery process, kick off the next query which will use the Snowflake QUERY_HISTORY function to retrieve a list of all update statements we have made against our
SELECT query_id, query_text, user_name, query_type, start_time FROM TABLE(frostbyte_tasty_bytes.information_schema.query_history()) WHERE 1=1 AND query_type = 'UPDATE' AND query_text LIKE '%frostbyte_tasty_bytes.raw_pos.truck_dev%' ORDER BY start_time DESC;
As expected, we see our typo correction as well as our bad calculation update and their associated unique query_id's. Please run the next query which creates a
query_id SQL Variable that we will use to revert our changes via Time-Travel in the next step.
After execution you will recieve a
Statement executed successfully result.
SET query_id = ( SELECT TOP 1 query_id FROM TABLE(frostbyte_tasty_bytes.information_schema.query_history()) WHERE 1=1 AND query_type = 'UPDATE' AND query_text LIKE '%SET truck_age = (YEAR(CURRENT_DATE()) / t.year);' ORDER BY start_time DESC );
With our bad query_id stored as a Variable, we can execute the next query which will replace our
truck_dev Table with what it looked like BEFORE the incorrect query_id statement using Time-Travel.
CREATE OR REPLACE TABLE frostbyte_tasty_bytes.raw_pos.truck_dev AS SELECT * FROM frostbyte_tasty_bytes.raw_pos.truck_dev BEFORE(STATEMENT => $query_id);
Please refer to the list below for the other Time-Travel Statement options available.
truck_dev Table back to the state it was before our incorrect update statement, we can now make sure the column is correctly updated. From there we will promote our Table with the correct calculation to Production to complete our assigned task.
Using the same process as before, please run the next query making sure we now double check we are using subtraction instead of division.
UPDATE frostbyte_tasty_bytes.raw_pos.truck_dev t SET truck_age = (YEAR(CURRENT_DATE()) - t.year);
With everything complete in
truck_dev please kick off the next two queries where we first assume the more privileged
sysadmin role. As a
sysadmin the second query utilizes ALTER TABLE... SWAP WITH to promote our
truck_dev table to
truck and vice versa.
Once complete you will recieve a
Statement executed successfully. result.
USE ROLE sysadmin; ALTER TABLE frostbyte_tasty_bytes.raw_pos.truck_dev SWAP WITH frostbyte_tasty_bytes.raw_pos.truck;
To confirm our process was successful, let's now take a look at the Production
truck table so we can validate the swap was successful and the
truck_age results are valid.
SELECT t.truck_id, t.year, t.truck_age FROM frostbyte_tasty_bytes.raw_pos.truck t WHERE t.make = 'Ford';
We can officially say our developer has completed their assigned task. With the
truck_age column in place and correctly calulated, our
sysadmin can clean up the left over Table and sign off for the day.
To remove the Table from our Database, please execute the next query which leverages DROP TABLE.
DROP TABLE frostbyte_tasty_bytes.raw_pos.truck;
Uh oh!! That result set shows that even our
sysadmin can make mistakes. We incorrectly dropped production
truck and not development
truck_dev! Thankfully, Snowflake's Time-Travel can come to the rescue again.
Hurry up and run the next query before any systems are impacted which will UNDROP the
UNDROP TABLE frostbyte_tasty_bytes.raw_pos.truck;
Alright, now let's officially close things out by running the final query to correctly drop
DROP TABLE frostbyte_tasty_bytes.raw_pos.truck_dev;
Fantastic work! You have successfully completed the Tasty Bytes - Zero to Snowflake - Transformation Quickstart.
By doing so you have now:
If you would like to re-run this Quickstart please leverage the Reset scripts in the bottom of your associated Worksheet.
To continue your journey in the Snowflake Data Cloud, please now visit the link below to see all other Powered by Taste Bytes - Quickstarts available to you.