A highly interactive course for learners who are new to Snowflake or new to databases in general.
Each lesson has a short video followed by a hands-on lab with sample data. Each lesson is followed by a knowledge check so users can practice for a Module Exam of 59 questions at the end of the course.
Hands On ESSENTIALS
Lesson 1 – User Roles
After this lesson you’ll be able to answer the following questions:
- What is the functional effect of the default role setting?
- What are the four roles set up automatically by Snowflake in each Snowflake account?
- How do the navigation options vary by role?
And you’ll be able to carry out tasks like:
- Change your current role.
- Identify your default role.
Lesson 2 – Database Navigation
After this lesson, you’ll be able to carry out tasks like:
- Identify the current database, schema, and object when shown a breadcrumb trail.
- View/hide the privileges of a database or database object.
- Drill into a database or database object.
- Orient yourself in the Databases area using the tab highlight and breadcrumb in the GUI.
Lesson 3 – Data Exploration
After this lesson, you’ll be able to carry out tasks like:
- Identify the navigation tree, results/preview pane and SQL entry pane in the Worksheets area.
- Use the navigation tree to preview data from a table.
- Use a shortcut to input a fully-qualified database object name into the SQL entry pane.
- Run a simple SQL query.
- Output results to the system clipboard.
- Output results to a CSV or TSV file.
Lesson 4 – Introduction to Warehouses
After this lesson you’ll be able to answer the following questions:
- What two settings are needed to configure an elastic warehouse that are not needed for a single cluster warehouse?
- What is “code highlight” and what are the benefits to having it turned on?
And you’ll be able to carry out tasks like:
- Create a warehouse
- Edit an existing warehouse
- Toggle the Code Highlight feature off and on.
- Use the Context Menu in the Worksheets area to set the warehouse and view the current size and state of your warehouses.
- Identify your current account as multi-cluster/elastic or single cluster by viewing warehouses in the Warehouse area.
- Write a SQL statement that changes the current warehouse context.
Lesson 5 – Creating Database Objects
After this lesson, you’ll be able to carry out tasks like:
- Create a database.
- Create a table.
- Create a file format.
- Load a file using the load wizard.
And, you’ll be able to answer questions like:
- What is the difference between a column separator and a field enclosure in a Snowflake file format?
- Which of the two settings allows for custom (“other”) characters like tildes and which is limited to just a few options or “none”?
Lesson 6 – Transforming Data
After this lesson you’ll be able to answer the following questions:
- What do ETL and ELT stand for?
- What are some simple SQL functions that can be used to transform data?
And you’ll be able to carry out tasks like:
- Use the REPLACE function in a SQL statement.
- Use the CONCAT function in a SQL statement.
- Use the pipe symbol to concatenate data in a SQL statement.
- Load an external script into the Snowflake Worksheets area.
- Use comments in SQL worksheets.
Lesson 7 – Advanced Warehousing Concepts
After this lesson you’ll be able to answer the following questions:
- How do the concepts of real-world warehouses map to data warehousing?
- How do Snowflake warehouses compare to and differ from data marts?
- What is the difference between scaling up and down versus scaling in and out?
- Which scaling processes are manual and which are automated?
- What two settings can be configured for elastic warehouses that are not available for standard warehouses?
- Which Snowflake Editions have elastic warehouses?
- How is the process of adding servers to a warehouse different than adding clusters?
Lesson 8 – Data Stages
After this lesson you’ll be able to carry out tasks like:
- Create and configure and external stage (Amazon S3)
- Navigate files and query data within an external stage.
- Load data from an external stage into a table using the COPY INTO command.
- Define “stage” or “staging area” in a way that covers both real-world warehouses and data warehouses.
And, answer questions like:
- What are the two types of Snowflake Stages and which type requires a cloud services account?
- What are the three cloud services providers supported by Snowflake for use with external stages?
- What are the three components needed to configure an External Stage in Snowflake? (S3)
Lesson 9 – Data Storage Structures
| NOTE: This lesson is somewhat rudimentary for seasoned database professionals but it provides essential background information needed for Lessons 10 and 11 for those who are new to database development concepts and who may not have had the experience of working with data before the modern data deluge. |
After this lesson you’ll be able to carry out tasks like:
- Create a sequence.
- Use the Sequence Next Value function in a select statement.
- Use the Sequence Next Value function in a table column’s default value.
- Describe how a sequence can be used when assigning a unique id to a database record.
- Interpret a simplified ERD.
- Predict the values output from Sequence object, based on the initial value and interval settings.
- Match the concepts of entity, attribute and value to their storage counterparts of table, column and row.
Lesson 10 – Semi-Structured Data
After this lesson you’ll be able to answer questions like:
- What are the five semi-structured data formats that can be easily ingested into Snowflake?
- What data type is used when creating a table column that will hold semi-structured data?
- What file format setting for a semi-structured file format will separate each record into its own row?
- What do the terms DDL and DML mean, and what are some examples of each type?
And, you’ll be able to carry out tasks like:
- Create an XML file format, load XML data and query it.
- Create a JSON file format, load JSON data and query it.
Lesson 11 – Nested Semi-Structured Data
After this lesson you’ll be able to carry out tasks like:
- Describe the general structure of Twitter data, including the concept of nested entities.
- Describe how an understanding of object models are necessary to use PATH statements in JSON queries.
- Describe the role of the FLATTEN function for saving development time and resources so that semi-structured data can be viewed and queried more like normalized data.
- Use the CAST function.
- Create a View.
Module Exam
Each lesson in the module includes Knowledge Check questions to ensure the learner has a chance to synthesize concepts from the video and lab. At the end of the module, learners are asked to do a final knowledge check that covers the same concepts. This delayed but comprehensive exam helps the user retain information from the various modules and increases the likelihood they will feel capable of putting all the concepts into practice.
Snowflake University
Snowflake University is your top resource in learning the fundamentals of our revolutionary cloud data warehouse, including the Hands On Essentials training.
