<?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 Jagjit Natt on Medium]]></title>
        <description><![CDATA[Stories by Jagjit Natt on Medium]]></description>
        <link>https://medium.com/@jagjitnatt?source=rss-85cd97bdabd8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*3TVoOelmS1dMZPUX.jpg</url>
            <title>Stories by Jagjit Natt on Medium</title>
            <link>https://medium.com/@jagjitnatt?source=rss-85cd97bdabd8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 10:19:09 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jagjitnatt/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[Reversing the Filter: A Counterintuitive Optimization for Complex Predicates]]></title>
            <link>https://medium.com/@jagjitnatt/reversing-the-filter-a-counterintuitive-optimization-for-complex-predicates-a27b2b059274?source=rss-85cd97bdabd8------2</link>
            <guid isPermaLink="false">https://medium.com/p/a27b2b059274</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Jagjit Natt]]></dc:creator>
            <pubDate>Fri, 14 Nov 2025 05:15:31 GMT</pubDate>
            <atom:updated>2025-11-14T05:15:31.288Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1012/1*ByFNS5loK71OoVz7sqlYBA.jpeg" /></figure><p>As a Solutions Architect working with massive Delta Lake deployments, I’ve encountered countless queries that make even experienced data engineers cringe. You know the ones — queries that scan terabytes of data just to find a handful of matching rows, bringing your cluster to its knees and your CFO to your desk asking about cloud costs.</p><p>Today, I want to share a counterintuitive optimization technique that can transform these nightmare queries into efficient, partition-pruning operations. I call it “filter inversion” or “reverse predicate pushdown,” and it’s saved my clients millions in compute costs.</p><h3>The Problem: Functions That Kill Performance</h3><p>Let’s start with a real example I encountered at a telecommunications company. They had a device tracking table with 50 billion rows, and analysts regularly ran queries like this:</p><pre>SELECT *<br>FROM device_inventory<br>WHERE REGEXP_REPLACE(device_id, &#39;[.-]&#39;, &#39;&#39;) = &#39;D401AB2C&#39;</pre><p>The device_id column stored identifiers in various formats: D4.01.AB.2C, D4–01-AB-2C, D401-AB2C, etc. Different systems used different separators, and the analysts wanted to search for devices regardless of format.</p><p><strong>The problem?</strong> This query caused a full table scan. Every single one of the 50 billion rows.</p><p>Why? Because when you apply a function to the left side of a predicate (the column side), the query optimizer can’t use any of these powerful features:</p><ul><li>Data skipping statistics (min/max values)</li><li>Partition pruning</li><li>Liquid clustering benefits</li><li>Bloom filters</li><li>Column statistics</li></ul><p>The optimizer sees REGEXP_REPLACE(device_id, …) and thinks: “I have no idea what values this might produce, so I need to check every row.”</p><h3>The Traditional “Solutions” (That Don’t Really Work)</h3><p>Before we get to the real solution, let’s acknowledge the typical approaches and why they fall short:</p><h3>Approach 1: Computed Columns</h3><pre>ALTER TABLE device_inventory<br>ADD COLUMN device_id_normalized STRING<br>GENERATED ALWAYS AS (REGEXP_REPLACE(device_id, &#39;[.-]&#39;, &#39;&#39;));</pre><p><strong>Problems:</strong></p><ul><li>Increases storage by 20–30% for redundant data</li><li>Doesn’t help with existing tables or data</li><li>Requires schema changes that may not be permissible</li><li>Still needs to be maintained and indexed</li></ul><h3>Approach 2: Pre-normalize at Ingestion</h3><p>“Just clean the data before you load it!”</p><p><strong>Problems:</strong></p><ul><li>Doesn’t help with legacy data</li><li>Loses the original format (which may be required for compliance)</li><li>Requires changes to upstream systems</li><li>Doesn’t solve the general problem for ad-hoc queries</li></ul><h3>Approach 3: Create an Index Table</h3><p><strong>Problems:</strong></p><ul><li>Massive storage overhead</li><li>Complex maintenance</li><li>Synchronization challenges</li><li>Doesn’t scale to multiple filter scenarios</li></ul><p>These “solutions” either don’t work for existing data, require significant infrastructure changes, or simply shift the problem elsewhere.</p><h3>The Real Solution: Invert the Filter</h3><p>Here’s the key insight: <strong>Instead of transforming the left side (column) to match the right side (constant), generate all possible variations of the right side to match the untransformed column.</strong></p><p>For our device ID example, instead of removing dots from the column, we add dots to the search value in all possible positions:</p><pre>-- Instead of this (full table scan):<br>SELECT *<br>FROM device_inventory<br>WHERE REGEXP_REPLACE(device_id, &#39;[.-]&#39;, &#39;&#39;) = &#39;D401AB2C&#39;<br><br>-- Do this (can use statistics and pruning):<br>SELECT *<br>FROM device_inventory<br>WHERE device_id IN (<br>&#39;D401AB2C&#39;, -- no separators<br>&#39;D.401AB2C&#39;, -- one position<br>&#39;D4.01AB2C&#39;,<br>&#39;D40.1AB2C&#39;,<br>&#39;D401.AB2C&#39;,<br>&#39;D401A.B2C&#39;,<br>&#39;D401AB.2C&#39;,<br>&#39;D.4.01AB2C&#39;, -- two positions<br>&#39;D.40.1AB2C&#39;,<br>&#39;D.401.AB2C&#39;,<br> -- … all combinations<br>&#39;D.4.0.1.A.B.2.C&#39; -- all positions<br>)</pre><h3>Why This Works</h3><p>When you use a simple equality or IN predicate on a raw column:</p><ol><li><strong>Delta Lake can skip files</strong> using min/max statistics</li><li><strong>Partition pruning works</strong> if the column is a partition key</li><li><strong>Liquid clustering provides massive benefits</strong> by co-locating similar values</li><li><strong>Bloom filters can be used</strong> (if enabled) for near-instant lookups</li><li><strong>The query optimizer understands the predicate</strong> and can make intelligent decisions</li></ol><p>The query transforms from “scan everything and check a condition” to “look up these specific values” — a fundamentally different operation.</p><h3>Implementation: Generating the Variants</h3><p>Of course, we need to generate all possible variations programmatically. Here’s a Python UDF approach for Spark:</p><pre>python<br>from pyspark.sql.functions import udf<br>from pyspark.sql.types import ArrayType, StringType<br>from itertools import combinations<br><br>@udf(returnType=ArrayType(StringType()))<br>def generate_separator_variants(base_string, separator=&#39;.&#39;):<br>  &quot;&quot;&quot;<br>  Generate all possible variants of a string with separators inserted<br>  at different positions.<br>  For &#39;D401AB2C&#39; with separator &#39;.&#39;, generates:<br>  [&#39;D401AB2C&#39;, &#39;D.401AB2C&#39;, &#39;D4.01AB2C&#39;, …, &#39;D.4.0.1.A.B.2.C&#39;]<br>  &quot;&quot;&quot;<br>  if not base_string:<br>    return [base_string]<br>  variants = {base_string} # Start with no separators<br>  n = len(base_string)<br><br>  # Generate all possible positions for separators (between characters)<br>  positions = list(range(1, n))<br><br>  # For each possible number of separators (1 to n-1)<br>  for num_seps in range(1, n):<br><br>    # Get all combinations of positions<br>    for pos_combo in combinations(positions, num_seps):<br>  <br>      # Build string with separators at these positions<br>      result = []<br>      last_pos = 0<br>      for pos in pos_combo:<br>        result.append(base_string[last_pos:pos])<br>        result.append(separator)<br>        last_pos = pos<br>      result.append(base_string[last_pos:])<br>      variants.add(&#39;&#39;.join(result))<br>  return list(variants)<br><br># Usage<br>search_value = &#39;D401AB2C&#39;<br>variants_df = spark.createDataFrame([(search_value,)], [&#39;search_value&#39;]) \<br>.withColumn(&#39;variants&#39;, generate_separator_variants(&#39;search_value&#39;))<br><br># Get the list of variants<br>variants_list = variants_df.select(&#39;variants&#39;).first()[0]<br><br># Now use in your query<br>result = spark.sql(f&quot;&quot;&quot;<br>SELECT *<br>FROM device_inventory<br>WHERE device_id IN ({&#39;,&#39;.join([f&quot;&#39;{v}&#39;&quot; for v in variants_list])})<br>&quot;&quot;&quot;)</pre><h3>Handling Multiple Separator Types</h3><p>For the case where you have both dots and dashes:</p><pre>@udf(returnType=ArrayType(StringType()))<br>def generate_multi_separator_variants(base_string, separators=[&#39;.&#39;, &#39;-&#39;]):<br>  &quot;&quot;&quot;<br>  Generate variants with multiple separator types.<br>  This can explode quickly, so use with caution.<br>  &quot;&quot;&quot;<br>  variants = {base_string}<br>  n = len(base_string)<br>  positions = list(range(1, n))<br><br>  # Limit to reasonable number of separators to avoid explosion<br>  max_separators = min(4, n - 1)<br>  for num_seps in range(1, max_separators + 1):<br>    for pos_combo in combinations(positions, num_seps):<br>  <br>      # For each position combination, try each separator type<br>      for sep_types in product(separators, repeat=num_seps):<br>        result = []<br>        last_pos = 0<br>          for pos, sep in zip(pos_combo, sep_types):<br>          result.append(base_string[last_pos:pos])<br>          result.append(sep)<br>          last_pos = pos<br>        result.append(base_string[last_pos:])<br>        variant = &#39;&#39;.join(result)<br>        <br>        # Stop if we&#39;re generating too many variants<br>        if len(variants) &gt; 10000:<br>          return list(variants)<br>        variants.add(variant)<br>  return list(variants)</pre><h3>Practical Consideration: Variant Explosion</h3><p>The number of possible variants grows exponentially with string length. For a string of length n, you can have up to 2^(n-1) variants with a single separator type.</p><p><strong>Strategy:</strong> Limit the search based on business rules:</p><ul><li>Most device IDs follow specific patterns (e.g., separators every 2 or 4 characters)</li><li>Generate only “likely” variants based on known formats</li><li>Set a maximum variant count and fall back to full scan if exceeded</li></ul><pre>@udf(returnType=ArrayType(StringType()))<br>def generate_realistic_device_variants(base_string, separator=&#39;.&#39;):<br>  &quot;&quot;&quot;<br>  Generate only realistic device ID variants based on common patterns.<br>  Assumes separators typically appear every 2–4 characters.<br>  &quot;&quot;&quot;<br>  variants = {base_string}<br>  n = len(base_string)<br><br>  # Pattern 1: Every 2 characters (D4.01.AB.2C)<br>  if n &gt;= 2:<br>    variant = separator.join([base_string[i:i+2]<br>    for i in range(0, n, 2)])<br>    variants.add(variant)<br><br>  # Pattern 2: Every 4 characters (D401.AB2C)<br>  if n &gt;= 4:<br>    variant = separator.join([base_string[i:i+4]<br>    for i in range(0, n, 4)])<br>    variants.add(variant)<br><br>  # Pattern 3: First 4, then every 2 (D401.AB.2C)<br>  if n &gt; 4:<br>    parts = [base_string[:4]]<br>    parts.extend([base_string[i:i+2]<br>    for i in range(4, n, 2)])<br>    variants.add(separator.join(parts))<br><br>  # Add no-separator version<br>  variants.add(base_string)<br>  return list(variants)</pre><h3><em>Measuring the Impact</em></h3><p>Let’s look at real performance numbers from that telecommunications case:</p><pre>Original Query (with REGEXP_REPLACE):<br><br>  Query Duration: 847 seconds<br>  Data Scanned: 4.2 TB<br>  Files Read: 8,431<br>  Rows Scanned: 50,234,891,203<br>  Compute Cost: ~$340<br><br>Optimized Query (with IN clause):<br><br>  Query Duration: 3.2 seconds<br>  Data Scanned: 180 MB<br>  Files Read: 12<br>  Rows Scanned: 8,234 rows<br>  Compute Cost: ~$0.12</pre><p>That’s a <strong>265x speedup</strong> and <strong>99.996% reduction in data scanned</strong>. The query went from unusable to nearly instant.</p><h3>Beyond String Manipulation: Other Applications</h3><p>This filter inversion technique applies to many scenarios beyond string manipulation. Let’s explore some real-world examples.</p><h3>Case 1: Mathematical Transformations</h3><p><strong>Problem:</strong> Finding orders where the discounted price equals a target:</p><pre>-- Slow: function on column<br>SELECT *<br>FROM orders<br>WHERE price * (1 - discount_rate) = 99.99</pre><p><strong>Solution:</strong> Invert the math:</p><pre>-- Fast: compute possible original values<br>SELECT *<br>FROM orders<br>WHERE price IN (<br>99.99 / (1–0.10),<br>99.99 / (1–0.15),<br>99.99 / (1–0.20),<br>99.99 / (1–0.25),<br> - … for all known discount rates<br>)</pre><p>If your discount rates are standardized (10%, 15%, 20%, 25%), you can pre-compute the original prices needed to hit your target after each discount.</p><p>Another example is a query below:</p><pre>SELECT * from<br>  item_transaction<br>where<br>  mod(item_transaction_key), 4868178) = 1</pre><p>This can be handled by generating a series of numbers beforehand and using that as filter:</p><pre>DECLARE OR REPLACE max_val BIGINT = 0;<br>DECLARE OR REPLACE min_val BIGINT = 0;<br>DECLARE OR REPLACE start_val BIGINT = 0;<br>DECLARE OR REPLACE mod_val BIGINT = 1;<br>DECLARE OR REPLACE mod_by BIGINT = 4868178;<br>SET VAR max_val = (SELECT CAST(MAX(item_transaction_key) as BIGINT) FROM item_transaction);<br>SET VAR min_val = (SELECT CAST(MIN(item_transaction_key) as BIGINT) FROM item_transaction);<br>SET VAR start_val = (SELECT min_val + mod_by - mod(min_val, mod_by) - mod_val);<br><br>SELECT * from<br>  item_transaction<br>where item_transaction_key IN (SELECT explode(sequence(start_val, 0, mod_by)))</pre><h3>Case 2: Date Manipulations</h3><p><strong>Problem:</strong> Finding events that happened on specific days of the week:</p><pre>- Slow: date function on column<br>SELECT *<br>FROM events<br>WHERE DAYOFWEEK(event_timestamp) = 2 - Tuesday</pre><p><strong>Solution:</strong> Generate all Tuesdays in the relevant range:</p><pre>from datetime import datetime, timedelta<br><br>def generate_tuesdays(start_date, end_date):<br>  &quot;&quot;&quot;Generate all Tuesday dates in range&quot;&quot;&quot;<br>  current = start_date<br><br>  # Fast forward to first Tuesday<br>  days_ahead = (1 - current.weekday()) % 7<br>  current += timedelta(days=days_ahead)<br>  tuesdays = []<br>  while current &lt;= end_date:<br>    tuesdays.append(current.strftime(&#39;%Y-%m-%d&#39;))<br>    current += timedelta(days=7)<br>  return tuesdays<br><br># Generate list of Tuesdays for the year<br>tuesdays = generate_tuesdays(datetime(2024, 1, 1), datetime(2024, 12, 31))<br><br># Use in query<br>spark.sql(f&quot;&quot;&quot;<br>SELECT *<br>FROM events<br>WHERE DATE(event_timestamp) IN ({&#39;,&#39;.join([f&quot;&#39;{d}&#39;&quot; for d in tuesdays])})<br>&quot;&quot;&quot;)</pre><p>This is especially powerful when your table is partitioned by date — now the query can skip entire partitions!</p><h3>Case 3: Case-Insensitive Searches</h3><p><strong>Problem:</strong> Case-insensitive matching:</p><pre>-- Slow: function on column<br>SELECT *<br>FROM users<br>WHERE LOWER(username) = &#39;john.smith&#39;</pre><p><strong>Solution:</strong> Generate all case variations (if the domain is small):</p><pre>def generate_case_variants(text):<br>  &quot;&quot;&quot;<br>  Generate realistic case variations.<br>  For &#39;john.smith&#39;, generates:<br>  john.smith, John.smith, John.Smith, JOHN.SMITH, etc.<br>  &quot;&quot;&quot;<br>  variants = {text.lower()}<br><br>  # Title case variations<br>  parts = text.split(&#39;.&#39;)<br>  variants.add(&#39;.&#39;.join([p.capitalize() for p in parts]))<br>  variants.add(text.upper())<br>  variants.add(text.lower())<br><br>  # First letter uppercase<br>  if text:<br>    variants.add(text[0].upper() + text[1:].lower())<br>  return list(variants)<br><br># For username search<br>variants = generate_case_variants(&#39;john.smith&#39;)<br><br># [&#39;john.smith&#39;, &#39;John.Smith&#39;, &#39;JOHN.SMITH&#39;, &#39;John.smith&#39;]<br>spark.sql(f&quot;&quot;&quot;<br>SELECT *<br>FROM users<br>WHERE username IN ({&#39;,&#39;.join([f&quot;&#39;{v}&#39;&quot; for v in variants])})<br>&quot;&quot;&quot;)</pre><h3>Implementation Best Practices</h3><p>Based on deploying this pattern across dozens of production systems, here are my recommendations:</p><h3>1. Know Your Cardinality</h3><p>This technique works best when the right side (the constant) has limited variations:</p><p><strong>Good candidates:</strong></p><ul><li>Standardized codes with known formats (device IDs, product codes)</li><li>Dates within a bounded range</li><li>Known discount rates or percentage values</li><li>IP addresses in specific subnets</li><li>Enum-like values with case variations</li></ul><p><strong>Poor candidates:</strong></p><ul><li>Free-text fields with arbitrary transformations</li><li>Unbounded numeric calculations</li><li>Complex regex patterns with infinite possibilities</li></ul><h3>2. Set Guardrails</h3><p>Always implement limits to prevent variant explosion:</p><pre>def generate_variants_safe(base_value, generator_func, max_variants=1000):<br>  &quot;&quot;&quot;<br>  Safely generate variants with a limit.<br>  Falls back to original query if too many variants.<br>  &quot;&quot;&quot;<br>  variants = generator_func(base_value)<br>  if len(variants) &gt; max_variants:<br>    raise ValueError(<br>      f&quot;Too many variants ({len(variants)}). &quot;<br>      f&quot;Consider using original query or refining search.&quot;<br>    )<br>  return variants</pre><h3>3. Cache Common Variant Lists</h3><p>For frequently used patterns, pre-compute and cache the variants:</p><pre># One-time setup<br>common_searches = {<br>  &#39;D401AB2C&#39;: generate_device_variants(&#39;D401AB2C&#39;),<br>  &#39;X2Y4Z8&#39;: generate_device_variants(&#39;X2Y4Z8&#39;),<br>  # … other common searches<br>}<br><br># Save to Delta table<br>variant_cache = spark.createDataFrame([<br>  (search, variant)<br>  for search, variants in common_searches.items()<br>  for variant in variants<br>], [&#39;search_value&#39;, &#39;variant&#39;])<br><br>variant_cache.write.format(&#39;delta&#39;).save(&#39;/path/to/variant_cache&#39;)<br><br># Use in queries<br>spark.sql(&quot;&quot;&quot;<br>SELECT e.*<br>FROM events e<br>JOIN variant_cache v ON e.device_id = v.variant<br>WHERE v.search_value = &#39;D401AB2C&#39;<br>&quot;&quot;&quot;)</pre><h3>4. Monitor Query Patterns</h3><p>Instrument your queries to track when this optimization is used:</p><pre>def execute_inverted_filter(table, column, search_value, generator_func):<br>  &quot;&quot;&quot;<br>  Execute query with filter inversion and log metrics.<br>  &quot;&quot;&quot;<br>  start_time = time.time()<br>  variants = generator_func(search_value)<br>  generation_time = time.time() - start_time<br><br>  # Log metrics<br>  print(f&quot;Generated {len(variants)} variants in {generation_time:.2f}s&quot;)<br><br>  # Execute query<br>  query_start = time.time()<br>  result = spark.sql(f&quot;&quot;&quot;<br>    SELECT * FROM {table}<br>    WHERE {column} IN ({&#39;,&#39;.join([f&quot;&#39;{v}&#39;&quot; for v in variants])})<br>  &quot;&quot;&quot;)<br>  result.cache() <br><br>  # Materialize to measure query time<br>  count = result.count()<br>  query_time = time.time() - query_start<br>  print(f&quot;Query returned {count} rows in {query_time:.2f}s&quot;)<br>  print(f&quot;Speedup over full scan: {estimate_full_scan_time(table) / query_time:.1f}x&quot;)<br>  return result</pre><h3>5. Combine with Table Optimization</h3><p>This technique works even better when combined with proper table optimization:</p><pre>-- Enable liquid clustering on the filtered column<br>ALTER TABLE device_inventory<br>CLUSTER BY (device_id);<br>OPTIMIZE device_inventory;<br>-- Now the inverted filter is even faster<br>-- because matching values are co-located in files</pre><h3>When NOT to Use This Technique</h3><p>Be honest about limitations:</p><p><strong>Don’t use filter inversion when:</strong></p><ol><li><strong>Variant count exceeds ~10,000</strong>: The IN clause becomes unwieldy and performance degrades</li><li><strong>Pattern is truly open-ended</strong>: If any transformation is possible, you can’t enumerate variants</li><li><strong>Column cardinality is very low</strong>: If the column only has 100 distinct values, a full scan might be fine</li><li><strong>Original query is already fast</strong>: Don’t optimize prematurely</li><li><strong>You can restructure the data</strong>: Sometimes fixing the root cause (data structure) is better than clever queries</li></ol><h3>Conclusion</h3><p>Filter inversion is a powerful technique that transforms impossible queries into efficient operations. By shifting computation from the left side (column) to the right side (constant), we unlock Delta Lake’s powerful optimization features: data skipping, partition pruning, and clustering benefits.</p><p>The core principle is simple: <strong>when you can’t efficiently transform billions of rows to match one value, transform one value to match billions of rows.</strong></p><p>This isn’t about being clever for cleverness’s sake. It’s about understanding how query engines work and exploiting their strengths. When you respect the optimizer’s need for simple predicates on raw columns, it rewards you with dramatic performance gains.</p><p>Start by identifying your most expensive queries — the ones with functions wrapped around columns in WHERE clauses. Evaluate whether you can enumerate the right-side variations. For many real-world cases involving standardized formats, dates, or bounded domains, you absolutely can.</p><p>The next time someone tells you a query “just needs to scan the whole table,” ask yourself: can I invert this filter?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a27b2b059274" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing Data Storage: A Guide to Complex Data Types]]></title>
            <link>https://medium.com/@jagjitnatt/optimizing-data-storage-a-guide-to-complex-data-types-807da9ac89c4?source=rss-85cd97bdabd8------2</link>
            <guid isPermaLink="false">https://medium.com/p/807da9ac89c4</guid>
            <category><![CDATA[storage]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Jagjit Natt]]></dc:creator>
            <pubDate>Fri, 14 Nov 2025 04:44:18 GMT</pubDate>
            <atom:updated>2025-11-14T04:44:18.874Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1010/1*hre2MbxalWG-yeACfvoKAw.jpeg" /></figure><p>In the world of big data, storage costs and query performance are perpetual concerns. As data engineers, we’re constantly seeking ways to optimize our data lakes without sacrificing functionality. Today, I want to share some powerful techniques for reducing Delta Lake table sizes — techniques that go beyond traditional advice and leverage modern capabilities like complex data types and liquid clustering.</p><h3>The Low Cardinality Problem</h3><p>Before diving into solutions, let’s understand the problem. Many real-world datasets suffer from what I call “the low cardinality bloat.” Consider these common scenarios:</p><p><strong>E-commerce order tables</strong> often have dozens of columns tracking order status, payment method, shipping method, warehouse location, and various flags. Most of these columns contain only a handful of distinct values repeated millions of times.</p><p><strong>IoT sensor data</strong> typically includes device type, location identifier, sensor status codes, and alert types — all low cardinality fields that create significant overhead when stored as individual columns.</p><p><strong>Application logs</strong> contain repeated values for log level, application name, environment, region, and various categorical attributes that rarely change but appear in every single row.</p><p>The traditional approach of storing each attribute as a separate column in a wide table creates substantial overhead. Each column requires metadata, compression dictionaries, and page headers in the underlying Parquet files. When you have 50 columns but only 10 of them contain truly unique data, you’re paying a heavy storage penalty.</p><h3>Complex Data Types: Your Secret Weapon</h3><p>The solution lies in restructuring your data using complex types — arrays, structs, and nested combinations. Here’s why this works and when to use it.</p><h3>Structs for Related Low Cardinality Fields</h3><p>Instead of storing order metadata as separate columns:</p><pre>-- Traditional approach (inefficient)<br>CREATE TABLE orders (<br>order_id STRING,<br>customer_id STRING,<br>order_date TIMESTAMP,<br>status STRING,<br>payment_method STRING,<br>shipping_method STRING,<br>warehouse_id STRING,<br>priority_flag BOOLEAN,<br>gift_wrap_flag BOOLEAN,<br>… -- 20 more similar columns<br>)</pre><p>Group related low cardinality fields into structs:</p><pre>-- Optimized approach<br>CREATE TABLE orders (<br>order_id STRING,<br>customer_id STRING,<br>order_date TIMESTAMP,<br>metadata STRUCT&lt;<br>  status: STRING,<br>  payment_method: STRING,<br>  shipping_method STRING,<br>  warehouse_id: STRING<br>&gt;,<br>flags STRUCT&lt;<br>  priority: BOOLEAN,<br>  gift_wrap: BOOLEAN,<br>  expedited: BOOLEAN<br>&gt;,<br>… -- other high cardinality fields<br>)</pre><p>This approach reduces the number of top-level columns, which means fewer column chunks in Parquet files and better compression. The struct fields still benefit from column-oriented storage within the struct, but you avoid the per-column overhead.</p><h3>Arrays for Repeated Categorical Data</h3><p>Consider a product catalog where products have multiple tags, categories, or attributes. Instead of creating separate Boolean columns for each possible tag or using a separate table:</p><pre>-- Efficient approach using arrays<br>CREATE TABLE products (<br>product_id STRING,<br>name STRING,<br>price DECIMAL(10,2),<br>tags ARRAY&lt;STRING&gt;, -- [&quot;electronics&quot;, &quot;sale&quot;, &quot;featured&quot;]<br>categories ARRAY&lt;STRING&gt;,<br>attributes MAP&lt;STRING, STRING&gt;<br>)</pre><h3>Structs of Arrays for Time-Series Events</h3><p>For scenarios where you track multiple events or state changes per entity, consider this pattern:</p><pre>-- Customer interaction history<br>CREATE TABLE customer_interactions (<br>customer_id STRING,<br>interaction_history ARRAY&lt;STRUCT&lt;<br>  timestamp: TIMESTAMP,<br>  interaction_type: STRING,<br>  channel: STRING,<br>  outcome: STRING<br>  &gt;<br>&gt;<br>)</pre><p>This pattern is particularly powerful for slowly changing dimensions or audit trails where you need to maintain history but don’t need to query each historical state independently.</p><h3>The Storage Savings</h3><p>In my experience working with Delta Lake at scale, these transformations typically yield:</p><ul><li><strong>30–50% reduction in storage size</strong> for tables with many low cardinality columns</li><li><strong>Improved compression ratios</strong> because similar data is physically co-located</li><li><strong>Faster metadata operations</strong> since Delta Lake tracks fewer top-level columns</li><li><strong>Reduced memory overhead</strong> during query planning and execution</li></ul><p>The exact savings depend on your data characteristics, but the pattern holds: fewer top-level columns with low cardinality values mean better storage efficiency.</p><h3>The Sorting Paradox</h3><p>Now, let’s address a piece of advice that’s become almost dogmatic in the data engineering community: “Always sort your data before writing to Parquet.” While this made perfect sense in the early days of Hadoop and static data warehouses, it’s increasingly impractical with modern table formats like Delta Lake, Iceberg, and Hudi.</p><h3>Why Traditional Sorting Breaks Down</h3><p>The problem is simple: <strong>table formats with ACID transactions and incremental updates don’t preserve global sort order</strong>.</p><p>When you write your initial dataset with perfect Z-ordering on date and customer_id, everything looks great. But what happens next month when you append new data?</p><ol><li>New data arrives covering the same date ranges as existing data</li><li>You write it as new files (because that’s how append works)</li><li>Your carefully crafted sort order now exists only within individual files</li><li>Data for the same date and customer is spread across multiple files</li><li>The problem is compounded with Merge and Update queries</li><li>Query performance degrades as the table grows</li></ol><p>Running OPTIMIZE with Z-ORDER after every write is prohibitively expensive for large tables with frequent updates. You end up rewriting massive amounts of data just to maintain ordering.</p><h3>The Reality of Modern Data Lakes</h3><p>In production environments, I see teams struggling with this tension:</p><ul><li><strong>Streaming pipelines</strong> write data continuously, making pre-sorting impossible</li><li><strong>Incremental batch jobs</strong> append data throughout the day</li><li><strong>Late-arriving data</strong> breaks chronological ordering</li><li><strong>Updates and merges</strong> scatter related records across files</li></ul><p>The traditional “sort once at write time” approach simply doesn’t fit these patterns. We need something better.</p><h3>Liquid Clustering: The Modern Solution</h3><p>This is where Delta Lake’s liquid clustering feature becomes a game-changer. Introduced to address exactly these challenges, liquid clustering provides automatic, incremental data organization without the overhead of full table rewrites.</p><h3>How Liquid Clustering Works</h3><p>Liquid clustering uses a fundamentally different approach than Z-ordering:</p><ol><li><strong>Clustering keys</strong> define how data should be organized (similar to sort keys)</li><li><strong>Incremental clustering</strong> happens automatically during writes and OPTIMIZE operations</li><li><strong>Data is physically co-located</strong> by the clustering keys within and across files</li><li><strong>No manual intervention</strong> required to maintain organization</li></ol><p>Most importantly: liquid clustering <strong>sorts your data internally as part of its operation</strong>. When the clustering algorithm reorganizes files, it maintains sorted order within the clustered groups.</p><h3>Implementation</h3><p>Here’s how to enable liquid clustering on a table:</p><pre>-- Create a new table with liquid clustering<br>CREATE TABLE customer_orders (<br>order_id STRING,<br>customer_id STRING,<br>order_date DATE,<br>metadata STRUCT&lt;<br>  status: STRING,<br>  payment_method: STRING,<br>  shipping_method: STRING<br>&gt;,<br>order_total DECIMAL(10,2)<br>)<br>USING DELTA<br>CLUSTER BY (order_date, customer_id);</pre><p>Or convert an existing table:</p><pre>-- Enable liquid clustering on existing table<br>ALTER TABLE customer_orders<br>CLUSTER BY (order_date, customer_id);<br>-- Run optimization to apply clustering<br>OPTIMIZE customer_orders;</pre><h3>Choosing Clustering Keys</h3><p>The key to effective liquid clustering is choosing the right clustering keys. Here are my guidelines:</p><p><strong>Good clustering candidates:</strong></p><ul><li>Columns frequently used in WHERE clauses (especially equality filters)</li><li>Columns used in JOIN operations</li><li>Medium to High cardinality fields that create meaningful groups</li><li>Time-based columns combined with entity identifiers</li></ul><p><strong>Poor clustering candidates:</strong></p><ul><li>Hashed values like MD5</li><li>Columns rarely or never used in filters</li><li>Columns with extremely skewed distributions</li></ul><p>For our earlier examples:</p><pre>-- E-commerce orders: cluster by date and customer<br>CLUSTER BY (order_date, customer_id)<br>-- IoT sensor data: cluster by device and timestamp<br>CLUSTER BY (device_id, event_timestamp)<br>-- Application logs: cluster by date and application<br>CLUSTER BY (log_date, application_name)</pre><h3>The Storage Benefit</h3><p>Here’s the beautiful part: liquid clustering not only improves query performance but can also <strong>reduce storage size</strong>. How?</p><ol><li><strong>Better compression:</strong> Data with similar characteristics is physically adjacent</li><li><strong>Smaller files with better stats:</strong> Clustering creates optimally sized files with tight min/max statistics</li><li><strong>Reduced metadata overhead:</strong> Fewer small files means less per-file overhead</li><li><strong>Natural deduplication:</strong> Similar records end up in the same files, enabling better compression algorithms</li></ol><p>In practice, I’ve seen liquid clustering provide an additional 15–25% storage reduction on top of the savings from using complex data types, while simultaneously improving query performance by 2–5x.</p><h3>Putting It All Together</h3><p>Let’s look at a complete example combining all these techniques:</p><pre>-- Original table (inefficient)<br>CREATE TABLE user_events_old (<br>user_id STRING,<br>event_timestamp TIMESTAMP,<br>event_type STRING,<br>page_url STRING,<br>device_type STRING,<br>browser STRING,<br>os STRING,<br>country STRING,<br>region STRING,<br>city STRING,<br>session_id STRING,<br>is_mobile BOOLEAN,<br>is_logged_in BOOLEAN,<br>is_premium BOOLEAN<br> -- 30+ more columns…<br>);<br><br>-- Optimized table<br>CREATE TABLE user_events_optimized (<br>user_id STRING,<br>event_timestamp TIMESTAMP,<br>session_id STRING,<br>page_url STRING, -- high cardinality<br>-- Low cardinality fields grouped<br>device_info STRUCT&lt;<br>  device_type: STRING,<br>  browser: STRING,<br>  os: STRING,<br>  is_mobile: BOOLEAN<br>&gt;,<br>location STRUCT&lt;<br>  country: STRING,<br>  region: STRING,<br>  city: STRING<br>&gt;,<br>user_flags STRUCT&lt;<br>  is_logged_in: BOOLEAN,<br>  is_premium: BOOLEAN<br>&gt;,<br>-- Event-specific attributes<br>event_attributes MAP&lt;STRING, STRING&gt;<br>)<br>USING DELTA<br>CLUSTER BY (DATE(event_timestamp), user_id);</pre><h3>Practical Recommendations</h3><p>Based on my experience, here’s a decision framework:</p><p><strong>Use complex data types when:</strong></p><ul><li>You have 5+ related low cardinality columns</li><li>Columns are queried together (e.g., all device info)</li><li>You need to maintain a flexible schema (use MAP types)</li><li>Storage costs are a primary concern</li></ul><p><strong>Use liquid clustering when:</strong></p><ul><li>You have tables larger than 1TB</li><li>Data is frequently appended or updated</li><li>You have clear access patterns (known filter columns)</li><li>Query performance on filtered scans matters</li></ul><p><strong>Combine both approaches when:</strong></p><ul><li>You have large tables with many low cardinality columns</li><li>You need both storage efficiency and query performance</li><li>You’re building a production data lake for the long term</li></ul><h3>Conclusion</h3><p>The data engineering landscape has evolved significantly. The old advice of “sort your data and use wide tables” doesn’t fit modern lakehouse architectures with continuous updates and ACID transactions.</p><p>By thoughtfully restructuring your Delta Lake tables with complex data types and leveraging liquid clustering, you can achieve substantial storage savings while improving query performance. The key is understanding your data’s characteristics — particularly the cardinality of your columns and your access patterns.</p><p>Storage optimization isn’t just about reducing costs; it’s about building sustainable data architectures that scale efficiently as your data grows. Start with your largest, most frequently accessed tables, apply these techniques, and measure the results. You might be surprised by how much you can gain.</p><p><em>Have you implemented similar optimizations in your data lake? I’d love to hear about your experiences and results in the comments below.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=807da9ac89c4" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>