Elastic Queries with Azure SQL Database and Synapse Analytics Serverless SQL Pools

Recently I have been exploring an option to use an Azure SQL Database for a small footprint Data Warehouse and wondered whether I could create an elastic query connection to a Serverless SQL Pools database and query a Delta folder in an Azure Data Lake Gen2. It can be done successfully using an External Table in a Serverless SQL Pools database and creating the same table in the Azure SQL Database. Now the data within the Azure SQL Database can be queried alongside the data within the Data Lake using the Serverless SQL Pools engine.

In this video we’ll use an example database, there is an Azure SQL Database that contains AdventureWorks Dimension and Fact tables with less than 10GB of data overall (not enough data for a Dedicated SQL Pool!). There is also an Azure Data Lake Gen2 account that contains around 1.2 billion web telemetry events about users browsing/purchasing behaviour on the (fictious) website. This data is stored in Delta format (Parquet files). The goal here is to query this data via a single surface area without moving the web telemetry data and use the Serverless SQL Pools engine to do the “heavy lifting” when querying data in the data lake.

Chapters:
00:00 Introduction
04:00 Recap on exporting raw data from Azure SQL Database

[MDevion]

Well its nice to query cross database / datalakes, its just painstakingly slow, especially with joins.

Isnt it just better to, whatever in the Azure SQL DB, to put it in a datalake? Azure SQL isnt exactly ideal for DWH loads and it can become quite costly.

Also my impression is that a datalake solution (Or datalakehouse/deltalakehouse) is much faster then an Azure SQL DB.