Share via

Azure sever-less SQL database or Azure Synapse for data warehouse

Dongchul Yoo 26 Reputation points
2022-02-12T04:26:49.257+00:00

Hello,
I am trying to find a cost efficient way to build a data warehouse? Azure has so many products with the ton of options. I have no idea where to start in a limited time. I can see that there are Azure sever-less SQL database or Azure Synapse. Which one is better? Is there any other alternative? Do you have any resource showing a simple comparison or tutorial? I appreciate all of your inputs.

Azure SQL Database
0 comments
Accepted answer
  1. Alberto Morillo 33,426 Reputation points MVP
    2022-02-12T17:54:13.09+00:00

    Please refer to this blog post. Azure SQL Data Warehouse is now named Azure Synapse (Dedicated SQL Pools or Serverless SQL Pools). SQL Pools are dedicated Azure SQL Data Warehouses. Serverless pools are like dedicated pools but no need to provision pools, you just query and it will destroy resources once finished.

    Synapse is the only solution that connects natively to other Azure services. Synapse integrates with Azure Purview (Data Governance), Power BI (Business Intelligence) to make data-driven business decisions, and Azure Machine Learning to use organizational data to train ML models and make business decisions.

    Dedicated pools can scale to peta-bytes but Azure SQL can scale to 14 TB or 4 TB depending of the model and Hyperscale can scale to 100 TB.

    Azure Synapse has a nice integration with Azure Data Factory for ETL and ELT processes, Azure SQL does not have that integration.

    Azure Synapse dedicated/serverless pools can use relational table schemas as Azure SQL does, but you are supposed to use denormalized schemas to speed up queries on data warehouse.

    Azure Synapse dedicated pools can be paused at any time, Azure SQL serverless can be paused after one hour or more of inactivity, the rest of Azure SQL options cannot be paused.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dongchul Yoo 26 Reputation points
    2022-02-13T01:23:42.453+00:00

    @Alberto Morillo Thank you! My initial thoughts was to use AZURE server-less SQL database with Tableau. AZURE Synapse seems at least 7.5 times expensive than the AZURE server-less SQL database. Have you ever used the Snowflake? If so, between AZURE synapse and Snowflake, which one would you recommend? As an alternative question, what would be the most cost efficient way to build a data warehouse?

    0 comments
  2. Alberto Morillo 33,426 Reputation points MVP
    2022-02-13T02:41:31.357+00:00

    I have created a Data Warehouse with Synapse (Dedicated Server Pool not Serverless) for testing and I have it paused, I have consumed less than 9 dollars on 12 days. I agree serverless is cheaper, but Synapse provides better performance at good value. Give it a try to Synapse Serverless also.

    About snowflake, in general, I encourage you to handle many-to-one hierarchical relationships in a single dimension table rather than snowflaking. Snowflakes may appear optimal to an experienced OLTP data modeler, but they're suboptimal for query performance.

    I would also say snowflaked tables create complexity and confusion for users directly exposed to the table structures. Star schemas are more natural for the users.

    Snowflaking increases complexity for the optimizer, which must link hundreds of tables together to resolve queries. Snowflakes also put a burden on the ETL system to manage the keys linking the normalized tables, which can become grossly complex when the linked hierarchical relationships are subject to change. Though snowflaking may save some space by replacing repeated text strings with codes, the savings are negligible, especially in light of the price paid for the extra ETL burden and query complexity.