<?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 William on Medium]]></title>
        <description><![CDATA[Stories by William on Medium]]></description>
        <link>https://medium.com/@iamwilliamde?source=rss-49d55a39b7b7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*mRKSzKM0d2DnAIvFm-ez7g.png</url>
            <title>Stories by William on Medium</title>
            <link>https://medium.com/@iamwilliamde?source=rss-49d55a39b7b7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 04:03:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@iamwilliamde/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[The Case for a Declarative Shift: Fixing the Foundation Before We Chase AI]]></title>
            <link>https://medium.com/cutting-through-the-data-spaghetti/the-case-for-a-declarative-shift-fixing-the-foundation-before-we-chase-ai-c2eb63016dae?source=rss-49d55a39b7b7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c2eb63016dae</guid>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[William]]></dc:creator>
            <pubDate>Thu, 16 Apr 2026 10:14:59 GMT</pubDate>
            <atom:updated>2026-04-16T13:19:58.601Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tbqXqUhdacg6H9zva7X-XA.png" /></figure><h3>1. The Maintenance Trap</h3><blockquote>Why our Data Strategy must evolve before we pursue AI ?</blockquote><p><strong>This is where we are now: moving from “Data Movers” to “Data Architects.”</strong></p><p>Everyone is excited about AI, but our current imperative PySpark scripts are holding us back. We are currently writing manual “recipes” for every data move. While it works for the moment, it’s brittle — our business logic is hard-coded into physical paths and rigid, manual steps.</p><p>If we don’t change how we work, we’re going to stay <strong>stuck in the weeds</strong>. We’re currently spending over <strong>50% of our time</strong> just babysitting “glue code.” You simply can’t build high-level AI on top of manual, unscalable pipelines.</p><h3>2. The Strategic Pivot</h3><p><strong>This shift is about the declarative advantage: building “Data Blueprints.”</strong> Switching to a declarative paradigm means we stop obsessing over <em>how</em> to move files and start defining <strong>what</strong> the data actually represents.</p><h4>The Difference in Practice (Example: High-Value User Definition)</h4><ul><li><strong>Before (The Black Box):</strong> Logic is buried inside Python scripts, tied to specific file paths. If the folder moves, everything breaks.</li></ul><pre># Hard-coded &amp; hidden in a script<br>df = spark.read.parquet(&quot;/hdfs/path/v2/payments/&quot;)<br>high_value_df = df.filter(df.amount &gt; 100).select(&quot;user_id&quot;)# Hard-coded &amp; hidden in a script df = spark.read.parquet(&quot;/hdfs/path/v2/payments/&quot;) high_value_df = df.filter(df.amount &gt; 100).select(&quot;user_id&quot;)</pre><ul><li><strong>After (The Data Blueprint):</strong> Logic is a standalone, human-readable specification. It describes the <strong>Business Rule</strong>, not the file path.</li></ul><pre>-- Human-readable &amp; Portable<br>CREATE VIEW high_value_users AS<br>SELECT user_id <br>FROM payments_base <br>WHERE amount &gt; 100</pre><p><strong>The Impact:</strong> This logic becomes <strong>portable</strong>. Whether we run on our local cluster today or a cloud AI platform tomorrow, the “design” of our data remains the same.</p><h3>3. The Big Picture</h3><p><strong>This is why we can’t afford to wait.</strong> If we don’t make this move, our most valuable logic stays “locked” inside scripts, creating massive <strong>Knowledge Silos</strong>. A declarative approach makes our data lineage transparent. It frees up our engineers to focus on <strong>high-value architecture</strong> and AI readiness instead of endless debugging.</p><p><strong>The bottom line:</strong> We can’t chase an AI future while we’re still anchored by outdated processes. We need to fix the foundation first.</p><h3>The ROI</h3><p>All come back to the business impact.</p><ul><li><strong>Scale the Team, Not the Debt:</strong> Modular SQL is far easier for new hires to maintain.</li><li><strong>Cost Efficiency:</strong> Smarter execution means lower hardware consumption on our local cluster.</li><li><strong>AI Foundations:</strong> We move from “fixing scripts” to “feeding models.”</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c2eb63016dae" width="1" height="1" alt=""><hr><p><a href="https://medium.com/cutting-through-the-data-spaghetti/the-case-for-a-declarative-shift-fixing-the-foundation-before-we-chase-ai-c2eb63016dae">The Case for a Declarative Shift: Fixing the Foundation Before We Chase AI</a> was originally published in <a href="https://medium.com/cutting-through-the-data-spaghetti">Cutting Through the Data Spaghetti</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Drawing a Map in the Commit Maze: Maintaining a 7-Year-Old Report Pipeline with Agents and Leaving…]]></title>
            <link>https://medium.com/cutting-through-the-data-spaghetti/drawing-a-map-in-the-commit-maze-maintaining-a-7-year-old-report-pipeline-with-agents-and-leaving-3887a88d1f1f?source=rss-49d55a39b7b7------2</link>
            <guid isPermaLink="false">https://medium.com/p/3887a88d1f1f</guid>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[William]]></dc:creator>
            <pubDate>Wed, 11 Mar 2026 17:03:47 GMT</pubDate>
            <atom:updated>2026-04-16T13:20:16.761Z</atom:updated>
            <content:encoded><![CDATA[<h3>Drawing a Map in the Commit Maze: Maintaining a 7-Year-Old Report Pipeline with Agents and Leaving a Trail for the Next Person</h3><h4>Ops asked for a dimension change. I got lost in a repo built from 295 commits — along with the AI — then tried to make it a little less lost.</h4><p>Operations (the report users) asked to change the dimensions of an online advertising report that’s been around for about seven and a half years. The pipeline and code behind that report have lived in the same repo since December 2018, with roughly 295 commits touching this path — and multiple tech stacks and product needs layered on top.</p><p>I was the one implementing this dimension change in the existing setup. Along the way, I wasn’t the only one getting lost: the AI code agent in Cursor was right there with me, lost in the commits and the data-processing logic.</p><p>This post is a short account of that experience, how we started “drawing a map,” where we still need a human in the loop, and what might come next.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zOxM6DoqSDWKJWeKACcNQg.png" /></figure><h2>1. Getting Lost: In the Data Logic of Commits, Together with the Agent</h2><p>This really is an old, low-maintainability, not-at-all-modern data report.</p><ul><li><strong>Context is scattered.</strong> Paths, environments, and table names live in code or ad-hoc config, with no single place to manage them. Two generations of implementation coexist (legacy MySQL scripts vs. Latte / ClickHouse). To understand the full data processing logic, you have to dig through many commits and files.</li><li><strong>The agent gets lost too.</strong> When you ask it “why is this logic written this way?” or “where do this report’s dimensions and metrics come from?”, it can only infer from the current file and whatever docs you @ in. Without repo-level README, changelog, or data lineage, the agent — like a human — has no stable anchor in 295 commits and multiple layers of joins.</li><li><strong>Change one thing, risk breaking many.</strong> Ops only asked to “change dimensions,” but dimensions touch data modeling, metric logic, joins, and filters across many files. Without a “map,” both humans and agents tend to change the wrong place or miss a spot, and only notice when the pipeline runs or after release.</li></ul><p><strong>Getting lost is the outcome.</strong> The cause is that this repo was built for “the people back then”; it never left readable context for “future us” or “agents in the new era.”</p><h2>2. Drawing the Map: Changelog, README, and Docs So the Repo Lives Longer and Agents Can Help</h2><p>To extend the repo’s life and let modern agents contribute — so the report pipeline can become more modern — we started drawing a map.</p><ul><li><strong>Changelog.</strong> Record the date, scope, and reason of each significant change (e.g. “Ops request: dimension change for report X,” “ad-level join and dimension fill for channel Y”). That lets the next person — and the next agent — reconstruct “what changed on this line” by time and topic.</li><li><strong>README / repo intro.</strong> Describe directory layout, which stack runs today (Latte / CK), key reports and data flow, and dependencies. Add data lineage or a simple “report ↔ script ↔ table” mapping so “where to change” has a clear entry point.</li><li><strong>Semantic commits.</strong> Each commit that clearly states <em>which</em> report, <em>which</em> dimension, <em>which</em> layer is affected writes context into Git history and makes the next maintainer’s job possible. Later, both agents and humans can use git log or search and actually find clues.</li></ul><p>The point of drawing the map isn’t to pay off all technical debt at once. It’s so <strong>the next person and the next agent don’t have to get lost from scratch.</strong> The repo’s longevity and maintainability rest on that readable context.</p><h2>3. Where to Guard: What Humans Must Own, and How to Verify</h2><p>Agents can help edit code, fill in docs, and scan commits. Some things still need a human in the loop, with clear ways to verify.</p><h3>3.1 What to guard</h3><ul><li><strong>Data modeling.</strong> Dimensions, grain, keys, and the boundary between fact and dimension tables — these determine whether the report is correct and extensible. Agents can draft from docs, but “does this model match the business?” and “will this cause duplicates or gaps?” need human judgment.</li><li><strong>Metric and indicator logic.</strong> Aggregation rules, definitions, numerators and denominators, filters (e.g. status, date range). Getting this wrong directly affects ops decisions. Humans should lock down formulas and edge cases, document or comment them, then let agents help implement or refactor.</li><li><strong>Paths, environment, table names.</strong> If they’re not fully centralized in config yet, at least check on each change: “did we miss another place that references this path or table?” Humans can own the checklist or dependency map; agents can help search and suggest.</li></ul><h3>3.2 How to verify</h3><ul><li><strong>Reconcile numbers.</strong> Before and after the change, compare key metrics or row counts (same period, same filters) to ensure the dimension change didn’t introduce duplicates or drop rows.</li><li><strong>Sample and eyeball.</strong> For the changed dimensions (e.g. new dimension fields, filter conditions), pull a few real rows and check that the fields look right and match upstream or ops’ expectations.</li><li><strong>Limited rollout and monitoring.</strong> If you have staging environment, run there for a while before full rollout. Add monitoring or alerts so you can roll back or fix quickly when something looks off.</li></ul><p>Guarding doesn’t mean “don’t use agents.” It means <strong>putting agents inside a process with clear boundaries and checks</strong>: they execute and search; humans decide and validate.</p><h2>4. What’s Next: Agents for Ops, Auto-Generated Charts, and Beyond</h2><ul><li><strong>Agents for ops.</strong> e.g. “ask the report in natural language,” “auto-generate charts and drill-downs by dimension.” That only works if the semantics and definitions of reports and dimensions are already written down in docs and the data model. The map we’re drawing now — README, changelog, data lineage, semantic commits — is the input those agents will need.</li><li>A typical ops analysis scenario: Monday morning, ops asks, “How did last week’s campaign perform on channel A vs. B, and which segment had the worst conversion?” Right now that means opening the report, filtering dimensions, pulling into Excel, then turning it into a chart. If the map and definitions are in place, it could become: “Compare last week’s performance by channel and segment with drill-down,” and the agent returns the chart and summary.</li></ul><p>From there, data engineers’ hands-on ops work will keep shrinking. When that day comes — will we still need architects? I don’t have an answer, but drawing the map and writing clear commits at least means that when “that day” arrives, both humans and agents will still have a path to follow.</p><p>The future isn’t “humans get replaced.” It’s <strong>humans drawing the map and holding the line so agents and automation run on the right track.</strong> That kind of accountability — leaving a readable trail in commits and docs for the next person — is what we leave behind today.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3887a88d1f1f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/cutting-through-the-data-spaghetti/drawing-a-map-in-the-commit-maze-maintaining-a-7-year-old-report-pipeline-with-agents-and-leaving-3887a88d1f1f">Drawing a Map in the Commit Maze: Maintaining a 7-Year-Old Report Pipeline with Agents and Leaving…</a> was originally published in <a href="https://medium.com/cutting-through-the-data-spaghetti">Cutting Through the Data Spaghetti</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Splitting a Spark Dataset Randomly into Two Parts: Same Group Key Must Stay in One Part]]></title>
            <link>https://medium.com/cutting-through-the-data-spaghetti/splitting-a-spark-dataset-randomly-into-two-parts-same-group-key-must-stay-in-one-part-571cda2027af?source=rss-49d55a39b7b7------2</link>
            <guid isPermaLink="false">https://medium.com/p/571cda2027af</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[spark]]></category>
            <dc:creator><![CDATA[William]]></dc:creator>
            <pubDate>Sun, 08 Mar 2026 08:26:37 GMT</pubDate>
            <atom:updated>2026-04-16T13:20:25.969Z</atom:updated>
            <content:encoded><![CDATA[<h4><strong>I told the Agent “split by group key so each group stays together” — then data validation kept failing. A data engineer’s implementation notes.</strong></h4><h2>1. Clarifying the requirements</h2><p>We need to split one dataset randomly into two parts (e.g. treatment 70% / control 30%) and <strong>adhere to one rule</strong>: <strong>all records under the same grouping key must end up in the same part</strong> — no half in A and half in B. For example, if the grouping key is a composite of “user + dimension”, one user may have multiple detail rows; we must split by “group” as the unit. If we split row-by-row at random, the same user could appear in both treatment and control, and the experiment design would be <strong>invalid</strong>.<br>We also need the split to be <strong>reproducible</strong>: same seed and same data should yield the same split every time, so we can debug and re-run reliably. The grouping unit is the grouping key (single column or composite); the split ratio is set as needed.</p><h2>2. The approach</h2><h3>2.1 What we did originally (anti-pattern)</h3><p>The following is an <strong>anti-pattern</strong> from the output-layer list split <strong>before</strong> the fix — excerpted from the actual implementation.</p><pre># Anti-pattern: split_dataframe_by_ratio logic (excerpt)<br>ratio = split_ratio / 100.0<br>df = df.cache()<br>total_count = df.count()<br>target_part1 = int(round(ratio * total_count))<br><br>group_sizes = df.groupBy(&quot;key1&quot;, &quot;key2&quot;).agg(F.count(&quot;*&quot;).alias(&quot;_gcount&quot;))<br>group_sizes = group_sizes.withColumn(&quot;_rand&quot;, rand(random_seed))  # ⚠️ Main issue: rand depends on partition ID; split boundary can change between runs, so counts don&#39;t match on re-run<br><br>w = Window.orderBy(&quot;_rand&quot;, &quot;key1&quot;, &quot;key2&quot;).rowsBetween(Window.unboundedPreceding, Window.currentRow)<br><br>group_with_cum = group_sizes.withColumn(&quot;_running_sum&quot;, F.sum(&quot;_gcount&quot;).over(w)).withColumn(<br>    &quot;_in_part1&quot;, F.col(&quot;_running_sum&quot;) &lt;= F.lit(target_part1)<br>)<br># group_with_cum has one row per (key1, key2); join then gives that group a single _in_part1<br>df_with_flag = df.join(group_with_cum.select(&quot;key1&quot;, &quot;key2&quot;, &quot;_in_part1&quot;), [&quot;key1&quot;, &quot;key2&quot;])<br>df_part1 = df_with_flag.filter(F.col(&quot;_in_part1&quot;)).drop(&quot;_in_part1&quot;)<br>df_part2 = df_with_flag.filter(~F.col(&quot;_in_part1&quot;)).drop(&quot;_in_part1&quot;)<br><br># ⚠️ No cache before count: the two count()s trigger two separate runs; <br># rand can give different boundaries each time,<br># so part1_count and part2_count correspond to two different splits; <br># the sum may ≠ total_count and the check below fails<br>part1_count = df_part1.count()<br>part2_count = df_part2.count()<br><br>print(&quot;[{}] Part 1 (treatment) rows: {} ({:.2f}%)&quot;.format(task_name, part1_count, part1_count / total_count * 100))<br>print(&quot;[{}] Part 2 (control) rows: {} ({:.2f}%)&quot;.format(task_name, part2_count, part2_count / total_count * 100))<br><br>if part1_count + part2_count != total_count:<br>    print(&quot;[{}] Warning: Row count mismatch! Total: {}, Sum: {}&quot;.format(task_name, total_count, part1_count + part2_count))<br><br>group_check = df.groupBy(&quot;key1&quot;, &quot;key2&quot;).count().withColumnRenamed(&quot;count&quot;, &quot;_total_cnt&quot;)<br><br>part1_check = df_part1.groupBy(&quot;key1&quot;, &quot;key2&quot;).count().withColumnRenamed(&quot;count&quot;, &quot;_part1_cnt&quot;)<br><br>part2_check = df_part2.groupBy(&quot;key1&quot;, &quot;key2&quot;).count().withColumnRenamed(&quot;count&quot;, &quot;_part2_cnt&quot;)<br><br>validation_df = group_check.join(part1_check, [&quot;key1&quot;, &quot;key2&quot;], &quot;left&quot;).join(<br>    part2_check, [&quot;key1&quot;, &quot;key2&quot;], &quot;left&quot;<br>).fillna(0, subset=[&quot;_part1_cnt&quot;, &quot;_part2_cnt&quot;])<br>split_groups = validation_df.filter(<br>    (col(&quot;_part1_cnt&quot;) &gt; 0) &amp; (col(&quot;_part2_cnt&quot;) &gt; 0)<br>).count()<br><br><br>if split_groups &gt; 0:<br>    print(&quot;[{}] Warning: {} groups were split across both parts!&quot;.format(task_name, split_groups))<br>else:<br>    print(&quot;[{}] Check: All key1+key2 groups are intact (no group in both parts)&quot;.format(task_name))</pre><p>At first glance, you’d expect the check if part1_count + part2_count != total_count: to pass. In theory, for the same df and the same split, part1 + part2 should equal total_count. It often doesn’t because <strong>part1_count</strong> and <strong>part2_count</strong> come from <strong>two separate actions</strong> (two count() calls), each triggering a full recomputation. Each time, rand() can produce a different group order depending on partitioning or execution plan, so the split boundary changes—the two counts correspond to <strong>two different split outcomes</strong>, and their sum may not equal total_count. The <strong>main issue</strong> is the <strong>_rand = rand(random_seed)</strong> line; second, we didn’t <strong>cache before count</strong> after building part1/part2, so every later action recomputes and can yield different results.</p><h3>2.2 The fix</h3><p>Use <strong>hash(grouping key, seed)</strong> as the sort key (partition-independent, boundary reproducible), <strong>cache right after the split then count</strong> (so validation and writes use the same materialized result), and when needed: filter nulls, or deduplicate per group. Example:</p><pre># Fix: hash(key, seed) is partition-independent; same seed + same data ⇒ fixed order<br>df_clean = df.filter(F.col(&quot;key1&quot;).isNotNull() &amp; F.col(&quot;key2&quot;).isNotNull())<br>total = df_clean.count()<br>target_part1 = int(round(total * split_ratio / 100.0))<br><br>group_sizes = (<br>    df_clean.groupBy(&quot;key1&quot;, &quot;key2&quot;)<br>    .agg(F.count(&quot;*&quot;).alias(&quot;_gcount&quot;))<br>    .withColumn(&quot;_order&quot;, F.hash(&quot;key1&quot;, &quot;key2&quot;, F.lit(seed)))<br>)<br>w = Window.partitionBy(F.lit(0)).orderBy(&quot;_order&quot;, &quot;key1&quot;, &quot;key2&quot;)<br>assignment = (<br>    group_sizes.withColumn(&quot;_running_sum&quot;, F.sum(&quot;_gcount&quot;).over(w))<br>    .withColumn(&quot;_in_part1&quot;, F.col(&quot;_running_sum&quot;) &lt;= target_part1)<br>    .select(&quot;key1&quot;, &quot;key2&quot;, &quot;_in_part1&quot;)<br>)<br><br><br># Dedupe per group (one row per group), then left join to avoid one-to-many<br>w_dedup = Window.partitionBy(&quot;key1&quot;, &quot;key2&quot;).orderBy(&quot;key1&quot;, &quot;key2&quot;)<br>assignment = assignment.withColumn(&quot;_rn&quot;, F.row_number().over(w_dedup)).filter(F.col(&quot;_rn&quot;) == 1).select(&quot;key1&quot;, &quot;key2&quot;, &quot;_in_part1&quot;)<br>df_with_flag = df_clean.join(assignment, [&quot;key1&quot;, &quot;key2&quot;], &quot;left&quot;)<br>df_with_flag = df_with_flag.withColumn(&quot;_in_part1&quot;, F.coalesce(F.col(&quot;_in_part1&quot;), F.lit(False)))<br>df_part1 = df_with_flag.filter(F.col(&quot;_in_part1&quot;)).drop(&quot;_in_part1&quot;)<br>df_part2 = df_with_flag.filter(~F.col(&quot;_in_part1&quot;)).drop(&quot;_in_part1&quot;)<br>df_part1 = df_part1.cache()<br>df_part2 = df_part2.cache()<br>n1, n2 = df_part1.count(), df_part2.count()<br><br><br># Validation and writes use only these cached DFs; counts stay consistent<br># Row-count and key-overlap checks (log only, no exception)<br>sum_parts = n1 + n2<br><br><br>if sum_parts != total:<br>    print(f&quot;Warning: Row count mismatch! Total: {total}, Sum: {sum_parts} (part1={n1}, part2={n2})&quot;)<br>else:<br>    print(f&quot;Check: Row count OK (total={total}, part1+part2={sum_parts})&quot;)<br><br><br>overlap = df_part1.select(&quot;key1&quot;, &quot;key2&quot;).join(df_part2.select(&quot;key1&quot;, &quot;key2&quot;), [&quot;key1&quot;, &quot;key2&quot;], &quot;inner&quot;).count()<br>if overlap &gt; 0:<br>    print(f&quot;Warning: part_1 and part_2 share {overlap} (key1, key2) group(s)&quot;)<br>else:<br>    print(&quot;Check: No overlapping (key1, key2) between parts&quot;)</pre><p>Why this works: <strong>hash(key, seed)</strong> depends only on input data and seed, not on partitioning or execution order, so the split boundary is reproducible. <strong>Caching right after the split then counting</strong> means all later validation and writes use the same part1/part2; there’s no recomputation with a different boundary, so row-count and no-overlap checks pass consistently.</p><h2>3. My role in this project</h2><h3>3.1 The Agent can find the implementation</h3><p>Things like “deterministic sort with hash”, “Window.partitionBy(lit(0))”, and “cache before count” — the Agent can look them up and produce the code. Partitioning making rand non-reproducible, lazy evaluation pitfalls, dedup after groupBy, left join to align — you can search or ask and get implementations. The hard part isn’t finding a technical approach; it’s stating the requirements clearly, explaining why validation fails, and choosing which approach to adopt and being <strong>accountable</strong> for the outcome.</p><h3>3.2 Beyond that, it’s just ownership</h3><p>The Agent is better at looking up solutions, writing code, and proposing fixes. When I tune the Agent’s output, it’s like <strong>a conductor and the orchestra</strong>: the Agent plays the notes; I point out what’s wrong and how to fix it, and I’m the one responsible for whether it sounds right and whether we <strong>sign off</strong> on it. Or like <strong>an architect and the builder</strong>: the Agent builds from the blueprint; I inspect and decide whether to sign off. So apart from taking responsibility, it comes down to this — judgment, <strong>oversight</strong>, and whether I’m willing to hand it over.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=571cda2027af" width="1" height="1" alt=""><hr><p><a href="https://medium.com/cutting-through-the-data-spaghetti/splitting-a-spark-dataset-randomly-into-two-parts-same-group-key-must-stay-in-one-part-571cda2027af">Splitting a Spark Dataset Randomly into Two Parts: Same Group Key Must Stay in One Part</a> was originally published in <a href="https://medium.com/cutting-through-the-data-spaghetti">Cutting Through the Data Spaghetti</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Three Days Fixing the Same DWS Pipeline: A Data Engineer’s Debugging with Agent]]></title>
            <link>https://medium.com/cutting-through-the-data-spaghetti/three-days-fixing-the-same-dws-pipeline-a-data-engineers-debug-log-and-reflection-7b2518c5e177?source=rss-49d55a39b7b7------2</link>
            <guid isPermaLink="false">https://medium.com/p/7b2518c5e177</guid>
            <category><![CDATA[context-engineering]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[William]]></dc:creator>
            <pubDate>Sat, 07 Mar 2026 13:18:41 GMT</pubDate>
            <atom:updated>2026-04-16T13:20:35.960Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V_Eb-FxUJmU8XPwqk3oaVQ.png" /></figure><p>A data engineer’s day is full of <strong>manual workflows</strong>: aligning source tables, checking key distributions, comparing output to expectations, writing validation scripts, fixing DAGs, and filling in documentation.<br>As agents become more common, this work feels more and more like <strong>archaeology</strong> — or <strong>mining</strong>: shovel by shovel, digging out data buried in different places and shaping it into something usable.</p><h2>Part 1: What I Actually Did as a Human Over Those Three Days</h2><p>Over those three days I was repeatedly fixing the same PySpark script — because every change required waiting for the pipeline to run before I could see the result. The script is a <strong>DWS (summary layer) pipeline</strong> that the analysts wanted — the kind of multi-table join that produces a wide table for downstream analytics or campaigns, often called a <strong>mart</strong> or <strong>summary / aggregate layer</strong> in communities like dbt.</p><p>The goal here was to produce a “users eligible for push” wide table; the analysts had provided a Python script, but <strong>the script had never actually been run on the cluster</strong> before it reached me. The first two days of test runs were okay, but from the third day onward, Spark tasks started taking hours and never finishing.</p><p>Here’s what I did step by step.</p><h3>1. Initial blind spot: trusting the analyst’s script</h3><p><strong>I assumed the analyst’s SQL or Python was correct</strong> — they’d done the analysis and had already delivered results to the business; senior colleagues said the analysts had reviewed the SQL and usually didn’t change it.<br>When analysts write scripts, <strong>the validation setup often doesn’t match production</strong>: they may have run only locally or on a sample, not on a distributed cluster over full or multi-day incremental data. Once in production, you hit “data shape” the script never considered — e.g. the logic has <strong>full</strong> and <strong>incremental</strong> modes, and incremental does “previous day’s result + today’s changes” joined; if you never <strong>inspect join key distribution</strong>, you can easily miss that one side is <strong>1-to-many</strong> (one “user/account key” can map to many “detail” rows — e.g. multiple devices, multiple push tokens). With that key in the join, a few “heavy” keys pull in dozens or hundreds of rows, and shuffle sends most of the output to a few partitions, causing severe <strong>data skew</strong>.</p><h3>2. What I saw in Spark UI (skew) and the urge to “optimize join”</h3><p>In Spark UI’s Stages tab you see classic <strong>data skew</strong>: <strong>one stage never finishes</strong> (progress stuck around 98–99%, one or two tasks running for tens of minutes or hours); <strong>shuffle write heavily skewed</strong> (most tasks write a few MB, one or two write many GB or even hundreds of GB — when keys are very skewed, most of the join output can land in one or two partitions); <strong>most executors idle, a few overloaded</strong> (memory filled by large partitions, then spill and GC spikes, sometimes OOM). The result is much slower runs or row counts that don’t match expectations.<br>Faced with that, the first thing Cursor suggested was <strong>“optimize the join”</strong> — e.g. salted join, tune partitions. I went along and didn’t question the analyst’s data logic. But often the root cause is <strong>data shape</strong>, especially join key distribution on both sides; if you don’t look at that first, tuning alone rarely fixes it.</p><h3>3. Introducing salted join to spread heavy keys</h3><p>I introduced <strong>salted join</strong> in the 1-to-many join step (e.g. dimension table join detail table): add salt to the join key so that heavy keys are spread across multiple partitions and no single task holds the whole mass; the same salted join is applied in both full and incremental paths to keep behavior consistent.</p><h3>4. Repeatedly tuning salted join parameters</h3><p>After adding salted join I <strong>kept tuning its parameters</strong> (salt size, partition count, etc.) trying to get the job to finish. It worked for a while, but as <strong>more days of incremental logic</strong> accumulated, join volume and key distribution worsened and skew got worse — eventually the Spark job wouldn’t finish at all. Parameter tuning alone wasn’t enough; it had to be combined with join key fixes, deduplication, and repartitioning.</p><h3>5. Salted join couldn’t fix it by itself</h3><p>After salted join and tuning had no real effect, I <strong>actually looked at the shape of the data</strong> for a previously successful run — inputs and outputs.</p><ul><li><strong>Key distribution checks</strong> on both sides of the join and on the join result: groupBy on the join key and count rows per key.</li><li>I found that in some data, fields that were supposed to be primary key had <strong>duplicate rows</strong>. I immediately connected that to <strong>1-to-many join output</strong>. And if the pipeline runs in incremental mode, it gets worse over time: first 1→5, next day 5→25 — <strong>exponential row growth</strong>.</li></ul><h3>6. Fix: align join key and deduplicate to avoid Cartesian explosion and duplicate rows</h3><ul><li><strong>Deduplicate at source</strong>: Dimension or fact sources often have multiple rows per join key (e.g. history, duplicate writes, multiple versions). On read, use <strong>Window + row_number</strong> by business rule (e.g. latest by timestamp or by version) to keep one row per key before joining, and add <strong>dropDuplicates on business key</strong> after the join in several places as defensive coding.</li><li><strong>Align join key</strong>: If the join uses only a <strong>coarse key</strong> and not <strong>fields that distinguish multiple rows per key</strong> — i.e. join key granularity is coarser than the actual grain of both sides — then “many rows per key” on the left and “many rows per key” on the right <strong>cross-multiply</strong> (<strong>Cartesian explosion</strong>). So the join key must be <strong>complete to a unique row</strong> (same as business primary key or key combination), one row to one row, to avoid duplicate rows.</li></ul><h3>7. Final debug: writing to HDFS still sometimes hit SparkOutOfMemoryError</h3><p>This was <strong>skewed partition sizes at write time</strong>: a few write tasks hit <strong>SparkOutOfMemoryError (UnsafeExternalSorter)</strong>.</p><p><strong>Why:</strong> An earlier 1-to-many join (e.g. dimension join detail table) partitions its output by join key, so a few “heavy” keys make a few Spark partitions very large. If we don’t <strong>repartition</strong> to rebalance in the pipeline, we still write with that skewed layout; those few partitions stay huge. If we then use <strong>coalesce</strong> to merge partitions, we only merge neighboring ones, so already-large partitions get even larger and the corresponding write tasks are more likely to OOM.</p><p><strong>Fix:</strong> Replace <strong>coalesce</strong> before write with <strong>repartition</strong> — force a shuffle so partition load is more even, then write to HDFS.</p><h2>Part 2: Reflection</h2><h3>1. Why it took so long</h3><p><strong>Three reasons</strong> (all at once, and no ready-made answers at the start):</p><ol><li><strong>No “table context” to look up</strong>: What’s the primary key, what do we join with, why this join? Not written in table docs or a dedicated file; I had to infer from code and comments, and even run the data once to confirm “so it’s 1-to-many and the previous-day join was missing a key.”</li><li><strong>Root cause had to be narrowed step by step</strong>: Stage stuck, shuffle write exploding — at first I didn’t think of join key distribution; I suspected partitions, resources, data volume. Only after ruling those out did I check key distribution, add skew checks, try salted join; each step meant running jobs or checking Spark UI.</li><li><strong>High cost of trial and error</strong>: Changing join key, repartition, coalesce — each change required waiting for the pipeline to run to see if it helped or hit OOM again; without a “measure then change” habit or scripts, several rounds of trial and error.</li></ol><h3>2. The value of a Data Engineer</h3><p>Against the backdrop of <strong>miners’ decline</strong>, I see the DE’s risk like this: data work is shifting from a <strong>craft that needs experience</strong> (the kind of thing I did these three days) to a <strong>commodity that only needs compute</strong>. Once agents complete the “industrialization” of the data world, DEs who stay at the extraction layer will watch their value get packaged into AI’s black box.</p><p>AI is like a junior who’s very good at writing code. If you give it context, it can help with what machines are already better at — e.g. checking data shape and producing reports. I think the <strong>remaining value</strong> of a DE is handling how <strong>data warehouse context is inherently messy</strong>: different teams, sources, and eras of pipeline development leave “where this table came from, what’s the primary key, how it’s joined” scattered, or only in someone’s head. But I suspect I won’t be doing that for much longer either.</p><p>There is still <strong>data architecture design</strong>, <strong>business context</strong>, <strong>correctness definitions</strong>, and <strong>data contracts</strong> — the ability to build a data system from 0→1. The good news, as dbt’s Tristan Handy said: data engineers will be busier than ever, but the work will be more <strong>strategic</strong>. So hang in there.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7b2518c5e177" width="1" height="1" alt=""><hr><p><a href="https://medium.com/cutting-through-the-data-spaghetti/three-days-fixing-the-same-dws-pipeline-a-data-engineers-debug-log-and-reflection-7b2518c5e177">Three Days Fixing the Same DWS Pipeline: A Data Engineer’s Debugging with Agent</a> was originally published in <a href="https://medium.com/cutting-through-the-data-spaghetti">Cutting Through the Data Spaghetti</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>