<?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 Boris Serebrinskiy on Medium]]></title>
        <description><![CDATA[Stories by Boris Serebrinskiy on Medium]]></description>
        <link>https://medium.com/@silverbob?source=rss-1444731888f7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*2_ao4SuarkD07dxTf0VB4w.jpeg</url>
            <title>Stories by Boris Serebrinskiy on Medium</title>
            <link>https://medium.com/@silverbob?source=rss-1444731888f7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 01:24:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@silverbob/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[small data — small problems, BIG Data — BIG Problems. Part 1.]]></title>
            <link>https://silverbob.medium.com/small-data-small-problems-big-data-big-problems-part-1-3e2a92ed9e59?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/3e2a92ed9e59</guid>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Sun, 22 Oct 2023 23:11:06 GMT</pubDate>
            <atom:updated>2023-10-22T23:11:06.927Z</atom:updated>
            <content:encoded><![CDATA[<h3>small data — small problems, BIG Data — BIG Problems. Part 1.</h3><p>ChatGPT. LLM. OpenAI. There, the obligatory Gen AI buzzwords have been included (otherwise would anyone even look at my blog?), and we can get on with a real data engineering challenge.</p><p>Anyhow, I get a call one day — some team has been trying to run an update on a table in their massively parallel processing (MPP) cloud data warehouse but the process was getting terminated no matter how much compute they apply to it. They hit limits on execution time, compute size, and are now reaching out to me, one of the SMEs, for help. Oh yeah, since the platform is pay-per-use, each of their attempts costs our cloud budget another C-note.</p><p>As always, I won’t mention vendor names, or specific technologies we use, and the actual names of the tables and columns have been modified. The problem is quite generic, albeit not many people will encounter such sizable datasets. The solution though will be useful for anyone with “big data”. The cloud technology we use allows us to scale compute independently and run parallel compute clusters against the same data.</p><p>So, I started troubleshooting. The table in question had only a handful of columns and stored information related to usage of a globally distributed object system. It is essentially a log table (let’s call it “BadLog”) with a timestamp, ObjectID, ObjectName, and a few more string and numeric columns. The table is used to identify the last time an Object was last accessed. ObjectName has been loaded incorrectly and needs to be updated. The correct data is another table (let’s call it “CorrectObjectName” ) and can be applied to the BadLog table via a join on ObjectID. This table has only 2 columns: ObjectID and ObjectName. In a classic star schema, BadLog would be a fact table and CorrectObjectName would be a dimension. Doesn’t seem too bad… Basically, it looked like this pseudo code:</p><p>UPDATE BadLog SET ObjectName = CorrectObjectName.ObjectName</p><p>FROM BadLog inner join CorrectObjectName ON ObjectID = ObjectID</p><p>But a few questions immediately come to mind:</p><ol><li>Why is ObjectName denormalized as a column of the BadLog table and not kept separately? Simple answer — for performance reasons. It is quite common in analytical reporting to denormalize columns to avoid joining tables. It is a trade off — spend more money on storage, but spend less money on compute/joins. Plus strings compress really well, so all in all not a bad choice.</li><li>Why was their update query timing out? — This is our policy, we do not allow queries to run more than X minutes, typically 30–60 minutes max. This is to avoid runaway cloud costs.</li><li>Why not use a larger cloud compute, don’t clouds offer unlimited compute? — Another policy, we don’t allow exceeding preset compute limits to prevent runaways.</li></ol><p>In all my years in the data engineering business rarely have I seen such a bad data quality situation requiring an update of a… BadLog had over a TRILLION records (&gt;1,300,000,000,000 rows actually!), while CorrectObjectNames was also <strong><em>“just”</em></strong> 3 billion rows (I write “billion” with a small “b” to contrast with the <strong>T</strong>rillion). The update query was a real beast — it would scan the trillion row fact table, join to the billion row dimension, and was never going to finish. I actually estimated that on a 64 node compute cluster it would run for 2 weeks, give or take a week :) In real dollars — at approximately 250 dollars an hour for that compute cluster, 24 hours a day, 14 days of running — 86,000 USD! Even if I was wrong in my estimate, it is still many thousands of dollars per day. For comparison, on a 32 node cluster, the query progressed less than 0.5% in the first hour. And there were no guarantees that a larger cluster would speed things up linearly, meaning a 64 node may not run 2x faster than 32 node, it might do just 30–40% better, at twice the cost.</p><p>My instinct for solving such problems is to look for a way to split the process into either multiple steps running sequentially — e.g. breaking multiple joins into a sequence of doing one join at a time and storing intermediate results into a temporary table — or multiple very similar parts (or chunks) running in parallel. Or a combination of the two methods. Since there weren’t many steps to go sequentially here — the query literally had only one join, the parallelization based on chunking was the path forward.</p><p>People reading this might ask — you already have a massively parallel data platform, why not go with the largest compute possible and let the MPP engine do its job? Well, this is where Big Data becomes a Big Problem. The issues are following:</p><ul><li>Any long running process is likelier to fail than a short running process, e.g. due to restart of some nodes or even client machine that kicked off the query. Such a failure will roll back all updates. Money spent on running cloud compute WILL NOT be refunded. You want to be able to demonstrate incremental progress for completed parts of work and in case of a job failure restart from that failure point. One large query cannot do that. And while Spark is capable of restarting individual tasks (this was not a Spark job by the way), even Spark cannot recover from a failure of a cluster or multiple nodes in a cluster (depending on the implementation).</li><li>You don’t know exactly how much of the update process has completed. This is not like copying files where each file completion is easy to see, you may be sitting there for hours or days or weeks waiting for an “all done” sign…. And then your computer reboots or cloud machine(s) crashes (see bullet #1 above)</li><li>You may be creating a massive block on your target table, literally making the whole system unusable. This can be true even on the most modern analytical systems. This is not a matter of exhausting available compute in the system, it is a logical blocking that many data warehouse systems apply when a table is being updated.</li><li>Joins use memory and when they run out of memory they spill to disk. Any such spilling will drag query performance down by orders of magnitude. Larger memory machines can eliminate some of the spilling but they cost more money and may not be as efficient as smaller machines.</li><li>And lastly — gasp! — a smaller query performs better than a larger one.</li></ul><p>Let me explain the last one as it seems like I stated something ridiculously obvious. But this is Big Data, and things aren’t as simple as they seem. What follows isn’t exact math but an illustration. Suppose we have 2 tables, A and B, each with a billion rows, joined together on a unique key. As each row is joined, the intermediate results go first into memory, and then, if no memory is left, they are spilled to disk. Since by definition, a single query can only use one compute cluster, we either need to get a very, very, very large cluster, or… the query will likely start spilling and slowing down. Contrast this with a smaller query, e.g. joining two 10 million row tables — a lot less or no spilling and thus much better performance. Spilling is a major problem in Big Data processing, it occurs on joins, sorts, group bys, etc.</p><p>But what do you do if you actually need to process a billion row table? You partition your queries! In our example, we would have to run one hundred queries with ten million row tables instead of a single billion row join — and let’s see how this addresses the issues I listed above:</p><ul><li>Each of the smaller queries will run much faster and deterministically complete. If it fails you just rerun it, without waiting for hours or days for the monster billion row one to finish.</li><li>You know exactly how far you’ve progressed as each smaller query progress is clearly marked.</li><li>The blocking is minimized.</li><li>The spilling is minimized or eliminated — performing much better, smaller compute can be utilized.</li><li>And lastly — in cloud computing you gain another massive advantage — you can use independent compute clusters for smaller queries running in parallel. In fact, you can run all 100 queries in parallel if you want to use 100 parallel compute clusters. So, rephrasing the last issue I listed above — a set of smaller queries in aggregate processing the same data space will run faster and more reliably than a single large query.</li></ul><p>But, breaking large queries into smaller ones introduces quite a bit of complexity and is definitely not recommended unless you have to handle… you guessed it — Big Data. You need to be very careful, write potentially multi threaded or even multi process code, handle failures, retries, logging, etc. Some might say — maybe we should have just gotten a much bigger compute cluster. Trust me, that is not going to scale as well as your own partitioning.</p><p>With all that settled, the team started looking for ways to partition the updates. This ended up quite an interesting project, to be continued in Part 2 of the blog.</p><p>Thanks for reading, talk to you soon in Part 2.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3e2a92ed9e59" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The 80/20 rule — “Shared” Data Platforms or… “Sharded” or… both?]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-snippet">It&#x2019;s been several months since I wrote my last Avant-garde your Dimensions&#x200A;&#x2014;&#x200A;Part 1&#x200A;&#x2014;&#x200A;Data Engineering by a Pragmatist | by Boris&#x2026;</p><p class="medium-feed-link"><a href="https://silverbob.medium.com/the-80-20-rule-shared-data-platforms-or-sharded-or-both-fe32795d7a50?source=rss-1444731888f7------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://silverbob.medium.com/the-80-20-rule-shared-data-platforms-or-sharded-or-both-fe32795d7a50?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe32795d7a50</guid>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Mon, 25 Jul 2022 00:42:11 GMT</pubDate>
            <atom:updated>2022-07-25T00:42:11.055Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Avant-garde your Dimensions — Part 1 — Data Engineering by a Pragmatist]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://silverbob.medium.com/avant-garde-your-dimensions-part-1-data-engineering-by-a-pragmatist-ff7a23a74bad?source=rss-1444731888f7------2"><img src="https://cdn-images-1.medium.com/max/727/1*aNiYTHrzFv-dWZvLa6FPQg.png" width="727"></a></p><p class="medium-feed-snippet">One of the critical benefits of a modern data analytics system (OLAP types Online analytical processing&#x200A;&#x2014;&#x200A;Wikipedia) is its ability to&#x2026;</p><p class="medium-feed-link"><a href="https://silverbob.medium.com/avant-garde-your-dimensions-part-1-data-engineering-by-a-pragmatist-ff7a23a74bad?source=rss-1444731888f7------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://silverbob.medium.com/avant-garde-your-dimensions-part-1-data-engineering-by-a-pragmatist-ff7a23a74bad?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ff7a23a74bad</guid>
            <category><![CDATA[data-eng]]></category>
            <category><![CDATA[slowly-changing-dimension]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Tue, 22 Feb 2022 00:48:40 GMT</pubDate>
            <atom:updated>2022-02-22T00:48:40.639Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Are Integers really better for joins than Strings? — Data Engineering by a Pragmatist]]></title>
            <link>https://silverbob.medium.com/are-integers-really-better-for-joins-than-strings-data-engineering-by-a-pragmatist-ab35bd8b3788?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab35bd8b3788</guid>
            <category><![CDATA[hashing]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[spark]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Sun, 06 Feb 2022 20:14:34 GMT</pubDate>
            <atom:updated>2022-02-06T20:14:34.320Z</atom:updated>
            <content:encoded><![CDATA[<p>Are Integers really better for joins than Strings? — Data Engineering by a Pragmatist</p><p>Firstly, I’d like to give thanks to everyone who chose to follow me on Medium. I’m humbled by the thought my blog is read and followed by hundreds of people — all that in just over a month! Incredible! I hope to maintain the same level of engagement, and while I missed last week’s entry — I’d love to commit to one story a week — I think I got an interesting topic today.</p><p>Back in my very first story <a href="https://medium.com/p/3a1e68952ea9">Data Engineering Story by a Pragmatist | by Boris Serebrinskiy | Dec, 2021 | Medium</a> I’ve dropped this phrase — “use “strings” instead of “integers” for star schema joins”. I meant to start a bit of a debate — does one really need to have number-based keys for joins, or can one stay with “natural keys” — or “Strings”. Over my career I’ve heard many experts and novices alike to go with the obvious — numbers are easier to match, join, order, etc. But what about the reality of modern data engines?</p><p>In the follow up stories collectively called “Thou Shalt Not Skew” <a href="https://medium.com/p/309ddec12719">Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1) | by Boris Serebrinskiy | Jan, 2022 | Medium</a> and <a href="https://medium.com/p/b5920a6dda9a">Data Engineering By a Pragmatist — Thou Shalt Not Skew (Part 2) | by Boris Serebrinskiy | Jan, 2022 | Medium</a> and <a href="https://medium.com/p/939756ec2473">How Joins affect Data Skew — Data Engineering by a Pragmatist | by Boris Serebrinskiy | Jan, 2022 | Medium</a> I’ve gone off the deep end talking about hashing, skewing, etc. But it is the hashing that matters to the story here. In the big data space, hashing and the hash joins are one of the primary ways database match data, i.e. execute such joins.</p><p>Hash joins <a href="https://en.wikipedia.org/wiki/Hash_join">Hash join — Wikipedia</a> attempt to hash both sides of the condition and then compare hashes and actual values. What follows below is not a very scientific way of proving my hypothesis — but a pragmatist one :)</p><p>Past few weeks I’ve been playing with a handy piece of cloud hardware — a pretty beefy Ubunty virtual machine with lots and lots of fast cores. While the size of the VM is not critical to this story, having a “shock and awe” kind of a machine always helps to prove a point :)</p><p>My plan was to test out its performance on some Spark benchmarks. Within 20 minutes I had the Spark engine up and running. I am a big fan of this technology and consider it indispensable for any “Big” data engineer.</p><p>So, let’s see how fast this beauty is. Kicking the tires — let’s sum up a <strong><em>trillion </em></strong>numbers in Scala (go big or go home, right?)</p><p>spark.conf.set(“spark.sql.codegen.wholeStage”, true)</p><p>spark.range(1000L * 1000 * 1000 * 1000).selectExpr(“sum(id)”).show()</p><p>+ — — — — — — — — — -+</p><p>| sum(id)|</p><p>+ — — — — — — — — — -+</p><p>|1001881602603448320|</p><p>+ — — — — — — — — — -+</p><p>Boom! Time taken to sum a trillion numbers: 6.963524675 seconds (this is truly ridiculously fast — doing the same thing in single threaded Python took 16 seconds to do just 1 billion — I decided not to wait <strong><em>16* 1000 seconds = 4.5 hours</em></strong>. If you use Spark — you are awesome! If you don’t — you’re still awesome but please install it today and enjoy!</p><p>By the way, how did the CPU cores look during that time? As expected, no skew. And while this story is not about skews, I want to demonstrate how it looks on a real machine — picture from htop shows evenly busy cores.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/0*wqWYOPTrGlhYsvV1" /></figure><p>Picture 1 — non skewed Sum operation.</p><p>I was doing some other experiments and I screen grabbed a pretty bad skew on one of the join operations. I can’t share my code but it was based on Spark benchmark from <a href="https://github.com/databricks/spark-sql-perf">GitHub — databricks/spark-sql-perf</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/0*FJugCYoN2Xmcji9U" /></figure><p>Picture 2. Serious skew on join operations.</p><p>Moving on. Let’s add some hashing operations.</p><p>With the same trillion numbers, hash each and find the sum of the hashes.</p><p>spark.range(1000L*1000*1000*1000).createOrReplaceTempView(“test”)</p><p>spark.sql(“SELECT *, hash(id) as p1 from test”).selectExpr(“sum(p1)”).show()</p><p>+ — — — — — — — +</p><p>| sum(p1)|</p><p>+ — — — — — — — +</p><p>|26267346032547|</p><p>+ — — — — — — — +</p><p>Time taken to hash a trillion numbers and take their sum: 48.670489114 seconds. Which means roughly 41 seconds to do the hashing.</p><p>So, I’m thinking — let’s try to hash a trillion strings.</p><p>This did not work:</p><p>spark.sql(“SELECT *, hash(cast id as string) as p1 from test”).selectExpr(“sum(p1)”).show()</p><p>The cast was crawling, I waited 5 minutes with Spark UI showing it would take over an hour to finish the job. But the goal is to hash strings and see how long it takes, not to see how long the cast takes.</p><p>Trying something different:</p><p>spark.sql(“SELECT *, hash( ‘’ || id) as p1 from test”).selectExpr(“sum(p1)”).show()</p><p>This is going slow too. 10 billion rows per minute. 1.5 hours. So I take out the hash function — and it is still the same timing, almost. So, it seems making a string out of the “id” column is the problem. I still can’t get the hash of a string timing measured.</p><p>So, I am going to scale down the test to 10 billion numbers and run 2 functions:</p><p>spark.sql(“SELECT *, hash( ‘’ || id) as p1 from test”).selectExpr(“sum(p1)”).show()</p><p>That’s about 40 seconds. (Compare this to doing 1000 times more hashes of a trillion in 48 seconds</p><p>Now, run just the string conversion:</p><p>spark.sql(“SELECT *, ( ‘’ || id) as p1 from test”).selectExpr(“max(p1)”).show()</p><p>This will actually take longer since MAXing strings is slower than integers (but this is not relevant to the join performance of the hashes — Aggregation of strings is definitely slower than integers, but we are discussing joins — different matter)</p><p>I will cast back the strings to help the max function:</p><p>spark.sql(“SELECT *, cast ( ( ‘’ || id) as long) as p1 from test”).selectExpr(“max(p1)”).show()</p><p>About 40 seconds again!</p><p>Which to my less than scientific mind feels that hashing 10 billion of strings vs 10 billion of integers on a modern machine was pretty much about the same (I ran that separately — 0.6 seconds to do 10 billion hash of integers). I know Scala uses Murmur3Hash <a href="https://en.wikipedia.org/wiki/MurmurHash">MurmurHash — Wikipedia</a> and I know strings are slower to hash than numbers. But speaking pragmatically, does it matter that much anymore? Imagine that you no longer need to design your data model and ETL processes to support conversion of natural string keys into surrogate numeric keys? That your star schema joins can be run simply on the natural keys…. Whole dissertations been written on the subject… So much of the coding overhead can be removed. So many trees could have been saved from those dissertations :)</p><p>Given that we don’t really need to worry about storage costs (at least on the cloud), and the computing power is so abundant, I conclude that hash join performance of strings is pretty much about the same as integers.</p><p>With that, have fun with Spark and I’ll see you at my next story.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab35bd8b3788" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Joins affect Data Skew — Data Engineering by a Pragmatist]]></title>
            <link>https://silverbob.medium.com/how-joins-affect-data-skew-data-engineering-by-a-pragmatist-939756ec2473?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/939756ec2473</guid>
            <category><![CDATA[sql-joins]]></category>
            <category><![CDATA[query-optimizer]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[skew]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Sun, 23 Jan 2022 22:58:04 GMT</pubDate>
            <atom:updated>2022-01-23T22:58:04.157Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>How Joins affect Data Skew — Data Engineering by a Pragmatist</strong></h3><p>In my 2-part mini-series “Thou Shalt Not Skew”<br><a href="https://medium.com/me/stats/post/309ddec12719">Stats for Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1) (medium.com)</a> and</p><p><a href="https://medium.com/me/stats/post/b5920a6dda9a">Stats for Data Engineering By a Pragmatist — Thou Shalt Not Skew (Part 2) (medium.com)</a> I’ve tried to explain how to best distribute data to keep a parallel processing system running at its peak efficiency. The key was to ensure information equally aligns to each processing unit (computer or human). Specifically, by applying a hashing algorithm. After I wrote the blog, I found a great visual for this at <a href="https://www.geeksforgeeks.org/hashing-in-distributed-systems/">Hashing in Distributed Systems — GeeksforGeeks</a></p><p>What I showed though was a very simple system with just one data set and no joins.</p><p>Joins, of course, are a bread and butter of relational theory <a href="https://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators">Relational algebra — Wikipedia</a> and any RDBMS worth its salt supports them. There is a fascinating side to how relational databases employ these algorithms when they process join operators. But I wanted to focus this conversation on parallel processing of joins by MPP systems and the concept of a <strong>join skew</strong>.</p><p><strong>Sidebar #1</strong>: In the so called “Shared Nothing” (“Independent Compute over Independent Storage”) <a href="https://en.wikipedia.org/wiki/Shared-nothing_architecture">Shared-nothing architecture — Wikipedia</a> systems eliminating data skew is absolutely critical to keeping each node local storage equally allocated and each machine equally busy. This is because there is a finite amount of storage that’s attached to each processing unit and if there is a data skew, that storage may become full on some of the nodes, while others will be underutilized. This is not the case in the systems known as “Independent Compute over Shared Storage” (or Shared Disk), where all storage is accessible to all compute nodes. Separation of Compute and Storage has been heralded as one of the major features of modern databases. Such systems are often found in cloud computing and use high scale blog storage. It has its drawbacks though, specifically around high speed OLTP workloads — this is because multiple readers are OK reading the same storage, but multiple competing writers have to cross-coordinate and run slower when compared to Shared Nothing engines where coordination is not required. Blob storage is also quite slow compared to the locally attached disks. Logically, putting a network between Storage and Compute layers also leads to latency and requires investments into caching layers, with more coordination required. See <a href="https://www.geeksforgeeks.org/difference-between-shared-nothing-architecture-and-shared-disk-architecture/">Difference between Shared Nothing Architecture and Shared Disk Architecture — GeeksforGeeks</a> for other interesting details.</p><p>I will now distinguish between “<strong>storage</strong>” vs “<strong>join</strong>” skews. Storage skew is a static type of skew, it can be seen by looking how much disk has been utilized by each node in an MPP system. Join skew occurs only within queries with joins and leads to one or more servers running “hot” and queries run longer than expected as some machines have to process more data. Many data engineers only focus on the data skew when they lay out their tables — they look at which columns to hash their tables by, to maintain that near-even data distribution across all the machines. Well, let me give you the good and the bad news. First, the good news — modern cloud databases based on Shared Storage don’t require this technique anymore — see the Sidebar above. Now, the bad news — <strong>all parallel processing relational engines suffer from join skew, every one of them. </strong>I’ve had to explain to very seasoned data engineers and DBAs how and why despite their valiant efforts to maintain zero skew data in their tables, the queries still exhibited signs of inefficient skewing and run slower than expected.</p><p>Let me explain how a join is executed by a parallel query engine. We follow 3 simple rules:</p><p>Rule #1 — a join is a type of a compute operator and is executed within CPU/memory, not storage.</p><p>Rule #2 — join matches two or more pieces of data by computing a “join condition” between tables (typically).</p><p>Rule #3 — <strong>very important!</strong> — <strong>join cannot be computed unless data is co-located on the same computer.</strong></p><p>It is the rule #3 that brings us to the topic of the join skew. Database engines cannot figure out whether a join condition is true or false until it brings data from both tables into the same computer. Sounds simple, right? Take this query for example — find SUM of all orders grouped by CustomerID:</p><p><strong>SELECT Customers.CustomerID, SUM(Orders.OrderQuantity) FROM Customers INNER JOIN Orders on Customer.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID</strong></p><p>In this query, the database engine will attempt to satisfy Rule #3 by “moving” all customers and orders with the same CustomerID into the same CPU and then running an INNER JOIN equality check. That is easy if there is one computer, but what happens if you have 10 or 100? We want to execute a query in parallel on multiple computers, with data residing on different pieces of storage. Clearly, we can’t have all the data to be moved into memory of a single machine,</p><p><strong>Sidebar #2:</strong> All relational query systems employ a complex piece of software called “query optimizer” or “query compiler”. These are tasked with creating a plan on how to execute queries, specifically, which operators to run first, then second, etc. In the query sample above, the optimizer would have to choose which table to move to which computer and whether to do a JOIN before the SUM, and when to do the GROUP BY. Parallel query engines are VERY complex and employ many statistical techniques to attempt to hit the best possible execution path. In my blog I will be referencing modern advanced optimizers built for analytical workloads. Such workloads are known for routinely involving multiple joins per query.</p><p>Let’s put some numbers on the table. Assume we have 100 million orders for 1 million customers.</p><p>Problem #1 for the optimizer — how to distribute work across many machines equally? One of the things the optimizer will attempt is to execute what’s called a “hash” join. It will hash each side of the INNER join (CustomerID fields) and send data to the corresponding machines with the same <strong>modulo </strong>of the hash (I’ve described this process in my previous blog).</p><p>Here is what will happen if the optimizer goes for a hash join:</p><ul><li>Hashes of the identical values hash identically and result in equal modulos, thus:</li><li>Hash all values of Customers.CustomerID column- send data to individual processing nodes according to the hash of CustomerID</li><li>Hash all values of Orders.CustomerID column — send data to individual processing nodes according to the hash of CustomerID</li><li>We have achieved colocation — the engine will compare actual values and identify INNER JOIN matches.</li></ul><p>Note: all this distribution only happens as a transient copy of data, nothing is written to disk on any of the computers, all data is just read into memory and sent to another machine over the network.</p><p>OK, so far so good. But what if one (or a minority of your customers) have been doing a lot of orders? Imagine the following distribution of orders by customer (counts of orders by customer).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/392/0*I0ujr9bIEYARFT_t" /></figure><p>Each customer, except for Boris, has just a few orders, but Boris has 50 million orders out of 100 million. When the optimizer chooses to hash by CustomerID, it will have no choice but to send all data for Boris’ orders to a single computer, and that node will run for much, much longer than other nodes, potentially never completing the query. In a system with say 40 nodes, only 1 will have all Boris’ orders. Optimizer has made an awful decision and created a join skew!</p><p>I want to emphasize — this has nothing to do with the data distribution on disk.<strong> It does not matter how you laid out your tables, which distribution keys you used to store the data</strong>. <strong>Only the join keys matter.</strong></p><p>The operation of moving data between processing nodes based on hashes of the join key is called “<strong>distribution</strong>” (or “<strong>redistribution</strong>”) as the engine is temporarily redistributing data using its interconnect network.</p><p>The situation gets even more complex when the query has 2, 3, 4, 5, 6,7,8 and more joins. This blog is not aiming to explain how the optimizer operates in such cases, but suffice it to say, it needs to decide which joins to execute first. Here is a basic example — find SUM or Customers orders and group by CustomerID and CategoryName of the Product:</p><p><strong>SELECT Customers.CustomerID, Categories.CategoryName, SUM(Orders.OrderQuantity) FROM Customers INNER JOIN Orders on Customer.CustomerID = Orders.CustomerID</strong></p><p><strong>INNER JOIN Products on Orders.ProductID = Products.ProductID</strong></p><p><strong>INNER JOIN Categories on Categories.CategoryID = Products.CategoryID</strong></p><p><strong>GROUP BY Customers.CustomerID, Categories.CategoryName</strong></p><p>Does the optimizer run join between Products and Categories first or does it run Orders to Products? Every one of these joins can lead to a join skew depending on the data. And, again, that skew is independent from hashing on disk. Queries with 10–20 joins may take several minutes (!!!) to compile (not execute, just compile!) as the optimizer is searching for the best solution.</p><p>In order to combat this join skew the optimizers will employ a technique called “broadcasting”. Using special heuristics (table statistics, data distribution analysis, etc), an optimizer may choose to <strong>broadcast </strong>smaller tables to <strong>every </strong>node in the system. Not redistribute, but broadcast — Customers table is likely to be replicated fully, all one million records, to every computer in the system. Here is what will happen then:</p><ul><li><strong>Engine will not be using hashing algorithm for the join</strong></li><li>Engine will randomly and evenly distribute Orders table across all computers</li><li>Engine will broadcast Customers table to every computer, fully</li><li>Engine will run INNER JOIN equality check and discard all mismatches</li></ul><p>Broadcasting is not without its flaws and cannot be used in all the cases. In general, it can only be done for small tables as it consumes network bandwidth and scales poorly on larger systems. Also, an optimizer has to make a decision — do I broadcast Table A “toward” Table B or do I do it in reverse? It gets really complicated with 10–20 join queries. If it makes a mistake and broadcasts a very large table, it will saturate all network bandwidth between the nodes.</p><p><strong>Sidebar #3:</strong> Does join skew exist in SMP machines (single computer data systems) ? Yes, it does, but it is expressed as different threads/CPU cores having to do different amounts of work. Such skew is difficult to identify and is typically ignored.</p><p>Lastly, I’d like to make a note that joins are not the only operators causing skew. GROUP BYs and other group base aggregators will do so as well.</p><p>In this example:</p><p><strong>SELECT Customers.CustomerID, SUM(Orders.OrderQuantity) FROM Customers INNER JOIN Orders on Customer.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID</strong></p><p>Even if the Customers table was broadcasted, the GROUP BY operator still requires all data for a given customer to be co-located. Not for the purposes of the join, but for the SUM operator. And though one might say — “but I can run SUM on many computers and then roll it up on one” (because SUM of SUMS is fully additive), this may not work for all operators, for example COUNT DISTINCT is not a fully additive operation. This is a more advanced topic, perhaps for another blog.</p><p>In conclusion, I personally call “join skew” as “<strong>processing skew</strong>” (“runtime” skew, if you will), to highlight a more general nature of this phenomenon, rather than limiting it to just joins. I’ve seen it occur in window functions, in rollups, cubes, etc. The fundamental idea I wanted to convey in this blog — the processing skew is independent from the storage skew and can be a confusing subject. Separation of compute and storage does not solve it. Not every query can be completely skew free. The optimizers make mistakes and there is no substitute for advanced query plan analysis by data engineers.</p><p>With that, thank you and till the next time!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=939756ec2473" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineering By a Pragmatist — Thou Shalt Not Skew (Part 2)]]></title>
            <link>https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-2-b5920a6dda9a?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/b5920a6dda9a</guid>
            <category><![CDATA[skew]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Mon, 17 Jan 2022 03:21:23 GMT</pubDate>
            <atom:updated>2022-01-17T04:12:09.487Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Engineering By a Pragmatist — Thou Shalt Not Skew (Part 2)</h3><p>The first part of “Thou Shalt Not Skew” <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-1-309ddec12719">Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1) | by Boris Serebrinskiy | Jan, 2022 | Medium</a> contained questionable attempts to demonstrate examples of skewed data distribution in real life situations — getting into a hockey game or finding a parking spot in Brooklyn, or voting for the new mayor of New York City. I want to declare it has been the most resounding… What’s the opposite of “success” ?… “Meh”? This conclusion is based on hard data — 1. The viewership and readership is WAY, WAY down. 2. Linkedin comments are inconsequential (complaints about Brooklyn parking… like that was the point of my blog — call 311, I say, or move to the West Coast! Or sell your car and laugh all the way to the bank given where used car prices are nowadays). 3. I’ve received fewer “claps” on Medium than the guy who wrote a 2 paragraph blog about his last night dreams. Incredible, thumbs up, people! I don’t want to sound like Jeb Bush, but he and I both mean it when we say “Please clap!” <a href="https://www.youtube.com/watch?v=OUXvrWeQU0g">Jeb Bush to audience: ‘Please clap’ — YouTube</a></p><p>Clearly, based on these 3 points, I must press on and continue with converting some of the resounding “success” of Part 1 into Part 2 — “how do you fight the data skew?”. I may even bring some technical conversation into this lifeless blog as I feel my fellow data engineers are falling asleep (I’m afraid the guy who blogged his dreams was one of my frustrated readers…Damn him for getting more claps!) But there is an interesting thing happening — it seems some of my non-technical friends have become interested in learning what I do for living — one even got to googling “data quality’, “referential integrity”, “multidimensional modeling”. This is what I wanted to achieve — wake up the “pragmatist” in people, and maybe get them interested in “Data”! Amazing!</p><p>With that, let’s ratchet it up. Using the examples from Part 1 <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-1-309ddec12719">Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1) | by Boris Serebrinskiy | Jan, 2022 | Medium</a> (which I reluctantly must recommend reading prior to this opus), we’ll jump into pragmatic solutions, and potentially demonstrate the differences between a human mind and a computer. I’ll start with the most egregious examples — the Election voting place set up. Splitting voters by the first initial of their last name, or by first initial of the street they live on, or any other “humans can easily understand this” model leads to a heavy data skew. For simplicity we assume all desks are equal in size and each staffer processes an equal number of voters per hour. There are far more people in the United States and in my voting district with last names starting with an “S” than with a “Y”. OK, that’s fairly obvious. How do we fix it? As a data architect, I can tell the election office chose to use the “Shared Nothing” model <a href="https://en.wikipedia.org/wiki/Shared-nothing_architecture">Shared-nothing architecture — Wikipedia</a>. While they may not even realize that, the model is potentially prone to two characteristics — high processing rate (good!) and a high data skew (bad!), and what’s most interesting — these two can often compete against each other! Imagine that there was one only table where all voters would go — the processing would be quite slow, but there would be no skew at all as everyone would line one into a single queue. But, with 26 different desks, one for each letter of the English alphabet, the rate would be much better but the “Y” desk would always be empty, while “S” desk is as busy as hell. But how much better? Let’s say 5 out every 26 people have their last name starting with “S”. The best processing rate with 26 desks as compared to 1 desk would not improve 26 times — there will be a bottleneck at the “S” table. And, sure, you can add more people to that desk, but that might be difficult — what if each desk can only seat one person? In a “Shared Nothing” system the key to best processing rate is to keep all “desks” equally busy (that property is not unique to SN systems, but a critical one).</p><p>Let’s replace desks and election officials analogy with the real computers, we’d be keeping the same “Shared Nothing” model. Now, the computers won’t be using the 1st initial of the last names, they are going to “scramble” each last name using logic called “hash”, i.e. they will be “hashing” the names. <a href="https://en.wikipedia.org/wiki/Hash_function">Hash function — Wikipedia</a> Each computer also knows its position, from 1 to 26, or as programmers “think” — from 0 to 25. By the way, we won’t even need all 26 computers, but that will become apparent later. There are many hashing functions, but they all result in one thing — some arbitrary data gets converted a shorter, fixed size, “hash” representing that data. Different data become different hashes, same data results in the same hashes. Can different data end up as the same hash? Sure, and that’s called hash collision, but for the sake of data skew it is not critical and is OK to have. Hash collision is critical to cryptography but not so much to data engineering. A good topic in its own right, maybe for a later blog.</p><p>Let’s go through the examples. Using my last name, Serebrinskiy, and hashing it using SHA256 tool <a href="https://www.tools4noobs.com/online_tools/hash/">https://www.tools4noobs.com/online_tools/hash/</a> hash of it is afc841366f5c61d6ee55c9a01a00d75f91ef423a643c27a5e36a1f0747028a94 Note, that hash of upper cased SEREBRINSKIY is very different — 1cffdb3de8cc0061ca5eb8ea41385ff6c84f7883175f7ca54946a859e141177b. Even changing 1 letter is going to produce massive moves in the hash — let me drop “y” at the end of my name — Serebrinski — hashes to aadb79fd319d83ecf2053245af3592c41cbb569014db75bdece2fd3d9c10027b.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gys8lo5uq5wOVYvmw3Emcg.png" /></figure><p>So, what good do these numbers do for us? One more step, please be patient. Remember, we have 26 computers and each computer is numbered from 0 to 25 (or 1 through 26). Where should the voter with the Bucket of “45000” go? For that we need to divide 45000 by the number of the computers (26) and find the remainder. This is called a “modulo function”. Using <a href="https://www.calculators.org/math/modulo.php">https://www.calculators.org/math/modulo.php</a>, we get remainder of the modulo of 26 for 45000 is 20! This means I have to go to the 21st computer (computers are numbered 0 to 25). Sandman is going to go to “29717 mod 26 = 25” — 26th computer. Sanders is going to “40907 mod 26 = 9” — 10th computer.</p><p>Amazing! 3 people with the same initial now go to three different desks. Again, this is not random, this is a well defined math. Doing this randomly can work but will break data locality, a topic of one of my next blogs. Data locality is critical to processing data joins and random tasks assignment would break it. Hashing, on the other hand, is not random, and maintains data locality.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jh-1-urVbvsQpxEfZjWiaw.png" /></figure><p>If I were to keep adding more voters (a.k.a. Data tasks), their hashes would continue to approximately equally distribute them across all 26 computers. But why 26 you say? Great question — and the answer to it — we actually can use any number of computers, 1 or a 1000! All that would change is what we put into our modulo function.</p><p>Let’s test this theory using just 3 computers. 45000 mod 3 = 0” — I go to the 1st computer. Sandman’s 29717 mod 3 = 2–3rd computer. Sanders’ 40907 mod 3 = 2” — also 3rd computer.</p><p>How about 1000 computers — 45000 mod 1000 = 0–1st computer, 29717 mod 1000 = 718th computer, 40907 mod 1000 = 908th computer.</p><p>And so on. Increase the number of the hex characters and you can scale the number of computers to as high as one can imagine, while maintaining a near equal number of tasks each computer has to handle.</p><p>The same algorithm can be applied to any data, e.g. car license plates — how do we distribute processing of tolls or new car registrations. Quickly — a car with a license plate of “NYC123” hashes to aabd70d946fd8c81e74b41bfe98b9208f63b09e2feaa1a41085f9ddaa417372e, with “aabd” turning into 43709 decimal. Assuming the Department of Motor Vehicles uses 6 computers to process the plates — 43709 mod 6 = 5, meaning it is the 6th computer responsibility. If, for example, two of the 6 computers break down, the function will become 43709 mod 4 = 1 i.e. the 2nd computer has to handle it. Magic!</p><p>There is some danger in too many people having the same last name and ending up going to the same computer. An easy problem to solve — just mix something else into their hash bases — date of birth, address, etc. The beauty of the “hash and mod” approach is that it always results in a number pointing to the computer doing the work. That number is called a “data partition”, and the process “data partitioning”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FrRQ7II-miELjKC-D4VdEg.png" /></figure><p>By the way, in my last blog I mentioned that a supermarket next to my house uses a single lane for all its customers and someone is directing them to the next available cashier. This is, by far, a much simpler model — no hashing, no modulo, you can add or remove computers (“cashiers”) as you wish. So, why not use it? Well, for starters, this guy directing everyone is a “bottleneck”, and if he were, say getting distracted by looking at his phone and texting his girlfriend, the queue would stall. In computer terms, it is no longer a “Shared Nothing” architecture and it is prone to such bottlenecks. It especially starts to break down when the number of computers is getting really high, in hundreds or thousands. Imagine the poor guy having to direct customers to a thousand cashiers. He would be spinning like a hamster on a wheel. And if it would only take him a second to identify the next available cashier — that’s just 60 customers per minute. With a thousand cashiers spending an average of 5 minutes per customer, the whole system would stall on the bottleneck. The system theoretically would be able to move at 1000/5=200 customers per minute. But the bottleneck is only doing 60 assignments per minute. The system will never be able to get to its maximum rate of 200. What’s worse — it is also very difficult to scale up — if you wanted the bottleneck to work even faster — you could put 2 guys to do the job, but then they would have to coordinate which cashier they send people to. That’s a tough problem.</p><p>This is why most of high end data engineering is using hashing.</p><p>Hashing is math driven and requires very little computer-to-computer coordination. Absence of such coordination allows for the very high processing speed. You know, like in real life — the less you need to talk to anyone, the faster you can get your job done.</p><p>Hash based partitioning is not the only way to combat the infamous data skew but it is one of the most widely used. It has drawbacks, and those worth their own blog story, which I will embark on in my “processing vs on-disk data skew” blog. One of the obvious challenges is that whenever the number of computers changes, all assignments must shift around to balance out the work. To alleviate this issue there has been research done to implement what’s called “sticky” or “stable” hashing algorithms that attempt to preserve assignments during rebalancing.</p><p>OK, enough of the dry talk. Let’s look at some real data. I am bringing down a list of all soccer (sorry, football :) FIFA roster, 18728 players ( <a href="https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset">FIFA 20 complete player dataset | Kaggle</a> courtesy of <a href="https://www.kaggle.com/stefanoleone992">Stefano Leone | Expert | Kaggle</a> )</p><p>I am using the most practical tool of any data engineer for Big-ish Data — Excel :) And, yes, I know and use Python too.</p><p>In 5-ish minutes I got this:</p><ol><li>Graph demonstrating 1st initial of player’s name, count of each initial.</li></ol><p>We see huge spikes in As, Js and Ms.</p><p>WHAT HAPPENED TO ALL THE Q GUYS!?!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*X__CpwTOP5vNHEZE" /><figcaption>Distribution By 1st initial — one bucket per initial</figcaption></figure><p>The skew is unbelievably bad. If you compare the highest to the lowest bucket, it varies thousands of times. If the players had to go vote for NYC mayor, some of them would still be at it. While the “Q” guys would be long done! Poor Js and Ms :(</p><p>2. Now with the hashing added — I’m adding a few steps. Feature engineering is fun!</p><p>The 2nd graph shows what happens if we hash and modulo the names. I’ve played a bit with the code from <a href="https://superuser.com/questions/550592/is-there-an-excel-function-to-create-a-hash-value">Is there an Excel function to create a hash value? — Super User</a> and <a href="https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba/125844">Password hash function for Excel VBA — Stack Overflow</a></p><p>Step 1. Add SHA1 hash</p><p>Step 2. Take 5 left most hex digits</p><p>Step 3. Convert to decimal</p><p>Step 4. Find modulo of a sample set assuming 30 computers running some job (just to be close to the 1st graph above).</p><p>And here is the result — near even distribution from 0 to 29th data partition. The skew is barely noticeable, less than 10% between min and max partitions. 18000 players across 30 computers should average about 600 per machine. We got exactly that.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8SFlvdF-IlFj__8O" /><figcaption>hashed and modulo over 30 data partitions</figcaption></figure><p>Let’s increase the number of computers — 100 now! And they are all equally busy, with 150–200 players per machine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Avnit5s7c9bqzCAC" /><figcaption>hashed over 100 data partitions</figcaption></figure><p>Everything continues to work nicely! But the skew has increased to about 25%. Still OK though.</p><p>On this note, I think I’ve made all the points I wanted. In the next blog entry I am going to talk about how database joins are affected by skew and what things to look for. I will explain what database joins are as well, don’t worry. This is going to be fun!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b5920a6dda9a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1)]]></title>
            <link>https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-1-309ddec12719?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/309ddec12719</guid>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Sun, 09 Jan 2022 21:31:58 GMT</pubDate>
            <atom:updated>2022-01-17T13:50:39.262Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1)</strong></p><p>I’m going to go on a limb and claim that Data Engineering is an art. Ability to find brilliant solutions and discover them in unconventional ways is nothing short of the creativity of an artist. But I digress — so, if art imitates life, and data engineering is … an art — thus data engineering imitates life (of course, I am joking, all you math gurus, please sheath your pitchforks :)</p><p>On that transitive and somewhat ridiculous derivation, let’s keep going. As I start this, 3rd, blog (which will come in 2 parts) I wanted to get into one of the most obscure and confusing subjects of Big Data processing — the “data skew”. If you followed my last blog (bless the souls of 267 people who spent an average 5 minutes 12 seconds reading <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-tales-of-choosing-a-database-5f9f2f3cc1ab">https://silverbob.medium.com/data-engineering-by-a-pragmatist-tales-of-choosing-a-database-5f9f2f3cc1ab</a> , and my profound respect to the few who wisely chose to close their browser and delete all its history instead of reading that nonsense) — you recall I went to the Rangers game with my two sons. That event will become pertinent to the subject of the skew.</p><p>You ever wonder why there is no parking on your street, and you have to drive in ever expanding circles to find a spot? It isn’t even a circle, it is more like a drunken monkey Google Maps torture by the time you’ve succeeded. You don’t have this problem? But for us Brooklynites, that’s daily life — pack 2.7 million people into 70 square miles, with a predictable result — the 2nd most densely populated “parking lot” in the entire United States (the top prize goes to Manhattan, of course, but Brooklynites own SO MANY MORE cars). In one sentence — distribution gone wrong! Weirder stuff still — my street is always fully packed with cars, though all the houses have driveways, and the owners keep their cars off the street. Walk a block or two and you see almost no cars in the next streets over — what gives?! Walk a dozen more blocks and the streets are full again. Don’t people know how to distribute their cars evenly? The mystery is a pretty easy one to solve — my street is bordering a very large residential, business, and arguably famous area known as “Little Odessa” or officially, Brighton Beach, while the other side of the neighborhood pushes against the fence of Kingsborough Community college, where many students are thankfully taking public transportation, but not all.</p><p>Why am I telling you all this (especially if you don’t live in a big city or are lucky enough not to own a car)? Two things I pick up from all the “data” above — firstly, in life, distribution is often uneven (that one is painfully obvious as I look at my bank account and then read news about Jeff or Elon :), and secondly, locality matters — people (or cars) like to park at the nearest spot. Notice, I did not say “distribution is unfair” — this is not a social equality blog, but a data engineering one. So, how do these two very profound observations matter for the pragmatists that we are? And what is “data skew”? Data skew is, simply put, uneven distribution of data.</p><p>Life example #1 — We are back to the Rangers game. There are three or four types of lines (or as they say on the island(s) of Albion — “queues”) one has to pass before you make it to the seats.</p><p>Queue #1 — they check your vaccination status. There are several staffers, and you can approach any. Once you enter a particular worker queue, you cannot get out and must wait your turn. “Worker queue” — we are finally getting some real convergence of life and data engineering terms here :) OK, identifiable bottleneck — some people don’t have their vaccine card ready and stall the line. Sucks, but OK. The three of us each took a separate lane, and we were through.</p><p>Queue #2 — they put us through metal detectors. There are dozens of those, and you can jump from one lane to another if you see one stalled by some fan of Metallica or Black Sabbath. This is a really efficient system, we fly through.</p><p>Queue #3 — a.k.a disaster! A.k.a. ticket scanning. a.k.a. people think Madison Square Garden technology equals Apple Pay, equals “I’ll just wave my phone next to the scanner and magic…”. Identifiable bottleneck — though there are quite a few lanes, many people travel in groups (like the 3 of us) and have to scan the tickets together if one person bought them all, like I did. This is called “data locality” (keep this term handy). Not only are the scanners faulty (read — “poor processing rate”), once you get stuck behind a large group all you can do is curse loudly (socially accepted behavior at MSG). So, very inefficient. I should have split my electronic tickets into 3. Too late.</p><p>Queue #4 — escalators to the upper seats. There is only one at our gate, so everyone has to pile into that — there is no distribution here, we are in a single threaded mode, just sit and wait your turn. Sucks.</p><p><strong>Sidebar</strong>: Single Threaded vs SMP vs MPP processing. Briefly — we can’t talk seriously about modern data engineering without at least some basic acronyms. Single threaded means you have one queue, and every piece of work is lined up on it — that’s our escalator. SMP — symmetric multiprocessor system — many queues and many workers checking these queues, but all staying together within the confines of some area (e.g., your personal computer or one gate at MSG). That’s technically queues #1,2,3 above. MPP — massive parallel processing — imagine Madison Square Garden with 6 separate entrances or gates — I only described one, but together they may look like 6 separate computers working together — that’s MPP-ish. In a real MPP system people or tasks from one gate can instantly jump to another gate. In modern data processing we may use hundreds of servers to work on a single problem.</p><p>So, the whole MSG building is operating using queues, distribution of workers, locality of ticket holders and their friends and families. G-d knows what other things go on behind the ticket scanners (I really need to talk to their people, those things are awful), food concessions, etc. — they all use distribution. The more uneven this distribution is, the less efficient is the system, resulting in performance bottlenecks. In a pragmatist’s view — I can’t get my beer and I miss the singing of the national anthem and the start of the game.</p><p>Life example #2 — I’ll be short this time. Presidential elections, November 2020. New York City passes a law allowing up to 10 days of early in person voting, so I go a week before the final Election Day. I see many distribution and locality issues — firstly, I have to go to a school a mile away instead of walking to the nearest one. Secondly, the staffers split all voter rolls by the first initial of the last name. NOOOOO! This is wrong — there are a lot more people with the initial “S” (me), than the letter “X”. And while the staffers bunch a few of the seldom used “X”, “Y”, “Z” to one lane, it is still not efficient! But they can’t do anything — the rolls are printed and have been “co-located” (remember this word!) on the staffer’s desks. What they should have done is to have computers on each desk and allow people to join a random lane!!! This would then become a no-localized SMP model, which I will explain in Part 2 of the blog. Instead, I am standing in a line of 5–6 people to the poor “S” lady while half the desks are empty.</p><p>And finally, Life example #3 — my local supermarket, called “Tashkent” (the capital of Uzbekistan). The model of efficiency! 15 cashiers. One queue. There is a guy directing each shopper to the next available cashier. The store moves massive volumes every hour. The only problem — they have another store, and when this one is busy, the other store can’t help it. Because this is an SMP model, and in SMP two computers can’t help each other. You need MPP for that.</p><p>What have we learned — distribution or lack of it, the skew, is critical to efficient data processing. “Thou Shalt Not Skew” then if Thou Wantest Efficient Data Processing!</p><p>On how to do that in real life data space we shall talk in Part 2. Best wishes, see you next time!</p><p>P.S. Part 2 published here <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-2-b5920a6dda9a">Data Engineering By a Pragmatist — Thou Shalt Not Skew (Part 2) | by Boris Serebrinskiy | Jan, 2022 | Medium</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=309ddec12719" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineering by a Pragmatist — Tales of Choosing a Database]]></title>
            <link>https://silverbob.medium.com/data-engineering-by-a-pragmatist-tales-of-choosing-a-database-5f9f2f3cc1ab?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/5f9f2f3cc1ab</guid>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Mon, 03 Jan 2022 03:40:26 GMT</pubDate>
            <atom:updated>2022-01-14T18:27:21.331Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Engineering by a Pragmatist — Tales of Choosing a Database</h3><p>2022. 2nd day of that. 1st Sunday of that. Interestingly, 52nd week of 2021. 8th day since I “prologed” <a href="https://silverbob.medium.com/data-engineering-story-by-a-pragmatist-3a1e68952ea9">Data Engineering Story by a Pragmatist | by Boris Serebrinskiy | Dec, 2021 | Medium</a> to my astonishment LinkedIn showed it to close to 8000 people, and about 50 chose to follow me on Medium. Forever grateful to everyone! 7 days of thinking what to write about next, interrupted by the anti-diet post-Christmas week, culminating in a criminal-almost eating event known as “Russians celebrating New Year”. As I slowly recover from the food coma (2 workouts on the 1st day of the year, hopefully a dozen more (total) by the end of year :), I sit down to write the highly anticipated (by 50 people?) “how one chooses their database” blog…</p><p>Look at how much food… err.. sorry… data is all around us. 30 minutes into writing this, I hear “Dad, let’s go see the Rangers!” (NYC ice hockey team, beloved by their fans, including my family). My smirk about “but you guys wanted me to write this blog…” is shut down with a shrug and “you can do it on the train like I do my school homework”. That will have to be discussed later. But right now, I have more data to contend with — ticket prices, subway schedule (does it even run given the Omicron news?), phone battery levels, remembering password for ticketmaster app, and more, and more…. 15 minutes later, I’m looking out of the Q train window and mentally locking on to other “data”… Does the train speed get logged? How often? What about the temperature of its brakes? 3rd rail voltage? I’ve got to ask one day where they keep that… My dad worked for the subway transit authority, he might have some contacts. Wow, more data — velocity, temperature, voltage, contacts…</p><p>We are at the arena — “the world’s most famous one”, they say — Madison Square Garden (in fact, it is the oldest in the US, but is oldest = famous? Let’s ignore this useless data for now). Our tickets and vaccination cards are getting scanned — I can feel how the data is virtually flowing from our phones into the hands of the guys with the scanners… My son’s data is a little messed up — the city misspelled his last name — so we need to show the paper card. A data quality issue… The hockey game rages on, the home crowd is on its feet half the time — we have crushed our nemesis, Tampa Bay Lightning, twice in a row now, and the Lightnings are Stanley Cup champions, no less! So much more new data to contend with, the famous “celebrity” kind — sports statistics, the heart of the modern “Moneyball” (you saw the movie, right?) industry.</p><p>Where would I keep all of it? Where does my company keep something like that? Luckily, I’m well familiar with these questions. Part of my job is to look for such technology, test it, and then advise others on how to make better choices when it comes to picking a database.</p><p>These are not very easy questions to answer. The challenge is quite a multidimensional one. There are dozens of commercial vendors to choose from, plus multiples of that in the open-source space, and the man-in-the-middle kind of vendors who’d offer you a commercial distro of an open-source software. Then you got your turnkey database appliance vendors who promise to roll in a box that just needs some power and network, and you’d be all set. The acronym galore can make your head spin — OLTP, OLAP, HTAP, HOLAP, MPP, SMP, with choices of operating systems, cloud vs on-premise (and let’s not forget venerable mainframe), columnar vs row-based vs hybrid table organization, or no tables at all but some “collections” instead. I better stop before I go down deeper into the rabbit hole (btw, I watched the new Matrix movie yesterday, talk about abundance of “data” in that thing!)</p><p>Let’s lay down some principles for this conversation. I will resist all attempts, foreign and domestic, to mention any commercial vendors, where possible, maybe. You, of course, are free to comment on your favorite database vendor later, I may have an opinion too then :) I reserve the right to mention open-source software. I will focus on the ideas that drove and continue to drive our company choices, and since I’ve been here for many years there is a very high chance these choices and my advice correlate, a lot. Couple more things to mention — being a large bank, we are influenced by several critical factors — a) any open source software we use must be backed by a commercial distributor, b) we often invest, develop, and actually use software from startup database companies one may never heard of until they go public, c) there is a very high barrier to entry for any new database software due to strict requirements to data protection, security, etc. d) we are predominantly a Linux shop, but interestingly enough not 100% when it comes to the databases.</p><p>What does it all mean for the readers of this blog? It means our choices are pragmatic, both conservative and innovative at the same time, commercially viable, vetted on a variety of use cases, from small scale proofs of concept to high-end “Big Data” super projects. And we do innovate — we have identified and onboarded 4–5 new database vendors in the last 15 or so years. That’s a crazy figure for a large investment bank. And I am not even including things like Kafka, Spark, various BI platforms.</p><p>Here are a few more principles I use myself in addition to the company ones.</p><ol><li>When looking for a new database, first see what you already got in house. That includes cloud data services too, not just on-premise offerings. See <a href="https://en.wikipedia.org/wiki/Possession_is_nine-tenths_of_the_law">Possession is nine-tenths of the law — Wikipedia</a>. Reasoning: the costs of onboarding of a new database are very high, it is a hell lot easier to use something you already have, a bird in the hand… and so on. Your senior technology leadership will love you for that because nothing strikes fear into the DBA’s heart as the sentence “we are going to purchase a new database platform” :) A little personal anecdote — many years ago I was running a complex analytical platform on top of a traditional relational row-based database. The system could not be scaled anymore, and I wanted to bring a modern, columnar MPP platform to run a proof of concept. I still keep an email from the head of Data Engineering stating “we already have several platforms to choose from and the Firm would not be entertaining adding another”. Well, he was wrong, I was right, and that “new” platform now houses over 100 Petabytes of data within the bank. And he is not with the Firm anymore :)</li><li>Anything new you bring has to be an “order of magnitude” better than what you already have. I know, this sounds subjective. So, let’s bring it down to the proper level of pragmatism — say you have an in-house database capable of 1000 transactions per second. Can you ride the “hardware wave” and scale it to 10000? Probably, and thus you don’t need that shiny new database, and the head DBA is right. But if you can’t, or the new software can do it on the same hardware or just scales better in general, that’s your ticket! So, the rule reads as “10 times better on the same hardware or 50x better on the new hardware”. Well, that’s a dream of course.</li><li>You will have multiple database platforms. And you will have the same data copied into multiple databases. Despite all efforts by the senior management to keep everything in just one database. That’s pragmatism. Databases are like cars. Some are like dump trucks — they can carry huge loads but aren’t very fast; some are incredibly fast but lack horizontal scale and can only hold 1 day worth of data; some are very rigid in structure but allow instant access through that structure; and some are ultimately flexible and can hold any data but require heavy indexing to make it work. Just like you wouldn’t take a dump truck to drive your kids to school, you also don’t take your family car to carry 40 sheets of plywood from Home Depot. (“But I do that!” you say. And I do too! But it does not make it right and eventually that SUV will become a piece of s…. if you keep doing that Home Depot run every week) Don’t turn rules 1 and 2 into a religion. And thus, we have rule #4.</li><li>“3 strikes and you’re out!” rule. What it means in practice — don’t count how many things your favorite database can do for you, count how many features it lacks, and you cannot live without them. That sounded a little like JFK so let’s bring it down with an example — say, you want to store PDFs and images, but that beautiful database can only store columns of text and numbers. That’s a “strike”. Or maybe you want it to be able to concurrently update multiple rows per transaction, but it can only do 1. That’s a strike too. Ignore all the marketing mambo-jumbo and focus just on those critical must haves. I’ll list a few more examples — lack of a modern resource manager to support high concurrency workloads; lack of massive parallel processing and horizontal scale out; lack of SQL support; lack of modern authentication support; lack of modern encryption support, lack of flexible schema-on-read, lack of time series support, lack of window functions, lack of secondary indexes, etc.</li><li>Unless the feature you want already exists or at least is in beta and going to be released in the next 6 months, at the most, <strong>it does not exist</strong>. Future roadmaps do not count. Database development is a very time-consuming process, with features laid out years in advance and roadmaps difficult to change. It is also incredibly difficult to change core design of an existing database, in the best-case scenario it will be a bolt-on feature lacking the same maturity as the core engine. Example — one of major database vendors added support for columnar table storage under pressure from their marketing department. It came with so many limitations that only following several major versions worked OK. Seeing through the marketing claims of the sales department was a challenge, and only deeper understanding of the product let us identify the immaturity of the claim.</li><li>If possible, look deeper into the leadership of the commercial vendor, or open-source community behind the platform. Are they visionaries? Are they capable of marketing the product to grow its market share? Can they bring enough new capital to fund future development? Working for a bank has its advantages — we have a very strong technology research department focusing on the startup space. But if you don’t have access to such a resource, do your own due diligence. For an open-source product, see if there is a strong company backing it, employing major committers. If there isn’t, more likely than not this product won’t make it. For a commercial vendor I check if the database product is their main offering or part of some larger platform. Essentially, is the leadership of that company committed to their product or the database was acquired in some merger, and they don’t know what to do with it now. I stay away from the “merger acquisitions”.</li><li>Market share. A highly subjective topic. From a pragmatic point of view, I look into the relative growth, not the absolute — is the platform gaining or losing their ranking. This is like doing any other due diligence. Check out <a href="https://db-engines.com/">https://db-engines.com</a></li><li>Ecosystem<strong>!</strong> With a very bold exclamation point<strong>!</strong> One of my biggest “beefs” with DBAs and database vendors is lack of attention to the drivers and the connectivity from compute platforms and BI tools. I made it a point that any database platform is reviewed in terms of compatibility with the clients. Consider it a “strike” if the database does not offer a Python driver or has no Windows driver support, or whatever other tool you want to connect to it. I feel really good when a vendor offers a full spread of the drivers, and if they can’t build one on their own, they may resell one from the other vendors like CDATA.</li><li>Commercially feasible, practical pricing and no arm twisting by the vendor. Let me explain what I mean. Despite being a big bank with lots of money, we want to get the biggest bang for the buck — we look for pricing models where we can grow, and these have to be modern and not atavistic. When I hear someone charging by a number of cores, my mood turns sour. Pragmatic outcome — vendors who ask for less money and give us a better deal, e.g. unlimited use for a few years tend to have a much better future with us, conversely someone who sold us a few servers at a high price eventually fizzled away. This is why open-source platforms with commercial support (like Kafka, Postgres, MongoDB) are so successful.</li><li>I don’t believe in database appliances. I did not believe in them when we had kept all the data on premises, but with the advent of the cloud databases I think even less of that business model.</li><li>And this brings me to the critical point — enthusiasm of the external and internal communities (not to be confused with the market share). This is related to our ability to hire talent and influence senior leadership within the company. A database platform with a great following is more important (to me) than one with a larger market share or even with “better” features.</li></ol><p>Well, I think that’s enough of “principles”. If I was reading this blog myself, I’d have thought — “you have no system”. That’d be harsh, if not pragmatic assessment. It would not be true tough. I did not go into how exactly we test and validate designs — starting with an initial sales call all the way to high performance proofs of concepts, security reviews, etc. That sounds like an excellent topic in itself. Perhaps, I will do that next. Or I may actually jump into why doing joins on strings is not any worse than on integers. I am looking forward to any comments on medium or linkedin. And on that note, I thank you all for spending a few moments of your life on reading this blog and have a HAPPY NEW YEAR!!!</p><p>Edit: Link to the third blog in the series <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-thou-shalt-not-skew-part-1-309ddec12719">Data Engineering by a Pragmatist — Thou Shalt Not Skew! (Part 1) | by Boris Serebrinskiy | Jan, 2022 | Medium</a></p><p>And the 2nd — <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-tales-of-choosing-a-database-5f9f2f3cc1ab">Data Engineering by a Pragmatist — Tales of Choosing a Database | by Boris Serebrinskiy | Jan, 2022 | Medium</a></p><p>P.S You can also find me on LinkedIn <a href="https://www.linkedin.com/in/silverboris">https://www.linkedin.com/in/silverboris</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5f9f2f3cc1ab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineering Story by a Pragmatist]]></title>
            <link>https://silverbob.medium.com/data-engineering-story-by-a-pragmatist-3a1e68952ea9?source=rss-1444731888f7------2</link>
            <guid isPermaLink="false">https://medium.com/p/3a1e68952ea9</guid>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Boris Serebrinskiy]]></dc:creator>
            <pubDate>Sat, 25 Dec 2021 22:32:41 GMT</pubDate>
            <atom:updated>2022-01-14T18:18:25.116Z</atom:updated>
            <content:encoded><![CDATA[<p>Prolog: As I look at the rainy Christmas morning through the window of my Brooklyn work-from-home-for-almost-two-years office, I am still a little lethargic and short on coffee from waking up early to watch James Webb space telescope lift off. My two cats watched it with me (or maybe it was them letting me watch? Cats…) Right now in my mind I am still playing back last night’s Christmas dinner…. My sons telling me (I have two, neither watched Webb’s liftoff but surely will want to hear all about it, in the most minute detail as they unpack their presents) — “Dad, you love what you do but stop telling us “when I retire, I’ll open a school in Costa Rica to teach kids how to write software programs…write a blog instead, and do it tomorrow!”</p><p>They make good points — while I love Costa Rica (6 visits and counting), I am not planning to retire any time soon — to the utter bemusement of my wife. But I do love to talk about what I do, and I don’t think of it as software programming. Those Costa Ricans kids — no chance to hear me talk then?</p><p>What exactly do I do for a living? In short, I work for a large US bank and design computer related “stuff”. To most, computer programming jobs are not considered your typical engineering field, and in most US schools likely to be aligned with math departments. But I think of it as an engineering job. Back in the day, I graduated with a “real” engineering degree from a university in the USSR, learning how to design nuclear plants, and one thing for certain — I could not have done it without first falling in love with the art of “software engineering”. I spent more time on our mainframe than I did drawing blueprints. I simulated hydro and thermodynamics scenarios and ran through thousands of variables in my first Pascal programs to find the most efficient curve of the turbine blades. My first paying job was designing computer simulators for refueling of nuclear reactors. I already knew back then that I code to solve engineering problems, not because I wanted to find a better algorithm.</p><p>Don’t get me wrong, there was a real computer science school at the university, and I took classes there. But one thing struck me — while I had real engineering challenges to solve and I used computers to do it, the compsci major folks just liked tweaking their algorithms to be “even more efficient”. Pragmatism was nowhere in sight. The concepts of “Time to market” or “Can this be used in real life?” haven’t been on their minds. Well, except maybe during the finals, but for all the different reasons.</p><p>My Masters thesis was the first in the history of the department with presentation boards holding block diagrams of C++ simulation of a nuclear reactor losing its cooling following an earthquake breaching its containment. That idea of “applied programming” stuck with me. And while life took me into a different direction away from designing nuclear power plants, that principle of pragmatism is what I “preach” in my daily job.</p><p>OK, so I think I am still an engineer. But just like doctors have specializations, so do engineers. I don’t calculate loads for a new bridge across Hudson river (that will never happen anyway), or thickness of concrete for a highway bed, or maximum rotational speed of an aircraft engine fan, or size of elevator shafts in skyscrapers. I deal with “data” problems.</p><p>My engineering specialty is “Data Engineer” (no relation to the famous character in Star Trek Next Generation TV show). While I hope this blog is read by my peers, other data engineers who obviously understand what this job entails, I want to offer how I personally understand it. To me, it is the amalgam of knowledge, technology, software, hardware aiming to solve challenges of capturing, storing, processing and analyzing the vastness of the information (a.k.a. “data”) surrounding us. I won’t bore you with the infamous “3Vs of Big Data” (feel free to google that, I hear there are even “7Vs” now), but the job essentially is battle all those Vs.</p><p>With 25 plus years on the job, I will attempt to provide my somewhat biased opinions deeply rooted in pragmatism rather than an academic approach. I’ll push against the conventional wisdom or marketing hype. I’ll advocate to bear “technical debt” to deliver results to the business, use “strings” instead of “integers” for star schema joins, and all such other nonsense.</p><p>In my first post-prolog blog I will be discussing the choices of databases to store your favorite data. Marketing departments need not apply, this is going to get real! See you soon!</p><p>Edit: Link to the second blog in the series <a href="https://silverbob.medium.com/data-engineering-by-a-pragmatist-tales-of-choosing-a-database-5f9f2f3cc1ab">Data Engineering by a Pragmatist — Tales of Choosing a Database | by Boris Serebrinskiy | Jan, 2022 | Medium</a></p><p>P.S You can also find me on LinkedIn https://www.linkedin.com/in/silverboris</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3a1e68952ea9" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>