1.3 Data Types & The VARIANT Type
Before diving into creating database objects, it is important to understand the specific data types Snowflake supports for storing data.
- Views: Virtual tables representing the result of a SQL query.
- Stages: Locations where files (CSV, JSON) are stored for loading/unloading.
- Pipes: Objects used for continuous data ingestion (Snowpipe).
- UDFs / Stored Procedures: Custom logic and functions.
Supported Data Types
Snowflake supports all standard SQL data types:
- Numeric:
NUMBER,DECIMAL,INT,FLOAT - String:
VARCHAR,STRING,TEXT - Date & Time:
DATE,DATETIME,TIMESTAMP_LTZ,TIMESTAMP_NTZ,TIMESTAMP_TZ - Logical:
BOOLEAN
🌟 The Magic of the VARIANT Data Type
One of Snowflake's most powerful features is its native support for semi-structured data via the VARIANT data type.
Unlike traditional RDBMS systems where you must parse, flatten, and extract JSON data before loading it into strict columns, Snowflake allows you to load raw JSON, Parquet, or Avro files directly into a single VARIANT column.
Snowflake automatically parses the semi-structured data internally, extracts the attributes, and stores them in a columnar format behind the scenes—giving you relational database query speeds directly on JSON data!
-- Querying JSON data natively
SELECT
raw_data:customer_name::STRING AS customer_name,
raw_data:address.city::STRING AS city
FROM raw_json_table;