Best Practices & Performance
Writing efficient queries and choosing the right warehouse. Snowflake bills by warehouse-seconds — an oversized warehouse, or a query that scans a billion-row table without a pruning predicate, costs real money and slows everyone sharing it.
Warehouse selection
Pick the smallest warehouse that fits the workload. Replace {ENV} with DEVELOPMENT, PREPRODUCTION, or PRODUCTION.
| Use case | Warehouse | Who can use |
|---|---|---|
| Ad-hoc queries, quick lookups | RWD_{ENV}_QUERY_INTERACTIVE_WH | Engineers, Researchers |
| Heavy analytics, large aggregations | RWD_{ENV}_QUERY_ANALYTICS_WH | Analysts |
| BI dashboard queries (QuickSight) | RWD_{ENV}_BI_WH | Viewers |
| AI / Cortex Agent / semantic views | RWD_{ENV}_AI_WH | Analysts, Streamlit devs |
| ETL transforms (dbt), admin only | RWD_{ENV}_ETL_TRANSFORM_WH | Engineers (admin) |
Clustering key awareness
Most foundation and cohort Dynamic Tables are clustered on casefile_id. Queries filtering on it prune to a small set of micro-partitions and run fast; queries without it may scan the entire table. When joining, make sure at least one side has a casefile_id predicate.
Search Optimization Service (SOS)
The four VARIANTS.SAMPLE_VARIANT_CALLS_* tables have SOS enabled for equality predicates. These tables hold billions of rows, so always include at least one SOS column in your WHERE clause.
Anti-patterns to avoid
| Anti-pattern | Do this instead |
|---|---|
SELECT * FROM large_table | Specify only the columns you need. |
| No USE WAREHOUSE after login | Always run USE WAREHOUSE … first. |
| Querying staging views for analytics | Use foundation tables in LIMS_PUB (pre-joined, clustered). |
| Re-joining cohort tables to foundation | Cohorts already include the relevant joins — query the cohort directly. |
| VARIANTS queries without SOS predicates | Always filter on casefile_id, chrom, or another SOS column. |
Dynamic Table freshness
Dynamic Tables refresh on a target lag, so the data is some bounded interval behind the source. For near-real-time data, query the staging views directly (they read from DATA_SHARE), but they are slower because they are not materialized.
SELECT name, data_timestamp, refresh_versionFROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())WHERE name = 'BASE_RENASIGHT'ORDER BY data_timestamp DESCLIMIT 1;