Ed Elliott's Agile SQL Club

Running tSQLt unit tests is great from Visual Studio but my development workflow isn’t just write tests, run tests, fix tests, run tests anymore, it is 2026 and I am a big fan of using claude code to help me write more efficiently than I used to be with j…

I have had a number of requests for me to update the tSqlt Test Adapter over the years so it would work with more recent versions of Visual Studio.

SQL Server Management Studio (SSMS) has recently added support for Github Copilot.

All Spark Connect Posts I have just finished an update for the spark connect dotnet lib that contains the DeltaTable implementation so that we can now use .NET to maintain delta tables, over and above what we get out of the box by using DataFrame.Write.Fo…

I recently encountered an interesting issue with ADF where the publish feature suddenly attempted to republish every single object, claiming they were new, despite having incrementally published changed objects for some time.

All Spark Connect Posts Introduction There have been quite a few changes in the last couple of months and I just wanted to give a quick update on the current state of the project.

All Spark Connect Posts I recently published the latest version of the Spark Connect Dotnet library which includes support for the new Variant data type in Apache Spark 4.0 here.

UPDATE - I have implemented delta in spark-connect-dotnet All Spark Connect Posts Code What to do?

All Spark Connect Posts Code Goal of this post The goal of this post is to look at creating a SparkSession and a DataFrame that will wrap the Range relation and then we will use the WithColumn function to add a column to the DataFrame and then we will use…

All Spark Connect Posts Code Goal of this post I wanted to explore what the Spark Connect API looked like from other languages, I am not a php developer - I used it a long time ago and read up on some of the modern changes but apologies if I insult any ph…

All Spark Connect Posts Code Goal of this post So there are two goals of this post, the first is to take a look at Apache Arrow and how we can do things like show the output from DataFrame.Show, the second is to start to create objects that look more fami…

All Spark Connect Posts Code Goal of this post In this post we will continue looking at the gRPC API and the AnalyzePlan method which takes a plan and analyzes it.

All Spark Connect Posts Code Goal of this post In the first two posts, we looked at how to run some Spark code, firstly against a local Spark Connect server and then against a Databricks cluster.

All Spark Connect Posts Goal of this post This post aims to show how we can create a .NET application, deploy it to Databricks, and then run a Databricks job that calls our .NET code, which uses Spark Connect to run a Spark job on the Databricks job clust…

All Spark Connect Posts Introductory Ramble Spark Connect In July 2022, at the Data and AI summit, Apache Spark announced “Spark Connect,” which was a way of connecting to Apache Spark using the gRPC protocol rather than using the supplied Java or Python…

All Spark Connect Posts When I wrote the spark-connect-dotnet lib I didn’t envisage that I would implement every function, instead it would be a combination of implementing the most common functionality and showing people how they can make their own gRPC…

Spark Connect References https://spark.apache.org/docs/latest/spark-connect-overview.html https://www.databricks.com/blog/2022/07/07/introducing-spark-connect-the-power-of-apache-spark-everywhere.html Reference Implementations Rust GOLang Source gRPC proto

The Agile SQL CLub Privacy Policy (google Made me do it) The website uses cookies to track views and where you go, if you block cookies then they will be stopped.

I made a mistake recently when I was creating an ADF pipeline, annoyingly I made loads of changes and then clicked the debug button, when I pressed debug the pipeline failed to start and I was presented with this little beaut of an error message: The pipe…

In my previous blog post I talked about how to read from an XML Webervice and use xpath to query the XML on the expressions side of things.

When you use ADF, there are two sides to the coin.

This is a bit of a longer one, a look at how to do all the different joins and the exciting thing for MSSQL developers is that we get a couple of extra joins (semi and anti semi oooooooh).

The next example is how to do a CTE (Common Table Expression).

The next example is how to do a ROW_NUMBER(), my favourite window function.

I have been playing around with the new Azure Synapse Analytics, and I realised that this is an excellent opportunity for people to move to Apache Spark.

When I first started working with Apache Spark, one of the things I struggled with was that I would have some variable or data in my code that I wanted to work on with Apache Spark.

Git is hard, probably harder than it needs to be but I have been using it for about 5 years and have a workflow that works for me.

Spark ML in .NET for Apache Spark Apache Spark has had a machine learning API for quite some time and this has been partially implemented in .NET for Apache Spark.

The Problem When you use a UDF in .NET for Apache Spark, something like this code: class Program { static void Main(string[] args) { var spark = SparkSession.Builder().GetOrCreate(); _logging.AppendLine("Starting Select");

Last Updated: 2020-10-18 NOTE: What you read here was before .NET for Apache Spark 1.0 which includes everything we need to do this purely in .NET - in this post you will see an example that is no longer necessary for TF-IDF, instead view: https://the.agi…

Databricks is fantastic, but there is a small issue with how people use it.

Every ETL pipeline is only ever as reliable as the data that the upstream system provides.

When we use ADF to call Databricks we can pass parameters, nice.

When you use delta lake there are a couple of interesting things to note based around the fact that the data is stored in parquet files which are read-only and delta lake includes the ability to delete and update data and view the state of a table at a sp…

It is a non-null constraint, not a non-ish-null constraint You are writing an ETL process, part of this process you need to import a semi-structured file (think CSV, JSON, XM-bleurgh-L, etc.) when you import the data into an RDBMS you get all sorts of thi…

It has been a little while but I have updated SQLCover to include a number of fixes and small features, the majority of which are improvements to the html output: For full details and to download the latest version see: https://github.com/GoEddie/SQLCover…

This is the final part in the four-part series into testing ETL pipelines, how exciting!

“[Error] [JvmBridge] java.sql.SQLException: No suitable driver” - unable to connect spark to Microsoft SQL Server.

In the first two parts of this series (https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelines-part-one-unit-tests/ and https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-processes-are-correct-how-do-we-make-sure-upstream-changes-dont-break-…

I finally got around to updating the tSQLt test adapter for visual studio, you can download it from: https://marketplace.visualstudio.com/items?

Steps needed Getting Apache Spark running on windows involves: Installing a JRE 8 (Java 1.8/OpenJDK 8) Downloading and extracting SPARK and setting SPARK_HOME Downloading winutils.exe and setting HADOOP_HOME If using the dotnet driver also downloading the…

When you run an application using spark-dotnet, to launch the application you need to use spark-submit to start a java virtual machine which starts the spark-dotnet driver which then runs your program so that leaves us a problem, how to write our programs…

ETL Testing Part 2 - Operational Data Testing This is the second part of a series on ETL testing, the first part explained about unit testing, and in this part, we will talk about how we can prove the correctness of the actual data, both today and in the…

I found this question on stack overflow that went something like this: “I have a file that includes line endings in the wrong place and I need to parse the text manually into rows” (https://stackoverflow.com/questions/57294619/read-a-textfile-of-fixed-len…

There was a breaking change with version 0.4.0 that changed the name of the class that is used to load the dotnet driver in Apache Spark.

Why do we bother testing?

How do you read and write CSV files using the dotnet driver for Apache Spark?

Apache Spark is written in scala, scala compiles to Java and runs inside a Java virtual machine.

I really like the new dotnet driver for Spark because I think it makes spark more accesable to devs who might not know pythpn or scala.

Here are three scenarios, if you work with SQL Server either as a provider of database environments (DBA) or a consumer of database environments for your application (developer) then you will likely see yourself in one of these descriptions.