Transform Your
Snowflake Data Engineering

Use Case:

"Create a secure analytics view with data masking"

Solution:

                        
-- Create secure view with dynamic data masking
CREATE OR REPLACE SECURE VIEW customer_analytics_secure AS
WITH customer_metrics AS (
    SELECT 
        c.customer_id,
        SHA2(c.email) as hashed_email,
        MASK(c.phone_number, 'XXX-XXX-XXXX') as masked_phone,
        COUNT(DISTINCT o.order_id) as total_orders,
        SUM(o.total_amount) as total_spent,
        MAX(o.order_date) as last_order_date,
        MIN(o.order_date) as first_order_date,
        AVG(o.total_amount) as avg_order_value
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= DATEADD(year, -1, CURRENT_DATE())
    GROUP BY 1, 2, 3
),
customer_segments AS (
    SELECT 
        *,
        DATEDIFF('day', last_order_date, CURRENT_DATE()) as days_since_last_order,
        CASE 
            WHEN total_spent >= 1000 AND total_orders >= 5 THEN 'VIP'
            WHEN total_spent >= 500 THEN 'Regular'
            ELSE 'New'
        END as customer_segment,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_spent) 
            OVER () as median_spent
    FROM customer_metrics
)
SELECT 
    customer_id,
    hashed_email,
    masked_phone,
    customer_segment,
    total_orders,
    total_spent,
    avg_order_value,
    days_since_last_order,
    CASE 
        WHEN total_spent > median_spent THEN 'Above Average'
        ELSE 'Below Average'
    END as spending_level,
    IFF(days_since_last_order <= 30, 'Active', 'Inactive') as status
FROM customer_segments;
                    

Use Case:

"Implement time-travel based analysis"

Solution:

                        
-- Create a zero-copy clone for analysis
CREATE DATABASE analytics_sandbox CLONE production_db
    AT (TIMESTAMP => DATEADD(hours, -24, CURRENT_TIMESTAMP()));

-- Analyze changes over time
WITH historical_metrics AS (
    SELECT 
        product_id,
        product_name,
        category,
        price,
        inventory_level
    FROM products AT(OFFSET => -86400)
),
current_metrics AS (
    SELECT 
        product_id,
        product_name,
        category,
        price,
        inventory_level
    FROM products
),
change_analysis AS (
    SELECT 
        c.product_id,
        c.product_name,
        c.category,
        c.price as current_price,
        h.price as historical_price,
        c.inventory_level as current_inventory,
        h.inventory_level as historical_inventory,
        ((c.price - h.price) / h.price * 100) as price_change_percent,
        (c.inventory_level - h.inventory_level) as inventory_change
    FROM current_metrics c
    JOIN historical_metrics h ON c.product_id = h.product_id
    WHERE c.price <> h.price OR c.inventory_level <> h.inventory_level
)
SELECT 
    category,
    COUNT(*) as products_changed,
    AVG(ABS(price_change_percent)) as avg_price_change,
    SUM(inventory_change) as total_inventory_change,
    ARRAY_AGG(OBJECT_CONSTRUCT(
        'product', product_name,
        'price_change', price_change_percent,
        'inventory_change', inventory_change
    )) as change_details
FROM change_analysis
GROUP BY category
HAVING COUNT(*) > 0;