Database / Snowflake Interview Questions
What is the difference between Snowflake's columnar storage and row-based storage in traditional RDBMS?
In row-based storage (MySQL, PostgreSQL, Oracle), a complete row is written as a contiguous block on disk. This is optimal for OLTP: a single-row read or update is one I/O. But analytical queries that select 3 columns out of 100 must scan the full row for every record — wasting I/O on irrelevant data.
In columnar storage (Snowflake, BigQuery, Redshift), all values from the same column are stored contiguously. An analytical SELECT of 3 columns skips the other 97 entirely — no wasted I/O. Values of the same data type also compress far better: run-length encoding, delta encoding, and dictionary encoding on homogeneous column data yield 3–10x compression ratios versus mixed-type row blocks. Snowflake additionally stores per-column min/max metadata per micro-partition, enabling partition pruning that is impossible with row-oriented storage.
| Aspect | Row-Based | Columnar (Snowflake) |
|---|---|---|
| Best for | OLTP (point reads/writes) | OLAP (scans, aggregations) |
| Compression | Moderate (mixed types per block) | High (same type per column) |
| Partial column query | Must read full row | Only reads selected columns |
| Single-row access | Fast — 1 I/O | Slower — reconstructs across column blocks |
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...
