Snowflake is a well rounded data warehouse solution and very popular in finance industry. It provides conventional data warehouse, data pipeline, data governance and ML capabilities, and recently added Cortex etc. AI capability.

People have been building data pipeline, ML pipeline and now AI chatbot in Snowflake. With data and pipelines all in Snowflake, we start to build more and more complex business logics inside Snowflake, with tools like Task, Stream, Stored Procedures etc. However, Snowflake is not best suited as a replacement of the typical web application.

Snowflake is not true event driven

There is no way to implement in Snowflake something like a AWS Lambda that can listen to some tables and fire off. Instead, what I typically see is a Snowflake Task that scans the tables every few minutes and then triggers some Snowflake Stored Procedures. This makes the logic cumbersome, because it needs to determine (1) what kind of records triggers the Stored Procedures and (2) afterwards how to update them so they wouldn't trigger again.

I think we'd be better off just implement such logics in the backend of a web app. When the backend generates the data, it can also trigger some logic either sequentially or concurrently, instead of trying to trigger the Stored Procedure. This is very direct and clear: data change causes logic execution right away.

Avoid complex logic in Snowflake stored procedure

The stored procedure is defined in SQL, but can container other languages as the actual handler, i.e. function body. So I basically need to embed, e.g. python code, inside SQL statement within $$...$$. It becomes impossible to lint, syntax check, unit test etc. If stored procedure can't be avoided, better to keep it simple. And with a web app backend, it typically doesn't need stored procedures.

Avoid Snowflake as operational database

Snowflake by design is meant for large data storage in analytical use cases. Using it as a replacement of typical relational database for operational use case is not the best choice. I was surprised that primary key, foreign key and uniqueness constraint can be defined in snowflake tables, but they are NOT enforced. Such database level rules are not there to protect my business logic.