The Data Warehouse Dating Game:
Snowflake vs. Azure Synapse

×

Bob Looney
Sr. Director of Software Engineering
Senturus, Inc.

Bob leads software development and BI architecture efforts at Senturus, focusing on software and cloud architecture in the Power BI and Tableau practices. Before coming to Senturus, Bob was designing, building and scaling business intelligence software, reports and dashboards for use by thousands of restaurants.

Gary Lock
Sr. Microsoft Architect
Senturus, Inc.

Gary is a versatile solution architect at Senturus with over 20 years of experience ranging multiple industries and disciplines. An MBA with end-to-end expertise in business intelligence, he enjoys combining his background in business with the latest technologies in data and analytics.

Reeves Smith
Principal Snowflake Architect
Senturus, Inc.

Reeves is a Microsoft Certified Master and Snowflake – SnowPro Advanced: Architect. He has more than 20 years of experience working on development and data warehouse projects within a large variety of industries.

Read more

×

0:06
Greetings everyone, and welcome to the latest installment of the Senturus Knowledge Series. Today, we are pleased to be presenting to you on the topic of Azure Synapse versus Snowflake, comparing analytic data clouds.

0:20
Before we get into the heart of the presentation, we encourage you to submit questions via the GoToWebinar control panel to help make the session interactive.

0:30
We do try to answer all the questions at the end.

0:35
But during the webinar, in the event that we are unable to, for whatever reason, we will provide a written response document that we’ll post on Senturus.com, Along with the next slide here, where everyone asks, Can I get the presentation?

0:53
We put it up on our resource library. You can see the link there,

0:57
www.senturus.com/resources, and there’s a link in the chat, if you prefer to go there and go get it right away, we’ll put it there, and while you’re there, make sure you bookmark it, it’s got tons of great

1:11
analytics, business intelligence, content, that’s worth visiting frequently.

1:15
We post the recordings there, as well as the deck, and any other relevant content, including response, document, if we can’t answer all the questions.

1:24
So, our agenda today, after some quick introductions, we’ll get into the heart of the presentation or Azure Synapse versus snowflake comparisons and then stick around for a very brief Senturus overview.

1:36
For those of you who may not be familiar with what we offer and additional free resources, and then we’ll get to the Q and A.

1:47
I’m pleased to be joined today by several of my esteemed colleagues, Bob Looney Reeves Smith, and Gary Locke.

1:54
Mr. Looney leads our software development and BI architecture efforts here at Senturus, focusing on software and cloud architecture with Snowflake, Power BI, and Tableau.

2:05
Prior to Senturus, Bob was designing, building, and scaling BI software reports and dashboards for use by thousands of restaurants.

2:13
Reeves is a certified Microsoft Certified Master and Snowflake Snow Pro Advanced architect, with more than 20 years of experience, working on various development and data warehouse projects, within a large variety of industries.

2:27
And last but not least, Gary Locke is a versatile solution architect here at Senturus, with over 20 years of experience ranging across multiple industries and disciplines.

2:38
He’s an MBA with end-to-end expertise and BI who enjoys combining his background in business with the latest technologies in data and analytics.

2:47
My name is Mike Weinhauer, I’m a director here at Senturus.

2:50
Among my myriad responsibilities, I have the pleasure of hosting our knowledge series webinars, and before we get last thing here, before we get into the main presentation, we like to always ask our audience a question.

3:05
Get a sense for what they’re up to, relative to this presentation. And our question today is, where is your data stored now?

3:14
Pretty easy 3 choices, either on prem, VM based into the cloud, IE, sort of lift and shift?

3:22
Or are you using a cloud native app, such as Snowflake or Azure Synapse?

3:28
So go ahead and get those votes in here.

3:31
And we’ll give you a few more seconds to do that, and then we’ll share the results back with you.

3:41
We’re taking their time today.

3:47
Got about two thirds of you with your votes in here. Go ahead and get those last votes in, and we’ll close this out.

4:01
It looks good, and I’ll share this back.

4:04
So, most of you are still on prem, about two thirds there, quarter of you all using cloud native apps, though, and then the rest, doing, lift, and shift, So, always interesting to see what are clients and the folks joining our webinars are doing in their own organizations.

4:19
So, thank you again for sharing those insights with us.

4:23
And then, without further ado, I’m going to hand the microphone on the floor over to Bob. Reeves and Gary, gentlemen, take it away.

4:31
Thanks, Mike. Thanks for the introduction there.

4:34
Looking forward to sharing some great content about Azure Synapse and Snowflake here, some discussions with Gary and Reeves as we go through these various comparison points.

4:46
Both are great tools; we’ve started working with them with clients. And as we’ve done Sir, started discovering some of these interesting, oh, this works like this.

4:56
This other one works like, in a different way, type comparisons.

4:59
And so we’ve tried to pull those together into this webinar to, to share that information, maybe help you along with your decision making process, if that’s where you’re at.

5:09
So, we’re going to run through this, these points and talk through and do a couple of demos as we go.

5:16
So, we’re going to start with just kind of general overview of the platforms. There are both cloud based, kind of data, data cloud, data analytic cloud platforms. That means they’re highly scalable, and you get these demand-based computing resources, just your classic cloud advantages to being on platforms like this.

5:36
They both support the idea of creating SQL databases, so that as you can curate and structure your, your known data from raw data, you can get that into a SQL database. And it’s optimized, and all the technology under the covers is optimized for analytical purposes.

5:55
And, these architectures cover your typical use cases that you’re, you’re going to see in your environments these days, which is covering things like classic BI, you know, first of all, reporting, some of those type things.

6:10
It covers self-service use cases, as well as your data science and, you know, AI ML type use cases as well, so, we’ll touch on each and some of those points as we go through the webinar.

6:22
So, Gary, I’m going to hit you up to, kind of give us a little bit deeper dive into Azure Synapse is architecture here.

6:30
OK, great, thanks, Bob. Sure. So, with Azure Synapse, really, it brings together in one platform features and components that are available elsewhere individually in Azure and the Microsoft platform. But within Azure Synapse, you access it off of one workspace in the Azure portal.

6:50
So, within the workspace, there’s different components for ETL and storing and accessing data, compute resources, and database technologies.

7:00
So, for ingesting data into Azure Synapse, a flavor of Azure Data Factory is included, which is Microsoft’s modern ETL tool, and really a great replacement for SSIS if you use that.

7:14
For storing data and files, there’s Azure Data Lake Storage, which is integrated is Microsoft’s highly scalable Cloud Storage Platform.

7:23
For compute, Azure Synapse actually includes multiple different analytics pools.

7:29
First being this serverless SQL pool, which allows you to use SQL to directly access data in your lake.

7:36
And that can be like CSV files or parquet files where you came and hit multiple files that are stored within folders.

7:44
Besides that, there’s a dedicated SQL pool, which is Microsoft’s massively parallel processing engine, previously known as SQL Data Warehouse.

7:55
And that’s really designed for handling the largest of data warehouses, it’s good for when you have more predictable usage patterns. And you have, like, higher compute utilization.

8:06
And with a dedicated SQL pool, your data is actually stored in a database, and it can be accessed using T SQL, stored procedures, things like that.

8:14
And also, there’s Apache Spark. So, Microsoft has a serverless version of Apache Spark and Cloud, which allows you parallel processing framework that supports you in memory processing of your data.

8:27
And then, finally, within Azure Synapse is Analytics as well. So, of course, Azure Synapse integrates with Power BI and there’s also some Azure Machine Learning capabilities in there as well.

8:40
Great. That’s a, that’s a great overview.

8:42
I’m going to slide in a logged in Synapse, Analytics Workspace and lets you kind of walk us through a couple of pieces and parts and are in a real environment here.

8:51
Sure.

8:52
So, accessing the workspace from the Azure Portal, this is basically your homepage, so, you know, there’s always things changing here, down below, but, over in the left menu, you can see it’s really easy, kind of broken into components. So, let’s start actually in the bottom.

9:06
So, if you click Manage, here, as you can see up top, and we’ll get into a lot of this more later, but you can see it’s all within one workspace. You can get to your different analytics pools. You can set up link services with all types of external connections.

9:22
Um, and then once you started getting into integrations, you can set up different integration runtimes, and trigger them via schedule.

9:31
You have integrated security within, you know, Azure Active Directory, and things like that.

9:37
You can pull in code libraries for working with Spark, and then, we’ll get into this a bit more later, but source control, as well with Git, then, you know, once you do get things fired up, there’s the ability to monitor, and see how things are running, stuff like that, But, say, yeah, we’ll get into more of that later. But this is definitely, you know, the important thing here, is that it’s, it’s a single workspace. It really gives you access to everything in one place.

10:08
Great. Thanks for the overview.

10:10
Alright raised, so we’re going to toss it over to you to talk, to talk to the snowflake architecture a little bit, and maybe do a little bit of comparing and contrasting there, OK, Thanks, Bob.

10:22
So, snowflake, it’s a little bit different, and some of that difference is that snowflake concentrate more on the database side and leaves the tooling and that kind of stuff for other partners in the space.

10:34
And part of that reason is, as you can see in the centre, there, or we talk about data lake, it’s from each of the three cloud providers.

10:42
So yeah, excuse me, Snowflake runs on all three of the cloud providers.

10:47
And because of that, they don’t have the ability to create, are they?

10:51
They do, but they could, but they, they choose to concentrate on the database engine, and allow the providers that are cross-platform to deal with the different, the different cloud providers. So, it’s kind of a little bit of a different concept from that standpoint. It is a database and a compute, and they call compute and snowflake, a warehouse.

11:12
So, it’s kind of a misused or multi use term that can have some confusion. So, when we say, we spin up a warehouse is what we’re saying is we’re spinning up compute.

11:22
So that’s the snowflake side.

11:27
They are able to integrate with any of the data lake, like an S three and Azure storage, or Google Cloud Storage here able to ingest data through the data lake, and you’re able to use any of those ETL tools that that or clot cross-platform.

11:45
So, great for an offeror, talking to the UI yet, no, that’s fine, and we’re talking to the UI.

11:53
Um, as you can see, it’s very similar, but it’s just from a SQL development sniped standpoint. If you look, we have current database left, you can write your basic SQL statements.

12:07
Um, certain databases have, you know, it’s the same concept as it, it’s the same concept that it’s database schema.

12:17
And tables just like every other database, it’s not, it’s it doesn’t use different. It doesn’t use different language while you’re used to on your on-premises databases. But, with that, you’re able to change compute. And if you click over to the, if you click over to the Warehouses, we’ll talk about that for a second okra Warehouses to Yeah. Right there. As you can see, we’ve created two warehouses here. And you can create. You can create as many as you need.

12:41
And here’s a really cool feature of snowflake. I can create a warehouse for any department, so I can track departmental costs very easily. And there’s no cost. We’re spinning up.

12:53
Or there’s no cost with creating warehouses. They have to spin up time or that they have the ability to spin up on demand and then auto suspend based on some settings that you can set. Their already cannot suspend it yourself.

13:06
But it allows you to scale up compute very easily. If you have something running, you could If you have something running and it’s not working on an extra small. You could you could spend that compute up and right now you can spin it up to one.

13:18
For Excel, which is has quite a bit of processing power or so, but the nice part is that that auto suspend, so they turn off when they’re not in use, and you can set that are spent from wherever, whenever you’d like.

13:31
Absolutely. one of the best features. Yes, so, and then that one piece to finish it off.

13:37
If you click on, if you click on partner connect, show it to them over to the right. Here you go.

13:45
And here’s where those, here’s where you can integrate those partners in to two snowflakes. So, if you wanted to use a five trend, you can come here, and it just has some steps that will walk you through it a little easier. Because it already is somewhat in your, if you’re kicking it off, it’s from your account. So, there’s a semi-integration there with some of these tools. It seems like, yeah, OK.

14:09
Thanks, we’re going to dive deeper a little bit later, but that’s a great intro. Thank you.

14:13
mm. So, the next thing we’re going to talk to is kind of wide robust tooling.

14:18

These are both, you know, leaders in this space.

14:21
So, they, they both have no solid web interfaces for management support, major third-party tools for data analytics and ETL.

14:31
They have already BC drivers and command line tools as well for automation.

14:36
Things like that see, the three visualization tools, that Senturus as partners with there at the bottom, all heavily support all three of these platforms very well. So, if you’re having issues connecting Snowflake with Cognos or Tableau or something like that, we’re more than happy to help with those types of issues.

14:59
Um, so, Gary, let’s dive a little bit deeper into the synapse kind of workspace overview and show us how you maybe use this in a development environment.

15:11
Sure.

15:11
So, we briefly touched on this already, but so, the Synapse workspace, the web development interface has seen a lot of recent investment and updates within the workspace. You really have access to end-to-end BI capabilities. And there’s features for like data engineering, data science, data visualization.

15:31
As I mentioned, for data engineering, you have Azure data factory baked in into the workspace. It’s not 100% full Azure Data Factory as if you’ve ever worked with it.

15:41
You’ll notice there are some slight differences, but it’s pretty full featured. So, with Azure Data Factory, do you have a ton of functionality for transforming and moving data?

15:52
Uh, for example. Yes. So, here’s, you know, pipeline allows you to really orchestrate some of the transformations that you’ve made.

16:00
Actually, if you don’t mind, Bob, can you go over to Develop and absolutely show Do Flow, to start. All right. Yeah. Sure. So, Dataflow features a nice replacement for SSIS and has actually very easy drag and drop interface.

16:17
So, yeah, So, once you connect to a source So, of course, within Synapse, your source can be like, I think there’s 90 different types of sources. But, once you have a connection to your source, to show that, if you click that plus sign there.

16:33
And, as you can see here, you know, once you have a connection to your source, you can see all the different transformation activities that are available. So, this is similar to like an SSIS and where you can start getting into things like joining and doing lookups and aggregations and pivots and then pivots.

16:47
And you know, basically you can string activities together within this drag and drop workspace to make it very user friendly.

16:56
And really, most of the transformation activities in here are available without you having to do any real coding.

17:03
Occasionally, you might want to pass parameters back and forth, great variables, things like that, but for the most part, it’s, like, drag and drop. Otherwise, on this develop side, you can write SQL scripts directly against your SQL pool.

17:17
Or you can actually work with your data directly in an Apache Spark pool via Notebook.

17:22
Yeah.

17:24
The, yeah, I mean, in the web browser, kind of, interact directly with your data, which is great.

17:30
Kind of gives you a little bit of the kid, pre written as you get ready to go here.

17:35
Exactly.

17:37
Yep. And then, yes, you had showed below that with the integrations when you start getting into pipelines is where you can really orchestrate all kinds of activities, including data flows.

17:47
Synapse Notebooks, you can actually integrate with Azure functions, data bricks, Notebooks, you SQL scripts, web APIs, stored procedures, and even there’s some machine learning down at the bottom there.

18:01
Yeah, so it’s, you can see, you can get to a ton of different things depending on what your expertise is and what you’re already using within your environment.

18:09
Then also, you know, as you’re working in here, you can integrate directly into get, so up in the top there. So, we’re working in the dev branch, but you can actually save and commit your changes directly into Git, which is very nice. There was a quick, quick setup.

18:27
I guess it’s the last note, you know, with the SQL pool’s, whether dedicated or serverless. You can, you can also work in SQL management studio if that’s where you’re comfortable. Or Azure Data Studio. So, you can connect, just like you would connect to any other database.

18:43
So that’s a nice feature. Although, when you do start working in there, you will realize that it’s not 100% like, working with, like, an Azure SQL database or an on prem SQL database. Some of the features are different, or just aren’t there.

18:58
Thanks.

19:00
Are Reeves’, let’s talk a little bit about snowflake, and you’re going to develop, again, Snowflake.

19:07
Already, Like I showed.

19:10
with the web UI, you can do a lot of your development within the web UI.

19:17
But there’s not really any integrations.

19:20
So, what I mean by that is code source integration, so there’s a tool that I use.

19:28
See?

19:32
You were seeing it? Perfect, OK, OK, thank you.

19:35
So, there’s a tool that I like to use and its data grip and it’s a really, full, featured UI for Working with Databases, and as you can see, I can do all the same things that I was doing that I was doing from within the within the Snowflake UI.

19:52
But now I’m able to, now I’m able to change or track those changes very easily, because this tool integrates very well with Git. And I’m able to see and commit changes as I’m going along. And I, and another nice feature that it gives you is, is code completion.

20:08
I can see, I must have typed something incorrect here, and I’m like, oh, it’s demo DB.

20:15
I can come in here, update that, and this UI understands that, OK, those fields are, those fields are, are now valid, and it’s not, it’s, it kind of helps you out. So, I think, that’s kind of a nice piece to this.

20:28
And then, I can make that, I can make that commit, right, I can come in here, and I can.

20:35
Oops, that’s the Maybe I didn’t know that. But I can make a commit here on that code change, and then say that up to my source code repository. That is the one thing that the UI doesn’t give us, right? In.

20:48
the UI, from here, I can’t keep this code, are put this code into a repository without cutting and pasting it out. So right now, that’s a small limitation with the way that their UIS kind of setup. So that’s where I like. Tools, like, you know, a date of grip that allows me to do that kind of stuff.

21:04
And I can keep my projects and I can, you know, the authors deal with any type of database, right. I don’t have to be dealing with a snowflake database. I can, I can deal with any of the other providers.

21:17
So, I can set up a connection here. So, this, this environment, I think, is really, really nice for working with Snowflake.

21:25
Love it.

21:25
And, yeah, I would assume with both of those, you could then just layer on your kind of CI CD processes, as far as promoting changes to staging and right things like that that you’d have in a typical enterprise environment, right.

21:39
And then, with the other tools, like what I showed on that, it’s in the marketplace, a data marketplace, now, the other tap, sorry, the partner Connect.

21:50
Yes.

21:50
You’ll be using, you’ll be using the interface that they app with those.

21:55
Got you.

21:57
All right, Well, let’s move along, and let’s, let’s talk a little bit how patent these two tools are passed versus the platform as a service versus software as a service.

22:08
If you’re unfamiliar with those terms, like this, Microsoft provided graphic here, that kind of talks about where, each of the, there’s three commonly used terms there, and, but, it’s, it, it is a key differentiator here for reasons we’re going to talk about. So, Gary, I’m going to let you, go ahead and talk about the Azure Synapse side of being a platform as a service.

22:34
Sure. So, you know, being a platform, it is kind of a collection of Azure resources, which I mentioned earlier. They are available individually, but this brings them together, really, as a platform.

22:46
So, you know, as you go into the Azure portal and you want to fire up Azure Synapse, you’re basically, you know, creating some different resources within Azure. So, you’ll, you’ll want to either choose an existing or create, like, a resource group.

23:03
And, at the same time, you’ll spin up edge data, like storage, as well as your, your SQL pool.

23:12
Yep, so, here’s the, here’s the setup page that you would walk through, and you would choose your subscription.

23:18
And, like I said, either you would choose an existing, or create a new resource group, then, set up a workspace. Choose the region, and one thing we did find is, you as we’re kind of talking about this and working on the webinar, it’s interesting to see that the pricing for some of, these features are actually different in different regions. So, like, a west US, two versus a West US. The pricing can be different.

23:39
So, depending on where, where are you doing your work and where are some of the other services are held, you may want to consider that.

23:48
But, really, at its core, once you fire up Azure Synapse, at its core, you’re starting off with a serverless SQL pool, Azure Data Lake Storage and an Azure integration runtime.

24:02
I don’t know if you want to bounce over to the actual environment that you had spun up. All, right, perfect. And go to manage.

24:11
And up top, under Analytics Poolesville, SQL pool. Yes, when you start off, you’re going to start off with a Serverless SQL school. It’s called built-in.

24:19
This one cannot be deleted.

24:21
So that’s kind of just, it’s there for you when you need it. And with the serverless side, it’s similar to snowflake in some ways. In that it’s, it’s great for ad hoc querying of data within your lake.

24:33
So, you can use T SQL without having to, or any reserve any capacity in a dedicated SQL pool.

24:40
But also, here within SQL Pools, you can fire up, you know, a dedicated SQL pool or multiple of each.

24:47
Then, under added Analytics, pools is where you could also fire up an Apache Spark pool, depending on what your needs, what your needs are.

24:56
Great, and when thing we’ve found is what these dedicated SQL pools, they kind of tie 1 to 1 with the database. Is that set the right way to think of that?

25:04
Yes, Yep, and I’ll get into that little more later, but that’s definitely the case. And then we get into pricing a little bit more later, just to give a little more background on that.

25:14
Perfect. But, so, you see, you have the SQL pools and then down below, you see external connections. So, that’s just a great feature within here with linked services. So, when you initially start off, you’ll have to link services.

25:25
one is connecting to Azure Synapse analytics are kind of your SQL pool, and also to your Azure data Lake store Bridge. So, that allows you to, you know, start doing integrations between those two environments. But you can also add linked services to, like, 90 other things.

25:43
And those. Yeah, that’s great. So, you know, within Azure, you can connect to, like, blob Storage, or Cosmos DB, or if you just want to connect to an Azure SQL database, maybe some transactional data out there, you can do that. If you want to use additional compute, you can connect to Azure Databricks, or an Azure function, Azure Machine learning.

26:03
On the database side, you have, you can actually connect to like Amazon, Redshift, DB two, Google big query or Oracle, and then with files, you know, if you don’t, if you have some data that’s outside of Azure, you can actually connect to S three.

26:20
You can connect to Google Cloud Storage, Perfect.

26:26
And then also, when you when you start off, you’ll start off with one integration runtime, which is basically in Azure, you know, a place where compute happens.

26:36
But you can also spin up additional integration runtimes here. Whether they be in Azure or self-hosted.

26:41
Which you can have your on prem server that allows you to do some types of activities there as well.

26:50
And these integration runtimes, these are for pulling in on prem data in this case.

26:56
You can, Yeah, so if you did have almost like a gateway. It, like, it’s, yeah, it’s similar to a gateway. Like in Power BI, where you set it up to zero point or work on an on prem server.

27:07
and then yeah, so, like recently, I worked with a client that had data on prem in an Oracle database, and you basically set up that runtime and then pretty quickly, you’re working with on prem data, and pulling it in, and then, you know, transforming it and working it in with other types of data as well.

27:26
Great, thank you.

27:29
Alright, Reve, same thing, but let’s talk about Software as a service now.

27:35
Already, ah.

27:36
So, let me have, I’m going to get a screen, let me share the screen. So, it’s not like it takes a little bit different approach, right? It is abstracting as much of that as it can to just make configuration a little bit easier.

27:50
And, as you can see, similar to the pricing that we’re looking at from a snap standpoint, yeah, the same kind of idea here, except for a big, is, you can pick any of the cloud providers right into AWS, Azure, or Google Platform.

28:07
And why that becomes important, is, as you’re searching for a cloud, if you’re searching for a cloud database, you don’t have to go with the provider based on the cloud. You can pick what you want to do, and then you can pick the cloud.

28:23
That is, is most relevant to you, and where I see that is, if you have, if you have a lot of data lake Asset’s, already, out there, and a cloud, you don’t have to move those data assets out of that cloud, and into a new cloud.

28:37
You can you can just go. You can set up snowflake within that region, and you can actually set up snowflake within any region, right? And you can set up multiple.

28:45
You can have snowflake, and AWS, you know, Snowflake in Azure, and they can actually replicate between each other.

28:54
With snowflake is what you’re doing, is you pick a region, and you pick, or you pick a provider, you pick a region, and then you basically decide, you know, what level of features you’d need.

29:05
From a standpoint of do I just need standard enterprise business critical, or BPS?

29:11
And those features just come on a per cost, or cost per credit basis.

29:17
So, your just your credit just costs a little bit more as you need more features.

29:22
So, these, these three are pretty standard. This one is if you decide I want to be in my own VPC. I do not want to share the VPC with anybody.

29:31
And that comes with, you know, this is, this is a little bit different, and it creates and make some changes on availability of resources as well.

29:41
But these three are the pretty standard ones, and then you just pay per monthly charge, on per monthly, charge, on, on storage.

29:49
So, there is a no there.

29:53
You could say buttons and nobs to turn are very limited from a standpoint of you just pay per credit.

30:01
Well, resources are running and he’s playing on years pay a storage fee per month, so it’s kind of a little bit different from that standpoint. But that allows you to, you’re not managing as much, right? And what I mean by that, and the big feature that we’ll talk about is snowflake doesn’t have indexes.

30:18
And anyone that’s worked on database, they might be going, oh, my word, but I can attest to the fact that I’ve worked on a petabyte scale database, and we, and they’ve performed very well.

30:30
I’ve got no queries where we’re running, you know, multibillion row records, and, and they were coming back, you know, and, and, very quickly.

30:42
So that’s not necessarily an issue, which allows you to concentrate on, just getting, you know, just getting requirements done, and not having to worry as much about tuning.

30:52
Sure.

30:53
I found that use case interesting, where you could replicate your databases across clouds, but still be in this, you know, in a snowflake, very familiar sniffs like environment.

31:04
Right? And it’s, it can be an easy migration path, right? So, I could, I could migrate my data over your application and then turn off the previous turn off the previous plot, if I wanted to.

31:14
So, it lends some unique, unique ideas where you’re not necessarily tied to a cloud provider.

31:21
You can move, know, you can move within three cloud providers. Fairly easy. Yeah.

31:27
No, that’s a, really, that’s a really cool come use case.

31:32
Alright, so we’re going to keep talking a little bit more about architecture, at least as it pertains to compute. And this is little downhill, weeds a little technical.

31:41
But, um, it’s also very important to understand why, why these two systems are different.

31:47
So, no, you need compute capacity, anytime you’re performing the actions on your data.

31:53
And then, in both systems, and we’ll talk a little bit deeper on pricing, bed capacity is always priced by how much you use, how long you use it, and then what performance level you’re looking for the system.

32:07
Said, Gary, we dive deep with us.

32:09
A little Nana, kind of how Azure is approaching compute capacity with Synapse.

32:16
Sure. So, of course, within Azure Synapse, there’s multiple options when it comes to compute.

32:21
But focusing specifically on dedicated SQL pool, which, as I mentioned, it’s Microsoft’s parallel processing engine within SQL. So, there’s some interesting things to note.

32:34
First one, as you mentioned, Bob, a dedicated SQL pull aligns with one SQL database, and then also, that database can only be accessed from the SQL pools running, which is different than the Serverless SQL pool, which is always available.

32:48
But the dedicated SQL pool can be scaled, stopped and resumed either manually or via the portal, sorry, manually via the portal, or programmatically via an API.

32:59
Then, also, you can isolate and prioritize your actions using workload groups within the pool so that, you know, loads don’t take up too much too many resources, or, or maybe one individual doesn’t take a lot of resources either. But really, you know, thinking about architecture and under the covers, as you can see in this, this screenshot here.

33:20
The way it works is that there’s a control node that handles orchestrating your queries and passing operations to compute nodes.

33:28
And then, really, those compute nodes perform the work in parallel, using between 1 and 60 different nodes, depending on the service level you choose, so if you have the lowest level of service, you’ll have one node, and if you pay for the highest-level service, you’ll have 60.

33:44
Um, so it basically, it splits your queries into 60 smaller queries.

33:50
So, running the queries in parallel, of course, is where you get the performance boost.

33:56
But then also, you know, with a dedicated SQL pool, your data is actually stored and managed in Azure storage.

34:03
So, when you go into that database and you’re creating tables for your data warehouse, you actually had the ability to choose a distribution method for each table within the database. And I won’t get into the details here, but you have three options. There’s hash, there’s round robin and replicate, and basically the different distribution methods available.

34:22
They each have pros and cons and based on the size and usage of the data within the table. And this kind of gets into what Reeves was mentioning about.

34:31
Know, do you want fewer settings view out more settings? So, this gives you the ability to make those fine tunes that you might want to make and, you know, I mean, I, I’m not trying to knock Snowflake at all here, but, you know, the black box approach, a lot of times, is great until it’s not great. And then you’re like, well, what’s it actually doing? And then you don’t have that ability, So, just, definitely something to consider there.

34:57
Thanks, and so, so, just to clarify, you are saying the performance setting, you’re going to get more nodes and more kind of resources as you as you scale up. Exactly, your overall dedicated SQL.

35:09
Right.

35:11
Thank you.

35:12
And so, re-used, snowflakes, that’s all, that’s a little different.

35:17
Yeah. You have, you have the concept of compute and it is completely independent.

35:25
You can have as many as you want running. I would imagine. There’s some actual limit, but you can have quite a few.

35:31
Quite a few warehouses is running and they can access the same data and you don’t have to worry about doing data. You know, doing data federation or moving data out into different.

35:46
In the different nodes, it handles all that because it’s using, it’s using basically S three or Azure storage, that’s letting Azure storage and, and those services that are able to provide multiple connections to.

36:00
The same to the same data. And it doesn’t have to worry about that. So, he can set up these multiple Reese. These multiple computes like warehouses so that you can access that data.

36:11
So, you can run your data transformation during the day without any concern. And it, it will not interfere with, it will not interfere with the other warehouses.

36:19
So, it’s a little bit different from that standpoint, and it has the ability to scale, you know, to scale out.

36:27
I can, I can create a warehouse that has multiple nodes associated to it.

36:34
And as demand increases, those nodes can be added in.

36:38
Or, we could say, more warehouses are added in, handle that load, and then they will spend down as they’re not needed. So, it’s pretty nice from that standpoint.

36:46
I can also scale up and scale down, two, to bring more compute into, into data transformation or data, no query that I might need.

36:58
So, it’s different from that standpoint and You know, it’s like we’re saying is less nob and that can be kind of scary, but I’ve been in enough situations where I’ve seen that.

37:10
I haven’t had to I mean, they have somewhat of an option, which is called clustering, which enables, you know, better pruning of the data.

37:19
But we haven’t seen the need to do that in huge datasets.

37:24
So, I mean you can have some benefit in doing it for sure, but it hasn’t been what I would term as needed because the performance wasn’t there. So just a little bit different from that stamp.

37:36
Interesting.

37:38
As it’s kind of interesting to see how they took two different approaches to assault, to those solving this idea of contention and, hey, I want to load data while other users are querying data.

37:49
Um, and, they kind of, both got there, slightly different ways, but both systems work well as well.

37:57
All right.

37:59
Going to touch on a couple of system specific things now. So, we’ll start with Azure Synapse and then, Gary, I think we kind of already did a short demo here, this one, but you want to keep talking to this.

38:11
Sure. Yeah, this will be brief. I mean, this is just, one thing that’s nice in Azure Synapse is the Apache Spark pool. So, you know, if your team’s expertise or preferences work, working directly in a Spark notebook.

38:23
And, you know, say you want to quickly load SQL tables into data frames.

38:27
You can spin up an Apache Spark pool and develop in Python or Scala or c-sharp or Spark SQL.

38:35
And its successful right in the synapse workspace and then of course the data involves here. And the notebooks can be integrated with data flows and other activities within a pipeline. So, when you start scheduling your jobs to run, you have multiple different computes. So, you can use with Apache Spark being one of them, and actually in a similar fashion, you can set up a linked service to Azure Databricks as well, and develop in a Databricks notebook or a Python file there.

39:05
Yeah. It really just kind of depends on your team’s capabilities and what technologies you prefer.

39:09
But it seems like you get to just about anything you want to use all in that same environment. Very, very slick.

39:18
And review and attack and a couple Smith snowflake specifics Here. Pass me back the screen. kind of walk through a demo.

39:31
And what we’re going to show here is, I’m going to show, kind of, two things really quick is we’re going to talk about Cloning, which is a unique feature, which is unique feature to snowflake.

39:40
But to kind of set that up, I’m going to, I’m going to start back a little, I’m going to back up a little bit, and kind of just show kind of a performance thing. So, kind of just do that. So, I’m going to create or replace this table, which means I just created this table and there’s no data in it, right?

39:55
So, Rohn, you can see there’s no, there’s no data in this table.

40:00
So, I’m going to load this NBA copy command which there are files that we have out on Azure storage that we’re going to grab and load into this table.

40:09
So, I’m going to kick that off, and that’s going to take a second.

40:11
As you can see, it’s running.

40:16
This is loading a little bit ETS or doing a little bit ETL into that table.

40:20
And it’s going to take seconds somewhere around there.

40:26
I’m going to show you then how many rows run that.

40:28
And as we saw when we first, when we first ran this, there were no rows, right? So, we now have all the we had these files that loaded.

40:36
And to kind of show you from a performance standpoint, we just loaded 37 million records in what was the amount of time I go back 23, 24 seconds. And that is on.

40:52
As you can see, we’re on an extra small warehouse, so that’s a $2 an hour chart.

40:56
So, I think if I were to do the math on that one, it was pennies to just load that, right?

41:01
And nothing was there’s no sleight of hand here. I haven’t sorted anything. I haven’t done anything.

41:07
And if I were to just look at aggregations among that 37 million rows, you can see that comes back in less than a second. And I just aggregated all that data in less than a second.

41:19
So, from a performance standpoint, I want to say snowflakes off the charts.

41:24
but let’s just pretend that this data is our production data, and we want to run some tests.

41:30
We want to run some, you know, set some of our, some tasks are some scripts against that test data, but it’s like, how do we always do it? Because in dev, we never had the, we never have the, you know, the data, exactly, the same. Can’t replicate it, the bug and dev, because my three-row dataset just works every time.

41:51
So, what’s a nice part that you can do? And its snowflake, because I can create a database that has a clone of that database. And it’s a metadata operation.

42:00
So, it happens very quickly.

42:02
And as you can see, the current demo is not over here. And if I refresh, I now have a current demo.

42:08
And I have a table in here, and if I were to preview that table, you can see that I have that 37 million rows.

42:14
So, all of a sudden, I’ve just created that exact same, that exact same thing.

42:18
So, if I were to now go in and update that query, which will take a second or two, because it is a update over that 37 million rows, I think it’s 500,000 rows that I need to update.

42:30
So, it takes it Yeah, half a million rows.

42:33
I’m going to steal that ID, I’m going to steal that ID, then go back. I didn’t mean to do that.

42:46
I want to put it there, just Sleight of hand, OK. So, I’ve got a cell, if I want to look at what my current, what the current demo shows, from a price standpoint.

42:58
I can see that I’m at about $23, right, because my process that I was doing here was my eye.

43:06
ETL is short, my machine learning, or my, whatever, you know, increase.

43:10
The price for just, this customer, was, my, was my scenario.

43:15
So, I can see that that price has been affected, and then I can go back to the demo database, and I can see that nothing’s been affected.

43:23
So, I’m able to run these transformations against that production dataset.

43:29
And is what ends up happening is I only create micro partitions if the ones that I changed.

43:33
So, you’re still using that same, you’re still having access to that same data that says that, that’s in this database, along with all the records that you changed here. So, it’s keeping that differential, and understanding how to piece those things back together.

43:48
So, you haven’t heard production at all. I haven’t touched this at all, so nothing has changed there.

43:54
And, you know, that’s, that’s really cool. From a testing standpoint. I can test against the same, exact same thing that I’m seeing in production.

44:02
And if I want to see both of the results together, right, I can query both of them. At the same time, I can see the difference between the two.

44:10
And then, just as a, you know, just as an example of time travel, I still have access to go back.

44:17
If you remembered, I grabbed that query ID, I can say, what did this query look like prior to this query ID.

44:23
So, if I do that, I’ll see the exact same data that’s in exact same data that’s in my production database, because I’m saying, what did that look like prior to that, with that execution rant.

44:35
So that’s, it.

44:36
Opens up a whole new way of being able to test because you’re able to test against production data very easily.

44:45
And then I can drop that database and be done with it.

44:50
And it’s, and now it’s gone.

44:53
And I have an affected production at all.

44:55
Let me just prove that by nothing in production has changed.

45:01
So that’s kind of a neat feature.

45:03
That’s kind of a neat feature for, for snowflake, is the data or the cloning and the time travel.

45:09
It’s pretty slick.

45:12
Yeah.

45:14
I think we’ve already touched on this last point here, the less administration versus a synapse having kind of more settings and more configuration ability.

45:22
Um, so, let’s talk pricing Just a little bit.

45:26
We tried to put together something high level to, you know, pricing is so, customer specific, but we did want to touch on how it works. We’ve already talked a little bit on some of this.

45:37
But compute and storage are billed separately in both products and its consumption-based compute, as we’ve talked about, all, about how much, how long you use it and how powerful it is when you’re using it.

45:49
And then that last point, there are options to pre-purchase or reserve some of this, some of these resources to save you money.

45:56
So, if you’re, are using them, and you’re running them all the time, and think about that.

46:02
So, Gary, you know, kind of walk us through here, this and apps, approach to pricing.

46:09
Sure, yeah, and this, this pricing is focused specifically on pricing related to the Azure Synapse SQL pools and not anything else. That’s available like transformations or other integrated services. But, for the most part, pricing is broken down into three different components, as you mentioned. You know, first being compute.

46:27
So, for the dedicated SQL pool, I mentioned earlier, you choose a service level based on your needs.

46:34
So, within those service levels, they have what they call Data Warehousing units. You can see in the screenshot there, the Smalls Being 100, and you, can see there, it starts at a dollar, 20 an hour.

46:43
You can scale all the way up to, I think, it costs like $360 an hour, and that’s where you get the full, you know, 60, 60 nodes and things like that.

46:55
But the one thing with dedicated SQL pool, if you know you’re going that route and you want to reserve space, you know, Microsoft does offer up to like, I think a 65% discount with three-year reserved pricing.

47:08
But, you know, depending on your needs, with Land Anywhere. Kind of between the lowest and highest amount. With the serverless SQL pool, it’s pretty simple.

47:18
It’s $5 per terabyte of data processed. Since if you’re not really storing data in a database, you’re really just consuming data directly against your data lake.

47:28
So, it makes it a bit easier.

47:29
But, you know, there’s different pros and cons to that, of course.

47:34
Within the dedicated SQL pool, you are storing data in your database. So, with that, it’s like $23 per terabyte of data process. You can see on a note there and kind of what’s included in the data storage.

47:48
And then with both varieties, you’re, you’re consuming raw file storage that’s in your data lake. And you can see the screenshot there, yes? like, I think, 15% per gigabyte in their premium tier.

48:00
So there’s different components and different pieces to the pricing.

48:05
Yeah. Lots of nuances for each organization.

48:08
And Reeves you kind of touched on some of the additions and credit pricing here earlier, anything to add, here.

48:16
Now, just, yeah, you’re paying, basically.

48:19
You’re paying basically compute costs and storage costs that are eager as costs that everybody has to pay for, if you’re addressing data in and out.

48:29
But if you’re all within the same provider, that’s, that’s not going to be, that’s not going to be a cost You pay for, but it is a cost that could happen, but it’s pretty straightforward. And they have, I think, as, as I was shown on the screen, A little bit discounted price if you buy price, if you buy storage.

48:47
If you pre buy storage, sir.

48:51
You want, one thing, if you that I was thinking about is, you can, you can also query within smelt like I didn’t mention it.

48:57
You can query within snowflake data that is in your, your data lake, without having to bring it into without having to bring it into snowflake.

49:06
Once it lands in snowflake, the causes, it’s fairly minimal. So that if there’s, the reason I would see is that as if you wanted to look at files. But don’t look at them very often, and just don’t want to bring them into your system. I can set up what they call external tables. And those external tables. I can query the data and the data lake without bringing it in.

49:25
Got you.

49:27
So, we’ve tried to mock up a couple of just, use cases.

49:31
And, again, your mileage may vary based on your organization, but in this first example, we just said, OK, well, let’s pick the smallest compute that you can spin up on each platform.

49:45
And let’s assume a one terabyte SQL database and, you know, kind of ignore all other cost and just that you run that compute all the time.

49:53
And so that’s kind of what your, your monthly cost breakdown looks like.

49:57
In that case, you know, you can argue fair, not fair, based off of so many factors here, but we tried to, kind of flip that on its head with the second example, where we showed more of it.

50:10
Variable demand pricing, and using that auto start auto pause feature in snowflake can really save you a ton of hours.

50:19
And then, over on the Azure Synapse side, maybe you’re, you’re only running it during your business hours or something like that.

50:27
Know, there’s so many different ways to look at pricing and so many different organizational specific use cases. We would be happy to dive deep on that with anyone, but it’s hard to wrap up at a high level to say, oh, this is the one to pick based off of a price alone, if that’s your, your driving decision point.

50:45
So just a couple of things to think about there.

50:48
Then a little bit of a recap table to kind of show maybe the strengths and weaknesses of each platform.

50:57
I guess I knew Gary and Reeves could probably argue certain points on every single line here.

51:03
Um, you know, I revisit one of the last things we talked about. It, was that SQL Stored procedure? you want to touch on that? Because we haven’t, we didn’t, that’s one, yeah. That’s the one feature that I gave you, guys.

51:18
Yes.

51:21
So right now, it’s a snowflake. They have stored procedures, but their JavaScript or procedures, and they’re a little different than the stored procedures that everyone’s really accustomed to using.

51:31
And one of those, one of those differences, the fact that a stored procedure cannot return result sets.

51:37
A current JavaScript store procedure in snowflake cannot return results it.

51:42
So just try to be transparent. If you’re a heavy, if you’re a heavy store procedure shop, there will be a little bit of pain in converting those over to JavaScript.

51:51
But it’s actually not that difficult, but there’s also the fact that you’re not going to be pulling back results from stored procedures.

51:59
So, there’s just a little bit different, and that’s the thing, and I want to call it out, and that’s why it’s Asterix, because it is on the roadmap to add SQL stored procedures.

52:09
So, that is something they’re doing and it’s just something that, they don’t, they don’t currently, got you Gary.

52:17
here, anything you want to point out at the high level here on the Azure Synapse column, maybe you don’t agree with or you think should be a heavy consideration.

52:28
Know, I mean, I think I already mentioned, you know, kind of the ease-of-use thing. I mean, I guess that’s a preference but, I mean, they’re both great platforms if you’re a Microsoft shop already. I mean, I think Synapse makes sense. But if you’re not and maybe you’re already heavily invested, like ETL tool against radicchio or, you know, data stage, I mean, Snowflake is definitely a great option as well.

52:50
All right.

52:51
That sounds great.

52:53
Well, Mike, I think we’re going to hand it back over to you at this point, that the kind of finish rates are comparison of the two products.

53:01
Thanks, gentlemen.

53:01
That was pretty seamless between the three of you. Everybody sticks around, we got some questions here in the question panel. I’m going to run through a couple of things here. As you can tell, there’s a lot to consider. When trying to decide which one is the best data platform for you. So, we have’s interests, we do this all day, every day. We got out of bed in the morning for our pants, eats and breakfast.

53:23
And we can help you do this to evaluate your unique environments and requirements and provide unbiased recommendations, whether that’s cloud hybrid or on prem, give you best practices to optimize your spend and performance, as well as guiding you, and, or even executing the implementation. So, we have that experience across these different vendors and can offer this expertise and a truly unbiased.

53:48
Quickly about Senturus, we concentrate our expertise solely on business intelligence with the depth of knowledge across the entire BI stack.

53:57
Clients know us for providing clarity from the chaos of complex business requirements, disparate data sources, and constantly moving targets.

54:05
We’ve made a name for ourselves based on our strength at Bridging the Gap between IT and business users. We deliver solutions that give you access to reliable analysis, ready data across your organization, so you can quickly and easily get answers at the point of impact, in the form of the decisions you make and the actions you take.

54:22
Consultants are leading experts in the field of analytics with years of pragmatic, real-world expertise, and experience advancing the state-of-the-art.

54:30
We’re so confident in our team, and our methodology that we back our projects with a 100% money back guarantee, that is unique in the industry.

54:38
We’ve been doing this for a long time, about two decades, at this point. Over 1350 clients, and 2500 successful projects.

54:45
Working across the spectrum, from the Fortune 500 down to the mid-market, solving business problems across virtually every different industry, and functional areas, including the office of finance, sales and marketing, manufacturing, operations, HR, and IT.

54:59
Our team here at Senturus has built large enough to meet all your business analytics needs at small enough to provide personalized attention.

55:06
We do, again, invite you to visit Senturus.com/resources

55:09
and bookmark that you’ll find today’s webinar, other webinars, and a bunch of other tips and tricks and great things BI, including our fabulous up to the minute easily consumable blog.

55:26
In terms of upcoming events, similar to this one, we have an anomaly. We have our Power Query Editor Jumpstart that we’re going to be doing actually on a Wednesday.

55:36
And then we have which is next Wednesday, same time.

55:39
And then we’ll be doing six ways to publish and share Tableau dashboards a couple Thursdays from then, so head on over to our resource page and register for that and keep an eye on it.

55:49
We’d be remiss if we didn’t mention our comprehensive BI training offerings. Bob mentioned the three platforms that we focus on here.

55:56
Analytics platforms that we focus on here at Senturus is Cognos, Power BI and Tableau, and we offer training in all of those and are ideal for organizations running multiple platforms, or, those, perhaps, moving from one to the other.

56:09
We offer all the different modes, tailored group sessions, mentoring, instructor led online courses, and self-paced e-learning, and can customize that to best meet the needs of your user community.

56:21
And then, finally, we provide hundreds of free resources on our website. Some of those I’ve already alluded to, and I’ve been committed to sharing our BI expertise for over a decade.

56:32
I went through that very, very rapidly.

56:36
We do have some questions that, hopefully, gentlemen. I’ve had a chance to take a look at the panel here.

56:43
And one of the questions was, can the version of Azure Data Factory and Synapse do that SSIS cannot and how about the converse of that?

56:57
Yes. I mean, you know, on the spot here, I don’t know that I can pinpoint something that you can’t do in one or the other.

57:04
I think if, depending on how good you are, you could probably accomplish the same thing in both, but the idea is with, With Synapse, you know, it’s designed for the cloud. So, it just makes it so easy to connect to all different types of sources, you know, whether it be something that’s already in Azure, or if you want to connect to something in AWS or Google Cloud. It’s just the idea of that, you know, with self-service and, you know, cloud in mind, this tool is designed for making it very easy to consume data from multiple sources.

57:39
Hopefully, that answered that question.

57:42
Is the data pipeline in Azure Synapse the same as Azure Data Factory?

57:48
So, it is mostly the same. So, Azure Data Factory would be the full featured data set or it has all the capabilities. in Synapse there are a few key things that are missing. But of course, and, you know, as is usual with Microsoft, they have a roadmap and, you know, things are on the schedule and just not there yet, but they are really the same. When you start working with them, they’ll look and feel exactly the same, but there are a few settings that you might find aren’t there and synapse?

58:19
Got it. On the snowflake side when compute is turned off.

58:24
What kind of performance can be expected from the first query issued?

58:34
Compute is stopped. It will start back up.

58:36
Most of the time, it starts up in a very quick manner, I would say, a couple of seconds, there are times I’ve seen where you’re looking for resources. It takes a little bit of time to spin up, but it’s nothing like spinning up your own compute. It’s, it’s almost instantaneous.

58:52
And the only place that that can become a bigger issue is with your own VPC, and that addition, you don’t have this pool. You don’t have as much of a pool spinning in waiting. So, there’s, there’s a little bit of a difference here, but within all the rest, it’s pretty quick.

59:10
I’ve seen, I think once or twice while I’m trying to do work where it took maybe 30 seconds or more, but not very often. So, it spins up very quickly And, again, it can spend down so you’re not paying for that cost.

59:26
Great, good to know.

59:28
So, we actually have a great webinar up on the Senturus’s resources website where we discuss five different ways of doing ETL and we do have several that talk about various different types of data preparation. Some are platform specific, some not. So, definitely head on over to that Resources page, and you can find that one.

59:49
The one that we’re referencing earlier is, was presented by Andy Kinnear.

59:57
So, you can find that up there on the Senturus’s website.

1:00:05
There’s one last question about what are the key criteria that would make you choose one over the other?

1:00:11
Gosh, that’s the million-dollar question, right? And I think I would argue that, I hate to say it, but it depends. And the key criteria are going to be the ones that are most important to your organization.

1:00:22
Is it scale, is it availability, is it costs?

1:00:26
And what are the relative importance of those specific features? So again, those are the sorts of things that we help people do all the time, so if you want to drill into that, we’d be happy to chat with you about that.

1:00:39
If any of you have anything to add to that, that I might have overlooked but that seems like.

1:00:56
So, with that, I’d like to thank our presenters, Bob and Reeves and Gary. Thanks for taking time out of your day today to join us for the Knowledge Series presentation. If you have any business analytics needs, we do encourage you to reach out to us, you can e-mail us at info@senturus.com. Still use the phone, we’ve got a 888 number down here for you, and we’d love to hear from you. So, hopefully, we’ll see you all very soon on the next Knowledge Series presentation, and have a great rest of your day. Thanks a lot.

Read more