<?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 Oyekanmi Olamilekan on Medium]]></title>
        <description><![CDATA[Stories by Oyekanmi Olamilekan on Medium]]></description>
        <link>https://medium.com/@oyekanmiakande?source=rss-ac22395e4b4c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*g8IIwqV1iNefMIT5M_vOgQ.jpeg</url>
            <title>Stories by Oyekanmi Olamilekan on Medium</title>
            <link>https://medium.com/@oyekanmiakande?source=rss-ac22395e4b4c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 14:20:06 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@oyekanmiakande/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Building a Modern Data Pipeline with dbt, Postgresql and Docker.]]></title>
            <link>https://medium.com/@oyekanmiakande/building-a-modern-data-pipeline-with-dbt-postgresql-and-docker-a68fe2d19a3c?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/a68fe2d19a3c</guid>
            <category><![CDATA[streamlit]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[orchestration]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[docker]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Wed, 08 Oct 2025 19:29:55 GMT</pubDate>
            <atom:updated>2025-10-08T19:34:20.945Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LBxcfaYeKViOGisp9HoIYw.png" /></figure><h3>Introduction</h3><p>In this post, I’ll walk you through the architecture, the implementation, and lessons learned from building a fully containerized and orchestrated ETL stack. This will cover how to i implemented a python EL (Extraction and Load) app, configured PostgreSQL as a data warehouse, initiated and run dbt models, use github workflow to orchestrate data ingestion and transformation, and visualize the data with Streamlit.</p><p>By the end of this guide, you’ll be able to clone my GitHub repository and set everything up from scratch. Whether you’re a beginner or a seasoned professional, you’ll be able to spin up and run this project with ease.</p><h4>Project Requirements</h4><p>Before you get started, make sure you have the following tools installed and properly configured on your system:</p><ul><li><strong>Python 3.8+</strong> — For scripting and managing auxiliary data processing tasks.</li><li><strong>Docker</strong> — To containerize and orchestrate the services.</li><li><strong>Docker Compose</strong> — To define and run multi-container Docker applications.</li><li><strong>Git</strong> — To clone the project <a href="https://github.com/intellisenseCodez/superstore-etl-pipeline">repository</a>.</li><li><strong>dbt (Data Build Tool)</strong> <strong>Core</strong>— For data transformation and modeling.</li><li><strong>PostgreSQL</strong> — To serve as the data warehouse (you can use the one provisioned in the Docker setup).</li><li><strong>VS Code / Any IDE</strong> — For exploring and editing the project files.</li></ul><p>Once you have these installed, you’re ready to clone the repo and set everything up locally.</p><h4>Case Study</h4><p>As a <strong>data professional at a retail analytics company</strong>, you’ve been assigned to develop a <strong>data pipeline</strong> that enables the business to make faster and more informed decisions.</p><p>Your mission is to design and implement a <strong>modern ELT (Extract, Load, Transform)</strong> workflow that automates the following:</p><ol><li><strong>Extract</strong> raw sales data from the <strong>KaggleHub API</strong> on a scheduled basis.</li><li><strong>Load</strong> the ingested data into a <strong>PostgreSQL database</strong> serving as the company’s <strong>central data warehouse</strong>.</li><li><strong>Transform</strong> the data using <strong>dbt (Data Build Tool)</strong> to clean, standardize, and model it into analytical layers (Bronze → Gold).</li></ol><p>The transformed data will power a <strong>Streamlit-based business intelligence dashboard</strong>, allowing decision-makers to:</p><ul><li>Track sales performance and profitability across regions and product categories.</li><li>Monitor key performance indicators (KPIs) such as revenue, discounts, and order trends.</li><li>Identify customer segments driving the most value.</li></ul><p>This solution helps the organization move from manual data reporting to an <strong>automated, reproducible, and scalable analytics workflow</strong>, ensuring that business teams always have access to <strong>accurate, fresh, and actionable insights</strong>.</p><h4>Project Core Services</h4><p>The pipeline consists of <strong>four core services</strong>:</p><ul><li><strong>Python Service (Extract &amp; Load):<br>- </strong>Extracts data from a raw source (e.g., CSV or API).<br>- Loads the cleaned data into a <strong>PostgreSQL staging database</strong>.</li><li><strong>PostgreSQL Service (Data Warehouse):<br>- </strong>Acts as the central data repository.<br>- Stores raw, <strong>staging (bronze)</strong>, and <strong>transformed (gold)</strong> data layers.</li><li><strong>dbt Service (Transform):<br>- </strong>Performs all data transformations using dbt models.<br>- Creates structured and analytical-ready tables in the <strong>gold layer</strong>.</li><li><strong>Streamlit (Visualization):<br>- </strong>Connects directly to the <strong>PostgreSQL gold layer</strong> to visualize final analytics.<br>- Provides an <strong>interactive dashboard</strong> for exploring sales insights and performance metrics.</li></ul><p>All services are orchestrated with <strong>Docker Compose</strong>, ensuring smooth dependency management and reproducibility across environments. The entire pipeline is <strong>automatically scheduled to run daily at midnight (12:00 a.m.)</strong> using a <strong>github workflow</strong>.</p><p>This setup delivers a complete, production-ready ELT workflow that integrates <strong>data ingestion, transformation, storage, and visualization</strong> within one consistent Docker environment.</p><h4><strong>Python Service (Extract &amp; Load)</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t45MwLyDuAHgT202gbzF_w.png" /></figure><p>The <strong>Python ETL service</strong> (specifically performing <strong>Extract and Load</strong> operations) is responsible for automating the ingestion of raw data from the <strong>KaggleHub API</strong> into the <strong>PostgreSQL data warehouse</strong>. Implemented as a lightweight, containerized microservice, it ensures seamless data delivery into the structured environment initialized by the PostgreSQL service.</p><p>In the <strong>Extract</strong> phase, the service connects to KaggleHub to fetch the latest version of the <strong>Superstore dataset</strong> in CSV format. This process is automated through the kagglehub library, ensuring consistent retrieval of clean and versioned data from the official source.</p><p>Once extracted, the pipeline proceeds to the <strong>Load</strong> phase, where the dataset is written directly into the <strong>raw.raw_orders</strong> table inside PostgreSQL. Leveraging <strong>SQLAlchemy</strong> and <strong>Pandas’ </strong><strong>to_sql()</strong> method, the data is efficiently loaded without intermediate transformation—preserving its original state for lineage and traceability.</p><p>The service is containerized using <strong>Docker</strong>, directly to the PostgreSQL container within the same network. This design allows smooth communication between both services and guarantees that data loading can begin immediately after PostgreSQL initialization.</p><p><strong>PostgreSQL Service (Data Warehouse):</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZzQgFCiB5029Cxo0w-ghvA.png" /><figcaption>Screenshot of Postgres Database (Data Warehouse)</figcaption></figure><p>At the heart of the pipeline is the <strong>PostgreSQL service</strong>, which acts as the central <strong>data warehouse</strong> for storing and organizing all datasets ingested from external sources. The service was deployed using the official <strong>PostgreSQL Docker image</strong>, ensuring consistency, portability, and ease of setup across environments.</p><p>To automate the initialization process, a custom <strong>init.sql</strong> script was mounted into the PostgreSQL container using <strong>Docker volumes</strong>. This script executes automatically at container startup, creating essential database structures such as:</p><ul><li><strong>Users</strong> with appropriate roles and permissions,</li><li><strong>Schemas</strong> (notably raw, bronze and gold) for separating raw and transformed data, and</li><li><strong>Tables</strong> required for incoming datasets like raw_orders, ensuring they are correctly structured before any data load occurs.</li></ul><p>This initialization process guarantees that every environment — whether local or in production — starts with a consistent and preconfigured PostgreSQL instance, ready to receive data. By defining these components declaratively in SQL and leveraging Docker’s automation, the setup achieves both reproducibility and maintainability.</p><h4><strong>dbt Service (Transform):</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CgVpsDzH_sm9OBH_bi6f8Q.png" /></figure><p>Once the raw data has been ingested into the PostgreSQL data warehouse, the <strong>dbt (Data Build Tool) service</strong> takes over to perform the <strong>transformation</strong> and <strong>modeling</strong> layer of the pipeline. This service plays a crucial role in transforming the unprocessed data from the raw schema into clean, analytics-ready tables stored in higher-level schemas such as bronze, silver, and gold.</p><p>Unlike using the default dbt-core image, this project features a <strong>custom-built dbt Docker image</strong>, designed to include all required dependencies, configurations, and project files. The custom image approach provided greater flexibility — allowing environment-specific adjustments, volume mounting for local development, and a smoother integration with other services defined in the Docker Compose setup.</p><p>When the dbt container starts, it connects directly to the PostgreSQL instance (data warehouse) using credentials and schema information defined in the profiles.yml configuration file. The dbt project directory is mounted into the container as a volume, giving it access to transformation models, macros, and tests.</p><p>Within the dbt project, models are organized to follow a <strong>layered transformation architecture</strong> inspired by the <strong>Medallion Data Model</strong> pattern:</p><ul><li><strong>Raw Layer (</strong><strong>raw)</strong> – Holds unprocessed data as loaded by the Python ETL service.</li><li><strong>Bronze Layer (</strong><strong>bronze)</strong> – Cleans and standardizes raw data, such as correcting types and handling nulls.</li><li><strong>Silver Layer (</strong><strong>silver)</strong> – Applies transformation logic, joins related tables, and derives meaningful intermediate datasets.</li><li><strong>Gold Layer (</strong><strong>gold)</strong> – Produces the final, curated datasets used for analysis and visualization.</li></ul><p>The dbt service compiles these SQL-based models into executable queries and runs them directly against the PostgreSQL database, automatically handling dependencies via the ref() function. This ensures each model builds in the correct order — from raw ingestion to refined analytics tables.</p><p>By containerizing dbt and customizing its image, the transformation process becomes <strong>fully automated, reproducible, and isolated</strong> — ensuring consistent results across environments. Once transformations are completed, the <strong>gold schema</strong> serves as the source for analytics and is later consumed by the <strong>Streamlit visualization service</strong> to deliver actionable insights.</p><h4><strong>Streamlit (Visualization)</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d4Qvj7UqGKrgXbBHcIj8Rg.png" /></figure><p>The final stage of the pipeline is the <strong>Streamlit visualization service</strong>, which transforms the refined data stored in PostgreSQL into <strong>interactive dashboards and insights</strong> for business decision-making.</p><p>This service is built using <strong>Streamlit</strong>, a lightweight Python framework for building data-driven web applications. The Streamlit app connects directly to the <strong>PostgreSQL data warehouse</strong>, specifically querying the <strong>transformed (gold) tables</strong> created by the dbt service. This ensures that all visualizations and metrics are based on clean, structured, and business-ready data.</p><p>In this project, the Streamlit service was containerized using a lightweight Python base image. The application code and dependency files (requirements.txt) are included in the Docker image, ensuring that all necessary Python libraries—such as psycopg2, pandas, and plotly—are preinstalled and ready to run.</p><p>When deployed, the Streamlit container communicates seamlessly with the Postgres container within the same Docker network. The connection parameters (host, database, user, password, and port) are securely managed via environment variables defined in the Docker Compose file, allowing for flexible configuration without hardcoding credentials.</p><p>By containerizing the visualization layer alongside the ETL and dbt services, the entire analytics workflow — from <strong>data extraction</strong> to <strong>insight generation</strong> — is fully reproducible and portable. Any user can spin up the complete system using a single docker-compose up command and instantly access the Streamlit dashboard via their web browser.</p><h4>Bringing It All Together</h4><p>After implementing the core services — the <strong>Python Extract &amp; Load Service</strong>, the <strong>PostgreSQL Data Warehouse</strong>, the <strong>dbt Transformation Service</strong>, and the <strong>Streamlit Visualization App</strong> — the next step was to bring them all together into a unified and automated data workflow.</p><p>This was achieved using <strong>Docker Compose</strong> for multi-container orchestration and <strong>GitHub Actions</strong> for automated workflow execution.</p><h4>Docker Compose: Multi-Service Integration</h4><p>At the foundation of the system is the <strong>Docker Compose file</strong>, which defines all services, their interdependencies, networks, and environment variables. Each service runs in an isolated container but communicates over the same internal Docker network to ensure seamless data flow.</p><p>For example:</p><ul><li>The <strong>Python ETL container</strong> extracts raw data from the source API and loads it into the <strong>PostgreSQL</strong> database.</li><li>The <strong>dbt container</strong> then connects to the same database to perform transformations and materialize clean, analytics-ready models in the <strong>bronze</strong>, <strong>silver</strong>, and <strong>gold</strong> schemas.</li><li>Finally, the <strong>Streamlit container</strong> visualizes the transformed data by connecting to the <strong>gold</strong> schema tables.</li></ul><p>Docker Compose simplifies this by allowing you to start up all these services with a single command:</p><pre>docker-compose up -d</pre><p>This ensures that developers and analysts can replicate the entire environment locally or on any server without worrying about system configuration — maintaining full portability and reproducibility.</p><h4>Conclusion &amp; Key Takeaways</h4><p>This project demonstrates how to build a <strong>modular, containerized ELT pipeline</strong> using modern data engineering tools — from data ingestion to visualization — all orchestrated seamlessly using <strong>Docker Compose</strong> and <strong>GitHub Actions</strong>.</p><p>Together, these components form a <strong>production-ready ELT pipeline</strong> that’s reproducible, scalable, and cloud-agnostic. It’s a perfect blueprint for teams looking to modernize their data stack or for learners eager to get hands-on with <strong>real-world data engineering practices</strong>.</p><h4>🔗 Next Steps</h4><p>If you’d like to explore or run the project yourself, you can find the complete setup and instructions here:<br> 👉 <strong>GitHub Repository:</strong> <a href="https://github.com/intellisenseCodez/superstore-etl-pipeline">intellisenseCodez/superstore-etl-pipeline</a></p><p>Clone it, experiment, and feel free to extend it — whether it’s by adding new data sources, incorporating Airflow for orchestration, or enhancing the dashboard with richer analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a68fe2d19a3c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 4 of 5]]></title>
            <link>https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-4-of-5-2936338fb822?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/2936338fb822</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Sat, 28 Jun 2025 22:32:38 GMT</pubDate>
            <atom:updated>2025-07-01T20:48:41.935Z</atom:updated>
            <content:encoded><![CDATA[<h3>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 4 of 5</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uHMn8_QyRRTqz5HUmUFGDQ.jpeg" /></figure><p>In <a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498">Part 3</a> of our series, we normalized the Superstore dataset into logical tables like DimCategories, DimSubCategories, DimProducts, DimCustomers, DimShipMode, DimLocations, DimOrders, and FactOrderDetails. Now it’s time to <strong>bring that structure to life</strong> with <strong>SQL queries</strong> that drive powerful <strong>visual insights</strong>.</p><p>This part we’ll use <strong>SQL</strong> to answer real-world business questions across sales, profit, operations, customer behavior, and more — all using clean, human-friendly queries.</p><h3>Exploratory Data Analysis (EDA) using SQL</h3><p>Exploratory Data Analysis (EDA) is the process of investigating a dataset to <strong>uncover patterns</strong>, <strong>detect anomalies</strong>, <strong>test hypotheses</strong>, and summarize its main characteristics — often using visualizations and statistical methods.</p><p>Think of it like being a <strong>data detective</strong> — your goal is to understand:</p><ul><li>What is in the data?</li><li>What questions can it help answer?</li><li>What insight and patterns can be found in the data?</li></ul><h4>1. Top 5 customers by total sales</h4><p><strong>Who brings in the most money?</strong><br>This helps identify your highest-value customers for retention or upselling.</p><pre>SELECT <br>    c.customer_name,<br>    ROUND(SUM(od.sales), 2) AS total_sales<br>FROM FactOrderDetails as od<br>JOIN DimOrders o ON od.order_id = o.order_id<br>JOIN DimCustomers c ON o.customer_id = c.customer_id<br>GROUP BY c.customer_name<br>ORDER BY total_sales DESC<br>LIMIT 5;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/336/1*N46E7e6qmY0L2qV5i2j_gQ.png" /></figure><h4>2. Total Profit Per Region</h4><p><strong>Which regions are most profitable?</strong><br> This reveals where your business performs best geographically.</p><pre>SELECT <br>    l.region,<br>    ROUND(SUM(od.Profit), 2) AS total_profit<br>FROM DimOrders  as o<br>JOIN DimLocations l ON o.location_id = l.location_id<br>JOIN FactOrderDetails od ON o.order_id = od.order_id<br>GROUP BY l.region<br>ORDER BY total_profit DESC;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/336/1*OMW9mG_jd6iunpUXI2wy-w.png" /></figure><h4>3. Most Profitable Sub-Category</h4><p><strong>What products drive the most profit?</strong><br>Focuses on performance at the sub-category level to inform product strategy.</p><pre>SELECT <br>    sc.subcategory_name,<br>    ROUND(SUM(od.profit), 2) AS total_profit<br>FROM FactOrderDetails as od<br>JOIN DimProducts as p ON od.product_id = p.product_id<br>JOIN DimSubCategories as sc ON p.subcategory_id = sc.subcategory_id<br>GROUP BY sc.subcategory_name<br>ORDER BY total_profit DESC<br>LIMIT 1;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/336/1*yt24sldz8_2P-3fTlhZz0Q.png" /></figure><h4>4. Year-over-Year Sales Growth</h4><p><strong>How are sales trending across years?</strong><br>This allows leadership to evaluate growth and make annual comparisons.</p><pre>SELECT <br>    EXTRACT(YEAR FROM o.order_date) AS Year,<br>    ROUND(SUM(od.sales), 2) AS total_sales<br>FROM DimOrders as o<br>JOIN FactOrderDetails od ON o.order_id = od.order_id<br>GROUP BY Year<br>ORDER BY Year;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/178/1*au-tz-FmRTVn8alL6_0vjg.png" /></figure><h4>5. Top 3 States with Highest Number of Orders</h4><p><strong>Where do the most orders come from?</strong><br>Helps target sales efforts and optimize regional supply chains.</p><pre>SELECT <br>    l.state,<br>    COUNT(o.order_id) AS order_count<br>FROM DimOrders as o<br>INNER JOIN DimLocations as l on o.location_id = l.location_id<br>GROUP BY l.state<br>ORDER BY order_count DESC<br>LIMIT 3;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/178/1*au-tz-FmRTVn8alL6_0vjg.png" /></figure><h4>6. Average Delivery Time per Ship Mode</h4><p><strong>Which shipping methods are fastest?</strong><br>Supports operational improvements and sets customer expectations.</p><pre>SELECT <br>    sm.ship_mode,<br>    ROUND(AVG(o.ship_date - o.order_date), 2) AS avg_delivery_days<br>FROM DimOrders as o<br>JOIN DimShipMode as sm ON o.ship_mode_id = sm.shipmode_id<br>GROUP BY sm.ship_mode<br>ORDER BY avg_delivery_days desc;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/233/1*g1CmTw37hblEoOLIqSBU6A.png" /></figure><h4>7. Total sales per category</h4><p><strong>How does each product category perform?</strong><br>Useful for product mix optimization and performance review.</p><pre>SELECT <br>    c.category_name,<br>    ROUND(SUM(od.sales), 2) AS total_sales<br>FROM DimCategories as c<br>JOIN DimSubCategories as sc ON c.category_id = sc.category_id<br>JOIN DimProducts as p ON sc.subcategory_id = p.subcategory_id<br>JOIN FactOrderDetails as od ON od.product_id = p.product_id<br>GROUP BY c.category_name;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/233/1*Jkwkli8prMmfQwiwJdoSDA.png" /></figure><h4>8. Monthly trend of sales and profit</h4><p><strong>How do sales and profit evolve monthly?</strong><br>Helpful for trend spotting and forecasting.</p><pre>SELECT <br> MONTHNAME(o.order_date) as order_month,<br>    ROUND(SUM(od.sales), 2) AS total_sales,<br>    ROUND(SUM(od.profit), 2) AS total_profit<br>FROM DimOrders as o<br>JOIN FactOrderDetails as od ON o.order_id = od.order_id<br>GROUP BY order_month, MONTH(o.order_date)<br>ORDER BY MONTH(o.order_date) ASC;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/286/1*r8bchXFpXcPlCKWIq7Wn2g.png" /></figure><h4>9. Customer Segmentation by Frequency and Volume</h4><p><strong>How often and how much do customers buy?</strong><br>A key step toward customer segmentation or loyalty analysis.</p><pre>SELECT <br>    c.customer_name,<br>    COUNT(DISTINCT o.order_id) AS order_count,<br>    ROUND(SUM(od.sales), 2) AS total_sales<br>FROM DimCustomers AS c<br>JOIN DimOrders o ON c.customer_id = o.customer_id<br>JOIN FactOrderDetails od ON o.order_id = od.order_id<br>GROUP BY c.customer_name<br>ORDER BY total_sales DESC;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/286/1*xLa5DrgvdMkZuSTDARhfVg.png" /></figure><h4>10. Total Sales, Profit, Quantity Sold</h4><p><strong>The big picture view.</strong><br>Provides quick summary metrics for dashboards and reporting.</p><pre>SELECT <br>    ROUND(SUM(sales), 2) AS total_sales,<br>    ROUND(SUM(profit), 2) AS total_profit,<br>    SUM(quantity) AS total_quantity<br>FROM FactOrderDetails;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/286/1*U3kDCvJF8W-xB1YH-jaoaw.png" /></figure><h4>11. Sales, Profit &amp; Quantity Trends Over Time (by Quarter)</h4><p><strong>Spot quarterly seasonality and performance.</strong></p><pre>SELECT <br>    QUARTER(o.order_date) AS Quarter,<br>    SUM(od.quantity) AS total_quantity,<br>    ROUND(SUM(od.sales), 2) AS total_sales,<br>    ROUND(SUM(od.profit), 2) AS total_profit<br>FROM DimOrders AS o<br>JOIN FactOrderDetails AS od ON o.order_id = od.order_id<br>GROUP BY Quarter<br>ORDER BY Quarter;</pre><p><strong>Result View:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*qxheSFG5K-DC1BUF282oMw.png" /></figure><p>🔗 <strong>For the complete source code on data profiling using the Superstore dataset, visit this GitHub repository: <br></strong><a href="https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/05_analysis.sql">https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/05_analysis.sql</a></p><h3>Final Thoughts</h3><p>With these queries, you’re equipped to explore customer value, product performance, regional strategy, and time-based trends — all in one elegant dataset.</p><p><strong>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset (5 Part Series)</strong></p><ol><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46">Fundamental Concepts and Teminologies in SQL Database.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef">SQL Data Understanding, Profiling and Cleaning.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498">SQL Data Modelling</a>.</li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-4-of-5-2936338fb822">SQL Data Analysis (EDA)</a>.</li><li>Data Visualization with PowerBI <em>coming soon</em>.</li></ol><h3>Let’s Stay Connected!</h3><p>If you’re enjoying this series and learning something new, don’t forget to <strong>subscribe</strong> and <strong>follow me</strong> here on Medium.<br>You can also <strong>connect with me on </strong><a href="https://www.linkedin.com/in/olamilekan-oyekanmi/"><strong>LinkedIn</strong></a> or drop a comment — I love hearing from fellow learners and data enthusiasts!</p><p>Let’s keep learning and building — together. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2936338fb822" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 3 of 5]]></title>
            <link>https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/99268f4a9498</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Sat, 28 Jun 2025 21:05:19 GMT</pubDate>
            <atom:updated>2025-06-28T22:33:30.685Z</atom:updated>
            <content:encoded><![CDATA[<h3>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 3 of 5</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LbcGWXGa9YWV7X7ZT4NOnw.jpeg" /></figure><p>In the previous parts of this series, we explored and cleaned the <strong>Superstore dataset</strong>. Now, it’s time to normalize this dataset — breaking it into smaller, logical tables to improve efficiency, reduce redundancy, and ensure data integrity.</p><p>If you’ve ever organized a messy closet by separating clothes into categories (shirts, pants, shoes), you’ve already practiced normalization! Similarly, database normalization structures data to make it easier to manage and query.</p><p>Let’s dive into what normalization means, why it’s necessary, and how to apply it using our Superstore data.</p><h3>What Is Normalization?</h3><p><strong>Normalization</strong> is the process of organizing data in a database to reduce redundancy and improve data integrity. It’s like sorting your wardrobe — put all shirts in one drawer, trousers in another, and shoes in a rack. That way, you won’t find sneakers mixed with socks.</p><p>In the data world, instead of storing the customer’s name and address with every order record, we store that customer information <strong>once</strong> in a <strong>Customer table</strong> and just refer to it from the <strong>Orders table</strong>.</p><h3>Why Normalize? (The Problems With Flat Files)</h3><p>The original Superstore dataset comes as a <strong>single flat Excel sheet</strong>, where each row represents an order line, but with lots of <strong>repetition</strong>:</p><ul><li>Customer names and details are repeated every time they place an order.</li><li>Product and category info are repeated for each sale.</li><li>Ship mode and region appear in every row, even when they’re the same.</li></ul><p>This leads to:</p><ul><li><strong>Data Redundancy</strong>: The same information (e.g., “Consumer” segment) appears multiple times, wasting storage.</li><li><strong>Update Anomalies</strong>: If a customer changes their address, you must update it in every related order — risking mistakes.</li><li><strong>Insertion/Deletion Issues</strong>: Deleting an order might accidentally remove the only record of a product.</li></ul><p>Normalization solves these problems by splitting data into related tables connected by keys.</p><h3>Benefits of Normalizing the Superstore Dataset</h3><ul><li><strong>Faster Queries</strong>: Less repeated data means quicker access.</li><li><strong>Smarter Joins</strong>: Easily join Customers to Orders or Products to Categories.</li><li><strong>Better Analytics</strong>: Group, filter, and pivot with clarity.</li><li><strong>Data Integrity</strong>: Update a customer once — everywhere stays consistent.</li></ul><h3>How We Normalized the Superstore Dataset</h3><p>We’ll split the flat table into these normalized tables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*XwvaHQtjLBP84z89TnSDZg.png" /><figcaption>Entity Relationship Diagraw (Draw.io)</figcaption></figure><ul><li><strong>DimCategories</strong>: Broad product categories — category_id <strong>(PK)</strong>, category_name.</li><li><strong>DimSubCategories</strong>: More specific groupings of products — subcategory_id <strong>(PK)</strong>, subcategory_name, category_id (FK).</li><li><strong>DimProducts</strong>: Unique products sold — product_id <strong>(PK)</strong>, product_name (PK), subcategory_id (FK).</li><li><strong>DimCustomers: </strong>Unique details for each customer — customer_id <strong>(PK)</strong>, customer_name, segment.</li><li><strong>DimShipModes</strong>: Methods of delivery — shipmode_id <strong>(PK)</strong>, ship_mode.</li><li><strong>DimLocations</strong>: Geographic grouping of markets — location_id (PK), postal_code, city, state, region, country.</li><li><strong>DimOrders: </strong>Details for each order — order_id <strong>(PK)</strong>, customer_id (FK), order_date, ship_date, ship_mode_id (FK), location_id (FK).</li><li><strong>FactOrderDetails</strong>: what was sold, when, and how much — fact_order_details_id <strong>(PK)</strong>, order_id (FK), product_id (FK), quantity, sales, discount, profit.</li></ul><h3>Key Techniques Used in Normalization</h3><h4>1. Eliminate Repeating Groups</h4><p>If the same name, product, or value appears over and over — chances are, it belongs in a separate table. In our Superstore data:</p><ul><li>Repeated <em>Customer Names</em> → goes to <strong>Customers</strong> table</li><li>Repeated <em>Product Names</em> → goes to <strong>Products</strong> table</li><li>Repeated <em>Ship Modes</em> → goes to <strong>Ship Modes</strong> table.</li></ul><h4>2. Assign Primary Keys</h4><p>Each table should have a <strong>Primary Key (PK)</strong> — a unique identifier.</p><ul><li>Customers → Customer ID</li><li>Products → Product ID</li><li>Orders → Order ID</li></ul><p>These keys become <strong>Foreign Keys (FK)</strong> in other tables. For example, Customer ID appears in the Orders table to show who placed the order.</p><h4>3. Avoid Duplication (1NF — First Normal Form)</h4><ul><li>Each table contains only atomic values (no lists or duplicates).</li><li>Example: Instead of storing multiple products in one order row, we use Order Details as a separate table.</li></ul><h4>4. Remove Partial Dependencies (2NF — Second Normal Form)</h4><ul><li>Every non-key column must depend on the entire primary key (not just part of it).</li><li>Example: In Order Details, Product_Name doesn’t depend on Order_ID alone—so we move it to a Products table.</li></ul><h4>5. Remove Transitive Dependencies (3NF — Third Normal Form)</h4><ul><li>Non-key columns should not depend on other non-key columns.</li><li>Example: In the original dataset, Sub_Category depends on Category—so we split them into Categories and Sub-Categories tables.</li></ul><h3>Best Practices for Normalization</h3><ul><li>Use Primary &amp; Foreign Keys to link tables (e.g., Customer_ID in Orders references Customers).</li><li>Avoid Storing Calculated Fields (e.g., Profit should be computed, not hardcoded).</li><li>Denormalize Selectively for Performance (sometimes, minor redundancy helps speed up queries).</li></ul><h3>Implemention of schemas (with CREATE TABLE statements).</h3><p>Here’s how 1translate our normalized design into SQL CREATE TABLE statements:</p><h4>DimCategories Table</h4><pre>CREATE TABLE IF NOT EXISTS DimCategories (<br>    category_id INT PRIMARY KEY AUTO_INCREMENT,<br>    category_name VARCHAR(100) NOT NULL<br>);</pre><h4>DimSubCategories Table</h4><pre>CREATE TABLE IF NOT EXISTS DimSubCategories (<br>    subcategory_id INT PRIMARY KEY AUTO_INCREMENT,<br>    subcategory_name VARCHAR(100) NOT NULL,<br>    category_id INT,<br>    FOREIGN KEY (category_id) REFERENCES DimCategories(category_id)<br>);</pre><h4>DimProducts Table</h4><pre>CREATE TABLE IF NOT EXISTS DimProducts (<br>    product_id VARCHAR(20),<br>    product_name VARCHAR(255),<br>    subcategory_id INT NOT NULL,<br>    PRIMARY KEY (product_id, product_name),  -- Composite primary key<br>    FOREIGN KEY (subcategory_id) REFERENCES DimSubCategories(subcategory_id)<br>);</pre><h4>DimCustomers Table</h4><pre>CREATE TABLE IF NOT EXISTS DimCustomers (<br>    customer_id VARCHAR(10) PRIMARY KEY,<br>    customer_name VARCHAR(100) NOT NULL,<br>    segment VARCHAR(50)<br>);</pre><h4>DimShipModes Table</h4><pre>CREATE TABLE IF NOT EXISTS DimShipMode (<br>    shipmode_id INT PRIMARY KEY AUTO_INCREMENT,<br>    ship_mode VARCHAR(50) NOT NULL<br>);</pre><h4>DimLocations Table</h4><pre>CREATE TABLE IF NOT EXISTS DimLocations (<br> location_id INT PRIMARY KEY AUTO_INCREMENT,<br>    postal_code INT,<br>    city VARCHAR(100),<br>    state VARCHAR(100),<br>    region VARCHAR(100),<br>    country VARCHAR(100)<br>);</pre><h4>DimOrders Table</h4><pre>CREATE TABLE IF NOT EXISTS DimOrders (<br>    order_id VARCHAR(15) PRIMARY KEY,<br>    customer_id VARCHAR(10),<br>    order_date DATE,<br>    ship_date DATE,<br>    ship_mode_id INT,<br>    location_id INT,<br>    FOREIGN KEY (customer_id) REFERENCES DimCustomers(customer_id),<br>    FOREIGN KEY (ship_mode_id) REFERENCES DimShipMode(shipmode_id),<br>    FOREIGN KEY (location_id) REFERENCES DimLocations(location_id)<br>);</pre><h4>FactOrderDetails Table</h4><pre>CREATE TABLE IF NOT EXISTS FactOrderDetails (<br> fact_order_details_id INT PRIMARY KEY AUTO_INCREMENT,<br>    order_id VARCHAR(15),<br>    product_id VARCHAR(20),<br>    quantity INT,<br>    sales FLOAT,<br>    discount FLOAT,<br>    profit FLOAT,<br>    FOREIGN KEY (order_id) REFERENCES DimOrders(order_id),<br>    FOREIGN KEY (product_id) REFERENCES DimProducts(product_id)<br>);</pre><p>🔗 <strong>For the complete source code on implementing the data modelling schemas, visit this GitHub repository: <br></strong><a href="https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/03_create_schema.sql">https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/03_create_schema.sql</a></p><h3>Inserting data into schemas (with INSERT INTO TABLE statements).</h3><p>Here’s how i populate our normalized tables with data usingINSERT INTO TABLE statements:</p><h4>Populate DimCategories</h4><p>This SQL statement is inserting new categories into a DimCategories table while ensuring no duplicates are added.</p><pre>INSERT INTO DimCategories (category_name)<br>SELECT DISTINCT Category<br>FROM <br>    clean_superstore<br>WHERE <br>     Category NOT IN (<br>        SELECT category_name <br>        FROM DimCategories<br>    );</pre><ol><li>It inserts new records into the DimCategories table (a dimension table in a data warehouse)</li><li>It selects only distinct Category values from the clean_superstore table</li><li>The WHERE clause ensures it only inserts categories that don&#39;t already exist in DimCategories. This process is called <strong>SQL Idempotency — </strong>refers to performing an operation multiple times while producing the same result as performing it once. It avoid having duplicate data or change the system state beyond the initial application.</li></ol><h4>Populate DimSubCategories</h4><p>This query inserts new subcategories into a DimSubCategories dimension table while ensuring no duplicates are created.</p><pre>INSERT INTO DimSubCategories(subcategory_name, category_id)<br>SELECT <br>    cs.sub_category,<br>    c.category_id<br>FROM <br>    (SELECT DISTINCT sub_category, Category FROM clean_superstore) AS cs -- distinct sub-category from raw data<br>INNER JOIN<br>    DimCategories AS c ON cs.Category = c.category_name<br>WHERE <br>    cs.sub_category NOT IN (<br>        SELECT subcategory_name <br>        FROM DimSubCategories<br>    ); </pre><ul><li>Gets all unique subcategory-category pairs from the raw clean_superstore table.</li><li>Matches each raw category name with its corresponding category_id from the DimCategories table.</li><li>Only inserts subcategories that don’t already exist in the target table.</li></ul><p>🔗 <strong>For the complete source code on populating data into theschemas, visit this GitHub repository: <br></strong><a href="https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/04_insert_data.sql">https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/04_insert_data.sql</a></p><h3>Final Thoughts</h3><p>Think of normalization as organizing your kitchen. If your ingredients, utensils, and spices are scattered everywhere, cooking becomes stressful. But with labeled drawers and shelves, you work faster, smarter, and cleaner.</p><p>By normalizing the Superstore dataset, we’re not just “cleaning up” — we’re building a <strong>strong foundation</strong> for advanced analytics, dashboards, and even machine learning.</p><p><strong>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset (5 Part Series)</strong></p><ol><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46">Fundamental Concepts and Teminologies in SQL Database.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef">SQL Data Understanding, Profiling and Cleaning.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498">SQL Data Modelling</a>.</li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-4-of-5-2936338fb822">SQL Data Analysis (EDA)</a>.</li><li>Data Visualization with PowerBI <em>coming soon</em>.</li></ol><h3>Let’s Stay Connected!</h3><p>If you’re enjoying this series and learning something new, don’t forget to <strong>subscribe</strong> and <strong>follow me</strong> here on Medium.<br>You can also <strong>connect with me on </strong><a href="https://www.linkedin.com/in/olamilekan-oyekanmi/"><strong>LinkedIn</strong></a> or drop a comment — I love hearing from fellow learners and data enthusiasts!</p><p>Let’s keep learning and building — together. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=99268f4a9498" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 2 of 5]]></title>
            <link>https://blog.devgenius.io/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/bde9e4aebcef</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Fri, 27 Jun 2025 00:33:33 GMT</pubDate>
            <atom:updated>2025-07-01T16:03:59.022Z</atom:updated>
            <content:encoded><![CDATA[<h3>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 2 of 5</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C3vziFZpQ-Ru69NBGFyiQA.jpeg" /></figure><p>Welcome back to the second part of <em>“From Spreadsheets to SQL”</em>! In <a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46">Part 1</a>, we covered the fundamentals of SQL and databases — what they are, why they matter, and how they differ from spreadsheets.</p><p>Now, it’s time to roll up our sleeves and get hands-on! In this post, we’ll:</p><ol><li>Install MySQL and MySQL Workbench</li><li>Download and load the Superstore dataset from Kaggle</li><li>Perform basic data profiling using SQL</li><li>Discuss why denormalized data is problematic (setting the stage for normalization in Part 3)</li></ol><p>Let’s dive in!</p><h3>Installing MySQL and MySQL Workbench</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o3Ra63dwRLadml3y62D34g.png" /></figure><p>Before we start querying, we need a database system to work with. MySQL is one of the most popular relational database management systems (RDBMS), and MySQL Workbench provides a friendly interface to interact with it.</p><h4>Step-by-Step Installation</h4><h4>For Windows:</h4><ol><li>Download MySQL Installer from <a href="https://dev.mysql.com/downloads/installer/">https://dev.mysql.com/downloads/installer/</a></li><li>Run the installer</li><li>Choose the <strong>Full Setup</strong> if you have a good internet connection.</li><li>During installation, ensure the following components are selected:</li></ol><ul><li>MySQL Server</li><li>MySQL Workbench (the GUI tool)</li><li>Connector/ODBC (optional, useful for Excel/Power BI)</li></ul><p>Follow the prompts, set a root password (remember this!), and complete the installation. Then launch <strong>MySQL Workbench</strong> and connect to your server using the root password.</p><h4>For Mac (using Homebrew):</h4><pre>brew install mysql<br>brew install --cask mysqlworkbench</pre><p>Then start MySQL:</p><pre>mysql.server start</pre><h4>For Linux (Ubuntu/Debian):</h4><pre>sudo apt update<br>sudo apt install mysql-server mysql-workbench</pre><p>Secure your installation:</p><pre>sudo mysql_secure_installation</pre><p>Once installed, open MySQL Workbench and connect to your local server using the root password you set.</p><h3>Downloading and Loading the Superstore Dataset</h3><p>We’ll be using the <strong>Global Superstore dataset</strong> available on Kaggle. It’s a common dataset for analytics practice, mimicking a real-world retail company.</p><h4>Steps:</h4><ol><li>Download the dataset from <a href="https://www.kaggle.com/datasets/ishanshrivastava28/superstore-sales">Kaggle</a> (you may need a free Kaggle account).</li><li>Unzip the file into a folder. This contain two files “Superstore.csv” and “Superstore.xlsx”. We only need the Superstore.csv since MySQL only allows the import of a CSV or JSON files.</li><li>Open MySQL Workbench and create a new database:</li></ol><pre>CREATE DATABASE IF NOT EXISTS superstore;<br><br>USE superstore;</pre><p>4. Import the dataset:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*exOBLbGsK1d-J5JbNb2dfA.jpeg" /></figure><ul><li>Right-click in the left sidebar and select “Refresh All” to see the new database.</li><li>Right-click on “Tables” under the superstore schema and choose &quot;Table Data Import Wizard&quot;.</li><li>Select the downloaded Superstore CSV file and click Next. While loading data into MySQL Workbench i observe that i couldn’t load the CSV file so i have to convert to JSON format using an online tool.</li><li>Choose “Create new table” and name it (e.g., RawSuperstore).</li><li>Follow the prompts to map columns and complete the import.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D3cRZcoK6bmR0_bMtieaKA.jpeg" /></figure><p>All fields in the table have been initially loaded as text data types to prevent any potential data loss due to inconsistencies. We’ll now proceed with profiling the dataset to identify any inconsistent or invalid values. This step ensures we can clean the data appropriately before applying the correct SQL data types.</p><p>Now, your data is loaded and ready for exploration!</p><h3>Dataset Metadata</h3><p>Row ID =&gt; Unique ID for each row.<br>Order ID =&gt; Unique Order ID for each Customer.<br>Order Date =&gt; Order Date of the product.<br>Ship Date =&gt; Shipping Date of the Product.<br>Ship Mode=&gt; Shipping Mode specified by the Customer.<br>Customer ID =&gt; Unique ID to identify each Customer.<br>Customer Name =&gt; Name of the Customer.<br>Segment =&gt; The segment where the Customer belongs.<br>Country =&gt; Country of residence of the Customer.<br>City =&gt; City of residence of of the Customer.<br>State =&gt; State of residence of the Customer.<br>Postal Code =&gt; Postal Code of every Customer.<br>Region =&gt; Region where the Customer belong.<br>Product ID =&gt; Unique ID of the Product.<br>Category =&gt; Category of the product ordered.<br>Sub-Category =&gt; Sub-Category of the product ordered.<br>Product Name =&gt; Name of the Product<br>Sales =&gt; Sales of the Product.<br>Quantity =&gt; Quantity of the Product.<br>Discount =&gt; Discount provided.<br>Profit =&gt; Profit/Loss incurred.</p><h3>Data Profiling: Understanding the Dataset</h3><p><strong>Data Profiling</strong> is the process of examining the data to understand its structure, content, and quality. It helps you discover issues <strong>before</strong> cleaning or using the data.</p><h4>Data Profiling Answers Questions Like:</h4><ul><li>What columns are in the dataset?</li><li>What are the data types and value distributions?</li><li>Are there missing values or duplicates?</li><li>How many unique values exist in each column?</li><li>Are there anomalies or outliers?</li></ul><p>🔗 <strong>For the complete source code on data profiling using the Superstore dataset, visit this GitHub repository: </strong><a href="https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/01_raw_data_profiling.sql">https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/01_raw_data_profiling.sql</a></p><h4>Findings from Data Profiling and How to Fix Them</h4><ol><li>I verified that the table includes all expected columns, matching the CSV header.<br><strong>Action</strong>: No immediate fix required, but data types still need validation (e.g., dates, numbers, text).</li><li>I confirmed there are <strong>9,994 rows</strong> — this aligns with the original dataset.</li><li>No missing values in any column.</li><li>No duplicate records found.</li><li>All shipping dates occur after order dates as expected.</li><li>The Sales column contains <strong>text values</strong> like &quot;Wirebound&quot;, not just numbers.<br><strong>Action</strong>: Clean the column by removing non-numeric values and Convert column to FLOAT once cleaned.</li><li>Similar to Sales, the Quantity column contains <strong>text values</strong> like &quot;Wirebound Standard Line Memo&quot;.<br><strong>Action</strong>: Clean the column by removing non-numeric values and Convert column to INT once cleaned.</li><li>Discount field contains string values like &quot;Sand&quot;.<br><strong>Action</strong>: Clean the column by removing non-numeric values and Convert column to FLOAT once cleaned.</li><li>Some rows in the profit field show <strong>negative profit</strong>, indicating a <strong>loss</strong> — this is <strong>not an error</strong>, but a useful insight.</li></ol><p>You can see that without a proper data profiling, we did be <strong>blindly trusting</strong> our dataset — which could lead to faulty analyses and bad decisions.</p><h3>Data Cleaning: Making the Raw Superstore Data Usable</h3><p>After profiling the rawsuperstore table, we discovered that some numeric fields like <strong>Sales</strong>, <strong>Quantity</strong>, and <strong>Discount</strong> contain invalid text values such as &quot;Wirebound&quot; or &quot;Sand&quot;. These non-numeric entries make it impossible to perform proper analysis, aggregation, or type conversion. So, it’s time to <strong>clean our data</strong> by removing or correcting these inconsistencies.</p><p>Here’s how we cleaned and standardized the raw dataset using SQL:</p><pre>CREATE TABLE IF NOT EXISTS clean_superstore AS<br>SELECT * FROM rawsuperstore;</pre><p>This statement creates a new table called clean_superstore by copying all data and structure from an existing table rawsuperstore.</p><h4>Remove Rows with Invalid Sales, Quantity, and Discount Values</h4><pre>DELETE FROM clean_superstore<br>WHERE Sales NOT REGEXP &#39;^[0-9]+(\\.[0-9]+)?$&#39;;<br><br>DELETE FROM clean_superstore<br>WHERE Quantity NOT REGEXP &#39;^[0-9]+(\\.[0-9]+)?$&#39;;<br><br>DELETE FROM clean_superstore<br>WHERE Discount NOT REGEXP &#39;^[0-9]+(\\.[0-9]+)?$&#39;;</pre><h4>Cast Columns to Appropriate Data Types</h4><pre>ALTER TABLE clean_superstore<br>MODIFY COLUMN Segment VARCHAR(255),<br>MODIFY COLUMN Country VARCHAR(255),<br>MODIFY COLUMN City VARCHAR(255),<br>MODIFY COLUMN State VARCHAR(255),<br>MODIFY COLUMN Region VARCHAR(255),<br>MODIFY COLUMN Category VARCHAR(255),<br>MODIFY COLUMN Sales FLOAT,<br>MODIFY COLUMN Quantity INT,<br>MODIFY COLUMN Discount FLOAT,<br>MODIFY COLUMN Profit FLOAT;</pre><p>After cleaning the dataset, we retained <strong>9,947 records</strong> out of the original <strong>9,994 records</strong> imported — meaning a few rows were removed due to invalid or inconsistent values.</p><p>🔗 <strong>For the complete source code on data cleaning using the Superstore dataset, visit this GitHub repository:</strong><br> <a href="https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/02_data_cleaning.sql">https://github.com/intellisenseCodez/superstore-analysis-using-mysql/blob/master/sql/02_data_cleaning.sql</a></p><p>This cleaning process helps us transition from <strong>raw mess</strong> to <strong>analysis-ready</strong>.</p><p>Nevertheless the Superstore dataset is denormalized — all data is stored in one table with repeated information (e.g., customer names, product categories).</p><h3>Why This is Problematic:</h3><ul><li><strong>Data redundancy</strong>: The same customer name appears hundreds of times, wasting space.</li><li><strong>Update anomalies</strong>: Changing a customer’s address requires updating every related row.</li><li><strong>Inconsistent deletions</strong>: Removing an order might accidentally erase product details.</li></ul><p>In Part 3, we’ll normalize this dataset — splitting it into logical tables (Customers, Products, Orders) to solve these issues!</p><p><strong>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset (5 Part Series)</strong></p><ol><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46">Fundamental Concepts and Teminologies in SQL Database.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef">SQL Data Understanding, Profiling and Cleaning.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498">SQL Data Modelling</a>.</li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-4-of-5-2936338fb822">SQL Data Analysis (EDA)</a>.</li><li>Data Visualization with PowerBI <em>coming soon</em>.</li></ol><h3>Let’s Stay Connected!</h3><p>If you’re enjoying this series and learning something new, don’t forget to <strong>subscribe</strong> and <strong>follow me</strong> here on Medium.<br> You can also <strong>connect with me on </strong><a href="https://www.linkedin.com/in/olamilekan-oyekanmi/"><strong>LinkedIn</strong></a> or drop a comment — I love hearing from fellow learners and data enthusiasts!</p><p>Let’s keep learning and building — together. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bde9e4aebcef" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef">From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 2 of 5</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 1 of 5]]></title>
            <link>https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd9a9cf76b46</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Fri, 27 Jun 2025 00:12:03 GMT</pubDate>
            <atom:updated>2025-06-28T22:34:30.016Z</atom:updated>
            <content:encoded><![CDATA[<h3>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset — Part 1 of 5</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zFI24kQTiUEEG20gTRNw9A.jpeg" /></figure><p>SQL isn’t just useful — it’s <em>unavoidable</em>. With 48.66% of professional developers relying on it (<a href="https://survey.stackoverflow.co/2023/#technology-most-popular-technologies">Stack Overflow 2023 Survey</a>) and #5 in-demand skill for data jobs (<a href="https://www.linkedin.com/pulse/why-sql-still-matters-2025-trends-career-insights-excelgoodies-sowxc/">LinkedIn’s Most In-Demand Skills</a>), SQL powers everything from real-time analytics to AI/ML pipelines. As data volumes explode, tools like Snowflake, BigQuery, and PostgreSQL have made SQL faster and more scalable — but the fundamentals remain timeless.</p><p>This article walk you through from understanding core concepts of databases to translating the Superstore dataset into a relational database, use <strong>SQL to explore it</strong>, and explain <strong>key database concepts</strong> like data query, data transformation, data modeling, normalization, SQL idempotency, and more — all with beginner-friendly explanations and real-life context. Whether you’re a data analyst, scientist, or engineer, mastering SQL is your gateway to unlocking data’s full potential.</p><p>The <a href="https://www.kaggle.com/datasets/ishanshrivastava28/superstore-sales">Superstore dataset</a> is a staple for learning data analysis. But while many jump straight into dashboards and machine learning, they often skip a crucial step: <strong>understanding data through SQL</strong>.</p><p>In this post, I’ll walk you through the core concepts of databases and terminologies in SQL</p><h3>Understanding Databases</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L3Rc8WLYlsp-dPlJ7l_OQQ.png" /></figure><p>Imagine you run a small neighborhood grocery store. You have notebooks where you track each orders — the customer’s name, the product info, the price, and the date. At first, one notebook is enough. But as your business grows and more orders comes in, flipping through pages becomes slow, you start writing the same customer names repeatedly — in the field of SQL this is know as <strong>Data Redundacy</strong>, and it gets harder to answer questions like: “Who are my top customers?” or “Which product sold the most this month?”. This form of storage also lack data consistency, maintaince and integrity</p><p>This is where <strong>databases</strong> come in.</p><p>A <strong>database</strong> is like a super-organized digital version of your notebook — structured in a way that makes storing, updating, and retrieving data fast and easy. Instead of one messy notebook, you now have separate <strong>tables</strong>: one for customers, one for products, one for orders. These tables are linked together, so you don’t have to repeat information. For instance, a customer’s name is stored once in the “Customers” table, and every order they make simply refers to their customer ID. This is known as <strong>Database Normalization</strong> — a design process that organizes tables and relationships to reduce redundancy and improve data integrity. This structure allows you to quickly find patterns, generate reports, and make data-driven decisions — all with a few lines of SQL code.</p><p>In essence, a <strong>database</strong> helps you manage information the way a well-organized business would — logically, efficiently, and without confusion.</p><h3>Types of Databases</h3><p>Different databases serve different purposes. Here are the most common types:</p><h4>A. Relational Databases (SQL)</h4><ul><li>Store data in tables with rows and columns.</li><li>Use SQL (Structured Query Language) for queries.</li><li>Best for structured data with clear relationships.</li><li>Examples: MySQL, PostgreSQL, Oracle, SQL Server.</li></ul><h4>B. NoSQL Databases</h4><ul><li>Handle unstructured or semi-structured data (e.g., JSON, documents).</li><li>Flexible schema (no fixed table structure).</li><li>Types: <strong>Document-based</strong> (MongoDB, Firebase), <strong>Key-Value</strong> (Redis, DynamoDB), <strong>Graph-based</strong> (Neo4j), <strong>Column-family</strong> (Cassandra)</li></ul><p><strong>C. In-Memory Databases</strong></p><ul><li>Store data in RAM instead of disk for ultra-fast access.</li><li>Used in caching and real-time analytics.</li><li>Example: Redis.</li></ul><p><strong>D. Time-Series Databases</strong></p><ul><li>Optimized for time-stamped data (e.g., logs, IoT sensors).</li><li>Example: InfluxDB.</li></ul><h3>Key Properties of Databases (ACID vs. CAP)</h3><p>When you store data in a database, you want it to be reliable, fast, and consistent. But depending on whether you’re using a <strong>traditional relational database</strong> (like MySQL or PostgreSQL) or a <strong>modern distributed database</strong> (like MongoDB or Cassandra), different rules apply.</p><p>Let’s break them down:</p><h3>A. ACID Properties — for Relational Databases</h3><p>Relational databases store data in <strong>tables with rows and columns</strong>. They are used in systems where <strong>accuracy, integrity, and rules </strong>are very important — like <strong>banks, hospitals, or e-commerce</strong>.</p><p>ACID stands for:</p><p><strong>1. Atomicity: All or Nothing<br></strong>A transaction must fully happen or not happen at all.</p><p><strong>Example:</strong> You transfer ₦10,000 from <strong>Account A</strong> to <strong>Account B</strong>. The system subtracts ₦10,000 from <strong>Account A</strong> <strong>and</strong> adds ₦10,000 to <strong>Account B</strong>. If one part fails (e.g., adding to Account B), the entire transaction is <strong>cancelled</strong>. No half-complete transactions allowed.</p><h4>2. Consistency: Follow the Rules</h4><p>The database must always remain in a valid state.</p><p><strong>Example:</strong> If a bank account can’t go below ₦0, the system will <strong>reject</strong> any transaction that breaks this rule — even if the math adds up.</p><h4>3. Isolation: Transactions Don’t Clash</h4><p>Multiple users doing transactions at the same time shouldn’t mess each other up.</p><p><strong>Example:</strong> If two customers try to buy the <strong>last copy</strong> of a laptop online at the same time, only <strong>one</strong> transaction should succeed.<br>Isolation prevents the system from accidentally selling <strong>more than it has</strong>.</p><h4>4. Durability: It Stays Saved</h4><p>Once a transaction is complete, it’s <strong>saved permanently</strong>, even if the system crashes.</p><p><strong>Example:</strong> If you pay for airtime and get a success message, the system ensures your payment is <strong>never lost</strong>, even if power goes out right after.</p><h3>B. CAP Theorem (For Distributed Databases)</h3><p>In large systems like <strong>Facebook, WhatsApp, or Netflix</strong>, data is stored <strong>across many servers and locations</strong>. These systems must deal with <strong>network issues, outages</strong>, and <strong>millions of users</strong>.</p><p>The <strong>CAP theorem</strong> says a distributed system can <strong>guarantee only two out of three</strong> at any given time:</p><h4>1. Consistency</h4><p>Every user sees <strong>the same data</strong> at the same time.</p><p><strong>Example:</strong> You post a photo on Instagram. If your friend sees it instantly on their feed, that’s <strong>consistency</strong>.</p><h4>2. Availability</h4><p>The system <strong>always responds</strong> — even if some data is slightly outdated.</p><p><strong>Example:</strong> You try to like a post on Twitter, and it works <strong>even if part of the network is down</strong>. That’s <strong>availability</strong>.</p><h4>3. Partition Tolerance</h4><p>The system keeps working even when parts of the network <strong>fail or get disconnected</strong>.</p><p><strong>Example:</strong> During a storm, some servers go offline, but TikTok still loads for you. That’s <strong>partition tolerance</strong>.</p><h3>Important Concepts and Terms you need to know.</h3><h4>1. Database</h4><p>A structured collection of data that allows efficient storage, retrieval, and management.</p><h4>2. Table</h4><p>A set of rows and columns to store data (like a spreadsheet).<br> <strong>Example:</strong> A Customers table with columns like id, name, email.</p><h4>3. Row (Record)</h4><p>A single entry in a table representing one item or entity.<br><strong>Example:</strong> One customer’s data.</p><h4>4. Column (Field)</h4><p>A vertical section in a table representing a data attribute.<br><strong>Example:</strong> email column holds all customer emails.</p><h4>5. Primary Key</h4><p>A unique identifier (field) for each record in a table. No duplicates or null values allowed.</p><h4>6. Foreign Key</h4><p>A field in one table that refers to the <strong>primary key</strong> in another — creates relationships. This field establish a relationship between two tables.</p><h4>7. SQL (Structured Query Language)</h4><p>The language used to interact with databases (e.g., querying, inserting, updating data).<br><strong>Example:</strong> SELECT * FROM Customers;</p><h4>8. Query</h4><p>A request for data or information from a database.<br><strong>Example:</strong> A SQL command to find all orders placed in June.</p><h4>9. Schema</h4><p>A blueprint or structure of a database — defines tables, fields, data types, and relationships.</p><h4>10. DBMS (Database Management System)</h4><p>Software used to manage databases, allowing users to create, read, update, and delete data.<br><strong>Examples:</strong> MySQL, PostgreSQL, Oracle, MongoDB, SQLite</p><h4>11. RDBMS (Relational Database Management System)</h4><p>A type of DBMS based on the relational model where data is stored in <strong>tables</strong>.<br><strong>Examples:</strong> MySQL Workbench, pgAdmin, SQL Server etc.</p><h4>12. Data Model</h4><p>Defines how data is logically organized.<br><strong>Types:<br>- Relational Model</strong> (tables)<br>- <strong>Document Model</strong> (JSON-like, e.g., MongoDB)<br>- <strong>Key-Value Store<br>- Graph Model</strong> (nodes and edges)</p><h4>13. Normalization</h4><p>The process of organizing data to:<br>- Reduce redundancy<br>- Improve data integrity<br>- <strong>Normal Forms (1NF, 2NF, 3NF, BCNF)</strong></p><h4>14. Denormalization</h4><p>Opposite of normalization — intentionally adding redundancy to improve <strong>read performance</strong>.</p><h4>15. Entity-Relationship (ER) Model</h4><p>A diagrammatic approach to database design — shows <strong>entities</strong>, <strong>attributes</strong>, and <strong>relationships</strong>.</p><h4>16. Index</h4><p>A data structure used to quickly locate records without scanning the entire table. Speeds up queries.</p><h4>17. Transaction</h4><p>A sequence of operations performed as a single logical unit.<br><strong>Example:</strong> Transferring money between accounts.</p><h4>18. Replication</h4><p>Copying data across multiple servers to ensure <strong>availability</strong> and <strong>fault tolerance</strong>.</p><h4>19. Sharding</h4><p>Horizontal partitioning — distributing a table’s rows across different databases or servers for <strong>scalability</strong>.</p><h4>20. Data Integrity</h4><p>Ensuring accuracy and consistency of data through constraints and validation.</p><h4>21. OLTP vs OLAP</h4><ul><li><strong>OLTP</strong> (Online Transaction Processing): Real-time transactions (e.g., ATM withdrawals)</li><li><strong>OLAP</strong> (Online Analytical Processing): Analytical queries on large datasets (e.g., dashboards)</li></ul><h4>22. NoSQL</h4><p>“Not Only SQL” databases designed for unstructured or semi-structured data.<br><strong>Types:</strong> Document, Key-Value, Columnar, Graph<br><strong>Examples:</strong> MongoDB, Cassandra, Redis, Neo4j</p><h4>23. Data Warehouse</h4><p>A central repository for analytical reporting and data analysis. Optimized for reads, not writes.</p><h4>24. Query Optimization</h4><p>Techniques used by DBMS to execute SQL queries in the most efficient way possible.</p><h4>25. Metadata</h4><p>Data about data. For example, column names, data types, and index info.</p><h4>26. ETL (Extract, Transform, Load)</h4><p>The process of:<br>- <strong>Extracting</strong> data from source systems<br>- <strong>Transforming</strong> it (cleaning, reformatting)<br>- <strong>Loading</strong> it into a target database or warehouse</p><h4>27. Data Cleaning</h4><p><strong>Data Cleaning</strong> (also called data cleansing or scrubbing) is the process of detecting and correcting (or removing) inaccurate, incomplete, inconsistent, or irrelevant data from a dataset.</p><h4>28. Data Profiling?</h4><p><strong>Data Profiling</strong> is the process of examining the data to understand its structure, content, and quality. It helps you discover issues <strong>before</strong> cleaning or using the data.</p><p><strong>From Spreadsheets to SQL: Mastering SQL Concepts Using the Superstore Dataset (5 Part Series)</strong></p><ol><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-1-of-5-fd9a9cf76b46">Fundamental Concepts and Teminologies in SQL Database.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-2-of-5-bde9e4aebcef">SQL Data Understanding, Profiling and Cleaning.</a></li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-3-of-5-99268f4a9498">SQL Data Modelling</a>.</li><li><a href="https://medium.com/@oyekanmiakande/from-spreadsheets-to-sql-mastering-sql-concepts-using-the-superstore-dataset-part-4-of-5-2936338fb822">SQL Data Analysis (EDA)</a>.</li><li>Data Visualization with PowerBI <em>coming soon</em>.</li></ol><h3>Let’s Stay Connected!</h3><p>If you’re enjoying this series and learning something new, don’t forget to <strong>subscribe</strong> and <strong>follow me</strong> here on Medium.</p><p>You can also <strong>connect with me on </strong><a href="https://www.linkedin.com/in/olamilekan-oyekanmi/"><strong>LinkedIn</strong></a> or drop a comment — I love hearing from fellow learners and data enthusiasts!</p><p>Let’s keep learning and building — together. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd9a9cf76b46" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Step-by-Step Guide: Launch a Linux System Using Vagrant on Mac or Windows (Ubuntu 22.04.3 LTS)]]></title>
            <link>https://medium.com/@oyekanmiakande/step-by-step-guide-launch-a-linux-system-using-vagrant-on-mac-or-windows-ubuntu-22-04-3-lts-855e45444d69?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/855e45444d69</guid>
            <category><![CDATA[virtualization]]></category>
            <category><![CDATA[softeare-engineering]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Tue, 10 Jun 2025 16:47:48 GMT</pubDate>
            <atom:updated>2025-06-17T14:22:42.683Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*48ymJuwHgPtlDz9aKGgdkw.png" /></figure><p>Ever wanted to test scripts or configurations on a clean Linux box — without dual booting or spinning up a cloud instance (AWS or Azure)? Vagrant makes that possible, and easier than you think.</p><p>Picture this: You’re trying to test a Linux-specific deployment script or project, but you’re on a Windows machine. Your team uses Ubuntu, and you’re stuck manually tweaking Docker or dual-booting. I’ve experienced that pain firsthand. In the world of DevOps, the consistency of your environments can mean the difference between a seamless deployment and a panicked 3 AM Slack alert.</p><p>That’s where <strong>Vagrant</strong> comes in. With just a few commands, you can launch a full Linux environment on any machine — Mac or Windows — without the clutter or overhead of full-blown virtualisation setups. Let me show you how I use it daily, and how it has saved me countless hours of frustration.</p><h3><strong>What is Vagrant and Why Use It?</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/920/1*OFexWLGjYd6nwzSsUraRwg.png" /></figure><p>Vagrant, by <a href="https://www.hashicorp.com/en">HashiCorp</a>, is an open-source tool that lets you manage virtual machines through simple configuration files. It works with providers like VirtualBox, VMware, or even the cloud.</p><p><strong>Why DevOps folks love Vagrant:</strong></p><ul><li>Create identical dev/test environments</li><li>Run provisioning scripts automatically</li><li>Share environments with teammates</li><li>Test automation without risking your host OS</li></ul><h4><strong>Prerequisites:</strong></h4><p>Before we get started, install:</p><ul><li><a href="https://www.virtualbox.org/">VirtualBox</a></li><li><a href="https://developer.hashicorp.com/vagrant">Vagrant</a></li><li><strong>Mac users:</strong> Use Terminal.</li><li><strong>Windows users:</strong> Use Git Bash or PowerShell.</li></ul><h4><strong>Quick install (macOS + Homebrew):</strong></h4><pre>brew install --cask virtualbox<br>brew install vagrant</pre><p><strong>Windows Installation Steps:</strong></p><ol><li>Download and install <a href="https://www.virtualbox.org/wiki/Downloads">VirtualBox for Windows</a>.</li><li>Download and install <a href="https://developer.hashicorp.com/vagrant/install#windows">Vagrant for Windows</a>.</li><li>Install Git for Windows to use <a href="https://git-scm.com/downloads/win">Git Bash</a>.</li><li>After installation, launch <strong>Git Bash</strong> or <strong>PowerShell</strong>, and run:</li></ol><pre>vagrant --version</pre><p><strong>Getting Your First Linux VM Running</strong></p><ul><li><strong>Create a project folder:</strong></li></ul><pre>mkdir devops-lab <br>cd devops-lab</pre><ul><li><strong>Initialize with a base box:</strong></li></ul><pre>vagrant init ubuntu/bionic64</pre><ul><li><strong>(Optional) Customize the Vagrantfile:</strong></li></ul><pre>Vagrant.configure(&quot;2&quot;) do |config|<br><br>    config.vm.box = &quot;generic/ubuntu2204&quot; # Ubuntu 22.04.3 LTS<br><br>    config.vm.hostname = &quot;ubuntu&quot;<br>    <br>    config.vm.provider &quot;virtualbox&quot; do |vb|<br>      vb.memory = &quot;2048&quot;<br>      vb.cpus = 2<br>    end<br><br>end</pre><ul><li><strong>Start your VM:</strong></li></ul><pre>vagrant up</pre><ul><li><strong>Access it via SSH:</strong></li></ul><pre>vagrant ssh</pre><p>Now you’re in a full-blown Linux system. On a Mac or Windows box. Neat, right?</p><p><strong>DevOps Use Cases</strong></p><ul><li>CI pipeline testing (Jenkins agents)</li><li>Local Ansible or Puppet development</li><li>Simulating production environments before real deployment</li><li>Running Docker inside a VM for layered isolation</li></ul><p><strong>Helpful Commands Reference</strong></p><ul><li>vagrant init : initialise Vagrant with a Vagrantfile.</li><li>vagrant status : outputs status of the vagrant machine.</li><li>vagrant up : start the virtual machine.</li><li>vagrant resume : resume a suspended machine.</li><li>vagrant ssh : Connect to the virtual machine.</li><li>vagrant halt : Shutdown the virtual machine.</li><li>vagrant destroy : Delete the virtual machine.</li><li>vagrant reload : Reboot virtual machine with updated config.</li><li>vagrant suspend : suspends a virtual machine.</li></ul><p><strong>Conclusion</strong></p><p>If you’re serious about DevOps, you need quick, clean environments that mirror production. Vagrant gives you that power without requiring you to be a virtualisation expert.</p><p>Whether you’re writing shell scripts, testing infrastructure as code, or training junior engineers, Vagrant helps you stay consistent and productive.</p><p><strong>Next steps:</strong> Try creating multi-VM networks, use Ansible for provisioning, or share your Vagrantfile with your team.</p><p>Thanks for reading — and as always, happy automating!</p><p><em>Got questions or want to see a multi-tier Vagrant setup in action? Drop a comment or ping me on </em><a href="https://www.linkedin.com/in/olamilekan-oyekanmi/"><em>LinkedIn</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=855e45444d69" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[“Speed Up Your Analytics”: Dockerized Olist Ecommerce Dataset]]></title>
            <link>https://medium.com/@oyekanmiakande/speed-up-your-analytics-dockerized-olist-ecommerce-dataset-23b9d073165d?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/23b9d073165d</guid>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Sun, 02 Jun 2024 17:44:02 GMT</pubDate>
            <atom:updated>2024-06-02T17:44:02.849Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3hdaKLjUKkz8H01hn39V8g.png" /></figure><ul><li>As a data engineer have you ever thought of the possibility of dockerizing a database with existing data?</li></ul><p>Thsi is what inspired this project. As a data analyst and scientist who has work on several datasets to drive an insights i know the importance of transforming the dataset before anything else.</p><p>Data transformation is the process of converting and modifying data from its original format to a format that is more suitable for analysis, processing, or storage. It involves changing the data structure, format, or content to make it more consistent, organized, and easily accessible.</p><p>The goal of this step during any data analytic project is to:</p><ol><li>Improve data quality and consistency.</li><li>Enhance data usability and accessibility.</li><li>Support data analysis and reporting.</li><li>Enable data integration and interoperability.</li><li>Reduce data redundancy and errors.</li></ol><h3>Objective</h3><p>During transformation of the olist dataset, i discover a lot of inconsistency and irregulaties. This gave me sleepless night but at the end i was able to fix it 🕺. But i thought if someone else need to work on the same dataset, they will have to go through the same nightmare i went through.</p><p>That is not good enough… i think.</p><p>🤔 So i said to myself if it’s possible to dockeriz a web application, why not a database. So i took up the challenge to dockerize the transformed form of the olist dataset into a postgres database. Just so you don’t have to do the transformation anymore.</p><p>In this post i am going to be introducing you to the lastest docker image on the planet. It is the docker image of the popular Olist ecommerce dataset found on <a href="https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce"><strong>kaggle</strong></a> and the Olist Market Funnel dataset, also on <a href="https://www.kaggle.com/datasets/olistbr/marketing-funnel-olist"><strong>kaggle</strong></a>.. I will also guide you on how to run and connect to the database.</p><p>Let’s jump into this 😊</p><h4>Prerequisites</h4><ol><li>Docker Destop</li><li>PostgreSQL</li></ol><h4>1. Pull the docker image</h4><p>A Docker image is a snapshot or blueprint of the libraries and dependencies required inside a container for an application to run. This image contains: our cleaned olist dataset, SQL queries to create schemas, tables, instructions on how to copy the cleaned data into the postgres database etc.</p><p>Image repository: <a href="https://hub.docker.com/r/horlar/olist-cleaned-data"><strong>horlar/olist-cleaned-data</strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LXSQ6vszz8FeR2EmCfVmug.png" /></figure><p>Inorder to be able to pull the olist docker image from the docker hub repository, you need to install <a href="https://docs.docker.com/engine/install/"><strong>docker desktop</strong></a> on your machine. Once that is done kindly start the docker destop and ensure it’s in a running mode.</p><p>Now you can put up your command prompt or terminal to pull the image.</p><pre>docker pull horlar/olist-cleaned-data</pre><p>you can confirm the images available on your machine using:</p><pre>docker images</pre><h4>2. Run a docker container</h4><p>Now, you have pull the image to your machine, the next this is to run a container of this image.</p><p>A Docker container is a runtime environment with all the necessary components — like code, dependencies, and libraries — needed to run the application code without using host machine dependencies.</p><pre>docker run -d --name &lt;your-container-name&gt; -p 5434:5432 -v &lt;your-volume-name&gt;:/var/lib/postgresql/data olist-cleaned-data:v0.01</pre><ul><li><strong>your-container-name</strong>: replace this what whatever you prefer to name the container rather than allowing docker to generate a random name.</li><li><strong>your-volume-name</strong>: replace this also with your prefer volume name. Volumes are the preferred mechanism for persisting data generated by and used by Docker containers.</li></ul><p>Also, you can confirm if the container is up and running on your machine using:</p><pre>docker container ps</pre><h4>3. Connect to the container</h4><p>Its time to connect to the our olist postgres database through the container. to be able to do this you need the credentials of the database. This can be found also on the <a href="https://hub.docker.com/r/horlar/olist-cleaned-data"><strong>repository</strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/767/1*7w_51Bjai-Pl4Q8-eHZexQ.png" /></figure><p>While the container is still runing in a detached more. Enter this command on your terminal:</p><p>At this point you can connect with a database connect either MySQL, PostgreSQL or others, but i am using postgreSQL.</p><p>For installation and setting up postgresql, check out my post.</p><pre>psql -U olist_user -d olist_ecommerce</pre><p>Now you are logged into the database. write a simple query to test this.</p><pre>-- query the first 10 records of the customers table<br>SELECT *<br>FROM ecommerce.customers<br>LIMIT 10;</pre><p>Thats it ☺️.</p><p>If you prefer using a database management system such as DBeaver, feel free to do that, all you need is the credentials provide up there.</p><p>📌 You can check out my <a href="https://www.youtube.com/channel/@data_coach_ola"><strong>youtube channel</strong></a> on how to run and connect to this docker container.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FJwDwG51TXr8%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DJwDwG51TXr8&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FJwDwG51TXr8%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/20bb8f336c7c6a13db613f0c92432178/href">https://medium.com/media/20bb8f336c7c6a13db613f0c92432178/href</a></iframe><h3>📜Conclusion</h3><p>Data science team can utilized the Dockerized Olist dataset to analyze customer purchase behavior and optimize product recommendations. By leveraging the pre-transformed and easily accessible dataset, The Dockerized dataset enabled seamless collaboration and iteration among team members, and the containerized environment ensured consistency and reproducibility across different stages of the project. This project demonstrated the value of the Dockerized Olist dataset in accelerating data-driven insights and business impact, and has since become a standard tool in our ecommerce analytics workflow.</p><p><strong>Related Posts</strong></p><ul><li><a href="https://medium.com/dev-genius/understanding-postgresql-database-administration-43e37c7dda72">Understanding PostgreSQL Database Administration.</a></li><li><a href="https://blog.devgenius.io/portfolio-project-approach-to-learning-sql-part-1-ae7544ba7818">Portfolio Project Approach to Learning SQL (Part 1).</a></li><li><a href="https://medium.com/dev-genius/portfolio-project-approach-to-learning-sql-part-2-ca552643d88c">Portfolio Project Approach to Learning SQL (Part 2).</a></li></ul><p><strong>Thank you for Reading!</strong></p><h3>Liked the blog? Connect with Me</h3><ul><li><strong>LinkedIN</strong>: <a href="https://www.linkedin.com/in/olamilekan-oyekanmi/">https://www.linkedin.com/in/olamilekan-oyekanmi/</a></li><li><strong>Youtube: </strong><a href="https://www.youtube.com/@data_coach_ola">https://www.youtube.com/@data_coach_ola</a></li><li><strong>Tiktok: </strong><a href="https://www.tiktok.com/@data_coach_ola">https://www.tiktok.com/@data_coach_ola</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=23b9d073165d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Portfolio Project Approach to Learning SQL (Part 2).]]></title>
            <link>https://blog.devgenius.io/portfolio-project-approach-to-learning-sql-part-2-ca552643d88c?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca552643d88c</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[portfolio]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Sun, 05 Nov 2023 14:54:54 GMT</pubDate>
            <atom:updated>2023-11-06T18:50:01.575Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XUnOXFRpL7MT3fTriS3Vjw.jpeg" /><figcaption>mage by <a href="https://www.freepik.com/free-vector/illustration-social-media-concept_2807765.htm#query=data%20engineering&amp;position=2&amp;from_view=search&amp;track=ais">rawpixel.com</a> on Freepik</figcaption></figure><blockquote>Thank you for joining me on this exciting journey through the world of data engineering and SQL. In <a href="https://medium.com/@oyekanmiakande/portfolio-project-approach-to-learning-sql-part-1-ae7544ba7818">Part 1</a>, we laid the foundation by setting up our PostgreSQL database, defining roles and schemas, and loading the crypto dataset. Now, in Part 2, our goal is to dive headfirst into exploring the dataset using a wide array of SQL commands. We’ll unlock the power of SQL to uncover valuable insights, patterns, and trends within the cryptocurrency data.</blockquote><h4><strong>UNDERSTANDING THE DATASET</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Emb_CJh89aaywiQICh8zKA.png" /><figcaption><a href="https://drawsql.app/teams/my-space-2/diagrams/crypto-db">Image exported from drawSQL</a></figcaption></figure><p>Before we embark on our SQL exploration, let’s take a moment to revisit the crypto dataset we’re working with. Remember that it comprises three key tables: Member, Price, and Transactions, each offering unique insights into the world of cryptocurrency. The data dictionary we discussed earlier will be your trusty guide as we navigate through the tables and fields.</p><h4>OBJECTIVES</h4><p>In this part of the post, we’ll:</p><ol><li><strong>Learn SQL Query Basics:</strong> We’ll start with the fundamental SQL SELECT statement to retrieve data from the tables. We’ll explore how to filter, sort, and limit data to extract the most relevant information.</li><li><strong>Data Exploration:</strong> We’ll dive into the Member, Price, and Transactions tables and execute SQL queries to understand user demographics, track cryptocurrency prices, and analyze transaction data.</li><li><strong>Data Aggregation:</strong> We’ll explore the power of SQL aggregation functions like SUM, COUNT, and AVG to gain insights into the dataset at a higher level, such as total transaction amounts or average prices.</li><li><strong>Joins and Relationships:</strong> We’ll tackle the relationships between the tables, including how to perform JOIN operations to combine data from multiple tables.</li></ol><h4>RESOURCES</h4><p>For datasets and codes, visit my <a href="https://github.com/intellisenseCodez/Data-Engineering-with-ALTSchool/tree/master/Assessment/crypto%20analytics">github</a> account.</p><p>Let’s get started … 😀</p><h4><strong>Learn SQL Query Basics</strong></h4><p>In this section, we will dive into the fundamentals of SQL query execution. We’ll explore basic SQL commands using the crypto dataset and provide SQL examples to illustrate each concept. Let’s get started:</p><p>1. <strong>The SELECT Statement:</strong></p><ul><li>The most basic SQL command is the SELECT statement, used to retrieve data from one or more tables.</li><li>Example 1: Retrieve all data from the Member table.</li></ul><pre><br>-- Retrieve all data from the Member table.<br>SELECT * FROM raw.members;</pre><ul><li>Example 2: Retrieve specific columns from the Price table.</li></ul><pre>-- Retrieve specific columns from the Price table.<br>SELECT ticker, price FROM raw.prices;</pre><p>2. <strong>Filtering Data with WHERE</strong>:</p><ul><li>To narrow down the results, you can use the <strong>WHERE</strong> clause to specify conditions that rows must meet.</li><li>Example: Retrieve transactions from the Transactions table with quantity equal to 50.</li></ul><pre>-- Retrieve transactions from the Transactions table with quantity equal to 50.<br>SELECT * FROM raw.transactions t  WHERE quantity  = 50;</pre><p>3. <strong>Sorting Data with ORDER BY</strong>:</p><ul><li>You can order the results by one or more columns using the ORDER BY clause.</li><li>Example: Retrieve member data from the Member table sorted by first_name in alphabetical order.</li></ul><pre>-- Retrieve member data from the Member table sorted by first_name in alphabetical order.<br>SELECT  *<br>FROM raw.members<br>ORDER BY first_name ASC;</pre><p>4. <strong>Limiting Results with LIMIT:</strong></p><ul><li>The LIMIT clause allows you to restrict the number of rows returned.</li><li>Example: Retrieve the top 10 prices from the Price table.</li></ul><pre>-- Retrieve the top 10 prices from the Price table.<br>SELECT * <br>FROM raw.prices <br>ORDER BY price DESC  <br>LIMIT 10;</pre><p>5. <strong>Aggregate Functions:</strong></p><ul><li>SQL provides various aggregate functions like SUM, COUNT, AVG, MIN, and MAX to summarize data.</li><li>Example: Calculate the total quantity of all transactions.</li></ul><pre>-- Calculate the total quantity of all transactions.<br>SELECT SUM(quantity) FROM raw.transactions;</pre><p>6. <strong>Grouping Data with GROUP BY</strong>:</p><ul><li>GROUP BY is used in combination with aggregate functions to group data by one or more columns.</li><li>Example: Calculate the total transaction quantity for each member..</li></ul><pre>-- Calculate the total of quantity for each member.<br>SELECT member_id, sum(quantity) <br>FROM raw.transactions<br>GROUP BY member_id;</pre><p>7.<strong> Aliasing Columns:</strong></p><ul><li>You can assign aliases to columns or tables to make your query results more readable.</li><li>Example: Alias the total transaction quantity in a grouped result.</li></ul><pre>-- Alias the total transaction quantity in a grouped result.<br>SELECT member_id, SUM(quantity) AS total_transaction_amount <br>FROM raw.transactions<br>GROUP BY member_id;</pre><p>These basic SQL commands are your starting point for exploring the crypto dataset. With these concepts, you can retrieve, filter, sort, and aggregate data, providing a solid foundation for more advanced SQL techniques. Stay tuned for the following sections, where we’ll explore more complex SQL commands and data relationships, taking you one step closer to becoming a proficient data engineer.</p><h4><strong>Data Exploration</strong></h4><p>In this section, we will continue our exploration of the crypto dataset, focusing on more advanced SQL commands to delve deeper into the data. Here are some SQL examples to help you explore the data effectively:</p><p>1.<strong> Distinct Values:</strong></p><ul><li>You can use the DISTINCT keyword to retrieve unique values from a specific column.</li><li>Example: Find the unique ticker symbols in the Price table.</li></ul><pre>-- Find the unique ticker symbols in the Price table.<br>SELECT DISTINCT ticker FROM raw.prices;</pre><p>2. <strong>Filtering with Multiple Conditions:</strong></p><ul><li>You can use logical operators (AND, OR) to filter data based on multiple conditions.</li><li>Example: Retrieve transactions with quantity greater than 20 in ETH or BTC.</li></ul><pre>-- Retrieve transactions with quantity greater than 20 in ETH or BTC.<br>SELECT * <br>FROM raw.transactions t  <br>WHERE (quantity &gt; 20 AND ticker = &#39;ETH&#39;) OR (ticker = &#39;BTC&#39;);</pre><p>3. <strong>Date and Time Manipulation:</strong></p><ul><li>SQL allows you to perform operations on date and time columns.</li><li>Example: Find the total quantity and average quantity for each year.</li></ul><pre>-- Find the total quantity and average quantity for each year.<br>SELECT <br>  EXTRACT(YEAR FROM txn_date::date) AS txn_year, <br>  sum(quantity) AS &quot;total_quantity&quot;, <br>  avg(quantity) AS &quot;average_quantity&quot;<br>FROM raw.transactions<br>WHERE  ticker = &#39;BTC&#39;<br>GROUP BY txn_type, txn_year;</pre><p>4. <strong>Aggregating and Sorting:</strong></p><ul><li>You can combine aggregation functions and sorting for more in-depth analysis.</li><li>Example: Who are the top 3 members with the most bitcoin quantity?</li></ul><pre>-- Who are the top 3 members with the most bitcoin quantity? <br>SELECT m.first_name , sum(t.quantity) AS total_quantity<br>FROM raw.members m <br>INNER JOIN raw.transactions t ON m.member_id = t.member_id<br>WHERE ticker = &#39;BTC&#39;<br>GROUP BY m.first_name<br>ORDER BY total_quantity DESC<br>LIMIT 3;</pre><p>These SQL commands provide a robust toolkit for data exploration, allowing you to uncover insights and patterns within the crypto dataset. As you experiment with these examples and get comfortable with SQL, you’ll be better equipped to tackle more complex queries and data analysis tasks in your journey toward becoming a proficient data engineer. Stay tuned for the next sections, where we’ll delve into data aggregation, joins, and data visualization techniques to further enhance your SQL skills.</p><h4><strong>Data Aggregation</strong></h4><p>In this section, we’ll focus on data aggregation using SQL commands to summarize and gain insights from the crypto dataset. We’ll explore various aggregation functions and provide SQL examples to illustrate their use:</p><p>1. <strong>SUM and COUNT:</strong></p><ul><li>The SUM() function calculates the total of a numeric column.</li><li>The COUNT() function counts the number of rows in a table or meeting a specified condition.</li><li>Example: Calculate the total transaction amount for each member.</li></ul><pre>-- Calculate the total transaction amount for each member<br>SELECT m.member_id, count(t.txn_id) AS transaction_count, SUM(p.price * t.quantity) AS total_transaction_amount<br>FROM raw.members m <br>INNER JOIN raw.transactions t ON m.member_id = t.member_id <br>INNER JOIN raw.prices p ON t.ticker = p.ticker <br>GROUP BY m.member_id;</pre><p>2. <strong>AVG:</strong></p><ul><li>The AVG() function computes the average value of a numeric column.</li><li>Example: What was the monthly average quantity purchased and sold for Ethereum in 2020?</li></ul><pre>-- What was the monthly average quantity purchased and sold for Ethereum in 2020? Your query <br>-- should return exactly three columns - calendar_month, buy_quantity, sell_quantity.<br>SELECT <br>  EXTRACT(MONTH FROM txn_date::date) AS calendar_month,<br>  avg(CASE WHEN txn_type = &#39;BUY&#39; AND ticker = &#39;ETH&#39; THEN quantity ELSE 0 END) AS buy_quantity,<br>     avg(CASE WHEN txn_type = &#39;SELL&#39; AND ticker = &#39;ETH&#39; THEN quantity ELSE 0 END) AS sell_quantity<br>FROM raw.transactions t <br>WHERE ticker = &#39;ETH&#39; AND EXTRACT(YEAR FROM txn_date::date) = 2020<br>GROUP BY calendar_month;</pre><p>3. <strong>MIN and MAX:</strong></p><ul><li>The MIN() and MAX() functions find the minimum and maximum values in a column, respectively.</li><li>Example: Find the minimum and maximum prices for each cryptocurrency.</li></ul><pre>-- Find the minimum and maximum prices for each cryptocurrency.<br>SELECT ticker, MIN(price) AS min_price, MAX(price) AS max_price<br>FROM raw.prices p <br>GROUP BY ticker;</pre><p>These SQL aggregation functions provide you with the means to summarize data and extract meaningful insights from the crypto dataset. Whether you’re interested in total amounts, counts, averages, or more complex aggregations, these commands enable you to gain a deeper understanding of the dataset and uncover trends and patterns within the cryptocurrency data. In the upcoming sections, we’ll explore joins and relationships to further enrich your SQL skills and data engineering journey.</p><h4><strong>Joins and Relationships</strong></h4><p>In this section, we’ll explore SQL commands for combining data from multiple tables through JOIN operations. Establishing relationships between tables is crucial for data engineering, and we’ll provide SQL examples to illustrate these concepts:</p><p>1. <strong>INNER JOIN:</strong></p><ul><li>INNER JOIN combines rows from two or more tables based on a related column.</li><li>Example: Retrieve transactions with member details, joining the Transactions and Member tables on the member_id.</li></ul><pre>-- Retrieve transactions with member details, joining the Transactions and Member tables on the member_id.<br>SELECT m.first_name, m.region, t.*<br>FROM raw.transactions t<br>INNER JOIN raw.members m ON t.member_id = m.member_id;</pre><p>2. <strong>LEFT JOIN (or LEFT OUTER JOIN):</strong></p><ul><li>LEFT JOIN retrieves all rows from the left table and the matched rows from the right table. If there’s no match, NULL values are filled in.</li><li>Example: Find members and their transactions, including members with no transactions.</li></ul><pre>-- Find members and their transactions quantity, including members with no transactions.<br>SELECT m.first_name, t.quantity<br>FROM raw.members m<br>LEFT JOIN raw.transactions t ON m.member_id = t.member_id;</pre><p>3. <strong>RIGHT JOIN (or RIGHT OUTER JOIN):</strong></p><ul><li>RIGHT JOIN is similar to LEFT JOIN but retrieves all rows from the right table and the matched rows from the left table.</li><li>Example: Retrieve transactions and their associated member details, including transactions with no members.</li></ul><pre>-- Retrieve transactions and their associated member details, including transactions with no members.<br>SELECT m.first_name, m.region, t.*<br>FROM raw.transactions t<br>RIGHT JOIN raw.members m ON t.member_id = m.member_id;</pre><p>4. <strong>FULL JOIN (or FULL OUTER JOIN):</strong></p><ul><li>FULL JOIN retrieves all rows when there is a match in either the left or right table.</li><li>Example: Combine transactions and members, including unmatched records from both tables.</li></ul><pre>-- Combine transactions and members, including unmatched records from both tables.<br>SELECT m.first_name, m.region, t.*<br>FROM raw.transactions t<br>FULL JOIN raw.members m ON t.member_id = m.member_id;</pre><p>5. <strong>Self-Join:</strong></p><ul><li>A self-join is used when a table needs to be joined with itself, often to establish hierarchical relationships.</li><li>Example: Find pairs of members who live in the same region.</li></ul><pre>-- Find pairs of members who live in the same region.<br>SELECT m1.first_name AS member1, m2.first_name AS member2, m1.region <br>FROM raw.members m1<br>JOIN raw.members m2 ON m1.region = m2.region AND m1.member_id &lt; m2.member_id;</pre><p>6. <strong>CROSS JOIN:</strong></p><ul><li>CROSS JOIN returns the Cartesian product of two tables, resulting in all possible combinations.</li><li>Example: Create a matrix of all members and currencies to explore potential transactions.</li></ul><pre>-- Create a matrix of all members and currencies to explore potential transactions.<br>SELECT m.first_name , p.ticker <br>FROM raw.members m<br>CROSS JOIN raw.prices p;</pre><p>By mastering these JOIN operations, you’ll be able to combine and extract valuable insights from related data across multiple tables. These SQL examples demonstrate how to create meaningful relationships and enrich your data engineering skills, enabling you to conduct more complex analyses and draw meaningful conclusions from the crypto dataset. In the following section, we’ll briefly touch on data visualization techniques to further enhance your data exploration journey.</p><h4>CONCLUSION</h4><p>As we come to the end of this post, it’s essential to reflect on the journey you’ve embarked upon as a data engineer. Throughout this exploration of SQL and data manipulation, i’ve acquired fundamental skills that are invaluable in the world of data engineering and analysis. You’ve set up a PostgreSQL database, learned to craft SQL queries, explored data, and understood the importance of data visualization.</p><p>My ability to aggregate, filter, and join data from various tables allows you to extract insights and make data-driven decisions. With these skills, i am well-prepared to take on the challenges and opportunities in the dynamic field of data engineering.</p><p>Remember, the journey doesn’t end here. The world of data is constantly evolving, and as a data engineer, you’ll continue to adapt and grow. You can explore more advanced SQL techniques, delve into big data technologies, and harness the power of machine learning to analyze data at scale.</p><p>Your journey as a data engineer is marked by continuous learning, problem-solving, and a passion for turning data into knowledge. As you move forward, stay curious, seek out new challenges, and apply your skills to real-world projects. The world of data engineering is filled with opportunities, and you’re now equipped to leverage them in your professional endeavors.</p><p>Thank you for joining me on this journey, and remember that every line of code and every SQL query you write brings you closer to becoming a proficient data engineer. Continue to explore, innovate, and shape the future through data, and you’ll find that your skills are in high demand in our data-driven world. Your journey as a data engineer has only just begun, and the future is full of exciting possibilities.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca552643d88c" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/portfolio-project-approach-to-learning-sql-part-2-ca552643d88c">Portfolio Project Approach to Learning SQL (Part 2).</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Portfolio Project Approach to Learning SQL (Part 1).]]></title>
            <link>https://blog.devgenius.io/portfolio-project-approach-to-learning-sql-part-1-ae7544ba7818?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/ae7544ba7818</guid>
            <category><![CDATA[entity-relationship-model]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Sun, 05 Nov 2023 13:02:07 GMT</pubDate>
            <atom:updated>2023-11-06T18:50:10.778Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XUnOXFRpL7MT3fTriS3Vjw.jpeg" /><figcaption>Image by <a href="https://www.freepik.com/free-vector/illustration-social-media-concept_2807765.htm#query=data%20engineering&amp;position=2&amp;from_view=search&amp;track=ais">rawpixel.com</a> on Freepik</figcaption></figure><h4>MOTIVATION</h4><p>As I embark on my journey to transition into a career as a data engineer, I find myself two months deep into the world of SQL, particularly focusing on PostgreSQL. This blog post is born out of my passion for mastering SQL and the desire to share my progress with others. With every line of code I write and every query I execute, I’ve realized the transformative power of SQL in the realm of data engineering. This post serves as a reflection of my dedication to continuous learning and my commitment to building a strong foundation in SQL. Through a portfolio project-based approach, I hope to not only solidify my own understanding but also inspire and assist others who are embarking on similar journeys in the exciting world of data engineering.</p><p>Before diving into the intricacies of SQL and PostgreSQL in this post, I highly recommend checking out my <a href="https://medium.com/dev-genius/understanding-postgresql-database-administration-43e37c7dda72">previous article</a> where I detailed the steps to install PostgreSQL and set up DBeaver, a versatile database tool that will be essential for practicing the code discussed here. This setup will not only facilitate your learning experience but also empower you to follow along with the examples and exercises I’ll be sharing. So, if you haven’t already, be sure to take a quick detour to my last post to ensure you’re well-prepared for the SQL journey ahead. Your proficiency in SQL and PostgreSQL will undoubtedly benefit from a solid foundation, and these tools will play a pivotal role in your hands-on learning process.</p><p>Let’s get started … 😀</p><h4>CASE STUDY</h4><p>For this portfolio project, I chose to work with a crypto dataset provided by <strong>Altschool</strong>. This dataset comprises three key tables: Members, Prices, and Transactions. Let’s briefly delve into each of these tables and understand their role in this project:</p><ol><li><strong>Member Table</strong>: This table serves as a repository of member information within the crypto ecosystem. It includes fields such as member_id, first_name, and region.By analyzing the Member table, we can gain insights into the user base, their demographics, and their activity over time.</li><li><strong>Price Table</strong>: The Price table contains crucial data related to cryptocurrency prices. It includes information on the cryptocurrency’s ticker symbol, the price at a specific date and time, and the currency in which the price is denominated. This table is pivotal for tracking the value and fluctuations of various cryptocurrencies.</li><li><strong>Transactions Table</strong>: The Transactions table records every transaction within the crypto platform. It contains details such as the transaction ID, member involved, the amount transacted, the quantity, and the date of the transaction. Analyzing this table can help us understand transaction patterns, user behavior, and the overall financial activity within the ecosystem.</li></ol><p><strong>Entity Relation Diagram:<br></strong>To navigate this dataset effectively, it’s essential to have a clear understanding of the data mdel. Here’s a brief overview of the key fields in each table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Emb_CJh89aaywiQICh8zKA.png" /><figcaption><a href="https://drawsql.app/teams/my-space-2/diagrams/crypto-db">Image exported from drawSQL</a></figcaption></figure><p>With these tables and their associated fields, we have the foundation needed to embark on a comprehensive SQL portfolio project. In the following sections, we’ll explore the how to setup a postgres database, roles, privileges and schema.</p><h4>Setting Up Database, Role and Priviledge.</h4><p>To embark on my SQL exploration with the crypto dataset, it’s crucial to set up the necessary database, roles, and schema.</p><h4><strong>Create Role:</strong></h4><p>In the context of a multi-user database, it’s a good practice to define roles for various users or applications. So i started by creating a new postgres ROLE to manage access to my database. You can do this by executing SQL commands. Give your user an appropriate name, such as “cryptoverse_admin,” and make sure you assign the neccessary attributes.</p><pre>-- Create a user called cryptoverse_admin with CREATEDB and CREATEROLE attributes<br>CREATE ROLE cryptoverse_admin WITH LOGIN CREATEDB CREATEROLE ENCRYPTED PASSWORD &#39;admin1234&#39;;</pre><p>This separation of roles enhances security and data integrity.</p><h4>Create Database:</h4><p>Then i create a PostgreSQL database to house mycrypto dataset. Give your database an appropriate name, such as “metaverse,” and make sure it’s accessible for your SQL work.</p><pre>-- create database metaverse<br>CREATE DATABASE metaverse;</pre><h4>Grant Priviledge:</h4><p>Its also important to grant the necessary privileges to my created roles. Depending on your project’s requirements, you might grant SELECT, INSERT, UPDATE, DELETE, or other privileges to control what actions the roles can perform within the schema.</p><pre>-- Grant all database priviledge to user<br>GRANT ALL PRIVILEGES ON DATABASE metaverse TO cryptoverse_admin;</pre><p>Here, i granted all privileges on my database to the user.</p><h4><strong>Connecting to DBeaver</strong></h4><p>Now it’s time to connect to our PostgreSQL database using DBeaver in order to dive into SQL commands and extracting valuable insights from your crypto dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/855/1*2WolEHRublwtEzGe9TGeag.png" /><figcaption>Screenshot</figcaption></figure><p>1. <strong>Launch DBeaver</strong>: Start by opening DBeaver on your computer. If you haven’t already installed DBeaver, make sure to follow the installation steps outlined in my previous <a href="https://blog.devgenius.io/understanding-postgresql-database-administration-43e37c7dda72">post</a>.</p><p>2. <strong>Create a New Database Connection</strong>: In the DBeaver application, go to the “Database” menu and select “New Database Connection. <br> Choose “PostgreSQL” as your database type. DBeaver supports various database systems, and PostgreSQL is what we’re using for this project. Click “Next.”</p><p>3. <strong>Configure the Connection:<br> </strong>In the connection settings, provide the following information:</p><p>· <strong>Host</strong>: Specify the host where your PostgreSQL database is running (e.g., localhost if it’s on your local machine).</p><p>· <strong>Port</strong>: The default port for PostgreSQL is 5432.</p><p>· <strong>Database</strong>: Enter the name of the database you’ve created for your crypto dataset (e.g., “metaverse”).</p><p>· <strong>Username</strong>: Input the username associated with the role you’ve created for this project.</p><p>· <strong>Password</strong>: Enter the password for the username.</p><p>You can also save the password if you want DBeaver to remember it for future connections.</p><p>4. <strong>Test the Connection:<br></strong>After configuring the connection details, click “Test Connection” to ensure that DBeaver can successfully connect to your PostgreSQL database. If everything is set up correctly, you should receive a “Connection established” message.</p><p><strong>5. Save and Connect:</strong><br>Once the connection is successfully tested, click “Finish” to save the connection configuration. You can now see your new connection listed in DBeaver’s database explorer.</p><h4>Create Schema</h4><p>Schemas help us organize and structure our database. I created a dedicated schema for my crypto dataset, named “raw,” within the database. This will help keep our project organized and prevent naming conflicts with other database objects.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/598/1*KMMc-VCnV2rE9AHlrx_fgg.png" /></figure><h4><strong>Load Dataset</strong></h4><p>Now that our database, roles, and schema are in place, we can begin loading our crypto dataset into the database. You can use DBeaver to do this by importing data into the created schema. Make sure your data is accurately mapped to the respective tables and fields within your schema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7r5cFnzCZl7MishVjlFrRg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8VxwF-H_N-QEjelu60iFqQ.png" /></figure><h4>What Next?</h4><p>With our database, roles, and schema set up and the crypto dataset loaded, we’re well-prepared to embark on an exciting journey of SQL exploration. In the next part of this post, we’ll dive into SQL commands and techniques to derive valuable insights from the dataset.</p><p>We’ll explore how to craft SQL queries to extract meaningful information, perform data manipulations, and analyze the cryptocurrency data. Whether you’re on a path to become a data engineer or simply looking to enhance your SQL skills, this journey promises to be both educational and rewarding.</p><p>Stay tuned for the <a href="https://medium.com/@oyekanmiakande/portfolio-project-approach-to-learning-sql-part-2-ca552643d88c">part 2</a> where we’ll delve into SQL queries and uncover hidden gems in the world of cryptocurrency data. Join me in this hands-on adventure as we unlock the potential of SQL and data engineering!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ae7544ba7818" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/portfolio-project-approach-to-learning-sql-part-1-ae7544ba7818">Portfolio Project Approach to Learning SQL (Part 1).</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding PostgreSQL Database Administration.]]></title>
            <link>https://blog.devgenius.io/understanding-postgresql-database-administration-43e37c7dda72?source=rss-ac22395e4b4c------2</link>
            <guid isPermaLink="false">https://medium.com/p/43e37c7dda72</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[installation]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dbeaver]]></category>
            <dc:creator><![CDATA[Oyekanmi Olamilekan]]></dc:creator>
            <pubDate>Wed, 27 Sep 2023 07:56:42 GMT</pubDate>
            <atom:updated>2023-09-29T18:06:10.917Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YD5BKEekKYTJ1byfR--6sA.png" /><figcaption>Made with <a href="https://www.canva.com/design/DAFvnxkJDhs/9OZ79ST87jDdDbTUR9IddQ/edit?ui=eyJHIjp7fX0">Canvas</a></figcaption></figure><p>It’s a month today, since I embarked on a journey together with <a href="https://learn.altschoolafrica.com/">AltSchool Africa</a> to become a world-class Data Engineer. I must say its has been a wonderful journey so far 😁. So, recently I had to install PostgreSQL and DBeaver on my OS and I found myself struggling with setting up my development environment.</p><p>When following steps as provided by the instructor on the LMS platform, I couldn’t install the PostgreSQL on my PC. So I had to do some research online from blogs and Youtube.</p><blockquote>“Knowledge, like the light of a candle, only grows brighter when shared with others.”</blockquote><p>I am sharing with you the steps I took to set up my system.</p><p>In this post I focused on guiding you on how to install PostgreSQL and DBeaver. As a Data Engineer, one of the most important skills you need is the understanding on how to manage and manipulate a Database.</p><blockquote>A Database is an organized collection of data stored and accessed electronically.</blockquote><p>A Database allows the storage of structure data into tables — which are related by field known as Foreign Key.</p><p>In order to store, manage, or manipulate data in a database there is the need for a Database Administration Tools.</p><blockquote>A Database Administration Tools are tools that provide an interface for automating database tasks like: creating, modifying, and deleting databases, setting roles and principles, extracting data from tables, and any other tasks that can run on a Database.</blockquote><h3>Downloading and Installing PostgreSQL for MacOS.</h3><ol><li>Now, let’s download a PostgreSQL for MacOS user. Click <a href="https://www.postgresql.org/download/">here</a>, to download PostgreSQL for your pc.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LcgqfIN2ZAl1Odrhb_znSA.png" /><figcaption>screenshot</figcaption></figure><p>2. Click on the macOS icon to download PostgreSQL. This time we will be installingPostgreSQL into our PC using the <a href="https://postgresapp.com/">Postgres.app</a>. This is one of the most easiest way to download PostgreSQL.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JGghkSG39llBwoFyIYF_iw.png" /></figure><p>3. Once you click on the <a href="https://postgresapp.com/">Postgres.app</a>. You will be directed to a new page to download the <a href="https://postgresapp.com/">Postgres.app</a>. Click to Download.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jc_sc68_pi9GMini4_hsvw.png" /></figure><p>Then the download begins … After downloading the PostgreSQL app onto your system you can double click on the installer to install. Drag the Postgres app onto the Applications folder and thats it for the installation process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/580/1*bWwf225InKdKDsyOtxMV1Q.png" /></figure><h3>Setting up the PostgreSQL</h3><ol><li>Now, we are done dowloading and installing PostgreSQL for macOS user. Let’s move ahead to set our database up. To do this, launch the installed application from your Applications folder. You will get a response like below.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/719/1*lvQcTJ91nNz34buRuYr9Aw.png" /></figure><p>2. The server is currently not running. Click the start button to start the server. Immediately after starting the server you will see that the server comes with three dafault database — user, postgres and template1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/719/1*C-kd4DZ9Zw1G2GPms2BJzQ.png" /></figure><p>3. We need to interact with this database to create a new user, create database, create table, etc. How do we do this without the installation of an RDBMS? Obviously this will be done using the PostgreSQL terminal.</p><p>4. To launch the terminal, double-click on the default postgres database showing above and a terminal will be launch.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*CcIigDaDQN2Br8KXOlMKjA.png" /></figure><p>5. Once you have this we are already in the Postgres environment. Let’s play with the terminal for a while.</p><blockquote>To view the list of Databases, type <strong>\list </strong>or <strong>\l</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RZj4qtWOHiXItQkQ9s-jZw.png" /></figure><blockquote>To view the list of users, type <strong>\du;</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*1wfoEUGkK4dhbfUQ0s4yQQ.png" /></figure><blockquote>To Create a new user, type <strong>CREATE USER altschool_db_user WITH ENCRYPTED PASSWORD ‘altschool_db_password’;</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*-C7MV38EtV5eW4iK_6Pygw.png" /></figure><blockquote>To create a new Database, type <strong>CREATE DATABASE altschool_db;</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*8kMMLpl7i8K25ntF2ptjHg.png" /></figure><blockquote>To grant privilege of a database to a user. Type <strong>GRANT ALL PRIVILEGES ON DATABASE altschool_db TO altschool_db_user;</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*9YBRjLk0OvUcdmRmi_hmpw.png" /></figure><h3>Downloading and Installing DBeaver</h3><ol><li>To begin click <a href="https://dbeaver.io/download/">here</a> to download DBeaver on to your PC.</li><li>Select your situable OS. For this post I am focusing on macOS. Download and Install by dragging into the application folder.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*j2PWRhroNqYO3zvMKp2YIQ.png" /></figure><p>3. Now double-click on DBeaver from the application folder to launch.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AFZmRkPjk3ggYU6th9oyGg.png" /></figure><p>4. Now we have DBeaver installed. So lets connect the DBeaver to our PostgreSQL. Select the first icon to your left to connect to a database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/303/1*k8vcYQlqWk5Z0nRgRo-0Mg.png" /></figure><p>5. Select the PostgreSQL Database and click next.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BR_TZwr5VCzJLKxE-1yREw.png" /></figure><p>6. Set the details to match your database and user info as set above in the PostgreSQL server.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/808/1*vGSVaMAq8JxHpXIaCVdJjQ.png" /></figure><p>7. Click on Test Connection to verify your credentials.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/808/1*EP23RlNVFDL-6LbIRhh4cA.png" /></figure><p>8. If Successfully Connected, now you click finish and thats the end.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v8Bkb25fCYQ9Yx3qFPhCeQ.png" /></figure><p>I hope having all these resources in one place made this process a bit faster. Email me or leave a comment if you have any questions or suggestions on improving this guide. Thanks for reading!</p><p><em>Special thanks to Pascal Onuorah for providing his expertise as a Data Engineer Trainer for explaining the intricacies of the MacOS installation process.</em></p><p><strong><em>If you find this article helpful, please kindly share it within your network and don’t forget to follow me for more great articles, tutorials and expreriences at </em></strong><a href="https://learn.altschoolafrica.com/"><strong><em>AltSchool Africa </em></strong></a><strong><em>.</em></strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=43e37c7dda72" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/understanding-postgresql-database-administration-43e37c7dda72">Understanding PostgreSQL Database Administration.</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>