Write

Security prerequisites: You should have the following permission in your Snowflake account to execute this Snap:

• Usage (DB and Schema): Privilege to use the database, role, and schema.

The following commands enable minimum privileges in the Snowflake Console:

grant usage on database <database_name> to role <role_name>;
grant usage on schema <database_name>.<schema_name>;

For more information on Snowflake privileges, refer to Access Control Privileges.

The below are mandatory when using an external staging location:

When using an Amazon S3 bucket for storage:

• The Snowflake account should contain S3 Access-key ID, S3 Secret key, S3 Bucket and S3 Folder.
• The Amazon S3 bucket where the Snowflake will write the output files must reside in the same region as your cluster.

When using a Microsoft Azure storage blob:

• A working Snowflake Azure database account.

This Snap uses the following commands internally:

• SELECT FROM TABLE: SELECT as a statement enables you to query the database and retrieve a set of rows. SELECT as a clause enables you to define the set of columns returned by a query.

• COPY INTO <table>: Enables loading data from staged files to an existing table. 

• CREATE TEMPORARY TABLE: Enables creating temporary table in the database. See Create Temporary Table Privilege for more information.

• MERGE: Enables inserting, updating, and deleting values in a table based on values in a second table or a subquery.

• Works in Ultra Task Pipelines.
• The special character '~' is not supported if it is there in temp directory name for Windows. It is reserved for user's home directory.

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. The S3 Bucket, S3 Access-key ID, and S3 Secret key properties are not needed for the Snowflake Bulk Upsert Snap as Snowflake uses its internal staging area to host the input data. See Configuring Snowflake Accounts for information on setting up this type of account.

The type of staging location that is used for data loading.

The options available include:

• External: Location that is not managed by Snowflake. The location should be an AWS S3 Bucket or Microsoft Azure Storage Blob. These credentials are mandatory while validating the Account.
• Internal: Location that is managed by Snowflake.

Default value:  Internal

Specifies the type of encryption to be used on the data. The available encryption options are:

• None: Files do not get encrypted.
• Server Side Encryption: The output files on Amazon S3 are encrypted with the server-side encryption.
• Server-Side KMS Encryption: The output files on Amazon S3 are encrypted with Amazon S3-generated KMS key. 

Default value: No default value.

The KMS key that you want to use for S3 encryption. For more information about the KMS key, see AWS KMS Overview and Using Server Side Encryption.

Default value: No default value.

The size of each part, or chunk, of data being uploaded, in MB. 

Minimum value: 6 MB

Maximum value: 5000 MB

Default value: 10 MB

The Snap uses the S3 Multipart Upload API to write files to S3. For more details, see Amazon S3 Multipart Upload Limits.

Required. Columns to use to check for existing entries in the target table.

Default:  None 
 

Delete Upsert Condition when true, causes the case to be executed.
Default:  None

Select this property to decide whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the pipeline.

Default value: Continue to execute queued queries when the pipeline is stopped or if it fails

If selected, empty string values in the input documents are loaded as empty strings to the string-type fields. Otherwise, empty string values in the input documents are loaded as null. Null values are loaded as null regardless.

Default value:  Selected