<?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 Jeffrey Jacobs on Medium]]></title>
        <description><![CDATA[Stories by Jeffrey Jacobs on Medium]]></description>
        <link>https://medium.com/@jmjacobs?source=rss-55cf65245c0b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Jeffrey Jacobs on Medium</title>
            <link>https://medium.com/@jmjacobs?source=rss-55cf65245c0b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 20:09:57 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jmjacobs/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[Column Alias Syntax vs Conventional SELECT]]></title>
            <link>https://medium.com/altasql-for-snowflake/column-alias-syntax-vs-conventional-select-85657710854c?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/85657710854c</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[altasql-for-snowflake]]></category>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sun, 25 May 2025 21:27:06 GMT</pubDate>
            <atom:updated>2025-05-25T21:27:06.994Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/96/0*Ks2NPs-bUUB09Kjx.png" /></figure><p>Why Column Alias Syntax with Object Aliases Should Be the Standard in SQL Development</p><p>If you’ve spent time managing SQL codebases-especially in complex Snowflake environments-you’ve likely wrestled with the ambiguity and fragility of traditional SELECT column queries. While these are quick to write, they come at the cost of clarity, reusability, and governance.</p><p>AltaSQL <em>generates </em>all SELECT statements and clauses from meta-data using Column Alias Syntax, eliminating hand crafting!</p><p>At AltaSQL, we advocate for a new standard: using column alias syntax with object qualification , like:</p><pre>SELECT <br>cust.&quot;CustomerId&quot; AS &quot;Customer ID&quot;, <br>cust.&quot;FirstName&quot; AS &quot;First Name&quot; <br>FROM &quot;SALES_DB&quot;.&quot;PUBLIC&quot;.&quot;Customer&quot; AS cust</pre><p>AltaSQL generates all SELECT statements and clauses using Column Alias Syntax.</p><p>It’s not just cleaner SQL-it’s foundational for automation, documentation, and scalable metadata management.</p><p>What Makes This Approach Superior?</p><p>✅ 1. Enhanced Readability and Documentation</p><p>Readable column names such as “Customer ID” help both technical and non-technical stakeholders immediately understand the data’s intent. Object aliases (cust) make joins easier to trace.</p><p>🔄 2. Safe Refactoring and Rapid Iteration</p><p>Want to rename a column? Add an expression? Reorder fields? Even add a CTE? Column alias syntax makes this frictionless. It eliminates the dangers of conventional SELECT and reduces the need to constantly revisit underlying schemas.</p><p>🔍 3. Properly Qualified References Improve Joins</p><p>Object aliases paired with properly qualified source references prevent conflicts during JOIN operations and make debugging significantly easier. This is especially crucial in data warehouses with deeply nested schemas.</p><p>⚙️ 4. Unlock Metadata Automation with AltaSQL</p><p>AltaSQL automates this structure directly from metadata repositories adding comments, tags, and masking policies for generating CREATE VIEW statements. You get consistent, editable, production-ready SQL without writing it from scratch.</p><p>Why Don’t More Developers Do This?</p><p>Despite the clear benefits, developers often avoid alias syntax due to:</p><ul><li>Speed over structure during prototyping</li><li>Lack of enforced standards</li><li>Tools that autocomplete simple names</li><li>The “extra effort” perception</li></ul><p>AltaSQL eliminates these barriers with a metadata-first approach that makes best practices the path of least resistance.</p><p>The Future: Metadata-Driven SQL</p><p>This isn’t just about pretty queries. It’s about moving toward semantic layers , automated governance , and low-friction collaboration . SQL doesn’t have to be hand-crafted anymore!</p><p>Let’s move past conventional SELECT and embrace a cleaner, smarter SQL standard.</p><p>#SQLBestPractices #Snowflake #DataEngineering #MetadataManagement #DataGovernance #AltaSQL #ColumnAlias #SQLDevelopment #DataQuality #AnalyticsEngineering #SemanticLayer #DataAutomation</p><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/column-alias-syntax-vs-conventional-select-jeffrey-jacobs-w2qkc/?trackingId=GIQ7nt1MRUSiaon58fxicw%3D%3D"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=85657710854c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altasql-for-snowflake/column-alias-syntax-vs-conventional-select-85657710854c">Column Alias Syntax vs Conventional SELECT</a> was originally published in <a href="https://medium.com/altasql-for-snowflake">AltaSQL for Snowflake</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Pivot ANYTHING in Snowflake, Without the SQL PIVOT Function]]></title>
            <link>https://medium.com/snowflake/pivot-anything-in-snowflake-without-the-sql-pivot-function-ba030dce8dfc?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/ba030dce8dfc</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Mon, 16 May 2022 22:06:21 GMT</pubDate>
            <atom:updated>2022-05-17T03:39:34.826Z</atom:updated>
            <content:encoded><![CDATA[<p>The SQL PIVOT function has very limited functionality. It is only useful for numeric data, with very explicit, “hard coding” requirements. We frequently require more flexibility in pivoting data.</p><p>The desired flexibility can be accomplished with a combination of LISTAGG and SPLIT_PART SQL functions. This approach allows more robust pivoting functionality; <strong><em>it can be used to pivot any type of data as well as complex expressions.</em></strong> The examples may also be feasible in other RDBMS, as most have both LISTAGG and SPLIT_PART functions.</p><p>LISTAGG is an “aggregation” function, requiring GROUP BY column(s), while allowing complex column expressions for the pivoted values.</p><p>The basic code is similar to PIVOT:</p><pre>SELECT<br>        &lt;group_by_columns&gt;, -- Typically Primary Key Columns<br>        LISTAGG(&lt;column_expr&gt;,&#39;&lt;seperator&gt;&#39;) <br>        [WITHIN GROUP ( &lt;orderby_clause&gt; )] COLUMN_LIST<br>        FROM<br>            &lt;source_object&gt;<br>        [WHERE] <br>        GROUP BY<br>            &lt;group_by_columns&gt;</pre><p>We will look at three different types of pivoting.</p><h3>Example One</h3><p>In this example we know all of the desired pivoted column names and effectively hard code them. Following is the management structure of a consulting firm, consisting of the Account Mgr, the Project Mgr and the Chief Architect.</p><p>First, create the table:</p><pre>CREATE TABLE<br>    IF NOT EXISTS PROJECT_MGMT<br>    (<br>        PROJECT_NAME VARCHAR,<br>        PROJECT_ROLE VARCHAR,<br>        PERSON       VARCHAR<br>    );</pre><p>Populate the table:</p><pre>INSERT INTO &quot;project_mgmt&quot; (&quot;PROJECT_NAME&quot;, &quot;PROJECT_ROLE&quot;, &quot;PERSON&quot;) VALUES <br>(&#39;Organic Foods&#39;, &#39;Project Mgr&#39;, &#39;Bug Bunny&#39;),<br>(&#39;Ring a Ding Phones&#39;, &#39;Project Mgr&#39;, &#39;Ernestine Smith&#39;),<br>(&#39;Fast Cars Dealership&#39;, &#39;Project Mgr&#39;, &#39;Wilber Jones&#39;),<br>(&#39;Organic Foods&#39;, &#39;Account Mgr&#39;, &#39;Daffy Duck&#39;),<br>(&#39;Ring a Ding Phones&#39;, &#39;Account Mgr&#39;, &#39;Marjorie Jackson&#39;),<br>(&#39;Fast Cars Dealership&#39;, &#39;Account Mgr&#39;, &#39;TBD&#39;),<br>(&#39;Organic Foods&#39;, &#39;Chief Architect&#39;, &#39;Dilbert Clark&#39;),<br>(&#39;Ring a Ding Phones&#39;, &#39;Chief Architect&#39;, &#39;Nora Eclaire&#39;),<br>(&#39;Fast Cars Dealership&#39;, &#39;Chief Architect&#39;, &#39;Linda Brock&#39;);</pre><p>The elements in the LISTAGG are ordered by the desired column name. The SPLIT_PART function is used to pick the desired column from the ordered LISTAGG elements to create the desired pivoted column(s).</p><pre>SELECT<br>    &lt;group_by_columns&gt;<br>    SPLIT_PART(COLUMN_LIST,&#39;,&#39;,1) PIVOTED_COLUMN_1,<br>    SPLIT_PART(COLUMN_LIST,&#39;,&#39;,2)     PIVOTED_COLUMN_2 ,<br>    SPLIT_PART(COLUMN_LIST,&#39;,&#39;,3)     PIVOTED_COLUMN_3,<br>    SPLIT_PART(COLUMN_LIST,&#39;,&#39;,4)     PIVOTED_COLUMN_4<br>FROM<br>    (   SELECT<br>            &lt;group_by_columns&gt;, -- Typically Primary Key Columns<br>            LISTAGG(&lt;column_expr&gt;,&#39;&lt;seperator&gt;&#39;)<br>            [WITHIN GROUP (ORDER BY &lt;orderby_clause&gt;)] COLUMN_LIST<br>        FROM<br>            &lt;source_object&gt;<br>        [WHERE] <br>        GROUP BY<br>            &lt;group_by_columns&gt;<br>    );</pre><p>The ORDER BY is critical to this type of pivot, as it orders the elements in the LISTAGG by the desired column names.</p><p>For the project management pivot query:</p><pre>SELECT<br>    PROJECT_NAME,<br>    SPLIT_PART(PROJ_MGMT,&#39;,&#39;,1) &quot;Account Mgr&quot;,<br>    SPLIT_PART(PROJ_MGMT,&#39;,&#39;,2) &quot;Chief Architect&quot;,<br>    SPLIT_PART(PROJ_MGMT,&#39;,&#39;,3) &quot;Project Mgr&quot;<br>FROM<br>    (   SELECT<br>            &quot;PROJECT_NAME&quot;,<br>            LISTAGG(PERSON,&#39;,&#39;) WITHIN GROUP (ORDER BY PROJECT_ROLE)  <br>               AS PROJ_MGMT<br>        FROM<br>            (   SELECT<br>                    PROJECT_NAME,<br>                    PROJECT_ROLE,<br>                    PERSON<br>                FROM<br>                    &quot;PROJECT_MGMT&quot;<br>                WHERE<br>                    PROJECT_ROLE IN (&#39;Account Mgr&#39;,<br>                                     &#39;Chief Architect&#39;,<br>                                     &#39;Project Mgr&#39;) )<br>        GROUP BY<br>            PROJECT_NAME);</pre><p>Executing the query:</p><pre>PROJECT_NAME         Account Mgr      Chief Architect Project Mgr     <br>-------------------- ---------------- --------------- --------------- <br>Ring a Ding Phones   Marjorie Jackson Nora Eclaire    Ernestine Smith <br>Fast Cars Dealership TBD              Linda Brock     Wilber Jones    <br>Organic Foods        Daffy Duck       Dilbert Clark   Bug Bunny</pre><h3>Example Two</h3><p>In this example, we demonstrate handling a fixed possible number of columns with the same type value. We handle the case of less than the maximum number of records existing to fill desired columns.</p><p>We demonstrate this with a loan application that allows a maximum of four co-signers. Only records of actual co-signers exist. For less than 4 co-signers, we want NULL in the column. If SPLIT_PART specifies a part greater than the length of the LISTAGG, it returns the empty string, not NULL; the query converts the empty string to NULL.</p><p>Create the table:</p><pre>CREATE TABLE<br>    IF NOT EXISTS LOAN_APPLICATION<br>    (<br>        BORROWER  VARCHAR,<br>        CO_SIGNER VARCHAR<br>    );</pre><p>Populate the table:</p><pre>INSERT INTO &quot;LOAN_APPLICATION&quot; (&quot;BORROWER&quot;, &quot;CO_SIGNER&quot;) VALUES <br>(&#39;Albert Smith&#39;, &#39;Marjorie Smith&#39;),<br>(&#39;Albert Smith&#39;, &#39;Bob Smith&#39;),<br>(&#39;Albert Smith&#39;, &#39;Rudy Gonzalez&#39;),<br>(&#39;Albert Smith&#39;, &#39;Scrooge McDuck&#39;),<br>(&#39;Elmer Fudd&#39;, &#39;Bugs Bunny&#39;),<br>(&#39;Elmer Fudd&#39;, &#39;Daffy Duck&#39;),<br>(&#39;Elmer Fudd&#39;, &#39;Susan Fudd&#39;),<br>(&#39;Indira Jones&#39;, &#39;Aya Jones&#39;),<br>(&#39;Indira Jones&#39;, &#39;George Jones&#39;);</pre><p>The queryordering by co-signer name:</p><pre>SELECT<br>    BORROWER,<br>    IFF(SPLIT_PART(CO_SIGNERS,&#39;,&#39;,1)&lt;&gt;&#39;&#39;,SPLIT_PART(CO_SIGNERS,&#39;,&#39;,1),NULL) &quot;Co-signer 1&quot;,<br>    IFF(SPLIT_PART(CO_SIGNERS,&#39;,&#39;,2)&lt;&gt;&#39;&#39;,SPLIT_PART(CO_SIGNERS,&#39;,&#39;,2),NULL) &quot;Co-signer 2&quot;,<br>    IFF(SPLIT_PART(CO_SIGNERS,&#39;,&#39;,3)&lt;&gt;&#39;&#39;,SPLIT_PART(CO_SIGNERS,&#39;,&#39;,3),NULL) &quot;Co-signer 3&quot;,<br>    IFF(SPLIT_PART(CO_SIGNERS,&#39;,&#39;,4)&lt;&gt;&#39;&#39;,SPLIT_PART(CO_SIGNERS,&#39;,&#39;,4),NULL) &quot;Co-signer 4&quot;<br>FROM<br>    (   SELECT<br>            BORROWER,<br>            LISTAGG(CO_SIGNER,&#39;,&#39;) WITHIN GROUP (ORDER BY CO_SIGNER)<br>            AS CO_SIGNERS<br>        FROM<br>            (   SELECT<br>                    BORROWER,<br>                    LOAN_APPLICATION.CO_SIGNER<br>                FROM<br>                    PUBLIC.LOAN_APPLICATION )<br>        GROUP BY</pre><p>Execute the query:</p><pre>BORROWER     Co-signer 1 Co-signer 2    Co-signer 3   Co-signer 4    <br>------------ ----------- -------------- ------------- -------------- <br>Albert Smith Bob Smith   Marjorie Smith Rudy Gonzalez Scrooge McDuck <br>Elmer Fudd   Bugs Bunny  Daffy Duck     Susan Fudd    (null)         <br>Indira Jones Aya Jones   George Jones   (null)        (null)</pre><h3>Example Three</h3><p>Finally, the “ultimate” pivot! LISTAGG accepts <em>column expressions</em>, not just columns ,as arguments. We rotate the desired data into JSON VARIANTs. We can now write queries SELECTing the desired pivot columns and their values<em>. </em><strong><em>We do not need to predefine the pivot column names.</em></strong> If the desired pivot column doesn’t exist, the pivoted column has NULL as the value. We can now expand the number of roles for our projects at will!</p><p>This type of query can be packaged as a CTE, VIEW or sub-query.</p><pre>SELECT<br>            PARSE_JSON(PROJ_ROLES) PARSED<br>        FROM<br>            (   SELECT<br>                    CONCAT(&#39;{&#39;, CONCAT(&#39;&quot;PROJECT_NAME&#39;,&#39;&quot;:&quot;&#39;,PROJECT_NAME,&#39;&quot;,&#39;, LISTAGG(CONCAT(&#39;&quot;&#39;<br>                    ,PROJECT_ROLE, &#39;&quot;:&#39;,&#39;&quot;&#39;,PERSON,&#39;&quot;&#39;),&#39;,&#39;) ),&#39;}&#39;)PROJ_ROLES<br>                FROM<br>                    (   SELECT<br>                            PROJECT_NAME,<br>                            PROJECT_ROLE,<br>                            PERSON<br>                        FROM<br>                            &quot;PROJECT_MGMT&quot;)<br>                GROUP BY<br>                    PROJECT_NAME));</pre><p>This form does not require SPLIT_PART. SELECT is used instead. The fields in the example are cast to ‘::string’ to strip the double quotes from the JSON. Other data types will required an appropriates CAST/::.</p><p>Remember that results are indeterminate if duplicate fields exist in the object!</p><pre>SELECT<br>    PARSED:PROJECT_NAME::string &quot;Project&quot;,<br>    PARSED:&quot;Project Mgr&quot;::string &quot;Project Mgr&quot;,<br>    PARSED:&quot;Chief Architect&quot;::string &quot;Chief Architect&quot;,<br>    PARSED:&quot;DBA&quot;::string &quot;DBA&quot;<br>FROM<br>    (   SELECT<br>            PARSE_JSON(PROJ_ROLES) PARSED<br>        FROM<br>            (   SELECT<br>                    CONCAT(&#39;{&#39;, CONCAT(&#39;&quot;PROJECT_NAME&#39;,&#39;&quot;:&quot;&#39;,PROJECT_NAME,&#39;&quot;,&#39;, LISTAGG(CONCAT(&#39;&quot;&#39;<br>                    ,PROJECT_ROLE, &#39;&quot;:&#39;,&#39;&quot;&#39;,PERSON,&#39;&quot;&#39;),&#39;,&#39;) ),&#39;}&#39;)PROJ_ROLES<br>                FROM<br>                    (   SELECT<br>                            PROJECT_NAME,<br>                            PROJECT_ROLE,<br>                            PERSON<br>                        FROM<br>                            &quot;PROJECT_MGMT&quot;)<br>                GROUP BY<br>                    PROJECT_NAME));</pre><p>Execute the query:</p><pre>Project              Project Mgr     Chief Architect DBA    <br>-------------------- --------------- --------------- ------ <br>Organic Foods        Bug Bunny       Dilbert Clark   (null) <br>Fast Cars Dealership Wilber Jones    Linda Brock     (null) <br>Ring a Ding Phones   Ernestine Smith Nora Eclaire    (null)</pre><p>Add a newly hired DBA to the Organic Foods project; recall that no DBAs were previously present!</p><pre>INSERT INTO <br>   PROJECT_MGMT VALUES <br>    (   &#39;Organic Foods&#39;, <br>        &#39;DBA&#39;, <br>        &#39;Clark Kent&#39;);A</pre><p>Execute the previous SELECT:</p><pre>Project              Project Mgr     Chief Architect DBA        <br>-------------------- --------------- --------------- ---------- <br>Organic Foods        Bug Bunny       Dilbert Clark   Clark Kent <br>Fast Cars Dealership Wilber Jones    Linda Brock     (null)     <br>Ring a Ding Phones   Ernestine Smith Nora Eclaire    (null)</pre><p>We now have the ultimately flexible pivot query pattern! Constructing the JSON with appropriate naming enables pivoting multiple columns, potentially the entire table!</p><p>Thanks to fellow Snowflake Data Super Hero for his review and improvements!</p><p>Copyright © 2022, Jeffrey Jacobs &amp; Associate, LLC</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ba030dce8dfc" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/pivot-anything-in-snowflake-without-the-sql-pivot-function-ba030dce8dfc">Pivot ANYTHING in Snowflake, Without the SQL PIVOT Function</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake DBA-101; Deploying Standardized, Fully Functional Databases]]></title>
            <link>https://medium.com/snowflake/snowflake-dba-101-deploying-standardized-fully-functional-databases-34b9c935c223?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/34b9c935c223</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[dba]]></category>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sun, 20 Mar 2022 20:27:36 GMT</pubDate>
            <atom:updated>2022-03-21T16:56:18.005Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/643/1*HzIRsGwjt2sVBw1iD3YTMQ.jpeg" /></figure><p>This article provides both a guide and script for creating and deploying standardized, fully functional, Snowflake DATABASEs. A complete script is described. The script is available at <a href="https://github.com/Jeffreymjacobs/DBA-101-Scripts">https://github.com/Jeffreymjacobs/DBA-101-Scripts</a></p><p>The following standard architecture is used for each database. This is the basic architecture used for creating ELVT architectures for my clients.</p><p>The resulting Snowflake objects have the following features:</p><ol><li>Consistent naming standards. All objects have an appropriate suffix, e.g. _DB, _ROLE, etc.</li><li>The Snowflake objects are “encapsulated”, i.e. no shared resource or roles, with exception of the administration role being granted to SYSADMIN.</li><li>Basic Role Based Access (RBAC)</li></ol><p>The following Snowflake objects and privileges are created with example names:</p><ol><li>Three ROLEs:</li><li>SALES_DEV_ADMIN_ROLE — the owner of the DATABASE with full privileges</li><li>SALES_DEV_DML_ROLE — has DML privileges on the tables, but no DDL privileges</li><li>SALES_DEV_ANALYST_ROLE — has SELECT and UDF USAGE privileges on VIEWs and UDFs only, including all FUTURE VIEWs and UDFs</li><li>A DATABASE, SALES_DEV_DB.</li><li>Three SCHEMAs are created with the following names. Note that they are <em>not</em> qualified with the DATABASE name:</li><li>PHYSICAL_SCHEMA — contains physical tables</li><li>VIEW_SCHEMA — contains “presentation” VIEWs for each physical table, e.g. mapping physical COLUMN names to desired BI presentation names</li><li>BI_VIEW_SCHEMA — contains more complex VIEWs and UDFs, e.g. JOINs between presentations VIEWs, aggregations, etc.</li><li>Two WAREHOUSEs. Additional warehouses may be need for loading and TASK execution; SERVERLESS TASKs are recommended, but are still in preview at this time.</li><li>SALES_DEV_WH_SMALL — a small warehouse</li><li>SALES_DEV_WH_XSM — an extra small warehouse</li><li>All appropriate USAGE privileges</li></ol><p>Note that the scriptis <em>totally idempotent, with the exception of the CREATE DATABASE statement</em>. This can be very useful when cloning DATABASE and SCHEMA or when re-defining roles and privileges on legacy databases.</p><p>Let’s walk through the script. We start with a header section that defines session variables for all of the objects to be created. The only two variables that are needed are <em>APP_AREA_NAME</em> and <em>DB_TYPE</em>.</p><pre>SET APP_AREA_NAME =&#39;SALES&#39;; SET APP_AREA_NAME =&#39;&#39;; -- set your business area name, e.g; MARKETING SET DB_TYPE = &#39;&#39;; -- set your database type name, e.g. DEV, QA, etc<br>SET DB_NAME= CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_DB&#39;);<br>SET VIEW_SCHEMA_NAME = CONCAT($DB_NAME,&#39;.&#39;,&#39;SFORCE_VIEW_SCHEMA&#39;); SET PHYSICAL_SCHEMA_NAME = CONCAT($DB_NAME,&#39;.&#39;,&#39;SFORCE_PHYSICAL_SCHEMA&#39;);<br>SET BI_VIEW_SCHEMA_NAME = CONCAT($DB_NAME,&#39;.&#39;,&#39;BI_VIEW_SCHEMA&#39;);<br>SET ADMIN_ROLE = CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_ADMIN_ROLE&#39;); SET ANALYST_ROLE = CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_ANALYST_ROLE&#39;);<br>SET DML_ROLE = CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_DML_ROLE&#39;);<br>SET DW_XSM = CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_XSM_WH&#39;);<br>SET DW_SMALL = CONCAT($APP_AREA_NAME,&#39;_&#39;,$DB_TYPE,&#39;_SM_WH&#39;); <br>SET CURRENT_USER = CURRENT_USER();</pre><p>Now that we have all session variables set, the next step is to create all of the roles using SECURITYADMIN and ACCOUNTADMIN role:</p><pre>USE ROLE SECURITYADMIN;<br>-- Grant ADMIN_ROLE, ANALYST_ROLE and DML_ROLE<br>-- to CURRENT_USER<br>CREATE ROLE IF NOT EXISTS IDENTIFIER($ADMIN_ROLE);<br>GRANT ROLE IDENTIFIER($ADMIN_ROLE) TO USER IDENTIFIER($CURRENT_USER);<br>CREATE ROLE IF NOT EXISTS IDENTIFIER($ANALYST_ROLE);<br>GRANT ROLE IDENTIFIER($ANALYST_ROLE) TO USER IDENTIFIER($CURRENT_USER);<br>CREATE ROLE IF NOT EXISTS IDENTIFIER($DML_ROLE);<br>-- GRANT EXECUTE TASK ON ACCOUNT<br>USE ROLE ACCOUNTADMIN;<br>GRANT EXECUTE TASK, EXECUTE MANAGED TASK ON ACCOUNT TO ROLE IDENTIFIER($ADMIN_ROLE);</pre><p>Now create the database using SYSADMIN and change the owner to SALES_DEV_ADMIN_ROLE. This avoids overloading SYSADMIN with too many privileges, which can lead to unintended changes.</p><pre>-- CREATE DATABASE and GRANT OWNERSHIP to ADMIN_ROLE<br>USE ROLE SYSADMIN;<br>CREATE DATABASE IF NOT EXISTS IDENTIFIER($DB_NAME);<br>GRANT OWNERSHIP ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE);</pre><p>Next, we create the WAREHOUSEs and GRANT USAGE to the various roles.</p><pre>--CREATE WAREHOUSEs and GRANT USAGE<br>USE ROLE SYSADMIN;<br>CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($DW_XSM) WITH WAREHOUSE_SIZE = &#39;XSMALL&#39; WAREHOUSE_TYPE = &#39;STANDARD&#39; AUTO_SUSPEND = 600 AUTO_RESUME = TRUE;<br>CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($DW_SMALL) WITH WAREHOUSE_SIZE = &#39;SMALL&#39; WAREHOUSE_TYPE = &#39;STANDARD&#39; AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($ADMIN_ROLE);<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($DML_ROLE);<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($ADMIN_ROLE);<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($DML_ROLE);</pre><p>Next, we create the SCHEMA. Note that these do not have fully qualified names. This avoids the issues of having to fully qualify code; the SCHEMA is dependent on the current database of the session. Queries, VIEWs etc. are portable accross DATABASEs.</p><pre>-- CREATE SCHEMAs<br>USE ROLE IDENTIFIER($ADMIN_ROLE);<br>CREATE SCHEMA IF NOT EXISTS IDENTIFIER($PHYSICAL_SCHEMA_NAME);<br>CREATE SCHEMA IF NOT EXISTS IDENTIFIER($VIEW_SCHEMA_NAME);<br>CREATE SCHEMA IF NOT EXISTS IDENTIFIER($BI_VIEW_SCHEMA_NAME);</pre><p>Next, we grant all the necessary privileges on the DATABASE, SCHEMAs and any objects that might already exist.</p><pre>-- GRANT USAGE ON DATABASE<br>GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($DML_ROLE);<br>GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>-- GRANT USAGE ON View schemas to $ANALYST_ROLE<br>GRANT USAGE ON SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);</pre><pre>-- Set up the DML user to access any existing tables, not required on empty database but good practice <br>GRANT USAGE ON SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($DML_ROLE);<br>GRANT SELECT,INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN  SCHEMA  IDENTIFIER($PHYSICAL_SCHEMA_NAME)TO  ROLE IDENTIFIER($DML_ROLE);</pre><pre>-- Set up Analyst roles for SELECT, UDF Usage and SELECT from STREAMs, not required on empty database, but good practice<br>GRANT SELECT ON ALL VIEWS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT SELECT ON ALL VIEWS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON ALL FUNCTIONS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON ALL FUNCTIONS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT SELECT ON ALL STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);</pre><p>Next, GRANT FUTURE privileges on appropriate objects.</p><pre>USE ROLE SECURITYADMIN;<br>GRANT SELECT ON FUTURE VIEWS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT SELECT ON FUTURE VIEWS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);<br>GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE);<br>GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($DML_ROLE);</pre><p>Finally, we grant the admin role to SYSADMIN and leave the user who ran the script in the ADMIN_ROLE.</p><p>Copyright © 2022, Jeffrey M. Jacobs</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=34b9c935c223" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-dba-101-deploying-standardized-fully-functional-databases-34b9c935c223">Snowflake DBA-101; Deploying Standardized, Fully Functional Databases</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake and Duo Mobile, How to Lose Login Ability and MFA Across Multiple Accounts:ERROR: “USER…]]></title>
            <link>https://medium.com/snowflake/snowflake-and-duo-mobile-how-to-lose-login-ability-and-mfa-across-multiple-accounts-error-user-750c0dacc31e?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/750c0dacc31e</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sat, 08 Jan 2022 19:40:17 GMT</pubDate>
            <atom:updated>2022-05-09T05:55:24.359Z</atom:updated>
            <content:encoded><![CDATA[<h3>Snowflake and Duo Mobile, How to Lose Login Ability and MFA Across Multiple Accounts:ERROR: “USER IS NOT ENROLLED IN DUO SECURITY. CONTACT YOUR LOCAL</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uRJyN9E44NI2ODh_-LnnEw.jpeg" /></figure><p>In working with multiple Snowflake accounts, I found a serious issue that I have not seen addressed elsewhere. NOTE: this issue applies across<strong><em>ALL</em></strong>Snowflake accounts using the same DuoMobile/phone number account, at least in the same region.</p><p>I enabled MFA for multiple Snowflake accounts using the same phone. number, which created a DuoMobile account.</p><p>In one account, I needed Duo Mobile to automatically send me a push, but hit the wrong button [Continue to Login], which resulted in my getting the DuoMobile prompt on subsequent login rather than automatic push. You must click [Save] to save your selected option, which, IMO, should be required before [Continue to Login]:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*5f_CvKMjM4NqY85Jq3iR9Q.jpeg" /></figure><p>The Settings button in the Duo Mobile prompt does not allow changing to automatic push.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*fnrH31GVhTmPQet9dLzVuw.jpeg" /></figure><p>I then went into that account and performed:</p><pre>USE ROLE ACCOUNTADMIN; ALTER USER &lt;username&gt; SET DISABLE_MFA = TRUE;</pre><p>This led to:</p><p><strong>ERROR: “USER IS NOT ENROLLED IN DUO SECURITY. CONTACT YOUR LOCAL SYSTEM ADMINISTRATOR.</strong></p><p>across <strong><em>all of the accounts </em></strong>using the same Duo Mobile account/phone number.</p><p>There may be ways to avoid this issue, but I have not discovered it. Please let me know if there is a better way to avoid this problem.</p><p>Be sure that at least one other user has ACCOUNTADMIN privilege in each account. That person will need to perform the above operation on all affected Snowflake accounts.</p><p>Note that Snowflake is aware of the issue.</p><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/snowflake-duo-mobile-how-lose-login-ability-mfa-across-jeffrey-jacobs"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=750c0dacc31e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-and-duo-mobile-how-to-lose-login-ability-and-mfa-across-multiple-accounts-error-user-750c0dacc31e">Snowflake and Duo Mobile, How to Lose Login Ability and MFA Across Multiple Accounts:ERROR: “USER…</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake and ELVT vs [ELT|ETL] — Case Study Part 2, Real Time Availability for Single Row INSERTs]]></title>
            <link>https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-case-study-part-2-real-time-availability-for-single-row-inserts-623621bc6ac5?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/623621bc6ac5</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sat, 01 Jan 2022 20:01:12 GMT</pubDate>
            <atom:updated>2022-01-07T18:02:48.113Z</atom:updated>
            <content:encoded><![CDATA[<h3>Snowflake and ELVT vs [ELT|ETL] — Case Study Part 2, Real-Time Availability for Single Row INSERTs</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3koaVpalwuPlAbajA0UmaQ.jpeg" /></figure><p>In my previous article, <a href="https://www.linkedin.com/pulse/snowflake-elvt-vs-eltetl-case-study-part-1-data-model-jeffrey-jacobs">Snowflake and ELVT vs [ELT|ETL] — Case Study Part 1, The “No Data Model” Data Architecture</a>, I described challenges and solutions to building a data analytics architecture before development of the application had even begun. In addition to the four requirements below discussed in that article:</p><ol><li>The primary source of data will be a custom Salesforce application, using both standard and custom Salesforce Objects.</li><li>Every change to every Salesforce Object record must be captured.</li><li>The architecture must support the full data life cycle, from development through production and maintenance.</li><li>There will be ongoing changes to the Salesforce Object fields over time.</li></ol><p>There is a fifth requirement:</p><ul><li>Data must be available within 10 seconds of creation/update in Salesforce.</li></ul><p>As a reminder, the data is delivered via Kafka Connector, which is set to load available data into Snowflake every 10 seconds.</p><p>For any given Salesforce object, this effectively becomes single row INSERTs, resulting in single row micro-partitions. This will result in ongoing declining performance.</p><p>The challenge is having the data available and still maintain good performance.</p><p>STREAMs to the rescue!</p><p>Recall that the architecture is INSERT only and that VIEWs are used for all data access. (See &lt;other articles&gt;.</p><p>The solution is both simple and tunable. (It’s not clear if all Salesforce objects require 10-second availability).</p><p>We create both staging tables loaded by Kafka Connector and “optimized” tables, e.g. ACCOUNT_KAFKA_STG, the staging table and ACCOUNT, the “optimized” table.</p><pre>CREATE TABLE <br>    SFORCE_PHYSICAL_SCHEMA.ACCOUNT IF NOT EXISTS <br>    ( <br>        LAST_MODIFIED_DTIME TIMESTAMP_TZ(9), <br>        CREATED_DTIME TIMESTAMP_TZ(9), <br>        RECORD_CONTENT VARIANT <br>    ) <br>    DATA_RETENTION_TIME_IN_DAYS = 90;<br>CREATE TABLE <br>    SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STG IF NOT EXISTS <br>    ( <br>        RECORD_METADATA VARIANT, <br>        RECORD_CONTENT VARIANT <br>    ) <br>    DATA_RETENTION_TIME_IN_DAYS = 30;</pre><p>We create STREAMs on the staging tables, e.g. ACCOUNT_KAFKA_STREAM. The relevant VIEWS perform a UNION ALL between the STREAM and the optimized table.</p><pre>CREATE STREAM <br>IF NOT EXISTS SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM ON TABLE <br>    SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STG APPEND_ONLY = TRUE SHOW_INITIAL_ROWS =FALSE;</pre><p>A TASK is created for each set of Snowflake tables which periodically performs INSERT from the STREAM into the optimized table:</p><pre>CREATE<br>OR <br>REPLACE TASK <br>SFORCE_PHYSICAL_SCHEMA.INSERT_INTO_ACCOUNT_TASK <br>SCHEDULE = &#39;1440 MINUTE&#39;<br>ALLOW_OVERLAPPING_EXECUTION FALSE WAREHOUSE = <br>SFORCE_TASK_USAGE_WH USER_TASK_TIMEOUT_MS 300000<br>WHEN SYSTEM$STREAM_HAS_DATA(&#39;SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM&#39;) AS<br>    INSERT INTO <br>        SFORCE_PHYSICAL_SCHEMA.ACCOUNT<br>        (   SELECT<br>                convert_timezone( &#39;America/Los_Angeles&#39;, TO_TIMESTAMP_TZ(record_content <br>                :Target_Payload__c.LastModifiedDate::STRING, &#39;YYYY-MM-DD&quot;T&quot; HH24:MI:SS.FF TZHTZM&#39;<br>                )) AS LAST_MODIFIED_DTIME,<br>                convert_timezone( &#39;America/Los_Angeles&#39;, TO_TIMESTAMP_TZ(record_content <br>                :Target_Payload__c.CreatedDate::STRING, &#39;YYYY-MM-DD&quot;T&quot; HH24:MI:SS.FF TZHTZM&#39; )) AS <br>                CREATED_DTIME<br>            FROM <br>                SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM <br>            ORDER BY <br>                LAST_MODIFIED_DTIME <br>        );</pre><p>When a TASK is created, it is SUSPENDed; be sure to start/RESUME it:</p><pre><strong>ALTER</strong> TASK SFORCE_PHYSICAL_SCHEMA.INSERT_INTO_ACCOUNT_TASK RESUME;</pre><p>The default schedule for the TASKs is 24 hours, resulting in a full day’s data in a micro-partition. Note that both the Kafka Connector settings and the TASK scheduling are tunable if necessary in the future.</p><p>This provides both real-time availability as well as more efficient use of Snowflake micro-partitions.</p><p>A second task periodically removes old micro-partitions from the staging tables.</p><p>This pattern provides a consistent solution for the project’s availability requirement in a flexible manner.</p><p>Copyright © 2022, Jeffrey Jacobs &amp; Associate, LLC</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=623621bc6ac5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-case-study-part-2-real-time-availability-for-single-row-inserts-623621bc6ac5">Snowflake and ELVT vs [ELT|ETL] — Case Study Part 2, Real Time Availability for Single Row INSERTs</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake vs Databricks: TPCS-DS Benchmark Wars — Who Cares?]]></title>
            <link>https://jmjacobs.medium.com/snowflake-vs-databricks-tpcs-ds-benchmark-wars-who-cares-63de9763c90b?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/63de9763c90b</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Mon, 06 Dec 2021 19:06:14 GMT</pubDate>
            <atom:updated>2021-12-06T19:06:14.027Z</atom:updated>
            <content:encoded><![CDATA[<h3>Snowflake vs Databricks: TPCS-DS Benchmark Wars — Who Cares?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*MZlLEfzUyGrMwBHpnwrrUA.jpeg" /></figure><p>This article discusses the relevance of the recent TPC-DS “results” to potential customers.</p><p>Let’s start with disclaimers:</p><p>1. Unlike my past articles, this article contains opinions and speculations in addition to technical commentary. These opinions and speculations are strictly my own and do not represent the views of any of my clients or any database vendors.</p><p>2. I make my living as a Consulting Data Architect specializing in Snowflake. I’m also a huge fan going back to its stealth days. This article was written without any communication or input from Snowflake.</p><p>3. Nothing in this article should be construed as a commentary on Databricks technology. It is primarily about the use of TPC-DS as a marketing vehicle.</p><h3>A Little History</h3><p>RDBMs vendors using TPC benchmarks goes back decades. It started as benchmarks for transaction processing around 1990. At its peak, 33 vendors were publishing TPC benchmarks; that was a looong time ago. (See <a href="http://tpc.org/information/about/history5.asp">TPC History</a>).</p><p>This was frankly marketing. In those days, the similarities between RDBMS offerings were much stronger; Snowflake and Databricks are completely different technologies.</p><p>Those of us in the profession at the time could only LOL; we knew how easy it was to optimize the benchmarks. The <a href="https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html">Dewitt Clause</a> was included in licensing agreement to suppress vendors publishing benchmarks on competitors’ products. We shall see why this was desirable. Despite the DeWitt clause the so called TPC wars were filled with accusations about misleading pronouncements.</p><h3>Bold Statements</h3><p>A customer purchasing an RDBMS based solely or even most heavily weighted on performance benchmarks has far more serious issues facing them. This isn’t the 1990s, when RDBMs ran in fairly similar hardware and largely offered the same features.</p><p>Today’s products offer vastly different features sets and typically run on very different data and compute sources. Performance is only one of many factors to consider.</p><p>Even more important, the TPC-DS Power test, as noted in <a href="http://tpc.org/TPC_Documents_Current_Versions/pdf/TPC-DS_v3.2.0.pdf"><em>TPC-DS 3.2.0 Specification</em></a>, paragraph 7.1.10, <em>A Power Test consists of exactly one query session running a single query stream .</em> Basing performance decisions on this core benchmark ignores the critical issues of concurrency and scalability, not to mention functional aspects. The Power Test is the benchmark in dispute.</p><p>There are as many vendors striving to achieve a new TPC-DS World 100,000 GB Record as competitors for most Guinness Book World Record. According to the TPC website, the number of competitors for this award is <a href="http://tpc.org/tpcds/results/tpcds_results5.asp?version=3">two</a> (2): Alibaba and Databricks.</p><h3>Conflicting Statements</h3><p>Databricks opened with <a href="https://databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html"><em>Databricks Sets Official Data Warehousing Performance Record</em></a> claiming superior performance and cost compared to Snowflake.</p><p>Snowflake responded with their own benchmark, <a href="https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/"><em>Industry Benchmarks and Competing with Integrity</em></a>, with their own numbers on price and performance.</p><p>Databricks responded with <a href="https://databricks.com/blog/2021/11/15/snowflake-claims-similar-price-performance-to-databricks-but-not-so-fast.html"><em>Snowflake Claims Similar Price/Performance to Databricks, but Not So Fast!</em></a> In which they ran the TPC-DS Power Test provided in Snowflake’s <em>Tutorial 4: TPC-DS 100 TB Complete Query Test.</em></p><h3>Technical Commentary</h3><p>In their response, Databricks “ <em>noticed that the Snowflake pre-baked TPC-DS dataset had been recreated two days after our benchmark results were announced</em> “.</p><p>I had a twofold reaction:</p><p>1. If you’re going to publish a benchmark, you want to show your best technology and results.</p><p>2. Technically, it was about time!</p><p>Snowflake Sample data has been available since day one. I noticed when writing my article, <a href="https://www.linkedin.com/pulse/snowflake-micro-partition-vs-legacy-macro-partition-pruning-jacobs/?lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BRKcahmaNS7%2B9W19XSWidXA%3D%3D&amp;licu=urn%3Ali%3Acontrol%3Ad_flagship3_profile_view_base-featured_item_detail_view"><em>Snowflake Micro-partition vs Legacy Macro-partition Pruning</em></a>, that the TPC-DS data was poorly and not realistically clustered. In the real world, data would typically be loaded on a regular basis, e.g., daily, and <em>tend to be naturally clustered.</em> The intent of that article was to demonstrate micro-partitioning, and while the performance of the unclustered data was fine, it didn’t clearly demonstrate the point of the article.</p><p>A second critical observation is that earlier this year, Snowflake completely changed the underlying data storage structure of its product. <em>Data stored prior to this change remained in the old format!</em> Recreating the TPC-DS data not only improved performance, it also resulted in more realistic data!</p><p>Databricks claims to have reloaded the data themselves and come up with different results than Snowflake. There are several potential issues with how this was done. In particular:</p><p>1. <a href="https://docs.snowflake.com/en/user-guide/tables-auto-reclustering.html"><em>“After you define a clustering key for a table, the rows are not necessarily updated immediately”</em></a><em>.</em></p><p><em>2. “</em><a href="https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html#what-is-a-clustering-key"><strong><em>Clustering Key… is useful for very large tables where the ordering was not ideal (at the time the data was inserted/loaded) or extensive DML has caused the table’s natural clustering to degrade</em></strong></a><em>”</em></p><p>Databricks’ results are only relevant if the clustering depth at the time its queries were executed match those of Snowflake’s. Simply adding the cluster key columns is not sufficient.</p><p>The clustering efficiency can be determined by <a href="https://docs.snowflake.com/en/sql-reference/functions/system_clustering_depth.html"><em>SYSTEM$CLUSTERING_DEPTH</em></a> function.</p><p>Snowflake’s table is clustered on columns <em>ss_sold_date_sk </em>and<em> ss_item_sk</em>. <em>ss_sold_date_sk</em> is the primary clustering column. The clustering depth on this column is effectively perfect. Cluster key on both columns is, as would be expected, not as good.</p><p>All large tables I examined were nearly perfectly clustered on the relevant data column.</p><p>Unless these numbers match, Databricks’ results are not comparable!</p><p>Simply running Powers queries with no further elaboration on the setup isn’t helpful.</p><h3>“Final” Battle?</h3><p>Shortly after the above exchange, Snowflake announced <a href="https://www.snowflake.com/blog/more-throughput-and-faster-execution-for-interactive-use-cases-now-in-public-preview/">significant performance improvements</a>, which probably make the above exchanges obsolete. No surprise, Snowflake uses TPC-DS for benchmarking their own product and provides very detailed setup information.</p><p>I sincerely hope this is the end of the TPC-DS “bench-marketing” wars. Nobody should make purchasing decisions based on these types of exchanges.</p><p>Copyright ©2021, Jeffrey Jacobs &amp; Associates, LLC</p><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/snowflake-vs-databricks-tpcs-ds-benchmark-wars-who-cares-jacobs"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=63de9763c90b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake and ELVT vs [ELT|ETL] — Case Study Part 1, The “No Data Model” Data Architecture]]></title>
            <link>https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-case-study-part-1-the-no-data-model-data-architecture-4e3346956738?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/4e3346956738</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Mon, 15 Nov 2021 03:18:28 GMT</pubDate>
            <atom:updated>2021-12-25T20:16:54.832Z</atom:updated>
            <content:encoded><![CDATA[<h3>Snowflake and ELVT vs [ELT|ETL] — Case Study Part 1, The “No Data Model” Data Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aImHdWzkdIr7t2N5aUAFXA.jpeg" /></figure><p>This article is a case study of real-world implementation. The client and purpose of the application are not relevant to the architecture.</p><p>My previous articles on Snowflake and ELVT], <a href="https://www.linkedin.com/pulse/snowflake-elvt-vs-elt-etl-jeffrey-jacobs/">Snowflake and ELVT vs [ELT, ETL], Part 1</a> and <a href="https://www.linkedin.com/pulse/snowflake-elvt-data-cake-model-bi-vendor-independence-jeffrey-jacobs/">Snowflake and ELVT vs [ELT,ETL], Part 2, The ELVT Reference Architecture</a> have been primarily theoretical, only alluding to implementations using this technique.</p><p>The client presented a unique challenge. Create an architecture before the application and its data model are even defined or implemented!</p><p>The only known “requirements” are:</p><ol><li>The primary source of data will be a custom Salesforce application, using both standard and custom Salesforce Objects.</li><li>Every change to every Salesforce Object record must be captured.</li><li>The architecture must support the full data life cycle, from development through production and maintenance.</li><li>There will be ongoing changes to the Salesforce Object fields over time.</li></ol><p>At this point, the reader might be saying to themselves, as I did initially, “No big deal, use a data integration product such as Fivetran”. This will handle both data and schema changes.</p><p>Except there is the 5th requirement; “the solution must be FedDRAMP Mod” certified! None of the currently available data integration offerings meet this requirement. As a result, Kafka and JSON form the data pipeline from Salesforce to Snowflake. The pipeline is repurposed from a prior implementation for Salesforce to PostgreSQL replication.</p><p>How to cope with all the above?</p><p>A Snowflake-based Salesforce “Object” DDL Generator! Each Salesforce Object results in three VIEWs, two TABLEs, one STREAM, and one TASK in Snowflake for capturing the Salesforce data for reporting and analytics.</p><p>The Object Generator consists of metadata TABLEs and UDFs for generating DDL physical data TABLEs, STREAMs, and VIEWs corresponding to Salesforce Objects.</p><h3>Meta-data Tables</h3><p>A TABLE, SFORCE_META_DATA, consisting of meta-data extracted from Salesforce with the following columns:</p><ol><li><em>SFORCE_OBJECT — the name used for creating the various Snowflake objects e.g., ACCOUNT for standard object or CUSTOM for a custom object, e.g., CUSTOM__c.</em></li><li><em>API_NAME — the Salesforce field API Name — used to extract the appropriate JSON field for the VIEW column name</em></li><li><em>FIELD_LABEL — the Salesforce field label — this becomes the VIEW column name.</em></li><li><em>DATA_TYPE — the Salesforce data type.</em></li><li><em>Security Classification — used for data masking</em></li></ol><p>A TABLE, SFORCE_JSON_CASTING, for casting the Salesforce data type to Snowflake with the following columns:</p><ol><li><em>SFORCE_DATA_TYPE — the Salesforce data type</em></li><li><em>CAST_TO — the desired Snowflake data type</em></li><li><em>IGNORE_FLAG — ignore columns with this data type</em></li></ol><p>A Tdimension TABLE, CALENDAR_DIM_DATE, with a DATE column</p><h3>Physical Model</h3><p>Every Salesforce Object, e.g., ACCOUNT and CONTACT, will have the following corresponding physical Snowflake objects:</p><p>A staging TABLE for Kafka payload, e.g., ACCOUNT_KAFKA_STG, with the two standard VARIANT/JSON columns from the Kafka Connector:</p><ol><li><em>RECORD_META_DATA</em></li><li><em>RECORD_CONTENT</em></li></ol><p>An “append-only” STREAM, e.g., ACCOUNT_KAFKA_STREAM on the staging table, e.g., ACCOUNT_KAFKA_STG</p><ol><li><em>History TABLE, e.g., ACCOUNT, holding all data from Salesforce with the following columns:</em></li><li><em>LAST_MODIFIED_DTIME — the last timestamp the source Salesforce record was modified</em></li><li><em>CREATED_DTIME — the time the record was created in Salesforce</em></li><li><em>RECORD_CONTENT — the VARIANT column from the staging table STREAM</em></li></ol><p>A TASK, e.g., ACCOUNT_INSERT_INTO_TASK — performs the ELT from the STREAM, removing unwanted JSON objects and filling the DTIME columns.</p><p>UDFs are created to generate the DDL for each of the physical objects. Each of the UDFs takes the desired object name, e.g., ‘ACCOUNT’, and returns the DDL for creating the appropriate object.</p><p>The “standardized” UDFs:</p><ul><li>UDF_GEN_SFORCE_OBJECT_TABLE(‘ACCOUNT’) produces:</li></ul><pre>CREATE TABLE <br>    SFORCE_PHYSICAL_SCHEMA.ACCOUNT IF NOT EXISTS <br>    (LAST_MODIFIED_DTIME TIMESTAMP_TZ(9), <br>     CREATED_DTIME TIMESTAMP_TZ(9), <br>     RECORD_CONTENT VARIANT) <br>    DATA_RETENTION_TIME_IN_DAYS = 90;</pre><ul><li>UDF_GEN_SFORCE_KAFKA_STG_TABLE(‘ACCOUNT’) produces:</li></ul><pre>CREATE TABLE <br>    SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STG IF NOT EXISTS <br>    (RECORD_METADATA VARIANT, <br>        RECORD_CONTENT VARIANT) <br>    DATA_RETENTION_TIME_IN_DAYS = 30;</pre><ul><li>UDF_GEN_SFORCE_KAFKA_STREAM(‘ACCOUNT’) produces:</li></ul><pre>CREATE STREAM IF NOT EXISTS <br>SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM ON TABLE<br>  SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STG <br>  APPEND_ONLY = TRUE SHOW_INITIAL_ROWS =false;</pre><ul><li>UDF_GEN_INSERT_INTO_TASK (‘ACCOUNT’) produces:</li></ul><pre>CREATE OR<br>REPLACE TASK SFORCE_PHYSICAL_SCHEMA.INSERT_INTO_ACCOUNT_TASK S<br>SCHEDULE = &#39;1440 MINUTE&#39;<br>ALLOW_OVERLAPPING_EXECUTION  = FALSE WAREHOUSE =<br>SFORCE_TASK_USAGE_WH USER_TASK_TIMEOUT_MS      = 300000<br>WHEN SYSTEM$STREAM_HAS_DATA(&#39;SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM&#39;) <br>AS<br>INSERT INTO SFORCE_PHYSICAL_SCHEMA.ACCOUNT<br>  (SELECT  <br>convert_timezone(&#39;America/Los_Angeles&#39;,record_content:LastModifiedDate) AS<br>LAST_MODIFIED_DTIME,<br>convert_timezone(&#39;America/Los_Angeles&#39;,record_content:CreatedDate::timestamp) AS<br>CREATED_DTIME<br>FROM<br>SFORCE_PHYSICAL_SCHEMA.ACCOUNT_KAFKA_STREAM<br>ORDER BY<br>LAST_MODIFIED_DTIME);</pre><p>There are corresponding overloaded UDFs that have additional parameters to govern the options used in the DDL such as schemas and CREATE OR REPLACE/IF NOT EXISTS options. The “standardized” UDFs call these functions with standard settings.</p><p>This means that all generated objects have the same structure; only the names differ. The deployment schema is SFORCE_PHYSICAL_SCHEMA. Changes to the Salesforce Object do not require DDL ALTERing any physical objects.</p><p>This approach facilitates the development and deployment process. Eventually, an automated pipeline will create the physical objects when new Salesforce Objects are added. Until then, we can substantially automate the process by loading the SFORCE_META_DATA table from a .csv file.</p><p>This allows us to create simple SQL to produce scripts, e.g.</p><pre>SELECT<br>UDF_GEN_SFORCE_OBJECT_TABLE(‘ACCOUNT’)<br>UNION ALL <br>SELECT UDF_GEN_SFORCE_KAFKA_STG(‘ACCOUNT’)<br>UNION ALL<br>SELECT UDF_GEN_SFORCE_KAFKA_STREAM(‘ACCOUNT’);</pre><p>We combine UDFs calls into a single UDF creating DDL to deploy all physical objects:</p><pre>UDF_DEPLOY_SFORCE_PHYSICAL(object_name, VARCHAR)</pre><p>We can then generate a script for deploying either the entire set of Salesforce Objects or a subset with a simple SQL statement, e.g.:</p><pre>SELECT <br>    UDF_DEPLOY_SFORCE_PHYSICAL(SFORCE_OBJECT)<br>FROM<br>    (   SELECT <br>            DISTINCT SFORCE_OBJECT<br>        FROM <br>            SFORCE_META_DATA);</pre><h3>Presentation VIEW Model</h3><p>One of the “best practices” with JSON is to use VIEWs for presentation. JSON fields are shredded into relational columns only as needed, typically for performance reasons. A common use case is converting a JSON UTC column to the desired time zone. In this application, the LAST_MODIFIED_DTIME and CREATED_DTIME are particularly important and need to be in Pacific Time.</p><p>As the number and structure of the Salesforce Objects are both unknown and certain to be subject to frequent changes, a simple Snowflake VIEW generator is the key part of this architecture.</p><p>The application’s requirement is to keep a history of every change to every record in every Salesforce Object. This presents challenges common to a temporal database. In this application, most metrics are counts and date ranges instead of numeric values, but the same techniques are applicable.</p><p>As the rate of change differs between Salesforce Objects, there is no reliable join between objects for historical data. A “normalization” technique is needed to enable consistent joins between Snowflake objects.</p><p>The solution is a three-layer stack of VIEWs:</p><ol><li>The “historical” VIEW. This VIEW presents the data for every row in the TABLE. These VIEWs are referred to and named with “_HIST” as the suffix, e.g., VW_ACCOUNT_HIST.</li><li>The “as of date” VIEW. This VIEW presents a daily snapshot of the values of each record at the end of every calendar DATE, e.g.,</li></ol><pre>WHERE MAX(LAST_MODIFIED_DTIME) &lt;= DIM_CALENDAR_DATE.DATE</pre><p>The suffix is “_AS_OF_DATE”, e.g., VW_ACCOUNT_AS_OF_DATE. This allows meaningful JOINs between objects based on the appropriate IDs and the AS_OF_DATE column, e.g.,</p><pre>JOIN ON  VW_CONTACT_AS_OF_DATE.ACCOUNT_ID = VW_ACCOUNT_AS_OF_DATE.ACCOUNT_ID<br>AND<br>VW_CONTACT_AS_OF_DATE.AS_OF_DATE = VW_ACCOUNT_AS_OF_DATE.AS_OF_DATE</pre><pre>PERFORMANCE NOTE: The above is the &quot;logical&quot; join condition.  Due to ACCOUNT_ID being an 18 character string, Snowflake (and most DBs) perform very poorly joining on long characters string. The actual joins are use HASH(AS_OF_DATE, &lt;name&gt;_ID), for the join keys.</pre><p>3. The “current” record status, with the suffice “_CURR” This is simply:</p><pre>SELECT… FROM… VW_object_AS_OF_DATE WHERE AS_OF_DATE = CURRENT_DATE()</pre><p>Joins only require appropriate ID columns, as seen below.</p><p>The “as of date” and “current” VIEWs are the primary VIEWs for the anticipated analytics and KPIs.</p><h3>Generating VIEWs</h3><p>Let’s discuss the key UDFs used to generate the VIEWs.</p><p>UDF_GEN_VW_HIST(‘object_name’) generates the first level of VIEWs DDL, the “history” _HIST VIEW. This is the “presentation”/SOPR VIEW as described in <a href="https://www.linkedin.com/pulse/snowflake-elvt-data-cake-model-bi-vendor-independence-jeffrey-jacobs/">Snowflake and ELVT vs [ELT,ETL], Part 2, The ELVT Reference Architecture.</a></p><p>UDF_GEN_VW_HIST uses the SFORCE_META_DATA and SFORCE_JSON_CASTING tables to generate the CREATE VIEW DDL. It maps each JSON API field to a VIEW column based on the field label, casting each field according to the SFORCE_JSON_CASTING mapping.</p><p>UDF_GEN_VW_HIST is composed of calls to several functions. The key child function is UDF_GEN_TARGET_COLUMNS. This function creates the columns in the VIEW for both standard fields and custom fields. It uses the FIELD_LABEL as the VIEW’s column name in mixed case, except for the ID field and fields with data types of REFERENCE and LOOKUP; for these, the resulting column name is upper, snake_case. The ID field will be named as &lt;object_name&gt;_ID. A partial example for VW_CONTACT_HIST:</p><pre>RECORD_CONTENT:Target_Payload__c.AccountId::STRING AS &quot;ACCOUNT_ID&quot; , <br>RECORD_CONTENT:Target_Payload__c.Annual_Income__c::STRING AS &quot;Annual Income&quot; , <br>RECORD_CONTENT:Target_Payload__c.Birthdate::DATE   AS &quot;Birthdate&quot; , <br>RECORD_CONTENT:Target_Payload__c.Id::STRING	   AS &quot;CONTACT_ID&quot; , <br>RECORD_CONTENT:Target_Payload__c.CreatedById::STRING 	AS &quot;CREATED_BY_ID&quot; , <br>RECORD_CONTENT:Target_Payload__c.FirstName::STRING 	AS &quot;First Name&quot;</pre><p>UDF_GEN_VW_AS_OF_DATE generates the second level VIEW DDL, joining the _HIST VIEW with DIM_CALENDAR_DATE as noted above.</p><p>UDF_GEN_VW_CURR generates the third level VIEW’s DDL. It is</p><p>SELECT &lt;columns&gt; FROM VW_object_AS_OF_DATE WHERE AS_OF_DATE = CURRENT_DATE</p><p>All the generated VIEWs use CREATE OR REPLACE syntax. This allows simple updates to the VIEWs based on changes to the SFORCE_META_DATA table. NOTE: due to Snowflake’s compiling of VIEWs, all three VIEWs need to be recreated in the following order when the meta-data for the Salesforce Object changes:</p><ol><li>_HIST</li><li>_AS_OF_DATE</li><li>_CURR</li></ol><p>as both _AS_OF_DATE and _CURR use SELECT &lt;columns&gt; from lower-level VIEW in their DDL, which is resolved at CREATE VIEW execution with text substitution.</p><p>UDF_DEPLOY_SFORCE_VIEWS(‘object_name’) generates a DDL script for creating the three VIEWs.</p><p>The same simple SQL technique as noted above for the physical objects can be used to generate a script to create or refresh multiple objects.</p><h3>Query Examples</h3><p>Let’s examine the results for a Salesforce Account Object selected from the different VIEW types.</p><pre>SELECT <br>    &quot;Account Name&quot;,<br>    ACCOUNT_ID,<br>    last_modified_dtime,<br>    TO_DATE(LAST_MODIFIED_DTIME)<br>FROM<br>    VW_ACCOUNT_HIST<br>WHERE<br>    ACCOUNT_ID = &#39;0013R000003pNu8QAE&#39;<br>ORDER BY<br>    to_Date(last_modified_dtime);</pre><p>returns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/0*-Re_dm0kWUsumOKw" /></figure><p>Note that the account had several updates on two different dates, 10/22 and 10/29.</p><p>Let’s see the data for the daily snapshot from VW_ACCOUNT_AS_OF_DATE:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/807/0*vP2-qceEPl2_ODaF" /></figure><p>Note that the results remain the same from the last update on 10/22 through 10/28 and change on 10/29.</p><p>Let’s look at the current status, VW_ACCOUNT_CURR:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/810/0*Q2u2mr4MwcV7Gx6n" /></figure><p>Note that the status has not changed since 10/29.</p><p>Finally, let’s see the current values for this Account and its CONTACTs:</p><pre>SELECT <br>    &quot;Account Name&quot;, <br>    &quot;First Name&quot;, <br>    &quot;Birthdate&quot;, <br>    &quot;Driver&#39;s License State&quot;<br>FROM <br>    VW_ACCOUNT_CURR ACCT<br>JOIN <br>    VW_CONTACT_CURR CNTC<br>ON <br>    ACCT.ACCOUNT_ID = CNTC.ACCOUNT_ID<br>WHERE <br>    ACCT.ACCOUNT_ID = &#39;0013R000003pNu8QAE&#39;;</pre><p>produces:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/656/0*aoeoyHM9wWKORhwo" /></figure><h3>Summary</h3><p>The architecture described in this article has numerous advantages:</p><ol><li>Every Salesforce Object in the application has/will have a corresponding set of physical objects</li><li>All the physical objects have the identical structure</li><li>The physical objects are all generated from meta-data</li><li>The default standard for all generated physical objects is idempotent, using appropriate IF NOT EXISTS or OR REPLACE in the CREATE statements</li><li>The generated DDL is idempotent</li><li>Only the _HIST VIEWs DDL needs to be generated for new or changed Salesforce Objects</li><li>As noted for VIEWs, all three DDL definitions need to be executed. Other VIEWs may also need to be recreated</li><li>ETL/ELT is minimized and standardized</li><li>The Kafka connector is used for ELT from Salesforce</li><li>Tasks are used to copy the data from the Kafka stage TABLE to the main table</li><li>Monitoring the ETL/ELT processes is via queries to the SNOWFLAKE.ACCOUNT_USAGE schema</li><li>Using JSON/VARIANT as the primary persistence store allows the Salesforce Objects to be changed before exposing them in the VIEWs; the new data will be present when finally exposed in the VIEWs</li><li>There is no need for traditional schema maintenance, as the architecture is true “schema on read”.</li></ol><h3>Epilogue</h3><p>The architecture and examples in this article were developed months and tested months before the first Salesforce Objects were implemented. While the application is still in its initial stages, 22 standard and custom Salesforce Objects have been implemented producing 161Snowflake objects. No changes have been required to the architecture.</p><p>Copyright ©2021, Jeffrey Jacobs &amp; Associates, LLC</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4e3346956738" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-case-study-part-1-the-no-data-model-data-architecture-4e3346956738">Snowflake and ELVT vs [ELT|ETL] — Case Study Part 1, The “No Data Model” Data Architecture</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake and ELVT vs [ELT,ETL], Part 2, The ELVT Reference Architecture]]></title>
            <link>https://medium.com/snowflake/snowflake-elvt-the-data-cake-model-and-bi-vendor-independence-29db24d50401?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/29db24d50401</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sun, 16 May 2021 22:01:03 GMT</pubDate>
            <atom:updated>2022-05-30T19:20:20.211Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*9lDvR1xyFfYikMy-blEE_w.jpeg" /></figure><p>This is the promised follow up to my last article, <a href="https://jeffreyjacobs.wordpress.com/2021/04/15/snowflake-and-elvt-vs-elt-etl/"><em>Snowflake, and ELVT vs. [ETL, ELT]</em></a>, discussing the advantages of Extract, Load and Virtual Transform (ELVT) using VIEWs instead of physical [ETL, ELT].</p><p>This article expands ELVT into a layered data model I call the Data Cake model. The ELVT style of using VIEWs is not new; Snowflake’s unique architecture and performance enable a more comprehensive and robust use of this technique.</p><p>The Data Cake model’s purpose is “freeing the data” from BI vendor lock-in. providing all the EVLT advantages previously described in <a href="https://jeffreyjacobs.wordpress.com/2021/04/15/snowflake-and-elvt-vs-elt-etl/"><em>Snowflake and ELVT vs. [ETL, ELT]</em></a>.</p><p>Every BI tool has three basic functions:</p><p>1. Presentation naming, i.e., business user terms instead of often cryptic column names</p><p>2. Business logic, ranging from currency conversion to aggregations</p><p>3. Querying data from the database</p><p>Querying data and the different BI tool’s internal processing of the data varies tremendously.</p><p>Development in a BI tool requires significant investment resulting in vendor lock-in. Developing a QLIK application with scripts is significantly different than developing in Tableau, both of which are different from Looker, ad infinitum. This investment is not portable. Migrating from one vendor tool to another can be complex and time-consuming, especially if you have a lot of business logic locked up in the tool.</p><h3>Benefits of this approach</h3><p>The Data Cake model removes much of this investment from the BI tools and implements them in the database. The net result is an easy-to-use, consistent, reusable, governable set of Snowflake objects. The advantages are numerous:</p><p>1. “Freeing the data” from individual BI tools, preserving the original investment if converting from existing BI implementations</p><p>2. Consistent naming conventions</p><p>3. Consistent business logic</p><p>4. Enhanced “single source of truth.”</p><p>5. Modular construction enabling rapid development and testing; analysts concentrate on analysis and visualizations instead of renaming columns and implementing common business logic</p><p>6. Reduced copy and paste operations (the resulting maintenance issues and errors!)</p><p>7. Analysts’ queries are significantly simpler, with consistent results across different BI tools</p><p>8. Independence from the underlying physical model</p><p>9. Avoiding denormalization in the physical model</p><p>10.Simple replacement of Data Cake’s VIEWs with physical tables when needed for performance improvement</p><p>11.Improved database performance over BI tools’ generated SQL, which is typically generic, lowest common denominator code with no means of tuning</p><p>12.Improved use of Snowflake’s caching capabilities</p><p>13.Flexibility. Analysts can choose the appropriate VIEWs as needed, with common joins already available.</p><p>14.Multiple points for data masking, row-level access, access control (RBAC), <a href="https://docs.snowflake.com/en/user-guide/views-introduction.html#secure-views"><em>SECURE VIEW</em></a> creation, and flexible sharing</p><p>15.Better source of metadata for data catalog and lineage tools</p><p>A key assumption is that the physical tables are “clean.” Data Cake layers should not cleanse or correct data.</p><h3>Data Cake Layers</h3><p>Let us describe the layers in the model, from lowest to higher. We will use a simple naming convention to illustrate and discuss the various levels; unlike Datavault, the naming convention is not a key part of the model. Although our discussion focuses on VIEWs, the naming convention is also applicable to describing physical tables and materialized VIEWs. This allows the architect greater flexibility during initial creation and subsequent performance enhancements.</p><p>The names, from the lowest layer to highest, are:</p><p>1. SOPR — Single Object Presentation</p><p>2. SOBL — Single Object Business Logic</p><p>3. MOS — Multiple Object Single</p><p>4. MOM — Multiple Object Multiple</p><p>A SOPR VIEW references a single database object, usually a table. The SOPR VIEW has only the underlying object’s column names and their common business aliases, forming a simple presentation layer. These aliases are ideally from a glossary of common business terms or similar sources. When converting from existing BI applications, the presentation names should be the final names as used in the original app to ensure compatibility.</p><p>Typically, there will be only one SOPR VIEW per underlying object. However, columns in a SOPR VIEW may have multiple presentation aliases for the same physical column. SOPR VIEWs may include column aliases converting lower case and mixed case physical column names to upper case to facilitate coding ease as well as the true physical column names. Exposing semi-structured data as structured columns would also be performed in the SOPR VIEW.</p><p>A SOPR VIEW provides the base layer for all higher layers. SOPR provides the first opportunity to implement security, primarily data masking and row access; a SECURE VIEW is probably not yet appropriate.</p><p>Single Object Business Logic (SOBL) VIEWs are the next layer. The SOBL VIEW is based on a single SOPR VIEW and adds business logic based on row-level data, e.g., currency conversion or calculating elapsed time between dates. There may be multiple SOBLs based on a given SOPR. SQL filtering is considered a form of business logic for specialization and subtyping.</p><p>The SOBL provides a base for the next higher layer of VIEWs, potentially starting a branching effect. A SOBL is, in my opinion, an excellent place to add data masking, row-level access, and SECURE VIEW. A recommended naming convention would be to add _DM (for data masking), _RA (for row access) following SOBL. For SECURE VIEWs, I recommend SEC_SOBL as the prefix.</p><p>Multiple Object Single (MOS) VIEWs are the next layer. At this level, SOBLs are joined, and additional business logic is added for the joined data. While the SOBLs are fundamental layers, MOS is a classification for clarity. MOS is an ideal layer in which to build a dimensional model and combine hubs and satellites from a data vault.</p><p>Multiple Object Multiple (MOM) VIEWs are the next higher layer. This is where we combine SOBLs, MOSs, and other MOMs. More complex business logic, security, etc., are highly appropriate here as well.</p><p>These layers are good practice but are not hard and fast rules, which will be discussed further.</p><h3>Recommended Practices</h3><p>Guidelines for JOINs between Data Cake VIEW layers:</p><p>1. Avoid JOINs to physical tables</p><p>2. SOPR VIEWs should not be used in JOINs; to retain consistency for future enhancement SOBLs should be considered even if no business logic is initially added</p><p>3. SOBL VIEWs should not contain any JOINs</p><p>4. MOS VIEWs may consist of JOINs with other MOSes and SOBL VIEWs</p><p>5. Multiple Object Multiple VIEWs may join with other MOMs, MOSes and SOBL VIEWs</p><p>6. Aggregate VIEWs may consist of JOINs as needed</p><p>Let us examine a sample model based on order history.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/918/1*tCv8LqOlR5fhMbbB-SwdtA.jpeg" /></figure><ul><li>Six physical tables, white filled, with cryptic names; VIEWS are gray filled.</li><li>SOPRs provide the business-friendly aliases for non-key columns</li><li>The primary key columns are included but may be renamed, e.g. PROD_ID to PRODUCT_ID and/or converted to upper case</li><li>SOBL_MSK_CUSTOMER hashes the customer’s email address</li><li>SOBL_SALE_PERSON renames the SOPR “First Name” and “Last Name” columns indicating they are sales personnel with <em>WHERE “Job Title” = ‘Sales’</em></li><li>SOBL_RA_ORDER applies row level access policy</li><li>SOBL_LINE_ITEM calculates the line items total sale</li><li>SOBL_DIM_CALENDAR calculates the day of the week, which was omitted from the DIM_CAL table</li><li>MOS_ORDER joins the dimensions and the order header</li><li>MOS_LINE_ITEM joins the dimension and the line items</li><li>MOM_ORDER_HISTORY performs a snowflake join</li></ul><h3>What about Business Logic?</h3><p>Let us address the issue of business logic. There are two ways to capture business logic in a column definition:</p><ol><li>As an expression, e.g., <em>“Line Item Total” AS “Units Sold” * “Unit Price”</em></li><li>User Defined Functions (<a href="https://docs.snowflake.com/en/sql-reference/user-defined-functions.html"><em>UDF</em></a><a href="https://d.docs.live.net/925f632c6ca5525d/Documents/Blogs/Data%20Cake%200515.docx#_msocom_2"><em>[KG2]</em></a>)</li></ol><p>Common business logic is best captured in UDFs, providing reusable business components. One of the great features of Snowflake’s UDFs is overloading.</p><p>Overloading proved useful when converting legacy QLIK scripts calculating elapsed time with differing physical data types. In some cases, the underlying physical columns were text dates/timestamps; in other cases the physical columns were true dates/timestamps. The UDF_DURATION(END_DATE, START_DATE) has multiple definitions depending on the datatypes passed.</p><pre>CREATE FUNCTION&quot;UDF_DATES_HOURS_DAYS_RANGE&quot;(END_DATE TIMESTAMP_NTZ, START_DATE TIMESTAMP_NTZ)...</pre><pre>CREATE FUNCTION &quot;UDF_DATES_DAYS_RANGE&quot;<br>(END_DATE TIMESTAMP_NTZ, START_DATE TIMESTAMP_NTZ)...</pre><p>Accepting a different number of arguments is another useful UDF overloading feature. This feature was used extensively to handle QLIK’s <em>wild_match</em>.</p><p>A total of 57 UDFs were created in converting a set of QLIK applications to Snowflake data objects using the Data Cake technique.</p><p>The MOM prefix loses its usefulness as the MOM VIEWs become more complex, e.g. when a MOM is composed of multiple MOMs. The modeler may choose to adopt a more business oriented naming convention, possibly using the prefix “CMP” for “complex”.</p><p>The modeler may also find the need for additional layers and naming conventions. I have not fully addressed building a Business Vault Data Cake model from a Data Vault physical model. One possibility is treating the objects in the Business Vault as though they were physical tables.</p><p>The reader might have noticed that the previous discussion addresses VIEWs, but the naming convention does <strong><em>not.</em></strong> A key feature of the Data Cake model is that a VIEW at any level in the model except SOPR may be replaced by simply substituting a physical table with the following basic steps:</p><ol><li>CTAS table as SELECT … FROM &lt;view&gt;</li><li>Rename the VIEW</li><li>Rename the table to the original VIEW name, e.g., VW_MOM_…</li></ol><p>While addressing ETL/ELTL for the new table is beyond the scope of this article, note that the VIEW is likely to be especially useful for designing the maintenance of the physical table.</p><p>Snowflake’s upcoming Object Tagging feature should prove very useful for further identifying the various categories of objects created in the Data Cake Model.</p><p>We will refer to a collection of related Data Cake VIEWs (or tables) as a “Data Bakery”. Data Cakes tend to consist of a large number of columns, particularly when converting legacy BI apps to Data Cakes. A key goal of the Data Cake Model is reducing the BI tools user’s workload. A “Data Cake Slice” (SLC) is a simple VIEW selecting a subset of columns from a Data Cake often with filtering and additional row level business logic.</p><h3>Conclusion</h3><p>A Data Bakery should be the source for data marts going forward. In Snowflake, there should be no need for ETL/ELTL to create physical tables for data marts. Data marts should be created based on the Data Bakery VIEWs, whether they be full Data Cakes, Data Cake Slices or creating new Data Cakes in the data mart.</p><p>Copyright © 2021, Jeffrey Jacobs, Jeffrey Jacobs &amp; Associates, LLC</p><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/snowflake-elvt-data-cake-model-bi-vendor-independence-jeffrey-jacobs/?lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BekwbrZ88S62NuiT0ZRjn0A%3D%3D&amp;licu=urn%3Ali%3Acontrol%3Ad_flagship3_profile_view_base-featured_item_detail_view"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=29db24d50401" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-elvt-the-data-cake-model-and-bi-vendor-independence-29db24d50401">Snowflake and ELVT vs [ELT,ETL], Part 2, The ELVT Reference Architecture</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake and ELVT vs [ELT, ETL]]]></title>
            <link>https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-e1d91cbc5c9d?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1d91cbc5c9d</guid>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Thu, 15 Apr 2021 23:35:49 GMT</pubDate>
            <atom:updated>2021-07-20T16:16:26.297Z</atom:updated>
            <content:encoded><![CDATA[<h3>Snowflake and ELVT vs. [ELT, ETL]</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*_cpOF25WOOS9ub8C" /></figure><p>Over several generations of RDBM technologies, I have learned that common practices, knowledge, and attitudes have become obsolete. Query planning and optimization are continually evolving. This evolution, combined with Snowflake’s revolutionary performance capabilities and data model agnosticism, leads many database practitioners to a new architectural pattern. I call <strong>Extract, Load, Virtual Transform (ELVT) a key element of this new pattern</strong>.</p><p>A little historical perspective is in order. Running a SQL query involves three fundamental steps:</p><ol><li>Creating an execution plan. This operation is performed by the Query Optimizer (or sometimes <em>Query Compiler</em>).</li><li>Executing the plan, i.e., retrieving the desired data from storage.</li><li>Returning the data to the user.</li></ol><p>I spent much of my professional database career working with Oracle’s RDBMS product suite. In the earlier versions of Oracle, the execution plan creation was based on a simple set of rules. This was known as the Rule-Based Optimizer (RBO). The optimization was quite simple; use either an index if it exists or scans the data if no index is available. Query tuning was largely a function of shared tribal knowledge and understanding the rules. Those who worked with early versions of Oracle all have stories about dramatically improving performance by reversing the join order and similar tales.</p><p>The Cost Based Optimizer (CBO) was introduced with Release 7. It was primitive and mostly ignored, but it was the beginning of one of the most sophisticated computer science applications in any technology. The creators of Snowflake, Thierry Cruanes, and Benoit Dageville were significant contributors to the CBO. (Oracle’s CBO has been an endless source of fascination to me.) Let us not forget the incredible improvements in computer hardware in general.</p><p>A CBO evaluates many possible executions plans based on table statistics and the operations available in the underlying execution engine. For our purposes, an even more important CBO feature is rewriting original queries into better-performing code. The underlying theory of relational algebra enables the nearly infinite transformation of a SQL query into its logical equivalent. Only an exceedingly small number of these will perform better than the original.</p><p>A VIEW is essentially a pre-packaged query and may be used any place a physical table is used. When VIEWs were introduced, DBAs often discouraged their use; joining VIEWs in a query was strongly discouraged. Despite the abilities of today’s CBOs, this attitude and a preference for using physical tables instead of VIEWs are still quite common.</p><p>Extract, Transform, and Load (ETL) was the earliest method for loading large amounts of data into a database. ETL was performed by a separate application, such as Informatica, usually hosted on its own servers. The necessary data transformations were performed outside the database and then loaded into the database.</p><p>As database platform performance improved, the order changed. Data was loaded into the database, and the transformation was performed by SQL operations which INSERT the data into tables. Hence, Extract Load and Transform (ELT). This is not particularly new, having become more common with each new generation of hardware.</p><p>ELT is a somewhat misleading term. It should be a four-letter acronym, <em>ELTL,</em> as the final step is still <em>Loading</em> data into physical tables. Snowflake’s performance has made ELTL very popular.</p><p>In many cases, the primary rationale for using the ELT/ELTL approach is the practitioner’s experience with data marts, operational data stores, and prior experience with database performance.</p><p>Snowflake’s revolutionary performance enables a new approach requiring a new name and acronym; <strong><em>Extract, Load, Virtual Transform (ELVT)</em></strong>. Data is still Extracted from source systems and loaded into the database. ETL/ELTL is used to produce clean physical tables. But VIEWs perform the logical Transformation without loading into or creating physical tables.</p><p>Let us keep three common engineering adages in mind:</p><ol><li>Murphy’s Law: whatever can go wrong, will go wrong</li><li>KISS: Keep It Simple Stupid</li><li>The probability of Murphy’s Law being observed in any system increases with the number of moving parts.</li></ol><p>ETL/ETLL pipelines typically have multiple steps, with intermediate storage between steps. Things may fail or break for many reasons.</p><p>ELVT has the following advantages, with the assumption that physical tables contain the required level of cleanliness:</p><ol><li>Simplicity: VIEWs are SQL</li><li>Fewer moving parts</li><li>True single source of data; no transformed physical data that may be incorrect or out of date</li><li>Easily modified and fixed.</li><li>Simpler governance and security; SECURE VIEWs may be used</li><li>It is straightforward to replace a VIEW with a physical table</li><li>Business Intelligence tool vendor independence — this will be covered in detail in my next article, <a href="https://www.linkedin.com/pulse/snowflake-elvt-data-cake-model-bi-vendor-independence-jeffrey-jacobs/"><em>Free Your Data from BI Vendor Lock In with a New Reference Model in Snowflake</em></a></li><li>Reduced development time, effort, and cost across the entire data cycle</li></ol><p>The reader might question the impact of ELVT on performance and computing costs. The major factor in query performance is the amount of data to be processed. If ELT/ELTL produces much smaller tables, then they should be considered. A well-designed ELVT architecture enables straightforward replacement of VIEWs with access to physical tables if needed. The use of VIEWs instead of direct physical tables has little impact on CPU usage.</p><p>I have used ELVT on several projects. I have participated in many discussions and seen numerous case studies where ELVT is commonly used in place of ELT/ETLL.</p><p>The article <a href="https://www.linkedin.com/pulse/snowflake-micro-partition-vs-legacy-macro-partition-pruning-jacobs/?lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BWEcb1n3nTEmBjbH2EagBCA%3D%3D&amp;licu=urn%3Ali%3Acontrol%3Ad_flagship3_profile_view_base-featured_item_detail_view"><em>Pivoting IIOT Data in Snowflake</em></a> describes a simple ELVT model for creating pivoted VIEWs for BI purposes. The underlying tables are never directly referenced in queries. One VIEW, VW_SAMPLE_QUARTER_HOUR_STATS, aggregates the data into the average, maximum and minimum values for quarter-hour intervals.</p><p>Let us examine a more complex VIEW from that article’s data model, shown below at the end of this article. This VIEW determines the start and end times for decanting natural gas from trucks into a pipeline. A “totalizer” sensor maintains the running total of the amount of gas decanted over time. The trucks come from different farms, and each farm has a dedicated decanting station, aka “pole.” Changes in the totalizer value determine the time and duration for each decanting. Totalizers occasionally “burp” an invalid sample, which needs to be ignored.</p><p>Using a Small virtual warehouse, SELECT * FROM VW_YJ_DECANT_INJECTIONs, using a complex VIEW with 289M rows in the base table took ~2.7s total time, ~0.6s CPU. Query execution was “first time” to avoid local caching, and Small was used to minimize local disk. The query has 26 steps with the following statistics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/251/0*S4Vfxuf8G5vxtLKf" /></figure><p>This was a very inexpensive operation to get the results required and was faster to develop and deploy. Compare that to the cost of storage plus time and resources for developing, managing, and testing an ETL or ELT pipeline process had we chosen to create a 2 ndphysical table instead. This ELVT is a much more agile and cost-effective approach.</p><p>Snowflake’s unique capabilities enable this new architectural pattern. ELVT is not a silver bullet; ETL/ELTL pipelines need to be examined to see if they can be replaced with VIEWs. In my next article, I will discuss a common, disciplined approach to building ELVT architectures.</p><pre>VIEW TEXT<br> <strong>CREATE</strong> <strong>OR</strong> REPLACE <strong>VIEW</strong><br>     VW_YJ_DECANT_INJECTION_TEST copy grants <strong>AS</strong><br> <strong>SELECT</strong><br>     <strong>*</strong> ,<br>     TRUNC( TIMEDIFF(&#39;minutes&#39;,START_TIME,END_TIME)<strong>/</strong>60,2) <strong>AS</strong> DURATION_HOURS<br> <strong>FROM</strong><br>     (   <strong>SELECT</strong><br>             DESCRIPTIVE_TAG_NAME,<br>             <strong>CASE</strong><br>                 <strong>WHEN</strong> DESCRIPTIVE_TAG_NAME <strong>=</strong> &#39;pole_1_totalizer_scf&#39;<br>                 <strong>THEN</strong> &#39;McDonalds 1<br>                 <strong>WHEN</strong> DESCRIPTIVE_TAG_NAME <strong>=</strong> &#39;pole_2_totalizer_scf&#39;<br>                 <strong>THEN</strong> &#39;McDonalds 2<br>                 <strong>WHEN</strong> DESCRIPTIVE_TAG_NAME <strong>=</strong> &#39;pole_3_totalizer_scf&#39;<br>                 <strong>THEN</strong> &#39;McDondalds 2<br>                 <strong>WHEN</strong> DESCRIPTIVE_TAG_NAME <strong>=</strong> &#39;pole_4_totalizer_scf&#39;<br>                 <strong>THEN</strong> &#39;McDondalds 4&#39;<br>                 <strong>ELSE</strong> &#39;Unknonn&#39;<br>             <strong>END</strong> <strong>AS</strong> farm,<br>             <strong>CASE</strong><br>                 <strong>WHEN</strong> current_value <strong>=</strong> next_value<br>                 <strong>AND</strong> current_value <strong>&lt;&gt;</strong> first_prior_value<br>                 <strong>THEN</strong> TRUNC(current_value <strong>-</strong> lag( TRUNC (current_value,0),1) <strong>over</strong><br>                                                                                  (<br>                                                                              <strong>PARTITION</strong> <strong>BY</strong><br>                                                                                  DESCRIPTIVE_TAG_NAME<br>                                                                              <strong>ORDER</strong> <strong>BY</strong><br>                                                                                  <strong>CURRENT_TIME</strong>) )<br>             <strong>END</strong> <strong>AS</strong> SCF_INJECTED ,<br>             LAG(T.<strong>current_time</strong>,1) <strong>OVER</strong> (<br>                                     <strong>PARTITION</strong> <strong>BY</strong><br>                                         DESCRIPTIVE_TAG_NAME<br>                                     <strong>ORDER</strong> <strong>BY</strong><br>                                         <strong>CURRENT_TIME</strong>) <strong>AS</strong> START_TIME,<br>             T.<strong>CURRENT_TIME</strong>                            <strong>AS</strong> END_TIME<em>--,</em><br>         <strong>FROM</strong><br>             (   <strong>SELECT</strong><br>                     DESCRIPTIVE_TAG_NAME,<br>                     SAMPLE_QRTR_HOUR             <strong>CURRENT_TIME</strong>,<br>                     TRUNC (&quot;Quarter Hour Avg&quot;,0) current_value,<br>                     lead( TRUNC (&quot;Quarter Hour Avg&quot;,0),1)<strong>over</strong><br>                                                                (<br>                                                            <strong>PARTITION</strong> <strong>BY</strong><br>                                                                DESCRIPTIVE_TAG_NAME<br>                                                            <strong>ORDER</strong> <strong>BY</strong><br>                                                                SAMPLE_QRTR_HOUR) next_value,<br>                     lead (SAMPLE_QRTR_HOUR,1) <strong>over</strong><br>                                                     (<br>                                                 <strong>PARTITION</strong> <strong>BY</strong><br>                                                     DESCRIPTIVE_TAG_NAME<br>                                                 <strong>ORDER</strong> <strong>BY</strong><br>                                                     SAMPLE_QRTR_HOUR) next_time,<br>                     lag( TRUNC (&quot;Quarter Hour Avg&quot;,0),1) <strong>over</strong><br>                                                                (<br>                                                            <strong>PARTITION</strong> <strong>BY</strong><br>                                                                DESCRIPTIVE_TAG_NAME<br>                                                            <strong>ORDER</strong> <strong>BY</strong><br>                                                                SAMPLE_QRTR_HOUR) <strong>AS</strong><br>                     first_prior_value,<br>                     lag (SAMPLE_QRTR_HOUR,1) <strong>over</strong><br>                                                    (<br>                                                <strong>PARTITION</strong> <strong>BY</strong><br>                                                    DESCRIPTIVE_TAG_NAME<br>                                                <strong>ORDER</strong> <strong>BY</strong><br>                                                    SAMPLE_QRTR_HOUR) <strong>AS</strong> first_prior_time<br>                 <strong>FROM</strong><br>                     VW_SAMPLE_QUARTER_HOUR_STATS<br>                 <strong>WHERE</strong><br>                     tagpath <strong>IN</strong><br>                     (   <strong>SELECT</strong><br>                             tagpath<br>                         <strong>FROM</strong><br>                             tag_detail<br>                         <strong>WHERE</strong><br>                             DESCRIPTIVE_TAG_NAME <strong>IN</strong> ( &#39;pole_1_totalizer_scf&#39; ,<br>                                                      &#39;pole_2_totalizer_scf&#39;,<br>                                                      &#39;pole_3_totalizer_scf&#39;,<br>                                                      &#39;pole_4_totalizer_scf&#39; ) )) T<br>         <strong>WHERE</strong><br>             (<br>                 current_value <strong>&lt;&gt;</strong> first_prior_value<br>             <strong>AND</strong> CURRENT_VALUE <strong>=</strong> NEXT_VALUE )<br>         <strong>OR</strong><br>             (<br>                 current_value <strong>&lt;&gt;</strong> next_value<br>             <strong>AND</strong> CURRENT_VALUE <strong>=</strong> FIRST_PRIOR_VALUE ))<br> <strong>WHERE</strong><br>     scf_injected <strong>IS</strong> <strong>NOT</strong> <strong>NULL</strong><br> <strong>AND</strong> scf_injected <strong>&gt;=</strong> 500<br> <strong>ORDER</strong> <strong>BY</strong><br>     DESCRIPTIVE_TAG_NAME,<br>     START_TIME;</pre><p>Copyright © 2021, Jeffrey Jacobs</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/42/0*DvEPSjn8B7ntfBza" /></figure><p><em>Originally published at </em><a href="https://jeffreyjacobs.wordpress.com/2021/04/15/snowflake-and-elvt-vs-elt-etl/"><em>http://jeffreyjacobs.wordpress.com</em></a><em> on April 15, 2021.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1d91cbc5c9d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-and-elvt-vs-elt-etl-e1d91cbc5c9d">Snowflake and ELVT vs [ELT, ETL]</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake Micro-partition vs Legacy Macro-partition Pruning]]></title>
            <link>https://medium.com/snowflake/snowflake-micro-partition-vs-legacy-macro-partition-pruning-b533128c513b?source=rss-55cf65245c0b------2</link>
            <guid isPermaLink="false">https://medium.com/p/b533128c513b</guid>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[table-partitioning]]></category>
            <category><![CDATA[partition-pruning]]></category>
            <category><![CDATA[snowflake-computing]]></category>
            <dc:creator><![CDATA[Jeffrey Jacobs]]></dc:creator>
            <pubDate>Sun, 04 Apr 2021 20:10:22 GMT</pubDate>
            <atom:updated>2021-04-22T19:47:04.840Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*4XeNuH0quTrC-_KkMyv-ww.jpeg" /></figure><p>I have been in the data business through several RDBM generations and have seen many attempts at comparing performance between competing vendors.</p><p>To say those comparisons should be taken with a grain of salt is an understatement. The resulting salt consumption would not be good for anybody’s health.</p><p>The Transaction Processing Council ( <a href="http://tpc.org/">TPC</a>) performance benchmarks provide the standard. TPC provides datasets and specifications for various benchmarks.</p><p>Historically, RDBMS vendors ran (or avoided running) <a href="http://tpc.org/">TPC</a> benchmarks themselves and boasted about the results.</p><p>This process came with the caveat: “there are lies, damn lies, and (vendor) benchmarks”. There were (and are) just too many variables rendering claims unreliable at best.</p><p>I have not seen any TPC benchmarks used to compare current RDBMS vendors. I have seen many, in my opinion, overly simplistic query performance comparisons, but none that I consider credible or reliable. Snowflake provides TPC datasets in the SNOWFLAKE_SAMPLE_DATA in every account, but I have yet to hear results of anybody performing a significant comparison using TPC datasets. (I would not be surprised to learn that Snowflake used them extensively for their own testing.)</p><p>There are numerous customer testimonials stating that moving from their prior vendor to Snowflake has resulted in greatly improved query performance, but there could be many reasons for this.</p><p>Putting this in perspective requires understanding the internals of Snowflake’s performance. While there are many factors involved in query performance, this article focuses on one major ingredient, partitioning and partition pruning.</p><p>Partitioning divides a table’s storage into pieces. The fewer partitions requiring processing, the better the query performance. A query skipping the data in a partition is referred to as <em>pruning.</em> The query engine’s optimizer examines the meta-data of a table’s partition to determine if it can be pruned.</p><p>Partitions in both on-prem and cloud-based legacy RDBMSes (e.g., Oracle, Teradata, Synapse, Big Query) tend to be very large. We will refer to these as “macro-partitions”. Macro-partitions require specifying a partition-key, which is a <em>very small</em> set of the table’s columns. Each partition contains only the data that satisfies the condition specified for <em>all</em> key columns. The most common condition is a range of values, typically dates, for each partition, although a scalar value may also be used. Some RDBMSes allow sub-partitioning as well, with the sub-partition-key consisting of the parent’s partition-key columns and additional key columns for the sub-partition(s).</p><p>If a partition is not pruned, the data within the partition must be processed. For analytics, this is typically the most expensive operation, a full scan of the data.</p><p>Macro-partitioning is both an art and a science, requiring substantial planning, setup and often maintenance.</p><p>Snowflake’s approach is completely different. The table is automatically partitioned into micro-partitions, with a maximum size of 16MB compressed data, typically 100–150MB uncompressed. The meta-data for every column in a micro-partition has the minimum and maximum values for that column. Unlike macro-partitioned tables, every column in the table can <em>potentially</em> be used to determine if the micro-partition can be pruned<em>.</em> This includes appropriate fields (or sub-columns) in semi-structured data contained in VARIANT columns, e.g., JSON.</p><p>The statistics are gathered when the micro-partition is created and are kept in the management level’s meta-data. Once created, a micro-partition is immutable.</p><p>There are many other aspects to Snowflake’s performance, but micro-partitioning is a key differentiator from all other RDBMSes.</p><p>Here are the key differentiators between micro-partitions and legacy partitions:</p><ol><li>Macro-partitions are comparatively large, e.g., one week’s or one month’s data. Micro-partitions are smaller, e.g., one day’s data.</li><li>Macro-partitions must have the partition-key columns and the range of values for each partition-key column specified. Pruning occurs based on the order of the partition-key columns. Pruning granularity occurs based on left to right ordering of the partition-key columns in the partition-key definition. If the leading/left-most column is not used in the WHERE clause, no pruning takes place. If the 2nd partition-key column is not used in the WHERE clause, pruning is based only on the 1st column resulting in scanning multiple partitions matching the 1st column’s filter. Etc.</li><li>In micro-partitions, the partition-key columns are not specified, as every column has maximum and minimum values in the meta-data.</li></ol><p>A table in Snowflake is effectively <em>range </em>partitioned on <em>every</em> column. Using more filtering columns in the WHERE clause <em>may</em> dramatically increase the pruning effectiveness.</p><p>We will demonstrate using a denormalized TPC web sales table created from “SNOWFLAKE_SAMPLE_DATA”.”TPCDS_SF10TCL” with data from 2002. The table, DEMO_PRUNING, was created to ensure that pruning is on a single table. Daily loading of the data was simulated using ORDER BY D_DATE, resulting in minor overlap of data in the micro-partitions.</p><p>We will look at Q4’s data, which includes Black Friday and what is now called Cyber Monday. Although the table contains eight columns, only three are of interest for our purpose:</p><pre>D_DATE <br>WS_EXT_SALES_PRICE <br>WS_SALES_PRICE</pre><p>The table contains all data for 2002.</p><ul><li>1,437,206,906 rows</li><li>795 micro-partitions</li></ul><p>In Q4 of 2002</p><ul><li>One day’s data is ~7.8M rows</li><li>One week’s data is ~54.6M rows</li></ul><p>Snowflake’s UI shows the execution plan and statistics in the History Profile Tab reached by clicking on Query ID link in the History window or the Query_ID link in the Results pane in the Worksheet.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/275/1*ErQaE9SENxXKH7wDw2Tgag.jpeg" /></figure><p>The statistics for the week containing Black Friday will serve as a proxy for comparing a one-week macro-partition query with micro-partition queries.</p><p>The following query provides the desired statistics :</p><pre>SELECT COUNT(*), MAX(WS_LIST_PRICE), MAX(WS_SALES_PRICE), AVG(WS_EXT_SALES_PRICE) <br>  FROM DEMO_PRUNING <br>  WHERE D_DATE BETWEEN &#39;2002-11-24&#39; AND &#39;2002-11-30&#39;</pre><p>The query scans 31 partitions, 346MB which we will use when comparing micro- and macro- partition pruning in the following examples. . (Note that in row storage RDBMSes, the number of bytes scanned in a macro-partition would be significantly greater.)</p><p>In a macro-partition RDBMS, any query WHERE D_DATE falls within these dates requires scanning <em>all</em> macro-partition data, e.g.:</p><pre>SELECT COUNT(*), MAX(WS_LIST_PRICE), MAX(WS_SALES_PRICE), AVG(WS_EXT_SALES_PRICE) <br>  FROM DEMO_PRUNING <br>  WHERE D_DATE = &#39;2002-11-29&#39; - Black Friday</pre><ul><li>The macro-partitioned RDBMS scans 2 full weeks of data, 62 partitions, 728MB.</li><li>Snowflake scans 23 micro-partitions, 308MB.</li></ul><p>Let us look at the very largest purchases for the same period:</p><pre>SELECT COUNT(*), MAX(WS_LIST_PRICE), MAX(WS_SALES_PRICE), AVG(WS_EXT_SALES_PRICE) <br>  FROM DEMO_PRUNING <br>  WHERE <br>   D_DATE BETWEEN &#39;2002-12-28&#39; AND &#39;2002-12-03&#39; <br>   AND WS_EXT_SALES_PRICE &gt; 29000</pre><ul><li>The macro-partitioned RDBMS scans 2 full weeks of data, 62 partitions, 728MB. WS_EXT_SALES would <strong><em>not</em> </strong>typically be a column in a macro-partition-key specification.</li><li>Snowflake uses the new filter as an “ad-hoc” partition-key column, 12 partitions, 152MB.</li></ul><p>Finally, let us add yet another filter column to look at high priced item purchases:</p><pre>SELECT COUNT(*), MAX(WS_LIST_PRICE), MAX(WS_SALES_PRICE), AVG(WS_EXT_SALES_PRICE) <br>  FROM DEMO_PRUNING <br>  WHERE D_DATE BETWEEN &#39;2002-12-28&#39; <br>   AND &#39;2002-12-03&#39; AND WS_EXT_SALES_PRICE &gt; 29000 <br>   AND WS_SALES_PRICE &gt; 297</pre><ul><li>The macro-partitioned RDBMS scans 2 full weeks of data, 62 partitions, 728MB. WS_EXT_SALES_PRICE would also <strong><em>not</em> </strong>typically be a column in a macro-partition-key specification.</li><li>Snowflake uses the new filter to further reduce the number to 11 partitions, 111MB.</li></ul><p>Clustering of the data is a key factor in effective partition pruning. Data that is loaded on a regular basis, e.g., daily, is typically well clustered. Even poorly clustered data often performs surprisingly well.</p><p>Loading the data with COPY INTO … FROM SELECT … ORDER BY is a highly effective technique for some types of loads, with a higher, one time compute cost.</p><p>The <a href="https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html">Clustering</a> feature may be used to enforce clustering. It is a background service. UPDATE and DELETE operations may result in additional service charges. Please refer to the <a href="https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html">Clustering</a> page for recommendations about this feature.</p><p>Snowflake’s unique micro-partitioning strategy is a key factor in its exceptional query performance.</p><p>Special thanks to my long time friend Kent Graziano for his feedback on this article.</p><p>Copyright © 2021, Jeffrey M Jacobs</p><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/snowflake-micro-partition-vs-legacy-macro-partition-pruning-jacobs"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b533128c513b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-micro-partition-vs-legacy-macro-partition-pruning-b533128c513b">Snowflake Micro-partition vs Legacy Macro-partition Pruning</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>