How to Use Data Types in Snowflake

What are the data types available in Snowflake?

Snowflake data types define how numeric, text, date/time, and semi-structured values are stored, indexed, and processed.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Which numeric data types should I pick?

Choose NUMBER for exact precision (e.g., monetary totals) and FLOAT for scientific or approximate calculations. NUMBER(38,0) handles large integers like order IDs. Use DECIMAL as an alias of NUMBER to keep code portable.

How do I store customer names and emails?

Use VARCHAR without a length limit for free-form text. For shorter codes, CHAR(N) fixes storage to N bytes but is rarely needed.Always set COLLATE to ensure case-insensitive searches when required.

What’s the best way to save dates & times?

DATE stores calendar dates; TIME captures clock time; TIMESTAMP_NTZ stores both without a time zone; TIMESTAMP_TZ keeps the offset. Favor TIMESTAMP_TZ for global ecommerce apps to avoid math on offsets.

Can Snowflake handle JSON product specs?

Yes. VARIANT stores semi-structured data like JSON, Avro, or XML. Use OBJECT and ARRAY for stricter typing when you know the structure.Query with the colon operator (product_specs:color::string).

How to convert between data types?

Use CAST(value AS target_type) or the :: shorthand. Keep an eye on rounding behavior when casting FLOAT to NUMBER.

Best practices

1) Declare column precision only when necessary; wider columns compress well. 2) Prefer NUMBER over FLOAT for currency. 3) Index complex filters with CLUSTER BY, not data type tweaks.

Why How to Use Data Types in Snowflake is important

How to Use Data Types in Snowflake Example Usage


-- Store JSON specs for each product and query nested fields
CREATE TABLE Products (
  id      NUMBER(38,0),
  name    VARCHAR,
  price   NUMBER(10,2),
  stock   NUMBER(10,0),
  specs   VARIANT
);

-- Insert a product with JSON specs
INSERT INTO Products VALUES
  (1, 'Galaxy Hoodie', 49.99, 150,
   PARSE_JSON('{"color":"black","sizes":["S","M","L"]}'));

-- Retrieve only black items in stock
SELECT id, name, price
FROM   Products
WHERE  specs:color::string = 'black'
  AND  stock > 0;

How to Use Data Types in Snowflake Syntax


-- Creating a table with common Snowflake data types
CREATE TABLE Orders (
  id            NUMBER(38,0),
  customer_id   NUMBER(38,0),
  order_date    TIMESTAMP_TZ,
  total_amount  NUMBER(12,2),
  notes         VARCHAR,
  metadata      VARIANT
);

-- Casting examples
SELECT CAST(total_amount AS VARCHAR) AS amount_txt,
       order_date::DATE            AS order_day
FROM   Orders;

Common Mistakes

Relying on FLOAT for currency values causes rounding errors. Fix by switching to NUMBER(12,2) or DECIMAL for exact arithmetic.
Casting JSON fields directly to NUMBER without null checks can fail on blank values. Wrap with TRY_CAST or COALESCE to provide defaults.