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 Snowflakes unique architecture, we can instantly create a snapshot of our production raw_pos.truck
using CLONE functionality and name it raw_pos.truck_dev
.
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 Snowflakes Result Set Cache.
Now that we are going to query our Table, we will need to use our tasty_dev_wh
Warehouse.
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 truck_id
Column.
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 Snowflakes 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 truck_dev
table.
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 truck_age
column.
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 truck_dev
Table.
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.
With our 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, Snowflakes Time-Travel can come to the rescue again.
Hurry up and run the next query before any systems are impacted which will UNDROP the truck
table.
UNDROP TABLE frostbyte_tasty_bytes.raw_pos.truck;
Alright, now let's officially close things out by running the final query to correctly drop truck_dev
.
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.