Raise Warning when Table and Column Names are Equal in Snowflake Join

App Development
1

Occasionally users submit a query where the table and column names are equal in the join condition. This can lead to fan-outs.

Example

select
    table_a.*
from table_a
left outer join table_b
    on table_a.col_1 = table_a.col_1

Exception handling seems to be only available for Snowflake Scripting

Is there a programatic way to raise a warning when this happens?

4

Hi @veerahkr
Welcome to the community :wave: and thanks for being patient.

Can you share further details about the setup? Unfortunately, I am not able to see how your app/integration interacts with Pipedrive and how the issue originates as a result of that.

5

Hey veerahkr, Snowflake doesn’t have a native way to raise warnings on arbitrary SQL patterns outside of scripting blocks, so you’re right that exception handling alone won’t cover this.

The most practical route is parsing the query text before it runs. Python’s sqlparse library can tokenize an ON clause and you check whether both sides of the = share the same table qualifier. Something like extracting the identifiers from each comparison, splitting on the dot, and comparing the table prefix. You can wire that into whatever layer accepts queries from users and reject or flag them before they ever hit Snowflake.

If you want to catch it after execution instead, GET_QUERY_OPERATOR_STATS() lets you compare output rows to input rows per join operator ( GET_QUERY_OPERATOR_STATS | Snowflake Documentation ):

SELECT operator_id,
       operator_statistics:output_rows / operator_statistics:input_rows AS row_multiple
FROM TABLE(GET_QUERY_OPERATOR_STATS(LAST_QUERY_ID()))
WHERE operator_type = 'Join';

A ratio way above 1.0 on what should be a normal join is a strong signal of a self reference or missing predicate. We see this across a lot of client Snowflake environments at Stacksync and the pre execution parse approach saves a lot more pain than profiling after the fan out already happened.