Synapse and Data Warehousing Services


If you are looking for an expert level partner to help architect and build you Synapse warehouse, then Prodata offer crucial services to massively cut your implementation time, time to market, quality of solution and operational readiness.

  • Modern Architecture using Azure, data lake, synapse (aka sqldw).
  • DW automation accelerators for Ingress and staging using IP developed and used across numerous enterprise solutions.
  • Templates and guidance on data virtualization, transformation layers and implementation.
  • DW automation for Kimball Star schema loading
  • Out of the box frameworks for logging, lineage, auditing, performance monitoring and error tracking.
  • Operational dashboards tracking solution and business process health

When you engage with Prodata, we bring this IP and experience so that you don’t have to build it from scratch.


Data warehouse architecture in Ireland has finally embraced the use of cloud and massively scale out platforms such as Synapse. This has been driven over the last few years by factors such as:

  • The movement to Azure based solutions. Pretty much all our new solutions from 2020 onwards have been in Azure in some shape or form, from IaaS to serverless PaaS.
  • The emergence of the data lake as a means of ingesting, storing and archiving source data, while providing big data analytics in tools like Databricks direct on top of the data lake.
  • The emergence of Synapse data pools (SQLDW) from its early 100TB+  specialist and expensive days into a mainstream data warehouse platform suitable for the more mainstream 0.5-5TB sized solutions common in medium and large Irish companies. With synapse dedicated pools down to about 20 euro per TB storage and often under 750 euro a month compute cost.
  • Increased meta-data automation tools and processes, moving development for large teams of human labour creating 1,000s of ETL packages which instantly become legacy status, into a flexible and extensible meta data drive architecture than embraces change

EDW Modelling and Automation

For both data ingress, egress and transformation into star schema we utilise Prodata proven templates and meta data frameworks. These allow you to focus more on design and data modelling and less on ETL, platform and tooling.

Business modelling techniques like the Kimball BUS Matrix are used to model business processes, server as meta data and ultimately automate creation of a star schema using a Prodata framework optimised for the Synapse Platform.

Devops and Release Automation

Using Azure Pipelines and PowerShell automation we bring the latest in release pipelines to your business intelligence project. No longer are releases a dreaded affair requiring much manual labour and human processes. Now, we strive towards the “ten releases a day” mantra with automated build, release and regression test, bug tracking and release management dashboards.

Operational and Business Monitoring

We have templates to help build a custom, operational dashboard for your enterprise data warehouse and business intelligence platform.

Events monitored include:

  • SLA Metrics
  • Data Availability and Accuracy
  • Pipeline failures
  • Load failures
  • Model refresh failures
  • Business data quality

Managed Self Service

Managed self-service involves building a semantic model for your business (ERP or other processes) with data correctly categorised and secured int data models. This is a more enterprise and pragmatic approach to just letting the majority of users loose on raw unsecured and unprocessed data.

Users can then connect to these models to create their own self-service analytics and reporting in power BI. This empowers finance, business analysts and any user who can become familiar in Power BI.

Prodata can help you get the correct level of governance with templates and accelerators for:

  • Creating data dictionaries for users.
  • Help libraries and guides.
  • End user Training.
  • Logging and Monitoring analytics.
  • Guidance on using Power BI visualizations on your data model.

Synapse and Data Warehousing Blogs

Bug – Synapse SqlPool Language and dates

We found this interesting bug on Dedicated SQL Pool when working with British style language. If you use Dynamic SQL to change the language, then selects on the date fail with a conversion error, but prints work. Weird… An example…

Meta data Automation at SqlBits 2022

The absolute key to getting agility and extensibility in a data warehouse project is a good meta data based automation framework and tools, regardless of whether you are using TSQL, Pool, Databricks and/or Python. If you are coming to SqlBits…

Top 10 SQL Partition Commandments

I wrote this in 2010, before Clustered Column Store Indexes, SQL Pools (so rule 3 looks a bit odd), and before date data type was mainstream, but the rest of these still look quite Thou Shalt use something that exists…

Top 10 SSIS/DW Commandments

Blast from the past. I wrote this is 2008 and at one point we had it made into a poster for the office rule whereby on code review we not say a word but just point to the poster. Not…

CI/CD For Azure Synapse Analytics – Part 2

In CI/CD Azure Synapse Analytics – part 1 we have covered: Setting up git source control in Synapse Studio The difference in main collaboration and workspace publish branch In this blog we are going to cover: How to create build…

CI/CD for Azure Synapse Analytics – Part 1

Do you also wonder how to do continuous integration (CI) and continuous deployment (CD) for Synapse Analytics?  But first, lets talk about basic, what is CI/CD is simple terms. CI/CD is one of the best practices of agile methodology and…


Something went wrong. Please refresh the page and/or try again.