<?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 James Weakley on Medium]]></title>
        <description><![CDATA[Stories by James Weakley on Medium]]></description>
        <link>https://medium.com/@jamesweakley?source=rss-4e334e6e2130------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*DuyR5ghesBoIpB8lXFJWJA.png</url>
            <title>Stories by James Weakley on Medium</title>
            <link>https://medium.com/@jamesweakley?source=rss-4e334e6e2130------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 07:28:43 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jamesweakley/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[Measuring code coverage in Snowpark Python Stored Procedures]]></title>
            <link>https://medium.com/snowflake/measuring-code-coverage-in-snowpark-python-stored-procedures-7c75c00912c6?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/7c75c00912c6</guid>
            <category><![CDATA[snowpark]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-superhero]]></category>
            <category><![CDATA[testing]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Thu, 09 Mar 2023 10:02:48 GMT</pubDate>
            <atom:updated>2023-03-09T10:02:48.393Z</atom:updated>
            <content:encoded><![CDATA[<p>Python Stored Procedures provide a convenient and flexible way to package and run your python code on Snowflake. They are useful for tasks like:</p><ul><li>Data processing and transformation</li><li>Machine learning</li><li>Custom business logic</li></ul><p>As you build larger and more sophisticated Stored Procedures, you’ll want to write test cases to validate that they work as expected, and to ensure they continue to work as you make changes. So you write a suite of tests, and they all pass 🥳.</p><p>But how do you measure the extent of testing, and ensure that the critical parts of your code are included? This type of measurement is called Code Coverage. Software engineering teams use it for:</p><ul><li>Identifying untested areas of the codebase</li><li>Providing a metric for progress</li><li>Risk management: if coverage is low in critical areas, you can allocate resources to improving coverage until it meets your desired thresholds</li><li>Compliance requirements: Some industries have standards and regulations for software to meet, and code coverage can provide a quantitative measure</li></ul><p>New in version 0.1.25 of <a href="https://github.com/Snowflake-Labs/snowcli">snowcli</a>, you can automatically measure code coverage on your Stored Procedures as your tests run, using the <a href="https://coverage.readthedocs.io/en/7.2.1/">coverage.py</a> package.</p><p>In this article, we’ll do a walkthrough of the new feature.</p><h3>The scenario</h3><p>Here’s a very contrived Stored Procedure which can add, subtract, multiply or divide two numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*LVR29DMvt4qV1TAg2evutA.png" /></figure><p>As you can see, there are a few different code paths that lead to different business logic, including some of it from another module (other_stuff.py).</p><h3>snowcli in normal mode</h3><p>Normally, you run snow procedure packagefollowed bysnow procedure create to upload your procedure to snowflake, so the process looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/787/1*ev2btJV4q23Je2Xh2CnzVA.png" /><figcaption>your code in red</figcaption></figure><h3>snowcli adding code coverage</h3><p>To measure code coverage as we test, we can simply add the new --install-coverage-wrapper parameter. The full commands to package and deploy our procedure are:</p><pre>snow procedure package<br>snow procedure update -n calculator -h &quot;app.calculator&quot; -i &quot;(thing_to_do string, first float, second float)&quot; --return-type &quot;numeric&quot; --install-coverage-wrapper --replace-always</pre><p>Here’s what happens when that flag is added:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*xZjvUviZAwfUGlL3D_DicQ.png" /><figcaption>your code in red, auto-generated code in cyan</figcaption></figure><h3>Invoking the procedure</h3><p>Now that we’re measuring code coverage, let’s give it a test:</p><pre>snow procedure execute -p &quot;calculator(&#39;add&#39;,7,10)&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/86/1*CcPLqaIb0tVcZcQjojnhiA.png" /></figure><p>So the calculator works!</p><p><em>Note: Normally we’d build test cases using code so that they can be run in a CI pipeline, including an assertion that the returned value is correct given the inputs (the approach </em><a href="https://medium.com/snowflake/unit-testing-with-snowpark-python-in-azure-devops-f50452dd1682"><em>this article</em></a><em> describes)</em></p><h3>Generating a coverage report</h3><p>Now that we’ve invoked our procedure at least once, we can generate a coverage report:</p><pre>snow procedure coverage report -n &quot;calculator&quot; -i &quot;(thing_to_do string, first float, second float)&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*xkTQBpSlHS6wapQgwJ3ogg.png" /></figure><p>Behind the scenes, snowcli fetched all of the .coverage files from the DEPLOYMENTS stage under this proc’s /coverage/ subfolder.</p><p>These .coverage files are tiny little sqlite databases containing information on which lines in each file were executed. If there is more than one coverage file, snowcli will ask coverage.py to merge them together before generating the report.</p><p>If we open the coverage report, we see:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/738/1*p1mFF3ry9W3eZv1YBKgn1A.png" /></figure><p>38% is not so great. Looking at the files themselves:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nIJQ-Th8tlqQMYL8iApaVw.png" /></figure><h3>Increasing our coverage</h3><p>We can see visually the code paths we need to test. Let’s do some more testing:</p><pre># subtract 3 from 8, returns 5<br>snow procedure execute -p &quot;calculator(&#39;subtract&#39;,8,3)&quot;<br># muliply 3 by 4, returns 12<br>snow procedure execute -p &quot;calculator(&#39;multiply&#39;,3,4)&quot;<br># divide 10 by 2, returns 5<br>snow procedure execute -p &quot;calculator(&#39;divide&#39;,10,2)&quot;<br># test an operation that doesn&#39;t exist, returns an error<br>snow procedure execute -p &quot;calculator(&#39;to the power of&#39;,10,2)&quot;</pre><p>With that done, we re-run our coverage report:</p><pre>snow procedure coverage report -n &quot;calculator&quot; -i &quot;(thing_to_do string, first float, second float)&quot;</pre><p>This time we see there’s been 5 invocations in total:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*7lWltj_fGNNPJGebgHRvUw.png" /></figure><p>And after refreshing the report, we see we’ve obtained the mythical 100% code coverage:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/737/1*NVqMDOMsFWgAQ8hALWOtCA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MHNKZmcCoPVUFo9vM5coqQ.png" /></figure><p>If we want to start over (e.g. after changing our test cases or the stored procedure itself), we can delete all the coverage files:</p><pre>snow procedure coverage clear -n &quot;calculator&quot; -i &quot;(thing_to_do string, first float, second float)&quot;</pre><h3>What should I be aiming for?</h3><p>100% code coverage is an unreasonable goal on a typical codebase, and it’s important not to over-fixate on the number. Remember that in any organisation “you get what you measure”, and just like measuring lines of code as a proxy for developer productivity, creating pressure around code coverage stats can create the wrong incentives.</p><p>Often there are diminishing returns above 70–80%, so you should instead use a more realistic number as a minimum threshold. Remember that code coverage treats each line with equal weight, but in reality some bugs are inconsequential, others are embarrassing, and others are company-ending, depending on where in the codebase they occur. So it can be a good idea to ensure you have high coverage in the higher criticality areas.</p><p>You should equally encourage qualitative aspects such as readability and maintainability (via manual code review).</p><h3>Summary</h3><p>In this article, we demonstrated how to measure the coverage of tests for our code. By investing a small amount of time getting this in place, you can ship code faster with a greater degree of confidence.</p><p>That’s it — happy testing!</p><h3>Further Reading</h3><ul><li><a href="https://docs.snowflake.com/en/sql-reference/stored-procedures-python">Writing Stored Procedures in Snowpark (Python) | Snowflake Documentation</a></li><li><a href="https://interworks.com/blog/2022/08/16/a-definitive-guide-to-python-stored-procedures-in-the-snowflake-ui/">A Definitive Guide to Python Stored Procedures in the Snowflake UI - InterWorks</a></li><li><a href="https://medium.com/snowflake/unit-testing-with-snowpark-python-in-azure-devops-f50452dd1682">Unit testing with Snowpark Python in Azure DevOps</a></li><li><a href="https://www.bullseye.com/minimum.html">Minimum Acceptable Code Coverage</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7c75c00912c6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/measuring-code-coverage-in-snowpark-python-stored-procedures-7c75c00912c6">Measuring code coverage in Snowpark Python Stored Procedures</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[Running pip packages in Snowflake]]></title>
            <link>https://medium.com/snowflake/running-pip-packages-in-snowflake-d43581a67439?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/d43581a67439</guid>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[anaconda]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Mon, 04 Jul 2022 14:01:52 GMT</pubDate>
            <atom:updated>2023-08-23T16:46:49.073Z</atom:updated>
            <content:encoded><![CDATA[<h3>UPDATE: Since this article was written, there are far more streamlined ways to achieve this:</h3><h4><a href="https://medium.com/snowflake/introducing-simple-workflow-for-using-python-packages-in-snowpark-928f667ff1aa">https://medium.com/snowflake/introducing-simple-workflow-for-using-python-packages-in-snowpark-928f667ff1aa</a></h4><h3>Definitely check out <a href="https://github.com/Snowflake-Labs/snowcli">snowcli</a>, it will automatically detect which of the python packages aren’t on Anaconda and will download bundle them in a zip artifact for you.</h3><p>Recently, Snowflake <a href="https://www.snowflake.com/news/snowflake-expands-native-python-support-and-data-access-to-advance-programmability-in-the-data-cloud/">announced</a> support for Python in public preview, and I wrote a <a href="https://medium.com/snowflake/flaker-2-0-fake-snowflake-data-the-easy-way-dc5e65225a13">post</a> showcasing how easy it is to use. There’s also a great overview <a href="https://www.snowflake.com/blog/snowpark-python-innovation-available-all-snowflake-customers/">here</a> on some use cases, and how the feature fits in the ecosystem.</p><p>Snowflake partnered with Anaconda to simplify package management. They provide a comprehensive set of curated third party packages that you can import by simply listing them in the function definition. Dependencies will automatically be resolved, and you don’t need to worry about loading package files into your Snowflake account.</p><p>You can extend this further via the use of Snowflake Stages. The <a href="https://docs.snowflake.com/en/developer-guide/udf/python/udf-python-creating.html#">UDF documentation</a> shows some examples of importing your own python scripts, a text file, and even a zipped NLP model.</p><p>In this post, I’ll cover one extra scenario which is importing a “wheel” file which has been downloaded from PyPi. This should work as long as the package is platform-independant and doesn’t require OS-native libraries or specific CPU architectures.</p><p>I should also add that if there’s a useful PyPi package missing from the Snowflake Anaconda channel, it’s worth asking Snowflake to add it — they are eager to make it convenient to use popular, relevant Python packages.</p><p>To demonstrate this, I’m going to expand on my previous Flaker 2.0 post, linked above. Faker has various <a href="https://faker.readthedocs.io/en/master/communityproviders.html">community providers</a> that have pip packages, but are not part of Snowflake’s Anaconda channel.</p><p>To use them, we’re going to download the package from PyPi, upload it to a Snowflake stage, then extract and import it from within our Snowflake function.</p><h4>Step 1: Download the wheel file</h4><p>One you’ve found your project on PyPi, go to the “Download files” menu and download the Built Distribution (a .whl file).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gJycTp098YMV5l7ni17OCg.png" /></figure><p>In my example, I’ll be loading both <a href="https://pypi.org/project/faker-biology">faker-biology</a> and <a href="https://pypi.org/project/faker_music/">faker-music</a>.</p><h4>Step 2. Download the wrapper script</h4><p>Save the following code snippet locally, with the file name “wheel_loader.py”.</p><p>The script uses the approach <a href="https://docs.snowflake.com/en/developer-guide/udf/python/udf-python-creating.html#unzipping-a-staged-file">described</a> in Snowflake’s documentation, to extract the contents of a staged zip file. Then it adds the extracted package contents to the system path so that the package can be imported.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/484205ceb7e9a4c97dbc1189e893c8e7/href">https://medium.com/media/484205ceb7e9a4c97dbc1189e893c8e7/href</a></iframe><h4>Step 3: Upload files to a Snowflake stage</h4><p>First, create a Snowflake stage:</p><pre>CREATE STAGE OTHER_PYTHON_PACKAGES</pre><p>Then, upload the files using Snowsql:</p><pre>snowsql -q &quot;PUT file://wheel_loader.py <a href="http://twitter.com/OTHER_PYTHON_PACKAGE">@OTHER_PYTHON_PACKAGE</a>S AUTO_COMPRESS=FALSE OVERWRITE=TRUE&quot;<br>snowsql -q &quot;PUT file://faker_music-0.4-py2.py3-none-any.whl <a href="http://twitter.com/OTHER_PYTHON_PACKAGE">@OTHER_PYTHON_PACKAGE</a>S AUTO_COMPRESS=FALSE OVERWRITE=TRUE&quot;<br>snowsql -q &quot;PUT file://faker_biology-0.6.0-py3-none-any.whl <a href="http://twitter.com/OTHER_PYTHON_PACKAGE">@OTHER_PYTHON_PACKAGE</a>S AUTO_COMPRESS=FALSE OVERWRITE=TRUE&quot;</pre><h4>Step 4: Create the Snowflake function</h4><p>In the example below, I include the staged files in the function call (lines 6–10). I import the wheel_loader script (line 17), and use it to extract the wheel files and add them to the system path (lines 20–21). Then I can import the libraries (lines 23–24).</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/14209121890277bf6a7eaabb80a8de45/href">https://medium.com/media/14209121890277bf6a7eaabb80a8de45/href</a></iframe><h4>Step 5: Call the function</h4><pre>select FAKE_WITH_EXTENSIONS(&#39;en_US&#39;,&#39;celltype&#39;,null)::varchar as FAKE_CELLTYPE<br> from table(generator(rowcount =&gt; 50));</pre><p>The results below show cell types from the third party faker-biology provider:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/1*H2oq32-lT1qR3uxSxDtPzg.png" /></figure><h4>Conclusion</h4><p>I’ve demonstrated it’s possible to run pip packages inside Snowflake, and it’s a useful fallback if a Conda package isn’t readily available.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d43581a67439" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/running-pip-packages-in-snowflake-d43581a67439">Running pip packages in Snowflake</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[Flaker 2.0 — Fake Snowflake data the easy way]]></title>
            <link>https://medium.com/snowflake/flaker-2-0-fake-snowflake-data-the-easy-way-dc5e65225a13?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/dc5e65225a13</guid>
            <category><![CDATA[data-superhero]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[snowflake-computing]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Wed, 15 Jun 2022 16:02:52 GMT</pubDate>
            <atom:updated>2022-06-15T16:02:52.138Z</atom:updated>
            <content:encoded><![CDATA[<h3>Flaker 2.0 — Fake Snowflake data the easy way</h3><p>The ability to generate convincing synthetic data is as important as ever, and is useful in a variety of situations:</p><ul><li>Replacing Personally Identifiable Information with something that retains the same shape and structure of the original — you can use this to build non-production versions of your data pipelines. 🕵️</li><li>Taking your own dataset and extrapolating it to something equivalent but much larger, to test its future performance. 📈</li><li>Generating a dataset for demonstrating a data product or analytical technique. 🧑‍🏫</li><li>Generating a dataset as part of demonstrating a SQL scenario, like an example query to post to a public forum. 🧑‍💻</li></ul><p>It’s been almost two years since I first <a href="https://medium.com/snowflake/generating-realistic-looking-fake-data-in-snowflake-80796c77adb3">blogged</a> about fake data generation on Snowflake, which was later featured in an <a href="https://www.youtube.com/watch?v=ZMRpmRi0G_E">interview</a> on Behind the Data Cloud.</p><p>The first version of “Flaker” (Faker for Snowflake) was created as a Snowflake External Functions wrapper, and required an AWS deployment.</p><p>While Javascript UDFs have existed on Snowflake for some time, External Functions were the first step toward a more general extensibility of the platform.</p><p>The challenge with External Functions is that you need more than just a Snowflake account — you need an account with a public cloud provider like AWS or Azure, and you need to deploy some infrastructure that Snowflake communicates with.</p><p>This is a powerful feature, and makes sense when the information you need is, well…external. But when you just want to invoke a code library, it’s not the usual level of simplicity and time-to-value that Snowflake customers are used to.</p><p>If you check out the <a href="https://github.com/jamesweakley/flaker/tree/external_function">original Flaker codebase</a>, you can see that it involves a counter-productive detour into cloud infrastructure land before the faking can commence. Too much of this in a product (as others in the market are guilty of!), and a data analysts’ job starts to look like this:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FAbSehcT19u0%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DAbSehcT19u0&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FAbSehcT19u0%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/2ac9b1ca459b1e56987ab12f9ea2869a/href">https://medium.com/media/2ac9b1ca459b1e56987ab12f9ea2869a/href</a></iframe><p>Anyway, fast forward to a couple of months ago, and I was excited to be included on a Private Preview of Python for Snowflake. Naturally, one of the first things I did was to try to run Faker directly from within Snowflake.</p><h3>How it works</h3><p>The process of doing this, frankly, was so fast and simple that it barely warrants a blog post! So I did what any technical writer would do; add more features and examples until there’s enough to talk about 😅</p><p>Here’s everything you now need to generate fake data on Snowflake:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e2ed114b19cf2cbfa813b86f52326c5d/href">https://medium.com/media/e2ed114b19cf2cbfa813b86f52326c5d/href</a></iframe><p>I’ll step through and explain what’s going on, and do some examples at the end.</p><p>We define the function and its parameters, this is the interface that SQL users see. We’ll return a variant, because sometimes Faker generates composite information, like a complete user profile.</p><p>We mark it as volatile so that Snowflake doesn’t cache results (we want each row to contain a uniquely generated value). We include the Anaconda packages ‘faker’ and ‘simplejson’ from the <a href="https://repo.anaconda.com/pkgs/snowflake/">Snowflake channel</a>, and we tell Snowflake to invoke the ‘fake’ function we provide as the entry point.</p><p>Lines 13 and 14 are really just a courtesy to the caller, so they can pass NULL as a parameter instead of an empty object.</p><p>Finally, on line 16 you can see that we convert the Faker output to JSON and back again before returning, and we tell the json.dumps call to default to str (a string). The reason we do this is because Faker uses all sorts of Python types in its output (Decimals, Dates etc), so when Snowflake needs to convert the result back to a variant, there’s too much ambiguity to do it correctly. So we intervene and just force a conversion to string.</p><h3>Examples</h3><h4>Data Generation</h4><p>50 names in the United States locale:</p><pre>select FAKE(&#39;en_US&#39;,&#39;name&#39;,null)::varchar as FAKE_NAME<br> from table(generator(rowcount =&gt; 50));</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/177/1*vpqgmjb2pd5wmHB-K2_KJA.png" /></figure><p>100 dates between 180 days ago and today:</p><pre>select FAKE(&#39;en_US&#39;,&#39;date_between&#39;,{&#39;start_date&#39;:&#39;-180d&#39;,&#39;end_date&#39;:&#39;today&#39;})::date as FAKE_DATE<br> from table(generator(rowcount =&gt; 50));</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/153/1*iWrjIyytSHkChG6GlSQErQ.png" /></figure><p>It doesn’t have to be people either. All sorts of random things like barcodes are supported:</p><pre>select FAKE(&#39;en_US&#39;,&#39;ean&#39;,{&#39;length&#39;:8})::varchar as FAKE_BARCODE<br> from table(generator(rowcount =&gt; 50));</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/165/1*DLqTzplb1H52wIV5VILVAw.png" /></figure><h4>Data Anonymization</h4><p>Let’s finish with a more elaborate example. Instead of using a generator function, let’s take an existing table of data and anonymize it conditionally, based on who is querying it.</p><p>Create or choose a database and schema you can write to, and create a view over the standard Snowflake benchmark dataset like so:</p><pre>create view CUSTOMER as (select * from SFC_SAMPLES_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER)</pre><p>If we take a peek at this data, we see some customers.</p><pre>select C_CUSTOMER_SK,C_SALUTATION,C_FIRST_NAME, C_LAST_NAME,C_BIRTH_COUNTRY, C_EMAIL_ADDRESS<br>from CUSTOMER<br>where C_CUSTOMER_SK &lt;= 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UVpd5rwEyOZzhVAq057QwQ.png" /></figure><p>Let’s pretend these are real people who’s Personally Identifiable Information we want to limit access to.</p><p>We can create some data masking policies that generate fake information for anyone who isn’t a SYSADMIN.</p><pre>create or replace masking policy first_name_mask as (val string) returns string -&gt;<br>  case<br>    when current_role() in (&#39;SYSADMIN&#39;) then val<br>    else FAKE(&#39;en_US&#39;,&#39;first_name&#39;,null)::varchar<br>  end;<br>  <br>create or replace masking policy last_name_mask as (val string) returns string -&gt;<br>  case<br>    when current_role() in (&#39;SYSADMIN&#39;) then val<br>    else FAKE(&#39;en_US&#39;,&#39;last_name&#39;,null)::varchar<br>  end;</pre><pre>create or replace masking policy country_mask as (val string) returns string -&gt;<br>  case<br>    when current_role() in (&#39;SYSADMIN&#39;) then val<br>    else UPPER(FAKE(&#39;en_US&#39;,&#39;country&#39;,null)::varchar)<br>  end;</pre><pre>create or replace masking policy email_mask as (val string) returns string -&gt;<br>  case<br>    when current_role() in (&#39;SYSADMIN&#39;) then val<br>    else FAKE(&#39;en_US&#39;,&#39;ascii_safe_email&#39;,null)::varchar<br>  end;</pre><p>Now we assign the masking policies to the customer table:</p><pre>alter view CUSTOMER modify column C_FIRST_NAME set masking policy first_name_mask;<br>alter view CUSTOMER modify column C_LAST_NAME set masking policy last_name_mask;<br>alter view CUSTOMER modify column C_BIRTH_COUNTRY set masking policy country_mask;<br>alter view CUSTOMER modify column C_EMAIL_ADDRESS set masking policy email_mask;</pre><p>Now if I query the table as SYSADMIN, I continue to see the same results as above.</p><p>I’ll grant the PUBLIC role ability to query the view and invoke the function:</p><pre>grant select on view customer to role public;<br>grant usage on function FAKE(VARCHAR, VARCHAR, VARIANT) to role public;</pre><p>Now, if I switch to the PUBLIC role and run the same query as before, now I see fake customers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F7AMq96sh376D_FvqJIx4g.png" /></figure><h3>Can it support my use case?</h3><p>For the full list of providers supported by Faker, see here: <a href="https://faker.readthedocs.io/en/master/providers.html">https://faker.readthedocs.io/en/master/providers.html</a></p><p>You’ll see that the examples tend to call a function, like <em>fake.name()</em>. But the <a href="https://faker.readthedocs.io/en/master/index.html#basic-usage">documentation</a> states:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/685/1*JJrTZ3sVvBi2Nyi4M7coUg.png" /></figure><p>You can see that we’re doing the same thing in the Snowflake function. Most of the standard and community Providers should just work, and you can also write your own. If they have complex inputs, you may need to construct your own UDF to handle those parameters more easily.</p><h3>I need help!</h3><p>If you’re having any trouble getting your fake data to generate the way you like, feel free to ask a question in the Snowflake Community forums: <a href="https://community.snowflake.com/s/forum">https://community.snowflake.com/s/forum</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dc5e65225a13" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/flaker-2-0-fake-snowflake-data-the-easy-way-dc5e65225a13">Flaker 2.0 — Fake Snowflake data the easy way</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 Salesforce, how do I integrate thee? Let me count the ways]]></title>
            <link>https://medium.com/snowflake/snowflake-and-salesforce-how-do-i-integrate-thee-let-me-count-the-ways-e86883467ac3?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/e86883467ac3</guid>
            <category><![CDATA[snowflake-computing]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[integration]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Thu, 28 Jan 2021 23:23:40 GMT</pubDate>
            <atom:updated>2021-03-04T22:47:08.920Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /></figure><p>A very common (and very reasonable) question I hear, is “how is <a href="https://omnata.com/">Omnata</a> different to Tableau/Einstein/Fivetran/Mulesoft/Workato/etc with respect to Salesforce?”</p><p>There are a few questions we have to answer along the way:</p><p><strong>1) Operational or Analytical?</strong><br>The solution varies depending on whether or not you are trying to understand or report on trends across large datasets, or whether you are looking to improve operational processes by extending the visibility and reach of the main Salesforce platform.</p><p><strong>2) Which Salesforce cloud?</strong><br>The solution varies depending on whether or not you are talking about Salesforce Marketing Cloud, or the main Salesforce Platform. The former was an acquired product with a fundamentally different backend to the latter, with a different set of products enabled for it.</p><p><strong>3) Which direction?</strong></p><p>The solution varies depending on whether you are wanting to move Salesforce data out into Snowflake to form part of your data warehouse, or whether you are wanting to improve your sales/marketing/service efforts by incorporating more Snowflake datasets into the Salesforce ecosystem.</p><p>Let’s work through these.</p><h4>Operational or Analytical?</h4><p>Sometimes the line appears blurry, especially as both Tableau CRM and regular Tableau support embedding into Salesforce, and can each connect to both Snowflake and Salesforce to load data. But the experience is just that — “embedded”, and far from a full operational capability. They cannot integrate properly at the record level with the on-platform objects, and the external data cannot be used in process automation tools, standard list views, from Apex code, etc.</p><p>So if you want a true analytics/visualisation platform, go with either of these two — let Salesforce argue internally over which one is best for you 😃</p><p>Otherwise, read on. And we’ll come back to Tableau CRM again later as part of the third question.</p><h4>Which Salesforce cloud, and in which direction?</h4><p>Salesforce Marketing Cloud is an acquired product (ExactTarget), with a fundamentally different database backend to regular Salesforce, with a different set of products that enable integration. The same is true for Pardot, which came across with ExactTarget.</p><p><em>In this section, “Salesforce” means the core CRM product, including “Service Cloud”, “Sales Cloud”, and the various “industry clouds” that have been built off it.</em></p><p><strong>Marketing Cloud to Snowflake</strong></p><p>If you’re wanting to bring your Marketing Cloud data back into Snowflake, use an app-to-warehouse data replication tool like Fivetran or similar.</p><p><strong>Snowflake to Marketing Cloud</strong></p><p>If you’re wanting to bring Snowflake data into Marketing Cloud to enrich your marketing data, you can use a more general purpose replication tool like Workato or Mulesoft, or a warehouse-to-app replication tool like Census or Hightouch. Copying the data is the only way, there is currently no real time access of data between these systems.</p><p>Leaving Snowflake out for a second, if you want to better integrate Marketing Cloud with the rest of Salesforce (in either direction), check out the various options <a href="https://help.salesforce.com/articleView?id=mc_cross_cloud_products.htm&amp;type=5">here</a>.</p><p><strong>Salesforce to Snowflake</strong><br>If you want to add your Salesforce data to Snowflake as part of a data warehouse, you can use an app-to-warehouse replication tool like Fivetran.<br>Coming soon (announced 2020) is the Einstein output connector, which provide this functionality as part of an Einstein Data Prep recipe. See a demo <a href="https://www.youtube.com/watch?v=bdQAGc0eAmQ">here</a>.</p><p>The decision between these two options will probably come down to how widely you already use Einstein data prep or Fivetran, and of course pricing.</p><p><strong>Snowflake to Salesforce</strong></p><p>Now, if you want to access Snowflake datasets in Salesforce, here’s where Omnata comes in. Omnata works in two modes: Push and Connect.</p><p><strong>Omnata Push</strong> is a natural extension of your Snowflake data pipeline. It comes as a set of Snowflake External Functions which can be used to load data into Salesforce.</p><p>Typically, you would do this for data that belongs on the Salesforce platform, like Accounts, Contacts and Leads, and exist in small enough quantities that your storage and API quota can handle them. A common example is pushing a single Lead score from Snowflake into an existing Lead object in Salesforce. You can also use a warehouse-to-app sync tool like Census or Hightouch for this scenario, though they are managed as external middleware that queries from the outside, rather than invoked from within Snowflake.</p><p><strong>Omnata Connect</strong> is a natural extension of the Salesforce platform. It comes as a Salesforce Connect custom adapter that runs entirely on-platform and communicates directly, in real time, to your Snowflake warehouse.</p><p>Typically, you would do this for data that is generated by other systems, relevant to the customer, but does not belong on the Salesforce platform. Particularly when there is a large amount of it, or a near real time requirement.</p><p>For more details on the differences between the two modes of operation, see <a href="https://omnata.com/how-it-works">https://omnata.com/how-it-works</a></p><p>And finally, for the visually inclined, here’s a decision tree to summarise:</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/0*2RzjzTsdm1K_--M3.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e86883467ac3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-and-salesforce-how-do-i-integrate-thee-let-me-count-the-ways-e86883467ac3">Snowflake and Salesforce, how do I integrate thee? Let me count the ways</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[Feature Engineering in Snowflake]]></title>
            <link>https://medium.com/omnata/feature-engineering-in-snowflake-4312032e0d53?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/4312032e0d53</guid>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Mon, 11 Jan 2021 04:38:20 GMT</pubDate>
            <atom:updated>2021-01-11T04:38:20.196Z</atom:updated>
            <content:encoded><![CDATA[<p>In a previous story, I demonstrated that a true cloud data warehouse is capable of handling common machine learning tasks for structured data, like training tree-based models.</p><p>The gist of my argument was that if the architecture is right and the use case is common, then you shouldn’t need to transfer the data out of your database into a general-purpose compute cluster. As a Snowflake user, your analytics workloads can take advantage of its <a href="https://docs.snowflake.net/manuals/user-guide/tables-clustering-micropartitions.html">micro-partitioning</a> to prune away a lot of of the processing, and the warmed-up, per-second-billed compute clusters are ready to step in for very short but heavy number-crunching tasks.</p><p>However, it’s often said that 80% of a data scientist’s time is spent feature engineering, and it’s almost always a requirement before you can train a model. Can this part be done in-database as well?</p><h3>What is feature engineering?</h3><p>Feature engineering is about transforming the input data so that it can be used by a machine learning algorithm. There is a lot of contextual meaning behind data that humans intuitively interpret using our vast experience and ability to generalize our learning. But despite some of the marketing material that’s out there, machines aren’t able to do this on their own just yet.</p><p>There are several reasons why some ML algorithms require their inputs to be “engineered”, but to illustrate with an example: let’s say two of your input variables are salary and age, and your target variable is the likelihood to purchase a product. Salary might range between $30,000 and $200,000 in your dataset, and age could be between 10 and 90. You the reader probably understand that an age difference of 20 years is a lot more significant than a salary difference of $20, but to an algorithm they are just numbers it needs to fit a curve through. So in order to do the best job, computers need you to allow for that, which in this case you could do by scaling (e.g. make salary and age both range between 0.0 and 1.0). You can also use techniques like binning (aka bucketing) to place values into one of a fixed number of value ranges (e.g. salary bands 0 to 6).</p><h3>Implementing in-database</h3><p>When you look at something like <a href="https://scikit-learn.org/stable/modules/classes.html#module-sklearn.preprocessing">scikit-learn’s preprocessing libraries</a>, the prospect of implementing them from scratch to run in-database may seem quite daunting, conjuring up images of thousands of lines of code dealing with low level numeric operations.</p><p>The reason I don’t find it daunting is that most of it is not actually from scratch. Conceptually, it’s more like implementing the top parts of a pyramid where a lot of the base level building blocks are already in place. For example, in my decision tree algorithm I used standard deviation reduction to calculate the tree splits. But I didn’t have to implement standard deviation from scratch; that’s a built-in Snowflake function, I just needed to plug it in at the right place. And it’s not just the aggregate functions that are useful; the window functions help with breaking up datasets, and the table functions are great for slicing and dicing.</p><p>The same principle should apply to feature engineering. And if you do find a missing building block or just want to wrap things up all nice and tidy, you can always be a good engineer and solve it in isolation as a Snowflake function that you then call as needed. Read on, and you’ll see I do this several times.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hQ8jgnQljyR4fVkV.png" /></figure><p>In this story, I’m going to work my way through the full list of scikit-learn preprocessing functions and show an equivalent Snowflake solution, using the sample data available to all Snowflake accounts.</p><p>Unless otherwise specified, the database is “SNOWFLAKE_SAMPLE_DATA” and the schema is “TPCDS_SF10TCL”.</p><p>If you’re running these queries in Tableau Desktop like I did, make sure to add a <strong>sample(500 rows)</strong> at the end, as some of these tables have billions of rows that will really crowd a chart.</p><h4>preprocessing.Binarizer</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/160/1*W6P5cKB_1y0N9tWz6Blg1A.gif" /></figure><p><em>Binarize data (set feature values to 0 or 1) according to a threshold</em></p><p>Nice easy one as a warm-up exercise, let’s binarize the customer_demographics dependents count. Assume it’s only relevant that you have dependents or not, and we don’t care how many.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/299/1*760dJf2TmR4EOyA5XpjWzg.png" /><figcaption>Before</figcaption></figure><pre>select iff(CD_DEP_COUNT&gt;0, true, false ) as CD_DEPENDANTS<br>from CUSTOMER_DEMOGRAPHICS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/254/1*o6XEHtW7IXJhrOi9bm1eWQ.png" /><figcaption>After</figcaption></figure><h4>preprocessing.FunctionTransformer</h4><p><em>Constructs a transformer from an arbitrary callable.</em></p><p>This one just lets you BYO transform function, so not really an algorithm as such. We know we can do the same with a stored proc or function.</p><h4>preprocessing.KBinsDiscretizer</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/160/1*zoMRkAfTt6XULoQDUsRBZw.gif" /></figure><p><em>Bin continuous data into intervals.</em></p><p>There are a couple of choices to make here in the scikit-learn function. The encoder parameter can be ‘onehot’, ‘onehot-dense’, or ‘ordinal’. I’ll go the easy route with ordinal but don’t worry, we have to implement one hot encoding later as its own function.</p><p>Similarly I’ll take a strategy of ‘uniform’ for simplicity, but I plan to tackle k-means in a future post.</p><p>Here’s the SS_LIST_PRICE from STORE_SALES:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/367/1*8mbMxpky9tvpDSgCgHoXRA.png" /><figcaption>Before</figcaption></figure><p>Placed into 20 equal width bins:</p><pre>with aggregates as (<br>  select min(SS_LIST_PRICE) as min_price,<br>  max(SS_LIST_PRICE) as max_price<br>  from STORE_SALES)<br>select width_bucket(&quot;SS_LIST_PRICE&quot;,min_price,max_price,20)<br>from aggregates,STORE_SALES<br>limit 100</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/425/1*rziADg3BUzA8CTNKVnL4YQ.png" /><figcaption>After</figcaption></figure><p>And here’s what the bins look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TKChT8t1Q3C6Au_m63c4kw.png" /></figure><h4>preprocessing.KernelCenterer</h4><p><em>Center a kernel matrix</em></p><p>I don’t know about you, but I don’t often encounter kernel matrixes in the context of relational databases, so it’s difficult to image how this could apply. Post a comment if you think I’ve let myself off the hook too easily here.</p><h4>preprocessing.LabelBinarizer</h4><p><em>Binarize labels in a one-vs-all fashion</em></p><p>This is so similar to OneHotEncoder that I won’t repeat myself here.</p><h4>preprocessing.LabelEncoder</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/125/1*r_tNIvr4uGkZD1kmo0mOWw.gif" /></figure><p><em>Encode labels with value between 0 and n_classes-1.</em></p><p>Here we make use of some of the cool array functions in Snowflake, pushing all the distinct values into an array with <strong>array_agg </strong>so that we can use the <strong>array_position </strong>function to encode the label.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/331/1*1lG67fwhrjuh05L4dp_znw.png" /><figcaption>Before</figcaption></figure><pre>with distinct_values as (<br>    select array_agg(distinct cd_marital_status) as marital_status_array from CUSTOMER_DEMOGRAPHICS)<br>select array_position(cd_marital_status::variant,marital_status_array) <br>from distinct_values,CUSTOMER_DEMOGRAPHICS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/422/1*TGGwP7S7kbfnrxEEn4F6xg.png" /><figcaption>After</figcaption></figure><h4>preprocessing.MultiLabelBinarizer</h4><p><em>Transform between iterable of iterables and a multilabel format.</em></p><p>The only way this would really apply is if we were storing multi-label data using Snowflake arrays. I doubt this would be common, but it’s not a crazy idea either. I’ll create an example to match the second one in the <a href="https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.MultiLabelBinarizer.html#sklearn.preprocessing.MultiLabelBinarizer">doco</a>.</p><p>Sample table creation:</p><pre>create or replace temporary table films as (<br>  select array_construct(&#39;sci-fi&#39;,&#39;thriller&#39;) as genres<br>  union<br>  select array_construct(&#39;comedy&#39;));<br>  <br>select * from films</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/287/1*0A2QyPutgHOZjRe543c_3Q.png" /></figure><p>We’ll also need a simple function that, given a complete array of possible values, tells us whether or not each value was present in a given subset.</p><pre>create or replace function array_element_matches(MASTER_ARRAY array,ELEMENTS array)<br>  returns array<br>  language javascript<br>as<br>$$<br>  return MASTER_ARRAY.map(function(master_element){<br>    for (var i=0;i&lt;ELEMENTS.length;i++){<br>      if (ELEMENTS[i]==master_element){<br>        return true;<br>      }<br>    }<br>    return false;<br>  });<br>$$<br>;</pre><p>The function is much easier to explain by demonstration:</p><pre>select array_element_matches(<br>   array_construct(&#39;a&#39;,&#39;b&#39;,&#39;c&#39;,&#39;d&#39;,&#39;e&#39;),<br>   array_construct(&#39;b&#39;,&#39;d&#39;)) as test</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*H18rZ5bGkX21xXjd138UOA.png" /></figure><p>With the function created, we can go ahead and make a multi-label binarizer for our films. First we flatten all the arrays into a single array and get a list of distinct values.</p><p>Then we can use the function to do the binarizing.</p><pre>with distinct_values as (<br>    select array_agg(distinct value::string) as all_genres_array from films<br>    ,lateral flatten(input =&gt; genres) g<br>    )<br>select all_genres_array,genres,array_element_matches(all_genres_array,genres) as genres_mlb<br>from distinct_values,films</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/746/1*LJDRh7U-H-esxeocGJjl6g.png" /></figure><p>Let me just explain the array_agg + lateral flatten part. When you call array_agg on array values, you get arrays containing arrays (e.g. [ [1,2],[3,4] ]). These are hard to work with, so we needed to flatten them all out into one big single array ([1,2,3,4]).</p><h4>preprocessing.MaxAbsScaler</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*SRAvyLWcOC1ttEMqtsLMnA.gif" /></figure><p><em>Scale each feature by its maximum absolute value.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5dgdlMXjbSwEn-BPzVaXKw.png" /><figcaption>Before</figcaption></figure><p>This is as simple as dividing each value by the overall maximum absolute value:</p><pre>with aggregates as (<br>  select max(abs(WS_LIST_PRICE)) as max_abs_dep<br>  from WEB_SALES)<br>select WS_LIST_PRICE,max_abs_dep,WS_LIST_PRICE / max_abs_dep AS WS_LIST_PRICE_SCALED<br>from aggregates,WEB_SALES</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qEVI6ryJobMSTUE6i-xquQ.png" /><figcaption>After</figcaption></figure><h4>preprocessing.MinMaxScaler</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*6OfuvwgnJjq6O96gnQqAfg.gif" /></figure><p><em>Transforms features by scaling each feature to a given range.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5dgdlMXjbSwEn-BPzVaXKw.png" /><figcaption>Before</figcaption></figure><p>This is similar to MaxAbsScaler, except we use the minimum and maximum values instead.</p><pre>with aggregates as (<br>  select min(CD_DEP_COUNT) as min_dep,max(CD_DEP_COUNT) as max_dep<br>  from CUSTOMER_DEMOGRAPHICS)<br>select (CD_DEP_COUNT - min_dep)<br>        / (max_dep - min_dep)<br>from aggregates,CUSTOMER_DEMOGRAPHICS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6JXIIydNh54AOZbdNpTX1g.png" /><figcaption>After</figcaption></figure><h4>preprocessing.Normalizer</h4><p><em>Normalize samples individually to unit norm.</em></p><p>There’s a divide by zero safeguard we need for this formula, so let’s wrap it in a function. For this example we’ll use the <a href="http://mathworld.wolfram.com/L2-Norm.html">L2 norm</a>.</p><pre>create or replace function normalize_scale(x float,y float)<br>  returns float<br>  language javascript<br>as<br>$$<br>  var magnitude=Math.sqrt(X**2 + Y**2);<br>  if (magnitude==0){<br>    return 0;<br>  }else{<br>    return X/magnitude;<br>  }<br>$$<br>;</pre><p>Here’s CD_DEP_EMPLOYED_COUNT and CD_DEP_COLLEGE_COUNT from CUSTOMER_DEMOGRAPHICS. They are discrete values rather than continuous, but you get the idea.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KGkPK4KgSwS4XtMKGT1WrQ.png" /><figcaption>Before</figcaption></figure><p>Then, to normalize CD_DEP_EMPLOYED_COUNT in relation to itself and CD_DEP_COLLEGE_COUNT:</p><pre>select <br>  normalize_scale(CD_DEP_EMPLOYED_COUNT,CD_DEP_COLLEGE_COUNT) as dep_employed_count_normalized,<br>  normalize_scale(CD_DEP_COLLEGE_COUNT,CD_DEP_EMPLOYED_COUNT) as dep_college_count_normalized<br>from CUSTOMER_DEMOGRAPHICS<br>limit 1000000</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LLeKzs7De2tfujLSaAewxw.png" /></figure><h4>preprocessing.OneHotEncoder</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/125/1*cwDfV750UFtpUPDCYRzaJg.gif" /></figure><p><em>Encode categorical integer features as a one-hot numeric array.</em></p><p>Whether you encode categories or labels is neither here nor there, it’s just an <strong>if</strong> statement. And since that would provide one feature per column, you can also just select the particular features you plan to use.</p><p>Let’s go back to CD_MARITAL_STATUS from CUSTOMER_DEMOGRAPHICS:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/326/1*B_eu2sZyRw6J40IBW0-eUg.png" /><figcaption>Before</figcaption></figure><pre>select <br>    iff(cd_marital_status=&#39;S&#39;,true,false) as MARITAL_STATUS_S,<br>    iff(cd_marital_status=&#39;D&#39;,true,false) as MARITAL_STATUS_D,<br>    iff(cd_marital_status=&#39;W&#39;,true,false) as MARITAL_STATUS_W,<br>    iff(cd_marital_status=&#39;U&#39;,true,false) as MARITAL_STATUS_U<br>from CUSTOMER_DEMOGRAPHICS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/951/1*HFG4Uhcc0QewpMcX-9kDkg.png" /><figcaption>After</figcaption></figure><p>Note that you could easily roll up labels here, e.g. treat divorced and widowed as the same feature.</p><h4>preprocessing.OrdinalEncoder</h4><p><em>Encode categorical features as an integer array.</em></p><p>There’s really no point to this in a relational database, it’s not like you’re paying by the column. If you have a fixed number of features in an array, just break them out into their own columns and encode them separately.</p><h4>preprocessing.PolynomialFeatures</h4><p><em>Generate polynomial and interaction features.</em></p><p>These can be done trivially by adding the calculated columns you’re planning to use (using multiplication, SQUARE, etc).</p><h4>preprocessing.PowerTransformer</h4><p><em>Apply a power transform featurewise to make data more Gaussian-like.</em></p><p>‘yeo-johnson’ or ‘box-cox’ are the two functions that can be used to build a normal distribution. While they aren’t too complex, there’s some parameters that need to be automatically chosen and this is an area where some building is required. Scikit-learn contains a lot of parameter optimization functions (in this case, Brent’s method), and this would be a big one to implement. Maybe in a future post?</p><h4>preprocessing.QuantileTransformer</h4><p><em>Transform features using quantiles information.</em></p><p>In a nutshell, the scikit-learn implementation:</p><ol><li>calculates a bunch of percentiles(1000 by default)</li><li>uses linear interpolation with these percentiles to map each value to a 0–1 range.</li></ol><p>I’ll try to visualise this first. Using the C_BIRTH_YEAR column of the CUSTOMER table, we can imagine the years spread out across the x axis.</p><p>Choosing 10 as a simple number of percentiles, we can calculate these and plot these on the y axis. The first dot (10%, or 0.1) shows the birth year you’d have to choose to get 10% of values to its left and 90% to its right. If you were doing them one at a time, you could use one of Snowflake’s PERCENTILE_* functions, but we need to generate them all in a query.</p><pre>-- Make a 0-percentile row containing the minimum value <br>-- (i.e. 100% of values fall to its right)<br>select 0 as quantile,(select min(SS_LIST_PRICE) from STORE_SALES) as quantile_value<br>union all<br>-- generate 10 percentile values (10% increments) and for each, determine the maximum value that  <br>-- will divide the dataset row counts by that percentage<br>select quantile,max(case when rownum/numrows &lt;= quantile then SS_LIST_PRICE end) as quantile_value<br>from <br>(<br>  select <br>  row_number() over (partition by null order by null) -1 as seq,<br>  0.1+(seq/10) as quantile<br>  from table(generator(rowcount =&gt; 10)) v <br>  order by 1<br>) quantiles,<br>(<br>  select SS_LIST_PRICE,<br>    row_number() over (partition by NULL order by SS_LIST_PRICE) as rownum,<br>    count(*) over (partition by NULL) as numrows<br>  from STORE_SALES sample(10000 rows)<br>  where SS_LIST_PRICE is not null<br>) totals<br>group by quantile<br>order by quantile_value</pre><p>Which gives us the list price percentiles:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/523/1*tdN9kxvqmo3enU9AkXIDzQ.png" /></figure><p>Now let’s bring it all together on a 2D chart:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XJv_CdSJTIrcFC2DRrhZTg.gif" /></figure><p>Once we have our percentiles calculated, it’s a matter of going through every SS_LIST_PRICE value across the x axis, determining which two blue dots (percentiles) are the closest either side of it, and then using the <a href="https://en.wikipedia.org/wiki/Linear_interpolation">Linear Interpolation</a> formula to determine its value on the y axis (the red question mark).</p><p>First, we need a Linear Interpolation formula (often known as “lerp” by the character-count conscious):</p><pre>create or replace function linear_interpolation(x1 float,y1 float,x2 float,y2 float, x float)<br>  returns float<br>  as<br>  $$<br>    y1 + ((x-x1)/(x2-x1)) * (y2-y1)<br>  $$<br>  ;</pre><p>Then we expand our query further to tie it all together:</p><pre>with quantile_values as(<br>  -- Make a 0-percentile row containing the minimum value (i.e. 100% of values fall to its right)<br>  select 0 as quantile,(select min(SS_LIST_PRICE) from STORE_SALES) as quantile_value<br>  union all<br>  -- generate 10 percentile values (10% increments) and for each, determine the maximum value that  <br>  -- will divide the dataset row counts by that percentage<br>  select quantile,max(case when rownum/numrows &lt;= quantile then SS_LIST_PRICE end) as quantile_value<br>  from <br>  (<br>    select <br>    row_number() over (partition by null order by null) -1 as seq,<br>    0.1+(seq/10) as quantile<br>    from table(generator(rowcount =&gt; 10)) v <br>    order by 1<br>  ) quantiles,<br>  (<br>    select SS_LIST_PRICE,<br>      row_number() over (partition by NULL order by SS_LIST_PRICE) as rownum,<br>      count(*) over (partition by NULL) as numrows<br>    from STORE_SALES sample(1000 rows)<br>    where SS_LIST_PRICE is not null<br>  ) totals<br>  group by quantile<br>  order by quantile_value<br>)<br>select SS_LIST_PRICE as x,<br>(select max(b.quantile) from quantile_values b where b.quantile_value&lt;a.SS_LIST_PRICE) as y1,<br>(select min(b.quantile) from quantile_values b where b.quantile_value&gt;=a.SS_LIST_PRICE) as y2,<br>(select max(b.quantile_value) from quantile_values b where b.quantile_value&lt;a.SS_LIST_PRICE) as x1,<br>(select min(b.quantile_value) from quantile_values b where b.quantile_value&gt;=a.SS_LIST_PRICE) as x2,<br>coalesce(CONSORTIUM_SHARING.MOBILE.linear_interpolation(x1,y1,x2,y2,x),0) as y<br>from STORE_SALES a sample(1000 rows)<br>where SS_LIST_PRICE is not null<br>order by SS_LIST_PRICE</pre><p>Note that the use of min &amp; max in the final query is not ideal from a performance point of view, which is why I had to sample the table this time. We could instead build our own User Defined Table Function that did a one-shot pass of the data, leveraging what we know about the order of its values. But what we have serves to illustrate for now, and we are left with a distribution from 0 to 1:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Xlt8m_y6EjWPHr5E_SO-Uw.png" /><figcaption>After</figcaption></figure><h4>preprocessing.RobustScaler</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*PRucAf-me7fNnHmz7NqfCQ.gif" /></figure><p><em>Scale features using statistics that are robust to outliers.</em></p><p>This method uses the interquartile range, so instead of min and max, we are splitting down the median then splitting again on each side.</p><p>In this example, we’ll scale WR_RETURN_AMT in the WEB_RETURNS table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*-cydOnp9OGtwYo6eAWGYcg.png" /><figcaption>Before</figcaption></figure><pre>with med as (select <br>  median(WR_RETURN_AMT) as median_ret_amt <br>    from WEB_RETURNS),<br>  lower_med as (select median(WR_RETURN_AMT) as lower_median <br>    from WEB_RETURNS, med where WR_RETURN_AMT&lt; median_ret_amt),<br>  upper_med as (select median(WR_RETURN_AMT) as upper_median <br>    from WEB_RETURNS, med where WR_RETURN_AMT&gt; median_dep)<br>select (WR_RETURN_AMT- lower_median) / (upper_median - lower_median) as wr_return_amount_scaled<br>from lower_med,upper_med,WEB_RETURNS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*wt_0YbWskwYaNmbHY6aGMw.png" /><figcaption>After</figcaption></figure><h4>preprocessing.StandardScaler</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*fnP_ZBN8bJv5UaGk5BOKCg.gif" /></figure><p><em>Standardize features by removing the mean and scaling to unit variance.</em></p><p>Using the same WEB_RETURNS column as previous example.</p><pre>with aggregates as (<br>  select avg(WR_RETURN_AMT) as avg_dep,<br>  stddev(WR_RETURN_AMT) as stddev_dep<br>  from WEB_RETURNS)<br>select (WR_RETURN_AMT - avg_dep)<br>        / (stddev_dep) as wr_return_amount_scaled<br>from aggregates,WEB_RETURNS</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*V3SycjgKQZoSBBYkgIzdBw.png" /></figure><h3>Conclusion</h3><p>There’s a few things I’ve learned by writing this.</p><h4>It works!</h4><p>I’d call this exercise a roaring success. There’s a bit of boilerplate if you’re writing them as queries, but if they were being issued by front-end applications or as push-down statements in code libraries then that wouldn’t be a problem. PowerTransformer is doable, but I’d wait until I needed it.</p><h4>Most of the functions are not scary</h4><p>The sliding and squashing mostly involves standard aggregations. It’s easy to forget this when you call them by a different name for a long time.</p><p>However, when moving from a imperative language like Python to a more declarative language like SQL , it does tend to call for a rethink instead of a direct port of the code.</p><h4>Tableau is a good sidekick</h4><p>Visualisations are the best reinforcement of what you’re trying to achieve. With Snowflake, you can easily do feature engineering on very large datasets without sampling, just remember it’s not a good idea to ask Tableau to put billions of dots on a chart. But sampling the final result is much better than sampling the initial input!</p><p>As always, comments and feedback welcome.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4312032e0d53" width="1" height="1" alt=""><hr><p><a href="https://medium.com/omnata/feature-engineering-in-snowflake-4312032e0d53">Feature Engineering in Snowflake</a> was originally published in <a href="https://medium.com/omnata">Omnata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bootstrapping at scale in Snowflake]]></title>
            <link>https://medium.com/omnata/bootstrapping-at-scale-in-snowflake-7f1b9ff74a61?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/7f1b9ff74a61</guid>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Mon, 04 Jan 2021 04:20:38 GMT</pubDate>
            <atom:updated>2021-01-04T04:20:38.583Z</atom:updated>
            <content:encoded><![CDATA[<p>Sometimes when statisticians are sampling data, they like to put each one back before grabbing the next. This is called random sampling <strong>with </strong>replacement, as opposed to random sampling <strong>without </strong>replacement, which is the more common thing.</p><p>An example of when this is done in the wild is during the training of a Random Forest, and this was the trigger for me writing this post.</p><p>All of the Snowflake built-in sampling functions revolve around sampling without replacement. This means you only have to make a decision about each row once — it’s either in or out according to chance.</p><p>The difficulty with bootstrapping is that it’s a bit like taking up cycling as a hobby; to do it correctly is rather expensive.</p><p>A naive, but totally accurate way to bootstrap 10,000 rows, would be:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/542f4aad5a919bc3cc6ec3b59f7ec6f6/href">https://medium.com/media/542f4aad5a919bc3cc6ec3b59f7ec6f6/href</a></iframe><p>This gives all 150,000 customers in the source table an equal chance of being picked each time, no matter how many times they’ve been selected already.</p><p>There must be a better way! The answer, of course, is that we need a “good enough” alternative. We’re sampling after all, so the level of accuracy is usually flexible if it means performance gains.</p><p>So let’s see if we can come up with a different approach.</p><p>The goal will be to bootstrap 60% of the 150,000 records in the “SNOWFLAKE_SAMPLE_DATA”.”TPCH_SF1&quot;.”CUSTOMER” table.</p><p>We’d like to do one iteration on the table (in the algorithmic sense), and therefore make a decision on each row just once in isolation if possible. We’ll need to come up with a way of selecting it potentially multiple times, but keeping within the parameters of the sample.</p><p>A simple modification, if we decided arbitrarily that ten sweeps of the table was good enough, then we could just pick 6% each time, like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/70b37f2212660159e690883468b7e836/href">https://medium.com/media/70b37f2212660159e690883468b7e836/href</a></iframe><p>This isn’t very good at all. For starters, we dealt with every row ten times, and we haven’t really quantified the inaccuracy at all, we just made up a number.</p><p>So let’s have a look at what happens for each row each time we do an iteration, and we’ll consider the two extremes: always getting selected, and never getting selected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*LcFW6ENK2aUet7ESjWX2wA.png" /></figure><p>After a certain number of iterations, inevitably there’s a point where (as we’d say in Australia, each record has had a “fair go”. So we need a way of drawing that line. For the purpose of this exercise, let’s allow the user to specify two things:</p><ol><li>The desired size of the sample, as a percentage of the original table</li><li>A percentage value, where we stop iterating when the probability of never being selected dips below it. Imagine this as a horizontal line you can place somewhere on the chart above.</li></ol><h3>Equation time!</h3><p>As a reminder, the goal is a 60% sample.</p><p>Forgetting the “with replacement” part for a second, the probability of a row getting selected every time:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/155/1*cUem2EGSf0qweORqqRAZUA.png" /></figure><p>Or of <strong>not </strong>getting selected, just flip it around:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/162/1*0V6mFpCTbOILmqDZxF9bsA.png" /><figcaption>1–0.6 = 0.4</figcaption></figure><p>The value on the right-hand side is what we want the user to specify. As you can see, it always decreases, and we will keep adding iterations until we reach it.</p><p>So we can express this generally, then solve it to figure out how many iterations we should do:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/527/1*lDlLKT0EG39guU8ctWHJJA.png" /></figure><p>Great, now we have a formula to use.</p><p>Now, let’s get back to Snowflake. Remember, we don’t actually want to do multiple iterations. But the tricky part is, there isn’t any way to make a row appear more than once in a result set….<strong><em>or is there?!?!?!</em></strong></p><h3>Enter UDTFs</h3><p>I really like <a href="https://docs.snowflake.net/manuals/sql-reference/udf-js-table-functions.html">UDTFs</a>, they have a way of enabling elegant solutions instead of awful boilerplate.</p><p>Check this one out:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/79d7602a88a6fad97c5bebe6d3a2db45/href">https://medium.com/media/79d7602a88a6fad97c5bebe6d3a2db45/href</a></iframe><p>We can use this to kill two birds with one stone. First of all, it will apply that formula to work out a good number of iterations (i.e. number of chances to be selected) based on that threshold we invented.</p><p>Then, it is courteous enough to divide the sample fraction by that number, and write out that number of rows.</p><p>To demonstrate, the UDTF output on its own looks like this:</p><pre>select * from table(SAMPLE_PROBABILITY(0.6::double,0.00001::double))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/242/1*a1anLuki6chZBuaPt8em9g.png" /></figure><p>The formula says we should do 13 iterations, and 0.6/13 = 0.04615384615</p><p>In other words, we will give each row 13 chances at 0.04615384615 probability per try.</p><p>All we need to do is join to this UDTF like so:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6f8c0f5745b6823a8f149dac43d499a2/href">https://medium.com/media/6f8c0f5745b6823a8f149dac43d499a2/href</a></iframe><p>The raw result of:</p><pre>SELECT customer.*<br>FROM &quot;SNOWFLAKE_SAMPLE_DATA&quot;.&quot;TPCH_SF1&quot;.&quot;CUSTOMER&quot; customer,table(SAMPLE_PROBABILITY(0.6::double,0.00001::double))</pre><p>is actually 150,000 * 13 = 1,950,000 rows, but then we add the WHERE clause:</p><pre>where uniform(0::float, 1::float, random()) &lt; SAMPLE_PROBABILITY</pre><p>This generates a random number between 0.0 and 1.0 and removes those rows below the threshold.</p><p>A 100% correct implementation should give us 90,000 rows (150,000 * 0.6). This one (with the inputs I chose) seems to yield somewhere between 89,000 and 91,000 each time.</p><p>And of course, just as we wanted, there are the chance of duplicates. You can even see one in the first page of results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*syy0tzJ1uDPoVkJBJ6tTOA.png" /></figure><p>And on our 150,000 row table, to return the ~90k records, is a little over a second on an x-small warehouse.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7f1b9ff74a61" width="1" height="1" alt=""><hr><p><a href="https://medium.com/omnata/bootstrapping-at-scale-in-snowflake-7f1b9ff74a61">Bootstrapping at scale in Snowflake</a> was originally published in <a href="https://medium.com/omnata">Omnata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Generating realistic-looking fake data in Snowflake]]></title>
            <link>https://medium.com/omnata/generating-realistic-looking-fake-data-in-snowflake-9d76c34b326e?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/9d76c34b326e</guid>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Sun, 03 Jan 2021 23:56:54 GMT</pubDate>
            <atom:updated>2021-01-03T23:56:54.108Z</atom:updated>
            <content:encoded><![CDATA[<p>At <a href="https://omnata.com/salesforce_snowflake.html">Omnata</a>, we love to watch our prospects’ amazement as we demo the use of Salesforce to navigate through, and automate on, huge quantities of data in real-time from Snowflake, linked to individual records in their CRM. If you’re using both Snowflake and Salesforce and want to combine their strengths, you owe it to yourself to check us out!</p><p>Back to the topic though, it’s always nice to make the sample data look familiar and relatable. However, generating 100 million fake things (names, addresses, whatever) and importing them into Snowflake can be time consuming and error-prone.</p><p>Enter Flaker!</p><p>Flaker is a <a href="https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws.html">Snowflake External Functions</a> wrapper for the popular <a href="https://github.com/joke2k/faker">Faker</a> python library. It means you can generate a vast array of fake data right from within Snowflake, in large quantities, very quickly and easily.</p><h3>What kinds of data can it generate?</h3><p>The full list of standard Faker providers is here: <a href="https://faker.readthedocs.io/en/master/providers.html">https://faker.readthedocs.io/en/master/providers.html</a></p><h3>Examples</h3><p>500 fake names, in the US English locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*s9GlEOH32GfLmELM0TwJbA.png" /></figure><p>10 million addresses, in the Japanese locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*dyuFtaBvPcVx9sX5mPOBkg.png" /></figure><h3>Getting Started</h3><p>Instructions to deploy the external functions can be found in the Github repo: <a href="https://github.com/jamesweakley/flaker">https://github.com/jamesweakley/flaker</a></p><h3>Performance / cost</h3><p>To be honest, I haven’t tried to optimize it much yet because it’s already good enough for us. You can adjust the Lambda memory size in the serverless.yml file, and you can adjust the batch size in the Snowflake function definition.</p><p>Here’s a screenshot of the profile for the above 10M Japanese address generation running on an XS warehouse, a Lambda memory size of 1024MB, and the Snowflake function with a MAX_BATCH_ROWS of 100000:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*49gfmEwuTQc8FGx4gI37rQ.png" /></figure><p>So it sent 2442 requests to the API gateway, with an overall execution time of 5 minutes 20 seconds.</p><p>The Lambda cost for this was about 4c in USD, but don’t forget there’s a free usage tier of 1M requests and 400,000 GB-seconds per month. So you’d have to work pretty hard to get to the point where you’re paying for the AWS side.</p><h3>Next Steps</h3><ul><li>Checkout <a href="http://developers.snowflake.com/">developers.snowflake.com</a></li><li>checkout <a href="http://github.com/snowflake-labs">Snowflake Labs</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9d76c34b326e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/omnata/generating-realistic-looking-fake-data-in-snowflake-9d76c34b326e">Generating realistic-looking fake data in Snowflake</a> was originally published in <a href="https://medium.com/omnata">Omnata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Generate Fake Data on-the-fly with External Functions on Snowflake]]></title>
            <link>https://medium.com/snowflake/generating-realistic-looking-fake-data-in-snowflake-80796c77adb3?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/80796c77adb3</guid>
            <category><![CDATA[aws-lambda]]></category>
            <category><![CDATA[serverless]]></category>
            <category><![CDATA[aws-api-gateway]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Wed, 07 Oct 2020 14:30:41 GMT</pubDate>
            <atom:updated>2020-10-23T16:55:05.941Z</atom:updated>
            <content:encoded><![CDATA[<h3>Generating realistic-looking fake data in Snowflake</h3><p>At <a href="https://omnata.com/salesforce_snowflake.html">Omnata</a>, we love to watch our prospects’ amazement as we demo the use of Salesforce to navigate through, and automate on, huge quantities of data in real-time from Snowflake, linked to individual records in their CRM. If you’re using both Snowflake and Salesforce and want to combine their strengths, you owe it to yourself to check us out!</p><p>Back to the topic though, it’s always nice to make the sample data look familiar and relatable. However, generating 100 million fake things (names, addresses, whatever) and importing them into Snowflake can be time consuming and error-prone.</p><p>Enter Flaker!</p><p>Flaker is a <a href="https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws.html">Snowflake External Functions</a> wrapper for the popular <a href="https://github.com/joke2k/faker">Faker</a> python library. It means you can generate a vast array of fake data right from within Snowflake, in large quantities, very quickly and easily.</p><h3>What kinds of data can it generate?</h3><p>The full list of standard Faker providers is here: <a href="https://faker.readthedocs.io/en/master/providers.html">https://faker.readthedocs.io/en/master/providers.html</a></p><h3>Examples</h3><p>500 fake names, in the US English locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*s9GlEOH32GfLmELM0TwJbA.png" /></figure><p>10 million addresses, in the Japanese locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*dyuFtaBvPcVx9sX5mPOBkg.png" /></figure><h3>Getting Started</h3><p>Instructions to deploy the external functions can be found in the Github repo: <a href="https://github.com/jamesweakley/flaker">https://github.com/jamesweakley/flaker</a></p><h3>Performance / cost</h3><p>To be honest, I haven’t tried to optimize it much yet because it’s already good enough for us. You can adjust the Lambda memory size in the serverless.yml file, and you can adjust the batch size in the Snowflake function definition.</p><p>Here’s a screenshot of the profile for the above 10M Japanese address generation running on an XS warehouse, a Lambda memory size of 1024MB, and the Snowflake function with a MAX_BATCH_ROWS of 100000:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*49gfmEwuTQc8FGx4gI37rQ.png" /></figure><p>So it sent 2442 requests to the API gateway, with an overall execution time of 5 minutes 20 seconds.</p><p>The Lambda cost for this was about 4c in USD, but don’t forget there’s a free usage tier of 1M requests and 400,000 GB-seconds per month. So you’d have to work pretty hard to get to the point where you’re paying for the AWS side.</p><h3>Next Steps</h3><ul><li>Checkout <a href="http://developers.snowflake.com/">developers.snowflake.com</a></li><li>checkout <a href="http://github.com/snowflake-labs">Snowflake Labs</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=80796c77adb3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/generating-realistic-looking-fake-data-in-snowflake-80796c77adb3">Generate Fake Data on-the-fly with External Functions on Snowflake</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[Generating realistic looking fake data in Snowflake]]></title>
            <link>https://medium.com/@jamesweakley/generating-realistic-looking-fake-data-in-snowflake-aab18cc95c7?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/aab18cc95c7</guid>
            <category><![CDATA[snowflakedb]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[serverless]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Thu, 01 Oct 2020 06:00:34 GMT</pubDate>
            <atom:updated>2020-10-01T06:00:34.938Z</atom:updated>
            <content:encoded><![CDATA[<p>At <a href="https://omnata.com/salesforce_snowflake.html">Omnata</a>, we love to watch our prospects’ amazement as we demo the use of Salesforce to navigate through, and automate on, huge quantities of data in real time from Snowflake, linked to individual records in their CRM. If you’re using both Snowflake and Salesforce and want to combine their strengths, you owe it to yourself to check us out!</p><p>Back to the topic though, it’s always nice to make the sample data look familiar and relatable. However, generating 100 million fake things (names, addresses, whatever) and importing them into Snowflake can be time consuming and error prone.</p><p>Enter Flaker!</p><p>Flaker is a <a href="https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws.html">Snowflake External Functions</a> wrapper for the popular <a href="https://github.com/joke2k/faker">Faker</a> python library. It means you can generate a vast array of fake data right from within Snowflake, in large quantities, very quickly and easily.</p><h3>Examples</h3><p>500 fake names, in the US English locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*s9GlEOH32GfLmELM0TwJbA.png" /></figure><p>10 million addresses, in the Japanese locale:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*dyuFtaBvPcVx9sX5mPOBkg.png" /></figure><h3>Getting Started</h3><p>Instructions to deploy the external functions can be found in the Github repo: <a href="https://github.com/jamesweakley/flaker">https://github.com/jamesweakley/flaker</a></p><h3>Performance / cost</h3><p>To be honest, I haven’t tried to optimise it much yet because it’s already good enough for us. You can adjust the Lambda memory size in the serverless.yml file, and you can adjust the batch size in the Snowflake function definition.</p><p>Here’s a screenshot of the profile for the above 10M Japanese address generation running on an XS warehouse, a Lambda memory size of 1024MB, and the Snowflake function with a MAX_BATCH_ROWS of 100000:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*49gfmEwuTQc8FGx4gI37rQ.png" /></figure><p>So it sent 2442 requests to the API gateway, with an overall execution time of 5 minutes 20 seconds.</p><p>The Lambda cost for this was about 4c in USD, but don’t forget there’s a free usage tier of 1M requests and 400,000 GB-seconds per month. So you’d have to work pretty hard to get to the point where you’re paying for the AWS side.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aab18cc95c7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Middleware is dead. Long live the cloud data platform!]]></title>
            <link>https://medium.com/omnata/middleware-is-dead-long-live-the-cloud-data-platform-cb34f4f88feb?source=rss-4e334e6e2130------2</link>
            <guid isPermaLink="false">https://medium.com/p/cb34f4f88feb</guid>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[data-warehousing]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[middleware]]></category>
            <category><![CDATA[snowflake-computing]]></category>
            <dc:creator><![CDATA[James Weakley]]></dc:creator>
            <pubDate>Sun, 06 Sep 2020 11:50:10 GMT</pubDate>
            <atom:updated>2021-01-04T04:17:00.889Z</atom:updated>
            <content:encoded><![CDATA[<p>In this cloud era, not only are new possibilities continually being created, but existing activities become easier and less expensive. Whenever a new paradigm emerges, those products not only disrupt their direct competitors, but often enable a wave of change in adjacent markets.</p><p>I’ll be upfront; this is one of those architecture articles that ends with a product that: a) enables the solution to solving the problem it describes, and b) the author happens to have a stake in. But in my defense, the article wasn’t written retrospectively to promote the product, the product was built to help define a new category of SaaS-native integration, and has a natural fit in the new world of the cloud data platform.</p><p>With that said, let’s open with a couple of bold statements.</p><p>First, unless you have an extremely rare flavor of production database, you are running out of excuses to not have reliable, low latency data pipelines feeding an elastic cloud warehouse like Snowflake. Why? Well, the mechanism has been commoditized, the price of entry is low, and every business needs to be data driven to at least some extent.</p><p>Second, you should be running your business apps in public cloud. Ideally SaaS, like Salesforce. Why? This lets you outsource the common traditional system administration tasks, and allows your technology teams to focus on improving the way your business operates.</p><p>If you’re on board with both those statements, you’re in good company:</p><ol><li>The cloud data replication market has been continuing to heating up the past few years, with Stitch and Alooma being acquired by Talend and Google respectively, Fivetran going on to become a unicorn, and Amazon and Microsoft continuing to develop their own offerings.</li><li>According to Gartner, around 75% of CRM spend has been on SaaS offerings last year, and trending upward.</li></ol><p>So with a centralized, highly available and scalable data platform running right alongside your CRM in the cloud, why should you be brokering an integration back to all the various source systems with an expensive, unnecessary extra piece of infrastructure?</p><p>The good news is that you don’t need to anymore. With Omnata’s new <a href="https://omnata.com/salesforce_snowflake.html">Snowflake for Salesforce</a> product, the whole Salesforce platform can retrieve data directly from Snowflake, down to the individual record among billions of others.</p><p>It’s really quick and easy to get started by installing a trial from the AppExchange, just follow the link on the <a href="https://omnata.com/salesforce_snowflake.html">product page</a>.</p><p>P.S. This pattern is coming to other cloud data warehouses and other SaaS applications soon, so stay tuned!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cb34f4f88feb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/omnata/middleware-is-dead-long-live-the-cloud-data-platform-cb34f4f88feb">Middleware is dead. Long live the cloud data platform!</a> was originally published in <a href="https://medium.com/omnata">Omnata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>