Recipe

Snowflake design patterns

Practical patterns for modeling Snowflake warehouses that scale with Meridian inference traffic. Cover staged ingest, layered models, and cost-aware query routing without sacrificing latency on hot tables.

1. Multi-layer schema topology

Split your account into RAW, STAGE, and MART databases. RAW receives untouched JSON from Meridian gateway logs; STAGE flattens and types it; MART exposes wide tables for BI. Each layer has its own warehouse and grant scope so a single bad query cannot drain credits across the org.

RAW: append-only, VARIANT columns, no constraints.
STAGE: typed columns, soft deletes, dbt-managed.
MART: clustered on tenant_id + event_date.

2. Warehouse sizing per workload

Right-size by query shape, not by team. Use an X-Small with auto-suspend at 30s for ad-hoc dashboards, a Medium with multi-cluster for the gateway billing rollup, and a dedicated Large for nightly token-usage backfills. Tag every warehouse with cost_center so the Meridian admin console can chart spend per tenant.

CREATE WAREHOUSE meridian_billing_wh
  WAREHOUSE_SIZE = MEDIUM
  AUTO_SUSPEND = 30
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = STANDARD
  COMMENT = 'cost_center=billing';

3. Clustering and pruning for hot paths

Snowflake prunes micro-partitions by min/max metadata. Cluster MART tables on the columns your BI tools filter on most. For Meridian usage data, cluster on (event_date, tenant_id). Re-cluster weekly with ALTER TABLE ... RECLUSTER if your SYSTEM$CLUSTERING_DEPTH climbs above 4.

Pair clustering with a search optimization service on lookup columns like request_id to keep point queries under 200ms even on multi-billion row tables.