Snowflake Vs. AWS RedShift Vs. GCP BigQuery Vs. Azure Synapse for Data Warehousing!

hey y'all data guy here so I've seen

some comments asking for some more

comparison videos for people that might

be just starting out in kind of the data

verse and I thought a really pertinent

video to make would be comparing

contrasting kind of each of the major

Cloud providers data warehouse options

that they're pushing versus snowflake

which is kind of the cloud agnostic but

the only one that's really une equal

popularity um if not greater popularity

than the you know Cloud specific

providers cuz have a cloud provider it's

really easy to just pull that off the

rack so you kind of have a captive

audience of you know Amazon is all AWS

customers and so on and so forth um

versus snowflake you know you have to go

out and buy it um but snowflake is so

great that a lot of people do go out and

buy it so what I'm going to go through

is just kind of talk about high level

the architecture performance scalability

and then the ecosystem

integration of each of these different

tools each these different types of data

warehouses um to just give you a frame

work and kind of understanding of hey

which one is best for me um while they

are all you know data warehouses they do

all have kind of their own Specialties

and their own unique quirks and

strengths um that make them better

suited to a particular use case so

that's what we're going to explore today

and without further Ado let's get into

it so now the first database we're going

to talk about is the star of the show in

my opinion which is snowflake um and

snowflake is a fully managed Cloud

native data warehouse that's designed

with a unique unique architecture that

actually separates compute and storage

you can see that Illustrated here where

you have your compute available uh under

snow park container services directly

next to your snowflake data warehouse

and this separation allows users to

scale each of those components

individually which provides greater

flexibility for workloads that have

varying performance and storage needs

now snowflakes architecture relies on

Virtual warehouses for processing and

then a Central Storage layer that is

accessible to all compute nodes you

essentially have one large storage layer

which is the snowflake optimized compute

and then you have the snowflake native

apps which are the uh smaller data

warehouses the virtual data warehouses

that while they can access all of that

data they are typically only containing

a certain small subset of it um and this

multi cluster shared data design ensures

High concurrency without compromising

performance because each of those data

warehouses can run their own queries

independently so this makes it a really

great choice for super large scale

analytics another reason why snowflake

excels and scalability is because again

of that decoupled storage compute

architecture so a user can spin out

multiple virtual warehouses to handle

concurrent workloads without affecting

one another and this is really

beneficial for organizations that are

running complex queries and you need

consistent performance during peak times

then you might have a really large

amount of users executing those queries

concurrently snowflakes automatic

scaling ensures that optimal resource

utilization is attained while they're

minimizing the downtime there um so you

really make sure that hey you know I

don't need to single thread and

bottleneck everything through one data

warehouse or one query engine I can

actually have many different query

engines handling simpler smaller jobs at

the same time for a lower overall

processing time of those individual jobs

now in terms of pricing and how you're

going to be paying for all this

snowflake uses a consumption based

pricing model where you'll pay

separately for storage and compute and

so compute costs are based on the amount

of time that your virtual warehous are

actually running and those virtual

warehouses are how you're running those

queries uh versus the storage Co cost

for just you know that base layer

storage um just just going to depend on

the amount of data stored for x amount

of time um and this model is flexible

and semi-predictable um a big issue with

a lot of uh organizations is because

snowflake offers discounts for Reserve

capacity people will either overestimate

their capacity um and just have a bunch

of excess snowflake credits or vice

versa they won't buy enough and they'll

end up paying a ton of snowflake credits

when just kind of run if they don't

Implement really good cost control

measures and so just something to always

keep in mind now snowflake is also a

cloud agnostic platform so if you're on

AWS Google Cloud Azure doesn't matter it

integrates with all three of the major

Cloud ecosystems um and also integrates

really well with a bunch of different

tools for data ingestion transformation

analytics um airflow spark uh Tableau

and powerbi uh you know really any kind

of of of Analytics tool um and it's also

got a really strong data Marketplace

ecosystem and that's actually what

you're seeing here with the snowflake

Marketplace so there's actually a ton of

different uh organizations that have

their own kind of defined connectors or

data sharing tools that you can actually

leverage through the snowflake

Marketplace which is nice if you don't

want to have to build everything

yourself um and then also just

underpinning all of this is you know

Enterprise security grade features um so

end to end encryption rule-based Access

Control hipa gdpr sock compliance

um and also because it's multicloud you

can meet those Regional data residency

requirements um so that is snowflake in

a nutshell now let's move on to AWS

redshift now here to help us with this

redshift expertise is the data dog say

hi everyone um and so now moving into

redshift so AWS redshift kind of follows

a more traditional data warehouse

architecture where you have really

tightly coupled compute and storage but

you also have new features like red

shift spectrum that allow ex quering

external data and kind of a a um I would

say data Lake style format right here

you can see um for you know querying

less uh schema dependent data so more

object storage type data um and red

shift also as you can see here uses a

cluster based approach where users

Define node types and node sizes to

manage storage and performance um and

while this makes red shift predictable

in terms of resource allocation it also

leads to challenges in scaling because

compute and storage need to be scaled

together unlike in Snowflake so you

can't just scale up additional compute

to handle more complex queries unless

you also scale up

storage now you do have some recent

improvements such as ra3 nodes that

enable separate scaling of compute and

storage which is helped to narrow the

Gap with snowflake but generally not as

easy of a process and user experience as

within snowflake um and then red shift

also supports scalability through

cluster resizing as I said but requires

manual inter intervention um so instead

of snowflake which will pretty much just

Auto scale for you red shift Spectrum um

or the compute resource within red shift

needs to be explicitly defined um and so

things like hey if I need to use red

shift Spectrum to query external S3 data

without moving into the cluster I will

still need to scale compute resources in

the red shift cluster and that's going

to involve downtime um just to be able

to access those R those S3 that S3 data

and again you have the introduction of

ra3 nodes that has allowed you to

decouple the two from for some extent

but still not a perfect solution and

hopefully they kind of go more towards

the path of actually decoupling storage

compute um now so red shift also just

kind of how they work pricing wise they

offer both on demand and reserved

instance uh type instance types so on

demand pricing charges for the active

cluster which includes Computing storage

and then reserved instances actually can

you know basically say hey I'm going to

reserve an instance use at this time and

that can help you provide significant

cost savings um if you have really

predictable workloads um and then also

if you want to use that red shift

Spectrum tool I mentioned it does incur

extra cost for quering that external S3

data um

so not you know it's not great you to

pay extra just to access S3 but it does

allow you to avoid upfront storage cont

EXP es for Less frequently accessed data

so you can kind of use S3 as your

archival storage and only query it as

needed um and as an AWS product as part

of the AWS ecosystem red shift

integrates really well natively with you

know all the other Amazon services so S3

glue Athena um and if you're using a

full AWS stack it's a really you know

easy tool to integrate into ETL

workflows pretty attractive option for

organizations already using AWS but it's

not amazingly compatible with non-ads

tools so definitely keep that in mind if

you have a lot of non-ads tools in your

stack um and then similarly to snowflake

but honestly even more so because ads

has a bunch of government contracts um

provides a ton of security features

encryption at rest Transit VPC isolation

IM based Access Control uh got Hippa

gdpr PCI sock 2 so if you have an

organization that has really stringent

regulatory requirements you're using AWS

probably a good choice for you now next

on the docket we have Google big query

um and so Google big query again is

different from you know red shift and

and Snowflake and that it is a

serverless fully managed data warehouse

that is obviously really deeply

integrated with Google Cloud um and uses

a distributed architecture um which

abstracts compute and storage entirely

from the user which allows you to just

focus on running queries without

worrying about infrastructure management

um and so it's basically just pay per

query pricing so you only pay for the

queries you use um and big query is

really optimized for analytics workloads

hence the pay for query model because

that's what analytics workloads are

centered around and it also uses colum

or storage which makes it really

efficient for those kind of large scale

queries that are needed for analytics

workloads um and then also you know as

kind of intend with paper query it's

really seamlessly autoscaling um so it

makes it really ideal for dynamic or

unpredictable workloads because it'll

just automatically adjust to meet the

needs that workloads um and really it

the biggest benefit of big query serous

nature is it completely eliminates the

need for resource provisioning or

cluster management and it'll just

automatically scale compute resources

based on query demands um which helps

Ensure High Performance for even the

largest workloads and this Dynamic

scalability is also really ideal for

organizations that can experience

unpredictable or spiky query

patterns Google's uh and you know big

queries distribute infrastructure

because they have so much infrastructure

can help ensure minimal query latency

even for really really large data sets

um and bit query's pricing is also

relatively straightforward it's just

purely based on storage and query

execution um and storage costs are

charged per gigabyte of data stored

queries are based on the amount of data

processed and so very much just a pay as

you go model so pretty ideal for

organizations that have unpredictable

workloads or ones that don't really know

hey how much this workload might

actually needs and you want to avoid

over or under provision

um and then Google also offers flat rate

pricing for organizations that do have

more consistent query needs so you can

get some discounts there so it really is

still versatile for a wide range of use

cases um and similarly to AWS but Google

big query is really tightly integrated

with Google cloud services like data

flow data proc looker supports Federated

queries which actually allows you

analyze data stored in cloud storage

Google Sheets or even external databases

which is really cool um and then also

bigquery has a really tight integration

with integr with machine learning tools

like tensor flow so it's good choice for

AI and ml workloads as well um and then

additionally similarly AWS big query uh

emphasizes security pretty heavily so

you got things like customer manage

encryption Keys fine gra IM permissions

and support for gdpr and CCPA compliance

and then obviously Google's Global

infrastructure pretty much ensures High

availability and also data residency

compliance no matter what region you

deploy your data into so so now the

final stop on this tour is azure synapse

analytics kind of the newer kit on the

Block and and honestly kind of a

honestly almost not even a total data

warehouse um because it's more of a

combined big data and data warehouse

platform where there's a bunch of

different kind of subtools that make up

Azure synapse analytics that are now

bundled into synapse by Azure um so in a

provision mode so it has two different

modes provisioned which is like

dedicated and serverless provision mode

is a similar to you know red shift you

have a traditional cluster based

architecture you allocate resources up

front versus a serverless mode which is

more similar to big query where it's on

demand query execution pay by query pay

by gigabyte stored um and so you have

the optionality based on you know what

you need so if you're more dynamic or

more predictable you can choose the type

of mode you want to run in um which is

you know really nice to have and so

Azure synapse has some good scalability

options through through that dual

architecture where the provision mode

gives you that upfront resource

allocation scaling might lead to some

downtime but you can get some you know

credits and you can get some discounts

for provisioning and buying everything

up front versus the serus mode will

automatically scale your compute

resources based on the complexity of

queries um and then for large scale

operations synaps can actually

parallelize query execution across

distributed compute nodes um which makes

it really well suited for big data

analytics um and similarly synapse has

two pricing models you have provisioned

you have the provision model which is

going to charge based on the allocated

resources regardless of utilization so

even it's sit Idol you pay for it versus

the serverless model is going to charge

per terabyte of data processed um and so

while this provides flexibility it also

can lead to much higher costs for

workloads that require really frequent

resource adjustments or if you don't

have a defined strategy um so with great

power comes great responsibility um so

keep that in mind here and then

similarly to AWS query synapse is really

deeply embedded in the Azure ecosystem

so if you're using tools like data

Factory powerbi Azure data Lake it's got

really what nice built-in support for

spark pools and sinas pipelines for just

a you know all-in-one unified experience

across State Engineering data science

analytics um but obviously it's going to

struggle going outside of the Azure

ecosystem so it's a pretty good tool if

you're already invested in Azure

Services otherwise probably not worth

getting into Azure just for um and then

similarly

you know Azure is a government uh

contractor now so synap supports all the

advanced security features um you know

so you have Hippa gdpr Isis

certifications um and you also have

Callum level security Dynamic data math

masking and you can integrate with Azure

active directory um for Federation so

those are the big four data warehouses

out there in the market just kind of a

quick and dirty guide to each of them I

hope you've enjoyed this video video I

hope it's helped you make a informed

decision on which one is best for you

and I hope you have a great rest of your

day day to guy out day to dog out too