Snowflake — Conversation Data in a Cloud Warehouse Built for It

Start with X-Small. It handles thousands of rows per COPY INTO and most analytical queries comfortably. Scale up (Small, Medium) for heavy transforms - complex joins across millions of rows, dbt models with multiple CTEs. Scale out (multi-cluster) when concurrent users or dashboards create queue times. Never scale up for more data volume - Snowflake processes more data by scanning more micropartitions in parallel, not by needing a bigger machine. Set AUTO_SUSPEND to 60 seconds for development warehouses to avoid burning credits while you are reading documentation between queries.

Snowflake automatically organises data into micropartitions as it is loaded - each partition stores a contiguous range of rows in a compressed columnar format. For most conversation tables under 10TB, the natural ingestion order on call_timestamp provides good-enough partition pruning for time-filtered queries. Only add an explicit clustering key when you see scan-heavy queries that filter on non-time columns (like signal_name or account_id) and SYSTEM$CLUSTERING_INFORMATION confirms poor overlap ratios. Clustering is a background maintenance cost - do not add it speculatively.

Default time travel retention is 1 day. For the conversation_signals table - where an accidental DELETE or a bad scoring pipeline run can wipe production data - extend to 90 days. This lets you recover any point-in-time state within that window using AT or BEFORE syntax. For raw transcript tables, 1 to 7 days usually suffices since transcripts are immutable after initial load. Know that extended time travel retention increases storage costs proportionally - every changed micropartition is retained for the full retention period.

Credit consumption monitoring

Query ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY to track credit spend per warehouse per day. Set resource monitors at the account or warehouse level with email alerts at 80% and hard suspend at 100% of your monthly budget. Credits burn whether queries succeed or fail - a runaway query on a Large warehouse costs the same as a productive one.

Warehouse auto-suspend

Set AUTO_SUSPEND to 60 seconds for development and staging warehouses. Every second of idle compute burns credits. For production warehouses serving dashboards, 300 seconds (5 minutes) avoids frequent cold starts that add latency to the first query after idle. Never leave AUTO_SUSPEND at the default 10 minutes for dev - it is the single easiest cost saving.

Micropartition pruning

Check pruning efficiency with SYSTEM$CLUSTERING_INFORMATION('conversations', '(call_timestamp)'). If the average_overlap ratio is high or more than 50% of micropartitions are scanned for time-filtered queries, your natural ordering may have degraded from heavy UPDATE or DELETE operations. Recluster or consider adding an explicit clustering key on call_timestamp.

VARIANT query performance

Querying VARIANT paths (e.g. metadata:source_type::STRING) is slower than querying typed columns because Snowflake must deserialise the JSON at query time. If you notice repeated lateral flatten operations in slow queries or your dashboards are hammering the same VARIANT paths, create a scheduled Task that materialises those paths into typed columns in a downstream table.

Zero-copy clones for testing

Clone the entire database (CREATE DATABASE dev_clone CLONE production) for pipeline testing. The clone shares storage with the source until data diverges - so it costs nothing until you start writing to it. Test schema migrations, new COPY INTO patterns, or dbt model changes against real data. Drop the clone when done.

Data retention and compliance

Use time travel + fail-safe for recovery. Implement dynamic data masking policies on transcript_text and participants columns to redact PII for roles that should not see it. Use row access policies to restrict which accounts or teams a given role can query. For GDPR right-to-deletion, DELETE the rows and let time travel retention expire - after the retention period plus the 7-day fail-safe window, the data is permanently gone.

Signal to win-rate correlation (Sigma / Tableau)

Join conversation_signals to your CRM opportunity table on deal_id. Pivot signal names into columns using Snowflake's PIVOT or conditional aggregation. Calculate win rates by signal quartile - e.g. deals where the discovery_score signal was in the top 25% closed at 3x the rate of the bottom 25%. Sigma connects natively via Snowflake's partner connect. Tableau uses the Snowflake JDBC driver - point it at a view or a dbt mart and let analysts self-serve.

Rep cohort scoring trend (Snowsight dashboards)

Use Snowflake's built-in Snowsight dashboarding to plot average signal scores by rep and month. Filter by scoring_version to compare framework iterations side by side - did the new discovery rubric actually shift scores? Snowsight SQL worksheets with chart tiles make this a 10-minute setup. No external BI tool needed for internal team reviews.

Deal risk dashboard (dbt + Looker)

dbt models in your transform layer aggregate risk signals per deal - count of risk_flag = true, average confidence, latest scoring_version. A final mart table joins these to CRM stage and close date. Looker connects to the gold layer and provides self-service filtering by team, pipeline stage, and time period. Sales leadership gets a live view of which deals have the most risk signals without writing SQL.