Snowflake Native Apps Best Practices

Snowflake Native Apps Best Practices#

Warehouse Size#

For optimal performance, use a dedicated warehouse for Native Apps. A warehouse size of X-Small is typically sufficient.

Snowflake SQL Scripting#

General information about Snowflake SQL Scripting can be found in the Snowflake documentation at Snowflake SQL Scripting.

Batch Processing#

Native Apps are especially designed to process data in batches. We recommend using the following approach to handle large datasets:

  • Unless you have a specific requirement, we recommend using the default input and output fields. A smaller number of output fields can improve performance.

  • RecordID is Required for all input records and must be Unique. If you wish to use the same output table for multiple runs, make sure RecordID is unique across all runs.

  • To minimize the risk of duplicate requests caused by server outages or unknown issues during processing, we recommend the following approach:

    • Enable event sharing for an app to monitor the process.

    • Pre-process the input source to make sure all RecordID are unique, make a clean copy of it. Split large datasets into smaller batches.

    • Process each batch separately. Avoid unnecessary loops in your script.

    • Combine the results if necessary.

    • Set DUPLICATE_CHECK => TRUE (if available) to catch any duplicate RecordIDs.

      Note

      Enabling DUPLICATE_CHECK may increase runtime, especially for large datasets. We recommend testing with and without this option to determine the best approach for your use case.

Events and Logs#

Important

We highly recommend enabling Events and Logs to facilitate troubleshooting in case of any issues.

To observe and troubleshoot app behavior, you can enable Logging and Event Tracing for your account and share the app logs with us.

SELECT
   RESOURCE_ATTRIBUTES:"snow.application.name"::STRING as APP_NAME
   ,RECORD
   ,RECORD_ATTRIBUTES
   ,RECORD_TYPE
   ,TIMESTAMP
FROM <YOUR_EVENT_TABLE>
WHERE RECORD_TYPE LIKE 'SPAN_EVENT%'
   AND APP_NAME = '<YOUR_APP_NAME>'
ORDER BY TIMESTAMP DESC
LIMIT 100;

For more information, please check Snowflake Documentations below:

License Key as Input#

You can store your License Key in a secure location and reference it in your SQL scripts using the SELECT statement.

USE MELISSA_NATIVE_APP.CORE;

CALL STORED_PROCEDURE_NAME(
  LICENSE         => (SELECT LICENSE_KEY FROM <MY_SECURE_TABLE>),
  -- other parameters
);

Special Characters in Source Data#

Melissa Native Apps are designed to handle special characters in source data except for certain control characters like backslash \. We recommend cleaning and normalizing your data before processing to avoid any issues.

For more information how to handle special characters in Snowflake, please refer to Handling Special Characters in Snowflake.

Table Names as Input#

All input and output table names must follow Snowflake’s naming convention.

Please refer to Identifier Requirements for more details.