SnowflakeMetrics Service

Overview

The SnowflakeMetrics is a specialized Azure Function microservice that monitors and reports key performance indicators (KPIs) and operational metrics from the Snowflake data warehouse. This service acts as a data quality and operational monitoring system that queries Snowflake for critical business metrics, system health indicators, and data pipeline status, then publishes these metrics to Application Insights and Microsoft Teams for monitoring and alerting.

Business Purpose

This service serves as a comprehensive monitoring and alerting system that: - Monitors critical business KPIs and operational metrics from Snowflake - Tracks data pipeline health and data quality issues - Provides real-time alerts for system anomalies and failures - Reports revenue, session, and conversion metrics - Identifies data loading failures and missing data - Delivers automated reports to Microsoft Teams channels - Supports proactive monitoring and issue detection - Ensures data warehouse integrity and performance

Architecture

Service Type

Platform: Azure Functions (Containerized Kubernetes Microservice)
Runtime: Node.js
Trigger: HTTP Trigger (Anonymous authentication)
Pattern: Scheduled Monitoring with Multi-Channel Alerting

Key Components

graph TD
    A[HTTP Trigger] --> B[SnowflakeMetrics Handler]
    B --> C[Snowflake Connection]
    C --> D[Actions Controller]
    D --> E[SQL Query Execution]

    E --> F{Metric Type?}
    F -->|Standard Metrics| G[Application Insights]
    F -->|KPI Metrics| H[Teams Delivery]

    I[SQL Templates] --> E
    J[Configuration] --> D
    K[Time-Based Execution] --> D

    L[Snowflake Data Warehouse] --> C
    M[Application Insights] --> G
    N[Microsoft Teams] --> H

    O[Revenue Count] --> E
    P[Session Count] --> E
    Q[Load Failed] --> E
    R[Missing Router Sessions] --> E
    S[Unknown Campaign Requests] --> E
    T[Publisher KPIs] --> E

Core Functionality

Metric Collection System

Snowflake Connection: Establishes secure connection to Snowflake data warehouse
SQL Execution: Executes predefined SQL queries for metric collection
Data Processing: Processes query results and formats metrics
Metric Publishing: Publishes metrics to Application Insights
Alert Generation: Sends alerts to Microsoft Teams for critical metrics

Monitoring Categories

Operational Metrics

Revenue Count: Daily revenue transaction volumes
Session Count: User session volumes and trends
Load Failed: Data loading failures and errors
Missing Router Sessions: Missing session data issues
Unknown Campaign Requests: Unmatched campaign requests

Business KPIs (Production Only)

Publisher KPIs: Comprehensive business performance indicators
Full Funnel Sessions: Complete user journey metrics
Conversion Metrics: Conversion rates and performance
Revenue Performance: Revenue trends and analysis

Key Features

Automated Monitoring: Scheduled execution for continuous monitoring
Multi-Channel Alerting: Application Insights metrics and Teams notifications
Time-Based Execution: Different metrics run at different times
Environment-Specific Logic: Production-only KPI reporting
Configurable Lookback: Adjustable time windows for metric calculation
Error Handling: Comprehensive error handling and logging
SQL Template System: Reusable SQL templates with parameter substitution

Metric Definitions

Standard Metrics

Revenue Count

Purpose: Monitor daily revenue transaction volumes
Query: Counts revenue events within lookback period
Alert Threshold: Significant deviations from expected volumes

Session Count

Purpose: Track user session volumes and patterns
Query: Counts user sessions within lookback period
Monitoring: Session volume trends and anomalies

Load Failed

Purpose: Identify data loading failures
Query: Counts failed data loading operations
Alert: Critical for data pipeline health

Missing Router Sessions

Purpose: Detect missing session data
Query: Identifies sessions without router data
Impact: Affects revenue attribution and analytics

Unknown Campaign Requests

Purpose: Monitor unmatched campaign requests
Query: Counts requests without campaign matches
Concern: Potential revenue loss and data quality issues

KPI Metrics (Production Only)

Publisher KPIs

Execution: Runs at specific hour in production
Content: Comprehensive business performance metrics
Delivery: Formatted Teams notifications with detailed KPIs
Frequency: Daily execution at configured time

Configuration

Execution Timing

Standard Metrics: Run on every execution
Publisher KPIs: Run only at specific hour in production
Lookback Period: Configurable time window (default 60 minutes)

Environment Settings

Development: Basic metrics only
Integration: Standard metrics with testing
Production: Full metrics including KPIs and Teams delivery

Snowflake Configuration

{
    "account": "snowflake-account",
    "username": "service-username", 
    "password": "service-password",
    "database": "publisher-database",
    "schema": "analytics-schema",
    "role": "service-role",
    "warehouse": "compute-warehouse"
}

SQL Template System

Parameter Substitution

{lookback}: Time window in minutes for metric calculation
Dynamic Values: Runtime parameter replacement
Reusable Templates: Consistent query patterns across metrics

Query Structure

SELECT 
    metric_name as NAME,
    metric_value as VALUE
FROM data_source
WHERE timestamp >= DATEADD(minute, -{lookback}, CURRENT_TIMESTAMP())

Teams Integration

Notification System

Trigger: KPI metrics with FULL_FUNNEL_SESSIONS data
Format: Rich adaptive cards with formatted metrics
Content: Business KPIs, trends, and performance indicators
Delivery: Automated posting to configured Teams channels

Card Template Features

Visual Formatting: Professional card layout with branding
Data Presentation: Structured metric display
Trend Indicators: Performance trends and comparisons
Action Items: Links to detailed reports and dashboards

Performance Characteristics

Processing Metrics

Query Execution: 5-30 seconds per metric query
Total Runtime: 2-5 minutes for complete metric collection
Snowflake Performance: Depends on data volume and warehouse size
Teams Delivery: 1-3 seconds for notification posting

Scalability Considerations

Query Optimization: Efficient SQL queries with proper indexing
Parallel Execution: Concurrent metric collection
Resource Management: Snowflake warehouse auto-scaling
Error Isolation: Failed metrics don't affect others

Dependencies

External Services

Snowflake: Data warehouse for metric queries
Application Insights: Metric storage and monitoring
Microsoft Teams: Alert and notification delivery
Azure Functions: Hosting and execution platform

Key NPM Packages

snowflake-sdk: Snowflake database connectivity
async: Asynchronous flow control
axios: HTTP client for Teams webhook delivery

Error Handling

Error Scenarios

Snowflake Connection: Database connectivity failures
Query Execution: SQL errors or timeout issues
Metric Processing: Data format or calculation errors
Teams Delivery: Webhook or notification failures
Configuration Issues: Missing or invalid configuration

Recovery Mechanisms

Connection Retry: Automatic retry for connection failures
Query Isolation: Failed queries don't stop other metrics
Error Logging: Comprehensive error logging and tracing
Graceful Degradation: Continue processing despite individual failures

Monitoring and Observability

Application Insights Integration

Custom Metrics: All collected metrics published to Application Insights
Performance Tracking: Query execution times and success rates
Error Tracking: Comprehensive error logging and alerting
Dependency Monitoring: Snowflake connection and performance

Metric Tracking

Metric Names: Descriptive names for each collected metric
Metric Values: Numerical values for trending and alerting
Execution Logs: Detailed logs of metric collection process
Teams Delivery: Success/failure of notification delivery

Security Considerations

Snowflake Credentials: Secure storage of database credentials
Teams Webhooks: Secure webhook URLs for notification delivery
Data Privacy: Secure handling of business metrics and KPIs
Access Control: Restricted access to sensitive metric data

Alerting and Notifications

Application Insights Alerts

Metric Thresholds: Configurable alerts on metric values
Trend Analysis: Alerts on metric trends and anomalies
Service Health: Alerts on service execution failures
Performance Monitoring: Alerts on query performance issues

Teams Notifications

KPI Reports: Daily KPI summaries for business stakeholders
Alert Formatting: Rich formatting for easy consumption
Channel Routing: Different metrics to appropriate Teams channels
Escalation: Critical alerts with appropriate urgency

This service integrates with the broader Publisher ecosystem: - Snowflake Data Warehouse: Source of all metric data - Data Pipeline Services: Monitors health of data ingestion - Revenue Services: Monitors revenue processing metrics - Session Management: Monitors session data quality - Business Intelligence: Provides KPIs for business reporting

Troubleshooting

Common Issues

Snowflake Connection: Verify credentials and network connectivity
Query Performance: Optimize SQL queries and warehouse sizing
Missing Metrics: Check SQL query logic and data availability
Teams Delivery: Verify webhook URLs and Teams channel configuration
Timing Issues: Verify execution schedule and time zone settings

Debug Steps

Check Application Insights for execution logs and errors
Verify Snowflake connection and query execution
Test SQL queries manually in Snowflake console
Validate Teams webhook configuration and delivery
Review metric values and calculation logic

Development

Local Development Setup

Clone repository
Install dependencies: npm install
Configure Snowflake connection credentials
Set up Teams webhook URLs
Configure Application Insights connection
Run tests: npm test

Testing

# Test metric collection
node test.js

# Test individual SQL queries in Snowflake
# Verify Teams webhook delivery

Code Structure

src/Handler.js: Main metric collection orchestrator
src/Actions/index.js: SQL execution and metric processing
src/Actions/sql/: SQL template files for each metric
src/Helpers/teamsDelivery.js: Teams notification delivery
src/Helpers/cardTemplate.js: Teams card formatting
config/: Environment-specific configurations

Operational Considerations

Scheduling

Frequency: Typically scheduled hourly or daily
Time Zones: Consider business hours for KPI delivery
Dependencies: Coordinate with data pipeline schedules

Capacity Planning

Snowflake Costs: Monitor query costs and warehouse usage
Execution Time: Plan for metric collection duration
Alert Volume: Manage notification frequency and channels

Future Enhancements

Potential Improvements

Real-Time Metrics: Streaming metrics for critical indicators
Advanced Analytics: Trend analysis and predictive alerting
Custom Dashboards: Interactive dashboards for metric visualization
Automated Remediation: Automated responses to specific alerts
Enhanced Formatting: Richer Teams card formatting and interactivity
Metric Correlation: Cross-metric analysis and correlation alerts