<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vishal Verma on Medium]]></title>
        <description><![CDATA[Stories by Vishal Verma on Medium]]></description>
        <link>https://medium.com/@vishalverma_us?source=rss-423de3fae95b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*CIduaHHe2gAlpzkmLAjchQ.jpeg</url>
            <title>Stories by Vishal Verma on Medium</title>
            <link>https://medium.com/@vishalverma_us?source=rss-423de3fae95b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:37:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vishalverma_us/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Snowflake: Detecting Non-UTF-8 Characters in your data]]></title>
            <link>https://medium.com/@vishalverma_us/snowflake-detecting-non-utf-8-characters-in-your-data-98fa95ade638?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/98fa95ade638</guid>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[snowpark]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[utf-8]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Thu, 30 Jul 2026 18:04:44 GMT</pubDate>
            <atom:updated>2026-08-12T17:52:50.694Z</atom:updated>
            <content:encoded><![CDATA[<h3>The Problem</h3><p>UTF-8 is the universal standard for text encoding across modern data platforms, APIs, and applications. When your data contains characters that violate UTF-8 encoding rules, you face issues across multiple fronts:</p><ul><li><strong>Data application compatibility</strong> — BI tools, OLE DB connectors, REST APIs, and downstream consumers may reject or misrender non-UTF-8 data</li><li><strong>Open format migration</strong> — Moving to Iceberg, Delta, or any Parquet-based table format enforces strict UTF-8 validation at write time, causing pipeline failures on data that was previously accepted</li><li><strong>Data quality</strong> — Non-UTF-8 characters (control characters, replacement markers, orphaned byte sequences) represent corrupted or improperly encoded data that degrades analytical accuracy</li><li><strong>Global standards compliance</strong> — Unicode UTF-8 (RFC 3629) is the mandated encoding for web protocols, cloud APIs, and international data exchange</li><li><strong>Organizational policy</strong> — Enterprise data governance often requires all persisted data to be in a validated, portable encoding to ensure interoperability across teams, regions, and systems</li></ul><p>The challenge: legacy data loaded via older connectors (OLEDB, ODBC, bulk loaders) or from source systems with different encodings (Latin-1, Windows-1252, Shift-JIS) may have introduced non-UTF-8 bytes that went undetected for years. These bytes sit quietly in your tables until you try to export, migrate, or share the data — and then they break things.</p><p>Any character that violates UTF-8 encoding rules may cause the migration (CTAS or INSERT INTO) to fail with errors like:</p><ul><li>Invalid UTF8 detected in string...</li><li>Parquet row group write failed: invalid UTF-8</li></ul><h3>What Characters Are Problematic?</h3><p>Not all non-ASCII characters are a problem. Characters like é, ü, ñ, € are non-ASCII but perfectly valid UTF-8. The real issues come from:</p><ul><li><strong>U+FFFD</strong> — Unicode Replacement Character (injected when REPLACE_INVALID_CHARACTERS=TRUE was used during prior loads)</li><li><strong>C0 control characters</strong> — NULL byte (0x00), BEL (0x07), BS (0x08), and others in the 0x00–0x1F range (except TAB, LF, CR which are safe)</li><li><strong>C1 control characters</strong> — the U+0080 to U+009F range, commonly introduced when Windows-1252 encoded data is misinterpreted as UTF-8</li><li><strong>Surrogate code points</strong> — U+D800 to U+DFFF, which should never appear in valid UTF-8 text</li></ul><h3>The Solution: A Scan-Only Stored Procedure</h3><p>I built a Python stored procedure that scans any Snowflake table for ALL of the above problematic characters — universally, using code point range checks (not hardcoded specific characters). It does NOT modify your source data intentially as modification may require standard company policies to follow for compliance and process.</p><p><strong>What it does:</strong></p><ul><li>Creates a helper Python UDF that validates every character in every string column</li><li>Identifies all STRING/VARCHAR columns automatically</li><li>Scans every row for ANY problematic character</li><li>Copies affected rows into a separate report table ({TABLE}_NONUTF8_ROWS) for review</li><li>Returns a JSON summary with counts and affected column breakdown</li></ul><p><strong>What it does NOT do:</strong></p><ul><li>Does NOT update, modify, or delete anything in the source table</li><li>Does NOT require you to know which specific characters to look for</li><li>Does NOT flag valid non-ASCII UTF-8 characters (accents, umlauts, CJK, etc.)</li></ul><h3>The Detection Logic</h3><p>The core of the detection is a Python UDF that iterates every character and checks its Unicode code point against known problematic ranges:</p><pre>def is_clean(hex_str):<br>    if hex_str is None or hex_str == &quot;&quot;:<br>        return True<br>    try:<br>        raw_bytes = bytes.fromhex(hex_str)<br>        text = raw_bytes.decode(&quot;utf-8&quot;, errors=&quot;strict&quot;)<br>        for ch in text:<br>            cp = ord(ch)<br>            if cp == 0xFFFD: return False<br>            if cp == 0x0000: return False<br>            if cp &lt;= 0x08 or cp == 0x0B or cp == 0x0C or (0x0E &lt;= cp &lt;= 0x1F): return False<br>            if 0x0080 &lt;= cp &lt;= 0x009F: return False<br>            if 0xD800 &lt;= cp &lt;= 0xDFFF: return False<br>        return True<br>    except Exception:<br>        return False</pre><p><em>Note: I updated code to scan raw data as hex string to avoid any encoding issue while reading data by Snowflake engine and passing it to python runtime. Code now has better support for utf-8 detections.</em></p><p>This covers 2,110 problematic code points via range logic. Any character outside these ranges passes through — including all legitimate international characters.</p><h3>The Procedure</h3><pre>CREATE OR REPLACE PROCEDURE SCAN_NON_UTF8_CHARS(p_table_name STRING)<br>RETURNS VARIANT<br>LANGUAGE PYTHON<br>RUNTIME_VERSION = &#39;3.11&#39;<br>PACKAGES = (&#39;snowflake-snowpark-python&#39;)<br>HANDLER = &#39;run&#39;<br>EXECUTE AS CALLER<br>AS<br>$$<br>def run(session, p_table_name):<br>    parts = p_table_name.strip().replace(&#39;&quot;&#39;, &#39;&#39;).split(&#39;.&#39;)<br>    if len(parts) != 3:<br>        return {&quot;error&quot;: f&quot;Table name must be fully qualified (DB.SCHEMA.TABLE). Got: {p_table_name}&quot;}<br>    <br>    db_name, schema_name, table_name = parts[0].upper(), parts[1].upper(), parts[2].upper()<br>    fq_table = f&#39;&quot;{db_name}&quot;.&quot;{schema_name}&quot;.&quot;{table_name}&quot;&#39;<br>    report_table = f&#39;&quot;{db_name}&quot;.&quot;{schema_name}&quot;.&quot;{table_name}_NONUTF8_ROWS&quot;&#39;<br>    udf_schema = f&#39;&quot;{db_name}&quot;.&quot;{schema_name}&quot;&#39;<br>    <br>    result = {<br>        &quot;table&quot;: fq_table,<br>        &quot;report_table&quot;: report_table,<br>        &quot;status&quot;: &quot;SUCCESS&quot;,<br>        &quot;mode&quot;: &quot;SCAN_ONLY (source table NOT modified)&quot;,<br>        &quot;version&quot;: &quot;3.0&quot;,<br>        &quot;string_columns_scanned&quot;: 0,<br>        &quot;total_rows&quot;: 0,<br>        &quot;rows_with_problematic_chars&quot;: 0,<br>        &quot;rows_reported&quot;: 0,<br>        &quot;validation&quot;: &quot;NOT_RUN&quot;,<br>        &quot;columns_affected&quot;: [],<br>        &quot;detection_method&quot;: &quot;HEX_ENCODE bypass (avoids Python runtime UTF-8 decode failure)&quot;,<br>        &quot;chars_detected&quot;: &quot;raw invalid UTF-8 bytes, U+FFFD, C0 controls, C1 controls, NULL bytes, surrogates&quot;,<br>        &quot;error&quot;: None<br>    }<br>    <br>    try:<br>        # Step 1: Create helper UDF that accepts HEX-encoded string<br>        # HEX_ENCODE(col) converts raw bytes to hex ASCII (e.g., 0xD1 -&gt; &quot;D1&quot;)<br>        # This ALWAYS works regardless of byte validity because it&#39;s just hex chars<br>        # Inside Python, we convert hex -&gt; bytes -&gt; try UTF-8 decode -&gt; validate<br>        session.sql(f&#39;&#39;&#39;<br>            CREATE OR REPLACE FUNCTION {udf_schema}.&quot;_IS_CLEAN_UTF8_HEX&quot;(hex_str STRING)<br>            RETURNS BOOLEAN LANGUAGE PYTHON RUNTIME_VERSION = &#39;3.11&#39; HANDLER = &#39;is_clean&#39;<br>            AS &#39;<br>def is_clean(hex_str):<br>    if hex_str is None or hex_str == &quot;&quot;:<br>        return True<br>    try:<br>        raw_bytes = bytes.fromhex(hex_str)<br>        text = raw_bytes.decode(&quot;utf-8&quot;, errors=&quot;strict&quot;)<br>        for ch in text:<br>            cp = ord(ch)<br>            if cp == 0xFFFD: return False<br>            if cp == 0x0000: return False<br>            if cp &lt;= 0x08 or cp == 0x0B or cp == 0x0C or (0x0E &lt;= cp &lt;= 0x1F): return False<br>            if 0x0080 &lt;= cp &lt;= 0x009F: return False<br>            if 0xD800 &lt;= cp &lt;= 0xDFFF: return False<br>        return True<br>    except Exception:<br>        return False<br>&#39;<br>        &#39;&#39;&#39;).collect()<br>        <br>        # Step 2: Get total row count<br>        total_rows = session.sql(f&quot;SELECT COUNT(*) FROM {fq_table}&quot;).collect()[0][0]<br>        result[&quot;total_rows&quot;] = total_rows<br>        <br>        if total_rows == 0:<br>            result[&quot;status&quot;] = &quot;EMPTY_TABLE&quot;<br>            result[&quot;validation&quot;] = &quot;NO_DATA&quot;<br>            return result<br>        <br>        # Step 3: Identify STRING/VARCHAR columns<br>        cols_query = f&quot;&quot;&quot;<br>            SELECT column_name FROM &quot;{db_name}&quot;.INFORMATION_SCHEMA.COLUMNS<br>            WHERE table_schema = &#39;{schema_name}&#39; AND table_name = &#39;{table_name}&#39;<br>              AND data_type IN (&#39;TEXT&#39;,&#39;VARCHAR&#39;,&#39;STRING&#39;,&#39;CHAR&#39;,&#39;CHARACTER&#39;)<br>            ORDER BY ordinal_position<br>        &quot;&quot;&quot;<br>        col_names = [r[&#39;COLUMN_NAME&#39;] for r in session.sql(cols_query).collect()]<br>        result[&quot;string_columns_scanned&quot;] = len(col_names)<br>        <br>        if not col_names:<br>            result[&quot;status&quot;] = &quot;NO_STRING_COLUMNS&quot;<br>            result[&quot;validation&quot;] = &quot;N/A&quot;<br>            return result<br>        <br>        # Step 4: Detect rows using HEX_ENCODE bypass<br>        # HEX_ENCODE(col) -&gt; pure ASCII hex string -&gt; safe to pass to Python<br>        conditions = &quot; OR &quot;.join(<br>            [f&#39;NOT {udf_schema}.&quot;_IS_CLEAN_UTF8_HEX&quot;(HEX_ENCODE(&quot;{c}&quot;))&#39; for c in col_names]<br>        )<br>        detect_query = f&quot;SELECT COUNT(*) FROM {fq_table} WHERE {conditions}&quot;<br>        <br>        affected_count = session.sql(detect_query).collect()[0][0]<br>        result[&quot;rows_with_problematic_chars&quot;] = affected_count<br>        <br>        if affected_count == 0:<br>            result[&quot;status&quot;] = &quot;CLEAN&quot;<br>            result[&quot;validation&quot;] = &quot;UTF8_COMPLIANT&quot;<br>            return result<br>        <br>        result[&quot;validation&quot;] = &quot;PROBLEMATIC_CHARS_FOUND&quot;<br>        <br>        # Step 5: Per-column breakdown<br>        for col in col_names:<br>            cnt = session.sql(<br>                f&#39;SELECT COUNT(*) FROM {fq_table} WHERE NOT {udf_schema}.&quot;_IS_CLEAN_UTF8_HEX&quot;(HEX_ENCODE(&quot;{col}&quot;))&#39;<br>            ).collect()[0][0]<br>            if cnt &gt; 0:<br>                result[&quot;columns_affected&quot;].append({&quot;column&quot;: col, &quot;affected_rows&quot;: cnt})<br>        <br>        # Step 6: Archive affected rows to report table (source NOT modified)<br>        session.sql(f&quot;&quot;&quot;<br>            CREATE OR REPLACE TABLE {report_table} AS<br>            SELECT *, CURRENT_TIMESTAMP() AS _scan_timestamp<br>            FROM {fq_table} WHERE {conditions}<br>        &quot;&quot;&quot;).collect()<br>        <br>        result[&quot;rows_reported&quot;] = session.sql(f&quot;SELECT COUNT(*) FROM {report_table}&quot;).collect()[0][0]<br>        <br>    except Exception as e:<br>        result[&quot;status&quot;] = &quot;ERROR&quot;<br>        result[&quot;error&quot;] = str(e)[:1000]<br>    <br>    return result<br>$$;</pre><h3>Usage</h3><pre>-- Scan any table or view (fully qualified name required)<br>CALL SCAN_NON_UTF8_CHARS(&#39;MY_DATABASE.MY_SCHEMA.MY_TABLE&#39;);<br><br>/*<br><br>--Sample procedure response in json format from my internal test.<br>--You will receive similar response for your table.<br><br>{<br>  &quot;chars_detected&quot;: &quot;raw invalid UTF-8 bytes, U+FFFD, C0 controls, C1 controls, NULL bytes, surrogates&quot;,<br>  &quot;columns_affected&quot;: [<br>    {<br>      &quot;affected_rows&quot;: 3,<br>      &quot;column&quot;: &quot;NAME&quot;<br>    },<br>    {<br>      &quot;affected_rows&quot;: 1,<br>      &quot;column&quot;: &quot;ADDRESS&quot;<br>    },<br>    {<br>      &quot;affected_rows&quot;: 1,<br>      &quot;column&quot;: &quot;NOTES&quot;<br>    }<br>  ],<br>  &quot;detection_method&quot;: &quot;HEX_ENCODE bypass (avoids Python runtime UTF-8 decode failure)&quot;,<br>  &quot;error&quot;: null,<br>  &quot;mode&quot;: &quot;SCAN_ONLY (source table NOT modified)&quot;,<br>  &quot;report_table&quot;: &quot;\&quot;ICEBERG_TEST_DB\&quot;.\&quot;UTF8_DEMO\&quot;.\&quot;SCAN_TEST_V3_NONUTF8_ROWS\&quot;&quot;,<br>  &quot;rows_reported&quot;: 3,<br>  &quot;rows_with_problematic_chars&quot;: 3,<br>  &quot;status&quot;: &quot;SUCCESS&quot;,<br>  &quot;string_columns_scanned&quot;: 3,<br>  &quot;table&quot;: &quot;\&quot;ICEBERG_TEST_DB\&quot;.\&quot;UTF8_DEMO\&quot;.\&quot;SCAN_TEST_V3\&quot;&quot;,<br>  &quot;total_rows&quot;: 6,<br>  &quot;validation&quot;: &quot;PROBLEMATIC_CHARS_FOUND&quot;,<br>  &quot;version&quot;: &quot;3.0&quot;<br>}<br>*/<br><br>-- Extract the JSON result<br>SELECT <br>    $1:table::STRING AS scanned_table,<br>    $1:status::STRING AS status,<br>    $1:version::STRING AS version,<br>    $1:total_rows::NUMBER AS total_rows,<br>    $1:rows_with_problematic_chars::NUMBER AS rows_affected,<br>    $1:rows_reported::NUMBER AS rows_in_report,<br>    $1:validation::STRING AS validation,<br>    $1:columns_affected AS columns_affected,<br>    $1:detection_method::STRING AS method,<br>    $1:error::STRING AS error<br>FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));<br><br><br>-- Review report, filter with timestamp for multiple scans for same table<br>-- report_table contains the actual rows copies that has non-utf-8 characters, this is kept so that<br>-- you can easily find source rows and decide if you want to update it to be valid <br>-- utf-8 characters, comment to article if you need update statements as well.<br><br>SELECT &#39;=== Report Table ===&#39; AS check;<br>SELECT * FROM ICEBERG_TEST_DB.UTF8_DEMO.SCAN_TEST_V3_NONUTF8_ROWS ORDER BY id;</pre><h3>Sample Output</h3><p>When run against a table with 6 rows (3 clean, 3 with problematic characters):</p><ul><li><strong>scanned_table</strong>: &quot;ICEBERG_TEST_DB&quot;.&quot;UTF8_DEMO&quot;.&quot;SCAN_TEST_TABLE&quot;</li><li><strong>status</strong>: SUCCESS</li><li><strong>mode</strong>: SCAN_ONLY (source table NOT modified)</li><li><strong>total_rows</strong>: 6</li><li><strong>rows_with_problematic_chars</strong>: 3</li><li><strong>rows_in_report</strong>: 3</li><li><strong>validation</strong>: PROBLEMATIC_CHARS_FOUND</li><li><strong>columns_affected</strong>: [{column: NAME, affected_rows: 3}, {column: ADDRESS, affected_rows: 2}, {column: NOTES, affected_rows: 2}]</li></ul><p>The report table SCAN_TEST_TABLE_NONUTF8_ROWS contains a copy of the 3 affected rows for review, with a _scan_timestamp column.</p><h3>Key Design Decisions</h3><p><strong>Why Python UDF instead of REGEXP or SQL functions?</strong></p><p>SQL regex in Snowflake cannot reliably detect all problematic Unicode ranges. The CONTAINS(col, CHR(x)) approach only checks for specific characters — you&#39;d need 2,110 individual checks. The Python UDF iterates every character once, checking ranges efficiently.</p><p><strong>Why EXECUTE AS CALLER?</strong></p><p>The procedure needs to access tables in any database/schema. EXECUTE AS CALLER inherits the caller&#39;s privileges, so it works across the entire account without needing pre-granted access.</p><p><strong>Why a report table instead of just a count?</strong></p><p>Knowing “47/x rows are affected” is useful, but seeing the actual data helps you decide whether to:</p><ul><li>Remove the characters entirely</li><li>Replace them with safe equivalents</li><li>Go back to the source system and re-export with proper encoding</li></ul><p><strong>Why not modify the source table?</strong></p><p>This is a scan/audit tool. In enterprise environments, you want to assess impact before making changes. The companion procedure (CLEAN_NON_UTF8_CHARS) handles the actual remediation as a separate step.</p><h3>What To Do After Scanning</h3><p>If the procedure reports PROBLEMATIC_CHARS_FOUND, you have several options (in order of preference):</p><ol><li><strong>Re-load from source with correct ENCODING</strong> — Lossless. Use FILE_FORMAT (ENCODING = &#39;ISO-8859-1&#39;) or &#39;WINDOWS1252&#39; if you know the source encoding.</li><li><strong>Update source table</strong> — Removes problematic characters (some data loss, but pipeline won’t break).</li><li><strong>Accept and document</strong> — If affected rows are &lt; 0.1% of total data, the business may accept the loss.</li></ol><h3>Important: Non-ASCII is NOT Non-UTF-8</h3><p>A common misconception: characters like é, ü, ñ, Ö, €, Chinese/Japanese/Arabic characters are all <strong>valid UTF-8</strong>. They are non-ASCII (multi-byte) but perfectly fine for Iceberg/Parquet. This procedure correctly identifies ONLY the problematic characters and leaves valid international text untouched.</p><p><em>Built and tested on Snowflake with Snowpark Python 3.11. The procedure runs on your warehouse compute — no serverless or Cortex AI credits consumed.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=98fa95ade638" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake — Fast data export with Snowpark]]></title>
            <link>https://medium.com/@vishalverma_us/snowflake-fast-data-export-with-snowpark-e5bc3ff0a4ab?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/e5bc3ff0a4ab</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[snowpark]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[snowflake-copy-into]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Thu, 16 Jul 2026 02:05:39 GMT</pubDate>
            <atom:updated>2026-07-16T02:05:39.917Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A-l7Dr2AlWanxKe_3kKtrQ.png" /></figure><h3>The Problem</h3><p>During a large-scale data migration, the compliance team needed to peek into million+ tables across multiple databases with sample snapshot of random rows of data set into Azure Data Lake Storage (ADLS). Initial requirements were:</p><ul><li>Export 1,000 sample rows from every active table in the account</li><li>Write each table’s sample as a CSV file to a structured path on an Azure external stage</li><li>Track success/failure per table with error details</li><li>Run incrementally (only export tables created since the last run)</li><li>Full utilization of snowflake compute power</li></ul><p>A sequential approach — looping over large number tables one by one — would take days. We needed concurrency.</p><h3>Why Not Just Use a Task Graph or Multiple Warehouses?</h3><p>Task graphs (DAGs) are great for orchestrating independent SQL steps, but spawning 900 child tasks introduces scheduling overhead, monitoring complexity, and credit waste from task startup latency. Multiple warehouses would work but adds cost and operational burden.</p><p>The simpler solution: a single Python stored procedure that launches COPY INTO commands asynchronously using Snowflake’s collect_nowait() API, bounded by the warehouse&#39;s thread capacity.</p><h3>Architecture Overview</h3><pre>                    +-------------------+<br>                    |  Scheduling Task  |<br>                    |  (CRON daily)     |<br>                    +--------+----------+<br>                             |<br>                    +--------v----------+<br>                    |  Python Stored    |<br>                    |  Procedure        |<br>                    |  (EXECUTE AS      |<br>                    |   CALLER)         |<br>                    +--------+----------+<br>                             |<br>          +------------------+------------------+<br>          |                  |                  |<br>    +-----v-----+     +-----v-----+     +-----v-----+<br>    | COPY INTO |     | COPY INTO |     | COPY INTO |   x N concurrent<br>    | table_1   |     | table_2   |     | table_N   |<br>    +-----+-----+     +-----+-----+     +-----+-----+<br>          |                  |                  |<br>          +------------------+------------------+<br>                             |<br>                    +--------v----------+<br>                    |  Azure ADLS Gen2  |<br>                    |  External Stage   |<br>                    +-------------------+<br>                             |<br>                    +--------v----------+<br>                    |  Monitoring Table |<br>                    |  (per-table log)  |<br>                    +-------------------+</pre><h3>Key Design Decisions</h3><h3>1. Concurrency via collect_nowait()</h3><p>Snowpark Python’s collect_nowait() method submits a SQL query to the warehouse and immediately returns a handle — without blocking. This lets you launch dozens of queries in parallel, bounded only by the warehouse&#39;s execution threads.</p><p>The concurrency cap is determined by warehouse size:</p><p>Warehouse Size = Max Concurrent Threads</p><p>X-Small = 8</p><p>Small = 16</p><p>Medium = 32</p><p>Large = 64</p><p>X-Large = 128</p><p>The procedure detects the current warehouse size and caps in-flight jobs accordingly. Concurrency can be altered any time using <a href="https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse">ALTER WAREHOUSE</a> command in Snowflake</p><pre>alter warehouse MY_WH set max_concurrency_level = 32;</pre><h3>2. SAMPLE vs LIMIT</h3><p>We use SAMPLE (1000 ROWS) instead of LIMIT 1000. Why?</p><ul><li>LIMIT requires reading from the start of the table (or sorting first), which can be slow on large tables</li><li>SAMPLE reads random micro-partitions and returns quickly regardless of table size</li><li>For a migration validation use case, statistical sampling is sufficient — we don’t need the “first” 1000 rows</li></ul><h3>3. Incremental by Default</h3><p>The procedure tracks the last successful export timestamp. On subsequent runs with p_table_create_date_ts = NULL, it only exports tables created after the last batch — making daily runs fast and incremental.</p><h3>4. Monitoring Table</h3><p>Every export attempt — success or failure — is logged with:</p><ul><li>Database, schema, table name</li><li>Row count and sample size</li><li>Status (SUCCESS/FAILED) with error message</li><li>Export path on the stage</li><li>Timestamp</li></ul><p>This enables dashboards, alerting, and retry logic.</p><h3>The Code</h3><h3>Prerequisites: File Format and External Stage</h3><pre>-- CSV file format for export<br>CREATE FILE FORMAT IF NOT EXISTS SNOW_MIG_DB.PUBLIC.FF_FILE_EXPORTER_CSV TYPE=CSV;</pre><pre>-- External stage pointing to Azure ADLS Gen2<br>CREATE STAGE IF NOT EXISTS SNOW_MIG_DB.PUBLIC.STG_EXT_AZURE_FILE_EXPORTER<br>  URL = &#39;azure://&lt;storage_account&gt;.blob.core.windows.net/&lt;container&gt;/file_exporter/&#39;<br>  STORAGE_INTEGRATION = &lt;your_storage_integration&gt;<br>  FILE_FORMAT = FF_FILE_EXPORTER_CSV;</pre><h3>Monitoring Table</h3><pre>CREATE TABLE IF NOT EXISTS SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS (<br>    export_id NUMBER AUTOINCREMENT,<br>    database_name STRING,<br>    schema_name STRING,<br>    table_name STRING,<br>    table_type STRING,<br>    row_count NUMBER,<br>    sample_rowcount NUMBER,<br>    export_status STRING,       -- SUCCESS | FAILED<br>    error_msg STRING,<br>    export_timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),<br>    external_stage STRING,<br>    export_path STRING,<br>    PRIMARY KEY (export_id)<br>);</pre><h3>Python Stored Procedure</h3><pre><br>/*<br># Author: @Vishal Verma<br># Full procedure code.<br># Key components:<br>#   - Warehouse size detection for concurrency cap<br>#   - ACCOUNT_USAGE.TABLES query to build table list<br>#   - Async COPY INTO loop with collect_nowait()<br>#   - Drain loop for remaining handles<br>#   - Per-table INSERT into monitoring table<br>*/<br>-- ============================================================================<br>-- SAMPLE DATA EXPORTER - Export sample rows from all active tables to cloud storage<br>-- Concurrency: Python async (collect_nowait) capped at warehouse thread count<br>-- ============================================================================<br><br>USE ROLE ACCOUNTADMIN;<br>USE WAREHOUSE XSMALL;<br>USE SCHEMA SNOW_MIG_DB.PUBLIC;<br><br>-- ============================================================================<br>-- STEP 1: Prerequisites (File Format + External Stage)<br>-- ============================================================================<br><br>CREATE FILE FORMAT IF NOT EXISTS SNOW_MIG_DB.PUBLIC.FF_FILE_EXPORTER_CSV TYPE=CSV;<br><br>CREATE STAGE IF NOT EXISTS SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER<br>  URL = &#39;azure://&lt;storage_account&gt;.blob.core.windows.net/&lt;container&gt;/file_exporter/&#39;<br>  STORAGE_INTEGRATION = &lt;your_storage_integration&gt;<br>  FILE_FORMAT = FF_FILE_EXPORTER_CSV;<br><br>-- ============================================================================<br>-- STEP 2: Monitoring Status Table<br>-- ============================================================================<br><br>CREATE TABLE IF NOT EXISTS SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS (<br>    export_id NUMBER AUTOINCREMENT,<br>    database_name STRING,<br>    schema_name STRING,<br>    table_name STRING,<br>    table_type STRING,<br>    row_count NUMBER,<br>    sample_rowcount NUMBER,<br>    export_status STRING,       -- SUCCESS | FAILED<br>    error_msg STRING,<br>    export_timestamp TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),<br>    external_stage STRING,<br>    export_path STRING,<br>    PRIMARY KEY (export_id)<br>);<br><br>-- ============================================================================<br>-- STEP 3: Export Stored Procedure (Python with Async Concurrency)<br>-- ============================================================================<br>-- Parameters:<br>--   p_external_stage: Fully qualified external stage name<br>--   p_database_name_list: Comma-separated database names (NULL = all)<br>--   p_sample_rowcount: Number of sample rows to export (default 1000)<br>--   p_table_create_date_ts: Export tables created on/after this timestamp<br>--                           (NULL = incremental from last successful export)<br>--<br>-- Concurrency Model:<br>--   Uses collect_nowait() to launch COPY INTO commands asynchronously<br>--   Max parallel jobs = warehouse thread count by size<br>--   XSmall=8, Small=16, Medium=32, Large=64, XLarge=128<br>--<br>-- Excludes:<br>--   SNOWFLAKE database, SNOW_MIG_DB, IMPORTED DATABASE, APPLICATION,<br>--   APPLICATION PACKAGE, PERSONAL DATABASE, CATALOG-LINKED DATABASE<br>-- ============================================================================<br><br>CREATE OR REPLACE PROCEDURE SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    p_external_stage STRING DEFAULT &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER&#39;,<br>    p_database_name_list STRING DEFAULT NULL,<br>    p_sample_rowcount NUMBER DEFAULT 1000,<br>    p_table_create_date_ts STRING DEFAULT NULL<br>)<br>RETURNS VARIANT<br>LANGUAGE PYTHON<br>RUNTIME_VERSION = &#39;3.11&#39;<br>PACKAGES = (&#39;snowflake-snowpark-python&#39;)<br>HANDLER = &#39;run&#39;<br>EXECUTE AS CALLER<br>AS<br>$$<br>import time<br><br>def run(session, p_external_stage, p_database_name_list, p_sample_rowcount, p_table_create_date_ts):<br>    account = session.sql(&quot;SELECT CURRENT_ACCOUNT()&quot;).collect()[0][0]<br>    <br>    # Determine warehouse concurrency based on size<br>    wh_name = session.sql(&quot;SELECT CURRENT_WAREHOUSE()&quot;).collect()[0][0]<br>    wh_info = session.sql(f&quot;SHOW WAREHOUSES LIKE &#39;{wh_name}&#39;&quot;).collect()<br>    wh_size = wh_info[0][&#39;size&#39;] if wh_info else &#39;X-Small&#39;<br>    size_map = {&#39;X-Small&#39;: 8, &#39;Small&#39;: 16, &#39;Medium&#39;: 32, &#39;Large&#39;: 64, &#39;X-Large&#39;: 128, &#39;2X-Large&#39;: 256}<br>    max_concurrency = size_map.get(wh_size, 8)<br>    <br>    # Determine cutoff timestamp (incremental logic)<br>    cutoff_ts = None<br>    if p_table_create_date_ts:<br>        cutoff_ts = p_table_create_date_ts<br>    else:<br>        last_ts = session.sql(&quot;&quot;&quot;<br>            SELECT MAX(export_timestamp)::STRING <br>            FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>            WHERE export_status = &#39;SUCCESS&#39;<br>        &quot;&quot;&quot;).collect()[0][0]<br>        cutoff_ts = last_ts<br>    <br>    # Build table list (active BASE TABLEs in STANDARD databases only)<br>    query = &quot;&quot;&quot;<br>    SELECT <br>        t.table_catalog AS DB_NAME,<br>        t.table_schema AS SCHEMA_NAME,<br>        t.table_name AS TBL_NAME,<br>        t.table_type AS TBL_TYPE,<br>        COALESCE(t.row_count, 0) AS TBL_ROW_COUNT<br>    FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t<br>    INNER JOIN SNOWFLAKE.ACCOUNT_USAGE.DATABASES d<br>        ON t.table_catalog = d.database_name AND d.deleted IS NULL<br>    WHERE t.deleted IS NULL<br>      AND t.table_type IN (&#39;BASE TABLE&#39;)<br>      AND d.type IN (&#39;STANDARD&#39;)<br>      AND d.database_name NOT IN (&#39;SNOWFLAKE&#39;, &#39;SNOW_MIG_DB&#39;)<br>      AND t.table_schema != &#39;INFORMATION_SCHEMA&#39;<br>    &quot;&quot;&quot;<br>    <br>    if p_database_name_list:<br>        db_list = &quot;,&quot;.join([f&quot;&#39;{db.strip()}&#39;&quot; for db in p_database_name_list.split(&quot;,&quot;)])<br>        query += f&quot; AND t.table_catalog IN ({db_list})&quot;<br>    <br>    if cutoff_ts:<br>        query += f&quot; AND t.created &gt;= &#39;{cutoff_ts}&#39;::TIMESTAMP_NTZ&quot;<br>    <br>    query += &quot; ORDER BY t.table_catalog, t.table_schema, t.table_name&quot;<br>    <br>    tables = session.sql(query).collect()<br>    <br>    total_tables = len(tables)<br>    success_count = 0<br>    fail_count = 0<br>    active_handles = []  # list of (async_job, table_info_dict)<br>    <br>    def process_completed(handles):<br>        &quot;&quot;&quot;Check all active handles, log completed ones, return still-running ones.&quot;&quot;&quot;<br>        nonlocal success_count, fail_count<br>        still_active = []<br>        for handle, tinfo in handles:<br>            try:<br>                if handle.is_done():<br>                    try:<br>                        handle.result()  # raises if query failed<br>                        session.sql(f&quot;&quot;&quot;<br>                            INSERT INTO SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS <br>                            (database_name, schema_name, table_name, table_type, row_count, sample_rowcount, export_status, error_msg, external_stage, export_path)<br>                            VALUES (&#39;{tinfo[&#39;db&#39;]}&#39;, &#39;{tinfo[&#39;schema&#39;]}&#39;, &#39;{tinfo[&#39;table&#39;]}&#39;, &#39;{tinfo[&#39;type&#39;]}&#39;, {tinfo[&#39;rows&#39;]}, {int(p_sample_rowcount)}, &#39;SUCCESS&#39;, NULL, &#39;{p_external_stage}&#39;, &#39;{tinfo[&#39;path&#39;]}&#39;)<br>                        &quot;&quot;&quot;).collect()<br>                        success_count += 1<br>                    except Exception as e:<br>                        err_msg = str(e).replace(&quot;&#39;&quot;, &quot;&#39;&#39;&quot;)[:500]<br>                        session.sql(f&quot;&quot;&quot;<br>                            INSERT INTO SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS <br>                            (database_name, schema_name, table_name, table_type, row_count, sample_rowcount, export_status, error_msg, external_stage, export_path)<br>                            VALUES (&#39;{tinfo[&#39;db&#39;]}&#39;, &#39;{tinfo[&#39;schema&#39;]}&#39;, &#39;{tinfo[&#39;table&#39;]}&#39;, &#39;{tinfo[&#39;type&#39;]}&#39;, {tinfo[&#39;rows&#39;]}, {int(p_sample_rowcount)}, &#39;FAILED&#39;, &#39;{err_msg}&#39;, &#39;{p_external_stage}&#39;, &#39;{tinfo[&#39;path&#39;]}&#39;)<br>                        &quot;&quot;&quot;).collect()<br>                        fail_count += 1<br>                else:<br>                    still_active.append((handle, tinfo))<br>            except Exception as e:<br>                err_msg = str(e).replace(&quot;&#39;&quot;, &quot;&#39;&#39;&quot;)[:500]<br>                session.sql(f&quot;&quot;&quot;<br>                    INSERT INTO SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS <br>                    (database_name, schema_name, table_name, table_type, row_count, sample_rowcount, export_status, error_msg, external_stage, export_path)<br>                    VALUES (&#39;{tinfo[&#39;db&#39;]}&#39;, &#39;{tinfo[&#39;schema&#39;]}&#39;, &#39;{tinfo[&#39;table&#39;]}&#39;, &#39;{tinfo[&#39;type&#39;]}&#39;, {tinfo[&#39;rows&#39;]}, {int(p_sample_rowcount)}, &#39;FAILED&#39;, &#39;{err_msg}&#39;, &#39;{p_external_stage}&#39;, &#39;{tinfo[&#39;path&#39;]}&#39;)<br>                &quot;&quot;&quot;).collect()<br>                fail_count += 1<br>        return still_active<br>    <br>    # Main loop: launch async COPY INTO, cap at max_concurrency<br>    for row in tables:<br>        db_name = row[&#39;DB_NAME&#39;]<br>        schema_name = row[&#39;SCHEMA_NAME&#39;]<br>        tbl_name = row[&#39;TBL_NAME&#39;]<br>        tbl_type = row[&#39;TBL_TYPE&#39;]<br>        row_count = row[&#39;TBL_ROW_COUNT&#39;]<br>        <br>        export_path = f&quot;{account}/{db_name}/{schema_name}/tables/{tbl_name}&quot;<br>        <br>        copy_sql = (<br>            f&#39;COPY INTO @{p_external_stage}/{export_path}/ &#39;<br>            f&#39;FROM (SELECT * FROM &quot;{db_name}&quot;.&quot;{schema_name}&quot;.&quot;{tbl_name}&quot; SAMPLE ({int(p_sample_rowcount)} ROWS)) &#39;<br>            f&quot;FILE_FORMAT = (FORMAT_NAME = &#39;SNOW_MIG_DB.PUBLIC.FF_FILE_EXPORTER_CSV&#39;) &quot;<br>            f&#39;OVERWRITE = TRUE HEADER = TRUE SINGLE = TRUE MAX_FILE_SIZE = 268435456&#39;<br>        )<br>        <br>        tinfo = {&#39;db&#39;: db_name, &#39;schema&#39;: schema_name, &#39;table&#39;: tbl_name, &#39;type&#39;: tbl_type, &#39;rows&#39;: row_count, &#39;path&#39;: export_path}<br>        <br>        # Block if at max concurrency - wait for a slot<br>        while len(active_handles) &gt;= max_concurrency:<br>            time.sleep(0.5)<br>            active_handles = process_completed(active_handles)<br>        <br>        # Launch COPY INTO asynchronously (non-blocking)<br>        try:<br>            async_job = session.sql(copy_sql).collect_nowait()<br>            active_handles.append((async_job, tinfo))<br>        except Exception as e:<br>            err_msg = str(e).replace(&quot;&#39;&quot;, &quot;&#39;&#39;&quot;)[:500]<br>            session.sql(f&quot;&quot;&quot;<br>                INSERT INTO SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS <br>                (database_name, schema_name, table_name, table_type, row_count, sample_rowcount, export_status, error_msg, external_stage, export_path)<br>                VALUES (&#39;{db_name}&#39;, &#39;{schema_name}&#39;, &#39;{tbl_name}&#39;, &#39;{tbl_type}&#39;, {row_count}, {int(p_sample_rowcount)}, &#39;FAILED&#39;, &#39;{err_msg}&#39;, &#39;{p_external_stage}&#39;, &#39;{export_path}&#39;)<br>            &quot;&quot;&quot;).collect()<br>            fail_count += 1<br>    <br>    # Drain: wait for all remaining async jobs to finish<br>    while active_handles:<br>        time.sleep(1)<br>        active_handles = process_completed(active_handles)<br>    <br>    return {<br>        &#39;account&#39;: account,<br>        &#39;warehouse_size&#39;: wh_size,<br>        &#39;max_concurrency&#39;: max_concurrency,<br>        &#39;cutoff_timestamp&#39;: str(cutoff_ts),<br>        &#39;total_tables&#39;: total_tables,<br>        &#39;success_count&#39;: success_count,<br>        &#39;fail_count&#39;: fail_count,<br>        &#39;database_filter&#39;: p_database_name_list,<br>        &#39;sample_rows&#39;: int(p_sample_rowcount)<br>    }<br>$$;<br><br>-- ============================================================================<br>-- STEP 4: Scheduled Task (runs daily at 2 AM UTC)<br>-- ============================================================================<br><br>CREATE OR REPLACE TASK SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_TASK<br>  WAREHOUSE = XSMALL<br>  SCHEDULE = &#39;USING CRON 0 2 * * * UTC&#39;<br>  COMMENT = &#39;Daily async export of sample data to cloud storage at 2 AM UTC&#39;<br>  AS<br>  CALL SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER&#39;,<br>    NULL,<br>    1000,<br>    NULL<br>  );<br><br>-- Enable the task (uncomment when ready)<br>-- ALTER TASK SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_TASK RESUME;<br><br>-- ============================================================================<br>-- STEP 5: TEST CASES<br>-- ============================================================================<br><br>-- 5.1: Create test tables<br>CREATE SCHEMA IF NOT EXISTS TEST_DB.EXP_TEST_SCHEMA;<br><br>CREATE OR REPLACE TABLE TEST_DB.EXP_TEST_SCHEMA.SAMPLE_CUSTOMERS AS<br>SELECT <br>    SEQ4() AS customer_id,<br>    &#39;Customer_&#39; || SEQ4() AS customer_name,<br>    DATEADD(&#39;day&#39;, -UNIFORM(1, 365, RANDOM()), CURRENT_DATE()) AS signup_date,<br>    UNIFORM(100, 10000, RANDOM())::DECIMAL(10,2) AS balance<br>FROM TABLE(GENERATOR(ROWCOUNT =&gt; 5000));<br><br>CREATE OR REPLACE TABLE TEST_DB.EXP_TEST_SCHEMA.SAMPLE_ORDERS AS<br>SELECT <br>    SEQ4() AS order_id,<br>    UNIFORM(1, 5000, RANDOM()) AS customer_id,<br>    DATEADD(&#39;day&#39;, -UNIFORM(1, 90, RANDOM()), CURRENT_DATE()) AS order_date,<br>    UNIFORM(10, 500, RANDOM())::DECIMAL(10,2) AS amount,<br>    ARRAY_CONSTRUCT(&#39;PENDING&#39;,&#39;SHIPPED&#39;,&#39;DELIVERED&#39;)[UNIFORM(0,2,RANDOM())]::STRING AS status<br>FROM TABLE(GENERATOR(ROWCOUNT =&gt; 10000));<br><br>-- 5.2: Clear monitoring and run test<br>TRUNCATE TABLE SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS;<br><br>-- Test: Export TEST_DB tables (100 sample rows, async parallel)<br>CALL SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER&#39;,<br>    &#39;TEST_DB&#39;,<br>    100,<br>    &#39;2020-01-01&#39;<br>);<br><br>-- 5.3: Verify monitoring table<br>SELECT database_name, schema_name, table_name, export_status, error_msg, export_timestamp, export_path<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>ORDER BY export_timestamp DESC<br>LIMIT 20;<br><br>-- 5.4: Verify files on stage<br>LIST @SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER/;<br><br>-- 5.5: Test incremental (NULL timestamp = from last export)<br>CALL SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER&#39;,<br>    &#39;TEST_DB&#39;,<br>    50,<br>    NULL<br>);<br><br>-- 5.6: Test multiple databases<br>CALL SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER&#39;,<br>    &#39;TEST_DB,DEMO_DB&#39;,<br>    100,<br>    &#39;2024-01-01&#39;<br>);<br><br>-- 5.7: Verify parallel execution (multiple exports completing in same second)<br>SELECT <br>    DATE_TRUNC(&#39;second&#39;, export_timestamp) AS second_bucket,<br>    COUNT(*) AS exports_in_same_second<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>GROUP BY 1<br>HAVING COUNT(*) &gt; 1<br>ORDER BY 2 DESC<br>LIMIT 10;<br><br>-- 5.8: Summary by status<br>SELECT export_status, COUNT(*) AS cnt <br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS <br>GROUP BY export_status;<br><br>-- 5.9: View failed exports with error details<br>SELECT database_name, schema_name, table_name, error_msg<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>WHERE export_status = &#39;FAILED&#39;<br>ORDER BY export_timestamp DESC<br>LIMIT 10;<br><br>-- ============================================================================<br>-- STEP 6: MONITORING QUERIES<br>-- ============================================================================<br><br>-- Export summary by database<br>SELECT <br>    database_name,<br>    COUNT(*) AS total_tables,<br>    SUM(CASE WHEN export_status = &#39;SUCCESS&#39; THEN 1 ELSE 0 END) AS success,<br>    SUM(CASE WHEN export_status = &#39;FAILED&#39; THEN 1 ELSE 0 END) AS failed,<br>    MAX(export_timestamp) AS last_export<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>GROUP BY database_name<br>ORDER BY database_name;<br><br>-- Concurrency proof: time span per batch<br>SELECT <br>    MIN(export_timestamp) AS batch_start,<br>    MAX(export_timestamp) AS batch_end,<br>    DATEDIFF(&#39;second&#39;, MIN(export_timestamp), MAX(export_timestamp)) AS duration_seconds,<br>    COUNT(*) AS tables_exported,<br>    ROUND(COUNT(*) / NULLIF(DATEDIFF(&#39;second&#39;, MIN(export_timestamp), MAX(export_timestamp)), 0), 1) AS tables_per_second<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS;<br><br>-- Task execution history<br>SELECT *<br>FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(<br>    TASK_NAME =&gt; &#39;EXP_TAB_DATA_TO_ADLS_TASK&#39;,<br>    SCHEDULED_TIME_RANGE_START =&gt; DATEADD(&#39;day&#39;, -7, CURRENT_TIMESTAMP())<br>))<br>ORDER BY SCHEDULED_TIME DESC;<br><br>-- ============================================================================<br>-- CLEANUP (if needed)<br>-- ============================================================================<br><br>-- ALTER TASK SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_TASK SUSPEND;<br>-- DROP TASK SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_TASK;<br>-- DROP PROCEDURE SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(STRING, STRING, NUMBER, STRING);<br>-- DROP TABLE SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS;<br>-- REMOVE @SNOW_MIG_DB.PUBLIC.STG_EXT_FILE_EXPORTER/;</pre><h3>Scheduled Task</h3><pre>CREATE OR REPLACE TASK SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_TASK<br>  WAREHOUSE = XSMALL<br>  SCHEDULE = &#39;USING CRON 0 2 * * * UTC&#39;<br>  COMMENT = &#39;Daily async export of sample data to ADLS at 2 AM UTC&#39;<br>  AS<br>  CALL SNOW_MIG_DB.PUBLIC.EXP_TAB_DATA_TO_ADLS_PROC(<br>    &#39;SNOW_MIG_DB.PUBLIC.STG_EXT_AZURE_FILE_EXPORTER&#39;,<br>    NULL,<br>    1000,<br>    NULL<br>  );</pre><h3>Results: 3000+ Tables in Under 5 Minutes</h3><p>Running against a full account with a <strong>Large warehouse</strong> (64 concurrent threads):</p><p>Results can highly vary based on warehouse size and storage account location (cross-region has latency).</p><p>Most failures were permission errors on tables owned by restricted roles — not export logic issues.</p><h3>Concurrency Proof</h3><p>Querying the monitoring table confirms true parallelism — multiple tables completing in the same second:</p><pre>SELECT <br>    DATE_TRUNC(&#39;second&#39;, export_timestamp) AS second_bucket,<br>    COUNT(*) AS exports_in_same_second<br>FROM SNOW_MIG_DB.PUBLIC.EXP_MONITORING_STATUS<br>GROUP BY 1<br>HAVING COUNT(*) &gt; 1<br>ORDER BY 2 DESC<br>LIMIT 5;</pre><pre>| second_bucket       | exports_in_same_second |<br>|---------------------|----------------------|<br>| 2026-07-10 15:23:45 | 12                   |<br>| 2026-07-10 15:23:44 | 11                   |<br>| 2026-07-10 15:23:46 | 10                   |<br>| 2026-07-10 15:23:47 | 9                    |<br>| 2026-07-10 15:23:43 | 8                    |</pre><h3>Lessons Learned</h3><h3>1. collect_nowait() is the key to warehouse-level parallelism</h3><p>Before discovering this API, the procedure was sequential and took 45+ minutes. One line change — collect() to collect_nowait() — and the architecture became truly concurrent.</p><h3>2. The concurrency cap matters</h3><p>Without a cap, you’ll overwhelm the warehouse queue and get queuing delays. Matching the cap to the warehouse’s thread count gives optimal throughput without wasted queueing.</p><h3>3. SAMPLE rows is fast, LIMIT is not</h3><p>On a 10B-row table, LIMIT 1000 can take 30+ seconds (full partition scan to row 1000). SAMPLE (1000 ROWS) returns in under 1 second because it reads only a few micro-partitions.</p><h3>4. Monitor everything</h3><p>The monitoring table turned out to be invaluable for:</p><ul><li>Identifying which tables had privilege issues (fix grants, re-run)</li><li>Proving to stakeholders that the export was comprehensive</li><li>Supporting incremental logic without re-scanning the entire account</li></ul><h3>5. Use EXECUTE AS CALLER</h3><p>The procedure uses EXECUTE AS CALLER so it inherits the caller&#39;s role and can access tables across all databases. EXECUTE AS OWNER would limit visibility to what the procedure owner can see.</p><h3>When to Use This Pattern</h3><p>This async Python procedure pattern works well for:</p><ul><li>Data sampling/profiling across many tables</li><li>Bulk COPY INTO to external stages</li><li>Parallel GRANT/REVOKE operations</li><li>Any fan-out workload where you have N independent SQL statements</li></ul><p>It does NOT replace:</p><ul><li>Dynamic tables (for materialized transformation pipelines)</li><li>Task graphs (for dependent step orchestration)</li><li>Snowpipe (for continuous ingestion)</li></ul><h3>Summary</h3><p>By combining Snowpark Python’s collect_nowait() with warehouse-aware concurrency capping. The pattern is reusable for any fan-out SQL workload where you need controlled parallelism within a single warehouse.</p><p><em>Built and tested on Snowflake (Azure East US 2) with Snowpark Python 3.11.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e5bc3ff0a4ab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Snowflake’s SYS_CONTEXT Function: A Complete Guide to Context-Aware SQL Development]]></title>
            <link>https://medium.com/snowflake/mastering-snowflakes-sys-context-function-a-complete-guide-to-context-aware-sql-development-547e9dd3089e?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/547e9dd3089e</guid>
            <category><![CDATA[snowflake-cortex]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Tue, 03 Mar 2026 22:01:00 GMT</pubDate>
            <atom:updated>2026-03-03T22:01:00.908Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qqBS3DFXtzyJC6iwaED-Gw.png" /></figure><p>When developing applications on Snowflake, you often need to understand the context in which your code is executing. Who is running this query? What role are they using? Which account and region are they connected to? Is this code running inside a Native App?</p><p>Snowflake’s SYS_CONTEXT function answers all these questions and more. It&#39;s a powerful context function that provides runtime information about sessions, environments, organizations, and applications. Whether you&#39;re building multi-tenant applications, implementing security policies, or debugging complex stored procedures, understanding SYS_CONTEXT is essential.</p><p>In this comprehensive guide, we’ll explore every namespace and property of SYS_CONTEXT, with practical examples and real-world use cases.</p><h3>Understanding SYS_CONTEXT</h3><h3>Basic Syntax</h3><pre>-- Retrieving properties<br>SYS_CONTEXT(&#39;&lt;namespace&gt;&#39;, &#39;&lt;property&gt;&#39;)<br>-- Calling functions<br>SYS_CONTEXT(&#39;&lt;namespace&gt;&#39;, &#39;&lt;function&gt;&#39;, &#39;&lt;argument&gt;&#39; [, ...])</pre><p>The SYS_CONTEXT function returns contextual information based on two primary parameters:</p><ul><li><strong>Namespace</strong>: Defines the category of context information</li><li><strong>Property/Function</strong>: Specifies exactly what information to retrieve</li></ul><h3>Available Namespaces</h3><pre>┌─────────────────────────────────┬────────────────────────────────────────────────────┐<br>│ Namespace                       │ Purpose                                            │<br>├─────────────────────────────────┼────────────────────────────────────────────────────┤<br>│ SNOWFLAKE$SESSION               │ Session-level info (user, roles, database context) │<br>│ SNOWFLAKE$ENVIRONMENT           │ Environment details (client, account, region)      │<br>│ SNOWFLAKE$ORGANIZATION          │ Organization-wide context and functions            │<br>│ SNOWFLAKE$ORGANIZATION_SESSION  │ Organization user session information              │<br>│ SNOWFLAKE$APPLICATION           │ Native App context (version, mode, app roles)      │<br>└─────────────────────────────────┴────────────────────────────────────────────────────┘</pre><p>Privileges required</p><pre>┌─────────────────────────────────┬────────────────────────────────────────────────────┐<br>│ Namespace                       │ Privilege                                          │<br>├─────────────────────────────────┼────────────────────────────────────────────────────┤<br>│ SNOWFLAKE$SESSION               │ None - returns your own session                    │<br>│ SNOWFLAKE$ENVIRONMENT           │ None - returns account/region info                 │<br>│ SNOWFLAKE$ORGANIZATION          │ Account must be part of an Organization;           |    <br>│ SNOWFLAKE$ORGANIZATION_SESSION  │ Must be an Organization user (federated identity)  |                       <br>│ SNOWFLAKE$APPLICATION           │ Must be running inside a Native App context        │                       <br>└─────────────────────────────────┴────────────────────────────────────────────────────│</pre><h3>1. SNOWFLAKE$SESSION Namespace</h3><p>The SNOWFLAKE$SESSION namespace is your go-to for session-specific information. It provides detailed context about who is running the code and with what permissions.</p><h4>All Available Properties</h4><pre>┌────────────────────────┬─────────────────────────────────────────────────────────────┐<br>│ Property               │ Description                                                 │<br>├────────────────────────┼─────────────────────────────────────────────────────────────┤<br>│ PRINCIPAL_NAME         │ Name of the principal (user, task, or SPCS service)         │<br>│ PRINCIPAL_TYPE         │ Type: USER, USER_PERSON, USER_SERVICE, TASK, SNOWSERVICE    │<br>│ PRINCIPAL_EMAIL        │ Email address associated with the principal                 │<br>│ PRINCIPAL_DATABASE     │ Database containing the principal (NULL for users)          │<br>│ PRINCIPAL_SCHEMA       │ Schema containing the principal (NULL for users)            │<br>│ ID                     │ Session identifier                                          │<br>│ ROLE                   │ Primary role for the session                                │<br>│ ROLE_TYPE              │ Type of primary role (ROLE for account role)                │<br>│ ROLE_DATABASE          │ Database containing the role (if database role)             │<br>│ SECONDARY_ROLES        │ JSON array of activated secondary roles                     │<br>│ WANTED_SECONDARY_ROLES │ JSON array of requested secondary roles                     │<br>│ DATABASE               │ Current database in use                                     │<br>│ SCHEMA                 │ Current schema in use                                       │<br>│ SCHEMAS                │ JSON array of schema search path                            │<br>│ WAREHOUSE              │ Current warehouse in use                                    │<br>├────────────────────────┼─────────────────────────────────────────────────────────────┤<br>│ Function               │ Description                                                 │<br>├────────────────────────┼─────────────────────────────────────────────────────────────┤<br>│ IS_ROLE_ACTIVATED      │ Checks if a specific role is activated (takes role name)    │<br>└────────────────────────┴─────────────────────────────────────────────────────────────┘</pre><h4>SQL to query all properties</h4><pre>-- All SNOWFLAKE$SESSION properties in one query<br>SELECT <br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_NAME&#39;) AS principal_name,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_TYPE&#39;) AS principal_type,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_EMAIL&#39;) AS principal_email,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_DATABASE&#39;) AS principal_database,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_SCHEMA&#39;) AS principal_schema,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;ID&#39;) AS session_id,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;ROLE&#39;) AS role,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;ROLE_TYPE&#39;) AS role_type,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;ROLE_DATABASE&#39;) AS role_database,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;SECONDARY_ROLES&#39;) AS secondary_roles,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;WANTED_SECONDARY_ROLES&#39;) AS wanted_secondary_roles,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;DATABASE&#39;) AS database,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;SCHEMA&#39;) AS schema,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;SCHEMAS&#39;) AS schemas,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;WAREHOUSE&#39;) AS warehouse,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;ACCOUNTADMIN&#39;) AS is_accountadmin_activated;</pre><p><strong>Sample Result:</strong></p><pre>PRINCIPAL_NAME          : VVERMA<br>PRINCIPAL_TYPE          : USER_PERSON<br>PRINCIPAL_EMAIL         : (email if set)<br>PRINCIPAL_DATABASE      : NULL (users are account-level objects)<br>PRINCIPAL_SCHEMA        : NULL (users are account-level objects)<br>SESSION_ID              : 3178718282001302<br>ROLE                    : ACCOUNTADMIN<br>ROLE_TYPE               : ROLE<br>ROLE_DATABASE           : NULL<br>SECONDARY_ROLES         : [&quot;ANALYST_ROLE&quot;,&quot;SECURITYADMIN&quot;,&quot;SYSADMIN&quot;,&quot;USERADMIN&quot;,...]<br>WANTED_SECONDARY_ROLES  : [&quot;ACCOUNTADMIN&quot;]<br>DATABASE                : COMMON_UTILITY<br>SCHEMA                  : SNOWSECURE<br>SCHEMAS                 : [&quot;COMMON_UTILITY.SNOWSECURE&quot;,&quot;COMMON_UTILITY.PUBLIC&quot;]<br>WAREHOUSE               : XSMALL<br>IS_ACCOUNTADMIN_ACTIVATED: TRUE</pre><h4>Understanding PRINCIPAL_DATABASE and PRINCIPAL_SCHEMA</h4><p>These properties are <strong>NULL for users</strong> because users are account-level objects. They only return values when the session is initiated by schema-level objects:</p><pre>┌─────────────────┬────────────────────┬───────────────────┐<br>│ Principal Type  │ PRINCIPAL_DATABASE │ PRINCIPAL_SCHEMA  │<br>├─────────────────┼────────────────────┼───────────────────┤<br>│ USER/USER_*     │ NULL               │ NULL              │<br>│ TASK            │ Task&#39;s database    │ Task&#39;s schema     │<br>│ SNOWSERVICE     │ Service&#39;s database │ Service&#39;s schema  │<br>└─────────────────┴────────────────────┴───────────────────┘</pre><h4>When to Use SNOWFLAKE$SESSION</h4><pre>┌────────────────────────────────────────┬──────────────────────────────────────┐<br>│ Use Case                               │ Property/Function                    │<br>├────────────────────────────────────────┼──────────────────────────────────────┤<br>│ Audit logging who executed a query     │ PRINCIPAL_NAME, PRINCIPAL_TYPE       │<br>│ Implement row-level security           │ PRINCIPAL_NAME, IS_ROLE_ACTIVATED    │<br>│ Debug stored procedures                │ DATABASE, SCHEMA, ROLE               │<br>│ Track session analytics                │ ID, WAREHOUSE                        │<br>│ Check permission before operations     │ IS_ROLE_ACTIVATED                    │<br>│ Identify task/service-initiated runs   │ PRINCIPAL_TYPE, PRINCIPAL_DATABASE   │<br>└────────────────────────────────────────┴──────────────────────────────────────┘</pre><h3>2. SNOWFLAKE$ENVIRONMENT Namespace</h3><p>The SNOWFLAKE$ENVIRONMENT namespace provides information about the execution environment—the client application, account, and region.</p><h4>All Available Properties</h4><pre>┌──────────┬─────────────────────────────────────────────┬────────────────────┐<br>│ Property │ Description                                 │ Equivalent Function│<br>├──────────┼─────────────────────────────────────────────┼────────────────────┤<br>│ CLIENT   │ Name and version of client/driver used      │ CURRENT_CLIENT()   │<br>│ ACCOUNT  │ Account locator for the current session     │ CURRENT_ACCOUNT()  │<br>│ REGION   │ Region of the account (with region group)   │ CURRENT_REGION()   │<br>└──────────┴─────────────────────────────────────────────┴────────────────────┘</pre><p><strong>No functions available</strong> for this namespace — only properties.</p><h4>SQL to Query All Properties</h4><pre>-- All SNOWFLAKE$ENVIRONMENT properties<br>SELECT <br>    SYS_CONTEXT(&#39;SNOWFLAKE$ENVIRONMENT&#39;, &#39;CLIENT&#39;) AS client,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$ENVIRONMENT&#39;, &#39;ACCOUNT&#39;) AS account,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$ENVIRONMENT&#39;, &#39;REGION&#39;) AS region,<br>    -- Compare with equivalent functions<br>    CURRENT_CLIENT() AS current_client_fn,<br>    CURRENT_ACCOUNT() AS current_account_fn,<br>    CURRENT_REGION() AS current_region_fn;</pre><p><strong>Sample Result:</strong></p><pre>CLIENT            : JavaScript 2.3.3<br>ACCOUNT           : USB97494<br>REGION            : AWS_US_WEST_2<br>CURRENT_CLIENT_FN : JavaScript 2.3.3<br>CURRENT_ACCOUNT_FN: USB97494<br>CURRENT_REGION_FN : AWS_US_WEST_2</pre><h4>When to Use SNOWFLAKE$ENVIRONMENT</h4><pre>┌─────────────────────────────────────┬──────────┐<br>│ Use Case                            │ Property │<br>├─────────────────────────────────────┼──────────┤<br>│ Log which client/driver is used     │ CLIENT   │<br>│ Multi-account deployment logic      │ ACCOUNT  │<br>│ Region-specific processing          │ REGION   │<br>│ Compliance/data residency checks    │ REGION   │<br>│ Debug connectivity issues           │ CLIENT   │<br>└─────────────────────────────────────┴──────────┘</pre><h3>3. SNOWFLAKE$ORGANIZATION Namespace</h3><p>The SNOWFLAKE$ORGANIZATION namespace provides functions for organization-level context, particularly useful for managing users and groups across multiple accounts.</p><h4>All Available Functions</h4><pre>┌────────────────────┬──────────────────────────────────────────────────────────────┐<br>│ Function           │ Description                                                  │<br>├────────────────────┼──────────────────────────────────────────────────────────────┤<br>│ IS_USER_IMPORTED   │ Checks if an org user has been imported to the account       │<br>│                    │ Arguments: &lt;user_name&gt;                                       │<br>├────────────────────┼──────────────────────────────────────────────────────────────┤<br>│ IS_GROUP_IMPORTED  │ Checks if a group has been imported to the account           │<br>│                    │ Arguments: &lt;group_name&gt;                                      │<br>├────────────────────┼──────────────────────────────────────────────────────────────┤<br>│ IS_GROUP_ACTIVATED │ Checks if an org group role is activated in the session      │<br>│                    │ Arguments: &lt;context&gt;, &lt;group_name&gt;                           │<br>│                    │ Context: &#39;SESSION&#39; or &#39;ACTIVE&#39;                               │<br>└────────────────────┴──────────────────────────────────────────────────────────────┘</pre><p><strong>No properties available</strong> — only functions (all require arguments).</p><h4>SQL to Query All Properties</h4><pre>-- All SNOWFLAKE$ORGANIZATION functions<br>-- Replace user/group names with actual values from your organization<br>SELECT <br>    SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION&#39;, &#39;IS_USER_IMPORTED&#39;, &#39;VVERMA&#39;) <br>        AS is_user_imported,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION&#39;, &#39;IS_GROUP_IMPORTED&#39;, &#39;DATA_ENGINEERS&#39;) <br>        AS is_group_imported,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION&#39;, &#39;IS_GROUP_ACTIVATED&#39;, &#39;SESSION&#39;, &#39;DATA_ENGINEERS&#39;) <br>        AS is_group_activated_session,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION&#39;, &#39;IS_GROUP_ACTIVATED&#39;, &#39;ACTIVE&#39;, &#39;DATA_ENGINEERS&#39;) <br>        AS is_group_activated_active;</pre><p><strong>Sample Result:</strong></p><pre>IS_USER_IMPORTED          : FALSE<br>IS_GROUP_IMPORTED         : FALSE<br>IS_GROUP_ACTIVATED_SESSION: FALSE<br>IS_GROUP_ACTIVATED_ACTIVE : FALSE</pre><blockquote><strong><em>Note:</em></strong><em> Returns FALSE for non-existent or non-imported users/groups.</em></blockquote><h4>IS_GROUP_ACTIVATED Context Options</h4><ul><li><strong>SESSION</strong>: Checks if the group role is in the primary/secondary role hierarchy</li><li><strong>ACTIVE</strong>: Checks if the group role is in the current execution context (useful for owner’s rights procedures)</li></ul><h4>Sample Procedure to Test Context Difference</h4><pre>CREATE OR REPLACE PROCEDURE test_org_group_context(group_name VARCHAR)<br>RETURNS TABLE (context VARCHAR, is_activated VARCHAR)<br>LANGUAGE SQL<br>EXECUTE AS OWNER<br>AS<br>&#39;<br>DECLARE<br>    res RESULTSET;<br>BEGIN<br>    res := (<br>        SELECT <br>            &#39;&#39;SESSION&#39;&#39; AS context,<br>            SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ORGANIZATION&#39;&#39;, &#39;&#39;IS_GROUP_ACTIVATED&#39;&#39;, &#39;&#39;SESSION&#39;&#39;, :group_name) AS is_activated<br>        UNION ALL<br>        SELECT <br>            &#39;&#39;ACTIVE&#39;&#39; AS context,<br>            SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ORGANIZATION&#39;&#39;, &#39;&#39;IS_GROUP_ACTIVATED&#39;&#39;, &#39;&#39;ACTIVE&#39;&#39;, :group_name) AS is_activated<br>    );<br>    RETURN TABLE(res);<br>END;<br>&#39;;<br><br>-- Test it<br>CALL test_org_group_context(&#39;DATA_ENGINEERS&#39;);</pre><h4>When to Use SNOWFLAKE$ORGANIZATION</h4><pre>┌────────────────────────────────────────────┬────────────────────┐<br>│ Use Case                                   │ Function           │<br>├────────────────────────────────────────────┼────────────────────┤<br>│ Verify organization user access            │ IS_USER_IMPORTED   │<br>│ Check group-based permissions              │ IS_GROUP_ACTIVATED │<br>│ Validate cross-account group setup         │ IS_GROUP_IMPORTED  │<br>│ Implement organization-wide security       │ All functions      │<br>└────────────────────────────────────────────┴────────────────────┘</pre><h3>4. SNOWFLAKE$ORGANIZATION_SESSION Namespace</h3><p>This namespace bridges organization-level identity with session context, providing information about organization users in the current session.</p><h4>All Available Properties</h4><pre>┌────────────────┬──────────────────────────────────────────────────────────────┐<br>│ Property       │ Description                                                  │<br>├────────────────┼──────────────────────────────────────────────────────────────┤<br>│ PRINCIPAL_NAME │ Name of the organization user that started the session       │<br>│                │ Returns NULL if the current user is not an organization user │<br>└────────────────┴──────────────────────────────────────────────────────────────┘</pre><p><strong>No functions available</strong> — only one property.</p><h4>SQL to Query All Properties</h4><pre>-- SNOWFLAKE$ORGANIZATION_SESSION property with comparison<br>SELECT <br>    SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION_SESSION&#39;, &#39;PRINCIPAL_NAME&#39;) AS org_principal_name,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_NAME&#39;) AS session_principal_name,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_TYPE&#39;) AS session_principal_type;</pre><p><strong>Sample Result:</strong></p><pre>ORG_PRINCIPAL_NAME    : NULL (not an organization user)<br>SESSION_PRINCIPAL_NAME: VVERMA<br>SESSION_PRINCIPAL_TYPE: USER_PERSON</pre><h4>Understanding the Difference: Local vs Organization Users</h4><pre>┌─────────────────────┬────────────────────────┬──────────────────────────────────┐<br>│ User Type           │ SNOWFLAKE$SESSION      │ SNOWFLAKE$ORGANIZATION_SESSION   │<br>│                     │ PRINCIPAL_NAME         │ PRINCIPAL_NAME                   │<br>├─────────────────────┼────────────────────────┼──────────────────────────────────┤<br>│ Local User          │ VVERMA                 │ NULL                             │<br>│ Organization User   │ VVERMA                 │ org_user@company                 │<br>└─────────────────────┴────────────────────────┴──────────────────────────────────┘</pre><h4>Sample Procedure to Detect User Type</h4><pre>CREATE OR REPLACE PROCEDURE check_user_type()<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>EXECUTE AS OWNER <br>AS<br>&#39;<br>DECLARE<br>    org_user VARCHAR;<br>    session_user VARCHAR;<br>BEGIN<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ORGANIZATION_SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_NAME&#39;&#39;) INTO org_user;<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_NAME&#39;&#39;) INTO session_user;<br>  <br>    IF (org_user IS NOT NULL AND org_user != &#39;&#39;&#39;&#39;) THEN<br>        RETURN &#39;&#39;Organization User: &#39;&#39; || org_user;<br>    ELSE<br>        RETURN &#39;&#39;Local Account User: &#39;&#39; || session_user;<br>    END IF;<br>END;<br>&#39;;<br><br><br>CALL check_user_type();<br>-- Returns: Local Account User: VVERMA</pre><h4>When to Use SNOWFLAKE$ORGANIZATION_SESSION</h4><pre>┌──────────────────────────────────────────────┬────────────────┐<br>│ Use Case                                     │ Property       │<br>├──────────────────────────────────────────────┼────────────────┤<br>│ Identify organization users vs local users   │ PRINCIPAL_NAME │<br>│ Implement org-user specific logic            │ PRINCIPAL_NAME │<br>│ Audit organization user activity             │ PRINCIPAL_NAME │<br>└──────────────────────────────────────────────┴────────────────┘</pre><h3>5. SNOWFLAKE$APPLICATION Namespace</h3><p>The SNOWFLAKE$APPLICATION namespace is specifically designed for <strong>Snowflake Native Apps</strong>. It provides context about the application in which code is executing.</p><h4>Important: Context Requirements</h4><p>This namespace <strong>only returns values</strong> when called from within a Native App context:</p><ul><li>Owner’s rights stored procedures or Streamlit apps within a Native App</li><li>UDFs, views, or policies owned by a Native App</li><li>Shared data content of a Native App</li></ul><p><strong>Outside these contexts, all properties return NULL.</strong></p><h4>All Available Properties</h4><pre>┌───────────────────┬──────────────────────────────────────────────────────────────┐<br>│ Property          │ Description                                                  │<br>├───────────────────┼──────────────────────────────────────────────────────────────┤<br>│ NAME              │ Name of the application                                      │<br>│ CURRENT_VERSION   │ Version currently executing                                  │<br>│ CURRENT_PATCH     │ Current patch number                                         │<br>│ INSTALLED_VERSION │ Installed version (may differ during upgrades)               │<br>│ INSTALLED_PATCH   │ Installed patch number                                       │<br>│ IS_DEV_MODE       │ TRUE if app is in development mode, FALSE otherwise          │<br>├───────────────────┼──────────────────────────────────────────────────────────────┤<br>│ Function          │ Description                                                  │<br>├───────────────────┼──────────────────────────────────────────────────────────────┤<br>│ IS_APPLICATION_ROLE_ACTIVATED │ Checks if an app role is activated              │<br>│                               │ Arguments: &lt;role_name&gt;                           │<br>└───────────────────────────────┴──────────────────────────────────────────────────┘</pre><h4>SQL to query all properties</h4><pre>-- All SNOWFLAKE$APPLICATION properties and functions<br>-- Note: Returns NULL when called outside a Native App<br>SELECT <br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;NAME&#39;) AS app_name,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;CURRENT_VERSION&#39;) AS current_version,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;CURRENT_PATCH&#39;) AS current_patch,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;INSTALLED_VERSION&#39;) AS installed_version,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;INSTALLED_PATCH&#39;) AS installed_patch,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;IS_DEV_MODE&#39;) AS is_dev_mode,<br>    SYS_CONTEXT(&#39;SNOWFLAKE$APPLICATION&#39;, &#39;IS_APPLICATION_ROLE_ACTIVATED&#39;, &#39;APP_ADMIN&#39;) AS is_app_admin_role;</pre><p><strong>Sample Result (outside Native App):</strong></p><pre>APP_NAME          : NULL<br>CURRENT_VERSION   : NULL<br>CURRENT_PATCH     : NULL<br>INSTALLED_VERSION : NULL<br>INSTALLED_PATCH   : NULL<br>IS_DEV_MODE       : NULL<br>IS_APP_ADMIN_ROLE : NULL</pre><h4>Sample Native App Procedure</h4><p>To see actual values, run this inside a Native App:</p><p><a href="https://www.snowflake.com/en/product/features/native-apps/"><em>Learn to build native apps here</em></a></p><pre>-- setup_script.sql (inside a Native App)<br>CREATE OR REPLACE PROCEDURE app_info()<br>RETURNS TABLE (property VARCHAR, value VARCHAR)<br>LANGUAGE SQL<br>EXECUTE AS OWNER<br>AS<br>&#39;<br>DECLARE<br>    res RESULTSET;<br>BEGIN<br>    res := (<br>        SELECT &#39;&#39;NAME&#39;&#39; AS property, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;NAME&#39;&#39;) AS value<br>        UNION ALL<br>        SELECT &#39;&#39;CURRENT_VERSION&#39;&#39;, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;CURRENT_VERSION&#39;&#39;)<br>        UNION ALL<br>        SELECT &#39;&#39;CURRENT_PATCH&#39;&#39;, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;CURRENT_PATCH&#39;&#39;)<br>        UNION ALL<br>        SELECT &#39;&#39;INSTALLED_VERSION&#39;&#39;, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;INSTALLED_VERSION&#39;&#39;)<br>        UNION ALL<br>        SELECT &#39;&#39;INSTALLED_PATCH&#39;&#39;, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;INSTALLED_PATCH&#39;&#39;)<br>        UNION ALL<br>        SELECT &#39;&#39;IS_DEV_MODE&#39;&#39;, <br>               SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;IS_DEV_MODE&#39;&#39;)<br>    );<br>    RETURN TABLE(res);<br>END;<br>&#39;;</pre><pre>-- When called from inside a Native App:<br>-- PROPERTY          | VALUE<br>-- ------------------|--------<br>-- NAME              | MY_APP<br>-- CURRENT_VERSION   | 1.2.0<br>-- CURRENT_PATCH     | 3<br>-- INSTALLED_VERSION | 1.2.0<br>-- INSTALLED_PATCH   | 3<br>-- IS_DEV_MODE       | FALSE</pre><h4>Version Handling During Upgrades</h4><p>During an application upgrade, CURRENT_VERSION and INSTALLED_VERSION can differ:</p><pre>CREATE OR REPLACE PROCEDURE handle_upgrade()<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>EXECUTE AS OWNER<br>AS<br>&#39;<br>DECLARE<br>    current_ver VARCHAR;<br>    installed_ver VARCHAR;<br>BEGIN<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;CURRENT_VERSION&#39;&#39;) INTO current_ver;<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$APPLICATION&#39;&#39;, &#39;&#39;INSTALLED_VERSION&#39;&#39;) INTO installed_ver;<br>  <br>    IF (current_ver != installed_ver) THEN<br>        -- During upgrade: CURRENT_VERSION = new, INSTALLED_VERSION = old<br>        RETURN &#39;&#39;Upgrading from &#39;&#39; || installed_ver || &#39;&#39; to &#39;&#39; || current_ver;<br>    ELSE<br>        RETURN &#39;&#39;No upgrade in progress. Version: &#39;&#39; || current_ver;<br>    END IF;<br>END;<br>&#39;;</pre><h4>When to Use SNOWFLAKE$APPLICATION</h4><pre>┌──────────────────────────────────────────┬──────────────────────────────────┐<br>│ Use Case                                 │ Property/Function                │<br>├──────────────────────────────────────────┼──────────────────────────────────┤<br>│ Version-specific feature flags           │ CURRENT_VERSION                  │<br>│ Development vs production behavior       │ IS_DEV_MODE                      │<br>│ Handle upgrade migrations                │ CURRENT_VERSION vs INSTALLED_*   │<br>│ Application role-based access control    │ IS_APPLICATION_ROLE_ACTIVATED    │<br>│ Logging and debugging in apps            │ NAME, CURRENT_VERSION, *_PATCH   │<br>└──────────────────────────────────────────┴──────────────────────────────────┘</pre><h3>Practical Examples and Use Cases</h3><h4>1. Dynamic Row-Level Security</h4><pre>CREATE OR REPLACE SECURE VIEW sales_data_secure AS<br>SELECT *<br>FROM sales_data<br>WHERE <br>    -- Show all data to admins, otherwise filter by region<br>    SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;ADMIN_ROLE&#39;)::BOOLEAN = TRUE<br>    OR region = SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;PRINCIPAL_NAME&#39;);</pre><h4>2. Comprehensive Audit Logging</h4><pre>CREATE OR REPLACE PROCEDURE log_audit_event(event_type VARCHAR, event_details VARCHAR)<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>EXECUTE AS OWNER<br>AS<br>&#39;<br>BEGIN<br>    INSERT INTO audit_log (<br>        event_time, event_type, event_details,<br>        user_name, user_type, role_name, session_id,<br>        client_info, account, region,<br>        warehouse, database_name, schema_name<br>    )<br>    SELECT <br>        CURRENT_TIMESTAMP(),<br>        :event_type,<br>        :event_details,<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_NAME&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_TYPE&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;ROLE&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;ID&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ENVIRONMENT&#39;&#39;, &#39;&#39;CLIENT&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ENVIRONMENT&#39;&#39;, &#39;&#39;ACCOUNT&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ENVIRONMENT&#39;&#39;, &#39;&#39;REGION&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;WAREHOUSE&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;DATABASE&#39;&#39;),<br>        SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;SCHEMA&#39;&#39;);<br>  <br>    RETURN &#39;&#39;Event logged successfully&#39;&#39;;<br>END;<br>&#39;;</pre><h4>3. Multi-Tenant Application Logic</h4><pre>CREATE OR REPLACE FUNCTION get_tenant_data()<br>RETURNS TABLE (data_value VARCHAR)<br>AS<br>$$<br>    SELECT data_value<br>    FROM tenant_data<br>    WHERE tenant_id = (<br>        SELECT tenant_id <br>        FROM tenant_mapping <br>        WHERE account_locator = SYS_CONTEXT(&#39;SNOWFLAKE$ENVIRONMENT&#39;, &#39;ACCOUNT&#39;)<br>    )<br>$$;</pre><h4>4. Region-Based Conditional Processing</h4><pre>CREATE OR REPLACE PROCEDURE process_data()<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>EXECUTE AS OWNER<br>AS<br>&#39;<br>DECLARE<br>    region VARCHAR;<br>BEGIN<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$ENVIRONMENT&#39;&#39;, &#39;&#39;REGION&#39;&#39;) INTO region;<br>  <br>    CASE <br>        WHEN region LIKE &#39;&#39;AWS_US%&#39;&#39; THEN<br>            CALL process_us_data();<br>        WHEN region LIKE &#39;&#39;AWS_EU%&#39;&#39; THEN<br>            CALL process_eu_data();<br>        ELSE<br>            CALL process_default();<br>    END CASE;<br>  <br>    RETURN &#39;&#39;Processing complete for region: &#39;&#39; || region;<br>END;<br>&#39;;</pre><h3>Quick Reference: Which Namespace for Which Scenario?</h3><pre>┌───────────────────────────────────────┬────────────────────────────────┬──────────────────────────────────┐<br>│ Scenario                              │ Namespace                      │ Key Properties/Functions         │<br>├───────────────────────────────────────┼────────────────────────────────┼──────────────────────────────────┤<br>│ Who is running this query?            │ SNOWFLAKE$SESSION              │ PRINCIPAL_NAME, PRINCIPAL_TYPE   │<br>│ What permissions do they have?        │ SNOWFLAKE$SESSION              │ ROLE, IS_ROLE_ACTIVATED          │<br>│ What&#39;s the current database context?  │ SNOWFLAKE$SESSION              │ DATABASE, SCHEMA, WAREHOUSE      │<br>│ Which client is connecting?           │ SNOWFLAKE$ENVIRONMENT          │ CLIENT                           │<br>│ What account/region is this?          │ SNOWFLAKE$ENVIRONMENT          │ ACCOUNT, REGION                  │<br>│ Is this an organization user?         │ SNOWFLAKE$ORGANIZATION_SESSION │ PRINCIPAL_NAME                   │<br>│ Is a group/user imported?             │ SNOWFLAKE$ORGANIZATION         │ IS_GROUP_IMPORTED, IS_USER_*     │<br>│ What Native App version?              │ SNOWFLAKE$APPLICATION          │ CURRENT_VERSION, INSTALLED_*     │<br>│ Is this dev mode?                     │ SNOWFLAKE$APPLICATION          │ IS_DEV_MODE                      │<br>└───────────────────────────────────────┴────────────────────────────────┴──────────────────────────────────┘</pre><h3>Context Availability Summary</h3><pre>┌────────────────────────────────┬─────────────────────┬─────────────────────────────────┐<br>│ Namespace                      │ Context Required    │ Returns NULL Outside Context    │<br>├────────────────────────────────┼─────────────────────┼─────────────────────────────────┤<br>│ SNOWFLAKE$SESSION              │ Any session         │ No (always works)               │<br>│ SNOWFLAKE$ENVIRONMENT          │ Any session         │ No (always works)               │<br>│ SNOWFLAKE$ORGANIZATION         │ Any account in org  │ Returns FALSE for invalid names │<br>│ SNOWFLAKE$ORGANIZATION_SESSION │ Org user session    │ Yes (NULL for local users)      │<br>│ SNOWFLAKE$APPLICATION          │ Native App only     │ Yes (all NULL outside apps)     │<br>└────────────────────────────────┴─────────────────────┴─────────────────────────────────┘</pre><h3>Important Considerations</h3><h4>1. NULL Return Values</h4><p>Functions return NULL when called outside their supported context. Always handle NULL cases in your logic.</p><h4>2. Type Casting for Boolean Values</h4><p>Boolean properties return &#39;TRUE&#39; or &#39;FALSE&#39; as VARCHAR. Cast to BOOLEAN for comparisons:</p><pre>SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;MY_ROLE&#39;)::BOOLEAN = TRUE</pre><h4>3. Shell Escaping</h4><p>When using in CLI with double quotes, escape the $:</p><pre>snow sql --query &quot;SELECT SYS_CONTEXT(&#39;SNOWFLAKE\$SESSION&#39;, &#39;PRINCIPAL_NAME&#39;);&quot;</pre><h4>4. Procedure Delimiter Issues</h4><p>When using SYS_CONTEXT in stored procedures, the $ in namespace names can conflict with the $$ delimiter. Use single quotes instead:</p><pre>-- Use single quotes with escaped internal quotes<br>CREATE OR REPLACE PROCEDURE my_proc()<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>AS<br>&#39;<br>DECLARE<br>    user_name VARCHAR;<br>BEGIN<br>    SELECT SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_NAME&#39;&#39;) INTO user_name;<br>    RETURN user_name;<br>END;<br>&#39;;</pre><h4>5. NULL-Safe Variable Assignment</h4><p>When assigning SYS_CONTEXT results to variables in procedures, use SELECT INTO instead of := to handle NULL values:</p><pre>-- May fail with NULL<br>org_user := SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION_SESSION&#39;, &#39;PRINCIPAL_NAME&#39;);<br>-- NULL-safe approach<br>SELECT SYS_CONTEXT(&#39;SNOWFLAKE$ORGANIZATION_SESSION&#39;, &#39;PRINCIPAL_NAME&#39;) INTO org_user;<br></pre><h4>6. Access Control</h4><ul><li>Owner’s rights procedures need READ SESSION privilege for some properties</li><li>Application namespace requires code to be within a Native App context</li></ul><pre>-- Important note on READ SESSION<br>-- Grant READ SESSION to a role (must be done by ACCOUNTADMIN)<br>GRANT READ SESSION ON ACCOUNT TO ROLE my_custom_role;<br><br>-- Verify the grant<br>SHOW GRANTS ON ACCOUNT;<br><br>-- Why it is important, lets see<br><br>-- Create a role that owns procedures needing session access<br>CREATE ROLE proc_owner_role;<br><br>-- Grant READ SESSION so its owner&#39;s rights procedures can access caller session<br>GRANT READ SESSION ON ACCOUNT TO ROLE proc_owner_role;<br><br>-- Now procedures owned by proc_owner_role can do this:<br>CREATE OR REPLACE PROCEDURE proc_owner_role.get_caller_info()<br>RETURNS VARCHAR<br>LANGUAGE SQL<br>EXECUTE AS OWNER  -- Owner&#39;s rights<br>AS<br>&#39;<br>BEGIN<br>    RETURN SYS_CONTEXT(&#39;&#39;SNOWFLAKE$SESSION&#39;&#39;, &#39;&#39;PRINCIPAL_NAME&#39;&#39;);<br>END;<br>&#39;;<br><br>-- Without READ SESSION, an owner&#39;s rights procedure calling SYS_CONTEXT(&#39;SNOWFLAKE$SESSION&#39;, ...) returns NULL.</pre><h3>SYS_CONTEXT with Snowflake Intelligence &amp; Cortex</h3><p>SYS_CONTEXT integrates seamlessly with Snowflake’s AI capabilities to build context-aware, personalized experiences. In <strong>Cortex Agents</strong> and<strong> Snowflake Intelligence</strong>, you can use SYS_CONTEXT(‘SNOWFLAKE$SESSION’, ‘PRINCIPAL_NAME’) to identify the current user and tailor AI responses accordingly — for example, filtering data recommendations based on user role or department. Combined with `IS_ROLE_ACTIVATED`, agents can dynamically adjust their behavior based on the caller’s privileges, showing sensitive analytics only to authorized roles.</p><p>For organization-enabled accounts, <strong>SNOWFLAKE$ORGANIZATION</strong> functions can verify whether users are federated from an identity provider, enabling agents to apply different security policies for external vs. internal users.</p><p>Within <strong>Native Apps</strong> built using Cortex functions, <strong>SNOWFLAKE$APPLICATION</strong> properties help track version information for A/B testing AI models or logging which app version generated specific predictions. This contextual awareness transforms generic AI assistants into secure, role-based intelligent applications that respect Snowflake’s governance model.</p><h3>Conclusion</h3><p>Snowflake’s SYS_CONTEXT function is an incredibly versatile tool for building context-aware applications. By understanding the five namespaces and their properties:</p><ul><li><strong>SNOWFLAKE$SESSION</strong>: Your primary source for session and user context</li><li><strong>SNOWFLAKE$ENVIRONMENT</strong>: Essential for multi-account and regional deployments</li><li><strong>SNOWFLAKE$ORGANIZATION</strong>: Critical for organization-wide user and group management</li><li><strong>SNOWFLAKE$ORGANIZATION_SESSION</strong>: Bridges organization identity with sessions</li><li><strong>SNOWFLAKE$APPLICATION</strong>: Indispensable for Native App development</li></ul><p>You can build more secure, auditable, and intelligent applications on Snowflake. Whether implementing row-level security, comprehensive audit trails, or version-aware Native Apps, SYS_CONTEXT provides the contextual intelligence your applications need.</p><p>Start incorporating these patterns into your Snowflake development today, and unlock the full potential of context-aware SQL!</p><p><em>Have questions or want to share your own </em><em>SYS_CONTEXT use cases? Drop a comment below!!!</em></p><p><a href="https://medium.com/snowflake">Follow Snowflake Publications</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=547e9dd3089e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/mastering-snowflakes-sys-context-function-a-complete-guide-to-context-aware-sql-development-547e9dd3089e">Mastering Snowflake’s SYS_CONTEXT Function: A Complete Guide to Context-Aware SQL Development</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unified Analytics: How to Federate Snowflake Data into Databricks Unity Catalog]]></title>
            <link>https://medium.com/snowflake/unified-analytics-how-to-federate-snowflake-data-into-databricks-unity-catalog-38c6ad85c9a7?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/38c6ad85c9a7</guid>
            <category><![CDATA[unified-analytics]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Tue, 03 Feb 2026 20:17:36 GMT</pubDate>
            <atom:updated>2026-02-03T20:17:36.243Z</atom:updated>
            <content:encoded><![CDATA[<p>A step-by-step guide to connecting Snowflake storage directly to Databricks for powerful, zero-ETL unified analytics</p><h4>Introduction</h4><p>In today’s data-driven world, organizations often leverage multiple platforms to maximize their analytics capabilities. Snowflake’s cloud data platform combined with Databricks’ unified analytics platform creates a powerful ecosystem for modern data teams. This guide demonstrates how to seamlessly access Snowflake data directly from Databricks, enabling you to leverage the strengths of both platforms without the complexity of data movement.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oeCpFWgFSfdTI5vyGoE1iA.png" /><figcaption>Snowflake and Databricks Integration</figcaption></figure><h4>The Power of Snowflake’s Open Architecture (Quick Refresher)</h4><p>One of Snowflake’s greatest strengths is its open and interoperable architecture. Unlike traditional data warehouses that lock you into proprietary formats and interfaces, Snowflake was built from the ground up to integrate seamlessly with the modern data ecosystem:</p><p>🔌 Universal Connectivity</p><ul><li>Standard Protocols: JDBC, ODBC, REST APIs</li><li>Native Connectors: Spark, Kafka, Python, Node.js, .NET</li><li>Partner Ecosystem: 200+ technology partners</li><li>Cloud Agnostic: Works across AWS, Azure, GCP</li></ul><p>🏗️ Architecture Advantages</p><ul><li>Separation of Storage and Compute: Scale independently</li><li>Multi-Cluster Shared Data: Concurrent workloads without contention</li><li>Zero-Copy Cloning: Share data without duplication</li><li>Time Travel: Query historical data states</li></ul><p>This architectural flexibility means you can easily connect external analytics engines like Databricks to your Snowflake data without compromising performance or security.</p><h4>Benefits of This Integration</h4><p>✅ For Data Teams</p><ul><li>No Data Movement: Access Snowflake data directly from Databricks</li><li>Unified Analytics: Use Databricks ML on Snowflake data</li><li>Cost Optimization: Pay for compute only when needed</li><li>Single Source of Truth: Maintain data governance in Snowflake</li></ul><p>✅ For Organizations</p><ul><li>Reduced Complexity: Eliminate ETL pipelines for analysis</li><li>Faster Insights: Query data where it lives</li><li>Enhanced Security: Centralized access controls</li><li>Vendor Flexibility: Avoid lock-in, use best-of-breed tools</li></ul><h4><strong>Step-by-Step Implementation Guide</strong></h4><p>Objective: Access Snowflake tables in Databricks seamlessly. This document is provided for poc purposes only.</p><p>️️<em>Databricks service is hosted in azure and Snowflake account is Azure cloud deployment in east 2 region.</em></p><p><strong>Access requirement</strong></p><ul><li>Snowflake Account (Admin Access).</li><li>Databricks Workspace (Unity Catalog Enabled).</li><li>Cloud Provider Permissions (if using PrivateLink/VNet injection).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j1V--hJJ3PyY4QZITxDzlg.png" /></figure><p><strong>Part 1: Snowflake Setup</strong></p><p>First, we’ll create the necessary infrastructure in Snowflake to support external connections.</p><p>⚠️ Please change Account, Database, Schema, Table, Username as needed.</p><p>Note: Public and Private key used in this article are real so you don’t have a generate one.</p><p>1. Create Database and Schema</p><pre>USE ROLE SYSADMIN;<br>CREATE DATABASE IF NOT EXISTS snow_src_db;<br>CREATE SCHEMA IF NOT EXISTS snow_src_db.snow_src_schema;</pre><p>2. Create Sample Table with Data</p><pre>CREATE OR REPLACE TABLE snow_src_db.snow_src_schema.emp_table (<br>emp_id INT,<br>first_name VARCHAR(50),<br>last_name VARCHAR(50),<br>department VARCHAR(50),<br>salary DECIMAL(10,2),<br>hire_date DATE,<br>email VARCHAR(100)<br>);<br><br><br>INSERT INTO snow_src_db.snow_src_schema.emp_table VALUES<br>(1, &#39;John&#39;, &#39;Doe&#39;, &#39;Engineering&#39;, 75000.00, &#39;2020-01-15&#39;, &#39;john.doe@company.com&#39;),<br>(2, &#39;Jane&#39;, &#39;Smith&#39;, &#39;Marketing&#39;, 65000.00, &#39;2019-03-22&#39;, &#39;jane.smith@company.com&#39;),<br>(3, &#39;Mike&#39;, &#39;Johnson&#39;, &#39;Sales&#39;, 70000.00, &#39;2021-06-10&#39;, &#39;mike.johnson@company.com&#39;),<br>(4, &#39;Sarah&#39;, &#39;Williams&#39;, &#39;HR&#39;, 60000.00, &#39;2018-11-05&#39;, &#39;sarah.williams@company.com&#39;),<br>(5, &#39;David&#39;, &#39;Brown&#39;, &#39;Engineering&#39;, 80000.00, &#39;2020-09-12&#39;, &#39;david.brown@company.com&#39;),<br>(6, &#39;Lisa&#39;, &#39;Davis&#39;, &#39;Finance&#39;, 72000.00, &#39;2019-07-18&#39;, &#39;lisa.davis@company.com&#39;),<br>(7, &#39;Tom&#39;, &#39;Wilson&#39;, &#39;Engineering&#39;, 78000.00, &#39;2021-02-28&#39;, &#39;tom.wilson@company.com&#39;),<br>(8, &#39;Emily&#39;, &#39;Garcia&#39;, &#39;Marketing&#39;, 68000.00, &#39;2020-04-14&#39;, &#39;emily.garcia@company.com&#39;),<br>(9, &#39;Chris&#39;, &#39;Martinez&#39;, &#39;Sales&#39;, 74000.00, &#39;2019-12-03&#39;, &#39;chris.martinez@company.com&#39;),<br>(10, &#39;Anna&#39;, &#39;Rodriguez&#39;, &#39;HR&#39;, 62000.00, &#39;2021-08-25&#39;, &#39;anna.rodriguez@company.com&#39;),<br>(11, &#39;Kevin&#39;, &#39;Lee&#39;, &#39;Engineering&#39;, 82000.00, &#39;2018-05-30&#39;, &#39;kevin.lee@company.com&#39;),<br>(12, &#39;Michelle&#39;, &#39;Taylor&#39;, &#39;Finance&#39;, 76000.00, &#39;2020-10-07&#39;, &#39;michelle.taylor@company.com&#39;),<br>(13, &#39;James&#39;, &#39;Anderson&#39;, &#39;Sales&#39;, 71000.00, &#39;2019-01-20&#39;, &#39;james.anderson@company.com&#39;),<br>(14, &#39;Amanda&#39;, &#39;Thomas&#39;, &#39;Marketing&#39;, 66000.00, &#39;2021-11-12&#39;, &#39;amanda.thomas@company.com&#39;),<br>(15, &#39;Ryan&#39;, &#39;Jackson&#39;, &#39;Engineering&#39;, 79000.00, &#39;2020-03-08&#39;, &#39;ryan.jackson@company.com&#39;),<br>(16, &#39;Jennifer&#39;, &#39;White&#39;, &#39;HR&#39;, 63000.00, &#39;2018-08-16&#39;, &#39;jennifer.white@company.com&#39;),<br>(17, &#39;Mark&#39;, &#39;Harris&#39;, &#39;Finance&#39;, 73000.00, &#39;2021-04-02&#39;, &#39;mark.harris@company.com&#39;),<br>(18, &#39;Nicole&#39;, &#39;Clark&#39;, &#39;Sales&#39;, 69000.00, &#39;2019-09-14&#39;, &#39;nicole.clark@company.com&#39;),<br>(19, &#39;Brian&#39;, &#39;Lewis&#39;, &#39;Engineering&#39;, 81000.00, &#39;2020-12-19&#39;, &#39;brian.lewis@company.com&#39;),<br>(20, &#39;Stephanie&#39;, &#39;Walker&#39;, &#39;Marketing&#39;, 67000.00, &#39;2018-02-11&#39;, &#39;stephanie.walker@company.com&#39;);</pre><p>3. Create Dedicated Warehouse</p><pre>CREATE OR REPLACE WAREHOUSE dbx_reader_wh WITH<br>WAREHOUSE_SIZE = &#39;XSMALL&#39;<br>AUTO_SUSPEND = 300<br>AUTO_RESUME = TRUE<br>INITIALLY_SUSPENDED = TRUE;</pre><p>4. Set Up Role and Permissions</p><pre>USE ROLE ACCOUNTADMIN;<br>CREATE OR REPLACE ROLE dbx_reader_role;<br> - Grant database and schema access<br>GRANT USAGE ON DATABASE snow_src_db TO ROLE dbx_reader_role;<br>GRANT USAGE ON SCHEMA snow_src_db.snow_src_schema TO ROLE dbx_reader_role;<br> - Grant table access (current and future)<br>GRANT SELECT ON ALL TABLES IN SCHEMA snow_src_db.snow_src_schema TO ROLE dbx_reader_role;<br>GRANT SELECT ON FUTURE TABLES IN SCHEMA snow_src_db.snow_src_schema TO ROLE dbx_reader_role;<br> - Grant warehouse access<br>GRANT USAGE ON WAREHOUSE dbx_reader_wh TO ROLE dbx_reader_role;</pre><p>5. Create Service User</p><pre>CREATE OR REPLACE USER dbx_reader<br>PASSWORD = &#39;SecureP@ssw0rd123!&#39;<br>MUST_CHANGE_PASSWORD = FALSE;<br>GRANT ROLE dbx_reader_role TO USER dbx_reader;<br><br>--set a default role<br>ALTER USER dbx_reader SET DEFAULT_ROLE = &#39;dbx_reader_role&#39;;<br><br>--Avoid MFA prompt<br>ALTER USER dbx_reader SET TYPE = LEGACY_SERVICE;</pre><p>6. Configure Public Key Authentication (Optional)</p><p>For enhanced security, you can use key-pair authentication:</p><pre>ALTER USER dbx_reader SET RSA_PUBLIC_KEY=&#39;MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwzD0bdaoNhnmDU4Shx3P<br>GPYPxSjGgN1gSdaB5CNBTs+Htuo372/ttAAxxdGg+jS++CGl7GMQ+E7IaSs2S2rb<br>giFjTESqduIqaPDs2oXT10KgKLxhWD6KAi4JU/XWFpcErkd8qtkSkb/vlL/KdqrJ<br>ZNE1ya9Co8iegBiAF4OoF8WEGydvYSNIIjWMcRK+nQoLGoMcDnv5mD5rp4LUz2Xv<br>coSwv9YvVCG1NxpPCs+WUqwAm5MMP+i3yRo4Fsr2hdhd0ZLbJxOp7wbCUeuKlmjd<br>LcqnYy1oAT3VsWPwVAOj08fYVCmPnKYyiFXtftgNBycC/I6XddXIHd1F2UZEt7DH<br>IwIDAQAB&#39;;</pre><p>7. Network Policy (Development Only)</p><p>⚠️ For development/POC only — In production, restrict to specific IP ranges:</p><pre>CREATE OR REPLACE NETWORK POLICY ALLOW_ALL_POLICY<br>ALLOWED_IP_LIST = (&#39;0.0.0.0/0&#39;);<br>ALTER USER dbx_reader SET NETWORK_POLICY = ALLOW_ALL_POLICY;</pre><p><strong>Part 2: Databricks Setup</strong></p><p>Now we’ll configure Databricks to connect to our Snowflake environment.</p><p>Use serverless compute (recommended) or you may use custom compute</p><ol><li>Create Compute Cluster<br>A. Navigate to Compute in your Databricks workspace<br>B. Click Create Compute<br>C. Configure cluster:<br>* Name: snowflake-integration-cluster<br>* Runtime: 17.3 LTS (Scala 2.13, Spark 4.0.0)<br>* Node Type: Standard_DS3_v2 (14 GB Memory, 4 Cores)<br>* Auto Termination: 20 minutes of inactivity</li><li>Create Notebook<br>A. Navigate to Workspace → Create → Notebook<br>B. Name: “Snowflake Data Access Tutorial”<br>C. Select your cluster</li></ol><p>3. Option A: Password Authentication</p><p><em>Not using Databricks secret option for password for simplicity of this article</em></p><pre>-- Clean up any existing connections<br>DROP CATALOG IF EXISTS SNOW_SRC_DB;<br>DROP CONNECTION IF EXISTS dbx_reader_conn;<br><br>--Create connection with password authentication<br>CREATE CONNECTION dbx_reader_conn<br>TYPE snowflake<br>OPTIONS (<br>host &#39;example-account.east-us-2.azure.snowflakecomputing.com&#39;,<br>user &#39;DBX_READER&#39;,<br>password &#39;SecureP@ssw0rd123!&#39;,<br>sfWarehouse &#39;DBX_READER_WH&#39;,<br>sfRole &#39;DBX_READER_ROLE&#39;<br>);<br><br>--Review connection details<br>DESCRIBE CONNECTION dbx_reader_conn;</pre><p>4. Create Foreign Catalog</p><pre>--Create catalog using password based connection<br>CREATE FOREIGN CATALOG SNOW_SRC_DB<br>USING CONNECTION dbx_reader_conn<br>OPTIONS (<br>database &#39;SNOW_SRC_DB&#39;<br>);<br><br>-- Refresh catalog to sync metadata via sql or click refresh icon on catalog explorer<br>REFRESH FOREIGN CATALOG SNOW_SRC_DB;<br><br>-- Verify setup<br>SHOW CATALOGS;<br>SHOW SCHEMAS IN SNOW_SRC_DB;<br>SHOW TABLES IN SNOW_SRC_DB.SNOW_SRC_SCHEMA;</pre><p>5. Test Data Access</p><pre>-- Query Snowflake data directly from Databricks<br>SELECT * FROM SNOW_SRC_DB.SNOW_SRC_SCHEMA.EMP_TABLE LIMIT 10;<br>-- Perform analytics<br>SELECT<br>department,<br>COUNT(*) as employee_count,<br>AVG(salary) as avg_salary,<br>MAX(salary) as max_salary<br>FROM SNOW_SRC_DB.SNOW_SRC_SCHEMA.EMP_TABLE<br>GROUP BY department<br>ORDER BY avg_salary DESC;</pre><p>6. Option B: Key-Pair Authentication</p><p>For production environments, use key-pair authentication:</p><p><em>Not using Databricks secret option for password for simplicity of this article</em></p><pre><br>-- create connection to Snowflake<br>DROP CATALOG IF EXISTS SNOW_SRC_DB_KP;<br>DROP CONNECTION IF EXISTS dbx_reader_conn_kp;<br>CREATE CONNECTION dbx_reader_conn_kp<br>TYPE snowflake<br>OPTIONS (<br>host &#39;example-account.east-us-2.azure.snowflakecomputing.com&#39;,<br>user &#39;DBX_READER&#39;,<br>pem_private_key &#39;MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDWSS+7DBxhVWVMVkTkZvyHuxpa7hAUvANIQYprxc+pkom7iEQmxo1PlEohSVqOp1Mb/2VUfo22M4KdQhebICfzPAkCT9qkVv/tcgexvKvikfUBS2nPxj+MK9eBYLOijGHWS1BO3vu+srfB9WXn/ApdgNCLDuaqXBFGDRM/ecKktmTi5rbjbvV+gAkLscSyM8xu4zINYYnisyTRA9CFESwsaSPGuXqLuyq1XWQzqNxgYvAOj0SNgS4oawpNo/uWTo+aiar2zRTyqIL64hmdWQe0LFJM+oNVsMkX0hDNe+BEf0afr0QWnPv508mkYXgrNgbTZgec3UCJpF4E1IxdKs/5AgMBAAECggEAMSgU1PnQQNvQIxTfNO4THCflwdo+wiLnNdojINn96058XhLCX4rIu+qunrgvF+52eEO8QQfPBm8GlYCl3aYgxAGPp4IeC/kROK0h6rn3TEBjTeq42TsHNOaRx1IhozZE813PjGhGo9ZnpQzgVblRv2Vfcab3bzkzlThGpBcQeawaCH4g7eKdMc0sv2NfPuQVFAO5UA/rzQ2fFk8CaPUcLPP7ySGZaU+BTeZQQmRODk/SVvXnJmIsd/zJIneL3872Ect9+Saif7GT8G82NI/qN8u6XSlFeaC5PaYBdqc/CIAWgoefiPB+YJ6cCFxBZBe6JeD8OxhSUzL/9CMA6TfMoQKBgQD5dYyO4NEJBrKIUmzavnDPiSMCgWXR1m0v9H4m4bsqPaNY8x6RFnbFiQ46PF4boDwXhLcIJx4ZBrIEyDHQfsH403LmT5pMhzuuY2JK5CEx2DLYumXYxmQsih3sivoLDVTkcpdGZRksLxBHio+4+aRa3Rv68LCpQiOcGjrkXJHK1QKBgQDb54r39O8hE+Awt1wp1kfOrn99krST90U9Ke4ZwFwnBM3ilCtERTLHGDj9nOW8G8PMrHELQIczm/aCAlov3u8DZ2YOCHyFOKVnaFiQXt14nlV1MsDCE8Y2EoBbfNVPT6IBaYHNZGlLKJJR7dBa0wKZCJeX7T79unQ0h69VCa26lQKBgQC4R1LPFVq1/UGTnLgXG0nAtgCr0CUETw+XtlgepvAZczEg+3AgI+UXZXiiIoguhj797Kc9GZCNUjndeHn8GDd4ddNSOYm1cMzwtTAePMocxSZCneewrFXB5UHXxI6T7YRBwjjn4rUmw5mWD9C+B4SXLvGFfZozXs1peqL4oT1+BQKBgQC4vw8mf/WCkaMw6msYjvim8D0Vr3HcOcpGt3pc0hnZghTCfX7YDVa6o2QF1ZHAdocJ5fnW3TB5MFdqJDXGsAGXl3/K3GBYwB1eAfEd4UUpd8QOAUSmCFzmwqZptjezORC/JPezKd+4D+7Vu/jmxQ4SqYBKWDwqiEHme/PWepok/QKBgARJdlsJhf9H9Xy+BhQHq0k1ZS0IeXuAKmrgn4MM86AYfXGhFTDhP1Oqdk7c+ki1Z6UJIE16pl0UxjTktWurses/skEZuc7C5ZXCWdpKJE5FBymFFIgF4gwA9tFYr1aD5bbTexW3qN8M951ZP8npLLaGKg+GnGQILthwNHcDC4/U&#39;,<br>sfWarehouse &#39;DBX_READER_WH&#39;,<br>sfRole &#39;DBX_READER_ROLE&#39;<br>);<br><br>--Create catalog using key-pair based connection<br>CREATE FOREIGN CATALOG SNOW_SRC_DB_KP<br>USING CONNECTION dbx_reader_conn_kp<br>OPTIONS (<br>database &#39;SNOW_SRC_DB&#39;<br>);<br><br>-- Refresh catalog to sync metadata via sql or click refresh icon on catalog explorer<br>REFRESH FOREIGN CATALOG SNOW_SRC_DB_KP;<br><br>-- Verify setup<br>SHOW CATALOGS;<br>SHOW SCHEMAS IN SNOW_SRC_DB_KP;<br>SHOW TABLES IN SNOW_SRC_DB_KP.SNOW_SRC_SCHEMA;</pre><p>7. Test Data Access</p><pre>-- Query Snowflake data directly from Databricks<br>SELECT * FROM SNOW_SRC_DB_KP.SNOW_SRC_SCHEMA.EMP_TABLE LIMIT 10;<br>-- Perform analytics<br>SELECT<br>department,<br>COUNT(*) as employee_count,<br>AVG(salary) as avg_salary,<br>MAX(salary) as max_salary<br>FROM SNOW_SRC_DB_KP.SNOW_SRC_SCHEMA.EMP_TABLE<br>GROUP BY department<br>ORDER BY avg_salary DESC;</pre><h4>Important Considerations</h4><p>🔒 Security</p><ul><li>Network Policies: Restrict IP ranges in production]</li><li>Key-Pair Auth: Use for production environments</li><li>Role-Based Access: Follow principle of least privilege</li><li>Secret Management: Use Databricks secret management for credentials</li></ul><p>🚀 Performance</p><ul><li>Data Location: Consider data transfer costs between regions</li><li>Query Patterns: Large result sets transfer over network</li><li>Warehouse Sizing: Right-size Snowflake warehouses for workload</li><li>Cluster Scaling: Configure Databricks auto-scaling appropriately</li></ul><p>💰 Cost Management</p><ul><li>Auto-Suspend: Configure both platforms for automatic suspension</li><li>Resource Monitoring: Monitor compute usage on both sides</li><li>Query Optimization: Use LIMIT for development/testing</li><li>Storage: Data remains in Snowflake (no duplication costs)</li><li>Cross region data movement egress cost</li><li>Private link usage on Snowflake and Databricks</li></ul><p><em>Warning: This architecture prioritizes convenience and speed over raw storage cost. Heavy usage incurs compute costs on both platforms simultaneously.</em></p><h4>Important Limitations</h4><p>🔄 Data Movement</p><ul><li>Large Datasets: Full table scans require network transfer</li><li>Real-time: Not suitable for low-latency operational workloads</li><li>Write Operations: Foreign Catalogs (Federation) are currently read-only. To write data back to Snowflake, you must use the standard Snowflake Connector for Spark (spark.write.format(&#39;snowflake&#39;)...).</li></ul><p>🛠️ Feature Constraints</p><ul><li>Snowflake Features: Some advanced features may not be accessible</li><li>SQL Compatibility: Differences between Spark SQL and Snowflake SQL</li><li>Metadata Sync: Manual refresh required for schema changes</li></ul><p>🌐 Network Dependencies</p><ul><li>Connectivity: Requires stable internet connection</li><li>Latency: Network latency affects query performance</li><li>Firewall: May require network policy adjustments</li></ul><h4>Troubleshooting Common Issues</h4><p>Connection Problems</p><pre>- Test network connectivity<br>%sh<br>nc -vz example-account.east-us-2.azure.snowflakecomputing.com 443<br>ping -c 4 example-account.east-us-2.azure.snowflakecomputing.com</pre><p>Verification Steps</p><pre>- Verify connection<br>DESCRIBE CONNECTION dbx_reader_conn;<br> - Check permissions<br>SHOW CONNECTIONS;<br>SHOW CATALOGS;<br> - Test data access<br>SELECT COUNT(*) FROM SNOW_SRC_DB.SNOW_SRC_SCHEMA.EMP_TABLE;<br>SELECT COUNT(*) FROM SNOW_SRC_DB_KP.SNOW_SRC_SCHEMA.EMP_TABLE;<br></pre><h4>Benefits of This Architecture</h4><p>🎯 Operational Excellence</p><ul><li>Simplified Architecture: Reduce data pipeline complexity</li><li>Single Source of Truth: Maintain data governance in Snowflake</li><li>Cost Efficiency: Pay for compute when you need it</li><li>Faster Time to Value: Query data where it lives</li></ul><p>🔧 Technical Advantages</p><ul><li>No Data Duplication: Access fresh data in real-time</li><li>Unified Analytics: Combine Databricks ML with Snowflake data</li><li>Scalability: Independent scaling of storage and compute</li><li>Flexibility: Use best-of-breed tools for different workloads</li></ul><p>📈 Business Impact</p><ul><li>Faster Insights: Reduce time from data to decision</li><li>Lower TCO: Eliminate unnecessary data movement</li><li>Innovation Enablement: Free up resources for value-add activities</li><li>Risk Reduction: Centralized data governance and security</li></ul><h4>Summary</h4><p>The integration between Snowflake and Databricks represents the future of cloud analytics architecture. By leveraging Snowflake’s open, interoperable platform, organizations can:</p><p>✅ Access data directly without complex ETL processes</p><p>✅ Maintain single source of truth with centralized governance</p><p>✅ Use specialized tools for different analytical workloads</p><p>✅ Scale independently based on specific needs</p><p>✅ Reduce costs by eliminating data duplication</p><p>This architecture pattern enables data teams to focus on generating insights rather than managing data infrastructure, ultimately accelerating time to value and supporting data-driven decision making across the organization.</p><p>The combination of Snowflake’s enterprise-grade data platform with Databricks’ unified analytics capabilities provides a powerful foundation for modern data and AI workloads, demonstrating how the modern data stack should work: open, interoperable, and built for the cloud.</p><p>— -</p><p><em>Ready to implement this integration in your environment? Start with the step-by-step guide above, and remember to follow security best practices for production deployments.</em></p><p><em>Interested in Snowflake Iceberg tables, externally managed tables and accessing them in Databricks, leave a comment.</em></p><p>About the Author</p><p><em>This article demonstrates the power of cloud-native data platforms and their ability to work together seamlessly. For more insights on modern data architecture, follow me on Medium.</em></p><p>— -</p><p>Tags: #Snowflake #Databricks #DataEngineering #CloudAnalytics #DataIntegration #ModernDataStack #UnifiedAnalytics</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=38c6ad85c9a7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/unified-analytics-how-to-federate-snowflake-data-into-databricks-unity-catalog-38c6ad85c9a7">Unified Analytics: How to Federate Snowflake Data into Databricks Unity Catalog</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake UUID Type: The Modern Guide]]></title>
            <link>https://medium.com/@vishalverma_us/snowflake-uuid-type-the-modern-guide-cf5548ec6c96?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/cf5548ec6c96</guid>
            <category><![CDATA[snowflake-uuid]]></category>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[snowflake-data-types]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Sat, 31 Jan 2026 03:23:34 GMT</pubDate>
            <atom:updated>2026-01-31T03:23:34.850Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DGMRkDBkl9MkrQ0Jg6AYTw.png" /><figcaption>Snowflake powered UUID</figcaption></figure><p>For a long time, Snowflake developers had a standard, albeit slightly inefficient, way of handling Universally Unique Identifiers (UUIDs): they stored them as VARCHAR(36). While functional, this approach was never optimal for storage or performance.</p><p>With the introduction of the native <strong>UUID data type</strong>, Snowflake has brought a 128-bit optimized solution to the table. In this guide, we’ll explore the benefits, limitations, and practical use cases for this new type, including how to handle custom string casting.</p><h3>What is the UUID Data Type?</h3><p>A UUID is a 128-bit binary value designed to be globally unique. In its string representation, it’s typically seen as a 36-character hexadecimal string (e.g., f353ca91-4fc5-49f2-9b9e-304f83d11914).</p><p>While you <em>could</em> store this as text, Snowflake’s native UUID type understands the underlying binary structure. This allows for more efficient storage and faster processing than a standard string.</p><h3>The Core Benefits in Snowflake</h3><h3>1. Storage Efficiency</h3><p>Storing a UUID as a VARCHAR(36) consumes significantly more space than the 16 bytes (128 bits) required by the native UUID type. In massive tables with billions of rows, this difference results in smaller micro-partitions, reduced storage costs, and faster I/O.</p><h3>2. Performance Optimization</h3><p>Because the native UUID type is smaller and has a fixed length, Snowflake can process it more efficiently during joins and filters. Joins on UUID columns are generally faster than joins on their VARCHAR equivalents.</p><h3>3. Case Insensitivity</h3><p>UUIDs in Snowflake are case-insensitive. Whether you provide A1... or a1..., Snowflake recognizes them as the same identifier, eliminating the need for LOWER() or UPPER() functions in your JOIN predicates, which often break pruning.</p><h3>Use Cases: When to Use UUID vs. Others</h3><h3>✅ Use UUID when:</h3><ul><li><strong>Primary/Foreign Keys:</strong> It is the gold standard for distributed systems where keys are generated outside the database (e.g., in microservices).</li><li><strong>Merging Datasets:</strong> When you are consolidating data from multiple sources and need to ensure no key collisions occur.</li><li><strong>High-Volume Tables:</strong> When you want to minimize the storage footprint of ID columns in trillion-row tables.</li></ul><h3>❌ Stick to VARCHAR or INT when:</h3><ul><li><strong>Human Readability is Paramount:</strong> If users frequently need to type out IDs (though this is rare for UUIDs anyway).</li><li><strong>Auto-Incrementing Logic:</strong> If you need a strict chronological order (1, 2, 3…), use a standard IDENTITY or SEQUENCE with an integer type.</li><li><strong>External Tooling:</strong> If you use a legacy BI tool that doesn’t yet recognize the UUID type (though Snowflake drivers generally handle this by treating them as strings).</li></ul><h3>Working with UUIDs: Generation and Casting</h3><h3>Generating UUIDs</h3><p>Snowflake provides the UUID_STRING() function, which can be used to generate IDs on the fly. You can even set it as a default value for a column:</p><pre>--set context<br>use role sysadmin;<br>create database if not exists demo_db;<br>create schema if not exists demo_db.demo_schema;</pre><pre>--create table with uuid<br>CREATE OR REPLACE TABLE orders_uuid (<br>    order_id UUID DEFAULT UUID_STRING() primary key,<br>    customer_id VARCHAR,<br>    order_date TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()<br>);<br><br>--Table ORDERS_UUID successfully created.<br><br><br>--insert few rows, uuid and order date are auto generated<br>insert into orders_uuid(customer_id) values (1),(2),(3);<br><br>select order_id from orders_uuid;<br><br>-- f2d2778b-e23f-4915-b85e-b57743864cc8<br>-- e0289cc3-c13b-4519-91c1-ea86d68f609b<br>-- 621dc816-e412-4c94-92d6-a8c62827490d<br><br>--uuid in lowercase<br>select * from orders_uuid where order_id = &#39;f2d2778b-e23f-4915-b85e-b57743864cc8&#39;;<br>--1 row returned<br><br>--uuid to uppercase or Mixed case<br>select * from orders_uuid where order_id = &#39;F2D2778B-E23F-4915-B85E-B57743864CC8&#39;;<br>--1 row returned<br></pre><p>Pretty amazing Snowflake UUID data type are not case sensitive and this is a huge performance and cost advantage for large data set scan.</p><h3>Casting Custom Strings to UUID</h3><p>If you have existing data in string format, you can easily cast it to the native UUID type. This is crucial for migrating old tables to the new format.</p><pre>-- Explicit casting using :: syntax<br>SELECT &#39;f353ca91-4fc5-49f2-9b9e-304f83d11914&#39;::UUID;<br><br>--I personally prefer this way instead of cast function below</pre><pre>-- Using the CAST function<br>SELECT CAST(&#39;da563283-e201-4744-b158-221dd204a61f&#39; AS UUID);</pre><h3>Using UUID_STRING without Namespaces (Version 4)</h3><pre>-- UUID_STRING(&#39;&lt;namespace_uuid&gt;&#39;, &#39;&lt;name&gt;&#39;)<br>SELECT UUID_STRING();<br>--1d781e26-db01-4c87-a2bb-8356a4bf9338</pre><h3>Using UUID_STRING with Namespaces (Version 5)</h3><p>Sometimes you want a “deterministic” UUID — one that is the same every time you provide a specific input. UUID_STRING supports RFC 4122 Version 5 (named UUIDs):</p><pre>-- UUID_STRING(&#39;&lt;namespace_uuid&gt;&#39;, &#39;&lt;name&gt;&#39;)<br>SELECT UUID_STRING(&#39;fe971b24-9572-4005-b22f-351e9c09274d&#39;, &#39;my_custom_string&#39;)::UUID;</pre><h4><strong>The Parameters Explained</strong></h4><ul><li><strong>&lt;</strong>namespace_uuid<strong>&gt; (The Namespace):</strong> Think of this as the &quot;Category&quot; or &quot;Scope.&quot; It is a valid UUID that acts as a seed. It ensures that a name used in one project doesn&#39;t result in the same UUID as the same name used in a different project.</li><li><strong>&lt;name&gt; (user input string):</strong> This is the specific string you want to turn into a unique ID (e.g., an email address, a product SKU, or a customer login). This is so cool.</li></ul><h3>Limitations to Keep in Mind</h3><p>While powerful, the UUID type currently has a few “gotchas” in the Snowflake ecosystem:</p><ul><li><strong>Semi-structured Data:</strong> You cannot store a UUID directly inside an OBJECT or VARIANT column. You must cast it to a string first.</li><li><strong>Stored Procedures &amp; UDFs:</strong> Currently, the native UUID type is not supported in non-SQL languages like Python, Java, or Scala UDFs/Stored Procs. You’ll need to pass them as strings and cast them within your SQL calls.</li><li><strong>Snowpark:</strong> As of the latest updates, Snowpark does not yet have a first-class native UUID type, so strings remain the intermediary.</li></ul><h3>Conclusion</h3><p>The native UUID data type is a “low-hanging fruit” optimization for Snowflake architects. By migrating your ID columns from VARCHAR to UUID, you gain better performance, lower storage costs, and built-in validation for your identifiers.</p><p><strong>Pro-tip:</strong> When migrating, use a TRANSIENT table to test the storage savings on a representative sample of your data. You might be surprised at how much space you reclaim!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cf5548ec6c96" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Snowflake SQL REST API: A Complete Guide to JWT and PAT Authentication]]></title>
            <link>https://medium.com/@vishalverma_us/mastering-snowflake-sql-rest-api-a-complete-guide-to-jwt-and-pat-authentication-094c1166b798?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/094c1166b798</guid>
            <category><![CDATA[snowflake-sql-rest-api]]></category>
            <category><![CDATA[jwt-and-pat-token-auth]]></category>
            <category><![CDATA[pat-authentication]]></category>
            <category><![CDATA[snowflake-sql-api]]></category>
            <category><![CDATA[jwt-authentication]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Fri, 30 Jan 2026 05:25:02 GMT</pubDate>
            <atom:updated>2026-02-04T02:06:29.064Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Why Snowflake’s SQL REST API Changes the Game</strong></p><p>In today’s cloud-native world, Snowflake has emerged as the leading data cloud platform, revolutionizing how organizations store, process, and analyze data. But what makes Snowflake truly powerful isn’t just its data warehouse capabilities — it’s the flexibility it offers developers through its SQL REST API.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JT2xgQEh6LBjq-NW5G5Exg.png" /></figure><p><strong>The Snowflake Advantage (quick refresher)</strong></p><p>Separation of Storage and Compute: Unlike traditional databases, Snowflake’s architecture allows you to scale storage and compute independently. Pay only for what you use, when you use it.</p><p>Zero Maintenance: No indexes to maintain, no vacuuming, no tuning. Snowflake handles all the heavy lifting automatically.</p><p>Instant Elasticity: Scale up for heavy workloads, scale down during quiet periods — all without downtime.</p><p>Multi-Cloud Freedom: Run on AWS, Azure, or GCP. Switch between them without rewriting code or moving data.</p><p>Built-in Security: End-to-end encryption, role-based access control, and compliance certifications out of the box.</p><p>But here’s where it gets exciting: <strong>Snowflake’s SQL REST API</strong> lets you tap into all this power programmatically, without spinning up a full JDBC/ODBC connection. Perfect for microservices, serverless functions, and lightweight integrations.</p><p>— -</p><p>Understanding Snowflake’s SQL REST API</p><p>The SQL REST API (`/api/v2/statements`) is Snowflake’s HTTP-based interface for executing SQL statements. Think of it as a way to run queries without maintaining persistent database connections.</p><p>Why Use the REST API?</p><p>✅ Lightweight: No heavy database drivers required</p><p>✅ Stateless: Perfect for serverless architectures (AWS Lambda, Azure Functions)</p><p>✅ Language Agnostic: Works with any programming language that can make HTTP requests</p><p>✅ Asynchronous Support: Submit long-running queries and check status later</p><p>✅ Token-Based Security: Modern authentication without passwords</p><p>— -</p><p>Two Paths to Authentication: JWT vs PAT</p><p>Snowflake offers two primary authentication methods for the SQL REST API:</p><p>1. JWT (JSON Web Tokens) with Key-Pair Authentication</p><p>How it works: You generate an RSA key pair, upload the public key to Snowflake, and use the private key to sign JWT tokens.</p><p>Best for:</p><p>- Automated systems and service accounts</p><p>- CI/CD pipelines</p><p>- Applications requiring programmatic access</p><p>- Long-term, non-interactive authentication</p><p>Advantages:</p><p>- No password management needed</p><p>- Tokens expire automatically (59 minutes max)</p><p>- Audit trail of token generation</p><p>- Stronger security posture</p><p>2. PAT (Personal Access Tokens)</p><p>How it works: Generate a token directly in Snowflake that acts as a bearer token for API authentication.</p><p>Best for:</p><p>- Individual developer access</p><p>- Testing and development</p><p>- Short-term integrations</p><p>- Personal automation scripts</p><p>Advantages:</p><p>- Quick to generate (one SQL command)</p><p>- No key pair management</p><p>- Easy to revoke</p><p>- Simpler setup for getting started</p><p>— -</p><p>Building a Token Validator in Python</p><p>Let’s build a practical application that generates both JWT and PAT tokens, then validates them against Snowflake’s SQL REST API.</p><p>Prerequisites</p><pre>pip install requests PyJWT cryptography</pre><p>Step 1: Generate Your RSA Key Pair</p><p>First, create an unencrypted RSA private key for JWT authentication:</p><pre>openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt</pre><p>Extract the public key:</p><pre>openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub</pre><p>Upload the public key to Snowflake:</p><pre>--extract complete public key from rsa_key.pub file<br>ALTER USER your_username SET RSA_PUBLIC_KEY=&#39;MIIBIjANBgkq…&#39;;</pre><p>Step 2: Generate a PAT Token</p><p>Generate a Personal Access Token with one SQL command:</p><pre>ALTER USER your_username ADD PROGRAMMATIC ACCESS TOKEN token_name<br>--ROLE_RESTRICTION = &#39;&lt;string_literal&gt;&#39; --example SYSADMIN<br>--DAYS_TO_EXPIRY = &lt;integer&gt; --example 2<br>--MINS_TO_BYPASS_NETWORK_POLICY_REQUIREMENT = &lt;integer&gt; --example 5<br>--COMMENT = &#39;&lt;string_literal&gt;&#39; --example Python POC Rest API Temp Token<br>;<br><br></pre><p>ROLE_RESTRICTION controls the scope of access granted to PAT token by restricting to role provided. Be considerate that this should have both data and compute access in this case. Secondary roles will be disabled.</p><p>Recommendation: Always provide ROLE_RESTRICTION when using a service account, while for human users it can be optional because human users tends to access broader set of data sets during analysis. Service accounts typically have controlled access.</p><p>Snowflake will return your token immediately. Save it securely — you won’t be able to retrieve it again!</p><p>Step 3: The Complete Python Implementation</p><p>Here’s the complete, production-ready code that handles both authentication methods:</p><p>file: sql-rest-api-all-tests</p><pre>#!/usr/bin/env python3<br>import sys<br>import time<br>import logging<br>import requests<br>from typing import Dict, Tuple<br>from rest_sql_api_jwt_token_helper import get_jwt_token<br><br>logging.basicConfig(level=logging.INFO, format=&#39;%(asctime)s - %(levelname)s - %(message)s&#39;)<br>logger = logging.getLogger(__name__)<br><br>CONFIG = {<br>    &quot;org_account&quot;: &quot;your_account&quot;,<br>    &quot;account_url&quot;: &quot;https://your_account.snowflakecomputing.com&quot;,<br>    &quot;user&quot;: &quot;your_username&quot;,<br>    &quot;private_key_file&quot;: &quot;/path/to/rsa_key.p8&quot;,<br>    &quot;warehouse&quot;: &quot;your_warehouse&quot;,<br>    &quot;pat_token&quot;: &quot;your_pat_token_here&quot;<br>}<br><br>TEST_SQL = &quot;SELECT CURRENT_VERSION() AS VERSION, CURRENT_USER() AS USER, CURRENT_ROLE() AS ROLE, CURRENT_WAREHOUSE() AS WAREHOUSE, CURRENT_DATABASE() AS DATABASE, CURRENT_SCHEMA() AS SCHEMA&quot;<br><br>def generate_jwt_token(config: Dict) -&gt; Tuple[str, str]:<br>    try:<br>        jwt_config = {<br>            &#39;org_account&#39;: config[&#39;org_account&#39;],<br>            &#39;db_user&#39;: config[&#39;user&#39;],<br>            &#39;private_key_path&#39;: config[&#39;private_key_file&#39;],<br>            &#39;token_validity_minutes&#39;: 59,<br>            &#39;private_key_password&#39;: config.get(&#39;private_key_password&#39;)<br>        }<br>        jwt_token = get_jwt_token(jwt_config)<br>        if jwt_token:<br>            logger.info(f&quot;✓ JWT token generated: {jwt_token[:50]}...&quot;)<br>            return jwt_token, None<br>        return None, &quot;JWT token generation failed&quot;<br>    except Exception as e:<br>        return None, str(e)<br><br>def validate_token(token: str, token_type: str, skip_header: bool = False) -&gt; Tuple[bool, Dict]:<br>    try:<br>        headers = {<br>            &quot;Content-Type&quot;: &quot;application/json&quot;,<br>            &quot;Authorization&quot;: f&quot;Bearer {token}&quot;<br>        }<br>        if not skip_header:<br>            headers[&quot;X-Snowflake-Authorization-Token-Type&quot;] = token_type<br>        <br>        payload = {<br>            &quot;statement&quot;: TEST_SQL,<br>            &quot;timeout&quot;: 60,<br>            &quot;warehouse&quot;: CONFIG[&#39;warehouse&#39;]<br>        }<br>        <br>        start = time.time()<br>        response = requests.post(f&quot;{CONFIG[&#39;account_url&#39;]}/api/v2/statements&quot;, <br>                                headers=headers, json=payload, timeout=30)<br>        elapsed = time.time() - start<br>        <br>        if response.status_code == 200:<br>            result = response.json()<br>            if &#39;data&#39; in result and result[&#39;data&#39;]:<br>                logger.info(f&quot;✓ Success ({elapsed:.2f}s): {result[&#39;data&#39;]}&quot;)<br>                return True, {&#39;status&#39;: &#39;SUCCESS&#39;, &#39;data&#39;: result[&#39;data&#39;]}<br>        <br>        error = response.json() if response.status_code != 200 else {}<br>        logger.error(f&quot;✗ Failed: {error.get(&#39;message&#39;, response.text)}&quot;)<br>        return False, {&#39;status&#39;: &#39;FAILED&#39;, &#39;error&#39;: error.get(&#39;message&#39;, &#39;Unknown error&#39;)}<br>    except Exception as e:<br>        logger.error(f&quot;✗ Error: {str(e)}&quot;)<br>        return False, {&#39;status&#39;: &#39;ERROR&#39;, &#39;error&#39;: str(e)}<br><br>def run_tests():<br>    logger.info(&quot;=&quot;*70)<br>    logger.info(&quot;JWT &amp; PAT Token Validation Tests&quot;)<br>    logger.info(&quot;=&quot;*70)<br>    <br>    results = []<br>    <br>    logger.info(&quot;\nTest 1: JWT Token&quot;)<br>    jwt_token, error = generate_jwt_token(CONFIG)<br>    if jwt_token:<br>        success, result = validate_token(jwt_token, &#39;KEYPAIR_JWT&#39;)<br>        results.append((&#39;JWT&#39;, success))<br>    else:<br>        logger.error(f&quot;✗ JWT generation failed: {error}&quot;)<br>        results.append((&#39;JWT&#39;, False))<br>    <br>    logger.info(&quot;\nTest 2: PAT Token&quot;)<br>    pat_token = CONFIG.get(&#39;pat_token&#39;)<br>    if pat_token:<br>        logger.info(f&quot;✓ PAT token: {pat_token[:50]}...&quot;)<br>        success, result = validate_token(pat_token, &#39;OAUTH&#39;, skip_header=True)<br>        results.append((&#39;PAT&#39;, success))<br>    else:<br>        logger.error(&quot;✗ PAT token not configured&quot;)<br>        results.append((&#39;PAT&#39;, False))<br>    <br>    logger.info(&quot;\n&quot; + &quot;=&quot;*70)<br>    logger.info(&quot;Summary:&quot;)<br>    passed = sum(1 for _, s in results if s)<br>    for name, success in results:<br>        logger.info(f&quot;  {&#39;✓&#39; if success else &#39;✗&#39;} {name}: {&#39;PASSED&#39; if success else &#39;FAILED&#39;}&quot;)<br>    logger.info(f&quot;Result: {passed}/{len(results)} passed&quot;)<br>    logger.info(&quot;=&quot;*70)<br>    <br>    return 0 if passed == len(results) else 1<br><br>if __name__ == &quot;__main__&quot;:<br>    try:<br>        sys.exit(run_tests())<br>    except KeyboardInterrupt:<br>        logger.info(&quot;\nInterrupted&quot;)<br>        sys.exit(130)<br>    except Exception as e:<br>        logger.error(f&quot;Error: {str(e)}&quot;)<br>        sys.exit(1)</pre><p>Step 4: The JWT Helper Module</p><p>The code above depends on a helper module for JWT generation. Here’s the implementation:<br>file: rest_sql_api_jwt_token_helper.py</p><pre>import base64<br>import hashlib<br>import json<br>import logging<br>from datetime import timedelta, timezone, datetime<br>from getpass import getpass<br><br>import jwt<br>from cryptography.hazmat.backends import default_backend<br>from cryptography.hazmat.primitives.serialization import Encoding<br>from cryptography.hazmat.primitives.serialization import PublicFormat<br>from cryptography.hazmat.primitives.serialization import load_pem_private_key<br><br>logger = logging.getLogger(__name__)<br><br>class JWTGenerator(object):<br>    LIFETIME = timedelta(minutes=59)<br>    RENEWAL_DELTA = timedelta(minutes=54)<br>    ALGORITHM = &quot;RS256&quot;<br><br>    def __init__(self, account: str, user: str, private_key_file_path: str, <br>                 lifetime: timedelta = LIFETIME, renewal_delay: timedelta = RENEWAL_DELTA, <br>                 private_key_passphrase: str = None):<br>        <br>        self.account = self.prepare_account_name_for_jwt(account)<br>        self.user = user.upper()<br>        self.qualified_username = self.account + &quot;.&quot; + self.user<br>        self.lifetime = lifetime<br>        self.renewal_delay = renewal_delay<br>        self.private_key_file_path = private_key_file_path<br>        self.renew_time = datetime.now(timezone.utc)<br>        self.token = None<br><br>        with open(self.private_key_file_path, &#39;rb&#39;) as pem_in:<br>            pemlines = pem_in.read()<br>            try:<br>                self.private_key = load_pem_private_key(pemlines, None, default_backend())<br>            except TypeError:<br>                if private_key_passphrase:<br>                    self.private_key = load_pem_private_key(pemlines, private_key_passphrase.encode(),<br>                                                            default_backend())<br>                else:<br>                    self.private_key = load_pem_private_key(pemlines, getpass(&#39;Passphrase: &#39;).encode(),<br>                                                            default_backend())<br><br>    @staticmethod<br>    def prepare_account_name_for_jwt(raw_account: str) -&gt; str:<br>        account = raw_account<br>        if &#39;.global&#39; not in account:<br>            idx = account.find(&#39;.&#39;)<br>            if idx &gt; 0:<br>                account = account[0:idx]<br>        else:<br>            idx = account.find(&#39;-&#39;)<br>            if idx &gt; 0:<br>                account = account[0:idx]<br>        return account.upper()<br><br>    def get_token(self) -&gt; str:<br>        now = datetime.now(timezone.utc)<br>        <br>        if self.token is None or self.renew_time &lt;= now:<br>            self.renew_time = now + self.renewal_delay<br>            public_key_fp = self.calculate_public_key_fingerprint(self.private_key)<br>            <br>            payload = {<br>                &quot;iss&quot;: self.qualified_username + &#39;.&#39; + public_key_fp,<br>                &quot;sub&quot;: self.qualified_username,<br>                &quot;iat&quot;: now,<br>                &quot;exp&quot;: now + self.lifetime<br>            }<br>            <br>            token = jwt.encode(payload, key=self.private_key, algorithm=JWTGenerator.ALGORITHM)<br>            if isinstance(token, bytes):<br>                token = token.decode(&#39;utf-8&#39;)<br>            self.token = token<br>        <br>        return self.token<br><br>    @staticmethod<br>    def calculate_public_key_fingerprint(private_key) -&gt; str:<br>        public_key_raw = private_key.public_key().public_bytes(Encoding.DER, PublicFormat.SubjectPublicKeyInfo)<br>        sha256hash = hashlib.sha256()<br>        sha256hash.update(public_key_raw)<br>        public_key_fp = &#39;SHA256:&#39; + base64.b64encode(sha256hash.digest()).decode(&#39;utf-8&#39;)<br>        return public_key_fp<br><br>def get_jwt_token(config: json) -&gt; str:<br>    private_key_passphrase = config.get(&#39;private_key_password&#39;, None)<br>    <br>    jwt_token = JWTGenerator(config[&#39;org_account&#39;],<br>                             config[&#39;db_user&#39;],<br>                             config[&#39;private_key_path&#39;],<br>                             timedelta(minutes=config[&#39;token_validity_minutes&#39;]),<br>                             timedelta(minutes=config[&#39;token_validity_minutes&#39;]),<br>                             private_key_passphrase=private_key_passphrase<br>                             ).get_token()<br>    <br>    return jwt_token</pre><p>Running the Validator</p><p>Execute the script and watch both authentication methods in action:</p><pre>python3 sql-rest-api-all-tests.py</pre><p>Expected Output:</p><p>======================================================================</p><p>JWT &amp; PAT Token Validation Tests</p><p>======================================================================</p><p>Test 1: JWT Token</p><p>✓ JWT token generated: eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9…</p><p>✓ Success (0.70s): [[‘10.2.1’, ‘VVERMA’, ‘SYSADMIN’, None, ‘COMMON_UTILITY’, ‘SNOWSECURE’]]</p><p>Test 2: PAT Token</p><p>✓ PAT token: eyJraWQiOiIzMTc4NzE4MTg5ODU4ODIyIiwiYWxnIjoiRVMyNT…</p><p>✓ Success (0.71s): [[‘10.2.1’, ‘VVERMA’, ‘SYSADMIN’, None, ‘COMMON_UTILITY’, ‘SNOWSECURE’]]</p><p>======================================================================</p><p>Summary:</p><p>✓ JWT: PASSED</p><p>✓ PAT: PASSED</p><p>Result: 2/2 passed</p><p>======================================================================</p><p>Key Implementation Insights</p><p>1. JWT Authentication Headers</p><p>For JWT tokens, you must include the `X-Snowflake-Authorization-Token-Type` header:</p><pre>headers = {<br>    &quot;Authorization&quot;: f&quot;Bearer {jwt_token}&quot;,<br>    &quot;X-Snowflake-Authorization-Token-Type&quot;: &quot;KEYPAIR_JWT&quot;<br>}</pre><p>2. PAT Authentication Quirk</p><p>Interestingly, PAT tokens work without the token type header:</p><p># This works for PAT tokens</p><pre>headers = {<br>    &quot;Authorization&quot;: f&quot;Bearer {pat_token}&quot;<br>}</pre><p>3. Token Expiration</p><p>- JWT tokens: Maximum 59 minutes (enforced by Snowflake)</p><p>- PAT tokens: Configurable, default 180 days</p><p>- Always implement token refresh logic in production</p><p>4. Error Handling</p><p>The API returns helpful error codes:</p><p>- `390189`: Role not authorized</p><p>- `390303`: Invalid OAuth token</p><p>- `394313`: JWT subject claim empty</p><p>Production Best Practices</p><p>Security</p><p>🔒 Never commit tokens or private keys to version control</p><p>🔒 Use environment variables or secure vaults (AWS Secrets Manager, Azure Key Vault)</p><p>🔒 Rotate PAT tokens regularly</p><p>🔒 Use encrypted private keys for JWT in production</p><p>🔒 Implement proper token refresh logic</p><p>Performance</p><p>⚡ Reuse JWT tokens until expiration (don’t generate new ones for each request)</p><p>⚡ Use connection pooling for the HTTP client</p><p>⚡ Implement retry logic with exponential backoff</p><p>⚡ Cache token generation results</p><p>Monitoring</p><p>📊 Log all authentication attempts</p><p>📊 Track token expiration times</p><p>📊 Monitor API response times</p><p>📊 Set up alerts for authentication failures</p><p>When to Use Which Method?</p><p>Choose JWT When:</p><p>- Building automated systems</p><p>- Need fine-grained access control</p><p>- Want audit trails of access</p><p>- Integrating with service accounts</p><p>- Security is paramount</p><p>Choose PAT When:</p><p>- Quick prototyping</p><p>- Personal development work</p><p>- Short-term integrations</p><p>- Need fast setup without key management</p><p>- Testing and experimentation</p><p>Real-World Use Cases</p><p>1. Serverless Data Pipeline</p><p>Deploy an AWS Lambda function that uses JWT authentication to execute Snowflake queries, process results, and trigger downstream workflows — all without maintaining database connections.</p><p>2. CI/CD Integration</p><p>Use PAT tokens in GitHub Actions to run data quality tests against your Snowflake warehouse as part of your deployment pipeline.</p><p>3. Microservices Architecture</p><p>Multiple microservices query Snowflake via the REST API using JWT tokens, each with appropriate role-based access control.</p><p>4. Custom Analytics Dashboard</p><p>A React frontend calls a Node.js backend that uses the SQL REST API to fetch data from Snowflake, eliminating the need for traditional database drivers.</p><p>Troubleshooting Common Issues</p><p>Issue: “Invalid OAuth access token”</p><p>Solution: Remove the `X-Snowflake-Authorization-Token-Type` header for PAT tokens.</p><p>Issue: “Role not authorized”</p><p>Solution: Verify the user has the specified role assigned. Use default role by omitting the `role` parameter.</p><p>Issue: “JWT subject claim empty”</p><p>Solution: Ensure your account identifier doesn’t include region/cloud information when generating the JWT.</p><p>Issue: 404 Not Found</p><p>Solution: Check your account URL format. Use the full regional URL for non-standard accounts.</p><p>Validate if Authentication Policy is applied for all or specific user. This may be helpful to troubleshoot. Note, these command may require administrative privileges.</p><pre><br>USE ROLE ACCOUNTADMIN;<br>SHOW AUTHENTICATION POLICIES IN ACCOUNT;<br><br>SHOW AUTHENTICATION POLICIES ON USER &lt;USERNAME&gt;;<br><br>--Capture the database, schema, policy name and use in below command<br>USE SCHEMA &lt;database&gt;.&lt;schema&gt;;<br>DESC AUTHENTICATION POLICY &lt;policy_name&gt;;<br><br>--JWT token is produced via KEYPAIR authentication internally<br>--PAT token is obtained using OAuth<br>--Check for AUTHENTICATION_METHODS </pre><p>The Future of Snowflake API Access</p><p>Snowflake continues to invest heavily in its API ecosystem:</p><p>- GraphQL API for more flexible data queries</p><p>- Enhanced async support for long-running operations</p><p>- Improved token management with longer-lived tokens</p><p>- OAuth 2.0 integration for third-party applications</p><p>The SQL REST API is just the beginning. As Snowflake evolves, expect even more powerful programmatic access patterns.</p><p>Conclusion</p><p>Snowflake’s SQL REST API, combined with modern authentication methods like JWT and PAT tokens, opens up a world of possibilities for integrating your data cloud with any application stack.</p><p>Whether you’re building serverless functions, orchestrating data pipelines, or creating custom analytics tools, the REST API provides a lightweight, secure, and scalable way to tap into Snowflake’s power.</p><p>Key Takeaways:</p><p>✅ JWT tokens offer enterprise-grade security for automated systems</p><p>✅ PAT tokens provide quick, easy access for development</p><p>✅ The REST API eliminates heavy database driver dependencies</p><p>✅ Snowflake’s architecture makes it perfect for modern, cloud-native applications</p><p>Start experimenting today, and unlock the full potential of your Snowflake data cloud!</p><p>— -</p><p>Resources</p><p>- [Snowflake SQL REST API Documentation](https://docs.snowflake.com/en/developer-guide/sql-api/index.html)</p><p>- [Key Pair Authentication Guide](https://docs.snowflake.com/en/user-guide/key-pair-auth.html)</p><p>Have questions or insights about Snowflake authentication? Drop a comment below! I’d love to hear about your use cases and experiences.</p><p>Follow me for more deep dives into modern data engineering and cloud platforms.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=094c1166b798" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake Foundation Table to Managed Iceberg Table Migration Checklist]]></title>
            <link>https://medium.com/@vishalverma_us/snowflake-foundation-table-to-managed-iceberg-table-migration-checklist-1cdc2ebac9fc?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/1cdc2ebac9fc</guid>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Thu, 22 Jan 2026 23:39:39 GMT</pubDate>
            <atom:updated>2026-01-22T23:39:39.423Z</atom:updated>
            <content:encoded><![CDATA[<p>As organizations increasingly adopt open table formats, the shift from Snowflake’s native “Foundation” tables to <strong>Managed Iceberg Tables</strong> is becoming a critical architectural move. While Managed Iceberg Tables offer the best of both worlds — Snowflake’s performance engine combined with the interoperability of open standards — the migration process is rarely a simple “lift and shift.”</p><p>After navigating several complex migrations and learning a few hard lessons about what happens when dependencies are overlooked, I realized the need for a standardized approach. Below is a comprehensive <strong>Foundation-to-Iceberg Migration Checklist</strong>. It is designed to help you validate your schema, map dependencies, and execute your migration without disrupting your data ecosystem.</p><h3><strong>Pre-Migration Assessment</strong></h3><p><strong>Table Structure &amp; Schema</strong></p><p>- [ ] Verify table name and fully qualified name (DATABASE.SCHEMA.TABLE)</p><p>- [ ] Verify table type</p><p>- [ ] Document column names and order</p><p>- [ ] Document data types for each column</p><p>- [ ] Check for identity/auto-increment columns/sequence</p><p>- [ ] Check for default values on columns</p><p>- [ ] Verify NOT NULL constraints</p><p>- [ ] Document primary key constraints</p><p>- [ ] Document unique key constraints</p><p>- [ ] Document foreign key constraints</p><p>- [ ] Check for check constraints</p><p>- [ ] Verify collation settings per column</p><p>- [ ] Document comment on table</p><p>- [ ] Document comments on columns</p><p>- [ ] Document all Tags attached to the table and columns.</p><p><strong>Table Properties</strong></p><p>- [ ] Check if table is TRANSIENT</p><p>- [ ] Check if table is TEMPORARY</p><p>- [ ] Check if table is PERMANENT</p><p>- [ ] Verify retention time (TIME_TRAVEL) settings</p><p>- [ ] Check MAX_DATA_EXTENSION_TIME_IN_DAYS</p><p>- [ ] Document CHANGE_TRACKING status</p><p>- [ ] Document DATA_RETENTION_TIME_IN_DAYS</p><p>- [ ] Check ENABLE_SCHEMA_EVOLUTION setting</p><p>- [ ] Verify table ownership/owner role</p><p><strong>Performance Features</strong></p><p>- [ ] Document clustering keys (if any)</p><p>- [ ] Verify automatic clustering status</p><p>- [ ] Document search optimization service status</p><p>- [ ] Check if search optimization is configured for specific columns</p><p>- [ ] Document any table-level parameters</p><p><strong>Data Validation</strong></p><p>- [ ] Record current row count (SELECT COUNT(*) FROM table)</p><p>- [ ] Calculate table size in bytes</p><p>- [ ] Document active bytes vs time travel bytes</p><p>- [ ] Calculate data distribution across micro-partitions</p><p>- [ ] Run sample data queries to verify data integrity</p><p>- [ ] Document any NULL value patterns</p><p>- [ ] Check for duplicate records (if applicable)</p><p>- [ ] Verify min/max values for key columns</p><h3><strong>Dependencies Assessment</strong></h3><p><strong>Direct Object Dependencies</strong></p><p>- [ ] List all views that reference this table</p><p>- [ ] List all materialized views that reference this table</p><p>- [ ] List all dynamic tables that use this table as source</p><p>- [ ] List all streams on this table</p><p>- [ ] List all tasks that query this table</p><p>- [ ] List all stored procedures that reference this table</p><p>- [ ] List all user-defined functions (UDF) that reference this table</p><p>- [ ] List all external functions that reference this table</p><p>- [ ] List all pipes that load into this table</p><p><strong>Security &amp; Access</strong></p><p>- [ ] Document all grants and privileges on this table</p><p>- [ ] Document role with ownership</p><p>- [ ] Check for row access policies applied</p><p>- [ ] Check for column masking policies applied</p><p>- [ ] Check for projection policies applied</p><p>- [ ] Document future grants on schema that affect this table</p><p><strong>Data Sharing</strong></p><p>- [ ] Check if table is shared via Snowflake Data Sharing</p><p>- [ ] List all outbound shares including this table</p><p>- [ ] Check if table is part of a secure view in shares</p><p>- [ ] Check if table is part of any listings</p><p>- [ ] Document consumer accounts accessing this table</p><p><strong>Replication &amp; Cloning</strong></p><p>- [ ] Check if table is part of database replication</p><p>- [ ] Check if table is part of failover groups</p><p>- [ ] Document any clones of this table</p><p>- [ ] Check if this table is itself a clone</p><p>- [ ] Verify replication schedule (if applicable)</p><h3><strong>Iceberg Table Compatibility Check</strong></h3><p><strong>Feature Compatibility</strong></p><p>- [ ] Verify data types are supported in Iceberg format</p><p>- [ ] Check if clustering is needed (Iceberg uses different partitioning)</p><p>- [ ] Verify search optimization compatibility with Iceberg</p><p>- [ ] Check if time travel requirements are compatible (Iceberg supports snapshots)</p><p>- [ ] Verify if TRANSIENT property is needed (Iceberg tables are permanent)</p><p>- [ ] Check constraints support in Iceberg tables</p><p>- [ ] Verify VECTOR data type support (if using Cortex/GenAI features)</p><p>- [ ] Assess UPDATE/DELETE frequency (Churn rate) — COW / MOR</p><p><strong>Known Limitations</strong></p><p>- [ ] Verify no use of unsupported Iceberg features</p><p>- [ ] Check if table has GEOGRAPHY or GEOMETRY columns (Iceberg support)</p><p>- [ ] Verify VARIANT/OBJECT/ARRAY column compatibility</p><p>- [ ] Check for any semi-structured data type constraints</p><p>- [ ] Confirm no usage of external tables as dependencies</p><p>note: even if limitations are supported, ensure functionality will behave the same way</p><h3><strong>Migration Plan</strong></h3><p><strong>Pre-Migration Tasks</strong></p><p>- [ ] Create external volume for Iceberg table (if not exists)</p><p>- [ ] Verify External Volume storage is in the same region as Snowflake (avoid egress costs)</p><p>- [ ] Validate Cloud Provider permissions (AWS IAM/Azure Principal/GCP SA) for the External Volume.</p><p>- [ ] Define catalog integration (if using external catalog)</p><p>- [ ] Plan migration window and downtime</p><p>- [ ] Communicate migration plan to stakeholders</p><p>- [ ] Set up rollback plan</p><p>- [ ] Create test environment for validation</p><p>- [ ] Document expected storage location</p><p><strong>Migration Options</strong></p><p>- [ ] Decide on migration method: CREATE TABLE AS SELECT (CTAS)</p><p>- [ ] Decide on migration method: ALTER TABLE…CONVERT TO ICEBERG</p><p>- [ ] Decide on migration method: Manual CREATE + INSERT</p><p>- [ ] Custom script/framework</p><p>- [ ] Plan for data validation post-migration</p><p>- [ ] Define partition strategy for Iceberg table (if applicable)</p><p>- [ ] Define sort order for Iceberg table (if applicable)</p><p><strong>Migration Execution</strong></p><p>- [ ] Create Iceberg table with matching schema (table definition)</p><p>- [ ] Preserve column order in Iceberg table</p><p>- [ ] Apply collation settings to Iceberg table columns</p><p>- [ ] Migrate data (full load or incremental)</p><p>- [ ] Verify row count matches source table</p><p>- [ ] Validate data integrity (checksums, sample queries)</p><p>- [ ] Re-apply clustering strategy (as partitioning in Iceberg)</p><p>- [ ] Configure search optimization on Iceberg table (if needed)</p><p>- [ ] Apply table comments and column comments</p><p>- [ ] Set retention time and time travel settings</p><h3><strong>Post-Migration Validation</strong></h3><p><strong>Data Validation</strong></p><p>- [ ] Compare row counts: source vs Iceberg table</p><p>- [ ] Verify column data types match</p><p>- [ ] Verify column order matches</p><p>- [ ] Run aggregation queries to compare results</p><p>- [ ] Verify NULL value patterns match</p><p>- [ ] Check for any data loss or corruption</p><p>- [ ] Validate min/max values for key columns</p><p>- [ ] Test joins with other tables</p><p>- [ ] Verify query performance on Iceberg table</p><p><strong>Metadata Validation</strong></p><p>- [ ] Verify table comments are preserved</p><p>- [ ] Verify column comments are preserved</p><p>- [ ] Verify collation settings are preserved</p><p>- [ ] Check ownership is correctly set</p><p>- [ ] Verify time travel works as expected</p><p><strong>Performance Validation</strong></p><p>- [ ] Run SELECT queries and compare performance</p><p>- [ ] Test INSERT operations</p><p>- [ ] Test UPDATE operations (if applicable)</p><p>- [ ] Test DELETE operations (if applicable)</p><p>- [ ] Test MERGE operations (if applicable)</p><p>- [ ] Verify partition pruning effectiveness</p><p>- [ ] Compare query plans between FDN and Iceberg</p><p>- [ ] Monitor micro-partition count</p><p>- [ ] Check clustering depth (if applicable)</p><h3><strong>Dependency Recreation</strong></h3><p><strong>Recreate Dependent Objects</strong></p><p>- [ ] Recreate or update views to point to Iceberg table</p><p>- [ ] Recreate or update materialized views</p><p>- [ ] Recreate or update dynamic tables</p><p>- [ ] Recreate streams on Iceberg table</p><p>- [ ] Update tasks to reference Iceberg table</p><p>- [ ] Update stored procedures to reference Iceberg table</p><p>- [ ] Update UDFs if needed</p><p>- [ ] Update pipes if applicable</p><p>- [ ] Test all dependent objects</p><p>- [ ] Acknowledge Stream staleness strategy (Old streams cannot be moved)</p><p><strong>Update Access Controls</strong></p><p>- [ ] Recreate all grants and privileges</p><p>- [ ] Reapply ownership</p><p>- [ ] Reapply row access policies</p><p>- [ ] Reapply masking policies</p><p>- [ ] Reapply projection policies</p><p>- [ ] Set future grants on schema (if applicable)</p><p>- [ ] Test access with different roles</p><p>- [ ] Grant may include additional external volume access</p><p><strong>Update Data Sharing</strong></p><p>- [ ] Add Iceberg table to outbound shares (if applicable)</p><p>- [ ] Verify consumers can access Iceberg table</p><p>- [ ] Update secure views in shares (if applicable)</p><p>- [ ] Update listings to include Iceberg table</p><p>- [ ] Test cross-account access</p><p><strong>Update Replication</strong></p><p>- [ ] Include Iceberg table in database replication (if applicable)</p><p>- [ ] Update failover groups</p><p>- [ ] Test failover with Iceberg table</p><p>- [ ] Verify replication lag</p><p><strong>Application Updates</strong></p><p><strong>Code Changes</strong></p><p>- [ ] Update application queries to reference Iceberg table</p><p>- [ ] Update ETL/ELT pipelines</p><p>- [ ] Update BI tool connections/queries</p><p>- [ ] Update data integration scripts</p><p>- [ ] Update orchestration workflows (Airflow, dbt, etc.)</p><p>- [ ] Update documentation with new table name/location</p><p><strong>Testing</strong></p><p>- [ ] Run unit tests against Iceberg table</p><p>- [ ] Run integration tests</p><p>- [ ] Perform UAT (User Acceptance Testing)</p><p>- [ ] Verify batch jobs complete successfully</p><p>- [ ] Verify real-time data ingestion works</p><p>- [ ] Test error handling and edge cases</p><h3><strong>Monitoring &amp; Optimization</strong></h3><p><strong>Post-Migration Monitoring</strong></p><p>- [ ] Monitor query performance on Iceberg table</p><p>- [ ] Monitor storage growth</p><p>- [ ] Monitor credit usage</p><p>- [ ] Set up alerts for data quality issues</p><p>- [ ] Monitor time travel usage</p><p>- [ ] Track query patterns and optimize accordingly</p><p><strong>Optimization</strong></p><p>- [ ] Optimize partition strategy based on query patterns</p><p>- [ ] Fine-tune clustering/sort order</p><p>- [ ] Adjust search optimization configuration</p><p>- [ ] Run OPTIMIZE command on Iceberg table (if needed)</p><p>- [ ] Review and adjust table maintenance schedules</p><h3><strong>Decommissioning Old Table</strong></h3><p><strong>Cleanup Tasks</strong></p><p>- [ ] Verify all applications using Iceberg table</p><p>- [ ] Verify no queries hitting old FDN table</p><p>- [ ] Remove grants from old FDN table</p><p>- [ ] Drop old streams on FDN table</p><p>- [ ] Document final state of FDN table before drop</p><p>- [ ] Backup old table metadata for audit</p><p>- [ ] Drop or rename old FDN table</p><p>- [ ] Clean up any temporary objects created during migration</p><p>- [ ] Update architecture documentation</p><h3><strong>Final Sign-off</strong></h3><p><strong>Documentation</strong></p><p>- [ ] Document migration process and lessons learned</p><p>- [ ] Update data dictionary with Iceberg table details</p><p>- [ ] Update data lineage diagrams</p><p>- [ ] Update runbooks and operational procedures</p><p>- [ ] Archive migration logs and validation reports</p><p><strong>Stakeholder Communication</strong></p><p>- [ ] Notify stakeholders of successful migration</p><p>- [ ] Provide new table access instructions</p><p>- [ ] Share performance comparison results</p><p>- [ ] Document known issues or limitations (if any)</p><p>- [ ] Schedule post-migration review meeting</p><p>Migration Date: <em>__</em>______________</p><p>Migrated By: <em>__</em>______________</p><p>Reviewed By:<em>__</em>______________</p><p>Sign-off Date: <em>__</em>______________</p><p><strong>Final Thoughts</strong></p><p>Migrating to Iceberg is more than just a change in storage format; it’s a step toward a more open and flexible data architecture. However, success lies in the preparation. By methodically following this checklist, you ensure that security, governance, and performance are preserved in your new environment.</p><p><strong>Note on Region Co-location:</strong> Ensure your target Iceberg cloud storage (External Volume) is located in the same region as your Snowflake account. Cross-region data movement will incur significant egress/ingress costs. While this is typically addressed during architectural planning, verify it now to avoid unexpected billing spikes.</p><p>If you need assistance with your migration strategy, don’t hesitate to reach out to your Snowflake account team or Snowflake support for guidance.</p><p><strong>Did I miss anything?</strong> Migration scenarios can vary wildly. If you have run into a checklist item I overlooked, please share it in the comments below, and I will update the article so the community can benefit.</p><p>Thank you, <strong>Vishal Verma</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1cdc2ebac9fc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unlocking Your Lakehouse: Querying Snowflake-Managed Iceberg Tables with Spark via the Horizon REST…]]></title>
            <link>https://medium.com/snowflake/unlocking-your-lakehouse-querying-snowflake-managed-iceberg-tables-with-spark-via-the-horizon-rest-18adaf6b0bbe?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/18adaf6b0bbe</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Thu, 04 Dec 2025 15:07:52 GMT</pubDate>
            <atom:updated>2026-02-03T21:39:28.003Z</atom:updated>
            <content:encoded><![CDATA[<h3>Unlocking Your Lakehouse: Querying Snowflake-Managed Iceberg Tables with Spark via the Horizon REST API</h3><p>The data lakehouse architecture promises the best of both worlds: the scalability and flexibility of a data lake combined with the performance and transactional integrity of a data warehouse. <strong>Apache Iceberg</strong> has emerged as a key open standard for implementing this vision. <strong>Snowflake</strong> has embraced this standard, and with the introduction of the <strong>Snowflake Horizon Catalog</strong>, it’s now easier than ever to achieve true interoperability between your Snowflake-managed Iceberg tables and external query engines like <strong>Apache Spark</strong>.</p><p>REST API bring significant inter-operability and flexible adoption by various tools, engines and frameworks. Snowflake scalable architecture provides high performant and high throughput REST API infrastructure.</p><p>Accessing Snowflake-managed Iceberg tables through traditional methods, such as syncing them with an external catalog like Snowflake Open Catalog, often introduces complexity and potential overhead. This can lead to challenges in maintaining data consistency and managing duplicate catalog information.</p><p>This offers significant advantages by providing a direct, secure, and efficient way to query these tables from external engines. Some of the key benefits includes but not limited to:</p><ul><li><strong>Security</strong>: This method leverages existing Snowflake security framework, including users, roles, policies, and authentication. Access is controlled through standard Snowflake credentials like OAuth or programmatic access tokens, JWT tokens, ensuring that external engines adhere to the same governance policies you’ve already established in you Snowflake account.</li><li><strong>Cost-Effectiveness</strong>: By eliminating the need to sync or move data, we can reduce storage and potential data egress costs. The REST API allows external engines to read the Iceberg metadata directly from Snowflake’s catalog and access the underlying data files using vended credentials, streamlining the process without data duplication.</li><li><strong>Performance and Simplicity</strong>: It simplifies the architecture by providing a single endpoint for any external query engine that supports the Iceberg REST protocol. This removes the operational burden of managing a separate catalog and sync processes, allowing for more direct and potentially faster access to the most up-to-date data managed by Snowflake.</li></ul><p>Considerations:</p><ul><li><strong>Read-only access</strong>: Currently, you can only query tables; write operations are not supported via the API.</li><li><strong>Supported Tables</strong>: Only Snowflake-managed Iceberg tables are supported. Native Snowflake tables, remote Iceberg tables, and dynamic tables are not accessible.</li><li><strong>Data Policies</strong>: Tables with row access policies or column-level security (masking) cannot be queried through the API.</li><li><strong>Cloud Storage</strong>: The feature is supported for tables stored on AWS S3, Google Cloud Storage, or Azure.</li></ul><p>Following section will guide you through the process of setting up and using the Horizon REST API to read Snowflake-managed Iceberg tables directly from a PySpark environment.</p><p>High Level Architecture:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*ocpFHF_dZjretAafL2a9kw.png" /><figcaption>Iceberg Rest API on Snowflake Horizon Catalog</figcaption></figure><h3>Prerequisites: Setting Up Your Environment</h3><p>Before we dive into the code, we need to configure both the Snowflake environment and our local machine.</p><h4>1. Snowflake Service Account and Role Setup</h4><p>First, we’ll create a dedicated service account and role in Snowflake to handle authentication and authorization for our external Spark job. This ensures that access is properly controlled and auditable. The role will be granted the necessary privileges to access the specific Iceberg table.</p><p>Setup requires you to setup an external volume where your Iceberg metadata and data will reside. See Snowflake documentation on creating external volume or you can leverage any existing external volume you may have already created.</p><p><a href="https://docs.snowflake.com/en/sql-reference/sql/create-external-volume">CREATE EXTERNAL VOLUME | Snowflake Documentation</a></p><pre>USE ROLE SYSADMIN;<br>CREATE DATABASE IF NOT EXISTS ICEBERG_TEST_DB;<br><br>set external_volume = &#39;YOUR_EXTERNAL_VOLUME_NAME&#39;;<br>alter database ICEBERG_TEST_DB set external_volume = $external_volume;<br><br>CREATE OR REPLACE ICEBERG TABLE ICEBERG_TEST_DB.PUBLIC.TEST_TABLE_EXTENDED (<br>  col_int INT comment &#39;int column&#39;, <br>  col_string STRING comment &#39;string column&#39;,<br>  col_timestamp_ntz timestamp_ntz(6) comment &#39;timestamp_ntz column&#39;<br>  )<br>  CATALOG=&#39;SNOWFLAKE&#39; <br>  EXTERNAL_VOLUME=$external_volume <br>  BASE_LOCATION=&#39;test_table_extended&#39;;<br><br>insert into ICEBERG_TEST_DB.PUBLIC.TEST_TABLE_EXTENDED (col_int, col_string, col_timestamp_ntz)<br>values (1, &#39;test&#39;, &#39;2025-01-01 01:00:00&#39;), <br>(2, &#39;test2&#39;, &#39;2025-01-02 02:00:00&#39;),<br>(3, &#39;test3&#39;, &#39;2025-01-03 03:00:00&#39;);<br><br><br><br>USE ROLE SECURITYADMIN;<br><br>-- Setup service account role and grant access to an iceberg table<br>CREATE OR REPLACE ROLE HORIZON_REST_SRV_ACCOUNT_ROLE;<br>-- Grant usage on the database and schema<br>GRANT USAGE,MONITOR ON DATABASE ICEBERG_TEST_DB TO ROLE HORIZON_REST_SRV_ACCOUNT_ROLE;<br>GRANT USAGE,MONITOR ON SCHEMA ICEBERG_TEST_DB.PUBLIC TO ROLE HORIZON_REST_SRV_ACCOUNT_ROLE;<br>-- Grant select on the specific Iceberg table<br>GRANT SELECT ON TABLE ICEBERG_TEST_DB.PUBLIC.TEST_TABLE_EXTENDED TO ROLE HORIZON_REST_SRV_ACCOUNT_ROLE;<br><br>-- Create a service user and assign the role<br>CREATE OR REPLACE USER HORIZON_REST_SRV_ACCOUNT_USER TYPE=SERVICE DEFAULT_ROLE=HORIZON_REST_SRV_ACCOUNT_ROLE;<br>GRANT ROLE HORIZON_REST_SRV_ACCOUNT_ROLE TO USER HORIZON_REST_SRV_ACCOUNT_USER;</pre><h4>2. Generate a Programmatic Access Token (PAT)</h4><p>Next, we need an access token for authentication. For this example, we’ll generate a <strong>Programmatic Access Token (PAT)</strong> for our new service user. This token will act as our credential when connecting from Spark.</p><pre>USE ROLE ACCOUNTADMIN;<br>-- Generate a PAT for the service user, valid for 1 day<br>ALTER USER IF EXISTS HORIZON_REST_SRV_ACCOUNT_USER<br>ADD PAT HORIZON_REST_SRV_ACCOUNT_USER_PAT<br>  DAYS_TO_EXPIRY = 1<br>  ROLE_RESTRICTION = &#39;HORIZON_REST_SRV_ACCOUNT_ROLE&#39;<br>  COMMENT = &#39;HORIZON REST API PAT FOR SERVICE ACCOUNT&#39;;</pre><p>After running this command, Snowflake will provide you with the token secret. <strong>Be sure to copy this token immediately</strong>, as it will not be shown again. This is the token we will use in our Python script.</p><h4>3. Local Python Environment Setup</h4><p>On your local machine, you’ll need a Conda environment with PySpark and its dependencies. The following commands will create the environment and install the necessary packages. You may use Jupyter notebook if you prefer.</p><pre># Create the conda environment<br>conda create --name horizon_irc -c conda-forge findspark=2.0.1 jupyter=1.0.0 pyspark=3.5.0<br># Activate the environment<br>conda activate horizon_irc</pre><h3>The PySpark Implementation: Reading Iceberg Tables</h3><p>Now we’re ready to write the Python script that connects to Snowflake’s Horizon REST API and queries our Iceberg table.</p><h4>Configuration Settings</h4><p>The script begins by defining the key configuration parameters. These tell Spark how to find and authenticate with your Horizon Catalog.</p><pre># Snowflake Horizon Catalog Configuration, change as per your environment<br>ACCOUNT_NAME = &quot;ORG-ACCOUNTNAME&quot; <br>ROLE_NAME = &quot;HORIZON_REST_SRV_ACCOUNT_ROLE&quot;<br>HORIZON_CATALOG_URI = f&quot;https://{ACCOUNT_NAME}.snowflakecomputing.com/polaris/api/catalog&quot;<br>HORIZON_SESSION_ROLE = f&quot;session:role:{ROLE_NAME}&quot;<br>CATALOG_NAME = &quot;ICEBERG_TEST_DB&quot;<br>SCHEMA_NAME = &quot;PUBLIC&quot;<br>ICEBERG_TABLE_NAME = &quot;TEST_TABLE_EXTENDED&quot;<br><br># Azure Configuration (where the Iceberg data is stored)<br>AZURE_REGION = &quot;eastus2&quot;<br><br># Paste the PAT you generated in Snowflake here<br>TOKEN = &quot;YOUR_PROGRAMMATIC_ACCESS_TOKEN_HERE&quot;<br><br># Dependency Versions<br>ICEBERG_VERSION = &quot;1.9.1&quot;</pre><p>A few key variables to note:</p><ul><li><strong>ACCOUNT_NAME</strong>: This is your Snowflake account identifier (organization-account_name).</li><li><strong>HORIZON_CATALOG_URI</strong>: This is the standard endpoint for the Horizon REST API, formatted as https://&lt;snowflakeaccount&gt;.snowflakecomputing.com/polaris/api/catalog.</li><li><strong>HORIZON_SESSION_ROLE</strong>: This specifies the role to use within the Snowflake session, prefixed with session:role:.</li><li><strong>TOKEN</strong>: This is the PAT you saved earlier.</li></ul><h4>Spark Session Setup</h4><p>The core of the connection logic is within the Spark session configuration. Here, we define the catalog type as rest, point it to our HORIZON_CATALOG_URI, and provide the authentication token and role scope. We also specify necessary Iceberg and Azure JAR packages.</p><pre>def create_spark_session():<br>    &quot;&quot;&quot;Create and configure Spark session for Snowflake Iceberg access.&quot;&quot;&quot;<br>    spark = (<br>        SparkSession.builder<br>        .appName(&quot;SnowflakeIcebergReader&quot;)<br>        .master(&quot;local[*]&quot;)<br># JAR Dependencies for Iceberg and Azure<br>        .config(<br>            &quot;spark.jars.packages&quot;,<br>            f&quot;org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:{ICEBERG_VERSION},&quot;<br>            f&quot;org.apache.iceberg:iceberg-azure-bundle:{ICEBERG_VERSION}&quot;<br>            # for AWS storage, use the below package and comment above azure bundle<br>            # f&quot;org.apache.iceberg:iceberg-aws-bundle:{ICEBERG_VERSION}&quot;<br>        )<br>        # Iceberg SQL Extensions<br>        .config(&quot;spark.sql.extensions&quot;, &quot;org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions&quot;)<br>        .config(&quot;spark.sql.defaultCatalog&quot;, CATALOG_NAME)<br>        # Horizon REST Catalog Configuration<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}&quot;, &quot;org.apache.iceberg.spark.SparkCatalog&quot;)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.type&quot;, &quot;rest&quot;)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.uri&quot;, HORIZON_CATALOG_URI)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.warehouse&quot;, CATALOG_NAME)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.credential&quot;, TOKEN)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.scope&quot;, HORIZON_SESSION_ROLE)<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.client.region&quot;, AZURE_REGION)<br>        <br>        # Required for vended credentials<br>        .config(f&quot;spark.sql.catalog.{CATALOG_NAME}.header.X-Iceberg-Access-Delegation&quot;, &quot;vended-credentials&quot;)<br>        .config(&quot;spark.sql.iceberg.vectorization.enabled&quot;, &quot;false&quot;)<br>        .getOrCreate()<br>    )<br>    spark.sparkContext.setLogLevel(&quot;ERROR&quot;)<br>    return spark</pre><p>Thevended-credentials header is crucial; it tells Horizon API to provide temporary credentials to Spark for accessing the underlying data files in cloud storage. Default validity if vended credentials is 60 minutes.</p><h4>Executing Queries</h4><p>With the session configured, executing queries is as simple as running standard Spark SQL commands. The script demonstrates how to list namespaces (schemas), tables, and finally, query the data itself.</p><pre>def main():<br>    &quot;&quot;&quot;Main execution function.&quot;&quot;&quot;<br>    print(&quot;Creating Spark session...&quot;)<br>    <br>try:<br>        spark = create_spark_session()<br>        # Display available namespaces (schemas)<br>        print(&quot;\n=== Available Namespaces ===&quot;)<br>        spark.sql(&quot;SHOW NAMESPACES&quot;).show(truncate=False)<br>        # Use the specified namespace<br>        spark.sql(f&quot;USE {CATALOG_NAME}.{SCHEMA_NAME}&quot;)<br>        # List tables in the schema<br>        print(f&quot;\n=== Tables in {CATALOG_NAME}.{SCHEMA_NAME} ===&quot;)<br>        spark.sql(f&quot;SHOW TABLES IN {CATALOG_NAME}.{SCHEMA_NAME}&quot;).show(truncate=False)<br>        # Describe the table schema<br>        print(f&quot;\n=== Describe Table ===&quot;)<br>        spark.sql(f&quot;DESCRIBE TABLE {CATALOG_NAME}.{SCHEMA_NAME}.{ICEBERG_TABLE_NAME}&quot;).show(truncate=False)<br>        # Query the Iceberg table<br>        print(f&quot;\n=== Querying Table ===&quot;)<br>        spark.sql(f&quot;SELECT * FROM {CATALOG_NAME}.{SCHEMA_NAME}.{ICEBERG_TABLE_NAME}&quot;).show(n=10, truncate=False)<br>    except Exception as e:<br>        print(f&quot;Error during execution: {e}&quot;)<br>    finally:<br>        print(&quot;\nStopping Spark session...&quot;)<br>        spark.stop()<br><br>if __name__ == &quot;__main__&quot;:<br>    main()<br></pre><p>Running this script will connect to your Snowflake account, authenticate using the PAT, and execute the SQL commands, displaying the results directly in your console.</p><h3>Privatelink Support</h3><p>Snowflake Horizon Rest API for Apache Iceberg support Private link connectivity. There are two private link path discovery, one between Snowflake and External Volume and other one between client and storage account.</p><p>Here is link for <a href="https://docs.snowflake.com/en/user-guide/tables-iceberg-configure-external-volume-azure-private">Microsoft Azure storage account with private link setup</a></p><p>Here is the link for <a href="https://docs.snowflake.com/en/user-guide/tables-iceberg-configure-external-volume-s3-private">AWS S3 Storage account with private link setup</a></p><p>Ensure that external volume property USE_PRIVATELINK_ENDPOINT is set to TRUE.</p><h3>Conclusion and Key Considerations</h3><p>The Snowflake Horizon Catalog REST API is a powerful feature that bridges the gap between Snowflake and the broader open-sourced data ecosystem. This integration is a significant step forward for organizations building a flexible and open lakehouse architecture on Snowflake, providing a standardized way to interact with data while maintaining centralized control.</p><p><strong>The Snowflake Horizon REST API (in Public Preview) is forging an open future, creating a seamless bridge where external query engines can connect directly to your governed data. This unlocks limitless innovation, building a future where the entire open data ecosystem can thrive securely on a single platform.</strong></p><p>Special thanks to <a href="https://medium.com/u/21e3fc54f3ab">Umesh Patel</a> for his valuable contribution.</p><p><em>World is a better place, with Snowflake.</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FaENolfHIvLI%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DaENolfHIvLI&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FaENolfHIvLI%2Fhqdefault.jpg&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/577ff7481f7597d423023d8b4b259486/href">https://medium.com/media/577ff7481f7597d423023d8b4b259486/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=18adaf6b0bbe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/unlocking-your-lakehouse-querying-snowflake-managed-iceberg-tables-with-spark-via-the-horizon-rest-18adaf6b0bbe">Unlocking Your Lakehouse: Querying Snowflake-Managed Iceberg Tables with Spark via the Horizon REST…</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Quick Start Guide to the Snowflake ADBC Driver with Python]]></title>
            <link>https://medium.com/snowflake/a-quick-start-guide-to-the-snowflake-adbc-driver-with-python-6de3eb28ee52?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/6de3eb28ee52</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[apache]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Thu, 16 Oct 2025 22:02:18 GMT</pubDate>
            <atom:updated>2025-10-16T22:02:18.377Z</atom:updated>
            <content:encoded><![CDATA[<p>This article walks through installing, configuring, and using the Apache Arrow ADBC Snowflake driver from Python, with a focus on practical connection options, authentication, and a simple query workflow.</p><p>We also cover tips such as MFA support, connection URIs, and bulk ingestion behavior.</p><p>Official docs: <a href="https://arrow.apache.org/adbc/current/driver/snowflake.html">https://arrow.apache.org/adbc/current/driver/snowflake.html</a></p><h3>Why ADBC for Snowflake?</h3><p>ADBC provides a standardized, language-agnostic database connectivity API backed by Apache Arrow. It enables efficient, columnar data movement and offers driver-level options across C/C++, Go, Java, Python, R, and more.</p><h3>Installation</h3><p>You can install the Snowflake driver via conda-forge or PyPI. On macOS, mamba/conda is convenient:</p><pre>mamba install libadbc-driver-snowflake<br>pip install adbc_driver_snowflake</pre><h3>Connecting from Python</h3><p>Below is a minimal example using the Python DB-API facade from adbc_driver_snowflake. It demonstrates common options including MFA token caching, session keep-alive, region/account, and app name.</p><pre>with adbc_driver_snowflake.dbapi.connect(<br>        db_kwargs={&quot;username&quot;: user, &quot;password&quot;: user_password, &quot;adbc.snowflake.sql.db&quot;: database,<br>            &quot;adbc.snowflake.sql.schema&quot;: schema, &quot;adbc.snowflake.sql.warehouse&quot;: warehouse,<br>            &quot;adbc.snowflake.sql.role&quot;: role, &quot;adbc.snowflake.sql.region&quot;: f&quot;{region}&quot;,<br>            &quot;adbc.snowflake.sql.account&quot;: f&quot;{account}&quot;, &quot;adbc.snowflake.sql.uri.protocol&quot;: protocol,<br>            &quot;adbc.snowflake.sql.uri.port&quot;: port, &quot;adbc.snowflake.sql.uri.host&quot;: host,<br>            &quot;adbc.snowflake.sql.auth_type&quot;: auth, &quot;adbc.snowflake.sql.client_option.app_name&quot;: f&quot;{app_name}&quot;,<br>            &quot;adbc.snowflake.sql.client_option.keep_session_alive&quot;: &quot;true&quot;,<br>            &quot;adbc.snowflake.sql.client_option.disable_telemetry&quot;: &quot;false&quot;,<br>            &quot;adbc.snowflake.sql.client_option.cache_mfa_token&quot;: f&quot;{cache_mfa_token}&quot;,<br>            &quot;adbc.snowflake.sql.client_option.store_temp_creds&quot;: f&quot;{store_temp_creds}&quot;}) as session:<br>    print(&quot;-&quot;*50)<br>    print(f&quot;Connected to account {account} &quot;)<br>    with session.cursor() as cursor:<br>        sql = &quot;SELECT CURRENT_CLIENT()&quot;<br>        print(f&quot;Executing SQL: {sql}&quot;)<br>        cursor.execute(sql)<br>        current_client = cursor.fetchone()[0]<br>        print(f&quot;ADBC Connection is made using snowflake Client Driver: {current_client}&quot;)<br>        print(f&quot;ADBC Arrow Application Name: {app_name}&quot;)<br>        cursor.execute(&quot;show databases -&gt;&gt; select * from $1 limit 1&quot;)<br>        rows = cursor.fetchall()<br>        print(f&quot;rows : {len(rows)}&quot;)<br>        print(f&quot;First row : {rows[0]}&quot;)<br>    print(&quot;-&quot;*50)</pre><h3>Sample Response</h3><pre>--------------------------------------------------<br>Connected to account SnowflakeAccount1234 <br>Executing SQL: SELECT CURRENT_CLIENT()<br>ADBC Connection is made using snowflake Client Driver: Go 1.14.1<br>ADBC Arrow Application Name: ADBC Arrow Test App<br>rows : 1<br>First row : (Timestamp(&#39;2023-01-19 21:15:06.900000+0000&#39;, tz=&#39;UTC&#39;), &#39;ADHOC&#39;, &#39;N&#39;, &#39;N&#39;, &#39;&#39;, &#39;ACCOUNTADMIN&#39;, &#39;&#39;, &#39;&#39;, &#39;1&#39;, &#39;STANDARD&#39;, &#39;ROLE&#39;, None)<br>--------------------------------------------------</pre><p>The driver supports username/password by default, SSO via external browser (set auth_type to ‘auth_ext_browser’), key pair (JWT), and workload identity federation. See the docs for full option names and behaviors.</p><h3>URI Format</h3><p>URIs can encode account, user, password, database, and schema; or you can pass options individually. Examples include user[:password]@account/database/schema or host:port/database/schema?account=user_account.</p><h3>References:</h3><p><a href="https://arrow.apache.org/adbc/current/driver/snowflake.html">Apache Arrow ADBC Snowflake Driver</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6de3eb28ee52" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/a-quick-start-guide-to-the-snowflake-adbc-driver-with-python-6de3eb28ee52">A Quick Start Guide to the Snowflake ADBC Driver with Python</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Breaking Silos: Integrating Snowflake and Databricks Unity Catalog]]></title>
            <link>https://medium.com/snowflake/breaking-silos-integrating-snowflake-and-databricks-unity-catalog-8c9d5df6a0b3?source=rss-423de3fae95b------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c9d5df6a0b3</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[delta-lake]]></category>
            <dc:creator><![CDATA[Vishal Verma]]></dc:creator>
            <pubDate>Tue, 26 Aug 2025 19:01:33 GMT</pubDate>
            <atom:updated>2025-09-05T21:45:24.282Z</atom:updated>
            <content:encoded><![CDATA[<p>This article is contributed by <a href="https://medium.com/u/21e3fc54f3ab">Umesh Patel</a> and <a href="https://medium.com/@vishalverma_us">Vishal Verma</a>.</p><h3>Background</h3><p>Enterprises today rarely run on a single data platform. They are using both Snowflake and Databricks and often struggle with data silos, duplicated pipelines, and governance gaps. Traditionally, accessing Delta tables in Snowflake meant complex ETL or data duplication<strong> and governance headaches</strong> — all of which slowed down insights and increased costs. So, <strong>how do you seamlessly access Delta tables stored in Databricks from within Snowflake without duplicating data or building complex pipelines?</strong></p><p>That’s where Snowflake <strong>Horizon Catalog integration</strong> changes the game. By acting as a <strong>single source of truth for governance and metadata</strong>, Horizon Catalog allows Snowflake to <strong>query Delta tables directly</strong>, eliminating silos and giving you the flexibility to use the right engine for the right workload.</p><p>Supporting Iceberg demonstrated Snowflake’s ongoing shift toward embracing open formats. Now, Snowflake is open to integrating with other catalogs like Unity Catalog, Glue Catalog, Polaris etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zxEnXVwOKbzaKdJm.png" /><figcaption>Press enter or click to view image in full size</figcaption></figure><p>Snowflake has another feature called <a href="https://docs.snowflake.com/en/sql-reference/sql/create-iceberg-table-delta">Delta Direct</a>, which I <a href="https://medium.com/snowflake/integrating-delta-tables-with-snowflake-performance-beyond-external-tables-52b54d31251c">wrote</a> about how you can query data stored using the Delta table. With this, you have to create a table in Snowflake for every Delta table. However, with Horizon Catalog Integration using Catalog Link Database, you can link the catalog into Snowflake, so if you create a Delta table in Databricks using Uniform format, it will automatically show up in Snowflake Horizon Catalog from where you can use Snowflake’s powerful query engine to query data. If you do not have a uniform format, do not use this method; use <a href="https://medium.com/snowflake/integrating-delta-tables-with-snowflake-performance-beyond-external-tables-52b54d31251c">Delta Direct.</a></p><blockquote><strong><em>Let’s see how this can be done in Azure:</em></strong></blockquote><p><strong>Step 1:</strong> You need to <strong>create a Service Principal in Databricks. </strong>In Databricks, click on your Profile, Settings, Identity and Access, Manage Principals, click on Add service principal, click on Add New, select Databricks Managed, and give the name of the service principal, eg, “snowflake_integration”, click Add. Note down “Application Id”, you will need to grant access to this service principal to restrict which objects are accessible from Snowflake. Now, click on that service principal, “snowflake_integration”, and click on the “Secrets” tab, where you can create an Oauth secret. Click on “Generate Secret”, provide expiry days, choose 90 days or whatever you find it best, then click on Generate. Copy and save Client ID and Secret, which you will need later to use for integration. At this point, you have the Application ID, Client ID, and Secret of Service Principal.</p><p>Next, you need to have a <strong>catalog, schema, and table with a uniform format</strong> in the Databricks workspace. If you don’t have one, you can create in notebook using the following commands:</p><pre>%sql<br>create catalog if not exists cdp_catalog;<br>create schema if not exists cdp_catalog.cdp_schema;<br>use catalog cdp_catalog;<br>use schema cdp_schema;<br>CREATE or REPLACE TABLE customer_data (<br>    customer_id INT,<br>    customer_name STRING,<br>    email STRING,<br>    registration_date DATE,<br>    city STRING<br>)<br>TBLPROPERTIES (<br>  &#39;delta.columnMapping.mode&#39; = &#39;name&#39;,<br>  &#39;delta.enableIcebergCompatV2&#39; = &#39;true&#39;,<br>  &#39;delta.universalFormat.enabledFormats&#39; = &#39;iceberg&#39;<br>);<br><br>INSERT INTO customer_data VALUES<br>    (1, &#39;Alice Smith&#39;, &#39;alice.smith@example.com&#39;, &#39;2023-01-15&#39;, &#39;New York&#39;),<br>    (2, &#39;Bob Johnson&#39;, &#39;bob.j@example.com&#39;, &#39;2023-02-20&#39;, &#39;Los Angeles&#39;),<br>    (3, &#39;Charlie Brown&#39;, &#39;charlie.b@example.com&#39;, &#39;2023-03-10&#39;, &#39;Chicago&#39;),<br>    (4, &#39;Diana Prince&#39;, &#39;diana.p@example.com&#39;, &#39;2023-04-05&#39;, &#39;London&#39;),<br>    (5, &#39;Eve Adams&#39;, &#39;eve.a@example.com&#39;, &#39;2023-05-22&#39;, &#39;Paris&#39;);</pre><p><strong>Grant access to the objects/tables </strong>that you need to make available in Snowflake, use the following command:</p><pre>-- applicaiton id is service principal id you created in step 1s<br>GRANT USE CATALOG ON CATALOG cdp_catalog TO `{application id from step 1}`;<br>GRANT USE SCHEMA ON SCHEMA cdp_Schema TO  `{application id from step 1}`;<br><br>-- some grant privileges are Unity privilege metastore version specific.<br>--in case catalog and schema grants above does not work, you can try <br>below grants for the same purpose<br>--GRANT USAGE ON CATALOG cdp_catalog TO `{application id from step 1}`;<br>--GRANT USAGE ON SCHEMA cdp_Schema TO  `{application id from step 1}`;<br><br>GRANT SELECT ON TABLE customer_data TO `{application id from step 1}`;<br><br>GRANT EXTERNAL USE SCHEMA ON SCHEMA cdp_catalog.cdp_schema <br>   TO  `{application id from step 1}`;</pre><p>Since Vended Credential is not <strong>yet</strong> supported for Databricks metastore Azure storage account, we have to create an external volume instead of Vended Credential to grant access to the storage account. To do so, you will need the storage URL where the delta table is stored. Use the following command to find the storage URL where the Delta tables are stored. Note down the location value from the output and build the storage URL. It could be a little tricky, but you need to have a URL in the <strong>azure:// </strong>format with <strong>blob instead of dfs (dfs support coming soon):</strong></p><pre>DESCRIBE TABLE EXTENDED customer_data;<br>-- you will see like below from above<br>-- abfss://azure-eastus2-metastore@sridbxsa.dfs.core.windows.net/53a57538-c87b-4cf1-ab8d-xxxxxxx<br>-- convert url like below, note that blob, not dfs, and azure protocol and not abfss<br>-- azure://xxxxxx.blob.core.windows.net/azure-eastus2-metastore/53a57538-c87b-4cf1-ab8d-xxxxxxxx</pre><p>This is all you need to do from the Databricks side.</p><p>Important Note: if your using non-azure based databricks environment, you can use VENDED_CREDENTIALS as option where you don’t need external volume as credentials are obtained as part of catalog integration.</p><p>In Snowflake, we need to create Catalog Integration, External Volume, and Catalog-managed-database. To do so, open a worksheet in Snowflake and use the following code to set it up:</p><p>Important Note: if you are using privatelink connectivity, ensure that CATALOG_API_TYPE set to PRIVATE. The default value for CATALOG_API_TYPE is PUBLIC, indicating traffic going to public internet.</p><pre>USE ROLE accountadmin;<br><br>-- this you get when you created a service principal in Databricks<br>set client_id = &#39;2247f89e-d258-45c2-b366-xxxxxxxxx&#39;;<br>set client_secret = &#39;dosee206d00d43xxxxxxxx&#39;;<br>--get catalog uri from dbx browser i.e workspace url<br>set workspace_url=&#39;https://adb-xxxxxxx.xx.azuredatabricks.net&#39;;<br>set catalog_uri = $workspace_url||&#39;/api/2.1/unity-catalog/iceberg&#39;;<br>set token_uri = $workspace_url||&#39;/oidc/v1/token&#39;;<br>set catalog_name = &#39;cdp_catalog&#39;;<br>set catalog_namespace = &#39;cdp_schema&#39;;<br>CREATE OR REPLACE CATALOG INTEGRATION unity_upatelcatalog_int_oauth<br>  CATALOG_SOURCE = ICEBERG_REST<br>  TABLE_FORMAT = ICEBERG<br>  CATALOG_NAMESPACE = $catalog_namespace<br>  REST_CONFIG = (<br>    CATALOG_URI = $catalog_uri<br>    CATALOG_NAME = $catalog_name<br>    --CATALOG_API_TYPE = PRIVATE /* for privatelink connectivity */<br>    CATALOG_API_TYPE = PUBLIC /* for public connectivity */<br>    --ACCESS_DELEGATION_MODE = {VENDED_CREDENTIALS | EXTERNAL_VOLUME_CREDENTIALS}<br>    ACCESS_DELEGATION_MODE = EXTERNAL_VOLUME_CREDENTIALS<br> )<br>  REST_AUTHENTICATION = (<br>    TYPE = OAUTH<br>    OAUTH_TOKEN_URI = $token_uri<br>    OAUTH_CLIENT_ID = $client_id<br>    OAUTH_CLIENT_SECRET = $client_secret<br>    OAUTH_ALLOWED_SCOPES = (&#39;all-apis&#39;, &#39;sql&#39;)<br>  )<br>  ENABLED = TRUE<br>  REFRESH_INTERVAL_SECONDS = 30;<br>-- make sure created without any error<br>SELECT SYSTEM$VERIFY_CATALOG_INTEGRATION(&#39;unity_upatelcatalog_int_oauth&#39;);<br>-- use  storage_base_url from previous step<br>CREATE OR REPLACE EXTERNAL VOLUME azure_dbx_upatel_ext_vol<br>  STORAGE_LOCATIONS =<br>    (<br>      (<br>        NAME = &#39;azure_dbx_upatel_ext_vol&#39;<br>        STORAGE_PROVIDER = &#39;AZURE&#39;<br>        STORAGE_BASE_URL = &#39;azure://xxxxxxxx.blob.core.windows.net/azure-eastus2-metastore/53a57538-c87b-4cf1-ab8d-xxxxx&#39;<br>        AZURE_TENANT_ID = &#39;9a2d78cb-73e9-40ee-a558-xxxxxxxx&#39;<br>      )<br>    )<br>  ALLOW_WRITES = TRUE;<br>select SYSTEM$VERIFY_EXTERNAL_VOLUME(&#39;azure_dbx_upatel_ext_vol&#39;);<br>desc external volume azure_dbx_upatel_ext_vol;<br>-- complete consent url and also note down AZURE_MULTI_TENANT_APP_NAME<br>-- grant data contributor to storage account to app name<br>-- example: &quot;AZURE_MULTI_TENANT_APP_NAME&quot;:&quot;xbydapsnowflakepacint_xxxxxx&quot;,<br>-- app name is xbydapsnowflakepacint<br>-- goto storage account, add role, select data contributor access <br>--     and select xbydapsnowflakepacint as identity and <br>CREATE DATABASE dbx_upatel_db<br>  LINKED_CATALOG = (<br>    CATALOG = &#39;unity_upatelcatalog_int_oauth&#39;,<br>    SYNC_INTERVAL_SECONDS = 30<br>    --ALLOWED_NAMESPACES = (&#39;cdp_schema&#39;,&#39;schema_2&#39;,&#39;schema_3&#39;) -- restrict to sync only specific schema from unity catalog, default all accessible schemas<br>  )<br>  EXTERNAL_VOLUME = &#39;azure_dbx_upatel_ext_vol&#39;;<br>-- verify catalog link database if any error<br>select SYSTEM$CATALOG_LINK_STATUS( &#39;dbx_upatel_db&#39; );<br>--Note: catalog and table names are case sensitive, hence double quote<br>select * from dbx_upatel_db.&quot;cdp_schema&quot;.&quot;customer_data&quot;;<br>-- If you create table in Databricks, <br>-- it will automatically show up in Snowflake in 30sec<br>--If you want to indivdually create iceberg table you can do this way<br>-- please note that this table created in different database and schema below <br>create database if not exists demodb;<br>CREATE OR REPLACE ICEBERG TABLE demodb.public.icebergtest       CATALOG = &#39;unity_upatelcatalog_int_oauth&#39;       EXTERNAL_VOLUME=azure_dbx_upatel_ext_vol       <br>CATALOG_TABLE_NAME = &#39;customer_data&#39;       <br>AUTO_REFRESH = TRUE;  <br>select * from demodb.public.icebergtest;</pre><h3>Conclusion:</h3><p><strong>Snowflake is once again breaking down data silos.</strong> With the new catalog integration, you can now query data stored in <strong>Databricks</strong> directly from Snowflake — without the need to move or duplicate data. At the same time, the <strong>Horizon catalog</strong> ensures world-class security and governance, acting as a single point of control across platforms.</p><p>For the business, this translates into clear wins:</p><ul><li><strong>Lower Costs</strong> — No redundant pipelines or duplicate storage.</li><li><strong>Faster Time-to-Insight</strong> — Analysts and business teams can access fresh data instantly.</li><li><strong>Stronger Governance</strong> — A unified catalog authority reduces compliance risks.</li><li><strong>Seamless Interoperability</strong> — Both Snowflake and Databricks can be used in harmony, each excelling at its strengths.</li></ul><p>For technical teams, the value is just as compelling:</p><ul><li><strong>Cleaner Architecture</strong> — Simplified data flows reduce engineering overhead.</li><li><strong>Less Data Sprawl</strong> — No more scattered copies of the same dataset across platforms.</li></ul><p>In short, this integration allows enterprises to combine the <strong>best of Snowflake’s compute engine</strong> with <strong>Databricks’ Delta Lake</strong>, all under a consistent governance framework.</p><p><strong><em>Disclaimer</em></strong><em>: The opinions expressed in this post are our own and not necessarily those of our employer (Snowflake).</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c9d5df6a0b3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/breaking-silos-integrating-snowflake-and-databricks-unity-catalog-8c9d5df6a0b3">Breaking Silos: Integrating Snowflake and Databricks Unity Catalog</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>