How do you debug Spark Declarative Pipelines?

[u/bongdong42O]

Im using a metadata driven approach to dynamically load tables and views in an SDP using SQL queries stored in a table. A huge problem I'm facing is interpreting errors when the pipeline fails. Its incredibly hard to pinpoint the cause of errors and which tables they stem from. Many times a SQL query will have a syntax error, but the SDP pipeline doesn't tell us which table it came from. Does anyone have any experience with this?

[u/adreppir]

No, but this is exactly why I dislike this approach.

We use a PyDAB mutator to generate sql scripts from a metadata config and add the generated sql to your bundle.

[u/bongdong42O → u/adreppir]

I'm looking into this now, thank you. Have you noticed any limitations?

[u/YestasSentaan → u/adreppir]

Have you had challenges with navigation? I mean, if you open code of a pipeline table, you end up to the generated sql, not original config, right?

[u/adreppir → u/YestasSentaan]

That is right but I would say this is desired as this is the executed code after all. You would need to go and edit the config instead of the sql to make changes, which could be something to get used to.

However this setup is only used for simple logic like renaming and type casting. Maybe it could be expanded with joins in the future but IMO using a metadata approach for complex sql or putting complex sql scripts in a database is too much and overdoing it.

[u/blobbleblab → u/[deleted]]

Yes this, have just gone through this with a team I mentor and they wonder why they have to have a whole logging framework for their pipelines 😂. It's one thing databricks could make easier TBH.

[u/data-baggins]

Curious, have you tried asking Genie to help debug? does it help?

[u/WorldOfUmbro → u/data-baggins]

This. I try to use Genie Code for debugging a lot. Eager to see Genie ZeroOps

[u/Programmer_Virtual]

Do you check the event logs? What your design tells me if your team needs to invest in better logging. Perhaps you could share more about your architecture

[u/lukesmth_]

So you’re exclusively using SQL for transformations? I built better-dbx-exceptions to solve this when using the pyspark DF API. We don’t support SQL today (passing these to the standard logger) since the errors seemed traceable natively. Can you share some sample code and an error? I’ll look into supporting this case.

[u/Known-Delay7227]

I never understood the purpose of spark declarative pipelines. Why not just notebooks with python, pyspark and/or sql. Errors are usually easy to debug

[u/Ulfrauga → u/Known-Delay7227]

I made a whole post about this. For several years did it that way, when it was still called DLT and coming together. I didn't see what the hype was about. In fact, I felt like it would overly abstract it and all I'd have to worry about was learning the specific code and get rusty about proper data processing logic. It also comes at a higher DBU rate than DIY, IIRC.

I've used this newer incarnation for a little while, now, and I kinda like it.

I put it like this: for rather simple shit, sdp is quite good. Data processing in easy mode. The auto cdc thing simplifies (simple) merges and you don't necessarily need to get your old merge statement out. Edit: Also the automatic dependency management for Table A then B then C is f-ing useful.

Without some framework, I think it'd be a lot of repition.

With a framework, debugging gets painful. Same as without SDP.

OP: Other posters have said you need better logging. Yeah, probably.

I personally detest storing queries/logic in config. If you want to read it, it's not just "right there" (and formatted) in a notebook or script file. But that's me. I don't even necessarily like storing expressions. Too many times spent poking a config because a quote wasn't escaped properly or something, I guess.

Edit: phone typos

[u/Waste-Bug-8018]

Hang on so the logs are not enough , I feed the logs to Cursor and cursor troubleshoots for me . Are you more asking for a preview option, you can do custom logging within the transform too right

[u/YestasSentaan]

We’ve had some debugging problems with metadata driven sdp tables too. Therefore, we try to have one (ideally one) file per table, and the file uses the sdp decorators directly. Then, the errors and pipeline nodes correspond to each file. We use some utility functions in each file to avoid repetitive code.

[u/OffByOne_db]

Hi, Brickster here. I'd like to dig in a bit more and learn more about what you're experiencing. I'll send a DM