A Snowflake development environment — Part 2 — SQL & Git

When building applications for databases a lot of SQL scripts are created. These can be executed in Snowflake but what is missing is an automation. Something that upgrades the database objects from an old version, that executes the scripts in the correct order — in short a CI/CD pipeline for Snowflake development.

That is the second area the AppContainer does help.

Dealing with upgrades

In the Editor each sql script can be activated — that is the top right button next to the save icon — and with that the script runs and each SQL command is executed. Either itself deals with upgrades of existing objects or the script engine allows conditions.

For example in Snowflake there is no option to say “execute the alter table add column” command only if such column does not exist yet. A conditional comment of // if column not exists ORDER_FACT.REPLENISH_FLAG runs the subsequent SQL commands only if the condition is true.

This deals with tables, views, procedures and similar database objects. Getting data into the large tables is no problem using Snowpipe. For the tons of tiny tables that need just a few rows, CSV files can be provided and get loaded/merged into the table at activation time as well.

Activate all

To activate all content, the Design Time Browser has the same activation option and does activate all files (based on file extensions) within a (root) directory. With that all tests are possible:

• Create a fresh system
• Upgrade the current version via scripts to the new development version
• Test an upgrade from any version to the new development version
• Deploy the same code multiple times, e.g. in dev, test and production

For the latter the script engine supports various ways to deal with schema aliases. Either no schema is provided in the scripts, the the given schema is used. A schema name ${production} is replaced with the actual schema for the current setup using a mapping file in the root directory. And there are more options to allow for maximum flexibility with the least amount of work.

Activation result with detailed feedback

Git Repo

The nice thing about the Design Time Browser is its git integration. The buttons pull get the latest changes from the configured git repository and the push updates the repo with the user’s version.

This makes working together in a team straight forward, also deploying a new version is just a git pull away.

Summary

This functionality is one part of the AppContainer enhancing the developer productivity by magnitudes.