alivirgo Snowflake Warehouse AI Skill Guide
Overview & Engine Architecture
Snowflake separates storage and compute: virtual warehouses run SQL against micro-partitioned tables in cloud storage. Roles and grants gate access; stages + COPY/Snowpipe land files. Agents size warehouses to workload, design clustering for large filters, use Zero-Copy Clones/Time Travel carefully (cost), and keep secrets out of worksheets committed to git.
Stages / Snowpipe / connectors
-> tables (micro-partitions)
-> virtual warehouse compute
-> result cache / services
When to use this skill
- Cloud warehouse SQL, ELT with
@dbt - Loading Parquet/CSV from cloud stages
- Role-based access and environment isolation (DEV/PROD)
Operational directives
- Use least-privilege roles; never develop as
ACCOUNTADMINfor daily work. - Suspend warehouses when idle; match size to query shape, not habit.
- Prefer set-based
COPY INTO/MERGEover row-by-row inserts. - Bound exploratory queries (
LIMIT, sample, date filters) on large facts. - Treat Time Travel and Fail-safe retention as cost decisions.
Load + merge sketch
CREATE FILE FORMAT IF NOT EXISTS json_parquet TYPE = PARQUET;
CREATE STAGE IF NOT EXISTS stg_orders
URL='s3://bucket/orders/'
STORAGE_INTEGRATION = si_prod;
COPY INTO raw.orders_ext
FROM @stg_orders
FILE_FORMAT = (FORMAT_NAME = json_parquet)
PATTERN = '.*\\.parquet';
MERGE INTO marts.fct_orders t
USING raw.orders_ext s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, amount, updated_at)
VALUES (s.order_id, s.amount, s.updated_at);
Session hygiene
USE ROLE TRANSFORMER;
USE WAREHOUSE ETL_WH;
USE DATABASE ANALYTICS;
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 600;
Common failures
| Symptom | Cause | Fix |
|---|---|---|
| Credits spike | large WH left running | auto-suspend; right-size |
| Slow prune | poor clustering / casting filters | cluster keys; avoid wrapping columns |
| Privilege error | wrong role/secondary roles | SHOW GRANTS; grant usage |
| Duplicate loads | non-idempotent COPY | force/purge strategy; load history |
Best practices
- Separate loader vs transformer vs reporter roles.
- Use transient/temp tables for staging when retention is unnecessary.
- Monitor
QUERY_HISTORYand warehouse metering views. - Pair with
@dbtfor versioned models and tests.
Limitations
- Account params, editions, and region features differ by contract.
- External network/secrets integrations need admin setup.
- Unload costs and cross-cloud egress are easy to underestimate.
Related skills
@dbt- model lifecycle on Snowflake@airflow/@prefect- orchestration of loads@great-expectations- data quality suites