Option to change order of execution for scripts

[cfritzlar]

In our development process we currently face the issue from time to time that in a versioned script (V) we are referencing a view or stored procedure that is introduced or updated in the same change via a repeatable script.
But since versioned scripts always run before repeatable scripts we need to include the new view / stored procedure definition also in the versioned script. This should be avoided since it's error prone and contradicts the DRY principle.
I'm proposing to make this configurable in schemachange by i.e. adding a certain string in the V script name like "post_deploy__"
that enables a different order of execution for that specific script.

[FelipeLema]

I'd like to run a repeatable script before the version scripts to do USE SCHEMA {{ SCHEMA }}

[sfc-gh-tmathew]

Hello @cfritzlar, @FelipeLema

Thank you for raising the issue. Could you check out Issue #110 and see if you can leverage the -f and -m options to control the order of execution of the scripts using jinja templating.

Please let us know if this is helpful.

[FelipeLema]

thx for the tip, @sfc-gh-tmathew , although it is not something I could use.

Let me explain

the use-case for me would be to have a single command say alias company-dev-run-migrations=... that runs a command like schemachange --pre-run=some/file/pre.sql

this way, when running make run-migrations I'll know that before running any script, the script some/file/pre.sql will be run as a setup for each of the actual migration scripts.

since I'm using a one-liner to set the schema, using -f or -m would not change much my situation ("having this one-liner at the top of every script is a must")

alternatively, I could use something like --snowflake-schema=SOME_SCHEMA added as a flag (like --snowflake-account and --snowflake-user

[sfc-gh-tmathew]

Hello @FelipeLema

Let me see if I understood your requirement

if your file modules/pre.sql has the following code

SET START_TS=CURRENT_TIMESTAMP();
USE ROLE MY_DEPLOYMENT_ROLE;

Say your root folder -f deploy and -m modules that contains the scripts are

(project_root)
|
|-- deploy
    |-- V1.1.1__first_change.sql
    |-- V1.1.2__second_change.sql
|-- modules
    |-- pre.sql

Can you not have the following in the first line of each sql file to ensure it is executed at the beginning of each file?

Contents of V1.1.1___first_change.sql

{% include modules/pre.sql %}

SELECT $START_TS, CURRENT_ROLE();

OR is your requirement that you need the pre.sql to run once before looping through each SQL files in the -f location?

Could you provide an example of the kind of statements you will place in the pre.sql call ?

Also, are looking for a feature like the one in dbt docs.

If nothing I attempted above is what you intended, could you review #164 to see how far is it aligned with your use case as well ? Appreciate your thoughts and feedback.

[FelipeLema]

OR is your requirement that you need the pre.sql to run once before looping through each SQL files in the -f location?

this is my requirement. #164 sounds very much to what I am requesting

[CaptCorpMURICA]

The development for this functionality is already complete and is available for review with #173. This additional feature allows the user to specify if certain files should be executed at the beginning of every deployment. Additionally, this function can be turned off at the command line, so deployment scripts can be turned on for lower environments, but turned off for production (e.g. cloning operations).

[podung]

I'd like to run a repeatable script before the version scripts to do USE SCHEMA {{ SCHEMA }}

@FelipeLema I'm a bit late to this party, but I have a PR ready to enable supplying schema as part of the configuration: #210 . Assuming this gets accepted, you'll be able to accomplish this by simply supplying schema the same way you supply DB and warehouse.