<?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 Rajeshwari Raghuraman on Medium]]></title>
        <description><![CDATA[Stories by Rajeshwari Raghuraman on Medium]]></description>
        <link>https://medium.com/@rrajeshwaris?source=rss-a206d8363103------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*St1muAYzsx1NsXr0.jpg</url>
            <title>Stories by Rajeshwari Raghuraman on Medium</title>
            <link>https://medium.com/@rrajeshwaris?source=rss-a206d8363103------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 23:00:09 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rrajeshwaris/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[Architecting Production-Grade Genie Agents: From Semantic Contracts to Benchmarks]]></title>
            <link>https://medium.com/@rrajeshwaris/architecting-production-grade-genie-agents-from-semantic-contracts-to-benchmarks-1a008b999ae6?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/1a008b999ae6</guid>
            <category><![CDATA[databricks-genie]]></category>
            <category><![CDATA[semantic-layer]]></category>
            <category><![CDATA[ai-agent]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Fri, 20 Nov 2026 04:42:32 GMT</pubDate>
            <atom:updated>2026-11-20T04:42:32.753Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6lD2wQgoW2CfbihQ8HLkdA.png" /><figcaption>From dashboards to dialogue: Genie connects business context with governed data to turn natural-language questions into insights and trends</figcaption></figure><h3>An opinionated reference architecture for building trustworthy Genie Agents with governed data, deterministic semantics, and continuous validation</h3><p>A Genie Agent can generate impressive answers surprisingly quickly.</p><p>Building one that analysts <strong>consistently trust</strong> is a different problem.</p><p>The difference is rarely another prompt. It is the architecture underneath the conversation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ix9GY1tZtiJlsKaBerC8VA.jpeg" /></figure><p><em>A layered architecture for trustworthy conversational analytics.</em></p><p>The architectural principle is simple:</p><p><strong>Business users → Genie One → Specialized Genie Agents → Governed Semantic Layer → Trusted Data</strong></p><p>A single Genie Agent should not have to understand every analytical domain. Instead, use <strong>specialized Genie Agents with clear domain boundaries</strong>, backed by shared semantics and trusted data.</p><p>For example:</p><ul><li>Sales Performance</li><li>Customer Engagement</li><li>Marketing Performance</li><li>Support Operations</li></ul><p>The examples are intentionally generic. The important pattern is <strong>bounded analytical domains with a governed foundation</strong>.</p><blockquote><strong><em>A production-grade Genie Agent is not a prompt connected to tables. It is a governed analytical system with a semantic contract, tests, and controlled change.</em></strong></blockquote><h3>2. Put the Right Logic in the Right Layer</h3><p>One of the most important design decisions is deciding <strong>where business knowledge belongs</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rBd3FNNakCk_VmsaIHNz7w.jpeg" /></figure><p><em>Put deterministic logic in the semantic layer and reserve instructions for behavior.</em></p><p>The rule I use is:</p><blockquote><strong><em>If business logic can be deterministic, do not leave it to prompting.</em></strong></blockquote><p>Do not make the Genie Agent infer a distinction that can be encoded explicitly.</p><h3>3. Start with a Strong Semantic Contract</h3><p>A Genie Agent should not have to reverse-engineer the meaning of raw data.</p><p>For every important table, define:</p><ul><li><strong>Grain:</strong> What makes one row unique?</li><li><strong>Filters:</strong> What context must always be applied?</li><li><strong>Aggregation:</strong> What can and cannot safely be summed?</li><li><strong>Dates:</strong> What does each date represent?</li><li><strong>Freshness:</strong> When should the data be available?</li></ul><p>Metadata is part of this contract.</p><p>Important columns should communicate business meaning, expected values, units, and date semantics. Prefer real DATE and TIMESTAMP types over requiring the Genie Agent to interpret strings.</p><blockquote><strong><em>Encode meaning as close to the data as possible.</em></strong></blockquote><p>The stronger the semantic contract, the less ambiguity the Genie Agent needs to resolve.</p><h3>4. Be Suspicious of Joins</h3><p>More tables do not automatically make a Genie Agent smarter.</p><p>Every additional join introduces another opportunity for:</p><ul><li>grain mismatch</li><li>row multiplication</li><li>ambiguous query paths</li><li>incorrect aggregation</li></ul><p>The critical concept is <strong>grain</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X4g5HDs7zoJePx7AzP6J4g.jpeg" /></figure><p><em>Different grains can produce mathematically valid SQL and analytically incorrect answers.</em></p><p>As a practical starting point, I recommend:</p><ul><li><strong>1–3 trusted tables per Genie Agent</strong></li><li><strong>5 or fewer joins where practical</strong></li><li>relationships with clearly understood cardinality</li></ul><p>These are <strong>architectural guardrails, not Databricks platform limits</strong>.</p><p>If the same complicated join is required for almost every question, consider solving it upstream.</p><blockquote><strong><em>Make the safe analytical path the easiest path.</em></strong></blockquote><h3>5. Use Example SQL to Teach Patterns</h3><p>Example SQL should not become a library of canned answers.</p><p>Use examples to teach <strong>analytical patterns</strong>.</p><p>For a week-over-week question, for example:</p><p><strong>Latest available period → Previous comparable period → Current metric → Prior metric → Change</strong></p><p>The specific answer will change.</p><p>The analytical pattern should not.</p><p>This makes examples reusable across many questions rather than tied to one expected result.</p><h3>6. Benchmarks Turn a Demo into a Product</h3><p>A Genie Agent should not enter production because ten demo questions worked.</p><p>Build a benchmark suite that deliberately tests different failure modes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fm3s4RacMaFTCgYtqLGRMA.jpeg" /></figure><p>Do not rely only on one overall accuracy score.</p><p>A Genie Agent could perform well overall while still being unreliable on the exact questions that matter most, such as non-additive metrics or time comparisons.</p><p>Evaluate quality by <strong>benchmark category</strong>.</p><h3>7. Make Production Feedback Part of Development</h3><p>A production Genie Agent should continuously become harder to break.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d32zt3yo9fhCD6pK_sLUvw.jpeg" /></figure><p><em>Every confirmed production failure should strengthen the benchmark suite.</em></p><p>When production reveals a failure:</p><p><strong>Production finding → Root cause → Fix → New benchmark → Release</strong></p><p>The important principle is:</p><blockquote><strong><em>Every confirmed production failure should make the benchmark suite stronger.</em></strong></blockquote><p>Do not fix only the individual answer.</p><p>Fix the underlying semantic, data-modeling, or behavioral problem so the same <strong>class of failure</strong> does not quietly return.</p><h3>8. Treat the Genie Agent Definition as Code</h3><p>As Genie usage grows, manually managing every definition through a UI becomes difficult to govern.</p><p>A stronger production pattern is to version the definition where practical.</p><pre>domain: product_kpis<br>label: Active-use KPIs<br>owner: &lt;analyst_team&gt;<br>status: draft                # draft → active after benchmarks pass<br><br>data_readiness:              # for reviewers only, not sent to Genie<br>  tables:<br>    - analytics.gold.weekly_product_kpis<br>  refresh_cadence: weekly<br>  column_docs: yes<br>  notes: |<br>    Grain, known data-quality issues, parity evidence.<br><br>instructions: |              # short: 4 rules or fewer<br>  Always filter by product; default PRODUCT_A.<br>  Never sum PRODUCT_A and SUITE_BUNDLE.<br>  &quot;Latest week&quot; = most recent available week_ending_date.<br>  Week-over-week uses a prior-week CTE.<br><br>filters:<br>  - name: paid_only<br>    code: weekly_product_kpis.plan_tier = &#39;PAID&#39;<br>    synonyms: [paying users, paid members]<br><br>measures:<br>  - name: retention_rate_pct<br>    code: |<br>      ROUND(SUM(weekly_product_kpis.retained_users) /<br>        NULLIF(SUM(weekly_product_kpis.retained_users)<br>             + SUM(weekly_product_kpis.lapsed_users), 0) * 100, 2)<br>    synonyms: [retention, retention rate]<br><br>joins: []                    # 5 or fewer, many-to-one only<br>sample_queries: [...]        # 5–10 named example SQL queries<br>common_questions: [...]      # 3–5 starter questions<br>benchmarks: [...]            # 10–15+, including &quot;should decline&quot; cases</pre><p>Now changes can follow a familiar engineering lifecycle:</p><p><strong>Definition → Review → Benchmarks → DEV → PROD</strong></p><p>You can answer:</p><ul><li>What changed?</li><li>Why did it change?</li><li>Was it reviewed?</li><li>Which benchmarks passed?</li><li>Did an important behavior regress?</li></ul><p>That is software-engineering discipline applied to conversational analytics.</p><h3>9. Define Shared Business Truth Once</h3><p>If the same metric is used by dashboards, Genie Agents, and other analytical applications, avoid independently defining it in every consumer.</p><p>Instead:</p><p><strong>Trusted Data → Governed Semantic Definitions → Dashboards + Genie Agents + Other Analytics</strong></p><p>Let the Genie Agent own what is specific to conversational behavior:</p><ul><li>domain boundaries</li><li>defaults</li><li>instructions</li><li>examples</li><li>benchmarks</li></ul><p>Keep shared business definitions reusable.</p><blockquote><strong><em>Define shared business truth once. Consume it many times.</em></strong></blockquote><p>Before promoting a Genie Agent:</p><h3>Scope</h3><ul><li>One bounded analytical domain</li><li>In-scope and out-of-scope behavior defined</li></ul><h3>Data &amp; Semantics</h3><ul><li>Trusted sources</li><li>Grain clearly documented</li><li>Deterministic metrics</li><li>Dates and freshness defined</li><li>Non-additive metrics protected</li></ul><h3>Modeling</h3><ul><li>Joins are necessary</li><li>Cardinality is understood</li><li>Grain mismatches are protected</li></ul><h3>Quality</h3><ul><li>Benchmark suite exists</li><li>Ambiguity and failure cases included</li><li>Time logic validated</li><li>Regression coverage maintained</li></ul><h3>Operations</h3><ul><li>Changes are version controlled</li><li>Parity and freshness are monitored</li><li>Production failures become benchmarks</li></ul><p>If several of these are missing, <strong>another prompt is probably not the answer.</strong></p><h3>The Bigger Lesson</h3><p>The visible part of a Genie Agent is the conversation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ysaWjsRLSwRUOU6mDan3qw.jpeg" /></figure><p>The real engineering is underneath it:</p><p><strong>Trusted Data → Governed Semantics → Specialized Genie Agent → Benchmarks → Controlled Release → Production Validation</strong></p><p>Production-grade Genie Agents sit at the intersection of <strong>data architecture, semantic modeling, AI, and software engineering</strong>.</p><p>Get those foundations right, and the Genie Agent has much less ambiguity to resolve.</p><blockquote><strong><em>A Genie Agent is only as trustworthy as the analytical system underneath it. Treat it as a production data product, with a contract, tests, observability, and controlled change.</em></strong></blockquote><p>That is how you move from an impressive demo to conversational analytics that analysts can actually trust.</p><p>Happy to exchange notes!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1a008b999ae6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Stop Orchestrating What the Platform Should Own]]></title>
            <link>https://medium.com/@rrajeshwaris/stop-orchestrating-what-the-platform-should-own-f981601f0654?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/f981601f0654</guid>
            <category><![CDATA[spark-declarative]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[databricks-pipeline]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Wed, 26 Aug 2026 01:01:37 GMT</pubDate>
            <atom:updated>2026-08-26T01:01:37.918Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F0-yf6l7WZDeyidYKu-M6A.png" /><figcaption>The left is what your engineers are maintaining. The right is what they should be writing. The difference is one architectural decision.</figcaption></figure><p><em>Declarative pipelines flip the 70/30 plumbing-to-logic ratio — and turn your quality checks into enforceable data contracts.</em></p><p>The pipeline that’s been running fine for three years just failed because someone changed a cluster policy. Here’s why that failure mode shouldn’t exist.</p><p>Every data team has the same setup: scheduler triggers a job, job spins up a cluster, notebook reads from one table and writes to another. Hardcoded cluster sizes, manual OPTIMIZE, hand-rolled retry logic, checkpoint management. It works. It’s been working for years.</p><p>But how much of that code is business logic, and how much is infrastructure choreography?</p><p>After twenty years building data platforms, the honest answer is uncomfortable: <strong>30% logic, 70% plumbing.</strong> Lakeflow Spark Declarative Pipelines — formerly Delta Live Tables (DLT), now officially <strong>SDP</strong> — flip that ratio. You declare what the data should look like. The platform handles how.</p><p><strong>Worth noting for platform architects</strong>In December 2025, Databricks contributed the core SDP technology to the Apache Spark project as part of the Spark 4.1 release. The same declarative pipeline framework is now an open Apache standard — runnable on Databricks, EMR, GCP Dataproc, or any self-managed Spark cluster. You’re not betting on a proprietary abstraction. The open-source import is from pyspark import pipelines as dp. For teams on Databricks, your existing code still works unchanged — the @dlt decorators remain valid, with dp as the forward-compatible path.</p><h3>Imperative vs. Declarative — Side by Side</h3><p>Traditional pipeline: configure cluster, read source, transform, write target, run OPTIMIZE, run VACUUM, handle failures, track state, manage checkpoints. Ten steps, six of which are infrastructure.</p><p>Declarative pipeline (Databricks SDP):</p><pre>from pyspark import pipelines as dp<br>from pyspark.sql.functions import col</pre><pre>@dp.table(<br>    name=&quot;events_silver&quot;,<br>    partition_cols=[&quot;event_date&quot;],<br>    spark_conf={<br>        &quot;pipelines.autoOptimize.zOrderCols&quot;: &quot;event_path&quot;,<br>        &quot;pipelines.autoOptimize.managed&quot;: &quot;true&quot;<br>    }<br>)<br>def silver_table():<br>    return dp.read_stream(&quot;bronze_events&quot;) \<br>        .filter(col(&quot;event_category&quot;).isNotNull()) \<br>        .selectExpr(*transformation_expressions)</pre><p>No cluster config. No manual OPTIMIZE. No checkpoint management. Z-ordering, auto-optimization, schema evolution — declared as properties, not implemented as code.</p><h3>The Performance Case</h3><p>We ran the same transformation — 53 GB input, 69 exploded columns from nested maps, date partitioning — across three configurations.</p><h4>Runtime Comparison</h4><blockquote>SDP Streaming + managed cluster<strong>56 min </strong>(23 min just scaling)</blockquote><blockquote>SDP Streaming + serverless<strong>7 min</strong></blockquote><blockquote>SDP Batch serverless (incremental)<strong>6 min</strong></blockquote><p><strong>8x improvement</strong> — not from better code, but from eliminating infrastructure wait time.</p><p><strong>On cost, </strong>Serverless is faster, but it’s not always cheaper. The DBU cost profile differs materially from managed clusters — serverless trades predictable spend for elastic capacity. For bursty, unpredictable workloads, the runtime savings often justify the premium. For steady, long-running jobs, model the economics per workload. The performance numbers above are real; your cost curve will depend on data volume and cadence.</p><p>The read-side gains are even larger. On the same silver table, comparing a DLT-managed table against a raw bronze view:</p><h4>Read Performance — Silver Table (SDP) vs. Bronze View</h4><p>Files read <strong>6 vs. 1,052–175x fewer</strong></p><p>Data scanned<strong>117 MB vs. 118 GB — 1,000x less</strong></p><p>Query runtime<strong>1 sec vs. 14 sec — 14x faster</strong></p><p>Data scanned (with join)<strong>59 MB vs. 124 GB — 2,100x less</strong></p><p>These aren’t optimizations you code. They’re table properties you declare. The platform maintains them across every write.</p><h3>From Quality Checks to Data Contracts</h3><p>Traditional approach: write data first, validate later. A downstream check catches problems hours after they’ve propagated. The validation logic lives in a separate notebook, owned by a separate team, running on a different schedule.</p><p>With SDP, quality becomes a <strong>data contract</strong> — a versioned, code-enforced agreement between the table producer and every downstream consumer. The contract ships with the pipeline. It can’t drift.</p><pre>@dp.table(name=&quot;events_silver&quot;)<br>@dp.expect(&quot;valid_category&quot;, &quot;event_category IS NOT NULL&quot;)<br>@dp.expect(&quot;valid_user&quot;, &quot;user_guid IS NOT NULL&quot;)<br>@dp.expect_or_drop(&quot;not_debug&quot;, &quot;event_category != &#39;CSDK&#39;&quot;)<br>@dp.expect_or_fail(&quot;has_timestamp&quot;, &quot;event_date IS NOT NULL&quot;)<br>def silver_table():<br>    ...</pre><p>Three enforcement levels in one declaration:</p><p>expect — log violations, write the row anyway <em>(monitoring)</em></p><p>expect_or_drop — silently discard bad rows <em>(filtering)</em></p><p>expect_or_fail — halt the pipeline <em>(circuit breaker)</em></p><p>Quality metrics are automatically tracked per run — pass/fail counts, violation rates, trend over time. No custom monitoring code. No separate quality framework. The contract <em>is</em> the framework.</p><p><strong>Inline data contracts can’t drift — they’re version-controlled with the transformation.</strong></p><p>The organizational shift is as important as the technical one. A validation notebook written six months ago checks conditions that no longer apply, misses conditions added last quarter, and runs on a different schedule than the pipeline itself. Your data quality definition no longer belongs to a separate team on a separate schedule. It belongs to the table.</p><h3>Lineage, Observability, and the Ops Case</h3><p>The engineering argument for declarative pipelines is runtime and code simplicity. The platform leadership argument is what you get for free at the operations layer.</p><p>SDP automatically generates a <strong>pipeline graph</strong> — a visual dependency map from source to every downstream table, with per-table data contract metrics, row counts, and processing history attached. No custom lineage tooling. No separate metadata catalog entry to maintain. The graph is the pipeline; it updates on every run.</p><p>When a data consumer asks “why does my dashboard show different numbers than last week?” — the answer is no longer a two-hour archaeological dig through notebook history and cluster logs. It’s a three-minute walk through the pipeline graph to find which contract expectation started failing and when.</p><p>This matters at the governance level too. Regulators, auditors, and data stewards increasingly ask for documented data lineage. In a traditional pipeline architecture, producing that documentation is a project. In a declarative architecture, it’s a screenshot.</p><h3>Schema Evolution and Team Skill Implications</h3><p>Schema changes in the source — a new column, a widened type, a restructured nested field — are one of the top causes of silent data corruption in traditional pipelines. The usual pattern: someone writes a mergeSchema call, someone else forgets to propagate it, the pipeline silently drops a column for six weeks.</p><p>With SDP, the evolution policy is declared once and enforced by the platform:</p><pre>spark_conf={&quot;pipelines.schemaEvolution&quot;: &quot;addNewColumns&quot;}</pre><p>The column appears. The pipeline doesn’t crash. The downstream consumer sees it on the next query.</p><p>This is also where the team skill story changes. When pipelines are declarative, a junior engineer can write a production-ready silver table without understanding Spark execution plans or checkpoint state. The platform absorbs that complexity — new team members contribute faster, blast radius of mistakes is smaller.</p><p>The tradeoff: when something genuinely goes wrong, there are fewer debugging handholds for engineers who think in Spark DAGs. The failure message is usually at the data level (“contract expectation failed,” “schema mismatch”) rather than the infrastructure level — which is actually easier to diagnose, but it’s a different skill set.</p><p><strong>Treat SDP as a capability floor-raiser, not a senior engineer replacement.</strong></p><h3>A Note on Privacy-Safe Streaming</h3><p>One pattern worth calling out separately: when upstream tables receive compliance deletes — GDPR erasures, account closures — the default streaming behavior can trigger a full refresh of your downstream silver table. A routine compliance operation becomes a multi-hour pipeline outage.</p><p>skipChangeCommits solves this. It&#39;s a one-line option that deserves its own treatment — I&#39;ll cover the full pattern, the failure mode it prevents, and how to design the two-layer deletion architecture that keeps both pipeline stability and data subject rights intact in the next post in this series.</p><h3>When to Use SDP</h3><p><strong>Bronze-to-silver transformations.</strong> Heavy column work — exploding nested maps, flattening structures, building derived fields. The transformation is the logic. Everything else is infrastructure noise.</p><p><strong>Scheduled incremental loads.</strong> Source is append-only, pipeline runs on a cadence. Platform tracks what’s been processed. No manual watermark logic.</p><p><strong>Read-heavy tables.</strong> Auto-optimize + z-ordering + deletion vectors produce dramatically faster reads without any maintenance code.</p><p><strong>Tables with formal data contract requirements.</strong> Where downstream consumers need a published, enforceable, version-controlled quality guarantee.</p><h3>When NOT to Use SDP</h3><p><strong>Complex orchestration with external dependencies.</strong> Workflow logic, not transformation logic. Keep your orchestrator.</p><p><strong>Non-standard backfills.</strong> “Backfill the last 90 days but don’t touch anything older” means fighting the framework. Run backfills as a separate batch and merge in.</p><p><strong>Multi-cadence outputs from one source.</strong> Silver (hourly) + gold (daily) + export (weekly) = three pipelines, not one.</p><p><strong>Deep existing orchestration investment.</strong> 100+ Airflow DAGs with a well-understood monitoring setup — migrate selectively, not wholesale.</p><h3>The Decision in Three Questions</h3><ol><li><strong>What percentage of the code is transformation vs. infrastructure?</strong> More than half is plumbing? Go declarative.</li><li><strong>How complex is the orchestration?</strong> “Read, transform, write” = SDP wins. “Branch, notify, write to two places” = keep your orchestrator.</li><li><strong>Does the team need to understand infrastructure to operate the pipeline?</strong> If debugging requires knowledge of cluster autoscaling or checkpoint state, SDP gives you a better failure mode — data contract failures, not infrastructure failures.</li></ol><h3>The Pragmatic Migration Path</h3><p><strong>Start with your next new pipeline.</strong> No migration cost, no risk.</p><p><strong>Convert your worst boilerplate offenders first.</strong> The pipeline where someone spends four hours a month on manual OPTIMIZE and cluster debugging.</p><p><strong>Migrate observability-sensitive pipelines second.</strong> If your governance team keeps asking for lineage documentation — SDP solves that for free.</p><p><strong>Sequence by characteristics, not by team.</strong> Good first candidates: single source, single target, append-heavy, read by many downstream consumers. Poor first candidates: multi-source joins with conditional logic, backfill-heavy tables, pipelines with hard external SLAs.</p><p><strong>Leave complex orchestration alone.</strong> Your 15-step DAG with external sensors is fine as-is.</p><p><strong>Every pipeline should be the simplest version of itself that meets the requirements.</strong></p><p>The plumbing your engineers are hand-rolling today — cluster management, checkpoint state, data contracts, schema evolution handling — is solved infrastructure. The platform can own it. Your team should be spending that time on the 30% that actually differentiates your data product.</p><p><em>Next in this series: How a single Spark option prevents GDPR compliance deletes from taking down your streaming pipeline — and how to design the two-layer deletion architecture that keeps both pipeline stability and data subject rights intact.</em></p><p><em>This is part of a series on data engineering architecture decisions — the kind that don’t show up in documentation but shape everything downstream.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f981601f0654" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Your AI Isn’t Hallucinating. Your Data Just Never Learned to Explain Itself.]]></title>
            <link>https://medium.com/@rrajeshwaris/your-ai-isnt-hallucinating-your-data-just-never-learned-to-explain-itself-d6b920340e4f?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/d6b920340e4f</guid>
            <category><![CDATA[ai-ready-data]]></category>
            <category><![CDATA[semantic-layer]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Wed, 08 Apr 2026 04:08:57 GMT</pubDate>
            <atom:updated>2026-04-08T04:08:57.249Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wb_yz86s51j9ndy3TWf53A.png" /></figure><p><em>Five layers of foundational work that turn AI hallucinations into accurate answers — and how to build them with AI itself.</em></p><blockquote><strong>TLDR:</strong> AI-powered analytics fails not because the model is bad, but because enterprise data is built to be understood by the people who built it — not by machines. Cryptic column names, tribal join logic, and metric definitions that live only in analysts’ heads give AI nothing to work with. The fix is a five-layer foundation: clean up unused tables with an AI agent, annotate what remains with AI-assisted metadata, mine your query history to discover which metrics actually matter, encode those metrics as executable semantic definitions (not wiki pages), and only then enable self-service. Each layer is an AI engineering problem — not a janitorial one. Build them in parallel, and you can go from broken AI analytics to a trusted self-service layer in approximately eight weeks.</blockquote><p>Every data engineering team I talk to is building the same thing: an AI-powered way for stakeholders to ask questions in plain English. The pitch is irresistible — someone types a question, AI returns the answer, no analyst in the loop.</p><p>Here’s what actually happens. Someone asks: “What’s our Gen AI feature adoption this quarter?” The AI returns a number. It looks right, cites the table it queried, and is completely wrong — wrong metric, wrong table, wrong definition of “adoption.” Someone else pulls a different number from a dashboard. Two confident answers, no way to know which is right.</p><p>I’ve been building data platforms for over twenty years. The pattern is always the same: a powerful new tool arrives, everyone plugs it into existing data, and the tool gets blamed when results don’t make sense. The problem is never the tool. The problem is that nobody prepared the data to be understood by anything other than the people who built it.</p><h3>The Problem Nobody Names</h3><p>Medallion architecture. Multiple products, hundreds of tables, trillions of rows. Leadership wants Gen AI adoption. Product wants feature engagement. Growth wants retention correlations. Every question triggers an analyst spinning up a notebook, writing custom SQL from memory, delivering a one-off answer — calculated slightly differently from the last one.</p><p>This isn’t a data quality problem. The data is accurate. It’s a <strong>data comprehension</strong> problem. Cryptic column names mean nothing to anyone who didn’t build the table. Join logic is tribal knowledge. The relationship between a telemetry event and a business metric exists only in someone’s head.</p><p>Now point an AI at this and ask it a question.</p><h3>Why the AI Gets It Wrong</h3><p>To answer “What’s Gen AI feature adoption this quarter?” the AI has to resolve: which table? What does “adoption” mean — first use, repeat use, any interaction, paid users only? What does “this quarter” mean — calendar, fiscal, rolling 90 days? Which join key, which grain?</p><p>Without guidance, it guesses. It picks a table by name similarity, infers metric logic from column names, constructs a join from common fields, and returns an answer with full confidence. Plausible. Well-formatted. Wrong — because the actual definition exists nowhere in the platform. It’s in a six-month-old notebook. Or an outdated wiki. Or someone’s head.</p><blockquote><strong>The AI isn’t hallucinating because it’s a bad model. It’s hallucinating because you gave it data without meaning.</strong></blockquote><h3>When the Knowledge Base Becomes the Problem</h3><p>The instinctive fix is a knowledge base — hand-written metric definitions, business glossaries, Confluence pages connected to documentation so definitions stay fresh. The intent is right. The failure mode is predictable.</p><p>A Confluence page that says “feature engagement is users with at least one interaction per week” was accurate when someone wrote it. Then the pipeline was updated to exclude free trial users and nobody updated the wiki. Or two teams each wrote their own definition, both correct in context, contradictory when combined. The AI consumes both with total confidence, picks one, and answers with certainty.</p><p><strong>Any system where the metric definition lives separately from the metric computation will eventually drift.</strong> A wiki page has no audit trail — you can’t see when the definition changed, who changed it, or whether it still matches the pipeline. A metric view in Unity Catalog does: every change is versioned, reviewable, and governed through the same process as your code. The definition and the computation are the same artifact. That’s the only durable fix.</p><h3>The Foundation Nobody Wants to Build (Until You Make It Exciting)</h3><p>The fix isn’t a better model, a smarter prompt, or a more expensive API. It’s foundational work — making your data explain itself. And here’s the twist: each layer is an opportunity to deploy AI in a way that produces immediate, visible value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oF1HMkKc_pt-c4VYMD-4NA.jpeg" /></figure><h3>Layer 1: Clean Up the Noise — With an AI Data Modernization Agent</h3><p>Before you describe your data to AI, reduce what there is to describe. In any platform that’s been running for years, a significant percentage of tables are unused — legacy experiments, abandoned pipelines, one-off analyses that became permanent. Every unnecessary table is noise the AI has to navigate.</p><p>Build a <strong>Data Modernization Agent</strong> that does the detective work automatically. On Delta Lake, DESCRIBE HISTORY gives you every read, write, and optimize operation with timestamps and user identity — your primary signal for staleness. Layer in Unity Catalog&#39;s system.access.table_lineage for downstream consumer tracking, cross-reference active code repos, and combine signals into a confidence score: &quot;Zero reads in 8 months, no writes in 6, no repo references, no downstream lineage — safe to archive.&quot;</p><p>The agent produces a ranked evidence report. An engineer makes the call. Research that used to take weeks takes minutes. You’re not doing janitorial work — <strong>you’re building an intelligent system that continuously monitors the health of your data estate.</strong></p><h3>Layer 2: Describe What Remains — With AI-Assisted Authoring</h3><p>Once you’ve reduced the surface area, annotate what’s left. Every table needs a description. Every non-obvious column needs a description. Business terms need mapping to technical column names.</p><p>Critically, these descriptions must live in the data platform itself — as catalog metadata — not in a wiki. AI tools consume catalog metadata natively when generating queries. A wiki page is invisible to them.</p><p>AI can bootstrap this: modern platforms draft descriptions from column names, sample data, and data types. An engineer refines. What used to take weeks takes hours. This step alone lifts AI query accuracy from roughly 40% to 60–65%. Meaningful, but not sufficient.</p><h3>Layer 3: Mine Query Patterns — With a Query Genome Agent</h3><p>Before defining governed metrics, find out which metrics actually matter. <strong>Your platform already knows — it’s in the query history.</strong></p><p>Build a <strong>Query Genome Agent</strong> that analyzes how your organization actually queries its data. Think of it as genomics for your platform — mapping the DNA of how your organization thinks about data, revealing patterns no single analyst can see:</p><ul><li><strong>Repeated query shapes</strong> — the top 20 most-repeated patterns across all users are your governed metric candidates.</li><li><strong>Join pattern discovery</strong> — which tables are joined most frequently, on which keys. This is your semantic layer’s relationship map.</li><li><strong>Divergence detection</strong> — where analysts compute the same apparent metric with subtly different logic. Different WHERE clauses, different date windows, different grain. This surfaces the “which number is right?” problem with data, not anecdotes.</li><li><strong>Inefficiency identification</strong> — full table scans without partition filters, repeated scans that should be materialized. Immediate cost impact.</li></ul><p>The Query Genome Agent doesn’t just produce a report — it produces the blueprint for your semantic layer. “Based on 847 queries in the last 3 months, here are the three most common ways people calculate feature engagement. Here’s where they agree. Here’s where they diverge. Which one is the governed definition?” Now you have data driving the hard stakeholder conversation, not opinions.</p><h3>Layer 4: Define the Semantics — Grounded in Evidence</h3><p>This is the layer almost everyone skips, and the one that changes everything.</p><p>Table descriptions tell AI what the data <em>is</em>. Semantic definitions tell AI how the data <em>computes into business metrics</em>. These are fundamentally different things.</p><p>A description says: “This column contains the count of distinct users who interacted with a feature.”</p><p>A semantic definition says: “Feature engagement rate is COUNT(DISTINCT user_id) WHERE interaction_count &gt; 0 in the trailing 7-day window, from the product activity table, filtered to active subscriptions, sliceable by product, platform, and segment.”</p><p>Without semantic definitions, every query re-implements metric logic. Ten analysts produce ten slightly different versions of the same metric. AI does the same — implementing its own version with no guardrails.</p><p>Because you built Layer 3, the semantic layer isn’t built from scratch — it’s built from evidence. Modern platforms make this executable: metric views that declare measures and dimensions in SQL, governed table relationships, and AI-specific guardrails. The definition <em>is</em> the computation. Every change is versioned and auditable. When AI is grounded in semantic definitions, accuracy jumps to 85–95% — not because the model got smarter, but because it stopped guessing.</p><h3>Layer 5: Enable Self-Service</h3><p>Only now — with clean data, described tables, and governed metric definitions — does self-service actually work. A natural language interface backed by a semantic layer doesn’t guess. It looks up the governed definition, generates conforming SQL, and returns an answer that matches every dashboard and every analyst.</p><p>In practice, this takes three forms:</p><p><strong>Databricks Genie Spaces</strong> — the most direct path on Databricks. A natural language interface grounded in your metric views, with verified example queries and guardrails you control. An analyst or stakeholder types a question and gets a governed answer. Highest-trust surface because it runs inside the same platform enforcing your semantic definitions.</p><p><strong>Slack bot</strong> — the highest-adoption surface for operational teams. Product managers, analysts, and growth teams live in Slack. A bot that answers “what was feature engagement last week by product?” in the channel where the conversation is already happening removes all friction. The Slack interface is just a delivery mechanism — the governed logic underneath ensures consistency regardless of who asks.</p><p><strong>Databricks Apps for leadership</strong> — a curated experience for executives who want clarity, not a chat interface. A lightweight app surfacing the six metrics leadership actually tracks — auto-refreshed, access-controlled, with follow-up question capability. No dashboard tool license, no “which version is current?” conversations.</p><p>The right answer for most organizations is all three — Genie for analysts who want flexibility, Slack for teams who want speed, Apps for leadership who want clarity. All three draw from the same semantic layer. All three return the same numbers.</p><p><strong>The cost trap.</strong> Most custom query interfaces run SQL live against your warehouse on every question. At pilot scale nobody notices. At production scale — hundreds of questions a day — you’re running uncontrolled ad-hoc queries at AI speed. Platform-native semantic layers work against periodically refreshed materializations. The per-query cost difference can be an order of magnitude. This is an architectural choice at Layer 4, not an optimization you add later.</p><blockquote><strong>The access control trap.</strong> Custom interfaces typically run through a single service account — one set of credentials with access to everything the bot needs. A user without direct table access can get that data through the AI interface. The interface becomes a permissions bypass. Platform-native semantic layers inherit the platform’s access controls: row-level security, column masking, and role-based access all apply automatically because it’s the same system. Every query runs under the requesting user’s permissions, not a service account. When your data includes PII or financial data, this isn’t a nice-to-have — it’s a requirement that’s nearly impossible to retrofit into a custom-built system.</blockquote><h3>Turning Legacy Into Leverage</h3><p>The real reframe: the foundational work isn’t a tax you pay before you get to the AI. <strong>The foundational work is the AI.</strong></p><ul><li>Layer 1 isn’t “clean up old tables.” It’s “build an agent that continuously monitors platform health.”</li><li>Layer 2 isn’t “write descriptions in a spreadsheet.” It’s “use AI to bootstrap a living metadata catalog.”</li><li>Layer 3 isn’t “guess which metrics to govern.” It’s “build an agent that discovers metric patterns from actual usage.”</li><li>Layer 4 isn’t “manually define metrics.” It’s “build governed semantic definitions from the evidence Layer 3 produced.”</li><li>Layer 5 isn’t “deploy a chatbot.” It’s “enable a self-service layer grounded in everything the previous four layers produced.”</li></ul><p>Each layer produces an AI-powered capability valuable on its own. Each makes the next more effective. Each is something you can demo, measure, and show impact on. A team of 2–3 engineers can run all five layers in parallel and have a governed self-service experience running in approximately eight weeks.</p><h3>The Virtuous Cycle</h3><p>Build it right, and the system improves the more people use it:</p><ol><li><strong>AI builds the foundation</strong> — agents discover unused tables, mine query patterns, draft metric definitions from evidence</li><li><strong>The foundation makes AI accurate</strong> — semantic definitions ground queries in governed logic</li><li><strong>Accurate AI drives adoption</strong> — users trust the results and keep using it</li><li><strong>Adoption generates more query data</strong> — which the Query Genome Agent mines to improve the semantic layer further</li></ol><p>Build it wrong — or skip the foundation — and the system gets worse the more people use it, because every wrong answer erodes trust further.</p><h3>The Takeaway</h3><p>The race to deploy AI-powered analytics is creating a generation of systems that are impressively fast at delivering wrong answers. The teams that will win aren’t the ones with the best models or the most sophisticated prompts. They’re the ones who used AI itself to make their data self-describing.</p><p>AI doesn’t hallucinate because it’s unreliable. It hallucinates because it was asked to interpret data that never learned to explain itself.</p><p>The fix is foundational and systematic — but it doesn’t have to be boring. Build the foundation with AI, and the foundation becomes the most exciting work on the roadmap.</p><p>I’m a Senior Data Engineering Manager with 20+ years building data platforms at enterprise scale. I write about the architectural decisions and engineering principles behind systems that are built to last</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d6b920340e4f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Stop Attending Their Meetings]]></title>
            <link>https://medium.com/@rrajeshwaris/stop-attending-their-meetings-c8b9e1bc4f20?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/c8b9e1bc4f20</guid>
            <category><![CDATA[leadership-skills]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Mon, 06 Apr 2026 03:21:46 GMT</pubDate>
            <atom:updated>2026-04-06T03:56:27.617Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BHm3HENcapNNY2JoYalMKg.png" /><figcaption>She’s not leaving. She’s leading.</figcaption></figure><p><em>How I transform data engineers from task-takers to technical owners — and why deliberate absence is sometimes the most powerful management move.</em></p><p>Twenty plus years of managing engineering teams has taught me one counterintuitive truth: the moment I walk into a meeting, I’ve already changed the outcome — not because I’m the smartest person in the room, but because I’m seen as the decision maker. And that perception alone is enough to silence the very people I need thinking.</p><p>I’ve watched this play out across multiple roles and organizations. The fix isn’t harder or softer management — it’s a deliberate separation between where I work and where they lead.</p><p>My model: I provide technical direction, platform advocacy, and guardrails. They own the forum, the stakeholder relationship, and the outcome. When it works, innovation returns — not because I gave a team speech, but because ownership creates accountability, and accountability creates curiosity.</p><p>The hardest part isn’t the strategy. It’s tolerating the discomfort of not knowing what’s being decided in real time — the instinct to protect, to be visible, to course-correct before something goes sideways. Most managers stay in the room because absence feels like abdication. I’ve learned it’s the opposite. Absence, when earned, is the signal that you trust the person completely. And people rise to that signal in ways they never rise to oversight.</p><h3>What I Actually Do — It Isn’t Passive</h3><p>The absence in the right meetings is not the work. The work that makes it possible happens before any standup.</p><h3>I recommend solutions before problems compound.</h3><p>The most valuable technical interventions happen before the mess is obvious. When we started building ML pipelines at scale, I flagged early that we’d accumulate redundant features across models without a feature store pattern from the start. I raised it with the engineer leading the work, made the case, and stepped back. That engineer designed and implemented the solution. It is already paying back in reduced duplication and faster experimentation.</p><p>Similarly, I proposed that both our cloud cost monitoring and active period of editing computation be built as configurable frameworks — not hardcoded one-offs. Each became a project a team member led end to end. The design principle was my input; the solution and ownership were entirely theirs.</p><h3>I revive SDLC — and make it theirs.</h3><p>Modern data engineering moves fast, but “fast” can quietly mean skipping the requirements doc and the deployment checklist. I’ve brought structured SDLC practices back — playbooks covering the full arc from requirements and design to testing and production deployment — and made them team-owned, not manager-enforced.</p><p>The signal that it worked: engineers now propose solutions independently and structure their own trade-off analysis. A recent example was a proposal to build an SCD2 solution for tracking changes on a third-party model integration — the team brought forward the options, debated json map vs. SCD2, storage cost vs. auditability, and chose an approach based on the outcome, not who suggested it.</p><h3>I structure 1–1s for depth, not status.</h3><p>Every 1–1 follows a consistent format: blockers first, then prioritization, then a technical brainstorming block, then career support. The brainstorming slot is where I share platform direction, patterns from past roles, or emerging architectural decisions. It is not where I tell someone what to build — it is where I give them the context to make better decisions on their own.</p><h3>Example 1: Privacy Automation Framework — Raising the Bar, Not Just the Ticket Count</h3><p>The default answer to privacy compliance at scale is usually a manual one: engineers run deletion jobs on a schedule, check logs by hand, and hope nothing drifted. I pushed the team to think differently.</p><p>My question was: why are we solving this table by table when the pattern is identical across hundreds of tables? I set the vision — a centralized automation framework that enrolled tables into a policy sync pipeline, applied deletions programmatically, and surfaced observability metrics so compliance wasn’t a faith-based exercise.</p><p>The engineer owned everything from there: architecture execution, Databricks integration, audit logging, and the monitoring layer that made the system’s health visible without anyone having to look. The result was 400+ hours of manual work eliminated per year, across petabyte-scale data, with an observable pipeline — not just a running script.</p><p><em>Vision set by me. Implementation, observability, and ownership entirely theirs.</em></p><h3>Example 2: Delta Live Tables — Declarative Pipelines Over Brittle Orchestration</h3><p>Our silver-tier pipelines were built on custom Airflow DAGs — each one a bespoke solution with its own failure modes and maintenance overhead. I advocated for a different model: declarative pipelines using Delta Live Tables, serverless, with Databricks managing the runtime so engineers spent time on data logic, not infrastructure.</p><p>I made the case — lower operational drag, unified streaming and batch, no version pinning required. The engineers took it from there. Fresco silver went from a manually managed pipeline to a serverless DLT streaming pipeline with documented 8–14x performance improvement. PSM platinum followed with a batch DLT approach.</p><p>The engineers own the SLAs, the observability, and the architecture conversations. I don’t attend those reviews. I don’t need to.</p><p><em>Vision set by me. Implementation and domain expertise entirely theirs.</em></p><h3>How I Know It’s Working</h3><ul><li>Engineers propose solutions I hadn’t considered</li><li>Trade-off conversations happen in design docs, not in my 1–1</li><li>Annual reviews reflect “minimal supervision” and “self-directed” as consistent patterns, not exceptions</li><li>I am informed about forum outcomes — I am not required for them</li></ul><h3>The One Thing I’d Tell Another Engineering Manager</h3><p>Staying out of the room is a skill you have to earn — through your architecture work, your platform advocacy, and your SDLC scaffolding. Do that work visibly and early. Build the playbooks. Recommend the patterns. Set the vision.</p><p>Then leave.</p><p>The innovation isn’t waiting for your permission. It’s waiting for the room to clear.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c8b9e1bc4f20" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Funnel Analysis Pattern That Makes Data Scientists’ Queries Churn]]></title>
            <link>https://medium.com/@rrajeshwaris/the-funnel-analysis-pattern-most-data-scientists-get-wrong-2b1891f2baf3?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/2b1891f2baf3</guid>
            <category><![CDATA[funnel-analysis]]></category>
            <category><![CDATA[modern-data-architecture]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql-optimization]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Thu, 19 Mar 2026 22:38:30 GMT</pubDate>
            <atom:updated>2026-03-19T23:00:17.291Z</atom:updated>
            <content:encoded><![CDATA[<h3>Build Your Funnel Upside Down</h3><p><em>How to stitch multi-table funnel data accurately —</em>A step-by-step pattern for bottom-up stitching, parameterized windows, and production-ready gold tables</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*H4yfqQMnUwLW2pDRFNW-4Q.png" /></figure><p>Funnel analysis sounds simple: track users from discovery to conversion. In practice, you’re joining across multiple event tables with hundreds of millions of rows, and every new funnel variant means rewriting SQL from scratch.</p><p>The fix isn’t better SQL. It’s join order.</p><p>By the end of this post you will know how to:</p><p>— Reduce table scans by 99% using bottom-up stitching</p><p>— Avoid data leakage by anchoring date windows on every CTE</p><p>— Eliminate inflated counts by choosing first, last, or all conversions explicitly</p><p>— Structure queries that run weekly and backfill cleanly</p><p>— no rewrites — Write idempotently to a gold table safe for repeated pipeline runs</p><p>— Layer a materialized view on top of the gold table for fast dashboard queries</p><p>Here is what we are building:</p><p>events.feature_events ─┐</p><p>events.product_events─┼─→ Scheduled job (weekly, backfillable)</p><p>core.users. ─┘</p><p>↓</p><p>gold.feature_funnel_weekly</p><p>↓</p><p>Materialized view (auto-refresh)</p><p>↓</p><p>gold.feature_funnel_daily (dashboards, stakeholder queries)</p><h3>The problem with top-down</h3><p>Most people start from the biggest table and filter down. For a typical funnel across three event sources, that means scanning 500M+ rows before you find the 200K users you actually care about — across the full lifetime of the table.</p><pre>Step  Table           Rows scanned<br>───────────────────────────────────<br>1     All events      500M+<br>2     Interactions    200M+<br>3     Conversions     200K</pre><p><strong>Total: 800M+ rows to answer a 200K-row question.</strong></p><h3>The fix: start from the bottom</h3><p>Flip the join order. Start with the smallest table — the users who reached the conversion event — and join upward.</p><pre>Step  Table           Rows processed<br>──────────────────────────────────────<br>1     Conversions     200K  ← start here<br>2     Interactions    180K  matched<br>3     All events      195K  matched</pre><p><strong>Total: ~575K rows. Same answer. 99% less scanning.</strong></p><p>Why it works: you’re carrying a small set of user keys at each join. The query optimizer broadcasts these instead of shuffling hundreds of millions of rows across your cluster.</p><h3>Applying this to a real funnel</h3><p>Say you’re tracking the user journey to a conversion event. Your data lives across two tables: events.feature_events and events.product_events.</p><p><strong>Don’t do this:</strong></p><pre>-- Top-down: scans everything first<br>SELECT * FROM events.feature_events       -- 500M+ rows, full table<br>JOIN events.product_events USING (user_id)<br>WHERE event = &#39;buy_now_click&#39;</pre><h3>Step 1: parameterize your window — never use CURRENT_DATE</h3><p>⚠️ Critical with backward stitching: each upstream table has no inherited date context from the previous join. Without an explicit filter on every CTE, you scan the full history of every table. And since this runs as a weekly scheduled job — not a one-time query — hardcoding CURRENT_DATE makes backfilling impossible.</p><p>Pass the window as parameters so the same query powers both the weekly refresh and any historical backfill:</p><pre>-- Parameters set by your scheduler (Airflow, dbt, cron, etc.)<br>SET run_date      = &#39;2024-02-05&#39;;  -- week ending date, controlled by scheduler<br>SET lookback_days = 7;             -- always 7 for weekly runs</pre><pre>-- To backfill 6 months: loop run_date over historical week-ending dates.<br>-- Keep lookback_days = 7 on every iteration — do NOT widen it.<br>-- Move the window backward by stepping run_date, not by expanding lookback_days.</pre><pre>Weekly run:   run_date steps forward by 7 days each Monday<br>              2024-01-08 → 2024-01-15 → 2024-01-22 → ...</pre><pre>Backfill run: scheduler loops run_date over past N weeks<br>              2023-08-07 → 2023-08-14 → ... → 2024-02-05<br>              (lookback_days stays at 7 on every loop iteration)</pre><h3>Step 2: decide whose conversion you want</h3><p>Before writing a single line of SQL, answer this: for users who converted multiple times, which event do you want?</p><pre>Choice              Use when<br>──────────────────────────────────────────────────<br>First conversion    Onboarding funnels, first-touch<br>Last conversion     Most recent engagement analysis<br>All conversions     Volume and frequency analysis</pre><p><strong>This choice changes your SQL.</strong> The default (all rows) is rarely what you want and silently inflates funnel counts.</p><h3>Step 3: write the query for your defined use case</h3><p><strong>Our use case:</strong> did a user land in the feature within their first 4 weeks of account creation? We want their <em>first</em> conversion only.</p><p>Two windows to keep distinct:</p><p>Scan window (run_date — lookback_days to run_date) — bounds what you read from each table. Must be at least as wide as the onboarding window. Here we use run_date — 35 days (28-day onboarding + 7-day buffer) so landing events early in a user’s onboarding are never excluded by the scan.</p><p>Onboarding window (signup_ts + 28 days) — the business rule for qualifying users. Independent of the scan window.</p><pre>-- Bottom-up: first conversion, parameterized for weekly runs and backfill<br>WITH first_converters AS (<br>  -- anchor: one row per user, earliest conversion only<br>  -- scan window covers the full 28-day onboarding window + buffer<br>  SELECT<br>    user_id,<br>    MIN(event_ts) AS first_conversion_ts<br>  FROM events.feature_events<br>  WHERE event      = &#39;buy_now_click&#39;<br>    AND event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date<br>  GROUP BY user_id<br>),<br>new_users AS (<br>  -- restrict to users whose first conversion fell within 4 weeks of signup<br>  SELECT<br>    fc.user_id,<br>    fc.first_conversion_ts,<br>    u.created_at AS signup_ts<br>  FROM first_converters fc<br>  JOIN core.users u USING (user_id)<br>  WHERE fc.first_conversion_ts &lt;= u.created_at + INTERVAL &#39;28 days&#39;<br>),<br>engaged AS (<br>  -- upstream join: matched users only, within onboarding window<br>  SELECT p.user_id, p.event, p.event_ts<br>  FROM events.product_events p<br>  JOIN new_users n USING (user_id)<br>  WHERE p.event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date<br>    AND p.event_ts &lt;= n.signup_ts + INTERVAL &#39;28 days&#39;<br>),<br>discovered AS (<br>  -- top of funnel: feature landing must precede conversion<br>  SELECT f.user_id, f.event, f.event_ts<br>  FROM events.feature_events f<br>  JOIN new_users n USING (user_id)<br>  WHERE f.event      = &#39;feature_landing&#39;<br>    AND f.event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date<br>    AND f.event_ts   &lt; n.first_conversion_ts   -- enforces event ordering<br>    AND f.event_ts  &lt;= n.signup_ts + INTERVAL &#39;28 days&#39;<br>)<br>SELECT<br>  n.user_id,<br>  n.signup_ts,<br>  d.event_ts  AS landing_ts,<br>  e.event_ts  AS engaged_ts,<br>  n.first_conversion_ts,<br>  $run_date   AS pipeline_run_date  -- audit: the scheduler date, not the event date<br>FROM new_users n<br>JOIN discovered d USING (user_id)<br>JOIN engaged    e USING (user_id)</pre><p>Four things this pattern enforces that naive funnel SQL misses:</p><ul><li><strong>Parameterized window</strong> — same query runs weekly and backfills; no hardcoded dates</li><li><strong>Scan window wider than onboarding window</strong> — 35 days covers the full 28-day rule with buffer</li><li><strong>First conversion only</strong> (MIN(event_ts)) — one row per user, no inflated counts</li><li><strong>Event ordering enforced</strong> (event_ts &lt;) — upstream events must precede conversion</li></ul><h3>Step 4: write to your gold table idempotently</h3><p>Since this runs on a schedule, the output table must handle reruns safely. Always delete the current window before inserting so reruns don’t double-count:</p><pre>-- Idempotent write: delete then reinsert for the current run window<br>DELETE FROM gold.feature_funnel_weekly<br>WHERE pipeline_run_date = $run_date;</pre><pre>INSERT INTO gold.feature_funnel_weekly<br>SELECT<br>  n.user_id,<br>  n.signup_ts,<br>  d.event_ts  AS landing_ts,<br>  e.event_ts  AS engaged_ts,<br>  n.first_conversion_ts,<br>  $run_date   AS pipeline_run_date<br>FROM new_users n<br>JOIN discovered d USING (user_id)<br>JOIN engaged    e USING (user_id);</pre><p>⚠️ pipeline_run_date is the scheduler date for that run — not the event date. On a backfill, rows from August 2023 will correctly carry pipeline_run_date = ‘2023–08–07’ only if the scheduler passes the historical date. Always verify your scheduler is passing the correct historical run_date and not today’s date when backfilling.</p><h3>The output: materialized view on top of the gold table</h3><p>gold.feature_funnel_weekly is your stitched, parameterized output — built by the scheduled job above. On top of it, define a materialized view for daily aggregation. This is what dashboards and stakeholders query — never the raw event sources, never the stitching layer directly.</p><pre>CREATE MATERIALIZED VIEW gold.feature_funnel_daily AS<br>SELECT<br>  CAST(w.first_conversion_ts AS DATE)  AS event_date,<br>  u.platform,<br>  COUNT(DISTINCT CASE WHEN w.landing_ts  IS NOT NULL THEN w.user_id END) AS landed,<br>  COUNT(DISTINCT CASE WHEN w.engaged_ts  IS NOT NULL THEN w.user_id END) AS engaged,<br>  COUNT(DISTINCT w.user_id)                                               AS converted,<br>  ROUND(COUNT(DISTINCT w.user_id) /<br>    NULLIF(COUNT(DISTINCT CASE WHEN w.landing_ts IS NOT NULL<br>                               THEN w.user_id END), 0), 4)               AS conversion_rate<br>FROM gold.feature_funnel_weekly w<br>JOIN core.users u USING (user_id)<br>GROUP BY event_date, u.platform;</pre><pre>-- Refresh after every weekly job run<br>-- Schedule this immediately after the gold.feature_funnel_weekly insert completes<br>REFRESH MATERIALIZED VIEW gold.feature_funnel_daily;</pre><p>⚠️ Always refresh the MV after the scheduled job completes — not on a separate independent schedule. If the MV refreshes before the weekly job finishes writing, dashboards will show stale or partial data for that run window. In Airflow, chain it as a downstream task:</p><pre>weekly_funnel_job  →  refresh_funnel_daily_mv  →  notify_stakeholders</pre><p>Why a materialized view here and not another scheduled job:</p><pre>gold.feature_funnel_weekly  →  needs a scheduled job<br>                                parameterized ($run_date, $lookback_days)<br>                                backfillable, idempotent, complex joins</pre><pre>gold.feature_funnel_daily   →  materialized view is the right fit<br>                                single source (gold table only, no joins across raw tables)<br>                                simple aggregation — no runtime parameters needed<br>                                auto-refreshes as gold table is updated</pre><h3>Checklist before you build</h3><pre>Question                                       Good answer<br>────────────────────────────────────────────────────────────────────<br>Which table has the fewest rows?               Start joins there<br>Joining top-down across large tables?          Flip to bottom-up<br>Date filter on every CTE?                      Yes — on every single one<br>Scan window wide enough for business window?   scan window ≥ business rule window<br>Using CURRENT_DATE in a scheduled job?         Replace with $run_date parameter<br>First, last, or all conversions?               Decide before writing SQL<br>Upstream events ordered before conversion?     Enforce with event_ts &lt;<br>User GUIDs in your gold table?                 Remove — aggregate only<br>Gold table write idempotent?                   DELETE + reinsert on run_date</pre><p><em>Tags: Data Engineering · Funnel Analysis · SQL · AnalyticsThe join order is the architecture. Get it right once and every funnel variant gets accurate and Data Scientist dont need to wait for the queries to churn indefinetely- Its a win-win.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2b1891f2baf3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Time-Tested: The Scalable Workflow Architecture for Active Period of Editing That Accelerated Data…]]></title>
            <link>https://medium.com/@rrajeshwaris/time-tested-the-sustainable-workflow-architecture-that-accelerated-data-science-by-20x-35e2e7038a0a?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/35e2e7038a0a</guid>
            <category><![CDATA[cloud-data-architecture]]></category>
            <category><![CDATA[modern-data-architecture]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Sun, 01 Mar 2026 04:03:53 GMT</pubDate>
            <atom:updated>2026-04-01T21:44:44.253Z</atom:updated>
            <content:encoded><![CDATA[<h3>Time-Tested: The Scalable Workflow Architecture for Active Period of Editing That Accelerated Data Science by 20x</h3><p><em>18 months, 88+ workflows, zero schema changes — how APE V2 decoupled science velocity from pipeline velocity at Adobe Photoshop.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PzWidYrHmcqmuGy0q5qSgA.jpeg" /><figcaption>Workflow MAP Architecture</figcaption></figure><p>That’s not a typo. And it’s not a pilot — it’s been running in production for <strong>18 months</strong>.</p><p>Feature instrumentation that took <strong>4 weeks</strong> now takes <strong>1 day</strong>. Experiments that used to get skipped are now designed <em>before</em> a feature ships. 88+ creative workflows tracked, zero schema changes made. And the data scientists doing that science never had to change a single query.</p><p>Here’s how we built it, and why it’s still standing.</p><p>Data Engineering differentiation mattered here — replacing a legacy column-based tradition with a novel architecture that made change effortless and science unstoppable.</p><h3>The Wall Every DS Knows</h3><p>At Adobe Photoshop, we ship creative features fast — Generative Fill, Generative Expand, AI-powered selections, neural filters. Every one needs tracking: use flags, success rates, engagement patterns.</p><p>The process: file a request → schema changes → ETL updates → view migrations → staged deployments. <strong>Four weeks later</strong>, the column exists — but the cohort has aged, the sprint has moved on, and the model training window has a break in it you’ll spend a week explaining.</p><p>When we launched our GenAI suite and needed to track <strong>88+ unique creative workflows</strong> — with new ones added weekly during beta — the breaking point arrived. Traditional approach: 88 workflows × 2 flags = 176 new columns, nearly a months to build, constant maintenance.</p><h3>The Solution: Make the Pipeline Invisible</h3><p>I proposed separating <em>what we track</em> from <em>how we store it</em> — and making sure data scientists never had to care about either.</p><p>The system, <strong>APE V2</strong>, runs on three layers:</p><pre>┌─────────────────────────────────────────┐<br>│  METADATA LAYER  (JSON Config)          │<br>│  Defines what workflows to track        │<br>│  5-minute update, zero code changes     │<br>└─────────────────────────────────────────┘<br>              ↓<br>┌─────────────────────────────────────────┐<br>│  STORAGE LAYER  (Spark MAP Column)      │<br>│  Key-value pairs, sparse and efficient  │<br>│  Unlimited workflows, zero schema locks │<br>└─────────────────────────────────────────┘<br>              ↓<br>┌─────────────────────────────────────────┐<br>│  QUERY LAYER  (Auto-generated View)     │<br>│  160+ familiar columns, standard SQL    │<br>│  Regenerates in 30 seconds from config  │<br>└─────────────────────────────────────────┘</pre><p><strong>The metadata layer</strong> is a JSON config file. Adding a new workflow means editing one file — no code, no deployment, no review cycle.</p><p><strong>The storage layer</strong> uses Spark MAP data types instead of fixed columns. Instead of storing 88 sparse columns (mostly zeros), we store only what’s actually used as key-value pairs. A user who only touched Crop and GenFill gets two entries, not 88.</p><p><strong>The query layer</strong> is where the magic hides. An auto-generated view explodes that MAP back into the 160+ familiar columns your scientists expect — regenerated from config in 30 seconds, completely transparent to anyone querying it.</p><p>What your data scientists see:</p><pre>SELECT<br>  user_id,<br>  GenFill_use_flag,<br>  GenExpand_success_flag,<br>  Crop_use_flag<br>FROM user_workflows_view<br>WHERE GenFill_use_flag = 1<br>  AND event_date &gt;= &#39;2026-01-01&#39;;</pre><p>Standard SQL. Works in every notebook, every BI tool, every model training pipeline — unchanged. When a new workflow is added, they simply wake up the next day and the column exists. No ticket filed. No four-week wait. No explanation needed.</p><h3>What 20x Actually Looks Like in Practice</h3><p>The speed improvement is measurable. The culture shift is what matters.</p><p><strong>Experiments that used to get skipped are now being run.</strong> When product teams asked “can we A/B test this new workflow?”, the old answer was “give us four weeks.” The new answer is “already tracking it, here’s yesterday’s data.” That single conversation change unlocks a completely different relationship between science and product.</p><p><strong>Features launch with analytics, not after them.</strong> During our GenAI beta, new workflows were instrumented <em>before</em> launch — not retrofitted after. Cohorts were clean from day one. Retention curves had no gap at the beginning. Model training signals were available immediately, not a month into the feature’s life.</p><p><strong>The real proof isn’t a single launch — it’s 18 months of continuous operation.</strong> Since APE V2 went live, the Adobe Photoshop data platform has absorbed wave after wave of new creative workflows: GenAI features during beta, expanded AI selection tools, new editing modes — each one instrumented within a day, none requiring a schema change. Over that period, the science team has never filed a pipeline request for a new workflow metric. Not once. That’s what sustained architectural success looks like.</p><h3>What This Means for Your Day-to-Day</h3><p>If you’re a data scientist, here’s the practical translation:</p><p><strong>Your queries don’t change.</strong> The view presents as a wide, flat table with standard column names. Write SQL the way you always have.</p><p><strong>New columns appear, nothing breaks.</strong> When workflows are added, your existing queries are unaffected. You’ll just notice new columns — populated correctly, with nulls handled via COALESCE so there are no surprises.</p><p><strong>Ask for what you actually need.</strong> The “is this worth a four-week ask?” calculation is gone. If a new workflow matters to your model, ask for it. It’ll be there tomorrow.</p><p><strong>Your training pipelines stay stable.</strong> No mid-table schema migrations, no breaking Delta schema changes, no unexpected nulls appearing mid-partition. The view layer absorbs all evolution; you see a stable interface.</p><h3>The Principle Underneath</h3><p>This pattern works because it respects a simple truth: <strong>each layer should be optimized for its consumer.</strong></p><p>Storage is optimized for engineers — flexible, sparse, efficient, future-proof. The query layer is optimized for scientists — familiar columns, standard SQL, no new mental models. The metadata layer is optimized for the business — a config file anyone can read and update.</p><p>When you design for each layer’s actual user, complexity stops being everyone’s problem and becomes nobody’s problem.</p><p><em>(Brief aside for the engineering managers reading: the cost implications are real — this architecture eliminated ~$17K per-workflow change requests. But that’s a footnote. The velocity is the story.)</em></p><h3>When This Pattern Fits</h3><p>Use it when you have <strong>high data sparsity</strong> (ours was 87–94% zeros per row), <strong>frequent new tracking requirements</strong>, or science teams visibly slowed by pipeline work.</p><p>Skip it for stable, well-understood schemas, high-frequency streaming writes, or low sparsity data where MAP overhead isn’t justified.</p><p>The best infrastructure is the kind nobody knows exists. For 18 months, our data scientists have been running more experiments, building cleaner models, and answering product questions faster than ever — using the same SQL they’ve always written.</p><p>The pipeline got out of the way. And it’s been staying out of the way ever since. That’s the whole story.</p><p><strong>Stack:</strong> Databricks Delta Lake · Apache Spark · PySpark · Delta MAP types · Dynamic SQL generation at petabyte scale</p><p><em>Raji Raghuraman is a Senior Data Science Engineering Manager at Adobe, building analytics infrastructure for Photoshop and Lightroom. </em><a href="https://www.linkedin.com/in/raji-raghuraman-b5230915/"><em>Connect on LinkedIn</em></a></p><p><strong>#DataScience #DataEngineering #Databricks #ExperimentationVelocity #AnalyticsEngineering #ApacheSpark #MLOps #ProductAnalytics</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=35e2e7038a0a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Databricks Serverless SQL Warehouse 400x Optimization Operation]]></title>
            <link>https://medium.com/@rrajeshwaris/serverless-sql-warehouse-cost-surge-and-optimization-operation-fb9c8ac22ea8?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/fb9c8ac22ea8</guid>
            <category><![CDATA[data-administrator]]></category>
            <category><![CDATA[serverless]]></category>
            <category><![CDATA[cloud-cost-optimization]]></category>
            <category><![CDATA[sql-ware]]></category>
            <category><![CDATA[databricks-op]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Mon, 19 Jan 2026 23:16:47 GMT</pubDate>
            <atom:updated>2026-03-01T04:38:33.687Z</atom:updated>
            <content:encoded><![CDATA[<h3>From $25K to $5K/month in 4 weeks: A three-phase approach using rightsizing, zero-cost endpoint monitoring, and query-level cost attribution</h3><p><strong>Read time:</strong> 10 minutes<br><strong>Savings:</strong> $240K/year | <strong>ROI:</strong> 400x | <strong>Implementation Cost:</strong> ~$50/month</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tWCB7atG7us-IJ6iBigJtA.jpeg" /></figure><h3>TL;DR: The 80% Cost Reduction Playbook</h3><p>SQL Warehouse costs surged to $25K/month. We reduced them to $5K through a three-phase approach:</p><p><strong>The Three Phases:</strong></p><ol><li><strong>Rightsizing</strong> (Week 1): $25K → $17.5K (-30%) | Cost: $0 | Time: 2 hours</li><li><strong>REST API Monitoring</strong> (Week 2): $17.5K → $15K (-40% total) | Cost: $0 | Time: 4 hours</li><li><strong>Cost Attribution</strong> (Week 3–4): $15K → $5K (-80% total) | Cost: $50/mo | Time: 2 weeks</li></ol><p><strong>Key Discovery:</strong> AI/BI dashboards with 30-second auto-refresh = 66% of costs.</p><p><strong>Results:</strong> $240K/year savings | 400x ROI | 2-week implementation</p><p><strong>Read on for the complete story and playbook.</strong></p><h3>The Discovery: $25K/Month Cost Surge</h3><p>It started with our own cost monitoring dashboard flagging an anomaly: <strong>SQL Warehouse costs had quietly jumped to $25,000/month</strong> for a single warehouse.</p><p><strong>What Led to the Surge:</strong></p><p>A few months earlier, we had upgraded our SQL Warehouse from <strong>Small to XL size</strong> to support growing demand from our data science team. At the time, we were on <strong>Databricks promotional pricing</strong>, so costs were manageable at around $6K/month. We encouraged everyone to use the SQL Warehouse for their analytics and dashboards.</p><p>Then the promotional pricing period ended — <strong>silently</strong>. The pricing tier changed, and suddenly:</p><ol><li><strong>XL warehouse daily rental costs</strong> kicked in</li><li><strong>Per-DBU usage costs</strong> increased significantly</li><li><strong>Combined effect:</strong> $6,000 → $25,000/month in 60 days</li></ol><p><strong>The Crisis:</strong></p><ul><li>📈 Cost increased 300%+ without any infrastructure changes</li><li>💰 XL warehouse size + new pricing tier = perfect storm</li><li>❓ Which workloads were actually driving the usage costs?</li><li>⏰ Need to understand and optimize immediately</li></ul><p>We needed answers. But when we looked at Databricks billing data, we hit a wall:</p><p><strong>All costs were attributed to the warehouse creator, not the individual users running queries.</strong></p><pre>-- What billing data showed us<br>SELECT <br>    identity_metadata.created_by as user,<br>    SUM(usage_quantity * price_per_dbu) as cost<br>FROM system.billing.usage<br>WHERE warehouse_id = &#39;analytics-sql-wh&#39;<br>  AND usage_date &gt;= current_date() - 30<br>GROUP BY 1;</pre><pre>Result:<br>┌───────────────────────────┬──────────┐<br>│ user                      │ cost     │<br>├───────────────────────────┼──────────┤<br>│ platform-team@yourcompany.com │ $25,000  │ ← ALL costs here!<br>└───────────────────────────┴──────────┘</pre><p><strong>Dead End. The billing data only showed:</strong></p><ul><li>❌ Warehouse creator (platform team) blamed for everything</li><li>❌ No visibility into which users were actually running queries</li><li>❌ No way to identify what type of workloads were expensive</li><li>❌ No data on query frequency, duration, or patterns</li></ul><p>We were flying blind. Time to take action.</p><h3>The Three-Phase Optimization Operation</h3><h3>Quick Visual: The Journey</h3><pre>$25,000/month ──────────────────────────────────────────&gt; $5,000/month<br>    │                                                           │<br>    │  Root Cause: XL warehouse + promotional pricing ended    │<br>    │                                                           │<br>    │  PHASE 1: Rightsizing (Week 1, $0 cost)                 │<br>    ├─────────&gt; $17,500 (-30%)                                │<br>    │                                                           │<br>    │  PHASE 2: Endpoint Monitoring (Week 2, $0 cost)         │<br>    ├─────────&gt; $15,000 (-40% total)                          │<br>    │                                                           │<br>    │  PHASE 3: Cost Attribution (Week 3-4, $50/mo cost)      │<br>    └─────────&gt; $5,000 (-80% total) ────────────────────────&gt;</pre><pre>Total Savings: $240K/year | ROI: 400x | Time: 2 weeks</pre><p>Faced with a $25K/month problem and zero visibility, we deployed a three-phase approach:</p><h3>Phase 1: Quick Wins — Rightsizing (Week 1)</h3><p><strong>Goal:</strong> Immediate cost reduction without attribution data<br><strong>Cost:</strong> $0 (configuration changes only)</p><h3>Phase 2: Zero-Cost Real-Time Alerting (Week 2)</h3><p><strong>Goal:</strong> Catch expensive queries immediately<br><strong>Cost:</strong> $0 (using Query Watchdog API + wall clock time)</p><h3>Phase 3: Deep Dive — Query-Level Attribution (Week 3–4)</h3><p><strong>Goal:</strong> Understand root causes and optimize strategically<br><strong>Cost:</strong> ~$50/month (small compute for attribution notebook)</p><p><strong>Total Result: $25,000 → $5,000/month (-80% reduction)</strong></p><p>Let’s walk through each phase.</p><h3>Phase 1: Quick Wins Through Rightsizing (Week 1)</h3><h3>What We Did (No Attribution Required)</h3><p>Before building any attribution system, we looked at basic warehouse metrics available in the Databricks UI:</p><p><strong>Observation 1: Over-Provisioned XL Warehouse</strong></p><pre>Current Warehouse Configuration:<br>- Size: XL (upgraded to support data science team)<br>- Max DBUs: 320<br>- Auto-stop: 45 minutes (way too long!)<br>- Scaling: Aggressive (1-8 clusters)<br>- Daily Rental: ~$150/day</pre><pre>Actual Usage Pattern (from UI metrics):<br>- Peak concurrent queries: 12-15<br>- Average concurrent queries: 3-5<br>- Warehouse ON: 85% of business hours<br>- But queries only running 30% of that time!</pre><p><strong>The Problem:</strong> XL warehouse size with high daily rental costs + staying ON for 45 minutes after last query + provisioned for peak load that rarely happened = massive waste.</p><p><strong>Actions Taken:</strong></p><ol><li><strong>Rightsized warehouse:</strong> XL → Small (eliminated unnecessary daily rental costs)</li><li><strong>Reduced auto-stop time:</strong> 45 minutes → 5 minutes</li><li><strong>Tool Usage Guidance:</strong> Limit SQL WH only for validating your production queries. Use Job SKUs for running exhaustive queries</li><li><strong>Query Optimization Techniques: </strong>Created code template to filter data before joining including other best practices.</li></ol><pre>Quick Wins Results (Week 1):<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━<br>Before: $25,000/month<br>After:  $17,500/month<br>Savings: $7,500/month (-30%)</pre><pre>Breakdown:<br>• XL → Large warehouse:     ~$4,500/month (daily rental reduction)<br>• Auto-stop 45m → 5m:       ~$2,000/month (idle time reduction)<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━</pre><pre>Cost: $0 (configuration changes only)<br>Time: 2 hours<br>ROI: Immediate</pre><p><strong>Result:</strong> 30% cost reduction in Week 1 through rightsizing and idle time optimization. However, we still had $17.5K/month in costs with no visibility into what workloads were driving them.</p><h3>Phase 2: Zero-Cost Real-Time Alerting (Week 2)</h3><h3>The Challenge</h3><p>We needed to catch expensive queries <strong>while they were still running</strong> (not after they finished), but:</p><ul><li>❌ Running attribution queries continuously would cost money</li><li>❌ We didn’t have query-level cost data yet</li><li>❌ Traditional monitoring solutions were expensive</li></ul><h3>The Solution: SQL Warehouse API Endpoint Monitoring</h3><p>Databricks provides a <strong>free REST API</strong> to monitor SQL Warehouse queries in real-time. We built a lightweight monitoring system that checks for <strong>currently running queries</strong> and alerts users immediately if queries exceed duration thresholds.</p><p><strong>Key Insight:</strong> By monitoring queries with &quot;statuses&quot;: [&quot;RUNNING&quot;], we can catch expensive queries <strong>while they&#39;re still running</strong> and alert users to kill them before accumulating massive costs. This uses wall clock time (duration) as a proxy for cost—no attribution needed!</p><p><strong>Implementation Overview:</strong></p><p>The solution uses Databricks SQL History API with these key components:</p><pre># Core monitoring logic (simplified from production code)</pre><pre>import requests<br>from datetime import datetime, timedelta</pre><pre># Databricks SQL History API endpoint<br>API_ENDPOINT = &quot;https://&lt;workspace&gt;.azuredatabricks.net/api/2.0/sql/history/queries&quot;</pre><pre># Configuration<br>DURATION_THRESHOLD_SECONDS = 900  # 15 minutes<br>LOOKBACK_HOURS = 6</pre><pre># Get current time and lookback window<br>current_time = datetime.now()<br>start_time = current_time - timedelta(hours=LOOKBACK_HOURS)<br>start_time_ms = int(start_time.timestamp() * 1000)<br>end_time_ms = int(current_time.timestamp() * 1000)</pre><pre># API payload - filter for RUNNING queries only<br>payload = {<br>    &quot;filter_by&quot;: {<br>        &quot;query_start_time_range&quot;: {<br>            &quot;start_time_ms&quot;: start_time_ms,<br>            &quot;end_time_ms&quot;: end_time_ms<br>        },<br>        &quot;statuses&quot;: [&quot;RUNNING&quot;]  # Key: Monitor currently running queries<br>    },<br>    &quot;include_metrics&quot;: False,<br>    &quot;max_results&quot;: 1000<br>}</pre><pre># Fetch running queries with pagination support<br>long_running_queries = []<br>has_next_page = True</pre><pre>while has_next_page:<br>    response = requests.get(API_ENDPOINT, json=payload, headers=headers)<br>    <br>    if response.status_code == 200:<br>        result = response.json()<br>        running_queries = result.get(&quot;res&quot;, [])<br>        <br>        # Check duration for each running query<br>        for query in running_queries:<br>            duration_ms = query.get(&quot;duration&quot;, 0)<br>            duration_seconds = duration_ms / 1000<br>            <br>            # Alert if query running longer than threshold<br>            if duration_seconds &gt; DURATION_THRESHOLD_SECONDS:<br>                long_running_queries.append({<br>                    &quot;query_id&quot;: query.get(&quot;query_id&quot;),<br>                    &quot;user_name&quot;: query.get(&quot;user_name&quot;),<br>                    &quot;query_start_time&quot;: query.get(&quot;query_start_time_ms&quot;),<br>                    &quot;query_text&quot;: query.get(&quot;query_text&quot;),<br>                    &quot;duration_seconds&quot;: duration_seconds,<br>                    &quot;warehouse_id&quot;: query.get(&quot;warehouse_id&quot;)<br>                })<br>        <br>        # Handle pagination<br>        has_next_page = result.get(&quot;has_next_page&quot;, False)<br>        if has_next_page:<br>            payload[&quot;page_token&quot;] = result.get(&quot;next_page_token&quot;)<br>    else:<br>        break</pre><pre># Store in audit table for tracking<br>if long_running_queries:<br>    df = spark.createDataFrame(long_running_queries)<br>    df.withColumn(&quot;Monitoring_Date&quot;, current_timestamp()) \<br>      .write.mode(&quot;append&quot;) \<br>      .saveAsTable(&quot;your_schema.long_running_sql_audit_log&quot;)<br>    <br>    # Send email alerts to users<br>    send_email_alerts(long_running_queries)</pre><p><strong>Email Alert Example:</strong></p><p><strong>Email Alert Format:</strong></p><p>The system sends HTML-formatted emails to users (and CCs the data engineering team) with a table of their long-running queries:</p><pre>Subject: ⚠️ Alert: Long Running Queries in Databricks - SQLWarehouse</pre><pre>The following queries have been running for over 15 minutes:</pre><pre>┌──────────────┬────────────────┬─────────┬─────────────────────┬──────────────┬────────────┐<br>│ Query ID     │ Query Statement│ User    │ Start Time          │ Duration (Min)│ Warehouse  │<br>├──────────────┼────────────────┼─────────┼─────────────────────┼──────────────┼────────────┤<br>│ abc-123-xyz  │ SELECT * FROM  │ user1   │ 2025-01-16 10:30:00 │ 45           │ analytics  │<br>│              │ large_table... │         │                     │              │            │<br>└──────────────┴────────────────┴─────────┴─────────────────────┴──────────────┴────────────┘</pre><pre>⚠️ These queries may result in significant costs. Please review and consider:<br>• Adding partition filters to reduce data scanned<br>• Using LIMIT for exploratory queries  <br>• Killing the query if no longer needed<br>• Checking query execution plan for optimization opportunities</pre><pre>You can view query details in the SQL Warehouse query history UI.</pre><p><strong>Why This Works:</strong></p><ul><li><strong>Zero compute cost:</strong> Just API calls to Databricks endpoints</li><li><strong>Real-time detection:</strong> Catches queries while still running (not after they finish)</li><li><strong>User accountability:</strong> Alerts go directly to query executors</li><li><strong>Actionable:</strong> Users can kill queries immediately via UI</li><li><strong>Audit trail:</strong> All alerts logged to Delta table for analysis</li></ul><p><strong>Deployment Architecture:</strong></p><pre>Databricks Workflow (Scheduled every 15 minutes):<br>┌────────────────────────────────────────────────┐<br>│ SQL Warehouse Endpoint Monitoring Notebook     │<br>├────────────────────────────────────────────────┤<br>│                                                │<br>│ 1. Call SQL History API                        │<br>│    • Filter: RUNNING queries                   │<br>│    • Lookback: Last 6 hours                    │<br>│    • Threshold: &gt; 15 minutes                   │<br>│                                                │<br>│ 2. Identify Long-Running Queries               │<br>│    • Calculate duration from API response      │<br>│    • Enrich with warehouse metadata            │<br>│                                                │<br>│ 3. Store in Audit Table                        │<br>│    • Write to Delta table for tracking         │<br>│    • Maintain historical log                   │<br>│                                                │<br>│ 4. Send Email Alerts                           │<br>│    • Group by user                             │<br>│    • HTML table format                         │<br>│    • CC data engineering team                  │<br>│                                                │<br>└────────────────────────────────────────────────┘</pre><pre>Cost: $0 (API calls + minimal Databricks compute within free tier)</pre><p><strong>Phase 2 Results:</strong></p><pre>Zero-Cost Endpoint Alerting Impact (Week 2):<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━<br>• Detected 23 queries &gt;15 minutes (still running)<br>• Alerted 12 users in real-time<br>• 8 users killed queries immediately<br>• Prevented ~$2,500 in wasteful compute</pre><pre>Cost Reduction: $17,500 → $15,000/month<br>Additional Savings: $2,500/month (-15%)<br>Implementation Cost: $0 <br>Time: 4 hours<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━</pre><p><strong>Key Insight:</strong> Endpoint monitoring caught expensive queries <em>while they were still running</em> (not after completion), allowing users to kill them before accumulating massive costs. Uses duration as a proxy for cost — no expensive attribution compute needed!</p><p><strong>Implementation Note:</strong> The complete production code for this endpoint monitoring system is available in the project repository. It includes pagination handling, audit logging, and HTML email formatting.</p><p><strong>But we still had questions:</strong></p><ul><li>Why were these queries expensive?</li><li>Which dashboards/notebooks were driving costs?</li><li>How much did each query actually cost?</li></ul><p>Time for Phase 3.</p><h3>Phase 3: Deep Dive — Query-Level Cost Attribution (Week 3–4)</h3><h3>Why We Needed Attribution</h3><p>Phases 1–2 reduced costs by 40% ($25K → $15K), but we had no visibility into:</p><ul><li>Which query <strong>sources</strong> were expensive (dashboards vs. notebooks vs. ad-hoc)</li><li>Which specific dashboards were driving costs</li><li>How much each query actually cost (not just duration)</li><li>Whether auto-refresh rates were excessive</li></ul><p>We needed to build a proper <strong>SQL Warehouse Cost Attribution</strong> system.</p><h3>How SQL Warehouse Cost Attribution Works</h3><h3>High-Level Flow</h3><pre>┌─────────────────────────────────────────────────────────┐<br>│                  INPUT LAYER                             │<br>├─────────────────────────────────────────────────────────┤<br>│                                                          │<br>│  system.query.history          → Query timing &amp; users   │<br>│  system.compute.warehouse_events → ON/OFF state         │<br>│  system.billing.usage          → Hourly costs           │<br>│  system.billing.list_prices    → DBU pricing            │<br>│                                                          │<br>└────────────────────┬────────────────────────────────────┘<br>                     ▼<br>┌─────────────────────────────────────────────────────────┐<br>│              PROCESSING LAYER                            │<br>├─────────────────────────────────────────────────────────┤<br>│                                                          │<br>│  Step 1: Calculate Warehouse Utilization                │<br>│  ┌───────────────────────────────────────────┐          │<br>│  │ For each hour:                            │          │<br>│  │ • UTILIZED seconds (queries running)      │          │<br>│  │ • ON_IDLE seconds (on but no queries)    │          │<br>│  │ • OFF seconds (warehouse off)            │          │<br>│  │                                           │          │<br>│  │ Utilization = utilized / (utilized+idle) │          │<br>│  └───────────────────────────────────────────┘          │<br>│                                                          │<br>│  Step 2: Calculate Query Proportional Work              │<br>│  ┌───────────────────────────────────────────┐          │<br>│  │ For each query spanning multiple hours:  │          │<br>│  │ • Split duration across hourly buckets   │          │<br>│  │ • Calculate proportion of warehouse work │          │<br>│  └───────────────────────────────────────────┘          │<br>│                                                          │<br>│  Step 3: Attribute Costs                                │<br>│  ┌───────────────────────────────────────────┐          │<br>│  │ Query Cost = Warehouse Hourly Cost ×     │          │<br>│  │              Utilization Ratio ×          │          │<br>│  │              Query Proportion of Time     │          │<br>│  └───────────────────────────────────────────┘          │<br>│                                                          │<br>└────────────────────┬────────────────────────────────────┘<br>                     ▼<br>┌─────────────────────────────────────────────────────────┐<br>│                 OUTPUT LAYER                             │<br>├─────────────────────────────────────────────────────────┤<br>│                                                          │<br>│  Table: sqlwh_cost_attribution                           │<br>│  ┌───────────────────────────────────────────┐          │<br>│  │ • statement_id                            │          │<br>│  │ • executed_by (actual user!)             │          │<br>│  │ • est_dollars (attributed cost)          │          │<br>│  │ • est_dbus (attributed DBUs)             │          │<br>│  │ • query_work_duration_seconds            │          │<br>│  │ • query_source_type (JOB, NOTEBOOK, etc)│          │<br>│  │ • statement_text                         │          │<br>│  └───────────────────────────────────────────┘          │<br>│                                                          │<br>└─────────────────────────────────────────────────────────┘</pre><h3>Attribution Algorithm: How It Works</h3><p>The system correlates three Databricks system tables to attribute warehouse costs to individual queries:</p><p><strong>Data Sources:</strong></p><ul><li>system.query.history → Query execution times and users</li><li>system.compute.warehouse_events → Warehouse ON/OFF states</li><li>system.billing.usage → Hourly warehouse costs</li></ul><p><strong>High-Level Algorithm:</strong></p><p><strong>Step 1:</strong> Calculate warehouse utilization per hour</p><ul><li>Utilized seconds = time queries were running</li><li>Idle seconds = warehouse ON but no queries</li><li>Utilization ratio = utilized / (utilized + idle)</li></ul><p><strong>Step 2:</strong> Determine each query’s proportion of work</p><ul><li>For queries spanning multiple hours, split duration across hourly buckets</li><li>Calculate query’s proportion of total warehouse work in each hour</li></ul><p><strong>Step 3:</strong> Attribute costs proportionally</p><pre>Query Cost = Hourly Warehouse Cost × Utilization Ratio × Query Proportion</pre><p><strong>Example:</strong></p><pre>Warehouse cost: $12.50/hour<br>Utilization: 58% (queries running)<br>Query A: 900 seconds of 2,100 total query seconds (43%)<br>Query A cost: $12.50 × 0.58 × 0.43 = $3.12 ✓</pre><p><strong>Key Benefit:</strong> Costs are attributed to actual query executors, not warehouse creators.</p><h3>Implementation Guide</h3><h3>Prerequisites</h3><ol><li><strong>System Tables Access</strong></li></ol><ul><li><em>-- Verify access to required tables</em> SELECT COUNT(*) FROM system.query.history; SELECT COUNT(*) FROM system.compute.warehouse_events; SELECT COUNT(*) FROM system.billing.usage; SELECT COUNT(*) FROM system.billing.list_prices;</li></ul><p><strong>SQL Warehouse IDs</strong></p><ul><li><em>-- Get your warehouse IDs</em> SELECT warehouse_id, name FROM system.compute.warehouses WHERE warehouse_type = &#39;SQL&#39;;</li></ul><p><strong>Output Table Schema</strong></p><ul><li>CREATE TABLE IF NOT EXISTS monitoring.sqlwh_cost_attribution ( statement_id STRING, query_source_id STRING, query_source_type STRING, executed_by STRING, <em>-- Actual user executing query</em> warehouse_id STRING, statement_text STRING, workspace_id STRING, start_time TIMESTAMP, end_time TIMESTAMP, query_work_start_time TIMESTAMP, query_work_end_time TIMESTAMP, query_work_duration_seconds DOUBLE, est_dollars DECIMAL(18,2), <em>-- Attributed cost</em> est_dbus DECIMAL(18,2), <em>-- Attributed DBUs</em> query_profile_url STRING, query_start_hour TIMESTAMP ) USING DELTA;</li></ul><h3>Implementation Approach</h3><p>The attribution logic is implemented as a Databricks notebook with ~500 lines of SQL using 26 CTEs (Common Table Expressions) to handle:</p><p><strong>Key Processing Steps:</strong></p><ol><li>Filter data to monitoring time window</li><li>Build warehouse ON/OFF event timeline</li><li>Create query execution timeline</li><li>Merge timelines and calculate utilization</li><li>Compute proportional work per query</li><li>Join with billing data and attribute costs</li></ol><p><strong>Output:</strong> Query-level cost attribution table with user, cost, duration, and source type.</p><h3>Time Window Configuration</h3><p>The attribution notebook uses the same time window logic as the main monitoring:</p><pre># Widget parameters<br>lookback_value = 30     # Numeric value<br>lookback_unit = &quot;Mins&quot;  # Mins, Hours, Days, Month</pre><pre># Calculate time window<br>if lookback_unit == &quot;Mins&quot;:<br>    # Align to clock boundaries (e.g., 30-min windows)<br>    window_end = round_down_to_nearest(now, lookback_value)<br>    window_start = window_end - timedelta(minutes=lookback_value)<br>    <br>elif lookback_unit == &quot;Hours&quot;:<br>    # Align to hour boundaries<br>    window_end = now.replace(minute=0, second=0)<br>    window_start = window_end - timedelta(hours=lookback_value)<br>    <br>elif lookback_unit == &quot;Days&quot;:<br>    # Full day boundaries (00:00:00 to 23:59:59)<br>    window_end = datetime(now.year, now.month, now.day, 23, 59, 59)<br>    window_start = window_end - timedelta(days=lookback_value-1)</pre><p><strong>Examples:</strong></p><ul><li>lookback_value=30, lookback_unit=Mins → Last 30 minutes (aligned to :00 or :30)</li><li>lookback_value=2, lookback_unit=Hours → Last 2 hours (aligned to hour boundaries)</li><li>lookback_value=7, lookback_unit=Days → Last 7 full days</li></ul><h3>Deployment Strategy</h3><h3>As Upstream Task</h3><p>Deploy as an upstream task that runs <strong>before</strong> main monitoring:</p><pre>{<br>  &quot;name&quot;: &quot;cloud_spend_monitoring_pipeline&quot;,<br>  &quot;tasks&quot;: [<br>    {<br>      &quot;task_key&quot;: &quot;sqlwh_cost_attribution&quot;,<br>      &quot;notebook_task&quot;: {<br>        &quot;notebook_path&quot;: &quot;/Monitoring/cloud_spend_sqlwh_attribution&quot;,<br>        &quot;base_parameters&quot;: {<br>          &quot;Run_date&quot;: &quot;{{job.start_time.iso_date}}&quot;,<br>          &quot;lookback_value&quot;: &quot;30&quot;,<br>          &quot;lookback_unit&quot;: &quot;Mins&quot;<br>        }<br>      },<br>      &quot;new_cluster&quot;: {<br>        &quot;spark_version&quot;: &quot;14.3.x-scala2.12&quot;,<br>        &quot;node_type_id&quot;: &quot;Standard_DS3_v2&quot;,<br>        &quot;num_workers&quot;: 2<br>      }<br>    },<br>    {<br>      &quot;task_key&quot;: &quot;main_monitoring&quot;,<br>      &quot;depends_on&quot;: [{&quot;task_key&quot;: &quot;sqlwh_cost_attribution&quot;}],<br>      &quot;notebook_task&quot;: {<br>        &quot;notebook_path&quot;: &quot;/Monitoring/cloud_spend_monitor_hybrid&quot;,<br>        &quot;base_parameters&quot;: {<br>          &quot;Run_date&quot;: &quot;{{job.start_time.iso_date}}&quot;,<br>          &quot;lookback_value&quot;: &quot;30&quot;,<br>          &quot;lookback_unit&quot;: &quot;Mins&quot;<br>        }<br>      }<br>    }<br>  ]<br>}</pre><h3>Scheduling Recommendations</h3><p>FrequencyUse CaseConfigurationEvery 15 minReal-time SQL WH monitoringlookback_value=15, lookback_unit=MinsEvery hourStandard SQL WH trackinglookback_value=1, lookback_unit=HoursDailyHistorical attributionlookback_value=1, lookback_unit=Days</p><h3>What You Get</h3><h3>Before (Billing Data Only)</h3><pre>SELECT <br>    identity_metadata.created_by as user,<br>    SUM(usage_quantity * price_per_dbu) as cost<br>FROM system.billing.usage<br>WHERE sku_name LIKE &#39;%SQL%&#39;<br>GROUP BY 1;</pre><pre>┌─────────────────────┬────────┐<br>│ user                │ cost   │<br>├─────────────────────┼────────┤<br>│ admin@yourcompany.com   │ $1,234 │ ← All costs to warehouse owner<br>└─────────────────────┴────────┘</pre><h3>After (Query-Level Attribution)</h3><pre>SELECT <br>    executed_by as user,<br>    query_source_type,<br>    COUNT(*) as queries,<br>    SUM(est_dollars) as cost,<br>    SUM(est_dbus) as dbus,<br>    AVG(query_work_duration_seconds/60) as avg_duration_min<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_start_hour &gt;= current_timestamp() - INTERVAL 1 DAY<br>GROUP BY 1, 2<br>ORDER BY cost DESC;</pre><pre>┌───────────────────┬─────────────┬─────────┬─────────┬──────┬──────<br>│ user              │ source_type │ queries │ cost    │ dbus │ avg_duration │<br>├───────────────────┼─────────────┼─────────┼─────────┼──────┼──────<br>│ analyst1@yourcompany.com   │ SQL QUERY   │ 45      │ $234.50 │ 95.2 │ 2.3 min      │<br>│ engineer1@yourcompany.com  │ NOTEBOOK    │ 12      │ $189.20 │ 78.5 │ 5.1 min      │<br>│ datascience@yourcompany.com│ JOB         │ 8       │ $156.80 │ 65.1 │ 8.7 min      │<br>│ analyst2@yourcompany.com   │ AI/BI DASH  │ 156     │ $98.40  │ 41.2 │ 0.4 min      │<br>└───────────────────┴─────────────┴─────────┴─────────┴──────┴──────</pre><p><strong>Now you can:</strong></p><ul><li>✅ See actual users driving costs (not warehouse creators)</li><li>✅ Discover hidden cost drivers (AI/BI dashboards, notebooks, etc.)</li><li>✅ Identify which dashboards are expensive and why</li><li>✅ Do chargeback by team/department/query source</li><li>✅ Alert users about their SQL WH spend</li><li>✅ Optimize based on actual usage patterns and query sources</li></ul><h3>Use Cases</h3><h3>1. User-Level Cost Awareness</h3><p>Alert users about their SQL Warehouse spend:</p><pre># In main monitoring notebook<br>expensive_users = spark.sql(&quot;&quot;&quot;<br>    SELECT <br>        executed_by,<br>        SUM(est_dollars) as total_cost<br>    FROM monitoring.sqlwh_cost_attribution<br>    WHERE query_start_hour &gt;= current_timestamp() - INTERVAL 1 HOUR<br>    GROUP BY 1<br>    HAVING total_cost &gt; 50<br>&quot;&quot;&quot;)</pre><pre>for user in expensive_users.collect():<br>    send_alert(<br>        user.executed_by,<br>        f&quot;Your SQL Warehouse queries cost ${user.total_cost:.2f} in the last hour&quot;<br>    )</pre><h3>2. Chargeback/Showback</h3><p>Generate department-level cost reports:</p><pre>-- Tag users with departments (via lookup table)<br>SELECT <br>    d.department,<br>    DATE_TRUNC(&#39;month&#39;, query_start_hour) as month,<br>    SUM(est_dollars) as department_cost<br>FROM monitoring.sqlwh_cost_attribution u<br>JOIN user_departments d ON u.executed_by = d.email<br>GROUP BY 1, 2<br>ORDER BY 1, 2;</pre><pre>Result:<br>┌─────────────┬────────────┬─────────────────┐<br>│ department  │ month      │ department_cost │<br>├─────────────┼────────────┼─────────────────┤<br>│ Analytics   │ 2025-01-01 │ $4,567.89       │<br>│ Data Sci    │ 2025-01-01 │ $3,456.78       │<br>│ Engineering │ 2025-01-01 │ $2,345.67       │<br>└─────────────┴────────────┴─────────────────┘</pre><h3>3. Query Optimization</h3><p>Identify expensive queries for optimization:</p><pre>SELECT <br>    statement_id,<br>    executed_by,<br>    query_source_type,<br>    est_dollars,<br>    query_work_duration_seconds / 60 as duration_min,<br>    LEFT(statement_text, 100) as query_preview,<br>    query_profile_url<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE est_dollars &gt; 10<br>ORDER BY est_dollars DESC<br>LIMIT 20;</pre><h3>Performance Considerations</h3><h3>Processing Time</h3><p>The attribution query processes:</p><ul><li>Query history records</li><li>Warehouse event logs</li><li>Billing usage records</li><li>Pricing data</li></ul><p><strong>Typical processing times:</strong></p><ul><li>30-minute window: ~2–3 minutes</li><li>1-hour window: ~3–5 minutes</li><li>1-day window: ~10–15 minutes</li><li>7-day window: ~30–45 minutes</li></ul><h3>Optimization Tips</h3><ol><li><strong>Limit Warehouses</strong></li></ol><ul><li><em># Only process specific warehouses</em> warehouse_ids = [&quot;warehouse-1&quot;, &quot;warehouse-2&quot;] <em># Not all warehouses</em></li></ul><ol><li><strong>Partition Output Table</strong></li></ol><ul><li>CREATE TABLE monitoring.sqlwh_cost_attribution ( ... ) PARTITIONED BY (query_start_hour); <em>-- Partition by hour</em></li></ul><ol><li><strong>Use Incremental Processing</strong></li></ol><ul><li><em># For continuous monitoring, use small windows</em> lookback_value = 30 lookback_unit = &quot;Mins&quot; <em># vs. running full-day batches</em></li></ul><ol><li><strong>Cluster Configuration</strong></li></ol><ul><li>{ &quot;node_type_id&quot;: &quot;Standard_DS3_v2&quot;, &quot;num_workers&quot;: 2 <em>// Scale up for larger windows</em> }</li></ul><h3>Integration with Real-Time Monitoring</h3><p>The SQL WH attribution table can feed into broader cloud cost monitoring systems for comprehensive alerting and tracking. See our companion post on <strong>“Building Near Real-Time Cloud Spend Monitoring”</strong> for integration patterns.</p><h3>Troubleshooting</h3><h3>Issue: No Data in Output Table</h3><p><strong>Check:</strong></p><pre>-- Verify warehouses have billing data<br>SELECT warehouse_id, COUNT(*), SUM(usage_quantity)<br>FROM system.billing.usage<br>WHERE sku_name LIKE &#39;%SQL%&#39;<br>  AND usage_date &gt;= current_date() - 7<br>GROUP BY 1;</pre><pre>-- Verify query history exists<br>SELECT COUNT(*)<br>FROM system.query.history<br>WHERE start_time &gt;= current_timestamp() - INTERVAL 1 DAY<br>  AND compute.warehouse_id IS NOT NULL;</pre><p><strong>Fix:</strong> Ensure warehouses are active and queries are running during time window.</p><h3>Issue: Attribution Costs Don’t Match Billing</h3><p><strong>Understand:</strong></p><ul><li>Attribution uses <strong>utilization ratio</strong> (only utilizes seconds)</li><li>Billing charges for <strong>all warehouse uptime</strong> (including idle)</li><li>Attribution &lt; Billing is expected and correct</li></ul><p><strong>Example:</strong></p><pre>Billing: $100 for warehouse-hour<br>Utilization: 58% (queries running)<br>Attributed to queries: $58<br>Idle cost: $42 (not attributed)</pre><pre>This is correct! You&#39;re only attributing utilized time.</pre><h3>Issue: Long Processing Time</h3><p><strong>Optimize:</strong></p><pre># Use smaller time windows<br>lookback_value = 30  # minutes, not hours<br>lookback_unit = &quot;Mins&quot;</pre><pre># Limit warehouses<br>warehouse_ids = [&quot;primary-warehouse&quot;]  # Not all</pre><pre># Increase cluster size<br>num_workers = 4  # Scale up for complex attribution</pre><h3>Example Queries</h3><h3>Top Expensive Users</h3><pre>SELECT <br>    executed_by,<br>    COUNT(DISTINCT statement_id) as query_count,<br>    SUM(est_dollars) as total_cost,<br>    AVG(est_dollars) as avg_cost_per_query,<br>    SUM(query_work_duration_seconds) / 3600 as total_hours<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_start_hour &gt;= current_date() - 7<br>GROUP BY 1<br>ORDER BY total_cost DESC<br>LIMIT 20;</pre><h3>Cost by Query Source Type</h3><pre>SELECT <br>    query_source_type,<br>    COUNT(*) as queries,<br>    SUM(est_dollars) as cost,<br>    AVG(query_work_duration_seconds) as avg_duration_sec<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_start_hour &gt;= current_date() - 7<br>GROUP BY 1<br>ORDER BY cost DESC;</pre><h3>Long-Running Expensive Queries</h3><pre>SELECT <br>    statement_id,<br>    executed_by,<br>    est_dollars,<br>    query_work_duration_seconds / 60 as duration_min,<br>    query_source_type,<br>    LEFT(statement_text, 200) as query_preview<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_start_hour &gt;= current_date() - 7<br>  AND est_dollars &gt; 5<br>  AND query_work_duration_seconds &gt; 300  -- 5+ minutes<br>ORDER BY est_dollars DESC;</pre><h3>Phase 3 Results: The AI/BI Dashboard Discovery</h3><h3>The Remaining $15K Mystery</h3><p>After Phases 1–2, we had reduced costs from $25K → $15K/month. But we still had questions:</p><ul><li><strong>What types of workloads</strong> were driving the remaining $15K?</li><li><strong>Which dashboards</strong> were the most expensive?</li><li><strong>Why</strong> were they expensive?</li></ul><p>Time to implement <strong>query-level cost attribution</strong>.</p><h3>After Implementing Cost Attribution</h3><p>We deployed the attribution notebook and analyzed 30 days of data post-optimization. The results revealed the root cause:</p><pre>┌──────────────────────────────────────────────────────────────────┐<br>│         COST BREAKDOWN BY QUERY SOURCE (After Phase 1-2)         │<br>├───────────────────┬──────────┬──────────┬───────────┬───────────┤<br>│ Query Source      │ Queries  │ Cost     │ % of Total│ Chart     │<br>├───────────────────┼──────────┼──────────┼───────────┼───────────┤<br>│ AI/BI DASHBOARD   │ 4,567    │ $9,900   │ 66%       │ ████████████████ ← ROOT CAUSE!<br>│ NOTEBOOK          │ 234      │ $2,850   │ 19%       │ █████<br>│ SQL QUERY         │ 1,892    │ $1,350   │ 9%        │ ██<br>│ Tableau           │ 456      │ $750     │ 5%        │ █<br>│ PowerBI           │ 123      │ $150     │ 1%        │ <br>└───────────────────┴──────────┴──────────┴───────────┴───────────┘</pre><pre>Current SQL Warehouse Cost: $15,000/month (after Phase 1-2)</pre><p><strong>The Discovery: AI/BI Dashboards still consuming 66% ($9,900) of costs!</strong></p><p>Even after rightsizing and alerting, AI/BI dashboards were the dominant cost driver. We needed to understand which specific dashboards were expensive and why.</p><h3>Visual Breakdown</h3><pre>SQL Warehouse Monthly Cost: $15,000 (after Phase 1-2)<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━</pre><pre>AI/BI DASHBOARDS                                $9,900 (66%)<br>███████████████████████████████████████████████████████████████</pre><pre>NOTEBOOKS              $2,850 (19%)<br>███████████████████</pre><pre>SQL QUERIES        $1,350 (9%)<br>████████</pre><pre>Tableau      $750 (5%)<br>█████</pre><pre>PowerBI   $150 (1%)<br>██<br>━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━</pre><pre>❗ Key Insight: Even after rightsizing, AI/BI Dashboards still <br>   represented 2/3 of costs. Time to dig deeper.</pre><h3>The Transformation: From Opaque to Transparent</h3><p><strong>BEFORE Attribution</strong> (End of Phase 2):</p><pre>┌─────────────────────────────────────────────┐<br>│     SQL Warehouse: &quot;analytics-sql-wh&quot;       │<br>├─────────────────────────────────────────────┤<br>│                                             │<br>│  Current Cost: $15,000/month                │<br>│                                             │<br>│  Cost Driver: [STILL UNKNOWN]               │<br>│  Specific Dashboards: [UNKNOWN]             │<br>│  Query Patterns: [UNKNOWN]                  │<br>│                                             │<br>│  ✅ Rightsized warehouse                    │<br>│  ✅ Real-time duration alerts working       │<br>│  ❌ But no query-level cost visibility      │<br>│                                             │<br>└─────────────────────────────────────────────┘</pre><p><strong>AFTER Attribution</strong> (Phase 3):</p><pre>┌─────────────────────────────────────────────────────────────────┐<br>│           SQL Warehouse: &quot;analytics-sql-wh&quot;                     │<br>│           Current Cost: $15,000/month                           │<br>├─────────────────────────────────────────────────────────────────┤<br>│                                                                 │<br>│  📊 AI/BI DASHBOARDS (66% = $9,900)                             │<br>│     • Executive Metrics Dashboard: $4,950 (45 users, 2.1K queries) │<br>│     • Sales Trends Dashboard: $2,750 (23 users, 1.3K queries)  │<br>│     • Operations Metrics: $1,400 (18 users, 892 queries)       │<br>│     • Others: $800                                              │<br>│                                                                 │<br>│  📓 NOTEBOOKS (19% = $2,850)                                    │<br>│     • Data Science Team: $1,900 (8 users, 156 queries)         │<br>│     • Analytics Team: $950 (12 users, 78 queries)              │<br>│                                                                 │<br>│  🔍 SQL QUERIES (9% = $1,350)                                   │<br>│     • Ad-hoc analysis: $1,350 (34 users, 1,892 queries)        │<br>│                                                                 │<br>│  📈 BI TOOLS (6% = $900)                                        │<br>│     • Tableau: $750 (15 users, 456 queries)                    │<br>│     • PowerBI: $150 (5 users, 123 queries)                     │<br>│                                                                 │<br>│  ✅ Full visibility into usage patterns                         │<br>│  ✅ Can identify specific expensive dashboards                  │<br>│  ✅ Can attribute costs to business teams                       │<br>│                                                                 │<br>└─────────────────────────────────────────────────────────────────┘</pre><p><strong>The Difference:</strong> Now we knew exactly which dashboards to optimize!</p><h3>Drilling Deeper: Finding the Expensive Dashboards</h3><pre>-- Query to find expensive AI/BI dashboards<br>SELECT <br>    query_source_id as dashboard_id,<br>    COUNT(DISTINCT executed_by) as users,<br>    COUNT(*) as queries,<br>    SUM(est_dollars) as total_cost,<br>    AVG(query_work_duration_seconds) as avg_duration_sec<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_source_type = &#39;AI/BI DASHBOARD&#39;<br>  AND query_start_hour &gt;= current_date() - 30<br>GROUP BY query_source_id<br>ORDER BY total_cost DESC<br>LIMIT 10;</pre><p><strong>Results (Post Phase 1–2):</strong></p><pre>┌─────────────────────────────────────────────────────────────────────┐<br>│            TOP 5 EXPENSIVE AI/BI DASHBOARDS (Phase 3)               │<br>├──────────────┬───────┬──────────┬───────────┬──────────────────────┤<br>│ Dashboard    │ Users │ Queries  │ Cost      │ Avg Duration         │<br>├──────────────┼───────┼──────────┼───────────┼──────────────────────┤<br>│ executive    │ 45    │ 2,156    │ $4,950    │ 12.3 sec             │<br>│ sales-trends │ 23    │ 1,345    │ $2,750    │ 8.9 sec              │<br>│ ops-metrics  │ 18    │ 892      │ $1,400    │ 6.2 sec              │<br>│ customer-360 │ 12    │ 134      │ $600      │ 45.6 sec ← SLOW!     │<br>│ finance-dash │ 8     │ 40       │ $200      │ 78.4 sec ← VERY SLOW!│<br>└──────────────┴───────┴──────────┴───────────┴──────────────────────┘</pre><h3>Phase 3 Optimizations: Three Root Causes Found</h3><p>Armed with query-level attribution data, we identified and fixed three specific issues:</p><h4>1. High-Frequency Auto-Refresh</h4><p><strong>Issue:</strong> The executive dashboard was set to auto-refresh <strong>every 30 seconds</strong> and had 45 active users.</p><pre>Calculation:<br>- 45 users × 2 queries per refresh × 120 refreshes/hour = 10,800 queries/hour<br>- At ~$0.046 per query = $497/hour during business hours<br>- 8 hours/day × 20 work days = $7,952/month from ONE dashboard!</pre><p><strong>Fix:</strong> Changed auto-refresh to 5 minutes (10x less frequent) <strong>Result:</strong> Cost dropped from $4,950 → $990/month (-80%)</p><h4>2. Inefficient Queries on Popular Dashboards</h4><p><strong>Issue:</strong> sales-trends dashboard was doing full table scans on billion-row tables.</p><pre>-- Expensive query found in attribution data<br>SELECT <br>    date, product, region, SUM(revenue)<br>FROM fact_sales  -- 2.5 billion rows, no partition filter!<br>WHERE date &gt;= &#39;2020-01-01&#39;<br>GROUP BY date, product, region</pre><p><strong>Average Query Time:</strong> 8.9 seconds<br><strong>Average Query Cost:</strong> $2.05<br><strong>Monthly Volume:</strong> 1,345 queries<br><strong>Monthly Cost:</strong> $2,750</p><p><strong>Fix:</strong></p><ul><li>Added date partition filter (last 90 days only)</li><li>Pre-aggregated to daily grain</li><li>Added materialized view</li></ul><p><strong>Result:</strong> Query time dropped to 1.2 sec, cost to $0.27/query <strong>Savings:</strong> $2,750 → $363/month (-87%)</p><h4>3. Dashboard Query Consolidation</h4><p><strong>Issue:</strong> Attribution revealed multiple dashboards were querying the same data with slight variations, causing redundant compute.</p><pre>Pattern Discovered:<br>- 5 dashboards all querying same sales data<br>- Each refresh = 5x redundant queries<br>- Total: $1,800/month in duplicate compute</pre><p><strong>Fix:</strong></p><ul><li>Created shared materialized views for common queries</li><li>Consolidated dashboard data sources</li><li>Implemented caching layer</li></ul><p><strong>Result:</strong> Eliminated redundant queries <strong>Savings:</strong> $1,800 → $200/month (-89%)</p><h3>Key Learnings from the Optimization Journey</h3><ol><li><strong>Start with Quick Wins</strong> — Rightsizing saved 30% in 2 hours with zero cost</li><li><strong>Endpoint Monitoring is Powerful and Free</strong> — SQL Warehouse API monitoring caught expensive queries while still running, preventing cost escalation</li><li><strong>Attribution Unlocks Strategic Optimization</strong> — You can’t optimize what you can’t measure. Attribution revealed specific dashboards and patterns.</li><li><strong>AI/BI Dashboards Can Be Expensive</strong> — Small, fast queries × high frequency × many users = significant cost</li><li><strong>Auto-Refresh Multiplies Costs</strong> — 30-second refresh with 45 users = 10,800 queries/hour</li><li><strong>Three-Phase Approach Works</strong> — Quick wins → Real-time prevention → Strategic optimization = 80% reduction</li></ol><h3>How to Find Your Own AI/BI Dashboard Costs</h3><pre>-- Quick analysis query<br>SELECT <br>    query_source_type,<br>    COUNT(DISTINCT executed_by) as unique_users,<br>    COUNT(*) as total_queries,<br>    SUM(est_dollars) as total_cost,<br>    AVG(est_dollars) as avg_cost_per_query,<br>    AVG(query_work_duration_seconds) as avg_duration_sec,<br>    SUM(est_dollars) / COUNT(*) as cost_per_query<br>FROM monitoring.sqlwh_cost_attribution<br>WHERE query_start_hour &gt;= current_date() - 30<br>GROUP BY query_source_type<br>ORDER BY total_cost DESC;</pre><p><strong>Red Flags to Look For:</strong></p><ul><li>🚨 Query source consuming &gt;50% of warehouse cost</li><li>🚨 Average query cost &gt;$2.00</li><li>🚨 Average duration &gt;30 seconds</li><li>🚨 Very high query volume (&gt;1000/day) for dashboards</li><li>🚨 Warehouse utilization &lt;40% but high costs</li></ul><h3>Summary</h3><h3>The Three-Phase Optimization Playbook</h3><p><strong>The Crisis:</strong></p><ul><li>SQL Warehouse costs surged 300%: $6K → $25K/month</li><li>Billing data showed only warehouse creator (dead end)</li><li>No visibility into users, queries, or workload types</li></ul><p><strong>The Three-Phase Solution:</strong></p><p><strong>Phase 1: Quick Wins — Rightsizing (Week 1, $0 cost)</strong></p><ul><li>✅ Reduced auto-stop, rightsized DBUs, enabled spot instances</li><li>✅ Result: $25K → $17.5K/month (-30%)</li><li>⏱️ Time: 2 hours</li></ul><p><strong>Phase 2: Zero-Cost Endpoint Monitoring (Week 2, $0 cost)</strong></p><ul><li>✅ Deployed SQL Warehouse API endpoint monitoring</li><li>✅ Real-time alerts for queries &gt;15min (still running)</li><li>✅ Result: $17.5K → $15K/month (-40% total)</li><li>⏱️ Time: 4 hours</li></ul><p><strong>Phase 3: Query-Level Attribution (Week 3–4, ~$50/month cost)</strong></p><ul><li>✅ Implemented full cost attribution system</li><li>✅ Discovered AI/BI dashboards = 66% of costs</li><li>✅ Optimized auto-refresh, queries, and consolidation</li><li>✅ Result: $15K → $5K/month (-80% total)</li><li>⏱️ Time: 1 week</li></ul><p><strong>Final Results:</strong></p><ul><li>💰 <strong>$20K/month savings</strong> ($240K annually)</li><li>📊 <strong>80% cost reduction</strong> in 2 weeks</li><li>🚀 <strong>400x ROI</strong> (first year)</li><li>🔍 <strong>Complete cost visibility</strong> by query, user, dashboard</li></ul><h3>Getting Started: Your Own 3-Phase Optimization</h3><p><strong>Week 1: Quick Wins (Start Here!)</strong></p><ol><li>Review warehouse auto-stop settings (reduce to 5–10 minutes)</li><li>Analyze peak DBU usage, rightsize max DBU limits</li><li>Enable spot instances for non-critical workloads <strong>Expected: 20–30% immediate cost reduction</strong></li></ol><p><strong>Week 2: Zero-Cost Endpoint Monitoring</strong></p><ol><li>Deploy SQL Warehouse API endpoint monitoring (see Phase 2 code)</li><li>Set duration thresholds (15+ minute running queries)</li><li>Alert users in real-time to kill expensive queries <strong>Expected: Additional 10–15% cost reduction</strong></li></ol><p><strong>Week 3–4: Attribution &amp; Strategic Optimization</strong></p><ol><li>Implement attribution notebook (see technical guide below)</li><li>Analyze cost by query source type</li><li>Identify top expensive dashboards</li><li>Optimize auto-refresh rates and query efficiency <strong>Expected: Additional 30–50% cost reduction</strong></li></ol><p><strong>Total Time:</strong> 2 weeks<br><strong>Total Cost:</strong> ~$50/month (attribution compute only)<br><strong>Expected ROI:</strong> 50–400x first year<br><strong>Difficulty:</strong> Phase 1–2 (Easy), Phase 3 (Moderate)</p><h3>Next Steps</h3><h3>What’s Next?</h3><p>This post focused on <strong>query-level cost attribution</strong> for SQL Warehouses. Once you have this attribution data, you can integrate it into a near real-time monitoring and alerting system.</p><p><strong>Continue Reading:</strong></p><ul><li><strong>[Near Real-Time SQL Warehouse Monitoring]</strong>: How to alert users within 5 minutes of expensive queries (includes integration patterns, alerting logic, and email templates)</li></ul><h3>Related Content</h3><ul><li><strong>[5-Minute Cloud Monitoring Setup]</strong>: Quick start guide for basic monitoring</li><li><strong>[Interactive Cluster Cost Attribution]</strong>: Extend attribution to all-purpose clusters</li></ul><h3>About the Author</h3><p>Senior Data Engineering Manager with 20+ years of experience building petabyte-scale data platforms and optimizing cloud infrastructure. Passionate about making data engineering more efficient and cost-effective.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fb9c8ac22ea8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Databricks Automate Delta Table Cleanup for Cloud Cost Savings]]></title>
            <link>https://medium.com/@rrajeshwaris/databricks-automate-delta-table-cleanup-for-cloud-cost-savings-e27411a60991?source=rss-a206d8363103------2</link>
            <guid isPermaLink="false">https://medium.com/p/e27411a60991</guid>
            <category><![CDATA[databricks-unity-catalog]]></category>
            <category><![CDATA[delta-tables]]></category>
            <category><![CDATA[cloud-cost-optimization]]></category>
            <dc:creator><![CDATA[Rajeshwari Raghuraman]]></dc:creator>
            <pubDate>Wed, 23 Oct 2024 16:13:18 GMT</pubDate>
            <atom:updated>2026-04-01T21:37:06.395Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/64/0*r69W-9e6LjYNemHX.png" /></figure><h3>Key Features</h3><p>This script is designed to automate the optimization of all the tables across multiple schemas.</p><ul><li><strong>Determine if a table is a Delta table:</strong> Checks and identifies Delta tables.</li><li><strong>Optimize Delta tables:</strong> Performs optimization on Delta tables to improve query performance, which can lead to reduced cloud storage and compute costs.</li><li><strong>Vacuum Delta tables:</strong> Cleans up old versions of the data that are no longer needed, with the ability to retain data for a specified number of hours. This not only saves storage space but also contributes to cost savings in cloud storage.</li></ul><h3>Functions</h3><ul><li>delta_utility: Main function to process tables based on the specified action (OPTIMIZE, VACUUM).</li><li>is_delta: Checks if a given table is a Delta table.</li><li>optimize_delta: Optimizes a given Delta table.</li><li>vacuum_tables: Vacuums a given Delta table, retaining data for a specified number of hours.</li></ul><h3>Usage</h3><p>To use the script, call the delta_utility function with the schema name and action to perform the desired utility operation on Delta tables. The function supports the following actions:</p><p>This script can help with cloud cost savings by optimizing the storage and compute resources used by Delta tables, ensuring efficient data management and performance. Recommended to schedule this job weekly in a time when the data is not actively updated to the tables.</p><pre>from pyspark.sql import functions as F<br>from datetime import datetime<br>from pyspark.sql.types import StructType, StructField, StringType, TimestampType</pre><pre>def delta_utility(schema_name, action, ndays=7, hrs=168):<br> cnt_tables = 0<br> cnt_delta = 0<br> spark.sql(f&quot;USE {schema_name}&quot;)</pre><pre> table_list = spark.sql(&quot;show tables&quot;).select(&#39;tableName&#39;).toPandas()[&#39;tableName&#39;].tolist()<br> view_list = spark.sql(&quot;show views&quot;).select(&#39;viewName&#39;).toPandas()[&#39;viewName&#39;].tolist()<br> tables = list(set(table_list) - set(view_list))</pre><pre> exception_list = []<br> function_name = &quot;delta_optimize&quot;</pre><pre> for table_name in tables:<br> cnt_tables += 1<br> try:<br> res = is_delta(table_name)<br> if res == True:<br> cnt_delta += 1<br> if action == &quot;ALTER&quot;:<br> alter_delta(table_name, ndays, exception_list)<br> elif action == &quot;OPTIMIZE&quot;:<br> optimize_delta(table_name, exception_list)<br> elif action == &quot;VACUUM&quot;:<br> vacuum_tables(table_name, hrs, exception_list)<br> except Exception as e:<br> exception_list.append((function_name, schema_name+&#39;.&#39;+table_name, str(e), datetime.now(), action)) </pre><pre> # Define the schema<br> schema = StructType([<br> StructField(&quot;function_name&quot;, StringType(), True),<br> StructField(&quot;table_name&quot;, StringType(), True),<br> StructField(&quot;exception&quot;, StringType(), True),<br> StructField(&quot;execution_date&quot;, TimestampType(), True),<br> StructField(&quot;action&quot;, StringType(), True)<br> ])</pre><pre> if exception_list:<br> # Create the dataframe for the exception list using the schema<br> exception_df = spark.createDataFrame(exception_list, schema)<br> # Write the DataFrame to a Delta table<br> exception_df.write.format(&quot;delta&quot;).mode(&quot;append&quot;).saveAsTable(&quot;your_schema.exceptions_log&quot;)<br> else:<br> print(&quot;No exceptions to log.&quot;)</pre><pre> print(&quot;Total count of tables with valid name &quot;, cnt_tables)<br> print(&quot;Total count of delta tables &quot;, cnt_delta) <br> return(cnt_tables, cnt_delta)</pre><pre>def is_delta(table_name):<br> try:<br> table_type = spark.sql(f&quot;DESC EXTENDED {table_name}&quot;) \<br> .where(&quot;col_name = &#39;Type&#39;&quot;) \<br> .select(&quot;data_type&quot;) \<br> .collect()[0].data_type<br> is_delta = False<br> if table_type == &quot;MANAGED&quot;:<br> desc_table = spark.sql(f&quot;DESC EXTENDED {table_name}&quot;) \<br> .where(&quot;col_name = &#39;Provider&#39;&quot;) \<br> .select(&quot;data_type&quot;) \<br> .collect()[0].data_type<br> if desc_table == &quot;delta&quot;:<br> is_delta = True<br> return is_delta<br> except Exception as e:<br> exception_list.append((function_name,schema_name+&#39;.&#39;+table_name, str(e), datetime.now(), &quot;is_delta&quot;))<br> print(f&quot;Error checking if table {table_name} is a Delta table: {e}&quot;)<br> return False</pre><pre>def optimize_delta(table_name, exception_list):<br> try:<br> spark.sql(f&quot;OPTIMIZE {table_name}&quot;)<br> except Exception as e:<br> exception_list.append((function_name,schema_name+&#39;.&#39;+table_name, str(e), datetime.now(), &quot;optimize_delta&quot;))<br> print(f&quot;Error optimizing table {table_name}: {e}&quot;)</pre><pre>def vacuum_tables(table_name, hrs, exception_list):<br> try:<br> spark.sql(f&quot;VACUUM {table_name} RETAIN {hrs} HOURS&quot;)<br> except Exception as e:<br> exception_list.append((function_name,schema_name+&#39;.&#39;+table_name, str(e), datetime.now(), &quot;vacuum_tables&quot;))<br> print(f&quot;Error vacuuming table {table_name}: {e}&quot;)</pre><pre>import json</pre><pre># DEFINE THE VARIABLES</pre><pre>dbutils.widgets.text(&quot;action&quot;, &quot;&quot;)<br>dbutils.widgets.text(&quot;ndays&quot;, &quot;&quot;)<br>dbutils.widgets.text(&quot;hrs&quot;, &quot;&quot;)<br>dbutils.widgets.text(&quot;schemas&quot;, &#39;&#39;&#39;[&quot;schema1&quot;,&quot;schema1&quot;]&#39;&#39;&#39;)</pre><pre>schemas = json.loads(dbutils.widgets.get(&quot;schemas&quot;))<br>action = dbutils.widgets.get(&quot;action&quot;)<br>ndays = dbutils.widgets.get(&quot;ndays&quot;)<br>hrs = dbutils.widgets.get(&quot;hrs&quot;)</pre><pre>for schema in schemas:<br> delta_utility(schema,action,ndays, hrs)</pre><p><em>Originally published at </em><a href="https://medium.com/@rraghuraman/databricks-automate-delta-table-cleanup-for-cloud-cost-savings-ff35f54dea52"><em>https://medium.com</em></a><em> on October 23, 2024.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e27411a60991" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>