Skip to content

How to use SQL Notebooks to access Azure Synapse SQL Pools & SQL on demand

Howard van Rooijen By Howard van Rooijen Co-Founder
How to use SQL Notebooks to access Azure Synapse SQL Pools & SQL on demand

As much as consumption based cloud analytics has already lowered the barrier to entry for organisations to take advantage of big data, the addition of notebook-based development significantly delivers decreased time to (business) value, and eliminates waste by improving collaboration and productivity.

The notion of a cross-functional team has been a core ambition of Agile development, and there have been a number of occasions in my career where I've paired with domain experts to translate real world knowlege into a system. Sprint visions, user stories, personas, value-stream maps, and gherkin based executable specifications have all been steps along the way to help synchronise and consolidate the aims and knowledge of the busienss and delivery team.

These have, in my opinion, all been baby steps towards the value that notebooks can deliver, especially in data exploration and experimentation.

Notebooks FTW!

Take the following photo; this was from a data hack we ran for one of our customers last year. We took a 25 year old business process (onboarding pension data) that took on average 12 weeks to complete.

We had an entirely cross functional team that included the CTO, a pension data expert (who ran the onboarding process), a C# developer, a data expert, and me (I'll leave you to guess what value I brought to the team!).

A cross functional team collaborating on a recent data hackathon

Through the notebook experience, we could pull together all the relevent documentation, diagrams, business rules, regulatory requirements, aims and hypothesis from a business perspective, and start to experiment via code. Because it's highly contextual everyone (regardless of technical ability) could understand what we were trying to achieve and how a snippet of code related to the step in the process. Everyone was bought in, fully engaged, and the collaboration was both highly informative and extremely productive.

The end result was that we re-engineered and optimized the existing process and reduced it from 12 weeks to just under 45 minutes.

Results like this are why I've been such a huge advocate for notebooks across the Microsoft ecosystem; from .NET Interactive, to notebooks in Comos DB, Azure Notebooks (which, unfortunately shuts down on 09/10/2020), Azure Machine Learning, Azure Databricks and even Kusto (KQL) notebooks.

Azure Data Studio Notebooks and SQL on demand

So, it's great that Azure Synapse Studio has built-in notebooks, but as it stands these are geared towards Spark workloads. If you are using SQL Pools or serverless SQL on demand then you are out of luck.

Technically, you could use still use the built-in notebooks as Python, Scala and .NET all support SQL connections and querying, but you'd need to be running a Spark cluster to execute the queries, and wrap your SQL in another programming language, which kind of defeats the purpose. I'm sure pure SQL notebooks are something that's on Microsoft's radar but for now we need to find an alternative solution.

Fortunately, Azure Data Studio comes with support for SQL notebooks which you can easily connect to both Synapse SQL Pools and SQL on demand. What's more, the notebooks are standard Jupyter ipynb files which means they can be easily version controlled.

Checkout this video walk-through showing how to use Azure Data Studio notebooks for data exploration using serverless Azure Synapse SQL on Demand.

Want to get started with Synapse but not sure where to start?

If you'd like to know more about Azure Synapse, we offer a free 1 hour, 1-2-1 Azure Data Strategy Briefing. Please book a call and then we'll confirm the time and send you a meeting invite.

We also have created number of talks about Azure Synapse:

Finally, if you are interested in more content about Azure Synapse, we have a dedicated editions page which collates all our blog posts.

The best hour you can spend to refine your own data strategy and leverage the latest capabilities on Azure to accelerate your road map.

Also worth reading:

Does Azure Synapse Analytics spell the end for Azure Databricks?

Does Azure Synapse Analytics spell the end for Azure Databricks?

James Broome

Have you or are you about to invest in Azure Databricks? If so, the new Spark offering in Azure Synapse Analytics is likely to have grabbed your attention and rightly so. Why is Microsoft putting yet another Spark offering on the table and what does it mean for you?
Azure Synapse for C# Developers: 5 things you need to know

Azure Synapse for C# Developers: 5 things you need to know

James Broome

Did you know that Azure Synapse has great support for .NET and #csharp? Learning new languages is often a barrier to digital transformation, being able to use existing people, skills, tools and engineering disciplines can be a massive advantage.
How to test Azure Synapse notebooks

How to test Azure Synapse notebooks

James Broome

Interactive Spark notebooks are an incredibly powerful tool for data exploration and experimentation. And in Azure Synapse, the time to (business) value is significantly decreased due to tight integration with Pipelines and monitoring tooling. But as with any software process, the need to validate business rules is important, as is ensuring that quality doesn't regress over time - especially so in such a collaborative and productive environment. This post looks at some simple ways to add testing to your Synapse Notebooks.

Howard van Rooijen

Co-Founder

Howard van Rooijen

Howard spent 10 years as a technology consultant helping some of the UK's best known organisations work smarter, before founding endjin in 2010. He's a Microsoft ScaleUp Mentor, and a Microsoft Azure MVP, and helps small teams achieve big things using data, AI and Microsoft Azure.