<?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 Arnaud Milleker on Medium]]></title>
        <description><![CDATA[Stories by Arnaud Milleker on Medium]]></description>
        <link>https://medium.com/@arnaud-milleker?source=rss-74f99862b026------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*JeIzIY7K8MMSKZjc</url>
            <title>Stories by Arnaud Milleker on Medium</title>
            <link>https://medium.com/@arnaud-milleker?source=rss-74f99862b026------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 07:46:01 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@arnaud-milleker/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[Honest feedback about our FULL AWS Lakehouse migration, and pain points of such projects]]></title>
            <link>https://arnaud-milleker.medium.com/honest-feedback-about-our-full-aws-delta-lake-migration-and-pain-points-of-such-projects-8d33483bd23d?source=rss-74f99862b026------2</link>
            <guid isPermaLink="false">https://medium.com/p/8d33483bd23d</guid>
            <category><![CDATA[delta-lake]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[architecture]]></category>
            <dc:creator><![CDATA[Arnaud Milleker]]></dc:creator>
            <pubDate>Sun, 03 Nov 2024 18:53:21 GMT</pubDate>
            <atom:updated>2024-11-07T09:25:46.675Z</atom:updated>
            <content:encoded><![CDATA[<p>Let’s be honest: no one said “Let’s build a Lakehouse with only AWS tools! It’s gonna be fun”. But we did it! Is it that good? That bad? Let’s look at each AWS tool in this context 🥳. Then, we’ll take a step back to discuss Lakehouse migration in general (not only AWS) and think about the challenges we’ve faced, as well as the ones you might encounter.</p><p>Disclaimer: it may not be the best technical approach, but sometimes you have to deal with global architects or strategies from your company. The rule was: go full AWS, as managed as possible.</p><p>Summary<br>1 — The technical architecture, goodies and badies<br>2 — Lakehouse migration pain points (not AWS specific)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*Cztohk4ELT-kgOlgqNnawg.jpeg" /></figure><h3>The technical architecture, goodies and badies</h3><h4>How we deployed this:</h4><p>- S3 to store Iceberg files for bronze and silver layers<br>- Redshift as a single entry point to all Data (S3, Aurora) in SQL<br>- Mainly Athena or Spark in Glue to process the Data (Bronze and Silver layers)<br>- Redshift to store and process KPI Data (Gold layer)<br>- Aurora to serve API<br>- Airflow with MWAA to orchestrate that</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nrFhBoyAOtNoIsjsKKUiYg.jpeg" /><figcaption>Simplified overview of our Lakehouse architecture</figcaption></figure><p>Now that we have the big picture, let’s break this down for each AWS tool !</p><h4>S3, Athena and Glue catalog with Iceberg format</h4><p>✅ Really easy to deploy and manage.<br>✅ Low cost compared to Glue<br>✅ Iceberg wasn’t a game changer for automation, as we weren’t extensively using updates or complex deletes.<br>✅ However, Iceberg was a huge game changer for manual tasks: when fixing or creating new columns, updates and specific deletes were very useful.<br>✅ Athena excelled at computing large volumes with simple aggregations.<br>❌ However, for more complex processing, it didn’t perform as well as the Spark engine, and we encountered AWS processing account limitations.<br>✅ Partition management with Iceberg is a plus for handling small partitions.</p><h4>Glue Spark</h4><p>✅ Great for small/medium volumes.<br>❌ It’s not ideal for large volumes due to the latency of transferring data from S3 to Glue and back to S3.<br>✅ Great for complex calculations and/or chained queries.<br>❌ It’s both code AND a ressource to manage, making it a bit more complex than just code (like Athena)</p><h4>Redshift</h4><p>Important new mindset to have: it’s no longer just a processing engine. You have to think of it as a unified SQL interface for all our Data sources, including S3 and Aurora.<br>✅ Redshift is OK to be this single SQL entry point as it can access S3, Athena, other Redshift.<br>✅ Fast processing.<br>✅ Easy access for all users.<br>❌ High cost !<br>❌ You can’t migrate the entire silver layer out of it.<br>❌ Redshift serverless has some limitations making it hard to use (it’s always up if your reporting tool sends a heartbeat signal).<br>❌ You’ll need occasional DBA support.</p><h4>Aurora and Airflow</h4><p>Nothing specific here in the Lakehouse context. Aurora is a good OLTP database, perfect for API calls. Airflow is a good orchestrator (lots of great functionality, huge community, and customizable features), even with its limitations.</p><h3>Pain Points in Lakehouse Migration</h3><p>(This part is not AWS specific)</p><h4>Your main goal: the business value</h4><p>The main goal of everyone in a company is to improve the business directly or indirectly.<br>✅ The tools are easy to deploy and use, and they bring a lot of value in terms of cost or speed improvements.<br>✅ Since you have time for this migration project, you can use it to resolve some technical debt that could have been addressed in the previous architecture — but you didn’t have time then 😅.<br>❌ You can’t migrate EVERYTHING ❗ You will have too much legacy to migrate. Imagine you want to rename every ressource, jobs, tables, indicating the medallion layer step (bronze, silver, gold): if you can migrate everything, I think you invest too much time and money. Most of the companies don’t have that much time. You may need to “Pareto” your project. So be aware of this when you begin</p><h4>Lakehouse illusions</h4><p>In every project or new technology, there is 😎HYPE 😎. Let’s manage expectations to prevent future disappointments!<br>- You belong to an organisation with global rules. Sometimes global functions (like Architects) will say no to fancy tools. Speak with them before they stop you, making your architecture incomplete.<br>- You want to think of a FULL platform, answering to everything. That prevents you from using different tools. Some examples: we didn’t chose DBT because Spark jobs weren’t managed by DBT at that time; Snowflake and Databricks are actually platform that can replace very efficiently some part of your architecture (the Data warehouse for example) but are best replacing your whole platform. Here we (and global architect) choose to not to mix AWS with another platform.<br>- SQL at scale is a strength and a risk ! Under the hood, Redshift, Snowflake, Spark, everything take your SQL and process it quickly. But all those tools are clusterised so the query you do in appearance is simple, but it implies networking, skewness, possible memory overflow. Be aware of that and provision the Data expert of your team to watch this regularly … Especially when doing self-service BI ! Monitoring this will keep cost and long queries for away.</p><h3>Final word</h3><p>In the end, I find the AWS Lakehouse to be (not great but) good. We can have SQL everywhere, simplifying development. We have a single entry point for every Data, cleaned and organized. Compute and storage are decoupled. Costs are under control.<br>Sure, we are missing the fancy stuff (Snowflake, Databricks, DBT) but the stack is coherent with our platform, the company platform, and the business priorities. Still, evolutions are never too far away, and we may improve our platform again!</p><p>Feel free to share, critique or ask anything 🤗</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8d33483bd23d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineers are no longer “Data” folks]]></title>
            <link>https://medium.com/doctolib/data-engineers-are-no-longer-data-folks-d10d44712580?source=rss-74f99862b026------2</link>
            <guid isPermaLink="false">https://medium.com/p/d10d44712580</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[bi]]></category>
            <category><![CDATA[dataops]]></category>
            <category><![CDATA[engineer]]></category>
            <dc:creator><![CDATA[Arnaud Milleker]]></dc:creator>
            <pubDate>Mon, 05 Apr 2021 10:34:46 GMT</pubDate>
            <atom:updated>2021-05-21T08:31:51.254Z</atom:updated>
            <content:encoded><![CDATA[<p>Would you hire a Python/Airflow expert OR a Database/SQL expert for a Data Engineer position?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/482/1*01I8KlQpILU48Sq2_Tc51A.png" /><figcaption>Which expert would you choose?</figcaption></figure><p>In my team (and on the market) the job is shifting more and more, let’s look at that more closely!</p><p>1 — The Data Engineer and its ancestor role</p><p>2 — Software craftsmanship DataOps</p><p>3 — Data after all</p><h3>1 — The Data Engineer and its ancestor role</h3><p>We are in 1990, the sun is shining and somewhere, someone realizes: “We have no Data Management, John !”. And BOOM, Business Intelligence was born.</p><h4>Business Intelligence engineer</h4><p>Business Intelligence practices are a way to organize Data and make it understandable for reporting purposes. More precisely, a BI engineer manages the ETL processes (<strong>E</strong>xtracting the data from somewhere, <strong>T</strong>ransforming this data and <strong>L</strong>oading it elsewhere). The Data is rebuilt so that it can be used for analytics purposes (KPI, reporting, Data Mining). Sometimes BI engineers even build the reporting themselves.<br>The code is simple: there is (in most cases) no code! They use click-button tools that do the job for him: Datastage, Informatica, Talend etc. At the same time, they write SQL all day long, manages his database and reports. Data is a “business” thing.</p><h4>The new dawn of Data is here</h4><p>It’s 2010, Big Data is comiiiing! This is HUGE. It’s not Data, it’s DATA. It comes as a yellow elephant in a well organized ecosystem, it challenges everything. Within a couple of years, you realize that:<br>- You need to code to get that Big Data: Java, Scala, Python<br>- ETL tools are very expensive and are being challenged by Airflow (and other tools), an “infrastructure as code” tool that can manage workflows in a better way for free.<br>In five years, Airflow becomes the #1 tool for ETL. Depending on the Data use-case (with or without Airflow), you should probably code in Scala or Python… BOOM, Data Engineering is born!</p><p>ETL building went from SQL focused people with a business acumen to developers with infrastructure and SQL knowledge.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/410/1*73gu459WiH16PNVAO36tdg.png" /><figcaption>From no-code ETL to ETL developer</figcaption></figure><h3>2 — Software Craftsmanship DataOps</h3><p>I hope everyone wants to be a craftsman, whether they are a BI engineer or a Software engineer. Let’s hear the manifesto one more time because we love it:</p><blockquote>As aspiring Software Craftsmen we are raising the bar of professional software development by practicing it and helping others learn the craft. Through this work we have come to value:<br> Not only working software, but also well-crafted software<br> Not only responding to change, but also steadily adding value<br> Not only individuals and interactions, but also a community of professionals<br> Not only customer collaboration, but also productive partnerships<br>That is, in pursuit of the items on the left we have found the items on the right to be indispensable.</blockquote><h4>Scale with craftsmanship</h4><p>As a Data Engineer, you are building the ETL with Airflow, SQL, a Database, maybe some event Data management with streaming.<br>Wait! Did I say you do SQL (just like a BI engineer, so my demonstration may be wrong)? Well, yes and no… You do it for now but not for too long! Your goal is to build a Data Architecture around tools that will be used by the whole company. Indeed, at the beginning, you need to do SQL to set up the basics of your database. But your long term goal is to build a Data Architecture that will be used by the whole company. More and more, your role shifts from SQL-doer to Data facilitator. You cannot be a bottleneck every time someone wants new Data, so you have to help them manage their Data needs. As a true Software Craftsman, you build a clear and maintainable architecture, and make communities learn how to use your infrastructure easily. In the end, tech teams will add the Data they want to analyze in your Data Lake, Analysts and Data Scientists will modify your ETL to create the Data they want.</p><h4>Build faster!</h4><p>So your main focus is now to build and maintain an architecture. You use Terraform, an “infrastructure as code” tool to deploy your whole architecture through code. You also need to monitor your infrastructure in real-time as you are accountable for it. Because you love it, you may also manage the access to this platform. Oh wait… Am I becoming a DevOps?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/250/1*QkxrWdVi143Bk7BpLkj5Iw.jpeg" /></figure><h3>3 — Data after all… But differently</h3><p>Let me rephrase what I’ve previously said: Data Engineers now, do less and less (sometimes no more) databases or SQL-oriented jobs, so they are more and more Tech Engineers. They also tend to become DevOps, because they are managing infrastructure. Breaking news: I must recognize that you are still a Data person. Indeed, if you weren’t doing Data stuff, you would probably have used something else than Python or Scala. You wouldn’t use Airflow either. And finally, you are not DevOps, you are DataOps, only playing regularly but not always, with DevOps toys. So yes, you are still Data! We can even say that BI engineers evolved in a more efficient form by becoming Data Engineers.</p><p>This move where Data Engineers are going away from the old school Data is understandable. Databases are becoming serverless/on-demand/infinite-auto-scaling, they seem easier and less critical to manage. However, be careful with this “natural” move where you move further from Databases and SQL. It can lead to more expensive architecture in terms of money and usage. So don’t be too much lured by the code and infrastructure importance, or by those auto-managed-thingy promises. Still, the shortcut is understandable as you may have other priorities than architecture optimization.</p><p>In the end, it’s funny that in 10 years, the “Data” in Data Engineering is no longer referring to Data stuff directly (Data modeling, Databases and SQL) but more about tools to manage it, and that’s why I wanted to share this blog post with you.</p><p>To those of you who read this article till the end, thanks for reading! And …</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*RX4TWxcKzFHd-G_aRhMW9w.png" /><figcaption><em>Heal the World, <br>Make it a better place,<br>For you and your crew,<br>To give Data at the best paaaace</em></figcaption></figure><p><em>If you want more technical news, follow our journey through our </em><a href="https://doctolib.engineering/engineering-news-ruby-rails-react/"><em>docto-tech-life newsletter</em></a><em>.</em></p><p><em>And if you want to join us in scaling a high traffic website and transforming the healthcare system, we are hiring talented developers to grow our tech and product team in France and Germany, feel free to have a look at the </em><a href="https://about.doctolib.com/jobs?department=Engineering"><em>open positions</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d10d44712580" width="1" height="1" alt=""><hr><p><a href="https://medium.com/doctolib/data-engineers-are-no-longer-data-folks-d10d44712580">Data Engineers are no longer “Data” folks</a> was originally published in <a href="https://medium.com/doctolib">Doctolib</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Redshift : Troubleshouting made simple]]></title>
            <link>https://medium.com/doctolib/redshift-troubleshouting-made-simple-ed73b38d10d5?source=rss-74f99862b026------2</link>
            <guid isPermaLink="false">https://medium.com/p/ed73b38d10d5</guid>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[troubleshooting]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Arnaud Milleker]]></dc:creator>
            <pubDate>Thu, 02 Jan 2020 10:23:00 GMT</pubDate>
            <atom:updated>2020-06-15T14:34:07.564Z</atom:updated>
            <content:encoded><![CDATA[<h3>AWS Redshift: Troubleshooting made simple</h3><p>Welcome! Here you will discover how to make Redshift great again by identifying the exact problem you have, and the adequate solution for it.</p><p>At the beginning, everything was so simple. You were alone on your beautiful new cluster. He was new, big, and so fast. But quickly, more and more people are working on it. Just like invaders on a new promise land, they plunder the wealth of your cluster without thinking about consequences.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K2YGD0Yir5E3owkCgDBLNQ.png" /><figcaption>Pioneers discovering Redshift</figcaption></figure><p>Only one person is responsible for it: You! After a while, you encounter some problems that are penalizing:</p><ol><li>Disk full error</li><li>Your query is not running</li><li>Your query is slow</li></ol><p>We will see together how to tackle those problems. This article also discuss the basics of a performant Redshift cluster, where you can improve a query by an average x20-x30 time, especially Distkeys and Sortkeys parts. Feel free to use it for troubleshooting, but also for performance tuning.</p><h3>Disk Full</h3><p>Everything was fine. It’s a sunny day. Birds are singing. And you see it …</p><blockquote><strong>error: Disk full</strong></blockquote><p>It can’t be possible. Not during your watch. You run toward your admin interface (AWS Portal &gt; Redshift &gt; Clusters &gt; Your cluster &gt; Cluster performance &gt; Percentage disk) … And you see it: The Great Wave off Kanagawa</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CMf_AoD_6we8DVRFmcRiPA.png" /><figcaption>Disk hits 100%</figcaption></figure><p>This is the apocalypse. When disk hits more than 100%, every query is killed (not only yours). So, you can expect to have every user coming to you to know what did happen. Fun fact: the user often thinks he is the source of the problem and keeps it to himself, ashamed.</p><p>What did happen? There was not enough memory space, so the query used the disk to store the temporary needed data. This process is called “<strong>SWAP</strong>”. But again, it was not enough. All the disk was used, causing a massive crash. We will see how to avoid this through volume management.</p><p>To identify the tables that need your attention the most:</p><pre>SELECT “table”, size, tbl_rows<br>FROM svv_table_info<br>ORDER BY size DESC;</pre><h4>Architecture for volume management</h4><ol><li>Use Spectrum to externalize historical data. Ask your teams what data they need on a regular basis, and what data are considered “old”. Unload as Parquet the “old” data into S3. Use Glue crawler to have the structure. Remove the Data from Redshift.</li><li>Good practice: 66% of disk used by data when there is no Swap. If you are above it and there is nothing to reduce it, you need more nodes. Choose classic resizing if you add some nodes.</li><li>If you need temporary space, you can do an elastic resizing of your cluster to get more space. As it is a fake resizing (it redistributes the slice, not the data, which can induce skewness), go back to your previous configuration after doing it.</li></ol><h4>Table management for volume improvements</h4><ol><li>ENCODE every column (except sortkeys) as AZ64. If it’s a varchar column, encode it as ZSTD.</li><li>Remove tables that are never used (not scanned since 30 days). You can find <a href="https://github.com/amilleker/redshift_scripts/blob/master/get_last_scan_table_date.sql">a script here</a>.</li><li>Reduce at maximum the varchar columns as they are responsible for most of your disk space use. You can use the following query to know the max varchar size of a column:</li></ol><pre>-- get max varchar size of your_column<br>SELECT max(octet_length(your_column)) FROM your_table</pre><h4>Query optimization for volume handling</h4><ol><li>Don’t use large varchar columns (VARCHAR &gt; 256) for “join” as the keys could be put in memory for faster join, which can overload memory.</li><li>Never use select *, take only the column you need.</li><li>Limit the source tables with “where” filters.</li><li>Avoid data redistribution between nodes that multiplies the volume of your query SEVERAL times. Use Distkeys, which indicates how your data are split between your nodes. For example, if you have several tables with client_id and you know you will make JOIN with it, use it as the Distkey.</li><li>Do regular Vacuum and Analyze, so Redshift can use the Distkeys.</li><li>No nested SELECT, they don’t remember the Distkey.</li><li>When you JOIN data coming from Spectrum, use a temporary table with Distkey to avoid data redistribution from a huge Spectrum table.</li><li>Read the “query plan” to know if there is a data redistribution:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*au0Fo2-k9Y8sKcmMiMgulA.png" /><figcaption>Query plan: look at uppercases, if the last one is not “NONE” then there is a data redistribution - and it’s bad!</figcaption></figure><p>The good thing when you have a full disk error is that at least, you had your query running!…</p><h3>Query is not running</h3><p>You don’t understand. Your query is soooo slow. You don’t even know if it’s really running or not. You want to know? Simply run those queries:</p><pre>-- displays every query that run or will run<br>SELECT user_name, db_name, pid, query<br>FROM stv_recents WHERE status = ‘Running’;</pre><pre>-- displays every running queries<br>SELECT * FROM stv_inflight;</pre><p>If your queries are not running, you will see it in the result of the first query, but not in the second. What if there are more queries like yours? You can see it in the AWS Redshift interface: AWS Portal -&gt; Redshift -&gt; Clusters -&gt; Select your cluster -&gt; Database performance</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O5jLAJHYIUiyshqjy4emWA.png" /><figcaption>Waiting time in green</figcaption></figure><p>You can see that on Dec-30 at 2:40 (ETL scheduling), we had more waiting time than query execution (reading + writing time). There are 4 possible causes: locks, bad queues, architecture, bad performance.</p><h4>Locks</h4><p>Some queries (like DROP or TRUNCATE) need an “exclusive access” to tables. Doing so, it prevent queries from accessing to those tables. To identify those locks, you can execute this query:</p><pre>SELECT * FROM svv_transactions;</pre><p>If “lock_mode” is “AccessExclusiveLock”, you have a locked item. To unlock the item, you can either wait, or delete the query by doing:</p><pre>SELECT pg_terminate_backend(<em>your_pid_here</em>);</pre><p>In some cases, the query can be slow to revert (as Redshift needs to rollback some tasks). In other cases, this “terminate” command is not enough and you have to … Reboot the cluster. It will take several minutes.</p><h4>Bad queues and WLM management</h4><p>Sometimes your queries are blocked by the “queues” aka “Workload Management” (WLM). It’s bad, but Redshift without queue management can be even worse (numerous disk full error and slow queries). Queues allow you to limit the number of parallel queries and the memory allocated to them, depending on the user or rules. You would want to do that for two main reasons:</p><ol><li>Managing query priority</li><li>Prevent SWAP on disk</li></ol><p>As I won’t focus on WLM here, I can’t develop all the possibilities. I prefer manual WLM for all the control it gives compared to elastic WLM (didn’t work on our cluster because of the reporting tool).</p><p>WLM can allocate minimum memory always available for a user (you, the reporting tool, ETLs, …). The downside is that if you give 10% reserved memory for the ETL, those 10% will not be used, even if there is no ETL running. That’s why we do not use “Memory %”. As we are only using WLM to avoid “Disk full” and memory SWAP, limiting the memory would not be a good idea. Note that the AWS advice is to have around 15 queues.</p><p>Why and how do you prevent Redshift SWAP with WLM? SWAP uses disk, which is much slower than memory, and you want to avoid it. By knowing how many queries of the same type can run on your cluster, you can prevent<strong> </strong>SWAP (or other negative effects) with WLM. Here is an example with our cluster:</p><ul><li>3 queues for ETLs and automates. They hit the CPU hard and slow each other if there are more than 3 queries doing Insert/Copy/Drop/Truncate/…</li><li>10 queues for reporting. They are fast but come all together. With 10 queries at the same time we rarely hit the disk for swap.</li><li>3 queues for everything else. We can only have 3 user queries at the same time, which is enough for 25 data people. We added a timeout at 15 minutes for bad queries. NB: 15 minutes for a SELECT is often a REALLY bad performance.</li></ul><p>To know if your query is blocked by WLM:</p><pre>SELECT * FROM stv_wlm_query_state ORDER BY query;</pre><p>You will see the current state (waiting or running) of your query depending on the WLM with the queue_time.</p><h4>Architecture</h4><p>As Redshift is an OLAP database, it’s oriented to work on analytical queries (as opposed to OLTP with transactional queries). To resume, it prefers to get a couple of big queries rather than a lot of small ones. As soon as we know this, if you don’t want to overload/break Redshift you should avoid to:</p><ol><li>Stream events: prefer Kinesis or Lambda to aggregate bulk of data.</li><li>Do multiple insert to load row by row. Prefer COPY.</li><li>Connect a reporting tool directly on Redshift. Every common reporting tool has a cached/in-memory database. Put the Redshift data in it.</li></ol><p>The best way to avoid blocked queries, is to have only quick queries. Let’s see how we can do this…</p><h3>Your query is slow</h3><p>As soon as your SELECT takes more than 3 minutes, there is a great probability that you can do better. You need specific nodes, and a full understanding of distkeys and sortkeys!</p><h4>Your cluster nodes</h4><p>If you are not in a region where “ra3” nodes are available, use “dc2” nodes for your cluster, not “ds2”. Dense compute nodes have more RAM and an SSD disk (faster than HDD). Ds2 nodes have more disk space, but you don’t need it with Spectrum.</p><h4>Distkeys</h4><p>When you create a table, you have 3 distribution styles called “distyle”:</p><ol><li>Diststyle key:Data are distributed across nodes by the key. The same keys are on the same node, even between different tables. Example: the distkey of table1 and table2 is the customer_id. If you join both tables by customer_id, data are joined locally. It spares a lot of networking and memory, as you move only a subresult and not the full data. Do it if the table is big, with a lot of different values on the key, with data well distributed between keys, and the key regularly used in the JOINs.</li><li>Diststyle ALL: It puts the full table on every node. It’s interesting when the table is small and you have several JOIN keys in it. With this, every join on this table will be done on the local node. The data will never be replicated during a query as they are already on the node.</li><li>Diststyle EVEN: The data are redistributed equally between nodes. Do it when the other two solutions aren’t available.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OgU4f17UDM-d4sx1y1OkDg.png" /><figcaption>Table 1: distkey(col_id_T1), Table 2: distkey(C_ID_T2). Same IDs are on the same slice. If they are JOIN together, there is no data network</figcaption></figure><p>Again, the main goal of distkeys is to avoid any broadcast of data between nodes. By doing so, you also want your nodes to be equally loaded. If data are not equally distributed, you will have “skewness”: the overloaded node will always be late compared to others.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oeC9w5OF5hOnY-GiaGLeJA.png" /><figcaption>It’s not EQUAL, you have a 6% difference. You lose a 6% performance because the overloaded node needs to work 6% more</figcaption></figure><p>To identify table with skewness, you can use <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_analyzing-table-design.html">this link</a>.</p><pre>-- to know the slice distribution between nodes. It should be exactly equal. If not, you need to do an manual (not elastic) resizing of your cluster<br>SELECT node, count(*) FROM stv_slices GROUP BY 1;</pre><h4>Sortkeys</h4><p>It sorts the data into the blocks (1 MB) of slices (a part of a node) of nodes, and stores the possible values in a block zone map (min and max values). Thanks to this, Redshift knows if it needs to get the data quicker and doesn’t use the block if there is no need to this.</p><p><strong>Compound sortkey</strong></p><p>This is the default sortkey. It orders the data by the column that you specified, ordered by the priority you specified. Example: sortkey(id, date, city) will order your data by id, then by date for each id, and finally by city for each date for each id. It will help a lot the WHERE filter. Be aware that if the city is in the WHERE clause, but not the date, your sortkey will probably be a handicap as you will still need to get every block for specific city. You need to be sure that the first sortkey will always be used if you want the others. Dates are usually good compound sortkeys as they are often used in queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/548/1*3cfsv7R9Hv42ONkxnvLeMg.png" /><figcaption>compound sortkey (col_id_T1, date, city) with 3 values by blocks. For id1, Redshift will know in advance BLOCK1 &amp; 2 are good. For Paris, it needs to check every blocks</figcaption></figure><p><strong>Interleaved sortkey</strong></p><p>With this type of sortkey, you order the data by block without priority. If you know that you will want to filter either by date (without city), or by city (without date), or by other columns, this is the good sortkey.</p><p><strong>One optimization above all: the Merge Join</strong></p><p>When your distkey and your first compound sortkey are the same, Redshift uses the “merge join”. This join is incredibly fast and doesn’t need to use memory. As soon as your distkey can be the value you use to join tables, you should go for it. As you will have a sortkey with this join value, “WHERE” clauses won’t be so effective, but this is a small loss for a big win. This is super effective because Redshift has only one scan to do on both tables. As they are sorted alike (sortkey) with the same node distribution (distkey), the join can be done at the same time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/710/1*TeDih-_bQuyRDQoIEfzvfg.png" /><figcaption>Merge join mechanism</figcaption></figure><p>Example: A perfect match for this would be every customer table. “customer_id” is a good distkey (high cardinality, data split more or less equally between customers, very often used for join). In this case, you put “customer_id” as a distkey and sortkey of every table containing it.</p><p>If you want to fully understand and get recipe for distkeys and sortkeys, I strongly recommend you <a href="https://aws.amazon.com/fr/blogs/big-data/amazon-redshift-engineerings-advanced-table-design-playbook-distribution-styles-and-distribution-keys/">this AWS article</a>.</p><h4>Reminders</h4><ul><li>NEVER encode your sortkeys.</li><li>Run regular Vacuum and Analyze (after ETL for small tables, end of day for big tables). Be careful, it’s a long and heavy process. You need this because sortkeys are not respected by some queries (“INSERT INTO” for example), and tables &lt;80% sorted can’t use sortkeys optimizations.</li></ul><p>I hope you liked the article. More than just a troubleshooting review, these tips are my best practices. I regularly use these tricks to reduce queries time by 90–95% … Really! Feel free to ask any advice <a href="https://www.linkedin.com/in/arnaudmilleker/">on Linkedin</a>.</p><p>If you want more technical news, follow our journey through our docto-tech-life <a href="https://doctolib.us14.list-manage.com/subscribe?u=6f3cf1e1e4554097575191f5d&amp;id=a22645bb6c">newsletter</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u-AWnXWO9sMpPZVAWi78Hg.png" /><figcaption>Unleash your Redshift !</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ed73b38d10d5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/doctolib/redshift-troubleshouting-made-simple-ed73b38d10d5">Redshift : Troubleshouting made simple</a> was originally published in <a href="https://medium.com/doctolib">Doctolib</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>