Snowflake Reliability Patterns

Overview

Production-grade reliability patterns for Snowflake integrations.

Prerequisites

• Understanding of circuit breaker pattern
• opossum or similar library installed
• Queue infrastructure for DLQ
• Caching layer for fallbacks

Circuit Breaker

import CircuitBreaker from 'opossum';

const snowflakeBreaker = new CircuitBreaker(
  async (operation: () => Promise<any>) => operation(),
  {
    timeout: 30000,
    errorThresholdPercentage: 50,
    resetTimeout: 30000,
    volumeThreshold: 10,
  }
);

// Events
snowflakeBreaker.on('open', () => {
  console.warn('Snowflake circuit OPEN - requests failing fast');
  alertOps('Snowflake circuit breaker opened');
});

snowflakeBreaker.on('halfOpen', () => {
  console.info('Snowflake circuit HALF-OPEN - testing recovery');
});

snowflakeBreaker.on('close', () => {
  console.info('Snowflake circuit CLOSED - normal operation');
});

// Usage
async function safeSnowflakeCall<T>(fn: () => Promise<T>): Promise<T> {
  return snowflakeBreaker.fire(fn);
}

Idempotency Keys

import { v4 as uuidv4 } from 'uuid';
import crypto from 'crypto';

// Generate deterministic idempotency key from input
function generateIdempotencyKey(
  operation: string,
  params: Record<string, any>
): string {
  const data = JSON.stringify({ operation, params });
  return crypto.createHash('sha256').update(data).digest('hex');
}

// Or use random key with storage
class IdempotencyManager {
  private store: Map<string, { key: string; expiresAt: Date }> = new Map();

  getOrCreate(operationId: string): string {
    const existing = this.store.get(operationId);
    if (existing && existing.expiresAt > new Date()) {
      return existing.key;
    }

    const key = uuidv4();
    this.store.set(operationId, {
      key,
      expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000),
    });
    return key;
  }
}

Bulkhead Pattern

import PQueue from 'p-queue';

// Separate queues for different operations
const snowflakeQueues = {
  critical: new PQueue({ concurrency: 10 }),
  normal: new PQueue({ concurrency: 5 }),
  bulk: new PQueue({ concurrency: 2 }),
};

async function prioritizedSnowflakeCall<T>(
  priority: 'critical' | 'normal' | 'bulk',
  fn: () => Promise<T>
): Promise<T> {
  return snowflakeQueues[priority].add(fn);
}

// Usage
await prioritizedSnowflakeCall('critical', () =>
  snowflakeClient.processPayment(order)
);

await prioritizedSnowflakeCall('bulk', () =>
  snowflakeClient.syncCatalog(products)
);

Timeout Hierarchy

const TIMEOUT_CONFIG = {
  connect: 5000,      // Initial connection
  request: 30000,     // Standard requests
  upload: 120000,     // File uploads
  longPoll: 300000,   // Webhook long-polling
};

async function timedoutSnowflakeCall<T>(
  operation: 'connect' | 'request' | 'upload' | 'longPoll',
  fn: () => Promise<T>
): Promise<T> {
  const timeout = TIMEOUT_CONFIG[operation];

  return Promise.race([
    fn(),
    new Promise<never>((_, reject) =>
      setTimeout(() => reject(new Error(`Snowflake ${operation} timeout`)), timeout)
    ),
  ]);
}

Graceful Degradation

interface SnowflakeFallback {
  enabled: boolean;
  data: any;
  staleness: 'fresh' | 'stale' | 'very_stale';
}

async function withSnowflakeFallback<T>(
  fn: () => Promise<T>,
  fallbackFn: () => Promise<T>
): Promise<{ data: T; fallback: boolean }> {
  try {
    const data = await fn();
    // Update cache for future fallback
    await updateFallbackCache(data);
    return { data, fallback: false };
  } catch (error) {
    console.warn('Snowflake failed, using fallback:', error.message);
    const data = await fallbackFn();
    return { data, fallback: true };
  }
}

Dead Letter Queue

interface DeadLetterEntry {
  id: string;
  operation: string;
  payload: any;
  error: string;
  attempts: number;
  lastAttempt: Date;
}

class SnowflakeDeadLetterQueue {
  private queue: DeadLetterEntry[] = [];

  add(entry: Omit<DeadLetterEntry, 'id' | 'lastAttempt'>): void {
    this.queue.push({
      ...entry,
      id: uuidv4(),
      lastAttempt: new Date(),
    });
  }

  async processOne(): Promise<boolean> {
    const entry = this.queue.shift();
    if (!entry) return false;

    try {
      await snowflakeClient[entry.operation](entry.payload);
      console.log(`DLQ: Successfully reprocessed ${entry.id}`);
      return true;
    } catch (error) {
      entry.attempts++;
      entry.lastAttempt = new Date();

      if (entry.attempts < 5) {
        this.queue.push(entry);
      } else {
        console.error(`DLQ: Giving up on ${entry.id} after 5 attempts`);
        await alertOnPermanentFailure(entry);
      }
      return false;
    }
  }
}

Health Check with Degraded State

type HealthStatus = 'healthy' | 'degraded' | 'unhealthy';

async function snowflakeHealthCheck(): Promise<{
  status: HealthStatus;
  details: Record<string, any>;
}> {
  const checks = {
    api: await checkApiConnectivity(),
    circuitBreaker: snowflakeBreaker.stats(),
    dlqSize: deadLetterQueue.size(),
  };

  const status: HealthStatus =
    !checks.api.connected ? 'unhealthy' :
    checks.circuitBreaker.state === 'open' ? 'degraded' :
    checks.dlqSize > 100 ? 'degraded' :
    'healthy';

  return { status, details: checks };
}

Instructions

Step 1: Implement Circuit Breaker

Wrap Snowflake calls with circuit breaker.

Step 2: Add Idempotency Keys

Generate deterministic keys for operations.

Step 3: Configure Bulkheads

Separate queues for different priorities.

Step 4: Set Up Dead Letter Queue

Handle permanent failures gracefully.

Output

• Circuit breaker protecting Snowflake calls
• Idempotency preventing duplicates
• Bulkhead isolation implemented
• DLQ for failed operations

Error Handling

Examples

Quick Circuit Check

const state = snowflakeBreaker.stats().state;
console.log('Snowflake circuit:', state);

Resources

• Circuit Breaker Pattern
• Opossum Documentation
• Snowflake Reliability Guide

Next Steps

For policy enforcement, see snowflake-policy-guardrails.