Snowflake Data Sampling: A Complete Guide

Working with millions or billions of records in production environments can be expensive and time-consuming, especially during development and testing. This is where data sampling becomes invaluable. Let me walk you through everything you need to know about data sampling in Snowflake.

What is Data Sampling?

Data sampling is the process of selecting a subset of records from a table rather than working with the entire dataset. Think of it as taking a representative slice of your data that’s large enough to be meaningful but small enough to be practical.

Why Use Data Sampling?

There are several compelling reasons to implement data sampling in your workflow:

Query Building and Testing: When developing queries, you don’t need millions of records to verify syntax and logic. A small sample dataset allows you to iterate quickly and catch errors early.

Data Analysis and Understanding: After loading data from cloud storage or internal stages into Snowflake tables, you often need to understand the data structure and content. A sample provides this insight without the overhead of scanning entire tables.

Cost Optimization: This is perhaps the most important benefit. In production environments, running queries against billions of records with large warehouses (medium, large, or extra-large) can quickly rack up compute costs. By copying sample data to a development environment and using smaller warehouses, you can significantly reduce these costs. Remember, storage is considerably cheaper than compute in Snowflake.

Sampling Methods in Snowflake

Snowflake supports two primary sampling methods, each suited to different scenarios:

1. Bernoulli (Row) Sampling

With Bernoulli sampling, each row has an independent probability of being included in the sample. If you specify 10%, each row has a 10% chance of selection.

Characteristics:

• Scans the entire table row by row

• Provides approximately the specified percentage of data

• Best suited for smaller tables

• More predictable in terms of sample size

Example: If you have 1 million records and apply Bernoulli sampling at 10%, you’ll get approximately 100,000 records in your sample.

2. System (Block) Sampling

System sampling works at the micro-partition level rather than individual rows. When you specify 10%, Snowflake selects 10% of the micro-partitions and includes all data from those partitions.

Characteristics:

• Operates on entire blocks (micro-partitions)

• Faster than row sampling for large tables

• Less precise in terms of exact percentage

• Best suited for larger tables

Example: If your data is stored across 600 micro-partitions and you request 10%, Snowflake will select data from 60 micro-partitions (10% of 600). The exact number of rows will depend on how data is distributed across those partitions.

The Syntax

Here’s the basic syntax for data sampling in Snowflake:

SELECT columns
FROM table_name
SAMPLE [sampling_method] (probability | number ROWS)
[SEED(number)]

Key Components:

• SAMPLE or TABLESAMPLE: Both keywords work identically

• sampling_method: Either BERNOULLI/ROW or SYSTEM/BLOCK

• probability: A number representing the percentage (e.g., 10 for 10%)

• number ROWS: For fixed-size samples (e.g., 1000 ROWS)

• SEED: Optional parameter for reproducible sampling

Practical Examples

Getting a Percentage of Data

-- Get approximately 10% of rows using Bernoulli
SELECT * FROM customers
SAMPLE ROW (10);

-- Get data from 20% of blocks using System
SELECT * FROM customers
SAMPLE BLOCK (20);

Getting a Fixed Number of Rows

-- Get exactly 1000 rows
SELECT * FROM customers
SAMPLE (1000 ROWS);

Using Seeds for Reproducibility

The SEED parameter is crucial when you need consistent results across multiple queries:

-- This will always return the same dataset
SELECT * FROM customers
SAMPLE SYSTEM (10) SEED(111);

The seed can be any integer between 0 and 2,147,483,647. As long as the underlying table hasn’t changed, using the same seed will return identical results.

Creating Sample Tables

For development environments, you’ll often want to create dedicated sample tables:

-- Create a sample table with 10% of data
CREATE TABLE customers_sample AS
SELECT * FROM customers
SAMPLE ROW (10);

-- Create a sample with fixed rows and reproducibility
CREATE TABLE customers_dev AS
SELECT * FROM customers
SAMPLE SYSTEM (5) SEED(123);

Sampling vs. Cloning: When to Use Which?

You might wonder why you’d use sampling when Snowflake offers zero-copy cloning. Here’s the key difference:

Cloning: Creates a full copy without additional storage (initially), but queries against cloned tables still touch all the underlying data. Running queries on a cloned production table with a billion records using a small development warehouse will be slow and expensive.

Sampling: Creates a smaller physical dataset. Queries run faster with smaller warehouses, reducing compute costs significantly. While this does consume additional storage, storage costs are minimal compared to compute costs.

Rule of thumb: Use sampling when you need a smaller dataset for development or testing. Use cloning when you need complete data with metadata preservation.

Real-World Scenario

Let’s say you have a production table with 4 million records stored across 600 micro-partitions, and you need 10% for testing:

Using Bernoulli (Row):

• Scans all 4 million records

• Returns approximately 400,000 rows

• Predictable sample size

Using System (Block):

• Selects 60 micro-partitions (10% of 600)

• Returns all data from those 60 partitions

• Faster execution but less precise percentage

Best Practices

1. Choose the right method: Use Bernoulli for smaller tables and precise percentages. Use System for large tables where speed matters more than precision.

2. Use seeds in development: When building queries or analyzing data over time, use seeds to ensure consistency.

3. Start small: Begin with a small sample (1-5%) and increase only if needed.

4. Document your samples: Keep track of which seed values you’ve used and when samples were created.

5. Refresh periodically: As production data evolves, refresh your sample tables to maintain relevance.

Special Cases

Empty sample: SELECT * FROM table SAMPLE (0) returns no rows (rarely useful in practice).

Full table: SELECT * FROM table SAMPLE (100) returns all rows, essentially the same as a regular SELECT.

Conclusion

Data sampling is a powerful feature in Snowflake that balances the need for realistic data with the practical constraints of cost and performance. By understanding when and how to use Bernoulli versus System sampling, and leveraging features like seeds for reproducibility, you can create efficient development workflows that keep compute costs under control while maintaining data quality.

The key is matching your sampling strategy to your specific needs: use smaller samples for syntax checking, larger samples for performance testing, and always consider the trade-off between precision and execution speed when choosing your sampling method.

---

Have you implemented data sampling in your Snowflake workflows? What strategies have worked best for your team? Share your experiences in the comments below.