<?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 Asindu Gayangana on Medium]]></title>
        <description><![CDATA[Stories by Asindu Gayangana on Medium]]></description>
        <link>https://medium.com/@asindugayangana?source=rss-1948dd376e1a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*wHRNmLXoKPqc9bD9dFM5zg.jpeg</url>
            <title>Stories by Asindu Gayangana on Medium</title>
            <link>https://medium.com/@asindugayangana?source=rss-1948dd376e1a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 22:01:02 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@asindugayangana/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[How CDF Actually Works in Databricks]]></title>
            <link>https://medium.com/@asindugayangana/how-cdf-actually-works-in-databricks-5848d1f879e3?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/5848d1f879e3</guid>
            <category><![CDATA[cdf-features]]></category>
            <category><![CDATA[cdf-with-streaming]]></category>
            <category><![CDATA[cdf-with-batch-load]]></category>
            <category><![CDATA[cdf-with-dlt]]></category>
            <category><![CDATA[cdf-in-databricks]]></category>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 11:50:48 GMT</pubDate>
            <atom:updated>2026-06-23T11:50:48.966Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Stop reprocessing everything again</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/915/1*9dxLLJ30th22x6SwBoO7pw.png" /></figure><p>Change Data Feed (CDF) is a Delta Lake feature in Databricks that tracks row-level changes — inserts, updates, and deletes — made to a Delta table. It enables downstream processes to consume only the changed data instead of reprocessing the entire dataset.</p><p>In traditional pipelines without CDC at the source, engineers load the full dataset and use overwrite or MERGE operations to stay synchronized. This works, but becomes expensive at scale — every run processes the full dataset even if only a fraction of records changed.</p><p><strong>CDF solves this.</strong> By enabling it on a Delta table, downstream layers read only what changed since the last load, reducing compute costs, latency, and pipeline complexity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RLPAAS-aLKpSjeD4RMYf9A.png" /></figure><h3>A Concrete Example</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TIerogf_x4ezjgGb2cXbhQ.png" /></figure><p>Consider an orders dataset with 6 million records. The source system provides no CDC, and the requirement is to maintain the latest order status. Without CDF, every layer in a Medallion Architecture (Bronze → Silver → Gold) must process and MERGE all 6 million records on every run — even if only a few thousand records changed.</p><p>With CDF enabled on the Bronze table, Silver and Gold layers consume only the changed records since the last cycle.</p><p><strong>Two important caveats before going further:</strong></p><ul><li>CDF only tracks changes <em>after</em> it is enabled. Historical changes before enablement are not available.</li><li>If the source provides no CDC, you still need to extract the full dataset into Bronze. CDF optimizes <em>downstream</em>processing within Delta — it does not reduce what’s pulled from the source.</li></ul><h3>CDF Metadata Columns</h3><p>When CDF is enabled, every change carries three additional columns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rLh6bMM5skNZLmW83NDE-A.png" /></figure><ul><li><strong>insert</strong> — newly added row</li><li><strong>delete</strong> — row removed from the source</li><li><strong>update_preimage</strong> — row value <em>before</em> the update</li><li><strong>update_postimage</strong> — row value <em>after</em> the update</li></ul><p>_commit_version acts as a snapshot marker — a new version is created on every table update or load. _commit_timestamp is saved alongside it. You can extract changes by version or by timestamp (e.g., &quot;give me everything that changed after 12:00 PM&quot; is often easier expressed via timestamp).</p><h4>CDF Supports DELETE Detection</h4><p>This is one of CDF’s biggest advantages. Without CDC or CDF, identifying deleted source records is difficult. CDF emits _change_type = delete, letting downstream tables stay synchronized with source deletions.</p><h3>CDF Is Not a Replacement for CDC</h3><p>A common interview question.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9t8P9AuMxuNvreRbEdFjmg.png" /></figure><h3>Limitations</h3><ul><li>Does not reduce Bronze extraction volume — the source still sends full data</li><li>Does not backfill history from before CDF was enabled</li><li>Does not work on non-Delta tables</li><li>Not a substitute for CDC at the source layer</li></ul><p><strong>Retention note:</strong> CDF data is tied to the Delta transaction log. If VACUUM and retention policies remove old versions, you may lose the ability to read older changes — particularly relevant for long-running or infrequently-scheduled pipelines.</p><h3>Three Ways to Use CDF</h3><ol><li>CDF with Structured Streaming</li><li>CDF with table_changes() for batch loading</li><li>APPLY CHANGES INTO via Lakeflow Declarative Pipelines (formerly DLT)</li></ol><p>The rest of this article covers all three with hands-on code.</p><h3>Enabling CDF on a Table</h3><p><strong>Option A — At table creation</strong> (captures every change from the start):</p><p>sql</p><pre>CREATE OR REPLACE TABLE dev.bronze.sales_cdf (<br>  OrderID INT,<br>  CustomerID STRING,<br>  Product STRING,<br>  Quantity INT,<br>  UnitPrice DECIMAL(10,2),<br>  OrderDate DATE,<br>  Status STRING,<br>  LastUpdated TIMESTAMP<br>)<br>TBLPROPERTIES (<br>  delta.enableChangeDataFeed = true<br>);</pre><p><strong>Option B — On an existing table</strong> (only captures changes from this point forward):</p><p>sql</p><pre>ALTER TABLE dev.bronze.sales_cdf<br>SET TBLPROPERTIES (<br>  delta.enableChangeDataFeed = true<br>);</pre><p><strong>Option C — While writing a DataFrame:</strong></p><p>python</p><pre>initial_df.write \<br>  .format(&quot;delta&quot;) \<br>  .option(&quot;delta.enableChangeDataFeed&quot;, &quot;true&quot;) \<br>  .mode(&quot;overwrite&quot;) \<br>  .saveAsTable(&quot;dev.bronze.sales_cdf&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J3RlzIXq-3LehFt_ZDu3yA.png" /></figure><h3>Reading Changes</h3><p>You can extract changes by version or by timestamp. Both accept a start and optional end — omitting the end returns everything up to the latest commit.</p><p><strong>By version — SQL:</strong></p><p>sql</p><pre>SELECT orderid, customerid, product, quantity, status,<br>       _change_type, _commit_version, _commit_timestamp<br>FROM table_changes(&#39;dev.bronze.sales_cdf&#39;, 2, 4);</pre><p><strong>By version — PySpark:</strong></p><p>python</p><pre>changes = (<br>  spark.read<br>       .format(&quot;delta&quot;)<br>       .option(&quot;readChangeFeed&quot;, &quot;true&quot;)<br>       .option(&quot;startingVersion&quot;, 11)<br>       .option(&quot;endingVersion&quot;, 12)<br>       .table(&quot;dev.bronze.sales_cdf&quot;)<br>)</pre><p><strong>By timestamp — SQL:</strong></p><p>sql</p><pre>SELECT *<br>FROM table_changes(<br>  &#39;dev.bronze.sales_cdf&#39;,<br>  &#39;2026-06-16T12:53:46.000+00:00&#39;,<br>  &#39;2026-06-17T12:53:46.000+00:00&#39;<br>);</pre><p><strong>By timestamp — PySpark:</strong></p><p>python</p><pre>changes = (<br>  spark.read<br>       .format(&quot;delta&quot;)<br>       .option(&quot;readChangeFeed&quot;, &quot;true&quot;)<br>       .option(&quot;startingTimestamp&quot;, &quot;2026-06-16T12:52:46.000+00:00&quot;)<br>       .option(&quot;endingTimestamp&quot;,   &quot;2026-06-16T12:53:46.000+00:00&quot;)<br>       .table(&quot;dev.bronze.sales_cdf&quot;)<br>)</pre><h3>The Pre-image / Post-image Gotcha</h3><p>When a record is updated, CDF stores <strong>two rows</strong> update_preimage (before) and update_postimage (after). If you don&#39;t filter these out, downstream consumers will see duplicate entries for the same key.</p><p>For example, updating an order status:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*moZUPLDTaS67VwQB4mxmXg.png" /></figure><p>sql</p><pre>UPDATE dev.bronze.sales_cdf<br>SET Status = &#39;delivered&#39;, LastUpdated = &#39;2026-06-02 10:00:00&#39;<br>WHERE OrderID = 1002;</pre><p>Reading the changes produces two rows for OrderID = 1002. Filter to only what you need:</p><p>python</p><pre>from pyspark.sql.functions import col<br><br>filtered_df = changes.filter(<br>    col(&quot;_change_type&quot;).isin([&quot;insert&quot;, &quot;update_postimage&quot;, &quot;delete&quot;])<br>)</pre><p><strong>Rule of thumb:</strong> keep insert, update_postimage, and delete for current-state sync. Only keep update_preimage if you need to audit what a value used to be.</p><h3>Approach 1: Batch Processing</h3><p>In batch mode, you are responsible for tracking state. Unlike Structured Streaming, which manages progress through checkpoints, batch jobs have no built-in memory of what’s already been processed.</p><p>The standard pattern: store the last successfully processed Delta version (or timestamp) in a metadata table. Each run reads that value, uses it as startingVersion, and pulls only what changed since.</p><blockquote><strong><em>Critical:</em></strong><em> update the metadata table </em>after<em> the job completes — never before or mid-run. If you update it prematurely and the job fails, the next run will silently skip unprocessed changes.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1xIVI9PPBHpW7btWiQW5IA.png" /></figure><h4>Implementation</h4><p><strong>Step 1 — Create Bronze, Silver, and config tables:</strong></p><p>sql</p><pre>-- Bronze<br>CREATE OR REPLACE TABLE dev.bronze.sales_cdf (<br>  OrderID INT, CustomerID STRING, Product STRING,<br>  Quantity INT, UnitPrice DECIMAL(10,2),<br>  OrderDate DATE, Status STRING, LastUpdated TIMESTAMP<br>)<br>TBLPROPERTIES (delta.enableChangeDataFeed = true);<br><br>-- Silver<br>CREATE OR REPLACE TABLE dev.silver.sales_cdf (<br>  OrderID INT, CustomerID STRING, Product STRING,<br>  Quantity INT, UnitPrice DECIMAL(10,2),<br>  OrderDate DATE, Status STRING, LastUpdated TIMESTAMP<br>)<br>TBLPROPERTIES (delta.enableChangeDataFeed = true);<br>-- Config / metadata table<br>CREATE OR REPLACE TABLE dev.metadata.table_config (<br>  table_name STRING,<br>  last_processed_version INT,<br>  last_processed_timestamp TIMESTAMP<br>);<br>INSERT INTO dev.metadata.table_config VALUES (&#39;sales_cdf&#39;, 0, NULL);</pre><p><strong>Step 2 — Run the pipeline:</strong></p><p>The pipeline follows nine steps: read the last processed version, check the latest Bronze version, compare, read only new changes, filter change types, capture the new high-water mark, MERGE into Silver, update the config table.</p><p>python</p><pre>from pyspark.sql.functions import *<br>from delta.tables import DeltaTable<br><br># Step 1 - Read last processed version<br>max_version = (<br>    spark.read.format(&quot;delta&quot;).table(&quot;dev.metadata.table_config&quot;)<br>        .filter(col(&quot;table_name&quot;) == &quot;sales_cdf&quot;)<br>        .select(&quot;last_processed_version&quot;)<br>        .collect()[0][0]<br>)<br>if max_version is None:<br>    max_version = 0<br># Step 2 - Get the latest Bronze version<br>latest_version = (<br>    spark.sql(&quot;DESCRIBE HISTORY dev.bronze.sales_cdf&quot;)<br>        .selectExpr(&quot;max(version) as version&quot;)<br>        .collect()[0][&quot;version&quot;]<br>)<br># Step 3 - Check if there&#39;s anything new<br>if max_version &gt;= latest_version:<br>    print(&quot;No new changes to process.&quot;)<br>else:<br>    # Step 4-5 - Read only new changes<br>    changes = (<br>        spark.read<br>            .format(&quot;delta&quot;)<br>            .option(&quot;readChangeFeed&quot;, &quot;true&quot;)<br>            .option(&quot;startingVersion&quot;, max_version + 1)<br>            .table(&quot;dev.bronze.sales_cdf&quot;)<br>    )<br>    # Step 6 - Filter out preimage rows<br>    filtered_df = changes.filter(<br>        col(&quot;_change_type&quot;).isin([&quot;insert&quot;, &quot;update_postimage&quot;, &quot;delete&quot;])<br>    )<br>    # Step 7 - Capture new high-water mark<br>    new_max_version  = filtered_df.select(max(&quot;_commit_version&quot;)).collect()[0][0]<br>    new_max_timestamp = filtered_df.select(max(&quot;_commit_timestamp&quot;)).collect()[0][0]<br>    # Step 8 - MERGE into Silver<br>    silver_table = DeltaTable.forName(spark, &quot;dev.silver.sales_cdf&quot;)<br>    (silver_table.alias(&quot;target&quot;)<br>        .merge(filtered_df.alias(&quot;source&quot;), &quot;target.OrderID = source.OrderID&quot;)<br>        .whenMatchedDelete(condition=&quot;source._change_type = &#39;delete&#39;&quot;)<br>        .whenMatchedUpdate(<br>            condition=&quot;source._change_type = &#39;update_postimage&#39;&quot;,<br>            set={<br>                &quot;target.CustomerID&quot;  : &quot;source.CustomerID&quot;,<br>                &quot;target.Product&quot;     : &quot;source.Product&quot;,<br>                &quot;target.Quantity&quot;    : &quot;source.Quantity&quot;,<br>                &quot;target.UnitPrice&quot;   : &quot;source.UnitPrice&quot;,<br>                &quot;target.Status&quot;      : &quot;source.Status&quot;,<br>                &quot;target.LastUpdated&quot; : &quot;source.LastUpdated&quot;<br>            }<br>        )<br>        .whenNotMatchedInsert(<br>            condition=&quot;source._change_type IN (&#39;insert&#39;, &#39;update_postimage&#39;)&quot;,<br>            values={<br>                &quot;target.OrderID&quot;     : &quot;source.OrderID&quot;,<br>                &quot;target.CustomerID&quot;  : &quot;source.CustomerID&quot;,<br>                &quot;target.Product&quot;     : &quot;source.Product&quot;,<br>                &quot;target.Quantity&quot;    : &quot;source.Quantity&quot;,<br>                &quot;target.UnitPrice&quot;   : &quot;source.UnitPrice&quot;,<br>                &quot;target.OrderDate&quot;   : &quot;source.OrderDate&quot;,<br>                &quot;target.Status&quot;      : &quot;source.Status&quot;,<br>                &quot;target.LastUpdated&quot; : &quot;source.LastUpdated&quot;<br>            }<br>        )<br>        .execute()<br>    )<br>    # Step 9 - Update config table (only after successful MERGE)<br>    if new_max_version is not None:<br>        config_updates = spark.createDataFrame([<br>            Row(<br>                table_name=&quot;sales_cdf&quot;,<br>                last_processed_version=int(new_max_version),<br>                last_processed_timestamp=new_max_timestamp<br>            )<br>        ])<br>        config_table = DeltaTable.forName(spark, &quot;dev.metadata.table_config&quot;)<br>        (config_table.alias(&quot;target&quot;)<br>            .merge(config_updates.alias(&quot;source&quot;), &quot;target.table_name = source.table_name&quot;)<br>            .whenMatchedUpdate(set={<br>                &quot;last_processed_version&quot;  : &quot;source.last_processed_version&quot;,<br>                &quot;last_processed_timestamp&quot;: &quot;source.last_processed_timestamp&quot;<br>            })<br>            .execute()<br>        )</pre><h3>Approach 2: Structured Streaming</h3><p>Structured Streaming is one of the most powerful ways to consume CDF. When CDF is read as a streaming source, Databricks automatically tracks progress through checkpointing — eliminating manual version management entirely.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*__kYdNZLALzVaM5rCvawbw.png" /></figure><h4>How Checkpointing Works</h4><p>The checkpoint stores which Delta versions have been successfully committed to the sink. If a stream processes changes through version 150 and fails mid-way through 151, the next run resumes from 151 — not from the beginning.</p><p>This gives you:</p><ul><li>No manual version or timestamp tracking</li><li>Exactly-once processing semantics (with idempotent sinks)</li><li>Automatic failure recovery</li></ul><h4>What Is the Checkpoint, Really?</h4><p>The checkpoint belongs to the <em>pipe</em> (readStream → writeStream), not to either table. It tracks one thing: which Bronze CDF versions this specific stream has already consumed.</p><p>This matters when you have multiple consumers. If two streams both read from the same Bronze table one writing to Silver, another to a Gold aggregate — each needs its own distinct checkpoint location, since each tracks its own progress independently. Bronze itself has no concept of “what’s been read” — it simply holds commit versions in its transaction log.</p><h4>Standard readStream vs. readStream with CDF</h4><p>A common point of confusion: standard readStream on a Delta table is append-only. It detects new rows but is blind to updates and deletes on existing rows. If your pipeline needs the full spectrum of changes, you must enable CDF.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2RQH-vEbkYous6SQu_7XrQ.png" /></figure><h4>Implementation</h4><p><strong>Step 1 — Read CDF as a stream:</strong></p><p>python</p><pre>from pyspark.sql.functions import col<br><br>cdf_stream = (<br>    spark.readStream<br>        .format(&quot;delta&quot;)<br>        .option(&quot;readChangeFeed&quot;, &quot;true&quot;)<br>        .table(&quot;dev.bronze.sales_cdf&quot;)<br>)</pre><p><strong>Step 2 — Define </strong><strong>foreachBatch logic:</strong></p><p>Standard writeStream sinks only support appending rows — there&#39;s no native way to MERGE. foreachBatch solves this by converting each micro-batch into a static DataFrame, giving you full access to batch-only operations like DeltaTable.merge().</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZdFRxJhe9C3KnsknSMw1zw.png" /></figure><p>python</p><pre>from delta.tables import DeltaTable<br><br>def merge_to_silver(batch_df, batch_id):<br>    filtered_df = batch_df.filter(<br>        col(&quot;_change_type&quot;).isin([&quot;insert&quot;, &quot;update_postimage&quot;, &quot;delete&quot;])<br>    )<br>    if filtered_df.isEmpty():<br>        print(f&quot;Batch {batch_id}: no changes to process.&quot;)<br>        return<br>    silver_table = DeltaTable.forName(spark, &quot;dev.silver.sales_cdf&quot;)<br>    (silver_table.alias(&quot;target&quot;)<br>        .merge(filtered_df.alias(&quot;source&quot;), &quot;target.OrderID = source.OrderID&quot;)<br>        .whenMatchedDelete(condition=&quot;source._change_type = &#39;delete&#39;&quot;)<br>        .whenMatchedUpdate(<br>            condition=&quot;source._change_type = &#39;update_postimage&#39;&quot;,<br>            set={<br>                &quot;target.CustomerID&quot;  : &quot;source.CustomerID&quot;,<br>                &quot;target.Product&quot;     : &quot;source.Product&quot;,<br>                &quot;target.Quantity&quot;    : &quot;source.Quantity&quot;,<br>                &quot;target.UnitPrice&quot;   : &quot;source.UnitPrice&quot;,<br>                &quot;target.Status&quot;      : &quot;source.Status&quot;,<br>                &quot;target.LastUpdated&quot; : &quot;source.LastUpdated&quot;<br>            }<br>        )<br>        .whenNotMatchedInsert(<br>            condition=&quot;source._change_type = &#39;insert&#39;&quot;,<br>            values={<br>                &quot;target.OrderID&quot;     : &quot;source.OrderID&quot;,<br>                &quot;target.CustomerID&quot;  : &quot;source.CustomerID&quot;,<br>                &quot;target.Product&quot;     : &quot;source.Product&quot;,<br>                &quot;target.Quantity&quot;    : &quot;source.Quantity&quot;,<br>                &quot;target.UnitPrice&quot;   : &quot;source.UnitPrice&quot;,<br>                &quot;target.OrderDate&quot;   : &quot;source.OrderDate&quot;,<br>                &quot;target.Status&quot;      : &quot;source.Status&quot;,<br>                &quot;target.LastUpdated&quot; : &quot;source.LastUpdated&quot;<br>            }<br>        )<br>        .execute()<br>    )<br>    print(f&quot;Batch {batch_id}: merge completed.&quot;)</pre><p><strong>Step 3 — Wire </strong><strong>foreachBatch into the stream:</strong></p><p>python</p><pre>query = (<br>    cdf_stream.writeStream<br>        .option(&quot;checkpointLocation&quot;, &quot;/checkpoints/dev/silver/sales_cdf&quot;)<br>        .foreachBatch(merge_to_silver)<br>        .trigger(availableNow=True)<br>        .start()<br>)</pre><pre>query.awaitTermination()</pre><ul><li>checkpointLocation — stores the Bronze CDF version already consumed; the next run resumes automatically.</li><li>trigger(availableNow=True) — processes all currently available changes, then stops. Batch-like scheduling without manual version tracking.</li><li>awaitTermination() — blocks execution until the stream finishes catching up.</li></ul><p>One practical difference from batch: if there are no new changes, the stream does not fail empty micro-batches are skipped silently. Batch CDF, by contrast, will throw an error if you request a startingVersion that doesn&#39;t exist, unless you guard for it explicitly (as Step 3 of the batch pipeline does).</p><h4>Batch vs. Streaming — Side by Side</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nRetOiw5D1RGO81P8qv7lQ.png" /></figure><h3>Approach 3: Lakeflow Declarative Pipelines (AUTO CDC)</h3><p>Batch and streaming give you full control, but they also require significant overhead: managing checkpoints or metadata tables, filtering update_preimage rows, writing custom MERGE logic.</p><p><strong>Lakeflow Declarative Pipelines</strong> (formerly Delta Live Tables / DLT) simplify this through <strong>AUTO CDC</strong> (previously called APPLY CHANGES INTO). The pipeline automatically consumes CDF records, handles deduplication, tracks state internally, and keeps target tables synchronized — with minimal code.</p><blockquote><strong><em>Note:</em></strong><em> AUTO CDC is the new Lakeflow name for </em><em>APPLY CHANGES INTO. The SQL syntax continues to work, but Databricks documentation now refers to it as AUTO CDC.</em></blockquote><h4>What Is AUTO CDC?</h4><p>Instead of manually reading CDF records, filtering update_preimage, writing MERGE statements, and managing checkpoints, you declare:</p><ul><li><strong>KEYS</strong> — the business key used to identify records</li><li><strong>SEQUENCE BY</strong> — the column used to determine event ordering</li><li><strong>APPLY AS DELETE WHEN</strong> — the condition that marks a delete event</li></ul><p>The pipeline handles the rest.</p><blockquote><em>CDF must still be enabled on the source table — AUTO CDC consumes CDF records, it doesn’t bypass the requirement.</em></blockquote><h4>Manual vs. AUTO CDC</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cDHcfv1B6lSQSEBX0VNRKg.png" /></figure><h4>Key Configuration</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3S3TenpdDgyUz8WoLyl12w.png" /></figure><h4>SEQUENCE BY — The Most Misunderstood Setting</h4><p>Consider this update sequence for one order:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UVx_Kqn8yQyywYOnWmWP3g.png" /></figure><p>With sequence_by=col(&quot;LastUpdated&quot;), the pipeline knows <em>Delivered</em> is the latest state. This matters because a single update generates both update_preimage and update_postimage CDF rows — multiple updates can create several rows for the same key. AUTO CDC uses the sequence column to resolve the final state automatically.</p><h4>Implementation</h4><p><strong>Step 1 — Create the source table and load data:</strong></p><p>sql</p><pre>CREATE OR REPLACE TABLE dev.bronze.sales_stream (<br>    OrderID INT, CustomerID STRING, Product STRING,<br>    Quantity INT, UnitPrice DECIMAL(10,2),<br>    OrderDate DATE, Status STRING, LastUpdated TIMESTAMP<br>);<br><br>INSERT INTO dev.bronze.sales_stream VALUES<br>(1001, &#39;C001&#39;, &#39;Laptop&#39;,     2, 1200.00, &#39;2026-06-01&#39;, &#39;Pending&#39;, &#39;2026-06-01 08:00:00&#39;),<br>(1002, &#39;C002&#39;, &#39;Monitor&#39;,    1, 350.00,  &#39;2026-06-01&#39;, &#39;Pending&#39;, &#39;2026-06-01 08:05:00&#39;),<br>(1003, &#39;C003&#39;, &#39;Keyboard&#39;,   3, 75.00,   &#39;2026-06-01&#39;, &#39;Pending&#39;, &#39;2026-06-01 08:10:00&#39;),<br>(1004, &#39;C004&#39;, &#39;Mouse&#39;,      2, 45.00,   &#39;2026-06-01&#39;, &#39;Pending&#39;, &#39;2026-06-01 08:15:00&#39;),<br>(1005, &#39;C005&#39;, &#39;Headphones&#39;, 1, 200.00,  &#39;2026-06-01&#39;, &#39;Pending&#39;, &#39;2026-06-01 08:20:00&#39;);</pre><p><strong>Step 2 — Create the Bronze streaming table:</strong></p><p>python</p><pre>from pyspark import pipelines as dp<br>from pyspark.sql.functions import col<br><br>@dp.table(name=&quot;bronze_orders_demo&quot;)<br>def bronze_orders_demo():<br>    return (<br>        spark.readStream<br>             .option(&quot;readChangeFeed&quot;, &quot;true&quot;)<br>             .table(&quot;dev.bronze.sales_stream&quot;)<br>    )<br>dp.create_streaming_table(&quot;silver_orders_demo&quot;)</pre><p><strong>Step 3 — Apply AUTO CDC:</strong></p><p>python</p><pre><br>dp.create_auto_cdc_flow(<br>    target=&quot;silver_orders_demo&quot;,<br>    source=&quot;bronze_orders_demo&quot;,<br>    keys=[&quot;OrderID&quot;],<br>    sequence_by=col(&quot;_commit_version&quot;),<br>    apply_as_deletes=col(&quot;_change_type&quot;) == &quot;delete&quot;,<br>    stored_as_scd_type=1,<br>    except_column_list=[<br>        &quot;_change_type&quot;,<br>        &quot;_commit_version&quot;,<br>        &quot;_commit_timestamp&quot;<br>    ]<br>)</pre><h4>SCD Type 1 vs Type 2</h4><p>stored_as_scd_type controls how history is handled:</p><ul><li><strong>SCD Type 1</strong> — keeps only the latest record; updates overwrite previous values; no history preserved.</li><li><strong>SCD Type 2</strong> — preserves historical versions; automatically manages effective dating and record versioning; enables full change history tracking.</li></ul><p>Switching between them often requires changing only this one parameter.</p><h3>Summary</h3><p>CDF gives you three progressively simpler ways to build incremental pipelines:</p><p>ApproachState ManagementBest ForBatchManual config/metadata tableScheduled jobs needing precise version controlStructured StreamingCheckpoint (automatic)Continuous or near-real-time ingestionAUTO CDC (Lakeflow)Managed internallyMinimal code, declarative pipelines</p><p>Batch gives you explicit control. Streaming removes most of the boilerplate. AUTO CDC removes nearly all of it. The right choice depends on how much operational control you need versus how much complexity you want to manage.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5848d1f879e3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Databricks Auto Loader: Best Practices for Reliable and Scalable Data Ingestion]]></title>
            <link>https://medium.com/@asindugayangana/databricks-auto-loader-best-practices-for-reliable-and-scalable-data-ingestion-30e65bf4c520?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/30e65bf4c520</guid>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Thu, 25 Dec 2025 12:13:26 GMT</pubDate>
            <atom:updated>2025-12-25T12:13:26.851Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*y3fHD8-4J_KPF9SYoqTecw.jpeg" /></figure><p>This article provides a comprehensive overview of <strong>Databricks Auto Loader</strong> and its role in building scalable, reliable, and production-ready data pipelines. It covers the core capabilities of Auto Loader, including <strong>different trigger types</strong>, <strong>schema evolution strategies</strong>, and <strong>best practices for incremental/streaming data ingestion</strong>, duplicate handling, and JSON parsing using real-world use cases.</p><p>The article also explains <strong>watermarking for streaming pipelines</strong>, <strong>backfill concepts</strong>, and how <strong>parallelism works during backfills</strong>. It demonstrates how to <strong>parameterize notebooks and jobs</strong> to support both automated incremental runs and historical backfills without code changes. In addition, it covers important operational aspects such as <strong>retry policies</strong>, <strong>runtime thresholds</strong>, <strong>notifications</strong>, and <strong>orchestrating downstream actions</strong> like refreshing <strong>dbt models</strong> and <strong>dashboards </strong>as part of the same pipeline.</p><p>Beyond Auto Loader, <strong>Databricks is a powerful unified data platform</strong>. Databricks integrates seamlessly with all three major cloud providers — <strong>AWS, Azure, and GCP</strong> — and is built for large-scale data processing using <strong>Delta Lake and the Medallion (Bronze–Silver–Gold) architecture</strong>. The platform enables reliable batch and streaming workloads, strong governance through Unity Catalog, built-in data lineage, and smooth integration with <strong>machine learning and AI workflows</strong>.</p><p>With features such as <strong>serverless compute</strong>, <strong>job orchestration</strong>, <strong>native dashboarding</strong>, and <strong>AI-assisted development</strong>, Databricks significantly simplifies data engineering workflows while maintaining enterprise-grade scalability, reliability, and performance. This article aims to provide both conceptual understanding and practical guidance for implementing robust end-to-end pipelines using Databricks Auto Loader and the Lakehouse architecture.</p><h3>Databricks Job Trigger Types</h3><h3>Schedule Trigger</h3><ul><li>Runs on a <strong>specific time or date</strong></li><li>Common for <strong>daily/hourly batch jobs</strong></li></ul><h3>File Arrival Trigger (Auto Loader)</h3><ul><li>Automatically runs when <strong>new files arrive</strong></li><li><strong>Same file is never processed again</strong></li></ul><h3>Table Update Trigger</h3><ul><li>Watches a <strong>Delta table</strong></li><li>Job runs whenever the table is <strong>updated</strong></li><li>Useful for <strong>downstream dependencies</strong></li></ul><h3>Continuous Trigger (Streaming)</h3><ul><li>Used for <strong>real-time streaming</strong></li><li>Job runs continuously</li><li>Suitable for Kafka, Event Hubs, etc.</li></ul><h3>Auto Loader — Schema Evolution Behavior</h3><p>Databricks Auto Loader provides <strong>idempotent file ingestion</strong>, meaning the <strong>same file is never processed more than once</strong>.<br>Auto Loader tracks processed files using <strong>checkpoint metadata</strong> and stores inferred or evolving schemas in a <strong>schema location</strong>.</p><p>Because of this:</p><ul><li>Already processed files are not re-read</li><li>Schema changes are tracked separately from processing state</li></ul><p><strong>Both schemaLocation and checkpointLocation are mandatory</strong> when using Auto Loader in streaming mode.</p><h3>Supported Schema Evolution Scenarios</h3><ul><li>Adding new columns</li><li>Column renames (treated as new columns)</li><li>Dropped columns (values appear as NULL for new records)</li></ul><h3>Not Supported</h3><ul><li>Column data type changes<br>(These cause failures unless handled explicitly using rescue mode or raw ingestion)</li></ul><h3>Schema Evolution Scenarios</h3><h3>Adding New Columns (Best for Structured Files)</h3><p>This approach works <strong>best for structured formats such as CSV, Parquet, and Avro</strong>, where Spark can reliably parse records even when new columns appear.</p><p>Schema evolution must be enabled:</p><ul><li>At <strong>read time</strong> (Auto Loader)</li><li>At <strong>write time</strong> (Delta Lake)</li></ul><pre>df = (<br>spark.readStream<br>.format(&quot;cloudFiles&quot;)<br>.option(&quot;cloudFiles.format&quot;, &quot;csv&quot;)<br>.option(&quot;cloudFiles.schemaEvolutionMode&quot;, &quot;addNewColumns&quot;)<br>.option(&quot;cloudFiles.schemaLocation&quot;, schema)<br>.load(&quot;/Volumes/dev/landing/source_data/&quot;)<br>)<br>df.writeStream \<br>.format(&quot;delta&quot;) \<br>.option(&quot;checkpointLocation&quot;, checkpoint) \<br>.option(&quot;mergeSchema&quot;, &quot;true&quot;) \<br>.trigger(once=True) \<br>.table(&quot;dev.bronze.api_data&quot;)</pre><p>addNewColumns does <strong>not</strong> protect against schema drift at JSON parsing time. It primarily applies during Delta writes and structured formats.</p><h3>JSON Ingestion and Schema Drift</h3><p>When reading JSON files using Auto Loader, ingestion <strong>can fail</strong> if the source schema evolves unexpectedly, such as:</p><ul><li>New or missing fields</li><li>Nested structure changes</li><li>Array or struct shape changes</li><li>Data type changes</li></ul><p>This happens because Spark must successfully <strong>parse JSON before schema evolution can be applied</strong>.</p><h3>Rescue Mode for JSON (Recommended When Parsing in Bronze)</h3><p>To prevent pipeline failures, Auto Loader provides <strong>rescue mode</strong>, which captures all fields that do not match the inferred schema into a single column (for example, _unprocessed_column).</p><p>This allows ingestion to continue without errors.</p><pre>df = (<br> spark.readStream<br>   .format(&quot;cloudFiles&quot;)<br>   .option(&quot;cloudFiles.format&quot;, &quot;json&quot;)<br>   .option(&quot;cloudFiles.schemaEvolutionMode&quot;, &quot;rescue&quot;)<br>   .option(&quot;cloudFiles.rescuedDataColumn&quot;, &quot;_unprocessed_column&quot;)<br>   .option(&quot;cloudFiles.schemaLocation&quot;, schema)<br>   .load(&quot;/Volumes/dev/landing/test/&quot;)<br>)<br><br><br>df.writeStream \<br> .format(&quot;delta&quot;) \<br> .option(&quot;checkpointLocation&quot;, checkpoint) \<br> .trigger(once=True) \<br> .table(&quot;dev.bronze.coins_price_best_practice&quot;)</pre><h3>Bronze Layer Best Practice for JSON (Strong Recommendation)</h3><p>According to Lakehouse best practices, the <strong>Bronze layer should preserve source data exactly as received</strong>, without enforcing schemas or applying transformations.</p><p>For JSON data, the <strong>safest and most reliable approach</strong> is:</p><ul><li>Ingest JSON as <strong>raw text</strong> in Bronze</li><li>Parse, explode, and enforce schema in <strong>Silver</strong></li></ul><p>This guarantees:</p><ul><li>Zero ingestion failures due to schema drift</li><li>Full traceability and reprocessing capability</li><li>Clean separation of ingestion and transformation concerns</li></ul><h3>Schema Change Behavior Summary</h3><h3>Dropping Columns</h3><ul><li>Dropped columns appear as NULL in new records</li><li>No ingestion failure occurs</li></ul><h3>Renaming Columns</h3><ul><li>Treated as a new column</li><li>Old column retains historical values</li><li>New column contains values only after the rename</li></ul><h3>Fail on New Columns</h3><p>To explicitly <strong>fail ingestion when schema changes</strong> are detected:</p><pre>.option(&quot;cloudFiles.schemaEvolutionMode&quot;, &quot;failOnNewColumn&quot;)</pre><p>This is useful in tightly controlled pipelines where schema drift must be blocked.</p><h3>Schema Hints</h3><p>Schema hints allow Auto Loader to <strong>avoid schema inference</strong> and enforce column data types upfront.</p><pre>.option(<br>&quot;cloudFiles.schemaHints&quot;,<br>&quot;order_id INT, order_date DATE, customer_id INT, product_id INT, quantity INT, unit_price DOUBLE, store_id INT, total DOUBLE&quot;<br>)</pre><h3>File Discovery Modes</h3><h3>Default Directory Listing</h3><p>Best when:</p><ul><li>Many directories exist</li><li>Deeply partitioned folder structures (for example: year=2024/month=01)</li></ul><h3>useNotifications (Recommended for Large File Volumes)</h3><p>Faster<br>More cost-efficient<br>Lower file-listing overhead</p><p>Best when:</p><ul><li>Few directories</li><li>Many incoming files</li></ul><p>Uses cloud-native event systems such as:</p><ul><li>AWS SNS / SQS</li><li>Azure Event Grid</li><li>GCP Pub/Sub</li></ul><h3>Auto Loader Performance Options</h3><p>Control ingestion rate to protect downstream systems.</p><p>Maximum files per trigger:</p><pre>.option(&quot;maxFilesPerTrigger&quot;, 100)</pre><p>Maximum bytes per trigger:</p><pre>.option(&quot;maxBytesPerTrigger&quot;, &quot;1g&quot;)</pre><h3>JSON Formats</h3><h3>NDJSON (Newline-Delimited JSON)</h3><p>Each JSON object is written on a single line (one record per row).<br>This format can be ingested efficiently <strong>without multiline mode</strong>.</p><pre>spark.read.format(&quot;json&quot;).load(path)</pre><h3>Multiline / Complex JSON</h3><p>When a single JSON object or an array spans multiple lines, the reader must be configured explicitly:</p><pre>.option(&quot;multiline&quot;, &quot;true&quot;)</pre><h3>Compute Selection for Job Runs</h3><h3>Serverless vs. Classic</h3><ul><li><strong>Serverless Jobs are recommended</strong> for most ETL workloads</li><li>Instant startup</li><li>No cluster configuration</li><li>Lower operational overhead</li></ul><h3>Job Clusters vs. All-Purpose Clusters</h3><ul><li>Production jobs should <strong>never run on All-Purpose clusters</strong></li><li>All-Purpose clusters are approximately <strong>2× more expensive</strong></li><li>Always use <strong>Job clusters</strong> for scheduled and production workloads</li></ul><h3>Notebook Parameterization</h3><h4>Why Dynamic Parameterization?</h4><p>Dynamic parameterization enables the reusability of the same notebook without hardcoding values. It allows notebooks to be executed for specific time windows, making them flexible for different operational scenarios. By parameterizing inputs, the same notebook can support daily scheduled runs as well as historical backfill runs, reducing code duplication and improving maintainability.</p><h4>When to Use?</h4><p>In my use case, a scheduled Python script runs at predefined intervals and uploads JSON files into a landing bucket. Once a new file arrives, Databricks Auto Loader detects it and triggers the downstream pipeline.</p><p>The pipeline consists of three tasks:</p><ol><li><strong>Bronze ingestion</strong> — The newly arrived file is read as a text file from the landing bucket and loaded into a Bronze table.</li><li><strong>Silver transformation</strong> — A parameterized notebook is triggered to process data from the Bronze layer to the Silver layer.</li><li><strong>Incremental processing logic</strong> — The Silver notebook does not load the entire Bronze dataset. Instead, it applies dynamic filtering based on parameters.</li></ol><p>For regular scheduled runs, no parameters are explicitly passed. In this case, the notebook automatically processes only the last four hours of data and performs a merge operation to avoid duplicates.</p><p>For backfill scenarios, date/time parameters are passed through Databricks job parameters. This allows the same notebook to process historical data for a specific time range without any code changes.</p><p>This approach ensures flexibility, supports both incremental and backfill processing, and promotes clean, production-ready notebook design.</p><pre>#set widgets to pass values<br>dbutils.widgets.text(&quot;start_date&quot;, &quot;&quot;)<br>dbutils.widgets.text(&quot;end_date&quot;, &quot;&quot;)<br><br>#extract values from widgets<br>raw_start = dbutils.widgets.get(&quot;start_date&quot;).strip()<br>raw_end = dbutils.widgets.get(&quot;end_date&quot;).strip()<br><br><br># Backfill Mode: Triggered when parameters are provided in the Job UI<br>if len(raw_start) &gt; 0 and len(raw_end) &gt; 0:<br>start_date = datetime.strptime(raw_start, &quot;%Y-%m-%d&quot;) - timedelta(hours=4)<br>end_date = datetime.strptime(raw_end, &quot;%Y-%m-%d&quot;) + timedelta(hours=1)<br>print(f&quot;Running in BACKFILL mode for range: {start_date} to {end_date}&quot;)<br><br># Automated Mode: Default behavior for File Arrival triggers (last 4 hours)<br>else:<br>start_date = datetime.now() - timedelta(hours=4)<br>end_date = datetime.now() + timedelta(hours=1)<br>print(f&quot;Running in AUTOMATED mode for range: {start_date} to {end_date}&quot;)<br><br># 3. Extract incremental data from Bronze<br>silver_df = (<br>spark.read.format(&quot;delta&quot;)<br>.table(&quot;dev.bronze.coins_price_best_practice&quot;)<br>.filter(f&quot;LoadTime BETWEEN &#39;{start_date}&#39; AND &#39;{end_date}&#39;&quot;)<br>)</pre><p>When the notebook is configured as a task within a Databricks job, two parameters are defined at the job level. These parameters are populated during backfill runs to pass the required date or time range, while regular runs rely on current timestamps.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YYnIseXNUGVFknupgM5HMQ.png" /></figure><p>As shown in the screenshot above, two parameters are defined at the <strong>Databricks job level</strong>. These parameters are exposed when the job is triggered using the <strong>Backfill</strong> option, as shown in the screenshot below.</p><p>During backfill execution, Databricks creates <strong>separate job runs for each backfill interval</strong>. In this example, two concurrent runs are generated for <strong>18th December</strong> and <strong>19th December</strong>. For each run, Databricks automatically injects the corresponding parameter values based on the backfill configuration.</p><p>This approach ensures that each backfill run processes its assigned date range independently, while reusing the same parameterized notebook logic without any code changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1arZiVXbGjgtgEbAsHIH1g.png" /></figure><h3>Best Practice: Extract JSON into lakehouse</h3><h4>Recommended Approach</h4><ul><li>Read JSON files <strong>as text</strong></li><li>Store the <strong>entire file content as a single string column</strong></li><li>Add ingestion metadata ( filename, file_path, ingestion_time, source_system)</li></ul><h4>Why Read JSON as Text in Bronze?</h4><p>Reading JSON as text <strong>never fails</strong>, regardless of:</p><ul><li>Schema evolution</li><li>Nested structure changes</li><li>New or missing fields</li><li>Data type changes</li><li>Partially malformed JSON</li></ul><p>JSON parsing happens <strong>before schema evolution</strong>.<br>If parsing fails, schema evolution <strong>cannot help</strong>.</p><p>By storing raw JSON as text:</p><ul><li>You preserve the <strong>exact source payload</strong></li><li>No data loss occurs</li><li>Bronze remains <strong>schema-agnostic</strong></li><li>The pipeline is resilient to source changes</li></ul><p>If parsing fails later in Silver:</p><ul><li>You can reprocess from Bronze</li><li>No need to re-extract from source systems</li><li>Full auditability is preserved</li></ul><h3>Bronze Layer Principles</h3><ul><li>Append-only</li><li>No transformations</li><li>No schema enforcement</li><li>No deduplication</li><li>No business logic</li></ul><p>Bronze represents the <strong>system of record</strong>.</p><h3>Silver Layer Responsibilities</h3><p>All structure and quality enforcement happens here:</p><ul><li>Parse JSON using a <strong>defined schema</strong></li><li>Flatten nested structures</li><li>Explode arrays</li><li>Deduplicate using window functions</li><li>Apply data quality rules</li><li>Drop or isolate invalid records</li><li>Enforce schema evolution deliberately</li></ul><h3>Error Handling &amp; Dead Letter Queue (DLQ)</h3><h4>_rescued_data (Auto Loader Only)</h4><ul><li>Captures fields that <strong>do not match the schema</strong></li><li>Works only with <strong>Auto Loader</strong> &amp; <strong>COPY INTO</strong></li><li>Does not work with from_json / parse_json</li><li>Does not protect against malformed JSON</li></ul><p>Use this when:</p><ul><li>You must parse JSON in Bronze</li><li>You want limited schema drift tolerance</li></ul><h4>Validation in Silver</h4><ul><li>Apply explicit validation rules: isNotNull, Range checks, Referential checks</li><li>Invalid records can be: Dropped, Redirected to a DLQ table</li></ul><p>This keeps Silver clean and trusted.</p><h3>Streaming Considerations (Silver Layer)</h3><h4>Watermarking</h4><p>Watermarking is required <strong>only when Silver is running as a streaming job</strong>.</p><p>Without a watermark:</p><ul><li>Spark keeps the state forever</li><li>Deduplication windows never close</li><li>A hanging job can grow endlessly</li><li>Jobs can run for days and consume memory</li></ul><p>With a watermark:</p><ul><li>Spark knows <strong>how long to wait for late data</strong></li><li>Old state is safely cleaned up</li><li>Streaming jobs remain stable and bounded</li></ul><h3>Watermarking (Silver Layer — Streaming Jobs)</h3><p>When the Silver layer is implemented as a streaming job, watermarking must be applied on an <strong>event-time column</strong> (for example, last_updated).</p><p>A watermark defines how long Spark should wait for late-arriving data before discarding old state. This is especially important for:</p><ul><li>Stateful deduplication</li><li>Aggregations</li><li>Preventing unbounded state growth</li></ul><p>If watermarking is not set, a streaming job can retain state indefinitely and may run for days if the pipeline hangs or receives delayed data.</p><pre>from pyspark.sql.functions import col<br>df_structured = df_structured \<br>.withWatermark(&quot;last_updated&quot;, &quot;5 minutes&quot;)</pre><h3>Backfilling Strategy</h3><ul><li>Parameterize notebooks (date range, interval)</li><li>Run parallel backfill jobs by partition</li><li>Backfill reads from Bronze, not source</li><li>Can run alongside regular ingestion safely</li></ul><h3>Trigger Types (Corrected)</h3><h4>trigger(availableNow=True)</h4><ul><li>Processes all available data</li><li>Uses multiple micro-batches</li><li>Recommended for large backfills</li></ul><h4>trigger(once=True)</h4><ul><li>Processes all data in a single batch</li><li>Simpler, but less scalable</li></ul><h3>How to load Json from landing bucket into Bronze Ingestion (JSON as text)</h3><pre>from pyspark.sql.functions import col, current_timestamp<br>checkpoint = &quot;/Volumes/dev/bronze/checkpoint/coins_price_best_practice/&quot;<br>schema = &quot;/Volumes/dev/bronze/schema/coins_price_best_practice/&quot;<br>df = (<br>spark.readStream<br>.format(&quot;cloudFiles&quot;)<br>.option(&quot;cloudFiles.format&quot;, &quot;text&quot;)<br>.option(&quot;cloudFiles.schemaLocation&quot;, schema)<br>.load(&quot;/Volumes/dev/landing/source_data/&quot;)<br>)<br>df = df.select(<br>col(&quot;value&quot;).alias(&quot;jsonArray&quot;),<br>col(&quot;_metadata.file_name&quot;).alias(&quot;file_name&quot;),<br>col(&quot;_metadata.file_path&quot;).alias(&quot;file_path&quot;),<br>current_timestamp().alias(&quot;LoadTime&quot;)<br>)<br>df.writeStream \<br>.format(&quot;delta&quot;) \<br>.option(&quot;checkpointLocation&quot;, checkpoint) \<br>.trigger(once=True) \<br>.table(&quot;dev.bronze.coins_price_best_practice&quot;)</pre><h3>Writing into the Silver Layer</h3><p>In the Silver layer, the goal is to <strong>transform raw JSON data into a structured, reliable, and analytics-ready format</strong>. Unlike the Bronze layer, where data is stored as-is, the Silver layer is where <strong>schema enforcement and validation are intentionally applied</strong>.</p><h3>Define and Enforce the Schema</h3><p>Rather than relying on schema inference or automatic schema evolution, the recommended approach in the Silver layer is to <strong>explicitly define the expected JSON schema</strong>. This ensures:</p><ul><li>Consistent data types across all records.</li><li>Early detection of schema drift.</li><li>Predictable downstream behavior for analytics and reporting.</li></ul><pre>from pyspark.sql.functions import *<br>from pyspark.sql.types import *<br>schema = StructType([StructField(&quot;id&quot;, StringType(), True),<br>StructField(&quot;symbol&quot;, StringType(), True),<br>StructField(&quot;name&quot;, StringType(), True),<br>StructField(&quot;image&quot;, StringType(), True),<br>StructField(&quot;current_price&quot;, FloatType(), True),<br>StructField(&quot;market_cap&quot;, FloatType(), True),<br>StructField(&quot;market_cap_rank&quot;, IntegerType(), True),<br>StructField(&quot;fully_diluted_valuation&quot;, FloatType(), True),<br>StructField(&quot;total_volume&quot;, FloatType(), True),<br>StructField(&quot;high_24h&quot;, FloatType(), True),<br>StructField(&quot;low_24h&quot;, FloatType(), True),<br>StructField(&quot;price_change_24h&quot;, FloatType(), True),<br>StructField(&quot;price_change_percentage_24h&quot;, FloatType(), True),<br>StructField(&quot;market_cap_change_24h&quot;, FloatType(), True),<br>StructField(&quot;market_cap_change_percentage_24h&quot;, FloatType(), True),<br>StructField(&quot;circulating_supply&quot;, FloatType(), True),<br>StructField(&quot;total_supply&quot;, FloatType(), True),<br>StructField(&quot;max_supply&quot;, FloatType(), True),<br>StructField(&quot;ath&quot;, FloatType(), True),<br>StructField(&quot;ath_change_percentage&quot;, FloatType(), True),<br>StructField(&quot;ath_date&quot;, TimestampType(), True),<br>StructField(&quot;atl&quot;, FloatType(), True),<br>StructField(&quot;atl_change_percentage&quot;, FloatType(), True),<br>StructField(&quot;atl_date&quot;, TimestampType(), True),<br>StructField(&quot;roi&quot;, StructType([<br>              StructField(&quot;times&quot;, DoubleType()),<br>              StructField(&quot;currency&quot;, StringType()),<br>              StructField(&quot;percentage&quot;, DoubleType())<br>              ])),<br>StructField(&quot;last_updated&quot;, TimestampType(), True)<br>])</pre><p>In the code below, data is read from the Bronze table using a parameter-driven approach.<br>If no parameters are provided, the notebook performs a <strong>regular incremental load</strong>, extracting only the last four hours of data from the Bronze layer.<br>When parameters are supplied (for example, during a backfill), the same logic dynamically switches to load a <strong>specific date or time range</strong> based on the provided values.</p><p>This design makes the notebook <strong>fully reusable</strong> for both daily incremental processing and historical backfills without any code changes. Parameterization is therefore a key practice for building scalable, reliable, and maintainable incremental data pipelines.</p><pre>from datetime import datetime, timedelta<br><br>if len(start_date) &gt; 0 and len(end_date) &gt; 0:<br>start_date = datetime.strptime(start_date, &quot;%Y-%m-%d&quot;) - timedelta(hours=4)<br>end_date = datetime.strptime(end_date, &quot;%Y-%m-%d&quot;) + timedelta(hours=1)<br>else:<br>start_date = datetime.now() - timedelta(hours=4)<br>end_date = datetime.now() + timedelta(hours=1)<br>silver_df = (<br>spark.read.format(&quot;delta&quot;)<br>.table(&quot;dev.bronze.coins_price_best_practice&quot;)<br>.filter(<br>f&quot;LoadTime BETWEEN &#39;{start_date}&#39; AND &#39;{end_date}&#39;&quot;<br>)<br>)<br>display(silver_df)</pre><p><strong>Code Explanation:</strong></p><ol><li><strong>Parse JSON:<br></strong>The from_json function is used to convert the JSON string column (jsonArray) from the Bronze table into a structured array of JSON objects based on the defined schema.</li><li><strong>Explode JSON Array:<br></strong>Since each row contains an array of JSON objects, explode is used to <strong>split each array element into separate rows</strong>, so that each JSON object becomes a separate row in the DataFrame.</li><li><strong>Select Structured Columns:<br></strong>Finally, .select(“jsonArray.*”) flattens the JSON object into individual columns based on the schema.</li></ol><pre>from pyspark.sql.functions import from_json, col<br>from pyspark.sql.types import ArrayType<br>from pyspark.sql.functions import explode<br>df_parsed = silver_df.withColumn(<br>&quot;jsonArray&quot;,<br>from_json(col(&quot;jsonArray&quot;), ArrayType(schema))<br>)<br>df_structured = df_parsed.select(explode(col(&quot;jsonArray&quot;)).alias(&quot;jsonArray&quot;)).select(&quot;jsonArray.*&quot;)</pre><p><strong>Then flatten the ROI column into the main dataframe</strong></p><pre>df_structured = df_structured\<br>.withColumn(&quot;roi_times&quot;, col(&quot;roi.times&quot;)) \<br>.withColumn(&quot;roi_currency&quot;, col(&quot;roi.currency&quot;)) \<br>.withColumn(&quot;roi_percentage&quot;, col(&quot;roi.percentage&quot;)) \<br>.withColumn(&quot;key&quot;,concat(col(&quot;id&quot;),col(&quot;last_updated&quot;).cast(&quot;int&quot;)))\<br>.drop(&quot;roi&quot;)</pre><p><strong>Remove duplicates using window functions and load them into the temp table before merging into the main table.</strong></p><pre>from pyspark.sql.window import Window<br>from pyspark.sql.functions import row_number<br>windowspec = Window.partitionBy(&quot;key&quot;).orderBy(col(&quot;last_updated&quot;).desc())<br>dedup = df_structured.withColumn(&quot;rn&quot;,row_number().over(windowspec)).filter(col(&quot;rn&quot;)==1).drop(&quot;rn&quot;)<br>dedup.write.mode(&quot;overwrite&quot;).option(&quot;mergeSchema&quot;, &quot;true&quot;).format(&quot;delta&quot;).saveAsTable(&quot;dev.silver.coins_price_best_practice_temp&quot;)</pre><p><strong>Finally runs the Merge query to ensure no duplicates in the final table.</strong></p><pre>%sql<br>merge into dev.silver.coins_price_best_practice as t<br>using dev.silver.coins_price_best_practice_temp as s<br>on t.key = s.key<br>when matched then update set *<br>when not matched then insert *</pre><p><strong>Orchestrating Data Pipelines in Databricks</strong></p><p>Databricks provides powerful orchestration capabilities that go beyond just running notebooks. You can design end-to-end pipelines that not only <strong>load and transform data</strong> but also <strong>trigger downstream actions</strong> automatically. For example:</p><ul><li><strong>Dashboard Refresh:</strong> Once data is loaded into tables, the pipeline can automatically refresh dashboards — such as <strong>Databricks dashboards</strong> or <strong>Power BI dashboards</strong> — to ensure the latest data is reflected.</li><li><strong>Model Refresh:</strong> You can also trigger <strong>DBT models</strong> or other analytical workflows as part of the same pipeline.</li></ul><p>This makes Databricks pipelines fully integrated, allowing <strong>data ingestion, transformation, and reporting to work seamlessly together</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fXMH6SDXiywpOdfwutyoeA.png" /></figure><p><strong>Retry Policy:</strong></p><p>You can configure retry policies at the <strong>job or notebook level</strong>, so if a pipeline fails due to transient issues, it automatically reruns a set number of times. This ensures reliability without manual intervention.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q7IFiD4D11AjmKDFHvQrTg.png" /></figure><p><strong>Setting Notifications in Databricks</strong></p><p>Databricks provides flexible options to configure notifications at multiple levels: <strong>Notebook, Job, or Dashboard</strong>, helping teams stay informed about pipeline and report status.</p><p><strong>Notebook or Job Notifications:</strong></p><ul><li>Configure notifications to trigger when a <strong>Notebook or Job succeeds, fails, or starts running</strong>.</li><li>Alerts can be sent via <strong>email or webhook</strong>, allowing stakeholders to stay informed or trigger downstream processes automatically.</li></ul><p><strong>Dashboard Notifications:</strong></p><ul><li>You can also set notifications on <strong>Databricks dashboards</strong>.</li><li>Whenever a dashboard is refreshed, notifications can automatically <strong>send updated dashboard pages</strong> to your selected destinations, such as <strong>email, Teams, or Slack</strong>.</li><li>This ensures that stakeholders always have the <strong>latest view</strong> without manually checking the dashboard, making it easier to track and act on updated data.</li></ul><p>By effectively combining notifications with pipeline orchestration, you <strong>increase reliability, reduce manual monitoring</strong>, and provide a seamless way for teams to stay up-to-date with both <strong>pipeline status and refreshed dashboard data</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o_X3C82YI2S02w8u_eWmnw.png" /></figure><p><strong>Maximum Concurrent Runs</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E4XvPZtsnJU9VEdXho1p4w.png" /></figure><p>Databricks allows setting the maximum number of concurrent runs per job in the advanced settings. This helps scale jobs efficiently, avoid overlapping runs, and ensures reliable execution when multiple triggers or backfills occur.</p><p><strong>Thresholds<br></strong>Databricks allows setting thresholds at the job or notebook level to prevent long-running or stuck jobs. Thresholds are important for reliability, cost control, and operational safety.</p><p><strong>Importance:<br></strong>If thresholds are not set, a stuck or hung job can continue running indefinitely, potentially for days, consuming resources and delaying downstream processes. Setting thresholds ensures that long-running jobs are detected, stopped, and retried if needed, keeping pipelines reliable and efficient.</p><p>There are two types of thresholds:</p><ol><li><strong>Warning Threshold</strong> — Sends an alert if the job exceeds a specified run time, allowing you to monitor potential issues.</li><li><strong>Timeout Threshold</strong> — Automatically fails the job if it runs longer than the set limit. If a retry policy is configured, the job will rerun according to the defined rules.</li></ol><h3>Backfill with Databricks Jobs</h3><p>Backfills should always be <strong>controlled through job parameters</strong> rather than code changes. By parameterizing notebooks (for example, start date, end date, and interval), the same job can be reused for both regular incremental runs and historical backfills.</p><p>When a backfill is triggered, Databricks <strong>creates multiple concurrent job runs</strong> based on the defined backfill interval.<br>For example, if the backfill range includes <strong>18th and 19th December</strong>, Databricks will create <strong>two parallel runs</strong>, each processing its own date range. The level of parallelism is governed by the <strong>Maximum Concurrent Runs</strong> setting (for example, up to 100 concurrent runs).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1arZiVXbGjgtgEbAsHIH1g.png" /></figure><p>Backfills are ideally executed <strong>from the Bronze layer</strong>, since Bronze contains all source data preserved as-is. This approach avoids re-extracting data from the source system and ensures consistency and auditability.</p><p>If data must be reloaded from the source system, the recommended approach is to ingest the updated data as <strong>new files</strong> into the landing zone. Because Databricks Auto Loader tracks processed files using checkpoints, <strong>the same file cannot be reprocessed from the landing bucket</strong>. Reprocessing is therefore only possible by either:</p><ul><li>Loading newly ingested files, or</li><li>Replaying data from the Bronze layer.</li></ul><p>This design ensures reliable, scalable, and repeatable backfills without compromising idempotency or data integrity.</p><h3>Databricks Dashboards</h3><p>To create and run <strong>Databricks dashboards</strong>, a <strong>SQL Warehouse</strong> is required as the compute engine. Dashboards execute SQL queries directly against the warehouse and are tightly integrated with Databricks tables.</p><p>Databricks dashboards support <strong>parameterized queries</strong>, allowing users to pass values from dashboard controls (such as dropdowns or date pickers) to dynamically filter datasets. To enable this, the underlying SQL queries must be written using parameters.</p><p>While Databricks dashboards are well-suited for <strong>operational analytics and lightweight reporting</strong>, their visualization capabilities are more limited compared to tools like <strong>Power BI</strong>, which offer richer visuals and advanced formatting options.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-7i7bSWwnlilmk6mtprpFQ.png" /></figure><h3>Passing Parameters from Dashboards to Queries</h3><p>Dashboard input controls are mapped to SQL query parameters. When a user selects a value in the dashboard, that value is automatically injected into the parameterized query, and the dashboard refreshes accordingly.</p><p>This approach enables:</p><ul><li>Dynamic filtering without modifying SQL code</li><li>Reusable queries across multiple dashboard views</li><li>Interactive analysis directly within Databricks</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ygvMfukoBk4QA3acxz6dvw.png" /></figure><h3>Data Lineage</h3><p>Databricks automatically captures <strong>end-to-end data lineage</strong> when Unity Catalog is enabled. Lineage is tracked regardless of whether transformations are written using <strong>PySpark, Spark SQL, or Delta Live Tables</strong>.</p><p>Lineage is recorded across the full lakehouse stack, including:</p><ul><li><strong>Source volumes / cloud storage locations</strong></li><li><strong>Bronze, Silver, and Gold Delta tables</strong></li><li><strong>Views and materialized views</strong></li><li><strong>Jobs, notebooks, and SQL queries</strong></li><li><strong>Dashboards and downstream consumers</strong></li></ul><p>This lineage is available directly in the <strong>Unity Catalog UI</strong>, without any additional configuration.</p><h3>What Databricks Lineage Shows</h3><p>Databricks lineage allows you to:</p><ul><li>Trace <strong>where the data originated</strong> (volume, external location, or table)</li><li>See <strong>which notebooks, jobs, or SQL queries</strong> produced a table</li><li>Identify <strong>downstream dependencies</strong> such as dashboards and reports</li><li>Perform <strong>impact analysis</strong> before changing schemas or logic</li></ul><p>Lineage is captured at:</p><ul><li><strong>Table level</strong></li><li><strong>Column level</strong> (for supported operations)</li><li><strong>Volume / external location level</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8k9F0UUsWTwETIRY_WAzWA.png" /></figure><h3>Why Data Lineage Is Important</h3><ul><li><strong>Impact Analysis</strong> — Understand what breaks before changing a table or column</li><li><strong>Debugging</strong> — Quickly trace data quality issues back to the source</li><li><strong>Governance &amp; Auditing</strong> — Meet compliance and audit requirements</li><li><strong>Operational Confidence</strong> — Know exactly how data flows through the platform</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=30e65bf4c520" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Real-Time Data Pipeline on Google Cloud Platform (GCP)]]></title>
            <link>https://medium.com/@asindugayangana/building-a-real-time-data-pipeline-on-google-cloud-platform-gcp-ad8f0df7bfeb?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/ad8f0df7bfeb</guid>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Fri, 21 Nov 2025 03:43:21 GMT</pubDate>
            <atom:updated>2025-11-21T03:43:21.355Z</atom:updated>
            <content:encoded><![CDATA[<p>I have designed and implemented a <strong>real-time data pipeline</strong> on <strong>Google Cloud Platform (GCP)</strong>. The primary objective of this project was to automatically <strong>extract data from files as soon as they are uploaded</strong> and <strong>load it into BigQuery in real time</strong> for analytics and reporting.</p><p>To achieve this, I leveraged <strong>Pub/Sub</strong> to handle event-driven messaging. A <strong>Cloud Storage notification</strong> was configured on a GCS bucket so that whenever a new file is uploaded, metadata and the file path are automatically published to the Pub/Sub topic. A <strong>subscription</strong> then consumes these messages, which are processed using <strong>Apache Beam running on Dataflow</strong>. Beam reads the files from Cloud Storage, stores raw copies in the raw layer for auditing, transforms and processes the data, and finally writes the cleaned and structured output into <strong>BigQuery</strong> for analytics.</p><h3>High-Level Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fi-j4UI9NMwiDc-e4gIDIQ.png" /></figure><ol><li>A file is uploaded into the Landing Bucket</li><li>This triggers a Cloud Storage Notification.</li><li>Notification publishes an event into Pub/Sub.</li><li>A subscriber receives the message.</li><li>Dataflow (Apache Beam) reads file metadata, loads the file, processes it.</li><li>Raw data is moved to the Raw Layer bucket.</li><li>Processed data is written to BigQuery.</li><li>A secondary table tracks processed files to prevent duplicates.</li></ol><h3>Step 1 — Create a New GCP Project</h3><p>Create a dedicated project for the pipeline and enable required APIs:</p><ul><li>Cloud Storage</li><li>Pub/Sub</li><li>Dataflow</li><li>BigQuery</li><li>Cloud Logging &amp; Monitoring</li></ul><h3>Step 2 — Create a Pub/Sub Topic</h3><p>Go to Pub/Sub → Topics → Create Topic.</p><p>Example: pub-sub-topic</p><p>Important configurations:</p><ul><li>Schema: Optional, enforce schema validation for message quality.</li><li>Retention Period: Increase beyond 7 days if downstream systems may experience delays.</li></ul><h3>Step 3 — Create a Pub/Sub Subscription</h3><p>Inside the topic, create a subscription.</p><p>Example: subscriber</p><p>Delivery type:</p><ul><li>Pull (recommended for Dataflow / Beam)- We used Pull for this practice</li><li>Push (only for Cloud Functions or HTTP endpoints)</li></ul><p>Recommended settings:</p><ul><li>Acknowledge Deadline: ~10 seconds (tune based on processing time)</li><li>Dead-letter Topic: Optional, for capturing failed events</li><li>Retry Policy: Exponential backoff for reliability</li></ul><h3>Step 4 — Create Required Cloud Storage Buckets</h3><p>You need three buckets, each serving a different purpose:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4gwbKc6f22zh4bYRWCcHog.png" /></figure><p>Folder Structure:<br>Inside the landing bucket, create functional folders for categorization:</p><ul><li>/orders/</li><li>/inventory/</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TkSQSY_mImrcdK18AWrwrg.png" /></figure><h3>Step 5 — Configure Cloud Storage Notification → Pub/Sub</h3><p>This connects the Landing Bucket to Pub/Sub, so each file upload triggers a message.</p><p>Event message includes metadata:</p><ul><li>filename</li><li>bucket</li><li>event type (OBJECT_FINALIZE)</li><li>timestamp</li><li>CRC32C / MD5 hash</li></ul><p>Requirements:</p><ul><li>Pub/Sub API enabled</li><li>Service account must have Pub/Sub Publisher role</li></ul><p>Example command:</p><pre><br>gsutil notification create \                                                                                                                                       -t pub-sub-topic \<br> -f json \<br> -e OBJECT_FINALIZE \<br>gs://landing-layer<br><br></pre><p>Verification:</p><ul><li>Upload a test file → Pub/Sub → Subscription → Pull Messages → See the metadata event.</li></ul><h3>Step 6 — BigQuery Setup</h3><ol><li>Dataset Creation</li></ol><p>Dataset: silver</p><ul><li>BigQuery datasets are organizational containers: they group tables and manage access, but do not store data themselves.</li></ul><ol><li>Tables Created</li></ol><ul><li>orders → main transactional table</li><li>processed_file → tracks processed files (prevents duplicates</li></ul><h3>Step 7 — BigQuery Table Design &amp; Optimization</h3><h4>Partitioning</h4><ul><li>Partition orders table by order_date (TIMESTAMP)</li><li>Faster queries for recent data</li><li>Lower query cost (scans only necessary partitions)</li></ul><h4>Clustering</h4><ul><li>Cluster by commonly filtered columns:(country, product_id, customer_id, city)</li><li>Improves query performance by scanning only relevant data blocks</li></ul><h4>Schema Design Best Practices</h4><ol><li>Use correct data types aligned with the source.</li></ol><p>2. Flatten nested structures unless using JSON columns</p><p>3. Include metadata columns:</p><ul><li>source_file_name</li><li>loaded_timestamp</li></ul><p>Lifecycle Management</p><ul><li>Partition Expiration: Automatically delete old partitions to reduce storage cost</li></ul><pre>ALTER TABLE `project.dataset.table`<br>SET OPTIONS (partition_expiration_days = 180);</pre><ul><li>Table Expiration: Use only for temporary or intermediate tables</li></ul><h3>Step 8 — Data Quality Checks (Optional but Recommended)</h3><p>Using BigQuery Data Quality or Dataplex:</p><p>Mandatory Checks:</p><ul><li>Null checks on critical columns</li><li>Pattern validation on product_code (6 digits, starts with “P”)</li></ul><p>Monitoring &amp; Alerts:</p><ul><li>Email notifications</li><li>Scheduled reports</li><li>Automated incident alerts</li></ul><h3>Step 9 — IAM Roles &amp; Service Account Setup</h3><p>Dataflow, BigQuery, Pub/Sub, and GCS use a Service Account, not your personal account.</p><p>Why a single service account?</p><ul><li>Easier permission management</li><li>Cleaner auditing</li><li>Avoids “permission denied” errors</li><li>Consistent access across services</li></ul><p>Minimum Required IAM Roles:</p><p>BigQuery:</p><ul><li>BigQuery Data Editor</li><li>BigQuery Job User</li></ul><p>Dataflow:</p><ul><li>Dataflow Worker</li><li>Dataflow Admin (if SA triggers pipeline)</li></ul><p>Pub/Sub:</p><ul><li>Pub/Sub Subscriber</li><li>Pub/Sub Publisher</li><li>Pub/Sub Viewer</li></ul><p>Cloud Storage:</p><ul><li>Storage Object Admin</li></ul><p>Logging &amp; Monitoring:</p><ul><li>Logs Writer</li><li>Monitoring Metric Writer</li></ul><p>Service Account Permissions:</p><ul><li>Service Account User</li><li>Service Account Token Creator</li></ul><p>This ensures Dataflow worker VMs can start, authenticate, and access all resources.</p><h3><strong>Apache Beam for Pipeline Development</strong></h3><p>Apache Beam is an open-source, unified programming model designed for building both batch and streaming (real-time) data pipelines. Instead of writing separate pipelines for batch and streaming, Beam allows you to write one pipeline that runs anywhere.</p><p>Apache Beam supports multiple programming languages, including Java, Python, and Go, allowing developers to choose the language that best fits their workflow.</p><p>Beam pipelines can run on a variety of execution engines:</p><ul><li>Google Cloud Dataflow — the most common choice; fully serverless and autoscaling.</li><li>Apache Spark — for distributed batch and streaming processing.</li><li>Apache Flink — ideal for low-latency, stateful stream processing.</li><li>DirectRunner — for local development and testing.</li></ul><p>Beam processes data using PCollections, which are distributed datasets similar to Spark DataFrames or RDDs, but more flexible and suitable for both batch and streaming operations.</p><p>When running Beam pipelines on Dataflow:</p><ul><li>Google automatically provisions worker VMs.</li><li>Autoscaling adjusts the number of workers based on workload, scaling up under high load and scaling down when demand is low.</li><li>Parallel processing is handled automatically across all workers.</li><li>Checkpointing, retries, and fault-tolerance are built in, reducing the operational burden.</li><li>Developers can focus entirely on writing Beam pipeline code rather than managing infrastructure.</li></ul><p>This combination of Beam + Dataflow makes it the recommended approach on GCP for handling high-volume, real-time data pipelines.</p><h4>Pipeline operations in Beam include:</h4><ul><li>ParDo (DoFn): row-level processing for transforming individual elements.</li><li>Map / FlatMap: simple element-wise transformations.</li><li>GroupByKey: grouping operations based on key fields.</li><li>Combine: aggregations across grouped elements.</li><li>Window: time-based grouping for streaming data.</li></ul><p>Windows allow pipelines to process data in discrete time intervals. For example, a 5-minute window will accumulate all incoming events for five minutes before processing them together. Within each window, data can be deduplicated or aggregated according to business logic, ensuring correct and efficient handling of streaming data.</p><h4><strong>Step 10 — Example Beam Pipeline Flow</strong></h4><p>Here’s a simple and clear way to explain the first class and function:</p><pre><br>class ParseMessage(beam.DoFn):<br>   def process(self, element):<br>       for attempt in range(1, MAX_RETRIES + 1):<br>           try:<br>               message = element.decode(&quot;utf-8&quot;)<br>               data = json.loads(message)<br>               file_name = data.get(&quot;name&quot;, &quot;N/A&quot;)<br>               bucket = data.get(&quot;bucket&quot;, &quot;N/A&quot;)<br>               event_type = file_name.split(&#39;/&#39;)[0]<br>               yield {<br>                   &quot;file_name&quot;: file_name,<br>                   &quot;bucket&quot;: bucket,<br>                   &quot;event_type&quot;: event_type<br>               }<br>               break<br>           except Exception as e:<br>               print(f&quot;ParseMessage attempt {attempt} failed: {str(e)}&quot;)<br>               time.sleep(2 ** attempt)<br>               if attempt == MAX_RETRIES:<br>                   write_to_dlq(element, str(e))<br><br></pre><h3>Sample Pipeline Flow (Refined Explanation)</h3><ol><li>Pipeline Start — Read Messages from Pub/Sub</li></ol><ul><li>The pipeline begins by reading messages in real-time from a Pub/Sub subscription.</li><li>The output of this step is a PCollection, which is Beam’s abstraction for a distributed dataset.</li><li>Each element of the PCollection represents a single message from Pub/Sub.</li></ul><p>2. Parse Messages — ParseMessage() with ParDo()</p><ul><li>ParDo is Beam’s core parallel processing transformation.</li><li>It applies a DoFn (a user-defined processing function) to each element of a PCollection independently.</li><li>In this case, ParseMessage() decodes the Pub/Sub message, parses the JSON, and extracts relevant fields such as file_name, bucket, and event_type.</li><li>The function uses yield to produce a new PCollection with structured data.</li><li>Beam executes these functions in parallel across worker nodes, allowing scalable processing of high-throughput streams.</li></ul><p>3. Filter Orders — FilterOrders</p><ul><li>After parsing, the PCollection is filtered to retain only messages where event_type is “orders”.</li><li>This ensures that unrelated messages (e.g., inventory events) do not proceed further in the pipeline.</li></ul><p>4. Check Processed Files — CheckIfProcessed()</p><ul><li>This DoFn queries BigQuery to check whether a file has already been processed.</li><li>It outputs two PCollections using Beam’s side outputs:</li><li>new → files that have not been processed yet.</li><li>skip → files that were already processed.</li><li>This separation ensures idempotency, so the pipeline does not process the same file multiple times.</li></ul><p>5. Process Only New Files</p><ul><li>Only the new PCollection is sent to the next stage of the pipeline.</li><li>This stage reads the raw file from Cloud Storage, saves it in the raw layer, parses the order items, and prepares the records for BigQuery.</li><li>The skip PCollection is ignored for processing, but can be logged or monitored if needed.</li></ul><p>6. Write to BigQuery</p><ul><li>The final PCollection of structured order records is written to the orders table in BigQuery.</li><li>Beam handles batching, retries, and distributed writes automatically.</li></ul><p>7. Dependency Flow</p><ul><li>Each stage in the pipeline depends on the output of the previous stage.</li><li>By using PCollections and side outputs, Beam ensures that only new files are processed, maintaining data consistency and avoiding duplicates.</li></ul><pre>with beam.Pipeline(options=options) as p:<br>   checked = (<br>       p<br>       | &quot;ReadFromSubscription&quot; &gt;&gt; beam.io.ReadFromPubSub(subscription=subscription_path)<br>       | &quot;ParseMessage&quot; &gt;&gt; beam.ParDo(ParseMessage())<br>       | &quot;FilterOrders&quot; &gt;&gt; beam.Filter(lambda x: x[&quot;event_type&quot;].lower() == &quot;orders&quot;)<br>       | &quot;CheckIfProcessed&quot; &gt;&gt; beam.ParDo(CheckIfProcessed()).with_outputs(&#39;new&#39;, &#39;skip&#39;)<br>   )<br><br>   new_files = checked.new<br>   skipped_files = checked.skip<br><br>   processed = (<br>       new_files<br>       | &quot;ReadAndSaveRaw&quot; &gt;&gt; beam.ParDo(ReadAndSaveRaw())<br>       | &quot;ParseOrderMessage&quot; &gt;&gt; beam.ParDo(ParseOrderMessage())<br>   )<br><br>   processed | &quot;WriteToBigQuery&quot; &gt;&gt; WriteToBigQuery(<br>       table=f&quot;{PROJECT_ID}:silver.orders&quot;,<br>       write_disposition=beam.io.BigQueryDisposition.WRITE_APPEND,<br>       create_disposition=beam.io.BigQueryDisposition.CREATE_IF_NEEDED<br>   )<br></pre><p>Once the pipeline code is ready, <strong>save it as a Python file</strong> (e.g., pipeline.py).</p><p>To run the pipeline and create a Dataflow job via the CLI:</p><ol><li><strong>Set up a Python environment</strong> (virtual environment or Conda).</li><li><strong>Install all required dependencies</strong> listed in the Python file.</li><li><strong>Execute the Python file</strong> using the command:</li></ol><p>The pipeline options defined within the Python file (such as runner=” DataflowRunner”, project, region, and temp/staging locations) instruct Apache Beam to <strong>submit the job to Dataflow</strong>, automatically provisioning worker VMs and running the pipeline in streaming or batch mode as configured.</p><pre>options = PipelineOptions(<br>runner=&quot;DataflowRunner&quot;,   # This is required to submit to Dataflow<br>project=PROJECT_ID,<br>region=&quot;us-central1&quot;,<br># temp_location=&quot;gs://gcp-dev-temp/temp&quot;, <br># staging_location=&quot;gs://gcp-dev-temp/staging&quot;,<br>streaming=True<br>)<br>options.view_as(StandardOptions).streaming = True                                          </pre><p>Graphical view of the real-time Apache Beam pipeline running on Google Cloud Dataflow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r5GTLxCyA01IALPVfcWKNw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZGzp2fBZq5lNCsj7tMh9ig.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad8f0df7bfeb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Best Practices for Azure Synapse CI/CD Deployment]]></title>
            <link>https://medium.com/@asindugayangana/best-practices-for-azure-synapse-ci-cd-deployment-10d105a202e1?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/10d105a202e1</guid>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Mon, 13 Oct 2025 09:47:28 GMT</pubDate>
            <atom:updated>2025-10-13T09:47:28.813Z</atom:updated>
            <content:encoded><![CDATA[<p>This article describes how to develop a <strong>Continuous Integration and Continuous Delivery (CI/CD)</strong> pipeline for <strong>Azure Synapse Analytics</strong> using <strong>GitHub Actions</strong>, following industry best practices. For this setup, we use <strong>two Synapse environments</strong> — <strong>Development</strong> and <strong>Production</strong>.</p><h3><strong>Theoretical Framework: Building a Synapse CI/CD Pipeline</strong></h3><h3>1. Connect Only the Development Workspace to a Git Repository</h3><p>Only the <strong>development Synapse workspace</strong> should be integrated with the Git repository.<br>The <strong>UAT</strong> and <strong>Production</strong> workspaces should always remain in <strong>live mode</strong> (disconnected from Git).</p><p>This ensures:</p><ul><li>All changes and development activities are performed <strong>only in the development environment</strong>.</li><li>No manual or accidental changes occur directly in UAT or Production.</li><li>Deployment consistency and governance are maintained across environments.</li></ul><p>Once changes are ready and tested in Development, the <strong>Synapse Publish</strong> operation generates <strong>ARM templates</strong> (in the workspace_publish branch), which are then deployed to higher environments through an automated CI/CD pipeline.</p><h3>2. Use Feature Branches for All Development Work</h3><p>All new work should be done in <strong>feature branches</strong>, <strong>not</strong> directly in the main branch.<br>Developers should:</p><ul><li>Create a feature branch for their work.</li><li>Commit and test changes in that branch.</li><li>Create a <strong>Pull Request (PR)</strong> to merge into the main branch.</li></ul><p>This approach ensures:</p><ul><li>Code review and validation before merging.</li><li>Controlled and auditable change management.</li><li>Clean and stable main branch for publishing and deployment.</li></ul><h3>3. Add Branch Policies to Protect the Main Branch</h3><p>Enforce <strong>branch policies</strong> on the main branch to maintain quality and control:</p><ul><li>Require <strong>minimum code reviewers</strong> (e.g., 1–2 reviewers) before merging.</li><li>Enable <strong>build validation</strong> to ensure code compiles and passes checks before merging.</li><li>Restrict direct pushes to main — only PR merges should be allowed.</li></ul><p>These policies improve collaboration, governance, and deployment stability.</p><h3>4. Maintain Consistent Resource Names Across Environments</h3><p>Use <strong>consistent naming conventions</strong> for shared resources across environments, such as:</p><ul><li>Spark pool names</li><li>Key Vault secret names</li><li>Integration datasets and linked service names</li></ul><p>This helps:</p><ul><li>Minimize ARM template parameterization.</li><li>Simplify deployment configuration.</li><li>Avoid exceeding the <strong>20 MB ARM template size limit</strong>, which can cause deployment failures.</li></ul><p><strong>Example:<br></strong>If your dev Key Vault is kv-synapse-dev and production Key Vault is kv-synapse-prod, keep the <strong>secret names identical</strong> (e.g., adls-connection-string/ SQL-password).</p><h3>5. Parameterize Only Environment-Specific Configurations</h3><p>Keep ARM template parameterization to the <strong>minimum necessary</strong>, focusing on:</p><ul><li>Linked services (e.g., different ADLS, SQL, or Key Vault URIs)</li><li>Environment variables or configuration differences</li></ul><p>Avoid parameterizing resource names like Spark pools or pipelines unless required.<br>This improves template maintainability and reduces complexity.</p><h3>6. Validate and Version Control Published Artifacts</h3><p>After publishing from the dev environment:</p><ul><li>Commit the generated ARM templates and parameter files (from workspace_publish) into version control.</li><li>Maintain <strong>separate parameter files per environment</strong> (e.g., dev.parameters.json, prod.parameters.json).</li><li>Use CI/CD pipelines (e.g., GitHub Actions, Azure DevOps Pipelines) to deploy to the target environment using the appropriate parameter file.</li></ul><h3>7. Validate Deployment Logs and Governance</h3><p>After each deployment:</p><ul><li>Validate pipeline execution logs.</li><li>Verify Synapse resources (pipelines, datasets, triggers) are properly updated.</li><li>Track deployment history for compliance and rollback readiness.</li></ul><h3>8. Security and Access Governance</h3><ul><li>Restrict write permissions to production environments.</li><li>Use <strong>Managed Identities</strong> wherever possible instead of service principals.</li><li>Store all credentials in <strong>Azure Key Vault</strong>.</li><li>Limit direct workspace access — manage deployment via pipelines only.</li></ul><h3>The Step-by-Step Lab Guide</h3><h3>CI/CD Pipeline for Azure Synapse Analytics with GitHub Actions</h3><p>This guide provides a step-by-step process to establish a robust Continuous Integration and Continuous Deployment (CI/CD) pipeline for Azure Synapse Analytics, integrating with GitHub Actions for automated deployments from a development environment to a production environment.</p><h3>Architecture Overview</h3><p>The pipeline follows a standard pattern:</p><ol><li><strong>Development (Dev):</strong> All development work (pipelines, datasets, scripts) is done in the workspace-development Synapse workspace.</li><li><strong>Source Control:</strong> Code is committed to a <strong>GitHub repository</strong> (e.g., main branch).</li><li><strong>Continuous Integration (CI):</strong> Synapse’s “Live Mode” integrates with the Git repo. Publishing creates an ARM template in the workspace_publish branch.</li><li><strong>Continuous Deployment (CD):</strong> A <strong>GitHub Actions workflow</strong> is triggered on updates to the workspace_publish branch. It uses an Azure Service Principal (App Registration) to authenticate and deploys the ARM template to the <strong>Production Synapse workspace</strong> (workspace-production).</li></ol><h3>Phase 1: Prerequisite Azure Infrastructure Setup</h3><p>Create the following resources in your Azure subscription. It is recommended to use infrastructure-as-code (e.g., Terraform, Bicep) for this, but manual creation via the portal is also valid.</p><h4>1.1 Create Resource Groups &amp; Core Resources</h4><p>Create two separate resource groups to isolate your environments.</p><p><strong>Environment</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*chx2FqHvuTsfZ-1obhKcMw.png" /></figure><p><strong>Important:</strong> Ensure the Synapse Workspace is created with the ADLS Gen2 account and file system selected during creation.</p><h4>1.2 Create Spark Pools</h4><p>Create identical Spark Pools (e.g., sparkpool01) in <strong>both</strong> workspace-development and workspace-production. Using the same name in both environments is critical for the ARM template deployment to work correctly.</p><h4>1.3 Prepare ADLS Gen2 Storage</h4><p>In both the developmentadls and productionadls storage accounts:</p><ol><li>Create two containers:</li></ol><ul><li>bronze (for raw data)</li><li>silver (for cleansed/processed data)</li></ul><p>2. Upload a sample dataset for testing:</p><ul><li>Path: bronze/sales/sales_data.csv</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cUFot2j6o2CaQISDDohe3A.png" /></figure><h3>Phase 2: Security and Access Configuration</h3><h4>2.1 Create an Azure App Registration (Service Principal)</h4><p>This identity will be used by GitHub Actions to authenticate to Azure and perform deployments.</p><ol><li>Go to <strong>Azure Portal &gt; Azure Active Directory &gt; App registrations &gt; New registration</strong>.</li><li>Give it a name (e.g., synapse-cicd-spn).</li><li>Click <strong>Register</strong>.</li><li><strong>Note down the “Application (client) ID” and “Directory (tenant) ID”</strong> from the overview page.</li></ol><h4>2.2 Generate a Client Secret</h4><ol><li>In your App Registration, go to <strong>Certificates &amp; secrets</strong>.</li><li>Click <strong>+ New client secret</strong>.</li><li>Provide a description and select an expiry date.</li><li><strong>Click “Add” and immediately copy the secret value.</strong> You will not be able to retrieve it again.</li></ol><h4>2.3 Configure Azure Key Vaults</h4><p><strong>For both the Development and Production Key Vaults:</strong></p><ol><li><strong>Store ADLS Key:</strong> Navigate to your ADLS Gen2 storage account (developmentadls/productionadls).</li></ol><ul><li>Go to <strong>Security + networking &gt; Access keys</strong>.</li><li>Copy one of the keys.</li><li>In your Key Vault (kv-development-001/kv-production-001), go to <strong>Secrets &gt; Generate/Import</strong>.</li><li><strong>Name:</strong> adls</li><li><strong>Value:</strong> Paste the copied access key.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ihT0YkR6Z2Hc7VJz_Hbjmg.png" /></figure><p><strong>2. Grant Synapse Access:</strong> Each Synapse workspace needs to read secrets from its respective Key Vault.</p><ul><li>Go to your Synapse workspace (workspace-development) &gt; <strong>Overview</strong> &gt; <strong>Managed identity</strong> &gt; copy the Object (Principal) ID.</li><li>Go to your Key Vault (kv-development-001) &gt; <strong>Access control (IAM) &gt; Add role assignment</strong>.</li><li><strong>Role:</strong> Key Vault Secrets User</li><li><strong>Assign access to:</strong> Managed Identity</li><li><strong>Members:</strong> Select your workspace-development managed identity.</li><li><strong>Repeat</strong> this process for workspace-production and kv-production-001.</li></ul><h4>2.4 Grant the App Registration Access to Production</h4><p>The Service Principal needs permission to deploy artifacts to the production Synapse workspace.</p><ol><li>Go to the <strong>Production Synapse workspace</strong> (workspace-production) &gt; <strong>Access control (IAM) &gt; Add role assignment</strong>.</li><li><strong>Role:</strong> Synapse Artifact Publisher</li><li><strong>Assign access to:</strong> User, group, or service principal</li><li><strong>Members:</strong> Search for and select your App Registration (synapse-cicd-spn).</li></ol><h3>Phase 3: Synapse Studio Development &amp; Git Configuration</h3><p>All following steps are performed in the <strong>Development Synapse workspace</strong>.</p><h4>3.1 Configure Git Integration in Synapse Studio</h4><ol><li>In Synapse Studio, go to <strong>Manage &gt; Git configuration</strong>.</li><li>Click <strong>Configure</strong> and connect to your GitHub repository.</li><li>Select the <strong>Collaboration branch</strong> (e.g., main). This is where you will commit your code.</li><li>For the <strong>Publish branch</strong>, leave it as the default workspace_publish. This is the branch where Synapse will automatically generate the ARM templates upon publishing.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b_CPU4icvdeFB5bcayyJ-A.png" /></figure><h4>3.2 Create Parameterized Linked Services</h4><p><strong>Create a Key Vault Linked Service (in Dev only):</strong></p><ol><li>Go to <strong>Manage &gt; Linked services &gt; New</strong>.</li><li>Search for and select <strong>Azure Key Vault</strong>.</li><li><strong>Name:</strong> AzureKeyVault</li><li><strong>Base URL:</strong> Click Add dynamic content [Alt+Enter]. Enter a parameter, e.g., @{linkedService().base_url_key_vault_param}.</li><li>In the <strong>Parameters</strong> tab, create a new parameter named AKVBaseUrl.</li><li><strong>Test connection</strong> and save.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2xkxbGBe06cDOSaIjldyjA.png" /></figure><p><strong>Create an ADLS Gen2 Linked Service (in Dev only):</strong></p><ol><li><strong>New linked service &gt; Azure Data Lake Storage Gen2</strong>.</li><li><strong>Name:</strong> AzureDataLakeStorage</li><li><strong>Authentication type:</strong> Account Key</li><li><strong>URL:</strong> Parameterize this. Use @{linkedService().base_url_param}.</li><li><strong>Account Key:</strong> Select the <strong>Azure Key Vault</strong> option.</li></ol><ul><li><strong>Azure Key Vault:</strong> Select the AzureKeyVault linked service you just created.</li><li><strong>Secret Name:</strong> adls-storage-key</li></ul><p>6. In the <strong>Parameters</strong> tab, create a parameter named ADLSBaseUrl.</p><p><strong>7. Test connection</strong> (it will use the dev Key Vault secret). Save.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RDeKmp1F6FsQ34fiefqSkA.png" /></figure><h4>3.3 Create a Parameterized Dataset and Pipeline</h4><p><strong>1. </strong>Create a parameterized dataset for CSV files.</p><ul><li><strong>Linked Services: Azure Data Lake Storage</strong></li><li><strong>Path:</strong> Parameterize the path (e.g., bronze/sales). This allows you to point to different containers in different environments.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MXWpCeyMPtrZbaxrJlcwMg.png" /></figure><p><strong>2. Create a Pipeline:</strong> Build a simple pipeline that uses this dataset (e.g., a Copy Data activity).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4iE-9zzf5Yr7Jg_uUw4v7Q.png" /></figure><p><strong>3. Create a Trigger:</strong> Set up a trigger (e.g., a schedule trigger) for your pipeline.</p><h4>3.4 Publish and Generate ARM Templates</h4><ol><li>After committing your changes to the collaboration branch (main), click <strong>Publish</strong>.</li><li>This will generate/update the ARM templates (TemplateForWorkspace.json) and a parameter file (TemplateParametersForWorkspace.json) in the workspace_publish branch of your GitHub repository.</li></ol><h4>3.5 Create a Production Parameter File</h4><p>The default parameter file contains development values. You must create an environment-specific file for production.</p><ol><li>In your GitHub repository, on the workspace_publish branch, create a copy of TemplateParametersForWorkspace.json.</li><li>Name it TemplateParametersForProduction.json.</li><li>Update all values inside to point to your <strong>production resources</strong>:</li></ol><ul><li>workspaceName: workspace-production</li><li>base_url_param: <a href="https://productionadls.dfs.core.windows.net/">https://productionadls.dfs.core.windows.net</a></li><li>base_url_key_vault_param: <a href="https://kv-production-001.vault.azure.net/">https://kv-production-001.vault.azure.net/</a></li><li>… and all other parameters.</li></ul><p>For the ARM template deployment to work correctly across different environments, you must create separate parameter files. These files provide the environment-specific values (like resource names and URLs) during deployment.</p><h4>1. Default Development Parameter File</h4><p>When you publish from Synapse Studio, it automatically generates a default parameter file named TemplateParametersForWorkspace.json in the workspace_publish branch. This file contains the values from your <strong>development</strong> environment.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4SxXGWbYBpuHX-wdEsCOGQ.png" /></figure><p><strong>2. Creating the Production Parameter File</strong></p><p>You must create a dedicated parameter file for production to ensure the ARM template points to your production resources, not the development ones.</p><ol><li>In your workspace_publish branch, create a new file.</li><li>Name it TemplateParametersForProduction.json.</li><li>Populate it with the connection details and names of your <strong>production</strong> resources.</li></ol><p><strong>Example: TemplateParametersForProduction.json (Production)</strong></p><p>{<br> “$schema”: “<a href="https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#">https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#</a>&quot;,<br> “contentVersion”: “1.0.0.0”,<br> “parameters”: {<br> “workspaceName”: {<br> “value”: “workspace-development”<br> },<br> “workspace-development-WorkspaceDefaultSqlServer_connectionString”: {<br> “value”: “Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=ctp:workspace-development.sql.azuresynapse.net”<br> },<br> “AzureDataLakeStorage_properties_typeProperties_url”: {<br> “value”: “<a href="https://developmentadls.dfs.core.windows.net/">https://[production adls name].dfs.core.windows.net</a>&quot;<br> },<br> “AzureKeyVault_properties_typeProperties_baseUrl”: {<br> “value”: “<a href="https://[REDACTED].vault.azure.net/">https://[</a><a href="https://developmentadls.dfs.core.windows.net/">production key vault name</a><a href="https://[REDACTED].vault.azure.net/">].vault.azure.net/</a>&quot;<br> },<br> “workspace-development-WorkspaceDefaultStorage_properties_typeProperties_url”: {<br> “value”: “<a href="https://developmentadls.dfs.core.windows.net/">https://[</a><a href="https://developmentadls.dfs.core.windows.net/">production adls name</a><a href="https://developmentadls.dfs.core.windows.net/">].dfs.core.windows.net</a>&quot;<br> }<br> }<br>}</p><h3>Phase 4: GitHub Actions CI/CD Pipeline Setup</h3><h4>4.1 Store Azure Credentials in GitHub Secrets</h4><p>In your GitHub repository:</p><ol><li>Go to <strong>Settings &gt; Secrets and variables &gt; Actions &gt; New repository secret</strong>.</li><li>Create the following secrets:</li></ol><ul><li>AZURE_CLIENT_ID: The Application (Client) ID of your App Registration.</li><li>AZURE_CLIENT_SECRET: The Client Secret you generated.</li><li>AZURE_TENANT_ID: The Directory (Tenant) ID of your App Registration.</li><li>AZURE_SUBSCRIPTION_ID: Your Azure Subscription ID.</li></ul><h4>4.2 Create the GitHub Actions Workflow</h4><p>Create a new file in your repository at .github/workflows/synapse-deploy-production.yml.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Gj6zyfzKS-Qg7VI8nDY8CA.png" /></figure><h3>Phase 5: Validation &amp; Governance</h3><h4>Post-Deployment Checks</h4><ul><li>Monitor deployment logs</li><li>Verify Synapse resources updated correctly</li><li>Maintain deployment history for compliance</li><li>Test pipeline execution in production</li></ul><h4>Security Best Practices</h4><ul><li>Use Managed Identities where possible</li><li>Store all credentials in Azure Key Vault</li><li>Restrict production write access</li><li>Manage deployments via pipelines only</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=10d105a202e1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Delta Tables]]></title>
            <link>https://medium.com/@asindugayangana/delta-tables-a99cfe799557?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/a99cfe799557</guid>
            <category><![CDATA[delta-tables]]></category>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Wed, 10 Sep 2025 10:38:49 GMT</pubDate>
            <atom:updated>2025-09-10T10:38:49.237Z</atom:updated>
            <content:encoded><![CDATA[<p>A Delta Table is a modern table format built on top of Parquet files. It provides version control, schema enforcement, and schema evolution, while supporting ACID transactions (Atomicity, Consistency, Isolation, Durability). These features enable reliable concurrent read, write, update, and delete operations, ensuring data integrity and corruption-free tables. Delta Tables unify batch and streaming workloads within a single framework.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OSXxWRRP2fyW0nVzJu5njg.png" /></figure><p><strong>Why Parquet?</strong></p><p>Delta Tables are built on the Parquet file format, which offers fast analytical performance due to its columnar storage structure.</p><h4>Creating a Delta Table</h4><p>Delta Tables can be created using PySpark or SQL. When loading data, two primary modes are available:</p><ol><li>Overwrite — Replaces existing data with the new dataset.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZUYCpHXtxMytJYEAVQyP5w.png" /></figure><ol><li>Append — Adds new records without removing existing ones.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3APPZNHLlNWikAylmQdS4w.png" /></figure><h4>Partition Overwrite in Delta Lake</h4><p>Partition overwrite is particularly useful for large, partitioned datasets. Instead of rewriting the entire table, only the relevant partitions in the incoming dataset are updated.</p><p>How it works:</p><ul><li>Data is often partitioned by a key column (e.g., date).</li><li>Each partition is stored in a separate folder under the table’s storage path.</li><li>With dynamic partition overwrite, only the affected partitions are replaced, leaving others untouched.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O_3JQKkgNND07rCUIQ8xXA.png" /></figure><h3><strong>Schema Handling</strong></h3><h4>Schema Enforcement</h4><p>Prevents invalid data from being written by ensuring that the incoming data’s schema matches the table’s schema (columns, data types, nullability). Non-matching writes are rejected.</p><h4>Schema Evolution</h4><p>Enables changes to the schema over time (e.g., adding new columns) without breaking existing queries.</p><p>Options:</p><p><strong>overwriteSchema</strong> — Replaces the table’s schema with the new dataset schema. Works only in overwrite mode and does not alter column data types if existing data is present.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*py1oFwjQIegxwUtgohf2-w.png" /></figure><p><strong>mergeSchema</strong> — Adds new columns from the incoming dataset while preserving existing columns and data. Works in both append and overwrite modes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gckQBLg9Eun9xsbcAwSXTw.png" /></figure><h3>Version Control</h3><p>Delta Tables allow time travel across different states of the data. Every transaction (insert, update, delete, or merge) creates a new Parquet file along with an updated transaction log file.</p><ol><li>The transaction log (_delta_log) records which Parquet files represent the latest state of the table.</li><li>Older versions are retained temporarily, making them accessible for historical queries and audits.</li></ol><h4>How Delta Manages Versions</h4><p>Example workflow:</p><p>Initially created table with data set. It’s file view exists as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Xkf4U_YyDZLp2NXIDHJZiA.png" /></figure><ol><li>A record is updated or deleted.</li><li>Delta creates a new Parquet file reflecting the change without updating the existing one.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G6oGtKIUVM78nIbafAQhpg.png" /></figure><ol><li>A new JSON log file (e.g., 00000000000000000001.json) is written to _delta_log same time.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4y0uIXABym9BOfqTZSbDcg.png" /></figure><ol><li>That log file’s content marking old files as removed and the new file as active. See below images</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wk1mGdFxqegs4S9tT64y-w.png" /></figure><p>This ensures Delta Tables remain transactionally consistent and recoverable.</p><h3>Viewing Transaction History</h3><p>The transaction history of a Delta Table can be queried to review operations and versions.Each operation (insert, update, delete) creates a new version.</p><p>To view the operations history and version history, use below query.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D3p0Waz8vFtE51b1HtjYpA.png" /></figure><p>You can see there are 4 records since 4 operations happened. Each operation has it’s own version.</p><p>If you want to view the table’s state before the delete operation happened. That version is 0. But the current version of the table is 3. So you have to view the version as of 0.</p><p>You can query a table at a specific version to “time travel” and view historical data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Uj0UpVUOewqhpFjOU6T9ow.png" /></figure><ul><li>To restore an earlier version, you can simply query or revert to that version (e.g: now you can see the deleted record is available in the table- id-301).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j2_9mfIAs8yAbUAL-mc_Bg.png" /></figure><h3>Optimization</h3><p>There main 3 ways to optimize the performance.</p><ol><li>OPTIMIZE function</li><li>VACUUM function</li><li><strong>Z-Ordering</strong></li></ol><p>The <strong>OPTIMIZE</strong> command compacts multiple small files into larger ones, improving performance by reducing the number of files that need to be scanned during queries. This significantly enhances read efficiency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lk4Hg6QdzCAuvi3YB9JfPQ.png" /></figure><p>The above command optimizes the entire table; however, you can also optimize a <strong>specific partition</strong> if needed. Refer to the image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9tNZAhy8qJkohYTyhEeCAg.png" /></figure><p><strong>Z-Ordering</strong> arranges the data based on the specified column(s), storing similar values close together. This improves read performance by minimizing the amount of data scanned during queries. Since then, it doesn’t need to scan all files in the filtering and other operations , it improves performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E_hd6204PjNc9Zl82Hvw8w.png" /></figure><p>VACUUME</p><p>This command permanently removes unused files after a specified retention period. By default, the retention period is 7 days, but users can adjust it according to their requirements. However, best practice is to set the retention period more than or 7 days</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yrl-L23bRSC2kt8sDn8EDQ.png" /></figure><p>After running the above query, it deleted the files older than 3 days.</p><p>However, if you want to reduce the retention period below 7 days, you must first disable the default safety check.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qL500LEdx7CJa0kkNjQBXw.png" /></figure><p>Asindu Hathnapitiya</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a99cfe799557" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Managed vs External Tables in Spark / Fabric / Synapse]]></title>
            <link>https://medium.com/@asindugayangana/managed-vs-external-tables-in-spark-fabric-synapse-f8c641e422d3?source=rss-1948dd376e1a------2</link>
            <guid isPermaLink="false">https://medium.com/p/f8c641e422d3</guid>
            <dc:creator><![CDATA[Asindu Gayangana]]></dc:creator>
            <pubDate>Thu, 04 Sep 2025 13:12:14 GMT</pubDate>
            <atom:updated>2025-09-04T13:14:19.756Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pKIXrBuxG--f1ReVfwpJmA.png" /></figure><h3>1. Introduction</h3><p>In Spark-based environments (Fabric, Synapse, Databricks), tables can be created in two main ways:</p><ul><li><strong>Managed Tables</strong></li><li><strong>External Tables</strong></li></ul><p>Understanding the difference between them is essential for managing data lifecycle, storage, and access correctly.</p><h3>2. External Tables</h3><h3>Definition</h3><ul><li>Store <strong>only metadata</strong> (schema, structure, data types) in the metastore.</li><li>Actual <strong>data files live in a user-specified location</strong> (e.g., ABFSS path, ADLS).</li></ul><h3>Behavior</h3><ul><li>If the table is <strong>dropped</strong>, the data remains intact in storage.</li><li>Any changes to the data folder (files added/removed/updated) are reflected automatically in the table.</li><li>Supports <strong>partitioned data</strong>, which is stored as subfolders as per existing data..</li><li>Multiple tables can point to the <strong>same location</strong>, avoiding data duplication.</li><li>Fabric does not allow to create external tables.</li></ul><h3>Use Cases</h3><ul><li>When you want to <strong>share the same data with multiple tables</strong>.</li><li>When you need to <strong>reference raw data directly</strong> without moving it.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*co1FcgDAbLG9iDd9YmNRHg.png" /></figure><h3>3. Managed Tables</h3><h3>Definition</h3><ul><li>Both <strong>metadata and data files</strong> are managed by Spark/Fabric.</li><li>Data is stored in the default <strong>location</strong>(Eg:- Fabric — Lakehouse).</li></ul><h3>Behavior</h3><ul><li>If the table is <strong>dropped</strong>, both metadata and underlying data are deleted.</li><li>No need to specify a storage path explicitly when creating a table.</li><li>Supports <strong>partitioned data</strong>, which is stored as subfolders as per existing data.</li></ul><h3>Use Cases</h3><ul><li>When you want Spark/Fabric to <strong>fully manage table lifecycle</strong>.</li><li>For <strong>curated, production-ready datasets</strong> inside a Lakehouse.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7IvOYsR2JIiadKFCFt8YAQ.png" /></figure><h3>Summary</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KktbqpLl0m-gfHg8seQdDQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f8c641e422d3" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>