<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

Azure Synapse Analytics: Serverless Pools [Introduction to Azure Synapse Analytics Series - Ep. 5]

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

Leave a comment

Free Community Plan

On-demand learning

Most Recent

private training

Hackathons, enterprise training, virtual monitoring