Snowflake Data Types

Snowflake · Data Types & SQL Fundamentals

Theory

Every Snowflake column has a data type from a few families. Numeric types include NUMBER(p, s), where INT and INTEGER are synonyms for NUMBER(38,0), and FLOAT for approximate values. String types are VARCHAR (STRING and TEXT are synonyms) and BINARY. Date and time types are DATE, TIME and three timestamps: TIMESTAMP_NTZ (no time zone), TIMESTAMP_LTZ (stored in UTC, shown in the session time zone) and TIMESTAMP_TZ (keeps an offset). Semi-structured types are VARIANT, OBJECT and ARRAY. Others include BOOLEAN, GEOGRAPHY, GEOMETRY and VECTOR.

For example, money belongs in NUMBER(12,2), not FLOAT, because FLOAT can introduce rounding errors.

Why we use it

The data type decides which functions, comparisons and sort order apply, and how exact the stored values are. Picking the right type avoids rounding errors, failed casts and confusing time zone bugs.

When to use it

Use NUMBER with a scale for money and exact counts, FLOAT for scientific measurements, VARCHAR without a length unless you want one enforced, an explicit timestamp type for event times, and VARIANT for raw JSON you will parse later.

Numeric & String Types

NUMBER(precision, scale) is the general-purpose numeric type (FLOAT exists but loses exact precision); VARCHAR and TEXT are equivalent. With no length a VARCHAR defaults to 16,777,216 characters and can be declared up to 128 MB, and the declared length has no performance cost.

Date & Time Types

DATE, TIME, and TIMESTAMP (in three timezone variants — NTZ, LTZ, TZ) cover temporal data; TIMESTAMP_NTZ is the most common default since it stores a wall-clock value with no timezone conversion surprises.

Semi-Structured & Special Types

VARIANT holds any JSON-like value, OBJECT and ARRAY are its structured cousins, and BOOLEAN, BINARY, and GEOGRAPHY round out the less-common types for flags, raw bytes, and geospatial data.

How it works

  1. Designing a column
  2. Is it semi-structured data like JSON? Yes: Use VARIANT (or OBJECT/ARRAY). No: Pick a structured type.
  3. Is it an exact amount or count? Yes: Use NUMBER(p, s). No: Consider FLOAT, VARCHAR, DATE or BOOLEAN.
  4. Is it a point in time across time zones? Yes: Use TIMESTAMP_TZ or TIMESTAMP_LTZ. No: Use TIMESTAMP_NTZ or DATE.
  5. Cast explicitly with :: when loading or comparing

Example

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'EMPLOYEES';

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic