Snowflake Data Types: SQL Examples and a Beginner Validation Lab

Snowflake data types define the values a column or expression can hold and how those values behave. Start with NUMBER for exact decimal amounts, VARCHAR for text and identifiers, DATE for calendar dates, BOOLEAN for flags, and an explicit timestamp type for event times. Use VARIANT when the input structure can vary, then validate important fields before reporting.

This guide is for freshers, Excel users and career switchers who can run a SELECT query but are unsure why an amount rounds, an ID loses its leading zeros or a date fails to convert. You will build a four-row synthetic source, identify three faulty rows and create one trustworthy typed order. The goal is a repeatable decision process, rather than memorizing every supported type.

Use the Snowflake SQL tutorial for joins and aggregation, and the Snowflake account setup guide if you cannot yet select a role, warehouse and schema. This article focuses on type choices and conversion quality. The exercises are documented SQL examples; no connected Snowflake execution is claimed. Record your actual outputs beside the expected results below.

Begin with the meaning and allowed values, then choose the representation. A phone number or postal code is usually text even though it contains digits. Do not use FLOAT for an identifier because approximate arithmetic and formatting are irrelevant to its identity. Conversely, storing every amount as VARCHAR postpones errors until someone totals or sorts the report.

This is a beginner selection table, not an exhaustive catalog. Snowflake also documents binary, structured, geospatial, vector and other specialized types. Follow the relevant official reference when a workload needs them instead of fitting all inputs into this table.

Reference: Snowflake SQL data type reference.

1. Use an authorized practice role and an assigned small warehouse. Obtain USAGE on the database/schema and the privileges needed to create temporary tables.
2. Select your approved context, replacing the three YOUR_ names below. Do not create or resize shared compute for this exercise.
3. Run all temporary-table statements in the same session. Save the script before reconnecting.
4. Define the contract: order code is present; amount is a valid nonnegative decimal; order day is a real ISO date; attributes are valid nonempty JSON.

USE WAREHOUSE YOUR_PRACTICE_WAREHOUSE;
USE DATABASE YOUR_PRACTICE_DATABASE;
USE SCHEMA YOUR_PRACTICE_SCHEMA;
SELECT CURRENT_ROLE(),CURRENT_WAREHOUSE(),CURRENT_DATABASE(),CURRENT_SCHEMA();

The object names in this guide start with BA_TYPES_. CREATE deliberately fails if a name is already in use; inspect existing work or use a new name instead of replacing unknown objects. Warehouse time and stored data can incur usage. Keep auto-suspend enabled under the owner’s policy; this tiny input does not justify a larger warehouse.

Conceptual validation path: source text → explicit parsing → independent error flags → accepted typed rows and review rows → count/amount reconciliation. Keep the source columns while debugging so a NULL conversion result can be traced back to the original value. A clean-looking output with no rejected-row explanation is insufficient.

In NUMBER(p,s), precision is the total permitted decimal digits and scale is the digits to the right of the decimal point. NUMBER(5,2) allows three integer digits and two fractional digits. A value outside the accepted integer range fails; a value with excess fractional digits can be rounded during conversion. Choose a scale from the business requirement, then validate precision-sensitive inputs before accepting rounding.

SELECT TRY_TO_DECIMAL('999.99',5,2) AS fits,
       TRY_TO_DECIMAL('1000.00',5,2) AS outside_range,
       TRY_TO_DECIMAL('12.345',5,2) AS rounded;
-- Expected: 999.99, NULL, 12.35.

DECIMAL and NUMERIC are aliases for NUMBER. Snowflake integer aliases such as INT, BIGINT and SMALLINT map to NUMBER(38,0); do not copy a different database’s small-integer range assumptions into Snowflake validation. Use explicit range checks when the source contract requires a smaller interval.

FLOAT is approximate and useful where approximation is acceptable, such as some scientific measurements. Exact equality on results of floating-point calculations can be misleading. A blanket promise that reducing NUMBER precision saves storage is also wrong: numeric storage depends on the values and scale, rather than declared precision alone. Choose correctness before discussing performance.

Reference: Numeric types and storage behavior.

CREATE TEMP TABLE BA_TYPES_RAW (
 order_code VARCHAR, amount_text VARCHAR, day_text VARCHAR, attrs_text VARCHAR
);
INSERT INTO BA_TYPES_RAW VALUES
 ('0001','125.50','2026-10-01','{"channel":"web"}'),
 ('0002','oops','2026-10-02','{"channel":"store"}'),
 ('0003','20.00','2026-02-30','{"channel":"web"}'),
 ('0004','-5.00','2026-10-04','not-json');
SELECT * FROM BA_TYPES_RAW ORDER BY order_code;
SELECT COUNT(*) AS source_rows FROM BA_TYPES_RAW;
-- Expected source_rows: 4.

These invented orders are supplied in full and need no external dataset or customer information. There is one row per order code. Row 0002 has an invalid amount; row 0003 has an impossible date; row 0004 has both a negative amount and malformed JSON. The fourth row demonstrates why the number of errors can exceed the number of rejected rows.

The raw table deliberately uses VARCHAR columns so every input token can be inspected. That is a landing strategy for this exercise, not a recommendation to keep all reporting columns as text forever. A production source contract also needs currency, decimal precision, date rules and schema evolution decisions.

Run the INSERT once. A second execution inserts another four records; a later COUNT of eight is not a parser defect. Verify the raw row count and order-code uniqueness before doing conversion work. Keep this baseline in your evidence file.

CREATE TEMP TABLE BA_TYPES_PARSED AS
SELECT order_code,amount_text,day_text,attrs_text,
 TRY_TO_DECIMAL(amount_text,12,2) AS amount,
 TRY_TO_DATE(day_text,'YYYY-MM-DD') AS order_day,
 TRY_PARSE_JSON(attrs_text) AS attrs
FROM BA_TYPES_RAW;
CREATE TEMP TABLE BA_TYPES_CHECKED AS
SELECT *,
 (order_code IS NULL OR TRIM(order_code)='') AS bad_code,
 (amount IS NULL) AS bad_amount,
 (amount<0) AS negative_amount,
 (order_day IS NULL) AS bad_day,
 (attrs IS NULL) AS bad_json
FROM BA_TYPES_PARSED;
SELECT order_code,amount,order_day,bad_amount,negative_amount,bad_day,bad_json
FROM BA_TYPES_CHECKED ORDER BY order_code;

TRY conversion functions return NULL on conversion failure, which keeps the query running. That does not make a failed value acceptable. A genuine source NULL and a failed conversion can both produce SQL NULL, so retain source text and use the contract to distinguish missing from malformed input. Negative amounts parse successfully; they fail this exercise’s business rule separately.

The negative_amount flag is NULL when amount is NULL. The acceptance query in the next step uses COALESCE for that flag and also requires bad_amount to be false. This handles SQL’s three-valued logic explicitly. The JSON rule here rejects SQL NULL parser results; a real contract may also distinguish JSON null, empty objects and missing required keys.

Reference: TRY_TO_DECIMAL.

Reference: TRY_PARSE_JSON.

CREATE TEMP TABLE BA_TYPES_ACCEPTED AS
SELECT order_code,amount,order_day,attrs
FROM BA_TYPES_CHECKED
WHERE NOT bad_code AND NOT bad_amount AND NOT bad_day AND NOT bad_json
 AND NOT COALESCE(negative_amount,FALSE);
SELECT order_code,amount,order_day,attrs:channel::VARCHAR AS channel
FROM BA_TYPES_ACCEPTED ORDER BY order_code;
DESC TABLE BA_TYPES_ACCEPTED;

The expected accepted row is 0001, 125.50, 2026-10-01, web. Inspect DESC TABLE to verify the resulting column types instead of assuming that the column names prove typing. The order code remains VARCHAR, the amount is decimal, the day is DATE and attrs is VARIANT. The path extraction casts the channel value into a text output for a report.

VARIANT can hold values with different underlying types. TYPEOF can help inspect a value inside it; it is different from inspecting the declared table schema. A syntactically valid object can still miss a required key or hold the wrong business value. Add field-level validation before turning arbitrary JSON into an authoritative reporting attribute.

This lab uses typed tables to make the transition visible. A production design could use views, controlled transformations or other patterns according to its requirements. It should still preserve enough source and error evidence to explain every exclusion. Do not silently cast bad prices to zero to make a dashboard total appear complete.

Reference: Semi-structured data types.

SELECT COUNT(*) AS accepted_rows,SUM(amount) AS accepted_total
FROM BA_TYPES_ACCEPTED;
SELECT order_code FROM BA_TYPES_CHECKED
WHERE bad_code OR bad_amount OR bad_day OR bad_json
 OR COALESCE(negative_amount,FALSE)
ORDER BY order_code;
-- Expected: 1 accepted row, total 125.50; rejected IDs 0002,0003,0004.
SELECT TRY_TO_DECIMAL('10.00',12,2) AS corrected_amount,
       TRY_TO_DATE('2026-10-02','YYYY-MM-DD') AS corrected_day;
-- Counterfactual: correcting only row 0002's amount would allow 2 rows, total 135.50.

Four source rows reconcile to one accepted row plus three rejected rows. Row 0004 has two error flags but counts once in the rejected-row total. Keep both checks: a count reconciliation and an amount expectation. Comparing SUM on the raw text would either fail or hide the parsing policy.

The changed-input query is deliberately a counterfactual calculation; it does not update the raw table or automatically rebuild the derived tables. If you implement the correction in a disposable copy, rerun the transformation stages under new names and verify the two accepted IDs and 135.50 total. Changing the raw row alone leaves the existing CREATE TABLE AS SELECT snapshots unchanged.

For a precision extension, supply 12.345 and decide whether rounding to two decimal places is permitted. A parseable value is not necessarily acceptable. Document the rounding rule or quarantine excess precision rather than quietly changing the reported amount.

Record the failing token, chosen type, format string and expected behavior. That evidence makes an interview explanation useful: you can show how technical parsing differs from a business rule. Never paste credentials or real customer records into a public troubleshooting post.

Do not alter shared production column types to make this exercise pass. A real type migration requires dependent-query review, range/precision profiling and a rollback plan. The immediate beginner deliverable is a correct disposable lab and a documented set of decisions.