Introducing Bigabid Alta: neural-network retargeting that beats ROAS targets.

Blog

Avoiding SQL Dumb Mistakes

Avoiding SQL dumb mistakes
Avoiding SQL dumb mistakes

Practical CI test to avoid SQL syntax errors

Every developer encounters a situation where he/she pushes code to production, and at a critical moment, that code breaks everything. This can happen to people from all seniority levels and can be caused by silly or sophisticated reasons.

In this blog, I’ll teach you how to avoid dumb SQL syntax errors automatically. We will check the validity of each of our queries before we merge the code into production. The code for this article can be found on GitHub.

My Name is Kobi Balilty, I’m a Senior BI Data Engineer. Bigabid is a company of elite Programmatic User Acquisition and Retargeting DSP for Mobile Apps. Two of the greatest challenges in BI are the data’s scale and the data’s complexity.

Problem Definition

During software development, there are many validations that we want to go through before they “hit” the customer. We can automate these processes and block “bad” behaviors, through a process called the CI/CD. This result in a more stable product, increase productivity, provide faster release cycles and allow a better experience for our customers.

“CI/CD bridges the gaps between development and operation activities and teams by enforcing automation in the building, testing, and deployment of applications”

In addition, developers hate spending their time on checks and always will prefer automated processes that do it for them. More than that, the process maintains the energy and morale of the developers and allows them to spend less time troubleshooting and more time building quality products.
It’s pretty common to see ETL failing due to silly SQL query syntax. This can lead to downstream failure and incorrect data in dashboards. For example, one of our developers pushed a critical change of the query and unfortunately, he/she was not concentrated enough and pushed a silly syntax error with an extra comma, which leads to an error in the ETL and downtime of several minutes to all of our dashboards.

In the next section, we are going to show how to eliminate those.

How can we solve those issues?

We want to make sure syntax issues are detected in our CI/CD. When prepending a query with Explain clause it will return the execution plan. In case the query is broken it will result in an error.
So to validate all queries are syntax is ok we need to do some pre-steps first in our purest:

  • Connect to the Database — any database you’ll use, we need first to open a connection.
  • Get a list of queries in the project — to process each query we need to get a list of queries that we want to test in all subfolders
  • Add Explain — we need to add an “explain” clause at the beginning of each query, this is where the magic happens.
  • Run query — finally run the chosen query on our database
def test_queries_synatx():
    engine = create_engine('<connection string>')
    with engine.connect() as con:
        queries_list = glob.glob('**/*.sql',recursive=True)
        for query_file in queries_list:
            with open(query_file) as f:
                query = f.read()
                test_query = f"Explain {query}"
                con.execute(test_query)

Since in Bigabid we are heavy users of GitHub actions, we triggered our CI/CD pipeline from there (including test_queries_synatx). In GitHub action, we block merge/push into the master branch in case of any invalid queries.

name: CI tests
on:
  push:
    branches:
      - master
  pull_request:
    branches:
      - master
jobs:
  unit-testing:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repo content
      uses: actions/checkout@v2 # checkout the repository content to github runner
      - name: Set up Python 3.8
      uses: actions/setup-python@v2
      with:
            python-version: 3.8
      - name: Install Dependencies
        run: |
          python -m pip install -r deploy/ci_requirements.txt
      - name: Test with pytest
        run: |
          pytest .

Pros and Cons

This technique that I just described has some prominent pros:

  • Easy to Implement.
  • Supported in most databases (require the explain clause only).
  • Doesn’t requires the execution of the query. For all databases, it means, it’s quite fast in addition for some databases (Athena for example) it can cost-effective.
  • Checks the schema of the tables as well, as opposed to other (awesome) tools like sqlfluff.

Like everything interesting in life, it does have some limitations:

  • Require Explain clause — you can add this only in supported databases like Redshift, Snowflake, Presto, Firebolt, etc.
  • Require connectivity to the database– you need an internet connection, and you need to ingest a connection string.

In my opinion, those limitations pale in comparison to the value that we got and keep getting.

Last words

In this article, we began by explaining why it’s important to have a CI/CD on our queries. It helps both beginners and senior software engineers to avoid silly SQL syntax errors and increase the ETLs availability.

I hope I was able to share my enthusiasm for this fascinating topic and that you find it useful. You’re more than welcome to drop me a line via email or LinkedIn.

Thanks to Eyal Trabelsi, for reviewing this post and making it much clearer.

Results based advertising starts here.

Contact us

Please fill out the form below to submit your interest.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.