Database / Snowflake Interview Questions
What are common Snowflake anti-patterns and performance pitfalls to avoid?
Anti-patterns in Snowflake fall into four categories: security misuse, cost mismanagement, query performance degradation, and data loading inefficiency.
| Anti-Pattern | Root Cause | Fix |
|---|---|---|
| Using ACCOUNTADMIN for daily work | Violates least-privilege; accidental privilege grants | Create custom roles; use SYSADMIN for object management |
| Never suspending idle warehouses | Compute bills tick even with zero queries | Set AUTO_SUSPEND = 60 seconds on all non-critical warehouses |
| High partition scan ratio on large tables | No clustering; data not co-located with query predicates | Define a clustering key on frequently filtered columns |
| Loading thousands of tiny files with COPY INTO | Small-file overhead: metadata cost > data cost | Merge files to 100–250 MB before loading; use Snowpipe for micro-batches |
| Breaking the result cache | Non-deterministic functions (CURRENT_TIMESTAMP, RANDOM) in repeated queries | Standardize query SQL; move timestamp filters to parameters |
| Storing all text as VARCHAR | No column statistics for numerics or dates; worse compression | Use typed columns (NUMBER, DATE, TIMESTAMP_NTZ) |
| UNION instead of UNION ALL | Implicit deduplication scan on large result sets | Use UNION ALL when input rows are already distinct |
| SELECT * on wide tables | Reads all columns; wastes I/O and memory on irrelevant data | Always specify required columns in SELECT list |
The single highest-impact change for most Snowflake accounts: set AUTO_SUSPEND = 60 on every warehouse that is not required to be always-on, and audit warehouses weekly in SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY for unexpected runtime.
How does adding LIMIT 10 to an otherwise-identical repeated query affect Snowflake's result cache?
What is the 'small file problem' in Snowflake COPY INTO and which ingestion mechanism mitigates it?
More Related questions...
