Snowflake is a modern cloud-based data platform that allows you to store, process, and analyze large amounts of data efficiently. It is widely used for data warehousing, analytics, and data integration.
In SAP Integration Suite (CPI), the Snowflake Adapter helps you connect to Snowflake and perform operations like:
- Data loading
- Querying
- Data export
To get started with Snowflake Adapter, refer to the Snowflake Introductory Blog.
While these operations work out of the box, using optional parameters can significantly improve performance, cost, and reliability.
Why Do We Need Optional Parameters?
In real-world iFlows, you often deal with:
- Large files
- Multiple file uploads
- Error handling
- Storage optimization
This is where optional parameters become very useful.
Optional parameters help you to:
- Control how data is loaded/unloaded
- Improve performance
- Reduce storage costs
- Handle errors gracefully
The latest version (1.2.0) of the adapter also supports bulk load/unload using Snowflake Internal Stage with optional parameters.
Let us explore various scenarios where optional parameters can be used in your day to day processes.
SCENARIO 1: Handling reprocessing and errors while uploading a file
Problem: A file is uploaded into Snowflake and sometimes the same file may be sent again, or a few records inside it may have errors. You want the load to continue without failing completely despite a few bad rows or the file was already processed before.
Parameters Used:
FORCE = TRUE
ON_ERROR = CONTINUE
What Happens:
- FORCE = TRUE → Loads files even if they were already processed
- ON_ERROR = CONTINUE → Skips bad records and continues processing
Why Use This:
- Prevents pipeline failure due to a few bad rows.
- Useful in real-time or bulk integrations where data quality may vary.
SCENARIO 2: Purging staged file in Snowflake after BulkUpsert load
Problem: After BulkUpsert loads data from stage files into Snowflake tables, the files remain in the stage area unless they are removed. Over time, these leftover files build up, use extra storage, and may accidentally be picked up again for processing.
Parameter Used:
PURGE = TRUE
What Happens:
- Deletes files from the stage after successful load
Why Use This:
- Saves storage cost
- Keeps your stage clean
- Avoids reprocessing old files
SCENARIO 3: Loading files into Snowflake while limiting how much data is processed in one run
Problem: You may want to control batch size so that only files up to a certain total size are loaded at a time, while the remaining files are left for a later run.
Parameter Used:
SIZE_LIMIT = <num> (in bytes)
What Happens:
- Snowflake loads files until the total size reaches the limit
Behavior:
- Single small file → fully loaded
- Single large file → still fully loaded (no partial load)
- Multiple files → loaded until limit is reached; remaining files are skipped
Example: If you try to bulk upsert File 1 = 10 MB, File 2 = 5 MB, and SIZE_LIMIT = 1,000,000 bytes (1 MB), Snowflake will load the first file completely and skip the second file.
Why Use This:
- Helps control batch size
- Useful for testing or incremental loads
SCENARIO 4: Exporting Snowflake data as a single output file.
Problem: Some downstream systems expect only one file, so the export needs to produce a single file instead of multiple files.
Parameter Used:
SINGLE = TRUE
What Happens:
- Snowflake writes all data into a single file
Why Use This:
- Required when downstream systems expect a single file
- Best for small datasets (Note: Not recommended for large data as it reduces performance.)
- Useful for testing or incremental loads
SCENARIO 5: Exporting large Snowflake data while controlling output file size.
Problem: When unloading large volumes of data, you may want Snowflake to split the output into manageable file sizes instead of creating one very large file.
Parameter Used:
MAX_FILE_SIZE = <num>(in bytes)
What Happens:
- Snowflake splits data into multiple files
- Each file tries to stay within the specified size
Why files can be slightly LARGER:
- Snowflake does not split rows
- Formatting and metadata add extra size
Why files can be SMALLER:
- Snowflake uses multiple workers (parallel processing)
- Data is split based on internal storage (micro-partitions)
Important Understanding:
- MAX_FILE_SIZE is a limit, not a strict rule
- Snowflake prioritizes performance over exact file size
Example: If you set MAX_FILE_SIZE = 1,000,000 bytes (1 MB), you may still see files slightly larger than 1 MB and multiple smaller files due to parallel execution.
You might see file names like Unload-File_0_6_3.csv.
This means:
- 0 → Node ID
- 6 → Worker/Thread ID
- 3 → File sequence number
This helps Snowflake manage files during parallel processing.
Why Use This Parameter:
- Controls number of output files
- Balances performance vs file management
- Improves data transfer efficiency
Final Thoughts
Optional parameters in Snowflake are very powerful and help you optimize your integration flows based on your needs.
Whether it is handling errors, managing storage, controlling file sizes, improving performance, these parameters give you better control over your data operations.
For more advanced and complete details on all available parameters, refer to the official Snowflake documentation for Bulk Upsert and Unload.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.