Azure Synapse Analytics ➔ Amazon Redshift & Amazon Athena
From Azure Synapse 60 fixed distributions to Amazon Redshift slices & serverless Athena queries.
Azure Synapse divides every Dedicated SQL Pool into exactly 60 fixed distributions regardless of cluster size. Amazon Redshift divides data across compute node slices (e.g. 2 to 16 slices per node depending on instance type). In Redshift, distribution styles are `DISTSTYLE KEY`, `ALL`, `EVEN`, or `AUTO`, and sorting is governed by `SORTKEY` zone maps (which act like Synapse Columnstore Indexes). For Synapse Serverless SQL (`OPENROWSET`), the exact architectural twin is Amazon Athena!
1. Architectural Mechanism Comparison
Azure Synapse Analytics
Dedicated SQL Pools with fixed 60 storage distributions (HASH, REPLICATED, ROUND_ROBIN) + Serverless SQL Pools with OPENROWSET reading ADLS Gen2 files at $5/TB scanned.
- Fixed 60 distributions provide predictable parallel execution scaling.
- Serverless SQL pools allow instant ad-hoc T-SQL over Parquet/Delta Lake without compute provisioning.
- Unified Synapse Studio combining SQL, Spark, and Pipelines.
Amazon Redshift & Amazon Athena
Amazon Redshift uses MPP architecture with compute node slices and RMS (Redshift Managed Storage). Amazon Athena is a serverless Trino/Presto SQL engine querying S3 directly at $5/TB scanned.
- Redshift RA3 with RMS: Decouples compute from storage, caching hot blocks locally while spilling cold data to S3.
- Amazon Athena: 100% serverless ad-hoc SQL engine over S3 lakehouse tables (Apache Iceberg, Parquet, ORC).
- Redshift Serverless: Dynamic compute scaling that pauses automatically to $0 when idle.
2. Interactive Terminology & Concept Bridge
Interactive Concept Bridge: Terminology & Architectural Mapping
Click any concept below to see how your AZURE knowledge directly maps into AWS.
60 Fixed Distributions
Dedicated SQL Pool divides storage into exactly 60 underlying Azure Storage buckets.
Redshift Compute Node Slices
Redshift compute nodes are divided into slices; each slice processes a portion of table data.
In Synapse, the distribution count is fixed at 60. In Redshift, total slice count scales with the node size (e.g. 2 slices per ra3.xlplus, 16 slices per ra3.16xlarge).
3. Visual Architecture Pipeline (Amazon Redshift & Amazon Athena)
Amazon Redshift 3-Stage Architecture: S3 Lake Ingress ➔ Leader/Slices MPP ➔ BI Serving
Click any section below or run the simulation to explore Redshift DISTKEY sharding and S3 Spectrum.
2. Leader Node & Slice Sharding Engine
The Leader Node compiles SQL into optimized C++ execution code. Compute Nodes execute tasks across dedicated Slices. Tables are sharded by `DISTKEY` to achieve local zero-network-shuffle joins, and indexed by `SORTKEY` zone maps.
“Like a head chef (Leader) giving dedicated sous-chefs (Slices) precise ingredients so nobody has to walk across the kitchen (zero network shuffle).”
- Leader Node: Manages client connections, query parsing, and code compilation.
- `DISTSTYLE KEY`: Shards rows to slices based on join keys for co-located local joins.
- `SORTKEY`: Creates 1MB zone-map block metadata (min/max) to skip scanning irrelevant data.
4. Side-by-Side Code, CLI & Terraform Translator
Side-by-Side Code & Syntax Translator
-- Azure Synapse Dedicated SQL Pool DDL
CREATE TABLE dbo.FactOrders
(
OrderKey BIGINT NOT NULL,
CustomerKey INT NOT NULL,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(18,2) NOT NULL
)
WITH
(
DISTRIBUTION = HASH(CustomerKey),
CLUSTERED COLUMNSTORE INDEX,
PARTITION (OrderDate RANGE RIGHT FOR VALUES ('2026-01-01', '2026-04-01', '2026-07-01'))
);-- Amazon Redshift DDL
CREATE TABLE public.fact_orders
(
order_key BIGINT NOT NULL,
customer_key INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(18,2) NOT NULL
)
DISTSTYLE KEY
DISTKEY(customer_key)
COMPOUND SORTKEY(order_date, customer_key);5. Paradigm Shift Gotchas: Traps to Avoid in AWS
Redshift Disk Fragmentation: `VACUUM` and `ANALYZE` Required
In Redshift, updates and deletes leave ghost rows and unsorted regions on disk. Over time, query performance degrades drastically unless `VACUUM` and `ANALYZE` are executed.
Although Redshift now runs automated table maintenance in the background, high-throughput ETL batch pipelines should explicitly run `VACUUM DELETE ONLY <table>` and `ANALYZE <table>` after bulk loads.
Athena Query Cost Runaway Without Partitioning & Parquet
Querying raw JSON/CSV files in S3 without partition projection or columnar formatting forces Athena to read every single byte across the entire dataset.
Always convert data to Apache Parquet or ORC with Snappy compression, partition by date (`year=YYYY/month=MM`), and configure Athena Workgroup scan data limits.
6. Test Your Mental Model
Quick Knowledge Check: Test Your AWS Mental Model
Solidify your cross-cloud understanding with instant feedback.