How can I make a Snowflake / Snowpark scripting block return the resonse of a COPY INTO statment? In our ELT pipeline I use Snowpark to copy data from an S3 stage into a table on a daily basis, like this: sql_copy_into = f""" COPY INTO {target_table} FROM {stage_file_name_full_path} FILE_FORMAT = ( TYPE = CSV COMPRESSION = 'AUTO' FIELD_DELIMITER = {field_delimiter} ) ON_ERROR = CONTINUE; """ response = session.sql(sql_copy_into) results = response.collect() Now I want to store the results of the COPY INTO in a logging table, which is working well so far. However, once I wrap the COPY INTO into a Snowflake scripting block: sql_copy_into = f""" BEGIN TRUNCATE TABLE {target_table}; COPY INTO {target_table} FROM {stage_file_name_full_path} FILE_FORMAT = (...) ON_ERROR = CONTINUE; END; """ response = session.sql(sql_copy_into) results = response.collect() Then I get the results as [Row(anonymous block=None)] It looks like the scripting block isn't returning anything. How can I make the scripting block return the resonse of the COPY INTO statment? I have read about the RETURN command, but I can't figure out how this can help me here. The only option I see is to send two different SQL statements, once for the TRUNCATE and another one for the COPY INTO. Then I could avoid the BEGIN / END. But there has to be a better way. Any hints are greatly appreciated! Continue reading...
