<?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 egehan yorulmaz on Medium]]></title>
        <description><![CDATA[Stories by egehan yorulmaz on Medium]]></description>
        <link>https://medium.com/@egehanyorulmaz?source=rss-2dd74490bcab------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*feU1PpPeJJ5fOET6TS_PCQ.png</url>
            <title>Stories by egehan yorulmaz on Medium</title>
            <link>https://medium.com/@egehanyorulmaz?source=rss-2dd74490bcab------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 15:55:06 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@egehanyorulmaz/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[Building Better Python Projects: How Ruff and Pydantic Make Code More Maintainable]]></title>
            <link>https://medium.com/@egehanyorulmaz/a-step-towards-developing-maintainable-python-projects-with-ruff-and-pydantic-bba63f1006c4?source=rss-2dd74490bcab------2</link>
            <guid isPermaLink="false">https://medium.com/p/bba63f1006c4</guid>
            <category><![CDATA[developer-tools]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[code-quality]]></category>
            <category><![CDATA[pydantic]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[egehan yorulmaz]]></dc:creator>
            <pubDate>Sun, 20 Oct 2024 23:01:22 GMT</pubDate>
            <atom:updated>2025-01-07T20:22:39.096Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*L93oIfyL68ochxrP" /><figcaption>Photo by <a href="https://unsplash.com/@goian?utm_source=medium&amp;utm_medium=referral">Ian Schneider</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Improving our coding skills is an endless journey we all share. Regardless of your position or title, anyone involved in Python development should constantly embrace new techniques. While everyone talks about the importance of continuous learning, actionable recommendations are often hard to find. One of the best ways to grow is by having a talented and experienced mentor or senior engineer on your team — someone who has architected several end-to-end projects and has an intuition for code maintainability. This approach involves constantly questioning future requirements and considering how the team might evolve. So, for those who considers themselves as self-learners, I want to share some subjects I’ve learned this year that have been incredibly helpful for better code management.</p><h3>Ruff</h3><p>Ruff is a blazing-fast Python linter and formatter that keeps your code clean, consistent, and error-free with minimal effort. Ruff analyzes your entire codebase in the blink of an eye, catching issues that might slip through manual checks. It combines the capabilities of multiple tools like Flake8, isort, and pycodestyle into one seamless package.</p><p>Integrating Ruff into your workflow is straightforward. With minimal configuration, it enforces coding standards and highlights potential formatting errors automatically. This means you can focus more on writing great code and less on tedious cleanup.</p><pre>pip install ruff</pre><p>Now that you have ruff installed, let me introduce you some helpful terminal commands:</p><p>1- Identify the formatting errors on your project:</p><pre>ruff check .</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CLFaKy2JFqtt5FwXSAimsQ.png" /></figure><p>As you see on the output, these errors are flagged with [*]. This indicates that Ruff is willing to fix these for you.</p><p>2- Fix obvious mistakes:</p><pre>ruff check . --fix</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*54erl22IJ44qBHYqTebNIg.png" /></figure><p>Ruff fixed 6 out of 8 mistakes on the project. Just like that.</p><p>3- Auto-format your code:</p><pre>ruff format .</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/734/1*vm4UkBIO6EDDFkJ40efu6A.png" /></figure><p>4- Manually fix the mistakes that Ruff didn’t fix for you. For example, Ruff will not remove unused variables as logical changes on the code are considered as unsafe. After carefully checking the left-over errors with step 1, you can run the following command to clear everything up.</p><pre>ruff check . --unsafe-fixes --fix</pre><p>Your organization may have its own formatting guidelines. Ruff allows custom configurations as well! With the help of ruff.toml file, you can change almost every setting. Please refer to the following documentation for further information: <a href="https://github.com/astral-sh/ruff/blob/main/docs/configuration.md">https://github.com/astral-sh/ruff/blob/main/docs/configuration.md</a></p><h3>Pydantic</h3><p>Pydantic is a powerful Python library for data validation and settings management using Python type annotations. It allows you to define data models with type hints and then validates and parses input data automatically, ensuring that the data conforms to the specified types. This leads to more robust and maintainable code, especially in projects where data integrity is crucial.</p><p><strong>⚠️ Important Note:</strong> Pydantic version 2 introduced significant changes to its logical components compared to version 1. Many online resources and AI-generated responses might reference Pydantic version 1, so it’s essential to ensure that the information aligns with the version you’re using. AI models often exhibit a <strong>data availability bias</strong>, which means they are more likely to generate responses based on the information they’ve been exposed to most frequently during training. In the case of Pydantic, version 1 has been around longer and has accumulated a larger amount of documentation, tutorials, and community discussions compared to version 2. As a result, AI models trained on datasets that include internet content up to a certain cutoff date have encountered significantly more resources related to Pydantic v1. That’s why, the first test I do on a new AI model is to ask Pydantic v2 code, and see if it’s as advanced as the metrics suggest :)</p><p>Now, let’s talk about the features of Pydantic.</p><ul><li><strong>Easy Data Validation</strong>: Pydantic simplifies the process of validating and parsing data by using Python’s type annotations. You can define data models that automatically ensure the data fits the expected types.</li><li><strong>Type Hint Integration</strong>: It leverages type hints to make your code more expressive and easier to understand, enhancing readability and maintainability.</li><li><strong>Automatic Type Conversion</strong>: Pydantic can automatically convert input data to the desired type when possible, reducing the need for manual data handling.</li><li><strong>Error Detection</strong>: By enforcing type checks and validations, it helps catch data-related errors early in the development process, leading to more robust code.</li><li><strong>Nested Models Support</strong>: You can create complex, nested data models, which is especially useful when working with JSON or other hierarchical data structures.</li><li><strong>Custom Validation</strong>: Pydantic allows you to define custom validators to enforce additional rules and constraints specific to your application’s logic.</li><li><strong>Configuration Management</strong>: It provides tools for managing application settings and configurations, including loading variables from environment files or environment variables.</li><li><strong>Consistency Across Projects</strong>: Using Pydantic promotes a consistent approach to data handling across different projects, which can improve collaboration within development teams.</li></ul><p>This may sound overwhelming at first, but as you develop more with pydantic, you see how powerful it is. Integrating Pydantic to your workflow is a big step towards maintainability.</p><p>Let’s explore some of these features:</p><pre>from pydantic import BaseModel, ValidationError<br>from enum import Enum<br><br>class Genre(str, Enum):<br>    ACTION = &#39;Action&#39;<br>    DRAMA = &#39;Drama&#39;<br>    COMEDY = &#39;Comedy&#39;<br>    SCI_FI = &#39;Sci-Fi&#39;<br><br>class Movie(BaseModel):<br>    title: str<br>    year: int<br>    rating: float<br>    genre: Genre<br><br># Valid data<br>movie = Movie(title=&quot;Inception&quot;, year=2010, rating=8.8, genre=Genre.SCI_FI)<br>print(movie)<br># Output: title=&#39;Inception&#39; year=2010 rating=8.8 genre=&lt;Genre.SCI_FI: &#39;Sci-Fi&#39;&gt;<br><br># You can also pass the genre as a string<br>movie = Movie(title=&quot;The Godfather&quot;, year=1972, rating=9.2, genre=&quot;Drama&quot;)<br>print(movie)<br># Output: title=&#39;The Godfather&#39; year=1972 rating=9.2 genre=&lt;Genre.DRAMA: &#39;Drama&#39;&gt;<br><br># Invalid data raises a ValidationError<br>try:<br>    movie = Movie(title=&quot;Unknown Movie&quot;, year=&quot;Twenty Ten&quot;, rating=&quot;Excellent&quot;, genre=&quot;Fantasy&quot;)<br>except ValidationError as e:<br>    print(e)</pre><p>As you see here, we are restricting the possible values passed as genre. This is very powerful because it allows us to create a normalized data model, similar to what we do in relational databases. If you have a relational model, you don&#39;t worry about weird genres that your users may pass to your application. Also, we don&#39;t have to worry about type conversion. This may sound too basic, but now let&#39;s consider a JSON file we would like to read and make sure each value has the correct type.</p><pre>import json<br><br>json_data = &#39;&#39;&#39;<br>{<br>  &quot;title&quot;: &quot;Interstellar&quot;,<br>  &quot;year&quot;: &quot;2014&quot;,<br>  &quot;rating&quot;: &quot;8.6&quot;,<br>  &quot;genre&quot;: &quot;Sci-Fi&quot;<br>}<br>&#39;&#39;&#39;<br><br>try:<br>    data = json.loads(json_data)<br>    movie = Movie(**data)<br>    print(movie)<br>except ValidationError as e:<br>    print(e)<br># This code will not raise validation error as all the values are <br># casted to the correct data type</pre><p>Now let’s see how we can use Pydantic’s BaseSettings to load environment variables into a nested data model. This approach is especially useful for managing application configurations, where you might have multiple layers of settings that benefit from validation and structured organization.</p><p>First, make sure you have Pydantic version 2 installed:</p><p>Suppose we have an application that requires database settings and API settings. We can define nested models for these configurations.</p><pre>from pydantic import BaseModel<br>from pydantic_settings import BaseSettings<br><br><br>class DatabaseSettings(BaseModel):<br>    host: str<br>    port: int<br>    user: str<br>    password: str<br><br><br>class APISettings(BaseModel):<br>    endpoint: str<br>    token: str<br><br><br>class AppConfig(BaseSettings):<br>    debug: bool = False<br>    database: DatabaseSettings<br>    api: APISettings<br><br>    model_config = {&quot;env_nested_delimiter&quot;: &quot;__&quot;}</pre><p>In this setup:</p><ul><li>AppConfig inherits from BaseSettings, which tells Pydantic to read values from environment variables.</li><li>The database and api fields are instances of other Pydantic models (DatabaseSettings and APISettings), allowing for nested configuration.</li></ul><p>Next, we can set environment variables to configure our application. For example:</p><ul><li>DEBUG=True</li><li>DATABASE__HOST=localhost</li><li>DATABASE__PORT=5432</li><li>DATABASE__USER=admin</li><li>DATABASE__PASSWORD=secret</li><li>API__ENDPOINT=https://api.example.com</li><li>API__TOKEN=abc123</li></ul><p>Here’s how Pydantic captures the environment variables:</p><pre>debug=True <br>database=DatabaseSettings(host=&#39;localhost&#39;, port=5432, user=&#39;admin&#39;, password=&#39;secret&#39;) <br>api=APISettings(endpoint=&#39;https://api.example.com&#39;, token=&#39;abc123&#39;)</pre><p>Environment variables are particularly useful when working with Docker containers because they provide a flexible way to configure applications at runtime without modifying the code or rebuilding the image. Believe me when I say this, it saves a lot of time! In the context of Docker, environment variables allow you to:</p><ul><li><strong>Decouple Configuration from Code</strong>: By externalizing configuration settings, you can use the same Docker image across different environments (development, staging, production) and simply change the environment variables to adjust the application’s behavior.</li><li><strong>Manage Secrets Securely</strong>: Sensitive information like database passwords or API tokens can be supplied via environment variables, keeping them out of your codebase and Docker images. This reduces the risk of exposing secrets and makes them easier to manage securely.</li><li><strong>Promote Portability and Scalability</strong>: Environment variables make it easy to run multiple instances of a container with different configurations. This is essential in scalable architectures where each instance might need to connect to different resources or services.</li></ul><p>When using Pydantic’s BaseSettings, you can seamlessly integrate environment variables into your Python application’s configuration. Here’s how this is beneficial in a Docker context:</p><ul><li><strong>Automatic Parsing and Validation</strong>: Pydantic reads environment variables and parses them according to the types defined in your settings model. This ensures that your application starts with valid configurations, and any issues are caught early.</li><li><strong>Nested Configuration Support</strong>: With Pydantic’s support for nested models and environment variable parsing, you can organize complex configurations neatly. This is especially useful when dealing with multiple services or components within your application.</li><li><strong>Flexibility with .env Files</strong>: While Docker allows you to pass environment variables directly, you can also use .env files for local development. Pydantic can read from these files, making it easy to switch between development and production settings.</li></ul><p>Thanks for reading! I hope this article gave you some useful ideas on how Ruff and Pydantic can make your Python projects cleaner and easier to manage. If you found this helpful, feel free to follow along for more tips and insights — I have plenty more to share!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bba63f1006c4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Monitoring BigQuery Costs for Cost-Effective Pipelines]]></title>
            <link>https://blog.devgenius.io/bigquery-cost-monitoring-and-insights-for-cost-effective-pipelines-ca14f8715082?source=rss-2dd74490bcab------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca14f8715082</guid>
            <category><![CDATA[cloud-cost-optimization]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[bigquery-api]]></category>
            <dc:creator><![CDATA[egehan yorulmaz]]></dc:creator>
            <pubDate>Mon, 24 Oct 2022 00:39:06 GMT</pubDate>
            <atom:updated>2023-07-31T05:29:34.836Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*HbgVL0uaWxN1cB3Xpe8gog.png" /></figure><p>BigQuery is a full managed serverless data warehouse solution which is flexible, distributed and has hundreds of connectors that allows transferring data to BigQuery with minimal effort. Although there are different providers like Snowflake, Amazon Redshift and SAP Business Warehouse, choosing BigQuery is an architectural decision and thousands of businesses prefer BigQuery as the data warehouse for their data pipeline. Along with the advantages of BigQuery, BigQuery Data Transfer Service offers a variety of sources to integrate to BigQuery like Google Products (YouTube, Google Analytics, Cloud Resources), some AWS products (Redshift, S3), and hundreds of 3rd party transfer tools. Although these integration services are free, streaming data to BigQuery comes with the cost of running constant queries. In addition to these automated queries, data analysts, BI developers, data scientists and maybe outside resources are using BigQuery and running manual queries as well. Inspired by the <a href="https://www.acceldata.io/blog/data-engineering-netflix-data-infrastructure">Netflix’s approach on keeping their infrastructure cost-effective</a>, we can do the same with the BigQuery.</p><h3><strong>Story Plot</strong></h3><p>As a data engineer, you are responsible of monitoring the costs in your Google Cloud infrastructure and you observed that the costs in BigQuery increases at an exponential rate. If your data is growing exponentially as well, then this may not be the biggest concern, but we don’t like exponentially increasing costs, right?</p><p>After this observation, you started to search for ways of reducing the costs, and you came with some ideas that could be useful. But how can we make sure that our solution will be the fix to our problem? Do we have high costs because of manual queries or automated queries? Is there a non-optimized view that uses gigabytes of processing every time the new data comes and the view is called? How much are each table and view contributing to the cost? Maybe some users are running queries like “SELECT * FROM x.t LIMIT 10” as we know, using limit doesn’t reduce the total bytes processed in BigQuery. Before taking any actions, we must figure out the details of every query run and process the data to choose the best action to optimize our costs. Thankfully, BigQuery already offers a Python API and by using the BigQueryJobs class, we can solve this problem! We will be using <a href="https://github.com/egehanyorulmaz/BigQuery-Cost-Monitoring-and-Insights-with-Apache-airflow">the GitHub repository</a> that I created.</p><p>By utilizing the <a href="https://googleapis.dev/python/bigquery/latest/index.html">Python Client for BigQuery module</a>, we can create a client that allows us to interact with BigQuery. Using this client, you can create ETL pipelines that extract or ingest data to BigQuery. Everything you do in BigQuery UI, you can for sure do the same with the Python Client.</p><p><strong>1- BigQuery Class</strong><br>Creates a BigQuery client with the google credential path of the JSON file.</p><p>All we must do to use this repository is to replace our credentials in the class attributes in SqlQueryManager.py, so that our connection to BigQuery is properly authenticated and authorized.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d213940b0e0633b80068d3076c3afa64/href">https://medium.com/media/d213940b0e0633b80068d3076c3afa64/href</a></iframe><p><strong>2- BigQueryJobs Class</strong><br>Inheriting from the BigQuery class it uses the client to list the historical job records and iterates over the jobs for additional details.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/12961663a62f0b5c6652cb0791727a77/href">https://medium.com/media/12961663a62f0b5c6652cb0791727a77/href</a></iframe><p>Using the <strong>get_all_job_details</strong> function, we can extract the following information:</p><ol><li>Job Type (Query or Load)</li><li>Creation Time</li><li>User Email</li><li>Query</li><li>Query Run Time</li><li>Billed Bytes for the Query Run</li></ol><p>If we see a query that has the following format, then we can understand that it is an automated query requested by Google Data Studio.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/904/1*5X9RXv0oQBo51wjap_W9jw.png" /><figcaption>Google Data Studio Query Format</figcaption></figure><p>As a best practice, any automated operation must be authorized with <a href="https://cloud.google.com/iam/docs/service-accounts">service accounts</a>. So, in a well-designed Google Cloud architecture, you might be able to distinguish queries by using the User Email column. If you see an email that ends with @system.gserviceaccount.com, then you can find and filter the queries ran by every service account.</p><p><strong>Summary up to now: </strong>We can get details about all the queries ran in BigQuery using the Python Client of BigQuery. It can be easy to understand who executed these queries by using the User Email column, the time between consecutive creation times, query structure or whether the same query run before or not.</p><h4>Calculating Cost Per Table or View</h4><p>Now, that we have the raw data, we can further analyze it. Yet, we haven’t utilized the billed bytes for the query run. To come up with a cost that is caused by a table, we must understand which tables are used in SQL queries. Luckily, <a href="https://pypi.org/project/sql-metadata/">sql-metadata</a> module does this job for us! Using the sql-metadata library, we can extract which columns or tables are mentioned and used in a SQL query. An example:</p><p><strong>SQL Query:</strong> SELECT * FROM table1 t1\ LEFT JOIN table2 t2\ ON t1.x=t2.x<br><strong>sql_metadata Parser output:</strong> [&quot;table1&quot;, &quot;table2&quot;]</p><p>In the <strong>parse_query</strong> function, we extract the tables in a query and this function is iteratively called by <strong>get_all_job_details</strong>. At this point, assume that we have a long query that uses 3 tables and 2 views. We also have the total bytes billed for this long query. Using a uniform approach (assuming each table contributes the same amount of billed bytes), and converting the bytes to US dollars with <a href="https://cloud.google.com/skus/?currency=USD&amp;filter=bigquery+analysis">region-based analysis costs</a>, we can get a rough cost for each table. The better approach would be to use dry-run option of BigQuery and get the total billed bytes of each subquery. Then, equally divide the total bytes to the number of tables in the subquery</p><p>For the final part of the analysis, all we have to do is automate this process and visualize the costs over time and per table. In the repository, I’ve already developed the production-ready Airflow DAG. After adding an ingestion node or email node at the end, you can start collecting data. For the analysis part, with a simple grouping by tables and ordering by costs, we can see which tables and/or views are the major contributors to the cost. With this insight, we can start optimizing and prioritizing our cost-reduction strategy and have a cost-effective data infrastructure in BigQuery.</p><blockquote>Never underestimate the power of clapping to motivate an author. :)</blockquote><h4><strong>References</strong></h4><ul><li>Netflix Article: <a href="https://www.acceldata.io/blog/data-engineering-netflix-data-infrastructure">https://www.acceldata.io/blog/data-engineering-netflix-data-infrastructure</a></li><li>Image: <a href="https://flowygo.com/en/blog/bigquery-performance-optimization/">https://flowygo.com/en/blog/bigquery-performance-optimization/</a></li></ul><p><a href="https://googleapis.dev/python/bigquery/latest/index.html">https://googleapis.dev/python/bigquery/latest/index.html</a></p><p><a href="https://cloud.google.com/iam/docs/service-accounts">https://cloud.google.com/iam/docs/service-accounts</a></p><p><a href="https://cloud.google.com/skus/?currency=USD&amp;filter=bigquery%20analysis">https://cloud.google.com/skus/?currency=USD&amp;filter=bigquery%20analysis</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca14f8715082" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/bigquery-cost-monitoring-and-insights-for-cost-effective-pipelines-ca14f8715082">Monitoring BigQuery Costs for Cost-Effective Pipelines</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Developing flexible ETL pipelines between databases with Apache Airflow]]></title>
            <link>https://engineering.getmidas.com/developing-flexible-etl-pipelines-between-databases-with-apache-airflow-f8352329e651?source=rss-2dd74490bcab------2</link>
            <guid isPermaLink="false">https://medium.com/p/f8352329e651</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[apache-airflow]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <dc:creator><![CDATA[egehan yorulmaz]]></dc:creator>
            <pubDate>Sat, 19 Mar 2022 12:10:38 GMT</pubDate>
            <atom:updated>2022-03-20T05:29:25.502Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JFiCSBC9FN3fX24r" /><figcaption>Photo by <a href="https://unsplash.com/@sortino?utm_source=medium&amp;utm_medium=referral">Joshua Sortino</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p><strong>Requires knowledge on:</strong> PostgreSQL, MySQL, and as always, Python.</p><p>As data engineers, one of our responsibilities is to develop, manage and optimize Extract, Transform, Load (ETL) pipelines to extract data (APIs, web scraping, databases etc.) and load data to the target using appropriate transforms. This goal rarely changes according to role, but the source and destination of data might vary depending on the need. If let’s say, the marketing team decided to measure their campaign performance on various platforms, then this process will require reporting on a daily basis. But, as data team, our goal must be permanently eliminating time-consuming, repetitive processes in our team like going to the website, downloading a CSV, creating graphs out of it, and reporting it to the management &amp; stakeholders using PowerPoint each day. A well-qualified data engineer must always represent solutions to manual processes with the help of automation tools like Apache Airflow, Apache NiFi, SQL Server Integration Services (SSIS), or Talend using excellent data manipulation skills. These tools provides you a framework to organize your pipelines, but the most important thing about a pipeline is its flexibility. As your enterprise grows, your pipeline must be as scalable and flexible as possible to have sustainability in the long term.</p><h4>Flexible pipelines</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*L6cFdEjFfp4IaE1r" /><figcaption>Photo by <a href="https://unsplash.com/@skyclear?utm_source=medium&amp;utm_medium=referral">Dragoș Grigore</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>The most important thing about building a reliable pipeline is its flexibility to the changes. The development team can change the design of the database by adding a new column, changing data type, enforcing default values, adding new tables, or removing deprecated ones. Therefore, our pipeline should be manipulated easily against these changes. In Midas, our solution to this problem is a manager schema with reference tables specifically designed and integrated into pipelines. You can think of it as a pilot cockpit. The essential part of flight instruments of a pilot is the Mode Control Panel which is used to control heading, speed, altitude, vertical speed, vertical navigation, and lateral navigation. Inspired by design, our reference tables will be like the following:</p><p>The reference table is designed to be the input to a single or multiple ETL pipelines. Its columns are divided into three groups.</p><p>1- Source information — source database (PostgreSQL, MySQL, Cloud Databases), source schema, source table name, and key fields</p><p>2- Extraction configurations — extraction type (TRUNCATE/INSERT, UPSERT, DELETE/INSERT), extraction method (JDBC, PXF)</p><p>3- Target informations (destination database, destination schema, destination table and target_fields)</p><p>Our goal in this tutorial is to build a flexible ETL pipeline that extracts an example dataset from MySQL and ingests it to PostgreSQL using reference tables in the PostgreSQL database.</p><p><strong>Tasks that will be covered in this tutorial:</strong></p><p>1- Deploying Apache Airflow with Docker Compose<br>2- Deploying MySQL and PostgreSQL with Docker Compose<br>3- Data ingestion to MySQL<br>4- Implementing reference table logic in PostgreSQL to manage the ETL pipeline<br>5- Developing ETL pipeline to transfer data using Apache Airflow</p><h4><strong>Setting up the environment</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZeokgkbeOcmfksdo" /><figcaption>Photo by <a href="https://unsplash.com/@angelareinosa?utm_source=medium&amp;utm_medium=referral">ANGELA BENITO</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p><strong>Requirements: </strong><br>1- <a href="https://docs.docker.com/get-docker/">Docker Desktop</a><br>2- <a href="https://docs.docker.com/compose/install/">Docker Compose</a> (Not required for MacOS users since Docker Desktop includes Docker Compose)<br>3- <a href="https://www.python.org/downloads/">Python</a><br>4- Clone the repository for <a href="https://github.com/egehanyorulmaz/reference_etl">required materials</a></p><p>After successfully downloading Docker and Docker Compose, you have specific options to install PostgreSQL and MySQL, but the easiest way is to use docker-compose. I have designed two docker-compose yml files to install both of them for the sake of this tutorial, along with Apache Airflow to use.</p><p>After cloning the repository to your local computer, please follow these steps to deploy PostgreSQL, MySQL, and Apache Airflow as docker containers:</p><p>1- Navigate to the path of the .yml files using terminal <br>2- Make sure docker is up and running.<br>3- Run docker-compose -f etl_databases.yml up -d in the terminal to install Postgresql and MySQL databases. <br>4- Run docker-compose -f apache-airflow.yaml up -d in the terminal to install Apache Airflow.<br>5- Run python file,<em> initialize_databases.py</em>, which is located inside the dags folder.</p><p>To access the database, you can use different tools, but I highly recommend DataGrip if you are working with multiple databases simultaneously. It also allows manually migrating data between databases.</p><h3><strong><em>Helper classes</em></strong></h3><p>Helper classes are extremely useful to manage databases without writing any SQL scripts. If you are using Airflow, it is easy to go with already implemented operators like PostgresOperator, MysqlOperator. Unfortunately, after a certain amount of time, you will face limitations when running complex operations like deleting the last 7 days of data and inserting your data where you do nothing on conflict.</p><p>That ’s why we prefer to develop a subclass called <strong><em>Database </em></strong>(in connections.py) that offers SQL capabilities in Python. In this way, when you are working with Airflow, you have the option to customize your pipeline. Then, we are able to inherit the methods of the Database from database-specific classes. To do this, we will be using the phrase super().init(&lt;CHILD CLASS PARAMETERS&gt;). This definition allows the child class to use the methods (basically inherits from parent class) of the parent passed as parameters to the class definition.</p><h4>connections.py</h4><p>Our main class is as following:</p><pre>class Database:<br>    def __init__(self, database_type, host, port, db_name, user_name, password):<br>        self.host = host<br>        self.port = port<br>        self.db_name = db_name<br>        self._user_name = user_name <br>        self._password = password<br>        self.database_type = database_type<br>        self.conn = self.establish_connection()<br>        self.cursor = self.conn.cursor()<br><br>    def establish_connection(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def execute_query(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def truncate_table(&lt;PARAMETERS&gt;)<br>        pass<br><br>    def create_schema(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def create_table(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def drop_schema(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def drop_table(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def insert_values(&lt;PARAMETERS&gt;):<br>        pass<br><br>    def close_connection(&lt;PARAMETERS&gt;):<br>        pass</pre><p>Now, we will create two child classes for our operations in MySQL and PostgreSQL</p><pre>class Mysql(Database):<br>    def __init__(self, host, port, db_name, user_name, password):<br>        super().__init__(&#39;mysql&#39;, host, port, db_name, user_name, password)</pre><pre>class Postgresql(Database):<br>    def __init__(self, host, port, db_name, user_name, password):<br>        super().__init__(&#39;postgresql&#39;, host, port, db_name, user_name, password)</pre><h4>initialize_databases.py</h4><p>In this code, we will be inserting an example dataset into the Mysql database.</p><pre>from helpers.connections import Mysql<br>import pandas as pd<br>import os</pre><pre>CUR_DIR = os.path.abspath(os.path.dirname(__file__))</pre><pre>database = Mysql(host=&#39;localhost&#39;, port=&#39;3306&#39;, db_name=&#39;mysql_db&#39;, user_name=&#39;root&#39;, password=&#39;root_mysql&#39;)</pre><pre>database.drop_table(table_schema=&#39;stock&#39;,<br>table_name=&#39;stock_symbols&#39;)database.drop_table(table_schema=&#39;stock&#39;, table_name=&#39;stock_values&#39;)<br>database.drop_schema(table_schema=&#39;stock&#39;)<br><br>database.create_schema(&#39;stock&#39;)<br><br>stock_symbols_df = pd.read_csv(CUR_DIR + &quot;/tutorial_data/Company.csv&quot;)</pre><pre>database.create_table(table_schema=&#39;stock&#39;, table_name=&#39;stock_symbols&#39;,<br>                      columns={&#39;ticker_symbol&#39;: &#39;varchar(20)&#39;,<br>                               &#39;stock_name&#39;: &#39;varchar(20)&#39;})</pre><pre>database.insert_values(data=stock_symbols_df, table_schema=&#39;stock&#39;, table_name=&#39;stock_symbols&#39;,<br>columns=&#39;ticker_symbol, stock_name&#39;)</pre><pre>stock_values_df = pd.read_csv(CUR_DIR + &quot;/tutorial_data/CompanyValues.csv&quot;)</pre><pre>database.create_table(table_schema=&#39;stock&#39;, table_name=&#39;stock_values&#39;,<br>columns={&#39;ticker_symbol&#39;: &#39;varchar(20)&#39;,<br>         &#39;day_date&#39;: &#39;timestamp&#39;,<br>         &#39;close_value&#39;: &#39;float&#39;,<br>         &#39;volume&#39;: &#39;bigint&#39;,<br>         &#39;open_value&#39;: &#39;float&#39;,<br>         &#39;high_value&#39;: &#39;float&#39;,<br>         &#39;low_value&#39;: &#39;float&#39;})</pre><pre>database.insert_values(data=stock_values_df, table_schema=&#39;stock&#39;, table_name=&#39;stock_values&#39;, columns=&#39;ticker_symbol, day_date, close_value, volume, open_value, high_value, low_value&#39;)</pre><p>When you run the script provided above, then you will be having two different tables which are already downloaded to the repository from the <a href="https://www.kaggle.com/omermetinn/values-of-top-nasdaq-copanies-from-2010-to-2020">Kaggle dataset</a>, Daily Stock Market Values of Top NASDAQ Companies from 2010 to 2020, and running this code will insert our tutorial data to the MySQL database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*bKpW-ZvyEXTn9EgJrmhqZw.png" /><figcaption>Stock Symbols</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ndCWJtoSJXhBmR4ursiQ7w.png" /><figcaption>Stock Values</figcaption></figure><p>Now, our goal is quite clear. We have to develop a pipeline to transfer these two tables to PostgreSQL. Therefore, we are now ready to create our manager reference table for this particular pipeline in PostgreSQL.</p><h4>initialize_reference_table.py</h4><p>Firstly, we will establish a connection to the postgresql database.</p><pre>from helpers.connections import Postgresql<br>import pandas as pd<br>from datetime import datetime<br>import os<br><br>CUR_DIR = os.path.abspath(os.path.dirname(__file__))<br>database = Postgresql(host=&#39;localhost&#39;, port=&#39;5433&#39;, db_name=&#39;postgres_db&#39;, user_name=&#39;postgres&#39;, password=&#39;postgres&#39;)</pre><p>Then, we will create stock_symbols and stock_values tables for the ingestion process under stock schema.</p><pre># initialize destination tables for data ingestion<br>database.create_schema(&#39;stock&#39;)<br>database.create_table(table_schema=&#39;stock&#39;, table_name=&#39;stock_symbols&#39;,<br>                      columns={&#39;ticker_symbol&#39;: &#39;varchar&#39;,<br>                               &#39;stock_name&#39;: &#39;varchar&#39;})</pre><pre>database.create_table(table_schema=&#39;stock&#39;, table_name=&#39;stock_values&#39;,<br>columns={&#39;ticker_symbol&#39;: &#39;varchar&#39;,<br>         &#39;day_date&#39;: &#39;timestamp&#39;,<br>         &#39;close_value&#39;: &#39;numeric&#39;,<br>         &#39;volume&#39;: &#39;bigint&#39;,<br>         &#39;open_value&#39;: &#39;float&#39;,<br>         &#39;high_value&#39;: &#39;float&#39;,<br>         &#39;low_value&#39;: &#39;float&#39;})</pre><p>Later, we insert two rows to our reference table which will be used in the Airflow DAG.</p><pre>stock_symbol_dict = {&#39;insert_date&#39;: str(datetime.now()), &#39;source_connection&#39;: &#39;mysql&#39;, <br>&#39;source_schema&#39;: &#39;stock&#39;,<br>&#39;source_table&#39;: &#39;stock_symbols&#39;, <br>&#39;key_fields&#39;: &#39;ticker_symbol, stock_name&#39;, <br>&#39;extraction_method&#39;: &#39;jdbc&#39;, &#39;extraction_type&#39;: &#39;full&#39;, &#39;destination_connection&#39;: &#39;postgresql&#39;, <br>&#39;destination_schema&#39;: &#39;stock&#39;, <br>&#39;destination_table&#39;: &#39;stock_symbols&#39;, <br>&#39;target_fields&#39;: &#39;ticker_symbol, stock_name&#39;}<br><br>stock_values_dict = {&#39;insert_date&#39;: str(datetime.now()), &#39;source_connection&#39;: &#39;mysql&#39;, <br>&#39;source_schema&#39;: &#39;stock&#39;,<br>&#39;source_table&#39;: &#39;stock_values&#39;,<br>&#39;key_fields&#39;: &#39;ticker_symbol, day_date, close_value, volume, open_value, high_value, low_value&#39;, <br>&#39;extraction_method&#39;: &#39;jdbc&#39;,&#39;extraction_type&#39;: &#39;full&#39;, &#39;destination_connection&#39;: &#39;postgresql&#39;,<br>&#39;destination_schema&#39;: &#39;stock&#39;, <br>&#39;destination_table&#39;: &#39;stock_values&#39;,<br>&#39;target_fields&#39;: &#39;ticker_symbol, day_date, close_value, volume, open_value, high_value, low_value&#39;}<br><br>stock_symbol_dict = {k:[v,] for k,v in stock_symbol_dict.items()}<br>stock_values_dict = {k:[v,] for k,v in stock_values_dict.items()}<br><br>stock_symbol_df = pd.DataFrame(stock_symbol_dict)<br>stock_values_df = pd.DataFrame(stock_values_dict)<br><br>database.insert_values(data=stock_symbol_df, table_schema=&#39;etl_manager&#39;, table_name=&#39;database_flow_reference_table&#39;,<br>columns=&#39;, &#39;.join(stock_symbol_df.columns.tolist()))</pre><pre>database.insert_values(data=stock_values_df, table_schema=&#39;etl_manager&#39;, table_name=&#39;database_flow_reference_table&#39;,<br>columns=&#39;, &#39;.join(stock_values_df.columns.tolist()))</pre><h4>Airflow DAG design</h4><p>Luckily, you have already installed Airflow, and we are going to work with Directed Acyclic Graphs (DAGs). Apache Airflow UI can be accessed by localhost:8080 because of the airflow-webserver configuration on the apache-airflow.yaml file.</p><p>The login credentials are both ‘airflow’ for username and password. To build a pipeline, we must have the following functions:</p><p><strong>Function 1- Downloads the reference table and returns it as dataframe</strong></p><pre>def download_reference_table() -&gt; DataFrame:<br><em><br>    </em>conn_obj = Postgresql(host=POSTGRE_HOST, port=POSTGRE_PORT, db_name=POSTGRE_DB_NAME, user_name=POSTGRE_USER, password=POSTGRE_PASSWORD)<br>    query = &quot;&quot;&quot;SELECT * FROM etl_manager.database_flow_reference_table&quot;&quot;&quot;<br>    ref_table = conn_obj.execute_query(query=query, return_data=True)<br>    conn_obj.close_connection()  # close connection to greenplum db<br>    return ref_table</pre><p>In this function, we will first establish a connection to Postgresql, run a select query and retrieve the returned data frame. We will be using the returned reference table data to iterate over in our tasks.</p><p><strong>Function 2- Extracts data with given schema and table from MySQL database</strong></p><pre>def extract(source_connection_name: str, schema_name: str, table_name: str, key_fields: str) -&gt; None:<br>    if source_connection_name == &#39;mysql&#39;:<br>        conn_obj = Mysql(host=MYSQL_HOST, port=MYSQL_PORT, db_name=MYSQL_DB_NAME, user_name=MYSQL_USER, password=MYSQL_PASSWORD)<br>    query = f&quot;SELECT {key_fields} FROM {schema_name}.{table_name}&quot;<br>    data = conn_obj.execute_query(query, return_data=True)<br>    file_name = f&quot;{schema_name}_{table_name}.csv&quot;<br>    file_path = CUR_DIR + &#39;/&#39; + file_name<br>    data.to_csv(file_path, index=False)<br>    conn_obj.close_connection()</pre><p>Here, we will establish a connection to the source connection name provided in the reference table and retrieve the key fields from the table again specified in the reference table. The most beneficial factor of using reference tables, a.k.a. a table that manages the pipeline, we can easily add new source databases with minimum code development and change the configurations of the ETL pipeline.</p><p><strong>Function 3- Inserts data to given schema and table in the PostgreSQL database</strong></p><pre>def load_to_target(output_path: str, target_connection_name: str, target_schema: str, target_table: str,<br>                   target_fields: str) -&gt; None:<em><br>    </em>if target_connection_name == &#39;postgresql&#39;:<br>        conn_obj = Postgresql(host=POSTGRE_HOST, port=POSTGRE_PORT, db_name=POSTGRE_DB_NAME, user_name=POSTGRE_USER, password=POSTGRE_PASSWORD)<br><br>    data = pd.read_csv(output_path)<br>    conn_obj.truncate_table(table_schema=target_schema, table_name=target_table)<br>    conn_obj.insert_values(data=data, table_schema=target_schema, table_name=target_table, columns=target_fields)<br>    conn_obj.close_connection()</pre><p>This function will insert our extracted data to the destination passed as a parameter to the reference table. It is flexible because you can post any data with additional configuration in this function.</p><p>So, <a href="https://github.com/egehanyorulmaz/reference_etl/blob/main/dags/etl_pipeline_dag.py">the entire DAG</a> is easily manipulatable.</p><p>Let’s trigger our pipeline and see our green nodes :)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EcuXDASN2bi2rE5SuiqWEQ.png" /><figcaption>Successful DAG Run</figcaption></figure><p>Here we go! Now, we successfully transferred data from MySQL to PostgreSQL using Apache Airflow. Congratulations!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f8352329e651" width="1" height="1" alt=""><hr><p><a href="https://engineering.getmidas.com/developing-flexible-etl-pipelines-between-databases-with-apache-airflow-f8352329e651">Developing flexible ETL pipelines between databases with Apache Airflow</a> was originally published in <a href="https://engineering.getmidas.com/">Midas Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>