Get More Control with New Macros in Qlik™ Cloud Data Integration

27 October 2023
No Comments

Qlik™ Cloud Data Integration (QCDI) provides powerful capabilities for building automated and scalable data pipelines. One of the key benefits is incremental processing, which only processes changed data rather than the full data set on each run. This improves efficiency, especially for large datasets.

While core QCDI for landing, storing, or performing basic transformations are automatically incremental, custom SQL queries fully reprocess all data by default. With complex logic, this repetitive processing could become inefficient.

That’s why Qlik™ is excited to announce new Macro capabilities that give you more control over incremental processing in custom SQL pipelines. Now you can easily specify different logic to run during initial loads vs incremental updates.

Introducing Initial and Incremental Load Macros

The main enhancement is the addition of two macros – one for initial loads (Q_RUN_INITIAL LOAD) and the second for incremental loads (Q_RUN_INCREMENTAL). These macros allow you to customize what logic executes during initial loads when tables are first built, vs incremental runs that process new or changed data.

To use them, simply wrap any logic unique to initial or incremental loads in the corresponding macro tags. For example:

SELECT * FROM orders

{{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}}

WHERE update_tx > DATEADD(day, -1,GETDATE())

{{/QLIK_FUNC_IF}}

This query would select all order records during initial loads, but only newer records from the past day for incremental updates. The macro tags tell QCDI to only apply that filter logic when running incrementally.

Benefits of Load-Specific Macros

These new macros provide two major benefits:

  1. Improved efficiency by limiting incremental data volumes
  2. More flexibility to tailor logic independently

With macros, you can significantly reduce the data processed incrementally by filtering or sampling datasets. QCDI still performs change detection on the subset but avoids reprocessing your full history each run.

You also gain more flexibility to customize SQL logic independently for initial and incremental loads. For example, you may want to:

  1. Add filters or joins only for incremental loads
  2. Map columns or calculate values differently
  3. Union data sets only during initial loads
  4. Use different table variants like change history

 The possibilities are endless since you can fully customize the SQL within each macro!

Incremental Load Patterns and Examples

Let’s walk through some example patterns that leverage the new macros:

  1. Limit to recent changes only

Filter to only new or recently modified records, reducing incremental data volumes.

SELECT * FROM orders

{{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}}

WHERE update_tx > DATEADD(day, -1, GETDATE())

{{/QLIK_FUNC_IF}}

  1. Use different tables

Join the history table for initial loads, and for incremental loads leverage the current records using from/ to time stamps.

{{#QLIK_FUNC_IF Q_RUN_INITIAL_LOAD}}

SELECT oh.*, ch.* FROM orders_history oh

JOIN customers_history ch ON oh.CUST_ID = ch.CUST_ID

{{/QLIK_FUNC_IF}}

{{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}}

SELECT o.*, c.* FROM orders o

JOIN customers c ON o.CUST_ID = c.CUST_ID

WHERE o.OrderTimestamp >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AND o.OrderTimestamp < CAST(GETDATE() AS DATE);

{{/QLIK_FUNC_IF}}

  1. Add derived columns

Calculate or set values differently on initial load versus incremental loads

SELECT od.ODID, o.ORDERID, od.PRODUCTID, od.QUANTITY, o.ORDERDATE,

{{#QLIK_FUNC_IF Q_RUN_INITIAL_LOAD}} ‘INIT’ as LOADED_BY  

{{/QLIK_FUNC_IF}}

{{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}} ‘INCR’ as LOADED_BY {{/QLIK_FUNC_IF}}

FROM ${ORDERS} o 

JOIN ${ORDER_DETAILS} od  ON o.ORDERID= od.ORDERID

{{#QLIK_FUNC_IF Q_RUN_INCREMENTAL}}

WHERE o.ORDERDATE > DATEADD(hour, -12, CURRENT_TIMESTAMP())

{{/QLIK_FUNC_IF}}

Get Started with Macros Today!

The new macros enable you to greatly improve incremental processing efficiency and flexibility in QCDI. You can start applying them to your custom SQL tasks today using the latest QCDI release.

Check out a self-guided tour of Qlik™ Cloud Data Integration here. 

For information about Qlik™, please visit this site: qlik.com.
For specific and specialized solutions from QQinfo, please visit this page: QQsolutions.
In order to be in touch with the latest news in the field, unique solutions explained, but also with our personal perspectives regarding the world of management, data and analytics, we recommend the QQblog !