Solved

Snowflake - specify temp table location/schema

  • 23 October 2023
  • 3 replies
  • 110 views

Userlevel 3
Badge +1

When bulk loading to Snowflake, data is uploaded to a temp table (named by CData) and then insert/merged into the final target table.

Is there a way to specify, either at the Connection level, or the Job level, what Database or Schema should be used to store those tables? 

 

By default it just puts them in the target schema, but it would be nice to split those out so we can more quickly clean anything up after an aborted job.

icon

Best answer by Ankit Singh 8 November 2023, 12:00

View original

3 replies

Userlevel 5
Badge +1

Hi @DougN Nouria 

From what I know, it should be creating the Temp table in the same schema you select at the Task level settings for each table. Is that not the case in your testing?

Userlevel 3
Badge +1

Indeed, it is creating the temp tables in the same schema.  I would like to pick a different schema for where the temp tables go (like a WORKING schema) so that I have one place to look for any crashed-job manual cleanup work.

 

Userlevel 3
Badge +1

One other benefit, for Snowflake purposes:  If you can specify a single schema for temporary tables that CData creates, you can create that schema as a TRANSIENT schema.  This disables the fail-save/time-travel features, to reduce your storage costs.

Reply