Using Workflow SQL Steps

SQL Steps allow you to execute SQL scripts directly on a tenant’s data warehouse, making it a powerful tool for data preparation and processing.
Whether your SQL scripts are stored in Peak Platform’s SQL Explorer or in a version control system (VCS) repository, SQL Steps provide the flexibility to execute them automatically as part of your workflow.

Before diving into examples, let’s explore how to configure an SQL Step and its parameters.

SQL Step

To create an SQL Step, you need to define the following parameters:

To use SQL scripts stored in SQL Explorer:

sqlQueryPath: The path to the SQL script to be executed that is stored in SQL Explorer. For example, scripts/query.sql.

To use SQL scripts stored in a VCS repository:

repository: The repository details where the SQL script is stored.
- url: The URL of the repository where the SQL script is stored.
- branch: The branch of the repository from which the script should be pulled.
- token (optional): If access to the repository requires a token, specify it here. It is the name of the external credential containing the token.
- filePath: The path to the SQL script file within the repository.

SQL Parameters

SQL parameters can be dynamically injected into the SQL script at runtime to customize its behavior. These parameters are defined as follows:

parameters: Parameters can be passed to the SQL script in two ways:
- env: Plain text environment variables. It is an object with key-value pairs that can be used in the SQL script where the key is the parameter name and the value is the parameter value.
- inherit: It is an object with key-value pairs that can be used in the SQL script where the key is the parameter name and the value is the name of the output parameter from the parent steps.

Using SQL Parameters in Queries

To utilize SQL parameters within your SQL script, reference them using the {{ keyName }} syntax. This enables you to pass values dynamically when the workflow runs.

Example: Suppose you have the following SQL script:

INSERT INTO {{ schema }}.{{ tableName }}
(name)
VALUES
('{{ name }}');

In your SQL Step, you might configure the following parameters:

schema: stage
tableName: student
name: Sachin Tendulkar

When the workflow is executed, the placeholders in the SQL script are replaced with their corresponding values, resulting in the following query being run:

INSERT INTO stage.student
(name)
VALUES
('Sachin Tendulkar');

Important Considerations

Escaping data: SQL Steps do not escape data. Ensure that your SQL scripts are secure and do not expose your data to SQL injection vulnerabilities.
SQL Explorer Limitations: SQL Explorer does not support parameterized queries. If you attempt to run a parameterized query in SQL Explorer, it may fail.
Missing Parameters: If you do not pass in a parameter defined in the SQL script, no replacement will occur, and the query will be executed as is.

Now that you understand how to configure SQL Steps and use SQL parameters, let’s explore some examples to see them in action.

Examples

Execute SQL Script from SQL Explorer

body:
    name: sql-explorer-workflow
    triggers: []
    watchers: []
    tags: []
    steps:
        step-1:
            type: sql
            sqlQueryPath: scripts/query.sql # The path to the SQL script stored in SQL Explorer
            parameters:
                env:
                    schema: stage
                    tableName: student
                    name: Sachin Tendulkar

Execute SQL Script from VCS Repository

body:
    name: vcs-repository-workflow
    triggers: []
    watchers: []
    tags: []
    steps:
        step-1:
            type: sql
            repository:
                url: https://github.com/org/repo
                branch: main
                token: github-token
                filePath: scripts/query.sql
            parameters:
                env:
                    schema: stage
                    tableName: student
                    name: Sachin Tendulkar

Configuring SQL Steps in Press Spec

The following example demonstrates how to configure an SQL Step in a Block Spec. The similar configuration can be used for creating a spec release as well.

# block_spec.yaml

body:
    version: 1
    kind: workflow
    metadata:
        name: workflow-block
        title: Workflow Block
        summary: Workflow Block
        description: Creating a new workflow block spec
        descriptionContentType: text/markdown
        imageUrl: https://my-block-pics.com/image-0.jpg
        tags:
            - name: CLI
    release:
        version: 1.0.0
        notes: This is the original release
    config:
        steps:
            step-1:
                type: sql
                repository:
                    url: https://github.com/org/repo
                    branch: main
                    token: github-token
                    filePath: scripts/query.sql
                parameters:
                    env:
                        schema: stage
                        tableName: student
                        name: Sachin Tendulkar
featured: true
autoRunOnDeploy: true