Snowflake SQL Tutorial 97 - Data Types - Which Names Are Lies - Level 9 Opens

Snowflake SQL · Episode 97

In this post we are going to cover data types in Snowflake, and in particular which of their names are aliases for something else.

A data type is what a column promises to hold. You write the name in a CREATE TABLE and Snowflake stores a type. The useful question is not which types exist. It is which of the names you type every day get quietly turned into a different one on the way in.

This is the first post of Level 9, which is DDL — building the things our queries have been running against all along.

We will declare every name Snowflake accepts, one family at a time, and ask the catalog what it actually built.

Episode 97 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's set up somewhere to work
2. Let's declare every numeric name there is
3. So does SMALLINT constrain anything?
4. But the parameters are real
5. So what happens to 1.5 in a bare NUMBER column?
6. Let's prove the parameter is enforced
7. Now let's do the same thing with the string names
8. Does CHAR pad?
9. And is the length limit real?
10. One more thing about the default width
11. Now the dates and times
12. And the semi-structured types from Level 8
13. Can we ask for a type that does not exist?
14. Now let's put all four tables together
15. So what do we actually do with this?

Let's set up somewhere to work

There is no sample data to create for this post. Every table here is an empty shell with one job, which is to be looked at in the catalog, and we drop all of them at the end.

So all we need first is to point the session somewhere.

Where to run this

-- There is no sample table to build for this post. Every table on this page
-- gets created as we go and dropped again at the end, and all of them are
-- empty shells whose only job is to be looked at in the catalog.
-- Run this so the probe tables land somewhere you do not mind.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

Now let's start with the numbers.

Let's declare every numeric name there is

Here is a table with one column for every numeric name Snowflake accepts. In most other databases at least four of these would be different sizes.

CREATE TABLE

CREATE OR REPLACE TABLE NUM_SYNONYMS_T97 (
    AS_INT       INT,
    AS_INTEGER   INTEGER,
    AS_BIGINT    BIGINT,
    AS_SMALLINT  SMALLINT,
    AS_TINYINT   TINYINT,
    AS_BYTEINT   BYTEINT,
    AS_NUMBER    NUMBER,
    AS_DECIMAL   DECIMAL,
    AS_NUMERIC   NUMERIC
);

Result1 row · 1 column

status
Table NUM_SYNONYMS_T97 successfully created.

Now let's ask the catalog what it built, rather than assume it kept what we wrote.

Query

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'NUM_SYNONYMS_T97'
ORDER BY ORDINAL_POSITION;

Result9 rows · 4 columns

COLUMN_NAME	DATA_TYPE	NUMERIC_PRECISION	NUMERIC_SCALE
AS_INT	NUMBER	38	0
AS_INTEGER	NUMBER	38	0
AS_BIGINT	NUMBER	38	0
AS_SMALLINT	NUMBER	38	0
AS_TINYINT	NUMBER	38	0
AS_BYTEINT	NUMBER	38	0
AS_NUMBER	NUMBER	38	0
AS_DECIMAL	NUMBER	38	0
AS_NUMERIC	NUMBER	38	0

NUMBER, nine times. Precision 38, scale 0, nine times. TINYINT and BIGINT are the same column as each other and the same column as NUMBER.

That is a wall of the same word, so let's count it instead of reading it.

Query

SELECT COUNT(*)                                                   AS COLUMNS_DECLARED,
       COUNT(DISTINCT DATA_TYPE)                                  AS DISTINCT_TYPES,
       COUNT(DISTINCT DATA_TYPE || '(' || NUMERIC_PRECISION || ','
             || NUMERIC_SCALE || ')')                             AS DISTINCT_SHAPES
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'NUM_SYNONYMS_T97';

Result1 row · 3 columns

COLUMNS_DECLARED	DISTINCT_TYPES	DISTINCT_SHAPES
9	1	1

Nine columns declared, one distinct type, one distinct shape. And DESCRIBE TABLE agrees, in the form you would paste into a document.

Query

DESCRIBE TABLE NUM_SYNONYMS_T97;

Result9 rows · 13 columns · scroll →

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain	write default
AS_INT	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_INTEGER	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_BIGINT	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_SMALLINT	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_TINYINT	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_BYTEINT	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_NUMBER	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_DECIMAL	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
AS_NUMERIC	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL

NUMBER(38,0) on every row.

So does SMALLINT constrain anything?

That is the question the catalog raises. If they are all the same type, the small-sounding names cannot be enforcing anything. Let's test it with a value far too big for a small integer.

INSERT

INSERT INTO NUM_SYNONYMS_T97 (AS_SMALLINT, AS_TINYINT, AS_BYTEINT)
SELECT 99999999999, 99999999999, 99999999999;

Result1 row · 1 column

number of rows inserted
1

One row inserted, into the SMALLINT, TINYINT and BYTEINT columns. No complaint. Let's read it back.

Query

SELECT AS_SMALLINT, AS_TINYINT, AS_BYTEINT,
       AS_SMALLINT = 99999999999 AS IT_KEPT_THE_WHOLE_VALUE
FROM NUM_SYNONYMS_T97;

Result1 row · 4 columns

AS_SMALLINT	AS_TINYINT	AS_BYTEINT	IT_KEPT_THE_WHOLE_VALUE
99999999999	99999999999	99999999999	TRUE

All three kept the whole eleven-digit value, and the last column confirms it is exactly what we sent. No truncation, no warning, no error, because all three columns are NUMBER(38,0).

What that means for your DDL

A schema that carefully declares SMALLINT for a small column is decoration. It constrains nothing, and it tells the next person who reads the DDL something that is not true. If you want a column limited to small values, the type name will not do it for you.

But the parameters are real

Now the other half of this, or we will take the wrong lesson out of the post. The names are interchangeable. The parameters are not.

Let's build a second table: a bare NUMBER, a NUMBER(12,2), and the three names for a floating point column.

CREATE TABLE

CREATE OR REPLACE TABLE SCALE_PROBE_T97 (
    BARE_NUMBER   NUMBER,
    TWO_DECIMALS  NUMBER(12,2),
    AS_FLOAT      FLOAT,
    AS_DOUBLE     DOUBLE,
    AS_REAL       REAL
);

Result1 row · 1 column

status
Table SCALE_PROBE_T97 successfully created.

Query

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'SCALE_PROBE_T97'
ORDER BY ORDINAL_POSITION;

Result5 rows · 4 columns

COLUMN_NAME	DATA_TYPE	NUMERIC_PRECISION	NUMERIC_SCALE
BARE_NUMBER	NUMBER	38	0
TWO_DECIMALS	NUMBER	12	2
AS_FLOAT	FLOAT	NULL	NULL
AS_DOUBLE	FLOAT	NULL	NULL
AS_REAL	FLOAT	NULL	NULL

Five columns. The bare NUMBER came back as precision 38, scale 0. The NUMBER(12,2) kept 12 and 2, exactly as written. So the parameters survive where the names do not.

And FLOAT, DOUBLE and REAL all came back as FLOAT, with precision and scale both NULL. Not scale 0 — nothing at all. A float does not have a scale to report, which is exactly why it can hold a decimal we never asked it to round.

So what happens to 1.5 in a bare NUMBER column?

Scale 0 means no digits after the point. We never asked for that, we just left the parameter out. Let's put a decimal into all five columns and see.

INSERT

INSERT INTO SCALE_PROBE_T97 (BARE_NUMBER, TWO_DECIMALS, AS_FLOAT, AS_DOUBLE, AS_REAL)
SELECT 1.5, 1.5, 1.5, 1.5, 1.5;

Result1 row · 1 column

number of rows inserted
1

And a second row, because the half-way case is the interesting one.

INSERT

INSERT INTO SCALE_PROBE_T97 (BARE_NUMBER, TWO_DECIMALS, AS_FLOAT, AS_DOUBLE, AS_REAL)
SELECT 2.5, 2.5, 2.5, 2.5, 2.5;

Result1 row · 1 column

number of rows inserted
1

Both rows went in without a word. Now let's read the two columns back side by side.

Query

SELECT BARE_NUMBER, TWO_DECIMALS, AS_FLOAT,
       BARE_NUMBER = 2      AS ROUNDED_TO_2,
       BARE_NUMBER = 1      AS TRUNCATED_TO_1,
       BARE_NUMBER = 3      AS ROUNDED_TO_3
FROM SCALE_PROBE_T97
ORDER BY TWO_DECIMALS;

Result2 rows · 6 columns · scroll →

BARE_NUMBER	TWO_DECIMALS	AS_FLOAT	ROUNDED_TO_2	TRUNCATED_TO_1	ROUNDED_TO_3
2	1.50	1.5	TRUE	FALSE	FALSE
3	2.50	2.5	FALSE	FALSE	TRUE

1.5 became 2. 2.5 became 3. The NUMBER(12,2) column beside it kept both exactly, as 1.50 and 2.50, and the float column kept them too.

The last three columns are there to settle how it rounded. ROUNDED_TO_2 is TRUE on the first row, ROUNDED_TO_3 is TRUE on the second, and TRUNCATED_TO_1 is FALSE on both.

So it rounds, it does not truncate, and it rounds half away from zero. Banker's rounding would have sent 2.5 to 2. This sends it to 3. And nothing warned either time.

We have met this before

That is the third sighting of the same silent rounding in this course. Episode 89 found it in a VARIANT cast and Episode 94 found it in AS_DECIMAL. Here it is in a column definition. Same defect, three different places, and never a warning.

Which makes the parameter you leave out a decision rather than a blank. Never write a bare NUMBER for anything with a decimal point in it.

Let's prove the parameter is enforced

We have shown the parameter is stored. Let's show it is enforced, with a value too wide for NUMBER(12,2).

INSERT — refused

INSERT INTO SCALE_PROBE_T97 (TWO_DECIMALS) SELECT 12345678901234;

Query failed — Snowflake returned an error

100046 (22003): DML operation to table SCALE_PROBE_T97 failed on column TWO_DECIMALS with error: Number out of representable range: type FIXED[SB8](12,2){nullable}, value 12345678901234

Number out of representable range. It refused the row, and the message names the column and prints the type it was checking against.

So the limit you write yourself is a real constraint. The type name you write is not.

Now let's do the same thing with the string names

Strings next. Here is a table with every string name, two of them given a length, plus the two binary names.

CREATE TABLE

CREATE OR REPLACE TABLE STR_SYNONYMS_T97 (
    AS_VARCHAR   VARCHAR,
    AS_STRING    STRING,
    AS_TEXT      TEXT,
    AS_CHAR      CHAR,
    AS_CHAR5     CHAR(5),
    AS_VARCHAR5  VARCHAR(5),
    AS_NVARCHAR  NVARCHAR,
    AS_BINARY    BINARY,
    AS_VARBINARY VARBINARY
);

Result1 row · 1 column

status
Table STR_SYNONYMS_T97 successfully created.

Query

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'STR_SYNONYMS_T97'
ORDER BY ORDINAL_POSITION;

Result9 rows · 3 columns

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH
AS_VARCHAR	TEXT	16777216
AS_STRING	TEXT	16777216
AS_TEXT	TEXT	16777216
AS_CHAR	TEXT	1
AS_CHAR5	TEXT	5
AS_VARCHAR5	TEXT	5
AS_NVARCHAR	TEXT	16777216
AS_BINARY	BINARY	NULL
AS_VARBINARY	BINARY	NULL

Seven of those columns came back as TEXT. VARCHAR, STRING, TEXT, CHAR and NVARCHAR are all one type, the same way the nine numeric names were. The two binary columns came back as BINARY with no maximum length reported.

But look at the length column, because this is where the string family differs from the numeric one. CHAR(5) is 5 and VARCHAR(5) is 5. Those lengths survived.

And the bare CHAR is 1.

Does CHAR pad?

That is the real question about CHAR, and if you came from another database you already have an answer in mind. Let's put two characters into a CHAR(5) and a VARCHAR(5) and compare them.

INSERT

INSERT INTO STR_SYNONYMS_T97 (AS_CHAR5, AS_VARCHAR5, AS_VARCHAR)
SELECT 'ab', 'ab', 'ab';

Result1 row · 1 column

number of rows inserted
1

Query

SELECT AS_CHAR5, AS_VARCHAR5,
       LENGTH(AS_CHAR5)              AS CHAR5_LENGTH,
       LENGTH(AS_VARCHAR5)           AS VARCHAR5_LENGTH,
       AS_CHAR5 = AS_VARCHAR5        AS THEY_ARE_EQUAL,
       AS_CHAR5 = 'ab   '            AS EQUALS_THE_PADDED_FORM
FROM STR_SYNONYMS_T97;

Result1 row · 6 columns · scroll →

AS_CHAR5	AS_VARCHAR5	CHAR5_LENGTH	VARCHAR5_LENGTH	THEY_ARE_EQUAL	EQUALS_THE_PADDED_FORM
ab	ab	2	2	TRUE	FALSE

CHAR(5) gave us ab, and LENGTH says 2. Not 5. The VARCHAR(5) copy is the same length, and THEY_ARE_EQUAL is TRUE.

The last column is the one that settles it. Compared against 'ab' with three spaces after it, CHAR(5) says FALSE. There are no spaces in there to match.

So CHAR does not pad. CHAR is VARCHAR with a limit, and nothing else. If you came from a database where CHAR pads to its declared width, unlearn that here.

And is the length limit real?

We know the length is stored. Let's find out whether it is enforced, with a string far too long for VARCHAR(5).

INSERT — refused

INSERT INTO STR_SYNONYMS_T97 (AS_VARCHAR5) SELECT 'far too long for five';

Query failed — Snowflake returned an error

100078 (22000): DML operation to table STR_SYNONYMS_T97 failed on column AS_VARCHAR5 with error: String 'far too long for five' is too long and would be truncated

String 'far too long for five' is too long and would be truncated. Read those two words. Not was truncated. It refused the row rather than quietly shortening the value, which is the behaviour you want.

One more thing about the default width

Back up a section. The bare CHAR came back as 1 while the other bare names came back as something much larger. Let's put those four side by side on their own.

Query

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH,
       CHARACTER_MAXIMUM_LENGTH = 16777216 AS IT_IS_SIXTEEN_MEGABYTES
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'STR_SYNONYMS_T97'
  AND COLUMN_NAME IN ('AS_VARCHAR', 'AS_STRING', 'AS_TEXT', 'AS_CHAR')
ORDER BY COLUMN_NAME;

Result4 rows · 3 columns

COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	IT_IS_SIXTEEN_MEGABYTES
AS_CHAR	1	FALSE
AS_STRING	16777216	TRUE
AS_TEXT	16777216	TRUE
AS_VARCHAR	16777216	TRUE

VARCHAR, STRING and TEXT with no length are all 16777216, which is sixteen megabytes. The bare CHAR holds one character.

The one that catches people

Three of those four names are enormous and the fourth holds a single character, and the difference is invisible in the DDL: X CHAR and X VARCHAR look equally harmless side by side. Write CHAR(n) if you mean CHAR, and write VARCHAR(n) whenever you actually know the n.

Now the dates and times

Seven columns this time, covering the date and time names.

CREATE TABLE

CREATE OR REPLACE TABLE TIME_SYNONYMS_T97 (
    AS_DATE          DATE,
    AS_TIME          TIME,
    AS_DATETIME      DATETIME,
    AS_TIMESTAMP     TIMESTAMP,
    AS_TIMESTAMP_NTZ TIMESTAMP_NTZ,
    AS_TIMESTAMP_LTZ TIMESTAMP_LTZ,
    AS_TIMESTAMP_TZ  TIMESTAMP_TZ
);

Result1 row · 1 column

status
Table TIME_SYNONYMS_T97 successfully created.

Query

SELECT COLUMN_NAME, DATA_TYPE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TIME_SYNONYMS_T97'
ORDER BY ORDINAL_POSITION;

Result7 rows · 3 columns

COLUMN_NAME	DATA_TYPE	DATETIME_PRECISION
AS_DATE	DATE	NULL
AS_TIME	TIME	9
AS_DATETIME	TIMESTAMP_NTZ	9
AS_TIMESTAMP	TIMESTAMP_NTZ	9
AS_TIMESTAMP_NTZ	TIMESTAMP_NTZ	9
AS_TIMESTAMP_LTZ	TIMESTAMP_LTZ	9
AS_TIMESTAMP_TZ	TIMESTAMP_TZ	9

DATE is DATE and TIME is TIME, with a precision of 9. Then the interesting pair: DATETIME came back as TIMESTAMP_NTZ, and so did plain TIMESTAMP.

NTZ means no time zone. It stores what you gave it and nothing else. TIMESTAMP_LTZ and TIMESTAMP_TZ kept their own names, so those three really are different types.

Which one plain TIMESTAMP maps to is a parameter, not a law. Let's look at it.

SHOW PARAMETERS

SHOW PARAMETERS LIKE 'TIMESTAMP_TYPE_MAPPING';

Result1 row · 6 columns · scroll →

key	value	default	level	description	type
TIMESTAMP_TYPE_MAPPING	TIMESTAMP_NTZ	TIMESTAMP_NTZ		If TIMESTAMP type is used, what specific TIMESTAMP* type it should map to: TIMESTAMP_LTZ, TIMESTAMP_NTZ (default) or TIMESTAMP_TZ	STRING

The value is TIMESTAMP_NTZ and the default is TIMESTAMP_NTZ. Have a look at the level column: it is marked empty rather than NULL, and an empty level is SHOW PARAMETERS telling us nobody has set this anywhere. The default is simply in force.

It can be set, at account level or on a session, and somebody may have set it on the account you work on. Worth knowing it exists before you assume what TIMESTAMP means in somebody else's schema.

And the semi-structured types from Level 8

One more family. These are the types the whole of Level 8 was about.

CREATE TABLE

CREATE OR REPLACE TABLE SEMI_TYPES_T97 (
    AS_VARIANT   VARIANT,
    AS_OBJECT    OBJECT,
    AS_ARRAY     ARRAY,
    AS_BOOLEAN   BOOLEAN,
    AS_GEOGRAPHY GEOGRAPHY,
    AS_GEOMETRY  GEOMETRY
);

Result1 row · 1 column

status
Table SEMI_TYPES_T97 successfully created.

Query

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'SEMI_TYPES_T97'
ORDER BY ORDINAL_POSITION;

Result6 rows · 3 columns

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
AS_VARIANT	VARIANT	YES
AS_OBJECT	OBJECT	YES
AS_ARRAY	ARRAY	YES
AS_BOOLEAN	BOOLEAN	YES
AS_GEOGRAPHY	GEOGRAPHY	YES
AS_GEOMETRY	GEOMETRY	YES

All six kept their own names. Nothing collapsed here at all, which makes sense, because none of these is a synonym for any of the others.

Can we ask for a type that does not exist?

Everything so far has been accepted, one way or another. Let's find out whether the list of names is actually closed, with a name from a different database.

CREATE TABLE — refused

CREATE OR REPLACE TABLE BAD_TYPE_T97 (X BLOB);

Query failed — Snowflake returned an error

002040 (42601): SQL compilation error:
Unsupported data type 'BLOB'.

Unsupported data type 'BLOB'. A clean refusal. So the list is closed: if the CREATE TABLE compiles, the name you wrote is a real Snowflake type or a real alias for one.

Now let's put all four tables together

We have declared four tables' worth of names. Let's group every column in all four by the type Snowflake actually stored.

Query

SELECT DATA_TYPE,
       COUNT(*)                          AS COLUMNS_WITH_IT,
       ARRAY_AGG(COLUMN_NAME)            AS WHICH_COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME IN ('NUM_SYNONYMS_T97', 'STR_SYNONYMS_T97',
                     'TIME_SYNONYMS_T97', 'SEMI_TYPES_T97')
GROUP BY DATA_TYPE
ORDER BY COLUMNS_WITH_IT DESC, DATA_TYPE;

Result14 rows · 3 columns

DATA_TYPE	COLUMNS_WITH_IT	WHICH_COLUMNS
NUMBER	9	[ "AS_NUMERIC", "AS_INT", "AS_INTEGER", "AS_BYTEINT", "AS_NUMBER", "AS_TINYINT", "AS_BIGINT", "AS_DECIMAL", "AS_SMALLINT" ]
TEXT	7	[ "AS_NVARCHAR", "AS_STRING", "AS_TEXT", "AS_CHAR", "AS_CHAR5", "AS_VARCHAR", "AS_VARCHAR5" ]
TIMESTAMP_NTZ	3	[ "AS_TIMESTAMP_NTZ", "AS_DATETIME", "AS_TIMESTAMP" ]
BINARY	2	[ "AS_VARBINARY", "AS_BINARY" ]
ARRAY	1	[ "AS_ARRAY" ]
BOOLEAN	1	[ "AS_BOOLEAN" ]
DATE	1	[ "AS_DATE" ]
GEOGRAPHY	1	[ "AS_GEOGRAPHY" ]
GEOMETRY	1	[ "AS_GEOMETRY" ]
OBJECT	1	[ "AS_OBJECT" ]
TIME	1	[ "AS_TIME" ]
TIMESTAMP_LTZ	1	[ "AS_TIMESTAMP_LTZ" ]
TIMESTAMP_TZ	1	[ "AS_TIMESTAMP_TZ" ]
VARIANT	1	[ "AS_VARIANT" ]

Fourteen rows. NUMBER with nine columns, TEXT with seven, TIMESTAMP_NTZ with three and BINARY with two. Then ten more types holding one column each.

The WHICH_COLUMNS array on the first row is the whole post in one cell: every numeric name we can type, collected under NUMBER.

And the summary, in one query.

Query

SELECT COUNT(*)                     AS NAMES_I_DECLARED,
       COUNT(DISTINCT DATA_TYPE)    AS TYPES_SNOWFLAKE_KEPT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME IN ('NUM_SYNONYMS_T97', 'STR_SYNONYMS_T97',
                     'TIME_SYNONYMS_T97', 'SEMI_TYPES_T97');

Result1 row · 2 columns

NAMES_I_DECLARED	TYPES_SNOWFLAKE_KEPT
31	14

Thirty-one names declared, fourteen types stored. That leaves seventeen of the names as aliases for something else.

So what do we actually do with this?

Four things, and none of them is complicated.

1. Pick one name per type and use it everywhere. NUMBER or INT, VARCHAR or STRING. It genuinely does not matter which, because they are the same type. Consistency is the entire benefit.
2. Put the constraint in the parameter, because that is the part that is enforced. NUMBER(12,2) if you mean money. VARCHAR(50) if you mean fifty.
3. Never write a bare NUMBER for anything with a decimal point, because a bare NUMBER is scale 0 and it will round your value without a word.
4. Do not write SMALLINT to mean small. It does not mean small. It means NUMBER(38,0), and it misleads whoever reads the DDL next.

Let's put the four probe tables away.

Cleanup

DROP TABLE IF EXISTS NUM_SYNONYMS_T97;

Result1 row · 1 column

status
NUM_SYNONYMS_T97 successfully dropped.

Cleanup

DROP TABLE IF EXISTS SCALE_PROBE_T97;

Result1 row · 1 column

status
SCALE_PROBE_T97 successfully dropped.

Cleanup

DROP TABLE IF EXISTS STR_SYNONYMS_T97;

Result1 row · 1 column

status
STR_SYNONYMS_T97 successfully dropped.

Cleanup

DROP TABLE IF EXISTS TIME_SYNONYMS_T97;

Result1 row · 1 column

status
TIME_SYNONYMS_T97 successfully dropped.

Cleanup

DROP TABLE IF EXISTS SEMI_TYPES_T97;

Result1 row · 1 column

status
SEMI_TYPES_T97 successfully dropped.

Five statements, because the scale probe was a table of its own. Everything this post created is gone again.

Key points

• All nine numeric names — INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT, NUMBER, DECIMAL, NUMERIC — are stored as NUMBER(38,0).
• SMALLINT holds an eleven-digit value without complaint. Declaring it constrains nothing and misleads the next reader.
• The parameters you write are enforced. NUMBER(12,2) refuses an out-of-range value and VARCHAR(5) refuses a long string, saying it would be truncated rather than truncating it.
• A bare NUMBER is scale 0, so 1.5 stores as 2 and 2.5 stores as 3 — half away from zero, not banker's rounding, and silent both times.
• FLOAT, DOUBLE and REAL are all FLOAT, with no precision and no scale reported at all.
• VARCHAR, STRING, TEXT, CHAR and NVARCHAR are all stored as TEXT, but the length you declare survives.
• CHAR does not pad. 'ab' in a CHAR(5) has length 2 and is not equal to 'ab' with spaces. It is VARCHAR with a limit.
• A bare CHAR is one character, while bare VARCHAR, STRING and TEXT are 16777216 — sixteen megabytes.
• DATETIME and plain TIMESTAMP are both TIMESTAMP_NTZ, and which one plain TIMESTAMP maps to is a parameter. TIMESTAMP_LTZ and TIMESTAMP_TZ keep their own identities.
• VARIANT, OBJECT, ARRAY, BOOLEAN, GEOGRAPHY and GEOMETRY all survive intact.
• A name that is not a type is refused outright: Unsupported data type 'BLOB'.
• Thirty-one names in, fourteen types out. Pick one name per type, and put the real constraint in the parameter.

Next in this series

Episode 98 — databases: CREATE, ALTER, DROP and UNDROP. One of those statements is destructive in a way its name does not warn you about, and UNDROP cannot get you out of it.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, and that includes the three refusals. The four probe tables exist only to be read out of the catalog, and the last section drops all of them. Every count in the text above is read off one of the grids on this page, including the catalog's own report that the three floating point columns have no precision and no scale rather than a scale of zero. Nothing is timed and no performance claims are made anywhere in this series. Type behaviour follows the Snowflake data type documentation.