Re: Pros and Cons
The real question about security is not whether cloud-based DW is "secure". Nothing is absolutely secure. The question is whether it is secured as well or better than on-premises DWs, and the answer to that is "yes". First of all, enterprise SaaS vendors have more resources to spend on security, and can attract highly qualified security personnel (the expense is amortized across many customers). Secondly, cloud SaaS vendors are painfully aware that security is the major concern, so they design their software with security in mind from the day one. Which is quite different from how the conventional enterprise software is designed (the vendors just assume that security is something firewalls and LDAP servers would take care of).
In case of Snowflake, it encrypts everything. All data (table data, staged ingested and unloaded data, temporary files, etc) and all network communications across both WAN and datacenter LANs are encrypted. The only time data appears in unencrypted form is in memory when it is being processed. The key management follows best practices, and you can even opt for keeping master keys in hardware security module - so that even Snowflake staff won't have access to the data. It has extensive authentication and authorization infrastructure (2FA? Here. Role-based access controls? Here. Federation with customer's own authorization? Here.) This all is backed up by the robust and constantly tested organizational and operational security, and has a slew of security certifications. No on-premises DW comes close security-wise.
If you contact Snowflake, they will tell you exactly what they do to secure your data.
(In the interest of transparency: I was a software engineer at Snowflake before I left to start my own company, so I know what I'm talking about.)