<?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 Jacques on Medium]]></title>
        <description><![CDATA[Stories by Jacques on Medium]]></description>
        <link>https://medium.com/@jacques-n?source=rss-34d9a7787247------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*w9VzK9NxaCCFn6cfnWE_kg.png</url>
            <title>Stories by Jacques on Medium</title>
            <link>https://medium.com/@jacques-n?source=rss-34d9a7787247------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 18:14:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jacques-n/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[Lakehouse Format Trends to Watch in 2024]]></title>
            <link>https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/6c8d0730bf1c</guid>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[delta-lake]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Wed, 31 Jan 2024 05:00:08 GMT</pubDate>
            <atom:updated>2024-01-31T13:25:23.532Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KpdPqTZ2Xe_lgYSM2ISniw.png" /></figure><h3>Lakehouse Format Rundown pt3: Trends to Watch</h3><p>This is part 3 of our lakehouse format rundown. We started by covering <a href="https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428">engines &amp; gorillas</a> and then drilled into <a href="https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3">community dynamics</a>. Remember, all data used in this series is <a href="https://tableformats.sundeck.io/">openly available here</a>.</p><h3>The Rise of Metadata Translation?</h3><p>In March 2023, the OneHouse team (some of the people behind Hudi) announced <a href="https://onetable.dev/">OneTable</a> (now renamed <a href="https://cwiki.apache.org/confluence/display/INCUBATOR/XTable+Proposal">XTable</a>). Databricks cloned that strategy with the <a href="https://www.databricks.com/blog/delta-uniform-universal-format-lakehouse-interoperability">announcement of UniForm</a> in August. Snowflake more recently announced Iceberg native tables. All three of these technologies could best be described as metadata translation techniques. (For those that might question this wrt Snowflake, review this <a href="https://vinodhini-sd.medium.com/iceberg-tables-on-snowflake-design-considerations-and-life-of-an-insert-query-5026ea10bbea">in-depth technical blog</a> for more background.) XTable tries to bi-directionally translate between all formats. UniForm attempts to uni-directionally translate from Delta Lake to Iceberg (and Hudi in the future). Snowflake does one-time imports from Iceberg to Snowflake Iceberg and can expose read-only Iceberg tables. These techniques are built on the fact that nearly all the actual data behind these formats is Apache Parquet. Unfortunately, these technologies are more about marketing than a fundamentally sound architecture. Key things to keep in mind:</p><ul><li>It’s usually better to say “yes we support that” and hope users won’t notice the <a href="https://docs.databricks.com/en/delta/uniform.html#limitations">fine print</a>.</li><li>If you can’t beat them, join them. (For example, Delta Lake has less engine support than Iceberg so by generating extra Iceberg metadata, Delta Lake can claim broader engine support.)</li></ul><p>All three technologies suggest that table format choice isn’t important as long as you pick their preferred format. Unfortunately, these technologies suffer from real semantic differences between the formats. Yes, the basic features are generally the same. However, many features don’t map 1:1. This means metadata translation solutions come with the following limitations:</p><ul><li>You can’t use the best features of the target format.</li><li>You can’t use the best features of the source format.</li><li>Writes are format-locked. For example, in UniForm you can only write using Delta Lake. In Snowflake, you can only write using Snowflake.</li></ul><p>Furthermore, these “translation” initiatives are competitive in the same way the formats are, with each initiative only backed by people from one of the communities (and the competing community not embracing support of translation). At the end of the day, coexistence is something that you probably want to use only for migrations and exceptional circumstances. IOW, don’t pin big hopes on metadata translation.</p><h3>Iceberg Catalog Fragmentation, a Mess for Users</h3><p>One of the strengths of Iceberg is vendor support. That support comes with a massive asterisk. While most tools support Iceberg, each tool has its own generally incompatible catalog. Practically, that means engine support is much hazier than the high level numbers above describe.</p><h4>What’s an Iceberg Catalog</h4><p>Iceberg defines a system that uses <a href="https://iceberg.apache.org/spec/">well-specified files</a> to describe a table. These files have names like metadata, manifest list and manifest. The Iceberg specification defines how these files are created and versioned over time. A well-defined specification gives us a nice outcome: Iceberg-compatible technologies work consistently at this level. 🎉</p><p>To manage these files, Iceberg also needs a service to keep of track of things including:</p><ul><li>A list of available tables.</li><li>Which subset of files correspond to the current active version of each table.</li><li>Arbitrate which process wins when multiple processes want to make conflicting changes to a table.</li></ul><p>An Iceberg catalog provides these capabilities.</p><h4>Extensibility as Liability…</h4><p>Building a catalog service is easy. Too easy. It turns out that everyone implemented their own. Why did people build? The creators of Iceberg never provided a recommended reference catalog implementation and the Iceberg spec has no reference to catalogs nor how they should work (behavior, api, etc). Some of this comes out of the fact that Iceberg came out of Netflix and they had their own internal proprietary catalog. However noble the reason, the result is there are nearly a dozen different ways to read an Iceberg table. This includes seven distinct commercial offerings.</p><p><em>For those that are interested, Delta Lake also failed to specify catalog behavior within the specification, and it has far fewer tools that support writing. This also mean fewer catalog solutions. The vast majority of Delta Lake users are Databricks users and use the Databricks built-in catalog (Unity).</em></p><p>Having choice only helps users when things are compatible. While engines each work with Iceberg, their support of individual catalogs are mixed. Below is a list of top tools and which Iceberg catalogs are supported.</p><figure><a href="https://tableformats.sundeck.io/"><img alt="" src="https://cdn-images-1.medium.com/max/503/1*q9rX3gsz0RhcjLBbq5PchA.png" /></a><figcaption>Iceberg catalog support is heavily fragmented. Click through to see <a href="https://tableformats.sundeck.io/">additional underlying data</a>.</figcaption></figure><p>As you can see, when we move from “supports Iceberg” to “supports catalog X”, things get ugly. Catalog extensibility has become a liability. The good news is the project finally defined a <a href="https://github.com/apache/iceberg/blob/main/open-api/rest-catalog-open-api.yaml">standard network protocol</a> for catalog operations. Unfortunately, there has been little adoption, it isn’t part of the Iceberg spec and there is no reference implementation. To really be succesful, Iceberg catalog fragmentation needs to go away. Otherwise, we just suffer from <a href="https://xkcd.com/927/">XKCD 927</a>.</p><h3>Snowflake as serious lakehouse competitor to Databricks?!</h3><p>Snowflake made a lot of movement in the lakehouse space in 2023. Databricks’ most performant and default storage option has been an open format for five years. Until recently, Snowflake always recommended their proprietary internal format to achieve maximum performance. In 2023, things changed. Snowflake announced native Iceberg tables with <a href="https://www.snowflake.com/blog/unifying-iceberg-tables/">benchmarks</a> showing equivalent performance for Iceberg tables and Snowflake’s proprietary tables. This shift reinforces the end-user desire for open lakehouse formats. While there is much to celebrate, Snowflake still lags Delta Lake (&amp; Databricks) in several critical ways.</p><h4>Snowflake Doesn’t Default to Open</h4><p>Whereas Databricks always writes open formats by default, Snowflake doesn’t. All tables in Snowflake are in the native proprietary format, unless you actively define each table as Iceberg on creation. That means the default pattern in Snowflake continues to be import and export to work with other tools. Many people we’ve talked to see import as one of their biggest Snowflake cost challenges. With open table formats, this can be largely avoided but with the defaults as they are (and write problem, below), this still needs improvement.</p><h4>Snowflake Doesn’t Play Well with Writes</h4><p>Snowflake lags Databricks on write compatibility. Databricks Delta Lake tables can be written by many tools. Snowflake forces users to make a choice, at table creation time, about who gets to write.</p><ul><li>Option 1: Snowflake can write, other tools cannot (Snowflake Catalog)</li><li>Option 2: Other tools (Spark, Flink, Trino, Dremio, etc) can write but Snowflake cannot. Estimated to be half the performance of Option 1, per Snowflake’s self-published benchmarks.</li></ul><p>In other words, Snowflake still forces users to make an impossible choice when evaluating how to approach writes. For example, Snowflake’s best solution for Iceberg import is: write to a non-Snowflake catalog first; then to edit in Snowflake, have Snowflake do a “table takeover” using metadata translation (with all the constraints/limitations mentioned above). While this can provide the best of both worlds: fast import, native Snowflake support — it’s nasty and falls well short of the promise.</p><p><em>Side note: There have been some public </em><a href="https://www.linkedin.com/feed/update/urn:li:activity:7057006876982452224?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7057006876982452224%2C7057051561750884352%29&amp;replyUrn=urn%3Ali%3Acomment%3A%28activity%3A7057006876982452224%2C7057057074203029505%29&amp;dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287057051561750884352%2Curn%3Ali%3Aactivity%3A7057006876982452224%29&amp;dashReplyUrn=urn%3Ali%3Afsd_comment%3A%287057057074203029505%2Curn%3Ali%3Aactivity%3A7057006876982452224%29"><em>offhand</em></a><em> </em><a href="https://youtu.be/1-APptl8URs?si=8zJOBR5KOoyJOTHC&amp;t=1181"><em>comments</em></a><em> that suggest Snowflake will start supporting external writes for the Snowflake catalog, but no strong public commitment or timeline has been given.</em></p><h4>Snowflake Has Zero Core Iceberg Contributors</h4><p>None of the top Iceberg contributors work for Snowflake. This is unfortunate. Snowflake is at the mercy of the project community in terms of direction and compatibility. This okay in the near-term, since Iceberg wants/needs Snowflake’s backing to compete with the Delta Lake marketing engine. However, the lack of engagement and contribution call into question Snowflake’s true commitment to placing the Iceberg standard on equal (or better) footing with Snowflake’s legacy format.</p><h3>Guidance for 2024</h3><p>If you want maximum engine support and minimum risk of vendor project manipulation, your best option is Iceberg. It isn’t perfect but is probably the most defensible choice. You should expect to switch catalogs and/or use multiple catalogs for at least the next year. The biggest argument against Iceberg is the lack of full-throated product support from a gorilla. Snowflake showed signs of being that voice in 2023, but appears to still be keeping at least one foot in their legacy proprietary format. If Snowflake truly commits, Iceberg improves their bus factor and catalog APIs are consolidated, this choice becomes a no-brainer. We should finally be able to choose the tool we want instead of the tool that has access to our data, and truly start working towards a fairhouse. If these deficits continue to cast a shadow, we’re likely in for a long table-format winter.</p><h3>Missed the Rest?</h3><p>Don’t miss part 1 of this series where we review <a href="https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428">engine support and gorilla bets</a> and part 2 of this series where we <a href="https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3">analyze community health</a>. All data used in this series is <a href="https://tableformats.sundeck.io/">openly available</a>.</p><p><em>Research was done based on a review of available public content as well as discussions with engineers and product folks at several companies, including AWS, Microsoft, Databricks, Snowflake, Tabular, OneHouse and Dremio. If you see an error, assume good intent and open a PR </em><a href="https://github.com/sundeck-io/tableformats"><em>against the GitHub</em></a><em> project with references. I hope that this series and backing data can be a living neutral document that represents an accurate capability matrix, something that doesn’t seem to exist at this point.</em></p><p><em>It’s also important to disclose that a couple years ago I worked on Iceberg and related technologies. That means I have better familiarity with that technology. That being said, I have no horse in this race other than a desire for consolidation and community focus. Watching OSS projects fight and duplicate each other’s features at the behest of competing vendors is almost always bad for users. Blurring distinctions to trick people is even worse.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6c8d0730bf1c" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c">Lakehouse Format Trends to Watch in 2024</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Lakehouse Format Rundown: $ Influence & Control]]></title>
            <link>https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/e799e1fcced3</guid>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[delta-lake]]></category>
            <category><![CDATA[redshift]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Wed, 31 Jan 2024 04:59:57 GMT</pubDate>
            <atom:updated>2024-01-31T12:59:49.703Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VGZjg9TKemHkEmXlb1lwpg.png" /></figure><h3>2024 Lakehouse Format Rundown pt2: Corporate Influence &amp; Community Health</h3><p><em>This is part 2 of a 3-part series analyzing the current state of lakehouse formats. Don’t miss part 1 where we analyzed </em><a href="https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428"><em>engine &amp; vendor support</em></a><em> and part 3 where we look at </em><a href="https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c"><em>key developing trends</em></a><em>. All data used in this series is </em><a href="https://tableformats.sundeck.io/"><em>openly available</em></a><em>.</em></p><h3>Corporate Influence and Bus Factor</h3><p>When thinking about long term technology choices it is important to evaluate the nature of the open source you’re considering. While GitHub stars and forks are easy to use, they are also easy to game and are not a good way to think about the security of your OSS use. What is more important is how the core heart and soul of the project works. While all 3 table formats will talk about the large number of total contributors (444 for Iceberg, 283 for Delta and 431 for Hudi as of this writing), the reality is that most open source projects are largely driven by a small core contingent. To analyze these contributions, you want to focus on the core codebase for each. For this analysis, we decided to focus on the core format and implementations and look at key contributors: those that contributed at least 1% of the code in those core parts of the project.</p><h3>Hudi Has Good Diversity; Delta Lake has bad Diversity; Iceberg is somewhere in the middle</h3><p>Iceberg and Hudi are Apache Software Foundation projects. Ideally, that means that those projects are well insulated from corporate control &amp; influence. Practically, history has shown that the ASF is slow to respond to corporate control (Spark, Kafka, Hadoop and Cassandra all stand as stark examples where corporate interest ran amok for far too long). Delta Lake is run as part of the Linux Foundation, which is generally less sensitive to issues around corporate control. That means the best security one can have around projects is looking at key contributors and their employment affiliation. This allows one to evaluate each project’s balance between corporate control and community diversity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KM4CWApxktn8Q1TOusEHxQ.png" /><figcaption>Key contributors (&gt;1% of core code) by their Affiliation</figcaption></figure><p>It’s hard to sugarcoat the result. As critical components to an open data architecture, we’d like to see less than 25% of key contributors being backed by the enterprise software companies trying to profit off these formats. Only Hudi achieves this benchmark. Iceberg is below where it needs to be and the numbers for Delta Lake are rough. If you care about community diversity and protection from open source vendor abuse, you probably want to avoid Delta Lake. The top 30 key contributors are all on Databricks’ payroll.</p><h3>Iceberg Development is too Centralized</h3><p>Open source community health isn’t just about how many corporations are backing work. It is also about the distribution of code ownership. Do the initial creators write and make decisions about the vast majority of the code? Even though project creators are often benevolent, centralized responsibility is a substantial project consumption risk. At the end of the day, contributors are people. They can have bias, vendettas, hangups, ulterior motives, personality conflicts, etc. When responsibility is well distributed, these people risks pose little project risk. When the ownership is centralized, the risks of projects going sideways increase. The best way to estimate this potential for outsized impact is to look at the codebases of each technology and look at who works on the core code of the project.</p><p>In open source projects, there are often all sorts of tertiary add-ons that the core community is less interested in. This is especially true when projects are competing to appear diverse and active (very common in this arena). We shouldn’t let this distract us for who is making the important decisions. With some command-line fu, we find the following data:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*Ii-TKhkLl-R6ynnrbIQ_iQ.png" /><figcaption>The compression in the Iceberg curve above suggests less distribution of responsibility</figcaption></figure><p>As you can see, the Iceberg code coverage is a much steeper curve. Two developers own more than 50% of the core codebase. These two developers also work for Tabular, the primary vendor “selling” Iceberg. The chart below further quantizes the data above.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WLWCINVK7rbWYj9y8B_7TA.png" /><figcaption>Number of developers per project to achieve different coverage percentiles</figcaption></figure><p>Not only does Iceberg have more centralized code ownership at the 50% benchmark, it doesn’t get that much better at the 75% level. On the flipside, Delta Lake and Hudi have very good ownership distribution. It’s impressive that Iceberg has achieved the heights it has with the skeleton crew that are driving the core and we should thank those hard working developers (just like we should thank all hard working OSS contributors). At the same time, we also need those few people in charge to stay healthy and friendly. (The latter often being the biggest community risk).</p><p><em>Note: While lack of distributed responsibility often leads to a dictatorship, it doesn’t guarantee that outcome. Apache Arrow, where </em><a href="https://github.com/apache/arrow/commits?author=wesm"><em>Wes</em></a><em> </em><a href="https://arrow.apache.org/blog/2017/05/08/0.3-release/"><em>dominated contributions</em></a><em> for a long time is a great example of a centralized BDFL community becoming decentralized.</em></p><h3>Conclusion</h3><p>As you can see, community metrics are generally one of the weakest points of the table format ecosystem. Hudi has the healthiest community but sits a distant third in terms of engine and vendor support (see part 1 of this series). Our best hope is that the Iceberg community endeavors to broaden influence on the core codebase. This at least seems more likely than Databricks embracing a decentralized approach to Delta Lake’s technology direction.</p><h3>Continued: Key Trends and Looking to the Future</h3><p>Don’t miss part 3 of this series where we look at <a href="https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c">key developing lakehouse format trends</a>. Also look back at <a href="https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428">engine and vendor support</a> if you missed that. All data used in this series is <a href="https://tableformats.sundeck.io/">openly available</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e799e1fcced3" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3">Lakehouse Format Rundown: $ Influence &amp; Control</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[2024 Lakehouse Format Rundown]]></title>
            <link>https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/7edd75015428</guid>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[delta-lake]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Wed, 31 Jan 2024 04:59:48 GMT</pubDate>
            <atom:updated>2024-01-31T22:11:10.924Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hrEeCjo6ZNxYdAyhD2s4Dg.png" /></figure><h3>2024 Lakehouse Format Rundown: Engines &amp; Gorillas</h3><p><em>This is part 1 of a 3 part series focused on looking at the current state of lakehouse formats. Don’t miss part 2 where we </em><a href="https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3"><em>analyze community health</em></a><em> and part 3 where we look at </em><a href="https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c"><em>key trends</em></a><em>. All data used in this series is </em><a href="https://tableformats.sundeck.io/"><em>openly available</em></a><em>.</em></p><p>A few weeks ago, I started my regular review on the current state of table formats across a broad range of analytical engines. This work is part of my continued desire to drive us towards a Fairhouse, a future where end users can combine storage, compute and language/interface to best suit their application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WdToempcAwf2Fr1ePHNjlg.png" /><figcaption>Warehouse =&gt; Lakehouse =&gt; Fairhouse</figcaption></figure><p>You can learn more about the Fairhouse vision from my <a href="https://www.youtube.com/watch?v=YXnoBUQP258">Rethinking DBMS Composability</a> VLDB talk as well as a <a href="https://www.vldb.org/pvldb/vol16/p2679-pedreira.pdf">VLDB paper I contributed to this year</a>.</p><p>As my table format review wore on it became clear that there is not enough unbiased content regarding the table format war. To this end, I decided to share both the summary of my research as well as some additional observations. The detailed backing data to this summary data can be found <a href="https://tableformats.sundeck.io/">here</a>.</p><h3>TL;DR</h3><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FtBYVaqZ4aQM&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DtBYVaqZ4aQM&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/8e2c3c174aed00ea31227befb2b82360/href">https://medium.com/media/8e2c3c174aed00ea31227befb2b82360/href</a></iframe><h3>Evaluating technology backing &amp; viability</h3><p>In general, winning technologies are more about organizational and market dynamics than inherent technology quality or completeness (just ask Larry Ellison). There are lots of voices in the market that are trying to win. In this environment, it’s helpful to look at market-level metrics. For this, let’s focus on two key metrics: 800-pound gorilla bets and engine support.</p><h3>Iceberg is slightly ahead of Delta Lake amongst gorilla bets. Hudi isn’t even in the conversation.</h3><p>One of the key things to consider in selection of a new technology is organizational backing and posture. Who is betting on which technologies? If we look at the five gorillas driving the analytics market (Amazon, Google, Microsoft, Snowflake, Databricks), only two are open about their table format bet (Snowflake and Databricks). The other three generally maintain neutrality, stating they support all approaches. At the same time, companies have to make tradeoffs and each is actively making a bet and focusing most of their resources on that format. As a proxy metric for that bet, we look at the number of documentation pages focused on each technology. This metric gives a clear signal of relative gorilla investment amongst the formats.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/679/1*QU1zA10gGf-Fo24VCZmi-w.png" /><figcaption>Multiple of docs focused on one horse versus the other. Databricks’ 13.6x is truncated for readability.</figcaption></figure><p>Using this proxy, we see a pretty clear picture: Amazon, Google and Snowflake are more focused on Iceberg while Microsoft and Databricks are more focused on Delta Lake.</p><p>These results match the public stances of Databricks and Snowflake. I also did off-the-record discussions with several companies involved here and these metrics are consistent with their feedback. In essence: Amazon, Snowflake and Google have bet on Iceberg. Databricks and Microsoft have bet on Delta Lake. (FYI, Microsoft may be hedging this some as they are leading work on <a href="https://cwiki.apache.org/confluence/display/INCUBATOR/XTable+Proposal">XTable</a>, a metadata translation initiative that competes with Databricks’ UniForm.) Hudi never trends as the primary amongst all five, underscoring it’s increasing market irrelevance.</p><p><em>Interesting aside, Hudi is huge in a China with several of the largest B2Cs actively involved. The West is apparently focused on Iceberg and Delta Lake whereas the East seems to have bet much bigger on Hudi.</em></p><h3>Iceberg has the highest availability of read and write support</h3><p>It is useful to look at engine support for each format given that a primary reason to pick an open table format is to minimize risk of lock-in. We surveyed the read and write support of 11 top engines. This included Athena, BigQuery, Databricks, Dremio, DuckDB, Flink, PrestoDB, Redshift, Snowflake, Spark and Trino. Results can be found below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*JRBwTK3HIieBh6sl7suaUA.png" /><figcaption>Iceberg has the broadest support for reads and writes</figcaption></figure><p>As you can see, nearly three times as many engines support writing Iceberg tables compared with the number of engines that support writing Delta Lake tables. Interestingly, every tool reviewed except Databricks SQL supports reading Iceberg tables.</p><figure><a href="https://tableformats.sundeck.io/"><img alt="" src="https://cdn-images-1.medium.com/max/798/1*HnkzPVALUNYh-B1Zh7YwRw.png" /></a><figcaption>For detailed analysis of engine support, check out our openly available <a href="https://tableformats.sundeck.io/">support matrix</a> and backup data. Also available on Github</figcaption></figure><h3>Stop Comparing Features and Performance</h3><p>Most content written today about table formats are feature and performance comparisons. This is the biggest area of vendor marketing and FUD. Much of this content gives users a near-term tactical reason to adopt format X over format Y. While we all must operate in the near-term, I expect the war will be won based on the following fundamentals:</p><ul><li>In OSS competition, good ideas in one project are quickly incorporated into the others. E.g. initially, only Hudi had merge-on-read capabilities while Iceberg and Delta Lake only supported copy-on-write. More recently, both Iceberg and Delta Lake have filled out their merge-on-read story. A similar pattern is occurring with z-ordering capabilities.</li><li>Moving data gold copies is done slowly and rarely. It should be done on time horizons of 5–10 years.</li></ul><p>Because of these fundamentals, most people would be best served to ignore all of the feature and performance bake offs that exist around table formats.</p><blockquote>In OSS competition, good ideas in one project are quickly incorporated into the others… [Therefore], ignore all of the feature and performance bake offs</blockquote><p>All three communities are composed of smart, capable people who will copy any differentiating tech. And remember, this is especially true in this space, since all of the code is freely available under the Apache 2.0 license.</p><h3>Continued: Community Health &amp; Key Trends</h3><p>Don’t miss part 2 of this series where we <a href="https://blog.sundeck.io/lakehouse-format-corporate-influence-community-health-e799e1fcced3">analyze community health</a> and part 3 where we look at <a href="https://blog.sundeck.io/lakehouse-formats-trends-to-watch-6c8d0730bf1c">key trends</a>. All data used in this series is <a href="https://tableformats.sundeck.io/">openly available</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7edd75015428" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/2024-lakehouse-format-rundown-7edd75015428">2024 Lakehouse Format Rundown</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Is your warehouse half empty?]]></title>
            <link>https://medium.com/snowflake/is-your-warehouse-half-empty-a634bb6ce2a5?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/a634bb6ce2a5</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-ware-housing]]></category>
            <category><![CDATA[cloud-cost-optimization]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[cloud-cost-management]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Thu, 31 Aug 2023 13:02:30 GMT</pubDate>
            <atom:updated>2023-08-31T13:02:30.896Z</atom:updated>
            <content:encoded><![CDATA[<h4><em>Snowflake Warehouse Utilization: What it is and why it matters</em></h4><p>TL;DR: Install <a href="https://sundeck.io/community/opscenter">OpsCenter</a>, reduce your auto-suspend times, stop creating new warehouses at the drop of a hat and take utilization seriously. Your wallet will thank you.</p><p>I recently <a href="https://www.linkedin.com/posts/jacquesnadeau_query-engineering-for-snowflake-activity-7094822417738444800-crYO?utm_source=share&amp;utm_medium=member_desktop">shared a statistic on LinkedIn</a>: most Snowflake warehouses are idle half the time. Sure, it’s a little click-baity but most Snowflake users we talk to are surprised by their Snowflake utilization. The biggest reason for this surprise is the same as any metric: until you monitor it, you shouldn’t assume you know what is going on (and you will definitely struggle to improve it).</p><h3>Warehouse Utilization explained</h3><p>Warehouse utilization quantifies how effectively you’re using Snowflake compute spend. For most organizations, Snowflake compute spend is the largest component of one’s overall Snowflake bill. Each time you start a warehouse, you start incurring costs until that warehouse is suspended. Warehouse utilization describes how well you are using the warehouse while it is running. For example:</p><ul><li>0% Utilization =&gt; No queries ran on the warehouse while it was up.</li><li>100% Utilization =&gt; Queries utilized all the resources on the warehouse while it was up.</li></ul><p><strong>Most warehouses sit somewhere in the middle.</strong> You’d rarely start a warehouse without at least one query running on it. It’s also very difficult to ensure every cpu cycle is used in a warehouse from start to stop.</p><p>So let’s draw a more realistic example: a single query starts a warehouse. The query runs for 10 seconds, then stops. The warehouse has the Snowflake-default 10 minute auto-suspend value. In this case, if the query used all resources during that ten seconds, we’d calculate a warehouse utilization as follows:</p><ul><li>Query Seconds: 10</li><li>Warehouse Seconds: 610 (10 seconds running + 10 minutes idle)</li><li>Utilization: 1.6% (10/610)</li></ul><p>However, note that above we said “if the query used all resources during that ten seconds”. In reality, a single query rarely saturates a warehouse. Warehouses are built to run concurrent queries. So when doing this calculation, we also need to include the query’s actual consumption of warehouse capacity. Let’s assume this query used 25% of the warehouse while it ran. This results in the following updated numbers:</p><ul><li>Query Seconds: 10</li><li>Query-warehouse Seconds: 2.5</li><li>Warehouse Seconds: 610</li><li>Utilization: 0.4% (2.5/610)</li></ul><blockquote>If this was a medium warehouse at standard rates, you would have spent ~$1.33 for this query and of that, <strong>less than a penny would have gone towards actually running the query.</strong></blockquote><p>This really shows the impact that warehouse utilization can have on overall efficiency and spend.</p><h3>Auto-suspend to the rescue, sort of?</h3><p>Snowflake’s automatic suspension is great and drastically reduces resource waste. However, it can’t eliminate under-utilization. It’s important to remember:</p><ul><li>If you’re continuously running queries on a warehouse, that doesn’t mean that warehouse is fully utilized. A big warehouse with small queries may never use most of the capacity of that warehouse.</li><li>Suspension isn’t immediate. This value defaults to 10 minutes and the timers backing suspension can only be tuned down to ~60 seconds.</li><li>A warehouse always costs at least <a href="https://docs.snowflake.com/en/user-guide/warehouses-overview#impact-on-credit-usage-and-billing">60 seconds of credits</a>, even if a query runs for 1s and then the warehouse is manually suspended.</li><li>One or two queries rarely saturate a warehouse. In most cases, you need multiple concurrent queries on a warehouse to leverage its entire capacity.</li><li>Dividing query activity into a larger number of warehouses increases the likelihood of under-utilization on any one warehouse.</li></ul><p>So while auto-suspend helps, there are several dynamics that can still reduce utilization (and spend efficiency).</p><h3>What’s a good utilization target?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E68FzmCBc7ZHsA8jQWu1AQ.png" /><figcaption>Target low latency can hurt utilization</figcaption></figure><p>Utilization targets depend on a couple of things. Maximizing warehouse utilization is a <a href="https://en.wikipedia.org/wiki/Bin_packing_problem">bin packing</a> problem with a deadline component. This is an age-old resource problem: leave capacity for bursts of activity or don’t. In Snowflake that converts to: how important is latency versus maximum completed work per credit? In most organizations, it depends on the workload. Most queries will fall into the following categories:</p><ol><li>Machine-driven operations. People are not directly involved in immediately consuming the outputs. This includes things like ETL and dbt jobs.</li><li>Somewhat latency sensitive human-driven operations. This includes things like interactive analysis and drilldown.</li><li>Highly latency sensitive human-driven operations. This includes things like dashboards.</li></ol><p>As latency and predictability needs increase, you’ll want to maintain additional capacity to quickly respond to bursts of activity. As such, we recommend targeting utilizations for each type of workload differently. For machine-driven workloads like #1, you want to target a value close to 95% utilization. To achieve this, you often must queue workloads, and run them in parallel while mostly avoiding running warehouses until absolutely necessary (defer warehouse resume until queues are full). For the human-driven operations you are almost always going to have lower utilization since you want to minimize user latency (which means running queries even if you don’t have enough to maximize capacity (so maybe 80% utilization for #2 and 60% for #3).</p><h3>How do I monitor utilization?</h3><p>Unfortunately, Snowflake doesn’t yet provide direct reports on warehouse utilization. There is some talk about a new metric being shared but nothing is yet generally available. We built <a href="https://sundeck.io/community/opscenter">Sundeck OpsCenter</a>, a free and open Snowflake Native app that helps understand this. You can <a href="https://app.snowflake.com/marketplace/listing/GZTYZT5BVO">install it from Snowflake’s marketplace</a> and use it for free and we strongly suggest you do.</p><blockquote>If you aren’t familiar with native apps, they run entirely within your Snowflake account so you don’t have to worry about your query activity being shared with anyone else while using OpsCenter.</blockquote><p>One useful item OpsCenter provides is a utilization heatmap. This gives you an understanding of utilization over the last several days and weeks on a per-warehouse basis. This will allow you to start understanding where your biggest opportunities for improvement to utilization exist.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/0*UhFC9Yl9hLtr-6Mt" /><figcaption>Sundeck OpsCenter utilization heatmap</figcaption></figure><h3>Our Detailed Calculation</h3><p>Because OpsCenter is a community project, you can fully understand this calculation by looking at the OpsCenter code on github. To understand and monitor utilization, we built Sundeck OpsCenter, which calculates two key values:</p><ol><li>Metered compute credits per day/warehouse, a value we typically refer to as <strong>loaded cost</strong>. Loaded cost includes both query time and waste (e.g. idle time). This value is calculated by looking at actual billing amounts per warehouse in <a href="https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_metering_history">WAREHOUSE_METERING_HISTORY</a>. <em>Note, we avoid the </em><a href="https://docs.snowflake.com/en/sql-reference/account-usage/warehouse_events_history"><em>WAREHOUSE_EVENTS_HISTORY</em></a><em> view for this calculation because it is notoriously unreliable (and it also isn’t necessary).</em></li><li>Using <a href="https://docs.snowflake.com/en/sql-reference/account-usage/query_history">QUERY_HISTORY</a>, we multiply the runtime of each query by its reported QUERY_LOAD_PERCENT, and sum this value for all queries run during the day. If a query spans midnight, we only count the portion that falls within the day being computed.</li></ol><p>We then join our unloaded and loaded cost rollups by warehouse_id and date, to compute the utilization percent by day. The specific queries can be found <a href="https://github.com/sundeck-io/OpsCenter/blob/main/bootstrap/020_warehouse_utilzation.sql">here</a> with supporting views <a href="https://github.com/sundeck-io/OpsCenter/blob/main/bootstrap/015_warehouse_views.sql">here</a> and <a href="https://github.com/sundeck-io/OpsCenter/blob/main/bootstrap/016_query_history.sql">here</a>.</p><blockquote>We’ve explored approaches to deriving total credits consumed from per-query cost estimates, but have since decided the question is not about Snowflake’s billing (which is quite accurate). The question is: “Out of the amount of compute that we paid for, what amount did we actually use?</blockquote><h3>How accurate can we get?</h3><p>Sundeck’s model reliably represents warehouse utilization in most cases. However, there are a few challenges with the metric that you should be aware of.</p><ul><li>Poor <a href="https://docs.snowflake.com/en/sql-reference/account-usage/query_history">QUERY_LOAD_PERCENT</a> granularity: This metric helps us to determine the relative consumption of a warehouse to understand whether more queries could be run. However, this metric is a single value for an entire query. Most queries vary their consumption of resources over time and using a single metric means that this is probably some form of average or peak.</li><li>Nested queries are difficult to account for. Snowflake’s QUERY_HISTORY view doesn’t specify that one query triggered another (most common when using Snowflake scripting blocks). In this scenario, calculation may overestimate the actual load on the cluster (over-estimating utilization).</li></ul><p>It’s also important to remember that warehouse compute is only one component of overall Snowflake spend, and utilization is solely focused on compute. If your scenario is unusual (say 99% of your bill is for storage), raising warehouse utilization probably won’t improve your bottom line as much as it does for most.</p><h3>How does the OpsCenter metric compare with what Snowflake provides?</h3><p>Snowflake recently announced that they were planning to provide granular warehouse utilization insights. To date, this feature is still in a relatively narrow private preview. Unfortunately this means we can’t yet report on how it compares or when that will be more generally available.</p><h3>What should I do today to increase my utilization?</h3><ul><li>Install OpsCenter. It’s free, easy, and will ensure you know what your utilization is. We even have a <a href="https://quickstarts.snowflake.com/guide/sundeck_opscenter/#0">useful quickstart</a> to get you going.</li><li>Set auto-suspend time on all warehouses to 60 seconds. It’s the easiest way for most organizations to improve their utilization.</li><li>Avoid creating warehouse until absolutely necessary. As noted above, more warehouses almost always results in lower overall utilization. If you need fine-grained metrics on cost, you can use Sundeck’s <a href="https://docs.sundeck.io/reference/conditions/workload/">workload tagging</a> to avoid tying billing management to cost considerations. (Use separate warehouses only when you have query efficiency/performance reasons to do so.)</li></ul><p>And if you’re interested in this kind of thing, follow our <a href="https://blog.sundeck.io/">Snowflake blog</a>. We spend a lot of time thinking about ways to make your Snowflake <a href="https://blog.sundeck.io/save-money-by-automatically-routing-queries-to-a-right-sized-warehouse-4b24c798f27f">more efficient</a> and <a href="https://blog.sundeck.io/slack-notifications-for-bad-snowflake-queries-free-for-all-f671eadf8467">more capable</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a634bb6ce2a5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/is-your-warehouse-half-empty-a634bb6ce2a5">Is your warehouse half empty?</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[Announcing the Sundeck Query Engineering Platform for Snowflake]]></title>
            <link>https://blog.sundeck.io/announcing-the-sundeck-query-engineering-platform-for-snowflake-37a82e3da957?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/37a82e3da957</guid>
            <category><![CDATA[warehouse-optimization]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[query-engineering]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Wed, 31 May 2023 16:41:42 GMT</pubDate>
            <atom:updated>2023-05-31T16:41:42.918Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*e4gN6TsFHCf7RvgMoNhVIQ.png" /></figure><p>It’s an exciting day today. Sundeck is coming out of stealth. What’s more, we’re sharing fundraising news and announcing the public availability of our product.</p><p>Since we started Sundeck, our focus has been singular: expose a new creative frontier to data engineers, analysts and data scientists. Our specific target has been the space between data warehouses and the tools that use them. Our goal is to make this layer accessible to data engineers so they have more control and creativity about how queries are interpreted. We even think it should be possible to redefine what a query is. We call this Query Engineering. And today, we’re announcing our public preview: <a href="https://sundeck.io/?source=launchblog">Sundeck, a Query Engineering Platform</a>.</p><p>To help people better understand what a query engineering platform can help with, we provide a number of prepopulated recipes to enable common use cases including:</p><ul><li><a href="https://sundeck.io/usecases/warehouse-optimization?source=launchblog">Warehouse Optimization</a></li><li><a href="https://sundeck.io/usecases/adaptive-dbt-routing?source=launchblog">Adaptive dbt Routing</a></li><li><a href="https://sundeck.io/usecases/user-help-and-guardrails?source=launchblog">User Help &amp; Guardrails</a></li><li><a href="https://sundeck.io/usecases/granular-cost-control?source=launchblog">Granular Cost Control</a></li><li><a href="https://sundeck.io/usecases/add-new-query-syntax?source=launchblog">Adding New Query Syntax</a></li></ul><p>I’m happy with the recipes we provide but I’m even more energized by the magic that comes with the interaction between Sundeck’s capabilities and the creativity of the data engineers and analysts using it. We’ve already seen a bunch of this in our private preview (and several of the recipes above are inspired by real customer examples).</p><p>The amazing team at Sundeck got us to this milestone. This small group of insanely smart and talented people make me feel lucky every day. Creating something entirely new and different isn’t easy and their tenacity and fortitude have been our backbone. I’m also super happy to share the news of our seed funding round of $20 million from our partners at NEA, Coatue and Factory. They’ve been great partners in our early journey.</p><p>No technology is entirely new and several key technologies were especially formative in the creation of Sundeck.</p><ul><li><a href="https://github.com/dbt-labs/dbt-core">dbt</a>: The creative energy that dbt unleashed for analytics engineering is inspiring and we look to unleash similar energy for query engineering. Data engineers need tools that make things accessible, extensible and shareable. We look for Sundeck to satisfy those needs across all query use cases, just as dbt has done for etl and analytics engineering use cases.</li><li><a href="https://calcite.apache.org/">Apache Calcite</a>: For those that don’t know, Calcite is a highly modular database with a very extensible optimizer. It is less well known than dbt but has been around much longer. I was lucky enough to one of the first serious users of it as a modular optimizer in a production system and have used it in multiple projects over the last decade. It’s awesome. Thinking about how to make a small fraction of those types of capabilities to Snowflake users inspired many of the features we built.</li><li><a href="https://arrow.apache.org/">Apache Arrow</a>: When I co-founded Apache Arrow 9 years ago, so many people told me it was a pointless exercise. Why would a memory format + libraries be broadly useful. Today we find ourselves at more than 65 million downloads each month and industry-spanning prevalence (for example, Snowflake’s client protocol is built on Arrow). This is further proof that the more we can make powerful technologies available to broader communities, the more great things happen.</li><li><a href="https://substrait.io/">Substrait</a>: This is a relatively new open source project that I co-created eighteen months ago. It looks to do what Arrow did for data to query plans. It’s young but is also a key technology that underpins much of what we’ve built at Sundeck.</li></ul><p>While Sundeck the company has existed for a little over a year, the ideas behind Sundeck are part of a career-spanning journey of making data more accessible. I hope you find it useful! It’s <a href="https://sundeck.io/pricing">free</a> to start and <a href="https://sundeck.io/try?source=launchblog">easy to sign-up</a>. Reach out and let us know what you think!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=37a82e3da957" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/announcing-the-sundeck-query-engineering-platform-for-snowflake-37a82e3da957">Announcing the Sundeck Query Engineering Platform for Snowflake</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Future Opportunities for the Snowflake Source Connector Framework]]></title>
            <link>https://blog.sundeck.io/future-opportunities-for-the-snowflake-source-connector-framework-1153ce6e881c?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/1153ce6e881c</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[integration]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Mon, 10 Apr 2023 18:21:49 GMT</pubDate>
            <atom:updated>2023-04-10T18:21:49.744Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rpGdlA65DWzegIhN.png" /></figure><p><em>This is a bonus post from our five-part series about live-querying operational databases within Snowflake. If you just want to try things out, you can </em><a href="https://github.com/sundeck-io/snowflake-connector-framework"><em>go to GitHub</em></a><em>. If you want to read the series from the beginning, start with our </em><a href="https://blog.sundeck.io/creating-a-source-connector-framework-for-snowflake-d0551a72ff32"><em>introductory post</em></a><em>.</em></p><h3>Where do we go from here?</h3><p>We’re going to start leveraging this for more of our use cases moving forward. We also think there are a number of other situations where it would be useful to expose data within the Snowflake context that is up-to-date so we’ll probably be posting additional connectors like that soon. We would love to also see others contribute their own connectors based on their needs. Hopefully, we’re not the only ones who find places where this is helpful. We expect that at some point, Snowflake will release something that makes this easier.</p><h3>Ideas Worth Pursuing</h3><h4>Lightweight pagination service</h4><p>Athena was a great way to get this all working. However, it isn’t a super lightweight solution to the problem. It also disables one from pushing arbitrary query expressions all the way to the source (Athena does support predicate and projection pushdown but if we want to push down aggregates and joins, we are forced to create extra views in MySQL. A simple pagination service (either stateful using cursors or stateless using S3 for caching, could improve latency, simplify the infrastructure stack and allow one to push arbitrary queries into the lowest level source).</p><h4>Parallel Pagination</h4><p>In the current model, Athena only supports sequential page access (meaning each page returns the token for the next page). Additionally, Athena only returns up to 1000 records per get-query-results call, meaning that the return speed of this pattern is entirely linear (in a bad way). Things don’t have to be this way. Given that Athena knows how many records are returned, one could actually generate all the pages upfront and request them in parallel if Athena’s API was different. This would allow Snowflake to drastically improve the data movement performance since we’ve often seen Snowflake external functions to do 100+ simultaneous requests. 100x performance improvement would be nice. (An open source REST API enhancement project, <a href="https://www.npmjs.com/package/athena-express">Athena-Express</a>, actually supports this pattern and implements it by reading results directly from S3).</p><h4>Postgres Parallel Raw Table Retrieval Optimizations</h4><p>In the cases where we want to do faster table access, we can use the previously mentioned primary key + current snapshot trick. This would allow our initial query to generate a large number of page keys simultaneously and we again could use Snowflake mass parallelization to improve performance.</p><h4>Auto-Adapting Batch Size</h4><p>Since Snowflake only supports a response payload &lt;10mb, we have a second parameter that is the pagination size we pass into Athena. Realistically, that should be something invisible to the user. With an alternative paginator, the pagination system could identify the maximum number of records to retrieve in each 10mb batch so that a user doesn’t have to manage them manually.</p><p>Let us know what you think in the comments below or <a href="https://github.com/sundeck-io/snowflake-connector-framework">on Github</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1153ce6e881c" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/future-opportunities-for-the-snowflake-source-connector-framework-1153ce6e881c">Future Opportunities for the Snowflake Source Connector Framework</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deploying Snowflake External Functions with a Single Command]]></title>
            <link>https://blog.sundeck.io/deploying-snowflake-external-functions-with-a-single-command-eb6175c3b1c4?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/eb6175c3b1c4</guid>
            <category><![CDATA[integration]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Mon, 10 Apr 2023 18:16:58 GMT</pubDate>
            <atom:updated>2023-04-10T18:16:58.053Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-j6yf4GhF0RPoZtf.jpeg" /></figure><p><em>This is part five of our five-part series on inventing a source connector framework for Snowflake. If you’re interested in the entire journey, you might want to </em><a href="https://blog.sundeck.io/creating-a-source-connector-framework-for-snowflake-d0551a72ff32"><em>start at the beginning</em></a><em>. If you just want to try out the code, feel free to jump over to </em><a href="https://github.com/sundeck-io/snowflake-connector-framework"><em>our GitHub</em></a><em> and check things out.</em></p><p>As part of building our Snowflake source connector framework, we wanted to automate deployment (for both us and others). We had hoped we could deploy things using <a href="https://docs.getdbt.com/docs/build/jinja-macros">dbt macros</a>. However, it quickly became clear that we had far too much AWS infrastructure to configure to use dbt. We’ve been using <a href="https://www.pulumi.com/">Pulumi</a> for several other things and have found it to be relatively compact (especially when you can use things like <a href="https://www.pulumi.com/docs/guides/crosswalk/aws/">Crosswalk for AWS</a>). Additionally, the mixture of relatively declarative style combined with small amounts of code worked well. (For example, it allows us to create our JavaScript Snowflake functions in separate files and inline them in Snowflake DDL as necessary.) Given these properties, we decided to build up the deployment of our external functions and other assets using Pulumi.</p><p>Pulumi worked well for most things. Pulumi does a good job of managing the dependencies between various assets. This is especially nice when there are some challenging ping-pong patterns around how one configures a Snowflake external function. For example, AWS configuration must be passed to Snowflake, then once Snowflake is configured, the output of the Snowflake configuration needs to be passed back to AWS. This makes all tutorials around deployment to date include a number of manual UI steps. For example, Snowflake’s own documentation has <a href="https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-template.html">some automation using CloudFormation</a> but ultimately has a number of manual steps. We know that manual steps usually mean quality problems (and huge amounts of anxiety when there are infrastructure problems).</p><p>All in all, Pulumi worked reasonably well. However, there were a few places where Pulumi (and the Pulumi ecosystem) fell down. Specifically, Pulumi’s <a href="https://www.pulumi.com/registry/packages/snowflake/installation-configuration/">Snowflake adapter</a> (built on top of Snowflake’s <a href="https://github.com/Snowflake-Labs/terraform-provider-snowflake">Terraform provider</a>), doesn’t have great handling for external functions. It doesn’t support update and doesn’t support request/response translators. Additionally, the Terraform provider produces invalid SQL when you try to use the function resource for a table-returning function. Given these challenges, we had to create our own <a href="https://github.com/sundeck-io/snowflake-connectors/blob/main/snowflake/SnowflakeGenericProvider.ts">dynamic provider</a> to upload those kinds of assets:</p><pre>import * as pulumi from &quot;@pulumi/pulumi&quot;;<br>import {ID} from &quot;@pulumi/pulumi/resource&quot;;<br>import { Snowflake } from &#39;snowflake-promise&#39;;<br><br><br>export interface SnowflakeResourceInputs {<br>    database: pulumi.Input&lt;string&gt;;<br>    schema: pulumi.Input&lt;string&gt;;<br>    name: pulumi.Input&lt;string&gt;;<br>    type: pulumi.Input&lt;string&gt;;<br>    args: pulumi.Input&lt;FunctionArgumentInputs[]&gt;;<br>    theRest: pulumi.Input&lt;string&gt;;<br>}<br><br>export interface FunctionArgumentInputs {<br>    name: pulumi.Input&lt;string&gt;,<br>    type: pulumi.Input&lt;string&gt;<br>}<br><br>interface FunctionArgument {<br>    name: string,<br>    type: string<br>}<br><br>interface SnowflakeInputs {<br>    database: string;<br>    schema: string;<br>    name: string;<br>    type: &quot;FUNCTION&quot; | &quot;EXTERNAL FUNCTION&quot;;<br>    args: FunctionArgument[];<br>    theRest: string;<br>}<br><br>function createArgString(inputs: SnowflakeInputs ){<br>    return inputs.args.map(f =&gt; `${f.name} ${f.type}`).join(&#39;, &#39;);<br>}<br><br>function dropArgString(inputs: SnowflakeInputs ){<br>    return inputs.args.map(f =&gt; f.type).join(&#39;, &#39;);<br>}<br><br>function getId(inputs: SnowflakeInputs){<br>    return `&quot;${inputs.database}&quot;.&quot;${inputs.schema}&quot;.&quot;${inputs.name}&quot;`<br>}<br><br>class SnowflakeProvider implements pulumi.dynamic.ResourceProvider {<br>    connection: Snowflake | undefined = undefined;<br>    connected: boolean = false;<br><br>    async create(inputs: SnowflakeInputs) {<br>        await this.connectIfNotConnected();<br>        await runsql(this.connection, inputs.database, inputs.schema, `CREATE ${inputs.type} ${getId(inputs)}(${createArgString(inputs)}) ${inputs.theRest}`);<br>        const output = { id: getId(inputs) , outs: {database: inputs.database, schema: inputs.schema, name: inputs.name, type: inputs.type, theRest: inputs.theRest, args: inputs.args}};<br>        pulumi.log.debug(JSON.stringify(output));<br>        return output;<br>    }<br><br>    async connectIfNotConnected() {<br>        if(!this.connected) {<br>            pulumi.log.debug(&quot;Connecting.&quot;);<br>            const config = new pulumi.Config(&quot;snowflake&quot;);<br>            const account = config.require(&quot;account&quot;);<br>            const username = config.require(&quot;username&quot;);<br>            this.connection = new Snowflake({<br>                account: account,<br>                username: username,<br>                authenticator: &quot;EXTERNALBROWSER&quot;<br>            });<br>            await this.connection.connectAsync();<br>            pulumi.log.debug(&quot;Connected.&quot;);<br>            this.connected = true;<br>        }<br>    }<br><br>    async delete(id: ID, inputs: SnowflakeInputs) {<br>        await this.connectIfNotConnected();<br>        pulumi.log.debug(`database: ${inputs.database} schema: ${inputs.schema}, args: ${(JSON.stringify(inputs.args))}, name: ${inputs.name}`);<br>        await runsql(this.connection, inputs.database, inputs.schema, `DROP FUNCTION ${getId(inputs)}(${dropArgString(inputs)}) `);<br>    }<br><br>}<br><br>const snowflakeGenericProvider= new SnowflakeProvider();<br><br>interface SnowflakeConfig {<br>    account: string;<br>    username: string;<br>}<br><br>export class GenericSnowflake extends pulumi.dynamic.Resource {<br>    constructor(name: string, args: SnowflakeResourceInputs, opts?: pulumi.CustomResourceOptions) {<br>        super(snowflakeGenericProvider, name, args, opts);<br>    }<br>}<br><br>async function runsql(connection: Snowflake | undefined, database: string, schema: string, sql: string) {<br>    pulumi.log.debug(&quot;runsql: &quot; + sql );<br>    await connection?.execute(`USE &quot;${database}&quot;.&quot;${schema}&quot;`);<br>    return connection?.execute(sql);<br>}</pre><p>This is mildly annoying for users, because it means Pulumi prompts one to login to Snowflake several times. However, it still seems generally much easier than any other way for someone to set up the kit.</p><p>Once you deploy our configuration, the API for querying MySQL is pretty straightforward. Just call the appropriate table function and you get back a table of data:</p><pre>SELECT * FROM TABLE(query_athena(<br>    &#39;SELECT * <br>     FROM mysql.information_schema.tables&#39;<br>));</pre><p>One constraint of the current pattern is that Snowflake doesn’t allow any SQL operations to return dynamic root-level schemas. As such, we have the data returned in a single data field that contains each record. For our use cases, we’ll be wrapping individual table function invocations with views so that downstream consumers don’t have to worry about the gory details.</p><p>So what’s the performance of the first version? 50k records in ~10s. You can find the query profile below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_4OPi4N0i64RCMHFEWYXKw.png" /><figcaption>Query profile for example 50k record retrieval query</figcaption></figure><p>The key numbers to look at are the number of function invocations. In this case, we see 61 invocations. Decomposed, that is:</p><ul><li>1 Query submission invocation</li><li>50 pages of results (1000 items per page)</li><li>10 probes for status until Athena completed the query</li></ul><p>Since most of the queries we are using this for initially are on the order of 1,000s of records, this performance is acceptable. There are many <a href="https://sundeck.io/blog/future-opportunities-for-the-snowflake-source-connector-framework">optimization opportunities</a> but we also wanted to ship and share something sooner than later. Adding MongoDB and Postgres are easy since Athena already supports those standard sources (for Mongo, look for “DocumentDB”, which is an AWS-branded Mongo connector).</p><h3>Conclusion</h3><p>All in all, there were just enough capabilities within Snowflake to make this work (with a bit of elbow grease). And now that it is working, we’re finding it super useful. We’ll still use ELT for most things in Snowflake but for some use cases, having an external udtf/source connector for Snowflake opens up new possibilities.</p><p>If you’re interested in hearing more about some of the optimization opportunities, check out our bonus post: <a href="https://blog.sundeck.io/future-opportunities-for-the-snowflake-source-connector-framework-1153ce6e881c">Future Opportunities for the Snowflake Connector Framework</a>. If you just want to get to the code, you can find it <a href="https://github.com/sundeck-io/snowflake-connector-framework">on our Github</a>. Give it a shot and let us know what you think!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eb6175c3b1c4" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/deploying-snowflake-external-functions-with-a-single-command-eb6175c3b1c4">Deploying Snowflake External Functions with a Single Command</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Paginating MySQL Access for Snowflake Consumption]]></title>
            <link>https://blog.sundeck.io/paginating-mysql-access-for-snowflake-consumption-f09679255bb1?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/f09679255bb1</guid>
            <category><![CDATA[integration]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Mon, 10 Apr 2023 18:11:47 GMT</pubDate>
            <atom:updated>2023-04-10T18:11:47.137Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*p74ks0tLkN4iwvh9.png" /></figure><p><em>This is part four of our five-part series on inventing a source connector framework for Snowflake. If you’re interested in the entire journey, you might want to </em><a href="https://blog.sundeck.io/creating-a-source-connector-framework-for-snowflake-d0551a72ff32"><em>start at the beginning</em></a><em>. If you just want to try out the code, feel free to jump over to </em><a href="https://github.com/sundeck-io/snowflake-connector-framework"><em>our GitHub</em></a><em> and check things out.</em></p><p>Now that we had the connector pattern validated in Snowflake, we needed to get to productionalizing the pattern and connecting up to an actual operational database. We most often use MySQL and Postgres to store our operational data. While this is mostly straightforward when working with old-school stateful services using something like JDBC/ODBC, we needed a stateless REST API for our data. Unfortunately, while modern Web APIs almost always expose pagination APIs for data (including Snowflake), neither Postgres nor MySQL expose a first class pagination API.</p><p>The closest thing to a pagination API in traditional databases are cursors. Unfortunately, they typically depend on maintaining a stateful ODBC or JDBC connection. If you want to deploy a stateless scalable REST API running in something like AWS Lambda, you really want to make your API as stateless as possible. As we hunted around, we identified <a href="https://postgrest.org/en/stable/">PostgREST</a> as one possible solution. While initially promising, we found that they <a href="https://github.com/PostgREST/postgrest/issues/254">unforutnately hadn’t solved consistent pagination of any arbitrary query</a>; ultimately we abandoned this solution in preference to statelessness. We also considered using the primary key + current snapshot trick <a href="https://duckdb.org/2022/09/30/postgres-scanner.html">the DuckDB folks used</a> for their Postgres integration. However, that pattern is primarily relevant to tables, not arbitrary queries of other things, like views. (That makes more sense in the context of what DuckDB is accomplishing, just not as generally useful as what we were looking for where we’re querying views most of the time.)</p><p>As we were thinking about options, we realized that AWS Athena provides pagination support via S3. Not the most performant or lightest weight solution but it fit the bill and benefitted from less things to manage. As such, we decided to construct our initial end-to-end approach using Athena. The good news is that staying within the AWS family allowed us to leverage IAM security for much of the interconnections (Snowflake =&gt; API Gateway =&gt; Athena). It also meant that all of Athena’s data source connectors would now work out-of-the-box with Snowflake.</p><p>There were still some challenges that we had to solve. Athena has several different actions that were involved in completing a query. First, you call the <a href="https://awscli.amazonaws.com/v2/documentation/api/2.0.34/reference/athena/start-query-execution.html">start-query-execution</a> action. Then you need to probe <a href="https://awscli.amazonaws.com/v2/documentation/api/2.0.34/reference/athena/get-query-execution.html">get-query-execution</a> multiple times, until the query either fails or completes. If it succeeds, you then need to iterate over the pages produced by doing <a href="https://awscli.amazonaws.com/v2/documentation/api/2.0.34/reference/athena/get-query-results.html">get-query-results</a>. These various operations didn’t map all that well to Snowflake’s normal external function model. The good news is that we had two useful tools to help us solve this complexity. The first was that we could use request and response translators to convert our requests based on the nature of what we needed to do. The second was that the default Snowflake external function system is built on top of AWS API gateway. While it is primarily used for things like throttling and access control, it has a wide variety of features. We were able to combine the use of request translator’s <a href="https://docs.snowflake.com/en/sql-reference/external-functions-translators.html#request-translator-output-properties">urlSuffix</a> support with the API gateways support for remapping request queries parameters to headers. This allowed us to have a singular API gateway that dispatched to different Athena operations (actions) depending on the desired outcome. You can see the specific cases where we handle our different “modes” in our request translator below:</p><pre><br>function translate_request(EVENT) {<br>  if(!EVENT.body.data[0][1]) {<br>    throw new Error(&quot;Mode must be provided.&quot;);<br>  }<br>  let row = EVENT.body.data[0][0];<br>  let mode = EVENT.body.data[0][1].toLowerCase();<br>  let sql = EVENT.body.data[0][2];<br>  let execution_id = EVENT.body.data[0][3];<br>  let starting_token = EVENT.body.data[0][4];<br>  let max_results_per_page = EVENT.body.data[0][5];<br><br>  switch(mode) {<br><br>    case &quot;submit&quot;:<br>      if(!sql || execution_id || starting_token) {<br>        throw new Error(&quot;A non-null SQL argument must be passed for Submission.&quot;);<br>      }<br>      return {<br>        &quot;translatorData&quot;: {&quot;mode&quot; : mode},<br>        &quot;urlSuffix&quot; : &quot;?action=AmazonAthena.StartQueryExecution&quot;,<br>        &quot;body&quot;: { &quot;QueryString&quot;: sql,<br>          &quot;ClientRequestToken&quot;: EVENT.contextHeaders[&quot;sf-context-current-timestamp&quot;] + &quot;xxxxxxxxxxxxxxxx&quot;,<br>          &quot;ResultConfiguration&quot;: {&quot;OutputLocation&quot;: &quot;s3://INSERTBUCKETNAMEHERE/&quot;}<br>        },<br>      };<br>    case &quot;pending&quot;:<br>      if(sql || !execution_id || starting_token) {<br>        throw new Error(&quot;Only execution id should be passed for pending.&quot; + sql + &quot; &quot; + execution_id + &quot; &quot; + starting_token);<br>      }<br>      return {<br>        &quot;urlSuffix&quot; : &quot;?action=AmazonAthena.GetQueryExecution&quot;,<br>        &quot;body&quot;: {&quot;QueryExecutionId&quot;: execution_id},<br>        &quot;translatorData&quot;: {&quot;mode&quot; : mode, &quot;execution_id&quot;: execution_id},<br>      };<br>    case &quot;first_page&quot;:<br>      if(sql || !execution_id || starting_token) {<br>        throw new Error(&quot;First page should have execution id but no sql or starting token.&quot;);<br>      }<br>      return {<br>        &quot;urlSuffix&quot; : &quot;?action=AmazonAthena.GetQueryResults&quot;,<br>        &quot;translatorData&quot;: {&quot;mode&quot; : mode, &quot;execution_id&quot;: execution_id},<br>        &quot;body&quot;: {<br>          &quot;QueryExecutionId&quot;: execution_id,<br>          &quot;MaxResults&quot;: max_results_per_page<br>        }};<br><br>    case &quot;subsequent_page&quot;:<br>      if(sql || !execution_id || !starting_token) {<br>        throw new Error(&quot;Next page should have execution id and starting token but no sql.&quot;);<br>      }<br>      return {<br>        &quot;urlSuffix&quot; : &quot;?action=AmazonAthena.GetQueryResults&quot;,<br>        &quot;translatorData&quot;: {&quot;mode&quot; : mode, &quot;execution_id&quot;: execution_id},<br>        &quot;body&quot;: {<br>          &quot;QueryExecutionId&quot;: execution_id,<br>          &quot;MaxResults&quot;: max_results_per_page,<br>          &quot;NextToken&quot;: starting_token<br>        },<br>      };<br><br>    default:<br>      throw new Error(&quot;Unknown mode submitted.&quot;);<br>  }<br>}</pre><p>Our response translator also pivots on mode (carried via Snowflake’s translatorData capability, which can carry metadata between request and response within Snowflake). One of the things you’ll see is that because we were unable to figure out how to use async/wait code in Snowflake functions, we decided to just do a busy wait while Athena is processing the query. This worked reasonably well, as we could rely on the round trip delay of REST requests as a form of sleep.</p><pre><br>function translate_response(EVENT) {<br>  try {<br>    let mode = EVENT.translatorData.mode;<br>    switch(mode) {<br>      case &quot;submit&quot;:<br>        return {<br>          &quot;body&quot;: { data: [[0, {execution_id: EVENT.body.QueryExecutionId, mode: &quot;pending&quot;}]]}<br>        }<br>      case &quot;pending&quot;:<br>        switch(EVENT.body.QueryExecution.Status.State) {<br>          case &quot;SUCCEEDED&quot;:<br>            return {<br>              &quot;body&quot;: { data: [[0, {execution_id: EVENT.translatorData.execution_id, mode: &quot;first_page&quot;}]]}<br>            }<br>          case &quot;FAILED&quot;:<br>            throw new Error(EVENT.body.QueryExecution.Status.StateChangeReason);<br>        }<br>        // let us try again. ideally we would use await here but it is not clear on how to do this inside of snowflake.<br>        //await new Promise(r =&gt; setTimeout(r, 75));<br>        return {<br>          &quot;body&quot;: { data: [[0, {execution_id: EVENT.translatorData.execution_id, mode: &quot;pending&quot;}]]}<br>        }<br>      case &quot;first_page&quot;:<br>      case &quot;subsequent_page&quot;:<br>        const body = EVENT.body;<br>        let records = new Array();<br>        let columns = body.ResultSet.ColumnInfos;<br>        for (let i = mode == &quot;subsequent_page&quot; ? 0 : 1; i &lt; body.ResultSet.Rows.length; i++) {<br>          let row = body.ResultSet.Rows[i].Data;<br>          let o = new Object();<br>          for(let h = 0; h &lt; columns.length; h++) {<br>            let name = columns[h].Name;<br>            let value = row[h].VarCharValue;<br>            switch(columns[h].Type) {<br>              case &quot;boolean&quot;:<br>                value = Boolean(value);<br>                break;<br>              case &quot;tinyint&quot;:<br>              case &quot;smallint&quot;:<br>              case &quot;integer&quot;:<br>                value = parseInt(value);<br>                break;<br>            }<br><br>            o[name] = value;<br>          }<br><br>          records.push(o);<br>        }<br>        let data = {<br>          mode: &quot;subsequent_page&quot;,<br>          execution_id: EVENT.translatorData.execution_id,<br>          next: body.NextToken,<br>          data: records<br>        };<br>        return {<br>          &quot;body&quot;: { data: [[0, data]]}<br>        };<br>      default:<br>        return {<br>          &quot;body&quot;: { data: [[0, EVENT.body]] }<br>        }<br>    }<br>  } catch(err) {<br>    //throw new Error(err.message + JSON.stringify(EVENT.body))<br>    throw new Error(err.message)    ;<br>  }<br>}</pre><p>Once we configured the API gateway and request/response translators well, we were successfully querying Athena from Snowflake. Whew, we’re almost there. The last mile was relatively simple. Athena offers up source connectors as Lambdas. As such, we deployed the MySQL source connector and connected that to our MySQL database. At this point,we’re now able to do our first Snowflake to MySQL live query.</p><p>Feel free to <a href="https://github.com/sundeck-io/snowflake-connector-framework">check out the code</a> or continue to our next blog, where we talk through how we can <a href="https://blog.sundeck.io/deploying-snowflake-external-functions-with-a-single-command-eb6175c3b1c4">deploy our Snowflake Connector Framework using a Single Command</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f09679255bb1" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/paginating-mysql-access-for-snowflake-consumption-f09679255bb1">Paginating MySQL Access for Snowflake Consumption</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Yes, Snowflake External Functions and Recursive CTEs work together]]></title>
            <link>https://blog.sundeck.io/yes-snowflake-external-functions-and-recursive-ctes-work-together-ac89e86b237?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/ac89e86b237</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[integration]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Mon, 10 Apr 2023 18:07:06 GMT</pubDate>
            <atom:updated>2023-04-10T18:07:06.772Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4A_Od1RnHjDQs9n1.jpeg" /></figure><p><em>This is part three of our five-part series on inventing a source connector framework for Snowflake. If you’re interested in the entire journey, you might want to </em><a href="https://blog.sundeck.io/creating-a-source-connector-framework-for-snowflake-d0551a72ff32"><em>start at the beginning</em></a><em>. If you just want to try out the code, feel free to jump over to </em><a href="https://github.com/sundeck-io/snowflake-connector-framework"><em>our GitHub</em></a><em> and check things out.</em></p><p>Once we had the working prototype of our source connector framework running within Snowflake, the next step was to “simply” move our mock service out of Snowflake and double check that external functions didn’t cause any issue. So we created a simple Node.js server that served our static “pages” exactly as our UDTF previously did.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5179ecd0399de07557f31bf98f3e3a39/href">https://medium.com/media/5179ecd0399de07557f31bf98f3e3a39/href</a></iframe><p>As you might have guessed from the quotes around “simply”, the moment we swapped our mock external function for a real one, things didn’t work so well. It turned out that Snowflake would just error out any time we used external functions with recursive CTEs. At first, we thought that there was something special about our particular pattern of invocation. Unfortunately, even after we simplified our prototype to the simplest possible case, we still had issues. We’d always receive a 300010 error after several seconds and we couldn’t find documentation for the code. While you can find client-side error codes in Snowflake’s <a href="https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/jdbc/ErrorCode.java">OSS clients</a>, server codes like 300010 aren’t referenced anywhere. Searches showed scenarios that roughly equated to “internal error”.</p><p>We reached out to Snowflake support, sharing the simple version of our scenario. After working through some joint diagnostics we got confirmation of what we thought: there was some kind of internal bug that was preventing these two features (which are both GA and which each work well on their own) from working together. We have to assume that we’re among the first to try using these features in combination in this way. Having built databases ourselves in the past, we know how hard permutation can be and were happy that we were able to get Snowflake support to agree there was a defect. As an interesting aside, while discussing this with Snowflake support, they mentioned that Java UDFs didn’t yet work with recursive CTEs. This wasn’t a problem for our pattern but was good to know and something we hadn’t seen in the documentation.</p><h3>A Fix Sooner than Expected</h3><p>At this point we were stuck in a holding pattern. Support said they had filed a Jira with engineering. As a relatively moderate purchaser of Snowflake credits, we weren’t exactly hopeful that this issue would be resolved quickly. We decided to put the whole initiative on the back burner since there didn’t seem to be any alternative.</p><p>The good news was, after less time than we expected support informed us that the bug was being fixed and being readied for release. Sure enough, as part of <a href="https://community.snowflake.com/s/article/6-33-Release-Notes-October-14-17-2022">Snowflake 6.33</a> (released just a few weeks after we initially raised this with Snowflake), the bug was fixed and Snowflake now supports combining recursive CTEs and external functions. We picked up our code from earlier and tested: sure enough the pattern that originally worked in our mock function now worked using an external function. Hooray!</p><p>Feel free to <a href="https://github.com/sundeck-io/snowflake-connector-framework">check out the code</a> or continue to our next post in this series, where we talk through <a href="https://blog.sundeck.io/paginating-mysql-access-for-snowflake-consumption-f09679255bb1">how we can use request &amp; response translators to paginate data</a> from MySQL into Snowflake.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ac89e86b237" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/yes-snowflake-external-functions-and-recursive-ctes-work-together-ac89e86b237">Yes, Snowflake External Functions and Recursive CTEs work together</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Paginated Table using Snowflake UDFs and Recursive CTEs]]></title>
            <link>https://blog.sundeck.io/building-a-paginated-table-using-snowflake-udfs-and-recursive-ctes-895ad3924e30?source=rss-34d9a7787247------2</link>
            <guid isPermaLink="false">https://medium.com/p/895ad3924e30</guid>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[integration]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Jacques]]></dc:creator>
            <pubDate>Mon, 10 Apr 2023 17:56:57 GMT</pubDate>
            <atom:updated>2023-04-10T17:56:57.960Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*g1fW0uK7UpjTTedx.png" /></figure><p><em>This is part two of our five-part series on inventing a source connector framework for Snowflake. If you’re interested in the entire journey, you might want to </em><a href="https://blog.sundeck.io/creating-a-source-connector-framework-for-snowflake-d0551a72ff32"><em>start at the beginning</em></a><em>. If you just want to try out the code, feel free to jump over to </em><a href="https://github.com/sundeck-io/snowflake-connector-framework"><em>our GitHub</em></a><em> and check things out.</em></p><p>In order for us to evaluate whether Snowflake UDFs work well with recursive CTEs, we decided to prototype a basic version. While our end goal was focused on using external functions, we decided to first implement a basic version entirely within Snowflake to confirm that we could get the pagination/variant/flatten interaction to work. This allowed us to prototype quickly and find out whether this pattern was feasible. First we created a mock JavaScript function that would act as our pseudo REST endpoint:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/acdce911b217cdad72b989f9580d544a/href">https://medium.com/media/acdce911b217cdad72b989f9580d544a/href</a></iframe><p>This UDF provides several pages of results and includes a token in each page that references a subsequent page. We then attempted to build a recursive CTE that would interact with this and output the sequence of data. This turned out to be more difficult than one would expect. Along the way we learned that you have to be very careful about how you use recursive CTEs in Snowflake (certain typical SQL patterns aren’t allowed). Luckily, there was enough richness to the Snowflake SQL experience to complete the task. A favorite was Snowflake’s support for in-select expression calculation references (where you can write code similar to select a+b as x, sin(x) from ...with x referring to a previously calculated expression. We did this because we weren’t confident we could trust Snowflake’s common subexpression elimination on external functions given the need to use the VOLATILE keyword property to accurately represent how our functions work. Using this pattern, we came up with the following fairly simple pagination sql expression:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6071348048db8346b0d2c553643a4532/href">https://medium.com/media/6071348048db8346b0d2c553643a4532/href</a></iframe><p>Side note: In a perfect world, Snowflake would provide better clarity on the impact of the VOLATILEfunction property. There is more nuance in databases than this property exposes. This indiciation is typically used to inform optimizers about the nature of whether an expression can be collapsed for things like constant reduction and common subexpression elimination. Depending on the nature of volatility, different things should be allowed. For example CURRENT_TIMESTAMP() is one form of volatile. In this case, it is different for each query but consistent across multiple invocations within a single query. This contrasts to the volatility of RANDOM()which is different for each invocation within a single query. In the case of Snowflake function support, they’ve collapsed these two different kinds of variation into a single category of volatile. Since we expect our operations to most often behave consistent with CURRENT_TIMESTAMP(), which can be consolidated in many cases, we wish we could better indicate as opposed to using the in-select prior expression reference pattern mentioned above.</p><p>At this point, we confirmed that we had the key operations working. Next, we needed to layer FLATTENon top of the recursive CTE. Yay, finally something tried and true! To package the basic functionality, we decided a SQL-based Snowflake UDTF was the right solution. It could encapsulate the underlying details to provide a relatively clean table interface. And a reminder, only SQL UTDFs can return tables. Just watch the gotcha: the EBNF for a table returning function is substantially different from other functions even though they all start the same way.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ad0d73d0bb36518bd5b3ccf529d38840/href">https://medium.com/media/ad0d73d0bb36518bd5b3ccf529d38840/href</a></iframe><p>Unfortunately, unlike stored procedures, UDTFs must declare the shape of their output at declaration time. Since our output could be different depending on what data we’re accessing, we decided that we’d just have to use “schema views” on top of each invocation so that we can have easily consumed artifacts for consumption elsewhere. By schema view we mean something that turns a schema-opaque variant object into a normal table.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ebb5e3819e4c5d3c3d27cbab376d7092/href">https://medium.com/media/ebb5e3819e4c5d3c3d27cbab376d7092/href</a></iframe><p>And with that, our prototype worked. The pieces went together as well as could be expected. This seems promising… and of course, we’re thinking we’re in the home stretch at this point, right?</p><p>Feel free to <a href="https://github.com/sundeck-io/snowflake-connector-framework">check out the code</a> or continue to our next blog on <a href="https://blog.sundeck.io/yes-snowflake-external-functions-and-recursive-ctes-work-together-ac89e86b237">making this functionality work with external functions</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=895ad3924e30" width="1" height="1" alt=""><hr><p><a href="https://blog.sundeck.io/building-a-paginated-table-using-snowflake-udfs-and-recursive-ctes-895ad3924e30">Building a Paginated Table using Snowflake UDFs and Recursive CTEs</a> was originally published in <a href="https://blog.sundeck.io/">sundeck</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>