Views, Secure Views, Caching & Alerts: A Practical Guide

In Snowflake, data is stored in tables, but you often need to present only a subset of that data to different users or applications. This is where views come in. A view is a virtual table defined by a SQL query. It doesn't store data itself; it just stores the query. When you query a view, Snowflake runs the underlying query and returns the result. Views are essential for security (hiding sensitive columns), simplicity (encapsulating complex joins), and consistency (providing a stable interface).

But standard views have limitations: they expose the view definition to users, and they don't cache results. Snowflake addresses these with secure views (which hide the definition) and result caching (which automatically caches query results for 24 hours). For even better performance, materialized views precompute and store results, updating them as base data changes.

Additionally, Snowflake's alerting framework allows you to monitor your warehouse usage, query performance, and data changes. You can set up alerts to send notifications when, for example, credit consumption exceeds a threshold or a specific query pattern is detected.

In this tutorial, you'll learn how to create and manage views (standard, secure, materialized), leverage result caching, and set up alerts. We'll use a sample sales database with tables: customers, orders, and order_items. By the end, you'll be able to design a secure, performant data access layer and monitor your Snowflake environment effectively.

1. Understanding Views in Snowflake

Views are virtual tables defined by a SELECT statement. They don't store data; they store the query. When you query a view, Snowflake executes the underlying query and returns the result. Views are useful for: - Security: Hide sensitive columns (e.g., salary) from certain users. - Simplicity: Encapsulate complex joins and aggregations. - Consistency: Provide a stable interface even if underlying tables change.

In Snowflake, you can create a view using CREATE VIEW. Here's an example with our sales database:

CREATE VIEW customer_orders AS SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

This view joins customers and orders, making it easy for analysts to query customer order data without writing the join each time.

Views can be updated if they meet certain conditions (e.g., no aggregations, no DISTINCT). However, most views are read-only. Snowflake also supports recursive views and views with common table expressions (CTEs).

-- Create a view that shows customer order summary
CREATE VIEW customer_order_summary AS
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Query the view
SELECT * FROM customer_order_summary WHERE total_spent > 1000;

2. Secure Views: Hiding the Definition

Standard views expose their definition to any user with the MONITOR privilege on the view or schema. This can be a security risk if the view definition reveals table names, column names, or business logic. Secure views solve this by hiding the view definition from users. Only the view owner or users with the OWNERSHIP privilege can see the definition.

To create a secure view, use the WITH SECURE clause:

CREATE SECURE VIEW employee_salary AS SELECT employee_id, name, department FROM employees WHERE role != 'HR'; -- Hides salary column and HR employees

Once created, users can query the view but cannot see the underlying SQL. They also cannot see the base table structure through the view.

Secure views also support row-level security. For example, you can create a secure view that filters rows based on the current user:

CREATE SECURE VIEW user_orders AS SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE email = CURRENT_USER());

This ensures each user only sees their own orders.

Note: Secure views have a slight performance overhead because Snowflake must enforce the security checks. However, the overhead is minimal for most use cases.

-- Create a secure view that hides salary and filters by department
CREATE SECURE VIEW v_employee_public AS
SELECT employee_id, name, department
FROM employees
WHERE department != 'HR';

-- Try to view the definition (will fail for non-owners)
SHOW VIEWS LIKE 'v_employee_public';
-- The 'text' column will be NULL for non-owners.

-- Query the view
SELECT * FROM v_employee_public;

3. Materialized Views: Precomputed Results for Performance

Materialized views (MVs) store the result of a query physically, like a table. They are automatically refreshed when base data changes (within a few minutes). MVs are ideal for expensive queries that are run frequently, such as aggregations over large tables.

Creating a materialized view:

CREATE MATERIALIZED VIEW mv_daily_sales AS SELECT order_date, SUM(amount) AS daily_total FROM orders GROUP BY order_date;

Once created, querying mv_daily_sales is much faster than running the aggregation on the base table because the result is precomputed.

Snowflake automatically maintains MVs. You can also manually refresh them:

ALTER MATERIALIZED VIEW mv_daily_sales REFRESH;

To check if an MV is up-to-date, query the AUTOMATIC_CLUSTERING or the MV's metadata.

MVs consume storage and incur compute costs for maintenance. Use them judiciously for queries that are both expensive and frequent.

-- Create a materialized view for monthly sales by product
CREATE MATERIALIZED VIEW mv_monthly_product_sales AS
SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  oi.product_id,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY month, product_id;

-- Query the materialized view
SELECT * FROM mv_monthly_product_sales WHERE month = '2024-01-01';

-- Check refresh history
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE MATERIALIZED_VIEW_NAME = 'MV_MONTHLY_PRODUCT_SALES';

4. Result Caching: Automatic Query Acceleration

Snowflake automatically caches the results of queries for 24 hours. If you run the same query again (with the same SQL text and same context), Snowflake returns the cached result instantly, bypassing computation. This is called result caching.

Result caching works for any query, including those on views. It is transparent and requires no configuration. However, there are conditions: - The query must be exactly the same (including whitespace and comments). - The underlying data must not have changed (Snowflake tracks changes at the micro-partition level). - The session context (e.g., role, warehouse) must be the same. - The query must not use non-deterministic functions like CURRENT_TIMESTAMP or RANDOM.

You can check if a query used cached results by looking at the QUERY_HISTORY view:

SELECT query_id, query_text, executed_as, cached_result FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE query_text LIKE '%customer_order_summary%';

The CACHED_RESULT column shows 'true' if the result was served from cache.

You can disable result caching for a session if needed:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

This is useful for testing or when you want to force a fresh computation.

Result caching is especially beneficial for views that are queried frequently by multiple users. It reduces warehouse usage and speeds up response times.

-- Run a query on a view
SELECT * FROM customer_order_summary WHERE total_spent > 1000;

-- Run the same query again (will use cache)
SELECT * FROM customer_order_summary WHERE total_spent > 1000;

-- Check if cache was used
SELECT query_id, query_text, cached_result
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%customer_order_summary%'
ORDER BY start_time DESC
LIMIT 2;

5. Setting Up Alerts in Snowflake

Snowflake's alerting framework allows you to create alerts that trigger when specified conditions are met. Alerts can send notifications via email, webhook, or Snowflake's notification integration. Common use cases include: - Monitoring credit usage (e.g., alert when daily credit consumption exceeds a threshold). - Monitoring query performance (e.g., alert when a query runs longer than 10 minutes). - Monitoring data changes (e.g., alert when a table has not been updated in 24 hours).

To create an alert, you need: 1. A notification integration (e.g., email or webhook). 2. A SQL condition that returns true/false. 3. An action (e.g., call a stored procedure or send a notification).

Here's an example that alerts when the total credits used in the last hour exceed 10:

CREATE OR REPLACE ALERT high_credit_usage WAREHOUSE = my_wh SCHEDULE = '60 MINUTE' IF (EXISTS ( SELECT 1 FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE start_time > DATEADD('hour', -1, CURRENT_TIMESTAMP()) GROUP BY 1 HAVING SUM(credits_used) > 10 )) THEN CALL SYSTEM$SEND_EMAIL('my_email_int', 'admin@company.com', 'High Credit Usage Alert', 'Credits exceeded 10 in the last hour.');

Alerts are managed via the ALERT command. You can enable, disable, or drop alerts.

To view alert history:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ALERT_HISTORY;

Alerts are powerful for proactive monitoring. They help you catch issues before they impact users.

-- Create a notification integration for email
CREATE NOTIFICATION INTEGRATION my_email_int
  TYPE = EMAIL
  ENABLED = TRUE;

-- Create an alert for long-running queries
CREATE OR REPLACE ALERT long_running_queries
  WAREHOUSE = my_wh
  SCHEDULE = '10 MINUTE'
  IF (EXISTS (
    SELECT 1
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE start_time > DATEADD('minute', -10, CURRENT_TIMESTAMP())
      AND execution_time > 300000000  -- 5 minutes in nanoseconds
  ))
  THEN
    CALL SYSTEM$SEND_EMAIL('my_email_int', 'dba@company.com', 'Long Running Query Alert', 'A query ran longer than 5 minutes.');

-- Enable the alert
ALTER ALERT long_running_queries RESUME;

6. Best Practices for Views, Caching, and Alerts

Here are some best practices to follow in production:

Views: - Use secure views for any view that exposes sensitive data or business logic. - Limit the number of nested views to avoid performance degradation. - Document view definitions and their purpose. - Regularly review view usage and drop unused views.

Materialized Views: - Use MVs only for queries that are both expensive and run frequently. - Monitor MV storage and refresh costs. - Set up alerts for MV refresh failures. - Consider using clustering keys on MVs if they are large and queried with filters.

Result Caching: - Be aware that caching can hide performance issues. Periodically test with caching disabled. - Use the CACHED_RESULT column in QUERY_HISTORY to understand cache hit rates. - If you need fresh data every time, consider using a materialized view or disabling cache for critical queries.

Alerts: - Start with simple alerts and gradually add complexity. - Use a dedicated warehouse for alerts to avoid impacting user workloads. - Test alerts in a non-production environment first. - Set up multiple notification channels (email, Slack webhook) for redundancy.

By following these practices, you can build a robust data access layer and monitoring system in Snowflake.

-- Example: Create a secure materialized view with clustering
CREATE SECURE MATERIALIZED VIEW mv_secure_sales
CLUSTER BY (order_date)
AS
SELECT order_date, region, SUM(amount) AS total
FROM sales
GROUP BY order_date, region;

-- Monitor MV refresh history
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY;

-- Create an alert for MV refresh failures
CREATE OR REPLACE ALERT mv_refresh_failure
  WAREHOUSE = my_wh
  SCHEDULE = '5 MINUTE'
  IF (EXISTS (
    SELECT 1
    FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
    WHERE status = 'FAILED'
      AND refresh_time > DATEADD('minute', -5, CURRENT_TIMESTAMP())
  ))
  THEN
    CALL SYSTEM$SEND_EMAIL('my_email_int', 'dba@company.com', 'MV Refresh Failure', 'A materialized view refresh failed.');