Man sat in an office in front of a laptop with his hand on his chin pondering.I had one of those moments this week, I spent too long trying to figure out how something that I knew was broken, was running fine and throwing no errors. Can it be called troubleshooting if it isn’t causing trouble?

I have a Synapse pipeline that’s been running just fine for months, due to some migration work, I needed to take a look at the source query and found it was broken. I had it open in SSMS on the other monitor, ran it, received a conversion error. Same query, same data, two different outcomes.

Here’s what I found out, and what’s actually going on under the hood.

The Context

The source query is a view called in a dataflow task in the Synapse pipeline. One of the columns is defined as

CAST([MyVarcharColumn] AS NUMERIC(11,8)).

The staging data, as it turns out, contains numeric values with carriage returns and line feeds (CR/LF), almost-numeric, but not numeric enough. This is common in a lot of source systems, SQL Server’s CAST(varchar AS NUMERIC) tolerates leading and trailing spaces silently, but not tabs, LFs, or CRs. So the view’s CAST throws as Error converting data type varchar to numeric.

Fair enough. The naïve view has dirty data and no defensive clean. That’s exactly what you’d expect.

But it Works in Synapse?

The pipeline in question is part of a robust, enterprise ETL system with full logging and error management and alerting, yet the pipeline hadn’t been failing this entire time. I triggered the pipeline manually to test it, and it worked.

Confused, I clicked Data Preview in the same Data Flow and it failed (giving the same conversion error). I ran the underlying SQL in SSMS again and it fails the same way Data Preview did.

This bothered me. A pipeline shouldn’t accidentally succeed at executing broken code.

So what was actually happening here?

The Answer!

Man sat in an office in front of a laptop with his left hand pointing into the sky like he has just had an idea. He is smiling.

When a Synapse Data Flow pipeline runs against a SQL source, Spark’s Java Database Connectivity (JDBC) reader inspects which columns are actually consumed downstream, by transformations, derived columns, and ultimately by the sink mapping. It then pushes down a projection containing only those columns. The query that actually arrives at the SQL pool isn’t SELECT * FROM MyView. It’s SELECT [ColumnA], [ColumnB], [ColumnC], [etc] with [MyBrokenColumn] missing from the column list, because nothing downstream is using it. (It had previously been removed from the solution and the source query had not been tidied up)

SQL Server’s optimiser then does the rest. When the outer query doesn’t reference [MyBrokenColumn], the expression behind that column in the view’s projection, including the failing CAST, is removed from the plan. It never executes against any row. The bad data never goes through the cast. No error.

Data Preview deliberately doesn’t prune, its whole point is to let you inspect every column coming out of the source, so it ends up with the same plan as the SELECT * and dies the same way.

Verifying it

For the full picture, you can catch the actual query Spark issues during a pipeline run by running an Extended Events session on the SQL pool, or querying sys.dm_pdw_exec_requests (dedicated) / sys.dm_exec_requests (serverless). You’ll see the pruned column list with no [MyBrokenColumn] in sight.

Is this Worse than Failure?

Man sat in an office in front of a laptop with both hands shrugging slightly.

The pipeline isn’t really “working”. It’s accidentally not exercising the bug. That’s a very different thing.

A few ways this falls apart on its own:

  • [MyBrokenColumn] gets added back in to a downstream transformation or sink mapping. The CAST comes back into the plan. The pipeline starts failing in production with no view code change.
  • Someone adjusts a Select transformation in the Data Flow. Same problem.
  • The connector’s pushdown behaviour changes between runtime versions, or stops pushing for an unrelated reason. Same problem.

A test suite that runs the pipeline won’t catch the underlying brokenness, because the pipeline is the thing accidentally hiding it. You only notice when a downstream change quietly reactivates the bad code path, by then the diagnostic trail is cold because nothing changed in the failing component.

Takeaway

Pipelines that succeed when they shouldn’t are real head scratchers. A failure says “fix me.” A silent success says “everything is fine” right up to the day it isn’t, and by then nobody has any idea where to start.

My advice would be to audit and tidy up the source queries so there’s no secret column removal happening at runtime. The less hidden the mechanisms are, the easier failures are to troubleshoot.

If you’ve got a Data Flow over a SQL source and you’re not entirely sure the source view would survive a SELECT * FROM view in SSMS, give it a try. The answer might be more interesting than you’d like it to be.

 

Tags: , , ,

« Previous Post
Next Post »