ELT Snap Pack

Overview

SnapLogic’s ELT Snap Pack is a full pushdown optimization (FPDO) solution that extracts, loads, and transforms (ELT) data in the target database by building ELT Pipelines that parse SQL queries. As such, the ELT Pipeline generates and executes SQL on the target database. ELT Snaps help you to make data available to the enterprise in quick time; eliminating delays in transforming data for your specific purpose.

  • Extract data from the source system (SaaS application)
  • Load it into your Data Warehouse in its raw format
  • Transform the data using the power of the cloud

Automating Data Integration with ELT


Automating Data Integration with ELT

Features

  • Ease of Use
    • SQL passed between Snaps; execution on CDW (cloud data warehouse)
    • Visually build SQL Pipelines
    • Selectively Preview Data
    • Suggest capability
    • Extensive transformation capabilities
    • Load data using merge into
  • Enhanced Connectivity
    • Independent Snap Pack
    • Accounts specific to each target database
  • Security
    • Prevent SQL Injection
  • Write transformed data to target databases hosted in the following cloud locations
    • AWS (For Snowflake, Redshift, and Databricks Lakehouse Platform (DLP))
    • Microsoft Azure (For Snowflake, Azure Synapse, and Databricks Lakehouse Platform (DLP))
    • Google Cloud Platform (For Snowflake and BigQuery)
Important: You can also execute an ELT Pipeline from a standard-mode Pipeline by utilizing the Pipeline Execute Snap.

ELT Source-Target-Hosting Support Matrix

(Source) Data Storage Cloud Data Warehouse (Target) (Target) Cloud Host
AWS Simple Storage Service (S3) Amazon Redshift Microsoft Azure Azure External Location Google Cloud Storage Amazon AWS Microsoft Azure Google Cloud Platform (GCP)
Azure Blob Storage ADLS Gen2
✔ ✖ ✔ ✖ ✖ ✔ Snowflake ✔ ✔ ✔
✔ ✖ ✖ ✖ ✖ ✖ Amazon Redshift ✔ ✖ ✖
✖ ✖ ✖ ✔* ✔* ✖ Azure Synapse ✖ ✔ ✖
✔ ✖ ✖ ✔ ✔ ✖ Databricks Lakehouse Platform (DLP) ✔ ✔ ✖
✔ ✔ ✖ ✖ ✖ ✔ Google BigQuery ✖ ✖ ✔

* ELT Snap Pack supports Azure Blob Storage and ADLS Gen2 as the external (source) locations for loading and transforming data into Azure Synapse.

See Configuring ELT Database Accounts for more details.

Supported JDBC JAR Versions

You can configure your Configuring ELT Database Accounts to automatically use an appropriate JDBC JAR file for connecting to your target CDW and performing the load and transform operations.

Supported CDW Certified JDBC JAR file
Azure Synapse mssql-jdbc-11.2.1.jre11.jar
BigQuery SimbaJDBCDriverforGoogleBigQuery42_1.3.0.1001.zip
Databricks Lakehouse Platform (DLP) databricks-jdbc-2.6.29.jar
Redshift redshift-jdbc42-2.1.0.9.jar
Snowflake snowflake-jdbc-3.13.24.jar
Important:

Using Alternate JDBC JAR File Versions

We recommend you to let the ELT Snaps use the listed JAR file versions. However, you may use a different JAR file version of your choice.

Known Issues

  • In the ELT Merge Into Snap, the Records Updated parameter in the Snap Statistics tab (on Pipeline execution) displays the combined count of records that were inserted, updated, and deleted for target CDWs: Azure Synapse, Amazon Redshift, and DLP. However, in Snowflake target instances this issue is fixed in the patch release 431patches19240 to display the individual record counts.
  • When your Databricks Lakehouse Platform instance uses Databricks Runtime Version 8.4 or lower, ELT operations involving large amounts of data might fail due to the smaller memory capacity of 536870912 bytes (512MB) allocated by default. This issue does not occur if you are using Databricks Runtime Version 9.0.
  • In case of Databricks Lakehouse Platform (DLP), when you specify a CSV file to load data from and select the Load Action as Alter Table in the ELT Load Snap, the Snap fails with the error Database encountered an error during Bulk Load process.
  • When you configure an ELT Merge Into Snap to perform an Update or Delete operation or an ELT Execute Snap with a MERGE INTO statement that performs the Update or Delete operation on a Databricks Lakehouse Platform cluster, it may return an error if multiple source rows attempt to update or delete the same target row. To prevent such errors, you need to preprocess the source table to have only unique rows.
  • While using the SQL Expressions and Functions Supported for ELT to define the field values in ELT Snaps and accounts:
    • DO NOT use regular JavaScript expressions (supported in the Standard-mode Snaps and not in the above list).
    • DO NOT prefix variable names with $ symbol.
  • The Snaps - ELT Load, ELT Merge Into, ELT Insert-Select and ELT Select, display the Schema Name field suggestions from all databases that the Snap account user can access, instead of the database selected in the Snap account or the Snap Settings.