Skip to content
English
  • There are no suggestions because the search field is empty.

Accessing Self-Serve Data using custom SQL

This guide will help you access self-serve data in Snowflake by writing custom SQL queries. Unlike curated data, self-serve data provides a flexible structure, allowing you to explore and filter based on your unique needs. Let’s dive in!

Understanding Self-Serve Data in Snowflake

 Note: To protect your company's privacy, the generic, example names used in this document won't match what you see in Snowflake, but the principles outlined here are analogous, and can be replaced by your company's specific details.

Common Data Structure

Here’s an example of what self-serve data might look like in Snowflake:

  • Database: selfserve_db
  • Schema: public
  • Views: Commonly named after the data they store, such as:
    • transactions: Contains details of purchases or orders.
    • users: Contains user profile information.
    • products: Contains product catalog details.

The opposite of a self-serve data source is a curated data source, which tend to have very specific names that specify the data scope and timeframe, such as lob_consumptions_prior_month.

Common Fields for Filtering

When querying self-serve data, you’ll often use specific fields in the WHERE clause to narrow down results. Common fields include:

  • Date ranges: e.g., transaction_date, created_at
  • Identifiers: e.g., user_id, transaction_id, product_id
  • Categories: e.g., product_category, region

Step 1: Open a Worksheet

  1. Log in to your Snowflake Reader Account.
  2. Navigate to the Worksheets tab.
    Snowflake worksheets
  3. Open a new worksheet or an existing one for your queries.

Step 2: Explore the Data Structure

  1. Use the Data Explorer on the left-hand side to find the database and schema.
  2. Drill down into the tables to view their structure:
    • Click on a table name to see its columns, data types, and any descriptions (if available).
    • For example, the transactions table might include:
      • transaction_id (unique identifier)
      • transaction_date (date of the transaction)
      • total_amount (total value of the transaction)

Step 3: Write a Custom SQL Query

Start with a basic query to retrieve all data from a table:

SELECT *
FROM selfserve_db.public.transactions;

Add Filters to Narrow Results

To refine your data, add a WHERE clause:

  1. Filter by date range:

     
    SELECT *
    FROM selfserve_db.public.transactions
    WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

  2. Filter by specific user or product:

     
    SELECT *
    FROM selfserve_db.public.transactions
    WHERE user_id = '12345';

  3. Combine filters with AND or OR:

     
    SELECT *
    FROM selfserve_db.public.transactions
    WHERE transaction_date > '2023-01-01'
    AND total_amount > 100;

Select Specific Columns

To make your query more efficient and readable, request only the columns you need:

SELECT transaction_id, transaction_date, total_amount
FROM selfserve_db.public.transactions
WHERE total_amount > 100;

Step 4: Run the Query

  1. Click the Run button or press Ctrl + Enter / Cmd + Enter.
  2. Review the query results in the Results pane to ensure they meet your expectations.

Step 5: Review and Export

Tips for Using Self-Serve Data

  1. Start Simple: If you’re new to SQL, begin with small, simple queries before adding complex filters or joining tables.
  2. Explore the Schema: Use Snowflake’s Data Explorer to familiarize yourself with the table structure and column descriptions.
  3. Work with Your Team: If you’re unsure which fields to use or how to filter, consult with a colleague or your team for guidance.
  4. Test Filters: Always test your filters to ensure they return accurate and meaningful results.

Need Help?

If you need assistance with writing SQL queries or understanding the data structure, reach out to your team or iSeatz support. They can help you navigate self-serve data and optimize your queries for better results.

Happy querying!