Azure Synapse Analytics: Azure Data Lake Configuration [Introduction to Synapse Analytics - Ep. 6]
Azure Synapse Analytics: Serverless Pools [Introduction to Azure Synapse Analytics Series - Ep. 5]
In this video from Pragmatic Works, Mitchell Pearson continues his Azure Synapse Analytics series by introducing one of the most flexible and cost-effective ways to query data in a data lake: SQL on-demand using serverless SQL pools. Building on prior discussions around Azure Data Lake, Mitchell shifts the focus toward how users can efficiently access and analyze stored data without provisioning dedicated resources.
Understanding SQL Pools in Azure Synapse Analytics
Mitchell begins by explaining the two main types of SQL pools available in Azure Synapse Analytics:
- Dedicated SQL Pools – These are provisioned resources designed for large-scale analytical workloads, reporting, and aggregations. Users are billed continuously while the resource is running.
- Serverless SQL Pools (SQL on-demand) – These allow users to query data directly from the data lake without provisioning infrastructure, offering flexibility and cost savings.
While dedicated SQL pools are ideal for consistent, high-volume workloads, serverless SQL pools are better suited for ad hoc analysis, quick queries, and exploratory data access.
Why Choose Serverless SQL Pools?
Mitchell highlights several key benefits of using SQL on-demand:
- Pay-per-query pricing – Users are only charged for the data processed during query execution.
- No infrastructure management – There is no need to provision, scale, or pause resources.
- Always available – The serverless pool is ready whenever needed.
- Scalability – Capable of handling large-scale data processing without manual configuration.
This makes serverless SQL pools especially appealing for organizations seeking cost-efficient analytics solutions without sacrificing performance.
Accessing Data in Azure Synapse Workspace
Mitchell demonstrates how to access the Azure Synapse workspace through the web interface and navigate to the Data section. From there, users can:
- Browse connected data lakes
- View files stored in containers
- Interact directly with datasets
This tight integration between Synapse and the data lake simplifies the data exploration process, allowing users to work seamlessly across services.
Querying Data with SQL on Demand
One of the most powerful features demonstrated is the ability to query files directly from the data lake using SQL. By simply right-clicking a file, users can generate a query such as:
- Select Top 100 Rows – Quickly preview data
- Custom SQL Queries – Perform aggregations, counts, and filtering
Instead of querying traditional database tables, SQL on-demand uses the OPENROWSET function to access files like Parquet or CSV directly.
Mitchell emphasizes that this approach enables users to:
- Run ad hoc queries with minimal setup
- Analyze data instantly without data movement
- Leverage familiar SQL syntax
Working with Different File Formats
The demo highlights differences between file formats:
- Parquet files – Schema-aware and optimized for performance and compression
- CSV files – Require additional configuration, such as specifying header rows
For CSV files, Mitchell demonstrates how to use options like HEADER_ROW = TRUE to correctly interpret column names. This illustrates the importance of understanding file structure when querying data.
Best Practices and Tips
Throughout the session, Mitchell shares practical tips to improve workflow and avoid common mistakes:
- Use Shift + Enter to execute queries instead of F5 to avoid refreshing the browser
- Prefer Parquet files for better performance and efficiency
- Explore available options in the OPENROWSET function for advanced scenarios
Integration with Other Tools
SQL on-demand is not limited to the Synapse workspace. Mitchell explains that it can also be integrated with:
- Power BI for reporting and visualization
- Data pipelines and data flows for ETL processes
This flexibility allows organizations to build end-to-end analytics solutions while leveraging the power of serverless computing.
Conclusion
Mitchell Pearson concludes by reinforcing the value of SQL on-demand as a game-changing feature in Azure Synapse Analytics. By enabling direct querying of data lake files with minimal setup and cost, serverless SQL pools empower analysts and developers to perform fast, scalable, and efficient data analysis.
As the series progresses, viewers can expect deeper dives into dedicated SQL pools, Spark, and advanced integration scenarios, further expanding their understanding of modern data platforms.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Synapse Analytics and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.
Sign-up now and get instant access
ABOUT THE AUTHOR
Mitchell Pearson has been with Pragmatic Works for 10 years as a Data Platform Consultant and the Training Manager. Mitchell has authored books on SQL Server, Power BI and the Power Platform. Data Platform experience includes designing and implementing enterprise level Business Intelligence solutions with the Microsoft SQL Server stack (T-SQL, SSIS, SSAS, SSRS), the Power Platform and Microsoft Azure.
Free Community Plan
On-demand learning
Most Recent
- Azure Synapse Analytics: Serverless Pools [Introduction to Azure Synapse Analytics Series - Ep. 5]
- Power Apps: Working With Power Apps Collection Basics [Power Platform Series - Ep. 12]
- Microsoft Teams: Meeting Basics [Beginner to Pro Teams Series - Ep. 4]
- Azure Synapse Analytics: Azure Data Lake Configuration [Introduction to Synapse Analytics - Ep. 6]
private training

Leave a comment