Profile Snowflake Columns by Year to Find Quality Problems

What you will have at the end

A single SQL file, profile_columns_by_year.sql. For every column of the tables you list, it builds a profile with one row per year plus one row for the whole table. It also builds a list of anything it could not profile, a view of the column-years that look wrong, and a table of drafted one-sentence descriptions, one per column. We ran it on two TPC-H sample tables (25 columns in all), first with two planted errors and then on clean data.

The year comes from a date column you name for each table. Without one, "year by year" has nothing to group by.

Step 1. Say which date column gives each table its year

The profile only covers tables listed in a small config table. Each row names a schema, a table and the date column that gives it its year. Here we use the order date for orders and the ship date for line items, both in a test schema called PROFILE_TEST_DATA. Unquoted names, which Snowflake stores in uppercase, match however you type them, so the line-item row is typed in lowercase on purpose. A name created in quotes with lowercase letters has to be typed exactly.

Your tables stay where they are. The helper objects go in a new schema of their own, which has to be in the same database as the tables you profile. With the test steps removed, CREATE SCHEMA stops with an error if a schema of that name already exists, so the file does not replace anything of yours. As downloaded, the test steps drop and recreate PROFILE_SNOWFLAKE_COLUMNS_BY_YEAR and PROFILE_TEST_DATA, so run it unchanged only in a database where neither schema is yours. PROFILE_SKIPPED lists anything the run could not profile, and why, and PROFILE_TARGETS holds the columns of each listed table.

SQL: the config table

CREATE SCHEMA PROFILE_SNOWFLAKE_COLUMNS_BY_YEAR;
USE SCHEMA PROFILE_SNOWFLAKE_COLUMNS_BY_YEAR;

CREATE OR REPLACE TABLE YEAR_COLUMNS (TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR, YEAR_COLUMN VARCHAR);
INSERT INTO YEAR_COLUMNS VALUES
  ('PROFILE_TEST_DATA', 'ORDERS', 'O_ORDERDATE'),
  ('profile_test_data', 'lineitem', 'l_shipdate');

CREATE OR REPLACE TABLE COLUMN_PROFILE (
  TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR, COLUMN_NAME VARCHAR, DATA_YEAR NUMBER, ALL_YEARS BOOLEAN,
  ROW_COUNT NUMBER, NULL_COUNT NUMBER, DISTINCT_COUNT NUMBER);
CREATE OR REPLACE TABLE PROFILE_SKIPPED (
  TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR, COLUMN_NAME VARCHAR, REASON VARCHAR);
CREATE OR REPLACE TABLE PROFILE_TARGETS (
  TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR, COLUMN_NAME VARCHAR, ORDINAL_POSITION NUMBER,
  DATA_TYPE VARCHAR, YEAR_COLUMN VARCHAR);

Step 2. Profile each listed table’s columns, one query per column, grouped by year

Snowflake’s INFORMATION_SCHEMA.COLUMNS view has a row for each column in the tables of the specified or current database (Snowflake: COLUMNS view (read 2026-10-16)). It also carries the table name, schema, position and data type, which the procedure uses. A Snowflake Scripting cursor loop walks a copy of those rows for the listed tables, kept in PROFILE_TARGETS, as the docs describe for cursor-based FOR loops (Snowflake: Scripting loops (read 2026-10-16)), and runs one profiling query per column.

SQL: the profiling procedure

CREATE OR REPLACE PROCEDURE PROFILE_COLUMNS()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  listed CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME, YEAR_COLUMN FROM YEAR_COLUMNS;
  c CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, YEAR_COLUMN FROM PROFILE_TARGETS
    ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
  bad_entry EXCEPTION (-20001, 'YEAR_COLUMNS has rows that match no table and column in this database; see PROFILE_SKIPPED');
  s VARCHAR; t VARCHAR; k VARCHAR; yr VARCHAR; ycol VARCHAR; err VARCHAR;
  n INTEGER DEFAULT 0;
  skipped INTEGER DEFAULT 0;
  missing INTEGER DEFAULT 0;
BEGIN
  DELETE FROM PROFILE_SKIPPED;
  DELETE FROM PROFILE_TARGETS;
  FOR y IN listed DO
    s := y.TABLE_SCHEMA; t := y.TABLE_NAME; yr := y.YEAR_COLUMN;
    INSERT INTO PROFILE_TARGETS
      SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, NULL
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA IN (:s, UPPER(:s)) AND TABLE_NAME IN (:t, UPPER(:t));
    ycol := NULL;
    SELECT MAX(COLUMN_NAME) INTO :ycol FROM PROFILE_TARGETS
      WHERE TABLE_SCHEMA IN (:s, UPPER(:s)) AND TABLE_NAME IN (:t, UPPER(:t))
        AND COLUMN_NAME IN (:yr, UPPER(:yr));
    IF (ycol IS NULL) THEN
      INSERT INTO PROFILE_SKIPPED VALUES (:s, :t, :yr, 'YEAR_COLUMNS row matches no table and column');
      missing := missing + 1;
    ELSE
      UPDATE PROFILE_TARGETS SET YEAR_COLUMN = :ycol
        WHERE TABLE_SCHEMA IN (:s, UPPER(:s)) AND TABLE_NAME IN (:t, UPPER(:t));
    END IF;
  END FOR;
  IF (missing > 0) THEN
    RAISE bad_entry;
  END IF;
  DELETE FROM COLUMN_PROFILE;
  FOR r IN c DO
    s := r.TABLE_SCHEMA; t := r.TABLE_NAME; k := r.COLUMN_NAME; yr := r.YEAR_COLUMN;
    BEGIN
      EXECUTE IMMEDIATE
        'INSERT INTO COLUMN_PROFILE SELECT ''' || s || ''', ''' || t || ''', ''' || k || ''', '
        || 'YEAR("' || yr || '"), GROUPING(YEAR("' || yr || '")) = 1, '
        || 'COUNT(*), COUNT_IF("' || k || '" IS NULL), COUNT(DISTINCT "' || k || '") '
        || 'FROM "' || s || '"."' || t || '" GROUP BY GROUPING SETS ((YEAR("' || yr || '")), ())';
      n := n + 1;
    EXCEPTION
      WHEN OTHER THEN
        err := SQLERRM;
        INSERT INTO PROFILE_SKIPPED VALUES (:s, :t, :k, :err);
        skipped := skipped + 1;
    END;
  END FOR;
  RETURN 'profiled ' || n || ' columns, skipped ' || skipped;
END;
$$;

Before it touches the profile, the procedure reads each listed table’s columns from INFORMATION_SCHEMA.COLUMNS, one YEAR_COLUMNS row at a time and filtered on that schema and table name. Snowflake can refuse an information schema query that returns too much data and asks for more selective predicates (Snowflake: Information Schema (read 2026-10-16)), so reading one table at a time keeps each read small in a large database. It then checks that each row’s date column exists. The queries it generates use the names as Snowflake stores them, qualified with the schema. Each column’s query groups by year and also once over the whole table (GROUPING SETS), so every column gets an ALL_YEARS row next to its yearly rows. A Snowflake Scripting exception handler around each query records a failed column in PROFILE_SKIPPED and moves on to the next (Snowflake: Handling exceptions (read 2026-10-16)).

Calling the procedure on our two tables:

Output from our run

PROFILE_COLUMNS               
------------------------------
profiled 25 columns, skipped 0

That is 9 columns of orders plus 16 of line items. Each column costs one query, which reads that column and the year column, so the work grows with the number of columns and rows. We kept the tables small on purpose and did not measure cost.

We also tested the two ways a run on your own tables can go wrong. First, a YEAR_COLUMNS row naming a date column that does not exist. The call stops with an error, names the row, and leaves the existing profile as it was (200 rows before the call, 200 after):

Output from our guard test: a YEAR_COLUMNS row that matches nothing

CALL PROFILE_COLUMNS()
Uncaught exception of type 'BAD_ENTRY' on line 34 at position 4 : YEAR_COLUMNS has rows that match no table and column in this database; see PROFILE_SKIPPED

TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | REASON
------------------+------------+-------------+---------------------------------------------
PROFILE_TEST_DATA | ORDERS     | O_SHIP_DATE | YEAR_COLUMNS row matches no table and column

Second, a column whose query fails. We added a third test table whose “date” column holds text, so its year cannot be read. Its two columns were skipped with Snowflake’s reason, and the other 25 were still profiled:

Output from our guard test: a table whose date column is text

PROFILE_COLUMNS
------------------------------
profiled 25 columns, skipped 2

TABLE_SCHEMA      | TABLE_NAME | COLUMN_NAME | REASON
------------------+------------+-------------+-----------------------------------------------------------
PROFILE_TEST_DATA | PLACES     | D           | SQL compilation error: Function EXTRACT does not support VARCHAR(10) argument type
PROFILE_TEST_DATA | PLACES     | K           | SQL compilation error: Function EXTRACT does not support VARCHAR(10) argument type

The downloadable file does not include these two guard tests.

Step 5. Draft a business description for every profiled column

The same profile can feed a first draft of a description for every column. The query below calls SNOWFLAKE.CORTEX.COMPLETE once per column row, in a single CREATE TABLE AS SELECT. Snowflake says model costs differ and that the AI functions are available to accounts in select regions (Snowflake: COMPLETE (read 2026-10-16); Snowflake: Cortex AI functions (read 2026-10-16)), so check both for your account. We used the llama3.1-8b model and did not measure the cost.

The prompt carries only the table name, column name, data type, and the share of NULLs and of distinct values over the whole table, taken from the ALL_YEARS rows. The model never sees a row of data. That keeps the prompt small, and it also bounds what the draft can know. The file also records the region the run used.

SQL: draft the descriptions

CREATE OR REPLACE TABLE COLUMN_DESCRIPTION_DRAFTS AS
WITH facts AS (
  SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
    ROUND(100 * NULL_COUNT / ROW_COUNT, 1) AS NULL_PCT,
    ROUND(100 * DISTINCT_COUNT / NULLIF(ROW_COUNT - NULL_COUNT, 0), 1) AS DISTINCT_PCT
  FROM COLUMN_PROFILE
  WHERE ALL_YEARS
)
SELECT f.TABLE_SCHEMA, f.TABLE_NAME, f.COLUMN_NAME, col.DATA_TYPE,
  TRIM(SNOWFLAKE.CORTEX.COMPLETE('llama3.1-8b',
    'You write one-sentence business descriptions of database columns for a data catalog. '
    || 'Table: ' || f.TABLE_NAME || '. Column: ' || f.COLUMN_NAME || '. Data type: ' || col.DATA_TYPE
    || '. Share of NULLs: ' || f.NULL_PCT || ' percent. Share of distinct values among non-NULL rows, over the whole table: '
    || COALESCE(f.DISTINCT_PCT::VARCHAR, 'not known') || ' percent (100 means every value is different)'
    || '. Reply with the sentence only, no more than 25 words, and do not invent facts the name does not suggest.'
  )) AS DRAFT_DESCRIPTION
FROM facts f
JOIN PROFILE_TARGETS col
  ON col.TABLE_SCHEMA = f.TABLE_SCHEMA AND col.TABLE_NAME = f.TABLE_NAME AND col.COLUMN_NAME = f.COLUMN_NAME;

Output from our run: the drafts for the nine orders columns (the file drafts all 25; we print the orders ones)

TABLE_NAME | COLUMN_NAME     | DRAFT_DESCRIPTION                                                                                                                         
-----------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------
ORDERS     | O_CLERK         | The O_CLERK column in the ORDERS table stores the name of the sales clerk associated with each order as a text string.                    
ORDERS     | O_COMMENT       | The O_COMMENT column in the ORDERS table contains a text description of each order, with every order having a unique comment.             
ORDERS     | O_CUSTKEY       | The O_CUSTKEY column uniquely identifies each customer in the ORDERS table, with a high degree of uniqueness among non-null values.       
ORDERS     | O_ORDERDATE     | The O_ORDERDATE column in the ORDERS table stores the date of each order, represented as a date value.                                    
ORDERS     | O_ORDERKEY      | The O_ORDERKEY column uniquely identifies each order in the ORDERS table, with a unique numeric value assigned to each order.             
ORDERS     | O_ORDERPRIORITY | The O_ORDERPRIORITY column stores the priority of each order in the ORDERS table, with a data type of TEXT.                               
ORDERS     | O_ORDERSTATUS   | The O_ORDERSTATUS column in the ORDERS table stores the current status of each order as a text value, with a very low degree of variation.
ORDERS     | O_SHIPPRIORITY  | The O_SHIPPRIORITY column in the ORDERS table stores a numerical value representing the shipping priority of each order.                  
ORDERS     | O_TOTALPRICE    | The O_TOTALPRICE column in the ORDERS table stores the total price of each order as a numeric value.                                      

Output from our run: region

REGION       
-------------
AWS_US_EAST_1

Read these as drafts, not definitions. At least one is wrong: O_CUSTKEY is described as uniquely identifying each customer. The prompt told the model that 97.7 percent of its values are distinct, and the whole-table row above shows 4,883 distinct keys across 5,000 orders, so some customers have more than one order. The column name suggests a customer key on an order, not a unique identifier. The model got this wrong from correct numbers. We did not check the other eight drafts against the data. The wording of one description (O_ORDERSTATUS) differed between our two runs. A data owner has to accept, edit or reject each line before it goes into a catalog, and nothing in this file writes the drafts back to Snowflake.