Snowflake — Key Features
Thanks for reading first blog on Snowflake. If you haven’t read then you can read it at https://poojakelgaonkar.medium.com/snowflake-data-on-cloud-cf3898fee3d0 .
In the first blog, we have learnt about Snowflake — data on cloud and 3-layered architecture. This is second blog in the series where we are going to learn more about Snowflake key features.
Snowflake is data on cloud with shared data architecture. There are many features of Snowflake while some of them are key features which makes Snowflake different than any other Datawarehouse offerings on any other cloud.
We are going to learn more about the various connectors , Security features, Data Protection features, SQL support, tools and interfaces, Import and export features, Data Replication etc. We are going to learn more about Data Sharing, Data Cloning, Time-travel, Failsafe, Snowflake objects with upcoming blogs.
What are the different connectors available to connect to Snowflake?
There are various connectors available to connect and work with Snowflake.
1. Data Integrations — ETL Tools like Informatica, Datastage, Pentaho etc. — There are connectors available to connect to Snowflake as source or target. These are used in expanding existing ecosystem to read or write data to Snowflake. These are also referred as Data Integration tools.
2. BI Integrations — BI/Reporting Tools like Tableau, PowerBI, Looker — Connectors used to connect to Snowflake. These are referred as BI integrations
3. ML & Data Science — Integration with Spark, databricks, Qubole
4. SQL Development — Aginity (using connector/drivers), Snowflake native — SnowSQL, Web UI
5. Native Programming Interfaces — Integrations with Python, PhP, JDBC/ODBC , Spark, Node.js etc. These are native connectors used to connect to Snowflake to read/write data to Snowflake
What are Snowflake native tools & interfaces?
Snowflake offers 2 interfaces natively — Web interface and CLI. Web based UI of snowflake used to develop SQLs using worksheets. SnowSQL is CLI used to connect to Snowflake through command line to run SQLs, commands, jobs through command line.
What is type of SQL supported in Snowflake?
Snowflake supports standard SQL, ANSI SQL standards. Snowflake extended SQL like lateral views, materialized views, statistical aggregate functions, Scalar and tabular UDFs with support to Java, Python and Javascript. Snowflake supports stored procedures as well as procedural language support. Snowflake also supports Recursive CTE, Connect by, collation support and Geospatial data support.
What are Security and Data Protection supported by Snowflake?
Authentication methods –
1. Multi-factor Authentication (MFA)
2. Federated Authentication and SSO
3. OAuth
Data Security –
1. Object level access control
2. Role based access control
3. Column level security
4. Row access policies
Data compliance –
1. Supports PHI Data in compliance with HIPAA, HITRUST CSF regulations
Data Protection –
1. Automatic data encryption by snowflake managed keys
2. Time-travel (1 to 90 days) — querying historical data , restoring historical data
3. Fail safe (7 days) for disaster recovery
We will learn Time-travel and failsafe in detail with upcoming blogs.
What are types of load and unload supported in Snowflake?
Snowflake supports bulk load as well as real time data loads. Snowflake bulk loading supports –
1. Load flat files, delimited files ( CSV, TSV files)
2. Load data files in JSON, Avro, ORC, Parquet, and XML format.
3. Load from files in cloud storage or local files using the Snowflake web interface or command line client.
4. Load data from compressed files
Snowflake real time data loads from files –
1. Snowflake object Snowpipe is used to bring in data real time to Snowflake. These are loaded in micro batches and near real time data loads
Snowflake data unloads supports –
1. Data unload into Single or multiple files
2. Data can be loaded in fixed, delimited or semi-structured files
3. Data can also be unloaded partition data into separate files
We will learn more about bulk load, real time data load and unload options in detail with upcoming blogs.
What are distinguishing features of Snowflake?
Snowflake brings in 2 of most important and distinguishing features –
1. Data Sharing — Data sharing is used to create data shares or secure data shares. Providers and Consumers can create and consume data from Snowflake accounts as well as non-snowflake accounts
2. Data Cloning — Data clones are created across environments. This helps to setup dev, prod, QA environments.
We will learn more about data cloning and data sharing in detail with upcoming blogs.
I believe, by now you know some of key features of Snowflake. We have also quickly listed some of differentiating features of Snowflake. This is second chapter in the journey of Snowflake. In next blog, we will learn more about Snowflake import and export utilities.
About Me :
I am DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, Bigdata Implementations, Cloud platforms/Migrations. I am SnowPro Core certified Data Architect as well as Google certified Google Professional Cloud Architect. You can reach out to me LinkedIn you need any further help on certification, Data Solutions and Implementations!
