Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
These new macros provide two major benefits:
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:
The possibilities are endless since you can fully customize the SQL within each macro!
Let’s walk through some example patterns that leverage the new macros:
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}}
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}}
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}}
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.
For more information, please refer to the Qlik Cloud Data Integration documentation. We can’t wait to see all the innovative ways our users will leverage macros to optimize their incremental data pipelines!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.