SnowDDL - Possibility to short circuit the run of snowddl if no changes to the files/database?

[AndreiTapai]

Simple question. Is it possible to end the run early based on checksums or another method?

EDIT: This question was attempting to get at why SnowDDL ran some models over and over despite seemingly no changes to the config files. There were minute formatting issues, empty string comments, and default values that caused this.

[littleK0i]

Need a bit more info on specific use case.

Not sure if partial runs make sense in production environment, since objects heavily depend on each other. And object might be changed accidentally sometimes.

Everything must be checked every time in order to guarantee desired config to match actual Snowflake account state.

[AndreiTapai]

I was able to fix things in the following way:

1. For empty string comments, they were being stored as NULL when I inspected the table using DESC TABLE. I am avoiding empty strings for now using placeholder values
2. For the defaults the values I had were current_user and current_timestamp. I changed these to CURRENT_USER() and CURRENT_TIMESTAMP() to match the values in the table description. This seemed to do the trick.
3. Haven't been able to get the YAML !includes to work. It still runs a create or replace view. If I change the YAML syntax to something like:

text: |-
  with my_view as (
...

then it stops re-running the view code.

This is less than ideal as I would like to manage the SQL in separate files if possible. Any idea what the problem might be with the the include tag?

[littleK0i]

You may add CLI option --log-level=DEBUG. It will display additional information during logging and tell the specific reason why VIEW is being replaced.

!includes has a very simple implementation: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/_yaml.py#L14-L30

It finds a file, reads it as text and returns content. No transformations.

I suspect you may have some trailing spaces in VIEW text which are removed by YAML parser, but are not removed by direct text reading.

You may try to run SHOW VIEWS yourself and compare view text in Snowflake with view text in config using diff. You may also attach an example here, so we can take a look together.

[AndreiTapai]

Found what was causing it. My SQL files all have a new line at the end. The view code in Snowflake does not. Seems like something that should be stripped and/or ignored by the parser since a new line at the end of a file is pretty standard. When I removed the new lines suddenly no more DDL was being executed.

[littleK0i]

I did some tests and now I see what is going on.

Apparently, Snowflake Python driver has a line of code to strip spaces, tabs and newlines from the beginning and the end of each statement. It does not happen in Web interface.

SnowDDL did not normalise anything, but driver did.

I think it me reasonable to add some normalisation to SnowDDL itself. Here is the patch: b7a090c

It will go live with next major update.

Thank you for pointing this out.

[littleK0i]

(Uploaded fix as version 0.42.0)

[AndreiTapai]

Thank you!