This article is in BETA State.
In this article
Overview
Each of the Cloud Data Warehouses (CDW) that SnapLogic supports uses its own SQL Query Optimizer to assess and optimize the SQL queries generated by ELT Pipelines. This is primarily done to reduce the time and costs involved in executing these queries. But, because these optimizers consider optimizing only certain blocks in the query, but not the query as a whole (across these blocks of the query), they generate inefficient query plans and take longer times for execution and incur higher CDW costs. This is due to a generally conservative approach that these CDW optimizers tend to take—so as to not cause data integrity issues.
Consider the following example:
A Pipeline with Snaps—ELT Select → ELT Filter → ELT LIMIT—generates the following nested query without using the SPDO feature.
SELECT * FROM (SELECT * FROM ( SELECT * FROM (SELECT * FROM some_table) alias1 WHERE some_column > 1000) alias2 LIMIT 10) Alias3
As the CDW optimizers look at enhancing the query only at its various chunks but not the entire query, they MAY NOT generate the simple query that it actually is:
SELECT * FROM some_table WHERE some_column > 1000 LIMIT 10
To address this issue of inefficient query optimization, SnapLogic has introduced the Subquery Pushdown Optimization/Elimination (SPDO) feature in all ELT Snaps. Using this feature, the Snaps can read the query across blocks (from different Snaps) and rewrite it to be very close to the second query above (in this example) when the CDW optimizer reads it.
Benefits
SPDO entails the following benefits:
Cover for the shortcomings of the CDW optimizer and thus improve performance in execution for some cases.
Reduce the execution cost.
Reduce the derived table Subquery footprint.
Improve query readability and hence the ease of debugging failed queries.
Reduce the query size.
How to use this feature
There are two ways to use the SPDO feature in your Pipelines.
Using a Pipeline Parameter at a Pipeline level
Using Pipeline Parameters at a Snap level
Using a Pipeline Parameter at a Pipeline level
To enable the SPDO feature, define the Pipeline Parameter ELT_DISABLE_SUBQUERY_PUSHDOWN with the value False, false, or FALSE.
In case you do not want to use the SPDO feature, set the value for the same Pipeline Parameter to True or true or TRUE.
Using Pipeline Parameters at a Snap level
In addition to the above Pipeline Parameter at the Pipeline Level, SnapLogic allows you to choose using SPDO at a Snap level for certain Snaps. Each Snap has a unique Pipeline Parameter that you can use to toggle the SPDO feature anytime before running the Pipeline. See the table below for this list.
Snap | Parameter to toggle SPDO | To enable SPDO | To Disable SPDO |
|---|---|---|---|
ELT Aggregate | ELT_DISABLE_AGGREGATE_SUBQUERY_PUSHDOWN | False | True |
ELT Filter | ELT_DISABLE_FILTER_SUBQUERY_PUSHDOWN | False | True |
ELT Limit | ELT_DISABLE_LIMIT_SUBQUERY_PUSHDOWN | False | True |
ELT Pivot | ELT_DISABLE_PIVOT_SUBQUERY_PUSHDOWN | False | True |
ELT Sample | ELT_DISABLE_SAMPLE_SUBQUERY_PUSHDOWN | False | True |
ELT Sort | ELT_DISABLE_SORT_SUBQUERY_PUSHDOWN | False | True |
ELT Transform | ELT_DISABLE_TRANSFORM_SUBQUERY_PUSHDOWN | False | True |
ELT Unique | ELT_DISABLE_UNIQUE_SUBQUERY_PUSHDOWN | False | True |
ELT Unpivot | ELT_DISABLE_UNPIVOT_SUBQUERY_PUSHDOWN | False | True |
ELT Window Functions | ELT_DISABLE_WINDOW_FUNCTIONS_SUBQUERY_PUSHDOWN | False | True |
For more information on which upstream Snaps can pushdown subqueries into a given Snap, refer to the respective Snap document.