In Synapse SQL Pools, Hash distributions allow us to somewhat control data distribution. Whilst this can deliver performance in the right scenarios, I noted last week that these tables should be monitored to avoid skew. In this post I want to demonstrate how to identify the skew, and why a different distribution model may be […]
When designing tables in a Synapse Dedicated SQL Pool, I initially focussed on the ‘SQL’ and not so much the ‘pool’. How tables are stored is fundamentally different to other Azure SQL offerings, so it’s easy to get tripped up and impact efficiency and performance. Let’s see how the SQL pools work differently and how […]
Retries are a useful part of resilient pipelines. However, retries aren’t created equal, so I want to dive into recent changes to pipeline activity retries and the benefits they bring. The issue Failures happen, so we have retries to have another chance (or few) at completing the activity. They look something like this: These fixed […]
Pipeline sequences are a common orchestration pattern, and chaining them allows us to decompose processes into manageable chunks. Here I want to look at 3 ways to achieve this chaining in Fabric and how they compare. Invoke pipeline The default option is the native Invoke Pipeline activity. This allows us to call pipelines wherever they […]
We recently dived into Spark and saw how DataFrames use lazy evaluation to defer execution until needed. This is useful for defining segments of logic to be reused, but if this causes large, complex calculations to execute multiple times, there may be a better way. Lazy evaluation Let’s take an example with some sales data: The display triggers execution […]
Computed columns allow us to bake logic into our schema. I find particular use for these when reviewing or optimising established solutions where you spot the quirky ways the data is being used. Here I want to demonstrate 3 symptoms and show how different implementations of computed columns solve them. Baking the expression Common expressions […]
When developing Power BI models, we don’t always want to refresh the entire dataset before we start working with the data. We can speed up the refresh – and therefore our development – by reducing the volume we’re working with. Here we’ll look at a couple of ways to use parameters to adapt production-ready models to reduce […]
As a database developer, when I started using Power BI, I was concerned about it retrieving reams of data only to perform transformations downstream. The Power Query editor misleads us into thinking the retrieval and transformations are applied sequentially. Thanks to Query Folding, that’s not usually the case. And that gives us more power to […]
Being a Data Engineer, this month’s invitation from Meagan resonates. She’s asked us to share a tip, technique, or lesson about detecting changes – which can be crucial to reduce workload across a large dataset. I’ll rewind back a number of years to an overnight batch process which ingested key transactional data for a business. Due to […]
Having TOP (1) return multiple rows feels wrong… but that’s what WITH TIES can do. For a long time I used patterns like this to get the first record in a group: Then I found the cleaner alternative: Let’s dive into what it is, and why you (probably) shouldn’t use it. WITH TIES The WITH TIES syntax was added over 20 years ago but it […]
