Snowflake-Labs/schemachange

dependable objects - best practice?

Opened this issue · 0 comments

Hi all,
I'm implementing Schemachange for our use case, and wanted to deploy views as Repeatable scripts.
Given that this is a new project, and we are probably going to create dozens / hundreds of views in the upcoming months/years, and as they can be dependent on each other, what is the best practice to use since schemachange uses a simple alphabetical order for exectuions.

e.g.: if I have the following views:
R__1___view1.sql
R__2___view2.sql
R__3___view3.sql

this is all good until I need to make a change e.g.: to "view1" which makes it dependent on "view3" or a new view, e.g.: "view4"
At this point, to keep this runnable, we'd need to renumber all scripts as follows:

R__1___view3.sql
R__2___view4.sql
R__3___view1.sql
R__4___view2.sql

Which can become a problem when talking dozens or hundreds of views.

I was thinking about using a naming convention like the following:
R__5100_view1
R__5200_view2
R__5300_view3

This way I can simply add "view4" in one of the gaps and rename "view3" to use another slot of the gaps.
R__5040_view3
R__5050_view4
R__5100_view1
R__5200_view2

What is the best way of handling this?
I was thinking about a file to track dependencies, essentially putting all the scripts in the order of desired execution.
a bit of extra overhead but would still be a lot simpler then messing with the numbering / renaming exercise.

Best,
Gergely