Hands-On Essentials – Badge 1: Data Warehousing
DW 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.
DW Essentials 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.
DW Essentials 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.
DW Essentials 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.
DW Essentials 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”?
DW Essentials 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.
DW Essentials 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?
DW Essentials 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)
DW Essentials Lesson 9 – Data Storage Structures
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.
DW Essentials 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.
DW Essentials 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.
