Snowflake Platform Digest

-- Summarize customer feedback themes across a product category using AI_AGG.
-- AI_AGG aggregates text from multiple rows and returns a single synthesized insight.
-- Replace YOUR_DATABASE, YOUR_SCHEMA, YOUR_TABLE, and column names as appropriate.

SELECT
    product_category,
    COUNT(*)                                AS feedback_count,

    -- AI_AGG synthesizes themes across all rows in the group
    AI_AGG(
        feedback_text,
        'Identify the top 3 recurring themes in this customer feedback.
         For each theme, provide a one-sentence description and note
         whether sentiment is positive, negative, or mixed.'
    )                                       AS theme_analysis,

    -- AI_AGG can also be used for classification summaries
    AI_AGG(
        feedback_text,
        'What percentage of this feedback relates to: pricing, quality,
         delivery, and customer service? Return as a JSON object.'
    )                                       AS category_breakdown

FROM YOUR_DATABASE.YOUR_SCHEMA.CUSTOMER_FEEDBACK

-- Filter to recent feedback for relevance
WHERE feedback_date >= DATEADD('day', -30, CURRENT_DATE())
  AND feedback_text IS NOT NULL
  AND LENGTH(feedback_text) > 20         -- filter out trivial entries

GROUP BY product_category

-- Focus on categories with enough data for meaningful analysis
HAVING COUNT(*) >= 10

ORDER BY feedback_count DESC;

-- Create a Resource Monitor that alerts at 75% consumption and suspends at 100%.
-- Must be executed by an ACCOUNTADMIN or role with CREATE RESOURCE MONITOR privilege.

CREATE OR REPLACE RESOURCE MONITOR ANALYTICS_TEAM_MONTHLY
    WITH
        -- Set monthly credit quota (adjust to your actual allocation)
        CREDIT_QUOTA = 500
        -- Reset the counter on the first of each month
        FREQUENCY = MONTHLY
        START_TIMESTAMP = IMMEDIATELY

    -- Notification-only at 75% usage — sends alert email to monitor admins
    TRIGGERS
        ON 75 PERCENT DO NOTIFY

    -- Suspend NEW queries at 90%, but allow running queries to finish
        ON 90 PERCENT DO SUSPEND

    -- Immediately suspend ALL warehouses at 100% — hard stop
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;


-- Assign the monitor to specific warehouses (can assign to multiple)
ALTER WAREHOUSE ANALYTICS_WH SET RESOURCE_MONITOR = ANALYTICS_TEAM_MONTHLY;
ALTER WAREHOUSE REPORTING_WH  SET RESOURCE_MONITOR = ANALYTICS_TEAM_MONTHLY;


-- Verify the current monitor status and remaining credit budget
SELECT
    name,
    credit_quota,
    credits_used,
    credits_used_compute,
    credits_used_cloud_services,
    ROUND((credits_used / NULLIF(credit_quota, 0)) * 100, 2) AS pct_consumed,
    suspend_at,
    suspend_immediate_at
FROM SNOWFLAKE.ACCOUNT_USAGE.RESOURCE_MONITORS
WHERE name = 'ANALYTICS_TEAM_MONTHLY';

-- Create a Snowflake-managed Iceberg table (no external volume required).
-- Requires ENTERPRISE edition or higher and must be in a region where
-- Snowflake Storage for Apache Iceberg is available (AWS, Azure - Apr 2026 preview).

CREATE OR REPLACE ICEBERG TABLE YOUR_DATABASE.YOUR_SCHEMA.EVENTS (
    event_id        STRING          NOT NULL,
    event_timestamp TIMESTAMP_NTZ   NOT NULL,
    user_id         STRING,
    event_type      STRING,
    properties      OBJECT,         -- semi-structured data stored as Iceberg struct
    partition_date  DATE            NOT NULL
)
    -- Snowflake manages the underlying storage; no EXTERNAL_VOLUME needed
    CATALOG = 'SNOWFLAKE'
    BASE_LOCATION = 'events/'       -- relative path within Snowflake managed storage

    -- Partition by date for query performance and lifecycle management
    PARTITION BY (DAY(event_timestamp))

    -- Enable automatic file compaction to reduce small file overhead
    AUTO_REFRESH = TRUE;


-- Insert data (standard DML works identically to native tables)
INSERT INTO YOUR_DATABASE.YOUR_SCHEMA.EVENTS
SELECT
    UUID_STRING()       AS event_id,
    CURRENT_TIMESTAMP() AS event_timestamp,
    user_id,
    event_type,
    properties,
    CURRENT_DATE()      AS partition_date
FROM YOUR_SOURCE_TABLE
WHERE event_date = CURRENT_DATE();


-- Query with time travel (Iceberg tables support Snowflake time travel)
SELECT COUNT(*) AS events_yesterday
FROM YOUR_DATABASE.YOUR_SCHEMA.EVENTS
    AT (OFFSET => -86400)   -- 24 hours ago in seconds
WHERE event_type = 'purchase';


-- Inspect Iceberg metadata — useful for validating partition pruning
SELECT *
FROM TABLE(
    INFORMATION_SCHEMA.ICEBERG_TABLE_FILES(
        TABLE_NAME => 'EVENTS',
        TABLE_SCHEMA => 'YOUR_SCHEMA'
    )
)
LIMIT 20;

-- Identify the most expensive queries and their partition pruning efficiency
-- from the last 7 days. Run as ACCOUNTADMIN or a role with SNOWFLAKE DB access.

WITH expensive_queries AS (
    SELECT
        query_id,
        query_text,
        warehouse_name,
        warehouse_size,
        -- Convert milliseconds to seconds for readability
        ROUND(total_elapsed_time / 1000, 2)     AS elapsed_sec,
        ROUND(execution_time / 1000, 2)          AS execution_sec,
        credits_used_cloud_services,

        -- Partition pruning metrics
        partitions_scanned,
        partitions_total,
        ROUND(
            (partitions_scanned / NULLIF(partitions_total, 0)) * 100, 1
        )                                        AS pct_partitions_scanned,

        -- Bytes scanned (cost proxy)
        ROUND(bytes_scanned / 1e9, 3)            AS gb_scanned,
        ROUND(bytes_spilled_to_local_storage / 1e9, 3) AS gb_spilled_local,
        ROUND(bytes_spilled_to_remote_storage / 1e9, 3) AS gb_spilled_remote,

        start_time,
        user_name,
        role_name,
        database_name,
        schema_name

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
      AND execution_status = 'SUCCESS'
      AND query_type IN ('SELECT', 'DML')
      AND partitions_total > 0            -- only queries that touched tables
      AND total_elapsed_time > 10000      -- queries taking more than 10 seconds
)
SELECT
    query_id,
    LEFT(query_text, 120)               AS query_preview,
    warehouse_name,
    elapsed_sec,
    gb_scanned,
    pct_partitions_scanned,
    partitions_scanned,
    partitions_total,

    -- Flag queries with poor pruning (scanning >50% of partitions on large tables)
    CASE
        WHEN pct_partitions_scanned > 50
         AND partitions_total > 100
        THEN 'Clustering candidate'
        WHEN gb_spilled_local > 0 OR gb_spilled_remote > 0
        THEN 'Memory pressure — resize warehouse'
        ELSE 'OK'
    END                                  AS recommendation,

    gb_spilled_local,
    gb_spilled_remote,
    start_time,
    user_name

FROM expensive_queries
ORDER BY gb_scanned DESC
LIMIT 25;

-- Multi-tenant Row Access Policy: users only see rows belonging to their tenant.
-- Uses a mapping table to drive access — change access by updating the mapping,
-- no policy modification or redeployment required.

-- STEP 1: Create the tenant-to-role mapping table
CREATE OR REPLACE TABLE YOUR_DATABASE.SECURITY.TENANT_ACCESS_MAP (
    tenant_id   STRING NOT NULL,
    role_name   STRING NOT NULL,
    PRIMARY KEY (tenant_id, role_name)
);

-- Populate with your tenant-role assignments
INSERT INTO YOUR_DATABASE.SECURITY.TENANT_ACCESS_MAP VALUES
    ('TENANT_ACME',    'ROLE_ACME_USERS'),
    ('TENANT_ACME',    'ROLE_PLATFORM_ADMIN'),   -- admins see all tenants
    ('TENANT_GLOBEX',  'ROLE_GLOBEX_USERS'),
    ('TENANT_GLOBEX',  'ROLE_PLATFORM_ADMIN');


-- STEP 2: Create the Row Access Policy
-- The policy function returns TRUE (show row) or FALSE (hide row)
CREATE OR REPLACE ROW ACCESS POLICY YOUR_DATABASE.SECURITY.TENANT_ROW_POLICY
AS (row_tenant_id STRING) RETURNS BOOLEAN ->
    -- ACCOUNTADMIN and SYSADMIN always see all rows
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'SYSADMIN')

    OR

    -- Check if the current role has access to this row's tenant
    EXISTS (
        SELECT 1
        FROM YOUR_DATABASE.SECURITY.TENANT_ACCESS_MAP
        WHERE tenant_id  = row_tenant_id
          AND role_name  = CURRENT_ROLE()
    );


-- STEP 3: Apply the policy to your multi-tenant table
-- The 'tenant_id' column in the table maps to 'row_tenant_id' in the policy
ALTER TABLE YOUR_DATABASE.YOUR_SCHEMA.CUSTOMER_EVENTS
ADD ROW ACCESS POLICY YOUR_DATABASE.SECURITY.TENANT_ROW_POLICY
ON (tenant_id);


-- STEP 4: Verify policy application and coverage
SELECT
    policy_name,
    ref_entity_name     AS table_name,
    ref_column_name     AS policy_column,
    policy_status
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE policy_kind = 'ROW_ACCESS_POLICY'
  AND ref_entity_name = 'CUSTOMER_EVENTS';


-- Test: a user with ROLE_ACME_USERS should only see TENANT_ACME rows
-- (run as the target user/role to validate)
SELECT DISTINCT tenant_id FROM YOUR_DATABASE.YOUR_SCHEMA.CUSTOMER_EVENTS;
-- Expected: only 'TENANT_ACME' is returned for ROLE_ACME_USERS