When using a modern data warehouse, your organization is likely to see improved access to your data and more impactful analytics. One such data warehouse is Azure Synapse, a Microsoft service. When paired with a powerful BI tool, like Looker, or a data science platform, like Dataiku, your organization can more quickly gain access to impactful insights that will help you drive business decisions across the enterprise.
In this post, we’ll provide a high-level overview of Azure Synapse, including a description of the tool, why you should use it, pros and cons, and complementary tools and technologies.
Overview of Azure Synapse
Azure Synapse is Microsoft’s service that puts an umbrella around various existing and new offerings, including Azure DW, Azure Databricks, and on-demand SQL querying, but lacks the tight integration across these services. Similar to Redshift, Azure DW is charged by size of instance and time running, while other Synapse services offer more of a consumption-based model.
- Once data is stored within Azure Data Lake, no need to stage data again within the warehouse
- Easy to scale up or down on the fly with use of Azure
- Increases in pricing tiers only increase concurrent queries by 4 at each level
- Built for MPP (massive parallel processing)
- Performance optimal for data volumes larger than 1TB
- Not suitable for running high volumes of concurrent queries (four concurrent requests per service level)
- Requires active performance tuning (indexes, etc.)
- Native connection with Power BI
- Can select either a serverless SQL pool or a dedicated SQL pool based on the needs of the organization
- Supports the ability to run Spark on Databricks
- Core product still relies on Azure DW, an older technology
- Supports row-level and column-level security, multi-factor authentication, and Azure AD integration
Why Use Azure Synapse
The Microsoft SQL Server ecosystem is familiar, with tighter integrations into Azure’s data ecosystem, including Azure Databricks and the MPP version of SQL Server, Azure DW – just don’t expect a turnkey solution quite yet.
Pros of Azure Synapse
- Can be easily provisioned with existing Azure subscription and provides pay-as-you-go pricing
- Integration with Azure Active Directory and Azure Purview can provide an easy way to manage user roles and insights into data
- Transferable knowledge from on-premise Microsoft SQL Server background
Cons of Azure Synapse
- “Synapse” is largely a marketing umbrella of technologies, with Azure DW at its core, requiring management of disparate services
- Difficulty managing high volumes of concurrent queries due to tuning and cost of higher service tiers
- Requires complex database administration tasks, including performance tuning, which other cloud data solutions have made more turnkey
- Serverless capabilities are limited to newer Azure services, and lacks the on-demand, frictionless sizing of compute within Azure DW
Select Complementary Tools and Technologies for Azure Synapse
- Azure Analysis Services
- Azure Data Factory
- Azure Databricks
- Azure ML
- Azure Purview
- Power BI
We hope you found this high-level overview of Azure Synapse helpful. If you’re interested in learning more about Azure Synapse or other modern data warehouse tools like Amazon Redshift, Google BigQuery, and Snowflake, contact us to learn more.
The content of this blog is an excerpt of our 2021 Modern Data Warehouse Comparison Guide. Click here to download a copy of that guide.
Fred Bliss is the CTO at Aptitive. He brings over 15 years of experience solving complex business problems through data solutions including cloud integration, data warehouse modeling, ETL, and front-end reporting implementations.