[Query format] Can we define query format rules?

[josipsha]

Hello,

in the Big Query UI under the More button, we have a Format query option (look at the image). The problem is that the current rules do not follow the formatting rules that we defined at our team level.

My question is, would it be possible to define rules that would be applied while formatting?

Additionally, is there a way to do this outside of GCP UI?
I would like to have some sort of linter in my CI/CD pipeline.

[ms4446 · Accepted solution]

Unfortunately, BigQuery's built-in formatter does not support the direct customization of formatting rules to align with your team's specific style guide. However, there are effective workarounds and approaches to achieve consistent formatting:

• Manual Consistency: Establish a comprehensive style guide for your BigQuery SQL queries. While team members will need to manually adhere to this guide, the built-in formatter can be used for basic formatting adjustments. Regular code reviews play a crucial role in enforcing these guidelines, ensuring adherence before code merges.

• External Formatters: For more customizable formatting options, several external SQL linters and formatters are available. These tools can be integrated not only into CI/CD pipelines but also into local development environments for a more seamless workflow.

Integrating a SQL linter or formatter within your CI/CD pipeline can significantly enhance code quality. Consider the following popular tools:

• SQLFluff: This is a highly flexible SQL linter and formatter that supports custom rules and integrates seamlessly with CI/CD workflows. SQLFluff is compatible with BigQuery's SQL dialect, among others, making it a versatile choice for projects. For more information, visit SQLFluff Documentation.

• dbt (Data Build Tool): For teams using dbt for data transformations, dbt offers built-in SQL formatting capabilities. While customization options may be more limited compared to SQLFluff, dbt can still help maintain consistent code style. Learn more at dbt Best Practices.

[scott-cronson]

Hi @ms4446 - I appreciate your thorough response on this question (and frankly all of the BQ/SQL/Dataform questions in this forum!)

Do you know if there is a plan to introduce direct customization of formatting rules in the future?