Book Image

Snowflake Cookbook

By : Hamid Mahmood Qureshi, Hammad Sharif
5 (1)
Book Image

Snowflake Cookbook

5 (1)
By: Hamid Mahmood Qureshi, Hammad Sharif

Overview of this book

Snowflake is a unique cloud-based data warehousing platform built from scratch to perform data management on the cloud. This book introduces you to Snowflake's unique architecture, which places it at the forefront of cloud data warehouses. You'll explore the compute model available with Snowflake, and find out how Snowflake allows extensive scaling through the virtual warehouses. You will then learn how to configure a virtual warehouse for optimizing cost and performance. Moving on, you'll get to grips with the data ecosystem and discover how Snowflake integrates with other technologies for staging and loading data. As you progress through the chapters, you will leverage Snowflake's capabilities to process a series of SQL statements using tasks to build data pipelines and find out how you can create modern data solutions and pipelines designed to provide high performance and scalability. You will also get to grips with creating role hierarchies, adding custom roles, and setting default roles for users before covering advanced topics such as data sharing, cloning, and performance optimization. By the end of this Snowflake book, you will be well-versed in Snowflake's architecture for building modern analytical solutions and understand best practices for solving commonly faced problems using practical recipes.

Related Content you might be interested in

Current Title:

Small book image
Snowflake Cookbook
Hamid Mahmood Qureshi | Hammad Sharif
Feb, 2021 | 330 pages
Small book image
Data Modeling with Snowflake
Serge Gershkovich
May, 2023 | 324 pages
Small book image
The Ultimate Guide to Snowpark
Shankar Narayanan SGS | Vivekanandan SS
May, 2024 | 254 pages
Small book image
Amazon Redshift Cookbook
Mrs. Shruti Worlikar | Thiyagarajan Arumugam | Ms. Harshida Patel
Jul, 2021 | 384 pages
Table of Contents (12 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the color images
Conventions used
Sections
Get in touch
Reviews
1
Chapter 1: Getting Started with Snowflake
Chapter 1: Getting Started with Snowflake
Technical requirements
Creating a new Snowflake instance
Creating a tailored multi-cluster virtual warehouse
Using the Snowflake WebUI and executing a query
Using SnowSQL to connect to Snowflake
Connecting to Snowflake with JDBC
Creating a new account admin user and understanding built-in roles
Free Chapter
2
Chapter 2: Managing the Data Life Cycle
Chapter 2: Managing the Data Life Cycle
Technical requirements
Managing a database
Managing a schema
Managing tables
Managing external tables and stages
Managing views in Snowflake
3
Chapter 3: Loading and Extracting Data into and out of Snowflake
Chapter 3: Loading and Extracting Data into and out of Snowflake
Technical requirements
Configuring Snowflake access to private S3 buckets
Loading delimited bulk data into Snowflake from cloud storage
Loading delimited bulk data into Snowflake from your local machine
Loading Parquet files into Snowflake
Making sense of JSON semi-structured data and transforming to a relational view
Processing newline-delimited JSON (or NDJSON) into a Snowflake table
Processing near real-time data into a Snowflake table using Snowpipe
Extracting data from Snowflake
4
Chapter 4: Building Data Pipelines in Snowflake
Chapter 4: Building Data Pipelines in Snowflake
Technical requirements
Creating and scheduling a task
Conjugating pipelines through a task tree
Querying and viewing the task history
Exploring the concept of streams to capture table-level changes
Combining the concept of streams and tasks to build pipelines that process changed data on a schedule
Converting data types and Snowflake's failure management
Managing context using different utility functions
5
Chapter 5: Data Protection and Security in Snowflake
Chapter 5: Data Protection and Security in Snowflake
Technical requirements
Setting up custom roles and completing the role hierarchy
Configuring and assigning a default role to a user
Delineating user management from security and role management
Configuring custom roles for managing access to highly secure data
Setting up development, testing, pre-production, and production database hierarchies and roles
Safeguarding the ACCOUNTADMIN role and users in the ACCOUNTADMIN role
6
Chapter 6: Performance and Cost Optimization
Chapter 6: Performance and Cost Optimization
Technical requirements
Examining table schemas and deriving an optimal structure for a table
Identifying query plans and bottlenecks
Weeding out inefficient queries through analysis
Identifying and reducing unnecessary Fail-safe and Time Travel storage usage
Projections in Snowflake for performance
Reviewing query plans to modify table clustering
Optimizing virtual warehouse scale
7
Chapter 7: Secure Data Sharing
Chapter 7: Secure Data Sharing
Technical requirements
Sharing a table with another Snowflake account
Sharing data through a view with another Snowflake account
Sharing a complete database with another Snowflake account and setting up future objects to be shareable
Creating reader accounts and configuring them for non-Snowflake sharing
Keeping costs in check when sharing data with non-Snowflake users
8
Chapter 8: Back to the Future with Time Travel
Chapter 8: Back to the Future with Time Travel
Technical requirements
Using Time Travel to return to the state of data at a particular time
Using Time Travel to recover from the accidental loss of table data
Identifying dropped databases, tables, and other objects and restoring them using Time Travel
Using Time Travel in conjunction with cloning to improve debugging
Using cloning to set up new environments based on the production environment rapidly
9
Chapter 9: Advanced SQL Techniques
Chapter 9: Advanced SQL Techniques
Technical requirements
Managing timestamp data
Shredding date data to extract Calendar information
Unique counts and Snowflake
Managing transactions in Snowflake
Ordered analytics over window frames
Generating sequences in Snowflake
10
Chapter 10: Extending Snowflake Capabilities
Chapter 10: Extending Snowflake Capabilities
Technical requirements
Creating a Scalar user-defined function using SQL
Creating a Table user-defined function using SQL
Creating a Scalar user-defined function using JavaScript
Creating a Table user-defined function using JavaScript
Connecting Snowflake with Apache Spark
Using Apache Spark to prepare data for storage on Snowflake
Why subscribe?
11
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Leave a review - let other readers know what you think

Weeding out inefficient queries through analysis

We will learn about techniques to identify possible inefficient queries through this recipe. The identified inefficient queries can then be re-designed to be more efficient.

Getting ready

You will need to be connected to your Snowflake instance via the web UI or the SnowSQL client to execute this recipe.

How to do it…

We will be querying the QUERY_HISTORY Materialized View (MV) under the SNOWFLAKE database and ACCOUNT_USAGE schema to identify queries that have taken a long time or scanned a lot of data. Based on that result set, we can identify which queries are potentially inefficient. The steps for this recipe are as follows:

  1. We will start by simply selecting all rows from the QUERY_HISTORY view and order them by the time taken to execute:
    USE ROLE ACCOUNTADMIN;
USE SNOWFLAKE;
SELECT QUERY_ID, QUERY_TEXT, EXECUTION_TIME,USER_NAME 
FROM SNOWFLAKE.ACCOUNT_USAGE.query_history 
ORDER BY EXECUTION_TIME DESC;

    You...

Previous Section
End of Section 5
Next Section