<?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 Roger Fried on Medium]]></title>
        <description><![CDATA[Stories by Roger Fried on Medium]]></description>
        <link>https://medium.com/@rogerlfried?source=rss-677c4f7ccc00------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*QspOIptm4VyTxmkB.</url>
            <title>Stories by Roger Fried on Medium</title>
            <link>https://medium.com/@rogerlfried?source=rss-677c4f7ccc00------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 23:19:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rogerlfried/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[Forge Your Own: Custom Data Metrics Functions for Snowflake]]></title>
            <link>https://medium.com/@rogerlfried/forge-your-own-custom-data-metrics-functions-for-snowflake-bbdc8c9dc11f?source=rss-677c4f7ccc00------2</link>
            <guid isPermaLink="false">https://medium.com/p/bbdc8c9dc11f</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-quality]]></category>
            <dc:creator><![CDATA[Roger Fried]]></dc:creator>
            <pubDate>Sun, 15 Mar 2026 22:25:22 GMT</pubDate>
            <atom:updated>2026-03-15T22:25:22.277Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A practitioner’s guide to writing 65 custom quality checks on Snowflake’s DMF platform</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y_nKSJRneg0UHRl8Zz3pnA.jpeg" /></figure><p>Snowflake’s system DMFs give you null counts, duplicates, freshness, and more out of the box.</p><p>Custom DMFs let you build on that foundation — adding email validation, referential integrity checks, and whatever domain-specific rules your data requires.</p><p>This is the third article in a series on Snowflake Data Metric Functions.</p><p>· <em>Stop Flying Blind</em> covers system DMFs and the six core operations.</p><p>· <em>Turning the Data Wild West into an Organized Ranch</em> builds the enterprise framework that manages DMFs at scale.</p><p>This article walks through the syntax patterns, design decisions, and naming conventions we developed while building a production DMF framework with 65 custom functions.</p><p>Custom DMFs turn vague “data quality issues” into concrete, measurable checks that can drive SLAs and automated alerts — without building new pipelines.</p><p><strong>Contents:</strong></p><p><strong>· When to Use Custom DMFs</strong></p><p><strong>· Anatomy of a Custom DMF</strong></p><p><strong>· Basic Syntax Patterns</strong></p><p><strong>· Strict vs Permissive Comparison Modes</strong></p><p><strong>· How Fixed Data Types Shape Your DMF Library</strong></p><p><strong>· Composite Key Uniqueness</strong></p><p><strong>· Referential Integrity DMFs</strong></p><p><strong>· Composite Key Referential Integrity</strong></p><h3>When to Use Custom DMFs</h3><p>Snowflake ships a set of <strong>system DMFs</strong> in the SNOWFLAKE.CORE schema.</p><p>These cover the metrics that almost every data team needs out of the box:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*51ii1Lcb1Y7RLJuMpbejDQ.png" /></figure><p>For many tables, system DMFs are all you need.</p><p>Attach them, set expectations, and move on — Stop Flying Blind walks through that setup.</p><p>But production data has a way of demanding checks that no pre-built function can anticipate.</p><p>That’s when you write your own.</p><p>Create <strong>custom DMFs</strong> when you need:</p><p>· <strong>Business rules</strong> — values within expected ranges, domain-specific constraints</p><p>· <strong>Percentage metrics</strong> — null rate as percentage rather than count</p><p>· <strong>Regex validation</strong> — email formats, phone numbers, postal codes</p><p>· <strong>Referential integrity</strong> — foreign key checks across tables</p><p>· <strong>Composite checks</strong> — multi-column uniqueness or combinations</p><p>The one rule that applies to every DMF, system or custom: the function must return a single NUMBER. That scalar value is what Snowflake stores in SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS and evaluates against your expectations.</p><p>Everything else is up to you.</p><p><em>(The DDL syntax is always RETURNS NUMBER. FLOAT values work since FLOAT is a NUMBER subtype, which is useful for ratio and percentage metrics that return decimals.)</em></p><h3>Anatomy of a Custom DMF</h3><p>Every custom DMF is built from five components.</p><p>Understanding these building blocks makes the syntax patterns in the rest of this article easier to follow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WK5O1pgRUtoYch1dD3zU1Q.jpeg" /></figure><pre>CREATE OR REPLACE DATA METRIC FUNCTION       -- (1) Function Identity<br>  governance.dmfs.count_positive_numbers(<br>    ARG_T TABLE(                             -- (2) Argument Signature<br>      ARG_C1 NUMBER,<br>      ARG_C2 NUMBER,<br>      ARG_C3 NUMBER<br>    )<br>  )<br>RETURNS NUMBER                               -- (3) Return Type<br><br>COMMENT = &#39;Counts rows where all three columns are positive&#39;  <br>AS                                           -- (4) Optional Properties<br><br>$$                                           -- (5) Metric Logic<br>  SELECT COUNT(*)<br>  FROM ARG_T<br>  WHERE ARG_C1 &gt; 0<br>    AND ARG_C2 &gt; 0<br>    AND ARG_C3 &gt; 0<br>$$;</pre><h3>Component Breakdown</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WIG2xLUOOo4JbkKvTS8ghw.png" /></figure><p>The <strong>argument signature</strong> is the component you’ll customize most. The rest of this article shows how signatures change across single-column checks (Pattern A), regex validations (Pattern B), time-based metrics (Pattern C), multi-column combinations, and cross-table referential integrity — but the five-component structure stays the same.</p><h3>Basic Syntax Patterns</h3><p>Custom DMFs come in a few flavors, depending on how the SQL body is written.</p><h3>Pattern A: Single-Column, Inline SQL (Simplest)</h3><p>For simple calculations that fit in a single expression, use a single-quoted inline SQL string:</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION NULL_PCT_V(ARG_T TABLE(ARG_C VARCHAR))<br>RETURNS NUMBER<br>AS &#39;SELECT ROUND(100.0 * COUNT_IF(ARG_C IS NULL) / NULLIF(COUNT(*), 0), 2) FROM ARG_T&#39;;</pre><p>The _V suffix indicates this version targets VARCHAR columns.</p><p>We’ll come back to why that suffix matters below in the <em>“How Fixed Data Types Shape Your DMF Library.”</em></p><h3>Pattern B: Pass-Fail Flag</h3><p>Not every metric needs to count violations.</p><p>Sometimes the question is binary: does this table pass or fail?</p><p>Return 1.0 for pass and 0.0 for fail:</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION has_recent_data(<br>  ARG_T TABLE(ARG_C TIMESTAMP_LTZ)<br>)<br>RETURNS NUMBER<br>AS<br>$$<br>  SELECT CASE<br>    WHEN TIMEDIFF(HOUR, MAX(ARG_C), SNOWFLAKE.CORE.DATA_METRIC_SCHEDULED_TIME()) &lt;= 24<br>    THEN 1.0 ELSE 0.0<br>  END<br>  FROM ARG_T<br>$$;</pre><p>Pair with an expectation of VALUE = 1.0 for a clean pass/fail signal.</p><p>This pattern keeps dashboards and alerting simple when a binary answer is all you need.</p><h3>Pattern C: Multi-Line SQL Block</h3><p>When your logic spans multiple lines or includes characters that fight with single quotes (backslashes in regex patterns, for example), switch to the $$ dollar-quoting delimiter:</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION EXPECT_VALID_EMAIL_V(<br>  ARG_T TABLE(ARG_C VARCHAR)<br>)<br>RETURNS NUMBER<br>AS<br>$$<br>  SELECT COUNT(*)<br>  FROM ARG_T<br>  WHERE ARG_C IS NOT NULL<br>    AND REGEXP_LIKE(<br>      ARG_C,<br>      &#39;^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,63}$&#39;<br>    ) = FALSE<br>$$;</pre><p>In this email example, we explicitly exclude NULLs and then count rows where REGEXP_LIKE(…) = FALSE.</p><p>An expectation of VALUE = 0 means no bad emails allowed.</p><p>This pattern intentionally covers standard business email addresses, not every valid form; adjust the regex to match your organization’s requirements.</p><p>Our production framework uses inline SQL for this DMF (RETURNS NUMBER AS ‘SELECT …’), but the $$ block shown here is easier to read when regex patterns are involved.</p><p>For an example of pairing a counting DMF with a row-returning UDTF that shows you which rows failed, see my article on <em>“Turning the Data Wild West into an Organized Ranch.”</em></p><h3>Pattern D: Using System Functions</h3><p>Some checks need context that only Snowflake can provide.</p><p>Please note that Snowflake <strong>disallows</strong> nondeterministic functions like CURRENT_TIMESTAMP() and CURRENT_DATE() inside the DMF body. DMF expressions must be deterministic.</p><p>For time-based checks, use the non-deterministic function DATA_METRIC_SCHEDULED_TIME(), which returns the timestamp when the DMF was scheduled to run:</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION data_freshness_hour(<br>  ARG_T TABLE (ARG_C TIMESTAMP_LTZ)<br>)<br>RETURNS NUMBER<br>AS<br>$$<br>  SELECT TIMEDIFF(<br>           HOUR,<br>           MAX(ARG_C),<br>           SNOWFLAKE.CORE.DATA_METRIC_SCHEDULED_TIME()<br>         )<br>  FROM ARG_T<br>$$;</pre><p>This returns the number of hours since the most recent record.</p><h3>Choosing a Pattern</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q71eMxyPcfsCgAZt2elB7w.png" /></figure><p>Our production framework standardizes on Pattern A (inline SQL) for all 65 DMFs, using escaped single quotes throughout. This is more compact and avoids mixed conventions.</p><p>But Pattern C is easier to read and debug during development, so start there.</p><h3>Strict vs Permissive Comparison Modes</h3><p>Any DMF that compares values — uniqueness checks, referential integrity, deduplication — faces a question: <strong>how exact should the match be?</strong></p><p>Our framework answers this with two standard modes that apply across every comparison DMF.</p><p><strong>STRICT</strong> mode compares values exactly as stored.</p><p>· ‘hello’ and ‘HELLO’ are different.</p><p>· 3.1415926 and 3.1415927 are different.</p><p>· No normalization, no tolerance.</p><p><strong>PERMIS</strong> (permissive) mode normalizes values before comparing.</p><p>STRICT and PERMIS cover broad classes of data quality issues with reusable normalization rules, but they aren’t the only option. When a specific column has idiosyncratic formatting — mixed delimiters in phone numbers, inconsistent date strings, vendor-specific ID prefixes — a regex-based DMF tailored to that column’s actual patterns may be more appropriate than forcing the data through a generic normalization pipeline.</p><p>The normalization rules are type-specific:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VR_Vk9SkH2X-3lUf_SPL9A.png" /></figure><p>Both modes skip NULLs — a NULL value means “no data” and shouldn’t be treated as a duplicate or an orphan.</p><h3>Side-by-Side Example</h3><p>Here’s the same uniqueness check written both ways for a VARCHAR column:</p><pre>-- STRICT: exact match<br>CREATE OR REPLACE DATA METRIC FUNCTION EXPECT_UNIQUE_V_STRICT(<br>  ARG_T TABLE(ARG_C VARCHAR)<br>)<br>RETURNS NUMBER<br>AS &#39;SELECT COUNT(*) - COUNT(DISTINCT ARG_C) <br>  FROM ARG_T WHERE ARG_C IS NOT NULL&#39;;<br><br><br>-- PERMIS: normalized match<br>CREATE OR REPLACE DATA METRIC FUNCTION EXPECT_UNIQUE_V_PERMIS(<br>  ARG_T TABLE(ARG_C VARCHAR)<br>)<br>RETURNS NUMBER<br>AS &#39;SELECT COUNT(*) - COUNT(DISTINCT NULLIF(TRIM(UPPER(ARG_C)),&#39;&#39;&#39;&#39;)) <br>  FROM ARG_T WHERE ARG_C IS NOT NULL&#39;;</pre><p>With STRICT, ‘Smith’ and ‘smith’ are two distinct values.</p><p>With PERMIS, they collapse to one — and if both rows exist, the DMF flags a duplicate.</p><h3>Naming Convention</h3><p>Append _STRICT or _PERMIS to the function name.</p><p>Combined with the type suffix from the next section, this gives names like:</p><p>· EXPECT_UNIQUE_VN_STRICT</p><p>· EXPECT_REF_D_PERMIS</p><p>These encode both the column types and the comparison mode.</p><h3>How Fixed Data Types Shape Your DMF Library</h3><p>The DMF engine validates column types at creation time, not at runtime.</p><p>Every column in the argument signature needs an explicit type — VARCHAR, NUMBER, DATE, and time types like TIMESTAMP_LTZ.</p><p>No VARIANT, no ANY — no polymorphic argument types.</p><p>This design gives Snowflake the ability to validate attachments immediately when you run:</p><pre>ALTER TABLE ...<br>ADD DATA METRIC FUNCTION ...;</pre><p>…and it shapes the naming conventions for your entire DMF library.</p><p>For single-column checks like null percentage or email validation, this is straightforward — you create one version per data type (VARCHAR, NUMBER, DATE) and you’re done.</p><p>The type constraint becomes more significant when you need to validate composite keys, where each column in the key adds another type dimension to manage.</p><h3>What This Means in Practice</h3><p>If you come from a background in general-purpose programming, your instinct is to write one function that handles any column type.</p><p>You might expect something like this to work:</p><pre>-- THIS DOES NOT WORK<br><br>CREATE DATA METRIC FUNCTION null_pct_generic(ARG_T TABLE(ARG_C VARIANT))<br>...</pre><p>Snowflake validates types at creation time so it can catch mismatches when you attach the DMF, not when it runs at 2am.</p><p>There is no runtime type resolution.</p><h3>The Solution: Type-Specific Versions</h3><p>Create separate DMFs for each data type and use a naming convention to keep them organized:</p><p><strong>Naming convention:</strong> CHECK_NAME_[type-codes] where type codes are:</p><p>V = VARCHAR, N = NUMBER, D = DATE.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WBtXQl6zzVIicRIe_M9XUw.jpeg" /></figure><p>The _STRICT and _PERMIS suffixes refer to the comparison modes described in Strict vs Permissive Comparison Modes.</p><p>Examples:</p><p>· NULL_PCT_V</p><p>· EXPECT_UNIQUE_VN_STRICT</p><p>· EXPECT_REF_D_PERMIS</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION NULL_PCT_V(ARG_T TABLE(ARG_C VARCHAR))<br>RETURNS NUMBER<br>AS &#39;SELECT ROUND(100.0 * COUNT_IF(ARG_C IS NULL) / NULLIF(COUNT(*), 0), 2) <br>  FROM ARG_T&#39;;<br><br>CREATE OR REPLACE DATA METRIC FUNCTION NULL_PCT_N(ARG_T TABLE(ARG_C NUMBER))<br>RETURNS NUMBER<br>AS &#39;SELECT ROUND(100.0 * COUNT_IF(ARG_C IS NULL) / NULLIF(COUNT(*), 0), 2) <br>  FROM ARG_T&#39;;<br><br>CREATE OR REPLACE DATA METRIC FUNCTION NULL_PCT_D(ARG_T TABLE(ARG_C DATE))<br>RETURNS NUMBER<br>AS &#39;SELECT ROUND(100.0 * COUNT_IF(ARG_C IS NULL) / NULLIF(COUNT(*), 0), 2) <br>  FROM ARG_T&#39;;</pre><p>The SQL logic is identical across all three. Only the type declaration differs.</p><p>This feels redundant, but the naming convention makes it obvious which version to use. When you see NULL_PCT_V in a deployment script, you know it targets a VARCHAR column.</p><h3>Why We Moved Away from Function Overloading</h3><p>Early in our framework development, we tried function overloading. Snowflake allows multiple functions with the same name but different signatures, so we created variants like this:</p><pre>-- EARLIER APPROACH (we moved past this)<br>CREATE DATA METRIC FUNCTION NULL_PCT(ARG_T TABLE(ARG_C VARCHAR)) ...<br>CREATE DATA METRIC FUNCTION NULL_PCT(ARG_T TABLE(ARG_C NUMBER)) ...</pre><p><strong>What we learned:</strong></p><p>· Debugging was harder — unclear which variant was actually called</p><p>· Error messages didn’t clearly indicate type mismatches</p><p>· Documentation and maintenance were more complex</p><p>Explicit type suffixes turned out to be a better fit.</p><p>NULL_PCT_V and NULL_PCT_N make the type requirement visible in the function name, which pays off during code review and deployment.</p><h3>Composite Key Uniqueness</h3><p>Single-column checks cover most custom DMF use cases.</p><p>But when your tables have composite primary keys, you need DMFs that accept multiple columns to validate uniqueness across the full key. This is where the fixed-type requirement becomes more significant, because each column in the key adds a type dimension to the function signature.</p><p>Most teams won’t need the full set of multi-column variants described in the next few sections. These sections are included because composite key validation is the use case that best illustrates how DMF complexity scales, and understanding the pattern helps even if you only implement a few variants.</p><h3>Type Combination Naming</h3><p>The same suffix convention that works for single-column DMFs extends naturally to multi-column versions. Concatenate the type codes for each column in order:</p><p>A suffix like _VVN tells you immediately that the function expects three columns: VARCHAR, VARCHAR, NUMBER.</p><p>No guessing, no checking the signature.</p><h3>Example: 2-Column Unique Check</h3><p>Here’s a composite key check for (VARCHAR, NUMBER) uniqueness:</p><pre>CREATE OR REPLACE DATA METRIC FUNCTION EXPECT_UNIQUE_VN_STRICT(<br>ARG_T TABLE(ARG_C1 VARCHAR, ARG_C2 NUMBER)<br>)<br>RETURNS NUMBER<br>AS &#39;SELECT COUNT(*) - COUNT(DISTINCT HASH(ARG_C1, ARG_C2)) <br>  FROM ARG_T WHERE ARG_C1 IS NOT NULL AND ARG_C2 IS NOT NULL&#39;;</pre><p>Returns zero if all non-NULL (VARCHAR, NUMBER) combinations are unique; otherwise returns the duplicate count.</p><p>You need separate versions for every type combination in production: _VV, _VN, _VD, _ND, and so on.</p><p><em>Note: Snowflake doesn’t support `COUNT(DISTINCT (col1, col2))` tuples, so `HASH(col1, col2)` is a practical surrogate. HASH collisions are theoretically possible but statistically negligible at typical data volumes; this is a practical surrogate, not a cryptographic guarantee. The `WHERE` clause filters NULLs so that NULL combinations don’t inflate the duplicate count.</em></p><h3>Referential Integrity DMFs</h3><p>Composite key uniqueness validates columns within a single table.</p><p>Referential integrity goes further — these DMFs need access to <strong>two tables</strong>: the child table with the foreign key and the parent table with the primary key.</p><h3>How Two-Table Access Works</h3><p>Every DMF signature includes at least one TABLE(…) parameter — the table the DMF is attached to. Snowflake passes it automatically at runtime.</p><p>For referential integrity, you need a second table: the parent. Snowflake supports this through a second TABLE(…) parameter in the DMF signature.</p><h3>TABLE() Wrapper Syntax</h3><p>Add a second TABLE(…) parameter to the DMF signature, then use the attachment syntax to tell Snowflake where the parent table lives:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F6I3WOA4_3NWEzgwQmAeTg.jpeg" /></figure><pre>-- Step 1: Create the FK DMF (two TABLE arguments)<br>CREATE OR REPLACE DATA METRIC FUNCTION EXPECT_REF_V_STRICT(<br>  ARG_T1 TABLE(ARG_C1 VARCHAR),<br>  ARG_T2 TABLE(ARG_C2 VARCHAR)<br>)<br>RETURNS NUMBER<br>AS &#39;SELECT COUNT(*) FROM ARG_T1 c WHERE c.ARG_C1 IS NOT NULL AND NOT EXISTS <br>  (SELECT 1 FROM ARG_T2 p WHERE p.ARG_C2 = c.ARG_C1)&#39;;<br><br>-- Step 2: Attach with TABLE() syntax specifying both tables<br>ALTER TABLE orders<br>ADD DATA METRIC FUNCTION EXPECT_REF_V_STRICT<br>ON (customer_id, TABLE(customers(customer_id)));</pre><p>The TABLE() wrapper in the ALTER TABLE statement does the mapping: pass customer_id from customers as the second DMF argument (ARG_T2). The first argument (ARG_T1 = orders.customer_id) is auto-passed because the DMF is attached to orders.</p><h3>FK DMF TABLE() Syntax Matrix</h3><p>Each FK check comes in both STRICT and PERMIS variants, following the same comparison modes described in Strict vs Permissive Comparison Modes.</p><p>The full single-column FK matrix:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cxyfXJmXTy-_wGFEz5gUSQ.png" /></figure><h3>Composite Key Referential Integrity</h3><p>The composite key naming from uniqueness checks and the two-TABLE signature from FK checks combine when referential integrity spans multi-column keys. This is the most complex pattern in the DMF library.</p><h3>Multi-Column FK Attachment</h3><p>The same TABLE() wrapper pattern extends to composite foreign keys. Include all columns from both tables in the attachment syntax:</p><pre>-- 2-column FK attachment<br>ALTER TABLE order_items<br>ADD DATA METRIC FUNCTION EXPECT_REF_VN_STRICT<br>ON (order_id, line_num, TABLE(order_lines(order_id, line_num)));</pre><p>The DMF itself follows the same NOT EXISTS pattern as single-column FK checks, but with multiple columns in the signature and the comparison logic.</p><h3>Practical Tips from Building 65 DMFs</h3><p>After building 65 custom DMFs and deploying them across multiple environments, here’s what we wish we’d known from the start.</p><h3>Common Pitfalls and Fixes</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttKXzgU7hIEkI46ZJcua4Q.png" /></figure><p>A couple additional practices:</p><p>· <strong>Test in development first.</strong></p><p>A DMF that compiles can still return garbage if the logic doesn’t handle edge cases like empty tables or all-NULL columns.</p><p>· <strong>Keep DMF logic simple.</strong></p><p>If a check requires joins to multiple reference tables or conditional branching, split it into multiple focused DMFs rather than one monolithic function.</p><p>· <strong>Verify associations with `DATA_METRIC_FUNCTION_REFERENCES`.</strong></p><p>After attaching DMFs, confirm what’s associated where.</p><h3>Conclusion</h3><p>Custom DMFs extend Snowflake’s native monitoring to handle whatever validation your business requires.</p><p>The syntax is SQL wrapped in a well-defined structure, and once you internalize the five-component anatomy, writing new ones becomes mechanical. Most custom DMFs you write will be single-column checks with straightforward type variants. The multi-column patterns in this article represent the edge case of composite key validation — but they are a useful conceptual model for understanding how far the DMF system can scale when your data demands it.</p><p>The payoff: a library of reusable, type-safe quality checks that run on Snowflake’s serverless data quality infrastructure — no warehouse credits, only Data Quality Monitoring credits.</p><p>Patterns worth remembering:</p><p>· <strong>Return NUMBER</strong> — all DMFs return a scalar numeric value</p><p>· <strong>Fixed types only</strong> — declare exact column types, no VARIANT</p><p>· <strong>STRICT vs PERMIS</strong> — start with exact comparison, switch to permissive normalization when data quality requires it</p><p>· <strong>TABLE() for FK checks</strong> — wrapper syntax references the secondary table</p><p>With these patterns, you can build a custom quality library that plugs directly into Snowflake’s native monitoring, expectations, and alerting. If you’re managing those checks at scale, Turning the Data Wild West into an Organized Ranch covers the control-table architecture that ties it together.</p><h3>References</h3><p><strong>Snowflake Documentation</strong></p><p><strong>· Custom Data Metric Functions</strong></p><p><a href="https://docs.snowflake.com/en/user-guide/data-quality-custom-dmfs">https://docs.snowflake.com/en/user-guide/data-quality-custom-dmfs</a></p><p><strong>· Access Control for Data Quality</strong></p><p><a href="https://docs.snowflake.com/en/user-guide/data-quality-access-control">https://docs.snowflake.com/en/user-guide/data-quality-access-control</a></p><p><strong>· Use SQL to Set Up Data Metric Functions</strong></p><p><a href="https://docs.snowflake.com/en/user-guide/data-quality-working">https://docs.snowflake.com/en/user-guide/data-quality-working</a></p><p><strong>Other Articles in This Series</strong></p><p><strong>· Stop Flying Blind: Using Snowflake Data Metric Functions to Monitor Data Health — Roger Fried</strong></p><p><a href="https://medium.com/@rogerlfried/stop-flying-blind-using-snowflake-data-metric-functions-to-monitor-data-health-0a35cb9bf71f">https://medium.com/@rogerlfried/stop-flying-blind-using-snowflake-data-metric-functions-to-monitor-data-health-0a35cb9bf71f</a></p><p><strong>· Turning the Data Wild West into an Organized Ranch — Roger Fried</strong></p><p><a href="https://medium.com/@rogerlfried/turning-the-data-wild-west-into-an-organized-ranch-72cb47d7dc38">https://medium.com/@rogerlfried/turning-the-data-wild-west-into-an-organized-ranch-72cb47d7dc38</a></p><p><em>Roger Fried is a Solution Architect for the Public Sector at Snowflake, where he helps government and healthcare organizations build reliable, AI-ready data infrastructure. The opinions expressed here are his own.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bbdc8c9dc11f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Turning the Data Wild West into an Organized Ranch]]></title>
            <link>https://medium.com/@rogerlfried/turning-the-data-wild-west-into-an-organized-ranch-72cb47d7dc38?source=rss-677c4f7ccc00------2</link>
            <guid isPermaLink="false">https://medium.com/p/72cb47d7dc38</guid>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-governance]]></category>
            <dc:creator><![CDATA[Roger Fried]]></dc:creator>
            <pubDate>Sat, 14 Mar 2026 21:07:39 GMT</pubDate>
            <atom:updated>2026-03-14T21:07:39.997Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Turning the Data Wild West into an Organized Ranch</strong></p><p><em>Practical Framework for Wrangling Snowflake DMFs at Scale</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YgDMBiizFoPh9mRFxmcucA.jpeg" /><figcaption><em>Wrangling data quality into an organized ranch</em></figcaption></figure><p><em>You’ve set up your first DMFs. Now you have 50 tables to monitor, with different teams defining checks and schedules changing weekly. How do you manage this at scale?</em></p><p>This article builds on Getting Started with Snowflake Data Metric Functions to show how to design a modular framework for enterprise-scale DMF management. You’ll learn to track intended vs. actual state, automate deployments, and capture the specific rows that cause quality failures.</p><p><strong>What You’ll Learn</strong></p><p>· Why a framework approach beats ad-hoc DMF management</p><p>· How to design control tables that track test definitions and table state</p><p>· Modular procedures that handle each DMF operation independently</p><p>· The two-layer pattern for capturing violating rows with custom DMFs</p><p>· How to organize checks by business impact</p><h3>Why Build a Framework?</h3><p>Data quality is often challenging and cost-intensive due to dependencies on multiple tools, and is often eventually ignored. Poor credibility and inconsistent data lead businesses to mistrust their data pipelines. A well-designed framework addresses this by delivering:</p><p>· <strong>Credibility — </strong>Make data trustworthy, enabling reliable data-driven decision-making</p><p>· <strong>SLA Compliance — </strong>Enforce contracts between producers and consumers with measurable quality metrics</p><p>· <strong>Regulatory Compliance — </strong>Support governance requirements with auditable quality checks</p><p>· <strong>Automation — </strong>Eliminate manual intervention after initial setup</p><p>The good news: Snowflake’s DMF architecture is designed for this. The platform provides:</p><p>· <strong>Result storage — </strong>All DMF results are captured in event tables and queryable via SQL</p><p>· <strong>Programmatic access — </strong>System functions let you evaluate expectations and retrieve violating rows</p><p>· <strong>Horizon Catalog integration — </strong>Lineage, governance, and discoverability built in</p><p>· <strong>Snowflake Snowsight dashboards — </strong>Visual observability without custom BI tools</p><h3>The DMF Infrastructure Foundation</h3><p>Before building framework control tables, it’s worth understanding what Snowflake already provides. A framework layers on top of this native infrastructure and queries it rather than replacing it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o77WQVyFbMvvFF_k4Mff7A.png" /></figure><p>Framework control tables don’t replace this infrastructure; they extend it. The control tables track intent (what tests should exist, what schedules are desired) while Snowflake’s views track reality (what’s actually attached, what results were produced). Framework procedures reconcile the two.</p><p>A modular framework layers on top of this infrastructure, adding:</p><p>· <strong>Control tables</strong> to track test definitions and table-level state</p><p>· <strong>Discrete procedures</strong> for each operation (attach, schedule, suspend, etc.)</p><p>· <strong>Results logging</strong> for audit trails beyond Snowflake’s event tables</p><p>· <strong>Exception capture</strong> to identify and remediate specific violating rows</p><p>· <strong>Notification modules</strong> for alerting appropriate teams</p><h3>Framework Control Tables</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0EoiEOc9gXk_FYdm2ypQ-g.png" /><figcaption><em>Framework control tables architecture</em></figcaption></figure><p>A framework can track DMF state with control tables at different levels. The design uses four separate tables rather than one monolithic table because DMF operations work at different scopes: tests are defined per column, schedules are set per table, and expectations are reusable templates that can apply to many tests. Separating these concerns mirrors Snowflake’s own metadata structure (where references, schedules, and expectations are tracked in distinct views) and means you can change a schedule without touching test definitions, or reuse an expectation template across dozens of tests.</p><p>The relationship pattern is straightforward: many tests share one schedule (grouped by table), each test can have multiple expectations, and the same expectation template can apply to multiple tests. A junction table handles the many-to-many link between tests and expectations.</p><p><em>The schemas below show key columns; a production implementation would add timestamps and audit columns.</em></p><p><strong>Test Definitions — One row per DMF attachment:</strong></p><pre>CREATE TABLE DMF_CTRL_1_TESTS (<br>    TEST_ID             VARCHAR DEFAULT UUID_STRING(),<br>    DATABASE_NAME       VARCHAR NOT NULL,<br>    SCHEMA_NAME         VARCHAR NOT NULL,<br>    TABLE_NAME          VARCHAR NOT NULL,<br>    COLUMN_NAMES        VARCHAR,<br>    DMF_NAME            VARCHAR NOT NULL,<br>    FAILED_ROWS_SQL     VARCHAR,              -- Generated SQL to retrieve failing rows<br>    STATUS              VARCHAR DEFAULT &#39;PENDING&#39;,  -- PENDING, ATTACHED, DETACHED<br>    IS_ACTIVE           BOOLEAN DEFAULT TRUE<br>);</pre><p><strong>Table State — One row per monitored table:</strong></p><pre>CREATE TABLE DMF_CTRL_2_SCHEDULES (<br>    DATABASE_NAME       VARCHAR NOT NULL,<br>    SCHEMA_NAME         VARCHAR NOT NULL,<br>    TABLE_NAME          VARCHAR NOT NULL,<br>    SCHEDULE            VARCHAR,                    -- TRIGGER_ON_CHANGES, 5 MINUTES, etc.<br>    SUSPENDED           BOOLEAN DEFAULT FALSE,<br>    UNIQUE (DATABASE_NAME, SCHEMA_NAME, TABLE_NAME)<br>);</pre><p><strong>Expectation Templates — Reusable expectation definitions:</strong></p><pre>CREATE TABLE DMF_CTRL_3_EXPECTATIONS (<br>    EXPECTATION_NAME    VARCHAR NOT NULL PRIMARY KEY,<br>    EXPECTATION_VALUE   INTEGER NOT NULL,           -- Threshold value (e.g., 100)<br>    EXPECTATION_EXPRESSION VARCHAR DEFAULT &#39;VALUE &lt;=&#39;,  -- Comparison operator<br>    SEVERITY            VARCHAR DEFAULT &#39;WARNING&#39;   -- WARNING, CRITICAL<br>);</pre><p><strong>Test-Expectation Links — Junction table for many-to-many relationships</strong></p><pre>CREATE TABLE DMF_CTRL_4_TEST_EXPECTATIONS (<br>    TEST_ID             VARCHAR NOT NULL,           -- FK to TESTS<br>    EXPECTATION_NAME    VARCHAR NOT NULL,           -- FK to EXPECTATIONS<br>    IS_DEPLOYED         BOOLEAN DEFAULT FALSE<br>);</pre><h3>Severity-Based Expectations</h3><p>Since each DMF can have multiple expectations evaluated independently, you can define tiered thresholds representing different severity levels:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*io4rdhvSPsHkMciSwK0UXg.png" /></figure><pre>-- Apply tiered expectations to the same DMF<br>ALTER TABLE orders<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email)<br>  ADD EXPECTATION null_warning (VALUE &lt;= 100), null_critical (VALUE &lt;= 1000);</pre><p>Each expectation is evaluated and reported separately in DATA_QUALITY_MONITORING_EXPECTATION_STATUS. This enables graduated alerting: a warning threshold might trigger a Slack notification, while a critical threshold triggers PagerDuty. The SEVERITY column in the expectations template table lets you standardize these tiers across your organization.</p><p><strong>The four control tables work together: TESTS tracks what to check, SCHEDULES tracks when and whether to run, EXPECTATIONS provides reusable templates, and TEST_EXPECTATIONS links them in a many-to-many relationship so the same expectation template can be applied to multiple tests.</strong></p><h3>Modular Operations in Practice</h3><p>With this structure, framework procedures handle the six core DMF operations (attach, set expectation, schedule, suspend/resume, anomaly detection, remove). Each procedure updates the appropriate control table, executes the SQL operation, and logs the result. A failed attachment doesn’t affect the schedule. A schedule change doesn’t require re-attaching tests.</p><h3>Native Functions for Framework Automation</h3><p>Snowflake provides system functions that enable programmatic framework operations:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sc2O2Ql3k-eq6zi0XzCokA.png" /></figure><p>These functions allow framework procedures to:</p><p>· Check expectation status before proceeding in a pipeline</p><p>· Capture violating records for remediation workflows</p><p>· Alert appropriate teams when quality thresholds are breached</p><p>Example — evaluate expectations programmatically:</p><pre>SELECT METRIC_NAME, EXPECTATION_VIOLATED, METRIC_VALUE<br>FROM TABLE(SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(<br>    REF_ENTITY_NAME =&gt; &#39;MY_DB.MY_SCHEMA.ORDERS&#39;<br>));</pre><h3>Capturing Violating Rows</h3><p>A DMF tells you how many rows failed. To remediate the problem — fix bad records, notify upstream producers, or document SLA breaches — you need to know which rows failed. The approach depends on the type of DMF:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LSAucZ-a-mC0W71PPVNMYw.png" /></figure><h3>Built-in DMFs: SYSTEM$DATA_METRIC_SCAN</h3><p>For system DMFs, Snowflake provides SYSTEM$DATA_METRIC_SCAN to retrieve violating rows:</p><pre>SELECT * FROM TABLE(<br>    SYSTEM$DATA_METRIC_SCAN(<br>        REF_ENTITY_NAME =&gt; &#39;MY_DB.MY_SCHEMA.ORDERS&#39;,<br>        METRIC_NAME =&gt; &#39;SNOWFLAKE.CORE.NULL_COUNT&#39;,<br>        ARGUMENT_NAME =&gt; &#39;CUSTOMER_EMAIL&#39;<br>    )<br>);</pre><p>This runs synchronously on your warehouse and returns the actual problem records. However, SYSTEM$DATA_METRIC_SCAN does not support user-defined (custom) data metric functions. It only works with the built-in system metrics (NULL_COUNT, BLANK_COUNT, DUPLICATE_COUNT, etc.) listed in the Snowflake documentation.</p><h3>Custom DMFs: The Two-Layer Pattern</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mliw15q4gvJfyhuMk3MpVA.jpeg" /><figcaption><em>The two-layer pattern: DMF + paired UDTF</em></figcaption></figure><p>For custom DMFs, best practice is to create two paired functions that share the same predicate logic:</p><p>· <strong>DMF — </strong>Returns a scalar count (for monitoring, expectations, dashboards)</p><p>· <strong>Row-returning UDTF — </strong>Returns the actual failing rows</p><pre>-- DMF: returns count of bad rows (for monitoring)<br>CREATE OR REPLACE DATA METRIC FUNCTION invalid_email_count_dmf(<br>  ARG_T TABLE(ARG_EMAIL STRING)<br>)<br>RETURNS NUMBER<br>AS <br>$$<br>  SELECT COUNT(*)<br>  FROM ARG_T<br>  WHERE NOT REGEXP_LIKE(ARG_EMAIL, &#39;^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,63}$&#39;)<br>$$;<br><br>-- Row-level helper: returns bad rows<br>CREATE OR REPLACE FUNCTION invalid_email_rows(<br>  ARG_T TABLE(ARG_EMAIL STRING)<br>)<br>RETURNS TABLE (ARG_EMAIL STRING)<br>AS <br>$$<br>  SELECT ARG_EMAIL<br>  FROM ARG_T<br>  WHERE NOT REGEXP_LIKE(ARG_EMAIL, &#39;^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,63}$&#39;)<br>$$;</pre><p>The DMF conforms to Snowflake’s “returns NUMBER” contract for monitoring. The UDTF (table function) uses identical WHERE logic to surface the actual records.</p><h3>FAILED_ROWS_SQL: Executable, Copy-Paste Ready</h3><p>The FAILED_ROWS_SQL column in the control table stores executable SQL that calls the paired UDTF. When a DMF is attached, the framework generates this SQL automatically; when detached, it’s cleared. Analysts can copy the value directly from the control table into a Snowsight worksheet and run it:</p><pre>SELECT * FROM TABLE(<br>  invalid_email_rows(<br>    (SELECT customer_email FROM my_db.my_schema.orders)<br>  )<br>);</pre><h3>Key Considerations</h3><p>· <strong>Scope queries to incident windows.</strong> Row-returning queries re-scan the table. Filter by time window or partition to keep cost and latency predictable.</p><p>· <strong>Avoid double work.</strong> The scheduled DMF already computed the metric. Only call the UDTF when you actually need the rows (e.g., after a failure).</p><p>· <strong>Schema alignment.</strong> The TABLE argument must match column names and types. Use explicit column selection rather than SELECT * to avoid schema drift errors.</p><p>· <strong>Policies apply.</strong> Row-access and masking policies affect both SYSTEM$DATA_METRIC_SCAN and custom UDTFs.</p><p>Violating rows can then be routed to exception tables for manual review, automated correction, or root cause analysis.</p><h3>How Framework Procedures Wrap Each Operation</h3><p>For the underlying SQL syntax and a full walkthrough of all six operations, see Getting Started with DMFs, Putting it all together. That article also covers the prerequisite role grants (EXECUTE DATA METRIC FUNCTION, SNOWFLAKE.DATA_METRIC_USER, SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER).</p><p>The framework wraps each operation with a procedure that updates control tables before and after executing the SQL. Here’s what each procedure adds beyond the raw ALTER TABLE commands:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9MFL1UbUtL5PvWVJtQAcaw.png" /></figure><p>Each procedure also logs its result for audit purposes. A failed attachment doesn’t affect the schedule. A schedule change doesn’t require re-attaching tests. This separation means you can tune expectations, swap schedules, and add new tests independently.</p><h3>Reconciling Intent vs. Reality</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p90ta93b14t7RxxR9zyrzw.jpeg" /><figcaption><em>Reconciling intended vs. actual DMF state</em></figcaption></figure><p>The framework can compare control table state against Snowflake’s actual metadata to find drift:</p><pre>SELECT t.TABLE_NAME, t.DMF_NAME, t.STATUS AS INTENDED,<br>       CASE WHEN r.METRIC_DATABASE IS NOT NULL THEN &#39;ATTACHED&#39; ELSE &#39;MISSING&#39; END AS ACTUAL<br>FROM DMF_CTRL_1_TESTS t<br>LEFT JOIN TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(<br>    REF_ENTITY_NAME =&gt; t.DATABASE_NAME || &#39;.&#39; || t.SCHEMA_NAME || &#39;.&#39; || t.TABLE_NAME,<br>    REF_ENTITY_DOMAIN =&gt; &#39;TABLE&#39;<br>)) r<br>  ON r.METRIC_NAME = t.DMF_NAME<br>WHERE t.STATUS = &#39;ATTACHED&#39;<br>  AND r.METRIC_DATABASE IS NULL;</pre><p>This query surfaces tests that the control table says should be attached but that Snowflake doesn’t see, catching manual removals or failed deployments.</p><h3>Conclusion</h3><p>A modular DMF management framework turns ad-hoc quality checks into something manageable: control tables track definitions and schedules, discrete procedures handle each operation, the two-layer pattern captures violating rows, and business impact categorization prioritizes checks by risk. With this structure, you can manage hundreds or thousands of tests with consistency, clear role separation, and the ability to reconcile intended state against actual deployments.</p><h3>References</h3><p><strong>Snowflake Documentation</strong></p><p>· <strong>Introduction to Data Quality and Data Metric Functions</strong><br> <a href="https://docs.snowflake.com/en/user-guide/data-quality-intro">https://docs.snowflake.com/en/user-guide/data-quality-intro</a></p><p>· <strong>Use SQL to Set Up Data Metric Functions</strong><br> <a href="https://docs.snowflake.com/en/user-guide/data-quality-working">https://docs.snowflake.com/en/user-guide/data-quality-working</a></p><p>· <strong>Getting Started with Data Quality Monitoring in Snowflake (Quickstart)</strong><br> <a href="https://www.snowflake.com/en/developers/guides/getting-started-with-data-quality-in-snowflake/">https://www.snowflake.com/en/developers/guides/getting-started-with-data-quality-in-snowflake/</a></p><p><strong>Medium Articles</strong></p><p>· <strong>The Trust Layer — Proactive Data Quality with Snowflake’s Native Tools</strong><em> — Sriram Krishnan</em><br> <a href="https://medium.com/@sriramarun/the-trust-layer-proactive-data-quality-with-snowflakes-native-tools-b8c2c033fb92">https://medium.com/@sriramarun/the-trust-layer-proactive-data-quality-with-snowflakes-native-tools-b8c2c033fb92</a></p><p>· <strong>Snowflake Built-in Quality Metrics</strong><em> — Augusto Rosa</em><br> <a href="https://medium.com/snowflake/snowflake-built-in-quality-metrics-3417d7d9f040">https://medium.com/snowflake/snowflake-built-in-quality-metrics-3417d7d9f040</a></p><p>· <strong>Bulletproof Data Pipelines with Snowflake Data Metric Functions</strong><em> — Sabrina Liu</em><br> <a href="https://medium.com/snowflake/bulletproof-data-pipelines-with-snowflake-data-metric-functions-e13f19d9ebce">https://medium.com/snowflake/bulletproof-data-pipelines-with-snowflake-data-metric-functions-e13f19d9ebce</a></p><p><em>Roger Fried is a Solution Architect for the Public Sector at Snowflake, where he helps government and healthcare organizations build reliable, AI-ready data infrastructure. The opinions expressed here are his own.</em></p><p><em>Next in this series: Writing Custom DMFs</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=72cb47d7dc38" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Stop Flying Blind: Using Snowflake Data Metric Functions to Monitor Data Health]]></title>
            <link>https://medium.com/@rogerlfried/stop-flying-blind-using-snowflake-data-metric-functions-to-monitor-data-health-0a35cb9bf71f?source=rss-677c4f7ccc00------2</link>
            <guid isPermaLink="false">https://medium.com/p/0a35cb9bf71f</guid>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Roger Fried]]></dc:creator>
            <pubDate>Sat, 14 Mar 2026 20:06:37 GMT</pubDate>
            <atom:updated>2026-03-14T20:06:37.763Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Stop Flying Blind: Using Snowflake Data Metric Functions to Monitor Data Health</strong></p><p><em>A practical tour of system DMFs, expectations, and the Data Quality Monitoring framework.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cj66oGu99N6NT_scwoM7WQ.jpeg" /><figcaption><em>From data chaos to monitored quality</em></figcaption></figure><p><em>Snowflake’s Data Metric Functions let your warehouse validate itself continuously, without external tools or manual spot-checks.</em></p><p>Snowflake’s Data Quality Monitoring, powered by data metric functions (DMFs), is a native way to measure and monitor data quality inside the platform. DMFs run as a serverless service, integrate with Snowsight’s Data Quality UI, and support expectations and anomaly detection (Preview) so you can turn raw metrics into automated decisions. (Enterprise Edition and above.)</p><p><strong>In this article:</strong></p><p>· What DMFs are and when to use them</p><p>· The six core operations for configuring quality checks</p><p>· Snowflake’s built-in infrastructure for results storage and monitoring</p><p>· Setting up your first DMFs with practical examples</p><p><em>This is the first in a series of articles on DMFs. Future articles will cover building a management framework for DMFs at enterprise scale and writing custom DMFs for domain-specific checks.</em></p><h3>What are Data Metric Functions (DMFs)?</h3><p>Data metric functions (DMFs) are a serverless Snowflake feature that computes data quality metrics (for example, null rates, row counts, referential integrity) and logs the results into a dedicated event table.</p><p>Because DMFs run inside Snowflake, they let you:</p><p>· Use a single, standardized framework for measuring data quality across workloads and teams.</p><p>· Avoid the risks and costs of moving data out of Snowflake just to run data quality tests.</p><p>· Consistently evaluate data quality for both inbound ETL/ELT loads and downstream Snowflake transformations.</p><p>There are two broad categories of DMFs:</p><p>· <strong>System DMFs </strong>— 14 built-in functions covering common testing requirements: DUPLICATE_COUNT, NULL_COUNT, FRESHNESS, and more.</p><p>· <strong>Custom DMFs </strong>— User-defined functions for complex or domain-specific needs, such as referential integrity checks or regex validation.</p><p>You “attach” DMFs to tables, views, materialized views, dynamic tables, and external/Iceberg tables (DMFs cannot currently be attached to streams, hybrid tables, or shared objects).</p><p>They help you quantify key data quality dimensions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CvEG8fOtkkDe9rC5RN-zIw.png" /></figure><p>“Data quality” here means how timely, complete, correct, and consistent your data is. For analytics and AI to be trustworthy, you need repeatable ways to define expectations, measure them, and watch for drift. DMFs handle that.</p><h3>The Six Core DMF Operations</h3><p>Setting up DMFs comes down to six core operations. Some are table-level (schedules), others are DMF-level (expectations, anomaly detection):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rmc8XfaDy2XMDxvQT_RelA.png" /></figure><p>Each operation is explained below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QcHRMLWbVNP6o6BuLL9oFQ.jpeg" /><figcaption><em>The six core DMF operations</em></figcaption></figure><h3>1. Attach</h3><p>Connects a data metric function to a column on a table or to the overall table. The function contains the logic for the measurement to be executed.</p><p>This example applies the NULL_COUNT function on the customer_email column within the orders table.</p><pre>ALTER TABLE my_db.my_schema.orders<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email);</pre><p>Delegated Role (Optional): By default, a DMF executes with the privileges of the role that attached it. A delegated role lets you specify a different role to execute the DMF, useful when the attaching user doesn’t have full access to the target table but another role does.</p><pre>ALTER TABLE my_db.my_schema.orders<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email)<br>  EXECUTE AS ROLE data_quality_admin;</pre><p>Delegated roles are helpful when centralizing DMF management. A platform team can attach DMFs on behalf of data owners without requiring those owners to have direct table access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sOH2mVggzI5EsByMl-079g.jpeg" /><figcaption><em>DMF attachment model</em></figcaption></figure><h3>2. Expectation</h3><p>An expectation defines what a passing result looks like. If NULL_COUNT returns zero and you expected zero, the test passes. If it returns 5, the test fails.</p><p>This example adds an expectation that fails when NULL_COUNT returns anything other than zero.</p><pre>ALTER TABLE my_db.my_schema.orders<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email)<br>  ADD EXPECTATION no_nulls (VALUE = 0);</pre><p>You can define multiple expectations on a single DMF, each evaluated independently:</p><pre>ALTER TABLE my_db.my_schema.orders<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email)<br>  ADD EXPECTATION exp_warning (VALUE &lt;= 100), exp_critical (VALUE &lt;= 1000);</pre><p>Each expectation is reported separately in DATA_QUALITY_MONITORING_RESULTS, enabling tiered alerting (e.g., warning vs. critical thresholds).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HgvxXxFgqFAwlxTKGHZo7w.jpeg" /><figcaption><em>Expectations evaluation flow</em></figcaption></figure><h3>3. Schedule</h3><p>The schedule controls when Snowflake runs all DMFs on a table. Every DMF on the same table shares a single schedule, so you can’t run different checks at different times on the same table.</p><p>You can choose:</p><p>· <strong>Fixed interval </strong>— run regularly, such as ‘5 MINUTE’ or ‘1 HOUR’.</p><p>· <strong>Specific time of day (CRON) </strong>— run on a schedule like: ‘USING CRON 0 6 * * * America/New_York’ (every day at 6:00 a.m. ET).</p><p>· <strong>When data changes </strong>— ‘TRIGGER_ON_CHANGES’ runs DMFs automatically when rows in the table are inserted, updated, or deleted.</p><p><em>Note: TRIGGER_ON_CHANGES is only supported for tables and certain table-like objects. For views, you must use a time-based schedule (fixed interval or CRON).</em></p><p>In the example below, the schedule is set so that DMFs run automatically whenever the table’s data changes.</p><pre>ALTER TABLE my_db.my_schema.orders<br>  SET DATA_METRIC_SCHEDULE = &#39;TRIGGER_ON_CHANGES&#39;;</pre><h3>4. Suspend / Resume</h3><p>Pauses or restarts DMF evaluations without removing the function from the table. This is useful during maintenance windows, bulk data loads, or migrations where temporary quality failures are expected and would generate noise in your monitoring.</p><pre>-- Pause<br>ALTER TABLE my_db.my_schema.orders<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email) SUSPEND;<br> <br>-- Resume<br>ALTER TABLE my_db.my_schema.orders<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_email) RESUME;</pre><h3>5. Anomaly Detection (Preview)</h3><p>Enables Snowflake’s machine learning algorithm to automatically identify unusual values in DMF results. Rather than defining manual thresholds, Snowflake learns baseline patterns from historical data and flags values outside the predicted range.</p><p><em>Note: Anomaly detection is currently in Preview and supports ROW_COUNT and FRESHNESS system DMFs.</em></p><pre>ALTER TABLE my_db.my_schema.orders<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON ()<br>  ANOMALY_DETECTION = TRUE;</pre><p><em>Note: ROW_COUNT and other table-level DMFs (no column argument) require the ON () clause with empty parentheses, not NULL or omitted entirely.</em></p><h3>6. Remove</h3><p>Detaches a data metric function from a table or column. The DMF definition remains available; only the association with the specific object is removed. Use this when a check is no longer relevant — the business rule changed, the column is being deprecated, or you’re consolidating redundant checks.</p><pre>ALTER TABLE my_db.my_schema.customers<br>  DROP DATA METRIC FUNCTION data_freshness_hour ON (last_updated);</pre><p>For system DMFs, the function remains available in SNOWFLAKE.CORE for reattachment to other objects.</p><h3>Quick Reference</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xbz0C0XBnRjOV9Y7t1tKXg.png" /></figure><h3>Leveraging Snowflake’s DMF Data Infrastructure</h3><p>Beyond running quality checks, Snowflake provides system tables, views, and functions that log every result and track every configuration. The same data powers both the Snowsight UI and SQL-based analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E0kPpPgF87b9-LdCNfKK8g.jpeg" /><figcaption><em>Snowflake’s DMF data infrastructure</em></figcaption></figure><h3>DMF Tracking</h3><p>Snowflake maintains metadata that describes your DMF setup:</p><p>· <strong>DATA_METRIC_FUNCTION_REFERENCES — </strong>Lists all DMF attachments (what’s checking what)</p><p>· <strong>DATA_METRIC_FUNCTION_EXPECTATIONS — </strong>Tracks expectation definitions (what should pass)</p><p>These exist as views in ACCOUNT_USAGE (account-wide, with latency) and as table functions in INFORMATION_SCHEMA (database-scoped, real-time). They answer practical questions: What functions are attached where? What schedules are active? Which expectations have been defined?</p><h3>Test Execution Logging</h3><p>Each time a DMF runs, Snowflake captures the result in a system event table. You can access this data through:</p><p>· <strong>SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS — </strong>A view of all DMF results</p><p>· <strong>TABLE(SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS(…)) — </strong>A table function for parameterized queries</p><p>Every record includes the measured value, pass/fail status, evaluation timestamp, and the table and column context. This gives you a complete audit trail of your data quality over time.</p><pre>SELECT measurement_time, metric_name, value, expectation_result<br>  FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS<br>  WHERE table_name = &#39;ORDERS&#39;<br>  ORDER BY measurement_time DESC;</pre><h3>The Snowsight Data Quality UI</h3><p>Snowsight uses this same infrastructure to provide a Data Quality view for each object. In the Database Explorer, you can see:</p><p>· Attached DMFs and their schedules</p><p>· Recent results with pass/fail and anomaly indicators</p><p>· Downstream assets that depend on the table</p><p>· The underlying DMF logic for each check</p><h3>Putting It All Together</h3><p>This section walks through all six operations on a single production table, ORDERS_FACT — from role setup to ongoing monitoring.</p><h3>Prerequisites: Roles and Privileges</h3><p>Before configuring DMFs, ensure your role has the necessary grants:</p><pre>USE ROLE ACCOUNTADMIN;<br>CREATE ROLE IF NOT EXISTS DQ_ROLE;<br><br>GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE DQ_ROLE;<br>GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE DQ_ROLE;<br>GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE DQ_ROLE;</pre><p>At minimum, you need:</p><p>· EXECUTE DATA METRIC FUNCTION on the account</p><p>· Database role SNOWFLAKE.DATA_METRIC_USER (metadata and execution context)</p><p>· Application role SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER (Snowsight UI and event tables)</p><h3>1. Attach DMFs to your table</h3><p>Attach three system DMFs: null detection, duplicates, and overall row count.</p><pre>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (CUSTOMER_EMAIL);<br><br>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (CUSTOMER_EMAIL);<br><br>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON ();</pre><h3>2. Set expectations for pass/fail</h3><p>Without expectations, DMFs record values but nothing is flagged as passed or failed. These two expectations set a zero-tolerance policy for nulls and duplicates.</p><pre>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (CUSTOMER_EMAIL)<br>  ADD EXPECTATION exp_no_nulls (VALUE = 0);<br><br>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (CUSTOMER_EMAIL)<br>  ADD EXPECTATION exp_no_dupes (VALUE = 0);</pre><h3>3. Schedule table evaluation</h3><p>TRIGGER_ON_CHANGES evaluates quality only when data actually changes, keeping serverless costs proportional to activity.</p><pre>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  SET DATA_METRIC_SCHEDULE = &#39;TRIGGER_ON_CHANGES&#39;;</pre><h3>4. Suspend / Resume as needed</h3><p>Pause evaluations during maintenance windows without detaching functions.</p><pre>-- Suspend during maintenance window<br><br>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (CUSTOMER_EMAIL) SUSPEND;<br><br>-- Resume when ready<br>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (CUSTOMER_EMAIL) RESUME;</pre><h3>5. Apply anomaly detection (Preview)</h3><p>When you don’t know the right threshold, anomaly detection learns from historical patterns and flags outliers.</p><pre>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON ()<br>  ANOMALY_DETECTION = TRUE;</pre><h3>6. Remove when no longer needed</h3><p>Detach a DMF cleanly when a check is no longer relevant. The function definition remains available for reattachment.</p><pre>ALTER TABLE DATA_QUALITY_DB.MONITORING.ORDERS_FACT<br>  DROP DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (CUSTOMER_EMAIL);</pre><h3>Monitor results</h3><p>At this point, the table has two active DMFs with expectations, a change-triggered schedule, and anomaly detection on row count. Every evaluation is recorded automatically. View results in Snowsight’s Data Quality tab or query directly:</p><pre>SELECT METRIC_NAME, VALUE, EXPECTATION_RESULT, MEASUREMENT_TIME<br>FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS<br>WHERE TABLE_NAME = &#39;ORDERS_FACT&#39;<br>ORDER BY MEASUREMENT_TIME DESC;</pre><p><strong>What you’ve accomplished: The ORDERS_FACT table now has continuous quality monitoring with no external tools. Null counts and duplicate counts are checked every time data changes, expectations generate pass/fail verdicts, anomaly detection watches for unusual volume shifts, and all results are queryable through SQL or visible in Snowsight.</strong></p><h3>Conclusion</h3><p>DMFs give you continuous data quality monitoring inside Snowflake: system DMFs for common metrics, custom DMFs for domain-specific checks, expectations and anomaly detection for pass/fail decisions, and event tables plus Snowsight for visibility. No external tools required.</p><p>For enterprise-scale deployments managing hundreds or thousands of tests, see Building an Enterprise DMF Management Framework to learn about control tables, modular procedures, and capturing violating rows.</p><h3>References</h3><p><strong>Snowflake Documentation</strong></p><p>· <strong>Introduction to Data Quality and Data Metric Functions</strong><br> <a href="https://docs.snowflake.com/en/user-guide/data-quality-intro">https://docs.snowflake.com/en/user-guide/data-quality-intro</a></p><p>· <strong>Use SQL to Set Up Data Metric Functions</strong><br> <a href="https://docs.snowflake.com/en/user-guide/data-quality-working">https://docs.snowflake.com/en/user-guide/data-quality-working</a></p><p>· <strong>Getting Started with Data Quality Monitoring in Snowflake (Quickstart)</strong><br> <a href="https://www.snowflake.com/en/developers/guides/getting-started-with-data-quality-in-snowflake/">https://www.snowflake.com/en/developers/guides/getting-started-with-data-quality-in-snowflake/</a></p><p><strong>Medium Articles</strong></p><p>· <strong>The Trust Layer — Proactive Data Quality with Snowflake’s Native Tools</strong><em> — Sriram Krishnan</em><br> <a href="https://medium.com/@sriramarun/the-trust-layer-proactive-data-quality-with-snowflakes-native-tools-b8c2c033fb92">https://medium.com/@sriramarun/the-trust-layer-proactive-data-quality-with-snowflakes-native-tools-b8c2c033fb92</a></p><p>· <strong>Snowflake Built-in Quality Metrics</strong><em> — Augusto Rosa</em><br> <a href="https://medium.com/snowflake/snowflake-built-in-quality-metrics-3417d7d9f040">https://medium.com/snowflake/snowflake-built-in-quality-metrics-3417d7d9f040</a></p><p>· <strong>Bulletproof Data Pipelines with Snowflake Data Metric Functions</strong><em> — Sabrina Liu</em><br> <a href="https://medium.com/snowflake/bulletproof-data-pipelines-with-snowflake-data-metric-functions-e13f19d9ebce">https://medium.com/snowflake/bulletproof-data-pipelines-with-snowflake-data-metric-functions-e13f19d9ebce</a></p><h3>Appendix: Limits and Supported Objects</h3><p>· <strong>Supported objects:</strong> Tables, views, materialized views, dynamic tables, external tables, and Iceberg tables. Not supported on streams, hybrid tables, shared objects, or reader accounts.</p><p>· <strong>Association limit:</strong> 10,000 total DMF-object associations per account. Each DMF attached to a table, view, or other supported object counts as one association. Snowflake may adjust this limit over time; check the latest documentation if you’re approaching it.</p><p><em>Roger Fried is a Solution Architect for the Public Sector at Snowflake, where he helps government and healthcare organizations build reliable, AI-ready data infrastructure. The opinions expressed here are his own. Follow for more on data quality, Snowflake, and AI-ready data infrastructure.</em></p><p><em>Next in this series: Building an Enterprise DMF Management Framework — control tables, modular procedures, and capturing violating rows.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0a35cb9bf71f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Your AI Can Read Your Database But Still Can’t Answer Simple Questions]]></title>
            <link>https://medium.com/@rogerlfried/why-your-ai-can-read-your-database-but-still-cant-answer-simple-questions-cd8626946f47?source=rss-677c4f7ccc00------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd8626946f47</guid>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Roger Fried]]></dc:creator>
            <pubDate>Sat, 31 Jan 2026 20:53:40 GMT</pubDate>
            <atom:updated>2026-01-31T20:53:40.763Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Using Snowflake Cortex Analyst and the Next-Generation Semantic Layer to Successfully Deliver AI Access to Data</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZB1MGUp8DgncL7emjnVEmg.jpeg" /><figcaption><em>When AI meets enterprise data, confidence doesn’t equal correctness.</em></figcaption></figure><p>Whenever the topic of querying data warehouses with LLMs comes up someone inevitably says it: <em>“Why don’t we just point the AI at our data warehouse and let it figure everything out?”</em></p><p>It’s a reasonable question. Modern LLMs are genuinely impressive. They can parse database schemas, understand relationships between tables, write syntactically correct SQL, and respond in milliseconds. They sound confident. They sound authoritative.</p><p>Without the right support infrastructure they’re also confidently wrong about 80% of the time.</p><p>I work as a Solution Architect for the Public Sector at Snowflake where I support two healthcare focused organizations, which means the organizations I help can’t afford “mostly right.” When an AI tells a hospital system that readmission rates increased 12% last quarter, that number had better be correct. Staffing decisions, resource allocations, and patient care protocols hang in the balance.</p><p><em>(Full disclosure: I work for Snowflake, and later I’ll use our Cortex Analyst as a concrete example of how to provide LLMs with the right infrastructure to succeed. But the principles here apply regardless of vendor — architecture matters more than where you buy it.)</em></p><p>So I’ve spent a lot of time studying why LLMs fail at seemingly simple analytics questions — and what we can do about it. The answer isn’t to throw out AI or retreat to raw SQL and old-school BI tooling. The answer is a concept that’s been around for decades but has suddenly become critical for making LLMs much wiser about how they leverage structured data: the <strong>semantic layer</strong>.</p><p>If you’re evaluating AI-powered analytics, building data infrastructure, or just trying to understand why your chatbot keeps hallucinating revenue numbers, this is for you.</p><h3>The Seductive Lie of “Just Ask the Data”</h3><p>Let me show you why the “just point AI at the data” approach fails. This isn’t an LLM capability problem — they’re remarkably capable. The problem is that they’re missing roughly 90% of what they need to answer business questions correctly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5GE0aheC6f9w4z_VFwGLyA.jpeg" /><figcaption>LLMs can only see the tip of the iceberg. The business knowledge below the waterline is invisible to them.</figcaption></figure><h3>The Knowledge Iceberg</h3><p>Think of your data warehouse as an iceberg. Above the waterline — visible to any LLM that can read a schema — you have the obvious stuff:</p><ul><li>Table names: encounters, patients, claims</li><li>Column names: patient_id, admission_date, discharge_status</li><li>Data types: VARCHAR, INTEGER, TIMESTAMP</li><li>Primary and foreign keys</li></ul><p>This is maybe 10% of what you need to answer a business question correctly.</p><p><em>LLMs can only see the tip of the iceberg. The business knowledge below the waterline is invisible to them.</em></p><p>Below the waterline — invisible to an LLM without explicit context — lives everything that actually matters:</p><ul><li><strong>Business definitions:</strong> “Readmission” means a patient returning within 30 days for the same condition, excluding planned follow-ups and transfers from other facilities</li><li><strong>Historical context:</strong> “We switched EHR systems in 2023, so patient IDs before that date use a different format”</li><li><strong>Data quality rules:</strong> “Always exclude test patients where patient_type = &#39;TEST&#39; or mrn LIKE &#39;ZZZ%&#39;&quot;</li><li><strong>Join logic:</strong> “Use attending_physician_id for quality metrics, not admitting_physician_id&quot;</li><li><strong>Tribal knowledge:</strong> “The pediatric wing codes diagnoses differently — ask Maria before running reports on patients under 18”</li></ul><p>An LLM staring at your schema has no way to know any of this. It sees los_days and guesses it means “Length of Stay in Days”—but does that include the admission day? The discharge day? What about patients who were transferred to another unit mid-stay? The AI will pick something plausible-sounding and proceed with confidence.</p><h3>Three Ways Unsupported LLMs Fail</h3><p>When we analyze failed AI-generated queries, they usually fall into three categories:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LM0SVh91QGomDUBV7cgxbA.jpeg" /><figcaption><em>The three primary failure modes cascade into each other, compounding errors.</em></figcaption></figure><p><strong>Hallucinated Joins:</strong> The LLM sees two tables that both have a column called provider_id and decides to join on it. Except one refers to the attending physician and the other to the billing provider group. The result? Either a Cartesian explosion or mysteriously missing data.</p><p><strong>Aggregation Ambiguity:</strong> “Give me total patient visits” seems simple. But the LLM doesn’t know to exclude cancelled appointments, doesn’t know that each encounter_id can have multiple line items, and happily COUNTs rows that should be counted distinctly. The number it returns is technically “a number” but has no relationship to reality.</p><p><strong>Semantic Mismatch:</strong> A user asks for “admission date” but the database has registration_dt, triage_dt, bed_assign_dt, and admission_dt. The LLM picks registration_dt because it seems reasonable. Except in this organization, “admission date” specifically means when a bed was assigned. The query runs perfectly. The answer is completely wrong.</p><blockquote><strong>LLMs excel at syntax but struggle with semantics — the meaning behind the data.</strong></blockquote><h3>The Three-Tier Accuracy Progression</h3><p>This isn’t just my opinion. Academic research tells the same story.</p><p>A 2023 study by Sequeda, Allemang, and Jacob tested GPT-4 against enterprise databases and found that in zero-shot scenarios — where the model had only the raw schema — accuracy was just 16.7%. When they added structured business knowledge (what they call a “knowledge graph”), accuracy jumped to 54.2% — more than tripling performance.¹</p><p>But here’s the crucial nuance: <em>knowledge graphs alone aren’t enough</em>. The accuracy gains follow a predictable <strong>three-tier progression</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fLP_eB6mt5oyt00TscnlXw.png" /></figure><p>The Sequeda study tested Tier 2 — semantic context without retrieval. The jump from ~54% to 90%+ accuracy becomes <em>achievable</em> by adding <strong>retrieval-based components</strong> like verified query repositories, where AI retrieves human-approved SQL rather than generating it from scratch. This is the difference between “informed guessing” and “reliable retrieval.”</p><p>Internal benchmarks confirm this progression: the inclusion of a semantic model yields an average <strong>20% improvement</strong> in Text-to-SQL accuracy over schema-only approaches — before any retrieval components are added. Each tier compounds on the previous one.</p><p>The semantic layer isn’t an optimization. It’s a prerequisite.</p><p><em>¹ Sequeda, Allemang, Jacob. “A Benchmark to Understand the Role of Knowledge Graphs on LLM Accuracy for Q&amp;A on Enterprise SQL Databases.” 2023. </em><a href="https://arxiv.org/abs/2311.07509"><em>arXiv:2311.07509</em></a></p><h3>So What Exactly Is a Semantic Layer?</h3><p>Before we talk solutions, we need to clarify terms. The vocabulary in this space is a mess — “semantic layer,” “semantic model,” “metrics layer,” “business layer” — and different vendors use these words to mean different things. Let me cut through the noise.</p><h3>The Menu Analogy</h3><p>Let’s start with what’s most intuitive: the <strong>semantic model</strong>.</p><p>Think of a semantic model like a menu at a restaurant. Without the menu, you’d need to walk into the kitchen, inventory the ingredients, understand what the chef can make, and figure out what combinations are possible. With the menu, you just say “Denver Omelet, please.” The kitchen understands the term perfectly and delivers exactly what you expect. You don’t need to know which fridge the eggs are in or how the peppers are prepped.</p><p>A <strong>semantic model</strong> defines what you can “order” from a specific business domain. Just like a restaurant has different menus for different purposes — breakfast, lunch, dinner, drinks, happy hour, holiday specials — an organization has different semantic models for different domains:</p><ul><li>The <strong>Clinical Quality model</strong> defines readmission rates, mortality indices, patient outcomes</li><li>The <strong>Revenue Cycle model</strong> defines claims, denials, days in accounts receivable</li><li>The <strong>Operations model</strong> defines bed utilization, staffing ratios, wait times</li></ul><p>Each semantic model contains:</p><ul><li><strong>Entities:</strong> What business objects exist (Patients, Encounters, Claims)</li><li><strong>Measures:</strong> What calculations are needed (Readmission Rate, Average Length of Stay, Denial Rate)</li><li><strong>Dimensions:</strong> What attributes you filter by (Service Line, Payer, Discharge Date)</li></ul><p>Instead of asking for COUNT(DISTINCT patient_id) WHERE readmit_flag = 1 AND days_since_discharge &lt;= 30 AND planned_readmit = 0, you just ask for “Readmission Rate by Service Line.” The semantic model knows what that means.</p><p>The <strong>semantic layer</strong> is the complete collection of semantic models plus the infrastructure that stores and serves them. If semantic models are menus, the semantic layer is the restaurant’s entire menu system — all the menus organized together, kept consistent, and made available to everyone who needs them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QOaIsEcK0ewGYgCLM0t_Yg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G6hTuTH22DC7AeLSlzYS9g.jpeg" /><figcaption><em>Semantic models define the “what you can order” for each business domain. The semantic layer is the collection of all models plus the infrastructure that serves them.</em></figcaption></figure><p>You build semantic models (the definitions for each domain) and organize them within a semantic layer (the system). Everyone — humans, BI tools, AI agents — gets the exact same answers because they’re all ordering from the same governed menus.</p><h3>The Three-Layer Stack</h3><p>Here’s another way to think about it. Data architecture has three fundamental layers, like floors in a building:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z_dnvEupr2DO92SLlZXZlg.jpeg" /><figcaption><em>Physical = where. Logical = how. Semantic = what it means. Only the Semantic Layer speaks the language of the business.</em></figcaption></figure><p><strong>Physical layer</strong> (basement): <em>Where</em> data is stored. Partitioning, file formats (Parquet, Iceberg), compression, indexing. The plumbing and foundation. Audience: DBAs, cloud architects.</p><p><strong>Logical layer</strong> (floor plan): <em>How</em> data is organized and connected. Schemas, views, joins, transformations, dbt models. The structure and relationships. Audience: Data engineers, analytics engineers.</p><p><strong>Semantic layer</strong> (room labels): <em>What</em> data means to the business. Definitions, metrics, governed calculations. The labels on the doors that tell you what each space is for. Audience: Business analysts, executives — and AI.</p><p>Here’s the critical distinction: Physical and Logical layers are technical — they answer questions about storage and structure. The Semantic layer is the only one that captures <em>business meaning</em>. It’s what turns raw data into trusted answers.</p><h3>A Word on Terminology (Beware Semantic Drift)</h3><p>I mentioned the vocabulary is messy. Here’s why: these terms have evolved over 30+ years, and different products use them differently.</p><p>“Semantic Layer” = “Metrics Layer” = “Business Layer” = “Analytics Layer” (depending on who you ask)</p><p>“Semantic Model” = “Data Model” = “Logical Model” = “Business Model” (sort of, sometimes)</p><p>This document aims to provide a useful, common-sense framework for understanding these concepts and their relationships. But be prepared: you’ll encounter different terminology across products, vendors, and eras. What Snowflake calls a “semantic view,” another platform might call a “semantic model file” or “metrics definition.” The labels shift; the underlying concepts remain consistent.</p><p>When evaluating tools or discussing architecture with your team, always clarify definitions first. Don’t assume “semantic model” means the same thing to the Looker team as it does to the Power BI team. Focus on the concepts, not the labels.</p><h3>The Evolution: From BI Optimization to AI Infrastructure</h3><p>The semantic layer isn’t a new concept — it’s been around for decades. But it’s undergoing a fundamental transformation.</p><p><strong>The BI Era (2000s-2010s):</strong> In the traditional BI world, semantic layers served a narrower purpose: help BI developers build consistent dashboards. Tools like Business Objects, Cognos, and later Looker used semantic models to ensure that “Revenue” meant the same thing across every report. The audience was technical — BI developers who understood schemas and could navigate complexity.</p><p>The semantic model elements were relatively simple:</p><ul><li>Dimensions and measures</li><li>Table relationships and joins</li><li>Basic filters and hierarchies</li></ul><p>This worked because BI tools followed <strong>pre-defined paths</strong>. A dashboard had fixed queries, built by developers who knew the schema intimately.</p><blockquote><strong>The hidden cost:</strong> Semantics got locked inside BI tools. The carefully crafted definitions in Looker’s LookML couldn’t be shared with Tableau. The measures defined in Power BI were invisible to Python scripts. Each tool became a silo, and organizations ended up maintaining multiple conflicting versions of “Revenue” across different platforms.</blockquote><p><strong>The AI/LLM Era (2020s+):</strong> LLMs changed everything. Now end-users — not BI developers — ask questions directly. And they ask in natural language, with all its ambiguity, synonyms, and implied context.</p><p>This demands a <strong>richer semantic layer</strong> with new elements:</p><ul><li><strong>Custom Instructions</strong> — human-to-LLM guidance for edge cases</li><li><strong>Verified Query Repository</strong> — pre-approved SQL for complex patterns</li><li><strong>Synonyms &amp; Sample Values</strong> — teaching AI your vocabulary</li><li><strong>Unstructured Document Search</strong> — retrieving context from docs</li></ul><p>The semantic layer evolved from “help developers build dashboards” to “empower AI to answer any question correctly.” Companies like Snowflake with Cortex Analyst are building this new generation of semantic infrastructure.</p><blockquote><strong>The opportunity:</strong> This shift creates a chance to consolidate. Instead of semantic definitions scattered across Looker, Tableau, Power BI, and countless reports, organizations can centralize business logic in a unified semantic layer. Even where multiple tools are involved — such as dbt for transformation and Snowflake for analytics — modern architectures increasingly support semantic layer integration, allowing definitions to flow across platforms rather than remaining trapped in silos.</blockquote><h3>The Good News: We Can Actually Solve This</h3><p>If you’ve made it this far, you might be feeling a bit overwhelmed. Building a semantic layer sounds like a lot of work — and historically, it has been. Companies spent years manually documenting business logic in tools like Business Objects or Cognos.</p><p>Here’s the good news: we have more context available than ever before, and modern tools can help accelerate the process dramatically.</p><h3>The Metadata Goldmine</h3><p>Most organizations are sitting on a treasure trove of implicit semantic information:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X-KehSwclJBWhNH7N0lGLg.jpeg" /><figcaption><em>Query histories, BI models, and documentation can all accelerate semantic layer development.</em></figcaption></figure><p><strong>Query histories:</strong> Every SQL query that’s ever run against your warehouse reveals patterns — common joins, frequently used filters, aggregation conventions. This is a map of what people actually do with your data.</p><p><strong>BI data models:</strong> If you have Looker (LookML), Tableau, or Power BI, you already have years of accumulated business logic. Measures, dimensions, relationships — it’s all there, waiting to be extracted.</p><p><strong>Documentation:</strong> Data dictionaries, wiki pages, Confluence docs, even Slack threads. Tribal knowledge that’s been captured (often imperfectly) over time.</p><blockquote>⚠️ <strong>The catch:</strong> These sources show what people DID, not necessarily what they SHOULD do. Query histories capture bad patterns along with good ones. Documentation is often outdated. They’re starting points, not sources of truth.</blockquote><p>This is where the workflow comes together: AI can mine these sources and draft initial definitions, but someone needs to validate them. That “someone” is irreplaceable.</p><h3>Why Domain Experts Remain Irreplaceable</h3><p>This is where I have to deliver some tough news for the “fully automated AI” crowd: <strong>humans are not optional</strong>.</p><p>AI can scan schemas, identify patterns, extract definitions from BI models, and index documentation. It can suggest possible metrics and flag inconsistencies. What it cannot do:</p><ul><li>Decide which of three conflicting definitions is “correct”</li><li>Understand why a particular exception exists</li><li>Navigate organizational politics around data ownership</li><li>Provide historical context that was never written down</li><li>Take responsibility for decisions</li></ul><p>I call this the “Semantic Hallucination” problem. If an AI auto-generates a semantic model, it might incorrectly infer that credit_limit is a “Risk Metric” when your business actually treats it as a “Sales Opportunity Metric.” If that hallucinated definition gets codified, every downstream agent will be systematically wrong.</p><p>The opposite shortcut is equally dangerous: adopting generic, off-the-shelf semantic definitions that don’t capture your organization’s unique terminology. A “cookie-cutter” semantic layer built from industry templates might define “Customer” in ways that don’t match your business model, or calculate “Revenue” using assumptions that don’t apply to your contracts. Generic definitions are barely better than no definitions — they create false confidence while still producing wrong answers.</p><blockquote>LLMs can accelerate semantic layer creation, but humans must validate and own the result.</blockquote><h3>The Human-AI Partnership Model</h3><p>The optimal approach isn’t “AI builds everything” or “humans do it manually.” It’s a partnership where each party does what they’re good at:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*knX6iUffIuq7gF2MW82Tig.jpeg" /><figcaption><em>AI accelerates the tedious work; humans focus on judgment and validation.</em></figcaption></figure><p><strong>Discovery phase:</strong> AI scans schemas, query histories, and documentation. Humans prioritize which domains matter most.</p><p><strong>Drafting phase:</strong> AI generates initial metric definitions and suggests relationships. Humans review, correct, and enhance.</p><p><strong>Validation phase:</strong> AI tests queries and flags anomalies. Humans approve or reject results.</p><p><strong>Maintenance phase:</strong> AI detects schema drift and usage changes. Humans decide what to update.</p><p>This partnership model is what the industry is converging on. It’s faster than purely manual approaches, but maintains the human judgment that prevents semantic hallucination.</p><p>This partnership model describes the <em>workflow</em> — but it requires infrastructure purpose-built for the task. Let me show you what this looks like in practice.</p><h3>What This Looks Like in Practice</h3><p>Let me get concrete. What does a modern semantic layer implementation actually look like?</p><h3>The State of the Art</h3><p>The best systems today share four characteristics:</p><p><strong>1. Wide metadata inputs:</strong> They ingest not just schemas, but query histories, BI model definitions, documentation, and even unstructured text. The more context, the better.</p><p><strong>2. Automated generation:</strong> AI assists in drafting semantic definitions, identifying patterns, and suggesting relationships. This accelerates the tedious parts.</p><p><strong>3. Human curation:</strong> Domain experts validate and refine. The system makes it easy to review, approve, and modify — not just accept blindly.</p><p><strong>4. Portfolio of models:</strong> Rather than one monolithic semantic model, organizations maintain multiple domain-specific models (Clinical Quality, Revenue Cycle, Operations) that can be composed as needed.</p><h3>Snowflake’s Approach: Cortex Analyst</h3><p>As I mentioned earlier, I’ll use Snowflake’s approach as a concrete example — not because it’s the only option, but because I know it well and it illustrates the patterns that matter.</p><p>Cortex Analyst moves beyond traditional Text-to-SQL by combining multi-agent AI orchestration with a <strong>uniquely rich semantic layer</strong> — one that goes far beyond traditional approaches.</p><h3>The Next-Generation Semantic Layer</h3><p>What makes Cortex Analyst’s semantic layer different is that it represents the <strong>evolution</strong> from BI-era modeling to AI-native infrastructure. It combines traditional elements (proven over decades) with new AI-native enhancements (demanded by LLMs):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*peHdZlaP7jaIk_fOasXT5w.png" /></figure><p><strong>The Structure Spectrum:</strong></p><p>Semantic elements exist on a spectrum from highly structured to flexible:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uilqjm2DthcbLCOla0-1-w.png" /></figure><p>The left side handles what’s formulaic and repeatable. The right side handles ambiguity and edge cases. Custom Instructions are where domain experts speak directly to the LLM in natural language.</p><p>This hybrid approach is what makes 90%+ accuracy achievable vs. &lt;50% for traditional Text-to-SQL. The AI understands <em>both</em> your data structure and your business language.</p><h3>Why Each Component Matters for Accuracy</h3><p>What makes each semantic view element actually drive accuracy improvements? Here’s the mechanism behind each:</p><p><strong>Synonyms → Bridge proprietary vocabulary.</strong> Modern LLMs understand common medical terms, but they don’t know <em>your</em> organization’s unique language. Synonyms shine for proprietary terms, internal jargon, and legacy system names that only your team uses. When your database calls it enc_type_cd but clinicians say “Inpatient Visit” or “IP Admission,” synonyms make that connection explicit.</p><p><strong>Dimensions vs Measures → Prevent aggregation errors.</strong> When columns are explicitly tagged as categorical (dimensions: filter/group by) vs. quantitative (measures: aggregate), the AI can’t accidentally SUM an ID column or GROUP BY a metric. This classification prevents an entire category of logical errors.</p><p><strong>Named Filters → Standardize business logic.</strong> “Medicare Patients” always means payer_category = &#39;Medicare&#39; AND coverage_type IN (&#39;A&#39;, &#39;B&#39;, &#39;Advantage&#39;). “Active Census” always means discharge_date IS NULL AND patient_status = &#39;Admitted&#39;. Without named filters, the AI invents definitions—and they drift between queries.</p><p><strong>Table Joins → Prevent hallucinated relationships.</strong> By explicitly defining valid join paths, the AI cannot invent joins between unrelated tables. This eliminates the “Cartesian explosion” and “mysteriously missing data” failure modes described earlier.</p><h3>The Verified Query Repository: A Deeper Look</h3><p>Of all the AI-native enhancements, the <strong>Verified Query Repository (VQR)</strong> deserves special attention — it’s the single most impactful component for achieving high accuracy.</p><p><strong>What it is:</strong> A collection of human-approved “golden SQL” queries paired with the natural language questions they answer. When a user asks a question that matches (or closely resembles) a verified query, the system <em>retrieves</em> the pre-validated SQL rather than <em>generating</em> new SQL from scratch.</p><p><strong>Why it matters:</strong> This fundamentally changes the accuracy model. Generated SQL is probabilistic — the LLM might get it right, or it might hallucinate a join or misinterpret a filter. Retrieved SQL is deterministic — if the question matches, the answer is guaranteed correct. For matched patterns, VQR achieves 100% accuracy.</p><p><strong>The coverage question:</strong> “But how many verified queries do I need?” In practice, most organizations find that a moderate number of VQRs can cover a disproportionate share of actual user questions. Clinical and operational staff tend to ask variations of the same core questions: “What’s our readmission rate this month?” “Which units have the longest wait times?” “How are our quality scores trending?” Each VQR can match multiple phrasings of the same underlying question.</p><p><strong>The latency bonus:</strong> Because matched questions bypass multiple LLM generation steps, verified queries are also <em>faster</em> — often 2–3x faster than generated queries. You get better accuracy <em>and</em> better performance.</p><p><strong>Building your VQR:</strong> Start with the questions your team asks most frequently. Review query logs. Ask your analysts what they compute repeatedly. Each verified query you add is an investment that pays dividends every time that question pattern is asked.</p><p><strong>Custom Instructions → Direct human-to-LLM communication.</strong> This is where domain experts encode everything that doesn’t fit neatly into metrics or filters. Examples:</p><ul><li>“When users ask about length of stay, always use whole days, counting admission day but not discharge day”</li><li>“Round all rate calculations to 1 decimal place unless the user specifies otherwise”</li><li>“If the question involves patients before 2023, use the LEGACY_EHR tables; for all others, use CURRENT_EHR”</li><li>“Fiscal Year starts in July, so ‘Current Year’ means July 1 to June 30”</li><li>“Don’t respond to questions that could identify individual patients — decline and explain why”</li></ul><p>Custom Instructions handle the messy, context-dependent rules that can’t be expressed as formulas. They’re the escape valve for real-world complexity.</p><blockquote>“The term ‘High-Risk Patient’ is semantically opaque. A raw model might guess ‘Patients over 65.’ The semantic model explicitly defines it as risk_score &gt;= 3.5 AND chronic_condition_count &gt;= 2. This eliminates guesswork.”</blockquote><h3>How the Semantic Layer Powers Each Agent</h3><p>The multi-agent pipeline draws on different semantic elements at each stage:</p><p><strong>1. Classification Agent:</strong> Uses <em>descriptions, synonyms, and categorization instructions</em> to determine if the question is even answerable. If someone asks “What was the best product?” (too vague), it rejects the question and suggests alternatives instead of guessing. This alone eliminates a huge class of errors.</p><p><strong>2. SQL Generation:</strong> Uses <em>joins, metrics, filters, and SQL generation instructions</em> to draft accurate queries with correct business logic. Multiple models (Llama, Mistral) compete, and the best results are merged.</p><p><strong>3. Verified Query Repository:</strong> Checks for <em>human-approved “golden SQL”</em> matching the question pattern. This is the key differentiator: instead of <em>generating</em> queries (and risking errors), the AI <em>retrieves</em> pre-validated answers. For known complex queries, this provides 100% accuracy — transforming AI from a “guesser” into a reliable “retriever” of human-approved results.</p><p><strong>4. Document Search:</strong> Retrieves <em>context from unstructured docs</em> (wikis, PDFs) when additional business context is needed beyond the structured semantic model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3EI2picvQedRVxru_5IPKA.png" /></figure><p>Snowflake’s Semantic View: Where the Model Lives</p><p>In Snowflake, a semantic model is implemented as a <strong>semantic view</strong> — a specific Snowflake object type stored as a downloadable and uploadable .yaml file. The semantic view contains all the elements we’ve discussed: dimensions, metrics, joins, synonyms, custom instructions, and verified queries.</p><p>Think of the hierarchy this way:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VPCToMz0nCUvEm4hCz8zVQ.png" /></figure><h3>The Bottom Line</h3><p>The semantic layer has evolved from a “nice-to-have” for dashboards into a “must-have” for any AI-powered analytics. It’s not just another layer in your stack — this next-generation semantic layer is your secret to AI data infrastructure success.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/956/1*880GiWoH1UENnZ4ZTHcihg.png" /></figure><p>Here’s the insight that ties it all together: the bottleneck for AI-powered analytics is no longer the model’s ability to <em>write</em> SQL — modern LLMs do that remarkably well. The bottleneck is the model’s ability to <em>understand</em> the database: what the data means, how concepts relate, which business rules apply. The semantic layer provides that understanding.</p><p>The next time someone in your organization says “let’s just point AI at the data warehouse,” you’ll have a response: <em>“Great idea. First, let’s build the semantic layer that will make that actually work.”</em></p><p>Because without it, you’re not getting AI-powered analytics. You’re getting confidently wrong answers, delivered instantly.</p><p>And in a world where decisions have real consequences, “confidently wrong” is worse than no answer at all.</p><p><em>Roger Fried is a Solution Architect for the Public Sector at Snowflake, where he helps government and healthcare organizations build reliable, AI-ready data infrastructure. The opinions expressed here are his own.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd8626946f47" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>