Snowflake data unloading - multiple tables

Question

Does snowflake provide any utility to unload data from multiple tables at the same time? Somewhat similar to expdp (or export) in Oracle? I can write a procedure to fetch table names and copy/unload to stage files, but was wondering if there is any other utility that might be available out of the box in snowflake for this? Also it would be helpful if someone can point out approach or best practices to use for schema refreshes.

Answer 1

You can do what I mention above with SQL like this:

create table test.test.table_to_export(full_table_name text, write_path text);

create table test.test.table_a(id int);

insert into test.test.table_to_export values ('test.test.table_a', 'table_a/a_files_');

Then running this will use COPY TO to write you my_stage in the form of this example for each row in the at the same time point, so the data is coherent between them (via time travel) thus this method would only work on permanent tables.

declare
  c1 cursor for select full_table_name, write_path from test.test.table_to_export;
  sql text;
  sql_template text;
  id text;
begin
  select 1;
  select last_query_id(-1) into id;
  
  sql_template := $$copy into @my_stage/result/WRITE_PATH FROM (select * FROM TABLE_NAME AT(STATEMENT => '$$ || id || $$'))
   file_format=(format_name='myformat' compression='gzip');$$;
   
  for record in c1 do
    
    sql := replace(replace(sql_template, 'TABLE_NAME', record.full_table_name), 'WRITE_PATH', record.write_path);
    
    execute immediate sql; 

  end for;
end;