CODEWITHSUNDEEP
snowflake-cloud-data-platform
user3612987
user3612987

Reputation: 13

Snowflake as a Backend database for a Frontend UI

We have a requirement wherein we want our business users to manipulate data in Snowflake through some UI interface (which might require creating additional reference tables etc)

Is it a good practice as Snowflake is for DW purpose and not for transactional data and are there any performance issues in doing so?

Typical actions could be filtering data, searching for particular IDs, updating/deleting certain row(s), etc. For these activities wanted to know if it's cost effective?

Upvotes: 0

Views: 1966

Answers (1)

Haseeb
Haseeb

Reputation: 196

Yeah your point is correct, Snowflake db will suit DW workload better than the transactional one.

Having said that, we must also note that the Snowflake databases support a lot of features provided by traditional OLTP databases. For example, honouring ACID properties, transactional consistency, object recovery from accidental drops, read-only database copies for reporting purposes, data encryption, Role based access control, Secure Views, Materialized Views, support for semi-structured data, supportability of a wide variety of function such as Scalar Functions, Aggregation Functions, Window Functions, Table Functions, System Functions as well as support for External Function and customized User-defined Functions (UDFs) etc.

It also provides connectivity interface (driver/connectors) to connect to a variety of different database systems, big-data eco-systems as well as analytical tools.

The Snowflake engine also offers an amazing level of query performance and it has the ability to add dynamic compute power for higher concurrency and greater performance.

So if you are looking for a database for good query performance and doing operations such as querying data, filtering data and routine updates and deletes then it will serve the purpose.

But if you planning to create constraints on tables, PK/FK relationships between tables, indexes, a lot of single row Inserts, any isolation level apart from Read Committed, transaction management through a stored procedure etc. then it may not be a natural choice.

There is a concept of table clustering in place of Indexes. Single row Inserts must be converted into 'COPY Into Table' commands to reduce throttling and to get better performance. Primary Keys / Foreign Keys can be created but they are not enforced.

Upvotes: 1

Related Questions