Your data team will love Snowflake, just be sure to manage cost
Use Cases and Deployment Scope
Our company has grown organically and by acquisition, and as a result we have a series of disparate software products with around 800 databases across different clouds and different databases - SQL Server, MySQL, Postgres, AWS, Azure, etc.
My challenge was to implement an awesome and consistent reporting suite that allowed customers full access to all of their data across all of our products. While the software development team is working on integrating all these disparate products, that's a significant multi-year challenge.
Implementing a data lake in Snowflake greatly empowered my team to make tremendous reporting, blending data from many sources, without adding any load to the transactional databases. What's more, Snowflake has a vast array of excellent built-in features such as row-level security, data masking, secure sharing, and other items that allowed us to push security right down to the database level so we secure data no matter how customers access it, without relying on complex where clauses, etc., in queries.
The Snowflake model of separating compute from storage and allowing us to have differing workloads for differing usage scenarios is also very helpful.
My challenge was to implement an awesome and consistent reporting suite that allowed customers full access to all of their data across all of our products. While the software development team is working on integrating all these disparate products, that's a significant multi-year challenge.
Implementing a data lake in Snowflake greatly empowered my team to make tremendous reporting, blending data from many sources, without adding any load to the transactional databases. What's more, Snowflake has a vast array of excellent built-in features such as row-level security, data masking, secure sharing, and other items that allowed us to push security right down to the database level so we secure data no matter how customers access it, without relying on complex where clauses, etc., in queries.
The Snowflake model of separating compute from storage and allowing us to have differing workloads for differing usage scenarios is also very helpful.
Pros
- Security
- Scaling
- Support
- Separating compute from storage
- Flexible disparate compute models
- Detailed history of all your queries and activity, for analysis/review/troubleshooting
- Cloning, undeletion, sharing - all tied to how Snowflake stores data
Cons
- There is no support for triggers
- Materialised views are limited to only one table
- You can't create dynamic SQL in functions
Most Important Features
- Security - role-based access control, row-level policies, data masking
- Cloud agnostic - we can put it in any cloud, any region, so it lives where our apps/data is
- The support is very good; you can ask questions and get detailed, helpful answers
- The ability to review activity/usage, and look at any and all past queries is tremendously helpful for debugging and analysis
Return on Investment
- Snowflake has accelerated our ability to offer a single reporting solution that blends many disparate databases together
- There is a need, however, to carefully monitor costs. It's easy to bring costs up significantly through larger compute warehouses than needed, or by scaling too soon, or by not pairing the warehouse with the workload appropriately
- We have had some difficulty finding the best tools to ingest data into Snowflake from all our sources, and it must be remembered there is a cost to this element also
Alternatives Considered
Amazon Redshift, Google BigQuery and Databricks Lakehouse Platform (Unified Analytics Platform)
Other Software Used
Hevo Data, Logi Composer (Zoomdata), Logi Info (Logi Analytics Platform), Amazon Redshift, Microsoft SQL Server
