Snowflake vs Microsoft SQL Server / Synapse — Comparison Reference

General reference notes — not tied to a specific session.
Assumes comparison baseline: SQL Server / Azure Synapse (most common).

Snowflake

True separation of compute & storage — scale each independently, pay only for what you use
Zero-copy cloning — instant dev/test environments, no cost until you write
Multi-cloud (AWS, Azure, GCP) — not locked into one ecosystem
Time Travel — query historical data up to 90 days back, no backup restore needed
Automatic clustering & maintenance — no DBAs tuning indexes
Data sharing — share live data across orgs without copying
Semi-structured data (JSON, Avro, Parquet) is a first-class citizen

Pure consumption cost model — can get expensive fast with poorly written queries or always-on workloads
No row-level locking — not built for OLTP (transactional workloads)
Egress costs — moving data out cross-cloud hurts
Latency — cold warehouse startup adds seconds
Vendor lock-in — Snowflake SQL dialect, proprietary features

OLTP + OLAP in one ecosystem — SQL Server handles both; Synapse bridges to analytics
Deep Microsoft integration — AD/Entra, Power BI, Azure Data Factory, Fabric
On-prem option — SQL Server runs anywhere; useful if cloud isn't viable
Familiarity — most DBAs already know T-SQL
Cost predictability — licensing is annoying but costs are knowable upfront

Azure lock-in — Synapse only runs on Azure
Scaling is clunky — traditional SQL Server requires manual tuning; Synapse better but behind Snowflake
Maintenance overhead — indexes, stats, fragmentation — you manage it
Synapse is a mixed bag — some features immature or awkward vs. dedicated tools

Bottom line:

Variable load, data warehousing at scale → Snowflake wins
Already deep in Microsoft/Azure, need OLTP + analytical → Microsoft stack makes more sense