{

"metadata": {
"kernelspec": {
"display_name": "Streamlit Notebook",
"name": "streamlit"
}
},
"nbformat_minor": 5,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"id": "3775908f-ca36-4846-8f38-5adca39217f2",
"metadata": {
"collapsed": false,
"name": "lab_7_check_your_code",
"resultHeight": 259
},
"source": [
"# Lab 7: Check Your Code\n",
"\n",
"## Preparing for Code Checks\n",
"\n",
"To begin, let's grab **context information** we will use throughout this
lab. \n",
"\n",
"- Click the **Start** button to activate this notebook.\n",
"\n",
"- Run the following Python cell."
]
},
{
"cell_type": "markdown",
"id": "1e57cc1e-a123-48cd-87a5-4157d85d9f84",
"metadata": {
"collapsed": false,
"name": "cell2",
"resultHeight": 66
},
"source": [
"#### :warning: Each time a new session is started for this notebook, you need
to rerun the cell below to configure \"variables\" for use in later
cells. :warning:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "57d4c317-294a-4131-9d03-7d5b8d0bb17c",
"metadata": {
"collapsed": false,
"language": "python",
"name": "cell3",
"resultHeight": 0
},
"outputs": [],
"source": [
"from [Link] import get_active_session\n",
"session = get_active_session()\n",
 "user = session.get_current_user().strip('\"')\n",
"your_db = user + '_DB'\n",
"print('Your current CONTEXT information:')\n",
"print('---------------------------------')\n",
"print(session)\n",
"print('Your current USER is ' + user)"
]
},
{
"cell_type": "markdown",
"id": "8fba1c3a-eae9-4dd2-afb9-7f89dfdcc9d4",
"metadata": {
"collapsed": false,
"name": "cell4",
"resultHeight": 264
},
"source": [
"### Using the INFORMATION_SCHEMA to Query Metadata 🥋\n",
"\n",
"The word \"metadata\" means \"data about data.\" \n",
"\n",
"The `INFORMATION_SCHEMA` created in every Snowflake Database holds metadata.
In other words, it holds statistics about the number of databases, schemas, tables,
views, and more. It also holds data about the object names and other object
details. \n",
"\n",
"We can use the `INFORMATION_SCHEMA` to double-check our work and ensure we
completed the tasks correctly. \n",
"\n",
"Let's get a readout of all the schemas present in the
**(animal)_GARDEN_PLANTS** database you have been working with by querying its
`INFORMATION_SCHEMA`."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1aa25ac6-8fb5-476b-a6ef-fcbd8402bbe7",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell5",
"resultHeight": 0
},
"outputs": [],
"source": [
"SELECT * \n",
"FROM {{user}}_garden_plants.information_schema.schemata;"
]
},
{
"cell_type": "markdown",
"id": "ca373c80-58bb-4b45-bd4a-923b8e2b6489",
"metadata": {
"collapsed": false,
"name": "cell6",
"resultHeight": 232
},
"source": [
 "### Using Code to Check Your Work 🥋 \n",
"\n",
"You were asked to set up 3 schemas in your **(animal)_GARDEN_PLANTS**
database. You were also asked to delete a schema. Let's run some code to check if
those tasks have been completed.\n",
"\n",
"💡 **TIP**: The code in the following check makes use of [CTE (common table
expression)]([Link] structures. You
can think of a CTE as a \"temporary view\" that can be used in a statement. CTEs
are particularly useful for breaking down complex SQL statements, making them more
readable and easier to manage. By organizing logic into distinct, reusable parts,
CTEs simplify the query structure, improve clarity, and enhance maintainability."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "4580556b-712b-46b4-9fed-6fd0eaf275cd",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell7",
"resultHeight": 0
},
"outputs": [],
"source": [
"WITH schema_check_1 AS (\n",
" -- do the following three schemas exist?\n",
" SELECT COUNT(*) AS count_1\n",
" FROM {{user}}_garden_plants.information_schema.schemata\n",
" WHERE schema_name IN ('FLOWERS','VEGGIES','FRUITS')\n",
"),\n",
"schema_check_2 AS ( \n",
" -- the following schema SHOULD NOT exist (count of zero)\n",
" SELECT COUNT(*) AS count_2\n",
" FROM {{user}}_garden_plants.information_schema.schemata\n",
" WHERE schema_name = ('PUBLIC')\n",
") \n",
"SELECT IFF((count_1=3) AND (count_2=0),'\\u2705 Correct','\\u26D4 Incorrect.
Please review and try again') AS schema_check\n",
"from schema_check_1, schema_check_2;"
]
},
{
"cell_type": "markdown",
"id": "050b816b-17bd-42e0-9263-d4ac1a8c10af",
"metadata": {
"collapsed": false,
"name": "how_many_schemas",
"resultHeight": 519
},
"source": [
"## How many schemas does your (animal)_GARDEN PLANTS database have? \n",
"\n",
"### What Did I Do Wrong? 📓 \n",
"\n",
"Did you run the query above only to find a failure reported? Here are some
potential mistakes:\n",
"\n",
 "- You have a typo in the schema name, like \"WEGGIES\" instead
of \"VEGGIES\".\n",
"\n",
"- You put the schemas in the wrong database, like UTIL_DB, instead of
(user)_GARDEN_PLANTS.\n",
"\n",
"- You don't have your role set so that you can see the objects, as you created
them using `(animal)_LEARNER_RL`, but your worksheet is set to `PUBLIC`. \n",
"\n",
"---\n",
"\n",
"### How Can I Fix Things? 📓 \n",
"\n",
"**Typo**: `ALTER SCHEMA (animal)_GARDEN_PLANTS.WEGGIES RENAME TO
(animal)_GARDEN_PLANTS.VEGGIES;`\n",
"\n",
"**Wrong Place**: `ALTER SCHEMA DEMO_DB.VEGGIES RENAME TO
(animal)_GARDEN_PLANTS.VEGGIES;`\n",
"\n",
"**Cannot Find**: Change the Role Setting on your worksheet or transfer the
ownership of your object. "
]
},
{
"cell_type": "markdown",
"id": "7a182c83-f575-4084-acc6-76300ea780df",
"metadata": {
"collapsed": false,
"name": "cell9",
"resultHeight": 113
},
"source": [
"### Checking for Schemas by Name 🥋 \n",
"\n",
"Now let's check to see if the schemas you created have the correct names. You
might have named your schemas differently if this code doesn't return **3** rows. "
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5b1eefe2-aec9-4b20-b47c-fdce868ae9bf",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell10",
"resultHeight": 0
},
"outputs": [],
"source": [
"SELECT schema_name \n",
"FROM {{user}}_GARDEN_PLANTS.INFORMATION_SCHEMA.SCHEMATA\n",
"WHERE schema_name IN ('FLOWERS','FRUITS','VEGGIES');"
]
},
{
"cell_type": "markdown",
"id": "b330d09c-cfbb-48e9-8a5a-daac841aec13",
"metadata": {
 "collapsed": false,
"name": "check_your_work",
"resultHeight": 180
},
"source": [
"## Check Your Work 🔎\n",
"\n",
"### :mag_right: Check 1 (OB01)\n",
"\n",
"- Have you created 3 Garden Plant database schemas (**FLOWERS**, **VEGGIES**,
**FRUITS**)?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "39485ace-857f-4c74-b7b2-d269d659df14",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell12",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB01', '{{user}}');"
]
},
{
"cell_type": "markdown",
"id": "fcf3b2e6-defe-49bb-af87-c2b25e9e85c8",
"metadata": {
"collapsed": false,
"name": "cell13",
"resultHeight": 120
},
"source": [
"### :mag_right: Check 2 (OB02) 🔎\n",
"\n",
"- Have you deleted (dropped) the Garden Plant database schema named
**PUBLIC**?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8339c443-793c-4019-b64f-340c78d812eb",
"metadata": {
"codeCollapsed": false,
"collapsed": false,
"language": "sql",
"name": "cell14",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB02', '{{user}}');"
 ]
},
{
"cell_type": "markdown",
"id": "e548f303-9b26-4a58-8908-6abd3d703056",
"metadata": {
"collapsed": false,
"name": "cell15",
"resultHeight": 120
},
"source": [
"### :mag_right: Check 3 (OB03) 🔎\n",
"\n",
"- Have you created the **ROOT_DEPTH** table in the **VEGGIES** schema of your
Garden Plant database?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "53b4821e-a984-4412-9358-48298c1d96ac",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell16",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB03', '{{user}}');"
]
},
{
"cell_type": "markdown",
"id": "ad2408e0-f948-4290-a4f5-4a82b0d8262e",
"metadata": {
"collapsed": false,
"name": "cell17",
"resultHeight": 155
},
"source": [
"### Use Query History to Review Your Test Results 📓\n",
"\n",
"Snowflake retains a record of queries and statements executed in the system,
known as Query History, and provides UI and programmatic ways to access this. Query
History provides a convenient place to see queries you (or others, if you have the
privileges) have run over time.\n",
"\n",
"We can access Query History from a cell in a Snowflake Notebook."
]
},
{
"cell_type": "markdown",
"id": "523fa3b4-9a23-4ee0-95fd-e709159916c0",
"metadata": {
"collapsed": false,
"name": "cell18",
"resultHeight": 1211
 },
"source": [
"### Access Query History from a Snowflake Notebook SQL cell. 🥋\n",
"\n",
"1. Hover over the query execution time readout in the **Check 3** SQL cell
that you just ran.\n",
"1. You will see the message **View run details** appear.\n",
"1. Click on the query execution time readout and a dialog box appears.\n",
"1. Click on the blue **ID** field UUID, which contains a link to the Snowsight
Query History page.\n",
"1. This will launch the Snowsight Query History page in a new browser window.\
n",
"\n",
"![Access query history (image)]([Link]
[Link]/ob/ob_query_history_1.png)\n",
"\n",
"1. This will open into the **Query Profile** screen, where you can review the
execution steps for your query.\n",
"1. Click on the **Query Details** tab at the top of the screen.\n",
"1. Review the various details related to the query execution.\n",
"1. Check the **Results** section at the bottom of the page. \n",
"\n",
"![Query details
(image)]([Link]
ng)\n",
"\n",
":warning: **DO NOT PROCEED** past this point unless you see a green check ✅ in
the in the Results section of Query Details (Query History) for Check 3. :warning:"
]
},
{
"cell_type": "markdown",
"id": "79adf10d-672c-499b-94ad-097fc08103ee",
"metadata": {
"collapsed": false,
"name": "cell19",
"resultHeight": 222
},
"source": "### Access Query History programmatically. 🥋\n\nWe can also retrieve
Query History information using code, with options available for both Python and
SQL. \n\n`INFORMATION_SCHEMA` contains a collection of [table
functions]([Link]
that can be used to query Snowflake query history along various dimensions. In the
following example, we will use `QUERY_HISTORY_BY_USER()` to return queries
submitted by a specified user (you!) within the last seven days. \n\nWe will
identify the `DELETE` operation you ran in **Lab 6: The Load Wizard and Snowflake
Marketplace** that removed a single row from the **VEGETABLE_DETAILS** table
(...plant_name = 'Spinach' AND root_depth_code = 'D')."
},
{
"cell_type": "code",
"execution_count": null,
"id": "b8541bab-c4e1-4f36-a193-80af93ef730c",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell20",
"resultHeight": 0
},
 "outputs": [],
"source": "SELECT *\nFROM TABLE(information_schema.query_history_by_user(\n
USER_NAME => '{{user}}',\n RESULT_LIMIT => 10000\n))\nWHERE query_type =
'DELETE'\nAND execution_status = 'SUCCESS'\nORDER BY end_time DESC\nLIMIT 1;"
},
{
"cell_type": "markdown",
"id": "3835a681-3238-416d-868d-bcd321a77be3",
"metadata": {
"collapsed": false,
"name": "cell21",
"resultHeight": 108
},
"source": "Let's save the Query ID for this operation into a SQL variable that
we will make use of later in this lab.\n\n**:warning:
`INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER` and its variations only retain data for
seven days. If you run the query above outside of this window then no results will
be returned. You will need to complete the steps in Lab 6 again. :warning:**"
},
{
"cell_type": "code",
"execution_count": null,
"id": "54198646-876e-4b25-8564-317094961687",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell22",
"resultHeight": 0
},
"outputs": [],
"source": "SET delete_query_id = (\n SELECT query_id\n FROM
TABLE(information_schema.query_history_by_user(\n USER_NAME => '{{user}}',\n
RESULT_LIMIT => 10000\n ))\n WHERE query_type = 'DELETE'\n AND
execution_status = 'SUCCESS'\n ORDER BY end_time DESC\n LIMIT 1\n);\n\nSELECT
$delete_query_id;"
},
{
"cell_type": "markdown",
"id": "55e812b2-c6fc-4793-a499-48989446440c",
"metadata": {
"collapsed": false,
"name": "cell23",
"resultHeight": 120
},
"source": [
"### :mag_right: Check 4 (OB04) 🔎\n",
"\n",
"- Does your database **(animal)_UTIL_DB** contain 2 (and only 2) schemas?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2ea2e370-8392-4133-b1bf-97b5cd96fa12",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell24",
 "resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB04', '{{user}}');"
]
},
{
"cell_type": "markdown",
"id": "d5b3bc9a-658e-4ed5-a22c-ed997c0b9452",
"metadata": {
"collapsed": false,
"name": "cell25",
"resultHeight": 120
},
"source": [
"### :mag_right: Check 5 (OB05) 🔎\n",
"\n",
"- Have you created the **VEGETABLE_DETAILS** table in the **VEGGIES** schema
of the Garden Plant database?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b11fcb70-b0f3-498e-ae6a-32c7e0988298",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell26",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB05', '{{user}}');"
]
},
{
"cell_type": "markdown",
"id": "c8556419-cb57-4fc0-bf81-0860a6c12f97",
"metadata": {
"collapsed": false,
"name": "cell27",
"resultHeight": 120
},
"source": [
"### Check 6 (OB06) 🔎\n",
"\n",
"- Does your **ROOT_DEPTH** table contain **3** rows?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fbe96bb3-eb1f-4ae0-b1a1-62e5be4fff8e",
"metadata": {
"collapsed": false,
 "language": "sql",
"name": "cell28",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB06', '{{user}}'); "
]
},
{
"cell_type": "markdown",
"id": "9fe0e322-5684-4958-a160-f260eb0caec0",
"metadata": {
"collapsed": false,
"name": "cell29",
"resultHeight": 120
},
"source": [
"### Check 7 (OB07) 🔎\n",
"\n",
"- Does your **VEGETABLE_DETAILS** table contain **41** rows?\n",
"- Call the grading stored procedure to check your work."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d83ae44a-ce7c-4073-875c-0182e1d59d80",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell30",
"resultHeight": 0
},
"outputs": [],
"source": [
"CALL common_db.resources.local_grader('OB07', '{{user}}'); "
]
},
{
"cell_type": "markdown",
"id": "5f72b8fe-30b0-4f60-b7f9-d5b35540d8be",
"metadata": {
"collapsed": false,
"name": "cell31",
"resultHeight": 220
},
"source": "## Time Travel 📓\n\nSnowflake Time Travel enables accessing
historical data (i.e., data that has been changed or deleted) at any point within a
defined period. To support Time Travel, a number of [SQL
extensions]([Link]
sql-extensions) are available.\n\nWhen data in a table is modified, including
deletion of data or dropping an object containing data, Snowflake preserves the
state of the data before the update. A parameter called
`DATA_RETENTION_TIME_IN_DAYS` specifies the number of days for which this
historical data is preserved and, therefore, Time Travel operations (`SELECT`,
`CREATE` … `CLONE`, `UNDROP`) can be performed on the data."
},
{
 "cell_type": "markdown",
"id": "c7d792f9-8efe-4b0a-9112-195ab0b0bc07",
"metadata": {
"collapsed": false,
"name": "cell32",
"resultHeight": 350
},
"source": "### View a historical version of the **VEGETABLE_DETAILS** table. 🥋\
n\nWhen the **VEGETABLE_DETAILS** table was created early in **Lab 6: The Load
Wizard and Snowflake Marketplace**, it was configured with seven days of data
retention. This means any changes made to the table are retained for a seven-day
window. This allows us to navigate back in time to view the data associated with
the table at a particular point, whether chronological or before a specific
operation executed against the table.\n\nIn **Lab 6**, you then deleted a second
Spinach row from the **VEGETABLE_DETAILS** table. Earlier in this lab we identified
the Query ID associated with that operation and saved it in a local variable:
`$delete_query_id`. We can use Time Travel to view the data before this
operation. \n\nRun the following code to view and label the Spinach rows before the
`DELETE` **unioned** with the Spinach data from the current version of the table.
Note the [special syntax]([Link]
travel#querying-historical-data) used to query historical data using Time Travel:
`BEFORE( STATEMENT => $delete_query_id )` "
},
{
"cell_type": "code",
"execution_count": null,
"id": "947c70a3-ebb3-49a9-9d9d-83c56d1d9fe9",
"metadata": {
"collapsed": false,
"language": "sql",
"name": "cell33",
"resultHeight": 0
},
"outputs": [],
"source": "-- Time Travel query \nSELECT plant_name, root_depth_code,
'HISTORICAL (Time Travel)' as table_version \nFROM
{{user}}_garden_plants.veggies.vegetable_details\nBEFORE( STATEMENT =>
$delete_query_id )\nWHERE plant_name = 'Spinach'\n\nUNION\n\n-- current version
query\nSELECT plant_name, root_depth_code, 'CURRENT' as table_version\nFROM
{{user}}_garden_plants.veggies.vegetable_details\nWHERE plant_name = 'Spinach';"
},
{
"cell_type": "markdown",
"id": "d1b24261-0477-4040-9e7a-c25e279dde02",
"metadata": {
"collapsed": false,
"name": "test_your_knowledge",
"resultHeight": 169
},
"source": [
"## Test Your Knowledge. :mag_right:\n",
"\n",
"Run the following Python cell to present a Streamlit-driven widget and answer
the question about the Snowflake interfaces. You don't have to understand what this
is doing for now. Just go ahead and run the code.\n",
"\n",
"You need to answer this question correctly to proceed to the next section."
]
},
 {
"cell_type": "code",
"execution_count": null,
"id": "cdff4fb2-bd56-4849-82e4-c7243f6acd12",
"metadata": {
"collapsed": false,
"language": "python",
"name": "cell35",
"resultHeight": 0
},
"outputs": [],
"source": [
"import streamlit as st\n",
"[Link]()\n",
"question = \"What is the definition of Metadata?\"\n",
"options = [\"Pick selection below...\",\n",
" \"A) Data about meta\", \n",
" \"B) Data that is above other data\", \n",
" \"C) Data about data\"]\n",
"\n",
"user_answer = [Link](question, options, index=0)\n",
"if user_answer:\n",
" if user_answer == \"Pick selection below...\":\n",
" ''\n",
" else:\n",
" answer = '280b12ed81a2694d9619bb6fc33b2068'\n",
" response = [Link](f\"call
common_db.resources.quiz_temp('{answer}', '{user_answer}', 'False')\").collect()\
n",
" if response:\n",
" value = response[0]['QUIZ_TEMP']\n",
" [Link](value)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "67c82a3b-808c-4bcf-ac2b-f10f3ef72557",
"metadata": {
"collapsed": false,
"language": "python",
"name": "cell36",
"resultHeight": 0
},
"outputs": [],
"source": [
"[Link]()\n",
"question = \"Where does Snowflake store some of its Metadata?\"\n",
"options = [\"Pick selection below...\",\n",
" \"A) The INFORMATION_DB database of each account\", \n",
" \"B) The METADATA_SCHEMA of each database\", \n",
" \"C) The INFO_METADATA schema of each database\",\n",
" \"D) The INFORMATION_SCHEMA schema of each database\"]\n",
"\n",
"user_answer = [Link](question, options, index=0)\n",
"if user_answer:\n",
" if user_answer == \"Pick selection below...\":\n",
" ''\n",
" else:\n",
 " answer = 'b6d49c92ef7e49f514aab30a0533ab2e'\n",
" response = [Link](f\"call
common_db.resources.quiz_temp('{answer}', '{user_answer}', 'False')\").collect()\
n",
" if response:\n",
" value = response[0]['QUIZ_TEMP']\n",
" [Link](value)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "af7f295a-1399-4357-aa20-d1b6b56bb501",
"metadata": {
"collapsed": false,
"language": "python",
"name": "cell37",
"resultHeight": 0
},
"outputs": [],
"source": [
"[Link]()\n",
"question = \"If we want to check for the schemas we created, why not just look
with our own eyes to see if they are there?\"\n",
"options = [\"Pick selection below...\",\n",
" \"A) Because if we misspelled something, we might not notice the
name issue, but checking via code might catch it\", \n",
" \"B) Because we may want to automate the check for some reason\", \
n",
" \"C) Because manual checks are way more fun than coding\"]\n",
"\n",
"user_answer = [Link](question, options, index=0)\n",
"if user_answer:\n",
" if user_answer == \"Pick selection below...\":\n",
" ''\n",
" else:\n",
" answer = '8d588351a4464f11ac0525dba9142c93'\n",
" response = [Link](f\"call
common_db.resources.quiz_temp('{answer}', '{user_answer}', 'False')\").collect()\
n",
" if response:\n",
" value = response[0]['QUIZ_TEMP']\n",
" [Link](value)"
]
},
{
"cell_type": "markdown",
"id": "59acd062-d2ef-4daf-885a-24adad295928",
"metadata": {
"collapsed": false,
"name": "next_steps",
"resultHeight": 102
},
"source": [
"## Next Steps\n",
"\n",
"If your grading checks (1-7) have passed, and you have answered the
**Knowledge Test** questions correctly, then please proceed to the next Notebook
when advised by your Snowflake instructor."
 ]
}
]
}