Structured, semi-structured, and unstructured data Study Guide — COF-C03

14% of the exam · domain 4 of 7 · Snowflake SnowPro Core · Free Structured, semi-structured, and unstructured data practice questions

0 of 54 topics covered
What the exam asks here — tap to tick off0 / 6 covered
Domain 4 of 7

Structured, semi-structured, and unstructured data

This domain (14% of the exam, roughly 8–9 scored questions on a ~100-question, 115-minute exam) tests whether you can pick the right data type, the right file format option, and the right access mechanism for data that is relational, nested, or file-based. Questions are heavily syntax- and default-driven: expect to recognize exact type synonyms and size limits (NUMBER(38,0), 16 MB VARIANT), the exact behavior of FLATTEN arguments, which file formats support load vs. unload, the three kinds of file URLs and their expiration, and the exact syntax/limits of SAMPLE and AT/BEFORE. There is little conceptual hand-waving here — memorize the numbers and the defaults.

Structured data types: numeric, string, date/time, boolean

Snowflake has a deliberately small type system. Many "types" you see in other databases exist only as synonyms that resolve to one canonical type. The exam loves asking what a synonym actually becomes.

Numeric

Declared typeCanonical typeKey facts
NUMBER(p,s), DECIMAL, NUMERICNUMBERMax precision 38 digits. Defaults: NUMBER = NUMBER(38,0). Max scale 37.
INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINTNUMBER(38,0)All identical; precision/scale cannot be specified. TINYINT is not 1 byte.
FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, REALFLOATAll 64-bit IEEE-754 doubles (even FLOAT4). Approximate: ~15–17 significant digits. Supports 'NaN', 'inf', '-inf'.
  • Precision has no effect on storage or performance in Snowflake; scale does affect exactness. A NUMBER(2,0) column and a NUMBER(38,0) column cost the same.
  • There is no unsigned, no MONEY, no SERIAL. Auto-increment is a column default (IDENTITY/AUTOINCREMENT), not a type.
  • Fixed-point (NUMBER) is exact; FLOAT is not. Financial values → NUMBER.

String and binary

TypeDefault / maxNotes
VARCHAR(n)Default = max = 16,777,216 bytes (16 MB)n is a character limit; storage is UTF-8 bytes. No padding, no cost for a large declared size.
STRING, TEXT, NVARCHAR, CHAR VARYING= VARCHARPure synonyms.
CHAR(n), CHARACTERCHAR with no length = CHAR(1)Not blank-padded (unlike Oracle/DB2).
BINARY(n), VARBINARYMax 8,388,608 bytes (8 MB)BINARY_INPUT_FORMAT / BINARY_OUTPUT_FORMAT default HEX; also BASE64, UTF8.

A preview "large object" capability raises VARCHAR/VARIANT limits to 128 MB in some accounts. On the exam, answer 16 MB for VARCHAR and VARIANT and 8 MB for BINARY unless the question explicitly frames the larger limits.

Date/time

TypeStoresSession behavior
DATECalendar date onlySupported range 0001-01-01 → 9999-12-31; use 1582+ for reliable arithmetic.
TIME(p)Wall-clock time, default precision 9 (nanoseconds), 0–9 allowedNo time zone.
TIMESTAMP_NTZWall clock, no time zone, stored as givenTime zone is ignored on input and output.
TIMESTAMP_LTZUTC internallyDisplayed/computed in the session TIMEZONE parameter (account default America/Los_Angeles).
TIMESTAMP_TZUTC plus the offset suppliedStores the offset, not the region name — America/Los_Angeles becomes -08:00.
TIMESTAMPAlias onlyResolves via TIMESTAMP_TYPE_MAPPING, default TIMESTAMP_NTZ.

Other first-class types you should recognize: BOOLEAN, GEOGRAPHY, GEOMETRY, VECTOR, and the structured types ARRAY(type), OBJECT(key type), MAP(key,value) used mainly with Iceberg tables and typed query results.

Boolean

  • Values: TRUE, FALSE, NULL (three-valued logic).
  • Strings that convert to TRUE: 'true', 't', 'yes', 'y', 'on', '1' (case-insensitive); the false set mirrors it. Anything else errors.
  • Numbers: 0 → FALSE, any non-zero → TRUE.

Common traps

  • Choosing "TINYINT uses less storage" — all integer synonyms are NUMBER(38,0).
  • Believing FLOAT4 is 32-bit — it is not; every float type is a 64-bit double.
  • Thinking VARCHAR(50) is cheaper/faster than VARCHAR; it isn't, but it does enforce a constraint that can fail a load.
  • Confusing TIMESTAMP_TZ (offset retained) with TIMESTAMP_LTZ (rendered in session zone). If a question says "preserve the original UTC offset of each event," the answer is TIMESTAMP_TZ.
  • Assuming TIMESTAMP = TIMESTAMP_LTZ. Default mapping is NTZ.
  • Expecting CHAR(10) to pad with spaces.

Worked scenario. Requirement: store global IoT readings; analysts in three regions must each see the event time in their own local time, and the raw offset is irrelevant. Choice: TIMESTAMP_LTZ — the value is normalized to UTC and rendered per session TIMEZONE. TIMESTAMP_TZ would freeze the producer's offset; TIMESTAMP_NTZ would lose the zone entirely.

Unlock with a paid plan

8 more sections (about 3,518 words) continue below. Every paid plan unlocks all COF-C03 study guides, the full question bank and mock exams, from $4.97/month.

See plansAlready subscribed? Sign in
← Data loading, unloading, and transformationPerformance monitoring and optimization →