<?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 Panagiotis Chatzinikolaou on Medium]]></title>
        <description><![CDATA[Stories by Panagiotis Chatzinikolaou on Medium]]></description>
        <link>https://medium.com/@chatzinp?source=rss-888223d86e26------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*CNckvaFLMtOoRl8F</url>
            <title>Stories by Panagiotis Chatzinikolaou on Medium</title>
            <link>https://medium.com/@chatzinp?source=rss-888223d86e26------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 17:44:01 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@chatzinp/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 an ETL Data Pipeline with Python and SQL]]></title>
            <link>https://levelup.gitconnected.com/building-an-etl-data-pipeline-with-python-and-sql-0dec63d5caef?source=rss-888223d86e26------2</link>
            <guid isPermaLink="false">https://medium.com/p/0dec63d5caef</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[etl-pipeline]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Panagiotis Chatzinikolaou]]></dc:creator>
            <pubDate>Tue, 15 Oct 2024 17:44:50 GMT</pubDate>
            <atom:updated>2024-10-15T17:44:50.484Z</atom:updated>
            <content:encoded><![CDATA[<p>A first dive into data engineering for beginners</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6p-NMlInUzg2hx_J" /><figcaption>Photo retrieved from <a href="https://unsplash.com/@bruno_nascimento?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>Introduction</h4><p>Recently, I began a journey to learn data engineering using Python and SQL. Coming from a different field, I’ve been teaching myself by following courses, watching tutorials, and reading books. But as with most technical skills, no matter how much you study, the real learning happens when you try and build something yourself. That’s when I decided to create my first ETL data pipeline using Python and SQL.</p><p>With a background in Exercise Physiology, I naturally gravitated toward a fitness-related project for my first data pipeline. I wanted to work with data I understood and cared about. While there are plenty of open-source datasets available on platforms like Kaggle, I thought using my own fitness data would be more meaningful. So, I decided to create a fitness database and ETL pipeline using my Strava data.</p><p>In this article, I’ll walk through the step-by-step process I followed to build this project, making it approachable for both beginners and more advanced individuals. I’ll also share some challenges I encountered along the way and how I dealt with them.</p><h4>Data retrieval</h4><p>For this project, the easiest way to retrieve my Strava data was by synchronizing my account with <strong>Intervals.icu</strong>. If you’d like to follow along, here’s a step-by-step guide to download your own data:</p><ol><li><strong>Sign up</strong> for an account on <a href="https://intervals.icu/">Intervals.icu</a>.</li><li>Go to the <strong>Settings </strong>tab and sync your account with Strava (or any other fitness platform you use).</li><li>Navigate to the <strong>Activities </strong>tab and switch the view from “Calendar” to “List.”</li><li>Click on the <strong>Columns </strong>tab and select the variables you’re interested in.</li><li>Head to the <strong>Edit </strong>tab and download the CSV file.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RRllpKmjZ3UYI6CVn-ICTA.png" /><figcaption>Download data from Intervals.icu</figcaption></figure><p>For this data engineering project, I downloaded all activities between <strong>January 1, 2024, and October 8, 2024</strong>. The columns I selected included:</p><ul><li>“Type,” “Date,” “Distance,” “Moving Time,” “Name”</li><li>“Avg HR,” “Intensity,” “Avg Altitude,” “Avg HR%”</li><li>“Elapsed time,” “HRRc,” “kcal”</li><li>“Max Altitude,” “Max HR,” “Max HR%,” “Pace”</li></ul><p>The downloaded file will have a name like <strong>i123456_activities</strong>, where “i123456” represents your unique athlete ID. We’ll use this as the primary key in our database. The CSV file is the starting point for building the ETL pipeline.</p><h4>SQL database and python working environment</h4><p>After downloading the CSV file, the next step is to set up the working environment and create the database. I used <strong>VS Code</strong> for my project setup and created a Python virtual environment with the following command:</p><pre>python -m venv .venv</pre><p>Then, I installed the necessary libraries: <strong>Pandas</strong> for data manipulation and transformation, and <strong>SQLAlchemy</strong>, an ORM (Object Relational Mapping) tool that allows us to interact with the SQL database using Python.</p><pre>pip install pandas sqlalchemy</pre><p>For the database, I set up a local SQL Server using SQL Server Management Studio (SSMS) and created a table. You can name your database and table whatever you like, but make sure the names are configured correctly in the SQLAlchemy code.</p><p>In my Fitness table, I used AthleteId and ActivityId as a composite primary key, since each athlete and activity have unique IDs. Additionally, because not every athlete may track their heart rate, I set the heart rate variables (Avg HR, Max HR, etc.) as NULL values, while the rest of the columns were set to NOT NULL.</p><p>To replicate my database structure, you can use the following SQL code:</p><pre>CREATE DATABASE [myFitnessApp]<br>GO<br><br>USE [myFitnessApp]<br>CREATE TABLE FitnessData (<br>    AthleteId VARCHAR(50) NOT NULL,<br>    ActivityId BIGINT NOT NULL,<br>    Type VARCHAR(50) NOT NULL,<br>    Date DATETIME NOT NULL,<br>    Distance FLOAT NOT NULL,<br>    MovingTime FLOAT NOT NULL,<br>    Name VARCHAR(255) NOT NULL,<br>    AvgHR INT NULL,<br>    IntensityPercent INT NULL,<br>    AvgAltitude FLOAT NOT NULL,<br>    AvgHRPercent INT NULL,<br>    ElapsedTime FLOAT NOT NULL,<br>    HRRc INT NULL,<br>    kcal INT NOT NULL,<br>    MaxAltitude FLOAT NOT NULL,<br>    MaxHR INT NULL,<br>    MaxHRPercent INT NULL,<br>    Pace VARCHAR(10) NOT NULL,<br>    PRIMARY KEY (AthleteId, ActivityId)  -- Composite Primary Key<br>);</pre><h4>Building the ETL data pipeline</h4><p>In simple terms, <strong>ETL</strong> stands for <strong>Extract, Transform, Load</strong>. Thus, the goal of this project is to:</p><ul><li><strong>Extract</strong>: Read CSV files containing fitness activity data.</li><li><strong>Transform</strong>: Clean, reformat, and adjust columns to match the database schema.</li><li><strong>Load</strong>: Insert the transformed data into the SQL Server database.</li></ul><p>To keep the code modular and easier to debug, I created separate functions for each step — <strong>Extract</strong>, <strong>Transform</strong>, and <strong>Load</strong> — and then combined them into an overall ETL function.</p><p><strong>Extract</strong></p><p>For the Extract step, I created a function that reads the CSV file into a pandas DataFrame:</p><pre># Extract step: Read the CSV file<br>def extract_data(file_path):<br>    df = pd.read_csv(file_path)<br>    print(f&quot;Data extracted successfully from {file_path}.&quot;)<br>    return df</pre><p><strong>Transform</strong></p><p>In the Transform step, three key tasks were addressed:</p><ol><li><em>Pace Formatting</em><strong>:</strong> The convert_pace() function transforms the pace into the min/km format.</li><li><em>Unit Conversion</em><strong>: </strong>Time is converted from seconds to minutes and distance is converted from meters to kilometers.</li><li><em>Schema Alignment</em><strong>:</strong> Column names are adjusted to match the SQL Server table, ensuring smooth data integration.</li></ol><p>The transformation function is as follows:</p><pre># Function to format pace<br>def convert_pace(speed_m_per_s):<br>    &quot;&quot;&quot;<br>    Convert speed in meters per second (m/s) to pace in min:sec/km.<br><br>    Args:<br>    speed_m_per_s (float): Speed in meters per second.<br><br>    Returns:<br>    str: Pace in the format &#39;min:sec/km&#39;.<br>    &quot;&quot;&quot;<br>    # Convert speed from meters per second to kilometers per minute<br>    speed_km_per_min = speed_m_per_s * 60 / 1000<br><br>    # Convert speed (km/min) to time per km (minutes per km)<br>    minutes_per_km = 1 / speed_km_per_min<br><br>    # Get the integer part of the minutes<br>    minutes = int(minutes_per_km)<br><br>    # Get the remaining seconds (fractional part converted to seconds)<br>    seconds = (minutes_per_km - minutes) * 60<br>    seconds = round(seconds)<br><br>    # Handle case when rounding seconds goes to 60<br>    if seconds == 60:<br>        minutes += 1<br>        seconds = 0<br><br>    # Format the output as &#39;min:sec/km&#39;<br>    return f&quot;{minutes}:{seconds:02d}/km&quot;<br><br># Transform step<br>def transform_data(df, athlete_id):<br>    # Convert time from seconds to minutes<br>    df[&#39;Moving Time&#39;] = df[&#39;Moving Time&#39;] / 60<br>    df[&#39;Elapsed time&#39;] = df[&#39;Elapsed time&#39;] / 60<br>    <br>    # Convert distance from meters to kilometers<br>    df[&#39;Distance&#39;] = df[&#39;Distance&#39;] / 1000<br>    <br>    # Format pace using the new function<br>    df[&#39;Pace&#39;] = df[&#39;Pace&#39;].apply(lambda x: convert_pace(x))<br><br>    # Round specific columns<br>    df[&#39;Intensity&#39;] = df[&#39;Intensity&#39;].round(1)<br>    df[&#39;Avg Altitude&#39;] = df[&#39;Avg Altitude&#39;].round(1)<br>    df[&#39;Avg HR%&#39;] = df[&#39;Avg HR%&#39;].round(1)<br>    df[&#39;Max HR%&#39;] = df[&#39;Max HR%&#39;].round(1)<br>    df[&#39;Max Altitude&#39;] = df[&#39;Max Altitude&#39;].round(1)<br>    df[&#39;Pace&#39;] = df[&#39;Pace&#39;].round(2)<br>    df[&#39;Distance&#39;] = df[&#39;Distance&#39;].round(2)<br>    df[&#39;Moving Time&#39;] = df[&#39;Moving Time&#39;].round(2)<br>    df[&#39;Elapsed time&#39;] = df[&#39;Elapsed time&#39;].round(2)<br>    <br>    # Add athlete_id column<br>    df[&#39;athlete_id&#39;] = athlete_id<br>    print(f&quot;Transformed data for athlete {athlete_id}.&quot;)<br><br>    # Rename columns to match SQL Server table<br>    df.rename(columns={<br>        &#39;athlete_id&#39;: &#39;AthleteId&#39;,<br>        &#39;id&#39;: &#39;ActivityId&#39;,<br>        &#39;Intensity&#39;: &#39;IntensityPercent&#39;,<br>        &#39;Moving Time&#39;: &#39;MovingTime&#39;,<br>        &#39;Elapsed time&#39;: &#39;ElapsedTime&#39;,<br>        &#39;Avg Altitude&#39;: &#39;AvgAltitude&#39;,<br>        &#39;Avg HR%&#39;: &#39;AvgHRPercent&#39;,<br>        &#39;Max HR%&#39;: &#39;MaxHRPercent&#39;,<br>        &#39;Max Altitude&#39;: &#39;MaxAltitude&#39;,<br>        &#39;Max HR&#39;: &#39;MaxHR&#39;,<br>        &#39;HRRc&#39;: &#39;HRRc&#39;,<br>        &#39;Avg HR&#39;: &#39;AvgHR&#39;<br>    }, inplace=True)<br>    <br>    return df</pre><p><strong>Load</strong></p><p>In the Load step, I used the SQLAlchemy to_sql() function to insert the transformed data from the DataFrame into the SQL Server table.</p><pre># Load step<br>def load_data_to_sql(df, engine):<br>    try:<br>        df.to_sql(&#39;FitnessData&#39;, con=engine, if_exists=&#39;append&#39;, index=False)<br>        # Print the IDs of activities added<br>        added_ids = df[&#39;ActivityId&#39;].tolist()<br>        print(f&quot;Activities with the following IDs have been added: {added_ids}&quot;)<br>    except SQLAlchemyError as e:<br>        print(f&quot;Error occurred while inserting data: {e}&quot;)</pre><p>This function attempts to append the data to the <strong>FitnessData</strong> table. It also outputs the IDs of the newly added activities to help track successful insertions. If an error occurs during the insertion, it is caught and logged.</p><p><strong>SQL database connection</strong></p><p>Before loading the data, it is essential to establish a connection with the SQL Server. The configuration and connection code ensure that the correct server, database, and driver names are used.</p><p>In the config.py file, make sure to replace YOUR_SERVER_NAME, YOUR_DATABASE_NAME, and YOUR_DRIVER_NAME with the actual values from your setup. The driver name typically follows the format &quot;ODBC Driver 17 for SQL Server&quot;.</p><pre># config.py<br>SERVER_NAME = &quot;YOUR_SERVER_NAME&quot;<br>DATABASE_NAME = &quot;YOUR_DATABASE_NAME&quot;  # Correct database name<br>DATA_FOLDER = &quot;data/&quot;</pre><p>In the db_connection.py file, the connection string is constructed, and a SQLAlchemy engine is created using the create_engine() function.</p><pre># db_connection.py<br><br>from sqlalchemy import create_engine<br>from config import SERVER_NAME, DATABASE_NAME<br>def connect_to_db():<br>    conn_string = f&#39;mssql+pyodbc://{SERVER_NAME}/{DATABASE_NAME}?driver=[YOUR_DRIVER_NAME]&#39;<br>    engine = create_engine(conn_string)<br>    return engine</pre><p><strong>ETL pipeline function</strong></p><p>With the extract, transform, and load steps defined, I integrated them into a complete ETL pipeline function. This pipeline extracts CSV files from the data/ folder, applies the ETL process, and loads the results into the SQL Server database. Additionally, it extracts the athlete ID from the CSV file name and assigns it to the <strong>athlete_id</strong> column.</p><pre># Main ETL function<br>def etl_pipeline():<br>    engine = connect_to_db()<br><br>    for file_name in os.listdir(DATA_FOLDER):<br>        if file_name.endswith(&quot;_activities.csv&quot;) and len(file_name) == 22:  # Check for 6-digit number format<br>            # Extract the athlete_id from the file name<br>            athlete_id = file_name.split(&quot;_activities&quot;)[0]  # This extracts the &#39;i123456&#39; part<br>            <br>            file_path = os.path.join(DATA_FOLDER, file_name)<br>            <br>            # Extract<br>            df = extract_data(file_path)<br>            <br>            # Transform<br>            df_transformed = transform_data(df, athlete_id)<br>            <br>            # Load<br>            load_data_to_sql(df_transformed, engine)<br><br># Run the ETL pipeline<br>if __name__ == &quot;__main__&quot;:<br>    etl_pipeline()</pre><h4>Running the ETL data pipeline</h4><p>The complete project and code can be found in my GitHub repository. To run the pipeline:</p><ol><li>Clone the repository.</li><li>Place your CSV files in the data/ folder.</li><li>Execute the etl_pipeline() script to populate the database in SQL Server Management Studio (SSMS).</li></ol><p>Github repository: <a href="https://github.com/PanosChatzi/FitnessDatabase">https://github.com/PanosChatzi/FitnessDatabase</a></p><p>After running the ETL pipeline, you can verify the results by querying the <em>FitnessData </em>table in SQL Server:</p><pre>USE [myFitnessApp]<br>SELECT DB_NAME() AS CurrentDatabase<br>GO<br><br>SELECT * FROM [dbo].[FitnessData]<br>ORDER BY Date DESC</pre><p>Voila! If all went well, we will see our data sorted by date. If there was an error on the console, it might most probably have to do with the data types and you might have to change the constrains or reformat the data in python.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lnpQwfES_U7_8pudEpVs-g.png" /><figcaption>View data in SQL server</figcaption></figure><h4>Conclusion</h4><p>Looking back at the start of my journey, whenever I heard about automating pipelines and using libraries like SQLAlchemy, I imagined a data wizard using arcane tools and magic code. It turns out that by building a solid foundation and practicing (code, not magic), anyone can start building data engineering projects.</p><p>This project was a simple example, but I hope it clearly demonstrates how to structure and build an ETL data pipeline with Python and connect it to a local SQL server. There are many ways this project could evolve — some I can already imagine and many more that will unfold as I continue to learn. I am excited to see how this journey develops and how my perspective will change a few years from now, as I look back at where it all began.</p><p>In the meantime, I encourage you to check out the code on my GitHub. Whether you’re a fitness enthusiast looking to streamline your analytics or an aspiring data engineer seeking inspiration for your first project, I hope this work motivates you to take the leap and start your own journey.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0dec63d5caef" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/building-an-etl-data-pipeline-with-python-and-sql-0dec63d5caef">Building an ETL Data Pipeline with Python and SQL</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>