Using context variables for dynamically generated SQL queries

It is a very common practice to do string concatenations while forming SQL queries programmatically. Talend Data Integration Jobs allow such string concatenations in SQL related components like tDBRow and custom code components like tJavaRow. Depending on the component where the concatenations is done to form a SQL query, this could create difficulties for tracing lineage. The solution is to use context variables for the dynamic part of the SQL query within SQL components instead of custom code.

Take the example of the following simple Job with only a tDBInput component using a context variable defined for table name.

The data lineage works well without issues.

The second sample Job contains a tJavaRow where the SQL query is generated with concatenation. The same context variable is used for the table name as the first Job.

With tDBRow having the output row sql, this is defined for a query.

Although the data flow depicts link throughout, the data lineage breaks.