Add some try-catch like mechanism for SQL queries

[colmsnowplow]

One of the challenges to writing a set of general SQL models is that some of the data we'd like to have models for isn't always present. So we're aiming for a structure that allows one to enable certain things as 'add-ons', if the data is there.

For example, let's say we'd like to have the IAB enrichment available, but that's not a standard feature for everyone. If you don't have it switched on, the table doesn't exist. So any SQL that tries to query the table will fail the job, and SQL doesn't support IF logic to the level that solves this problem.

It feels like we could do some really awesome things if we could write some logic along the lines of:

• Try: SELECT * FROM some_table LIMIT 1
• If success: Run x module of SQL queries
• If {table doesn't exist failure}: Continue without attempting that module
• Else: fail as usual

Not sure how achievable it is, or if there's a better solution to that problem. Probably a 'nice to have', but could be pretty cool/powerful!

[alexanderdean]

You've reinvented PL/SQL, Oracle's imperative language:

https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm

Agree this would be a great thing to add, as I don't think any of our data warehouses support this kind of decision-tree capability...

[alexanderdean]

If {table doesn't exist failure}: Continue without attempting that module

I'd prefer to do that though with an explicit test for table existing rather than catching an exception. It's more deterministic.

[colmsnowplow]

I'd prefer to do that though with an explicit test for table existing rather than catching an exception. It's more deterministic.

Yeah for sure I agree.

Agree this would be a great thing to add, as I don't think any of our data warehouses support this kind of decision-tree capability...

So Redshift actually does support procedures: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

But I never drew the line between that and this kind of thing (I actually didn't realise that Redshift supported IF statements in procedures until now tbh!). I'll explore if this fits the bill for Redshift...

[alexanderdean]

Looks like BQ and Snowflake also support stored procs...

[colmsnowplow]

Interesting. The missing piece of the puzzle that I need to test then is whether this can handle non-existent resources (or checks for their existence).