<?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 Srinidhi Chundru on Medium]]></title>
        <description><![CDATA[Stories by Srinidhi Chundru on Medium]]></description>
        <link>https://medium.com/@srinidhichundru?source=rss-6f329d11cfec------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*3t-GX1gNESae56XS</url>
            <title>Stories by Srinidhi Chundru on Medium</title>
            <link>https://medium.com/@srinidhichundru?source=rss-6f329d11cfec------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 16:58:22 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@srinidhichundru/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[Databricks: Real-World Implementation Scenarios — Part 1 | How to Choose Liquid Clustering Keys]]></title>
            <link>https://medium.com/@srinidhichundru/databricks-real-world-implementation-scenarios-part-1-how-to-choose-liquid-clustering-keys-40620e43eb47?source=rss-6f329d11cfec------2</link>
            <guid isPermaLink="false">https://medium.com/p/40620e43eb47</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Srinidhi Chundru]]></dc:creator>
            <pubDate>Tue, 25 Feb 2025 05:06:55 GMT</pubDate>
            <atom:updated>2025-02-25T05:06:55.061Z</atom:updated>
            <content:encoded><![CDATA[<h3>Databricks: Real-World Implementation Scenarios — Part 1 | How to Choose Liquid Clustering Keys</h3><p>In these real world implementation scenarios series, I will discuss a few real-world scenarios I encountered in my work.</p><p>This article deals with How to choose liquid clustering Keys.</p><p>If you’re not familiar with Liquid Clustering, I recommend reading Deep Dive <strong>Part 1</strong> and <strong>Part 2</strong> before proceeding.</p><h3>Scenarios Covered: <strong>Selecting Liquid Clustering Keys</strong></h3><h4>Scenario 1: Selecting Liquid Clustering Keys for a table.</h4><p><strong>Background:</strong> Liquid Clustering is an optimization technique designed for tables, functioning as an incremental clustering method. Similar to indexing, it enhances query performance by efficiently skipping irrelevant data, reducing the need to scan the entire table.</p><p>In this scenario, we need to determine the appropriate clustering keys for a given table. What criteria should be considered when selecting clustering keys?</p><p>Although a table can have a maximum of four Liquid Clustering columns, blindly selecting four columns is not always the best approach. If the table size is less than <strong>10TB</strong>, including columns that are not used in filtering queries can negatively impact performance.</p><p>When selecting clustering keys, consider:</p><ul><li><strong>Columns used in filteringclauses</strong></li><li><strong>Joining keys</strong></li><li><strong>Merge keys</strong></li></ul><p>For example, suppose we have a fact table, <strong>F_transaction</strong>, and dimensional tables <strong>D_finance</strong>, <strong>D_transactionCategory</strong>, and <strong>D_account</strong>, all of which are linked to the transaction table. The choice of clustering keys should align with the queries frequently executed on these tables.</p><p>Sample data:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RKX9yD53-Y26IQ9QY12_fg.png" /><figcaption>Sample data of F_transaction</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1015/1*Zhq-PiAKjXdyQVpGhXduUw.png" /><figcaption>Sample data of Dimension tables</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0eXXKzbIKUcF3RKN2oMvKw.png" /></figure><h3>Sample Ad-hoc Queries Used:</h3><h4>Query 1</h4><pre>SELECT t.transaction_id, t.transaction_date, t.amount, c.category_name, a.account_name  <br>FROM F_transaction t  <br>JOIN D_transactionCategory c ON t.category_id = c.category_id  <br>JOIN D_account a ON t.account_id = a.account_id  <br>WHERE t.transaction_date &gt;= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH);</pre><p>This query retrieves transactions from the last three months. The filtering condition is based on transaction_date from the <strong>fact table</strong>.</p><h4>Query 2</h4><pre>SELECT t.transaction_id, t.transaction_date, t.amount, c.category_name, f.finance_type  <br>FROM F_transaction t  <br>JOIN D_transactionCategory c ON t.category_id = c.category_id  <br>JOIN D_finance f ON t.finance_id = f.finance_id  <br>WHERE t.transaction_date &gt;= &#39;2024-01-01&#39;  <br>AND t.category_id = &#39;C1&#39;  <br>AND t.amount &gt; 1000;</pre><p>This query analyzes transactions that belong to a specific <strong>category (C1)</strong> and have occurred on or after <strong>January 1, 2024</strong>, with an <strong>amount greater than 1000</strong>.</p><h4>Columns Considered for Liquid Clustering for F_transaction:</h4><p>Based on the queries, the relevant columns from the <strong>F_transaction</strong> table are:(used in filtering conditions)</p><ul><li><strong>transaction_date</strong></li><li><strong>category_id</strong></li><li><strong>finance_id</strong></li><li><strong>account_id</strong></li><li><strong>amount</strong></li></ul><p>Since the table size exceeds <strong>10TB</strong>, we can utilize the maximum of <strong>four</strong> Liquid Clustering columns. However, we have <strong>five</strong> candidate columns, so we need to prioritize the most effective ones.</p><h4>Priority Strategy for Clustering Keys:</h4><ol><li><strong>transaction_date</strong> → Highest priority as it is frequently used in filtering and has high cardinality.</li><li><strong>amount</strong> → Frequently used in reporting queries, especially with conditions like amount &gt; 100.</li><li><strong>category_id</strong> → Frequently used and has a cardinality of <strong>50</strong>.</li><li><strong>finance_id or account_id?</strong> →</li></ol><ul><li>Selection depends on table sizes, column cardinality, and query frequency.</li><li>The column used more frequently should be given priority.</li></ul><h4>Columns Considered for Liquid Clustering for <strong>D_finance</strong>:</h4><p>For this dimensional table, since only finance_id is used as the joining key, we can proceed with it. Additionally, if finance_type is also used, priority should be given to the column with higher cardinality, i.e, finance_id.</p><p>NOTE: The <strong>fact table (F_transaction)</strong> is the largest table and requires priority when selecting clustering keys. For example For example, if you are implementing Liquid Clustering on table <strong>X</strong>, and <strong>X</strong> joins with <strong>F_transaction</strong> as well as other smaller <strong>dimension tables</strong>, then <strong>more priority given to the join key columns that involve F_transaction</strong>. Prioritizing these join keys ensures better query performance by optimizing how data is clustered and accessed, especially when dealing with large datasets.</p><h3>Key Selection Strategy Considerations:</h3><ul><li>This strategy is a <strong>general guideline</strong>; actual priorities depend on <strong>organizational needs</strong>.</li><li>If the goal is to <strong>reduce reporting query latency</strong>, prioritize columns used in reporting.</li><li>If the focus is on <strong>optimizing joins across layers</strong>, consider <strong>merge keys/join keys</strong> as well.</li></ul><p>Cluster by AUTO:</p><p>Liquid clustering can be implemented automatically, eliminating the need to manually select keys, as the process is handled internally. The only requirement is to enable <strong>predictive optimization</strong>. When activated, Databricks analyzes the <strong>historical query workload</strong> for the table to identify the best candidate columns for clustering. The system then automatically selects clustering keys based on metadata and query usage while also running the <strong>OPTIMIZE</strong> command for further optimization.</p><p>To enable automatic clustering:</p><pre>ALTER TABLE table_name CLUSTER BY AUTO;<br>CREATE OR REPLACE TABLE table_name CLUSTER BY AUTO;</pre><p>Follow for more updates!</p><p>References:</p><p><a href="https://docs.databricks.com/aws/en/delta/clustering">https://docs.databricks.com/aws/en/delta/clustering</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=40620e43eb47" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deep Dive into Liquid Clustering, Z-ordering, and Partitioning — Part 2]]></title>
            <link>https://medium.com/@srinidhichundru/deep-dive-into-liquid-clustering-z-ordering-and-partitioning-part-2-4c11eb18ba8f?source=rss-6f329d11cfec------2</link>
            <guid isPermaLink="false">https://medium.com/p/4c11eb18ba8f</guid>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[liquid-clustering]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Srinidhi Chundru]]></dc:creator>
            <pubDate>Tue, 28 Jan 2025 20:32:41 GMT</pubDate>
            <atom:updated>2025-01-28T20:32:41.722Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Deep Dive into Liquid Clustering, Z-ordering, and Partitioning — Part 2</strong></h3><p>In this article, we’ll take an in-depth look at the inner workings of liquid clustering, along with other optimization techniques.</p><p>If you’re new to Databricks optimization techniques such as liquid clustering, partitioning, and Z-ordering, I recommend checking out part one of this series for more details.</p><p><a href="https://medium.com/@rama_venkata_srinidhi.chundru/deep-dive-into-liquid-clustering-zordering-partioning-part-1-be2cdfbaf0dc">Deep Dive into Liquid Clustering , zordering, partioning — Part 1</a></p><p><strong>How is Liquid Clustering Different from Z-order Clustering? What problems can be solved using liquid clustering? What Makes Liquid Clustering Solve Major Problems?</strong></p><p>Both <strong>Liquid Clustering</strong> and <strong>Z-ordering</strong> operate on <strong>Z-cubes</strong>. But, what exactly are Z-cubes and how are they formed?</p><ul><li><strong>Z-cubes</strong> are groups of files created by a single OPTIMIZE job. As discussed in Part 1, the OPTIMIZE command works in a batch-wise manner, which is why both clustering techniques are used in conjunction with the OPTIMIZE command. The OPTIMIZE job creates Z-cubes, which are essential for both clustering methods.</li></ul><p>So, if both techniques work on Z-cubes, what makes Liquid Clustering more effective?</p><p>To answer this, we need to understand how <strong>Liquid Clustering</strong> and <strong>Z-ordering</strong> work internally.</p><h4>How Z-ordering Works:</h4><p>Z-ordering is always used with the OPTIMIZE command. When applied to non-partitioned data:</p><ul><li>The OPTIMIZE job creates one large Z-cube, and data is clustered and sorted within that single Z-cube.</li></ul><p>When applied to partitioned data:</p><ul><li>The OPTIMIZE job processes each partition independently, creating one Z-cube per partition. Within each partition&#39;s Z-cube, Z-ordering is applied.</li></ul><h4>How Sorting Is Done in Z-cubes with Z-ordering:</h4><p>Z-ordering is a <strong>multi-dimensional clustering</strong> technique that co-locates similar data and sorts it using Z-order curves. Each column in each file within a Z-cube has statistics like minimum, maximum, and others collected by default. Data skipping is performed based on these column statistics.</p><p><strong>Z-order: A</strong>ll column x values are laid out on the horizontal dimension and column y values are on the vertical dimension. The same concept can be extended to more than two columns for data layout where each column’s values are on a separate dimension. The data laid out in this method is called Z-cube.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1012/1*-NOrVeenMR7NV90nQRFvUA.png" /><figcaption><em>Fig 2: Regular sorting of data by primary and secondary columns (left) and 2-dimensional Z-order clustering of data for two columns (right).</em></figcaption></figure><p>Example: Consider the below query. For regular linear sorting, the data is organized row by row. To locate points within x: 2 or y: 3, the system has to scan <strong>entire rows where x:2 is present</strong>.</p><p>For the query: SELECT * FROM points WHERE<strong> x = 2</strong> OR<strong> y = 3</strong></p><p>Incase of the Z-order clustering: the intuition is for each column to look at the column dimension in the Z-cube and narrow the search to a particular range on the column dimension. In this case the predicate x=2, we look at the files that fall in the horizontal dimension where the x value is `2`. Similarly for column y, we look at the files that fall in the vertical dimension where the y value is `3`. Using this method we end up reading only 7 files and an extra 13 records.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*Ahuth6fOSG4zxBBVw1ao1g.png" /><figcaption>Fig 3: <em>Comparison of data skipping in sorted data by primary and secondary columns (left) and 2-dimensional Z-order clustering of data for two columns (right).</em></figcaption></figure><p>However, Z-ordering can become less effective in some cases when the <strong>min-max range</strong> is nearly equal to the entire dataset range(Like showed in fig-1). This leads to all files being scanned, which increases read time. The issue arises because Z-order curves can have large distances between certain data points, making data skipping less efficient.</p><p><strong>Limitation:</strong> A major drawback of Z-order clustering is that it <strong>rewrites the entire Z-cube</strong> every time the command is run, regardless of whether new data has been added or not. This makes Z-ordering costly, especially when working with large datasets.</p><h4>How Liquid Clustering Works:</h4><p>In Liquid Clustering, Z-cubes are created with a target size limit. Every time the OPTIMIZE command runs in batches, the optimized files created during each batch are stored in a Z-cube. When a Z-cube size is big enough, the z-cube is sealed and the next clustering won’t re-cluster those files and so less write amplification.</p><p>Each clustered file in Zcube is tagged with ZCUBE_ID to track which ZCUBE it belongs to and the id is generated using UUID. Also another tag<br>ZCUBE_ZORDER_BY is used to track the clustering columns. Each clustered<br>file has the clustering Provider populated with liquid. This metadata is essential for <strong>incremental clustering</strong>.</p><h4>1) Incremental Clustering using z-cube:</h4><p>One of the key advantages of Liquid Clustering is its <strong>incremental clustering</strong>.</p><ul><li>Only fresh data will be optimized and clustered with unstable z-cubes, reducing write amplification as not all z-cubes are rewritten.</li><li>If clustering columns are altered in the future, only newly added data will be clustered with the new keys, leaving the previously clustered data untouched, significantly reducing the cost of re-clustering the entire table.</li></ul><p>Every time an OPTIMIZE job runs, it differentiates between <strong>stable</strong> and <strong>unstable Z-cubes</strong>. Files in <strong>unstable Z-cubes</strong> (i.e., those that haven&#39;t yet reached their target size) are optimized and clustered with new data. Stable Z-cubes, which are already optimized and meet the size criteria, are not re-clustered unless there are <strong>DML operations</strong> (like inserts or updates) that make them unstable.</p><p><strong>Stable vs. Unstable Z-cubes:</strong></p><ul><li><strong>Stable Z-cubes</strong>: Once the total size of files in a Z-cube exceeds the target size, it is considered stable and won’t be optimized further unless DML operations change it.</li><li><strong>Unstable Z-cubes</strong>: These are Z-cubes that haven’t reached the minimum target size and are considered for clustering with fresh files.</li></ul><p>This process of incremental clustering helps in <strong>reducing costs</strong> and improving performance over time.</p><figure><img alt="Fig: Stable Zcube dropping to Unstable Zcube due to DML operations" src="https://cdn-images-1.medium.com/max/721/1*dIk7LKwZ8hBhzgvoAjkTkg.png" /><figcaption>Fig: Stable Zcube dropping to unstable Zcube after DML operations</figcaption></figure><h4>2) Hilbert Curves in Liquid Clustering:</h4><p>Liquid Clustering uses <strong>Hilbert curves</strong> for sorting and clustering, unlike the Z-order curves used in Z-ordering. Hilbert curves maintain a more consistent distance between data points (i.e., each file in a Z-cube), which results in <strong>more effective data skipping</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/613/1*6-Uagbkr2EhrmDFSC_awzA.png" /><figcaption>Fig 4: Hilbert Curve: Adjacent Points Always Maintain a Distance of 1 for Optimal Data Skipping</figcaption></figure><p>For Z-curve, the bounding box shown in below fig covers the entire space! For the Hilbert curve, a range of this length could only cover half the space (as shown in Fig 4), because it could cover only two adjacent quadrants. This means for Z-Order, potentially some files will have min/max range equal to the full range, and data skipping can’t skip these files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*PIL2mLEZpqa1wJYatSWD7w.png" /><figcaption>fig: The <em>orange line spans the entire space for z-curve and he orange line spans only half the space for hilbert curve</em></figcaption></figure><h4>Why Z-ordering and Liquid Clustering Can’t Be Used Together:</h4><p>Z-ordering and Liquid Clustering cannot be used concurrently on the same table because their internal mechanisms differ. Z-ordering uses Z-order curves, while Liquid Clustering uses Hilbert curves for clustering and sorting, making them incompatible for concurrent use.</p><h4>Partitioning and Liquid Clustering:</h4><p>Partitioning and Liquid Clustering also cannot be used concurrently. Partitioning creates fixed physical boundaries for data, while Liquid Clustering has a dynamic data layout that adjusts according to incoming data. This fundamental difference prevents them from being applied simultaneously.</p><h4>How OPTIMIZE Works with Z-ordering, Liquid Clustering, and Without Clustering:</h4><p>Optimize command works differently based on in which mode the Optimize command is running. There are three valid modes: 1. Compaction 2. Z-Order 3. Clustering</p><ul><li><strong>Compaction</strong>: When no clustering technique is used, OPTIMIZE performs data compaction to merge smaller files into larger ones. By default it combines files to 1GB file, it can be configured as well.</li><li><strong>With Z-ordering</strong>: When Z-ordering is applied, OPTIMIZE creates a separate Z-cube for each partition and sorts the data within those Z-cubes.</li><li><strong>With Liquid Clustering</strong>: When Liquid Clustering is used, OPTIMIZE creates Z-cubes based on the configured target file size, and the clustering is applied incrementally.</li><li>The <strong>VACUUM</strong> command should be run each time the OPTIMIZE command is executed because it clears out the obsolete files left behind after data compaction.</li></ul><p>Finally, What problems are solved by liquid clustering?</p><ol><li>skewness: Everytime skewness detected, it automatically corrects it by adjusting the data layout dynamically.</li><li>concurrent writing: With z-order, write operation and applying zorder on a table can’t be done concurrently, while liquid clustering solves this problem by its dynamic adjusting nature.</li><li>Cost: By not having to re-write entire table every time the command runs, lots of cost can be saved.</li><li>Any cardinality can be used unlike zordering.</li></ol><p>So, Liquid clustering also have limitations. To know about it, please refer <a href="https://medium.com/p/be2cdfbaf0dc/edit">Deep Dive into Liquid Clustering, Z-ordering, and Partitioning — Part 1</a></p><p>This concludes Part 2 of the deep dive into <strong>Liquid Clustering, Z-ordering, and Partitioning</strong>.</p><p><strong>What’s Next?</strong><br>Stay tuned for Deep Dive into Liquid Clustering, Z-ordering, and Partitioning — Part 3, where we explore the practical implementations of Liquid Clustering, Zorder clustering and partitioning!</p><p><strong>Kindly let me know if you have any suggestions or specific topics you’d like me to write articles on.</strong></p><p>References:</p><p><a href="https://learn.microsoft.com/en-us/azure/databricks/delta/clustering">https://learn.microsoft.com/en-us/azure/databricks/delta/clustering</a></p><p><a href="https://docs.google.com/document/u/0/d/e/2PACX-1vREkVPDxqlKrwnaQ7Et1EnaiCF-VhFXCwit7bGSomWKtGEfkxbuGhX4GP3cJ20LgllYfjzsjr2lyY5y/pub?pli=1#kix.301alpimymwh">https://docs.google.com/document/u/0/d/e/2PACX-1vREkVPDxqlKrwnaQ7Et1EnaiCF-VhFXCwit7bGSomWKtGEfkxbuGhX4GP3cJ20LgllYfjzsjr2lyY5y/pub?pli=1#kix.301alpimymwh</a> .</p><p><a href="https://en.wikipedia.org/wiki/Z-order_curve">https://en.wikipedia.org/wiki/Z-order_curve</a></p><p><a href="https://en.wikipedia.org/wiki/Hilbert_curve">https://en.wikipedia.org/wiki/Hilbert_curve</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4c11eb18ba8f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deep Dive into Liquid Clustering , zordering, partioning — Part 1]]></title>
            <link>https://medium.com/@srinidhichundru/deep-dive-into-liquid-clustering-zordering-partioning-part-1-be2cdfbaf0dc?source=rss-6f329d11cfec------2</link>
            <guid isPermaLink="false">https://medium.com/p/be2cdfbaf0dc</guid>
            <category><![CDATA[performance-optimization]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[delta]]></category>
            <category><![CDATA[liquid-clustering]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Srinidhi Chundru]]></dc:creator>
            <pubDate>Tue, 07 Jan 2025 04:57:02 GMT</pubDate>
            <atom:updated>2025-01-07T04:59:59.896Z</atom:updated>
            <content:encoded><![CDATA[<h3>Deep Dive into Liquid Clustering , zordering, partioning — Part 1</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I85QpXw9fXDyQHGM4ZyfpA.png" /></figure><p><strong>What is Liquid Clustering? </strong>How does it work? What sets it apart from other optimization techniques like Z-order, partitioning? But why do we need optimizations? In what cases we need optimizations? This three-part series delves into these topics in depth.</p><ul><li><strong>Part 1:</strong> A brief overview of Liquid Clustering, Partitioning, Z-ordering, their mechanisms, and key differences.</li><li><strong>Part 2:</strong> A detailed explanation of Liquid Clustering’s and Zorder’s internal workings.</li><li><strong>Part 3:</strong> Implementation strategies for Liquid Clustering, Z-ordering, and Partitioning across various scenarios.</li></ul><h3>Understanding the Need for Optimization</h3><p>In today’s world, businesses generate massive amounts of data daily. This data helps them make better decisions, but managing it efficiently is a challenge. Typically, data is stored in <strong>layers</strong> that organize it based on its state:</p><ol><li><strong>Bronze Layer</strong>: Raw, unprocessed data collected from various sources.</li><li><strong>Silver Layer</strong>: Cleaned and filtered data.</li><li><strong>Gold Layer</strong>: Final, analytics-ready data used for reports or insights.</li></ol><p>As the data grows, even simple queries — like filtering rows using a <strong>WHERE</strong> clause — can take a lot of time because they may need to scan billions of rows. This is where <strong>data optimization</strong> techniques come in, speeding up data access and improving efficiency.</p><p>Databricks inherently provides robust optimization mechanisms to handle the complexities of large-scale data processing. At its core, <strong>Adaptive Query Execution (AQE)</strong> plays a significant role. AQE dynamically adjusts query execution plans at runtime based on the actual data characteristics, improving performance by addressing skewed data distribution, optimizing join strategies, and managing partition sizes more effectively.</p><p>Complementing AQE, Databricks also offers advanced optimization features such as <strong>Partitioning</strong>, <strong>Liquid Clustering</strong>, <strong>Z-order</strong> clustering, and several others, all designed to improve query performance and data management.</p><h3><strong>Optimize</strong>:</h3><p>First, let’s briefly understand the <strong>OPTIMIZE</strong> command, which is crucial for both <strong>Liquid Clustering</strong> and <strong>Z-ordering</strong>. The <strong>OPTIMIZE</strong> command compacts data, making it more organized and accessible. Its behavior depends on the optimization technique used, such as <strong>Z-ordering</strong> or <strong>Liquid Clustering</strong>.</p><p>For Z-ordering and Liquid Clustering, this command is mandatory to run along because it creates <strong>Z-cubes</strong></p><p>For a detailed explanation of the how optimize command behaviour varies , stay tune for part-2</p><h3>Partitioning</h3><p><strong>Partitioning</strong> in Hive-style organizes data into <strong>physical partitions</strong> (similar to folders) for faster access. The number of partitions is determined by the <strong>cardinality</strong> (which refers to the number of distinct values in a specific column).</p><p>In simpler terms, <strong>Partitioning</strong> is like organizing a library by genre. Imagine dividing the library into sections, each based on a different genre, such as <strong>Fiction</strong>, <strong>Non-Fiction</strong>, <strong>Science</strong>, and <strong>History</strong>.</p><ul><li>When you want to find a <strong>Science</strong> book, instead of searching the entire library, you can go directly to the <strong>Science</strong> section, making it much faster to find what you’re looking for.</li><li>Similarly, in a database, partitioning helps the system quickly find the data you’re interested in by separating it into smaller, more manageable pieces (like sections in the library).</li></ul><p><strong>Syntax:</strong></p><pre>CREATE TABLE table_name (col1 INT, col2 STRING)  <br>PARTITIONED BY (col1)</pre><p><strong>Pros:</strong></p><ul><li>Faster read operations can be performed.</li><li>We can choose as many partition keys as we want for a table.</li></ul><p><strong>Cons:</strong></p><ul><li>More partitions lead to smaller files, potentially increasing latency due to the overhead of opening and scanning numerous files.</li><li>Partition strategies are fixed and cannot be changed without recreating the table.</li><li>Figuring out the right partition strategy for optimal performance is difficult. Having too large files or too small files in each partitions causes poor query performance or slower reads.</li></ul><h3>Z-Ordering</h3><p><strong>Z-ordering</strong> is a multi-dimensional clustering technique leveraging Z-order curves to co-locate data for better skipping. It operates on <strong>Z-cubes</strong> (refer to the earlier section for more details on Z-cubes)</p><p>In simpler terms, Z-Ordering is like organizing a library by multiple criteria at once — <strong>genre</strong>, <strong>author</strong>, and <strong>year</strong> — rather than just <strong>genre</strong>. For example, instead of a general Fiction section, books are grouped into smaller sections like <strong>Science Fiction</strong> by a specific <strong>author</strong> from a certain <strong>year</strong>, making it much faster to find the exact book you need.</p><p><strong>Syntax:</strong></p><pre>OPTIMIZE table_name ZORDER BY (col1)</pre><ul><li>High-cardinality columns are ideal for Z-ordering. with many distinct values provide more granular data distribution, allowing the optimizer to skip over large chunks of irrelevant data when querying.</li></ul><p><strong>Pros:</strong></p><ul><li>Better data skipping due to its internal co-locating of data mechanism(which will be discussed more in detail in part-2)</li></ul><p><strong>Cons:</strong></p><ul><li>It rewrites all files each time it’s applied, making it costly for large tables.</li><li>Users must maintain records of Z-order columns, as they aren’t stored in metadata.</li><li>Z-ordering requires the selected columns to be among the first 32 columns, as column statistics are calculated only for these by default. But what if a column outside the first 32 needs to be used? In such cases, it can be configured, which will be covered in the upcoming part-2,part-3.</li><li>You can specify multiple columns for ZORDER BY as a comma-separated list. However, the effectiveness of the locality drops with each extra column.</li></ul><h3>Liquid Clustering</h3><p><strong>Liquid Clustering</strong> introduces <strong>incremental clustering</strong>, addressing the limitations of Z-ordering and Partitioning. Liquid Clustering also operates on Z-cubes.</p><p>In simpler terms, Liquid Clustering is like a librarian gradually re-sorting books in the library over time instead of re-organizing the entire library at once. As new books are added, the librarian adjusts the sections little by little to keep everything well-organized without the need for a big overhaul.</p><p><strong>Why choose Liquid Clustering?</strong></p><ul><li><strong>Incremental Clustering</strong>: Liquid Clustering optimizes data dynamically and incrementally, avoiding the high cost of repeatedly rewriting an entire table as datasets grow. It’s like gradually adding new books to the library without having to re-sort every section every time.</li><li><strong>Hilbert Curves</strong>: Liquid Clustering uses Hilbert curves for sorting data within Z-cubes, providing more effective data skipping compared to Z-order curves used in Z-ordering. It’s like sorting books by multiple factors (author, genre, year) in a way that groups related books together more efficiently than Z-ordering, helping the librarian find and place books faster.</li></ul><p><strong>syntax</strong>:</p><pre><br>-- Create an empty table<br>CREATE TABLE table1(col0 int, col1 string) CLUSTER BY (col0);<br><br> -- Using a CTAS statement<br>CREATE EXTERNAL TABLE table2 CLUSTER BY (col0) <br>LOCATION &#39;table_location&#39;<br>AS SELECT * FROM table1;<br><br>--Alter the exsiting table<br>ALTER TABLE &lt;table&gt;<br>CLUSTER BY (&lt;col1&gt;, &lt;col2&gt;, …)<br><br>--Removes the Liquid clustering columns<br>ALTER TABLE &lt;table&gt;<br>CLUSTER BY NONE<br><br>--Triggers Liquid clustering<br>OPTIMIZE &lt;table&gt;</pre><p><strong>Pros:</strong></p><ul><li>Supports up to 4 columns, regardless of cardinality.</li><li>Clustering columns can be altered in the future without recreating table, unlike Partitioning.</li><li>Dynamically adjusts data layout, improving both read and write operations.</li></ul><p><strong>Cons:</strong></p><ul><li>Only four columns can be used in liquid clustering.</li><li>Similar to Z-ordering, the columns used in Liquid Clustering should be among the first 32 columns, as column statistics are by default stored only for the first 32 columns.</li></ul><p><strong>What’s Next?</strong><br>Stay Tuned for Deep Dive into Liquid Clustering, Z-ordering, and Partitioning — Part 2, where we explore the detailed workings of Liquid Clustering, Zorder clustering and its practical applications!</p><p>Follow for more updates!</p><p>References:</p><ul><li><a href="https://learn.microsoft.com/en-us/azure/databricks/delta/clustering">Use liquid clustering for Delta tables - Azure Databricks</a></li><li><a href="https://en.wikipedia.org/wiki/Z-order_curve">Z-order curve - Wikipedia</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=be2cdfbaf0dc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimize Command: Key to Data Organization and Optimization]]></title>
            <link>https://medium.com/@srinidhichundru/optimize-command-key-to-data-organization-and-optimization-8ec64ebc7a93?source=rss-6f329d11cfec------2</link>
            <guid isPermaLink="false">https://medium.com/p/8ec64ebc7a93</guid>
            <category><![CDATA[optimizing-delta-lake]]></category>
            <category><![CDATA[optimize]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[databricks-delta-lake]]></category>
            <category><![CDATA[optimization-techniques]]></category>
            <dc:creator><![CDATA[Srinidhi Chundru]]></dc:creator>
            <pubDate>Thu, 12 Dec 2024 08:18:15 GMT</pubDate>
            <atom:updated>2024-12-12T16:14:39.317Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/879/1*iMXl_yP9VXSuXRoxKT7OkQ.png" /><figcaption>Fig 1:Organizing files efficiently for better access and organization.</figcaption></figure><p>The <strong>OPTIMIZE</strong> command plays a critical role in data optimization within Delta Lake. Its primary function is to compact smaller files into larger ones. Thus, it helps optimize the layout of Delta Lake tables, improving overall performance by reducing the number of calls to the underlying storage and results in faster table reads. You can also use it to optimize specific subsets of data or collocate data based on columns.</p><p><strong>Syntax:</strong></p><pre>OPTIMIZE table_name [FULL] [WHERE predicate] <br>[ZORDER BY (col_name1 [, …] ) ]</pre><p>This command is mandatory for techniques like <strong>Z-ordering</strong> and <strong>Liquid Clustering</strong>. But why is it so essential? Let’s dive deeper.</p><h3>When the OPTIMIZE Command is Used Alone</h3><p>When the <strong>OPTIMIZE</strong> command is run by itself, it performs <strong>bin-packing optimization</strong>. This process compacts smaller files into larger ones to improve data layout. Running <strong>OPTIMIZE</strong> a second time on the same data won’t have an effect because the files are already compacted from the first run.</p><p>It combine files by looping through each input file and writing its record batches into the new output file. By default, <strong>OPTIMIZE</strong> compacts files to a size of 1 GB, but this can be adjusted by setting the configuration parameter:</p><pre>spark.databricks.delta.optimize.maxFileSize</pre><h3>When Used with Partition:</h3><p>The <strong>OPTIMIZE</strong> command compacts files based on partitions. It groups files that share common partition membership and are smaller than the optimal size (usually 1 GB). This helps in improving query performance by reducing the number of small files, making the data more organized and easier to read.</p><h3>When Used with Z-ordering or Liquid Clustering</h3><p>When used with <strong>Z-ordering</strong> or <strong>Liquid Clustering</strong>, the <strong>OPTIMIZE</strong> command also triggers the creation of <strong>Z-cubes</strong>, which help further organize data for efficient access. It <strong>creates Z-cubes</strong>, which are logical groupings of files that make data access much more efficient. These Z-cubes allow for better data skipping and faster queries.</p><ul><li><strong>Z-ordering</strong>: This technique requires <strong>OPTIMIZE</strong> to create <strong>Z-cubes</strong> and perform <strong>Z-curve sorting</strong>, a special sorting method that enhances data skipping. Without <strong>OPTIMIZE</strong>, Z-ordering cannot work, as it depends on <strong>Z-cubes</strong> to store the sorted data. z-ordering is like organizing clothes in your closet based on type, color, and season to make everything easier to find.</li><li><strong>Liquid Clustering</strong>: Similar to Z-ordering, <strong>Liquid Clustering</strong> also relies on <strong>Z-cubes</strong> to sort data. However, instead of sorting all at once, <strong>Liquid Clustering</strong> sorts data incrementally as new data arrives, allowing for more efficient ongoing optimizations.</li></ul><p>With <strong>Z-ordering</strong>, the <strong>OPTIMIZE</strong> command must be run alongside it to organize data efficiently. However, for <strong>Liquid Clustering</strong>, the <strong>OPTIMIZE</strong> command is run separately to trigger the clustering technique.</p><h3>Vacuum: Cleaning Up After OPTIMIZE</h3><p>After running the <strong>OPTIMIZE</strong> command, it’s essential to run the <strong>VACUUM</strong> command to remove any small files that remain after compaction. While <strong>OPTIMIZE</strong> combines smaller files into larger ones, it doesn’t delete the smaller ones. <strong>VACUUM</strong> is needed to clean up the old, unnecessary files that are no longer managed by Delta.</p><p><strong>VACUUM</strong> is not triggered automatically after <strong>OPTIMIZE</strong>. It must be scheduled separately, and it removes files based on a retention period (the default is <strong>7 days</strong>), which can be adjusted to your needs.</p><p>Vacuum command just like optimize command, its <strong>idempotent</strong>, this means that running the VACUUM command multiple times within the same retention period will produce the same result, as it only deletes files that are no longer needed.</p><p><strong>Syntax:</strong></p><pre>VACUUM table_name [RETAIN num HOURS] [DRY RUN]</pre><p>You can configure the retention window with the following command:</p><pre>ALTER TABLE table_name SET TBLPROPERTIES (&#39;delta.deletedFileRetentionDuration&#39; = &#39;30 days&#39;);</pre><p>Additionally, <strong>OPTIMIZE</strong> is typically run alongside the <strong>VACUUM</strong> command.</p><p><strong>Note:</strong> The <strong>OPTIMIZE</strong> and <strong>VACUUM</strong> commands are often scheduled during off-peak hours or when there are no ongoing DML (Data Manipulation Language) operations like INSERT, UPDATE, or DELETE. This is to ensure that these maintenance operations do not interfere with active data operations and that system resources are optimally utilized.</p><p>By understanding the <strong>OPTIMIZE</strong> command and its role in creating <strong>Z-cubes</strong>, you can leverage techniques like <strong>Z-ordering</strong> and <strong>Liquid Clustering</strong> to maximize data efficiency and performance in your Delta Lake environment.</p><p><strong>What’s Next?</strong><br>Next week, stay tuned for a <strong>Deep Dive into Liquid Clustering, Z-ordering, and Partitioning series</strong>, which is more in detail about these optimization techniques!</p><p>Follow for more updates!</p><p>References:</p><ul><li><a href="https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/delta-vacuum">VACUUM - Azure Databricks - Databricks SQL</a></li><li><a href="https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/delta-optimize">OPTIMIZE - Azure Databricks - Databricks SQL</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8ec64ebc7a93" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>