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.
Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!
Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.
Invest now!!! Get Free equity stock (US, UK only)!
Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.
The Robinhood app makes it easy to trade stocks, crypto and more.
Webull! Receive free stock by signing up using the link: Webull signup.
More Related questions...
