Azure Data Explorer vs Azure Synapse Analytics (a.k.a SQL DW)

Question

I design a data management strategy for a big IoT company. Our use case is fairly typical, we ingest large quantities of data, analyze them, and produce datasets that customers can query to learn about the insights they need.

I am looking at both Azure Data Explorer and the Data Warehouse side of Azure Synapse Analytics (a.k.a Azure SQL Data Warehouse) and find many commonalities. Yes, they use different languages and a different query engine on the backend, but both serve as a "serving layer" that customers use to query read-only data at a large scale. I could not find any clear guidance from Microsoft about how to choose between the two, or maybe it makes sense to use them together? In that case, what is the best use case or type of data for each of the services?

If you can enlighten me please share your thoughts here. If you know about some guidance about the matter please reply with a link.

Answer 1

The classic and also the modern data warehouse pattern involve first designing a well curated data model, with documented entities and their attributes, creating a scheduled ETL pipeline that transforms and aggregates the raw data, big and small into the data model. Then you load and serve it. The curated data model provides stability, consistency and reliability when consuming these entities across an enterprise.

Azure Data Explorer was designed as an analytical data platform for telemetry. In this workload you do not aggregate the data first, but actually keep it close to the raw format as you do not want to lose data. It allows you to deal with the unexpected nature of security attacks, malfunctions, competitive behaviors, and in general the unknowns, as it allows looking at the fresh raw data from different angles and provide a lot flexibility. This is why Azure Data Explorer is the storage for Microsoft Telemetry and also a growing set of analytical solutions like: Azure Monitor, Azure Security Center, Azure Sentinel, Azure Time Series Insights, IoT Central, PlayFab gaming analytics, Windows Intune Analytics, Customer Insights, Teams Education analytics and more. Providing high performance analytics on raw data, with schema-on-read capability on textual, semi structured and structured data. Quite a few of our partners and customers are adopting ADX for the same reasons. Check out the overview webinar that describe these concepts in detail.

Azure Synapse Analytics packed SQL DW, ADF and Spark to have all the data warehouse pattern components highly integrated and easier to work with and manage. As we announced on the Azure Data Explorer Virtual Event, Azure Data Explorer is being integrated to Azure Synapse Analytics along side the SQL and Spark pools to cater for telemetry workloads - Real time analytics on high velocity, high volume, high variety data.

Check out some of the IoT cases Buhler, Daimler video,story, Bosch, AGL and there are more leading IoT platforms who are adopting Azure Data Explorer for this purpose. Reach out to us if you need additional help.