[ADAP-1081] [Bug] connections._split_queries() breaks stored procedure statements (LANGUAGE SQL)

[irvingpop]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I wish to manage a Snowflake stored procedure using dbt. Given the following dbt macro:

{% macro macro_sp_test() %}

CREATE OR REPLACE PROCEDURE macro_sp_test()
  RETURNS NUMBER
  LANGUAGE SQL
  AS
  DECLARE
    var1 NUMBER;
  BEGIN
    var1 := (select 1);
    RETURN :var1;
  END
;

{% endmacro %}

The statement is being broken because it's split on the semicolons:

23:35:10  On master: /* {"app": "dbt", "dbt_version": "1.7.3", "profile_name": "default", "target_name": "dev", "connection_name": "master"} */
CREATE OR REPLACE PROCEDURE macro_sp_test()
  RETURNS NUMBER
  LANGUAGE SQL
  AS
  DECLARE
    var1 NUMBER;
23:35:10  Opening a new connection, currently in state init
23:35:11  Snowflake adapter: Snowflake query id: 01b15827-0404-180b-0025-160311680546
23:35:11  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 6 at position 16 unexpected '<EOF>'.
23:35:11  Database error while running on-run-start

I traced this down to the use of snowflake.connector.util_text.split_statements

Expected Behavior

I expected that it would correctly detect that it's in a stored procedure and not break up the SP.

Steps To Reproduce

above

Relevant log output

above

Environment

- OS: Darwin 23.2.0
- Python: Python 3.11.6 (main, Nov  2 2023, 04:39:43) [Clang 14.0.3 (clang-1403.0.22.14.1)]
- dbt-core: 1.7.3
- dbt-snowflake: 1.7.0

Additional Context

I also mentioned in the dbt community slack: https://getdbt.slack.com/archives/C2JRRQDTL/p1703980883124329

[irvingpop]

also filed in the Snowflake repo here: snowflakedb/snowflake-connector-python#1843
because I'm not sure which is the better place to file this issue

[JohnDuqueCAIS]

Hi @irvingpop

Try with this:

{% macro macro_sp_test() %}
CREATE OR REPLACE PROCEDURE {{ target.database }}.{{target.schema}}.macro_sp_test()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
var1 NUMBER;
BEGIN
var1 := (select 1);
RETURN :var1;
END;
$$;
{% endmacro %}

[irvingpop]

omg, how did I miss that 🤦
You're right, it totally works if you surround the statement with $$'s. Sorry for the noise!