Writing the VIEW and PROCEDURE bodies outside of the .yaml file

[psequeirag]

As we started to use SnowDDL, one of our teams complained that developing and maintaining VIEWS and STORED PROCEDURES in the .yaml files was cumbersome because they had to go back and forth between the Worksheet where they write and test the queries and statements, and then pasting and reformatting the final version in the .yaml file to be committed to the source code repository.

So, the idea came that maybe SnowDDL could support grabbing the text of the VIEW (and the body of the PROCEDURE), from a separate text file (say, named identically to the .yaml file of the view, but with the .sql extension). That way, it would no longer be necessary to re-format the text of the view query when copying it back and forth between the external file and the worksheets. SnowDDL would know to look for the external file if the text/body elements are missing in the .yaml.

What do you think? I was planning to start working on the implementation of this feature myself to see it in action, but wanted to check if you, too, think that this feature would be desirable and that the PR could be welcomed.

[littleK0i]

Yes, this feature would be very helpful. And it is relatively easy to implement.

However, the main questions from the design standpoint are:

1. Where SQL files should be placed?
Placing SQL files in the same directory as YAML files is a straightforward solution, but it may not look good during browsing. Sub-directory is better for browsing experience and logical separation of file types, but it makes existence of separate view text a bit less obvious.

2. Should we keep a placeholder in YAML?
It might be a good idea to keep placeholder in YAML files for clarity. Something like this:

text: ${{ file:my_view.sql }}

3. Should we resolve placeholders in SQL files?
I guess the answer is yes, since we may need to specify ${{ env_prefix }} for cross-database VIEW text. But now we have to think about circular dependencies or resolve only basic placeholders (no nested files).

4. Should it be extended for UDF-functions in Python, Java, etc.?
It might be nice to support other file types, not only SQL. Ideally, any file type should be supported for any programming language which Snowflake may want to add in future.

Let's think about the best approach a little.

[psequeirag]

Hi @littleK0i , thank you so much for replying with your thuoughts, I greatly appreciate that!

I didn't consider initially as many factors as you already did, but here are thoughts I have on them so far:

1. Where SQL files should be placed?

So far I was only thinking of putting the file at the exact same location as the original .yaml for the view, with the exact same file name, but with the .sql extension instead of .yaml. As you said, this appears to be straightforward and has the advantage that the two pieces of which the view is made are still kept close.

It didn't occur to me that the .sql file could be placed in a subdirectory, and personally I wasn't bothered by having a mix of file types in the same directory as Iong as they were logically related, but that is just the impression I have.

Anyway, here's an example of how things could look when browsing the project files in an IDE:

2. Should we keep a placeholder in YAML?

I was wondering the same; but then I realized I would have to introduce a placeholder format and implement support for it, so I thought of opting to let the text element to be completely omitted from the YAML and let that mean that its contents should be loaded from the external file instead (same base name as the YAML, but with a different extension); a kind of "convention over configuration" approach that tries to keep things simple.

On the other hand, admittedly, not being explicit has the downside of probably being harder to grasp by the casual reader that is new to SnowDDL (I remember the first time I stopped to look at the resulting YAML in the image above, I thought it was a bit odd); so I'm also fine to go for the explicit placeholder in the YAML if you think that'd be preferable.

3. Should we resolve placeholders in SQL files?

Absolutely! I'm thinking of this feature as a kind of "include" directive which just inserts the contents of the external file into the text or body element of the YAML: all the same semantics that apply to the element (expansion of placeholders, resolution of references relative to the context of the view, etc), would remain exactly the same.

In fact, looking at ViewParser.process_view() I was thinking about implementing this feature by checking for the external file immediately after the YAML was parsed, but before the blueprint was instantiated. Hence, whether the text was in the YAML or came from the external file, the blueprint it would be exactly the same. Something like this:

    def process_view(self, f: ParsedFile):
        # snip ...

        try:
            text_blueprint = f.params['text']
        except KeyError:
            sql_path = f.path.with_suffix('.sql')
            f_sql = ParsedSqlFile(self, sql_path)
            text_blueprint = f_sql.params['text']

        bp = ViewBlueprint(
            full_name=SchemaObjectIdent(self.env_prefix, f.database, f.schema, f.name),
            text=text_blueprint,
            columns=column_blueprints if column_blueprints else None,
            is_secure=f.params.get('is_secure', False),
            depends_on=[build_schema_object_ident(self.env_prefix, v, f.database, f.schema) for v in f.params.get('depends_on', [])],
            comment=f.params.get('comment'),
        )

        # snip ...

4. Should it be extended for UDF-functions in Python, Java, etc.?

Yeah! I agree that would follow naturally from the initial idea focused on views and procedures.

[psequeirag]

Hmm, I just realized I wasn't explicit on addressing your question of whether or not to allow nested files; my answer is that I do not think we'll have to.

Maybe this placeholder should not be a SnowDDL one, but instead a custom Yaml tag to that effect. Based on https://stackoverflow.com/a/9577670/2004714, that could look like this:

text: !include my_view.sql

[littleK0i]

@psequeirag , please check the latest update 0.16.0.

It should be possible to link external files via !include tag.

Example:

body: !include pr003_pr1(number).sql

It is also possible to use sub-directories:

body: !include python/pr003_pr1(number).py

Paths are relative to parent directory of original YAML file. Absolute paths and wildcards are not allowed.

Placeholders inside included files should be fully functional.

Please let me know if it works for you. Thank you.

[psequeirag]

Whoa, @littleK0i , this is awesome! Thank you so much!

I'll let the my team know that this is now available, I'm sure they'll be very happy.

[oz-chewie]

Another unstated benefit here is that the sql file can be run directly to check results, and that leads to integration tests!