Overview

You must create ELT accounts to connect to data sources that you want to use in your Pipelines.

The Snap Pack does not support mixed accounts from different database-types in the same Pipeline. For example, a Pipeline in which some Snaps are connecting to the Snowflake database cannot have other Snaps connecting to the Redshift database.

Known Issue

When ELT and Spark SQL 2.x Snap account credentials—such as user names, passwords, client secrets, auth codes and tokens, secret keys, and keystores—are auto-filled using the Google Chrome browser, the accounts and hence the Pipelines fail. This is because the browser overwrites the field values with its own encrypted values that the SnapLogic Platform cannot read. SnapLogic recommends that you do not auto-save your Snap account credentials in the Chrome browser.
Ensure that you delete any credentials that the browser has already saved for elastic.snaplogic.com, and then perform ONE of the following actions:
- Option 1: Click that appears in the address bar after you submit your login credentials at elastic.snaplogic.com, and then click Never.
- Option 2: Disable the Offer to save Passwords option at chrome://settings/passwords while working with your SnapLogic Pipelines. If you disable this option, your Chrome browser will not remember your passwords on any other website.

Snap-Account Compatibility

Snap	ELT Database Account
ELT Aggregate Account configuration required	✔
ELT Copy	✖
ELT Execute	✔
ELT Filter	✔
ELT Insert-Select Account configuration required	✔
ELT Intersect	✔
ELT Join	✔
ELT Limit	✔
ELT Load Account configuration required	✔
ELT Merge Into Account configuration required	✔
ELT Minus	✔
ELT Pivot	✔
ELT SCD2	✔
ELT Sample	✔
ELT Select	✔
ELT Sort	✔
ELT Transform	✔
ELT Union	✔
ELT Unique	✔
ELT Unpivot	✔
ELT Window Functions Account configuration required	✔

Configuring ELT Accounts

You can configure your ELT accounts in SnapLogic using either the Designer or Manager.

Using SnapLogic Designer

Drag any ELT Snap to the Canvas and click the Snap to open its settings. Click the Account tab. You can now either use an existing account or create a new one. Write-type snaps such as ELT Insert-Select and ELT Merge Into require accounts to function properly so the Account tab is automatically displayed when you place these Snaps on the Canvas.

Selecting an existing account

SnapLogic organizes and displays all accounts to which you have access, sorting them by account type and location. To select an existing account:

Click the icon to view the accounts to which you have access and select the account that you want to use.
Click to save the account selection.

Creating an account

Click Add Account in the Account Reference dialog.
Select the Location in which you want to create the account, select the account type, and click Continue. The Add Account dialog associated with the account type appears.
Enter the required account details. For detailed guidance on how to provide information associated with each account type, use the following links:
- ELT Database Account
Enter additional information on this account in the Notes field of the Info tab. Doing so helps you–and other users–understand the purpose of the account, especially if there are multiple accounts of the same type.
Click Validate to verify the account, if the account type supports validation.
Click Apply to complete configuring the ELT account.

Using SnapLogic Manager

Use Manager to create accounts without associating them immediately with Pipelines.

Accounts in SnapLogic are associated with projects. You can use accounts created in other projects only if you have at least Read access to them.

In the left pane, browse to the project in which you want to create the account and click Create > Account > ELT, followed by the appropriate account type.

The Create Account dialog associated with the selected account type appears.
Repeat the steps numbered 3 through 5 in the previous section.

Avoid updating account credentials while Pipelines using that account are executing. Doing so may lead to unexpected results, including locking your account.

Snap Pack History

Click here to expand...

Release	Snap Pack Version	Date	Type	Updates
4.26-Patch	426patches12021	30 Sep 2021	Latest	Fixed an issue where the ELT Load Snap connecting to a Databricks Lakehouse Platform (DLP) instance failed to perform the load operation. Ensure that you provide a valid DBFS Folder path in the Snap's account settings as the Snap requires this folder path.
4.26-Patch	426patches11646	22 Sep 2021	Latest	Enhanced the ELT Database Account to support token-based authentication (Source Location Session Credentials) to S3 locations for Snowflake and Redshift target databases. Enhanced the ELT Aggregate Snap with the following changes: Revised the field labels from: GROUP BY Fields List field set > Output Field to GROUP BY Field. ORDER-By Fields to ORDER-BY Fields (Aggregate Concatenation Functions Only). Removed the Suggestion option for Field Name field under General Aggregate Functions List field. Made the Alias Name fields in the Aggregate Concatenation Functions List and the Percentile Distribution Functions List field sets mandatory. If your target database is a Databricks Lakehouse Platform (DLP) instance, then the ELT Load Snap supports loading data from source CSV files that contain only comma as the separator between values.
4.26-Patch	426patches11323	17 Aug 2021	Latest	Enhanced the ELT Database Account to allow parameterization of field values using Pipeline Parameters. You can define and use these parameters in expression-enabled fields to pass values during runtime.
4.26-Patch	426patches11262	16 Aug 2021	Latest	Fixed the following Known Issues recorded in the 4.26 GA version: For a Snowflake target instance, the ELT Insert Select Snap does not suggest column names to select for the Insert Column field in the Insert Expression List. The Snaps—ELT Merge Into, ELT Select, ELT Join, and ELT Filter—do not prevent the risk of SQL injection when your target database is Databricks Lakehouse Platform (DLP). Intermittent null-pointer exceptions in the ELT Load Snap on Databricks Lakehouse Platform (DLP). The ELT Insert Select Snap attempts to create the target table even when it exists in the Snowflake database. When loading data from a JSON file into a target Databricks Lakehouse Platform (DLP) instance using an ELT Load Snap, if you choose the Drop and Create Table option as the Load Action and specify an additional column (that is not available in the JSON file) for the new table, it results in one more column null added to the new target table. When you use the SQL editor in the ELT Select Snap configuration to define your SQL query, the Pipeline validation fails due to a syntax error in the following scenarios. However, the Pipeline execution works as expected. The only workaround is to drop the LIMIT clause and the optional OFFSET clause from the SQL query during Pipeline validation. The query contains a LIMIT clause on a Snowflake, Redshift or Databricks Lakehouse Platform target instance: The SQL query created during Pipeline validation includes an additional LIMIT clause, for example: `SELECT * FROM "STORE_DATA"."ORDERS" LIMIT 10 LIMIT 990`. The query contains an OFFSET clause (supported in case of Snowflake and Redshift): The SQL query created during Pipeline validation looks like `SELECT * FROM "STORE_DATA"."ORDERS" LIMIT 10 OFFSET 4 LIMIT 990`.
4.26	main11181	14 Aug 2021	Stable	Enhanced the ELT Snap preview to support the following Snowflake data types: array, object, variant, and timestamp. The Snaps convert the values to hexadecimal (HEX) equivalents—the default setting for the session parameter BINARY_OUTPUT_FORMAT in Snowflake. See Session Parameters for Binary Values for more information. If this setting is different from hexadecimal (such as base64) in the Snowflake table, the Snaps still convert the values to hexadecimal equivalents for rendering them in the Snap preview. Enhanced all ELT Snaps to display the Get preview data checkbox below the Snap's Label field. The ELT Database account is now mandatory for all Snaps in the ELT Snap Pack. Breaking change Starting with the 4.26 release, all Snaps in the ELT Snap Pack (except the ELT Copy Snap) require an account to connect to the respective target database. Your existing Pipelines that do not use an account may fail. We recommend you to associate an ELT Database Account to each of the ELT Snaps (except ELT Copy Snap) for your Pipelines. Enhanced the ELT Aggregate Snap to support Linear Regression functions on Redshift and Azure Synapse. The Snap also supports these functions on Databricks Lakehouse Platform. Enhanced the ELT Execute Snap to enable running multiple DML, DDL, and DCL SQL statements from the same Snap instance. Enhanced the ELT Join Snap to: Support LEFT ANTI JOIN and LEFT SEMI JOIN types on all supported databases. Display or hide the Resultant Column Names Prefix Type field based on the target database selected in the Snap's account. Enhanced the ELT Load and ELT SCD2 Snaps to provide a list of suggested data types, while adding columns to or creating a table.
4.25-Patch	425patches10017	17 Jun 2021	Latest	Updated the ELT SCD2 Snap to replace End date of historical row option in the Meaning field of Target Table SCD2 Fields field set with End Date of Current Row. Breaking change This may cause the existing Pipelines to fail as the End date of historical row option no longer exists. You need to make the following update in the ELT SCD2 Snap's settings across your Pipelines after upgrading your Snap Pack to this patch: Select End Date of Current Row from the Meaning drop-down list in the second entry (highlighted in the image). Fixed the issue with the ELT Insert Select Snap containing an open output preview that fails to retrieve output preview data in case of Redshift and Azure Synapse databases, though the Pipeline runs work as expected. Fixed an issue where the ELT Execute Snap does not error out (Snap turns Green) even when running an SQL query to drop a non-existent table from a Snowflake or Azure Synapse database. [Update on 22 Jul 2021]: Enhanced the ELT Snap previews to support the following data types: array, object, variant, and timestamp. The Snaps convert the values to hexadecimal (HEX) equivalents—the default setting for the session parameter BINARY_OUTPUT_FORMAT in Snowflake. See Session Parameters for Binary Values for more information. If this setting is different from hexadecimal (such as base64) in the Snowflake table, the Snaps still convert the values to hexadecimal equivalents for rendering them in the Snap previews.
4.25-Patch	425patches9725	19 May 2021	Latest	Enhanced the ELT Snap preview to display the exact binary and varbinary values from Snowflake database during Pipeline validation, by converting the values to hexadecimal equivalents—the default setting in Snowflake. If the setting is different from hexadecimal in the Snowflake table, then the Snaps still convert the values to hexadecimal for rendering the Snap preview. Enhanced the ELT Transform Snap to display the appropriate data type (binary or varbinary) for the column names populated in the output schema. Enhanced the ELT Window Functions Snap to address potential issues due to an incorrect definition for MINUS function in case of Redshift and Azure Synapse databases.
4.25	main9554	08 May 2021	Stable	Starting with the 4.25 release, SnapLogic has now certified the ELT Snap Pack to work with Snowflake hosted on Google Cloud Platform (GCP) as the target database, in addition to the other flavors of Snowflake hosted on AWS and Microsoft Azure. Introduced the ELT Execute Snap to enable you to run DML, DDL, and DCL SQL queries in Snowflake in Snowflake, Redshift, and Azure Synapse. Introduced the ELT SCD2 Snap to support Type 2 Slowly Changing Dimensions (SCD2) updates to the target databases—Snowflake, Redshift, and Azure Synapse. Enhanced the ELT Database Account to introduce: Support for Google Cloud Storage as a storage location (source) in addition to AWS S3 and Azure Data Lake Storage (ADLS) when your target database is Snowflake. Automatic download of the JDBC driver required for the selected Database Type using the new Download JDBC Driver Automatically check box. Enhanced the ELT Load Snap to prevent changes to existing tables during Pipeline validation. If you set the Load Action as Drop and Create table, and the target table does not exist, the Snap creates a new (empty) target table based on the schema specified in its settings. Enhanced the ELT Window Functions Snap to support Covariance, Correlation, and Linear Regression Functions on Snowflake, Redshift, and Azure Synapse databases. The Snap uses function-specific query re-writes to support these functions on Redshift and Azure Synapse databases. Enhanced the ELT Merge Into and ELT Insert Select Snaps to support up to one output view, and added the Get Preview Data check box to these Snaps. You can now connect downstream ELT Snaps to these Snaps.
4.24-Patch	424patches8793	04 Mar 2021	Latest	Fixes the issue of production job failures due to ELT Insert Select Snap after upgrading to 4.24 GA by updating the ELT Transform Snap to continue allowing duplication of fields in the Expression list for the Pipeline to complete successfully. No changes are needed to your existing Pipelines. Fixes the column name collision issue in the Snap's output when the two tables being joined have columns with the same/identical names. You can specify the extent of prefix (that is, to prefix all columns, only duplicate columns, or no prefix) using the Resultant Column Names Prefix Type drop-down list. Based on the prefix you choose, a table alias name is prefixed to the identical columns in the output. Behavior Change The behavior of ELT Load Snap for Load Action during Pipeline validation across the supported databases is as follows: Append rows to existing table: Does not append the data from the source files into the target table. Overwrite existing table: Does not overwrite the data. Drop and Create table: Does not drop the target table even if it exists, but the Snap creates a new target table if a table does not exist. Alter table: Does not modify the schema of the target table.
4.24	main8556	14 Nov 2020	Stable	Adds support for Azure Synapse database. You can now use the ELT Snap Pack to transform tables in the Snowflake, Redshift as well as Azure Synapse databases. Updates the Snap Pack with the following features: ELT Database Account: Enhances the ELT Database Account to support the Azure Synapse database. ELT Aggregate: Enhances the Snap to: Support Azure Synapse's T-SQL aggregate functions and the aggregate functions in Snowflake and Redshift databases. General Aggregate Function COUNT_IF in Snowflake. General Aggregate Functions in Snowflake. Linear Regression Aggregate Functions in Snowflake. Aggregate Concatenation Functions in Snowflake, Redshift, and Azure Synapse. Percentile Distribution Functions in Snowflake and Redshift. Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. ELT Insert Select: Enhances the Snap to: Suggest appropriate column names to select from, in the Snap fields. Create Hash-distributed tables using the Target Table Hash Distribution Column (Azure Synapse Only) field when the Load Action is selected as Drop and Create table and a condition like WHEN NOT MATCHED BY TARGET. ELT Join: Enhances the Snap to support Natural JOINS (NATURAL INNER JOIN, NATURAL LEFT OUTER JOIN, NATURAL RIGHT OUTER JOIN, and NATURAL FULL OUTER JOIN) in addition to the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS Joins in Azure Synapse Database. This enhancement also makes account configuration mandatory when using this Snap. Fixes the column name collision issue in the Snap's result set when the two tables being joined have columns with the same/identical names. You can specify the Resultant Column Names Prefix Type drop-down list. Based on the prefix type you choose, a table alias name is prefixed to identical columns in the output. ELT Load: Enhances the Snap to: Support the File Name Pattern option using Key Based Mechanism for Redshift database. Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. Create Hash-distributed tables using the Target Table Hash Distribution Column (Azure Synapse Only) field when the Load Action is selected as Drop and Create table. ELT Merge Into: Enhances the Snap to: Suggest appropriate column names to select from, in the Snap fields. This applies to Snowflake, Redshift, and Azure Synapse databases. Include the Target Table Hash Distribution Column (Azure Synapse Only) field for the Snap to create hash-distributed tables always. Include the Update Expression List - When Not Matched By Source field set to allow defining one or more Update Expressions for the WHEN clause - WHEN NOT MATCHED BY SOURCE. This applies to Azure Synapse database. Include the Target Table Alias field to specify the alias name required for the target table. The Snap is also equipped with the ability to auto-replace the actual table names (with the alias name), if any, used in the ON clause condition, secondary AND conditions, Update Expression list, or Insert Expression list. This applies to Snowflake, Redshift, and Azure Synapse databases. ELT Transform: Enhances the Snap to: Display input schema and output schema based on the upstream and downstream Snaps connected to this Snap. Delete fields mentioned in the Expression field from the Snap's output when the mappings have an empty Target Path. ELT Window Functions: Enhances the Snap to support the following Window Functions in addition to the existing ones: Value Based Analytic Functions LEAD and LAG Analytic Functions Fixes the issue of displaying generic error messages for Triggered Task failures with ELT Pipelines by displaying detailed error messages for ease in debugging.
4.23	main7430	14 Nov 2020	Stable	Introduces the following Snaps: ELT Load: Loads data from AWS S3 buckets and Azure clusters into the Snowflake and Redshift tables. ELT Sample: Generates a data subset from the source table. ELT Pivot: Converts row data into column data. ELT Unpivot: Converts column data into row data. ELT Window Functions: Provides support for SQL Window Functions in ELT Pipelines.
4.22	main6403	12 Sep 2020	Stable	Introduces the ELT Snap Pack that provides you with the Extract, Load, and Transform (ELT) capabilities. Use the following Snaps to build SQL queries that are executed in the Snowflake database: ELT Aggregate : Builds SQL query to perform aggregate functions such as SUM, COUNT, MIN, and MAX. Also offers the GROUP BY functionality. ELT Copy: Creates copies of the input SQL query. ELT Filter: Adds a WHERE clause in the input SQL query. Use this capability to create filters/conditions for your data set. ELT Insert Select: Performs the INSERT INTO SELECT operation on the specified table. ELT Intersect: Adds an INTERSECT SQL operator in the input queries. ELT Join: Builds SQL query with a JOIN clause. ELT Limit: Adds a LIMIT clause in the incoming SQL query. ELT Merge Into: Performs the MERGE INTO operation on the specified table. ELT Minus: Adds a MINUS SQL operator in the input queries. ELT Select: Builds an SQL SELECT query and provides a built-in SQL query editor that enables you to construct complex queries. ELT Sort: Adds the ORDER BY keyword in the input query. ELT Transform: Builds transformation-based SQL queries for the specified table. ELT Union: Adds a UNION ALL or UNION DISTINCT operator in the input queries. ELT Unique: Builds a SELECT DISTINCT SQL query.

Configuring the ELT Snap Pack Accounts