A Fine Slice Of SQL Server
Meagan Longoria spots the fork in the road:
At the moment there are two activities in Fabric pipelines that allow you to execute a “child” pipeline. They are both named “Invoke Pipeline” but are differentiated by the labels “Legacy” and “Preview” in parentheses.
Read on to learn more about these two and why choosing the new one may not always be the best option for you, at least not yet.
Comments closedIt’s been 13 months since Salesforce and Informatica called off their first attempt at an acquisition. But the second time appears to be the charm, as Informatica today announced that Salesforce will buy it for $8 billion.
Informatica was founded in 1993 ago to serve the burgeoning market for data integration tools, in particular the need for extract, transformation, and load (ETL) tools for early data warehouses. Companies at the time needed to pull transactional data out of mainframes, midrange, and Unix systems, transform the data into a suitable format, and then load it into their analytical database.
It will be interesting to see what comes out of this.
Comments closedNikola Ilic takes us through several data transformations:
In the lakehouse, for example, you can transform the data by using PySpark, but also Spark SQL, which is VERY similar to Microsoft’s dialect of SQL, called Transact-SQL (or T-SQL, abbreviated). In the warehouse, you can apply transformations using T-SQL, but Python is also an option by leveraging a special pyodbc library. Finally, in the KQL database, you can run both KQL and T-SQL statements. As you may rightly assume, the lines are blurred, and sometimes the path is not 100% clear.
Therefore, in this article, I’ll explore five common data transformations and how to perform each one using three Fabric languages: PySpark, T-SQL, and KQL.
Click through for those transformations, such as extracting date parts, fixing casing, and pivoting data.
Comments closedTrevor Olson announces a feature has become generally available:
Shortcuts in OneLake allow you to quickly and easily source data from external cloud providers and use it across all Fabric workloads such as Power BI reports, SQL, Spark and Kusto. However, each time these workloads read data from cross-cloud sources, the source provider (AWS, GCP) charges additional egress fees on the data. Thankfully, shortcut caching allows the data to only be sourced once and then used across all Fabric workloads without additional egress fees.
This is useful for data that hardly ever changes, and Trevor also shows you who can control the cache length and reset the cache. In addition, the on-premises gateway for shortcuts is now generally available, so you can take shortcuts of certain on-prem file systems.
Comments closedRobin Moffatt does some exploratory data analysis:
One of the challenges that I’d always had when it came to building streaming data pipelines is that once data is in a Kafka topic, it becomes trickier to query. Whether limited by the available tools to do this or the speed of access, querying Kafka is just not a smooth experience.
This blog post will show you a really nice way of exploring and validating data in Apache Kafka®. We’ll use Tableflow to expose the Kafka topics as Apache Iceberg™️ tables and then query them using standard SQL tools.
Click through for the demonstration using a real dataset.
Comments closedReitse Eskens has the need for speed:
The issue was quite straightforward. The client has a mapping data flow in Synapse that processes a few hundred to a few thousand rows but takes 15 minutes to complete. The low number of rows compared to the time necessary is a cause for concern.
The data extraction needs a staging storage account where the data is written into TXT files. The second step of the mapping data flow reads the TXT files and writes them out in delta format, which is Parquet files.
The source is an S4Hana CDC table, the target of which is a regular Azure storage account.
Read on for Reitse’s summarization of the troubleshooting and testing process, as well as what ended up working for this customer.
Comments closedKoen Verbeeck runs into a problem:
I recently had a new pipeline fail. It was actually a copy of an old pipeline where I had made some adjustments into as part of a database migration. When triggered during an execution run, it failed saying some expression could not be parsed. When I went into the pipeline and triggered a debug, it immediately failed with the following helpful error message:
Click through for the error message and how Koen was able to fix the issue.
Comments closedSuppose you want to call a certain Microsoft Fabric REST API endpoint from Azure Data Factory (or Synapse Pipelines). This can be done using a Web Activity, and most Fabric APIs now support service principals or managed identities. Let’s illustrate with an example. I’m going to call the REST API endpoint to create a new lakehouse.
Click through for the instructions.
Comments closedMehdi Ghapanvari explains why TABLOCK can be useful for bulk inserts:
Does bulk insert performance improve when you use the TABLOCK hint? In some cases, YES! Let’s take a look at this in action to see how this hint could improve insert performance when using SQL bulk insert with TABLOCK.
Of course, standard TABLOCK concurrency rules (specifically, the lack of concurrency) apply.
Comments closedChris Webb doesn’t want to wait:
If you’re working with slow data sources in Power BI/Fabric dataflows then you’re probably aware that validation (for Gen1 dataflows) or publishing (for Gen2 dataflows) them can sometimes take a long time. If you’re working with very slow data sources then you may run into the 10 minute timeout on validation/publishing that is documented here. For a Gen1 dataflow you’ll see the following error message if you try to save your dataflow and validation takes more than 10 minutes:
Click through for that common error message, as well as some tips to avoid this issue. There was also an interesting approach in the comments section that circumvented the problem as well.
Comments closed