[Query] Daily DataLink Schema & Column Change Monitor

Hey everyone! I’ve been working with DataLink since February and wanted to kick off our new community board. My goal here is to share insights, tackle shared pain points, and help all of us—myself included—get the most out of DataLink.

To start things off, here is a query I’ve been using to handle schema monitoring:

The Problem

When DataLink schema updates occur, downstream reporting, ETLs, or custom integrations can silently fail or break unexpectedly. Because INFORMATION_SCHEMA only reflects current state, catching what actually shifted day-to-day requires tracking changes manually.

The Solution

This lightweight, read-only Snowflake query identifies DataLink tables created or altered within a rolling lookback window and outputs a clean JSON array of all associated columns, data types, and ordinal positions.

WITH AlteredTables AS (
    SELECT
        T.TABLE_SCHEMA,
        T.TABLE_NAME,
        T.CREATED,
        T.LAST_ALTERED AS TABLE_LAST_ALTERED
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.CREATED >= DATEADD(day, -1, CURRENT_TIMESTAMP()) 
       OR T.LAST_ALTERED >= DATEADD(day, -1, CURRENT_TIMESTAMP())
),
AllColumnsForAlteredTables AS (
    SELECT
        C.TABLE_SCHEMA,
        C.TABLE_NAME,
        C.COLUMN_NAME,
        C.DATA_TYPE,
        C.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN AlteredTables at_cols 
        ON C.TABLE_SCHEMA = at_cols.TABLE_SCHEMA 
       AND C.TABLE_NAME = at_cols.TABLE_NAME
)
SELECT
    at.TABLE_SCHEMA,
    at.TABLE_NAME,
    at.CREATED,
    at.TABLE_LAST_ALTERED,
    COALESCE(
        ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'column_name', ac.COLUMN_NAME,
                'data_type', ac.DATA_TYPE,
                'ordinal_position', ac.ORDINAL_POSITION
            )
        ) WITHIN GROUP (ORDER BY ac.ORDINAL_POSITION),
        ARRAY_CONSTRUCT()
    ) AS ALTERED_COLUMNS_DETAILS
FROM AlteredTables at
LEFT JOIN AllColumnsForAlteredTables ac 
    ON at.TABLE_SCHEMA = ac.TABLE_SCHEMA 
   AND at.TABLE_NAME = ac.TABLE_NAME
GROUP BY
    at.TABLE_SCHEMA,
    at.TABLE_NAME,
    at.CREATED,
    at.TABLE_LAST_ALTERED
ORDER BY
    at.TABLE_SCHEMA,
    at.TABLE_NAME;

How to Use & Customize It

• Lookback Window: Default is set to 1 day (DATEADD(day, -1, ...)). Update the integer in the CTE to extend the lookback window.

• Automated Notifications: Run this query via your preferred external orchestrator (Power Automate, Python, ADF) and trigger an alert to your communication hub or email distribution list whenever COUNT(TABLE_NAME) > 0.

• Historical Tracking: Since DataLink is read-only, INFORMATION_SCHEMA won't preserve deleted columns. To build a true change log, store query results daily in an external database and compare current results with the previous day's schema.

Hope this helps streamline schema monitoring! Let me know if you run into any questions or have ideas on tweaking it further.