book

Snowflake Data Engineering

Name: Snowflake Data Engineering
Author: Maja Ferle
ISBN: 9781633436855

by Maja Ferle

December 2024

Beginner

368 pages

11h 5m

English

Manning Publications

Read now

Unlock full access

Snowflake Data Engineering
copyright
contents
foreword
preface
acknowledgments
about this book
Who should read this bookHow this book is organized: A road mapAbout the codeliveBook discussion forumOther online resources
about the author
about the cover illustration
Part 1 Introducing data engineering with Snowflake

1 Data engineering with Snowflake
1.1 Snowflake for data engineering1.1.1 Snowflake architecture1.1.2 Snowflake features for data engineering1.2 Responsibilities of a Snowflake data engineer1.2.1 Extracting data from source systems1.2.2 Performing data transformations1.2.3 Presenting data to downstream consumers1.2.4 Applying underlying components1.3 Building data pipelines1.4 Data engineering with Snowflake applicationsSummary
2 Creating your first data pipeline
2.1 Setting up your Snowflake account2.2 Staging a CSV file2.3 Loading data from a staged file into a target table2.3.1 Loading data from a staged file into a staging table2.3.2 Merging data from the staging table into the target table2.4 Transforming data with SQL commands2.5 Automating the process with tasksSummary
Part 2 Ingesting, transforming, and storing data
3 Best practices for data staging
3.1 Creating external stages3.1.1 Configuring a storage integration3.1.2 Creating an external stage using a storage integration3.1.3 Creating an external stage using credentials3.1.4 Loading data from staged files into a staging table3.1.5 Avoiding duplication when loading data from staged files3.1.6 Using a named file format3.2 Viewing stage metadata with directory tables3.3 Preparing data files for efficient ingestion3.3.1 File sizing recommendations3.3.2 Organizing data by path3.4 Building pipelines with external tables3.4.1 Querying data in external stages with external tables3.4.2 Using materialized views to improve query performanceSummary
4 Transforming data
4.1 Ingesting semistructured data from cloud storage4.1.1 Creating a storage integration4.1.2 Creating an external stage4.1.3 Examining the JSON structure4.1.4 Ingesting JSON data into a VARIANT data type4.2 Flattening semistructured data into relational tables4.3 Encapsulating transformations with stored procedures4.3.1 Creating a basic stored procedure4.3.2 Including a return value in a stored procedure4.3.3 Implementing exception handling in stored procedures4.4 Adding logging to stored procedures4.5 Building robust data pipelinesSummary
5 Continuous data ingestion
5.1 Comparing bulk and continuous data ingestion5.2 Preparing files in cloud storage5.2.1 Creating a storage integration5.2.2 Creating an external stage5.3 Configuring Snowpipe with cloud messaging5.3.1 Configuring event grid messages for blob storage events5.3.2 Creating a notification integration5.3.3 Creating a pipe object5.3.4 Ingesting data continuously5.3.5 Flattening the JSON structure to relational format5.4 Transforming data with dynamic tablesSummary
6 Executing code natively with Snowpark
6.1 Introducing Snowpark6.2 Creating a Snowpark procedure in a worksheet6.3 Using the SQL API from a local development environment6.3.1 Installing and configuring the local development environment6.3.2 Creating a Snowflake session6.3.3 Providing credentials in a configuration file6.3.4 Querying data and executing SQL commands6.4 Generating a date dimension in Snowpark Python6.5 Working with data frames6.6 Ingesting data from a CSV file into a Snowflake table6.7 Transforming data with data framesSummary
7 Augmenting data with outputs from large language models
7.1 Configuring external network access7.2 Calling an API endpoint from a Snowpark function7.2.1 Constructing the UDF that retrieves customer reviews7.2.2 Interpreting the results from the UDF7.2.3 Storing the customer reviews in a table7.3 Deriving customer review sentiments7.4 Interpreting order emails using LLMs to save time7.4.1 Creating a stored procedure that interprets customer emails7.4.2 Constructing the prompt7.4.3 Saving the CSV result to a table7.4.4 Evaluating the outputSummary
8 Optimizing query performance
8.1 Getting data from the Snowflake Marketplace8.2 Performing analysis of geographical data8.2.1 Snowflake’s geography functions8.2.2 Copying data from the shared database8.2.3 Viewing query execution parameters using the query profile8.3 Understanding Snowflake micro-partitions8.3.1 A conceptual example of micro-partitions8.3.2 Micro-partition pruning8.4 Optimizing storage with clustering8.4.1 Viewing clustering information8.4.2 Adding clustering keys to a table8.4.3 Monitoring the clustering process8.4.4 Viewing improved query execution after clustering8.5 Improving query performance with search optimization8.5.1 Adding search optimization to a table8.5.2 Reviewing query performance after adding search optimization8.6 General tips for improving query performance8.6.1 Writing efficient SQL queries8.6.2 Identifying queries that are candidates for optimizationSummary
9 Controlling costs
9.1 Understanding Snowflake costs9.1.1 Total Snowflake cost9.1.2 Compute resources cost9.1.3 Virtual warehouse credits9.2 Sizing virtual warehouses9.2.1 Using persisted query results9.2.2 Comparing query statistics between differently sized warehouses9.2.3 Optimizing query performance to reduce spilling9.3 Optimizing performance with data caching9.3.1 Illustrating the metadata cache9.3.2 Utilizing the warehouse cache efficiently9.4 Reducing query queuing9.4.1 Examining queuing9.4.2 Limiting concurrently running queries9.5 Monitoring compute consumptionSummary
10 Data governance and access control
10.1 Role-based access control10.1.1 System-defined roles10.1.2 Custom roles10.1.3 Designing RBAC10.2 Securing data with row access policies10.3 Protecting sensitive data with masking policiesSummary
Part 3 Building data pipelines
11 Designing data pipelines
11.1 Designing data pipelines11.1.1 Extracting data11.1.2 Comparing data pipeline patterns11.1.3 Choosing data transformation layers11.1.4 Organizing data warehouse layers11.1.5 Creating schemas with access control11.2 Building a sample data pipeline11.2.1 Implementing the extraction layer11.2.2 Implementing the staging layer11.2.3 Implementing the data warehouse layer11.2.4 Implementing the reporting layerSummary
12 Ingesting data incrementally
12.1 Comparing data ingestion approaches12.1.1 Full ingestion12.1.2 Incremental ingestion12.2 Preserving history with slowly changing dimensions12.2.1 SCD type 212.2.2 Append-only strategy12.2.3 Designing idempotent data pipelines12.3 Detecting changes with Snowflake streams12.3.1 Ingesting files from cloud storage incrementally12.3.2 Preserving history when ingesting data incrementally12.4 Maintaining data with dynamic tables12.4.1 Deciding when to use dynamic tables12.4.2 Querying historical dataSummary
13 Orchestrating data pipelines
13.1 Orchestrating with Snowflake tasks13.1.1 Creating a schema to store the orchestration objects13.1.2 Designing the orchestration tasks13.1.3 Creating tasks with dependencies13.2 Sending email notifications13.3 Orchestrating with task graphs13.3.1 Designing the task graph13.3.2 Creating the root task13.3.3 Creating the finalizer task13.3.4 Viewing the task graph13.4 Monitoring data pipeline execution13.4.1 Adding logging functionality to tasks13.4.2 Summarizing logging information in an email notification13.5 Troubleshooting data pipeline failuresSummary
14 Testing for data integrity and completeness
14.1 Data testing methods14.1.1 Performing data testing as steps in the pipeline14.1.2 Performing data testing independently of the pipeline14.2 Incorporating data testing steps in the pipeline14.2.1 Constructing the partner data quality task14.2.2 Constructing the product data quality task14.2.3 Executing the pipeline with the data testing tasks14.3 Applying the Snowflake data metric functions14.3.1 System-defined data metric functions14.3.2 User-defined data metric functions14.3.3 Viewing data metric function details14.4 Alerting users when data metrics exceed thresholds14.5 Detecting data volume anomalies14.5.1 Generating random data14.5.2 Displaying data as a line chart in Snowsight14.5.3 Working with the anomaly detection modelSummary
15 Data pipeline continuous integration
15.1 Separating the data engineering environments15.2 Database change management15.2.1 Comparing the imperative and the declarative approach to DCM15.2.2 Organizing the code in the repository15.3 Configuring Snowflake to use Git15.3.1 Creating a Git repository stage15.3.2 Executing commands from a Git repository stage15.4 Using the Snowflake CLI command line interface15.4.1 Installing and configuring Snowflake CLI15.4.2 Executing scripts with Snowflake CLI15.4.3 Continuous integration with Snowflake CLI15.5 Connecting to Snowflake securely15.5.1 Configuring key-pair authentication15.6 Applying what we learned in real-world scenariosSummary
appendix A Configuring your Snowflake environment
A.1 Signing up for a Snowflake free trial accountA.2 Installing and configuring Snowflake CLI
appendix B Snowflake objects used in the examples
B.1 Ingesting and transforming data from CSV files (chapter 2)B.2 Ingesting data from a cloud storage provider (chapter 3)B.3 Ingesting and flattening semi-structured data (chapter 4)B.4 Continuous data ingestion and dynamic tables (chapter 5)B.5 Executing code natively with Snowpark (chapter 6)B.6 Calling API endpoints and LLM functions (chapter 7)B.7 Optimizing query performance and controlling cost (chapters 8 and 9)B.8 Data governance and access control (chapter 10)B.9 Designing data pipelines (chapter 11)B.10 Ingesting data incrementally (chapter 12)B.11 Orchestrating data pipelines (chapter 13)B.12 Testing for data integrity and completeness (chapter 14)B.13 Data pipeline continuous integration (chapter 15)
index

Content preview from Snowflake Data Engineering

10 Data governance and access control

This chapter covers

Snowflake role-based access control
Securing data with row access policies
Protecting sensitive data with masking policies

Data stored in various repositories such as source systems, data lakes, data warehouses, reporting solutions, or data products can contain confidential or sensitive information only authorized users can access. Data engineers are responsible for including data governance and access control requirements when building data pipelines to ensure users see only the data they are authorized to see. Snowflake supports role-based access control and data governance features, such as row access policies and masking policies, that limit data access to authorized users. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781633436855Publisher Support Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Snowflake Data Engineering

by Maja Ferle