SQL Bits 2022 was in March this year and after two years of delay due to the pandemic, it was a welcome return for the UK’s main SQL Server and Data Analytics conference.

Full of Microsoft MVP’s and international speakers it was great to the see the conference return, and it provided a chance to listen to old friends doing share their knowledge.

Now that the dust has settled and we’ve had a chance to reflect, here are our key takeaways and thoughts on what we heard and discussed.

By some margin most of the sessions were in some way about either Azure Synapse and/or Power BI. This was not by chance; Synapse has come a long way in a short period of time and has matured rapidly.

The integration between the Synapse and Power BI services is now excellent, and Synapse now provides a solid wrapper across Data Lake, Spark, SQL, Pipeline, and analytics technology.

Using Notebooks, you can write data processing code in any language in Python (PySpark, SQL or C#).

We demonstrated Synapse to a client 12 months ago and at the time were still trying to work out the role it had to play. Yes, it enabled new capabilities (e.g., SQL over Data Lake), but we were still puzzled when it looked like you could achieve everything through the combination of existing Lake, SQL, Power BI and Data Factory.

Now it is clear, Synapse provides that single interface to all these technologies and more (i.e. Spark/Databricks). Once set up you work with your data platforms easily, adding and removing whole technology sets as needed for data processing jobs.

In a similar way to Synapse maturing as a product, the Data Lake House concept is also maturing and needs to be considered seriously in all architectural platform designs.

When the Data Lake concept first came along, data platform implementations built Data Warehouse ETL mechanisms from data in the lake loading into another repository such as SQL engine.

Technologies such as Synapse, to an extent Power BI, and most importantly Delta Lake, which provides ACID transactions over files stored in the lake, have now emerged.

Now Data Warehouse and Analytic models can be built directly over the data in the Lake, with no need to move the data somewhere else. This reduces cost, complexity and increases agility in terms of producing reporting models.

At BDO/C5 we’re pretty much devotees of Ralph Kimball’s dimensional modelling (star-schema) approach to producing data analytic models. Other approaches do exist, but we love the simplicity of star schemas using facts and well conformed dimensions. This was also evident amongst many of the presenters, especially in the Data Lakehouse sessions.

Power BI has long had two mechanisms for accessing data, import and direct query. Import is typically the preferred approach for performance reasons and typically you model using a dimensional model, but direct query (where data remains in the source data platform) has advantages from an update frequency perspective – you don’t need to load the data into Power BI to report on it. This is especially true in the data lake house approach.

In direct query, star-schemas can be slow for large data sets, but single table modelling, especially with “dual mode” partitions along with SQL Server, Data Bricks or Synapse doing the heavy lifting to process data into the table is now a recognised design pattern to be used when needed.

There were a good number of sessions on the more traditional aspects of database admin and tuning. The Azure SQL PaaS service is very good and does take away some of the maintenance jobs (e.g., patching) but indexes still need to be tuned, performance monitored, and maintenance performed to have a well-functioning SQL estate.

It’s also clear that Microsoft recognise that hybrid will be a route many customers will take as some workloads can’t be moved to public cloud for various reasons. SQL 2022 has many new features to help, but DBA Skills are going to be key for organisations for a long time yet.

Overall, we really enjoyed SQL Bits this year. There was lots of learning and training and some new ideas to try out. We’re going to look forward to watching the videos of the sessions missed and re-watching some of our favourite ones again.

For more information about how these new announcements can help you make the most of your data, email us at enquiries@c5alliance.com or call +441534 633 733.