Synapse

  • ADF Dataflow CTE workaround

    At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to deal with this limitation: re-write the query using subqueries instead of CTEs use a stored procedure that contains the query and reference the stored proc in the source of the dataflow write the query as a view and reference the view in the source of the dataflow (this is my preferred method and the one I will…

    » Read more
  • How to parameterise the Execute Pipeline activity in Azure Synapse Analytics.

    Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run. However, with the use of a Microsoft API there is method which we can use to overcome this. In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below: https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run…

    » Read more
  • Execute SQL Task in ADF

    If you’re new to ADF or Synapse pipelines and looking for a way to execute SQL commands in pipelines, unfortunately there is no equivalent to the Execute SQL Task in SSIS, but I have found some alternatives which work well for me. For something as simple as a SELECT statement where you want to return results to be used later, you can use a Lookup activity: You can then reference the output of this activity in other activities using dynamic…

    » Read more
  • Azure Synapse Series: What is Workload Management (part2)

    So in part 1 we found out what Workload Management was and why we wanted to use it. (If you haven’t read that part, please click here and read that post before you carry on, as we’re jumping in at the deep end!) In this part we’ll be learning how to configure it. Continuing from the end of the previous post, you have two main options to configure this. First is to assign a user or role to one of the existing system defined…

    » Read more
  • Azure Synapse Series: What is Workload Management (part1)

    So you’ve started using Synapse SQL Pools, you’ve scaled up to improve performance, but your queries aren’t going as fast as you’d hoped. You take a look at resource utilisation and you see that as you scale up, your total resource utilisation per query goes down. But didn’t we scale up to allow our query to use more resource and run quicker? What’s going on here? A little thing called Workload Management.…

    » Read more
  • Azure Synapse Series: Hash Distribution and Shuffle

    For this post I’m going to presume you’ve already taken a look at distributing your data using a hash column, and you’re not experiencing the performance you’re expecting. (If you’re not already aware of what this is, take a look at the following link to learn the basics of what a distributed table is and why you need it in Azure Synapse. I’ll be here when you get back.)…

    » Read more