@samhita has a great answer that was also considered as a solution.

What I've done is basically the same just using SQL instead of python. This was built inside our ETL tool so I could use local variables as you see below.

So the solution that I went with was as follows:

1. Load the .sql file into a Snowflake table directly from S3. I dumped it into a raw string table like so, create or replace TABLE RAW_SQL ( DATA VARCHAR(16777216) );
2. Next was to use SQL scripts to pull out each of the DROP / CREATE / INSERT statements and run them individually. I discovered that the INSERT values actually do fit in the varchar(max) 🤦🏻‍♂️, I was wrong about that.
3. Here is an example of the SQL to parse the INSERT INTO statement select replace(replace(concat(i,v),'`',''),$$'0000-00-00 00:00:00'$$ ,'null') as sql from (-- get insert and corresponding values clause (next row) select data as i,lead(data) over(order by row_id) as v from (-- get ordered list of stmts select data, row_number() over(order by 1) as row_id from raw_sql where data like any('INSERT INTO%','VALUES (%') ) ) where contains(i,'INSERT INTO')

You can see I had to do some cleanup of the incoming data (the replaces) but just put together the INSERT and VALUES clause and then EXECUTE IMMEDIATE.

execute immediate $$${sql}$$

Where {sql} is a variable that holds the sql statement in a string.

Maybe it's not pretty but it works! :D

Thanks to everyone for your help and responses!