Writing Your First SQL Query in Snowflake

Greg Trzeciak from Pragmatic Works introduces beginners to the essentials of querying data in Snowflake. This guide walks through creating your first SQL query, understanding Snowflake’s interface, and applying best practices for performance optimization.

Getting Started with Snowflake

Before diving into queries, Greg emphasizes the importance of setting up your environment:

• Ensure you are logged into a Snowflake instance as an account admin.
• Access the free trial’s sample datasets for practice—ranging from 1 TB to 10 TB.
• Create a new SQL worksheet by clicking the plus sign in the Snowflake menu or using the Projects view.

Key Elements to Check Before Querying

Greg highlights critical components that impact query success:

1. Database: Confirm which database you are using.
2. Schema: Understand the schema structure—it acts like a folder for organizing tables.
3. Table: Identify the correct table for your query.
4. Role: Ensure your role has the necessary permissions.
5. Warehouse: Note the compute warehouse size (e.g., XS, Medium) as it affects performance and cost.

Step 1: Viewing Data with SELECT

The first query uses the classic SELECT * statement:

SELECT * 
FROM SNOWFLAKE_SAMPLE_DATA.TPC.CUSTOMER
LIMIT 10;

This retrieves 10 rows from the customer table in the sample dataset, providing a quick preview without consuming excessive compute resources.

Step 2: Filtering Data with WHERE

To refine results, Greg introduces the WHERE clause and column selection:

SELECT C_NAME, C_ADDRESS
FROM SNOWFLAKE_SAMPLE_DATA.TPC.CUSTOMER
WHERE C_MKTSEGMENT = 'AUTOMOBILE';

This query filters customers by market segment and limits columns to name and address for clarity.

Step 3: Sorting Results with ORDER BY

Sorting adds structure to your output. Greg demonstrates:

SELECT C_NAME, C_ADDRESS
FROM SNOWFLAKE_SAMPLE_DATA.TPC.CUSTOMER
WHERE C_MKTSEGMENT = 'AUTOMOBILE'
ORDER BY C_CUSTKEY;

The ORDER BY clause organizes results by customer key, making data easier to interpret.

Performance Optimization Tips

Greg showcases how warehouse size impacts query speed:

• Extra Small (XS) warehouse: ~791 ms execution time.
• Medium warehouse: ~59 ms execution time.

While larger warehouses deliver faster results, they also increase costs. Balance performance needs with budget considerations.

Additional Insights

• Snowflake supports Python worksheets for advanced workflows.
• Always highlight and run the full query block to avoid syntax errors.
• Use LIMIT for quick previews and to conserve compute resources.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on SQL SnowFlake and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.