Welcome To TechBrothersIT: Snowflake SQL Tutorial 129 - INFER_SCHEMA Sized My Columns To Four Rows Of Data

Snowflake SQL Tutorial 129 - INFER_SCHEMA Sized My Columns To Four Rows Of Data

Snowflake SQL · Episode 129

In this post we are going to cover INFER_SCHEMA in Snowflake, and CREATE TABLE ... USING TEMPLATE, which turns its answer straight into a table.

The idea is that you should not have to type out a CREATE TABLE just to load a file. You point INFER_SCHEMA at the file, it tells you the shape, and the template builds the table for you.

There is one thing to understand about the guess it makes, and the whole post is really about that one thing. The guess comes from the data in the file, not from the table the file came out of.

To show that, we start from a table we already know the shape of and unload it twice, so the right answer is never in doubt. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

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

On this page

Let's create a table and unload it twice
Let's look at the CSV line by line
Now let's ask the file what shape it is
What else does it return if we ask for everything?
Now let's ask the same question of the Parquet file
PARSE_HEADER is not SKIP_HEADER
Where it does refuse
Let's turn that answer into a table
A comparison of mine that answers the wrong question
And the table the Parquet built
Let's load the file into the table its own guess built
One last thing: the refusals, and the one that is not
And let's put everything back

Let's create a table and unload it twice

The script below makes a table with five columns and four rows in it, and the three file formats we are going to need. Nothing exotic in any of it.

Sample setup

-- Sample setup for this post.
-- One table, and two files unloaded out of it, so we know the right answer.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE STAGE STAGE_T129;

CREATE OR REPLACE TABLE SOURCE_T129 (
    SALE_ID   INT,
    CITY      VARCHAR(20),
    AMOUNT    NUMBER(10,2),
    SOLD_ON   DATE,
    IS_ONLINE BOOLEAN
);

INSERT INTO SOURCE_T129 (SALE_ID, CITY, AMOUNT, SOLD_ON, IS_ONLINE) VALUES
    (1, 'Toronto', 100.00, '2026-01-15', TRUE),
    (2, 'Boston',  250.50, '2026-02-20', FALSE),
    (3, 'Zurich',   75.25, '2026-03-05', TRUE),
    (4, 'London',  310.00, '2026-04-30', FALSE);

-- PARSE_HEADER reads the first line as column names.
CREATE OR REPLACE FILE FORMAT CSV_HEADED_T129
    TYPE = CSV
    PARSE_HEADER = TRUE
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    COMPRESSION = NONE;

-- SKIP_HEADER throws the first line away instead.
CREATE OR REPLACE FILE FORMAT CSV_SKIPPED_T129
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    COMPRESSION = NONE;

CREATE OR REPLACE FILE FORMAT PARQUET_T129
    TYPE = PARQUET;

Let's get the real shape of that table out of the catalog first, because this is what we will be comparing every guess against.

Query

SELECT COLUMN_NAME          AS THE_COLUMN,
       DATA_TYPE            AS THE_TYPE,
       NUMERIC_PRECISION    AS PRECISION,
       NUMERIC_SCALE        AS SCALE,
       CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SOURCE_T129'
ORDER BY ORDINAL_POSITION;

Result5 rows · 5 columns

THE_COLUMN	THE_TYPE	PRECISION	SCALE	MAX_LENGTH
SALE_ID	NUMBER	38	0	NULL
CITY	TEXT	NULL	NULL	20
AMOUNT	NUMBER	10	2	NULL
SOLD_ON	DATE	NULL	NULL	NULL
IS_ONLINE	BOOLEAN	NULL	NULL	NULL

SALE_ID is NUMBER with a precision of 38 and a scale of zero, which is what INT means in Snowflake. CITY is text with a maximum length of 20. AMOUNT is NUMBER(10,2). Then a date and a boolean.

Now let's write that table out as a CSV with a header row, and again as a Parquet file.

Query

COPY INTO @STAGE_T129/csv/ FROM SOURCE_T129
FILE_FORMAT = (FORMAT_NAME = 'CSV_HEADED_T129')
HEADER = TRUE
SINGLE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
4	194	194

Query

COPY INTO @STAGE_T129/parquet/ FROM SOURCE_T129
FILE_FORMAT = (FORMAT_NAME = 'PARQUET_T129');

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
4	1259	1259

Query

LIST @STAGE_T129/;

Result2 rows · 4 columns

name	size	md5	last_modified
stage_t129/csv/data	208	c317afc882adb74ce6d4e5f2948a10b1	Thu, 1 Oct 2026 18:14:06 GMT
stage_t129/parquet/data_0_0_0.snappy.parquet	1264	04d00582b3f24a11f094b5f1119476ce	Thu, 1 Oct 2026 18:14:06 GMT

Two files. The CSV is called data with no extension, which is what SINGLE = TRUE does to a name, as we saw in Episode 128. The listing reports them at 208 and 1264 bytes.

Let's look at the CSV line by line

Before we ask anything to guess, let's see what there is to guess from. We read it with the skip-header format, so the names line is not in the way.

Query

SELECT METADATA$FILE_ROW_NUMBER AS LINE_NUMBER,
       $1 AS C1, $2 AS C2, $3 AS C3, $4 AS C4, $5 AS C5
FROM @STAGE_T129/csv/ (FILE_FORMAT => 'CSV_SKIPPED_T129')
ORDER BY LINE_NUMBER;

Result4 rows · 6 columns · scroll →

LINE_NUMBER	C1	C2	C3	C4	C5
1	1	Toronto	100.00	2026-01-15	true
2	2	Boston	250.50	2026-02-20	false
3	3	Zurich	75.25	2026-03-05	true
4	4	London	310.00	2026-04-30	false

Four rows. One Toronto, two Boston, three Zurich, four London. The ids are single digits and the amounts run from 75.25 to 310.00.

Keep those ranges in mind. They are the only thing INFER_SCHEMA has to work with.

Now let's ask the file what shape it is

INFER_SCHEMA is a table function. It takes a location and a file format, and the format is the one with PARSE_HEADER = TRUE on it.

Query

SELECT COLUMN_NAME AS THE_COLUMN,
       TYPE        AS THE_INFERRED_TYPE,
       NULLABLE    AS IS_NULLABLE,
       ORDER_ID    AS POSITION
FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/csv/',
    FILE_FORMAT => 'CSV_HEADED_T129'))
ORDER BY POSITION;

Result5 rows · 4 columns

THE_COLUMN	THE_INFERRED_TYPE	IS_NULLABLE	POSITION
SALE_ID	NUMBER(1, 0)	TRUE	0
CITY	TEXT	TRUE	1
AMOUNT	NUMBER(5, 2)	TRUE	2
SOLD_ON	DATE	TRUE	3
IS_ONLINE	BOOLEAN	TRUE	4

Four columns back. The column name, the type it inferred, whether it is nullable, and the position.

The names are right. SALE_ID, CITY, AMOUNT, SOLD_ON, IS_ONLINE. It read them off the header line, which is what PARSE_HEADER is for.

Now have a look at the types, and compare them with the grid from the first section.

This is the whole thing to understand

SALE_ID came back as NUMBER(1, 0). The table it came out of has a precision of 38.
AMOUNT came back as NUMBER(5, 2). It was NUMBER(10,2).
It did not read the table. It read the rows in the file and sized the columns to fit them.

The ids are one to four, which fits in a single digit. The amounts need three digits and two decimals, which fits in five. That is exactly what it gave back, and on four rows of sample data it is a perfectly reasonable answer to a question nobody meant to ask.

What else does it return if we ask for everything?

We picked four columns above. Let's take the lot.

Query

SELECT * FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/csv/',
    FILE_FORMAT => 'CSV_HEADED_T129'));

Result5 rows · 6 columns · scroll →

COLUMN_NAME	TYPE	NULLABLE	EXPRESSION	FILENAMES	ORDER_ID
SALE_ID	NUMBER(1, 0)	TRUE	$1::NUMBER(1, 0)	csv/data	0
CITY	TEXT	TRUE	$2::TEXT	csv/data	1
AMOUNT	NUMBER(5, 2)	TRUE	$3::NUMBER(5, 2)	csv/data	2
SOLD_ON	DATE	TRUE	$4::DATE	csv/data	3
IS_ONLINE	BOOLEAN	TRUE	$5::BOOLEAN	csv/data	4

Six columns in all, so there are two we had not seen. FILENAMES, which says which file in the prefix the answer came from, and EXPRESSION, which is the one worth knowing about.

EXPRESSION holds the cast it would use: $1::NUMBER(1, 0), $2::TEXT, and so on. That is the actual mechanism, written out in a column. The guess is not advice, it is a list of casts.

Now let's ask the same question of the Parquet file

This one settles something we left open. Episode 125 unloaded a table straight to Parquet, read it back by field name, and got rows of NULL while the count said the rows were there. The names were wrong and I had not captured what they were instead, so I would not guess.

Here is the capture.

Query

SELECT COLUMN_NAME AS THE_COLUMN,
       TYPE        AS THE_INFERRED_TYPE,
       ORDER_ID    AS POSITION
FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/parquet/',
    FILE_FORMAT => 'PARQUET_T129'))
ORDER BY POSITION;

Result5 rows · 3 columns

THE_COLUMN	THE_INFERRED_TYPE	POSITION
_COL_0	NUMBER(38, 0)	0
_COL_1	TEXT	1
_COL_2	NUMBER(10, 2)	2
_COL_3	DATE	3
_COL_4	BOOLEAN	4

_COL_0, _COL_1, _COL_2, _COL_3, _COL_4. That is what COPY INTO named them when it unloaded the table. Positions, not names.

But now look at the types, which is the part I did not expect. NUMBER(38, 0) and NUMBER(10, 2), exactly what the table had. The Parquet file kept the precision perfectly. It just threw the names away.

Let's put the two guesses side by side.

Query

SELECT 'csv'      AS WHICH_FILE, COLUMN_NAME AS THE_COLUMN, TYPE AS THE_TYPE
FROM TABLE(INFER_SCHEMA(LOCATION => '@STAGE_T129/csv/',
                        FILE_FORMAT => 'CSV_HEADED_T129'))
UNION ALL
SELECT 'parquet', COLUMN_NAME, TYPE
FROM TABLE(INFER_SCHEMA(LOCATION => '@STAGE_T129/parquet/',
                        FILE_FORMAT => 'PARQUET_T129'))
ORDER BY WHICH_FILE, THE_COLUMN;

Result10 rows · 3 columns

WHICH_FILE	THE_COLUMN	THE_TYPE
csv	AMOUNT	NUMBER(5, 2)
csv	CITY	TEXT
csv	IS_ONLINE	BOOLEAN
csv	SALE_ID	NUMBER(1, 0)
csv	SOLD_ON	DATE
parquet	_COL_0	NUMBER(38, 0)
parquet	_COL_1	TEXT
parquet	_COL_2	NUMBER(10, 2)
parquet	_COL_3	DATE
parquet	_COL_4	BOOLEAN

The pattern in two lines

CSV keeps the names and loses the precision.
Parquet keeps the precision and loses the names.
Two formats, losing opposite halves of the same answer.

PARSE_HEADER is not SKIP_HEADER

There are two header options and they do different jobs. SKIP_HEADER = 1 throws the first line away. PARSE_HEADER = TRUE reads it as column names.

I wanted to show you both side by side out of the catalog, and that query does not work.

Query

SELECT FILE_FORMAT_NAME AS THE_FORMAT,
       SKIP_HEADER      AS SKIP_HEADER,
       PARSE_HEADER     AS PARSE_HEADER
FROM INFORMATION_SCHEMA.FILE_FORMATS
WHERE RIGHT(FILE_FORMAT_NAME, 5) = '_T129'
ORDER BY FILE_FORMAT_NAME;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 3 at position 7
invalid identifier 'PARSE_HEADER'

invalid identifier 'PARSE_HEADER'. CREATE FILE FORMAT takes the option quite happily, as the setup script at the top shows, but INFORMATION_SCHEMA.FILE_FORMATS has no column for it.

That is my mistake and it stays on the page, because it is the kind you only find by running it.

So let's show the difference the other way

Let's run INFER_SCHEMA again with the skip-header format instead of the parse-header one. I expected this to refuse.

Query

SELECT COLUMN_NAME AS THE_COLUMN, TYPE AS THE_INFERRED_TYPE
FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/csv/',
    FILE_FORMAT => 'CSV_SKIPPED_T129'));

Result5 rows · 2 columns

THE_COLUMN	THE_INFERRED_TYPE
c1	NUMBER(1, 0)
c2	TEXT
c3	NUMBER(5, 2)
c4	DATE
c5	BOOLEAN

It does not refuse. c1 to c5, with the same five types as before. So without PARSE_HEADER it still works out the shape. It just has no names to put on it.

Where it does refuse

There is a place where the missing names are fatal, and this is it. MATCH_BY_COLUMN_NAME on a load from a format with no PARSE_HEADER.

Query

COPY INTO FROM_CSV_T129
FROM @STAGE_T129/csv/
FILE_FORMAT = (FORMAT_NAME = 'CSV_SKIPPED_T129')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
FORCE = TRUE;

Query failed — Snowflake returned an error

002170 (0A000): SQL compilation error: match_by_column_name option is not supported for file format CSV without PARSE_HEADER = TRUE

match_by_column_name option is not supported for file format CSV without PARSE_HEADER = TRUE. Which names the option and the fix in the same sentence. A good error.

Let's turn that answer into a table

CREATE TABLE ... USING TEMPLATE takes a query that returns the schema as an array of objects. So we wrap the INFER_SCHEMA call in ARRAY_AGG(OBJECT_CONSTRUCT(*)).

Query

CREATE OR REPLACE TABLE FROM_CSV_T129
USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(
        LOCATION => '@STAGE_T129/csv/',
        FILE_FORMAT => 'CSV_HEADED_T129'))
);

Result1 row · 1 column

status
Table FROM_CSV_T129 successfully created.

That is the incantation, and it works. Now what did it build? Let's ask the catalog, with the precision and the scale this time.

Query

SELECT COLUMN_NAME          AS THE_COLUMN,
       DATA_TYPE            AS THE_TYPE,
       NUMERIC_PRECISION    AS PRECISION,
       NUMERIC_SCALE        AS SCALE,
       CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FROM_CSV_T129'
ORDER BY ORDINAL_POSITION;

Result5 rows · 5 columns

THE_COLUMN	THE_TYPE	PRECISION	SCALE	MAX_LENGTH
SALE_ID	NUMBER	1	0	NULL
CITY	TEXT	NULL	NULL	16777216
AMOUNT	NUMBER	5	2	NULL
SOLD_ON	DATE	NULL	NULL	NULL
IS_ONLINE	BOOLEAN	NULL	NULL	NULL

SALE_ID is NUMBER with a precision of one. AMOUNT is a precision of five and a scale of two. The guess came through into the table exactly as it was made.

And CITY is text with a maximum length of 16777216, where the original was VARCHAR(20). For the numbers it guessed too small, and for the text it took the maximum.

A comparison of mine that answers the wrong question

I wrote a query to check the new table against the old one. It joins the two sets of columns by name and prints a verdict on each one.

Query

SELECT o.COLUMN_NAME AS THE_COLUMN,
       o.DATA_TYPE   AS THE_ORIGINAL_TYPE,
       g.DATA_TYPE   AS THE_INFERRED_TYPE,
       IFF(o.DATA_TYPE = g.DATA_TYPE, 'same', 'DIFFERENT') AS VERDICT
FROM INFORMATION_SCHEMA.COLUMNS o
JOIN INFORMATION_SCHEMA.COLUMNS g
  ON UPPER(g.COLUMN_NAME) = UPPER(o.COLUMN_NAME)
 AND g.TABLE_NAME = 'FROM_CSV_T129'
WHERE o.TABLE_NAME = 'SOURCE_T129'
ORDER BY o.ORDINAL_POSITION;

Result5 rows · 4 columns

THE_COLUMN	THE_ORIGINAL_TYPE	THE_INFERRED_TYPE	VERDICT
SALE_ID	NUMBER	NUMBER	same
CITY	TEXT	TEXT	same
AMOUNT	NUMBER	NUMBER	same
SOLD_ON	DATE	DATE	same
IS_ONLINE	BOOLEAN	BOOLEAN	same

Same, same, same, same, same. Five rows and five verdicts of same.

And that is true, and it is useless.

Both are NUMBER. Both are TEXT. The type names match, and I compared DATA_TYPE and nothing else. The precision and the scale, which are the whole story here, are not in that comparison at all. The two grids in the section above are the honest answer.

Be careful what you compare

If you are checking an inferred schema against something you already have, compare NUMERIC_PRECISION, NUMERIC_SCALE and CHARACTER_MAXIMUM_LENGTH as well as DATA_TYPE. A verdict that only reads DATA_TYPE will tell you everything is fine when none of the sizes match.

And the table the Parquet built

Let's do the same thing with the Parquet guess, which had the opposite problem.

Query

CREATE OR REPLACE TABLE FROM_PARQUET_T129
USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(
        LOCATION => '@STAGE_T129/parquet/',
        FILE_FORMAT => 'PARQUET_T129'))
);

Result1 row · 1 column

status
Table FROM_PARQUET_T129 successfully created.

Query

SELECT COLUMN_NAME AS THE_COLUMN,
       DATA_TYPE   AS THE_TYPE,
       NUMERIC_PRECISION AS PRECISION,
       NUMERIC_SCALE     AS SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FROM_PARQUET_T129'
ORDER BY ORDINAL_POSITION;

Result5 rows · 4 columns

THE_COLUMN	THE_TYPE	PRECISION	SCALE
_COL_0	NUMBER	38	0
_COL_1	TEXT	NULL	NULL
_COL_2	NUMBER	10	2
_COL_3	DATE	NULL	NULL
_COL_4	BOOLEAN	NULL	NULL

A precision of 38 and a scale of zero. A precision of 10 and a scale of two. The sizes came through exactly right.

And the column names came through exactly as the file had them, which is the problem. You now have a correctly typed table whose columns are called _COL_0 to _COL_4.

Let's load the file into the table its own guess built

The CSV table has the right names, so we can load into it by name rather than by position.

Query

COPY INTO FROM_CSV_T129
FROM @STAGE_T129/csv/
FILE_FORMAT = (FORMAT_NAME = 'CSV_HEADED_T129')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Result1 row · 10 columns · scroll →

file	status	rows_parsed	rows_loaded	error_limit	errors_seen	first_error	first_error_line	first_error_character	first_error_column_name
stage_t129/csv/data	LOADED	4	4	1	0	NULL	NULL	NULL	NULL

One file, four parsed, four loaded, no errors.

Query

SELECT * FROM FROM_CSV_T129 ORDER BY 1;

Result4 rows · 5 columns

SALE_ID	CITY	AMOUNT	SOLD_ON	IS_ONLINE
1	Toronto	100.00	2026-01-15	TRUE
2	Boston	250.50	2026-02-20	FALSE
3	Zurich	75.25	2026-03-05	TRUE
4	London	310.00	2026-04-30	FALSE

All four rows. And look at the last two columns. The dates are dates and the booleans are booleans, not text, because INFER_SCHEMA worked that out and the template built the columns that way.

Query

SELECT (SELECT COUNT(*) FROM SOURCE_T129)     AS ROWS_IN_THE_ORIGINAL,
       (SELECT SUM(AMOUNT) FROM SOURCE_T129)  AS TOTAL_IN_THE_ORIGINAL,
       (SELECT COUNT(*) FROM FROM_CSV_T129)   AS ROWS_IN_THE_NEW_ONE;

Result1 row · 3 columns

ROWS_IN_THE_ORIGINAL	TOTAL_IN_THE_ORIGINAL	ROWS_IN_THE_NEW_ONE
4	735.75	4

Four rows in the original, 735.75 in the original, four rows in the new one. The same data in a different shape.

One last thing: the refusals, and the one that is not

First, INFER_SCHEMA on a prefix with nothing in it.

Query

SELECT COLUMN_NAME AS THE_COLUMN, TYPE AS THE_INFERRED_TYPE
FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/nothing_here/',
    FILE_FORMAT => 'CSV_HEADED_T129'));

Result0 rows · 2 columns

THE_COLUMN	THE_INFERRED_TYPE

Zero rows, and not an error. Which is the same answer reading an empty stage gave us in Episode 124. An empty prefix is a quiet nothing, so if your USING TEMPLATE suddenly builds a table with no columns, check the path before anything else.

Then a file format that does not exist.

Query

SELECT COLUMN_NAME AS THE_COLUMN, TYPE AS THE_INFERRED_TYPE
FROM TABLE(INFER_SCHEMA(
    LOCATION => '@STAGE_T129/csv/',
    FILE_FORMAT => 'NO_SUCH_FORMAT_T129'));

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
File format 'NO_SUCH_FORMAT_T129' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on FILE FORMAT NO_SUCH_FORMAT_T129.

And USING TEMPLATE over a query that is not a schema at all.

Query

CREATE OR REPLACE TABLE NONSENSE_T129
USING TEMPLATE (SELECT 1 AS NOT_A_SCHEMA);

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'Table function must be used in the TEMPLATE sub-query'.

Table function must be used in the TEMPLATE sub-query. So the template really does want an INFER_SCHEMA call in there, and not just anything that returns rows.

And let's put everything back

Three formats, three tables and a stage, all made by the script at the top.

Cleanup

-- Put the schema back the way we found it.
REMOVE @STAGE_T129/;

DROP STAGE       IF EXISTS STAGE_T129;
DROP FILE FORMAT IF EXISTS CSV_HEADED_T129;
DROP FILE FORMAT IF EXISTS CSV_SKIPPED_T129;
DROP FILE FORMAT IF EXISTS PARQUET_T129;
DROP TABLE       IF EXISTS SOURCE_T129;
DROP TABLE       IF EXISTS FROM_CSV_T129;
DROP TABLE       IF EXISTS FROM_PARQUET_T129;
DROP TABLE       IF EXISTS NONSENSE_T129;

What REMOVE took out

REMOVE @STAGE_T129/;

Result2 rows · 2 columns

name	result
stage_t129/csv/data	removed
stage_t129/parquet/data_0_0_0.snappy.parquet	removed

Query

LIST @STAGE_T129/;

Result0 rows · 4 columns

name	size	md5	last_modified

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T129') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE STAGE_NAME LIKE '%_T129%')      AS PROBE_STAGES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILE_FORMATS
        WHERE RIGHT(FILE_FORMAT_NAME, 5) = '_T129') AS PROBE_FORMATS_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 6 columns · scroll →

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	PROBE_STAGES_LEFT	PROBE_FORMATS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	0	10

No probe tables, no probe stages, no probe formats, and the database still reads.

Key points

INFER_SCHEMA sizes the columns from the data in the file, not from the table the file came out of.
On four rows of sample data, NUMBER(38,0) came back as NUMBER(1, 0) and NUMBER(10,2) came back as NUMBER(5, 2).
It returns six columns in all. The useful extras are EXPRESSION, which holds the cast it would use, and FILENAMES.
COPY INTO names Parquet columns _COL_0 onwards when it unloads a table, which is why reading one back by field name gives nulls.
CSV keeps the names and loses the precision. Parquet keeps the precision and loses the names.
SKIP_HEADER throws the first line away. PARSE_HEADER reads it as column names. They are not the same option and the catalog view does not report the second one.
INFER_SCHEMA works without PARSE_HEADER. It just calls the columns c1 to c5.
MATCH_BY_COLUMN_NAME on a CSV with no PARSE_HEADER = TRUE is refused, and the error names the fix.
CREATE TABLE ... USING TEMPLATE (SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) FROM TABLE(INFER_SCHEMA(...))) is the form that works.
An inferred VARCHAR(20) becomes text with a maximum length of 16777216.
Comparing DATA_TYPE alone said same five times out of five while none of the sizes matched. Compare the precision, the scale and the maximum length too.
INFER_SCHEMA on an empty prefix returns no rows rather than an error, so a template built from it builds nothing.

Next in this series

Episode 130 — COPY FILES. Moving files between stages without leaving SQL, and the one kind of stage it will not touch.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The sales data is made up for teaching. This episode writes no local files at all: both files it reads were unloaded from SOURCE_T129 inside the same script, so the right answer is known exactly rather than assumed. The byte counts are Snowflake's own report columns and the file sizes are its own listing column, and the two are different measurements of the same file. Cells reading NULL in the catalog grids are real nulls. Nothing on this page is timed and no performance claims are made anywhere in this series. The inference behaviour follows the Snowflake INFER_SCHEMA documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 129 - INFER_SCHEMA Sized My Columns To Four Rows Of Data

Let's create a table and unload it twice

Let's look at the CSV line by line

Now let's ask the file what shape it is

What else does it return if we ask for everything?

Now let's ask the same question of the Parquet file

PARSE_HEADER is not SKIP_HEADER

So let's show the difference the other way

Where it does refuse

Let's turn that answer into a table

A comparison of mine that answers the wrong question

And the table the Parquet built

Let's load the file into the table its own guess built

One last thing: the refusals, and the one that is not

And let's put everything back

Key points

No comments:

Post a Comment