Dejan Sarka: Optimizing ETL on SQL Server Side

KCSSUG Meetup event from December 12, 2024
Summary
When you try to optimize Power BI, the first advice you get is to push the data processing as far upstream as possible. Query folding in Power Query tries to enforce this logic as much as possible. But what is going on SQL Server side? What if the daily extract – transform - load (ETL) process is still too slow? The real optimization of the ETL process starts with finding what has changed on the source, finding the delta. In SQL Server, you have many methods for this. You can use the Change Tracking (CT), Change Data Capture (CDC), system-versioned tables, ROWVERSION data type, triggers… Which method might be the fastest? In the next step, how do you optimize staging tables? Do you use regular tables, temporary tables, memory-optimized tables? Can you leverage minimally logged operations? Does it make sense to pre-aggregate the data? Disable indexes and constraints or not? What about table partitioning, if you do not have Enterprise license for SQL Server and / or Premium capacity for Power BI? This presentation will help you answer these questions and help finding the most suitable solution for you.