# 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) |

> **Bigger is rarely faster**
>
> For analytics workloads, jumping to a 2X-Large or larger usually shows little query improvement while doubling the credit burn. Size up only when a query is genuinely compute-bound, not just to “go faster.”

## 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.

> **ONC keys differ**
>
> The VARIANTS ONC tables cluster on `CASEBUNDLING_ID`, not `CASEFILE_ID`. Filter on both clustering columns for best pruning.

## 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.

> **Equality only**
>
> SOS accelerates equality predicates (`=`) only, not range predicates (`BETWEEN`, `>`, `<`). `WHERE chrom = 'chr17'` uses SOS; `WHERE pos BETWEEN 1000 AND 2000` on its own does not.

## 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.

```sql
SELECT name, data_timestamp, refresh_version
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE name = 'BASE_RENASIGHT'
ORDER BY data_timestamp DESC
LIMIT 1;
```