CICD is a must have for professional software development. And for code it works very well as code can be merged and code is usually a single unit of work.

A stored function is a good example for that.

What would be more important and where this simple approach starts to fall short is for real life scenarios:

1. A table is modified and must be deployed without losing the existing data.

2. One view consisting of tables must be populated in the proper order in all scenarios. First time deployment with the tables first, modification of a table recreates the view, view is changed without touching the table script.

For this you need to have dependecies between files, a delta apply, etc. That gets hard with using snowsql alone as deployment method.