<?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 Pravin Kumar on Medium]]></title>
        <description><![CDATA[Stories by Pravin Kumar on Medium]]></description>
        <link>https://medium.com/@nivarp1995?source=rss-627e01939eae------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*tLJY0KpmsBz1nlbp90870g.png</url>
            <title>Stories by Pravin Kumar on Medium</title>
            <link>https://medium.com/@nivarp1995?source=rss-627e01939eae------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 09:59:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@nivarp1995/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[What Did This Row Look Like Yesterday? Introducing SCD Type 4]]></title>
            <link>https://medium.com/@nivarp1995/what-did-this-row-look-like-yesterday-introducing-scd-type-4-de10bbe53886?source=rss-627e01939eae------2</link>
            <guid isPermaLink="false">https://medium.com/p/de10bbe53886</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[delta-lake]]></category>
            <dc:creator><![CDATA[Pravin Kumar]]></dc:creator>
            <pubDate>Sat, 17 Oct 2026 08:01:17 GMT</pubDate>
            <atom:updated>2026-10-17T08:01:17.495Z</atom:updated>
            <content:encoded><![CDATA[<h3>What Did This Row Look Like Yesterday? Teaching Your One Big Table to Remember — Introducing SCD Type 4</h3><h3>History Without Extra Rows: Capturing Column Changes Inside a Single MERGE</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ornJBm-ZDyCbg24KYd1iPg.png" /></figure><p><em>A take on slowly changing dimensions for One Big Table architectures.</em></p><p>If you build analytics on top of wide, denormalized tables, you’ve probably had this conversation:</p><blockquote><em>“Can we see what this value was last month?” “Sure. Well. Not really. It got overwritten.”</em></blockquote><p>This post walks through why the classic SCD types fight with One Big Table (OBT) designs, and a pattern I’ve been using on Delta Lake that keeps one row per key while still preserving column-level history. It’s implemented as a single flag, capture_column_changes, in a reusable merge_to_table helper.</p><h3>A quick refresher on SCD types</h3><p>Slowly Changing Dimensions (SCD) describe how a warehouse deals with attributes that change over time.</p><p><strong>Example:</strong> A device goes from firmware 4.2 to 4.3 and from “active” to “maintenance”.</p><h3>Type 1: overwrite</h3><p>The new value replaces the old one. Nothing else.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*GRDf94syxaY9ecyj99pS5g.png" /><figcaption>scd-type1-before</figcaption></figure><p>After a load with firmware 4.3:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/590/1*MpSLRVg3skTDOT6TPSkhtQ.png" /><figcaption>scd-type1-after</figcaption></figure><p>Simple, cheap, one row per key. History is destroyed.</p><h3>Type 2: new row per change</h3><p>The old row is closed and a new one is inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lActOq3bxZqR6P9_ksZMcA.png" /><figcaption>scd-type2</figcaption></figure><p>Complete history, but the table is now many-rows-per-key.</p><h3>Type 3: previous value in a column</h3><p>Keep the current and one prior value side by side.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*HF76pI0yUpoDvsWwBYdi8A.png" /><figcaption>scd-type3</figcaption></figure><p>One row per key, but only one step of history, and you need a new column for every attribute you want to track.</p><h3>Where this breaks for One Big Table</h3><p>A One Big Table (OBT) is popular because consumers can query it without joins and without thinking about modelling. That promise is exactly what the SCD types undermine.</p><p><strong>Type 1</strong> is the natural fit for an OBT, and it is also the one that loses information. Audit, debugging, and “when/why did this number move?” questions have no answer.</p><p><strong>Type 2</strong> preserves history by changing the grain of the table. Problems follow:</p><ul><li>Every consumer must remember WHERE is_current = true. Forgetting it silently double counts.</li><li>Row counts grow with change frequency, not entity count. On a wide table with dozens of attributes, one changed attribute duplicates the other forty.</li><li>Any change in any column creates a whole new wide row, so storage and scan costs scale badly.</li><li>Downstream joins between OBTs need point-in-time logic on both sides.</li></ul><p><strong>Type 3</strong> keeps the grain but doesn’t scale with width. Tracking history for 40 columns means 40 more columns, and you still only see one change back.</p><p>So the choice is: lose history, lose the grain, or lose width. None of them is great when the whole point is a flat, wide, easy table.</p><h3>The idea: keep the history inside the row</h3><p>What if the row carried its own change log, as a nested column?</p><pre>column_change_history  array&lt;struct&lt;<br>    column_name: string,<br>    old_value:   string,<br>    new_value:   string,<br>    changed_at:  timestamp<br>&gt;&gt;</pre><p>The grain stays one row per key, like Type 1. Every update appends one entry per changed column, so history is unbounded, unlike Type 3. And there’s no is_current flag and no duplicated wide rows, unlike Type 2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YOrX3N3tts7CsplFsup2rw.png" /><figcaption>scd types comparison</figcaption></figure><h3>How it works</h3><h3>The key observation</h3><p>In a Delta MERGE ... WHEN MATCHED THEN UPDATE SET ..., expressions on the right-hand side can reference both target.col (the value before the update) and source.col (the incoming value). The old value is already in scope. So we can compute the history entry in the same statement that performs the update. There is no need to join back to the table, read the Change Data Feed, or run a second pass.</p><h3>A worked example</h3><p>Starting state, keyed on device_id:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hbR8Eeh3S3yecV4rRvsbvg.png" /></figure><p>Incoming batch:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*d6NQp8vokzYvbXizvHv4dg.png" /></figure><p>After the merge:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IkKrU70W8hvGyP4QNLM-Gw.png" /></figure><p>The column_change_history value for D-100 is an array of two structs:</p><pre>[<br>  {<br>    &quot;column_name&quot;: &quot;firmware&quot;,<br>    &quot;old_value&quot;:   &quot;4.2&quot;,<br>    &quot;new_value&quot;:   &quot;4.3&quot;,<br>    &quot;changed_at&quot;:  &quot;2026-10-17T02:10:00&quot;<br>  },<br>  {<br>    &quot;column_name&quot;: &quot;status&quot;,<br>    &quot;old_value&quot;:   &quot;active&quot;,<br>    &quot;new_value&quot;:   &quot;maintenance&quot;,<br>    &quot;changed_at&quot;:  &quot;2026-10-17T02:10:00&quot;<br>  }<br>]</pre><p>D-100 got two entries. D-200 was not rewritten at all. D-300 was inserted with an empty history.</p><h3>The flow at a glance</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8p-E2pI_N691_Do4VBK-7A.png" /></figure><h3>The generated MERGE</h3><p>For tracked columns site, firmware and status, the statement is conceptually:</p><pre>MERGE INTO catalog.schema.devices AS target<br>USING source AS source<br>ON target.device_id &lt;=&gt; source.device_id<br><br>WHEN MATCHED AND (<br>       NOT (target.site     &lt;=&gt; source.site)<br>    OR NOT (target.firmware &lt;=&gt; source.firmware)<br>    OR NOT (target.status   &lt;=&gt; source.status)<br>) THEN UPDATE SET<br>    site              = source.site,<br>    firmware          = source.firmware,<br>    status            = source.status,<br>    updated_timestamp = current_timestamp(),<br>    column_change_history = concat(<br>        coalesce(<br>            target.column_change_history,<br>            CAST(array() AS array&lt;struct&lt;column_name:string,old_value:string,<br>                                         new_value:string,changed_at:timestamp&gt;&gt;)<br>        ),<br>        filter(<br>            array(<br>                CASE WHEN NOT (target.site &lt;=&gt; source.site) THEN named_struct(<br>                    &#39;column_name&#39;, &#39;site&#39;,<br>                    &#39;old_value&#39;,   CAST(target.site AS STRING),<br>                    &#39;new_value&#39;,   CAST(source.site AS STRING),<br>                    &#39;changed_at&#39;,  current_timestamp()) END,<br>                CASE WHEN NOT (target.firmware &lt;=&gt; source.firmware) THEN named_struct(<br>                    &#39;column_name&#39;, &#39;firmware&#39;,<br>                    &#39;old_value&#39;,   CAST(target.firmware AS STRING),<br>                    &#39;new_value&#39;,   CAST(source.firmware AS STRING),<br>                    &#39;changed_at&#39;,  current_timestamp()) END,<br>                CASE WHEN NOT (target.status &lt;=&gt; source.status) THEN named_struct(<br>                    &#39;column_name&#39;, &#39;status&#39;,<br>                    &#39;old_value&#39;,   CAST(target.status AS STRING),<br>                    &#39;new_value&#39;,   CAST(source.status AS STRING),<br>                    &#39;changed_at&#39;,  current_timestamp()) END<br>            ),<br>            x -&gt; x IS NOT NULL<br>        )<br>    )<br><br>WHEN NOT MATCHED THEN INSERT (<br>    device_id, site, firmware, status, created_timestamp, column_change_history<br>) VALUES (<br>    source.device_id, source.site, source.firmware, source.status,<br>    current_timestamp(),<br>    CAST(array() AS array&lt;struct&lt;column_name:string,old_value:string,<br>                                 new_value:string,changed_at:timestamp&gt;&gt;)<br>);</pre><h3>Why each piece is there</h3><ul><li><strong>&lt;=&gt; (null-safe equality).</strong> NULL -&gt; &#39;x&#39; is a real change, NULL -&gt; NULL is not. A plain = would yield NULL and silently skip both.</li><li><strong>The </strong><strong>WHEN MATCHED AND (...) condition.</strong> Rows where nothing differs are never touched. No rewrite, no updated_timestamp bump, no history noise. Reloading the same batch is idempotent.</li><li><strong>One </strong><strong>CASE per tracked column.</strong> Each returns a struct when that column changed, otherwise NULL.</li><li><strong>filter(array(...), x -&gt; x IS NOT NULL).</strong> Strips the NULLs so only real changes are appended.</li><li><strong>coalesce(target.history, empty).</strong> Rows that predate the feature, or were bootstrapped with a NULL history, still append cleanly.</li><li><strong>CAST(array() AS array&lt;struct&lt;...&gt;&gt;).</strong> A bare array() is array&lt;string&gt;, which causes a type mismatch inside concat. The explicit cast avoids it.</li><li><strong>CAST(... AS STRING).</strong> Old and new values are stored as strings so one uniform struct can describe columns of any type.</li></ul><h3>Which columns are tracked</h3><p>Everything in the incoming DataFrame except the merge keys (they are what identifies the row, so they cannot “change”)</p><h3>Generating it in code</h3><p>Nobody should hand-write that SQL for a 40-column table. It’s built from the DataFrame schema:</p><pre>HISTORY_TYPE = (<br>    &quot;array&lt;struct&lt;column_name:string,old_value:string,&quot;<br>    &quot;new_value:string,changed_at:timestamp&gt;&gt;&quot;<br>)</pre><pre>compare_cols = [c for c in df.columns]<br>change_structs = []<br>for c in compare_cols:<br>    changed = f&quot;NOT (target.`{c}` &lt;=&gt; source.`{c}`)&quot;<br>    change_structs.append(<br>        f&quot;CASE WHEN {changed} THEN named_struct(&quot;<br>        f&quot;&#39;column_name&#39;, &#39;{c}&#39;, &quot;<br>        f&quot;&#39;old_value&#39;, CAST(target.`{c}` AS STRING), &quot;<br>        f&quot;&#39;new_value&#39;, CAST(source.`{c}` AS STRING), &quot;<br>        f&quot;&#39;changed_at&#39;, current_timestamp()) END&quot;<br>    )<br>new_entries = &quot;filter(array(&quot; + &quot;, &quot;.join(change_structs) + &quot;), x -&gt; x IS NOT NULL)&quot;<br>empty_history = f&quot;CAST(array() AS {HISTORY_TYPE})&quot;<br>update_set[&quot;column_change_history&quot;] = (<br>    f&quot;concat(coalesce(target.`column_change_history`, {empty_history}), {new_entries})&quot;<br>)<br>insert_vals[&quot;column_change_history&quot;] = empty_history<br>(<br>    delta_table.alias(&quot;target&quot;)<br>    .merge(df.alias(&quot;source&quot;), merge_cond)<br>    .whenMatchedUpdate(condition=update_cond, set=update_set)<br>    .whenNotMatchedInsert(values=insert_vals)<br>    .execute()<br>)</pre><p>On first run, when the table is bootstrapped from the first batch, the history column is created automatically with the correct struct type (values start as NULL, which the coalesce handles).</p><h3>Querying the history</h3><p>Consumers who don’t care about history just ignore the column. Those who do can explode it.</p><pre>-- Every firmware change in the last 30 days<br>SELECT device_id, h.old_value, h.new_value, h.changed_at<br>FROM devices<br>LATERAL VIEW explode(column_change_history) t AS h<br>WHERE h.column_name = &#39;firmware&#39;<br>  AND h.changed_at &gt;= current_date() - INTERVAL 30 DAYS;</pre><pre>-- Which attributes churn the most?<br>SELECT h.column_name, count(*) AS changes<br>FROM devices<br>LATERAL VIEW explode(column_change_history) t AS h<br>GROUP BY h.column_name<br>ORDER BY changes DESC;</pre><pre>-- Devices that flipped status more than 3 times<br>SELECT device_id, count(*) AS flips<br>FROM devices<br>LATERAL VIEW explode(column_change_history) t AS h<br>WHERE h.column_name = &#39;status&#39;<br>GROUP BY device_id<br>HAVING count(*) &gt; 3;</pre><h3>Trade-offs</h3><p>Being honest about the limits:</p><ul><li><strong>Unbounded arrays.</strong> A column that changes constantly will grow the array without limit. Don’t track high-churn metrics (counters, utilization percentages). Keep this for slowly changing attributes, or trim the array periodically.</li><li><strong>String-typed values.</strong> old_value and new_value are strings. Great for uniformity, but typed analysis needs a cast back.</li><li><strong>Changes, not snapshots.</strong> You get a change log, not a ready-made “row as of date X”. You can reconstruct it by replaying the log backwards, but if point-in-time reconstruction is a core need, Type 2 or Delta time travel is a better fit.</li><li><strong>Row size.</strong> Large histories make rows heavier to rewrite on every update. Watch the array length on hot rows.</li><li><strong>Batch granularity.</strong> Entries are as precise as your load cadence. Two changes between loads appear as one net change.</li></ul><h3>When to use it</h3><p><em>Good fit:</em></p><ul><li>One Big Table designs where consumers must not deal with versioned rows</li><li>Slowly changing attributes: ownership, firmware, status, topology or region assignments</li><li>Audit and debugging: “what changed on this entity, and when?”</li></ul><p><em>Not a fit:</em></p><ul><li>Full bi-temporal modelling</li><li>Frequently changing metrics</li><li>Workloads that need arbitrary as-of joins across large fact tables</li></ul><h3>Wrapping up</h3><p>SCD Types 1, 2 and 3 each trade away something an OBT wants: history, grain, or width. Putting the change log in a nested column on the row gives you most of the benefit of each, with the cost of a single extra array column and a slightly smarter MERGE.</p><p>The part I find most satisfying is that it needs no new infrastructure. It’s the same MERGE you were already running, with one more expression in the SET clause.</p><p>#Databricks #Delta Lake #Data Engineering #SCD #Data Modeling #One Big Table</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=de10bbe53886" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Threshold-Aware Data Quality Gate with Databricks DQX]]></title>
            <link>https://medium.com/@nivarp1995/building-a-threshold-aware-data-quality-gate-with-databricks-dqx-d9b4236fcbe7?source=rss-627e01939eae------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9b4236fcbe7</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pyspark]]></category>
            <dc:creator><![CDATA[Pravin Kumar]]></dc:creator>
            <pubDate>Fri, 25 Sep 2026 14:33:59 GMT</pubDate>
            <atom:updated>2026-09-29T14:51:18.478Z</atom:updated>
            <content:encoded><![CDATA[<p><em>How we turned “pass/fail” data quality checks into something engineers actually trust</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*bkJ-INdOK2d9W6WJ" /></figure><h3>The problem with binary data quality checks</h3><p>Most data quality frameworks give you a single verdict: pass or fail. But real pipelines don’t work that way. A nightly job that ingests millions of rows from a dozen upstream feeds will <em>always</em> have some bad data — a few null values here, a handful of out-of-range measurements there. If your DQ check fails the whole pipeline every time 0.01% of rows are dirty, engineers stop trusting the alerts and start ignoring them.</p><p>What you actually want is: <strong>“tell me when the failure rate crosses a threshold I’ve decided matters — not every time a single row is imperfect.”</strong></p><p>That’s the model we built on top of <a href="https://github.com/databrickslabs/dqx">Databricks DQX</a>, the open-source data quality library from Databricks Labs. This post walks through the design: how rules are declared in YAML, how each rule carries its own failure-rate threshold as metadata, how the gate evaluates those thresholds, and how failures are surfaced to on-call engineers.</p><h3>Declaring rules — and their tolerance — in YAML</h3><p>DQX lets you define checks declaratively instead of writing imperative PySpark assertions. Each rule has a criticality (error or warn), a check function, and — this is the part we added — a user_metadata block carrying a threshold_pct:</p><pre>checks:<br>  # Join keys — zero tolerance, any failure stops the pipeline<br>  - criticality: error<br>    name: customer_id_is_not_null_and_not_empty<br>    user_metadata:<br>      threshold_pct: 0<br>    check:<br>      function: is_not_null_and_not_empty<br>      arguments:<br>        column: customer_id<br><br>  - criticality: error<br>    name: order_id_device_id_is_unique<br>    user_metadata:<br>      threshold_pct: 0<br>    check:<br>      function: is_unique<br>      arguments:<br>        columns:<br>          - order_id<br>          - device_id<br>          - event_date<br><br>  # Metric columns - lenient by default, some sensors legitimately drop out<br>  - criticality: warn<br>    name: signal_strength_is_not_null<br>    check:<br>      function: is_not_null_and_not_empty<br>      arguments:<br>        column: signal_strength<br><br>  # Percentage columns - strict, only a small blip is tolerated<br>  - criticality: warn<br>    name: utilization_pct_is_in_range<br>    user_metadata:<br>      threshold_pct: 5<br>    check:<br>      function: is_in_range<br>      arguments:<br>        column: utilization_pct<br>        min_limit: 0<br>        max_limit: 100</pre><p>Notice the pattern:</p><ul><li><strong>Join keys and uniqueness constraints</strong> are criticality: error with threshold_pct: 0 — even one bad row is a hard stop, because downstream merges will silently corrupt data.</li><li><strong>Metric/measurement columns</strong> are criticality: warn with no explicit threshold, falling back to a lenient default (we use 30%) — sensors and partner feeds are noisy, and that&#39;s expected.</li><li><strong>Percentage/rate columns</strong> get a tight, explicit threshold (5%) — a large spike here usually means a genuine upstream regression, not just sensor noise.</li></ul><p>The threshold isn’t a property of the <em>engine</em> — it’s metadata the pipeline author attaches to each rule, right next to the check it governs. That keeps the tolerance decision in the same file (and the same code review) as the rule itself.</p><h3>Evaluating thresholds against DQX’s quarantine output</h3><p>DQX splits your DataFrame into valid and quarantine outputs, with the quarantine rows carrying an _errors / _warnings struct column that records which named check(s) each row failed. Our gate reads that structure, groups failures by check name, and compares the resulting failure percentage against the threshold declared in YAML:</p><pre>def run_quality_gate(<br>    spark,<br>    df,<br>    yaml_path=None,<br>    quarantine_table=None,<br>    layer=&quot;gold&quot;,<br>    default_threshold_pct=30,<br>    custom_check_functions=None,<br>):<br>    &quot;&quot;&quot;Run DQX checks for a medallion layer and evaluate per-rule thresholds.<br>    Returns (valid_df, quarantine_df, quarantine_count, results). Each result<br>    entry includes threshold_exceeded and a per-check breakdown with<br>    failure_pct vs. threshold_pct for every rule.<br>    &quot;&quot;&quot;<br>    rules = load_rules_from_yaml(yaml_path)<br>    dq_engine = DQEngine(WorkspaceClient())<br>    valid_df, quarantine_df, observation = (<br>        dq_engine.apply_checks_by_metadata_and_split(<br>            df, rules, custom_check_functions=custom_check_functions<br>        )<br>    )<br>    total_count = df.count()<br>    threshold_mapping = build_threshold_mapping(rules, default_threshold_pct)<br>    # error-criticality checks can fail the job; warn-criticality never do<br>    any_exceeded, error_details = analyse_quarantine(<br>        quarantine_df, total_count, threshold_mapping,<br>        default_threshold_pct, column=&quot;_errors&quot;, criticality=&quot;error&quot;,<br>    )<br>    _, warning_details = analyse_quarantine(<br>        quarantine_df, total_count, threshold_mapping,<br>        default_threshold_pct, column=&quot;_warnings&quot;, criticality=&quot;warn&quot;,<br>    )<br>    results = [{<br>        &quot;layer&quot;: layer,<br>        &quot;quarantined&quot;: quarantine_df.count(),<br>        &quot;total_count&quot;: total_count,<br>        &quot;threshold_exceeded&quot;: any_exceeded,<br>        &quot;check_details&quot;: error_details + warning_details,<br>    }]<br>    return valid_df, quarantine_df, quarantine_df.count(), results<br></pre><p>The key design decision: <strong>only </strong><strong>error-criticality checks can ever set </strong><strong>threshold_exceeded = True.</strong> warn-criticality failures are always recorded — with their own failure percentage and threshold — but they never stop the pipeline. This gives pipeline authors a two-speed failure model without writing any extra conditional logic: criticality decides whether a breach is fatal, threshold decides how much is tolerated before it counts as a breach at all.</p><h3>Failing loudly — but only when it matters</h3><p>Once the gate has computed threshold_exceeded per rule, the calling notebook makes the final call. This is deliberately the <em>only</em> place that raises:</p><pre>_threshold_errors = [r for r in _results if r.get(&quot;threshold_exceeded&quot;)]<br>if _threshold_errors:<br>    msgs = [r[&quot;error_msg&quot;] for r in _threshold_errors if r.get(&quot;error_msg&quot;)]<br>    logger.error(f&quot;DQ gate failed — threshold exceeded:\n{chr(10).join(msgs)}&quot;)<br>    raise ValueError(<br>        &quot;DQ gate failed — failure threshold exceeded:\n&quot; + &quot;\n&quot;.join(msgs)<br>    )</pre><p>Separating “detect breaches” (run_quality_gate) from &quot;decide what to do about them&quot; (the notebook orchestration cell) keeps the library reusable: a backfill job might want to log and continue, while a production job wants to halt the merge entirely. Both call the same gate function — they just differ in what they do with threshold_exceeded.</p><h3>Alerting on-call engineers with the actual numbers</h3><p>A raised exception is good for stopping bad data from landing in a production table, but someone still has to <em>know</em> it happened, at 3 a.m., in a way that tells them exactly what broke. send_dq_alert takes the same results payload and publishes a plain-text summary to an SNS topic (fanned out to email, Slack, or PagerDuty by the subscribers — not by this code):</p><pre>def send_dq_alert(results, layer, notification_config, pipeline_name=&quot;&quot;):<br>    &quot;&quot;&quot;Publish DQ failure notifications to an SNS topic.  <br>    Only publishes when there are failures; no-ops when every check passed<br>    or notifications are disabled in notification_config.<br>    &quot;&quot;&quot;<br>    if not notification_config.get(&quot;enabled&quot;, False):<br>        return<br>    failures = get_failures(results)<br>    if not failures:<br>        return<br>    any_threshold_exceeded = any(r.get(&quot;threshold_exceeded&quot;) for r in failures)<br>    subject = build_subject(layer, pipeline_name, any_threshold_exceeded)<br>    message = build_alert_message(failures, layer, pipeline_name)<br>    publish_to_sns(<br>        message, subject,<br>        notification_config[&quot;topic_arn&quot;],<br>        notification_config.get(&quot;aws_region&quot;, &quot;us-east-1&quot;),<br>    )</pre><p>The message body renders each failing check as a row in a table — check name, rows failed, total rows evaluated, failure rate, configured threshold, and status — so the on-call engineer sees the exact same numbers the gate used to make its decision:</p><pre>| Check                          | Failed | Total     | Rate  | Threshold | Status   |<br>|---------------------------------|-------:|----------:|------:|----------:|----------|<br>| customer_id_is_not_null...       |     50 |   500,000 | 0.01% |     0.0%  | EXCEEDED |<br>| utilization_pct_is_in_range      |    340 |   500,000 | 0.07% |     5.0%  | ok       |</pre><p>No digging through Spark UI, no re-running the job to see what tripped — the alert <em>is</em> the root-cause summary.</p><h3>Overall flow for threshold based dq check</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lno85jA80Q89N7BHRy98BA.png" /><figcaption>threshold-based-dq-check-flow</figcaption></figure><h3>Why this pattern is worth stealing</h3><ol><li><strong>Thresholds live with the rule, not the engine.</strong> A reviewer looking at a YAML diff sees both the new check <em>and</em> how much of it is tolerated, in one place.</li><li><strong>Criticality and threshold are orthogonal.</strong> error vs. warn decides whether a breach can ever halt the job; threshold_pct decides what counts as a breach. Combining them gives you four practical behaviors (hard-stop-on-any-failure, hard-stop-on-drift, log-only, log-only-with- drift-tracking) without new code.</li><li><strong>The gate only detects; the caller decides.</strong> Keeping the raise outside the library function means the same quality gate serves both “fail fast” production jobs and “log and continue” backfills.</li><li><strong>Alerts carry the same numbers as the decision.</strong> Because the alert payload is built from the exact same check_details structure the gate used internally, there&#39;s no risk of the notification saying something different from what actually happened.</li><li><strong>Alerts get sent to SNS, and it fans-out to any system. </strong>Some teams may be listening for alerts in a Slack channel, some in Teams, few others through classic emails. Whatever the destination is, it can be configured in SNS, and its decoupled from the implementation.</li></ol><p>If you’re building on top of DQX, this threshold-plus-criticality pattern is a small addition to the metadata schema that pays for itself the first time a noisy upstream feed <em>doesn’t</em> wake someone up at 3 a.m. for a 0.01% blip — and <em>does</em> wake them up for a real regression.</p><p>#DataEngineering #Databricks #DataQuality #ApacheSpark #Pyspark #DQX #LakehouseArchitecture</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9b4236fcbe7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data backfills — a simple yet overlooked feature]]></title>
            <link>https://medium.com/@nivarp1995/data-backfills-a-simple-yet-overlooked-feature-0400b0a666ba?source=rss-627e01939eae------2</link>
            <guid isPermaLink="false">https://medium.com/p/0400b0a666ba</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[delta-lake]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Pravin Kumar]]></dc:creator>
            <pubDate>Fri, 11 Sep 2026 06:30:58 GMT</pubDate>
            <atom:updated>2026-09-11T06:30:58.709Z</atom:updated>
            <content:encoded><![CDATA[<p>The classic case of missing backfilling feature.</p><p>Backfills are the most integral part of any data pipeline, and the backbone of Machine Learning prediction algorithms, especially for time series forecast. It should be treated as a separate feature in your data platform, rather than as a nice-to-have add-on.</p><p>Imagine this scenario — you come up with an excellent data architecture for predicting network traffic analysis and growth by following the Medallion standards (Bronze, Silver and Gold) and data modelling techniques (Star, Snowflake, Kimball, etc). You also store the data in an open table format with all the niche capabilities with data quality checks, orchestration, alerts, and every other operational piece. Everything looks fine, and your pipeline starts running successfully, and suddenly there is an ask from the Machine Learning engineers — “We need historical data for more accurate prediction”. And thats when you realize your pipeline was not ready for backfills! 🤯</p><p>Even though the newer table formats like Delta and Iceberg store history as versions, its practically not easy and straightforward for anyone other than data engineers to query the historical data without looking at the metadata. Hence its always better to add a date column in the source and reporting tables/views, which can be the anchor for backfills.</p><p>There can be multiple backfill scenarios such as:</p><ol><li>Today</li><li>One day</li><li>Date window</li><li>Reporting week</li><li>Previous week</li><li>Relative offset from today</li><li>Full history</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S3TppdlEjfefSC_VjeNhuw.jpeg" /></figure><p>Keep in mind all of these need to be automated without any modifications to the code, and both reads and writes should support handling only the data that needs to be backfilled, so its always a good practice to have platform level modules/functions which can handle these automatically based on the chosen option. The data pipelines need to have input paramaters which can take these values and operate accordingly. This is what elevates a normal data team to a self-running DataOps team.</p><p><strong>Pro tips:</strong></p><ul><li><a href="https://docs.databricks.com/aws/en/jobs/backfill-jobs">Databricks supports backfills</a>, but as a best practice, its always better to implement your solution not tied to any vendor platform and make it generic, so that it can work on any platform if a migration happens in future.</li><li>Having a common date format (YYYY-MM-DD works universally) across all your tables and data sources will make the backfills easier without much hassle to find out the correct date to rerun the pipeline.</li><li>While backfilling streaming data is lot more complex and depends on external factors such as retention period, message ordering, watermarking, etc, the suggested approach works very well for batch data processing, considering that over 80% of current data pipelines process data in batches.</li></ul><p>#data #dataengineering #dataops #backfills #delta #snowflake #databricks</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0400b0a666ba" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Visualize PySpark ETL pipelines using Spline]]></title>
            <link>https://medium.com/@nivarp1995/visualize-pyspark-etl-pipelines-using-spline-b925683ec170?source=rss-627e01939eae------2</link>
            <guid isPermaLink="false">https://medium.com/p/b925683ec170</guid>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[spline]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-lineage]]></category>
            <dc:creator><![CDATA[Pravin Kumar]]></dc:creator>
            <pubDate>Sun, 06 Jul 2025 08:23:35 GMT</pubDate>
            <atom:updated>2025-07-06T08:38:52.964Z</atom:updated>
            <content:encoded><![CDATA[<p>One of the most dreaded nightmares a Data Engineer has to face when joining a new team or company is to look at and maintain/extend <strong>undocumented, non-formatted, spaghetti ETL (Extract, Transform and Load) pipelines </strong>without proper naming convention for the tables, columns, functions and variables, and no proper flow of data between the tables. Thanks to <a href="https://www.unitycatalog.io/">Unity Catalog</a> (UC), some of the Governance, Data Lineage and Catalog stuff has been made easier — one of the cool features of UC is <em>column level lineage</em>, which helps to identify what are the dependent columns in the parent table(s) for a child column.</p><p><a href="https://docs.databricks.com/aws/en/data-governance/unity-catalog/data-lineage">https://docs.databricks.com/aws/en/data-governance/unity-catalog/data-lineage</a></p><p>Still, there remains some key unanswered questions from the point of view of not just the Data Engineers, but also the Data Architecture and the Product teams:</p><ol><li><em>From a business POV, what are the transformations involved in the ETL pipeline? How can I efficiently see and understand whats happening in a pipeline, especially if DAG and spark UI are too complex to understand?</em></li><li><em>What will be the impact if a column is dropped/renamed in the middle of a pipeline? How will the data flow be affected?</em></li><li><em>If a column is derived from multiple other columns, what are the transformations involved to derive it?</em></li><li><em>What are the input and output schemas in each step of the pipeline?</em></li><li><em>What are the sources and targets — format, path, etc?</em></li></ol><p>These questions could not be answered by UC (as of now — you never know what features Databricks might add to it in future!)</p><p>Due to the above unknowns which cannot be answered by any existing tool, data teams go as far as maintaining the data catalog and transformations in an Excel sheet (MANUALLY!) which becomes too tedious to create and maintain (duh!), whereas some other teams make use of AI coding assistants like CoPilot to help them explain whats going on in the pipeline and document, neither of which is an efficient way to understand them.</p><h3>Enter Spline — an opensource Data Lineage tracking and Visualization solution</h3><p>After some rigorous search in Google and CoPilot, I came across <strong>Spline</strong> — a free and open-source tool for automated tracking data lineage and data pipeline structure. Looking at its features, it answered most of the above questions which every data team has.</p><p>Spline documentation: <a href="https://absaoss.github.io/spline/">https://absaoss.github.io/spline/</a></p><p>At a high-level, Spline consists of three main parts:</p><ul><li>Spline Server with ArangoDB backend</li><li>Spline Agents integrated with the applications running the Spark ETL code (Scala, Python or Spark SQL)</li><li>Spline UI</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/0*2Za_4esd6IRtB2Oi.png" /><figcaption>Spline architecture</figcaption></figure><p>According to the docs, one of the main motivations of Spline is:</p><blockquote><strong>Documentation of business logic</strong></blockquote><blockquote>Business analysts should get a chance to verify whether jobs were written according to the rules they provided. Moreover, it would be beneficial for them to have up-to-date documentation where they can refresh their knowledge of a project.</blockquote><p>There is also a Getting started example to integrate Databricks with Spline UI!</p><p><a href="https://github.com/AbsaOSS/spline-getting-started/tree/main/spline-on-databricks">https://github.com/AbsaOSS/spline-getting-started/tree/main/spline-on-databricks</a></p><h3>Getting the hands dirty with Spline</h3><p>Now that we have an awesome open-source tool which very well fits our use-case, its time to get our hands dirty by setting up Spline and viewing the data lineage for a sample pyspark ETL pipeline.</p><h4>Requirements</h4><p>Before starting, the following tools should be installed to try out the example:</p><ol><li>Docker</li><li>Docker-compose</li><li>Java 11</li><li>Pyspark</li><li>Spline-agent jar — You could either build it using maven from the source code, or download the jar from Maven artifactory<br>Source-code: <a href="https://github.com/AbsaOSS/spline-spark-agent/tree/develop">https://github.com/AbsaOSS/spline-spark-agent/tree/develop</a><br>Maven artifactory: <a href="https://mvnrepository.com/artifact/za.co.absa.spline.agent.spark">https://mvnrepository.com/artifact/za.co.absa.spline.agent.spark</a></li><li>Delta-storage jar</li></ol><h4>Docker-compose setup</h4><p>Create a new directory for this experiment, and download the docker-compose file for spline services, and start it.</p><pre>mkdir spline-sandbox<br>cd spline-sandbox<br><br># Download the docker-compose and environment variable files<br>wget https://raw.githubusercontent.com/AbsaOSS/spline-getting-started/main/docker/compose.yaml<br>wget https://raw.githubusercontent.com/AbsaOSS/spline-getting-started/main/docker/.env<br><br># Start the docker services<br>SEED=1 docker-compose up</pre><p>Once the spline services start, the logs look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*whhr70VxrawzgNzBUpDpDA.png" /><figcaption>Spline docker-compose services logs</figcaption></figure><p>Spline’s REST API server will be exposed on port 8080, and can be accessed by opening localhost:8080 on your browser.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NKXzrGePxHPeBbvNH9B7YQ.png" /><figcaption>Spline REST server</figcaption></figure><p>Spline UI can be accessed by opening localhost:9090 on your browser.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5JzGCyUE-WK9Ot4trLLHuw.png" /><figcaption>Spline UI</figcaption></figure><h4>Input files</h4><p>Now that we have done the initial setup for spline, lets create some sample data which will be used for the ETL pipeline.</p><p>We will be 2 csv files as inputs — locations.csv and user_favorites.csv.</p><pre># Create the folders required for input and output data<br>mkdir data<br>cd data<br>mkdir input<br>mkdir output<br><br># Create the input csv files<br>cd input<br>touch locations.csv<br>touch user_favorites.csv</pre><p>Open locations.csv in a text editor and enter the following data:</p><pre>id,city,state<br>1,bakersfield,california<br>2,portland,oregon<br>3,anchorage,alaska<br>4,mesa,arizona<br>5,denver,colorado</pre><p>Open user_favorites.csv in a text editor and enter the following data:</p><pre>id,favorite_color,favorite_city<br>1,blue,anchorage<br>2,red,denver<br>3,orange,mesa<br>4,yellow,bakersfield<br>5,purple,portland<br>6,pink,denver</pre><h4>Sample ETL code</h4><p>Next, create a sample pyspark ETL pipeline which reads the above 2 CSV files, does some transformations, and stores the result data in various formats — CSV and delta.</p><pre>from pyspark.sql import SparkSession<br>from pyspark.sql import functions as F<br>from pyspark.sql import Window as w<br><br># Create SparkSession<br>spark = SparkSession.builder.config(&quot;spark.sql.extensions&quot;, &quot;io.delta.sql.DeltaSparkSessionExtension&quot;).config(&quot;spark.sql.catalog.spark_catalog&quot;, &quot;org.apache.spark.sql.delta.catalog.DeltaCatalog&quot;).appName(&quot;MySparkJobDelta_2&quot;).master(&quot;local[*]&quot;).getOrCreate()<br><br># Read user_favorites to DataFrame and create a temporary view<br>user_favorites = (<br>    spark.read.option(&quot;header&quot;, &quot;true&quot;)<br>    .option(&quot;inferschema&quot;, &quot;true&quot;)<br>    .csv(&quot;data/input/user_favorites.csv&quot;)<br>)<br>user_favorites.show()<br><br># Read locations to DataFrame and create a temporary view<br>locations = (<br>    spark.read.option(&quot;header&quot;, &quot;true&quot;)<br>    .option(&quot;inferschema&quot;, &quot;true&quot;)<br>    .csv(&quot;data/input/locations.csv&quot;)<br>)<br>locations.show()<br><br># Join user_favorites and locations, and generate the nicknames<br>nicknames = (<br>    user_favorites<br>    .select(&quot;id&quot;, &quot;favorite_city&quot;, F.concat_ws(&quot; &quot;, &quot;favorite_color&quot;, &quot;favorite_city&quot;).alias(&quot;nickname&quot;))<br>    .join(locations.withColumnRenamed(&quot;id&quot;, &quot;locationsId&quot;), user_favorites.favorite_city == locations.city)<br>)<br>nicknames.show()<br><br># Write output and print final DataFrame to console<br>nicknames.write.mode(&quot;overwrite&quot;).csv(&quot;data/output/nicknames_delta&quot;)<br>nicknames.show(20, False)<br><br>window_spec = w.partitionBy(&quot;favorite_city&quot;).orderBy(F.col(&quot;nickname&quot;).desc())<br>nicknames_partitioned = (<br>    nicknames<br>    .withColumn(&quot;rowNum&quot;, F.dense_rank().over(window_spec))<br>)<br>nicknames_partitioned.show()<br><br>nicknames_partitioned.write.mode(&quot;overwrite&quot;).csv(&quot;data/output/nicknames_partitioned&quot;)<br><br><br>nicknames_cities_grouped = (<br>    nicknames.groupBy(&quot;favorite_city&quot;)<br>    .agg(F.collect_set(&quot;nickname&quot;).alias(&quot;city_nicknames&quot;))<br>    .unionByName(nicknames, allowMissingColumns=True)<br>    .withColumnsRenamed({&quot;id&quot;: &quot;user_id&quot;, &quot;favorite_city&quot;: &quot;user_favorite_city&quot;})<br>    .dropDuplicates()<br>)<br>nicknames_cities_grouped.write.format(&quot;delta&quot;).mode(&quot;overwrite&quot;).option(&quot;overwriteSchema&quot;, &quot;true&quot;).save(&quot;data/output/nicknames_cities_grouped&quot;)</pre><p>Save the above code in a file named <em>my_spark_job.py.</em></p><p>We have a good number of general ETL transformations used in the above pipeline — select, concat, withColumn, withColumnRenamed, withColumnsRenamed, unionByName, dropDuplicates, groupBy and window function.</p><h4>Running the pyspark ETL pipeline</h4><p>To run the pyspark code, run the following command:</p><pre>spark-submit \                                                                                                                         Py venv-3.9 Node system<br> --packages za.co.absa.spline.agent.spark:spark-3.5-spline-agent-bundle_2.12:2.2.3 \<br> --conf spark.sql.queryExecutionListeners=za.co.absa.spline.harvester.listener.SplineQueryExecutionListener \<br> --conf spark.spline.producer.url=http://localhost:8080/producer \<br> my_spark_job.py</pre><p>This will run the ETL pipeline, and store the results in the <strong>data/output</strong> folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*URKC9x99B1d1QGaqxzxLxA.png" /><figcaption>Data output folder</figcaption></figure><h4>Spline UI</h4><p>To view the data lineage in spline UI, go to localhost:9090 in your browser.</p><p>In the <strong>Execution Events </strong>page, you can see the execution event of your pipeline (each write operation in the code will be considered as an execution event).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XWG3pWJf5elOF0MPGer01A.png" /></figure><p>Click on the first event with <em>nicknames_cities_grouped</em> as destination to view the high level execution plan.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FpR1v3huwmKa55_nxBG5yw.png" /></figure><p>To view detailed execution details, click on the Execution details link on the MySparkJobDelta_2 widget.</p><p>The Execution details page shows the transformation details, input and output schema at each stage of the pipeline. You can see which column was used to group by in the aggergation, and the column that was collected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qv825b0WgNZcqzpuPVcY6w.png" /></figure><p>To view the lineage of a column, click on the <em>Output Schema</em> tab on the Selected Node section of a Project, and select the column. A Highlighted attribute widget pops up on the right. Click on Details button besides the Lineage.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dTrsi2xbIXGUVhQRpP6xFA.png" /></figure><p>Here we can see how the user_favorite_city column has been generated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_tk6acevXmo4gbRQHNOXFA.png" /></figure><p>You can also see the child columns which would be impacted if a column is modified. Lets look at the execution which writes to <em>nicknames_partitioned. </em>We need to find out the columns which would be affected if <em>favorite_city</em> column is modified. Click on the final Project stage, select <em>favorite_city</em> column in the Output Schema tab, and click on Impact Details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9fh3REFlRxtD4lcIG12zjQ.png" /></figure><p>We can see that the <em>nickname</em> and <em>rowNum </em>columns depend on the <em>favorite_city</em> column, and would be impacted if it is modified.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d1trsJIE2xXScPKur5sfCQ.png" /></figure><h4>Stop the docker-compose services</h4><p>Once you have completed playing with Spline UI, stop the services by pressing ctrl+c in the terminal, and run <em>docker-compose down.</em></p><h3>Conclusion</h3><p>Spline answers some of the most important questions in the data world in a way which even the non-technical people like Product Owners and Business Analysts can see whats happening to the data. Now that you have seen how easy it is to view the data lineage in Spline UI, the next steps would be to run the Spline services in a production ready environment, and integrate it with your ETL platform (Databricks, EMR, etc).</p><h3>References</h3><ol><li><a href="https://absaoss.github.io/spline/">https://absaoss.github.io/spline/</a></li><li><a href="https://www.capitalone.com/tech/software-engineering/spline-spark-data-lineage/">https://www.capitalone.com/tech/software-engineering/spline-spark-data-lineage/</a></li><li><a href="https://github.com/AbsaOSS/spline-getting-started/tree/main/spline-on-databricks">https://github.com/AbsaOSS/spline-getting-started/tree/main/spline-on-databricks</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b925683ec170" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>