(upbeat electronic music)
- Coming up, we'll look at Azure's
Modern Data Warehouse solution pattern,
which provides you with a robust foundation
for all enterprise analytics, spanning SQL queries
through to machine learning and AI.
Now, we're gonna start by looking at
the core updates to Azure's SQL Data Warehouse,
ranging from granular security at the perimeter,
all the way down to row and column level security.
Big improvements over query performance
and more with new workload importance capabilities.
And we're also gonna walk you through
a real Modern Data Warehouse example,
drilling into how Microsoft Digital Crimes Unit
is leveraging the Modern Data Warehouse
to stop tech scammers in their tracks.
(upbeat electronic music)
I'm joined today by Charles Feddersen
from the Azure Data Team.
Welcome to the show.
- It's great to be here.
Thanks for having me.
- So, when we had John Macintyre on a few months back,
we actually looked at data warehousing in Azure
and we demonstrated the benefits of Data Warehouse Gen 2,
and how you could really benefit
in terms of a query performance,
higher query concurrency, lower cost.
Now, if you missed that show,
you can check it out at the link shown.
- Yeah, that's right, but before we dive
into the Modern Data Warehouse,
let me show you some of the other things
that have got even better with Azure Data Warehouse Service.
Now, one of our ongoing focuses is around query performance,
since you can never be too fast, right?
- That's right, speed's always a great thing to have.
- So let me show you how far we've come
since John was on the show.
I've got a quick demo here.
So, here I've got two queries set up,
and I'm gonna get them started.
The one on the left is the version of
Data Warehouse Gen 2 from the last show,
the one on the right is the current version
available to customers.
We've got the exact same queries now, aggregates, joins,
really typical data warehouse and queries.
The only difference is the underlying
version of the service.
And so, the one on the right here,
we expect this to finish relatively soon.
There you go, about 16 seconds.
The one on the left is still running.
So it's, again, performance enhancements we've made
in only the last few months.
- So we'll let this run.
We'll check back in a moment,
but it's really good to see all the progress that we've made
in such a short time around performance,
but beyond performance,
self-service analytics and democratization
of data within companies has become more and more common,
but still, the top thing on everyone's mind is security.
- Yeah, I mean, that's right.
Performance is great.
It's really critical to productivity,
but security is always number one.
Let's go back to this demo and check it out.
You can see we've finished here and that took 59 seconds.
- [Jeremy] So, similar query set from John's,
about four times faster.
- Exactly, right, and that's just the underlying service,
so customers benefit from this today.
But, let's go back to security.
Without security, nothing else really matters,
and as you centralize more data for analytics,
the potential insights from this data
becomes interesting to a wider audience
in your organization.
Modern visualization tools such as Power BI
have allowed everyone to become a data analyst.
But, the value of this across the entire organization
can really only be realized if sufficiently granular access
can be applied to the data.
As the audience for data grows, so does the likelihood
that not everyone has access to all data.
We think very holistically about security
and we've built an enterprise-class set of capabilities
that enable you to host
even your most critical data in Azure.
This includes everything
from threat protection, network security,
authentication using Azure Active Directory,
access control, and data protection.
But, the most recent addition to Access Control
is native row level security
and I thought we might give a quick demo
of how this works today.
So, here I've got a query,
and you can see I'm logged in as the national analyst.
So, we'll get this started here,
and it returns a result set of 53 rows,
which represents all of the states
and a few territories in the United States.
So, this represents an aggregate of the entire table.
But, what if we had different users
that had different permissions
to different levels of the data?
So, let's now flip across to look at different analysts
and see what we've got here.
So, at the bottom you can see
I've got exactly the same server name.
At the top on each side,
you'll see I've got exactly the same database.
The only difference here is I've got
a Washington State login on the left
and a New York State login on the right.
Let's go run exactly the same query on both sides,
Washington and now New York,
and you can see that they're returning different results,
and these are context sensitive
to the security that the user has
for the rows in that table.
- And there's even the same query language
that we have for the national analyst,
so the good thing is here,
users basically don't know what they don't know.
It's the same query syntax,
not a lot of changes had to be made,
but how would you have done something like this in the past?
- There are a couple of common ways.
You typically used either filtered views or system functions
to determine the username when the query was executed,
but these were really clunky to manage
and native row level security simplifies the management,
and also the consumption of the data model
because it's consistent to everybody.
- And this looks like it could be really powerful
and think about times where you've got multitenancy,
you might have specific compliance needs,
you might not wanna spin up different data warehouses
in regions or for different departments
or different business units based on access needs.
The other thing that's really cool here is that,
if you've got vendors, maybe even an offshore dev,
those guys don't need to have access to
all the different data sets
and every row and column of data.
You need to have differentiated access there, as well.
- Yeah, these are all great examples
of where row level security is very powerful.
But beyond performance and security that we just showed,
there's another important area,
and that's how the platform supports
the variability of different workloads.
There are scenarios where it's important
that a given query or maybe a set of queries
just run predictably all the time.
- So before, we might've had a group of analysts,
maybe competing for resources.
And you know what it's like,
you've got the queue of all the different queries coming in,
there's maybe 100 queries in place.
The next one that comes in gets to the back of the line,
the back of the queue,
and maybe that one needs to run first.
- Exactly, sometimes that next query
needs to run immediately.
It could be a really important query,
it needs to run predictably when it's submitted.
So, for this we've introduced the concept
of workload importance and classification.
Here you can see I've got
two workload classifier statements.
The first has importance level to normal,
which is the default,
and when everything is normal,
it operates on a first in, first out basis.
We just handle everything
in accordance with the order it arrived.
But now we can plan for the importance of queries.
So, let's say our national analyst from before,
their query arrives, and we know their queries
are perhaps maybe more important than the state level.
You can see that this has been assigned
a higher importance level.
The system will automatically pop it
to the front of the queue for immediate execution,
and this allows for granular control
over the importance of your workloads,
and efficient utilization of all of your resources.
- And all these are really, really cool innovations
for people running their enterprise data warehouse on Azure,
but how do things evolve now when you think
about the Modern Data Warehouse and that solution pattern?
- Yeah, what we've just looked at
is really just the Azure SQL Data Warehouse.
The data warehouse itself has really been the cornerstone
of enterprise analytics for many, many years now.
But, as the diversity of data continues to grow,
as the type of analytics we want to perform evolves,
then we need capabilities that can meet
these evolving requirements.
On Azure, the Modern Data Warehouse is a solution pattern
that really empowers you to perform any analytics,
ranging from SQL queries to advanced machine learning,
without having to compromise on the fidelity of your data,
or over-provisioning wasted compute resources.
- Right, and we've kinda seen these things before
where actually, the fundamental here
is that you wanna pull in more data
than you might of thought you even needed in the past,
or you had been filtering that down
or doing other things with it,
because in its raw form,
it gives you more flexibility later
to pull out insights that you might want in the future
that you don't even know that you need right now.
- That's correct, Jeremy, absolutely.
With on-premise data warehouses,
and particularly, the appliance form factors,
there was a really fixed storage to compute ratio
and to add more storage incurred compute cost,
regardless of whether it was required or not.
It's just how they were architected, really,
and you're keeping historical or very granular data
that was valuable, but not frequently used.
It got expensive really quickly.
To get around this, either the ingestion
of the data into the warehouse had to be reduced,
or archiving strategies to remote storage
were getting employed.
The other challenge was that
it was very row- and column-focused
and so semi-structured data,
it required a lot of up-front transformation,
things like images just didn't fit at all, really.
- So, how have things changed then,
in terms of the Modern Data Warehouse pattern?
- So, Azure's completely changed the game
for these types of workloads
and I think it's really disrupted the space.
With Azure, you get a really cost-efficient,
analytics-optimized storage platform
called Azure Data Lake Store Gen 2,
which is entirely independent of any compute engines.
This provides a platform
to start accumulating and organizing any data
that's then gonna go and fuel these analytics applications.
But, with a giant data lake, you need a method to fill it.
For this, we've offered a fully managed service
called Azure Data Factory
that enables you data integration at scale.
It provide over 80 out-of-the-box connectors
to connect to everything from SaaS applications,
such as Salesforce or maybe Google AdWords,
other clouds, such as S3,
and hybrid connectivity, such as SQL or Oracle
that exist in your on-premise data centers.
If you spent much time around data,
you know that ingestion is really only half of the battle.
Once you've landed, you really need a way
to transform it to be fit for purpose.
And this is where scalable compute engines come in.
Azure Databricks Spark clusters can be provisioned on demand
and they're very powerful tool for
performing complex transformations at scale.
Additionally, Databricks happens
to also be a fantastic platform
for producing predictive models with data science.
- [Jeremy] Right, and this really enables you to explore
data before it goes into the Azure Data Warehouse.
- [Charles] Right, and once in the Data Warehouse,
this gives you the method of serving data
out to those consumers and as we've shown,
Azure SQL Data Warehouse scales
to support the most demanding workloads
with respect to performance, concurrency,
and, of course, security.
Finally, with Visualization tools, such as Power BI,
we've really empowered everyone
to analyze the data for themselves
and Azure SQL Data Warehouse is the engine
that's powering these insights.
So, this isn't just a linear process, though.
It's very iterative once your data lands in the data lake.
- So, it seems then like the cloud model
for Modern Data Warehousing's
really not only about being more flexible and scalable,
but there's also, in this case,
a lot of cost savings that you can derive from it, as well.
- Yeah, I think the unique elastic properties
of the cloud are very well known.
But, analytics workloads are one scenario, in particular,
where the elasticity really shines.
Data science is a pretty variable workload,
with respect to the compute capacity it requires
at any given point in time
and interactive SQL analytics is typically,
not always, but generally,
those platforms are consistently busy during working hours.
Because the Modern Data Warehouse
separates compute from storage entirely,
the compute can be tailored
in very cost-optimal ways for your requirements.
- This all sounds really good,
but can we see a real-world example?
- Of course.
Great example of this is the Digital Crimes Unit,
or as we call it, the DCU at Microsoft.
The Digital Crimes Unit fights cyber crime globally.
It's a combination of technology, forensics,
civil actions, criminal referrals, and more,
while protecting the security
and the privacy of our customers.
This is includes crime such as:
tech support fraud,
that affects our more vulnerable populations.
Before we addressed the issues, Microsoft used to receive
14,000 customer complaints globally
per month about tech scams.
You might have heard some of these,
what tech scammers are doing.
They're trying to convince you that your device has issues.
The scam often initiates with a phone call.
Your caller's identified themselves
as calling on behalf of Microsoft
or some other well-known security vendor.
They may even lure you into a pop-up on the web.
As the victim, you're convinced
that there is a significant security issue with your device
and you grant access, which enables the criminal
to emphasize the need for tech support
and then you end up paying unnecessarily
for this tech support.
We never want that to happen and in this case,
I think it's really difficult to track these folks down
because it's easy to cover your tracks
and the scammers frequently
change IP addresses or telephone numbers, perhaps.
So, there's a lot of evidence here
that may live only for a few days, sometimes only hours,
and these signals can be very, very small.
And, so, along with security,
the platform provides the scale they need to solve data
to the most granular level,
because this raw data maintains the fidelity
of the signal in the data.
- Right, and as customers report these types of tech scams,
a lot of it's actually just verbal account
of what might have happened off their memory.
The scammer was maybe remoted into their PC
and they might have gone off
to get a cup of coffee, or something, and come back,
and you never know what actually happened on their machine.
- So, not only are there a lot
of different types of data to deal with,
but a lot of this data is messy.
Relationships are imprecise.
It makes it difficult to join
and isolate patterns within the data.
At the DCU, they utilize structured and unstructured data
from more than 40 sources.
And this data is born in Azure and also non-cloud platforms.
So, the hybrid connectivity is critical to their success.
If you think about the tech scam example,
data may be coming in the form
that the customer's filled from a report to report the scam
or it might be from automated sources,
images that pop up on the web
that scare users into thinking that they have a tech issue.
- Right, so lots of different data sources,
but how does the DCU then capture all this data
and really make sense of it
to protect people from these types of scams?
- So, what they did is they went
and built a solution on Azure
that handles their entire pipeline
from ingestion all the way through to visualization,
using a lot of the scenarios that we just described.
Today, they employ the Modern Data Warehouse strategy
of ingesting data into the lake
and then bringing different compute engines to that data.
As you can see here,
ingestion is built entirely using Azure Data Factory,
which orchestrates the flow of data from the various sources
and then lands it in the data lake.
- [Jeremy] So, how's this move into the next phase, then?
- So, once in the data lake,
the DCU analysts can explore and prepare the data,
training their machine-learning models,
and then really looking for these suspicious activities.
The process here is really a blend
of massive machine automation,
combined with human investigation
and this us where they're gonna use Databricks.
They're running various Python scripts, as you can see here,
to do things like text classification
or deriving other insights.
Data also then flows into the data warehouse
and then is served out to the DCU analysts,
who are based globally investigating these cases.
- Okay, great, so they were able
to actually bring the data in and explore it.
But, how were they able to query it and make sense of it all
and what was going on in the data?
- So, in that tech scamming case,
what they wanted to do is see if they could reverse engineer
how the scammers were targeting users.
They undertook SQL-based analytics
to perform fraud detection,
where they joined image classification
to text classification tables
and then these were the outputs
of those Python scripts that we just looked at.
As you can see here,
one of the nice features we shift for SQL Data Warehouse
is the ability to look at graphical execution plans
and this helps them debug any issues
that might be in their queries very quickly and efficiently.
- Alright, so how do they go to the next part
where they actually get some actionable insights?
- So, remember I mentioned before
that a lot of these classical locational identifiers
can be changed very frequently,
but some things are harder.
You're modifying the code running
or you're changing things like the user interfaces
or even physically moving call centers takes a lot longer.
So, by analyzing screen shots,
URLs, redirects, IP addresses,
they can start to really triangulate patterns
for bad actors who are performing this fraud.
We can check our false positives and accuracy
to understand the quality of these insights
and then we reach out in very targeted ways
to local law enforcement to stop these activities.
- Now, how effective is all this work that the DCU's
been doing actually been to stop tech scammers?
- I think overall the effectiveness
of the DCU speaks for itself.
Microsoft fraud complaints have dropped
from 14,000 per month in 2017,
to only 8,400 per month in late 2018.
So, it's a great partnership with law enforcement
and for example, you've got about 16 call center raids
that's resulted in 63 arrests in India
in the past three months alone.
- This is a really great implementation.
It's really gonna help other folks that are watching improve
and future proof their data warehousing,
but where would you recommend people go to learn more?
- The main thing is we give you all of the components
you need to build a complete analytical solution,
but you can start small
and you can add capabilities as you need to evolve.
To learn more about the Modern Data Warehouse
solution pattern on Azure,
just click on the link shown.
- Thanks, Charles and, of course,
if you suspect that you've been a victim of a tech scam,
you can report that now at Microsoft.com/reportascam
and also keep watching Microsoft Mechanics
for the latest tech updates.
Thanks for watching.
We'll see you next time.
(upbeat electronic music)