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.

NOTE The SQL Pipeline feature is present only for Snowflake and BigQuery environments.

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.