Dataform CI CD

[valentiniacov]

Hello Community,

I have the following question:
In a GCP Dataform workspace lets say you have an .sqlx "operations" file

config {

    type: "operations",

    tags: ["someTag"]

}

You have the following:

SELECT a,b,c d from table

This sql code is incorrect, we got the following syntax error in GCP Dataform workspace

Syntax error: Expected end of input but got identifier "d"

In GitHub action I need to create the CI/CD data pipeline that is triggered when the code

is pushed to the remote branch.

Both github actions: dataform compile and dataform run --dry-run do not detect the syntax

errors. Please see the following issues:

https://github.com/dataform-co/dataform/issues/1530
https://github.com/dataform-co/dataform/issues/1532

Is there any task on GitHub actions that does detect the syntax errors?
I am not referring here about dataform run which it does, but it also executes all the

workflows which is something I do not want.

Thank you.

[ms4446 · Accepted solution]

The built-in dataform compile and dataform run --dry-run commands in Dataform are excellent for validating your project's overall structure, but they don't inherently catch SQL syntax errors. To proactively find these errors, consider these strategies:

1. External Linting / SQL Validation

• Integrate a dedicated SQL linter into your CI/CD pipeline. These tools are specifically designed to parse SQL code and report syntax errors.
• Choose a linter that supports the BigQuery SQL dialect to ensure compatibility with Dataform.

GitHub Actions Integration:

- name: SQL Linting
  uses: actions/setup-node@v2
  with:
    node-version: '14'  # Use a supported Node version
  run: | 
    npm install -g sqlfluff 
    sqlfluff lint --dialect bigquery my_dataform_project/definitions/ 

2. Pre-Commit Hooks

• Prevent the introduction of syntax errors by using a pre-commit hook framework like 'pre-commit'.
• Configure the hook to execute your SQL linter on modified .sqlx files before a developer can commit their changes.

3. Custom Validation Script

• Develop a script that uses the BigQuery API to execute dry-runs of your SQL queries.
• This offers accurate validation, but it does require some development effort.

Important Considerations:

• Query Extraction: Carefully parse and extract SQL from .sqlx files.
• API Quotas: Manage rate limits for BigQuery API calls.
• GitHub Actions Integration: Clearly output errors in your CI/CD logs.