<?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 Juhani Koskinen on Medium]]></title>
        <description><![CDATA[Stories by Juhani Koskinen on Medium]]></description>
        <link>https://medium.com/@juhanikoskinen?source=rss-c65feba09fb5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*B6I18eY8UuoorRG1j1cIow.jpeg</url>
            <title>Stories by Juhani Koskinen on Medium</title>
            <link>https://medium.com/@juhanikoskinen?source=rss-c65feba09fb5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 11:02:22 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@juhanikoskinen/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[Testing query optimization tips in BigQuery]]></title>
            <link>https://medium.com/@juhanikoskinen/testing-query-optimization-tips-in-bigquery-b1e417d5bf16?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/b1e417d5bf16</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Wed, 27 Sep 2023 17:20:30 GMT</pubDate>
            <atom:updated>2023-09-27T17:20:30.002Z</atom:updated>
            <content:encoded><![CDATA[<h4>Let’s see how BigQuery handles some easy examples</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lm9idZx0axf9Uls89sXRHg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@tvick?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Taylor Vick</a> on <a href="https://unsplash.com/photos/M5tzZtFCOfs?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>This is the second part of the series where I test a few query optimization techniques with Snowflake and Google’s BigQuery. <a href="https://medium.com/@juhanikoskinen/testing-query-optimization-tips-in-snowflake-8b65a4e177b0">In part one</a>, we tested Snowflake. Now, it is time to take BigQuery for a spin.</p><p>I’ve heard and read about several “tips” when it comes to writing queries in a columnar databases like Snowflake or BigQuery.</p><p>Some tips are for readability, or overall code quality, some relate to query optimization and how columnar databases shuffle data.</p><p>I wanted to test a few of these optimization techniques to see how different databases’ query optimizers handle them. These are very simple toy examples — the results could differ given a more complex query.</p><p>In this post, we’ll look at how BigQuery handles these tasks.</p><p>These examples use one of the public datasets available in BigQuery called “International Census Data”. The dataset <em>“provides estimates of country populations since 1950 and projections through 2050 — — data is provided for attributes including fertility rates, birth rates, death rates, and migration rates”.</em></p><p>The dataset is relatively small in size — it has only around 15000 rows and 15 columns.</p><p>In all of the examples, we do roughly the same thing: we take the average value of the male life expectancy per country. Then we join the average back to the original data and check whether the life expectancy in that country that year is bigger than the average of that country over time.</p><p>I also make sure to <a href="https://cloud.google.com/bigquery/docs/cached-results#disabling_retrieval_of_cached_results">disable using cached results</a>, because that would destroy the whole test.</p><p>All the examples are as close to 1-to-1 replicas of the queries used in <a href="https://medium.com/@juhanikoskinen/testing-query-optimization-tips-in-snowflake-8b65a4e177b0">my Snowflake post</a>.</p><h3>CTEs vs. subqueries</h3><p>I love CTEs. They make complex SQL so much more readable than an endless chain of nested subqueries.</p><p>There’s <a href="https://www.alisa-in.tech/post/2019-10-02-ctes/">a really popular blog post</a> that seems to indicate that CTEs are more performant than subqueries — at least when using Snowflake. Even GitLab refers to this blog post in their <a href="https://www.alisa-in.tech/post/2019-10-02-ctes/">SQL Style Guide</a> while advocating the use of CTEs.</p><p>Let’s see how BigQuery handles these.</p><h4>A single CTE and subquery</h4><p>The first version utilizes a single CTE to calculate the average:</p><pre>WITH average AS (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.country_code,<br>    IF(<br>        A.life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy` AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.country_code = B.country_code;</pre><p>The second version utilizes a subquery:</p><pre>SELECT<br>    A.country_code,<br>    IF(<br>        life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy` AS A<br>LEFT JOIN (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>    GROUP BY<br>        1<br>) AS B<br>    ON<br>        A.country_code = B.country_code;</pre><p>The results are <em>very</em> similar:</p><pre>| Query    | Elapsed Time | Bytes Processed |<br>| -------- | ------------ | --------------- |<br>| CTE      |       383 ms |       175.97 KB |<br>| Subquery |       360 ms |       175.97 KB |</pre><p>Both the elapsed time and the bytes processed metrics are practically the same. The slight variation in time elapsed can be attributed to variance. We can conclude that <strong>the two versions behave roughly identically</strong>.</p><h4>Multiple CTEs and Subqueries</h4><p>Let’s make this test a bit more complex and instead of a single CTE and subquery, we have two.</p><p>The first version still utilizes a CTE but joins that twice:</p><pre>WITH average AS (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.country_code,<br>    IF(<br>        life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg,<br>    IF(<br>        life_expectancy_male &lt; C.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_below_avg,<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy` AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.country_code = B.country_code<br>LEFT JOIN<br>    average AS C<br>        ON<br>            A.country_code = C.country_code;</pre><p>The second joins a subquery twice:</p><pre>SELECT<br>    A.country_code,<br>    IF(<br>        life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg,<br>    IF(<br>        life_expectancy_male &lt; C.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_below_avg,<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy` AS A<br>LEFT JOIN (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>    GROUP BY<br>        1<br>) AS B<br>    ON<br>        A.country_code = B.country_code<br>LEFT JOIN (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>    GROUP BY<br>        1<br>) AS C<br>    ON<br>        A.country_code = C.country_code;</pre><p>Again, the results are practically identical:</p><pre>| Query        | Elapsed Time | Bytes Processed |<br>| ------------ | ------------ | --------------- |<br>| 2 CTEs       |       438 ms |       175.97 KB |<br>| 2 Subqueries |       456 ms |       175.97 KB |</pre><p>This is a big difference compared to Snowflake. In Snowflake, the version with multiple subqueries processed a lot more data because it calculated each subquery separately instead of figuring out that it’s the same data we are using multiple times.</p><p>Looking at the results, and the execution graph provided in the BigQuery UI, it seems that <strong>BigQuery is “smart” enough to not calculate the subquery twice.</strong></p><h3>SELECT * vs. SELECT some</h3><p>The conventional wisdom is that due to the nature of how columnar databases store data selecting more columns than are needed to complete the query will be inefficient.</p><p>In this test, the first version selects all columns:</p><pre>WITH more_than_needed AS (<br>    SELECT<br>        *<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>),<br><br>average AS (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        more_than_needed<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.country_code,<br>    IF(<br>        life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy` AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.country_code = B.country_code;</pre><p>The second version selects only those columns that are needed:</p><pre>WITH what_is_needed AS (<br>    SELECT<br>        country_code,<br>        life_expectancy_male<br>    FROM<br>        `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>),<br><br>average AS (<br>    SELECT<br>        country_code,<br>        AVG(life_expectancy_male) AS avg_life_expectancy_male<br>    FROM<br>        what_is_needed<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.country_code,<br>    IF(<br>        life_expectancy_male &gt; B.avg_life_expectancy_male,<br>        TRUE,<br>        FALSE<br>    ) AS is_above_avg<br>FROM<br>    what_is_needed AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.country_code = B.country_code;</pre><p>The results are once again eerily similar:</p><pre>| Query        | Elapsed Time | Bytes Processed |<br>| ------------ | ------------ | --------------- |<br>| SELECT *     |       355 ms |       175.97 KB |<br>| SELECT some  |       371 ms |       175.97 KB |</pre><p>Again, the total execution times are identical. The amount of bytes processed is also identical suggesting that <strong>the two queries do roughly the same</strong>.</p><p>This is most likely due to the fact that in the final SELECT statement we only take a handful of columns: it is easy for the query optimizer to just skip the unnecessary columns that are included in the CTE.</p><p>Of course this does not mean that selecting unnecessary columns does not matter. Processing columns <em>is</em> inefficient in a columnar database. It is also a boost to readability if you explicitly state the columns you are using.</p><h3>Window-functions everywhere vs. GROUP BY</h3><p>This last one isn’t exactly anything I have heard a “best practice” about. But I have stumbled across a query similar to this one in my career.</p><p>The codebase I was working with had the following query where window functions were used quite interestingly:</p><pre>SELECT DISTINCT<br>    country_code,<br>    country_name,<br>    SUM(life_expectancy_male) OVER(<br>        PARTITION BY<br>            country_code,<br>            country_name<br>    ) AS life_expectancy_male_sum,<br>    AVG(life_expectancy_female) OVER(<br>        PARTITION BY<br>            country_code,<br>            country_name<br>    ) AS life_expectancy_female_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy`</pre><p>I really do not know what’s the story behind this query. There are a lot of things that don’t make much sense to me. Maybe it was written this way to tackle duplicate rows? But even then the calculation would be off because those window-functions would still contain the duplicates.</p><p>Anyway, we were basically utilizing window-functions where a simple “GROUP BY” would yield the same result:</p><pre>SELECT<br>    country_code,<br>    country_name,<br>    SUM(life_expectancy_male) AS life_expectancy_male_sum,<br>    AVG(life_expectancy_female) AS life_expectancy_female_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>GROUP BY<br>    1, 2</pre><p>The metrics show that the performance of the GROUP BY is way better:</p><pre>| Query     | Elapsed Time | Bytes Processed |<br>| --------- | ------------ | --------------- |<br>| WINDOW    |       517 ms |       457.32 KB |<br>| GROUP BY  |       246 ms |       457.32 KB |</pre><p><strong>The difference in elapsed time is so noticeable that I wouldn’t pin it on variance.</strong></p><p>The real life example this was copied from calculated dozens of metrics making the problem likely worse. As a bonus, using a GROUP BY makes the code a lot more concise and easier to work with.</p><h4>Bonus BigQuery tip</h4><p>When you have the same window-definition multiple times you can make your life a bit more concise and easy by using the following syntax:</p><pre>SELECT<br>    country_code,<br>    country_name,<br>    SUM(life_expectancy_male) OVER my_window AS life_expectancy_male_sum,<br>    AVG(life_expectancy_female) OVER my_window AS life_expectancy_female_avg<br>FROM<br>    `bigquery-public-data.census_bureau_international.mortality_life_expectancy`<br>WINDOW my_window AS (<br>    PARTITION BY<br>        country_code,<br>        country_name<br>)</pre><p><strong>You can define a window once at the end of the query</strong> with the “WINDOW [window_name] AS” syntax. This way you don’t have to copy and paste the same partitioning info over and over again.</p><h3>Key takeaways</h3><p>This limited test showed some interesting properties and differences compared to Snowflake that are useful to keep in mind:</p><ol><li>CTEs are not necessarily more efficient than subqueries. I will still take CTEs over subqueries any day because they <em>can be</em> more efficient and they make the code much more readable which is one of the most important aspects of code.</li><li>CTEs and subqueries are both “cached”. This is a difference compared to Snowflake suggesting that <em>in this particular example</em> BigQuery’s query optimizer performs better.</li><li>With simple queries like these using “SELECT *” won’t negatively affect your query performance.</li><li>GROUP BY beats window-functions in execution speed.</li></ol><p><strong>The big asterisk to all of the above points is that the examples were simple enough for BigQuery’s query optimizer to handle them efficiently.</strong> This would most likely change in a real work environment where transformations can be a combination of dozens of complex steps over thousands of lines of SQL.</p><p>If you have another database in mind where I could try something similar, leave a comment! I would also love to hear your experiences in optimizing query performance in columnar databases.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b1e417d5bf16" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Testing query optimization tips in Snowflake]]></title>
            <link>https://medium.com/@juhanikoskinen/testing-query-optimization-tips-in-snowflake-8b65a4e177b0?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/8b65a4e177b0</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Thu, 14 Sep 2023 18:23:23 GMT</pubDate>
            <atom:updated>2023-09-16T14:58:15.101Z</atom:updated>
            <content:encoded><![CDATA[<h4>Let’s see how Snowflake handles some easy examples</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8aiam5OZOTq5E3eQVKwl3g.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@lotusflowur?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Mihika</a> on <a href="https://unsplash.com/photos/hc1zbljZfSQ?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>I’ve heard and read about several “tips” when it comes to writing queries in a columnar databases like Snowflake or BigQuery.</p><p>Some tips are for readability, or overall code quality, some relate to query optimization and how columnar databases shuffle data.</p><p>I wanted to test a few of these optimization techniques to see how different databases’ query optimizers handle them. These are very simple toy examples — the results could differ given a more complex query.</p><p>In this post, I’ll show you the results I got with Snowflake.</p><p>In the next post, we’ll look at how Google’s BigQuery handles similar tasks.</p><p>These examples use <a href="https://www.avoindata.fi/data/fi/dataset/espoon-kaupungin-ostot">public municipality data</a> available in Finland. The data represents all purchases made by a city in a given time frame. The dataset holds roughly 1.4M rows with 60ish columns.</p><p>I also tell Snowflake to not use cache results — that would defeat the whole purpose of this exercise.</p><p>In all of the examples we do roughly the same thing: we take the average value of a purchase over a few columns. Then we join the average back to the original data and check whether the value of that row is bigger than the average of the group.</p><p>We need to do some casting because Finnish numbers use a decimal comma — not a decimal point. I have also changed the names of the columns from Finnish to English to make the examples a bit more easier to understand.</p><h3>CTEs vs. subqueries</h3><p>I love CTEs. They make complex SQL so much more readable than an endless chain of nested subqueries.</p><p>There’s even <a href="https://www.alisa-in.tech/post/2019-10-02-ctes/">a really popular blog post</a> that seems to indicate that CTEs are more performant than subqueries. Even GitLab refers to this blog post in their <a href="https://www.alisa-in.tech/post/2019-10-02-ctes/">SQL Style Guide</a> while advocating the use of CTEs.</p><p>Naturally, this will be our first test.</p><h4>A single CTE and subquery</h4><p>The first version utilizes a single CTE to calculate the average:</p><pre>WITH average AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        F_PURCHASES<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG<br>FROM<br>    F_PURCHASES AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.MUNICIPALITY_ID = B.MUNICIPALITY_ID;</pre><p>The second version utilizes a subquery:</p><pre>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG<br>FROM<br>    F_PURCHASES AS A<br>LEFT JOIN (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        F_PURCHASES<br>    GROUP BY<br>        1<br>) AS B<br>    ON<br>        A.MUNICIPALITY_ID = B.MUNICIPALITY_ID;</pre><p>The results are <em>very</em> similar:</p><pre>| Query    | Total Execution Time | Bytes Scanned |<br>| -------- | -------------------- | ------------- |<br>| CTE      |               577 ms |       5.39 MB |<br>| Subquery |               565 ms |       5.39 MB |</pre><p>Both the execution time and the bytes scanned metrics are practically identical. We can conclude that <strong>the two versions behave roughly identically</strong>.</p><h4>Multiple CTEs and Subqueries</h4><p>Let’s make this test a bit more complex and instead of a single CTE and subquery, we have two.</p><p>The first version still utilizes a CTE but joins that twice:</p><pre>WITH average AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        F_PURCHASES<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &lt; C.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_UNDER_AVG<br>FROM<br>    F_PURCHASES AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.MUNICIPALITY_ID = B.MUNICIPALITY_ID<br>LEFT JOIN<br>    average AS C<br>        ON<br>            A.MUNICIPALITY_ID = C.MUNICIPALITY_ID;</pre><p>The second joins a subquery twice:</p><pre>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &lt; C.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_UNDER_AVG<br>FROM<br>    F_PURCHASES AS A<br>LEFT JOIN (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        F_PURCHASES<br>    GROUP BY<br>        1<br>) AS B<br>    ON<br>        A.MUNICIPALITY_ID = B.MUNICIPALITY_ID<br>LEFT JOIN (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        F_PURCHASES<br>    GROUP BY<br>        1<br>) AS C<br>    ON<br>        A.MUNICIPALITY_ID = C.MUNICIPALITY_ID;</pre><p>This time around, we notice some differences in the key metrics:</p><pre>| Query        | Total Execution Time | Bytes Scanned |<br>| ------------ | -------------------- | ------------- |<br>| 2 CTEs       |               628 ms |       5.39 MB |<br>| 2 Subqueries |               722 ms |       8.08 MB |</pre><p>The differences in query times are minimal and probably due to variance. What stands out is the “Bytes Scanned” metric.</p><p><strong>The version with two subqueries scans way more bytes.</strong> This is most likely because Snowflake recognizes that we are utilizing the same CTE twice and only processes it once whereas it does not recognize that the subqueries are identical.</p><p>Snowflake UIs query profile shows that the subquery version has three table scans — the CTE version has only 2. Looking closer to the table scan nodes one notices that <strong>the subqueries are in fact processed separately</strong>.</p><h3>SELECT * vs. SELECT some</h3><p>The conventional wisdom is that due to the nature of how columnar databases store data selecting more columns than are needed to complete the query will be inefficient.</p><p>In this test, the first version selects all columns:</p><pre>WITH more_than_needed AS (<br>    SELECT<br>        *<br>    FROM<br>        F_PURCHASES<br>),<br>average AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        more_than_needed<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG<br>FROM<br>    more_than_needed AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.MUNICIPALITY_ID = B.MUNICIPALITY_ID;</pre><p>The second version selects only those columns that are needed:</p><pre>WITH what_is_needed AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        RECEIPT_ID,<br>        EUR<br>    FROM<br>        F_PURCHASES<br>),<br>average AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        AVG(CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC)) AS AVG_EUR<br>    FROM<br>        what_is_needed<br>    GROUP BY<br>        1<br>)<br><br>SELECT<br>    A.RECEIPT_ID,<br>    A.MUNICIPALITY_ID,<br>    IFF(<br>        CAST(REPLACE(A.EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) &gt; B.AVG_EUR,<br>        TRUE,<br>        FALSE<br>    ) AS IS_ABOVE_AVG<br>FROM<br>    what_is_needed AS A<br>LEFT JOIN<br>    average AS B<br>        ON<br>            A.MUNICIPALITY_ID = B.MUNICIPALITY_ID;</pre><p>The results are once again practicaly identical:</p><pre>| Query        | Total Execution Time | Bytes Scanned |<br>| ------------ | -------------------- | ------------- |<br>| SELECT *     |              1500 ms |       2.71 MB |<br>| SELECT some  |              1500 ms |       2.71 MB |</pre><p>Again, the total execution times are identical. The amount of bytes scanned are also identical suggesting that <strong>the two queries do roughly the same</strong>.</p><p>This is most likely due to the fact that in the final SELECT statement we only take a handful of columns: it is easy for the query optimizer to just skip the unnecessary columns that are included in the CTE.</p><p>Of course this does not mean that selecting unnecessary columns does not matter. Processing columns <em>is</em> inefficient in a columnar database. It is also a boost to readability if you explicitly state the columns you are using.</p><p>Funnily enough, even though these queries are logically identical to the CTE vs Subquery example, the number of bytes scanned has dropped significantly.</p><h3>Window-functions everywhere vs. GROUP BY</h3><p>This last one isn’t exactly anything I have heard a “best practice” about. But I have stumbled across a query similar to this one in my career.</p><p>The codebase I was working with had the following query where window functions were used quite interestingly:</p><pre>WITH BASE_ AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        COMPANY_ID,<br>        CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) AS EUR,<br>        CAST(<br>            REPLACE(TOTAL_EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC<br>        ) AS TOTAL_EUR<br>    FROM<br>        F_PURCHASES<br>)<br><br>SELECT DISTINCT<br>    MUNICIPALITY_ID,<br>    COMPANY_ID,<br>    SUM(EUR) OVER(<br>        PARTITION BY<br>            MUNICIPALITY_ID,<br>            COMPANY_ID<br>    ) AS EUR_SUM,<br>    AVG(TOTAL_EUR) OVER(<br>        PARTITION BY<br>            MUNICIPALITY_ID,<br>            COMPANY_ID<br>    ) AS TOTAL_EUR_AVG<br>FROM<br>    BASE_;</pre><p>I really do not know what’s the story behind this query. There are a lot of things that don’t make much sense to me. Maybe it was written this way to tackle duplicate rows? But even then the calculation would be off, because those window-functions would still contain those duplicates.</p><p>Anyway, we were basically utilizing window-functions where a simple “GROUP BY” would yield the same result:</p><pre>WITH BASE_ AS (<br>    SELECT<br>        MUNICIPALITY_ID,<br>        COMPANY_ID,<br>        CAST(REPLACE(EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC) AS EUR,<br>        CAST(<br>            REPLACE(TOTAL_EUR, &#39;,&#39;, &#39;.&#39;) AS NUMERIC<br>        ) AS TOTAL_EUR<br>    FROM<br>        F_PURCHASES<br>)<br><br>SELECT<br>    MUNICIPALITY_ID,<br>    COMPANY_ID,<br>    SUM(EUR) AS EUR_SUM,<br>    AVG(TOTAL_EUR) AS TOTAL_EUR_AVG<br>FROM<br>    BASE_<br>GROUP BY<br>    1, 2;</pre><p>The metrics show that the performance of the GROUP BY is way better:</p><pre>| Query     | Total Execution Time | Bytes Scanned |<br>| --------- | -------------------- | ------------- |<br>| WINDOW    |               655 ms |       6.51 MB |<br>| GROUP BY  |               286 ms |       6.51 MB |</pre><p><strong>The difference in execution time is so noticeable that I wouldn’t pin it on variance.</strong></p><p>The real life example this was copied from calculated dozens of metrics making the problem likely worse. As a bonus, using a GROUP BY makes the code a lot more concise and easier to work with.</p><h3>Key takeaways</h3><p>This limited test showed some interesting properties that are useful to keep in mind:</p><ol><li>CTEs are not necessarily more efficient than subqueries. I will still take CTEs over subqueries any day because they <em>can be</em> more efficient and they make the code much more readable which is one of the most important aspects of code.</li><li>CTEs are “cached” whereas subqueries are processed as being “unique”.</li><li>With simple queries like these using “SELECT *” won’t negatively affect your query performance.</li><li>GROUP BY beats window-functions in execution speed.</li></ol><p><strong>The big asterisk to all of the above points is that the examples were simple enough for Snowflake’s query optimizer to handle them efficiently.</strong> This would most likely change in a real work environment where transformations can be a combination of dozens of complex steps over thousands of lines of SQL.</p><p>Stay tuned for the next part of the series where I will take BigQuery on a similar journey! In the meanwhile, I would like to hear your experiences in optimizing Snowflake queries.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8b65a4e177b0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Dear software developers, your job is not to write code]]></title>
            <link>https://medium.com/@juhanikoskinen/dear-software-developers-your-job-is-not-to-write-code-f7ce70d20773?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/f7ce70d20773</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[careers]]></category>
            <category><![CDATA[software-engineering]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Wed, 23 Aug 2023 18:10:34 GMT</pubDate>
            <atom:updated>2023-08-29T04:51:37.210Z</atom:updated>
            <content:encoded><![CDATA[<h4>It can be a part of it, but it is not the purpose of it</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uSyAedamYOBGVH2PonGpZQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@sharonmccutcheon?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Alexander Grey</a> on <a href="https://unsplash.com/photos/dW6dFBoHUu4?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Software developers are funny. They make claims that their job is to write code or that they are a “Java developer”. One person I talked to recently even claimed that a “software developer will get fired if they don’t produce code”.</p><p>It’s funny to me because I think this is totally wrong. At the very least, it misses completely the point of what a job is or why jobs exist. Software <em>something</em> is, at the end of the day, a job. It can be a part of your identity, but it’s still “just a job” like any other.</p><p>As the title says, a software developers job is not to write code. It may be a part of his daily tasks but it is not their job.</p><p>What is it then? What’s the job of a software developer?</p><h4>The Anatomy of a Job</h4><p>Let’s take a small step back.</p><p>Ask yourself what a data analyst, a janitor and a widget polisher have in common. On the surface, it does not seem that there’s anything connecting these jobs — or that of a software developer. If you think that’s the case, that’s okay, but you are not looking deep enough.</p><p>Whether you are a data analysts, or a widget polisher, or a burger flipper there is one thing that is common and that explains your existence: <strong>value</strong>. Your function is ultimately to create value. The job is there because it generates more value than it costs.</p><p>That’s it.</p><p>But what about all those people who’s LinkedIn pages and Slack descriptions and employment contracts say “software developer” or “DevOps engineer” or “data scientist”? Shouldn’t we all be called “value creators”? What’s that all about?</p><p>Well, some of it is probably due to legislation which I’m no expert in.</p><p>But more broadly speaking it’s a way to describe what kind of skills a person has. A finance analyst knows what’s free cash flow or EBITDA and can deduce what those numbers mean for the business. A marketing manager knows what kind of messaging reaches the company’s customer base.</p><p>A software developer can fix problems and generate value by writing code.</p><p>It still does not mean that their <em>job</em> is to write code. It just means that they have this rare skillset and that they <em>can</em> write code if that maximizes the value they generate. If organizing paper plane flying competitions maximizes the value a person generates, then they should be organizing those competitions.</p><p>This is rarely the case for software developers, hence they are writing code.</p><h4>Software Development is so Much More</h4><p>Think back to your latest work week.</p><p>How much time did you actually spend writing code? Honestly. How much did you spend time thinking about the architecture of that new feature? Or reviewed someone else’s code? Or helped your colleague get familiar with a tool? Or talked to your stakeholders about that new feature?</p><p>When you did those activities, did you stop being a software developer? Of course not because all those activities were important and generated value.</p><p>Your job is not just “writing code”. It is not “writing Java”. In an ideal world, all those other activities generated the maximum amount of value and hence you did them.</p><p>Again, coding is just one aspect. Highlighting it, or describing yourself only through the code you generate, oversimplifies your job as a software engineer and you as a professional.</p><p>It’s all about generating value. If you want to progress in your career and get fancier titles and softer bread on your table then stop fixating about writing code. It’s fun, I know. But it is not your job.</p><p>Code is a tool for you to potentially create an immense amount of value, but don’t hinder your potential by fixating on <em>that</em> alone.</p><p>Your job is to figure out what you can do that maximizes the value you create for the company you work for and, well, do that.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f7ce70d20773" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[5 Lessons After 5 Years in Data (Science)]]></title>
            <link>https://medium.com/@juhanikoskinen/5-lessons-after-5-years-in-data-science-7e9026829505?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/7e9026829505</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Thu, 21 Jul 2022 11:11:06 GMT</pubDate>
            <atom:updated>2022-07-21T11:11:06.061Z</atom:updated>
            <content:encoded><![CDATA[<h4>Little pieces of knowledge I’ve picked up over the years.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ghqwTCJgmchzePOOsIcNwg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@niklas_hamann?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Niklas Hamann</a> on <a href="https://unsplash.com/s/photos/meadow?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><h4>1. Data is always crap</h4><p>Missing values, incorrect values, timezone mishaps, inconsistent data types… Data is always messy and you should expect it to be. I am yet to see an organization that has it all figured out.</p><p>Don’t trust any source data. Look through it. I mean <em>REALLY</em> look through it. Interview subject matter experts. Interview the people running the system that produces the data. They usually know — you are just making educated guesses.</p><p>Bonus tip: Do your data transformations within the database as much as possible — it’s most likely much more efficient than your Pandas scribbles.</p><h4>2. Start with intuition, finish with solid theory</h4><p>Having a statistical intuition, a rough idea of how things should be, will help you move fast. You can ditch ideas quickly and double down on ideas that might work.</p><p>No, you do not need to know every little detail or every single assumption behind a distribution/technique, but you need to know what could work and what most likely won’t work.</p><p>Try the ideas that might work, and then make sure that those ideas have a solid theoretical base.</p><h4>3. It’s rarely about the accuracy</h4><blockquote>“No-one ever said that the project would have been successful if only the model had been 1 % more accurate”</blockquote><p>I saw the above statement on LinkedIn and find it to be hauntingly true.</p><p>In my experience, data science projects usually fail because:</p><ul><li>there’s no suitable data for solving the problem</li><li>the problem definition itself is poor</li></ul><p><em>Either there’s no data or there is not a proper use case that brings value.</em></p><p>Forget about the fancy model that is complex even if it might be a bit more accurate. Start with a simple one and validate that it brings value.</p><p>If it does, then you have a great argument for allocating more time and resources to the problem.</p><p>If it does not, then fail fast and move on to the next promising venture.</p><p>If you start with coolest, latest new technique you might spend an awful lot on something that never becomes anything.</p><h4>4. Software engineering principles matter</h4><p>I live by the mantra “you build it, you run it”.</p><p>I used to work in a team of data scientists where the models we developed were pushed to another team to deploy.</p><p>I hated that working model.</p><p>It led to crooked incentives where data scientists did not need to care about software engineering or cloud infrastructure.</p><p>If your Jupyter Notebooks provided good results, you were golden. It was somebody else’s problem to turn that big pile of badly written code snippets into a proper product.</p><p>Modern data teams, and therefore data scientists, should in my opinion be responsible for the whole lifecycle of their work. That means not just playing around in a Notebook but also deploying the end result into a production environment and running it there.</p><p>If you work in a team like this, then you need to adopt a software engineering mindset: write quality code, test your code, design the data pipelines to be robust and document your work.</p><p>The added benefit is that you will develop a much broader range of valuable skills.</p><h4>5. You will often feel like a fraud and occasionally a genius</h4><p>The data landscape is vast and evolves all the time. Even after 5 years there are a gazillion things I know nothing about. This might make you feel that you don’t know anything. At least that’s how I feel sometimes.</p><p>What you need to understand is that it’s okay to not know.</p><p>You can’t be an expert on everything.</p><p>Time series are so hard. So is A/B testing. And deep learning. And numerical optimization. And distributed databases. And communication. And writing good software. And, well, most things.</p><p>Just because there are a lot of things you do not know does not make you a bad data-whatever. You still have valuable expertise and experience.</p><p>That knowledge will get its chances to shine.</p><p>With more expertise those chances happen more often.</p><p>And when they do, when that light bulb lights up in your brain, you get such a rush of positivity and confidence that you forget about all your doubts for a while. You will feel like a genius.</p><p>Remember those moments and trust yourself.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7e9026829505" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[My Top 2 Reasons to Implement Type Hinting in Python]]></title>
            <link>https://medium.com/@juhanikoskinen/my-top-2-reasons-to-implement-type-hinting-in-python-10b488c0ffbd?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/10b488c0ffbd</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Sun, 12 Jun 2022 10:53:07 GMT</pubDate>
            <atom:updated>2022-06-12T10:54:31.285Z</atom:updated>
            <content:encoded><![CDATA[<h4>In my opinion, there are two main reasons to utilize Python’s type hinting. Let’s explore those reasons in more detail.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ap6jXDBI3FYD3noP2hOAOw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@christianw?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Christian Wiediger</a> on <a href="https://unsplash.com/s/photos/computer?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Python 3.5 <a href="https://realpython.com/lessons/type-hinting/">introduced</a> type hinting to the language’s standard library. In Python 3.6 the concept was <a href="https://realpython.com/lessons/type-hinting/">extended</a> from function annotations to variable annotations. <a href="https://realpython.com/lessons/type-hinting/">Type hinting</a> can be used to “statically indicate the type of a value within your Python code”.</p><p>Personally, I use type hints in all of my work and I couldn’t think of writing production-grade Python code without utilizing them. I’ve noticed that there are 2 key reasons why one should use type hints: improved <strong>readability</strong> and <strong>planning</strong>.</p><p>In this post, I’ll explore those concepts in more detail.</p><h3>How to use type hints</h3><p>Using type hints is a breeze. If you have a relatively new version of Python, the standard library includes a library called typing which contains the support for type hints.</p><p>Here’s a little example of how type hints could look like in your application code:</p><pre>from typing import List</pre><pre>def divide_by_number(input: List[int], number: int) -&gt; List[int]:<br>    &quot;&quot;&quot;Divides every element in input with the given number&quot;&quot;&quot;<br>    return [x/number for x in input]</pre><p>From the example above you can notice a few interesting remarks:</p><ul><li>You can type hint both the arguments and the return value of a function</li><li>Variables are type hinted with : type syntax</li><li>Some data types need not be imported from typing</li></ul><p>Additionally you can define your own types, use the classes from your application code as types, use <a href="https://docs.python.org/3/library/typing.html#typing.TypeVar">type variables</a>… The possibilities are endless!</p><p>Now that you know the bare minimum about type hints let’s turn our focus to the two main aspects that I think add the greatest value.</p><h3>Improved readability</h3><p>The cliché is that <strong>code is read more often than it is written </strong>and type hints make your code much more readable.</p><p>Let’s take a look at the same code example with and without type hints:</p><pre>def parse_data(data):<br>    result_dict = {}<br>    for i, row in data.iterrows():<br>        if row[&quot;class&quot;] not in result_dict:<br>            results_dict[row[&quot;class&quot;]] = []<br>        result_dict[row[&quot;class&quot;]].append(row[&quot;value&quot;])<br>    return result_dict</pre><p>Here, we have a function that parses some data. What’s the expected input? Without a <a href="https://peps.python.org/pep-0257/">docstring</a> the data can be basically anything: a list, a dict, a <a href="https://numpy.org/doc/stable/reference/generated/numpy.array.html">numpy array</a> — you name it!</p><p>It’s also unclear what it actually returns. You really have to read through the method line by line to figure out what’s the output going to look like.</p><p>Let’s add type hints into the example:</p><pre>def parse_data(data: pd.DataFrame) -&gt; Dict[str, List[str]]:<br>    result_dict = {}<br>    for i, row in data.iterrows():<br>        if row[&quot;class&quot;] not in result_dict:<br>            results_dict[row[&quot;class&quot;]] = []<br>        result_dict[row[&quot;class&quot;]].append(row[&quot;value&quot;])<br>    return result_dict</pre><p>Immediately the method is more clear to the reader. You know that the function expects a <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html">Pandas DataFrame</a> and returns a dict. You even know what’s the expected structure of the returned dict.</p><p>Without a docstring (or a better function name) you still have to read through the actual implementation to understand what’s going on but the mental overhead is much smaller with type hints. You don’t have to deduce the inputs and outputs. They are there in writing for you to see all the time.</p><p>This example has a function with only one parameter, a few lines and the readability is still improved. Imagine the improvement if the function had 4+ parameters. And five times the lines of code.</p><p>The improved readability is especially important when you are glancing through a codebase. The different inputs and outputs are clearly defined which speeds up the reading. The code is much easier to follow — the writing is more <em>complete</em>.</p><h3>Improved planning</h3><p>When I’m writing some totally new application code I have the habit of first putting the needed methods in place without the actual implementation.</p><p>After a while a Python-file I’m working on might look something like this:</p><pre>def collect_strings(input: List[int], parameter: str) -&gt; List[str]:<br>    raise NotImplementedError</pre><pre>def do_something_meaningful_for_int(input: int) -&gt; int:<br>    raise NotImplementedError</pre><p>At this point you don’t really care about the implementation details, but you know what the different pieces of your code should/might do.</p><p>The quality of planning is greatly increased since you have to actively think, and write down, the data structures passed around in your application. This will help you spot potential errors before committing to the actual implementation.</p><p>Without type hints, the process would be quite different and not as useful. Without type hints, the above block would just be:</p><pre>def collect_strings(input, parameter):<br>    raise NotImplementedError</pre><pre>def do_something_meaningful_for_int(input):<br>    raise NotImplementedError</pre><p>Yes, the same pieces are there, but the only information you have are the method names. The mental overhead is greater when you have to “memorize” core details about the methods.</p><p>Walking through the draft with a coworker is much simpler since the reviewer can also see the type hints. They can concentrate on the overall structure of the code instead of keeping track of different input and output types.</p><h3>Conclusions</h3><p>Type hints are something I wish were present in every Python codebase. They make your code more readable, easier to understand and help you plan your code.</p><p>With that being said, Python’s type hints will not prevent you from passing around objects of the wrong type — there’s no compiler to throw you an error. There are tools that help with this and I am confident in arguing that combining type hints with a type checker like <a href="http://mypy-lang.org/">mypy</a> will allow you to write clearer and less buggy code.</p><p>What are your favourite reasons for using type hints?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=10b488c0ffbd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing a Widget Company with Poisson Distribution]]></title>
            <link>https://medium.com/@juhanikoskinen/optimizing-a-widget-company-with-poisson-distribution-e6b80b1a8f33?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/e6b80b1a8f33</guid>
            <category><![CDATA[probability]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Sun, 29 May 2022 14:13:26 GMT</pubDate>
            <atom:updated>2022-05-29T14:25:06.091Z</atom:updated>
            <content:encoded><![CDATA[<h4>How to maximize profits with a little help from statistics</h4><p>Let’s imagine you are leading a small manufacturing plant that produces <em>widgets</em>. You sell those <em>widgets</em> directly to consumers and each <em>widget</em> takes some time to produce. The plant produces three types of <em>widgets</em>: A, B and C.</p><p>Each day you have some fixed production capacity to produce more <em>widgets</em>. As a greedy (rational) manufacturing leader you want to maximize your profits. Each day you face a question of <em>“which widgets should I produce today if I want the most profits?”</em></p><p>Because you took a few courses in probability theory in the University of Widget Manufacturing, you decide to put those skills in to use.</p><h3>Poisson Distribution</h3><p>Your memories of probabilities suggests that the sales of <em>widgets</em> could probably be represented by a Poisson distribution. After all, Poisson distribution represents <em>discrete events in time</em>.</p><p>To refresh your memory, you do what every professional does and google for the specifics of the distribution. The first link redirects you to <a href="https://en.wikipedia.org/wiki/Poisson_distribution">Wikipedia</a>, which states the following:</p><blockquote><strong>Poisson distribution</strong> is a <a href="https://en.wikipedia.org/wiki/Discrete_probability_distribution">discrete probability distribution</a> that expresses the probability of a given number of events occurring in a fixed interval of time or space if these events occur with a known constant mean rate and <a href="https://en.wikipedia.org/wiki/Statistical_independence">independently</a> of the time since the last event.</blockquote><p>You glance at the reference of the sentence, but because it looks like a scientific publication, which is likely behind a paywall, you decide to just trust it and proceed with your thinking.</p><p>You note a few key ideas in the definition:</p><ul><li>Fixed interval of time</li><li>Known constant mean rate</li><li>Events occur independently</li></ul><p>Your hunch about Poisson distribution fitting the problem still seems promising:</p><ul><li>You are deciding the production plan for a day so the time interval is fixed.</li><li>Against all odds your sales numbers seem to be the only sales numbers in the universe that don’t have any seasonality so your sales have an approximately constant rate.</li><li>You sell directly to consumers so it is likely that they are all independent agents. You don’t even use influencers in your marketing which gives you even more confidence that the assumption holds.</li></ul><p>You conclude that you can model your sales by the distribution <em>Poisson(</em>λ<em>)</em> where λ is the mean rate of sales and continue reading.</p><h4>Survival Function</h4><p>The probability mass function (pmf) tells us the probability that we get a value <em>v </em>if we draw from some distribution. In Poisson distribution’s case it is <em>the probability of n events in time period t.</em> In other words, what’s the probability that we sell 10 <em>widgets</em> in a day if the daily <em>widget</em> sales follow <em>Poisson(20) </em>distribution?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/710/1*cqrObWYZssm5bHHhSMkDow.png" /><figcaption>Probability Mass Function for different Poisson distributions</figcaption></figure><p>For your manufacturing problem, the <strong>survival function</strong> is more interesting. Survival function tells us the probability that we draw a <em>v</em> <strong>or higher</strong> if our process follows some distribution. For example, if our sales follow <em>Poisson(20)</em> then we have approximately 44 % probability of selling <strong>20 <em>widgets</em> or more</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/695/1*bZjbgtT87P1qwdMnHtmuWg.png" /><figcaption>Survival Function for different Poisson distributions</figcaption></figure><h3>Data</h3><p>Looking at your data you notice that <em>widget A</em> sells roughly 50 units each day. <em>Widgets B</em> and <em>C</em> sell 20 and 70 units each day, respectively. Each <em>widget</em> has the same manufacturing cost, but a different sales price. Your plant can produce 100 units each day.</p><pre>+-----------------------------------------+<br>| <em>Widget</em>    | Sales per Day | Sales Price |<br>+-----------------------------------------+<br>| A         | 50            | 100         |<br>| B         | 20            | 150         |<br>| C         | 70            | 80          |<br>+-----------+---------------+-------------+</pre><p>Survival function tells you the probability of selling at least <em>n</em> units or the probability of selling <strong>an additional unit</strong>. Add in the sales price of a <em>widget</em>, and you can calculate the <strong>expected value (income)</strong> of each unit produced.</p><p>Your thinking is the following: the first unit produced is very likely to sell and so is the second. But every time I produce a new unit, the probability of that unit being sold decreases and so the expected value of producing that unit also decreases. If there is only a 40 % chance of selling an additional unit the value of that unit is not the sales price, but 40 % of that!</p><p>More precisely, the expected value can be calculated as: <strong>the probability of selling <em>Nth</em> unit * sales price</strong>, where the probability can be obtained from the survival function.</p><p>For <em>widgets</em>, the expected values look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/710/1*pKNL2PqVyEakbDZHfrTo0Q.png" /><figcaption>Expected values for widget<em> variations</em></figcaption></figure><p>Now your task as the plant manager is easy: if you can produce 100 units each day, pick the 100 units that have the highest expected value.</p><p>Looping over the arrays that made the above graph possible, you arrive at the following production plan:</p><pre>+----------------------------+<br>| <em>Widget</em>    | Units Produced |<br>+-----------+----------------+<br>| A         | 44             |<br>| B         | 29             |<br>| C         | 27             |<br>+-----------+----------------+</pre><p>With the above methodology, you can produce exactly those <em>widgets</em> that <strong>maximize your revenue.</strong></p><p>You pad yourself to the back, say a silent “thank you” to your college’s statistics professor and head home confident knowing that tomorrow your plant will have a major upgrade in production planning!</p><h3>Next steps</h3><p>Next night you suddenly wake up. You realized that your analysis is incomplete! What if the inventories aren’t empty each day? Your analysis assumes that each day you start from scratch, but that’s rarely the case — there’s always some <em>widgets</em> in your inventories that haven’t been sold.</p><p>After panicking for a few minutes you realize that your calculations are easily extended to take that into account: the expected value of a new unit produced needs to just take the inventory situation into account. If there are 10 units of variation A in the inventory already, then the next unit is the 11th unit. The probability of selling that unit is not the value of the survival function at point 1, but at point 11.</p><p>You make a small entry about this into your note taking app so that you remember it in the morning and go back to sleep.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e6b80b1a8f33" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Do Not Format Your SQL Manually]]></title>
            <link>https://medium.com/@juhanikoskinen/do-not-format-you-sql-manually-a50a5fd0b83f?source=rss-c65feba09fb5------2</link>
            <guid isPermaLink="false">https://medium.com/p/a50a5fd0b83f</guid>
            <category><![CDATA[code-style]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Juhani Koskinen]]></dc:creator>
            <pubDate>Fri, 20 May 2022 17:51:58 GMT</pubDate>
            <atom:updated>2022-06-19T12:46:52.646Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*amgZBwW3pBnsE2qQCSOPAQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@markusspiske?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Markus Spiske</a> on <a href="https://unsplash.com/s/photos/data?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>In a company I worked at all data transformations lived inside one big repository. The company was relatively small and so the number of people working with that repository was limited.</p><p>The repository had no agreed upon best practices. It was a wild west of SQL where each actor had their own “best” practices. After a while, I knew who had written the code by just looking at the <em>style</em> of the code. No need for git blame — if table aliases were implicit, it had to be John. If all SQL keywords were lower case it was most likely Tina.</p><p>One Tuesday morning (it wasn’t actually Tuesday. Or Morning. To be honest I don’t even remember the exact month) me and some other senior data people in the organization got together to discuss the mayhem that had evolved.</p><p>The conversation quickly turned to having a style guide for our organization’s SQL. The first draft came to fruition relatively quickly. But how to enforce it? How to make SQL quick to write but have it conform to our style guide?</p><p>The solution is simple thanks to a great open source project called <a href="https://github.com/sqlfluff/sqlfluff">SQLFluff</a>.</p><h3>Automatic Linting</h3><p>It’s much quicker to write SQL keywords in lower case. One doesn’t need to hold down the shift key or keep hitting CapsLock. At the same time, reading SQL were keywords are upper cased makes the reading more pleasurable.</p><p>The idea of automatic linting is that you can write your code however you like and the linting tool will correct the <em>style</em> of the code. It won’t fix your bugs, but it makes conforming to a style guide a breeze.</p><p>Here’s an outrageous example of how someone could write a SQL query:</p><pre>select *, col_a col_renamed,<br>CASE when col_c = NULL then 1 ELSE 0<br>END as is_it_null<br>FROM<br>some_table LEFT JOIN another_table AS foo<br>ON col_d = col_e</pre><p>As you can see, the formatting is literally all over the place. Some SQL keywords are upper case, some are lower. Some aliases are implicit, some are explicit. The above example makes me want to cry and it should have the same effect on you.</p><p>With SQLFluff installed and configured, we can run the following command:</p><pre>sqlfluff fix /path/to/the/nightmare_query.sql</pre><p>Which transforms the query into this:</p><pre><br>SELECT<br> *,<br> col_a AS col_renamed,<br> CASE WHEN col_c IS NULL THEN 1 ELSE 0<br>END AS is_it_null<br>FROM<br> some_table LEFT JOIN another_table<br> ON col_d = col_e</pre><p>Ain’t that a beauty.</p><p>The linting step can also be automated! Add SQLFluff as a <a href="https://pre-commit.com/">pre-commit hook</a> to your repository and every time a developer makes a commit their changes are automatically fixed according to the rules you have set up.</p><p>Do not fight a losing battle by trying to manually enforce a style guide. Let automatic tools take care of that.</p><h3>The world is not complete</h3><p>SQLFluff is awesome and I highly suggest organizations to use it. It’s not perfect though. I have some grievances with how SQLFluff formats the above example even after I have manually configured every rule SQLFluff offers.</p><p>Still, it’s better to have a not-perfect automatic linting than no linting at all. You can always check the results of the linter and manually change the bits you don’t agree with.</p><p>And, even if SQLFluff is able to automaticaly correct only half of the style mistakes, it’s still time that you can spend honing the business logic of your queries. After all, that’s were the true value is.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a50a5fd0b83f" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>