<?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 Vidura Mudalige on Medium]]></title>
        <description><![CDATA[Stories by Vidura Mudalige on Medium]]></description>
        <link>https://medium.com/@vbmudalige?source=rss-108f5ed2b457------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*NABbSWW3RnkF5Rk8kF3jIQ.jpeg</url>
            <title>Stories by Vidura Mudalige on Medium</title>
            <link>https://medium.com/@vbmudalige?source=rss-108f5ed2b457------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 23:01:11 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vbmudalige/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[The Journey to Building Cost-Effective Real-Time Data Pipelines with Databricks]]></title>
            <link>https://medium.com/@vbmudalige/the-journey-to-building-cost-effective-real-time-data-pipelines-with-databricks-90074042da80?source=rss-108f5ed2b457------2</link>
            <guid isPermaLink="false">https://medium.com/p/90074042da80</guid>
            <category><![CDATA[cost-optimization]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[real-time-data]]></category>
            <category><![CDATA[databricks]]></category>
            <dc:creator><![CDATA[Vidura Mudalige]]></dc:creator>
            <pubDate>Wed, 28 Aug 2024 01:36:47 GMT</pubDate>
            <atom:updated>2024-08-28T01:36:47.719Z</atom:updated>
            <content:encoded><![CDATA[<p>The hipages data platform has been evolving for over a decade, continuously striving to deliver business value and stay ahead of future needs. This evolution has resulted in a platform with three generations of data warehouses, each defined by the primary data warehouse solution used. The first generation used MySQL for data modeling and OLAP, the second generation transitioned to Redshift, and the third generation adopted Athena. Transitioning from one generation to the next always left some critical pipelines tied to the old systems, making it challenging to fully retire them.</p><p>When the time came to prepare the hipages data platform for next-generation requirements, we decided to fully embrace a modern data stack, focusing on current and future business needs, and finally eliminate the legacy data warehouses.</p><p>After a careful evaluation of multiple modern data stacks and leveraging years of experience, we chose Databricks as our future. Our S3-based data lake and Athena-based data warehouse were replaced by a Databricks lakehouse. Athena CTAS-based data modeling and transformation were transitioned to DBT. The Airflow-based snapshotting process for bringing MySQL production data to the data platform was replaced with CDC events, domain events, Kafka, and Delta Live Tables. Custom Airflow operators for ingesting external data sources were replaced by Fivetran, and data activation from the lakehouse to other systems was streamlined using Hightouch. Ultimately, we introduced real-time data processing to the business for the first time, marking a significant milestone in our data platform journey.</p><h3>The High-Level Data Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qRaxwXotXfHbpw-5tABuOA.jpeg" /></figure><p>This diagram illustrates the high-level architecture of the current state of our data platform. The hipages data ecosystem has evolved through multiple generations, resulting in a complex mix of legacy and modern systems.</p><p>In the legacy systems, we relied on PHP-based cron jobs to create a dimensional data model in a MySQL instance using the production MySQL database. Subsequently, Airflow-based ETLs built another dimensional model in Redshift, using tables from the MySQL data warehouse. The third-generation data warehouse, built on Athena, created yet another dimensional model. This was achieved using Airflow-based ELTs that utilized data from the production MySQL database, the first-generation MySQL data warehouse, and some tables from the second-generation Redshift data warehouse. The BI layer, comprised of Redash and Looker, is connected to this Athena-based data warehouse.</p><p>In our new platform, we have embraced a modern data stack, replacing legacy ELTs with modern data tools. CDC events from MySQL are now published to Kafka using Debezium and processed by Spark Structured Streaming and Delta Live Tables in Databricks to maintain up-to-date tables in our Lakehouse. Hipages is following an architectural vision to adopt Domain-Driven Design (DDD) and Event-Driven Architecture (EDA). The key business domains are split into domain teams, with software development being domain-focused. Events are used to communicate between these business domains, and these events are called domain events. The domain events gradually replace parts of the centralized MySQL production database that were used with a monolithic architecture. We consume these domain events from Kafka, creating streaming tables in Databricks. Data ingestion from external sources is streamlined with Fivetran, while Hightouch facilitates data activation to downstream systems like Braze. DBT has replaced PHP cron jobs and Athena CTAS-based data modeling and transformations, enabling more robust and scalable data processes. In Databricks, we use AWS as our data plane to leverage our expertise and tools from the legacy systems.</p><h3>Transitioning from Batch Processing to Real-Time Processing</h3><p>When we first began adopting the Databricks-based modern data stack for our new data platform, real-time data processing was not a major focus. Initially, we started by consuming CDC events as batches from Kafka using Spark Structured Streaming and processing them with Delta Live Tables (DLT) in Databricks to create copies of MySQL tables in Delta Lake with historical views for analytics. Domain events from various domain teams, published to Kafka, were also processed in a batch manner using DLTs. However, as we started exposing our data models in the BI layer, our end users wanted our datasets and reports to have very low latencies, in some cases even less than 10 minutes. This necessity prompted us to explore the new world of real-time data processing.</p><p>To achieve this, we leveraged the Delta architecture provided by Databricks, which offers significant advantages over both Kappa and Lambda architectures. The Delta architecture simplifies data processing by unifying batch and streaming data processing in a single pipeline, reducing complexity and improving performance. The Kappa architecture, while useful for some real-time scenarios, is not suitable for our batch-to-streaming transition. The Lambda architecture, on the other hand, involves maintaining two separate codebases for batch and stream processing, which increases maintenance overhead and the potential for inconsistencies. The Delta architecture ensures data reliability and consistency with ACID transactions, making it a robust solution for our transition from batch processing to real-time data processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_FxIzljTiEqxjRQqJzNNTg.png" /></figure><p>Based on the complexity, we chose to convert our domain events pipeline to real-time first, as it handled much less data and was fully DLT-based. The Spark notebook used for DLT was created to support both streaming and batch processing, with the nature of processing determined by the pipeline mode in DLT. The transition from batch to streaming involved simply changing the DLT pipeline mode from Triggered to Continuous. In Triggered mode, the pipeline runs once, processing all new events available in Kafka before shutting down the Spark cluster. In Continuous mode, the pipeline never stops; it processes events as soon as they become available in Kafka.</p><p>However, transitioning to streaming for CDC pipelines was more complex. The CDC pipeline involved more than just a single DLT. CDC events, based on MySQL bin logs, have a two-week retention period, but our production MySQL tables contain decades of data. To bring this historical data and new table updates into our analytics platform, we created a Spark Structured Streaming job to ingest CDC events into a custom Delta table partitioned by the Kafka topic name and used a separate Spark job to backfill this table by directly reading each MySQL table. DLT then reads from this raw CDC landing table to process CDC events.</p><p>During batch processing, a single Databricks workflow triggered both the Spark Structured Streaming job and the DLT pipeline. However, in real-time data processing, task dependencies like these are not feasible. Therefore, we split these jobs into two separate workflows.</p><p>For writing the DataFrame to Delta in the Spark Structured Streaming job, we used the availableNow=True trigger type in batch processing mode. In real-time processing, we changed the trigger to provide the micro-batch size, starting with a 30-second micro-batch size using the processingTime=30strigger. On the DLT side, we changed the pipeline mode to Continuous , as in the previous case.</p><p>After implementing these changes, we began receiving real-time data for both CDC and domain events in our Databricks Delta Lake. It was truly impressive to see the tables updating in real-time, and this was expected to be the conclusion of a successful transition. However, it was just the beginning of another chapter.</p><h3>Managing Cost</h3><p>Following our successful transition to real-time data, we received our first AWS cost report for the previous day and discovered a 50% jump in costs, primarily due to high EC2 and S3 usage. This alarming increase was quickly escalated, prompting immediate concern. We faced a tough decision: either reduce the AWS costs to an acceptable level while maintaining real-time data processing or revert to the previous batch processing state.</p><p>The first idea that came to my mind was changing the EC2 instance types we used for the Databricks pipelines. We had been using some storage-optimized, somewhat large EC2 instances, but we noticed that there were compute-optimized, much smaller instances available at a significantly lower price. Our strategy was to use the smallest possible instance with autoscaling enabled and a high maximum worker count. Since our workloads did not involve joins or group by operations, which require shuffling, it was safe to use many smaller instances rather than a few large ones. However, we couldn’t use the smallest possible instance type for the driver node, as it led to out-of-memory errors. Therefore, the smallest possible instance was only used for worker nodes. This change alone reduced the EC2 compute cost for these Databricks pipelines by around 40%.</p><p>However, that was not enough, so we looked for more ways to further reduce costs, particularly Databricks costs.</p><p>The next step was disabling Photon acceleration. The latency requirement for our use cases was around 10 minutes, so we did not need the lightning-fast processing that Photon acceleration provides with the new Spark engine. By disabling Photon acceleration, our DBU consumption dropped by more than 60%.</p><p>Additionally, we found some minor improvements we could make in the DLT pipelines to reduce Databricks costs further. Initially, we had chosen DLT Advancefor its latest features, but we realized that for the domain events pipeline, we could use DLT Core since we didn’t have any data quality checks at the moment (though this will change in the near future). For the CDC processing DLT, we decided to use DLT Pro instead of DLT Advance. There was no need for Advance as we didn’t have any data quality checks in the pipeline at the moment; however, we couldn’t use DLT Core as it doesn’t support CDC processing. By changing the DLT product type, we reduced our Databricks cost even further.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uByTGcLX-x0tLESgu60Fkg.png" /></figure><p>Another lesson we learned when dealing with DLT pipelines was the importance of using the correct pipeline run mode. This is especially important for batch-processing DLTs. When using the DevelopmentDLT run mode, the Spark cluster remains active for around 2 hours even after completing execution, resulting in idle EC2 instances for 2 hours. In contrast, the Production DLT run mode terminates the Spark cluster immediately after execution completes. However, when running our pipelines in real-time, this configuration is less of a concern.</p><p>Then we focused on the Spark structured streaming job that reads CDC events from Kafka and ingests them into the Delta landing table in Databricks. In addition to making the usual cost optimizations with instance types, autoscaling, and Photon acceleration, we noticed something else we could specifically optimize in this job. We adjusted the processingTime trigger value when writing the stream to the Delta table. Our business requirements allow for up to 10 minutes of latency for our reports, so we decided to increase the size of the micro-batches in this stream by increasing the processingTime from 30 seconds to 5 minutes. By doing this, we reduced the number of PUT requests to S3, which helped lower the S3 costs when running this pipeline continuously 24/7.</p><p>Even after implementing all these optimizations, the cost was still too high for us. We had to explore more ways to further reduce costs.</p><p>As I explained earlier, we had created two Databricks workflows to trigger the structured streaming job and the CDC pipeline. Both workflows were configured to run in continuous mode using the continuous trigger type. Databricks workflows are very stable and easy to manage for real-time data pipelines, making monitoring and managing the pipelines straightforward. The diagram below shows the workflow for the domain events pipeline that has been running continuously for two months.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fQRED_HzLH_ZVHvgTv_3ZQ.png" /></figure><p>Upon reviewing the costs for each pipeline, we found that the CDC pipeline was the most expensive due to its higher data load and the need for larger clusters. However, we noticed that the downstream business reports generated by this CDC pipeline are not used outside of business hours. After consulting with our business stakeholders, we learned that they only require real-time data in the reports between 8 AM and 6 PM. Therefore, we decided to run this pipeline only for 12 hours, from 7 AM to 7 PM. So we changed the workflow to use a scheduled trigger type, setting it to start at 7 AM every morning. However, Databricks workflows do not have an option to stop a pipeline at a specific time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9B7aXi3H6nTZtILlMcoOcA.png" /></figure><p>In our data platform, we made an architectural decision to use Airflow as our central orchestrator. However, we also wanted to leverage Databricks workflows for their real-time data pipeline support. So we created an Airflow DAG just to terminate the CDC workflows every day at 7 PM. This was easily implemented using a simple Airflow DAG that stops the Databricks workflows via the WorkspaceClient in the Databricks SDK. In the end, we had two Databricks workflows starting at 7 AM and an Airflow DAG to terminate both workflows at 7 PM. We also muted notifications for cancelled runs in the Databricks workflows to avoid receiving unwanted Databricks workflow failure alerts in Slack every day. This new 12-hour workflow change alone helped us reduce the S3 costs associated with this CDC pipeline by 40%. The diagram below shows the increase in S3 costs after adding real-time data processing and the subsequent drop after limiting the workflow execution time to 12 hours.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OeyDbjAu2XeaFb_StUo8nA.png" /></figure><h3>The Future</h3><p>Even though we have made significant progress in managing costs related to real-time data processing, we have more plans to improve further. One obvious choice is to use Serverless DLT.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iuN6--Ui2hZB1fjFlAAd3g.png" /></figure><p>With serverless DLT, the pipeline settings become much simpler. We no longer have to worry about the pipeline type, Photon acceleration, instance type, autoscaling, or maximum worker count. This approach allows us to completely remove compute costs from our AWS account and achieve more cost-optimized computing in Databricks.</p><p>However, we encountered a major issue with serverless DLTs. In our domain events DLT, we directly read from Kafka and use the schema registry to deserialize the values. Similarly, in the CDC DLT pipeline, we use the Kafka schema registry to deserialize Kafka message values in the landing table. Both of these DLT pipelines require access to Kafka. At hipages, we have an internally managed Kafka cluster that runs on a Kubernetes cluster hosted on AWS within a VPC. When we perform DLT processing in AWS, we do it in a dedicated VPC within our AWS account. We use VPC peering to connect this VPC with the Kubernetes cluster VPC.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/899/0*h513F0AcovhMnGZ6.png" /><figcaption>Overall Databricks Architecture</figcaption></figure><p>However, when using compute resources in the Databricks serverless compute plane, there was no way for the compute resources to connect to the internal Kubernetes cluster VPC. After discussing this issue with Databricks, they offered a preview feature to connect to the Kubernetes cluster VPC using network connectivity configurations (NCC) and Stable IPs in Databricks. This is something we plan to try out in the near future.</p><p>As we have come a long way in clearing up legacy processes and data pipelines, and decommissioning old data warehouses, we still have a long way to go. In the future, we need to fully decommission all the legacy systems and rely entirely on modern systems. As hipages adopts domain-driven design (DDD) and event-driven architecture (EDA), we need to fully decommission the legacy centralized MySQL production database and eliminate CDC processing. We aim to fully embrace DDD and EDA by relying exclusively on domain events from domain teams for data modeling and analytics. Additionally, we want to decentralize the ownership of domain-specific analytical data models to the respective domain teams by adopting data mesh concepts.</p><h3>Conclusion</h3><p>Our journey to building a cost-effective, real-time data pipeline with Databricks has been challenging but rewarding. We have successfully transitioned from a legacy system to a modern, real-time data platform while continuously optimizing costs and maintaining performance. Looking forward, we are excited to explore further advancements to continue enhancing our data capabilities and supporting hipages’ growth, transforming the trade industry, and building better lives for everyone.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=90074042da80" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Decentralising the Ownership of ETLs using Amazon Athena and Apache Airflow]]></title>
            <link>https://medium.com/hipages-engineering/decentralising-the-ownership-of-etls-using-amazon-athena-and-apache-airflow-cebd7105cae2?source=rss-108f5ed2b457------2</link>
            <guid isPermaLink="false">https://medium.com/p/cebd7105cae2</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[aws-athena]]></category>
            <dc:creator><![CDATA[Vidura Mudalige]]></dc:creator>
            <pubDate>Thu, 18 Nov 2021 04:44:52 GMT</pubDate>
            <atom:updated>2021-11-24T02:05:53.197Z</atom:updated>
            <content:encoded><![CDATA[<h4>hipages Data Platform Journey From a Data Mess to the Data Mesh — Part 1</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/794/1*fNJsQIt3OxAKhJ71m0GyRg.png" /></figure><p>Managing the data assets of a fast-growing organisation is a big challenge in the data world. Usually, the data assets grow much faster compared to the organisation itself. In such an environment, delivering the data needs of different product teams by building and maintaining different data pipelines is a big challenge for Data Engineers. In this article, I’ll explain how we overcame this challenge at hipages by making self-serve ETLs using Amazon Athena as an ETL solution with Apache Airflow.</p><h4>Where did we start?</h4><p>A data platform can be defined as a scalable system for the collection, curation, transformation and integration of datasets. A feature-rich comprehensive data platform is not the first thing that a startup company wants to have at the initial phase of its growth; hipages was no different. The main goal of the first version of our data platform was mainly focused on data collection because the business didn’t have many data requirements at the time. The first version of our data platform consisted of an AWS S3 based data lake, an Amazon Athena based SQL interface to query the data in S3, Pandas python library based ETLs, an Apache Airflow based workflow manager and Looker based BI solution. Looker was integrated with the data lake through Amazon Athena. There have been many data sources that bring data to the data lake. Business users and data analysts create various looker dashboards using the data available in the data lake to provide data-driven solutions to the business. To make this process efficient, commonly used datasets have been being extracted from the raw data in S3, transformed and stored in a different S3 location alongside an Athena table. Pandas library and Apache Airflow were used to implement these scheduled ETLs.</p><p>The internal structure of hipages consists of cross-functional teams, which are driven by a clear business scope and a vision. Each cross-functional team includes a data analyst, who is an expert on the business domain of his cross-functional team. When a cross-functional team needs to make a data-driven decision, the data analyst provides the data insights by analysing data through Looker. In contrast to the cross-functional teams, the data platform at hipages was fully centralised, monolithic and traditional. If data analysts couldn’t find the required dataset in the data lake for data analysis, they had to reach out to the Data engineers to request the required ETLs. All the ETLs were owned by the data platform (ultimately Data Engineers) irrespective of the nature of the business domain of data. The Data Engineers used to build various ETLs using Pandas to provide the requested Athena tables to data analysts for looker integrations.</p><h3>The Challenge</h3><p>This centralised data platform had a few drawbacks. Most of the time, the Data engineers who implement these ETLs have little to no domain knowledge of the data they work with. That makes it hard for Data engineers to identify some data quality issues and some subtle issues with the data transformation logic. As the business grows, implementing and maintaining ETLs had become full-time work for Data engineers. There was no time left for Data engineers to add new features and functionalities to the data platform.</p><h3>The Solution</h3><p>The trend towards decentralised architecture was started a long time ago with the arrival of service-oriented architecture and micro-services. The self-service architecture wasn’t a new concept for the hipages data platform. The self-service BI using Looker had already been a part of it. Applying this concept to our data pipelines by decentralising the ownership of the ETLs was the solution we came up with for our challenges. It was our first step towards the decentralised futuristic data platform or the data mesh. To decentralise the ownership of the ETLs, first, we had to make them self serviceable for data analysts. SQL has always been the preferred language for data analysts. Therefore, we decided to implement a SQL based self-serviceable ETL solution for our Amazon S3 based data lake.</p><p>Amazon Athena comes with some ETL support that we can easily adapt for our use case. Athena’s Create Table As Select (<a href="https://docs.aws.amazon.com/athena/latest/ug/ctas.html">CTAS</a>) and <a href="https://docs.aws.amazon.com/athena/latest/ug/insert-into.html">INSERT INTO</a> statements can be used to extract, transform, and load (ETL) data in Amazon S3. The INSERT INO feature was released sometime after the CTAS feature. Therefore, we had to use the CTAS feature creatively for our ETL solution.</p><p>A CREATE TABLE AS SELECT (CTAS) query creates a new table in Athena from the results of theSELECT statement. Athena stores data files created by the CTAS statement in a specified location in Amazon S3. Here is a sample CTAS query,</p><pre>CREATE table new_parquet <br>WITH (<br>      format=&#39;PARQUET&#39;, <br>      partitioned_by=array[&#39;year&#39;], <br>      external_location = &#39;s3://<em>your-bucket</em>/data/&#39;<br>) <br>AS SELECT <br>       id,          <br>       date,          <br>       element,          <br>       datavalue,          <br>       mflag,          <br>       qflag,          <br>       sflag,          <br>       obstime,          <br>       substr(&quot;date&quot;,1,4) AS year <br>FROM source_table <br>WHERE <br>   cast(substr(&quot;date&quot;,1,4) AS bigint) &gt;= 2015         <br>   AND <br>   cast(substr(&quot;date&quot;,1,4) AS bigint) &lt;= 2019</pre><p>By executing the query on Amazon Athena, we get following directories in the given S3 location,</p><pre>s3://<em>your-bucket</em>/data/year=2015/<br>s3://<em>your-bucket</em>/data/year=2016/<br>s3://<em>your-bucket</em>/data/year=2017/<br>s3://<em>your-bucket</em>/data/year=2018/<br>s3://<em>your-bucket</em>/data/year=2019/</pre><p>The SELECT part of the CTAS query extracts the data from “source_table” and loads it to the given location as parquet files. Here we can include all the transformation logics in the SELECT query.</p><p>Here we have used the CTAS partitioned_by feature. In this case, we get 5 partitions in the given S3 location. However, if we execute this query again for a different time range, all the partitions in this S3 location get overwritten. We had two key use cases for this ETL solution.</p><ol><li>It should be able to run on a schedule</li><li>It should generate time-based partitions</li></ol><p>Therefore, it was problematic when using this CTAS feature as it’s for an ETL solution.</p><p>To address this, we decided to use CTAS in a bit different way for our ETL solution. For example, let’s say we need to execute the above query to create yearly partitions. Therefore, it should run once a year and create a partition for that year. First, we need to find the number of yearly partitions that need to be created in the given location. For that, we need to execute a CTAS query like this;</p><pre>CREATE table temp_new_parquet <br>WITH (<br>      format=&#39;PARQUET&#39;, <br>      partitioned_by=array[&#39;year&#39;], <br>      external_location = &#39;s3://<em>your-bucket</em>/temp-data/&#39;<br>) <br>AS SELECT <br>       id,          <br>       date,          <br>       element,          <br>       datavalue,          <br>       mflag,          <br>       qflag,          <br>       sflag,          <br>       obstime,          <br>       substr(&quot;date&quot;,1,4) AS year <br>FROM source_table</pre><p>In this temp-data/ directory we get all the yearly partitions this table has. Then, we can execute a SHOW PARTITIONS temp_new_parquet query to get the list of partitions. This list includes all the partition s3 keys. Using this result, we can execute CTAS queries for each partition with a “WHERE” condition to extract the data for that particular partition and the s3 key to create the partition. For example, if this source_table has 5 yearly partitions, we execute 5 CTAS queries like this for each partition. The following shows the execution for the year=2015 partition.</p><pre>CREATE table new_parquet <br>WITH (<br>      format=&#39;PARQUET&#39;, <br>      partitioned_by=array[&#39;year&#39;], <br>      external_location = &#39;s3://<em>your-bucket</em>/data/year=2015/&#39;<br>) <br>AS SELECT <br>       id,          <br>       date,          <br>       element,          <br>       datavalue,          <br>       mflag,          <br>       qflag,          <br>       sflag,          <br>       obstime,          <br>       substr(&quot;date&quot;,1,4) AS year <br>FROM source_table <br>WHERE year = 2015</pre><h3>Airflow</h3><p>To use this ETL solution for real business use cases, we needed a tool for orchestration and SQL templating. Apache Airflow was the obvious solution for us because it was already a part of our data platform. We designed an Airflow plugin using the AWS boto3 library to programmatically execute CTAS queries on Amazon Athena. We can pass SQL queries with the target s3 location and the partition column to this plugin to generate a transformed, partitioned, parquet based Athena table. When passing the SQL we can easily parameterise the WHERE condition using templates. For example, we can pass the following query.</p><pre>SELECT name, value, run_date<br>FROM source_table<br>WHERE run_date = &#39;{end_year}-{end_month}-{end_day}&#39;</pre><p>This plugin is compatible with any complex SQL query. In this example, we pass the partition column (run_date ) and the target s3 location to the Airflow plugin. In Airflow we can easily inject the DAG execution date into this SQL and execute this SQL as a CTAS query for the DAG execution date using our plugin.</p><h3>Conclusion</h3><p>This ETL solution has become one of the widely used features in the hipages data platform. The data analysts and the engineers in the wider engineering team now use this solution a lot to build their lake to lake ETLs. All they need to do is write a SQL query for the data transformation logic. They can do all the orchestrations and monitoring of their data pipelines via Airflow. The people who write these ETLs are domain experts and they have a good understanding of their data. Therefore, this solution leverages a domain-oriented, self-serve design for ETLs. In other term, a step towards the “Data Mesh”.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cebd7105cae2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hipages-engineering/decentralising-the-ownership-of-etls-using-amazon-athena-and-apache-airflow-cebd7105cae2">Decentralising the Ownership of ETLs using Amazon Athena and Apache Airflow</a> was originally published in <a href="https://medium.com/hipages-engineering">hipages Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>