Snowflake Describe Table Command 101: Syntax & Usage Simplified

The ever-growing demand for Efficient Data Handling & Processing is on a new high. Due to the Limitations of On-Premise Data Storage & Analytics Tools, Businesses are now adopting Cloud Solutions such as Snowflake. Snowflake is a Cloud Data Warehousing & Analytics Platform that allows instant scaling of storage and computational resources independently. Supporting Standard SQL, Snowflake allows you to gain access to your data and perform high-speed analysis.

One of the essential SQL commands is the Snowflake Describe Table command. Using the Snowflake Describe Table you can retrieve information regarding columns in a table and the default values for the stage properties. For an External Table, you can employ the Describe External Table command to describe the table.

In this article, you will learn how to effectively use the Snowflake Describe Table and Describe External Table Command. 

What is Snowflake?

Snowflake is a Data Warehousing & Analytics Platform offered as a Software-as-a-Service that operates on a Cloud infrastructure. It is designed to use an advanced SQL Database Engine with a distinctive Cloud pattern. Its eminence relies on its capability of scaling storage and computing independently so the customers can control cost expenditures accordingly. It isn’t supported by private cloud or hosted infrastructures. Snowflake automatically administers all parts of the data storage process, including Organization, Structure, Metadata, File Size, Compression, and Statistics. It is available on Azure and AWS Cloud Platforms.

Key features of Snowflake

Snowflake offers the following remarkable features:

• Unique Architecture: A fusion of both traditional Shared Disk and Shared-Nothing databases. Akin to Shared-Disk Architecture, snowflakes draw on a central repository for the data that is accessible from all nodes in a platform. On the other hand, it operates using MPP (Massively Parallel Processing) compute clusters where every node stores some of the entire data individually. just like a shared-nothing database. so that users can enjoy the simplicity of the Shared-Disk Architecture and the performance and benefits of a Shared-Nothing Database.
• Scalability: Snowflake’s architecture independently scales the compute and storage resources. This allows users to scale up resources when a large amount of data has to be uploaded in a short interval of time and scale back down when done, without any intrusion to service. To minimize administration, snowflake has instilled auto-scaling. This makes an automatic start and cease of the clusters during unpredictable resource processing.
• Retrieving Objects: The UNDROP command is the solution to the mistakes we make while sharing the data by dropping the wrong table. Recovering the data traditionally can cost you a lot of time restoring and backing up. Using this command, snowflake enables you to recover the data instantly as long as you are in the Recovery Window.
• Caching Results: A remarkable feature of the snowflake is caching results at various levels. This means after a query is executed, the data result stays still for 24 hours. So if the same query is carried out again by any user within the account, results are already available. This is beneficial when you want to compare a query before and after an alteration.
• Handling Semi-Structured Data: To handle Semi-Structured Data, Snowflake’s architecture employs an on-schema reading data type called VARIANT. It stores both Structured and Semi-Structured data in the same destination. Once the data is loaded Snowflake automatically extracts the attributes by parsing the data. Later this data is stored in columnar format.
• Minimal Administration: Delivered as Data Warehouse as a service, Snowflake required minimal administration intervention from the user side. The scalability feature allows the least involvement from DBA(Database Administrator) and IT teams. There is no need to install software or hardware.
• Zero Copy Clones: If you want to clone an already existing database, employing traditional Data Warehouse Services is hectic as you have to deploy a totally new environment and upload the data. This isn’t feasible because it can cost an extra amount to the customer. Snowflake’s zero-copy feature is the solution to this problem. Using this feature you can instantly clone without creating a new copy that saves up your storage. It alters the clone on its Metadata Store while in the backend still referring to the original data file. Its Time-Traveling Feature used to make clones of the data from some past point in time makes it one of the Best Warehouses.
• Data sharing: Snowflake is offering a secure data sharing feature that enables object sharing from a database of one account to another without creating a duplicate. This ensures more storage space and fewer storage expenditures. Snowflake’s metadata store makes it easier and quicker to access the data. Hence, Snowflake creates a network of Data providers and Consumers that allows for many use cases. For those who don’t hold an account, Snowflake provides an option to create a Readers Account, a Cost-Effective way that enables consumers to access the shared data for free.

How to use the Snowflake Describe Table command? 

Describing a snowflake table means providing the information regarding columns in a table, current, and sometimes default values for the stage properties. You can use the Snowflake Describe Table command to achieve this. The Snowflake Describe Table command serves two purposes. Either it mines out the data for the table or looks upon if it matches the stated criterion. However, TYPE = STAGE is not pertinent regarding views because views lack stage properties. Also, DESC TABLE and DESCRIBE VIEW are interchangeable. 

A) Snowflake Describe Table Syntax

DESC[RIBE] TABLE <name> [ TYPE =  { COLUMNS | STAGE } ]

The Snowflake Describe Table Syntax has the following parameters:

• <name>: It cites the identifier for the table to describe. The entire string must be enclosed within double quotes in case the identifier has spaces or special characters. Identifiers in double quotes are case-sensitive.
• TYPE = COLUMNS | STAGE: It Identifies either to display the columns for or the stage properties (both current and default values) for the table. Default is TYPE = COLUMNS.                       

B) Snowflake Describe Table Usage Guidelines

To get the best results, follow the Snowflake Describe Table Usage Notes given below:

• Use SHOW PARAMETERS in the table to see the object parameters.
• DESCRIBE VIEW and DESC TABLE are replaceable. The DESCRIBE command serves two purposes. Either it mines out the data for the table or looks upon if it matches the stated criterion. However, TYPE = STAGE is not pertinent regarding views as views lack stage properties.
• Set on the column, there is an output including POLICY NAME column to show the Column-Level Security Masking Policy. Incase Snowflake is not enterprise edition or advanced and has no masking policy, snowflake NULLS.
• To post-process the command output, there is a function called RESULT_SCAN. It deals with the output as a table and is queried.

C) Snowflake Describe Table Examples

To understand the Snowflake Describe Table command, let’s create a sample Student Table and perform some sample queries.

create table student (RollNo number not null primary key, Firstname varchar(50), Lastname varchar(50), location varchar(100));

• Describing columns  using Snowflake Describe Table command     

desc table student;

• Describe the staging properties using Snowflake Describe Table command

desc table student type = stage;

• Finding masking policies using Snowflake Describe Table command

desc table ssn_record;

Output:

---------------+-------------+--------+-------+---------+-------------+
      name     |    type     |  kind  | null? | default | primary key | 
EMPLOYEE_SSN_1 | VARCHAR(32) | COLUMN | Y     | [NULL]  | N           | 
-----------+--------+------------+---------+----------------------------+
unique key | check  | expression | comment |       policy name          |
      N    | [NULL] | [NULL]     | [NULL]  | MY_DB.MY_SCHEMA.SSN_MASK_1 |
-----------+--------+------------+---------+----------------------------+

For more information, you can visit the Official Documention page of Snowflake Describe Table Command.

How to use the Snowflake Describe External Table command?

This command illustrates the VIRTUAL and VALUE columns in an external table. To understand this command, lets go through the syntax and an example.

A) Describe External Table Syntax

DESC[RIBE] [ EXTERNAL ] TABLE <name> [ TYPE =  { COLUMNS | STAGE } ]

 The <name> parameter cites the identifier for the external table to describe. The entire string must be enclosed within double quotes in case the identifier has spaces or special characters. Identifiers in double quotes are case-sensitive. To post-process the command output, there is a function of RESULT-SCAN. It deals with the output as a table and is queried.

B) Describe External Table Example

For reference, lets first create an external table:

create external table student ( ... );

Now to describe the columns of the external table, you can execute the following command:

desc external table student;

Conclusion

• Snowflake is a cloud data warehouse provided as Software-as-a-Service (SaaS) for efficient data storage, processing, and analytics.
• It offers support for Standard & Extended SQL, making it easy for data analysts to execute queries and retrieve table metadata using commands like DESCRIBE TABLE and DESCRIBE EXTERNAL TABLE.
• Snowflake simplifies decision-making by providing insights through data analysis.
• As businesses grow, they generate vast amounts of data across various SaaS applications.
• To process and analyze this data effectively, it’s important to integrate, clean, and transform it before loading it into a data warehouse like Snowflake.
• Cloud-based ETL tools, such as HevoData, can help automate and streamline these data integration and transformation tasks.

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 150+ sources to a Data Warehouse like Snowflake or a Destination of your choice to be visualised in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

Want to take Hevo for a ride? Explore its 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of working with the Snowflake Describe Table & Describe External Table Commands! Share your thoughts with us in the comments section below.

FAQs