<?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 Melat Teshome on Medium]]></title>
        <description><![CDATA[Stories by Melat Teshome on Medium]]></description>
        <link>https://medium.com/@mtadesse813?source=rss-5f3accb9f633------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*aEI-dfoclp0bnE5bapN_dg.jpeg</url>
            <title>Stories by Melat Teshome on Medium</title>
            <link>https://medium.com/@mtadesse813?source=rss-5f3accb9f633------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 01:17:56 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mtadesse813/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[SQL data warehouse project: production inspired architecture]]></title>
            <link>https://medium.com/@mtadesse813/sql-data-warehouse-project-production-inspired-architecture-d95eba6ec619?source=rss-5f3accb9f633------2</link>
            <guid isPermaLink="false">https://medium.com/p/d95eba6ec619</guid>
            <dc:creator><![CDATA[Melat Teshome]]></dc:creator>
            <pubDate>Mon, 10 Aug 2026 17:33:56 GMT</pubDate>
            <atom:updated>2026-08-13T07:50:05.304Z</atom:updated>
            <content:encoded><![CDATA[<p>When people think of SQL portfolio projects, they often picture a collection of queries answering business questions from a single dataset. While those projects are valuable for learning SQL, they don’t fully reflect how data is managed and transformed in real-world organizations like data modeling, data validation and such.</p><p>I decided to build a complete SQL data warehouse from the ground up. Rather than focusing solely on analysis, I designed a layered architecture that transforms raw operational data into clean, structured, and analytics-ready datasets. This project follows the Bronze, Silver, and Gold architecture commonly used in modern data platforms, demonstrating the complete journey from data ingestion to business-ready reporting.</p><p>lets get into it.</p><h4>Project Planning:</h4><p>The first thing I did is to plan the project so that I have a clear understanding and plan for the project. I created a project plan in Notion to define the overall scope, milestones, and deliverables.</p><p>The planning also included the major components of the warehouse, including data injestion , transformation dimentional modeling , validation and documentation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/401/1*jTAjt_fvaMEWb0rpNI-4Uw.png" /></figure><p><a href="https://app.notion.com/p/DATA-WAREHOUSE-PROJECT-WITH-SQL-3ad96933be06809088a2e201152b68a9?source=copy_link">here</a> is the link to the project plan on notion incase you want to take a deeper look into it.</p><h4>Analysing requirements</h4><p>In this phase of the project , usually you would setup a meeting with involved stakeholders and understand the end goal of the project but since this is a portfolio project I did all this by myself.</p><p>In our case, the goal is to develop a modern data warehouse using SQL Server that combines sales data into a single system for analytical reporting and better decision-making. The data comes from two source systems (ERP and CRM) and is provided as CSV files. Before loading the data into the warehouse, we must clean it and fix any data quality issues, since real-world data often contains missing, incorrect, or inconsistent values.</p><p>Another important requirement is data integration. The data from both source systems must be combined into a single, user-friendly data model that is optimized for analytics and reporting. The project only requires the latest version of the data, so there is no need to store historical records or implement historization.</p><p>Finally, we must document the completed data warehouse by providing clear descriptions of the data model. This documentation will help business users and analytics teams understand and use the data effectively.</p><h4><strong>Design Data A</strong>rchitecture</h4><p>Before deciding on the data architecture, I chose the data management first because it helps me choose the right data architecture for the project.</p><p>I had explored three options to choose from as my data management including data lake, data lakehouse, data warehouse. After assessing the use case of the project, the amount of data to be processed, and the type of data, I went with <strong>data warehousing</strong> because I will be working on a more structured data that needs a reliable foundation for reporting and analysing.</p><p>The next step was to choose a data architecture. I considered building a single-layer warehouse but rejected the approach and went with Medallion Architecture, It classifies the data into three layers namely Bronze , Silver and Gold layer. Because separating raw, cleaned, and business-ready data simplifies debugging, improves maintainability, and makes future transformations easier to implement.</p><p>This layered approach ensures that only high-quality data reaches business users which satisfies our needs of providing a high quality data for analysis and reporting.</p><p>After choosing the data management approach I went and drew a data architecture diagram with the details of each layer. I used the tool draw.io for the diagram.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*olt9ndkTjXZs4kczEJxTSg.png" /></figure><h4>Define Naming conventions</h4><p>Consistency is an important characteristic of maintainable data platforms.</p><p>Before implementation, I defined a naming convention that would be applied across schemas, tables, columns, stored procedures, and other database objects. The project follows the snake_case naming convention because it is widely adopted within SQL environments and provides consistent readability across the warehouse.</p><h3>Implementation</h3><p>The warehouse is organized into three database schemas corresponding to the Medallion Architecture:</p><ul><li>Bronze</li><li>Silver</li><li>Gold</li></ul><p>Each schema has a clearly defined responsibility and only performs the tasks appropriate for its layer. This separation of concerns keeps the pipeline modular while allowing future enhancements to be introduced without affecting unrelated parts of the warehouse.</p><pre>-- ============================================================<br>-- Data Warehouse Database Setup<br>-- MySQL version: 8.0+<br>-- Purpose:<br>--   Create logical warehouse layers:<br>--   - Bronze: raw/staging data<br>--   - Silver: cleaned/transformed data<br>--   - Gold: analytics/reporting data<br>-- ===========================================================<br>CREATE SCHEMA IF NOT EXISTS Bronze;<br>CREATE SCHEMA IF NOT EXISTS Silver;<br>CREATE SCHEMA IF NOT EXISTS Gold;</pre><h4>Bronze Layer</h4><p>Here the main objective is to perserve the raw data without applying any transformation and loading it to our server.</p><p>Before implementing the ingestion pipeline, I first analyzed the source systems from both business and technical perspectives</p><p><strong>Analyze the source system</strong> : analysing the source system includes understanding the bussinsess contex and understanding the technical architecture. To help me analyze the source system I had asked the following questions .</p><ol><li>To understand the bussiness context :</li></ol><ul><li>who owns the data ?</li><li>what bussiness process does the data support?</li><li>is there any existing documentation for the system(data catalog)?</li></ul><p>Here since this is a portfolio project I own the data , the data supports reporting and analysis of the bussiness, there is no existing documentation for the data.</p><p>2. To understand the technical architecture :</p><ul><li>how is the source system built ?</li><li>where is the data stored? sql server , cloud storage , on premises infrastructure ?</li><li>how can the data be extracted ? rest api? direct database connection ? csv files ? json files? kafka streaming</li></ul><p>Here the data is stored on my local machine as a csv file and it will be loaded to the sql server using file based injestion method.</p><p><strong>Plan data extraction : B</strong>efore I started to write any script for the bronze layer I had to decide on on how the data should be loaded choosing either from full load or incrimental load.</p><p>Because the data I am working with is small, Iwill be doing a <strong>full load</strong> to the system. This decision keeps the ingestion process straightforward while remaining appropriate for the project’s scale and requirements.</p><pre>/*<br>checks and drops the tables if they exist.<br>only exciute this query when trying to recreate the raw bronze layer.<br>*/<br>USE  Bronze;<br>DROP TABLE IF EXISTS crm_sales_details;<br>    <br>CREATE TABLE Bronze.crm_cust_info(<br> cst_id INT,<br>    cst_key char(50),<br>    cst_firstname CHAR(50) ,<br>    cst_lastname CHAR(50),<br>    cst_marital_status CHAR(50),<br>    cst_gndr char(50),<br>    cst_create_date date<br>);</pre><p>After creating the table structure the next task is to load the data to the created schema .</p><pre>USE Bronze;<br><br>    -- ========================================================<br>    -- Load CRM Customer Info<br>    -- ========================================================<br>    SELECT &#39;Loading CRM Customer Info...&#39; AS message;<br>    TRUNCATE TABLE Bronze.crm_cust_info;<br>    LOAD DATA INFILE &#39;/var/lib/mysql-files/source_crm/cust_info_valid.csv&#39;<br>    INTO TABLE Bronze.crm_cust_info<br>    FIELDS TERMINATED BY &#39;,&#39;<br>    OPTIONALLY ENCLOSED BY &#39;&quot;&#39;<br>    LINES TERMINATED BY &#39;\n&#39;<br>    IGNORE 1 ROWS<br>    (<br>        @cst_id,<br>        @cst_key,<br>        @cst_firstname,<br>        @cst_lastname,<br>        @cst_marital_status,<br>        @cst_gndr,<br>        @cst_create_date<br>    )<br>    SET<br>        cst_id             = NULLIF(TRIM(@cst_id), &#39;&#39;),<br>        cst_key            = NULLIF(TRIM(@cst_key), &#39;&#39;),<br>        cst_firstname      = NULLIF(TRIM(@cst_firstname), &#39;&#39;),<br>        cst_lastname       = NULLIF(TRIM(@cst_lastname), &#39;&#39;),<br>        cst_marital_status = NULLIF(TRIM(@cst_marital_status), &#39;&#39;),<br>        cst_gndr           = NULLIF(TRIM(@cst_gndr), &#39;&#39;),<br>        cst_create_date    = NULLIF(<br>            TRIM(TRAILING &#39;\r&#39; FROM @cst_create_date),<br>            &#39;&#39;<br>        );<br>    SELECT ROW_COUNT() AS crm_customer_rows_loaded;<br><br>    -- ========================================================<br>    -- Validate Loaded Row Counts<br>    -- ========================================================<br>    SELECT &#39;Validating loaded row counts...&#39; AS message;<br>    SELECT<br>        &#39;crm_cust_info&#39; AS table_name,<br>        COUNT(*) AS row_count<br>    FROM Bronze.crm_cust_info<br>    SELECT &#39;Bronze layer data load completed.&#39; AS message;</pre><p>After loading data into the Bronze layer, I had performed validation such as record count validation and schema validation to ensure that the ingestion process completed successfully.</p><p>These checks provide confidence that the Bronze layer accurately represents the source data.</p><p>At this point, the Bronze layer contains an accurate representation of the source systems and is ready for cleansing and transformation in the Silver layer.</p><h4>Silver Layer</h4><p>In this layer the main objective is to transform the Bronze layer data into a clean, standardized, and reliable dataset that is ready for analytical modeling. To achieve this I started by exploring the data from the bronze table and identifying relationships between entities. To help me visualize the relationship between entities I had used drawio to draw a data integration diagram. This analysis guided the transformation logic and ensured data from the CRM and ERP system could be integrated properly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1010/1*OQg4vSFUFeYHKXw9-svC_g.jpeg" /></figure><p>Before starting data transformation I checked each table from the bronze layer if they had issues like duplicated primary key, null values, inconsistent categorical values, unwanted whitespace, invalid date ranges, and missing business attributes. After identifying these issues, I developed SQL transformation pipeline to cleanse and standardize the data before loading it into the Silver schema.</p><p>The transformation process included:</p><ul><li>Removing duplicate records using window functions (ROW_NUMBER()) while retaining the most recent version of each business entity.</li><li>Standardizing categorical values by replacing abbreviated codes (e.g., M, F, S) with descriptive business values and assigning default values for missing data.</li><li>Cleaning string columns by trimming leading and trailing whitespace.</li><li>Handling missing numeric values using appropriate default values where permitted by business rules.</li><li>Deriving new business attributes, such as extracting category identifiers and product keys from composite product codes.</li><li>Correcting historical product records by reconstructing valid effective date ranges using SQL window functions (LEAD()), eliminating overlapping validity periods while preserving historical accuracy.</li><li>Adding metadata columns (e.g., DW_CreateDate) to support auditing, lineage, and operational monitoring.</li></ul><pre><br>DROP PROCEDURE IF EXISTS Silver.load_Silver;<br>DELIMITER $$<br>CREATE PROCEDURE Silver.load_Silver()<br>BEGIN<br>    /*=========================================================================<br>      Loading Silver.crm_cust_info<br>    =========================================================================*/<br>    SET v_start_time = NOW();<br>    SELECT &#39;&gt;&gt; Truncating Table: Silver.crm_cust_info&#39; AS log_message;<br>    TRUNCATE TABLE Silver.crm_cust_info;<br>    SELECT &#39;&gt;&gt; Inserting Data Into: Silver.crm_cust_info&#39; AS log_message;<br>    INSERT INTO Silver.crm_cust_info<br>    (<br>        cst_id,<br>        cst_key,<br>        cst_firstname,<br>        cst_lastname,<br>        cst_marital_status,<br>        cst_gndr,<br>        cst_create_date<br>    )<br>    SELECT<br>        cst_id,<br>        cst_key,<br>        TRIM(cst_firstname) AS cst_firstname,<br>        TRIM(cst_lastname) AS cst_lastname,<br>        CASE<br>            WHEN UPPER(TRIM(cst_marital_status)) = &#39;S&#39; THEN &#39;Single&#39;<br>            WHEN UPPER(TRIM(cst_marital_status)) = &#39;M&#39; THEN &#39;Married&#39;<br>            ELSE &#39;n/a&#39;<br>        END AS cst_marital_status,<br>        CASE<br>            WHEN UPPER(TRIM(cst_gndr)) = &#39;F&#39; THEN &#39;Female&#39;<br>            WHEN UPPER(TRIM(cst_gndr)) = &#39;M&#39; THEN &#39;Male&#39;<br>            ELSE &#39;n/a&#39;<br>        END AS cst_gndr,<br>        cst_create_date<br>    FROM<br>    (<br>        SELECT<br>            bronze_customer.*,<br>            ROW_NUMBER() OVER<br>            (<br>                PARTITION BY cst_id<br>                ORDER BY cst_create_date DESC<br>            ) AS flag_last<br>        FROM bronze.crm_cust_info AS bronze_customer<br>        WHERE cst_id IS NOT NULL<br>    ) AS ranked_customer<br>    WHERE flag_last = 1;<br>    SET v_end_time = NOW();<br>    SELECT CONCAT(<br>        &#39;&gt;&gt; Load Duration: &#39;,<br>        TIMESTAMPDIFF(SECOND, v_start_time, v_end_time),<br>        &#39; seconds&#39;<br>    ) AS log_message;<br>    SELECT &#39;&gt;&gt; -------------&#39; AS log_message;<br><br>    /*=========================================================================<br>      Batch completion<br>    =========================================================================*/<br>    SET v_batch_end_time = NOW();<br>    SELECT &#39;==========================================&#39; AS log_message<br>    UNION ALL<br>    SELECT &#39;Loading Silver Layer is Completed&#39;<br>    UNION ALL<br>    SELECT CONCAT(<br>        &#39;Total Load Duration: &#39;,<br>        TIMESTAMPDIFF(<br>            SECOND,<br>            v_batch_start_time,<br>            v_batch_end_time<br>        ),<br>        &#39; seconds&#39;<br>    )<br>    UNION ALL<br>    SELECT &#39;==========================================&#39;;<br>END$$<br>DELIMITER ;<br></pre><p>After loading the data into the Silver layer, the next step before proceeding to the Gold layer was to validate the transformed data. To ensure the data met the required quality standards, I wrote a set of SQL validation scripts to perform comprehensive data quality checks.</p><pre>-- ====================================================================<br>-- Checking &#39;Silver.crm_cust_info&#39;<br>-- ====================================================================<br>-- Check for NULLs or Duplicates in Primary Key<br>-- Expectation: No Results<br>SELECT <br>    cst_id,<br>    COUNT(*) <br>FROM Silver.crm_cust_info<br>GROUP BY cst_id<br>HAVING COUNT(*) &gt; 1 OR cst_id IS NULL;</pre><h4>Data Model</h4><p>With the Silver layer complete, the warehouse now contains clean, standardized, and validated datasets that accurately represent the underlying business entities.</p><p>The next architectural decision was selecting an analytical data model for the Gold layer.</p><p>I had considered two data models, star schema and snowflake. I decided to go with the Star Schema because it works well for reporting and analytics. It has fewer joins than the Snowflake Schema, making queries easier to write and maintain. Since storage optimization was not a priority for this project, the Star Schema was the better choice.</p><h4>Gold Layer</h4><p>This is the final layer of the architecture and where cleaned and validated data is transformed into bussiness friendly model optimized for reporting and analysis.</p><p>After I had decided on the star schema as my data model, I then went to identify the bussiness objects across the CRM and ERP source data. I grouped related tables under the same business entity before beginning the integration process.</p><p>The <strong>Customer Dimension</strong> is built by merging customer information from CRM and ERP systems, assigning user-friendly column names, and generating a <strong>surrogate key</strong> to uniquely identify each customer. Similarly, the <strong>Product Dimension</strong> combines product details with category information, filters out historical records to retain only current products, and creates a surrogate key for consistent relationships.</p><pre>-- ====================================================================<br>-- Create Dimension: gold.dim_customers<br>-- ====================================================================<br><br>CREATE VIEW Gold.dim_customers AS<br>SELECT <br>    ROW_NUMBER() OVER (ORDER BY cst_id) AS customer_key,<br>    ci.cst_id AS cusomer_id,<br>    ci.cst_key AS cusomer_number,<br>    ci.cst_firstname AS first_name,<br>    ci.cst_lastname AS last_name,<br>    ci.cst_marital_status AS martial_status,<br>    CASE<br>        WHEN ci.cst_gndr != &#39;n/a&#39; THEN ci.cst_gndr<br>        ELSE COALESCE(ca.gen, &#39;n/a&#39;)<br>    END AS gender,<br>    ci.cst_create_date AS created_date,<br>    ci.dwh_create_date,<br>    ca.bdate AS birth_date,<br>    la.cntry AS country<br>FROM Silver.crm_cust_info ci<br>LEFT JOIN Silver.erp_cust_az12 ca<br>    ON ci.cst_key = ca.cid<br>LEFT JOIN Silver.erp_loc_a101 la<br>    ON ci.cst_key = la.cid;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*0cFlq7JfaeD_yVEXs85Rlg.png" /></figure><p>Data validation for gold layer</p><pre>-- Checking &#39;Gold.dim_customers&#39;<br>-- ====================================================================<br>-- Check for Uniqueness of Customer Key in Gold.dim_customers<br>-- Expectation: No results <br>SELECT <br>    customer_key,<br>    COUNT(*) AS duplicate_count<br>FROM Gold.dim_customers<br>GROUP BY customer_key<br>HAVING COUNT(*) &gt; 1;<br></pre><h4>Data flow</h4><p>To improve maintainability and simplify future development, I documented the complete movement of data throughout the warehouse.</p><p>The documentation captures how data flows from the CRM and ERP source systems through the Bronze, Silver, and Gold layers, illustrating where transformations occur and how business entities are progressively refined throughout the pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/871/1*-Ov028JonvdIr3rDgXrhvQ.png" /></figure><h4>Conclusion</h4><p>This project demonstrates the complete process of building a production-inspired SQL data warehouse, from raw data ingestion to a business-ready analytical model. Along the way, I implemented ETL pipelines, data quality validation, dimensional modeling, and documentation while following industry-standard data warehousing practices. It provided valuable hands-on experience in designing scalable, maintainable, and analytics-ready data solutions.</p><p>Below I have attched the github repo please go and explore.</p><p><strong>Github Repo Link</strong> — <a href="https://github.com/melatteshome/sql-data-warehouse-project/tree/main"><em>project_link</em></a></p><p><strong>Project Plan — </strong><a href="https://app.notion.com/p/DATA-WAREHOUSE-PROJECT-WITH-SQL-3ad96933be06809088a2e201152b68a9"><em>link</em></a></p><p>If you have enjoyed the post please leave a 👏 and a comment, i highly apprciate your thought.</p><p>Lets Connect on LinkedIn <a href="http://linkedin.com/in/melat-teshome">MelatTeshome</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d95eba6ec619" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Pipeline with real time data with kafka, apache spark , airflow , databend and hadoop.]]></title>
            <link>https://medium.com/@mtadesse813/data-pipeline-with-real-time-data-with-kafka-apache-spark-airflow-databend-and-hadoop-7969f3708b0a?source=rss-5f3accb9f633------2</link>
            <guid isPermaLink="false">https://medium.com/p/7969f3708b0a</guid>
            <dc:creator><![CDATA[Melat Teshome]]></dc:creator>
            <pubDate>Fri, 22 Aug 2025 20:14:21 GMT</pubDate>
            <atom:updated>2026-03-02T07:35:23.529Z</atom:updated>
            <content:encoded><![CDATA[<h3>A Real-Time Data Pipelining with Kafka, Apache Spark, Airflow, Databend, and Hadoop</h3><p>In today’s data driven world, businesses need to react to customer behavior in real-time. Whether it’s a flash sale of products or personalizing user experience, the ability to process and analyze streaming data is crucial. This article walks you through building a robust, end-to-end data pipeline to capture, process, and store real-time clickstream data, making it ready for immediate analysis.</p><p>The goal of this project is to build a scalable data pipeline that ingests real-time user clickstream events from a mock e-commerce website. We will process the synthetically generated data to make it analysis ready and store it in a modern data warehouse, all orchestrated to run automatically.</p><p>IN this article I will guide you through building a data pipeline. We’ll get data from a data generating script, stream it using Kafka, orchestrate the process with Airflow, transform it with Spark, and store it in a locally hosted data platform called Databend, using Hadoop as a lakehouse.</p><p>The article will focus on practical application rather than the theoretical aspects of the tools utilized in the project. For a detailed understanding of the tools, there are many resources that I will attach at the end of the article.</p><h4>Overview</h4><p>lets breakdown the process of the data pipeline process:</p><ol><li>Data Streaming: Initially, data is streamed from our data-generating script into a Kafka topic.</li><li>Data Ingesting: Synthetic data (product, transaction, promo, and customer) is generated and stored in a CSV file format, which is then ingested into Hadoop and subsequently transferred to Databend in a timely manner</li><li>Data Processing: A Spark job then takes over, consuming the data from the Kafka topic and transferring it to Databend.</li><li>Scheduling: Both the streaming task and the Spark job are orchestrated using Apache Airflow. In a real-world scenario, Kafka producers would constantly listen to streaming data, but for this project, we will schedule the Kafka streaming task to run at a specific time of the day. Once the streaming is complete, the Spark job will transform the data and make it ready for analysis.</li></ol><p><strong>Why I chose the tools used in this project</strong></p><ul><li>Kafka: Chosen for its high-throughput, fault-tolerant, and real-time messaging capabilities, making it the industry standard for streaming data.</li><li>Apache Spark: Selected for its powerful distributed processing engine, ideal for transforming and enriching our streaming data at scale.</li><li>Airflow: Used for its robustness in orchestrating complex workflows, ensuring our data generation and processing tasks run reliably and on schedule.</li><li>Databend: A modern, cost-effective cloud data warehouse chosen for its impressive query performance and scalability.</li><li>Hadoop (HDFS): Utilized as our data lake for storing raw, unstructured data, providing a durable and scalable storage foundation.</li></ul><p>All the tools will be built using Docker on our local machine. I will be containerizing each tool in a different container and connecting them via a Docker network; using Docker Compose is also another option. This article assumes the readers can set up and containerize tools comfortably.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/802/1*lz2h3CkdA79_x-7Tev9_lw.png" /></figure><p>Now that we have seen the flow of our pipeline , lets dive into the neet and greet of it.</p><h4>Data Generation</h4><p>For this project, we will generate two types of data. The first is <strong>retail data</strong>, which will be stored in our data lake (Hadoop). The second is <strong>clickstream event data</strong>, which will simulate real-time streaming data from a retail website.</p><ol><li>Retail Data :This data includes customers, products, promotions, and transactions. We’ll create a separate function for each type of data. However, when generating transaction data, we need to account for the previously generated customer and product data — since transactions can only exist in the context of existing customers and products. For that reason, transaction data will be generated last, after both customer and product data are in place.</li></ol><pre><br>def make_products(n: int, fake: Faker) -&gt; pd.DataFrame:<br>    categories = [&quot;Electronics&quot;, &quot;Home&quot;, &quot;Fashion&quot;, &quot;Sports&quot;, &quot;Beauty&quot;]<br>    df = pd.DataFrame(<br>        {<br>            &quot;product_id&quot;: range(1, n + 1),<br>            &quot;name&quot;: [fake.unique.catch_phrase() for _ in range(n)],<br>            &quot;category&quot;: np.random.choice(categories, size=n),<br>            &quot;unit_price&quot;: np.round(np.random.uniform(5.0, 999.0, size=n), 2),<br>            &quot;inventory_qty&quot;: np.random.randint(0, 2_000, size=n),<br>        }<br>    )<br>    return df<br><br><br>def make_customers(n: int, fake: Faker) -&gt; pd.DataFrame:<br>    df = pd.DataFrame(<br>        {<br>            &quot;customer_id&quot;: range(1, n + 1),<br>            &quot;first_name&quot;: [fake.first_name() for _ in range(n)],<br>            &quot;last_name&quot;: [fake.last_name() for _ in range(n)],<br>            &quot;email&quot;: [fake.unique.email() for _ in range(n)],<br>            &quot;phone&quot;: [fake.phone_number() for _ in range(n)],<br>            &quot;country&quot;: [fake.country_code() for _ in range(n)],<br>            &quot;signup_date&quot;: [<br>                fake.date_between(start_date=&quot;-4y&quot;, end_date=&quot;today&quot;) for _ in range(n)<br>            ],<br>        }<br>    )<br>    return df<br><br><br>def make_promos(n: int, fake: Faker) -&gt; pd.DataFrame:<br>    df = pd.DataFrame(<br>        {<br>            &quot;promo_code&quot;: [fake.unique.bothify(text=&quot;????-##&quot;) for _ in range(n)],<br>            &quot;description&quot;: [fake.sentence(nb_words=6) for _ in range(n)],<br>            &quot;discount_pct&quot;: np.random.choice([5, 10, 15, 20, 25, 30], size=n),<br>            &quot;start_date&quot;: [<br>                fake.date_between(start_date=&quot;-2y&quot;, end_date=&quot;-1y&quot;) for _ in range(n)<br>            ],<br>        }<br>    )<br>    df[&quot;end_date&quot;] = df[&quot;start_date&quot;] + pd.to_timedelta(<br>        np.random.randint(15, 90, size=n), unit=&quot;D&quot;<br>    )<br>    return df<br><br><br>def make_transactions(<br>    n: int,<br>    fake: Faker,<br>    products: pd.DataFrame,<br>    customers: pd.DataFrame,<br>    promos: pd.DataFrame,<br>) -&gt; pd.DataFrame:<br>    product_ids = products[&quot;product_id&quot;].values<br>    customer_ids = customers[&quot;customer_id&quot;].values<br>    promo_codes = promos[&quot;promo_code&quot;].values<br><br>    dates = [<br>        fake.date_time_between(start_date=&quot;-365d&quot;, end_date=&quot;now&quot;) for _ in range(n)<br>    ]<br>    df = pd.DataFrame(<br>        {<br>            &quot;transaction_id&quot;: range(1, n + 1),<br>            &quot;customer_id&quot;: np.random.choice(customer_ids, size=n),<br>            &quot;product_id&quot;: np.random.choice(product_ids, size=n),<br>            &quot;promo_code&quot;: np.random.choice(<br>                np.append(promo_codes, [None] * 4),  # 80 % of orders have no promo<br>                size=n,<br>            ),<br>            &quot;quantity&quot;: np.random.randint(1, 5, size=n),<br>            &quot;transaction_timestamp&quot;: dates,<br>        }<br>    )<br><br>    # Add pricing fields (calculated, not random)<br>    prod_price_map = products.set_index(&quot;product_id&quot;)[&quot;unit_price&quot;].to_dict()<br>    promo_map = promos.set_index(&quot;promo_code&quot;)[&quot;discount_pct&quot;].to_dict()<br><br>    df[&quot;unit_price&quot;] = df[&quot;product_id&quot;].map(prod_price_map)<br>    df[&quot;discount_pct&quot;] = df[&quot;promo_code&quot;].map(promo_map).fillna(0).astype(int)<br>    df[&quot;gross_amount&quot;] = df[&quot;unit_price&quot;] * df[&quot;quantity&quot;]<br>    df[&quot;net_amount&quot;] = df[&quot;gross_amount&quot;] * (1 - df[&quot;discount_pct&quot;] / 100.0)<br><br>    return df<br></pre><p>The code generates data and stores each data as a csv file in a folder called “generated_data”.</p><p>2. Click stream event data: This data will also take into account the previously generated customer and product data. It will be streamed to Kafka as real-time clickstream events. To simulate a live environment, the data will be scheduled to publish events to a Kafka topic at regular intervals, such as every few minutes.</p><pre><br>EVENT_TYPES = [&quot;page_view&quot;, &quot;add_to_cart&quot;, &quot;checkout&quot;, &quot;purchase&quot; , &quot;Order Completed&quot;, &quot;Product Shared&quot;, &quot;Product Reviewed&quot;, &quot;select_promotion&quot;, &quot;Products Searched&quot;, &quot;Product List Viewed&quot;, &quot;Product Clicked&quot;]<br>EVENT_PROBS: Tuple[float, ...] = (0.55, 0.30, 0.10, 0.05)<br><br><br>class ClickStreamProvider(BaseProvider):<br>    _customer_ids: list[int] | None = None<br>    _product_ids:  list[int] | None = None<br><br>    @classmethod<br>    def configure(cls, customers, products) -&gt; None:<br>        cls._customer_ids = list(customers)<br>        cls._product_ids  = list(products)<br><br>    def click_event(self) -&gt; dict:<br>        if self._customer_ids is None or self._product_ids is None:<br>            raise RuntimeError(<br>                &quot;ClickStreamProvider not configured. &quot;<br>                &quot;Call ClickStreamProvider.configure(customer_ids, product_ids) first.&quot;<br>            )<br><br>        return {<br>            &quot;user_id&quot;:    random.choice(self._customer_ids),<br>            &quot;product_id&quot;: random.choice(self._product_ids),<br>            &quot;event_type&quot;: random.choices(EVENT_TYPES, weights=EVENT_PROBS, k=1)[0],<br>            &quot;timestamp&quot;:  self.generator.date_time_between(<br>                              start_date=&quot;-30d&quot;, end_date=&quot;now&quot;, tzinfo=timezone.utc<br>                          ).isoformat(),<br>        }<br><br></pre><h4><strong>Data Ingestion</strong></h4><p>Hadoop<br>The retail data(product , transaction , promo and transaction) that was generated earlier will now be ingested to hadoop our data lakehouse. to achive this we will need to dockerize hadoop on our local machine and connect it to the network that was created for all the tools.</p><pre>def upload_generated_data_to_hdfs(local_csv_path, hdfs_target_path, docker_container_name=&quot;hadoop-namenode&quot;):<br><br>    try:<br>        print(&quot;Copying file into the Hadoop Docker container...&quot;)<br>        subprocess.run([<br>            &quot;docker&quot;, &quot;cp&quot;, local_csv_path,<br>            f&quot;{docker_container_name}:/tmp/data.csv&quot;<br>        ], check=True)<br>    except subprocess.CalledProcessError as e:<br>        print(&quot; Failed to copy file into the container:&quot;, e)<br>        return<br><br>    try:<br>        print(&quot;Uploading file to HDFS...&quot;)<br>        subprocess.run([<br>            &quot;docker&quot;, &quot;exec&quot;, docker_container_name,<br>            &quot;hdfs&quot;, &quot;dfs&quot;, &quot;-put&quot;, &quot;-f&quot;, &quot;/tmp/data.csv&quot;, hdfs_target_path<br>        ], check=True)<br>        print(f&quot; File successfully uploaded to HDFS at: {hdfs_target_path}&quot;)<br>    except subprocess.CalledProcessError as e:<br>        print(&quot;Failed to upload file to HDFS:&quot;, e)<br><br><br><br></pre><p>after excuting the above code we have stored our unstrucutred generated data that is stored in a “csv” file format to our lake house . after loading the data our namenode will look like the following.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QTWPCmojDxFFl0ExOQQbDQ.png" /></figure><p>Databend</p><p>After dockerizing databend and connecting it to the same network for all the tools, we will create a schema for each data including product, customer , promo , transaction and click_event. This will allow us to structure our unstrucutred data. <br>earlier we have transfered our unstrucutred data to hadoop now we will be transferring it to databend while also creating a schema and structure for our data.</p><p>steps</p><ul><li>Enable WebHDFS in Hadoop by setting dfs.webhdfs.enabled=true in the Hadoop configuration.</li><li>Ensure both Hadoop and Databend containers are on the same network(the network created for all teh tools).</li><li>Configure Databend storage to point to WebHDFS.</li><li>Run Databend (both <strong>meta</strong> and <strong>query</strong> services) with the updated configuration.</li><li>Create a WebHDFS stage in Databend to load data stored in HDFS.</li></ul><h4>Data Streaming</h4><p>This script is a tiny Kafka “traffic simulator”: it uses Faker with our ClickStreamProvider (the class defined for the generation of click stream data ) to invent realistic user and purchase events for a simple catalog (100 customers, 50 products), then publishes them to Kafka.</p><pre><br><br>def ensure_topics(admin: AdminClient, topics: list[str], partitions=3, rf=1):<br>    new = [NewTopic(t, num_partitions=partitions, replication_factor=rf) for t in topics]<br>    futures = admin.create_topics(new)<br>    for t, f in futures.items():<br>        try:<br>            f.result()<br>            print(f&quot;created topic {t}&quot;)<br>        except Exception as e:<br>            if &quot;TopicExists&quot; in str(e) or &quot;already exists&quot; in str(e):<br>                pass  # ok<br>            else:<br>                raise<br><br>def topic_for_event(event: dict) -&gt; str:<br>    return ORDER_TOPIC if event[&quot;event_type&quot;] == &quot;purchase&quot; else USER_TOPIC<br><br>def delivery_cb(err, msg):<br>    if err:<br>        print(f&quot;delivery failed: {err}&quot;)<br>    else:<br>        print(f&quot;→ {msg.topic()} key={msg.key().decode()} offset={msg.offset()}&quot;)<br><br><br><br><br>def main(n=50):<br>    env_file =&#39;.env&#39;<br>    if Path(env_file).exists():<br>        load_dotenv(env_file)<br><br>        BOOTSTRAP = os.getenv(&quot;bootstrap&quot;) <br>        USER_TOPIC = os.getenv(&quot;user_topic&quot;) <br>        ORDER_TOPIC = os.getenv(&quot;order_topic&quot;) <br><br>    customers = pd.DataFrame({&quot;customer_id&quot;: range(1, 101)})<br>    products  = pd.DataFrame({&quot;product_id&quot;:  range(1, 51)})<br><br>    fake = Faker()<br>    ClickStreamProvider.configure(customers[&quot;customer_id&quot;], products[&quot;product_id&quot;])<br>    fake.add_provider(ClickStreamProvider)<br><br>    admin = AdminClient({&quot;bootstrap.servers&quot;: BOOTSTRAP})<br>    ensure_topics(admin, [USER_TOPIC, ORDER_TOPIC])<br><br>    producer = Producer({<br>        &quot;bootstrap.servers&quot;: BOOTSTRAP,<br>        &quot;enable.idempotence&quot;: True,<br>        &quot;acks&quot;: &quot;all&quot;,<br>        &quot;linger.ms&quot;: 5<br>    })<br><br>    for _ in range(n):<br>        event = fake.click_event()<br>        topic = topic_for_event(event)<br>        producer.produce(<br>            topic=topic,<br>            key=str(event[&quot;user_id&quot;]),<br>            value=json.dumps(event).encode(),<br>            headers=[(&quot;event_type&quot;, event[&quot;event_type&quot;].encode()),<br>                     (&quot;schema&quot;, b&quot;v1&quot;)],<br>            callback=delivery_cb<br>        )<br><br>    producer.flush()<br><br>if __name__ == &quot;__main__&quot;:<br>    main(n=100)</pre><p>just like shown below on the kafka UI, we created two topics that devides the generated click stream event into two namely order-events and user-events both used for different purposes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mqsxhJ85kgf2XSzgmdp8zA.png" /></figure><h4>Data Transforming</h4><p>After setup for apache spark is done , we define a spark job that reads data from kafka and loads it to our data storage databend.</p><pre><br>DATABEND_HOST      = os.getenv(&quot;DATABEND_HOST&quot;)   <br>DATABEND_PORT      = os.getenv(&quot;DATABEND_PORT&quot;)        <br>DATABEND_DB        = os.getenv(&quot;DATABEND_DB&quot;)<br>DATABEND_USER      = os.getenv(&quot;DATABEND_USER&quot;)<br>DATABEND_PASSWORD  = os.getenv(&quot;DATABEND_PASSWORD&quot;)<br>DATABEND_TABLE     = os.getenv(&quot;DATABEND_TABLE&quot;)<br>DATABEND_JDBC_URL = (<br>    f&quot;jdbc:mysql://{DATABEND_HOST}:{DATABEND_PORT}/{DATABEND_DB}&quot;<br>    &quot;?useSSL=false&amp;session_timezone=UTC&amp;rewriteBatchedStatements=true&quot;<br>)<br><br><br>KAFKA_BOOTSTRAP = os.getenv(&quot;KAFKA_BOOTSTRAP&quot;, &quot;kafka:9092&quot;)<br>KAFKA_TOPIC     = os.getenv(&quot;KAFKA_TOPIC&quot;, &quot;order-events&quot;)<br><br><br>CHECKPOINT_LOCATION = os.getenv(&quot;CHECKPOINT_LOCATION&quot;, &quot;/tmp/checkpoints/kafka_order_events&quot;)<br><br>def create_spark_session() -&gt; SparkSession:<br>    spark = (<br>        SparkSession.builder<br>        .appName(&quot;KafkaClickstreamIngest -&gt; Databend&quot;)<br>        .config(<br>            &quot;spark.jars.packages&quot;,<br>            &quot;,&quot;.join(<br>                &quot;org.apache.spark:spark-sql-kafka-0-10_2.13:4.0.0&quot;,<br>                &quot;mysql:mysql-connector-j:8.4.0&quot;<br>            ])<br>        )<br>        .getOrCreate()<br>    )<br>    logging.info(&quot;Spark session created successfully&quot;)<br>    return spark<br><br>def read_streaming_data_from_kafka(spark_session):<br>    try:<br>        raw_data = (<br>            spark_session.readStream<br>            .format(&quot;kafka&quot;)<br>            .option(&quot;kafka.bootstrap.servers&quot;, KAFKA_BOOTSTRAP)<br>            .option(&quot;subscribe&quot;, KAFKA_TOPIC)<br>            .option(&quot;startingOffsets&quot;, &quot;latest&quot;)<br>            .option(&quot;failOnDataLoss&quot;, &quot;false&quot;)<br>            .load()<br>        )<br>        logging.info(&quot;Raw data read successfully&quot;)<br>        return raw_data<br>    except Exception as e:<br>        logging.warning(f&quot;reading raw data was unsuccessful due to: {e}&quot;)<br>        raise<br><br><br>def create_schema_parsed_dataframe(raw_data):<br>    json_str = raw_data.selectExpr(&quot;CAST(value AS STRING) AS json_str&quot;)<br><br>    schema = StructType([<br>        StructField(&quot;user_id&quot;, StringType()),<br>        StructField(&quot;product-id&quot;, StringType()),<br>        StructField(&quot;event_type&quot;, StringType()),<br>        StructField(&quot;timestamp&quot;, StringType()),<br>    ])<br><br>    parsed = json_str.select(from_json(col(&quot;json_str&quot;), schema).alias(&quot;data&quot;))<br><br>    events = (<br>        parsed.select(<br>            col(&quot;data.user_id&quot;).alias(&quot;user_id&quot;),<br>            col(&quot;data.`product-id`&quot;).alias(&quot;product_id&quot;),<br>            col(&quot;data.event_type&quot;).alias(&quot;event_type&quot;),<br>            # If not ISO8601, pass the exact pattern in the 2nd arg<br>            to_timestamp(col(&quot;data.timestamp&quot;)).alias(&quot;event_time&quot;)<br>        )<br>    )<br>    return events<br><br><br>def write_batch_to_databend(batch_df, batch_id: int):<br>    if batch_df.isEmpty():<br>        return<br><br>    (batch_df<br>        .write<br>        .mode(&quot;append&quot;)<br>        .format(&quot;jdbc&quot;)<br>        .option(&quot;url&quot;, DATABEND_JDBC_URL)<br>        .option(&quot;dbtable&quot;, DATABEND_TABLE)<br>        .option(&quot;user&quot;, DATABEND_USER)<br>        .option(&quot;password&quot;, DATABEND_PASSWORD)<br>        .option(&quot;driver&quot;, &quot;com.mysql.cj.jdbc.Driver&quot;)<br>        .option(&quot;batchsize&quot;, &quot;5000&quot;)<br>        .option(&quot;truncate&quot;, &quot;false&quot;)<br>        .save()<br>    )<br>    logging.info(f&quot;Wrote batch_id={batch_id} to Databend table={DATABEND_TABLE}&quot;)<br><br><br>def start_streaming_data():<br>    spark = create_spark_session()<br>    raw_data = read_streaming_data_from_kafka(spark_session=spark)<br>    final_df = create_schema_parsed_dataframe(raw_data=raw_data)<br><br>   <br>    final_df = final_df.select(&quot;user_id&quot;, &quot;product_id&quot;, &quot;event_type&quot;, &quot;event_time&quot;)<br><br>    query = (<br>        final_df.writeStream<br>        .outputMode(&quot;append&quot;)<br>        .foreachBatch(write_batch_to_databend)<br>        .option(&quot;checkpointLocation&quot;, CHECKPOINT_LOCATION)<br>        .start()<br>    )<br><br>    logging.info(&quot;Streaming query started. Writing to Databend (local Docker) ...&quot;)<br>    query.awaitTermination()<br><br>if __name__ == &quot;__main__&quot;:<br>    start_streaming_data()<br></pre><p>Let’s break down the key highlights and functionalities of the spark job:</p><ol><li>first we create spark session:</li></ol><pre>def create_spark_session() -&gt; SparkSession:<br>    spark = (<br>        SparkSession.builder<br>        .appName(&quot;KafkaClickstreamIngest -&gt; Databend&quot;)<br>        .config(<br>            &quot;spark.jars.packages&quot;,<br>            &quot;,&quot;.join(<br>                &quot;org.apache.spark:spark-sql-kafka-0-10_2.13:4.0.0&quot;,<br>                &quot;mysql:mysql-connector-j:8.4.0&quot;<br>            ])<br>        )<br>        .getOrCreate()<br>    )<br>    logging.info(&quot;Spark session created successfully&quot;)<br>    return spark</pre><p>2. The read_streaming_data_from_kafka function accepts spark session as an arumet and reads raw stream data from kafka topic</p><pre>def read_streaming_data_from_kafka(spark_session):<br>    try:<br>        raw_data = (<br>            spark_session.readStream<br>            .format(&quot;kafka&quot;)<br>            .option(&quot;kafka.bootstrap.servers&quot;, KAFKA_BOOTSTRAP)<br>            .option(&quot;subscribe&quot;, KAFKA_TOPIC)<br>            .option(&quot;startingOffsets&quot;, &quot;latest&quot;)<br>            .option(&quot;failOnDataLoss&quot;, &quot;false&quot;)<br>            .load()<br>        )<br>        logging.info(&quot;Raw data read successfully&quot;)<br>        return raw_data<br>    except Exception as e:<br>        logging.warning(f&quot;reading raw data was unsuccessful due to: {e}&quot;)<br>        raise</pre><p>3. Once the data is injested from kafka topic, create_parsed_dataframe function then applies a schema (defined by the columns DB_FIELDS) to the incoming JSON data, ensuring that the data is structured and ready for further processing.</p><pre>def create_schema_parsed_dataframe(raw_data):<br>    json_str = raw_data.selectExpr(&quot;CAST(value AS STRING) AS json_str&quot;)<br><br>    schema = StructType([<br>        StructField(&quot;user_id&quot;, StringType()),<br>        StructField(&quot;product-id&quot;, StringType()),<br>        StructField(&quot;event_type&quot;, StringType()),<br>        StructField(&quot;timestamp&quot;, StringType()),<br>    ])<br><br>    parsed = json_str.select(from_json(col(&quot;json_str&quot;), schema).alias(&quot;data&quot;))<br><br>    events = (<br>        parsed.select(<br>            col(&quot;data.user_id&quot;).alias(&quot;user_id&quot;),<br>            col(&quot;data.`product-id`&quot;).alias(&quot;product_id&quot;),<br>            col(&quot;data.event_type&quot;).alias(&quot;event_type&quot;),<br>            # If not ISO8601, pass the exact pattern in the 2nd arg<br>            to_timestamp(col(&quot;data.timestamp&quot;)).alias(&quot;event_time&quot;)<br>        )<br>    )<br>    return events</pre><p>4. The start_streaming_data function then loads the data into our data storage databend.</p><pre>def start_streaming_data():<br>    spark = create_spark_session()<br>    raw_data = read_streaming_data_from_kafka(spark_session=spark)<br>    final_df = create_schema_parsed_dataframe(raw_data=raw_data)<br><br>   <br>    final_df = final_df.select(&quot;user_id&quot;, &quot;product_id&quot;, &quot;event_type&quot;, &quot;event_time&quot;)<br><br>    query = (<br>        final_df.writeStream<br>        .outputMode(&quot;append&quot;)<br>        .foreachBatch(write_batch_to_databend)<br>        .option(&quot;checkpointLocation&quot;, CHECKPOINT_LOCATION)<br>        .start()<br>    )<br><br>    logging.info(&quot;Streaming query started. Writing to Databend (local Docker) ...&quot;)<br>    query.awaitTermination()</pre><h4>Orchasteration</h4><p>For orchestrating our task we will be using airflow.</p><p>Apache Airflow serves as the orchestration tool in the data pipeline. It is responsible for scheduling and managing the workflow of the tasks, ensuring they are executed in a specified order and under defined conditions. In our system, Airflow is used to automate the data flow from streaming with Kafka to processing with Spark.</p><pre><br>KAFKA_TOPIC = Variable.get(&quot;KAFKA_TOPIC&quot;, default_var=&quot;events_topic&quot;)<br>BATCH_SIZE = int(Variable.get(&quot;KAFKA_BATCH_SIZE&quot;, default_var=100))<br><br>tz = pendulum.timezone(&quot;Africa/Addis_Ababa&quot;)<br><br>default_args = {<br>    &quot;owner&quot;: &quot;data-eng&quot;,<br>    &quot;depends_on_past&quot;: False,<br>    &quot;retries&quot;: 2,<br>    &quot;retry_delay&quot;: timedelta(minutes=5),<br>}<br><br>with DAG(<br>    dag_id=&quot;generate_to_kafka_then_run_spark&quot;,<br>    description=&quot;Generate data to Kafka, then launch a Spark job.&quot;,<br>    start_date=pendulum.now(tz).subtract(days=1),<br>    schedule_interval=&quot;0 * * * *&quot;,  # hourly at minute 0<br>    catchup=False,<br>    default_args=default_args,<br>    tags=[&quot;kafka&quot;, &quot;spark&quot;, &quot;batch&quot;],<br>) as dag:<br><br>    generate_and_send = PythonOperator(<br>        task_id=&quot;generate_and_send_to_kafka&quot;,<br>        python_callable=generate_and_stream,<br>        op_kwargs={<br>            &quot;topic&quot;: KAFKA_TOPIC,<br>            &quot;batch_size&quot;: BATCH_SIZE,<br>        },<br>    )<br><br>    spark_job = SparkSubmitOperator(<br>        task_id=&quot;run_spark_job&quot;,<br>        application=SPARK_APP,              <br>        conn_id=&quot;spark_default&quot;,            <br>        application_args=[<br>            f&quot;--topic={KAFKA_TOPIC}&quot;,<br>            &quot;--run-mode=batch&quot;,<br>        ],<br>        packages=None,                     <br>        executor_cores=2,<br>        executor_memory=&quot;2g&quot;,<br>        driver_memory=&quot;1g&quot;,<br>        verbose=False,<br>    )<br><br>    generate_and_send &gt;&gt; spark_job</pre><p>We have successfully built a fully functional, real-time data pipeline. We’ve seen how to generate and stream data with Kafka, process it with Spark, store it in both a data lake (Hadoop) and a data warehouse (Databend), and orchestrate the entire workflow with Airflow. The next step, which will be covered in Part 2, is to connect this pipeline to an analytics tool like Redash to build insightful dashboards that can drive business decisions.</p><p><strong>Project github link</strong>: <a href="https://github.com/melatteshome/Retail-and-e-commerece-data-pipeline">https://github.com/melatteshome/Retail-and-e-commerece-data-pipeline</a></p><h4>Articles to dive in more about the tools used:</h4><ul><li>Databend : <a href="https://docs.databend.com/guides/deploy/">https://docs.databend.com/guides/deploy/</a></li><li>Kafka : <a href="https://medium.com/swlh/apache-kafka-what-is-and-how-it-works-e176ab31fcd5">https://medium.com/swlh/apache-kafka-what-is-and-how-it-works-e176ab31fcd5</a></li><li>Airflow: <a href="https://medium.com/@sumitmudliar/beginners-guide-to-apache-airflow-4827c3f4bfe4">https://medium.com/@sumitmudliar/beginners-guide-to-apache-airflow-4827c3f4bfe4</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7969f3708b0a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Warehousing and ELT pipeline]]></title>
            <link>https://medium.com/@mtadesse813/data-warehousing-and-elt-pipeline-d31be0ad51f2?source=rss-5f3accb9f633------2</link>
            <guid isPermaLink="false">https://medium.com/p/d31be0ad51f2</guid>
            <dc:creator><![CDATA[Melat Teshome]]></dc:creator>
            <pubDate>Mon, 19 May 2025 22:14:57 GMT</pubDate>
            <atom:updated>2025-05-19T22:14:57.849Z</atom:updated>
            <content:encoded><![CDATA[<p>Data warehousing and elt pipeline with dbt, postgres, airflow and redash</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wtJOJhzuC9lSaaI_iOAReA.jpeg" /></figure><h3>Introduction</h3><p>In this post, we’ll explore how to enhance customer experience and refine marketing strategies for an e-commerce platform by leveraging historical data. we will achieve this by processing an already existing dataset from the platform. To achieve this we’ll build a scalable data warehouse tailored for e-commerce analytics. This warehouse will host the transformed data, making it easier to analyze and effecently query behavioral patterns, purchasing trends, and customer journeys. To facilitate this we’ll design and implement an efficient ELT (Extract, Load, Transform) pipeline using <strong>dbt (data build tool), </strong>a modern framework that enables analytics engineering at scale.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*TvFh3xp8ypXul7GpUwtuDw.png" /></figure><h4>What is Data warehousing?</h4><p>It is a process of collecting, storing and managing large volumes of data from diffrent sources in a central repsitory designed for analysis and reporting. it allows bussiness to organize historical and current data in a structured way.</p><h4>What is ELT?</h4><p>ELT (Extract, Load, Transform) is a data integration process used to move and prepare data for analysis. It involves three key steps:</p><ul><li><strong>Extract</strong> - Pulling data from various sources, which can be structured, unstructured, or semi-structured, and in different formats.</li><li><strong>Load</strong> - Storing the extracted data into a destination like a datalake , where it can be easily accessed and analyzed.</li><li><strong>Transform</strong> - Once data is stored on the target destination , we perform data transformation. This involves cleaning and converting the data into a consistent, structured format that fits your analysis needs.</li></ul><h4>Dataset description</h4><p>The dataset comes from an e-commerce app and includes several types of data collected over the years, such as clickstream activity, transaction records, customer information, and product details.</p><p>You can download the dataset <a href="https://www.kaggle.com/datasets/bytadit/transactional-ecommerce">Here </a>.</p><h4>Tech stack used</h4><ul><li><strong>dbt (data build tool)</strong> - dbt is used to transform raw, messy data into clean, structured data that’s ready for analysis. It handles the <strong>“Transform”</strong> step in the ELT process.</li><li><strong>Apache Airflow </strong>- a workflow automation tool. It helps schedule and manage tasks like running dbt models, extracting data, or triggering dashboards making sure everything happens in the right order and time.</li><li><strong>Redash</strong> - a data visualization tool that lets you explore and create dashboards from your data. Once your data is transformed, Redash helps you turn it into useful insights through charts and queries.</li><li><strong>PostgreSQL</strong> - a database where we store our clean, transformed data. In this setup, it acts as our <strong>data warehouse</strong>, serving as the central place where all analysis ready data lives.</li><li><strong>Docker</strong> - Docker is used to <em>containerize</em> our project. This means it packages everything like code, tools and settings into a single unit that can run the same way anywhere. It helps ensure our data pipeline works consistently across different environments, from development to production.</li></ul><h3>Methdology</h3><h4>Dockerization</h4><p>To get our project up and running, we’ll start by <strong>dockerizing all the tools mentioned above</strong>. This ensures a consistent and reliable setup across different environments, making it easier to deploy and manage the entire workflow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*81IVWVQihWJ3xxDjUd4SQg.png" /></figure><h4>Data loading (data ingestion)</h4><p>Once we’ve containerized all the tools we need, the next step is loading our dataset into the database and for that, we rely on Apache Airflow. It helps us automate the entire process using DAGs (Directed Acyclic Graphs), which let us schedule and manage tasks efficiently. We’ve set things up so that the data ingestion happens automatically every night at midnight. And it’s not just the loading part but our DBT transformations are also scheduled and orchestrated with Airflow, making the whole pipeline seamless and hands-free.</p><h4>Data Transformation with DBT</h4><p>We then transform our raw data utilizing DBT (data build tool). Data transforming includes but not limited to data cleaning ( remove or correct invalid, duplicate, or missing values), Type conversion and formatting , aggregation and summarization , joining and integration , derivation anf feature integration , deduplication and such . after we have transformed our data to into our desire its then loaded to our data warehouse, In ourcase its loaded to postgres database.</p><p>It is after we have transformed our data that it is analytic ready.</p><h4>Data visualization with Redash</h4><p>Once the data is successfully loaded and transformed, the next exciting step is <strong>data visualization. </strong>This is where we start uncovering patterns and gaining real insights from the data.</p><p>To do this, we connect <strong>Redash</strong> to our database as the data source. This connection gives us access to both the raw and transformed datasets, making it super flexible depending on what we need to explore.</p><p>What’s great about Redash is how it bridges the gap between complex data queries and simple, intuitive visuals. With its powerful querying capabilities and easy-to-use, interactive widgets, creating meaningful dashboards becomes a smooth and enjoyable process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g6UeqjE2a0Xu9dmWsP4xYQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*01_wZBt33hF4Twq-v3AXMw.png" /></figure><h4>Findings</h4><p>The initial goal of the project is to optimize customer experience and marketing strategies. From our anaysis, using redash dashboard we’ve identified the following points:</p><ul><li><strong>Seasonal Product Performance -</strong> By looking into the top products each season, we now know exactly which items sell best during spring, summer, fall, and winter. This enables the e-commerce team to proactively adjust inventory stocking up on in-demand products ahead of each season.</li><li><strong>Location-Based Recommendations -</strong> Analyzing top products by region shows local preferences, which we can build a recommendations API that works based on the customer’s lcation . Meanwhile, marketing can run targeted campaigns featuring region-specific bestsellers.</li><li><strong>Customer Segmentation Insights -</strong> our customer analysis graph break down buyers into Loyal, Champion,others and At-Risk segments by location. Marketers can use these insights to craft tailored promotions rewarding Champions, nurturing Loyal customers, and re-engaging customes at risk.</li></ul><h4>Conclusion</h4><p>By combining the power of dbt, Apache Airflow, PostgreSQL, and Redash(all containerized with Docker ) . we’ve built a scalable and efficient data warehousing and ELT pipeline tailored for e-commerce analytics. This setup not only automates the flow of raw data into meaningful insights but also equips businesses with the tools to understand customer behavior, optimize marketing strategies, and make smarter data-driven decisions. Whether you’re just starting with analytics engineering or looking to refine your existing workflows, this stack offers a solid foundation for turning data into impact.</p><p>If you have enjoyed the post please leave a 👏 and a comment.</p><p>Follow me on Github <a href="https://github.com/melatteshome">MelatTeshome</a></p><p>Lets Connect on LinkedIn <a href="http://linkedin.com/in/melat-teshome">MelatTeshome</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d31be0ad51f2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>