<?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 Rahmadiyan on Medium]]></title>
        <description><![CDATA[Stories by Rahmadiyan on Medium]]></description>
        <link>https://medium.com/@rianying?source=rss-3d4d0f6c4d7b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*0-CB2uT4pTb9mO4AWTexUg.jpeg</url>
            <title>Stories by Rahmadiyan on Medium</title>
            <link>https://medium.com/@rianying?source=rss-3d4d0f6c4d7b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 01:18:38 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rianying/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[Inside Apache Spark: Architecture, Optimizations, and Real-World Workflows]]></title>
            <link>https://medium.com/@rianying/inside-apache-spark-architecture-optimizations-and-real-world-workflows-07bd5b2c1f49?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/07bd5b2c1f49</guid>
            <category><![CDATA[spark-sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-spark]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Tue, 09 Sep 2025 10:56:46 GMT</pubDate>
            <atom:updated>2025-09-09T10:56:46.558Z</atom:updated>
            <content:encoded><![CDATA[<p>A fresh look at how Spark tackles large-scale data, why its execution plan matters, and when it makes sense to reach for alternatives.</p><h3>A Closer Look at Spark’s Architecture</h3><p>Spark was built to tackle massive data challenges, not to be conservative. It targets petabytes across clusters with fault tolerance, delivering in-memory speed and a rich set of knobs to tune. For a while it was seen as the future; today it remains a foundational pillar of the modern data stack and a rite of passage for anyone working at scale.</p><h3>The Spark Execution Model</h3><p>Think of Spark as a distributed orchestra where pieces sit in different regions but perform in harmony. The core idea is to treat data as Resilient Distributed Datasets (RDDs): immutable, fault-tolerant blocks that can span thousands of machines while behaving like a single logical unit.</p><p>Spark relies on lazy evaluation. When you write df.filter(col(&quot;age&quot;) &gt; 25).select(&quot;name&quot;), Spark does not start immediately. Instead, it builds a Directed Acyclic Graph (DAG) of your intent and defers execution until an action such as collect() or show() is requested.</p><p>A past debugging session reminded me that Spark’s laziness can yield surprising optimizations. Triggering the action prompts Spark to optimize the whole chain into an efficient plan rather than running step by step.</p><p>This lazy approach enables global optimization. Spark can inspect the entire workflow — filters, joins, and aggregations — and reorganize them for efficiency. It might push predicates down to the data source, avoid unnecessary shuffles, or cache intermediate results you will reuse.</p><p>When a job runs, it is broken into stages and tasks. Stages are acts in a play, separated by shuffles where data moves between partitions. Tasks are the individual scenes executed by executors on worker nodes. The driver orchestrates this flow and coordinates results while the executors perform work in parallel. A capable cluster manager allocates resources across machines.</p><p>The resilience of Spark comes from RDD lineage. If a node fails, Spark can reconstruct lost partitions by replaying the transformations, keeping the overall computation intact.</p><figure><img alt="execution flow image" src="https://cdn-images-1.medium.com/proxy/1*6hxNE6bv_tttTiNV-TUHMg.png" /></figure><p>The distributed execution flow demonstrates Spark’s elegance: laziness gives way to optimized evaluation once an action is invoked.</p><figure><img alt="fault tolerance image" src="https://cdn-images-1.medium.com/proxy/1*SkYoX_2GBXKTTw0biGsBuA.png" /></figure><p>And when hiccups occur, RDD lineage makes recovery possible and keeps processing on track.</p><h3>Catalyst and Tungsten: Spark’s Engineered Edge</h3><p>If the execution model is the orchestra, Catalyst and Tungsten are the backstage crew that makes the performance shine. Catalyst is Spark’s query optimizer. When you write SQL or DataFrame operations, Catalyst rewrites them into more efficient forms. It parses your query into a logical plan, applies rule-based optimizations such as predicate pushdown and column pruning, and uses cost-based optimization to pick effective physical plans.</p><p>I’ve personally seen Catalyst reshape a complex query, reordering joins and repositioning filters to deliver speedups well beyond manual tuning. The transformation is often invisible to you, yet the results speak for themselves.</p><figure><img alt="catalyst image" src="https://cdn-images-1.medium.com/proxy/1*5X-oC1tgZTRDgi3o8v_89A.png" /></figure><p>Tungsten targets low-level execution efficiency. It uses whole-stage code generation to fuse multiple operators into tight Java bytecode, reducing function-call overhead. Off-heap memory management minimizes garbage collection pauses, while vectorized execution and cache-conscious data structures extract more performance from modern CPUs.</p><p>The magic happens when Catalyst and Tungsten collaborate: you get an optimized logical plan and fast, efficient execution without changing your code. Upgrading Spark versions often brings substantial speedups as these components mature.</p><figure><img alt="tungsten image" src="https://cdn-images-1.medium.com/proxy/1*fEttBnkdRDAkS-Audvl32Q.png" /></figure><h3>Real-World Workflows</h3><p>Let us speak plainly about Spark’s day-to-day usage beyond hype. Batch ETL with Spark SQL shines when handling terabytes of raw logs: ingest data, harmonize schemas, apply business rules, and emit analytics-ready datasets. The unified API lets you read Parquet, join with external sources, apply transformations via SQL or DataFrame APIs, and write results to Delta Lake — within a single framework.</p><p>Schema evolution helps accommodate changing data shapes, though defensive design remains prudent. SparkML aimed to bring distributed machine learning to the masses, but today, deep learning workflows are typically better served by dedicated libraries. SparkML remains valuable for large-scale feature engineering and traditional algorithms, at massive scale.</p><p>Structured Streaming seeks to unify batch and streaming through a single API. It works well for many use cases but may lag behind purpose-built streaming engines on latency. It suits near-real-time scenarios where simplicity and reliability trump ultra-low latency.</p><p>For real-time recommendations processing millions of events per minute, Structured Streaming can work, with careful attention to watermarking, checkpoints, and failure recovery. The sweet spot is often fast-batch use cases rather than true streaming at sub-second latency.</p><h3>When to Use Spark (and When to Run)</h3><p>Experience has taught me when Spark earns its keep: with massive datasets, intricate transformations, and time to spare. Its startup overhead makes it less suitable for quick analyses or interactive exploration. For nightly batch workloads spanning petabytes, Spark shines; for sub-second user-facing queries, consider alternatives.</p><p>Cluster management and memory planning matter. Spark’s in-memory approach is powerful but sensitive to cluster sizing. In some cases, a lighter-weight engine on a single machine can outperform Spark for small datasets.</p><p>Consider these options as well: DuckDB for analytical workloads on a single machine, Polars for fast Python analytics with good memory use, Ray or Dask for Python-driven distributed workloads, and Flink for true low-latency streaming.</p><p>The key is to match the tool to the problem. Spark is a distributed data processing framework, not a universal solution. Start with simpler tools and graduate to Spark when scale or complexity demands it.</p><h3>Spark’s Legacy in a Post-Spark World</h3><p>Spark helped democratize big data by making distributed computation accessible via SQL and DataFrame APIs. The era of in‑memory processing, lazy evaluation, and DAG optimization has become a baseline in modern data platforms. The Spark ecosystem — Delta Lake, MLflow, Databricks — has shown how foundational ideas can spawn broader innovation.</p><p>Even as newer tools target specific niches more effectively, the Spark design lessons live on: unified analytics, resilient processing, and accessible scalability shape today’s data infrastructure. While the future may favor specialized systems — real-time streaming engines, ultra-fast analytical databases, or neural-network-aware ML platforms — Spark’s influence remains evident and enduring.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=07bd5b2c1f49" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding OpenMetadata: A Practical Overview]]></title>
            <link>https://medium.com/@rianying/understanding-openmetadata-a-practical-overview-0fa0d56ec523?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/0fa0d56ec523</guid>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[data-discovery]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data-lineage]]></category>
            <category><![CDATA[open-metadata]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Tue, 09 Sep 2025 04:24:29 GMT</pubDate>
            <atom:updated>2025-09-09T04:24:29.640Z</atom:updated>
            <content:encoded><![CDATA[<p>a look at how this open-source metadata platform unifies discovery, governance, and collaboration.</p><figure><img alt="Header image" src="https://cdn-images-1.medium.com/proxy/1*uGoL69UWRZAvzj1j9Ltqlg.jpeg" /></figure><h3>Introduction</h3><p>In recent studies, I explored Apache Iceberg, Apache Kafka, and Apache Flink. This exploration sparked the idea of a helpful extension for navigating modern data ecosystems. Effective metadata management improves data discovery, governance, and collaboration. Enter OpenMetadata, an open-source platform designed to streamline metadata management with robust data discovery, observability, and governance capabilities. In this post, we examine the technical underpinnings of OpenMetadata, its architecture, key features, and practical use cases, complemented by visuals to illustrate why it’s gaining traction in the data engineering community. Let’s dive in.</p><h3>What is OpenMetadata?</h3><p>OpenMetadata is a unified, open-source metadata platform that helps organizations manage their data assets efficiently. Launched in 2021 and inspired by Uber’s metadata infrastructure, it provides a centralized repository for metadata, enabling data discovery, lineage tracking, quality monitoring, and team collaboration. With 300+ contributors and adoption across industries, OpenMetadata stands out for its simplicity, extensibility, and vibrant community. It uses a schema-first approach and 90+ connectors to ingest metadata from databases, data warehouses, pipelines, and dashboards. Its user-friendly interface supports both technical and non-technical users, fostering a data-driven culture.</p><figure><img alt="Architecture diagram" src="https://cdn-images-1.medium.com/proxy/0*qFBRrOFQPY0I6SdS" /></figure><h3>Why OpenMetadata Matters</h3><p>In the past, reading a database and producing a report was straightforward. Today, data flows through complex pipelines from various sources. When results go astray, tracing the root cause is hard. OpenMetadata helps with data lineage, quality, and collaboration, addressing several pain points:</p><ul><li><strong>Fragmented Data Sources</strong>: Organizations use multiple tools (e.g., Snowflake, dbt, Metabase), creating siloed metadata. OpenMetadata centralizes this metadata into a unified graph.</li><li><strong>Data Discoverability</strong>: Finding relevant assets can be time-consuming. OpenMetadata’s search and metadata enrichment streamline discovery.</li><li><strong>Governance and Compliance</strong>: Robust governance with metadata versioning and automated workflows reduces manual effort.</li><li><strong>Scalability</strong>: Lightweight architecture and broad connector support suit enterprises of various sizes.</li></ul><h3>Architecture of OpenMetadata</h3><p>OpenMetadata comprises four core components:</p><ol><li><strong>Metadata Store</strong>: A central repository for the metadata graph connecting assets, users, and tool-generated metadata. It uses a relational database (e.g., MySQL, Postgres) instead of graph databases like Neo4j.</li><li><strong>Ingestion Framework</strong>: A pluggable framework that ingests metadata from 90+ sources, including databases (BigQuery, Snowflake), data lakes (S3, Iceberg), and BI tools (Power BI). Connectors are Python-based and support custom extensions.</li><li><strong>Metadata Schemas</strong>: JSON-based schemas define entities (tables, dashboards) and relationships. These schemas are extensible to fit organizational needs.</li><li><strong>User Interface</strong>: A web-based React UI offering search, lineage visualization, and collaboration tools. It integrates with Elasticsearch for full-text search and supports CMD + K shortcuts for quick navigation.</li></ol><p>The setup is straightforward, reducing deployment overhead. For example, a local environment can be up in minutes, and cloud deployments are supported on AWS, Azure, and Google Cloud.</p><h3>Key Features</h3><p>OpenMetadata provides a rich feature set that addresses common data-team needs. Here are the most impactful ones:</p><h3>1. Data Discovery</h3><p>A full-text search engine indexed via Elasticsearch covers entity names, descriptions, tags, and even conversations. Users refine searches with filters or advanced queries to explore tables, dashboards, pipelines, and more.</p><h3>2. Data Lineage</h3><p>Lineage tracking offers column-level visibility across pipelines and tools. You can trace data movement from a PostgreSQL table, through a dbt transformation, to a Power BI dashboard. Lineage can be exported as PNG or PDF for documentation.</p><h3>3. Data Quality and Profiling</h3><p>Includes no-code data quality tests and profiling tools. Users define test suites, monitor data health, and view results in an interactive dashboard. AutoPilot, an AI-driven feature, automates metadata extraction and profiling for new services, reducing onboarding time.</p><h3>4. Collaboration</h3><p>The platform enables collaboration through conversation threads, task creation, and event notifications. Data producers and consumers can discuss data assets directly, reducing silos.</p><h3>5. Governance</h3><p>Supports metadata versioning, tagging, and ownership assignment to enable compliance with governance policies. Two-way metadata synchronization pushes enriched metadata back to source systems like Snowflake, ensuring consistency.</p><h3>6. Extensibility</h3><p>A schema-first approach and REST APIs let developers extend metadata entities and integrate with custom tools. The ingestion framework supports community-contributed connectors for flexibility.</p><h3>Use Cases</h3><p>The flexible design applies across industries. Here are a few scenarios:</p><ul><li><strong>Data Centralization</strong>: A retail company consolidates metadata from Snowflake, dbt, and Metabase to provide a single source of truth for analytics.</li><li><strong>Governance Automation</strong>: A financial institution uses AutoPilot to automate metadata tagging and enforce data masking in BigQuery, supporting GDPR compliance.</li><li><strong>Data Discovery for AI</strong>: A SaaS provider standardizes metadata for diverse customer datasets to integrate into AI model pipelines.</li><li><strong>Collaboration Across Teams</strong>: An e-commerce platform uses OpenMetadata to bridge gaps between business analysts and data engineers for faster dashboard creation.</li></ul><h3>Getting Started</h3><p>Getting started with OpenMetadata can be done in several ways; the official quick-start guide is available here: <a href="https://docs.open-metadata.org/latest/quick-start">OpenMetadata Quick Start</a>. Note that the UI URL remains stable:</p><ul><li>Access the UI: <a href="http://localhost:8585/">http://localhost:8585</a></li><li>Configure Connectors: OpenMetadata supports 90+ connectors for ingestion. The docs provide step-by-step guides for popular tools.</li></ul><figure><img alt="Sandbox image" src="https://cdn-images-1.medium.com/proxy/0*KmoE4YXZhSwh4VFB" /></figure><p>You can try the OpenMetadata Sandbox at sandbox.open-metadata.org. The community Slack channel is also a valuable resource for support and feature discussions: <a href="https://slack.open-metadata.org/">https://slack.open-metadata.org/</a></p><h3>Challenges and Considerations</h3><p>OpenMetadata is powerful but has limitations. For modern data lakehouse architectures (e.g., Delta Lake), some connectors may be limited, and ingestion can incur costs if not optimized. Do not point it at a massive data lake without testing first. Check connector compatibility and run a proof-of-concept before full deployment.</p><h3>Summary</h3><p>OpenMetadata is an open-source project that provides a unified metadata management solution. Its lightweight architecture, broad connector support, and emphasis on collaboration make it compelling for modern data stacks. By centralizing metadata, improving discoverability, and automating governance, OpenMetadata helps organizations unlock their data assets. If you’re building a data-driven culture or addressing governance challenges, OpenMetadata is worth exploring.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0fa0d56ec523" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Weekly Spotify Wrapped: A Real Time Spotify Music Listening Data Streaming with PySpark, Kafka…]]></title>
            <link>https://medium.com/data-engineering-indonesia/weekly-spotify-wrapped-a-real-time-spotify-music-listening-data-streaming-with-pyspark-kafka-e1385d5e105e?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1385d5e105e</guid>
            <category><![CDATA[spark-streaming]]></category>
            <category><![CDATA[kafka]]></category>
            <category><![CDATA[spotify]]></category>
            <category><![CDATA[google-gemini-ai]]></category>
            <category><![CDATA[data-engineer]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Sun, 15 Sep 2024 20:59:44 GMT</pubDate>
            <atom:updated>2024-09-23T06:48:50.343Z</atom:updated>
            <content:encoded><![CDATA[<h3>Weekly Spotify Wrapped: A Real Time Spotify Music Listening Data Streaming with PySpark, Kafka, PostgreSQL, and Google’s Gemini AI</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cDAFJm28CiRoHHImwNyUJQ.png" /></figure><h3>Introduction</h3><p>Those who use Spotify to listen to music to escape the cruel world, have at least once in their lifetime expecting to get the notification from Spotify for the Spotify Wrapped. Well, I’m one of them. But I can’t and I won’t wait for a year for that, so, leveraging Kafka for messaging system, Spark for stream processing, PostgreSQL for database, Flask for web service, and Google’s Gemini AI for generative AI, let’s make a streaming pipeline to generate our own Weekly Spotify Wrapped.</p><p>This article is inspired by <a href="https://medium.com/@ahmadkamiludin">Ahmad Kamiludin’s</a> story of building a music stream data processing. You can read his story by clicking h<a href="https://medium.com/data-engineering-indonesia/real-time-music-data-streaming-using-apache-kafka-leveraging-avenged-sevenfold-data-from-spotify-25304abcb9bb">ere</a>.</p><p>Before we start, a little bit of disclaimer. I’m in no way an expert in Data Engineering or Software Development. Along the way, you might explore a lot of flaws in my code. My skill in system design and my coding practices is yet to be desired. This project is designed to offer me a valuable learning opportunity, and I aspire to assist you in getting started with utilizing these various tools.</p><p>Let’s start building.</p><h4>Tech Stack</h4><ul><li>Python: Main programming language</li><li>Flask: Webservice is the man in the middle between Spotify API and us. Also serves as an endpoint to call our weekly Spotify Wrapped.</li><li>Kafka: Messaging system to handle real-time data streams.</li><li>Spark: Stream processing framework to process and analyze data in real time.</li><li>PostgreSQL: Database to store processed data and user information.</li><li>Gemini AI: Google’s AI platform for generating insights and recommendations.</li></ul><h4>Project Plan</h4><p>This project will be divided into three parts:</p><ol><li>Setting up the Flask web server</li><li>Setting up the Kafka broker</li><li>Setting up the Spark streaming process.</li></ol><h4>Pre-requisites</h4><p>We’re gonna be setting up our project directory. Mine looks like this:</p><pre>.<br>├── README.md<br>├── .env<br>├── flask_backend<br>│   └── app.py<br>├── kafka-clusters<br>│   ├── __init__.py<br>│   └── docker-compose.yml<br>└── prod-con<br>    ├── __init__.py<br>    ├── client.properties<br>    ├── models.py<br>    ├── producer.py<br>    ├── spark-consumer-album.py<br>    ├── spark-consumer-artist.py<br>    ├── spark-consumer-history.py<br>    └── spark-consumer-song.py</pre><p>The docker-compose for Kafka can be downloaded <a href="https://raw.githubusercontent.com/confluentinc/cp-all-in-one/7.6.1-post/cp-all-in-one-kraft/docker-compose.yml">here</a>. Or copy and paste this into your terminal:</p><pre>wget https://raw.githubusercontent.com/confluentinc/cp-all-in-one/7.6.1-post/cp-all-in-one-kraft/docker-compose.yml</pre><p>Next, let’s install the Python packages:</p><pre>pip install flask requests spotipy google-generativeai confluent-kafka findspark pyspark psycopg2-binary python-dotenv requests sqlalchemy sqlalchemy.orm </pre><p>We’re gonna need to get the Spotify Client ID and Spotify Client Secret. You can head to <a href="https://developer.spotify.com/dashboard"><strong>https://developer.spotify.com/dashboard</strong></a>, create an app, fill in the details, go to the dashboard, open your app, click on settings in the top right corner, and there you’ll get your Client ID and Client Secret.</p><p>You also need to fill in the Redirect URIs form. Fill it with:</p><pre>http://127.0.0.1:5000/redirect</pre><p>This will be one of our endpoints.</p><p>We also need to get the Gemini API Key and Google App Password. You can get the Gemini API Key from: <a href="https://ai.google.dev/gemini-api/docs/api-key"><em>https://ai.google.dev/gemini-api/docs/api-key</em></a> and the Google App Password from: <a href="https://support.google.com/mail/thread/205453566/how-to-generate-an-app-password?hl=en"><em>https://support.google.com/mail/thread/205453566/how-to-generate-an-app-password?hl=en</em></a></p><p>Now in your root directory, create a .env file and fill it with:</p><pre>SPOTIFY_CLIENT_ID=1c9c9fe9723045b58a3dd31c2a7be91f<br>SPOTIFY_CLIENT_SECRET=577ae584282b4e539dfe893134b74430<br>PSQL_DB_HOST=[your psql host]<br>PSQL_DB_NAME=[your psql db name]<br>PSQL_DB_USER=[your psql db username]<br>PSQL_DB_PASSWORD=[your psql db password]<br>GENAI_API_KEY=[your genai api key]<br>GOOGLE_APP_PASSWORD=[your google app password]</pre><h3>Setting Up Flask Webservice</h3><p>Why do we need Flask?</p><p>We are gonna be fetching Spotify’s recently played tracks which is gonna need an access token with scope user-read-recently-played, hence we need an endpoint to let us log into Spotify to gain the access token and redirect us to the redirect link we specified earlier. This only happens once.</p><p>Also, we will be building the spotify-wrapped endpoint and using Gemini AI and generate the insight and metrics from our listening history data.</p><p>Let’s delve in by copying and pasting this code into app.py</p><pre>import uuid<br>import json<br>from flask import Flask, redirect, session, request, url_for<br>from spotipy import Spotify, SpotifyOAuth<br>import os<br>from dotenv import load_dotenv<br>import secrets<br>import time<br>from utils import get_insight<br><br>load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br><br>client_id = os.getenv(&#39;SPOTIFY_CLIENT_ID&#39;)<br>client_secret = os.getenv(&#39;SPOTIFY_CLIENT_SECRET&#39;)<br>TOKEN_INFO = &#39;&#39;<br>app = Flask(__name__)<br>app.secret_key = secrets.token_hex(16)<br><br>def create_spotify_oauth():<br>    return SpotifyOAuth(<br>        client_id=client_id,<br>        client_secret=client_secret,<br>        redirect_uri=url_for(&#39;redirect_page&#39;, _external=True),<br>        scope=&#39;user-read-recently-played&#39;<br>    )<br>    <br>def get_token():<br>    token_info = load_token_info()<br>    if not token_info:<br>        return redirect(url_for(&#39;login&#39;, _external=True))<br>        <br>    is_expired = token_info[&#39;expires_at&#39;] - int(time.time()) &lt; 600<br>    if is_expired:<br>        sp_oauth = create_spotify_oauth()<br>        token_info = sp_oauth.refresh_access_token(token_info[&#39;refresh_token&#39;])<br>        save_token_info(token_info)<br>    return token_info[&#39;access_token&#39;]<br><br>def save_token_info(token_info):<br>    # save token info to a file<br>    with open(&#39;token_info.json&#39;, &#39;w&#39;) as f:<br>        f.write(json.dumps(token_info))<br><br>def load_token_info(): <br>    try:<br>        with open(&#39;token_info.json&#39;, &#39;r&#39;) as file:<br>            data = json.load(file)<br>        return data<br>    except FileNotFoundError:<br>        return None<br>    except KeyError:<br>        return None<br><br>@app.route(&#39;/&#39;, methods=[&#39;POST&#39;])<br>def stream_data():<br>    try:<br>        token_info = get_token()<br>    except:<br>        print(&#39;User not logged in / token expired&#39;)<br>        return redirect(url_for(&#39;login&#39;, _external=True))<br>    sp = Spotify(auth=token_info)<br>    <br>    req_data = request.get_json()<br>    recent_played = sp.current_user_recently_played(after=req_data[&#39;after&#39;])<br>    if recent_played[&#39;items&#39;] == []:<br>        return {<br>            &quot;status_code&quot;: 204,<br>            &quot;message&quot;: &quot;No new data&quot;<br>        }<br>        <br>    # after = request.form.get(&#39;after&#39;)<br>    items = []<br>    for item in recent_played[&#39;items&#39;]:<br>        played_at = item[&#39;played_at&#39;][0],<br>        song_id = item[&#39;track&#39;][&#39;id&#39;]<br>        album_id = item[&#39;track&#39;][&#39;album&#39;][&#39;id&#39;]<br>        artist_id = item[&#39;track&#39;][&#39;artists&#39;][0][&#39;id&#39;]<br>        items.append({<br>            &#39;id&#39;: uuid.uuid4().hex,<br>            &#39;played_at&#39;: played_at,<br>            &#39;song_id&#39;: song_id,<br>            &#39;album_id&#39;: album_id,<br>            &#39;artist_id&#39;: artist_id,<br>        })<br>        <br>    return {<br>        &#39;status_code&#39;: 200,<br>        &#39;message&#39;: f&#39;{len(items)} new songs&#39;,<br>        &#39;items&#39;: items,<br>    }<br><br>@app.route(&#39;/insight&#39;, methods=[&#39;GET&#39;])<br>def insight():<br>    ai_resp = get_insight()<br>    if ai_resp != &#39;Success&#39;:<br>        return {<br>            &quot;status_code&quot;: 500,<br>            &quot;message&quot;: &quot;Internal Server Error&quot;<br>        }<br>    return {<br>        &quot;status_code&quot;: 200,<br>        &quot;message&quot;: &quot;Success. Check your email for the insights&quot;<br>    }<br>    <br>    <br>@app.route(&#39;/login&#39;, methods=[&#39;GET&#39;])<br>def login():<br>    auth_url = create_spotify_oauth().get_authorize_url()<br>    return redirect(auth_url)<br><br>@app.route(&#39;/redirect&#39;)<br>def redirect_page():<br>    session.clear()<br>    code = request.args.get(&#39;code&#39;)<br>    token_info = create_spotify_oauth().get_access_token(code)<br>    save_token_info(token_info)<br>    session[TOKEN_INFO] = token_info<br>    return redirect(url_for(&#39;homepage&#39;, _external=True),)</pre><p>We created a couple of endpoints and called get_insight function from utils.py which we will cover after this.</p><p>Our main endpoints are:</p><ul><li>‘/’: It will serve as our data streaming API. It needs a request body that we will discuss in the part where we build our producer.</li><li>‘/insight’: It will serve as our Gemini AI insight on our listening history data. It pulls the data from our PostgreSQL. Write an HTML template and send it to Gemini AI to gain insight, recap, and recommendation. More detail in the next part.</li></ul><p>Also, copy and paste this into your utils.py</p><pre>import psycopg2<br>import os<br>from dotenv import load_dotenv<br>import json<br>from genai import generate_content<br>import smtplib<br>from email.mime.multipart import MIMEMultipart<br>from email.mime.text import MIMEText<br>import google.generativeai as genai<br><br>load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br><br>def get_db_connection():<br>    connection = psycopg2.connect(<br>        dbname=os.getenv(&#39;PSQL_DB_NAME&#39;),<br>        user=os.getenv(&#39;PSQL_DB_USER&#39;),<br>        password=os.getenv(&#39;PSQL_DB_PASSWORD&#39;),<br>        host=os.getenv(&#39;PSQL_DB_HOST&#39;),<br>        port=5432<br>    )<br>    return connection<br><br>def generate_content(html_body):<br>    api_key = os.getenv(&#39;GENAI_API_KEY&#39;)<br><br>    system_instruction = &quot;&quot;&quot;<br>    You are an expert music and/or song analyst.<br>    Given an html body, I need you to generate a weekly recap summary, mood recap, and recommendations. <br>    You are to start and end your response with a fun and engaging opening and closing statement.<br>    Your responses should be engaging and fun to read, also use emojis where necessary.<br>    For song name, and artist name in the recaps, wrap it inside &#39;&#39; as I don&#39;t want you to return a markdown.<br>    Your response should be returned in json format like this:<br>    {<br>        &quot;opening_statement&quot;: opening_statement,<br>        &quot;weekly_recap&quot;: weekly_recap,<br>        &quot;mood_recap&quot;: mood_recap,<br>        &quot;recommendations&quot;: recommendations,<br>        &quot;closing_statement&quot;: closing_statement<br>    }<br>    &quot;&quot;&quot;<br><br>    genai.configure(api_key=api_key)<br>    model = genai.GenerativeModel(<br>        model_name=&#39;gemini-1.5-pro-latest&#39;,<br>        system_instruction=system_instruction<br>    )<br>    try:<br>        response = model.generate_content(html_body)<br>        response_text = response.text<br>        if &#39;```json&#39; in response_text:<br>            response_text = response_text.replace(&#39;```json&#39;, &#39;&#39;)<br>        if &#39;```&#39; in response_text:<br>            response_text = response_text.replace(&#39;```&#39;, &#39;&#39;)<br>        print(response_text)<br>        return response_text<br>    except Exception as e:<br>        print(&quot;error: &quot;, e)<br>        return {&quot;error&quot;: str(e)}<br><br>def get_insight():<br>    conn = get_db_connection()<br>    cursor = conn.cursor()<br><br>    queries = [<br>        {<br>            &#39;name&#39;: &#39;Top 10 Songs&#39;, &#39;query&#39;: &quot;&quot;&quot;<br>                SELECT <br>                    ds.title AS song_name,<br>                    da.title AS album,<br>                    dar.name AS artist,<br>                    da.image_url AS album_art,<br>                    FLOOR(SUM(ds.duration_ms) / (1000 * 60)) AS total_minute_listened<br>                FROM <br>                    fact_history fh<br>                JOIN <br>                    dim_song ds ON fh.song_id = ds.song_id<br>                JOIN <br>                    dim_album da ON fh.album_id = da.album_id<br>                JOIN <br>                    dim_artist dar ON fh.artist_id = dar.artist_id<br>                WHERE <br>                    fh.played_at &gt;= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY) + INTERVAL &#39;00:01:00&#39; HOUR_SECOND<br>                    AND fh.played_at &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 5 DAY), INTERVAL &#39;08:59:00&#39; HOUR_SECOND)<br>                GROUP BY <br>                    ds.title, da.title, dar.name, da.image_url<br>                ORDER BY <br>                    total_minute_listened DESC<br>                LIMIT 10;<br>            &quot;&quot;&quot;<br>        },<br>        {<br>            &#39;name&#39;: &#39;Top 5 Albums&#39;, &#39;query&#39;: &quot;&quot;&quot;<br>                SELECT <br>                    da.title AS album,<br>                    dar.name AS artist,<br>                    da.image_url AS album_art,<br>                    FLOOR(SUM(ds.duration_ms) / (1000 * 60)) AS total_minute_listened<br>                FROM <br>                    fact_history fh<br>                JOIN <br>                    dim_album da ON fh.album_id = da.album_id<br>                JOIN <br>                    dim_artist dar ON fh.artist_id = dar.artist_id<br>                JOIN <br>                    dim_song ds ON fh.song_id = ds.song_id<br>                WHERE <br>                    fh.played_at &gt;= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY) + INTERVAL &#39;00:01:00&#39; HOUR_SECOND<br>                    AND fh.played_at &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 5 DAY), INTERVAL &#39;08:59:00&#39; HOUR_SECOND)<br>                GROUP BY <br>                    da.title, dar.name, da.image_url<br>                ORDER BY <br>                    total_minute_listened DESC<br>                LIMIT 5;<br>            &quot;&quot;&quot;<br>        },<br>        {<br>            &#39;name&#39;: &#39;Top 5 Artists&#39;, &#39;query&#39;: &quot;&quot;&quot;<br>                SELECT <br>                    dar.name AS artist,<br>                    FLOOR(SUM(ds.duration_ms) / (1000 * 60)) AS total_minute_listened<br>                FROM <br>                    fact_history fh<br>                JOIN <br>                    dim_artist dar ON fh.artist_id = dar.artist_id<br>                JOIN <br>                    dim_song ds ON fh.song_id = ds.song_id<br>                WHERE <br>                    fh.played_at &gt;= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY) + INTERVAL &#39;00:01:00&#39; HOUR_SECOND<br>                    AND fh.played_at &lt; DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 5 DAY), INTERVAL &#39;08:59:00&#39; HOUR_SECOND)<br>                GROUP BY <br>                    dar.name<br>                ORDER BY <br>                    total_minute_listened DESC<br>                LIMIT 5;<br>            &quot;&quot;&quot;<br>        },<br>        {<br>            &#39;name&#39;: &#39;Songs you haven\&#39;t listened to in a while&#39;, &#39;query&#39;: &quot;&quot;&quot;<br>            SELECT <br>                ds.title AS song_name,<br>                da.image_url AS album_art,<br>                MAX(fh.played_at) AS last_played,<br>                DATEDIFF(NOW(), MAX(fh.played_at)) AS days_ago<br>            FROM <br>                fact_history fh<br>            JOIN <br>                dim_song ds ON fh.song_id = ds.song_id<br>            JOIN <br>                dim_album da ON fh.album_id = da.album_id<br>            GROUP BY <br>                ds.title, da.image_url<br>            ORDER BY <br>                days_ago DESC<br>            LIMIT 10;<br>            &quot;&quot;&quot;<br>        }<br>    ]<br><br>    for query in queries:<br>        cursor.execute(query[&#39;query&#39;])<br>        results = cursor.fetchall()<br>        data = []<br>        for row in results:<br>            data.append(row)<br>        query[&#39;data&#39;] = data<br><br>    body = &quot;&quot;<br><br>    for query in queries:<br>        if query[&#39;data&#39;]:<br>            body += f&quot;&lt;h3&gt;{query[&#39;name&#39;]}&lt;/h3&gt;&quot;<br>            body += &quot;&lt;div class=&#39;card-container&#39;&gt;&quot;<br>            for row in query[&#39;data&#39;]:<br>                if query[&#39;name&#39;] == &#39;Top 10 Songs&#39;:<br>                    body += f&quot;&lt;div class=&#39;album-card&#39; style=&#39;background-image: url(https://medium.com/%7Brow[&#39;album_art&#39;]});&#39;&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;album-content&#39;&gt;&lt;div class=&#39;title&#39;&gt;{row[&#39;song_name&#39;]}&lt;/div&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;subtitle&#39;&gt;by {row[&#39;artist&#39;]}&lt;/div&gt;&lt;p&gt;{row[&#39;total_minute_listened&#39;]}m listened&lt;/p&gt;&lt;/div&gt;&quot;<br>                elif query[&#39;name&#39;] == &#39;Top 5 Albums&#39;:<br>                    body += f&quot;&lt;div class=&#39;album-card&#39; style=&#39;background-image: url(https://medium.com/%7Brow[&#39;album_art&#39;]});&#39;&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;album-content&#39;&gt;&lt;div class=&#39;title&#39;&gt;{row[&#39;album&#39;]}&lt;/div&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;subtitle&#39;&gt;by {row[&#39;artist&#39;]}&lt;/div&gt;&lt;p&gt;{row[&#39;total_minute_listened&#39;]}m listened&lt;/p&gt;&lt;/div&gt;&quot;<br>                elif query[&#39;name&#39;] == &#39;Top 5 Artists&#39;:<br>                    body += &quot;&lt;div class=&#39;card&#39;&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;artist-title&#39;&gt;{row[&#39;artist&#39;]}&lt;/div&gt;&quot;<br>                    body += f&quot;&lt;p&gt;{row[&#39;total_minute_listened&#39;]}m listened&lt;/p&gt;&quot;<br>                elif query[&#39;name&#39;] == &#39;Songs you haven\&#39;t listened to in a while&#39;:<br>                    body += f&quot;&lt;div class=&#39;album-card&#39; style=&#39;background-image: url(https://medium.com/%7Brow[&#39;album_art&#39;]});&#39;&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;album-content&#39;&gt;&lt;div class=&#39;title&#39;&gt;{row[&#39;song_name&#39;]}&lt;/div&gt;&quot;<br>                    body += f&quot;&lt;div class=&#39;subtitle&#39;&gt;was played {row[&#39;days_ago&#39;]} days ago&lt;/div&gt;&lt;/div&gt;&quot;<br>                body += &quot;&lt;/div&gt;&quot;<br>            body += &quot;&lt;/div&gt;&quot;<br>        else:<br>            body += f&quot;&lt;h3&gt;{query[&#39;name&#39;]}&lt;/h3&gt;&quot;<br>            body += &quot;&lt;p&gt;No data available&lt;/p&gt;&quot;<br><br>    ai_response = json.loads(generate_content(body))<br>    opening_statement = ai_response[&#39;opening_statement&#39;]<br>    closing_statement = ai_response[&#39;closing_statement&#39;]<br>    weekly_recap = ai_response[&#39;weekly_recap&#39;]<br>    mood_recap = ai_response[&#39;mood_recap&#39;]<br>    recommendations = ai_response[&#39;recommendations&#39;]<br><br>    html_string = f&quot;&quot;&quot;<br>    &lt;!DOCTYPE html&gt;<br>    &lt;html lang=&quot;en&quot;&gt;<br>    &lt;head&gt;<br>        &lt;meta charset=&quot;UTF-8&quot;&gt;<br>        &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1.0&quot;&gt;<br>        &lt;title&gt;Weekly Wrap&lt;/title&gt;<br>        &lt;style&gt;<br>            body {{<br>                font-family: &#39;Roboto&#39;, Arial, sans-serif;<br>                margin: 0;<br>                padding: 20px;<br>                background-color: #f4f4f4;<br>                color: #333;<br>            }}<br>            .container {{<br>                max-width: 800px;<br>                margin: 0 auto;<br>                background-color: #fff;<br>                padding: 20px;<br>                border-radius: 10px;<br>                box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);<br>            }}<br>            .hero {{<br>                text-align: justify;<br>                margin-bottom: 40px;<br>            }}<br>            h1 {{<br>                color: #007bff;<br>                margin-bottom: 10px;<br>            }}<br>            h2 {{<br>                color: #555;<br>                margin-top: 40px;<br>                border-bottom: 2px solid #007bff;<br>                padding-bottom: 5px;<br>            }}<br>            h3 {{<br>                margin-top: 20px;<br>                color: #333;<br>                border-bottom: 1px solid #ddd;<br>                padding-bottom: 5px;<br>            }}<br>            .card-container {{<br>                display: flex;<br>                flex-wrap: wrap;<br>                gap: 20px;<br>                margin-bottom: 20px;<br>                justify-content: space-around;<br>            }}<br>            .card {{<br>                background-color: #fff;<br>                border: 1px solid #ddd;<br>                border-radius: 8px;<br>                box-shadow: 0 2px 5px rgba(0, 0, 0, 0.1);<br>                padding: 15px;<br>                width: calc(33.333% - 20px);<br>                box-sizing: border-box;<br>                transition: transform 0.3s, box-shadow 0.3s;<br>                display: flex;<br>                flex-direction: column;<br>                justify-content: space-between;<br>                text-align: center;<br>            }}<br>            .card:hover, .album-card:hover {{<br>                transform: translateY(-5px);<br>                box-shadow: 0 4px 10px rgba(0, 0, 0, 0.15);<br>            }}<br>            .card-image img {{<br>                max-width: 100%;<br>                border-radius: 5px;<br>            }}<br>            .card-content {{<br>                margin-top: 10px;<br>                font-size: 14px;<br>            }}<br>            .album-card {{<br>                background-size: cover;<br>                background-position: center;<br>                border-radius: 8px;<br>                color: #fff;<br>                display: flex;<br>                flex-direction: column;<br>                justify-content: center;<br>                align-items: center;<br>                text-align: center;<br>                padding: 20px;<br>                position: relative;<br>                width: calc(33.333% - 20px);<br>                box-sizing: border-box;<br>                box-shadow: 0 2px 5px rgba(0, 0, 0, 0.1);<br>                transition: transform 0.3s, box-shadow 0.3s;<br>                overflow: hidden;<br>                margin-bottom: 20px;<br>            }}<br>            .album-card::after {{<br>                content: &quot;&quot;;<br>                position: absolute;<br>                top: 0;<br>                left: 0;<br>                width: 100%;<br>                height: 100%;<br>                background: rgba(0, 0, 0, 0.5);<br>                border-radius: 8px;<br>            }}<br>            .album-content {{<br>                position: relative;<br>                z-index: 1;<br>                font-size: 14px;<br>            }}<br>            .title {{<br>                font-size: 18px;<br>                font-weight: bold;<br>                color: #fff;<br>                text-align: center;<br>                margin: 5px 0;<br>            }}<br>            .artist-title {{<br>                font-size: 18px;<br>                font-weight: bold;<br>                color: #000;<br>                text-align: center;<br>                margin: 5px 0;<br>            }}<br>            .subtitle {{<br>                font-size: 16px;<br>                color: #ddd;<br>                text-align: center;<br>                margin: 5px 0;<br>            }}<br>            .recap, .recommendations, .riddle {{<br>                padding: 15px;<br>                border-left: 4px solid;<br>                margin-bottom: 20px;<br>                font-size: 16px;<br>            }}<br>            .recap {{<br>                background-color: #e9f7ef;<br>                border-color: #28a745;<br>            }}<br>            .recommendations {{<br>                background-color: #e3f2fd;<br>                border-color: #007bff;<br>            }}<br>        &lt;/style&gt;<br>    &lt;/head&gt;<br>    &lt;body&gt;<br>        &lt;div class=&quot;container&quot;&gt;<br>            &lt;div class=&quot;hero&quot;&gt;<br>                &lt;h1&gt;Hey 👋&lt;/h1&gt;<br>                &lt;p&gt;{opening_statement}&lt;/p&gt;<br>            &lt;/div&gt;<br><br>            &lt;h2&gt;Recaps&lt;/h2&gt;<br>            &lt;div class=&quot;recap&quot;&gt;<br>                &lt;p&gt;{weekly_recap}&lt;/p&gt;<br>                &lt;p&gt;{mood_recap}&lt;/p&gt;<br>            &lt;/div&gt;<br>    &quot;&quot;&quot;<br><br>    html_string += body<br><br>    html_string += f&quot;&quot;&quot;<br>            &lt;h2&gt;Recommendations&lt;/h2&gt;<br>            &lt;div class=&quot;recommendations&quot;&gt;<br>                &lt;p&gt;{recommendations}&lt;/p&gt;<br>            &lt;/div&gt;<br>            &lt;p&gt;{closing_statement}&lt;/p&gt;<br>        &lt;/div&gt;<br>    &lt;/body&gt;<br>    &lt;/html&gt;<br>    &quot;&quot;&quot;<br><br>    cursor.close()<br>    conn.close()<br>    send_email(html_string)<br>    return &#39;Success&#39;<br><br>def send_email(html_content):<br>    # Email configuration<br>    sender_email = &quot;Sender email address&quot;<br>    receiver_email = &quot;Receiver email address&quot;<br>    password = os.getenv(&quot;GOOGLE_APP_PASSWORD&quot;)<br>    smtp_server = &quot;smtp.gmail.com&quot;<br>    smtp_port = 587  <br><br>    msg = MIMEMultipart(&#39;alternative&#39;)<br>    msg[&#39;Subject&#39;] = &quot;Your Weekly Listening Wrap&quot;<br>    msg[&#39;From&#39;] = sender_email<br>    msg[&#39;To&#39;] = receiver_email<br><br>    # Create the HTML part of the message<br>    html_part = MIMEText(html_content, &#39;html&#39;)<br><br>    # Attach HTML content to the email<br>    msg.attach(html_part)<br><br>    # Connect to SMTP server and send email<br>    with smtplib.SMTP(smtp_server, smtp_port) as server:<br>        server.starttls()  # Secure the connection<br>        server.login(sender_email, password)<br>        server.sendmail(sender_email, receiver_email, msg.as_string())</pre><p>This script handles most of the work regarding our Weekly Wrapped feature. It pulls the listening data from our PostgreSQL DB, generates a populated HTML with our data, requests analysis to Gemini with a custom instruction, and then sends email from and to an email of our choosing.</p><p>Let’s run our web server. Copy and paste this into your terminal:</p><pre>flask run --reload</pre><p>Your terminal should show something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/546/1*pARm7ayHI1chIObxijZVLw.png" /></figure><p>Don’t worry about the warning. If you decide to go on a production environment though, you might want to use Gunicorn to start the webserver.</p><p>Once our webserver is running. Let’s go ahead and access <a href="https://127.0.0.1:5000/login"><em>https://127.0.0.1:5000/login</em></a><em> </em>and it will redirect you to the Spotify login page as we do not have the access_token yet. Once logged in, you will be redirected to the home page ‘/’ which will return an error as it only accepts a POST request. No worries.</p><p>Now you may notice there is a new file ‘token_info.json’ inside your flask backend. It is generated by one of the functions that is running after we log into Spotify so that we only need to log into Spotify once. It has expired_at but do not worry. If we keep on running the script and calling the API, it will regularly check if our access_token is nearly expired, if so, it will call the refresh_access_token function and it will regenerate it for us.</p><p>Now if you try making a POST request to the endpoint, you might not get any data. It’s because we set to call the Spotify API with param ‘after’ and give its value with <em>int(time.time()) * 1000 </em>which is now in unix millisecond. Worry not, we will handle it in the Kafka producer script.</p><p>That’s a wrap on setting our Flask webserver.</p><h3>Setting up Kafka Broker</h3><p>This part is straightforward. Ensure you have docker running in your machine and just go into the Kafka-clusters folder and run this in your terminal:</p><pre>docker compose up -d --build</pre><p>It will build the necessary images and spin up all the containers. It may take a few minutes if this is your first time running it. Once it&#39;s done, you can open up <a href="http://127.0.0.1:9021/"><em>http://127.0.0.1:9021</em></a><em> </em>and access the Confluent Control Center.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/1*BXoie8DKAuCAXJzssSbJiA.png" /></figure><p>Let’s start by adding new topics. Click on the <em>controlcenter.cluster. </em>On the side panel, click on Topics, and click on Add Topic</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/245/1*-_9XfL-fPwXaddMqAJTFZw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/178/1*uZp2Li-ZJWQcDlflTo74aw.png" /></figure><p>We need 4 topics on this project for each dimension and fact table:</p><ul><li>album_topic</li><li>artist_topic</li><li>song_topic</li><li>item_topic</li></ul><p>Item topic will serve as our fact table topic. The rest is for the dimensional table.</p><p>Fill in the topic name form accordingly and click on <em>Create Default.</em> Repeat the process for each topic needed.</p><p>Once you’re done, your topics page should look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/448/1*zVoEbTzrUutuShRBL096iQ.png" /></figure><p>You can click on the topic and see its configuration, messages coming in, generate messages, and schema. I haven’t fully understood schema in Kafka yet, so I will keep it as it is for now.</p><p>And that is a wrap for setting up Kafka Clusters. Let’s jump to the next part.</p><h3>Setting up the Producer, Consumer, and Spark Streaming process</h3><p>This is the fun part. We’re gonna be building an SQLalchemy model, a script for the producer, 4 scripts for each topic consumer, and processing it using spark streaming.</p><p>I know there’s a way to consume all topics at once, but I haven’t managed to successfully build it yet. So uh, yeah, 4 scripts for each topic. Let’s get right on it.</p><p>Let’s start by building our SQLAlchemy model first. It will help us navigate our database and value checking.</p><p>Copy and paste this into your models.py:</p><pre>from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, BigInteger, Boolean<br>from sqlalchemy.ext.declarative import declarative_base<br><br>Base = declarative_base()<br><br># Define the schema for the tables<br>class DimArtist(Base):<br>    __tablename__ = &#39;dim_artist&#39;<br>    artist_id = Column(String, primary_key=True)<br>    name = Column(String, nullable=False)<br>    external_url = Column(String, nullable=False)<br>    follower_count = Column(Integer, nullable=False)<br>    image_url = Column(String, nullable=False)<br>    popularity = Column(Integer, nullable=False)<br><br>class DimAlbum(Base):<br>    __tablename__ = &#39;dim_album&#39;<br>    album_id = Column(String, primary_key=True)<br>    title = Column(String, nullable=False)<br>    total_tracks = Column(Integer, nullable=False)<br>    release_date=Column(DateTime, nullable=False)<br>    external_url = Column(String, nullable=False)<br>    image_url = Column(String, nullable=False)<br>    label = Column(String, nullable=False)<br>    popularity = Column(Integer, nullable=False)<br><br>class DimSong(Base):<br>    __tablename__ = &#39;dim_song&#39;<br>    song_id = Column(String, primary_key=True)<br>    title = Column(String, nullable=False)<br>    disc_number = Column(Integer, nullable=False)<br>    duration_ms = Column(BigInteger, nullable=False)<br>    explicit = Column(Boolean, nullable=False)<br>    external_url = Column(String, nullable=False)<br>    preview_url = Column(String, nullable=False)<br>    popularity = Column(Integer, nullable=False)<br><br>class FactHistory(Base):<br>    __tablename__ = &#39;fact_history&#39;<br>    listening_id = Column(Integer, primary_key=True, autoincrement=True)<br>    played_at = Column(DateTime, nullable=False)<br>    song_id = Column(String, ForeignKey(&#39;dim_song.song_id&#39;), nullable=False)<br>    album_id = Column(String, ForeignKey(&#39;dim_album.album_id&#39;), nullable=False)<br>    artist_id = Column(String, ForeignKey(&#39;dim_artist.artist_id&#39;), nullable=False)</pre><p>This model represents 3-dimensional table (dim_song, dim_album, dim_artist) and 1 fact table (fact_history). I made it this way to follow Kimball principles in database design and it follows star schema.</p><p>If you’re interested in Data Engineering, you might want to check out this book</p><p><a href="https://www.dedp.online/">Book: Data Engineering Design Patterns (DEDP): Timeless Practices for Data Engineers</a></p><p>Next, we’ll build our producer. Copy and paste this into your producer.py</p><pre>import requests<br>import time<br>from spotipy import Spotify<br>from spotipy.oauth2 import SpotifyClientCredentials<br>from confluent_kafka import Producer, KafkaException<br>from dotenv import load_dotenv<br>import os<br>import json<br>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br>from models import Base, DimAlbum, DimArtist, DimSong<br>from sqlalchemy.exc import OperationalError<br><br>#read config from client.properties for kafka producer<br>def read_config():<br>    config = {}<br>    try:<br>        with open(&quot;client.properties&quot;) as fh:<br>            for line in fh:<br>                line = line.strip()<br>                if len(line) != 0 and line[0] != &quot;#&quot;:<br>                    parameter, value = line.strip().split(&#39;=&#39;, 1)<br>                    config[parameter] = value.strip()<br>    except FileNotFoundError:<br>        print(&quot;Error: Configuration file &#39;client.properties&#39; not found.&quot;)<br>    except Exception as e:<br>        print(f&quot;Error reading configuration file: {e}&quot;)<br>    return config<br><br>#fetch artist data from spotify api using spotipy<br>def fetch_artist_data(artist_id: str) -&gt; dict:<br>    try:<br>        response = sp.artist(artist_id)<br>    except requests.exceptions.ReadTimeout:<br>        print(f&quot;Timeout occurred for artist_id: {artist_id}. Retrying...&quot;)<br>        response = sp.artist(artist_id)<br>    data = {<br>        &quot;artist_id&quot;: artist_id,<br>        &quot;name&quot;: response[&#39;name&#39;],<br>        &quot;external_url&quot;: response[&#39;external_urls&#39;][&#39;spotify&#39;],<br>        &quot;follower_count&quot;: response[&#39;followers&#39;][&#39;total&#39;],<br>        &quot;image_url&quot;: response[&#39;images&#39;][0][&#39;url&#39;],<br>        &quot;popularity&quot;: response[&#39;popularity&#39;],<br>    }<br>    return data<br><br>#fetch album data from spotify api using spotipy<br>def fetch_album_data(album_id: str) -&gt; dict:<br>    try:<br>        response = sp.album(album_id)<br>    except requests.exceptions.ReadTimeout:<br>        print(f&quot;Timeout occurred for album_id: {album_id}. Retrying...&quot;)<br>        response = sp.album(album_id)<br>    data = {<br>        &quot;album_id&quot;: album_id,<br>        &quot;title&quot;: response[&#39;name&#39;],<br>        &quot;total_tracks&quot;: response[&#39;total_tracks&#39;],<br>        &quot;release_date&quot;: response[&#39;release_date&#39;],<br>        &quot;external_url&quot;: response[&#39;external_urls&#39;][&#39;spotify&#39;],<br>        &quot;image_url&quot;: response[&#39;images&#39;][0][&#39;url&#39;],<br>        &quot;label&quot;: response[&#39;label&#39;],<br>        &quot;popularity&quot;: response[&#39;popularity&#39;],<br>    }<br>    return data<br><br>#fetch song data from spotify api using spotipy<br>def fetch_song_data(song_id: str) -&gt; dict:<br>    try:<br>        response = sp.track(song_id)<br>    except requests.exceptions.ReadTimeout:<br>        print(f&quot;Timeout occurred for song_id: {song_id}. Retrying...&quot;)<br>        response = sp.track(song_id)<br>    data = {<br>        &quot;song_id&quot;: song_id,<br>        &quot;title&quot;: response[&#39;name&#39;],<br>        &quot;disc_number&quot;: response[&#39;disc_number&#39;],<br>        &quot;duration_ms&quot;: response[&#39;duration_ms&#39;],<br>        &quot;explicit&quot;: response[&#39;explicit&#39;],<br>        &quot;external_url&quot;: response[&#39;external_urls&#39;][&#39;spotify&#39;],<br>        &quot;preview_url&quot;: response[&#39;preview_url&#39;] if &#39;preview_url&#39; in response else &#39;No preview available&#39;,<br>        &quot;popularity&quot;: response[&#39;popularity&#39;],<br>    }<br>    return data<br><br>#produce data to kafka topic<br>def produce_to_kafka(data, config, topic):<br>    producer = Producer(config)<br><br>    def delivery_callback(err, msg):<br>        if err:<br>            print(f&#39;ERROR: Message failed delivery: {err}&#39;)<br>        else:<br>            key = msg.key().decode(&#39;utf-8&#39;) if msg.key() else None<br>            value = msg.value().decode(&#39;utf-8&#39;) if msg.value() else None<br>            print(f&quot;Produced event to topic {msg.topic()}: key = {key} value = {value}&quot;)<br><br>    try:<br>        producer.produce(topic, value=json.dumps(data).encode(&#39;utf-8&#39;), callback=delivery_callback)<br>        producer.flush()<br>        print(f&quot;Produced data to topic {topic}\n&quot;)<br>    except KafkaException as e:<br>        print(f&quot;Error producing to Kafka: {e}&quot;)<br><br>if __name__ == &#39;__main__&#39;:<br>    config = read_config()<br>    load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br>    <br>    #spotify initialization<br>    client_id = os.getenv(&#39;SPOTIFY_CLIENT_ID&#39;)<br>    client_secret = os.getenv(&#39;SPOTIFY_CLIENT_SECRET&#39;)<br>    sp = Spotify(client_credentials_manager=SpotifyClientCredentials(client_id=client_id, client_secret=client_secret))<br>    <br>    #psql initialization<br>    psql_link = os.getenv(&#39;PSQL_DB_LINK&#39;)<br>    engine = create_engine(psql_link)<br>    Session = sessionmaker(bind=engine)<br>    <br>    #create all table if not exist<br>    Base.metadata.create_all(engine)<br>    <br>    # Create a single session<br>    session = Session()<br>    <br>    #endpoint initialization<br>    endpoint_url = &#39;http://127.0.0.1:5000&#39;<br>    last_fetched = int(time.time()) * 1000<br>    <br>    try:<br>        while True:<br>            now = int(time.time()) * 1000<br>            data = {<br>                &#39;after&#39;: last_fetched<br>            }<br>            try:<br>                response = requests.post(endpoint_url, json=data).json()<br>                if response[&#39;status_code&#39;] == 204:<br>                    print(response)<br>                elif response[&#39;status_code&#39;] == 200:<br>                    for item in response[&#39;items&#39;]:<br>                        song_data = session.query(DimSong).filter_by(song_id=item[&#39;song_id&#39;]).first()<br>                        if not song_data:<br>                            produce_to_kafka(fetch_song_data(item[&#39;song_id&#39;]), config, &#39;song_topic&#39;)<br>                        album_data = session.query(DimAlbum).filter_by(album_id=item[&#39;album_id&#39;]).first()<br>                        if not album_data:<br>                            produce_to_kafka(fetch_album_data(item[&#39;album_id&#39;]), config, &#39;album_topic&#39;)<br>                        artist_data = session.query(DimArtist).filter_by(artist_id=item[&#39;artist_id&#39;]).first()<br>                        if not artist_data:<br>                            produce_to_kafka(fetch_artist_data(item[&#39;artist_id&#39;]), config, &#39;artist_topic&#39;)<br>                        item_data = {<br>                            &#39;song_id&#39;: item[&#39;song_id&#39;],<br>                            &#39;album_id&#39;: item[&#39;album_id&#39;],<br>                            &#39;artist_id&#39;: item[&#39;artist_id&#39;],<br>                            &#39;played_at&#39;: item[&#39;played_at&#39;]<br>                        }<br>                        produce_to_kafka(item_data, config, &#39;item_topic&#39;)<br>            except OperationalError as e:<br>                print(f&quot;Database operation failed: {e}&quot;)<br>                session.rollback()<br><br>            last_fetched = now <br>            time.sleep(60)<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        session.close()<br>        engine.dispose()<br>        print(&quot;All sessions closed and resources released.&quot;)</pre><p>In this code, we define a couple of functions:</p><ul><li>read_config(): for loading the config file client.properties that we need for producing the message.</li><li>produce_to_kafka(): for producing the message to the kafka topic. It requires config, data, and topic for the parameter.</li><li>fetch_album_data(), fetch_artist_data(), fetch_song_data(): Each function correspond to its name. It will gather the necessary data using its id as the parameter and call the Spotify API to get the required data that we need to populate the corresponding dimension table.</li></ul><p>We set the entry point below the if __name__ = &quot;__main__&quot; and prepared the necessary variables. Including config for Kafka, Spotify client id and secret, and psql link. We also initialize Spotify and SQLAlchemy engine and session and also create our defined table if not exist.</p><pre>    endpoint_url = &#39;http://127.0.0.1:5000&#39;<br>    last_fetched = int(time.time()) * 1000<br>    <br>    try:<br>        while True:<br>            now = int(time.time()) * 1000<br>            data = {<br>                &#39;after&#39;: last_fetched<br>            }<br>            try:<br>                response = requests.post(endpoint_url, json=data).json()<br>                if response[&#39;status_code&#39;] == 204:<br>                    print(response)<br>                elif response[&#39;status_code&#39;] == 200:<br>                    for item in response[&#39;items&#39;]:<br>                        song_data = session.query(DimSong).filter_by(song_id=item[&#39;song_id&#39;]).first()<br>                        if not song_data:<br>                            produce_to_kafka(fetch_song_data(item[&#39;song_id&#39;]), config, &#39;song_topic&#39;)<br>                        album_data = session.query(DimAlbum).filter_by(album_id=item[&#39;album_id&#39;]).first()<br>                        if not album_data:<br>                            produce_to_kafka(fetch_album_data(item[&#39;album_id&#39;]), config, &#39;album_topic&#39;)<br>                        artist_data = session.query(DimArtist).filter_by(artist_id=item[&#39;artist_id&#39;]).first()<br>                        if not artist_data:<br>                            produce_to_kafka(fetch_artist_data(item[&#39;artist_id&#39;]), config, &#39;artist_topic&#39;)<br>                        item_data = {<br>                            &#39;song_id&#39;: item[&#39;song_id&#39;],<br>                            &#39;album_id&#39;: item[&#39;album_id&#39;],<br>                            &#39;artist_id&#39;: item[&#39;artist_id&#39;],<br>                            &#39;played_at&#39;: item[&#39;played_at&#39;]<br>                        }<br>                        produce_to_kafka(item_data, config, &#39;item_topic&#39;)<br>            except OperationalError as e:<br>                print(f&quot;Database operation failed: {e}&quot;)<br>                session.rollback()<br><br>            last_fetched = now <br>            time.sleep(60)<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        session.close()<br>        engine.dispose()<br>        print(&quot;All sessions closed and resources released.&quot;)</pre><p>In this part, we set the endpoint for fetching our listening history data, which was the root endpoint that we set earlier with Flask, and we set a variable last_fetched with the current timestamp. It will be updated dynamically as the script is running.</p><p>Inside the try block, we run an infinite loop which will timeout for a minute for every loop. It ensures the data is continuously streaming. Then we send a POST request to our API, providing last_fetchedin the request body to get only the data in which the timestamp is bigger than last_fetched. If the response returns 204, it means there is no data and we will simply print the response. If it returns 200, it means there is data and we will get the items (in this case the listening history data) array, and for each item, we check our dimension table if song_id, album_id, and artist_id exist or not, if not, we fetch the necessary data using the fetch function that we defined earlier, and then send the data to our dim topics then send the fact data to our fact topic. If the check passes, it will only send the fact data to the fact topic.</p><p>Finally, we set the last_fetched variable with the value from the variable now . So the next API call will only get the data where recent_played is bigger than 1 minute before the current time. We may not always get new data as an average song lasts for 3 minutes. But it ensures we won’t miss a thing.</p><p>Next, let’s set up our consumer.</p><p>Copy these consumers into your corresponding consumer file</p><pre># spark-consumer-album.py<br><br>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import from_json, col, when, to_date<br>from pyspark.sql.types import StructType, StructField, StringType, IntegerType<br>from dotenv import load_dotenv<br>import os<br>import findspark<br><br>def get_schemas():<br>    album_schema = StructType([<br>        StructField(&quot;album_id&quot;, StringType(), True),<br>        StructField(&quot;title&quot;, StringType(), True),<br>        StructField(&quot;total_tracks&quot;, IntegerType(), True),<br>        StructField(&quot;release_date&quot;, StringType(), True),<br>        StructField(&quot;external_url&quot;, StringType(), True),<br>        StructField(&quot;image_url&quot;, StringType(), True),<br>        StructField(&quot;label&quot;, StringType(), True),<br>        StructField(&quot;popularity&quot;, IntegerType(), True)<br>    ])<br><br>    return {<br>        &quot;album_topic&quot;: album_schema<br>    }<br><br>def write_to_postgres(batch_df, batch_id):<br>    properties = {<br>&quot;user&quot;: os.getenv(&#39;PSQL_DB_USER&#39;),<br>        &quot;password&quot;: os.getenv(&#39;PSQL_DB_PASSWORD&#39;),<br>        &quot;driver&quot;: &quot;org.postgresql.Driver&quot;,<br>        &#39;url&#39;: os.getenv(&#39;PSQL_DB_LINK&#39;),<br>        &quot;dbtable&quot;: &quot;dim_album&quot;,<br>    }<br>    <br>    if not batch_df.isEmpty():<br>        batch_df.write.format(&#39;jdbc&#39;).options(**properties).mode(&#39;append&#39;).save()<br><br>def create_spark_session():<br>    return SparkSession.builder \<br>        .appName(&quot;album-topic-spark-consumer&quot;) \<br>        .config(&quot;spark.jars&quot;, jdbc_jar) \<br>        .config(&quot;spark.jars.packages&quot;, &quot;org.apache.spark:spark-sql-kafka-0-10_2.12:3.3.2&quot;) \<br>        .getOrCreate()<br><br>def read_from_kafka(spark, kafka_bootstrap_servers, topics):<br>    return spark.readStream \<br>        .format(&quot;kafka&quot;) \<br>        .option(&quot;kafka.bootstrap.servers&quot;, kafka_bootstrap_servers) \<br>        .option(&quot;subscribe&quot;, topics) \<br>        .load()<br><br>def parse_json_data(df, schemas):<br>    album_df = df.filter(col(&quot;topic&quot;) == &quot;album_topic&quot;) \<br>                 .withColumn(&quot;parsed_data&quot;, from_json(col(&quot;json_value&quot;), schemas[&quot;album_topic&quot;])) \<br>                 .select(col(&quot;parsed_data.*&quot;))<br><br>    # Convert date fields to appropriate types<br>    album_df = album_df.withColumn(&quot;release_date&quot;, to_date(col(&quot;release_date&quot;)))<br><br>    return album_df<br><br>def main():<br>    schemas = get_schemas()<br>    spark = create_spark_session()<br>    kafka_bootstrap_servers = &quot;localhost:9092&quot;<br>    topics = &quot;album_topic&quot;  # Only consume album_topic<br><br>    df = read_from_kafka(spark, kafka_bootstrap_servers, topics)<br>    df = df.selectExpr(&quot;CAST(value AS STRING) as json_value&quot;, &quot;topic&quot;)<br>    df = parse_json_data(df, schemas)<br><br>    def process_batch(batch_df, batch_id):<br>        batch_df.printSchema()<br>        batch_df.show()<br>        write_to_postgres(batch_df, batch_id)<br><br>    query = df.writeStream \<br>        .foreachBatch(process_batch) \<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>    try:<br>        query.awaitTermination()<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        query.stop()<br>        print(&quot;Query stopped.&quot;)<br>        spark.stop()<br>        print(&quot;Spark session stopped.&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    spark_home = &quot;your spark home directory&quot;<br>    jdbc_jar = f&quot;{spark_home}/jars/PostgreSQL-42.7.0.jar&quot;<br>    findspark.init(spark_home)<br>    load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br>    main()</pre><pre># spark-consumer-artist.py<br><br>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import from_json, col, when, to_date<br>from pyspark.sql.types import StructType, StructField, StringType, IntegerType<br>from dotenv import load_dotenv<br>import os<br>import findspark<br><br>def get_schemas():<br>    artist_schema = StructType([<br>        StructField(&quot;artist_id&quot;, StringType(), True),<br>        StructField(&quot;name&quot;, StringType(), True),<br>        StructField(&quot;external_url&quot;, StringType(), True),<br>        StructField(&quot;follower_count&quot;, IntegerType(), True),<br>        StructField(&quot;image_url&quot;, StringType(), True),<br>        StructField(&quot;popularity&quot;, IntegerType(), True)<br>    ])<br><br>    return {<br>        &quot;artist_topic&quot;: artist_schema<br>    }<br><br>def write_to_postgres(batch_df, batch_id):<br>    properties = {<br>        &quot;user&quot;: os.getenv(&#39;PSQL_DB_USER&#39;),<br>        &quot;password&quot;: os.getenv(&#39;PSQL_DB_PASSWORD&#39;),<br>        &quot;driver&quot;: &quot;org.postgresql.Driver&quot;,<br>        &#39;url&#39;: os.getenv(&#39;PSQL_DB_LINK&#39;),<br>        &quot;dbtable&quot;: &quot;dim_artist&quot;,<br>    }<br>    <br>    if not batch_df.isEmpty():<br>        batch_df.write.format(&#39;jdbc&#39;).options(**properties).mode(&#39;append&#39;).save()<br><br>def create_spark_session():<br>    return SparkSession.builder \<br>        .appName(&quot;artist-topic-spark-consumer&quot;) \<br>        .config(&quot;spark.jars&quot;, jdbc_jar) \<br>        .config(&quot;spark.jars.packages&quot;, &quot;org.apache.spark:spark-sql-kafka-0-10_2.12:3.3.2&quot;) \<br>        .getOrCreate()<br><br>def read_from_kafka(spark, kafka_bootstrap_servers, topics):<br>    return spark.readStream \<br>        .format(&quot;kafka&quot;) \<br>        .option(&quot;kafka.bootstrap.servers&quot;, kafka_bootstrap_servers) \<br>        .option(&quot;subscribe&quot;, topics) \<br>        .load()<br><br>def parse_json_data(df, schemas):<br>    artist_df = df.filter(col(&quot;topic&quot;) == &quot;artist_topic&quot;) \<br>                 .withColumn(&quot;parsed_data&quot;, from_json(col(&quot;json_value&quot;), schemas[&quot;artist_topic&quot;])) \<br>                 .select(col(&quot;parsed_data.*&quot;))<br><br>    return artist_df<br><br>def main():<br>    schemas = get_schemas()<br>    spark = create_spark_session()<br>    kafka_bootstrap_servers = &quot;localhost:9092&quot;<br>    topics = &quot;artist_topic&quot;  # Only consume artist_topic<br><br>    df = read_from_kafka(spark, kafka_bootstrap_servers, topics)<br>    df = df.selectExpr(&quot;CAST(value AS STRING) as json_value&quot;, &quot;topic&quot;)<br>    df = parse_json_data(df, schemas)<br><br>    def process_batch(batch_df, batch_id):<br>        batch_df.printSchema()<br>        batch_df.show()<br>        write_to_postgres(batch_df, batch_id)<br><br>    query = df.writeStream \<br>        .foreachBatch(process_batch) \<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>    try:<br>        query.awaitTermination()<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        query.stop()<br>        print(&quot;Query stopped.&quot;)<br>        spark.stop()<br>        print(&quot;Spark session stopped.&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    spark_home = &quot;your spark home directory&quot;<br>    jdbc_jar = f&quot;{spark_home}/jars/PostgreSQL-42.7.0.jar&quot;<br>    findspark.init(spark_home)<br>    load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br>    main()</pre><pre># spark-consumer-song.py<br><br>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import from_json, col, when, to_date<br>from pyspark.sql.types import StructType, StructField, StringType, IntegerType, BooleanType<br>from dotenv import load_dotenv<br>import os<br>import findspark<br><br>def get_schemas():<br>    song_schema = StructType([<br>        StructField(&quot;song_id&quot;, StringType(), True),<br>        StructField(&quot;title&quot;, StringType(), True),<br>        StructField(&quot;disc_number&quot;, IntegerType(), True),<br>        StructField(&quot;duration_ms&quot;, IntegerType(), True),<br>        StructField(&quot;explicit&quot;, BooleanType(), True),<br>        StructField(&quot;external_url&quot;, StringType(), True),<br>        StructField(&quot;preview_url&quot;, StringType(), True),<br>        StructField(&quot;popularity&quot;, IntegerType(), True)<br>    ])<br><br>    return {<br>        &quot;song_topic&quot;: song_schema<br>    }<br><br>def write_to_postgres(batch_df, batch_id):<br>    properties = {<br>        &quot;user&quot;: os.getenv(&#39;POSTGRES_USER&#39;),<br>        &quot;password&quot;: os.getenv(&#39;POSTGRES_PASSWORD&#39;),<br>        &quot;driver&quot;: &quot;org.postgresql.Driver&quot;,<br>        &#39;url&#39;: os.getenv(&#39;POSTGRES_URL&#39;),<br>        &quot;dbtable&quot;: &quot;dim_song&quot;,<br>    }<br>    <br>    if not batch_df.isEmpty():<br>        batch_df.write.format(&#39;jdbc&#39;).options(**properties).mode(&#39;append&#39;).save()<br><br>def create_spark_session():<br>    return SparkSession.builder \<br>        .appName(&quot;song-topic-spark-consumer&quot;) \<br>        .config(&quot;spark.jars&quot;, jdbc_jar) \<br>        .config(&quot;spark.jars.packages&quot;, &quot;org.apache.spark:spark-sql-kafka-0-10_2.12:3.3.2&quot;) \<br>        .getOrCreate()<br><br>def read_from_kafka(spark, kafka_bootstrap_servers, topics):<br>    return spark.readStream \<br>        .format(&quot;kafka&quot;) \<br>        .option(&quot;kafka.bootstrap.servers&quot;, kafka_bootstrap_servers) \<br>        .option(&quot;subscribe&quot;, topics) \<br>        .load()<br><br>def parse_json_data(df, schemas):<br>    song_df = df.filter(col(&quot;topic&quot;) == &quot;song_topic&quot;) \<br>                 .withColumn(&quot;parsed_data&quot;, from_json(col(&quot;json_value&quot;), schemas[&quot;song_topic&quot;])) \<br>                 .select(col(&quot;parsed_data.*&quot;))<br><br>    return song_df<br><br>def main():<br>    schemas = get_schemas()<br>    spark = create_spark_session()<br>    kafka_bootstrap_servers = &quot;localhost:9092&quot;<br>    topics = &quot;song_topic&quot;  # Only consume song_topic<br><br>    df = read_from_kafka(spark, kafka_bootstrap_servers, topics)<br>    df = df.selectExpr(&quot;CAST(value AS STRING) as json_value&quot;, &quot;topic&quot;)<br>    df = parse_json_data(df, schemas)<br><br>    def process_batch(batch_df, batch_id):<br>        batch_df.printSchema()<br>        batch_df.show()<br>        write_to_postgres(batch_df, batch_id)<br><br>    query = df.writeStream \<br>        .foreachBatch(process_batch) \<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>    try:<br>        query.awaitTermination()<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        query.stop()<br>        print(&quot;Query stopped.&quot;)<br>        spark.stop()<br>        print(&quot;Spark session stopped.&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    spark_home = &quot;your spark home directory&quot;<br>    jdbc_jar = f&quot;{spark_home}/jars/PostgreSQL-42.7.0.jar&quot;<br>    findspark.init(spark_home)<br>    load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br>    main()</pre><p>Those codes run spark streaming to read data from each topic. It starts off by assigning spark_home and jar directory and loading environment variables then starting the main function.<br>The main function will first get the schema corresponding to the table, creating a spark session, then assign df to read the stream coming from the Kafka topic, it will then parse the data using the parse_json_data function and then print the schema, showing the data, and ingesting into our PostgreSQL database.</p><p>Afterward, copy this code into your spark-consumer-history.py</p><pre># spark-consumer-history.py<br><br>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import from_json, col, when, to_date, to_timestamp<br>from pyspark.sql.types import StructType, StructField, StringType<br>from dotenv import load_dotenv<br>import os<br>import findspark<br>import time<br><br>def get_schemas():<br>    item_schema = StructType([<br>        StructField(&quot;song_id&quot;, StringType(), True),<br>        StructField(&quot;album_id&quot;, StringType(), True),<br>        StructField(&quot;artist_id&quot;, StringType(), True),<br>        StructField(&quot;played_at&quot;, StringType(), True)<br>    ])<br><br>    return {<br>        &quot;item_topic&quot;: item_schema<br>    }<br><br>def write_to_postgres(batch_df, batch_id):<br>    properties = {<br>        &quot;user&quot;: os.getenv(&#39;POSTGRES_USER&#39;),<br>        &quot;password&quot;: os.getenv(&#39;POSTGRES_PASSWORD&#39;),<br>        &quot;driver&quot;: &quot;org.postgresql.Driver&quot;,<br>        &#39;url&#39;: os.getenv(&#39;POSTGRES_URL&#39;),<br>        &quot;dbtable&quot;: &quot;fact_history&quot;,<br>    }<br>    <br>    if not batch_df.isEmpty():<br>        time.sleep(5) # Wait for 5 seconds before writing to Postgres fact table in case of late-arriving data on dim tables<br>        batch_df.write.format(&#39;jdbc&#39;).options(**properties).mode(&#39;append&#39;).save()<br><br>def create_spark_session():<br>    return SparkSession.builder \<br>        .appName(&quot;fact-history-topic-spark-consumer&quot;) \<br>        .config(&quot;spark.jars&quot;, jdbc_jar) \<br>        .config(&quot;spark.jars.packages&quot;, &quot;org.apache.spark:spark-sql-kafka-0-10_2.12:3.3.2&quot;) \<br>        .getOrCreate()<br><br>def read_from_kafka(spark, kafka_bootstrap_servers, topics):<br>    return spark.readStream \<br>        .format(&quot;kafka&quot;) \<br>        .option(&quot;kafka.bootstrap.servers&quot;, kafka_bootstrap_servers) \<br>        .option(&quot;subscribe&quot;, topics) \<br>        .load()<br><br>def parse_json_data(df, schemas):<br>    item_df = df.filter(col(&quot;topic&quot;) == &quot;item_topic&quot;) \<br>                 .withColumn(&quot;parsed_data&quot;, from_json(col(&quot;json_value&quot;), schemas[&quot;item_topic&quot;])) \<br>                 .select(col(&quot;parsed_data.*&quot;))<br>                 <br>    # Use to_timestamp instead of to_date<br>    item_df = item_df.withColumn(&quot;played_at&quot;, to_timestamp(col(&quot;played_at&quot;), &quot;yyyy-MM-dd&#39;T&#39;HH:mm:ss.SSS&#39;Z&#39;&quot;))<br><br>    return item_df<br><br>def main():<br>    schemas = get_schemas()<br>    spark = create_spark_session()<br>    kafka_bootstrap_servers = &quot;localhost:9092&quot;<br>    topics = &quot;item_topic&quot;  # Only consume<br><br>    df = read_from_kafka(spark, kafka_bootstrap_servers, topics)<br>    df = df.selectExpr(&quot;CAST(value AS STRING) as json_value&quot;, &quot;topic&quot;)<br>    df = parse_json_data(df, schemas)<br><br>    def process_batch(batch_df, batch_id):<br>        batch_df.printSchema()<br>        batch_df.show()<br>        write_to_postgres(batch_df, batch_id)<br><br>    query = df.writeStream \<br>        .foreachBatch(process_batch) \<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>    try:<br>        query.awaitTermination()<br>    except KeyboardInterrupt:<br>        print(&quot;Process interrupted by user.&quot;)<br>    finally:<br>        query.stop()<br>        print(&quot;Query stopped.&quot;)<br>        spark.stop()<br>        print(&quot;Spark session stopped.&quot;)<br><br><br>if __name__ == &quot;__main__&quot;:<br>    spark_home = &quot;your spark home directory&quot;<br>    jdbc_jar = f&quot;{spark_home}/jars/PostgreSQL-42.7.0.jar&quot;<br>    findspark.init(spark_home)<br>    load_dotenv(dotenv_path=os.path.join(os.path.dirname(__file__), &#39;..&#39;, &#39;.env&#39;))<br>    main()</pre><p>This code will be our spark stream processing for our fact table. Not much of a difference from our dim table spark file, only that it has a timeout of 5 seconds before ingesting the data to our PostgreSQL DB to ensure there won’t be any foreign key constraint errors.</p><p>That’s a wrap on setting up our producer and consumer. Let’s spin our flask backend, kafka broker, producer, and 4 consumers up.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/516/1*1H6x59HwdsOSZQwbVFSLjg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FjicNQpwSyFIrMOCN6Q9yQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*egJT65L8S84HEtCDliUKZQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/1*QJjST61PJKMRHRO2RG2FNg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iUDKpzhqSkdOcDati7EunA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*nwmOGwy1jVE4lGyTov7iww.png" /></figure><p>These are the consumer&#39;s terminals when there is no data coming in the topics.</p><p>The producer terminal will look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*ZyCer_sC-cZoDZvSSUJDeA.png" /></figure><p>We’ve been working for a while, and I’m proud of you for following along. Let’s chill for a bit and listen to some music.</p><p>When Spotify has updated its database and populated our recently played table, our producer will fetch it, and it will show this message to the producer:</p><pre>Produced event to topic song_topic: key = None value = {&quot;song_id&quot;: &quot;5Gxwk3TSekI4GVMpFvPBEc&quot;, &quot;title&quot;: &quot;2112&quot;, &quot;disc_number&quot;: 1, &quot;duration_ms&quot;: 356612, &quot;explicit&quot;: false, &quot;external_url&quot;: &quot;https://open.spotify.com/track/5Gxwk3TSekI4GVMpFvPBEc&quot;, &quot;preview_url&quot;: &quot;No preview available&quot;, &quot;popularity&quot;: 54}<br>Produced data to topic song_topic<br><br>Produced event to topic album_topic: key = None value = {&quot;album_id&quot;: &quot;7gOhCvJD152GWf16fhs7Gp&quot;, &quot;title&quot;: &quot;What Do You Really Know?&quot;, &quot;total_tracks&quot;: 11, &quot;release_date&quot;: &quot;2019-08-30&quot;, &quot;external_url&quot;: &quot;https://open.spotify.com/album/7gOhCvJD152GWf16fhs7Gp&quot;, &quot;image_url&quot;: &quot;https://i.scdn.co/image/ab67616d0000b2736019b1ddb28634421cc291a0&quot;, &quot;label&quot;: &quot;Reality Club&quot;, &quot;popularity&quot;: 59}<br>Produced data to topic album_topic<br><br>Produced event to topic artist_topic: key = None value = {&quot;artist_id&quot;: &quot;1DjZI46mVZZZYmmmygRnTw&quot;, &quot;name&quot;: &quot;Reality Club&quot;, &quot;external_url&quot;: &quot;https://open.spotify.com/artist/1DjZI46mVZZZYmmmygRnTw&quot;, &quot;follower_count&quot;: 518608, &quot;image_url&quot;: &quot;https://i.scdn.co/image/ab6761610000e5ebf7b4399118ab2a6cdf754050&quot;, &quot;popularity&quot;: 61}<br>Produced data to topic artist_topic<br><br>Produced event to topic item_topic: key = None value = {&quot;song_id&quot;: &quot;5Gxwk3TSekI4GVMpFvPBEc&quot;, &quot;album_id&quot;: &quot;7gOhCvJD152GWf16fhs7Gp&quot;, &quot;artist_id&quot;: &quot;1DjZI46mVZZZYmmmygRnTw&quot;, &quot;played_at&quot;: &quot;2024-09-15T14:39:40.419Z&quot;}<br>Produced data to topic item_topic</pre><p>and our consumer will show something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lktLkMJxpwO987JONP8GKA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tN5iKU-8dXnmaj7mqcDOVA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*77RRemZ8enitTaXwgYD-HQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XLrg0xCXpj7mmmu3voVGpA.png" /></figure><p>and if we check our database, it will show something like this:</p><pre><br>verceldb=&gt; \dt<br>            List of relations<br> Schema |     Name     | Type  |  Owner<br>--------+--------------+-------+---------<br> public | dim_album    | table | default<br> public | dim_artist   | table | default<br> public | dim_song     | table | default<br> public | fact_history | table | default<br>(4 rows)<br><br>verceldb=&gt; select * from dim_album limit 1;<br>        album_id        |       title       | total_tracks |    release_date     |                     external_url                      |                            image_url                             |    label     | popularity<br>------------------------+-------------------+--------------+---------------------+-------------------------------------------------------+------------------------------------------------------------------+--------------+------------<br> 1U2aZShwoxZjSwuxYtZJ5y | The Greatest Ever |           10 | 2019-03-15 00:00:00 | https://open.spotify.com/album/1U2aZShwoxZjSwuxYtZJ5y | https://i.scdn.co/image/ab67616d0000b273d8f9ffe84d3b83564b37da8f | Mola Records |         41<br>(1 row)<br><br>verceldb=&gt; select * from dim_artist limit 1;<br>       artist_id        |     name      |                      external_url                      | follower_count |                            image_url                             | popularity<br>------------------------+---------------+--------------------------------------------------------+----------------+------------------------------------------------------------------+------------<br> 4xerUCsurnILh4KpC5FVWX | Elephant Kind | https://open.spotify.com/artist/4xerUCsurnILh4KpC5FVWX |         133093 | https://i.scdn.co/image/ab6761610000e5ebde7c0792f6a173331677fee1 |         40<br>(1 row)<br><br>verceldb=&gt; select * from dim_song limit 1;<br>        song_id         |   title   | disc_number | duration_ms | explicit |                     external_url                      |                                                 preview_url                                                 | popularity<br>------------------------+-----------+-------------+-------------+----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------+------------<br> 5Ven9G4zXjfTPf4vsZH5oq | Lights Up |           1 |      222293 | f        | https://open.spotify.com/track/5Ven9G4zXjfTPf4vsZH5oq | https://p.scdn.co/mp3-preview/ab5f5fca270ea74bca2d670ec2a8faa2b0f5ba1b?cid=1c9c9fe9723045b58a3dd31c2a7be91f |         33<br>(1 row)<br><br>verceldb=&gt; select * from fact_history limit 1;<br> listening_id |        played_at        |        song_id         |        album_id        |       artist_id<br>--------------+-------------------------+------------------------+------------------------+------------------------<br>            2 | 2024-09-15 14:26:13.423 | 5Ven9G4zXjfTPf4vsZH5oq | 1U2aZShwoxZjSwuxYtZJ5y | 4xerUCsurnILh4KpC5FVWX<br>(1 row)</pre><p>Congratulation! Our streaming ETL works as intended. Now as you may remember, the title says it&#39;s a Weekly Spotify Wrapped and we haven’t built any orchestration/scheduler to get the weekly insight. Our insight API can be run anytime you want but there won’t be any insight to gather if we run it too frequently, but it gives us flexibility. Since we have an API to generate the insight, we can schedule to call it every week using an orchestration tool like Airflow or a scheduler like cron. It&#39;s up to you.</p><p>For me, I call the API every Saturday at 9 AM just so I can start off my weekend with a new AI-generated analysis on my listening habit. I scheduled it using iOS automation. I won’t cover how to do that in this article.</p><p>Since we just built the project, our database isn’t populated enough to generate insight from, but I have been running it for quite a while to have enough. So here’s what the generated insight looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oFaWtOPEOESXtAM3PPUd3w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2jbcSkaUpd9RE9h3B4CCrw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DLBFxq7Oy5fAAEhssokr-A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fi0bcioq1LfgDV_BbJRe3A.png" /></figure><h3>Conclusion</h3><p>In this journey, we explore:</p><ul><li>how Flask can serve endless possibilities in serving an API as one of the most used tools to fetch data that we need as Data Engineer</li><li>how Kafka can integrate easily from and to any services</li><li>how Spark can conquer any kind of data transformation needed to fulfill any business requirements</li><li>how PostgreSQL can store our database in a correct and neat manner</li><li>how Gemini AI can produce valuable information given extra motivation (prompt)</li></ul><p>If you find this article educating and enjoyable, you might find my other article worth reading too! A follow is greatly appreciated.</p><p>You can access the source code by clicking <a href="https://github.com/rahmadiyann/data-eng-proj/tree/spotify-streaming-kafka-psql">here</a></p><p>Thank you for taking the time to read, see you on the next occasion!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1385d5e105e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-engineering-indonesia/weekly-spotify-wrapped-a-real-time-spotify-music-listening-data-streaming-with-pyspark-kafka-e1385d5e105e">Weekly Spotify Wrapped: A Real Time Spotify Music Listening Data Streaming with PySpark, Kafka…</a> was originally published in <a href="https://medium.com/data-engineering-indonesia">Data Engineering Indonesia</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building an E-commerce Data Pipeline with Airflow, Google Cloud, Twilio and MySQL]]></title>
            <link>https://medium.com/@rianying/building-an-e-commerce-data-pipeline-with-airflow-google-cloud-twilio-and-mysql-aac14886d754?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/aac14886d754</guid>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Mon, 23 Oct 2023 04:02:40 GMT</pubDate>
            <atom:updated>2023-10-23T04:02:40.007Z</atom:updated>
            <content:encoded><![CDATA[<p>In today’s digital age, the world is becoming increasingly data-driven. As businesses gather vast amounts of data from various sources, there’s a rising need to efficiently manage, process, and gain insights from this data. In this article, we’ll explore how to build a robust data pipeline for an e-commerce platform using Apache Airflow, Google Cloud, and MySQL.</p><h4>Project Overview and Goals</h4><p>Our goal is to create a pipeline that simulates batch data incoming every hour from our hypothetical e-commerce platform. This data would then be processed, stored, and any errors in the pipeline would trigger notifications. Specifically, our objectives are:</p><ol><li>Generate batch e-commerce data every hour.</li><li>Ingest this data into Google Cloud Storage (GCS).</li><li>Process and clean the data.</li><li>Store the processed data both in GCS and a MySQL database.</li><li>Notify the admin in case of any failures using Twilio.</li></ol><h4>Setting up the Environment</h4><p>The environment is crucial for smooth and consistent deployment. Here’s what we did:</p><ol><li>Google Cloud Compute Engine: This is our primary workspace where everything runs.</li><li>MySQL: A local database set up inside the Compute Engine to store processed data.</li><li>Google Cloud Storage (GCS): Acts as a centralized storage system for our raw and processed data.</li><li>Twilio: An external service integrated to send notifications if our pipeline encounters errors.</li><li>Data Generation Script: A Python script leveraging the Faker library to simulate incoming e-commerce data.</li></ol><p>Now, let’s delve into the code and understand its workings.</p><h4>Data Generation</h4><p>Our first step is to simulate the generation of e-commerce data. For this, we use the Faker library, which is excellent for creating dummy data.</p><pre>import csv<br>import os<br>from faker import Faker<br>import random<br>from datetime import datetime, timedelta<br>import time<br><br>fake = Faker()<br><br># Constants<br>NUM_USERS = 100<br>NUM_PRODUCTS = 50<br>NUM_TRANSACTIONS = 500<br>TODAY = datetime.today()<br><br>def append_or_write(filename):<br>    &quot;&quot;&quot;Return &#39;a&#39; (append) or &#39;w&#39; (write) depending on the file&#39;s existence.&quot;&quot;&quot;<br>    return &#39;a&#39; if os.path.exists(filename) else &#39;w&#39;<br><br>def generate_id():<br>    &quot;&quot;&quot;Generate a unique 5-digit integer ID.&quot;&quot;&quot;<br>    return str(random.randint(10000, 99999))<br><br>def generate_users(filename, num_users):<br>    mode = append_or_write(filename)<br>    user_ids = []<br>    <br>    with open(filename, mode, newline=&#39;&#39;) as csvfile:<br>        writer = csv.writer(csvfile)<br>        if mode == &#39;w&#39;:<br>            writer.writerow([&quot;UserID&quot;, &quot;Name&quot;, &quot;Email&quot;, &quot;SignUpDate&quot;])<br>        <br>        for _ in range(num_users):<br>            user_id = generate_id()<br>            user_ids.append(user_id)<br>            writer.writerow([user_id, fake.name(), fake.email(), fake.date_between_dates(date_start=TODAY - timedelta(days=365), date_end=TODAY)])<br>        print(f&quot;Generated {num_users} users.&quot;)<br>    return user_ids<br><br>def generate_products(filename, num_products):<br>    mode = append_or_write(filename)<br>    product_ids = []<br>    <br>    with open(filename, mode, newline=&#39;&#39;) as csvfile:<br>        writer = csv.writer(csvfile)<br>        if mode == &#39;w&#39;:<br>            writer.writerow([&quot;ProductID&quot;, &quot;ProductName&quot;, &quot;Category&quot;, &quot;Price&quot;])<br>        <br>        categories = [&quot;Electronics&quot;, &quot;Clothing&quot;, &quot;Groceries&quot;, &quot;Books&quot;, &quot;Toys&quot;]<br>        <br>        for _ in range(num_products):<br>            product_id = generate_id()<br>            product_ids.append(product_id)<br>            writer.writerow([product_id, fake.unique.first_name(), random.choice(categories), round(random.uniform(5.0, 100.0), 2)])<br>        print(f&quot;Generated {num_products} products.&quot;)<br>    return product_ids<br><br>def generate_transactions(filename, num_transactions, user_ids, product_ids):<br>    mode = append_or_write(filename)<br>    <br>    with open(filename, mode, newline=&#39;&#39;) as csvfile:<br>        writer = csv.writer(csvfile)<br>        if mode == &#39;w&#39;:<br>            writer.writerow([&quot;TransactionID&quot;, &quot;UserID&quot;, &quot;ProductID&quot;, &quot;Quantity&quot;, &quot;TransactionDate&quot;])<br>        <br>        for _ in range(num_transactions):<br>            writer.writerow([generate_id(), random.choice(user_ids), random.choice(product_ids), random.randint(1,5), fake.date_between_dates(date_start=TODAY - timedelta(days=30), date_end=TODAY)])<br>        print(f&quot;Generated {num_transactions} transactions.&quot;)<br>if __name__ == &quot;__main__&quot;:<br>    user_ids = generate_users(&#39;/home/rian/pipeline/scripts/unprocessed/users.csv&#39;, NUM_USERS)<br>    product_ids = generate_products(&#39;/home/rian/pipeline/scripts/unprocessed/products.csv&#39;, NUM_PRODUCTS)<br>    generate_transactions(&#39;/home/rian/pipeline/scripts/unprocessed/transactions.csv&#39;, NUM_TRANSACTIONS, user_ids, product_ids</pre><p>This script generates three CSV files: users.csv, products.csv, and transactions.csv to mimic user information, product listings, and user transactions respectively.</p><h4>Building the DAG with Apache Airflow</h4><p>Now that we have our data, it’s time to automate our pipeline using Airflow.</p><p><strong>DAG Definition</strong></p><p>We start by defining our DAG’s default arguments and metadata. We’ve also set an on_failure_callback to notify us if the DAG fails.</p><pre>from airflow import DAG<br>...<br>default_args = {...}<br>dag = DAG(<br>    &#39;sarwa_pipeline&#39;,<br>    default_args=default_args,<br>    ...<br>    on_failure_callback=notify_failure,<br>    ...<br>)</pre><p><strong>Data Generation Task</strong></p><p>Our DAG kicks off by generating e-commerce data:</p><pre>t0= PythonOperator(<br>    task_id=&#39;generate_data&#39;,<br>    python_callable=generate_data,<br>    dag=dag,<br>)</pre><p><strong>Data Ingestion to GCS</strong></p><p>The next set of tasks pushes our generated data to Google Cloud Storage:</p><pre>t1 = PythonOperator(<br>    task_id=&#39;ingest_user_data&#39;,<br>    python_callable=ingest_unprocessed_data_to_gcs,<br>    op_args=[bucket_name, &#39;/home/rian/pipeline/scripts/unprocessed/users.csv&#39;, &#39;unprocessed&#39;],<br>    dag=dag,<br>)<br><br>t2 = PythonOperator(<br>    task_id=&#39;ingest_product_data&#39;,<br>    python_callable=ingest_unprocessed_data_to_gcs,<br>    op_args=[bucket_name, &#39;/home/rian/pipeline/scripts/unprocessed/products.csv&#39;, &#39;unprocessed&#39;],<br>    dag=dag,<br>)<br><br>t3 = PythonOperator(<br>    task_id=&#39;ingest_transaction_data&#39;,<br>    python_callable=ingest_unprocessed_data_to_gcs,<br>    op_args=[bucket_name, &#39;/home/rian/pipeline/scripts/unprocessed/transactions.csv&#39;, &#39;unprocessed&#39;],<br>    dag=dag,<br>)</pre><p><strong>Data Processing</strong></p><p>Before storing data permanently, it’s essential to process and clean it:</p><pre>t4 = PythonOperator(<br>    task_id=&#39;process_data&#39;,<br>    python_callable=process_data,<br>    dag=dag,<br>)</pre><p>In this step, we read data from GCS, drop any NaN values, remove duplicates, and join the datasets to create a comprehensive e-commerce report.</p><p><strong>Saving Processed Data</strong></p><p>Finally, our processed data is ingested back into GCS and also saved in our local MySQL database:</p><pre>t5 = PythonOperator(<br>    task_id=&#39;ingest_processed_data&#39;,<br>    ...<br>)<br>t6 = PythonOperator(<br>    task_id=&#39;ingest_to_mysql&#39;,<br>    ...<br>)</pre><h4>Airflow DAG Dashboard Overview</h4><p>Once our DAG is up and running, we can monitor its execution via the airflow DAG dashboard. The dashboard provides a holistic view of our pipeline, showcastsing each tasks’s status, the overall success rate, and other vital metrics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iqB5Zr75pmaKPBjdLqntgw.png" /></figure><p>From the dashboard, we can derive the following insights:</p><ol><li>DAG Name: Our DAG is named sarwa_pipeline.</li><li>Task Execution: The green boxes represent successful execution of tasks. As seen in the screenshot, all tasks have been consistently successful over the past few runs, signifying a stable and reliable pipeline.</li><li>Task Breakdown:</li></ol><ul><li>generate_data: This initiates our pipeline by generating e-commerce data.</li><li>ingest_user_data, ingest_product_data, and ingest_transaction_data: These tasks are responsible for ingesting raw e-commerce data into Google Cloud Storage.</li><li>process_data: As the name suggests, this task processes and cleans the raw data.</li><li>ingest_processed_data: This pushes the processed data back into Google Cloud Storage.</li><li>ingest_to_mysql: The final task in our pipeline saves the processed data into our MySQL database.</li></ul><p>4. Execution Metrics:</p><ul><li>Total Runs Displayed: Over the period displayed, the DAG has been executed 25 times.</li><li>Run Durations: The longest run took 28 seconds, while the average run time is around 27 seconds. These metrics help in gauging the efficiency of our pipeline.</li></ul><p>5. Python Operators: All the tasks in our DAG utilize the PythonOperator, which allows us to execute Python functions as Airflow tasks.</p><p>Using the Airflow dashboard, we can easily monitor the pipeline, ensuring it runs smoothly and efficiently. Any anomalies or failures would be easily noticeable, allowing for prompt action and resolution.</p><h4>MySQL Data Verification</h4><p>After out data has been processed and ingested into our local MySQL database, it’s vital to ensure that the data integrity is maintained. One of the ways to verify this is to check the count of records in the target table.</p><p>Let’s delve into our sarwa database and check the number of records in the merged table:</p><pre>[mysql]&gt; use sarwa;<br>[mysql]&gt; select count(*) from merged;<br>+---------+<br>| count(*)|<br>+---------+<br>|  17249  |<br>+---------+</pre><p>From the above query result, we can see that the merged table contains 17,249 records. This count helps in providing a quick verification on the volume of data that has been processed and stored in our MySQL database. Moreover, by comparing this count with the source data, we can further validate if any records were missed during the ingestion or processing phase.</p><p>If you have set up alerts or monitoring on your Airflow tasks, you can further corroborate this number with the count of records processed in the respective Airflow task. This will add an additional layer of validation, ensuring that the pipeline is robust and data discrepancies are minimal.</p><h3>Conclusion</h3><p>Building data pipelines can be intricate, but tools like Airflow and services like Google Cloud make the process more streamlined. With the setup discussed in this article, e-commerce platforms can efficiently manage and analyze their data, driving insights that can significantly benefit their business.</p><p>I hope you found this walkthrough of our data pipeline insightful and helpful. If you’re curious about the intricacies of the code or wish to replicate and tweak the pipeline for your needs, feel free to explore the code repository on my <a href="https://github.com/rianying/pipeline_project">GitHub</a>. I encourage you to star, fork, or contribute if you find it beneficial.</p><p>Additionally, to gain a broader perspective on other projects and initiatives I’ve undertaken, don’t hesitate to visit my <a href="https://rian.social/">portfolio</a>. Your feedback and collaboration opportunities are always welcome!</p><p>Thank you for taking the time to read, and I’m looking forward to engaging with the community further on these exciting data-driven ventures.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aac14886d754" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automating Real-time Entry Notifications from Google Sheets using Python and Twilio]]></title>
            <link>https://medium.com/@rianying/automating-real-time-entry-notifications-from-google-sheets-using-python-and-twilio-7d03dcd76f07?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/7d03dcd76f07</guid>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Fri, 25 Aug 2023 09:41:35 GMT</pubDate>
            <atom:updated>2023-08-25T09:47:45.686Z</atom:updated>
            <content:encoded><![CDATA[<h4>Disclaimer</h4><p>This code is provided as a personal project demonstration and might not be suitable for direct use in a production environment.</p><h4>Introduction</h4><p>In today’s fast-paced world, staying updated with real-time information is crucial. Imagine having a Google Sheets document shared among your team members where new entries are being added frequently. Wouldn’t it be convenient to receive instant notifications whenever a new entry is made? In this article, we’ll explore how to achieve this using Python, the Google Sheets API, and the Twilio API for WhatsApp messaging.</p><h4>Project Overview</h4><p>Our goal is to create a Python script that monitors a specific Google Sheets document for new entries and sends WhatsApp notifications via the Twilio API. This solution is perfect for personal projects or small-scale collaborations where real-time updates are necessary.</p><h4>Prerequisites</h4><ol><li>Google Sheets API Credentials: Obtain a JSON keyfile from Google Cloud Platform to authenticate your script’s access to the Google Sheets API.</li><li>Twilio Account: Create a Twilio account to get your `twilio_account_sid`, `twilio_auth_token`, and a Twilio phone number. This will enable you to send WhatsApp messages programmatically.</li><li>Python Libraries: Install the required Python libraries by running:</li></ol><pre>pip install gspread oauth2client twilio</pre><h4>The Code</h4><p>Here’s the complete Python script:</p><pre>python<br>import time<br>import gspread<br>from oauth2client.service_account import ServiceAccountCredentials<br>from twilio.rest import Client<br>import signal<br>import sys<br>import json<br><br># … (code before loading credentials)<br># Set up the Twilio client<br>twilio_client = Client(twilio_account_sid, twilio_auth_token)<br># Set up the Ctrl+C signal handler<br>def handle_exit(sig, frame):<br> print(&quot;\nProgram exited&quot;)<br> sys.exit(0)<br>signal.signal(signal.SIGINT, handle_exit)<br># Main loop for continuous monitoring<br>while True:<br> try:<br> # … (code for loading credentials and Google Sheets setup)<br># Main logic for checking new entries<br> if current_last_row &gt; last_row:<br> new_entry_segment = None<br> new_entry_time = None<br> new_entry_so = sheet.cell(current_last_row, 3).value<br>for row_num in range(current_last_row, 0, -1):<br> segment = sheet.cell(row_num, 2).value<br> if segment:<br> new_entry_segment = segment<br> break<br> <br> for row_num in range(current_last_row, 0, -1):<br> ot = sheet.cell(row_num, 4).value<br> if ot:<br> new_entry_time = ot<br> break<br>print(f&quot;New entry added at Row: {current_last_row}, Segment: {new_entry_segment} - {new_entry_so}. Order time: {new_entry_time}&quot;)<br># Send a WhatsApp message using Twilio<br> message = twilio_client.messages.create(<br> from_=twilio_phone_number,<br> body=f&quot;New entry added: {new_entry_segment} - {new_entry_so}. Order time: {new_entry_time} at Row: {current_last_row}.\n\nCheck it now: {spreadsheet_url}&quot;,<br> to=to_phone_number<br> )<br>last_row = current_last_row<br> else:<br> print(&#39;No new entry found. Last checked row: {}…&#39;.format(last_row))<br>time.sleep(5) # Check every 5 seconds<br>except KeyboardInterrupt:<br> handle_exit(None, None) # Handle Ctrl+C</pre><h4>Results</h4><p>Let’s take a look at what a notification might look like when a new entry is added to the Google Sheets document:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*PXKf6EmND3Ys__dQjg2-5w.jpeg" /></figure><p>The notification provides information about the newly added entry, including the segment name, order identifier, and the order time. It also includes a link to the Google Sheets document for quick access.</p><h4>Conclusion</h4><p>While this script serves as a valuable tool for personal projects or small teams, it may require further refinement and security measures before use in production environments. By combining the power of Python, the Google Sheets API, and the Twilio API, you can easily stay updated on important changes in your Google Sheets document. Remember to adapt and customize the code to fit your specific needs and enjoy the benefits of real-time notifications in your collaborative projects. Happy coding!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7d03dcd76f07" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing Data Warehousing Strategies for Enhanced Business Intelligence]]></title>
            <link>https://medium.com/@rianying/optimizing-data-warehousing-strategies-for-enhanced-business-intelligence-38c02b3c8396?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/38c02b3c8396</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database-management]]></category>
            <category><![CDATA[data-optimization]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-warehousing]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Fri, 07 Jul 2023 10:32:17 GMT</pubDate>
            <atom:updated>2023-07-07T10:32:17.466Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In today’s data-driven world, businesses rely heavily on data analysis to gain valuable insights and make informed decisions. A crucial component of successful data analysis is the efficient storage and retrieval of data, which is where data warehousing plays a significant role. In this article, we will explore comprehensive strategies to optimize data warehousing for enhanced business intelligence. We’ll delve into various techniques, best practices, and considerations that can help organizations maximize the value of their data assets. Additionally, we’ll provide code snippets demonstrating sample implementations using SQL to illustrate key concepts.</p><h3>Understanding Data Warehousing</h3><p>Data warehousing involves consolidating and organizing data from various sources into a centralized repository, commonly known as a data warehouse. The data warehouse acts as a single source of truth, providing a holistic view of the organization’s data. To optimize data warehousing, it is essential to understand its core components and considerations:</p><ol><li>Data Modeling: Effective data modeling is the foundation of a well-designed data warehouse. It involves identifying the entities, attributes, and relationships that need to be captured to support analytical queries. Techniques such as star schema, snowflake schema, and data vault modeling are commonly used to structure the data.</li><li>Extract, Transform, Load (ETL): The ETL process is crucial for loading data into the warehouse. It involves extracting data from various sources, transforming it into a consistent format, and then loading it into the data warehouse. Optimizing ETL workflows can significantly impact data warehousing performance.</li></ol><h3>Optimization Techniques</h3><h4>Schema Design</h4><ul><li>Star Schema: Utilize a star schema design, consisting of a central fact table surrounded by dimension tables. This simplifies queries and improves performance by reducing the number of joins required.</li><li>Snowflake Schema: Employ a snowflake schema when dealing with highly normalized data. This design optimizes storage efficiency at the expense of increased join complexity.</li><li>Data Vault Modeling: Consider using a data vault modeling approach for scalability and flexibility in data warehousing. It provides a foundation for incremental loading and supports historical data tracking.</li></ul><h4>Indexing Strategies</h4><ul><li>Identify frequently queried columns and create indexes on those columns to speed up data retrieval.</li><li>Utilize composite indexes for queries involving multiple columns to further enhance performance.</li><li>Regularly monitor and update index statistics to ensure they remain effective.</li></ul><h4>Partitioning</h4><ul><li>Partition large tables based on defined criteria such as date ranges or key values.</li><li>Partition elimination allows queries to access only relevant partitions, improving query performance.</li><li>Implement partitioning schemes appropriate for the data distribution and query patterns.</li></ul><h4>Compression Techniques</h4><ul><li>Utilize data compression to reduce storage requirements and enhance query performance.</li><li>Evaluate and choose compression algorithms suitable for the data characteristics and workload.</li><li>Consider both column-level and table-level compression based on the data types and redundancy in the warehouse.</li></ul><h3>Sample Implementations: SQL Code Snippets</h3><h4>Schema Design — Star Schema</h4><pre>-- Fact table<br>CREATE TABLE sales (<br>    sale_id INT,<br>    sale_date DATE,<br>    product_id INT,<br>    quantity INT,<br>    price DECIMAL(10,2),<br>    -- Additional dimension columns<br>);<br><br>-- Dimension tables<br>CREATE TABLE products (<br>    product_id INT PRIMARY KEY,<br>    product_name VARCHAR(255),<br>    -- Additional product attributes<br>);<br><br>CREATE TABLE customers (<br>    customer_id INT PRIMARY KEY,<br>    customer_name VARCHAR(255),<br>    -- Additional customer attributes<br>);<br><br>-- Create relationships<br>ALTER TABLE sales<br>    ADD CONSTRAINT fk_product<br>    FOREIGN KEY (product_id)<br>    REFERENCES products (product_id);<br><br>ALTER TABLE sales<br>    ADD CONSTRAINT fk_customer<br>    FOREIGN KEY (customer_id)<br>    REFERENCES customers (customer_id);</pre><ul><li>The code snippet demonstrates the creation of a star schema for a sales data warehouse. The fact table “sales” contains the transactional data such as sale_id, sale_date, product_id, quantity, and price. The dimension tables “products” and “customers” contain additional attributes related to products and customers, respectively.</li><li>Relationships (foreign key constraints) are established between the fact table and dimension tables using the product_id and customer_id columns.</li></ul><h4>Indexing — Creating Indexes</h4><pre>-- Create an index on frequently queried column(s)<br>CREATE INDEX idx_sale_date ON sales (sale_date);<br><br>-- Create a composite index on multiple columns<br>CREATE INDEX idx_product_customer ON sales (product_id, customer_id);</pre><ul><li>This code snippet shows how to create an index on the frequently queried column “sale_date” in the “sales” table. Indexing this column can significantly improve query performance when filtering or sorting based on the sale_date.</li><li>Similarly, the snippet demonstrates the creation of a composite index named “idx_product_customer” on the “sales” table, covering both the product_id and customer_id columns. This composite index is useful when queries involve filtering or joining on both of these columns simultaneously.</li></ul><h4>Partitioning — Partitioning by Date Range</h4><pre>-- Create a partitioned table based on date ranges<br>CREATE TABLE sales_partitioned (<br>    sale_id INT,<br>    sale_date DATE,<br>    product_id INT,<br>    quantity INT,<br>    price DECIMAL(10,2),<br>    -- Additional dimension columns<br>)<br>PARTITION BY RANGE (sale_date)(<br>    PARTITION p1 VALUES LESS THAN (&#39;2022-01-01&#39;),<br>    PARTITION p2 VALUES LESS THAN (&#39;2023-01-01&#39;),<br>    PARTITION p3 VALUES LESS THAN (&#39;2024-01-01&#39;)<br>);</pre><ul><li>The code snippet illustrates the creation of a partitioned table named “sales_partitioned” based on date ranges. The table is partitioned using the “sale_date” column, which allows for efficient data access and management.</li><li>In this example, the table is partitioned into three partitions: p1, p2, and p3, representing different time intervals (e.g., years). Each partition stores data falling within the specified date range, optimizing query performance by reducing the amount of data that needs to be scanned.</li></ul><h4>Compression — Table-Level Compression</h4><pre>-- Create a table with table-level compression<br>CREATE TABLE sales_compressed (<br>    sale_id INT,<br>    sale_date DATE,<br>    product_id INT,<br>    quantity INT,<br>    price DECIMAL(10,2),<br>    -- Additional dimension columns<br>)<br>COMPRESS FOR QUERY;</pre><ul><li>The provided code snippet showcases the creation of a table named “sales_compressed” with table-level compression enabled. Compression reduces the storage footprint by applying efficient data compression algorithms to the table.</li><li>With table-level compression, the data is compressed at rest, occupying less space on the storage medium. When querying the table, the data is automatically decompressed for efficient retrieval. This technique helps to optimize storage utilization and can improve overall query performance.</li></ul><h3>Conclusion</h3><p>Optimizing data warehousing strategies is crucial for organizations seeking to extract maximum value from their data assets. By employing effective data modeling techniques, optimizing ETL workflows, and implementing strategies like schema design, indexing, partitioning, and compression, businesses can significantly enhance query performance, improve data retrieval times, and enable faster, more efficient data analysis. These techniques serve as a starting point, and continuous refinement and adaptation are vital to stay ahead in the rapidly evolving landscape of data-driven decision making.</p><p>(Note: The provided SQL code snippets are simplified examples for demonstration purposes only. Actual implementation may vary based on the specific database system, data volumes, and requirements.)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=38c02b3c8396" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Enhancing Data Engineering Pipelines with Data Quality Checks]]></title>
            <link>https://medium.com/@rianying/enhancing-data-engineering-pipelines-with-data-quality-checks-3ad90a3b5e0?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/3ad90a3b5e0</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-validation]]></category>
            <category><![CDATA[data-quality]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Thu, 06 Jul 2023 06:18:14 GMT</pubDate>
            <atom:updated>2023-07-06T06:18:14.634Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In the world of data engineering, ensuring the accuracy and reliability of data is paramount. A robust data quality framework is essential to maintain data consistency throughout the processing pipeline. In this article, we will explore the importance of implementing data quality checks and provide a practical example of how to integrate them into your data engineering pipelines.</p><h3>Why Data Quality Checks Matter</h3><p>Data quality checks play a crucial role in identifying and rectifying issues that can compromise the integrity of your data. By validating data at different stages of processing, you can catch anomalies, inconsistencies, and errors early on, preventing them from propagating further down the pipeline. This proactive approach not only helps maintain the reliability of your data but also saves time and effort in troubleshooting and debugging downstream processes.</p><h3>Implementing Data Quality Checks</h3><p>Next, let’s explore two additional examples of implementing data quality checks in your data engineering pipelines.</p><h4>Email Address Validation</h4><p>Suppose you are processing customer data, and one of the important fields is the email address. To ensure the validity of email addresses, you can incorporate the following data quality check:</p><pre>import re<br><br>def perform_email_validation(df):<br>    # Check for valid email addresses<br>    invalid_emails = df[~df[&#39;email&#39;].str.match(r&#39;^[\w\.-]+@[\w\.-]+\.\w+$&#39;)]<br>    if not invalid_emails.empty:<br>        raise ValueError(f&quot;Invalid email addresses detected:\n{invalid_emails}&quot;)<br><br># Example usage<br>data = {&#39;customer_id&#39;: [1, 2, 3, 4],<br>        &#39;name&#39;: [&#39;John Doe&#39;, &#39;Jane Smith&#39;, &#39;Robert&#39;, &#39;Alice&#39;],<br>        &#39;email&#39;: [&#39;john.doe@example.com&#39;, &#39;jane.smith@example.com&#39;, &#39;invalid_email&#39;, &#39;alice@example.com&#39;]}<br>df = pd.DataFrame(data)<br><br>perform_email_validation(df)</pre><p>In this example, we use regular expressions to validate email addresses. The data quality check identifies any invalid email addresses that do not adhere to the specified pattern. This helps ensure that only valid email addresses are processed, preventing any potential issues or errors downstream.</p><h4>Duplicate Record Detection</h4><p>Duplicate records can have a significant impact on data analysis and downstream processes. To identify and handle duplicate records, consider the following data quality check:</p><pre>def perform_duplicate_detection(df):<br>    # Check for duplicate records<br>    duplicates = df[df.duplicated()]<br>    if not duplicates.empty:<br>        raise ValueError(f&quot;Duplicate records detected:\n{duplicates}&quot;)<br><br># Example usage<br>data = {&#39;customer_id&#39;: [1, 2, 3, 2, 4],<br>        &#39;name&#39;: [&#39;John Doe&#39;, &#39;Jane Smith&#39;, &#39;Robert&#39;, &#39;Jane Smith&#39;, &#39;Alice&#39;],<br>        &#39;email&#39;: [&#39;john.doe@example.com&#39;, &#39;jane.smith@example.com&#39;, &#39;robert@example.com&#39;, &#39;jane.smith@example.com&#39;, &#39;alice@example.com&#39;]}<br>df = pd.DataFrame(data)<br><br>perform_duplicate_detection(df)</pre><p>In this example, we identify and handle duplicate customer records based on the customer_id field. The data quality check detects any duplicate records in the DataFrame and raises an exception if duplicates are found. This ensures data integrity and maintains the uniqueness of customer records in the pipeline.</p><h3>Conclusion</h3><p>Implementing data quality checks in your data engineering pipelines is crucial for maintaining data integrity and ensuring the reliability of your processed data. In this article, we explored two additional examples: email address validation and duplicate record detection. These checks help validate the quality, completeness, and uniqueness of specific fields in your data.</p><p>Remember to customize the data quality checks according to your pipeline requirements and consider additional checks based on your specific use cases. By proactively implementing data quality checks, you can minimize data inconsistencies, improve the accuracy of your analyses, and build trust in your data-driven decision-making processes.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3ad90a3b5e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Harnessing the Power of Faker Python Library: Empowering Data Analysis with Synthetic Data]]></title>
            <link>https://medium.com/@rianying/harnessing-the-power-of-faker-python-library-empowering-data-analysis-with-synthetic-data-3433c023102d?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/3433c023102d</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[faker-library]]></category>
            <category><![CDATA[pandas]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Sat, 01 Jul 2023 07:03:55 GMT</pubDate>
            <atom:updated>2023-07-01T07:03:55.418Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/349/1*y1ZnlVExxYoHc2sGFsAI1Q.png" /></figure><h3>Introduction</h3><p>In the ever-evolving landscape of data analysis, engineers, scientists, and analysts are constantly faced with the challenge of obtaining realistic data for testing, development, and analysis. It is often impractical or even impossible to acquire large datasets with diverse attributes and values, making it difficult to thoroughly validate algorithms, models, or systems. This is where the Faker Python library comes to the rescue, providing a valuable tool for generating synthetic data that closely resembles real-world scenarios. In this article, we will explore the advantages of using Faker and showcase a code snippet to demonstrate its application.</p><h3>What is Faker</h3><p>Faker is a Python library that enables the generation of large volumes of synthetic data with randomized attributes. It provides a wide range of data types, including names, addresses, phone numbers, dates, email addresses, and more. The library is based on a vast collection of localized providers, ensuring that the generated data adheres to regional conventions and cultural norms. Faker simplifies the process of creating realistic datasets, saving valuable time and effort in data analysis workflows.</p><h3>Advantages of Using Faker</h3><h4>Rapid Data Generation</h4><p>One of the key advantages of Faker is its ability to swiftly generate large volumes of data. With just a few lines of code, analysts can create extensive datasets tailored to their specific needs. This eliminates the tedious and time-consuming task of manually creating or gathering real-world data, allowing users to focus on the core aspects of their analysis.</p><h4>Data Privacy and Security</h4><p>In an era where data privacy is paramount, Faker plays a crucial role in protecting sensitive information. By utilizing synthetic data, analysts can anonymize or obfuscate personally identifiable information (PII) and other sensitive attributes. This ensures compliance with data protection regulations while maintaining the integrity of the analysis.</p><h4>Diverse Data Types</h4><p>Faker offers a broad range of data types and attributes, allowing analysts to generate data that accurately reflects real-world scenarios. Whether it’s creating customer profiles, simulating financial transactions, or generating geospatial data, the library covers a wide spectrum of use cases. This versatility empowers data scientists, engineers, and analysts to explore different domains and test their algorithms against various data distributions.</p><h4>Reproducibility and Debugging</h4><p>When working with synthetic data, reproducibility and debugging become significantly easier. By using Faker, analysts can precisely control the generation process, ensuring the same dataset can be recreated whenever required. This enables thorough testing, debugging, and validation of algorithms, models, or systems under different conditions and scenarios.</p><h3>Usage Example</h3><p>Generating Synthetic Customer Data Let’s dive into a code snippet that demonstrates how Faker can be utilized to generate synthetic customer data:</p><pre>from faker import Faker<br>import pandas as pd<br><br>fake = Faker()<br><br>def generate_customer_data(num_customers):<br>    customers = []<br>    for _ in range(num_customers):<br>        customer = {<br>            &#39;name&#39;: fake.name(),<br>            &#39;email&#39;: fake.email(),<br>            &#39;phone&#39;: fake.phone_number(),<br>            &#39;address&#39;: fake.address(),<br>            &#39;age&#39;: fake.random_int(min=18, max=65)<br>        }<br>        customers.append(customer)<br>    return customers<br><br># Generate 100 synthetic customer profiles<br>customer_data = generate_customer_data(100)<br><br>df = pd.DataFrame(customer_data)<br>print(df)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eoTWJ9MUJGsyuiscER9VBg.png" /></figure><p>In this example, we import the Faker library, instantiate a Faker object, and define a function generate_customer_data that generates customer profiles. The function creates a dictionary for each customer, populating it with randomly generated attributes such as name, email, phone number, address, and age. We can then specify the number of customer profiles we want to generate and call the function to obtain the synthetic data. Finally, we print the generated customer profiles.</p><h3><strong>Conclusion</strong></h3><p>Embrace the power of Faker Python library and unlock a world of possibilities in data analysis. Connect with me on <a href="https://www.linkedin.com/in/rahmadiyanmuhammad">LinkedIn</a> to explore more data-driven insights and connect with like-minded professionals who are shaping the future of analytics. Let’s revolutionize the way we analyze and interpret data together!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3433c023102d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Streamlining Data Processing with Python: Automating Data Filtering and Uploading]]></title>
            <link>https://medium.com/@rianying/streamlining-data-processing-with-python-automating-data-filtering-and-uploading-ea92b543edfb?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea92b543edfb</guid>
            <category><![CDATA[data-processing]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Tue, 27 Jun 2023 06:35:00 GMT</pubDate>
            <atom:updated>2023-06-27T06:35:00.743Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In today’s data-driven world, efficiency and automation are key to maximizing productivity. In this article, we will explore how to automate the process of filtering and uploading data using Python. We will dive into the code and provide a comprehensive explanation of each line, showcasing how automation can streamline data processing tasks. Let’s get started!</p><h4>Importing Libraries and Setting Variables</h4><pre>import pandas as pd<br>import gspread<br>from oauth2client.service_account import ServiceAccountCredentials<br><br>credentials = &#39;credentials.json&#39;<br>sheetlink = &#39;insert-sheet-link-here&#39;<br>sheetname = &#39;Sheet1&#39;</pre><p>To begin, we import the necessary libraries: pandas for data manipulation and gspread for interacting with Google Sheets. We also import the ServiceAccountCredentials module from oauth2client.service_account to authenticate the Google Sheets API using our credentials stored in ‘credentials.json’. Next, we set the variables sheetlink and sheetname to define the Google Sheet link and the name of the sheet we want to upload the data to.</p><h4>Reading and Filtering the Data</h4><pre>df = pd.read_csv(&#39;input.csv&#39;)<br>columns_to_keep = [&#39;Type&#39;, &#39;Sales per customer&#39;, &#39;Category Name&#39;, &#39;Customer City&#39;, &#39;Customer Country&#39;, &#39;Customer Segment&#39;, &#39;Customer State&#39;, &#39;Department Name&#39;, &#39;Latitude&#39;, &#39;Longitude&#39;, &#39;Order Country&#39;, &#39;order date (DateOrders)&#39;, &#39;Order Item Product Price&#39;, &#39;Order Item Quantity&#39;, &#39;Sales&#39;, &#39;Order Item Total&#39;, &#39;Order Region&#39;, &#39;Order State&#39;, &#39;Order Status&#39;, &#39;Shipping Mode&#39;]<br>df_cleaned = df[columns_to_keep]<br>df_final = df_cleaned[df_cleaned[&#39;Order Country&#39;] == &#39;Indonesia&#39;]</pre><p>Using pandas, we read the CSV file ‘input.csv’ and store the data in a DataFrame called df. We define a list columns_to_keep containing the column names we want to extract from the DataFrame. Then, we create a new DataFrame df_cleaned by selecting only the columns specified in columns_to_keep. Further, we filter the data by extracting rows where the &#39;Order Country&#39; column is equal to &#39;Indonesia&#39; and assign the result to df_final. This step ensures that we only retain the relevant data for our task.</p><h4>Uploading the Data to Google Sheets</h4><pre>scope = [&#39;https://spreadsheets.google.com/feeds&#39;, &#39;https://www.googleapis.com/auth/drive&#39;]<br>credentials = ServiceAccountCredentials.from_json_keyfile_name(credentials, scope)<br>client = gspread.authorize(credentials)<br>spreadsheet = client.open_by_url(https://medium.com/sheetlink)<br>worksheet = spreadsheet.worksheet(sheetname)<br>df_final_columns = df_final.columns.tolist()<br>data = [df_final_columns] + df_final.values.tolist()<br>worksheet.clear()<br>worksheet.update(data)</pre><p>To upload the filtered data to Google Sheets, we define the OAuth2 scope required for accessing Google Sheets API. We then create credentials using the ServiceAccountCredentials module and authorize the client. Using the sheetlink, we open the Google Sheet and select the sheetname where we want to upload the data.</p><p>We convert the column names of df_final to a list using df_final.columns.tolist() and store it in df_final_columns. Next, we combine the column names and the values of df_final into a 2D list called data, where the first element represents the column headers and the remaining elements correspond to the data rows.</p><p>Before updating the Google Sheet, we clear its contents using worksheet.clear(). Finally, we update the sheet by passing the data list to worksheet.update(), effectively uploading the filtered data.</p><h3>Conclusion</h3><p>In this article, we explored the process of automating data filtering and uploading using Python. By leveraging the power of pandas and the Google Sheets API, we streamlined the task of combing through data, filtering it based on specific criteria, and uploading the relevant information to a Google Sheet. By automating these steps, we saved valuable time and ensured accuracy and consistency in our data processing.</p><p>Automation plays a crucial role in enhancing productivity and efficiency in data-related tasks. As data professionals, it is essential to continuously explore opportunities to leverage automation and streamline our workflows.</p><p>Feel free to experiment with the provided code and adapt it to your specific requirements. Automate your data processing tasks and unlock the potential of Python and its libraries.</p><p>Happy automating!</p><h4>About the Author</h4><p>Rahmadiyan Muhammad is a freshman, currently learning Information Technology. He is a man with a passion for data analysis and automation. They frequently share insights and coding projects on <a href="https://chat.openai.com/c/YourGitHubProfileLink">GitHub</a> and publish articles on <a href="https://chat.openai.com/c/YourMediumProfileLink">Medium</a> related to data manipulation, Python, and automation. Connect with Rahmadiyan to stay updated on the latest advancements in the field and join the automation revolution!</p><p>Thank you for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea92b543edfb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Revolutionizing Payroll Management with Python: Calculating Salaries Made Easy ( A Basic Python…]]></title>
            <link>https://medium.com/@rianying/revolutionizing-payroll-management-with-python-calculating-salaries-made-easy-a-basic-python-1249eb953918?source=rss-3d4d0f6c4d7b------2</link>
            <guid isPermaLink="false">https://medium.com/p/1249eb953918</guid>
            <category><![CDATA[payroll]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Rahmadiyan]]></dc:creator>
            <pubDate>Sun, 25 Jun 2023 03:55:18 GMT</pubDate>
            <atom:updated>2023-06-25T03:55:18.423Z</atom:updated>
            <content:encoded><![CDATA[<h3>Revolutionizing Payroll Management with Python: Calculating Salaries Made Easy ( A Basic Python Programming Final Project )</h3><h3>Introduction</h3><p>In today’s fast-paced world, efficient payroll management is crucial for any organization. However, the complex calculations involved in determining employee salaries can often be daunting and time-consuming. But fear not! With the power of Python, we can revolutionize the way salaries are calculated, making the process faster, more accurate, and exciting. In this article, we’ll dive into a Python code snippet that showcases a salary calculation system, explaining it step by step. Let’s embark on this thrilling journey!</p><h3>Snippet Explanation</h3><h4>Data Initialization</h4><pre># Data pengguna<br>users = {<br>    &quot;19225201&quot;: &quot;fajarsadboy25&quot;,<br>    &quot;1&quot;: &quot;2&quot;,<br>    &quot;19225000&quot;: &quot;testing&quot;<br>}<br><br># Daftar jabatan<br>positions = {<br>    &quot;Senior Manager&quot;: 24000000,<br>    &quot;Manager&quot;: 13000000,<br>    &quot;Branch Manager&quot;: 12000000,<br>    &quot;Marketing Senior Staff&quot;: 20000000,<br>    &quot;Junior Manager&quot;: 8000000,<br>    &quot;Assistant Manager (Supervisor)&quot;: 8000000,<br>    &quot;Head Department&quot;: 8000000,<br>    &quot;HRD&quot;: 7500000,<br>    &quot;Analyst&quot;: 7500000,<br>    &quot;CMO&quot;: 7000000,<br>    &quot;Area Coordinator&quot;: 6500000,<br>    &quot;Collection Manager&quot;: 6000000,<br>    &quot;IT Staff&quot;: 5500000,<br>    &quot;Section Head (SPV Junior)&quot;: 5500000,<br>    &quot;Pelayanan Profesional&quot;: 4500000,<br>    &quot;Quality Assurance Staff&quot;: 4000000,<br>    &quot;Account Officer&quot;: 3500000,<br>    &quot;Administrasi&quot;: 3500000,<br>    &quot;Sales Counter&quot;: 3500000,<br>    &quot;Security&quot;: 3500000<br>}<br><br># Daftar peraturan tunjangan pendidikan<br>education_rules = {<br>    &quot;SMA&quot;: 0.025,<br>    &quot;D1&quot;: 0.05,<br>    &quot;D3&quot;: 0.2,<br>    &quot;S1&quot;: 0.3<br>}</pre><ul><li>The code snippet begins by initializing three dictionaries: “users,” “positions,” and “education_rules.”</li><li>“users” stores the usernames and passwords of authorized users.</li><li>“positions” contains a list of job positions and their corresponding salaries.</li><li>“education_rules” maps various levels of education to their respective education allowance percentages.</li></ul><h4>calculate_salary Function</h4><pre>def calculate_salary(jabatan, pendidikan, jam_lembur_biasa, jam_lembur_nasional, jam_lembur_sabtuminggu, jumlah_hari_kerja, jenis_lembur):<br>    # Menghitung gaji jabatan<br>    if jabatan in positions:<br>        gaji_jabatan = positions[jabatan]<br>    else:<br>        print(&quot;ERROR! Jabatan tidak valid.&quot;)<br>        return None<br><br>    # Menghitung tunjangan pendidikan<br>    if pendidikan in education_rules:<br>        persentase_tunjangan = education_rules[pendidikan]<br>    else:<br>        print(&quot;ERROR! Pendidikan tidak valid.&quot;)<br>        return None<br><br>    # Menghitung honor lembur<br>    # Menghitung honor lembur<br>    honor_lembur_biasa = 0<br>    honor_lembur_sabtuminggu = 0<br>    honor_lembur_nasional = 0<br>    <br>    upah_lembur = gaji_jabatan / 173<br>    if jenis_lembur == 1 and jam_lembur_biasa &gt; 0:  # Lembur Biasa<br>        if jam_lembur_biasa &lt;= 1:<br>            honor_lembur_biasa += upah_lembur * 1.5 * jam_lembur_biasa<br>        else:<br>            honor_lembur_biasa += (upah_lembur * 1.5) + ((jam_lembur_biasa - 1) * (upah_lembur * 2))<br>    elif jenis_lembur == 2 and jam_lembur_sabtuminggu &gt; 0:  # Lembur Hari Libur Sabtu Minggu<br>        if jam_lembur_sabtuminggu &lt;= 5:<br>            honor_lembur_sabtuminggu += upah_lembur * 2 * jam_lembur_sabtuminggu<br>        elif jam_lembur_sabtuminggu == 6:<br>            honor_lembur_sabtuminggu += upah_lembur * 3<br>        elif jam_lembur_sabtuminggu == 7:<br>            honor_lembur_sabtuminggu += upah_lembur * 3<br>        else:<br>            honor_lembur_sabtuminggu += upah_lembur * 4<br>    elif jenis_lembur == 3 and jam_lembur_nasional &gt; 0:  # Lembur Hari Libur Nasional<br>        if jam_lembur_nasional &lt;= 7:<br>            honor_lembur_nasional += upah_lembur * 2 * jam_lembur_nasional<br>        elif jam_lembur_nasional == 8:<br>            honor_lembur_nasional += upah_lembur * 3<br>        elif jam_lembur_nasional &gt;= 9:<br>            honor_lembur_nasional += upah_lembur * 4<br><br><br>    # Menghitung total upah<br>    total_upah_lembur = honor_lembur_sabtuminggu + honor_lembur_biasa + honor_lembur_nasional<br>    total_upah = (gaji_jabatan / 30) * jumlah_hari_kerja + gaji_jabatan * persentase_tunjangan + total_upah_lembur<br><br>    print(&quot;\n\n\n\n\n\n\n\n\n\n\n===================================JABARAN GAJI===============================&quot;)<br>    print(&quot;\nUpah hari kerja  : Rp. {:,.0f}&quot;.format((gaji_jabatan / 30) * jumlah_hari_kerja).replace(&#39;,&#39;, &#39;.&#39;))<br>    print(&quot;Upah tunjangan   : Rp. {:,.0f}&quot;.format(gaji_jabatan * persentase_tunjangan).replace(&#39;,&#39;, &#39;.&#39;))<br>    print(&quot;Upah lembur      : Rp. {:,.0f}&quot;.format(total_upah_lembur).replace(&#39;,&#39;, &#39;.&#39;))<br>    return total_upah</pre><ul><li>This function takes several parameters, including job position, education level, overtime hours, working days, and overtime type.</li><li>It calculates the employee’s salary based on the provided inputs.</li><li>First, it retrieves the salary for the given job position from the “positions” dictionary.</li><li>Next, it retrieves the education allowance percentage based on the education level from the “education_rules” dictionary.</li><li>The function then calculates the overtime pay based on the overtime hours and type.</li><li>Finally, it calculates the total salary by summing up the regular salary, education allowance, and overtime pay.</li></ul><h4>User Authentication</h4><pre>username = input(&quot;\nMasukkan username: &quot;)<br>password = input(&quot;Masukkan password: &quot;)<br><br>if username in users and password == users[username]:<br>    print(&quot;\nLogin berhasil!&quot;)</pre><ul><li>The code prompts the user for a username and password to authenticate their access.</li><li>If the provided username and password match the entries in the “users” dictionary, the user is granted access.</li></ul><h4>User Input</h4><pre>while True:<br>        nama = input(&quot;\nMasukkan nama: &quot;)<br>        if not nama.isalpha():<br>            print(&quot;ERROR! Nama tidak boleh kosong atau mengadung angka&quot;)<br>        elif not nama:<br>            print(&quot;ERROR! Nama tidak boleh kosong.&quot;)<br>        else:<br>            break<br>    print(&quot;\nDaftar Jabatan:&quot;)<br>    count = 1<br>    for i, jabatan in enumerate(positions.keys()):<br>        print(f&quot;{count}. {jabatan}&quot;)<br>        count += 1<br><br>    while True:<br>        try:<br>            jabatan_index = int(input(&quot;\nMasukkan nomor jabatan: &quot;))<br>            if jabatan_index &gt;= 1 and jabatan_index &lt;= len(positions):<br>                jabatan = list(positions.keys())[jabatan_index - 1]<br>                break<br>            else:<br>                print(&quot;ERROR! Nomor jabatan tidak valid.&quot;)<br>        except ValueError:<br>            print(&quot;ERROR! Input jabatan harus berupa angka.&quot;)<br><br>    jabatan = list(positions.keys())[jabatan_index - 1]<br><br>    while True:<br>        try:<br>            print(&quot;\nDaftar Pendidikan:&quot;)<br>            count = 1<br>            for i, pendidikan in enumerate(education_rules.keys()):<br>                print(f&quot;{count}. {pendidikan}&quot;)<br>                count += 1<br>            pendidikan_index = int(input(&quot;\nMasukkan nomor pendidikan: &quot;))<br>            if pendidikan_index &gt;= 1 and pendidikan_index &lt;= len(education_rules):<br>                break<br>            else:<br>                print(&quot;ERROR! Nomor pendidikan tidak valid.&quot;)<br>        except ValueError:<br>            print(&#39;ERROR! Input pendidikan harus berupa angka&#39;)<br><br>    pendidikan = list(education_rules.keys())[pendidikan_index - 1]<br><br>while True:<br>        try:<br>            jumlah_hari_kerja = int(input(&quot;\nJumlah hari kerja dalam sebulan: \n&quot;))<br>            if jumlah_hari_kerja &lt; 0:<br>                print(&quot;ERROR! Input tidak bisa negatif&quot;)<br>            else:<br>                break<br>        except ValueError:<br>            print(&quot;Input harus berupa angka!&quot;)</pre><ul><li>The code asks the user to input their name, select a job position, specify their education level, and indicate if they have overtime hours.</li><li>It validates the user inputs to ensure they are within the valid range.</li></ul><h4>Overtime Input</h4><pre>while True:<br>        try:<br>            jenis_lembur = int(input(&quot;\nApakah lembur? (0. Tidak Lembur, 1. Lembur Biasa, 2. Lembur Sabtu Minggu, 3. Lembur Hari Libur Nasional): &quot;))<br>            if jenis_lembur == 0 or jenis_lembur == 1 or jenis_lembur == 2 or jenis_lembur == 3:<br>                break<br>            else:<br>                print(&#39;ERROR! Pilihan tidak tersedia&#39;)<br>        except ValueError:<br>            print(&quot;Error! Input lembur harus berupa angka&quot;)<br><br>    jam_lembur_biasa = 0<br>    if jenis_lembur == 1:<br>        while True:<br>            try:<br>                jam_lembur_biasa = int(input(&#39;\nJam lembur: &#39;))<br>                if jam_lembur_biasa &lt; 0:<br>                    print(&quot;ERROR! Input tidak bisa negatif&quot;)<br>                else:<br>                    break<br>            except ValueError:<br>                print(&quot;ERROR! Input jam lembur harus berupa angka&quot;)<br><br>    jam_lembur_sabtuminggu = 0<br>    if jenis_lembur == 2:<br>        while True:<br>            try:<br>                jam_lembur_sabtuminggu = int(input(&#39;\nJam lembur:&#39;))<br>                if jam_lembur_sabtuminggu &lt; 0:<br>                    print(&quot;ERROR! Input tidak bisa negatif&quot;)<br>                else:<br>                    break<br>            except ValueError:<br>                print(&quot;ERROR! Input jam lembur harus berupa angka&quot;)<br><br>    jam_lembur_nasional = 0<br>    if jenis_lembur == 3:<br>        while True:<br>            try:<br>                jam_lembur_nasional = int(input(&#39;\nJam lembur:&#39;))<br>                if jam_lembur_nasional &lt; 0:<br>                    print(&quot;ERROR! Input tidak bisa negatif&quot;)<br>                else:<br>                    break<br>            except ValueError:<br>                print(&quot;ERROR! Input jam lembur harus berupa angka&quot;) </pre><ul><li>Depending on the overtime type selected by the user, the code prompts for the corresponding overtime hours.</li><li>It validates the input to ensure it is a non-negative integer.</li></ul><h4>Multiple Overtime Entries</h4><pre>while True:<br>        pilihan = input(&quot;Apakah ingin menginput lembur lagi? (Y/N): &quot;)<br>        if pilihan.lower() == &quot;y&quot;:<br>            while True:<br>                try:<br>                    jenis_lembur = int(input(&quot;\nApakah lembur? (0. Tidak Lembur, 1. Lembur Biasa, 2. Lembur Sabtu Minggu, 3. Lembur Hari Libur Nasional): &quot;))<br>                    if jenis_lembur == 0 or jenis_lembur == 1 or jenis_lembur == 2 or jenis_lembur == 3:<br>                        break<br>                    else:<br>                        print(&#39;ERROR! Pilihan tidak tersedia&#39;)<br>                except ValueError:<br>                    print(&quot;Error! Input lembur harus berupa angka&quot;)</pre><ul><li>After calculating the salary for the initial overtime entry, the code gives the option to input additional overtime.</li><li>If the user chooses to add more overtime, they can specify the type and hours for each additional entry.</li></ul><h4>Summary and Output</h4><pre>totalgaji = calculate_salary(jabatan, pendidikan, jam_lembur_biasa, jam_lembur_nasional, jam_lembur_sabtuminggu, jumlah_hari_kerja, jenis_lembur)<br><br>    if totalgaji is not None:<br>        print(&quot;\n=====================================SUMMARY==================================&quot;)<br>        print(&quot;\nNama              :&quot;, nama)<br>        print(&quot;Jabatan           :&quot;, jabatan)<br>        print(&quot;Pendidikan        :&quot;, pendidikan)<br>        print(&quot;Jumlah hari kerja :&quot;, jumlah_hari_kerja)<br>        print(&quot;Total gaji        : Rp. {:,.0f}&quot;.format(totalgaji).replace(&#39;,&#39;, &#39;.&#39;))<br>else:<br>    print(&quot;\nUsername atau password salah!&quot;)</pre><ul><li>Once all inputs are received, the code calculates the total salary using the “calculate_salary” function.</li><li>If a valid salary is obtained, it displays a summary of the user’s information along with the calculated salary.</li></ul><h3>Conclusion</h3><p>Gone are the days of manual salary calculations that were prone to errors and time-consuming. With Python, we can automate the process, making it more efficient and exciting. By breaking down the code snippet step by step, we have witnessed how Python empowers us to build advanced salary calculation systems effortlessly. So, why not embrace the power of Python and revolutionize your organization’s payroll management today? Happy coding!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1249eb953918" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>