<?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 David Blain on Medium]]></title>
        <description><![CDATA[Stories by David Blain on Medium]]></description>
        <link>https://medium.com/@dablomatique?source=rss-93d36a3d6be3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*ZtX3DvYDe7sBFgsC</url>
            <title>Stories by David Blain on Medium</title>
            <link>https://medium.com/@dablomatique?source=rss-93d36a3d6be3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 15:33:15 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dablomatique/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[Rethinking partial/expand usage in Apache Airflow]]></title>
            <link>https://medium.com/apache-airflow/rethinking-partial-expand-usage-in-apache-airflow-74362062ddf0?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/74362062ddf0</guid>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Wed, 15 Apr 2026 11:32:26 GMT</pubDate>
            <atom:updated>2026-08-26T12:19:08.503Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8W6yDjvuHGxm-pFvJiD7eQ.png" /></figure><p>In Apache Airflow, partial() and expand() (Dynamic Task Mapping) are powerful features for handling collections of work. In many scenarios, they are exactly the right tool and can significantly improve scalability and clarity.</p><p>However, there are cases where applying them can unintentionally introduce overhead — especially in data ingestion pipelines. Recognizing these situations can make a substantial difference in both performance and operational simplicity.</p><p>This post walks through a real-world example where a seemingly well-designed DAG led to unexpected performance issues, and how a small change in approach resulted in a significant improvement.</p><p>As part of this work, we also contributed back by improving the Airflow documentation to help others avoid running into the same pattern in the future.</p><h3>The example that looks perfectly fine</h3><p>Last week, I reviewed a merge request for a new DAG that, at first glance, looked very well written. It was clean, structured, and clearly showed that the author had put effort into using the right operators and keeping the code readable.</p><p>At the same time, the author had already noticed something wasn’t quite right. In the PR description, they mentioned performance issues but couldn’t pinpoint where they were coming from or how to improve them.</p><p>This is a situation many Airflow users will recognize: the code looks correct and idiomatic, yet the runtime behaviour tells a different story.</p><p>After taking a closer look, it became clear that the issue was not in what the DAG was doing, but <em>how</em> the work was being orchestrated.</p><p>The good news is that this was relatively easy to fix — and the improvement was significant enough that we took it a step further and updated the Airflow documentation for the upcoming release to make this pattern clearer for others.</p><p>Let’s take a closer look at the DAG:</p><pre>with DAG(<br>    dag_id=&quot;extract_employees&quot;,<br>    schedule=&quot;@daily&quot;,<br>    catchup=False,<br>):<br>    extract = HttpOperator(<br>        task_id=f&quot;extract_employees_data&quot;,<br>        method=&quot;GET&quot;,<br>        endpoint=endpoint,<br>        data=params,<br>        http_conn_id=&quot;http_conn_id&quot;,<br>        response_filter=lambda response: [<br>            employee.json().get(&quot;details&quot;) for employee in response<br>        ],<br>    )<br><br>    truncate_table = SQLExecuteQueryOperator(<br>        task_id=&quot;truncate_employees_table&quot;,<br>        conn_id=&quot;postgres_conn_id&quot;,<br>        sql=&quot;TRUNCATE HR.employees;&quot;,<br>    )<br><br>    load = SQLInsertRowsOperator.partial(<br>        task_id=&quot;load_employee_data&quot;,<br>        conn_id=&quot;postgres_conn_id&quot;,<br>        table_name=&quot;employees&quot;,<br>        schema=&quot;HR&quot;,<br>        insert_args={<br>            &quot;replace&quot;: True,<br>            &quot;executemany&quot;: True,<br>            &quot;commit_every&quot;: 1000,<br>            &quot;fast_executemany&quot;: True,<br>        },<br>    ).expand(rows=extract.output)<br><br>    generate_asset = EmptyOperator(<br>        task_id=&quot;generate_asset&quot;,<br>        outlets=[<br>            Asset(<br>                &quot;postgres://localhost:5432/db/HR/employees&quot;<br>            )<br>        ],<br>    )<br><br>    extract &gt;&gt; truncate_table &gt;&gt; load &gt;&gt; generate_asset</pre><p>At first glance, this looks elegant:</p><ul><li>The extract task fetches paginated API data</li><li>Each page becomes a mapped task</li><li>Each mapped task inserts a chunk into the database</li></ul><p>It <em>feels</em> scalable.</p><p>But this is where things go wrong.</p><h3>What’s actually happening under the hood</h3><p>To understand the performance impact, it’s important to look at what this DAG is really doing at runtime.</p><p>The HttpOperator returns a paginated response, which means the output is a list of pages. When expand(rows=extract.output) is applied, each of those pages is turned into a separate task instance. What looks like a single logical operation in the code becomes multiple independent executions in practice.</p><p>Each of these task instances behaves in isolation. It opens its own database connection, executes the insert logic for its subset of data, and commits independently. While this might seem like a good way to parallelize work, it comes at a cost.</p><p>Instead of a single task handling the full dataset efficiently, the scheduler now has to manage multiple task instances. This introduces additional overhead, both in terms of orchestration and resource usage. Multiple database connections are opened instead of one, the scheduler has to coordinate more tasks, and the metadata database sees increased load from tracking all these executions.</p><p>Most importantly, this approach removes the opportunity for the underlying database hook to optimize the workload. What could have been handled as a single, efficiently batched operation is now fragmented into many smaller, isolated pieces of work.</p><h3>The hidden inefficiency: you lost the batching layer</h3><p>The SQLInsertRowsOperator ultimately relies on DbApiHook.insert_rows(), which is already optimized to:</p><ul><li>Consume an iterable of rows</li><li>Batch data internally via commit_every</li><li>Use efficient bulk mechanisms (executemany / fast_executemany)</li><li>Reuse a single connection per task execution</li></ul><p>By mapping the operator, you effectively bypass this design.</p><p>Instead of the intended flow:</p><blockquote><em>1 operator → 1 connection → internal batching → efficient bulk insert</em></blockquote><p>You now get:</p><blockquote><em>N mapped tasks → N connections → N independent bulk insertions</em></blockquote><p>Even if each task handles only a small dataset, the overhead multiplies quickly: connection setup, transaction handling, and per-task orchestration costs all accumulate, often dominating the actual insert time.</p><h3>The better approach: flatten and stay lazy</h3><p>In this case, the extract output is:</p><pre>list[list[rows]]  # paginated API response</pre><p>Instead of mapping over pages, you can simply flatten it. Very important here is to not materialize the list of rows.</p><p>So this translates to the following with SQLInsertRowsOperator:</p><pre>from more_itertools import flatten<br><br>load = SQLInsertRowsOperator(<br>    task_id=&quot;load_employee_data&quot;,<br>    conn_id=&quot;postgres_conn_id&quot;,<br>    table_name=&quot;employees&quot;,<br>    schema=&quot;HR&quot;,<br>    rows_processor=lambda rows, **context: flatten(rows),<br>    insert_args={<br>        &quot;replace&quot;: True,<br>        &quot;executemany&quot;: True,<br>        &quot;commit_every&quot;: 1000,<br>        &quot;fast_executemany&quot;: True,<br>    },<br>    preoperator=&quot;TRUNCATE HR.employees;&quot;,<br>    outlets=[<br>        Asset(<br>            &quot;postgres://localhost:5432/db/HR/employees&quot;<br>        )<br>    ],<br>    rows=extract.output,<br>)</pre><p>Now you get:</p><ul><li>2 tasks per Dag run instead of 3 + N mapped tasks</li><li>1 DB connection instead of N</li><li>Internal batching still preserved</li><li>Scheduler load drastically reduced</li></ul><h3>Why this is faster</h3><p>The key insight is this:</p><blockquote><em>Airflow should orchestrate work, not execute fine-grained iteration.</em></blockquote><p>When you pass a full dataset:</p><ul><li>The hook can optimize execution (batching, executemany, commit grouping)</li><li>The DB driver can optimize network usage</li><li>The operator controls the lifecycle of a single execution</li></ul><p>When you use expand():</p><ul><li>You force Airflow into being the iterator</li><li>You lose control over batching strategy</li><li>You shift overhead from the database layer to the scheduler layer</li></ul><h3>Moving the truncate logic</h3><p>Once you remove mapping, you also unlock simplification.</p><p>Instead of:</p><pre>truncate_table &gt;&gt; load &gt;&gt; generate_asset</pre><p>You can move truncation and asset generation into the operator itself as one atomic feature:</p><pre>preoperator=&quot;TRUNCATE HR.employees;&quot;,<br>outlets=[<br>    Asset(<br>        &quot;postgres://localhost:5432/db/HR/employees&quot;<br>    )<br>],</pre><p>And suddenly:</p><ul><li>No extra dependency chain</li><li>No need for an EmptyOperator</li><li>One coherent unit of work</li></ul><h3>The final DAG shape</h3><p>After refactoring, the DAG becomes:</p><ul><li>Extract (API pagination)</li><li>Truncate, load (flattened insert with batching) and emit asset</li></ul><p>From:</p><pre>3 + N tasks per DAG run</pre><p>to:</p><pre>2 tasks per DAG run</pre><p>That alone is a massive operational win, but also the code is much more elegant and easier to understand in one glance.</p><h3>When you SHOULD use partial/expand</h3><p>This is not an argument against Dynamic Task Mapping, on the contrary, but you should only use it when it matters:</p><ul><li>Each item requires <strong>different logic</strong></li><li>Each task is <strong>logically independent</strong></li><li>You need <strong>parallel per-item computation</strong></li><li>You cannot batch safely</li></ul><p>Examples:</p><ul><li>Per-file transformations</li><li>Per-customer processing</li><li>Per-partition ETL with different logic</li><li>Independent API calls per entity</li></ul><h3>The core lesson</h3><blockquote><em>partial() and </em><em>expand() are not a performance feature. They are a control-flow feature.</em></blockquote><p>In cases like bulk database inserts:</p><ul><li>You don’t want more task instances</li><li>You don’t want more connections</li><li>You don’t want Airflow to be the iterator</li></ul><p>You want:</p><ul><li>fewer tasks</li><li>fewer connections</li><li>smarter batching inside the operator</li></ul><h3>Conclusion</h3><p>Dynamic Task Mapping in Apache Airflow is powerful, but it can easily be misapplied.</p><p>In ingestion-heavy pipelines, especially database loads, it often:</p><ul><li>increases overhead instead of reducing it</li><li>shifts optimization away from the database layer</li><li>introduces unnecessary scheduler pressure</li></ul><p>If your operator is doing the same thing for every item — like inserting rows — then mapping is usually a sign you’re solving the wrong problem at the wrong layer.</p><p>Sometimes, the fastest DAG is the one that does <strong>less orchestration, not more</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=74362062ddf0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/rethinking-partial-expand-usage-in-apache-airflow-74362062ddf0">Rethinking partial/expand usage in Apache Airflow</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Scaling with Airflow 3.2: When to Defer and When to Use Native Async Operators]]></title>
            <link>https://medium.com/apache-airflow/scaling-with-airflow-3-2-when-to-defer-and-when-to-use-native-async-operators-78a1ba35237d?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/78a1ba35237d</guid>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Tue, 03 Mar 2026 16:15:04 GMT</pubDate>
            <atom:updated>2026-08-17T15:15:15.315Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JX5PAtZQ9R2WF1GJVN-pIQ.jpeg" /></figure><p>In <a href="https://airflow.apache.org/">Apache Airflow</a>, we have long favoured dedicated operators over PythonOperators to improve ease of integration. For years, <strong>Deferrable Operators</strong> were the gold standard to offload async waiting to a triggerer.</p><p>However, as we scaled our DAGs in <a href="https://airflow.apache.org/">Apache Airflow</a> for heavy I/O workloads such as massive REST API integrations or thousands of SFTP transfers, we found that the <strong>triggerer</strong> became a bottleneck.</p><h3>Todays Limitations in I/O heavy or high transaction Async Tasks</h3><p>While deferrable operators are perfect for lightweight polling, they have significant architectural limitations for high-throughput data processing:</p><ul><li><strong>Database Pressure:</strong> Triggerers must store yielded events directly in the Airflow metadata database. Unlike workers, triggers cannot leverage a custom XCom backend (like S3 or GCS) to offload large payloads.</li><li><strong>Context Switching:</strong> Every deferral involves a roundtrip: Worker → Scheduler → Triggerer → Scheduler → Worker. This orchestration overhead is negligible for a 1-hour wait, but expensive for 10,000 small files. This will be less of an issue once we have start_from_trigger enabled again in Airflow 3 (e.g. PR <a href="https://github.com/apache/airflow/pull/55068">#55068</a>). Currently triggers also can only yield one event at a time, something that will be solved once <a href="https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=334760511">AIP-88</a> is implemented.</li><li><strong>Rigid Implementation:</strong> Not every operator has a deferrable counterpart (e.g. SFTP). This often forced us back to PythonOperators which, until now, didn&#39;t natively support async code.</li></ul><h3>The Solution: Native Async in PythonOperator &amp; @task</h3><p><a href="https://airflow.apache.org/">Apache Airflow</a> 3.2 introduces the ability for the PythonOperator to natively detect and execute async callables (e.g. <a href="https://cwiki.apache.org/confluence/display/AIRFLOW/AIP-98%3A+Add+async+support+for+PythonOperator+in+Airflow+3">AIP-98</a>). This allows us to use <strong>Async Hooks</strong> directly within a worker process, bypassing the triggerer entirely.</p><h4>Concrete Example: Async SFTP Downloads</h4><p>We benchmarked the download of ~17,000 XML files from a SFTP server. By using a single Celery worker orchestrating many concurrent downloads via an SFTPClientPool, the results were transformative:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/95e87a537f770764e21345ca80df4d28/href">https://medium.com/media/95e87a537f770764e21345ca80df4d28/href</a></iframe><p><strong>The DagRun time dropped from over 3 hours to just 3 and a half minutes.</strong></p><pre>from airflow.sdk import task<br><br>@task<br>async def retrieve_files(files: list[str]):<br>    import asyncio<br>    from os import cpu_count<br>    from airflow.providers.sftp.hooks.sftp import SFTPClientPool<br><br>    async with SFTPClientPool(<br>        sftp_conn_id=&quot;ssh_conn_id&quot;, pool_size=cpu_count()<br>    ) as pool:<br>        async def read_file(file_name):<br>            async with pool.get_sftp_client() as sftp:<br>                async with sftp.open(file_name, encoding=&quot;utf-8&quot;) as remote_file:<br>                    return await remote_file.read()<br>    <br>        results = await asyncio.gather(<br>            *(read_file(file) for file in files)<br>        )<br>    <br>        return results</pre><h3>Why Deferrable + Mapping Fails at Scale</h3><p>You might think: <em>“If I use a Deferrable Operator with Dynamic Task Mapping, shouldn’t the triggerer handle the concurrency?”</em> In theory, yes. In practice, using <strong>Dynamic Task Mapping (DTM)</strong> with deferrable operators for 17,000 files actually outweighs the benefits of the trigger mechanism. Here’s why:</p><ol><li><strong>The Task Instance Scheduling Tax:</strong> DTM creates 17,000 individual Task Instances. Each one must be scheduled, queued, and heart-beated. The scheduler overhead alone becomes larger than the actual download time if the files are small and short to process.</li><li><strong>Orchestration Round Trips:</strong> Each of those 17,000 tasks must perform the “Deferral Steps”: Worker → DB → Triggerer → DB → Scheduler → Worker. When you multiply this roundtrip by 17,000, you might DDOS-ing your own metadata database.</li><li><strong>Connection Exhaustion:</strong> In a mapped setup, each task is a separate process. You cannot easily share a single TCP connection pool across thousands of independent processes. Each task has to negotiate its own handshake, which is incredibly slow for SFTP as it puts a lot of load on the backend systems as well. That’s also why we introduced a new SFTPClientPool.</li><li><strong>The Triggerer’s Hidden Burden:</strong> Triggers were designed for “watching”, not “moving”. Because triggers cannot use custom XCom backends, every piece of data they return (like file contents or JSON REST API responses) must be serialized directly into the trigger table in your metadata database. At 17,000 rows, this causes massive database overhead and slow-downs.</li></ol><h3>Why Async? (And When It Doesn’t Matter)</h3><p>It is crucial to clarify: <strong>Native async does not make a single execution faster.</strong> If you are only running one task, the worker remains occupied regardless of whether the code is sync or async. The real advantage emerges in <strong>concurrency</strong>.</p><p>When you iterate inside an async task (or use the upcoming <strong>Dynamic Task Iteration </strong>feature), you share a single event loop. The worker isn’t just idling; it is multiplexing I/O operations continuously. No context switching, no triggerer overhead, no metadata database bloat but also no waiting times.</p><h3>The Power of Dynamic Task Iteration</h3><p>While <strong>Dynamic Task Mapping</strong> creates individual Task Instances (heavy for the scheduler), <strong>Dynamic Task Iteration</strong> iterates over an XCom within the same worker.</p><p>When combined with the new async feature:</p><ol><li><strong>Shared Event Loop:</strong> You get the performance of a triggerer but within the worker’s execution context.</li><li><strong>XCom Flexibility:</strong> You can finally use custom XCom backends for large async payloads, which was a major pain point with triggers.</li><li><strong>Pseudo-Async Operators:</strong> Even if an deferrable operator raises TaskDeferred, the iteration logic will catch it and run it asynchronously inside the worker, avoiding the scheduler/triggerer roundtrip entirely but still using the deferred operators logic without the need to re-implement it (DRY).</li></ol><pre>from os import cpu_count<br>from airflow.sdk import task<br><br>@task<br>async def retrieve_file(file_name: str):<br>    from airflow.providers.sftp.hooks.sftp import SFTPClientPool<br><br>    async with SFTPClientPool(sftp_conn_id=&quot;ssh_conn_id&quot;, pool_size=cpu_count()).get_sftp_client() as sftp:<br>        async with sftp.open(file_name, encoding=&quot;utf-8&quot;) as remote_file:<br>            return await remote_file.read()<br><br>retrieve_file.iterate(file_name=files)</pre><h3>The Next Frontier: Dynamic Task Mapping vs. Dynamic Task Iteration</h3><p>To understand why native async in <a href="https://airflow.apache.org/">Apache Airflow</a> 3.2 is so fast, we have to look at how Airflow handles “loops.” In the past, we only had one way to handle dynamic workloads: <strong>Dynamic Task Mapping.</strong> Now, we have a higher-performance alternative.</p><h3>1. Dynamic Task Mapping</h3><p>Introduced in <a href="https://airflow.apache.org/">Apache Airflow</a> 2.3, <strong>Dynamic Task Mapping (DTM)</strong> allows you to create separate Task Instances at runtime based on the output of an upstream task.</p><ul><li><strong>How it works:</strong> If an upstream task returns 1,000 IDs, Airflow creates 1,000 individual Task Instances.</li><li><strong>The Cost:</strong> Each instance is a separate row in the database, a separate process on a worker, and a separate heart-beat for the scheduler.</li><li><strong>The Benefit:</strong> You get perfect visibility in the UI. You can see exactly which of the 1,000 tasks failed and retry only that one even if the worker would have died in the meantime.</li></ul><h3>2. Dynamic Task Iteration</h3><p><strong>Dynamic Task Iteration (DTI)</strong>, which will be introduced in <a href="https://airflow.apache.org/">Apache Airflow</a> 3.3, takes a different approach. Instead of spawning new Task Instances, it <strong>iterates over the data within the same worker/executor.</strong></p><ul><li><strong>How it works:</strong> It uses partial/iterate instead of partial/expand.</li><li><strong>The Cost:</strong> You lose some “micro-visibility” in the UI. You see one task running, not 1,000 separate boxes, with separate retry per item but stateless. This means that if the worker dies or crashes during execution of the task, it has to be done all over again as the task instance won’t remember which iteration succeeded or failed.</li><li><strong>The Benefit:</strong> <strong>Massive performance.</strong> Because it stays within the same process, there is zero scheduler overhead to start the next item. It leverages multi-threading (for sync code) or a single event loop (for async code).</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b74785f1994deacf69b2a64e496f5730/href">https://medium.com/media/b74785f1994deacf69b2a64e496f5730/href</a></iframe><h3>Why they are “Better Together”</h3><p>In the old model (DTM + Deferrable), to download 17,000 files, the scheduler had to manage 17,000 tasks. Even if they were deferrable, the sheer volume of “context switching” between the Worker, Scheduler, and Triggerer created a massive drag.</p><p>In the new model (<strong>Native Async + DTI</strong>):</p><ol><li><strong>Shared Event Loop:</strong> All 17,000 iterations run in <strong>one</strong> worker slot using <strong>one</strong> event loop.</li><li><strong>Continuous Execution:</strong> As soon as one file download finishes its I/O wait, the loop immediately starts the next one. There is no “gap” while waiting for the scheduler to pick up the next mapped task.</li><li><strong>Connection Pooling:</strong> Because everything is in one process, you can use an <strong>Async Connection Pool (</strong>e.g.<strong> </strong>SFTPClientPool). You can’t share a TCP connection pool across 17,000 different mapped processes, but you <em>can</em> share it across 17,000 async coroutines in one process.</li></ol><blockquote><strong><em>The Result:</em></strong><em> You get “Triggerer-style” efficiency (not blocking threads) with “Worker-style” speed (no network/DB roundtrips to the scheduler).</em></blockquote><h3>Which one should you choose?</h3><p>When should I choose to use Dynamic Task Iteration or Dynamic Task Mapping?</p><ul><li><strong>Choose Dynamic Task Mapping + Deferred </strong>for “heavy” units of work (e.g. like a <a href="https://spark.apache.org/">Apache Spark</a> or a <a href="https://www.microsoft.com/en-us/microsoft-fabric">Microsoft Fabric</a> job). This is ideal when tasks are delegated to external systems, requiring individual retries and high UI visibility during long waiting times without actually consuming Airflow worker slots.</li><li><strong>Choose Dynamic Task Iteration + Async </strong>for high-frequency “micro-batches” of I/O, such as REST API calls or SFTP transfers. This approach eliminates the scheduling overhead that often outweighs the work itself. Unlike Deferrable Operators, this approach holds an Airflow worker slot for the duration of the batch. It is highly efficient for high-throughput tasks, provided you have the worker capacity and can accept batch-level retries.</li></ul><h3>Combining Dynamic Task Mapping with Iteration</h3><p>It is a mistake to view Dynamic Task Mapping and Dynamic Task Iteration as an “either/or” choice. In high-performance pipelines, the most efficient architecture is actually to use <strong>both together</strong>.</p><h4>Partition and Conquer</h4><p>When dealing with truly massive datasets — say, processing 1 million files — a single worker process using Iteration might eventually hit resource limits (CPU or memory). This is where <strong>Dynamic Task Mapping</strong> becomes the perfect “orchestrator” for <strong>Dynamic Task Iteration</strong>.</p><ol><li><strong>Phase 1: Mapping (The Partition):</strong> Use Dynamic Task Mapping to split your 1 million items into 100 chunks (partitions) of 10,000.</li><li><strong>Phase 2: Iteration (The Execution):</strong> Each of those 100 mapped tasks is sent to a different Celery worker.</li><li><strong>Phase 3: Async Concurrency:</strong> Inside each worker, <strong>Dynamic Task Iteration</strong> takes that chunk of 10,000 and processes them concurrently (multi-threaded or async) within a single event loop.</li></ol><h4>Why this is the “Gold Standard” Architecture:</h4><ul><li><strong>Horizontal Scalability:</strong> You still benefit from the ability to distribute load across a cluster of workers.</li><li><strong>Vertical Efficiency:</strong> You eliminate the “Task Instance explosion” by a factor of 100,000. Instead of 1 million metadata rows, the scheduler only sees 10.</li><li><strong>Optimal Resource Usage:</strong> You keep the worker busy with high-concurrency I/O (via Async/DTI) while maintaining the UI visibility and retry-ability of the larger partitions (via DTM).</li></ul><p>This “Map-to-Partition” and “Iterate-to-Execute” pattern represents the most sophisticated way to build data pipelines in <a href="https://airflow.apache.org/">Apache Airflow</a> 3. It allows for massive throughput without the “metadata tax” that previously slowed down high-volume DAGs.</p><h3>Conclusion: “Async Where it Matters”</h3><p>This feature isn’t meant to replace deferrable operators — triggers are still the best tool for long-running “dead time” where you want to free up worker slots.</p><p>Instead, <a href="https://airflow.apache.org/">Apache Airflow</a> 3.2 gives us a third execution option: <strong>Micro-batching via Native Async.</strong> This hybrid approach allows us to use Hooks as the true building blocks of high-performance workflows, reaching a level of throughput that standard orchestration simply couldn’t touch.</p><p>This is the “missing piece” of the puzzle that makes the new async features truly revolutionary. While <strong>Dynamic Task Mapping (DTM)</strong> was the star of <a href="https://airflow.apache.org/">Apache Airflow</a> 2, while the upcoming <strong>Task Iteration </strong>or <strong>Iterable Tasks (IT)</strong> is the complementary engine of <a href="https://airflow.apache.org/">Apache Airflow</a> 3.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=78a1ba35237d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/scaling-with-airflow-3-2-when-to-defer-and-when-to-use-native-async-operators-78a1ba35237d">Scaling with Airflow 3.2: When to Defer and When to Use Native Async Operators</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using the MS Graph Operator to trigger and monitor an MS Fabric pipeline in Apache Airflow]]></title>
            <link>https://medium.com/apache-airflow/using-the-ms-graph-operator-to-trigger-and-monitor-an-ms-fabric-pipeline-in-apache-airflow-08d299577002?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/08d299577002</guid>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Tue, 25 Feb 2025 14:36:22 GMT</pubDate>
            <atom:updated>2025-02-27T09:17:25.445Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*USW5q4QXQ5mLHryB" /></figure><p><a href="https://learn.microsoft.com/en-us/rest/api/fabric/articles/using-fabric-apis">Microsoft Fabric</a> provides a unified analytics platform for data engineering, data science, real-time analytics, and business intelligence. When using <a href="https://airflow.apache.org/">Apache Airflow</a> to orchestrate Fabric pipelines, the <a href="https://airflow.apache.org/docs/apache-airflow-providers-microsoft-azure/stable/operators/msgraph.html#msgraphasyncoperator"><strong>MSGraphAsyncOperator</strong></a> and <strong>MSGraphSensor</strong> can be used to trigger and monitor pipeline execution.</p><p>This article explains how to set up a dynamic Airflow DAG that triggers an MS Fabric pipeline using the <a href="https://airflow.apache.org/docs/apache-airflow-providers-microsoft-azure/stable/operators/msgraph.html#msgraphasyncoperator"><strong>MSGraphAsyncOperator</strong></a> and then monitors its execution using the <strong>MSGraphSensor</strong>.</p><h3>Prerequisites</h3><p>Before using the operators, ensure the following requirements are met:</p><ul><li>A Microsoft Fabric workspace with an active pipeline.</li><li>A registered Azure AD application with necessary permissions to trigger and monitor pipelines.</li><li>An Airflow connection (mgraph:fabric) configured to authenticate with Microsoft Graph API.</li></ul><figure><img alt="Airflow MS Graph Connection" src="https://cdn-images-1.medium.com/max/1024/1*LjCb2LFCmy0QRf8mjnyqug.png" /></figure><blockquote>Make sure when configuring the Airflow connection that the host is set to &#39;api.fabric.microsoft.com&#39; and the api_version is set to &#39;v1&#39;, as it&#39;s different than when using the MS Graph API or PowerBI REST endpoints.</blockquote><h3>DAG Implementation</h3><p>Below is an Airflow DAG that triggers an MS Fabric pipeline and monitors its execution:</p><pre>&quot;&quot;&quot;<br>Triggers Fabric {name} pipeline<br>&quot;&quot;&quot;<br>from datetime import datetime<br><br>from airflow import DAG<br>from airflow.exceptions import AirflowException<br>from airflow.providers.microsoft.azure.operators.msgraph import MSGraphAsyncOperator<br>from airflow.providers.microsoft.azure.sensors.msgraph import MSGraphSensor<br><br>def fabric_pipeline_trigger(<br>    name: str,<br>    workspace_id: str,<br>    pipeline_id: str,<br>    schedule_interval: str = &quot;@daily&quot;,<br>    retry_delay: int = 60,<br>):<br>    with DAG(<br>        dag_id=f&quot;trigger_fabric_{name.lower()}&quot;,<br>        description=__doc__.format(name=name).partition(&quot;.&quot;)[0],<br>        doc_md=__doc__.format(name=name),<br>        start_date=datetime(2025, 2, 25),<br>        schedule_interval=schedule_interval,<br>        max_active_runs=1,<br>    ) as dag:<br>        def extract_location(response, **context):<br>            return response[&quot;location&quot;]<br><br>        def event_processor(event: dict, **context) -&gt; bool:<br>            if event.get(&quot;failureReason&quot;):<br>                raise AirflowException(event.get(&quot;failureReason&quot;))<br>            return event.get(&quot;status&quot;) == &quot;Completed&quot;<br><br>        schedule_pipeline = MSGraphAsyncOperator(<br>            task_id=&quot;schedule_pipeline&quot;,<br>            conn_id=&quot;mgraph:fabric&quot;,<br>            method=&quot;POST&quot;,<br>            url=&quot;workspaces/{workspaceId}/items/{pipelineId}/jobs/instances?jobType=Pipeline&quot;,<br>            path_parameters={&quot;workspaceId&quot;: workspace_id, &quot;pipelineId&quot;: pipeline_id},<br>            result_processor=extract_location,<br>            dag=dag,<br>        )<br><br>        monitor_pipeline = MSGraphSensor(<br>            task_id=&quot;monitor_pipeline&quot;,<br>            conn_id=&quot;mgraph:fabric&quot;,<br>            url=schedule_pipeline.output,<br>            event_processor=event_processor,<br>            retry_delay=retry_delay,<br>            dag=dag,<br>        )<br><br>        schedule_pipeline &gt;&gt; monitor_pipeline<br><br>fabric_pipeline_trigger(<br>    name=&quot;HelloWorld&quot;,<br>    workspace_id=&quot;840c7416-f038-4c7f-a0c9-b50780e3f6e9&quot;,<br>    pipeline_id=&quot;7a0f5949-9a04-4252-a308-6e25ae791ee7&quot;,<br>    retry_delay=10,  # Consider the pipeline&#39;s execution time when setting this value.<br>)</pre><h3>Explanation of the DAG</h3><p><strong>Triggering the Pipeline</strong>:</p><ul><li>The MSGraphAsyncOperator sends a POST request to trigger the pipeline, passing the workspaceId and pipelineId as path parameters from the dynamic DAG parameters.</li><li>The extract_location function retrieves the location of the pipeline execution URL from the response, which will be used in the second operator to monitor pipeline execution.</li></ul><p><strong>Monitoring the Pipeline Execution</strong>:</p><ul><li>The MSGraphSensor periodically checks the pipeline&#39;s status at the defined retry_delay interval.</li><li>The event_processor function inspects the response:</li><li>If a failure reason is found, the task raises an AirflowException.</li><li>If the status is Completed, the task succeeds.</li></ul><p><strong>DAG Parameters</strong>:</p><ul><li>The schedule_interval determines how often the DAG runs.</li><li>The retry_delay defines the interval between monitoring attempts.</li></ul><h3>Considerations</h3><ul><li>Adjust retry_delay based on the expected pipeline execution time.</li><li>Ensure the Azure AD application has permissions to trigger and monitor the MS Fabric pipeline.</li><li>Verify the mgraph:fabric Airflow connection is correctly configured, taking into account the hostand api_version settings.</li></ul><h3>Conclusion</h3><p>By combining <a href="https://airflow.apache.org/docs/apache-airflow-providers-microsoft-azure/stable/operators/msgraph.html#msgraphasyncoperator">MSGraphAsyncOperator</a> and MSGraphSensor, we can efficiently trigger and monitor Microsoft Fabric pipelines in Airflow. This approach provides an automated and reliable way to integrate Fabric workflows into data pipelines.</p><p>If you have any questions or encounter issues, feel free to comment below!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=08d299577002" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/using-the-ms-graph-operator-to-trigger-and-monitor-an-ms-fabric-pipeline-in-apache-airflow-08d299577002">Using the MS Graph Operator to trigger and monitor an MS Fabric pipeline in Apache Airflow</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to load CSV Files in Airflow using the GenericTransfer with DuckDB]]></title>
            <link>https://medium.com/apache-airflow/how-to-load-csv-files-in-airflow-using-the-generictransfer-with-duckdb-7bd4a8eadd2b?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/7bd4a8eadd2b</guid>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Fri, 07 Feb 2025 15:32:48 GMT</pubDate>
            <atom:updated>2025-02-19T11:10:11.412Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iPF_70zBWvdhV_G5Ft41Wg.png" /></figure><h3>Overview</h3><p>This DAG is designed to fetch CSV-files from an SFTP server and load them into an SQL database. The DAG minimizes custom Python code and relies almost entirely on Airflow’s built-in operators, except for listing directories via an SFTPHook (since the SFTP operator lacks this capability).</p><h3>Key Benefits of This Approach</h3><ul><li><strong>Operator-Driven Implementation</strong>: Reduces maintenance and debugging efforts by leveraging Airflow’s built-in operators instead of custom Python scripts.</li><li><strong>Efficient File Processing</strong>: Uses SQL queries to track and filter which files have already been processed.</li><li><strong>Scalable Data Ingestion</strong>: Uses GenericTransfer to load CSV data into MSSQL without custom transformation logic.</li><li><strong>Iterative Execution</strong>: Dynamically processes files without hardcoded loops, making it more scalable.</li><li><strong>XComs for Data Passing</strong>: Efficiently transfers data between tasks using XComs, reducing dependencies on external storage.</li></ul><h3>DAG Workflow Breakdown</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sExckCpziV1rozWoILbgdQ.png" /></figure><h3>1. Identify Already Loaded Files</h3><pre>loaded_files = SQLExecuteQueryOperator(<br>    task_id=&quot;loaded_files&quot;,<br>    conn_id=MSSQL_CONN_ID,<br>    sql=&quot;SELECT file_name FROM BRONZE.loaded_files WHERE file_name LIKE &#39;%.txt&#39;&quot;,<br>    show_return_value_in_logs=True,<br>    dag=dag,<br>)</pre><ul><li>Retrieves the list of previously processed files from the database.</li><li>Helps prevent duplicate processing.</li></ul><h3>2. List Files in the SFTP Directory</h3><pre>@task<br>def list_files(path):<br>    from airflow.providers.sftp.hooks.sftp import SFTPHook<br><br>    return set(<br>        filter(<br>            lambda file: file.endswith(&quot;.txt&quot;),<br>            SFTPHook.get_hook(SFTP_CONN_ID).list_directory(path),<br>        )<br>    )</pre><ul><li>Uses SFTPHook to list available files on the SFTP server and only return txt-files.</li><li>This step requires custom Python since SFTPOperator doesn’t support directory listing.</li></ul><h3>3. Determine New Files to Process</h3><pre>@task<br>def files_to_retrieve(loaded_filenames, filenames):<br>    loaded_filenames = set(<br>        list(map(lambda loaded_file: loaded_file[0], loaded_filenames))<br>    )<br>    return list(filenames.difference(loaded_filenames))</pre><ul><li>Compares available files with already processed ones.</li><li>Only returns filenames that haven’t been loaded yet into the database.</li></ul><h3>4. Retrieve New Files from SFTP</h3><pre>retrieved_files = SFTPOperator.partial(<br>    task_id=&quot;retrieved_files&quot;,<br>    ssh_conn_id=SFTP_CONN_ID,<br>    operation=&quot;get&quot;,<br>    create_intermediate_dirs=True,<br>    retries=5,<br>    retry_exponential_backoff=True,<br>    retry_delay=timedelta(minutes=1),<br>    dag=dag,<br>).expand_kwargs(<br>    remote_files.map(<br>        lambda file: {<br>            &quot;remote_filepath&quot;: f&quot;{PATH}/{file}&quot;,<br>            &quot;local_filepath&quot;: f&quot;xcoms/{{{{ dag.dag_id }}}}__{{{{ run_id }}}}/{file}&quot;,<br>        }<br>    )<br>)</pre><ul><li>Uses SFTPOperator to download only the new files from SFTP.</li><li>Make sure intermediate directories are being created on the local filesystem when necessary.</li><li>Processes each file iteratively rather than in a single batch.</li><li>Use some jinja magic to append dag id and dag run id on each local filepath.</li></ul><h3>5. Load Retrieved Files into MSSQL</h3><pre>load_retrieved_files_to_db = GenericTransfer.partial(<br>    task_id=&quot;load_retrieved_files_to_db&quot;,<br>    source_conn_id=&quot;duckdb_duckdb_memory&quot;,<br>    destination_conn_id=MSSQL_CONN_ID,<br>    destination_table=&quot;BRONZE.log_files&quot;,<br>    insert_args={<br>        &quot;target_fields&quot;: [<br>            &quot;event_id&quot;,<br>            &quot;event_time&quot;,<br>            &quot;event&quot;,<br>            &quot;result&quot;,<br>            &quot;info&quot;,<br>            &quot;user_id&quot;,<br>            &quot;origin&quot;,<br>            &quot;destination&quot;,<br>            &quot;session_id&quot;,<br>        ],<br>        &quot;commit_every&quot;: 5000,<br>        &quot;executemany&quot;: True,<br>        &quot;fast_executemany&quot;: True,<br>    },<br>).expand(<br>    sql=retrieved_files.output.map(<br>       lambda file: f&quot;&quot;&quot;<br>       SELECT *<br>       FROM read_csv(<br>           &#39;{file}&#39;,<br>           delim = &#39;;&#39;,<br>           header = false,<br>           columns = {{<br>               &#39;event_id&#39;: &#39;INTEGER&#39;,<br>               &#39;event_time&#39;: &#39;TIMESTAMP&#39;,<br>               &#39;event&#39;: &#39;VARCHAR&#39;,<br>               &#39;result&#39;: &#39;VARCHAR&#39;,<br>               &#39;info&#39;: &#39;VARCHAR&#39;,<br>               &#39;user_id&#39;: &#39;VARCHAR&#39;,<br>               &#39;origin&#39;: &#39;VARCHAR&#39;,<br>               &#39;destination&#39;: &#39;VARCHAR&#39;,<br>               &#39;session_id&#39;: &#39;INTEGER&#39;<br>           }}<br>       );<br>    &quot;&quot;&quot;<br>    )<br>)</pre><ul><li>Uses GenericTransfer to directly load CSV data from an in-memory <a href="https://duckdb.org/docs/data/csv/overview">DuckDB </a>into MSSQL.</li><li>Avoids writing custom Python transformation function nor using <a href="https://pandas.pydata.org/">pandas </a>but doing it purely in SQL.</li><li>Be aware that the read_csv method of DuckDB will fail if a CSV-file would be empty, so best to filter those out in a preceding task if that could be the case.</li></ul><h3>6. Record Processed Files</h3><pre>record_file_as_loaded = SQLInsertRowsOperator(<br>    task_id=&quot;record_file_as_loaded&quot;,<br>    conn_id=MSSQL_CONN_ID,<br>    schema=&quot;BRONZE&quot;,<br>    table_name=&quot;loaded_files&quot;,<br>    columns=[&quot;file_name&quot;, &quot;loaded_at&quot;],<br>    rows=retrieved_files.output,<br>    rows_processor=lambda context, rows: map(<br>        lambda row: (row, context[&quot;execution_date&quot;].strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)),<br>        as_flattened_list(rows),<br>    ),<br>    insert_args={<br>        &quot;commit_every&quot;: 5000,<br>        &quot;autocommit&quot;: False,<br>        &quot;executemany&quot;: True,<br>        &quot;fast_executemany&quot;: True,<br>    },<br>)</pre><ul><li>Uses SQLInsertRowsOperator to track successfully processed files in MSSQL.</li><li>Append DAG Run execution date as load date per inserted filename into the table for partitioning.</li><li>Ensures files aren’t processed multiple times.</li></ul><h3>Why This DAG is Efficient</h3><h3>✅ Minimal Custom Python Code</h3><ul><li>Only list_files and files_to_retrieve tasks require Python code.</li><li>Everything else relies on standard Airflow operators, reducing maintenance effort.</li></ul><h3>✅ Scalable &amp; Dynamic File Handling</h3><ul><li>Uses expand_kwargs and expand, ensuring files are processed dynamically.</li><li>No static lists or hardcoded loops but make use of the dynamic task mapping feature in Airlow.</li></ul><h3>✅ Optimized Database Inserts</h3><ul><li>Uses executemany and fast_executemany for batch inserts, improving performance.</li></ul><h3>✅ XCom-based File Handling</h3><ul><li>Intermediate results are passed using XComs rather than writing/reading from disk.</li></ul><h3>✅ Resilient and Maintainable</h3><ul><li>Relies on SQLExecuteQueryOperator, GenericTransfer, and SQLInsertRowsOperator, which are well-tested components.</li><li>Can be adapted to new file formats or schemas with minimal code changes.</li></ul><h3>Summary</h3><p>This DAG demonstrates a best-practice approach for ingesting CSV files from an SFTP server into an MSSQL database. By leveraging Airflow’s built-in operators, the workflow remains scalable, maintainable, and performant. The only required Python code is for listing directory contents and filtering files, ensuring minimal custom logic and maximizing the benefits of Airflow’s native capabilities.</p><p>Below the complete DAG code:</p><pre>from airflow import DAG<br>from airflow.decorators import task<br>from airflow.operators.generic_transfer import GenericTransfer<br>from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator<br>from airflow.providers.sftp.operators.sftp import SFTPOperator<br>from airflow.utils.helpers import as_flattened_list<br><br>from common.sql.sql import SQLInsertRowsOperator<br><br>PATH = &quot;/var/opt/logs&quot;<br>SFTP_CONN_ID = &quot;sftp&quot;<br>MSSQL_CONN_ID = &quot;mssql&quot;<br><br>with DAG(<br>    dag_id=&quot;load-csv-data-example&quot;,<br>    start_date=datetime(2025, 2, 5),<br>    schedule_interval=&quot;@daily&quot;,<br>    max_active_runs=1,<br>) as dag:<br>    loaded_files = SQLExecuteQueryOperator(<br>        task_id=&quot;loaded_files&quot;,<br>        conn_id=MSSQL_CONN_ID,<br>        sql=&quot;SELECT file_name FROM BRONZE.loaded_files WHERE file_name LIKE &#39;%.txt&#39;&quot;,<br>        show_return_value_in_logs=True,<br>        dag=dag,<br>    )<br><br>    @task<br>    def list_files(path):<br>        from airflow.providers.sftp.hooks.sftp import SFTPHook<br>        return set(<br>            filter(<br>                lambda file: file.endswith(&quot;.txt&quot;),<br>                SFTPHook.get_hook(SFTP_CONN_ID).list_directory(path),<br>            )<br>        )<br><br>    @task<br>    def files_to_retrieve(loaded_filenames, filenames):<br>        loaded_filenames = set(<br>            list(map(lambda loaded_file: loaded_file[0], loaded_filenames))<br>        )<br>        return list(filenames.difference(loaded_filenames))<br><br>    remote_files = files_to_retrieve(loaded_files.output, list_files(PATH))<br><br>    retrieved_files = SFTPOperator.partial(<br>        task_id=&quot;retrieved_files&quot;,<br>        ssh_conn_id=SFTP_CONN_ID,<br>        operation=&quot;get&quot;,<br>        create_intermediate_dirs=True,<br>        dag=dag,<br>    ).expand_kwargs(<br>        remote_files.map(<br>            lambda file: {<br>                &quot;remote_filepath&quot;: f&quot;{PATH}/{file}&quot;,<br>                &quot;local_filepath&quot;: f&quot;xcoms/{{{{ dag.dag_id }}}}__{{{{ run_id }}}}/{file}&quot;,<br>            }<br>        )<br>    )<br><br>    load_retrieved_files_to_db = GenericTransfer.partial(<br>        task_id=&quot;load_retrieved_files_to_db&quot;,<br>        source_conn_id=&quot;duckdb_duckdb_memory&quot;,<br>        destination_conn_id=MSSQL_CONN_ID,<br>        destination_table=&quot;BRONZE.log_files&quot;,<br>        insert_args={<br>            &quot;target_fields&quot;: [<br>                &quot;event_id&quot;,<br>                &quot;event_time&quot;,<br>                &quot;event&quot;,<br>                &quot;result&quot;,<br>                &quot;info&quot;,<br>                &quot;user_id&quot;,<br>                &quot;origin&quot;,<br>                &quot;destination&quot;,<br>                &quot;session_id&quot;,<br>            ],<br>            &quot;commit_every&quot;: 5000,<br>            &quot;executemany&quot;: True,<br>            &quot;fast_executemany&quot;: True,<br>        },<br>    ).expand(<br>        sql=retrieved_files.output.map(<br>            lambda file: f&quot;&quot;&quot;<br>           SELECT *<br>           FROM read_csv(<br>               &#39;{file}&#39;,<br>               delim = &#39;;&#39;,<br>               header = false,<br>               columns = {{<br>                   &#39;event_id&#39;: &#39;INTEGER&#39;,<br>                   &#39;event_time&#39;: &#39;TIMESTAMP&#39;,<br>                   &#39;event&#39;: &#39;VARCHAR&#39;,<br>                   &#39;result&#39;: &#39;VARCHAR&#39;,<br>                   &#39;info&#39;: &#39;VARCHAR&#39;,<br>                   &#39;user_id&#39;: &#39;VARCHAR&#39;,<br>                   &#39;origin&#39;: &#39;VARCHAR&#39;,<br>                   &#39;destination&#39;: &#39;VARCHAR&#39;,<br>                   &#39;session_id&#39;: &#39;INTEGER&#39;<br>               }}<br>           );<br>        &quot;&quot;&quot;<br>        )<br>    )<br><br>    record_file_as_loaded = SQLInsertRowsOperator(<br>        task_id=&quot;record_file_as_loaded&quot;,<br>        conn_id=MSSQL_CONN_ID,<br>        schema=&quot;BRONZE&quot;,<br>        table_name=&quot;loaded_files&quot;,<br>        columns=[&quot;file_name&quot;, &quot;loaded_at&quot;],<br>        rows=retrieved_files.output,<br>        rows_processor=lambda context, rows: map(<br>            lambda row: (row, context[&quot;execution_date&quot;].strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)),<br>            as_flattened_list(rows),<br>        ),<br>        insert_args={<br>            &quot;commit_every&quot;: 5000,<br>            &quot;autocommit&quot;: False,<br>            &quot;executemany&quot;: True,<br>            &quot;fast_executemany&quot;: True,<br>        },<br>    )<br><br>    (<br>        loaded_files<br>        &gt;&gt; remote_files<br>        &gt;&gt; retrieved_files<br>        &gt;&gt; load_retrieved_files_to_db<br>        &gt;&gt; record_file_as_loaded<br>    )</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7bd4a8eadd2b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/how-to-load-csv-files-in-airflow-using-the-generictransfer-with-duckdb-7bd4a8eadd2b">How to load CSV Files in Airflow using the GenericTransfer with DuckDB</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Transfering data from SAP HANA to MSSQL using the Airflow GenericTransfer]]></title>
            <link>https://medium.com/apache-airflow/transfering-data-from-sap-hana-to-mssql-using-the-airflow-generictransfer-d29f147a9f1f?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d29f147a9f1f</guid>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Thu, 24 Oct 2024 09:29:43 GMT</pubDate>
            <atom:updated>2024-12-10T13:50:51.357Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="GenericTransfer operator in Apache Airflow" src="https://cdn-images-1.medium.com/max/1024/0*hp7GR18Th-c1mM-q.png" /></figure><p>For the migration of historical data from SAP HANA to MSSQL, we needed an efficient solution to copy large datasets between these systems. Since our data pipelines are built on Airflow, we explored the use of the GenericTransfer operator, which allows the execution of on or multiple SQL queries on a source database (SAP HANA, in our case) and inserts the returned results into a target database (here MSSQL).</p><p>While this approach worked well for small datasets, we encountered significant issues when handling larger tables. Below is an overview of the challenges we faced:</p><h3>Challenges with GenericTransfer for large datasets</h3><ol><li><strong>Memory Constraints</strong>: The default implementation of GenericTransfer retrieves all query results in one go, which led to memory exhaustion when dealing with large datasets.</li><li><strong>Performance Bottlenecks</strong>: The time required to process large amounts of data was unacceptably long.</li><li><strong>Timeouts</strong>: The prolonged execution times resulted in task timeouts, especially when copying millions of rows between databases.</li></ol><h3>Solutions to address the issues</h3><p><strong>1. Pagination to Avoid Memory Overload</strong></p><p>Our first solution was to modify the GenericTransfer operator to retrieve data in chunks using pagination. This allowed us to:</p><ul><li>Fetch smaller, more manageable data chunks.</li><li>Reduce memory usage, as only a portion of the data is held in memory at a time.</li><li>Write the data to the destination (MSSQL) progressively, before fetching the next chunk.</li></ul><p>However, although this approach solved the memory issue, it didn’t fully eliminate the problem of timeouts, as processing large tables in a single task was still a long-running operation.</p><p><strong>2. Introducing Deferred Execution</strong></p><p>To address the timeout issue, we introduced an asynchronous mechanism using a custom SQLExecuteQueryTrigger. This allowed the GenericTransfer operator to execute the SQL queries asynchronously, enabling it to:</p><ul><li>Fetch data in chunks via the trigger.</li><li>Use Airflow’s workers to handle the data insertion.</li></ul><p>By splitting the workload between the trigger and the workers, we avoided the long-running task issue, since each chunk was processed in a separate task instance due to the deferred mechanism, mitigating the risk of timeouts.</p><p><strong>3. Improving MSSQL Insert Performance</strong></p><p>Even with asynchronous processing and chunking, we still encountered performance issues when inserting data into MSSQL. By default, we used the executemanyoption for batch inserts, but it was not performing as efficiently as expected.</p><p>After <a href="https://towardsdatascience.com/how-i-made-inserts-into-sql-server-100x-faster-with-pyodbc-5a0b5afdba5">researching </a>further, we discovered a solution from <a href="https://github.com/BritishGeologicalSurvey/etlhelper/issues/86">this GitHub issue</a> which recommended enabling the fast_executemanyoption in the ODBC driver for MSSQL. This option significantly boosts the performance of bulk inserts by up to 10x. Fortunately, the Microsoft ODBC driver for MSSQL (version 17 and above) supports this option, which we are using.</p><p>To ensure compatibility with databases that do not support insert_rows, we patched the insert_rowsmethod of the DbApiHookto fall back to the standard implementation when the feature is unavailable, preserving the original behavior.</p><p>Example: Transferring Large Data with GenericTransfer</p><p>Below is an example of how to configure the GenericTransferoperator for transferring large datasets between SAP HANA and MSSQL, incorporating all the improvements:</p><pre>from airflow.providers.common.sql.operators.generic_transfer import GenericTransfer<br>from datetime import timedelta<br><br>transfer_table_task = GenericTransfer(<br>    task_id=&quot;transfer_table&quot;,<br>    source_conn_id=&quot;{{ params.source_conn_id }}&quot;,  # Source: SAP Hana<br>    destination_conn_id=&quot;{{ params.destination_conn_id }}&quot;,  # Destination: MSSQL<br>    sql=&quot;SELECT * FROM HR.EMPLOYEES&quot;,  # SQL query to run on SAP Hana<br>    destination_table=&quot;NEW_HR.EMPLOYEES&quot;,  # Target MSSQL table<br>    chunk_size=1000,  # Fetch data in chunks of 1000 rows for pagination<br>    insert_args={<br>        &quot;commit_every&quot;: 1000,  # Number of rows inserted in each batch<br>        &quot;executemany&quot;: True,  # Enable batch inserts<br>        &quot;fast_executemany&quot;: True,  # Boost performance for MSSQL inserts<br>        &quot;replace&quot;: True,  # Uses for upserts/merges if needed<br>    },<br>    execution_timeout=timedelta(hours=1),  # Set task execution timeout<br>    dag=dag,  # Reference to your DAG<br>)</pre><h3>Key Benefits of the Enhanced Approach</h3><ul><li><strong>Memory Efficiency</strong>: Data is processed in manageable chunks, reducing memory usage.</li><li><strong>Reduced Task Timeout Risks</strong>: Asynchronous execution distributes the load across multiple tasks (read/write), avoiding long-running tasks.</li><li><strong>Improved Insert Performance</strong>: Using the fast_executemany option accelerates data inserts into MSSQL, significantly reducing overall processing time.</li></ul><p>These improvements enable reliable and efficient data migration for large tables, ensuring both scalability and stability in Airflow pipelines.</p><p>To enable the use of the new chunk_size parameter and the fast_executemanyoption in the GenericTransfer operator, I plan to submit a new pull requests (PR) to the Airflow codebase, introducing this patched functionality. Additionally, there is an existing Airflow pull request (PR) <a href="https://github.com/apache/airflow/pull/41327/">#41327</a> that introduces support for the replace parameter through a dialects mechanism when using an MSSQL database through ODBC.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d29f147a9f1f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/transfering-data-from-sap-hana-to-mssql-using-the-airflow-generictransfer-d29f147a9f1f">Transfering data from SAP HANA to MSSQL using the Airflow GenericTransfer</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing Integration with the MS Graph API and Power BI with the MSGraphAsyncOperator in Airflow]]></title>
            <link>https://medium.com/apache-airflow/optimizing-integration-with-the-ms-graph-api-and-power-bi-with-the-msgraphasyncoperator-in-airflow-d1071f7c1b62?source=rss-93d36a3d6be3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d1071f7c1b62</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[microsoft]]></category>
            <dc:creator><![CDATA[David Blain]]></dc:creator>
            <pubDate>Fri, 15 Mar 2024 15:39:58 GMT</pubDate>
            <atom:updated>2025-02-11T14:00:50.511Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/530/0*K2Tlh9MJXQFRFBbs.png" /></figure><h3>Optimizing Integration of Microsoft Graph API and Power BI with the MSGraphAsyncOperator in Airflow</h3><h3>Introduction</h3><p>As a dedicated team of data practitioners at our company, we orchestrate a myriad of workflows, leveraging Apache Airflow as a pivotal tool for task scheduling and management.</p><h4>Evolution from SAP BODS to Airflow</h4><p>When we switched orchestrating our custom Python pipelines from SAP BODS (Business Object Data Services) to Apache Airflow 2 years ago, we just used Airflow as a scheduler. Our first goal back then was moving away from the monolithic SAP environment to a containerized Kubernetes environment so we could run our custom Python data pipelines using the KubernetesPodOperator. Back then we were already happy as we weren’t restricted to one Python environment and jobs would run in an isolated manner, so one job wouldn’t influence the running or scheduling of another one. We also thought back then that we were using Airflow the way it should as we came across following article titled<a href="https://medium.com/bluecore-engineering/were-all-using-airflow-wrong-and-how-to-fix-it-a56f14cb0753"> ‘We’re All Using Airflow Wrong and How to Fix It’</a>, actually explaining what we were already doing then. Although the article was good and maybe was true back then in 2018, it is certainly not true anymore today as a lot has evolved since then in Airflow.</p><h3>Data Pipelines</h3><h4>Challenges with Custom Pipelines</h4><p>As I explained in the introduction we initially were mainly using Airflow as a job scheduler for our Python scripts running on our Kubernetes cluster. Even though this solution worked well, it also had several disadvantages:</p><ul><li>Each pipeline required a custom build pipeline based on CI/CD, which worked but made deployment painful as each modification or fix needed a new Docker build.</li><li>Each data engineer wrote his pipeline in his own flavor, leading to a lot of custom code which made governance really difficult. At the end the data pipeline scripts were the same as when we were running them on SAP BODS, except now they were scheduled from Airflow running via a KubernetesPodOperator.</li></ul><h4>Consequences of Custom Pipeline Approach</h4><p>As we didn’t use Airflow operators to create our DAG’s, this had multiple consequences:</p><ul><li>We didn’t benefit from the parallelism and/or multi-processing offered by Airflow, as we were running our whole pipeline in a custom Docker image, if we wanted multi-processing we had to implement it ourselves.</li><li>We also didn’t benefit from the Airflow UI to follow/monitor the progress of each task within that pipeline. If a pipeline failed, it had to be re-run completely unless the developer took care of implementing a retry mechanism himself, which made it difficult and challenging to have resilient pipelines.</li><li>As a consequence, we don’t benifit from a functional overview like when using Airflow operators of what each task is doing.</li><li>Monitoring and checking the logs was painful.</li><li>Lineage was also out of the question as everything was custom made.</li></ul><h4>Industrialisation Approach</h4><p>To tackle those issues we tried to industrialize the data pipelines using common dependencies and reusable custom Python libraries. That’s when the story of the MSGraphAsyncOperator actually started.</p><h3>Architecture and Design</h3><h4>Custom Python Library for MS Graph API</h4><p>As we have a lot of data pipelines interacting with the MS Graph API, we decided to implement a common Python client library facilitating those calls. The main purpose was to avoid re-implementing the same logic over and over, but also to solve the following challenges once and for all:</p><ul><li>As we are behind a corporate proxy, we had to make sure our API calls also worked via the proxy.</li><li>We had to make sure authentication was easy to set up. When a bearer token would expire during a long-running pipeline, we had to be sure it was automatically refreshed to avoid failures.</li><li>Some API call’s return paged results, as the MS Graph API is based on the OData specification, it was possible to set up a generic paging solution so the user of the library just had to iterate over the results without worrying how the paging actually works.</li></ul><h4>Integration with Airflow</h4><p>This worked well for calling the MS Graph API from Python but it didn’t solve the issues we had with our containerized Airflow setup described previously. That’s when we realized that we had to migrate our data pipelines using Airflow operators as those would solve all these issues.</p><h3>Integration with Airflow</h3><h4>Operator Development</h4><p>Even though Airflow has a lot of operators already available out of the box through plenty of providers, it didn’t provide an easy to use one for calling the MS Graph API the way we had implemented it with our custom Python package. At this point we had 2 options to interact with the MS Graph API:</p><ul><li>Using the Airflow <a href="https://airflow.apache.org/docs/apache-airflow-providers-http/stable/operators.html">HttpOperator </a>to call the bearer token and handle the paging through custom Python code, and repeat that over and over again for each DAG.</li><li>Using our custom MS Graph Python library through a PythonOperator or a @task decorated function.</li></ul><h4>Custom Operator: MSGraphAsyncOperator</h4><p>Even though both solutions work, they still involve writing Python code. In the meantime, we discovered that Microsoft also created a Python library for facilitating the interaction with the MS Graph API, solving all the challenges we encountered before, except automatic result paging.</p><p>As we strive to have as little custom Python code as possible within our DAG’s, we decided to implement our own MS Graph operator using the Python library offered by Microsoft, that way we didn’t need to maintain our own Python client anymore and we could benefit from all the updates that would come in the future. We would still have custom Python code as we would have our own MS Graph operator implementation, but at least that code would be centralized and thus reusable across multiple DAG’s.</p><p>This would solve the need to write custom Python code within the DAG’s itself and, of course, we could have a dedicated provider for this that would be tested and checked through a CI/CD pipeline.</p><h3>Use Cases and Examples</h3><h4>Real-World Use Cases</h4><p>As the <a href="https://github.com/microsoftgraph/msgraph-sdk-python">msgraph-sdk-python</a> library from Microsoft is implemented in an async way, it also made us discover the <a href="https://learn.microsoft.com/en-us/azure/architecture/patterns/async-request-reply">Asynchronous Request-Reply pattern</a>, which in Airflow is already supported through triggers as part of deferrable operators. The main advantage of this approach, and I think every interaction with an external resource should be handled this way, is that you don’t block your worker resources waiting for a response to come back. Once a request is triggered in Airflow, the worker is released back to the pool allowing other tasks to run while awaiting the response to come back, this way resources are optimally used and parallelism and throughput is increased, which improves the responsiveness of the data pipelines.</p><p>Of course we don’t need the full-blown <a href="https://github.com/microsoftgraph/msgraph-sdk-python">msgraph-sdk-python</a> library from Microsoft, as we won’t be using the Python classes in Airflow. You could use it when using Python code with the <a href="https://airflow.apache.org/docs/apache-airflow/1.10.6/howto/operator/python.html">PythonOperator </a>or @task <a href="https://docs.astronomer.io/learn/airflow-decorators">decorated methods</a>, but as already mentioned, we want to avoid custom code as much as possible for obvious reasons. In case of the MSGraphAsyncOperator we only need to be able to easily interact with the API hence why we only need the <a href="https://pypi.org/project/msgraph-core/">msgraph-core</a> and <a href="https://pypi.org/project/azure-identity/">azure-identity</a> (already used in Airflow) dependencies as those are the building blocks used by the <a href="https://github.com/microsoftgraph/msgraph-sdk-python">msgraph-sdk-python</a> library.</p><h4>Integration with PowerBI</h4><p>So, beyond facilitating calls to the <a href="https://learn.microsoft.com/en-us/graph/use-the-api">Microsoft Graph API</a>, our custom in-house developed <a href="https://github.com/infrabel/apache-airflow-providers-msgraph">MSGraphAsyncOperator</a> also supports:</p><ul><li>Asynchronous request-reply pattern out of the box using the trigger mechanism from Airflow.</li><li>A generic result paging solution which also uses the <a href="https://medium.com/@mahernaija/messaging-patterns-cf4bc5b164cf">asynchronous request-reply</a> mechanism.</li><li>When the API returns bytes as a response, like when <a href="https://learn.microsoft.com/en-us/graph/api/driveitem-get-content?view=graph-rest-1.0&amp;tabs=http">downloading the contents of a driveItem</a>, those cannot be automatically serialized and stored as an XCom into Airflow, thus the operator encodes it automatically as a base64 string for you.</li><li>A result processor (e.g. like in the <a href="https://airflow.apache.org/docs/apache-airflow-providers-atlassian-jira/1.1.0/_api/airflow/providers/atlassian/jira/operators/jira/index.html">JiraOperator</a>) to do light transformations or filtering before the result gets stored into an XCom.</li><li>Interacting with the <a href="https://learn.microsoft.com/en-us/rest/api/power-bi/">PowerBI REST API</a>, as it is based on the same OData specification as the <a href="https://learn.microsoft.com/en-us/graph/use-the-api">Microsoft Graph API</a>.</li></ul><h4>Example DAGs</h4><p><strong>DAG 1: SharePoint Data Retrieval</strong></p><ul><li>DAG Name: sharepoint_news_site_reader</li><li>Description: Retrieves SharePoint pages and lists and stores them into a database using the MSGraphAsyncOperator.</li></ul><p>In the example below we have 3 DAG’s:</p><ul><li>The first DAG retrieves the site id of a SharePoint news site which will then be passed to the 2 other DAG’s using the Airflow TriggerDagRunOperator, that way we only need to retrieve the site id from a given site name once in SharePoint and share that id across both DAG’s, so the later 2 DAG’s will be triggered once the first one completes. We need to retrieve the site id as the consecutive calls to the MS Graph API only support id’s and not a site name (e.g. url) to do the call.</li><li>The DAG called ‘sharepoint_news_site_reader’ will retrieve all site pages for the given site id and persist that data in our data warehouse backend.</li><li>The third DAG called ‘sharepoint_news_stats_writer’ will retrieve all list items which we know contain documents (e.g. csv-files), hence the filter option. We then parse all those csv files which in fact contain statistics a client uploaded to the SharePoint site and parse those csv-files using panda’s to do some transformations and then finally persist those statistics in our data warehouse as well.</li></ul><pre>import logging<br>from datetime import datetime, timedelta<br>from airflow.decorators import task_group<br>from airflow.models import DAG<br>from airflow.operators.trigger_dagrun import TriggerDagRunOperator<br>from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator<br>from airflow.providers.microsoft.azure.operators.msgraph import MSGraphAsyncOperator<br>from airflow.utils.dates import days_ago<br>from custom.providers.sql.operators.sql import SQLInsertRowsOperator  # Custom operator which persists lists of rows into database<br><br>ODBC_CONN_ID = &quot;odbc_conn_id&quot;<br>MSGRAPH_CONN_ID = &quot;msgraph_conn_id&quot;<br>DEFAULT_ARGS = {<br>  &quot;owner&quot;: &quot;David Blain&quot;,<br>  &quot;depends_on_past&quot;: False,<br>  &quot;start_date&quot;: datetime(2024, 3, 1),<br>  &quot;email&quot;: [&quot;no-reply@anonymous.be&quot;],<br>  &quot;email_on_failure&quot;: False,<br>  &quot;email_on_retry&quot;: False,<br>  &quot;retries&quot;: 1,<br>  &quot;retry_delay&quot;: timedelta(minutes=10),<br>}<br><br><br># Subsection: Helper Functions<br>def fetch_max_date(cursor):<br>  &quot;&quot;&quot;Fetch the maximum date from the cursor result.&quot;&quot;&quot;<br>  if cursor.description is not None:<br>    return cursor.fetchone()<br>  return None<br><br><br>def is_modified_on_or_after_max_date(item, max_date) -&gt; bool:<br>  &quot;&quot;&quot;Check if the item was modified on or after the maximum date.&quot;&quot;&quot;<br>  if item:<br>    last_modified_date_time = item.get(&quot;lastModifiedDateTime&quot;)<br>    logging.debug(&quot;Last modified datetime: %s&quot;, last_modified_date_time)<br>    last_modified_date_time = datetime.strptime(last_modified_date_time, &quot;%Y-%m-%dT%H:%M:%S%z&quot;).date()<br>    return last_modified_date_time &gt;= max_date<br>  return False<br><br><br># Subsection: DAG Definitions<br>with DAG(<br>  &quot;sharepoint_news_site_reader&quot;,<br>  default_args=DEFAULT_ARGS,<br>  schedule_interval=timedelta(hours=24),<br>  max_active_runs=1,<br>  concurrency=2,<br>  catchup=False,<br>  tags=[&quot;Sharepoint News&quot;],<br>) as dag:<br>  site_task = MSGraphAsyncOperator(<br>    task_id=&quot;news_site&quot;,<br>    conn_id=MSGRAPH_CONN_ID,<br>    url=&quot;sites/850v1v.sharepoint.com:/sites/news&quot;,<br>    result_processor=lambda context, response: response[&quot;id&quot;].split(&quot;,&quot;)[1],<br>  )<br><br>  trigger_dags_task = TriggerDagRunOperator.partial(<br>    task_id=&quot;trigger_dags&quot;,<br>    conf={&quot;site_id&quot;: site_task.output}<br>  ).expand(<br>    trigger_dag_id=[&quot;sharepoint_news_pages_writer&quot;, &quot;sharepoint_news_stats_writer&quot;]<br>  )<br><br>  site_task &gt;&gt; trigger_dags_task<br><br><br>with DAG(<br>  &quot;sharepoint_news_pages_writer&quot;,<br>  default_args=DEFAULT_ARGS,<br>  schedule_interval=None,<br>  max_active_runs=1,<br>  concurrency=2,<br>  catchup=False,<br>  tags=[&quot;Sharepoint News&quot;],<br>) as dag:<br>  # Subsection: Page Filtering<br>  def filter_pages(context, response):<br>    &quot;&quot;&quot;Filter pages based on the maximum date.&quot;&quot;&quot;<br>    max_date = datetime.strptime(context[&quot;ti&quot;].xcom_pull(task_ids=&quot;max_date&quot;)[0], &quot;%Y-%m-%dT%H:%M:%S%z&quot;).date()<br>    logging.debug(&quot;Max date: %s&quot;, max_date)<br>    return list(filter(lambda item: is_modified_on_or_after_max_date(item, max_date), response.get(&quot;value&quot;)))<br><br>  # Subsection: Tasks<br>  max_date_task = SQLExecuteQueryOperator(<br>    task_id=&quot;max_date&quot;,<br>    conn_id=ODBC_CONN_ID,<br>    handler=fetch_max_date,<br>    sql=&quot;SELECT MAX(LAST_MODIFIED_DATE_TIME) as max_date FROM STG_SHAREPOINT_ONLINE_PAGES&quot;,<br>    dag=dag,<br>  )<br><br>  news_pages_task = MSGraphAsyncOperator(<br>    task_id=&quot;news_pages&quot;,<br>    conn_id=MSGRAPH_CONN_ID,<br>    result_processor=filter_pages,<br>    url=(<br>      &quot;sites/%s/pages&quot; % &quot;{{ dag_run.conf.site_id }}&quot;<br>    ),<br>  )<br><br>  persist_news_pages_task = SQLInsertRowsOperator(<br>    task_id=&quot;persist_news_pages&quot;,<br>    conn_id=ODBC_CONN_ID,<br>    table_name=&quot;STG_SHAREPOINT_ONLINE_PAGES&quot;,<br>    operation=&quot;upsert&quot;,<br>    rows=news_pages_task.output,<br>  )<br><br>  news_pages_task &gt;&gt; persist_news_pages_task<br><br><br>with DAG(<br>  &quot;sharepoint_news_stats_writer&quot;,<br>  default_args=DEFAULT_ARGS,<br>  schedule_interval=None,<br>  max_active_runs=1,<br>  concurrency=2,<br>  catchup=False,<br>  tags=[&quot;Sharepoint News&quot;],<br>) as dag:<br>  # Subsection: Drive Item Filtering<br>  def filter_drive_items(context, response):<br>    &quot;&quot;&quot;<br>    Filter drive items based on the maximum date as unfortunately the<br>    MS Graph API doesn&#39;t support it on the createDateTime property<br>    (see comment on list_items_task), otherwise we could have omitted<br>    this custom Python code.<br>    &quot;&quot;&quot;<br>    max_date = context[&quot;ti&quot;].xcom_pull(task_ids=&quot;max_date&quot;)[0]<br>    logging.debug(&quot;Max date: %s&quot;, max_date)<br>    return list(filter(None, map(lambda drive_item: drive_item.get(&quot;@microsoft.graph.downloadUrl&quot;), filter(lambda item: is_modified_on_or_after_max_date(item, max_date), map(lambda item: item.get(&quot;driveItem&quot;), response.get(&quot;value&quot;))))))<br><br>  # Subsection: Content Processing<br>  def drive_content_to_records(context, response):<br>    &quot;&quot;&quot;Convert drive content to records.&quot;&quot;&quot;<br>    from io import BytesIO<br>    from pandas import read_csv<br>    from base64 import b64decode<br><br>    if response and len(response) &gt; 0:<br>      creation_date = context[&quot;ti&quot;].dag_run.execution_date<br>      logging.info(&quot;Creation date: %s&quot;, creation_date)<br>      df = read_csv(BytesIO(b64decode(response))).fillna(0).drop(columns=[&quot;ID&quot;])<br>      df[&quot;CreationDate&quot;] = creation_date<br>      return df.to_dict(&quot;records&quot;)<br>    return None<br><br>  # Subsection: Tasks<br>  max_date_task = SQLExecuteQueryOperator(<br>    task_id=&quot;max_date&quot;,<br>    conn_id=ODBC_CONN_ID,<br>    handler=fetch_max_date,<br>    sql=&quot;SELECT MAX(CREATION_DATE) as max_date STG_SHAREPOINT_ONLINE_PAGES_WITH_STATS&quot;,<br>    dag=dag,<br>  )<br><br>  shared_documents_task = MSGraphAsyncOperator(<br>    task_id=&quot;shared_documents&quot;,<br>    conn_id=MSGRAPH_CONN_ID,<br>    result_processor=lambda context, response: next(<br>      filter(lambda item: item.get(&quot;name&quot;) == &quot;Shared Documents&quot;, response.get(&quot;value&quot;)), None),<br>    url=&quot;sites/%s/lists&quot; % &quot;{{ dag_run.conf.site_id }}&quot;,<br>  )<br><br>  list_items_task = MSGraphAsyncOperator(<br>    task_id=&quot;list_items&quot;,<br>    conn_id=MSGRAPH_CONN_ID,<br>    result_processor=filter_drive_items,<br>    url=(&quot;sites/%s/lists/%s/items?$filter=%s&amp;$expand=driveItem&quot; # &#39;fields/createdDateTime ge 2024–01–01T00:00:00Z&#39; &#39;createdDateTime ge 2024–01–01T23:59:59.0000000Z&#39;<br>      % (<br>      &quot;{{ dag_run.conf.site_id }}&quot;,<br>      &quot;{{ ti.xcom_pull(task_ids=&#39;shared_documents&#39;)[&#39;id&#39;] }}&quot;,<br>      &quot;contentType%2fname+eq+%27Document%27&quot;,)<br>      ),<br>    )<br><br>  @task_group<br>  def process_list_items(url):<br>    download_drive_item_content_task = MSGraphAsyncOperator(<br>      task_id=&quot;download_drive_item_content&quot;,<br>      conn_id=MSGRAPH_CONN_ID,<br>      response_type=&quot;bytes&quot;,<br>      url=url,<br>    )<br><br>    persist_drive_item_task = SQLInsertRowsOperator(<br>      task_id=&quot;persist_drive_item&quot;,<br>      conn_id=ODBC_CONN_ID,<br>      table_name=&quot;STG_SHAREPOINT_ONLINE_PAGES_WITH_STATS&quot;,<br>      operation=&quot;upsert&quot;,<br>      ignored_columns=[&quot;id&quot;],<br>      rows_processor=drive_content_to_records,<br>      rows=download_drive_item_content_task.output,<br>    )<br><br>    download_drive_item_content_task &gt;&gt; persist_drive_item_task<br><br>max_date_task &gt;&gt; shared_documents_task &gt;&gt; list_items_task &gt;&gt; process_list_items.expand(url=list_items_task.output)</pre><p><strong>DAG 2: PowerBI Dataset Refresh</strong></p><ul><li>DAG Name: refresh_powerbi_dataset</li><li>Description: Triggers a dataset refresh on the PowerBI platform with the MSGraphAsyncOperator.</li></ul><p>Another DAG example for triggering a dataset refresh on PowerBI using the MSGraphAsyncOperator. In this example, we opted to refresh the dataset daily for the sake of simplicity, but this DAG could also be triggered once another DAG completes using the dataset feature, so that we can refresh the PowerBI dataset once we know the data in our data warehouse has been updated.</p><pre>from datetime import datetime<br>from airflow.models import DAG<br>from airflow.providers.microsoft.azure.operators.msgraph import MSGraphAsyncOperator<br><br>DEFAULT_ARGS= {<br>  &quot;owner&quot;: &quot;David Blain&quot;,<br>  &quot;email&quot;: [&quot;no-reply@anonymous.be&quot;],<br>  &quot;depends_on_past&quot;: False,<br>  &quot;start_date&quot;: datetime(2022, 1, 1),<br>  &quot;email_on_failure&quot;: False,<br>  &quot;email_on_retry&quot;: False,<br>  &quot;retries&quot;: 1,<br>}<br><br><br>with DAG(<br>  dag_id=&quot;refresh_powerbi_dataset&quot;,<br>  default_args=DEFAULT_ARGS,<br>  schedule=&quot;@daily&quot;,<br>  catchup=False,<br>) as dag:<br>  refresh_dataset_task = MSGraphAsyncOperator(<br>    task_id=&quot;refresh_dataset&quot;,<br>    conn_id=&quot;powerbi&quot;,<br>    method=&quot;POST&quot;,<br>    url=&quot;myorg/groups/3de4dd29–2f02–4a41-b0a&quot;<br>  )</pre><h3>Other Airflow customizations</h3><h3>Monitoring and Troubleshooting</h3><h4>Custom Monitoring Solutions</h4><p>A combination of built-in features and custom solutions ensure effective monitoring and troubleshooting of Airflow workflows. Task logs and the Airflow UI provide real-time insights into job status and execution history. Custom logging and alerting mechanisms promptly detect and address issues such as API rate limits or connectivity errors, ensuring smooth workflow execution.</p><h4>Custom XCom Backend</h4><p>As our Airflow infrastructure is running on an Kubernetes cluster, we’ve implemented a custom XCom backend which writes the data to a PVC (e.g. Persistent Volume Claim) instead of the Airflow database.</p><p>This has some advantages:</p><ul><li>We don’t overload the Airflow database with large data as it will only have a reference to the filename stored on the PVC.</li><li>There is no limit in how much data is being passed between tasks.</li><li>We don’t have latency due to network communication as all pods can directly access the XCom from the same PVC.</li></ul><h4>Future Enhancements</h4><p>We are going to investigate how to show all those different metrics (Airflow, Kubernetes, Keda, Postgres) on a Grafana dashboard so we have a global visual overview, as well as insight into the history of how the Airflow infrastructure behaved over time and under different loads.</p><h3>Scaling and Optimization</h3><h4>Kubernetes-Based Architecture</h4><p>As data workflows grow in volume and complexity, scalability and optimization are paramount. We leverage a Kubernetes-based architecture deployed on a cluster, with Airflow’s Kubernetes executor efficiently managing task execution across worker nodes. Advantages of this architecture include:</p><ul><li>Scalability</li><li>Resource Isolation</li><li>Fault Tolerance</li><li>Containerization Benefits</li></ul><h4>Celery Integration</h4><p>Additionally, we enhance task scheduling and queuing using Celery as a task queue broker, further improving overall workflow performance.</p><h4>Kafka Integration</h4><p>We are also investigating the usage of Kafka to allow streaming in Airflow.</p><h3>Security and Authentication</h3><h4>OAuth2 Integration</h4><p>We prioritize authentication and security within its data infrastructure, with robust mechanisms in place to control access to sensitive data and external APIs. OAuth2 is employed for interacting with the Microsoft Graph API, and credentials are securely managed using Airflow’s built-in connections feature. Access controls and permissions are enforced at both, the infrastructure and application level, with role-based access control frameworks governing user privileges within Airflow.</p><h3>Future Trends and Considerations</h3><h4>Roadmap for Integration</h4><p>We also acknowledge the increasing importance of seamless integration between Airflow and analytics platforms like Power BI. Future enhancements we’d like to develop for our provider include:</p><ul><li>Industrialize DAG development</li><li>Functional and reusable DAG’s triggered through Datasets</li><li>Enhanced Automation</li><li>Advanced Monitoring (using Grafana)</li><li>Investigate streaming with Kafka</li><li>Optimized Performance (using KEDA for auto-scaling)</li></ul><h4>Innovation for Tomorrow</h4><p>By staying abreast of emerging trends in data orchestration and analytics, we remain well-positioned to drive value and innovation across the organization. In the future we want to investigate more into facilitating integration with the Microsoft REST API’s. We would like to integrate the MS Fabric Rest API through dedicated operators as well as for PowerBI REST API, all based on the <a href="https://github.com/infrabel/apache-airflow-providers-msgraph">MSGraphAsyncOperator</a> used as a common foundation.</p><h3>Conclusion</h3><p>The adoption of the MSGraphAsyncOperator within our Airflow environment underscores a commitment to efficiency, reliability, and innovation in data orchestration. By leveraging asynchronous communication with the Microsoft Graph API and PowerBI, the operator optimizes workflows for responsiveness and scalability, driving tangible business value. As we navigate the complexities of modern data ecosystems, Airflow plays a crucial role in empowering data practitioners and stakeholders alike. With a robust infrastructure powered by Airflow, we are poised to embrace future challenges and opportunities in data management and analytics.</p><h3>References</h3><ul><li>Microsoft Graph Python SDK:<a href="https://github.com/microsoftgraph/msgraph-sdk-python"> Link</a></li><li>The MSGraphAsyncOperator:<a href="https://github.com/infrabel/apache-airflow-providers-msgraph"> Link</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1071f7c1b62" width="1" height="1" alt=""><hr><p><a href="https://medium.com/apache-airflow/optimizing-integration-with-the-ms-graph-api-and-power-bi-with-the-msgraphasyncoperator-in-airflow-d1071f7c1b62">Optimizing Integration with the MS Graph API and Power BI with the MSGraphAsyncOperator in Airflow</a> was originally published in <a href="https://medium.com/apache-airflow">Apache Airflow</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>