Data Warehousing in Azure

Describing Azure Data Warehousing Services

• In Azure, the following services are used for creating and managing data warehouses:

  • Azure SQL Data Warehouse
  • Azure SQL Database

• In Azure, Azure Data Factory is a tool used for scheduling and orchestrating data transformations

  • For example, moving data between a SQL Data Warehouse and a SQL Database
• Azure Data Studio is a SQL editor used for connecting and querying Azure SQL Data Warehouse and Azure SQL Database

• Polybase is a tool used for moving data to and from:

  • Hadoop
  • Azure Blob Storage
  • Azure Data Lake
  • Other unstructured non-relational tables
• Azure Databricks is a tool used for processing data from Azure Data Warehouse using Spark functions

Comparing Azure SQL DW and Azure SQL DB

• Azure SQL Database is optimized for the following:

  • Performing CRUD operations for some running web application
  • Optimized for OLTP operations, meaning many small requests to the database
  • In other words, optimized for transactional usage

• Azure SQL Data Warehouse is optimized for the following:

  • Performing a few complex queries on a large amount of historical data
  • Optimized for OLAP operation, meaning few large requests to the database
  • In other words, optimized for analytical usage

References

• Documentation for Azure SQL Data Warehouse
• Documentation for Polybase
• Article Describing SQL Server
• Describing Data Warhousing and Parallel Processing
• Defining the Redshift Architecture
• Article about Hadoop and Data Warehousing
• Comparing OLTP and OLAP