Designing an SQL Pipeline

Overview

SQL pipelines empower skilled data engineers to write free-form SQL for creating complex data pipelines. Data engineers can take advantage of this feature as an alternative to the no-code visual pipeline, enabling them to write custom SQL queries for data transformation and analysis.

Procedure

Following are the steps to design a pipeline:

Step 1: In the Pipelines page, click the pipeline. The Overview page is displayed with the pipeline version details and a blank design space.

Step 2: Select the required SQL pipeline version and click Open Editor or click on the blank design space to open the editor page.

Step 3: Once the page opens, you can provide the SQL queries.

NOTE

• Tables present in the Domain's scope can be used as targets in the SQL query.
• When you provide the SQL query, the Auto-suggestion dropdown appears.
• The query validation might fail for complex SQL queries. If you want to skip the validation for such scenarios, you can either set the advanced configuration dt_skip_sql_validation to true for a specific pipeline or set it at the domain level.

Limitations

• The DDL Query support is limited. Hence, only CREATE command is supported as of now.
• Incremental load on the target is not supported.
• Switching between Visual and SQL Editor pipelines is not supported.
• Use of DDL statements in a transaction is not supported.
• Column lineage is not maintained.