Questions Log:10 Reasons Snowflake Is Great for Analytics
The questions in this log were collected during the Senturus webinar 10 Reasons Snowflake Is Great for Analytics
Q: Can you go over a possible Snowflake use case for a small/medium company (<200gb) that has several OLTP systems with SQL Server backends and a SQL Server data warehouse that is used to model data into fact tables and dimensions for use in Cognos? What would be the benefits of moving to Snowflake over accessing it from the SQL Server warehouse?
A: One benefit of moving to the Snowflake is the lack of maintenance. Another benefit is the ability to scale past what SQL Server is able to do. If those two items are not an issue and performance is acceptable at present, we don’t see a reason to move.
Q: Do we still need to cluster instead of index when data is huge when using Snowflake?
A: We have not seen the need with large tables (+22 billion rows per day) to cluster, but we would imagine there could be a benefit at some level. It is something to consider when performance is not acceptable. In general, tables in the multi-terabyte (TB) range will experience the most benefit from clustering, particularly if DML is performed regularly/continually on these tables. Read more about clustering on the Snowflake support page.
Q: What happens to existing data connections during a clone operation in Snowflake? Are the connections terminated, the cloning suspended or are the users none the wiser?
A: Connections are still referencing the original object, and nothing would happen to that connection. Clones are new metadata objects that point to the original object.
Q: Is Snowflake good for streaming data?
A: Yes, Snowflake can handle streaming loads, but it uses the term micro-batches. Read more about data load Snowpipe on the Snowflake support page.
Q: Is there support for geospatial data types in Snowflake?
A: Currently Snowflake supports the geography data type. Read more on the Snowflake support page.
Q: Is data retrieval through JDBC slow compared to native connectors?
A: We have not seen issues with JDBC connectors vs. other connectors.
Q: What are the top reasons to pick Snowflake over Azure Synapse Analytics?
A: Here are reasons to pick Snowflake over Azure Synapse Analytics
- It’s easier to manage with no indexes and excellent performance. Other solutions need to distribute data among nodes and add indexes to achieve performance.
- True separate storage from compute. Snowflake starts and stops depending on demand; it does not need to be running all of the time like other cloud offerings.
- Low cost to start a project because you pay for what you use. Even an extra-small warehouse can load 35+ million rows in under 30 seconds extra-small warehouse is between $2.00-$4.00 per hour.
- Incredible scale.
Q: Do I need to create a presentation layer for the report’s consumption in Snowflake? Or can I query directly?
A: You can query tables or views directly, just like other databases.
Q: How does a reader account work in Snowflake?
A: You can share data with customers that do not have a Snowflake account and they will be able to see the shared data on your account. This is a detailed subject that will require a lengthy response, read the Snowflake support documentation for more information.
Q: Where would you recommend the transformation of data into a star schema, on Snowflake or on-prem?
A: It depends, it could happen in both easily. If scale is not an issue and it is already done on-prem it might be hard to justify moving it unless there are other reasons. If you want to discuss this, please contact us for a free consultation at 888 601 6010 ext. 1 or firstname.lastname@example.org.
Q: Does Snowflake use any ColumnStore Indexes? What makes it so responsive?
A: Micro partitions have columnar storage/compression and metadata that helps with partition elimination. Read more about micro partitions on the Snowflake support page.
Q: What security considerations do we need to consider if we put financial data into Snowflake?
A: Snowflake is extremely secure and has additional security options like
- Customer-managed encryption keys through Tri-Secret Secure
- Support for secure, direct proxy to your other virtual networks or on-premises data centers using AWS PrivateLink or Azure Private Link
- Support for PHI data (in accordance with HIPAA and HITRUST CSF regulations)
- Support for PCI DSS
Read more on the Snowflake support page.
Q: If Snowflake is so fast, do we need to worry about our data scientists terrible SQL? Is there still a way to analyze a query for efficiency?
A: Yes, like other tools, Snowflake has query profile that displays execution details for a query. Read more about query profile on the Snowflake support page.
Q: Can we secure data in Snowflake so certain users see only certain subsets of data from the same warehouse?
A: Yes, you can use secure views. According to Snowflake, when deciding whether to use a secure view, you should consider the purpose of the view and weigh the trade-off between data privacy/security and query performance. Read more about secure views on the Snowflake support page.
Q: What is the licensing/cost structure for adding accounts that need to access data in Snowflake?
A: The is no licensing cost to users or for access. It is managed with storage, compute and cloud services, like Snowpipe use cost. Read more about use costs on the Snowflake support page.
Q: Currently we have Snowflake, and we are configuring Cognos to access it. Using Cognos, there is a performance degradation. How do we improve performance?
A: The first thing to try is to increase the warehouse size or if there are a lot of requests at the same time, scale out the warehouse. If you’d like to discuss your issue in more detail, contact us for a free consultation at 888 601 6010 ext. 1 or email@example.com. Read more about warehouse performance on the Snowflake support page.
Q: If I need a data lake, and build API's for data retrieval (large or small data set), is Snowflake a good candidate?
A: Yes, Snowflake sounds like it would be a good solution, but to say for sure, we’d need more information. Contact us to discuss your situation at 888 601 6010 ext. 1 or firstname.lastname@example.org.