What are the features of Snowflake?
Unique features of the Snowflake data warehouse are listed below:
- Database and Object Closing
- Support for XML
- External tables
- Hive meta store integration
- Supports geospatial data
- Security and data protection
- Data sharing
- Search optimization service
- Table streams on external tables and shared tables
- Result Caching
Describe Snowflake Schema
In Snowflake, a schema is a logical grouping of database objects such as tables, views, etc. The snowflake schema is made up of fact tables that are centralised and linked to multiple dimensions. A Snowflake Schema is a dimension-added extension of a Star Schema. The dimension tables have been normalized, resulting in the data being split into additional tables.
The benefits of using Snowflake schemas are it provides structured data and uses small disk space.
What kind of SQL does Snowflake use?
Snowflake supports the most common standardized version of SQL, i.e., ANSI for powerful relational database querying.
What ETL tools do you use with Snowflake?
Following are the best ETL tools for Snowflake
- Matillion
- Blendo
- Hevo Data
- StreamSets
- Etleap
What type of database is Snowflake?
Snowflake is built entirely on a SQL database. It’s a columnar-stored relational database that works well with Excel, Tableau, and many other tools. Snowflake contains its query tool, supports multi-statement transactions, role-based security, etc., which are expected in a SQL database.
Explain Snowflake Time Travel
Snowflake Time Travel tool allows us to access the past data at any moment in the specified period. Through this, we can see the data that we can change or delete. Through this tool, we can carry out the following tasks:
- Restore the data-associated objects that may have lost unintentionally.
- For examining the data utilization and changes done to the data in a specific time period.
- Duplicating and backing up the data from the essential points in history.
Explain Snowpipe in Snowflake
Snowpipe is Snowflake's continuous data ingestion service. Snowpipe loads data in minutes once files are uploaded to a stage and submitted for ingestion. Snowflake maintains load capacity with Snowpipe's serverless compute approach, assuring appropriate compute resources to meet demand. In a nutshell, Snowpipe provides a "pipeline" for loading new data in micro-batches as soon as it becomes available.
The data is loaded using the COPY command defined in a connected pipe. Snowpipe can use a pipe, which is a named, first-class Snowflake object containing a COPY statement. The COPY statement specifies the location of the data files (i.e., a stage) as well as the target table. All data types, including semi-structured data types like JSON and Avro, are supported.
There are several ways for detecting staged files:
- Using cloud messaging to automate Snowpipe
- REST endpoints in Snowpipe
The Snowpipe benefits are as follows:
- Real-time insights
- User-friendly
- Cost-efficient
- Resilience
Is Snowflake OLTP or OLAP?
An OLTP (Online Transactional Processing) database contains detailed and up-to-date data, as well as a large volume of typically small data transactions. In turn, online analytical processing (OLAP) often necessitates complex and aggregated queries with a small number of transactions. Snowflake's database schema is built around online analytical processing.
How to create a Snowflake task?
To create a Snowflake task, we have to use the “CREATE TASK” command. Procedure to create a snowflake task:
- CREATE TASK in the schema.
- USAGE in the warehouse on task definition.
- Run SQL statement or stored procedure in the task definition.
How do we create temporary tables?
To create temporary tables, we have to use the following syntax:
Create temporary table mytable (id number, creation_date date);
