Python ETL to PostgreSQL to Power BI

Build a sales data pipeline from raw CSV to an interactive Power BI dashboard.

Introduction

30 Second Summary

Every business generates mountains of raw data from sales transactions, customer orders, and product inventories. Turning that raw data into clear, visual insights is one of the most in-demand skills in the data analytics world.

In this project, you will build a complete data pipeline that generates sales data, cleans and transforms it with Python, loads it into a database, and visualizes it in an interactive dashboard.

What You'll Build

A full data analyst workflow using Python, PostgreSQL, and Power BI Desktop to go from raw CSV data to a live interactive dashboard.

By the end of this project, you'll have:

  • A JupyterLab notebook containing your full ETL pipeline code that extracts, transforms, and loads sales data into a database.
  • A PostgreSQL database with a sales table and two analytics views for monthly revenue and top products.
  • An interactive Power BI dashboard connected live to your database, showing revenue trends, product rankings, and category filters.
  • Secret Mission: Add customer segmentation using RFM (Recency, Frequency, Monetary) scoring directly in SQL.

Are there any prerequisites?

This project assumes basic Python knowledge (if/else statements, while loops) and basic SQL skills (CREATE TABLE, SELECT, subqueries). You do not need prior experience with pandas, SQLAlchemy, or Power BI.

Set Up Your Python Environment and Database

Before you can build an ETL pipeline, you need the right tools in place. Your Python environment needs libraries for data manipulation and database connectivity. Your PostgreSQL server needs a dedicated database to receive the transformed data.

In this step, you will install three Python libraries, create a fresh database, and confirm that Python can talk to it. Once this foundation is solid, everything else in the pipeline flows naturally.

In this step, get ready to:
  • Install pandas, SQLAlchemy, and psycopg2-binary in JupyterLab.
  • Create a PostgreSQL database for your sales data.
  • Verify the Python-to-database connection works.
Install Python libraries in JupyterLab

Your ETL pipeline depends on three libraries working together. pandas handles data manipulation, SQLAlchemy manages database connections, and psycopg2-binary is the PostgreSQL driver that makes the connection possible.

  • In your JupyterLab notebook, click into the first cell and install the libraries by running this command:
!pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

What does this command do?

The ! prefix tells JupyterLab to run this as a terminal command rather than Python code.

  • pandas==2.2.3 is a data analysis library for reading, cleaning, and transforming tabular data.
  • sqlalchemy==2.0.50 provides a Python interface for connecting to and querying databases.
  • psycopg2-binary==2.9.12 is a pre-compiled PostgreSQL adapter that requires no compiler on your machine.
  • Wait for the installation to finish. You should see "Successfully installed" messages in the output.
  • Comment out the install line (add # at the start) so it does not re-run every time you restart the notebook.

Now confirm the libraries are importable. This will also set up the imports you need for the rest of the project.

  • Create a new cell below and add your imports by pasting this code:
import pandas as pd
from sqlalchemy import create_engine, text
import random
from datetime import datetime, timedelta

What does this code do?

  • pandas as pd gives you the standard shorthand for calling pandas functions.
  • create_engine from SQLAlchemy creates a connection object that points to your database.
  • text lets you write raw SQL queries inside Python.
  • random and datetime are standard Python libraries you will use later to generate sample data.
  • Run the cell. If no errors appear, all libraries are installed correctly.

Seeing a ModuleNotFoundError?

  • Make sure you ran the !pip install cell first and it completed without errors.
  • Check that you are running the cell in the same notebook where you installed the packages.
  • Try restarting the kernel (click **Kernel** then **Restart Kernel**) and run both cells again from the top.

Help me fix a ModuleNotFoundError after pip install in JupyterLab.

Create the PostgreSQL database

Your ETL pipeline needs a destination database where the cleaned data will land. You will create a dedicated database called sales_analytics to keep your sales data separate from any other PostgreSQL databases on your machine.

  • Open your PostgreSQL client (psql in your terminal, or pgAdmin if you prefer a graphical tool).
  • Create your database by running this SQL command:
CREATE DATABASE sales_analytics;

What is a PostgreSQL database?

A PostgreSQL database is an isolated container for tables, views, and other objects. Creating a dedicated database for this project keeps your sales data organized and separate from PostgreSQL's built-in system databases.

  • You should see a CREATE DATABASE confirmation message.

Getting a permission denied error?

  • Make sure you are connected as the postgres superuser or a role with the CREATEDB privilege.
  • If using psql, try connecting with psql -U postgres and entering your password when prompted.

Help me connect to PostgreSQL and create a database.

Test the connection from Python

The final piece is proving that Python can reach your new database. SQLAlchemy uses a connection string (called an engine) that specifies the driver, credentials, host, port, and database name.

  • Back in your JupyterLab notebook, create a new cell and test the connection by running this code:
# Test connection to PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")
with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

What does this code do?

  • create_engine(...) builds a connection pool pointed at your sales_analytics database on localhost:5432.
  • The connection string format is postgresql+psycopg2://user:password@host:port/database.
  • SELECT version(); is a simple PostgreSQL query that returns the server version. If you see a version string, your connection works.
  • Replace your-password-here in the connection string with your actual PostgreSQL password.
  • Run the cell. You should see your PostgreSQL 17 version string printed in the output.

Seeing a connection refused error?

  • Confirm PostgreSQL is running on your machine. The server must be active to accept connections.
  • Double-check that your password in the connection string matches the one you set during PostgreSQL installation.
  • Verify the database name is spelled exactly sales_analytics (underscore, not a hyphen).

Help me fix my Python connection to PostgreSQL.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all three cells.

ⓧ I'd like to double check the full code

Here is what your full notebook should look like at this point:

# Cell 1: Install libraries
# !pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

# Cell 2: Imports
import pandas as pd
from sqlalchemy import create_engine, text
import random
from datetime import datetime, timedelta

# Cell 3: Test connection
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")
with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

Your Python environment is ready and confirmed talking to PostgreSQL. Next up, you will generate a realistic sales dataset and explore it with pandas.

Generate and Explore Sample Sales Data

Your Python environment is connected to PostgreSQL and ready to move data. The next question is: what data?

Real-world data pipelines start with raw data. In this step, you will generate a realistic 500-row sales dataset using Python, then explore it with pandas to understand its shape and quality before transforming it.

In this step, get ready to:
  • Generate a realistic 500-row sales dataset with Python and save it as a CSV file.
  • Explore the dataset's shape, types, and data quality using pandas.
Create sample sales data

Instead of downloading an external dataset that could break or disappear, you will generate your own. This gives you a known-quantity dataset that makes validation straightforward later.

  • In your etl_pipeline.ipynb notebook, create a new cell below your connection test and paste the following code:
# Cell 4: Generate sample sales data
random.seed(42)
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Drive", "Tablet"]
categories = {"Laptop": "Electronics", "Mouse": "Accessories", "Keyboard": "Accessories",
              "Monitor": "Electronics", "Headphones": "Audio", "Webcam": "Accessories",
              "USB Drive": "Storage", "Tablet": "Electronics"}
prices = {"Laptop": 999.99, "Mouse": 29.99, "Keyboard": 79.99, "Monitor": 349.99,
           "Headphones": 149.99, "Webcam": 89.99, "USB Drive": 19.99, "Tablet": 499.99}

rows = []
start_date = datetime(2024, 1, 1)
for i in range(1, 501):
    product = random.choice(products)
    order_date = start_date + timedelta(days=random.randint(0, 364))
    quantity = random.randint(1, 5)
    rows.append({
        "order_id": i,
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": f"CUST-{random.randint(1, 50):03d}",
        "product": product,
        "category": categories[product],
        "unit_price": prices[product],
        "quantity": quantity,
        "total_amount": round(prices[product] * quantity, 2)
    })

df_raw = pd.DataFrame(rows)
df_raw.to_csv("sales_data.csv", index=False)
print(f"Generated {len(df_raw)} rows")
print(df_raw.head())

What does this code do?

This cell builds a fake-but-realistic sales dataset with 500 orders spread across 2024.

  • random.seed(42) locks the randomness so everyone generates the same data. This makes debugging easier.
  • The three dictionaries (products, categories, prices) define the product catalog that the loop picks from randomly.
  • The loop creates 500 order dictionaries, each with a unique order_id, a random date, a random customer ID (up to 50 customers), and a calculated total_amount.
  • pd.DataFrame(rows) converts the list of dictionaries into a DataFrame, and to_csv() saves it to disk as a CSV file.
  • Run the cell by pressing Shift+Enter.
  • You should see Generated 500 rows followed by a table showing the first 5 rows of your dataset.

Seeing a NameError?

  • If you see NameError: name 'random' is not defined or similar, make sure you ran Cell 2 (the imports cell) first. Each time you reopen JupyterLab, you need to re-run earlier cells.
  • If you see NameError: name 'datetime' is not defined, confirm that your Cell 2 includes from datetime import datetime, timedelta.

Help me debug my NameError in JupyterLab

Explore the data

Before transforming data, a data analyst always checks its shape and quality. This tells you whether anything needs cleaning before it reaches the database.

  • Create a new cell below and paste the following exploration code:
# Cell 5: Read and explore data
df = pd.read_csv("sales_data.csv")
print(df.info())
print(df.describe())
print(f"\nNull values:\n{df.isnull().sum()}")
print(f"\nUnique customers: {df['customer_id'].nunique()}")
print(f"Date range: {df['order_date'].min()} to {df['order_date'].max()}")

What does this code do?

  • pd.read_csv("sales_data.csv") loads the CSV you just created into a new DataFrame called df. This simulates how you would load real data from a file.
  • df.info() shows the column names, data types, and non-null counts at a glance.
  • df.describe() gives summary statistics (mean, min, max) for numeric columns.
  • df.isnull().sum() checks for missing values in every column. Zero nulls means clean data.
  • nunique() counts distinct customer IDs, confirming the dataset has up to 50 unique customers.
  • Run the cell by pressing Shift+Enter.
  • You should see the DataFrame info showing 500 entries, 8 columns, and 0 null values across all columns. The date range should span from 2024-01-01 to 2024-12-30.

Seeing FileNotFoundError?

  • This means sales_data.csv was not created in your working directory. Go back and re-run Cell 4 (the data generation cell) first.
  • If you restarted your JupyterLab kernel, you need to re-run all cells from Cell 2 onward in order.

Help me fix my FileNotFoundError in JupyterLab

Why read the CSV back instead of using df_raw directly?

Reading from the saved CSV simulates a real ETL workflow where your source data comes from a file or external system. It also confirms the CSV was written correctly.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the full notebook code up to this point:

# Cell 1: Install libraries
# !pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

# Cell 2: Imports
import pandas as pd
from sqlalchemy import create_engine, text
import random
from datetime import datetime, timedelta

# Cell 3: Test connection
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")
with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

# Cell 4: Generate sample sales data
random.seed(42)
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Drive", "Tablet"]
categories = {"Laptop": "Electronics", "Mouse": "Accessories", "Keyboard": "Accessories",
              "Monitor": "Electronics", "Headphones": "Audio", "Webcam": "Accessories",
              "USB Drive": "Storage", "Tablet": "Electronics"}
prices = {"Laptop": 999.99, "Mouse": 29.99, "Keyboard": 79.99, "Monitor": 349.99,
           "Headphones": 149.99, "Webcam": 89.99, "USB Drive": 19.99, "Tablet": 499.99}

rows = []
start_date = datetime(2024, 1, 1)
for i in range(1, 501):
    product = random.choice(products)
    order_date = start_date + timedelta(days=random.randint(0, 364))
    quantity = random.randint(1, 5)
    rows.append({
        "order_id": i,
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": f"CUST-{random.randint(1, 50):03d}",
        "product": product,
        "category": categories[product],
        "unit_price": prices[product],
        "quantity": quantity,
        "total_amount": round(prices[product] * quantity, 2)
    })

df_raw = pd.DataFrame(rows)
df_raw.to_csv("sales_data.csv", index=False)
print(f"Generated {len(df_raw)} rows")
print(df_raw.head())

# Cell 5: Read and explore data
df = pd.read_csv("sales_data.csv")
print(df.info())
print(df.describe())
print(f"\nNull values:\n{df.isnull().sum()}")
print(f"\nUnique customers: {df['customer_id'].nunique()}")
print(f"Date range: {df['order_date'].min()} to {df['order_date'].max()}")

You now have a clean, well-understood dataset ready for transformation. Next up, you will clean this data with pandas and load it into your PostgreSQL database.

Transform and Load Data into PostgreSQL

You have a 500-row DataFrame ready to go. Right now, though, its columns are all generic types inferred from the CSV read.

Before loading data into a database, you need to enforce the correct data types and remove any duplicates. This is the Transform phase of your ETL pipeline. Once the data is clean, you will load it into PostgreSQL and create SQL views that Power BI can query directly.

In this step, get ready to:
  • Clean and cast your DataFrame columns to proper types.
  • Load the transformed data into a PostgreSQL table.
  • Create SQL views for monthly revenue and top products.
Transform the data with pandas

CSV files store everything as strings. Your database expects dates, integers, and floats in specific columns. Casting types now prevents silent errors when you query the data later.

  • In your JupyterLab notebook, create a new cell (Cell 6) and paste the following code to cast column types and remove duplicates:
# Cell 6: Transform data
df["order_date"] = pd.to_datetime(df["order_date"])
df["order_id"] = df["order_id"].astype(int)
df["quantity"] = df["quantity"].astype(int)
df["unit_price"] = df["unit_price"].astype(float)
df["total_amount"] = df["total_amount"].astype(float)
df = df.drop_duplicates(subset=["order_id"])
print(f"Cleaned rows: {len(df)}")
print(df.dtypes)

What does this code do?

Each line enforces the correct data type for a column. pd.to_datetime() converts the date strings into proper datetime objects that PostgreSQL can store as timestamps.

The .astype() calls ensure numeric columns are stored as integers or floats rather than generic objects. Finally, drop_duplicates() removes any rows with repeated order_id values to guarantee each order appears only once.

  • Run the cell by pressing Shift+Enter.
  • You should see Cleaned rows: 500 followed by a list of dtypes showing datetime64 for order_date and int64 or float64 for the numeric columns.

Seeing a KeyError?

Make sure you ran Cell 5 first. Cell 6 depends on the df variable created by pd.read_csv("sales_data.csv") in that earlier cell. If you restarted the kernel, re-run all cells above this one.

Help me fix a KeyError in my pandas transform cell.

Load the data into PostgreSQL

With clean data in hand, the next step is loading it into your database. The pandas to_sql() method paired with SQLAlchemy handles table creation and data insertion in a single call.

  • Create a new cell (Cell 7) and paste this code to load your DataFrame into PostgreSQL:
# Cell 7: Connect and load into PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")

df.to_sql("sales_data", engine, if_exists="replace", index=False)

with engine.connect() as conn:
    result = conn.execute(text("SELECT COUNT(*) FROM sales_data;"))
    print(f"Rows loaded: {result.fetchone()[0]}")

What does this code do?

The create_engine() call builds a fresh connection to your sales_analytics database. This is the same connection string from Cell 3, re-stated here so Cell 7 works independently if you restart.

The df.to_sql() method creates a table called sales_data in PostgreSQL and inserts all 500 rows. The if_exists="replace" argument means it will drop and recreate the table if you run the cell again. Setting index=False prevents pandas from adding an extra index column.

The SELECT COUNT(*) query at the end confirms exactly how many rows made it into the database.

  • Replace your-password-here with your actual PostgreSQL password (the same one you used in Cell 3).
  • Run the cell. You should see Rows loaded: 500 in the output.

Seeing a connection refused error?

Double-check that your PostgreSQL server is running. Also confirm your password matches exactly. If you changed your password since Cell 3, update it here too.

Help me troubleshoot my PostgreSQL connection error in pandas to_sql.

Create analytics views in PostgreSQL

Raw tables hold all the data, but dashboards need pre-aggregated summaries. SQL views act as saved queries that Power BI can treat like tables.

  • Create a new cell (Cell 8) and paste this code to build two analytics views:
# Cell 8: Create analytics views
with engine.connect() as conn:
    conn.execute(text("""
        CREATE OR REPLACE VIEW monthly_revenue AS
        SELECT
            DATE_TRUNC('month', order_date) AS month,
            SUM(total_amount) AS revenue,
            COUNT(order_id) AS total_orders
        FROM sales_data
        GROUP BY DATE_TRUNC('month', order_date)
        ORDER BY month;
    """))

    conn.execute(text("""
        CREATE OR REPLACE VIEW top_products AS
        SELECT
            product,
            category,
            SUM(quantity) AS units_sold,
            SUM(total_amount) AS total_revenue
        FROM sales_data
        GROUP BY product, category
        ORDER BY total_revenue DESC;
    """))
    conn.commit()
    print("Views created successfully")

What do these views do?

The monthly_revenue view groups all orders by month and calculates total revenue and order count per month. Power BI will use this for a line chart showing revenue trends over time.

The top_products view summarizes each product's total units sold and revenue. Power BI will use this for a bar chart ranking products by performance.

The conn.commit() call saves both views permanently to the database. Without it, the views would be lost when the connection closes.

  • Run the cell. You should see Views created successfully in the output.
  • Verify the views work by running this quick query in a new cell:
# Quick check: verify both views return data
with engine.connect() as conn:
    monthly = conn.execute(text("SELECT * FROM monthly_revenue LIMIT 5;"))
    print("Monthly revenue (first 5 rows):")
    for row in monthly:
        print(row)

    products = conn.execute(text("SELECT * FROM top_products;"))
    print("\nTop products:")
    for row in products:
        print(row)
  • Run this verification cell. You should see monthly revenue rows (with month timestamps, revenue totals, and order counts) and all 8 products ranked by total revenue.

Views not returning data?

Make sure Cell 7 ran successfully first. The views query the sales_data table, so it must exist and contain rows before the views can return results.

Also check for typos in the view names. They must be exactly monthly_revenue and top_products when queried.

Help me figure out why my SQL views aren't returning data.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the complete notebook up to this point. Compare it against your cells:

# Cell 1: Install libraries
# !pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

# Cell 2: Imports
import pandas as pd
from sqlalchemy import create_engine, text
import random
from datetime import datetime, timedelta

# Cell 3: Test connection
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")
with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

# Cell 4: Generate sample sales data
random.seed(42)
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Drive", "Tablet"]
categories = {"Laptop": "Electronics", "Mouse": "Accessories", "Keyboard": "Accessories",
              "Monitor": "Electronics", "Headphones": "Audio", "Webcam": "Accessories",
              "USB Drive": "Storage", "Tablet": "Electronics"}
prices = {"Laptop": 999.99, "Mouse": 29.99, "Keyboard": 79.99, "Monitor": 349.99,
           "Headphones": 149.99, "Webcam": 89.99, "USB Drive": 19.99, "Tablet": 499.99}

rows = []
start_date = datetime(2024, 1, 1)
for i in range(1, 501):
    product = random.choice(products)
    order_date = start_date + timedelta(days=random.randint(0, 364))
    quantity = random.randint(1, 5)
    rows.append({
        "order_id": i,
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": f"CUST-{random.randint(1, 50):03d}",
        "product": product,
        "category": categories[product],
        "unit_price": prices[product],
        "quantity": quantity,
        "total_amount": round(prices[product] * quantity, 2)
    })

df_raw = pd.DataFrame(rows)
df_raw.to_csv("sales_data.csv", index=False)
print(f"Generated {len(df_raw)} rows")
print(df_raw.head())

# Cell 5: Read and explore data
df = pd.read_csv("sales_data.csv")
print(df.info())
print(df.describe())
print(f"\nNull values:\n{df.isnull().sum()}")
print(f"\nUnique customers: {df['customer_id'].nunique()}")
print(f"Date range: {df['order_date'].min()} to {df['order_date'].max()}")

# Cell 6: Transform data
df["order_date"] = pd.to_datetime(df["order_date"])
df["order_id"] = df["order_id"].astype(int)
df["quantity"] = df["quantity"].astype(int)
df["unit_price"] = df["unit_price"].astype(float)
df["total_amount"] = df["total_amount"].astype(float)
df = df.drop_duplicates(subset=["order_id"])
print(f"Cleaned rows: {len(df)}")
print(df.dtypes)

# Cell 7: Connect and load into PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")

df.to_sql("sales_data", engine, if_exists="replace", index=False)

with engine.connect() as conn:
    result = conn.execute(text("SELECT COUNT(*) FROM sales_data;"))
    print(f"Rows loaded: {result.fetchone()[0]}")

# Cell 8: Create analytics views
with engine.connect() as conn:
    conn.execute(text("""
        CREATE OR REPLACE VIEW monthly_revenue AS
        SELECT
            DATE_TRUNC('month', order_date) AS month,
            SUM(total_amount) AS revenue,
            COUNT(order_id) AS total_orders
        FROM sales_data
        GROUP BY DATE_TRUNC('month', order_date)
        ORDER BY month;
    """))

    conn.execute(text("""
        CREATE OR REPLACE VIEW top_products AS
        SELECT
            product,
            category,
            SUM(quantity) AS units_sold,
            SUM(total_amount) AS total_revenue
        FROM sales_data
        GROUP BY product, category
        ORDER BY total_revenue DESC;
    """))
    conn.commit()
    print("Views created successfully")

Your data is now transformed, loaded, and queryable from two purpose-built views. Next up, you will validate the data from Python before connecting Power BI to your live database.

Query and Validate Your Data

Your database now has a clean sales table and two analytics views ready to query.

Before connecting Power BI, you need to verify that the views return accurate data. In this step, you'll query both views from Python and run a validation check to confirm the numbers add up correctly.

In this step, get ready to:
  • Query both analytics views from Python and inspect the results.
  • Run a validation check confirming revenue totals match between the source table and the view.
Run analytics queries from Python

Your monthly_revenue and top_products views exist in PostgreSQL, but you haven't pulled their results back into Python yet. Loading them into pandas DataFrames lets you inspect the output right inside your notebook.

  • In your notebook, create a new cell (Cell 9) and query both views by running this code:
# Cell 9: Query and validate
df_monthly = pd.read_sql("SELECT * FROM monthly_revenue", engine)
print("Monthly Revenue:")
print(df_monthly)

df_products = pd.read_sql("SELECT * FROM top_products", engine)
print("\nTop Products by Revenue:")
print(df_products)

What does this code do?

pd.read_sql() runs a SQL query through your existing engine connection and returns the results as a DataFrame.

df_monthly holds the monthly revenue aggregation (one row per month). df_products holds the product ranking by total revenue.

  • Run the cell.
  • You should see a table of monthly revenue figures (12 rows, one per month of 2024) followed by a table ranking all 8 products by total revenue.

Seeing an empty DataFrame?

Make sure you ran the previous cells in order. The engine variable must be defined from Cell 7, and the views must be created from Cell 8.

If you restarted your kernel, re-run all cells from Cell 2 onwards.

Help me debug why my read_sql query returns an empty DataFrame

Validate revenue totals match

Views aggregate data from your source table, so their totals should match exactly. A mismatch would mean your view logic has a bug or data was lost during loading.

  • Create a new cell (Cell 10) and run this validation check:
# Cell 10: Validation check
with engine.connect() as conn:
    total_revenue = conn.execute(text("SELECT SUM(total_amount) FROM sales_data;")).fetchone()[0]
    view_revenue = conn.execute(text("SELECT SUM(revenue) FROM monthly_revenue;")).fetchone()[0]
    print(f"Table total: ${total_revenue:,.2f}")
    print(f"View total:  ${view_revenue:,.2f}")
    print(f"Match: {total_revenue == view_revenue}")

What does this code do?

This queries the raw sales_data table for the total revenue, then queries the monthly_revenue view for its aggregated total.

Comparing the two values confirms that the view accurately sums all rows from the source table. If the output says Match: True, your data pipeline is producing consistent results from end to end.

  • Run the cell.
  • You should see two dollar amounts printed followed by Match: True.

Seeing Match: False?

This means the sum from your view doesn't equal the sum from your table. Re-run Cell 8 to recreate the views, then re-run Cell 10.

If you modified the sales_data table after creating the views, the views reflect the current table state automatically. Re-run the data load (Cell 7) to reset.

Help me figure out why my revenue totals don't match

Why validate before visualizing?

If your aggregation logic is wrong, every chart you build on top of it will show misleading numbers. Catching a mismatch now saves you from debugging inside Power BI later, where the root cause is much harder to trace.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the full notebook up to this point:

# Cell 1: Install libraries
# !pip install pandas==2.2.3 sqlalchemy==2.0.50 psycopg2-binary==2.9.12

# Cell 2: Imports
import pandas as pd
from sqlalchemy import create_engine, text
import random
from datetime import datetime, timedelta

# Cell 3: Test connection
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")
with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

# Cell 4: Generate sample sales data
random.seed(42)
products = ["Laptop", "Mouse", "Keyboard", "Monitor", "Headphones", "Webcam", "USB Drive", "Tablet"]
categories = {"Laptop": "Electronics", "Mouse": "Accessories", "Keyboard": "Accessories",
              "Monitor": "Electronics", "Headphones": "Audio", "Webcam": "Accessories",
              "USB Drive": "Storage", "Tablet": "Electronics"}
prices = {"Laptop": 999.99, "Mouse": 29.99, "Keyboard": 79.99, "Monitor": 349.99,
           "Headphones": 149.99, "Webcam": 89.99, "USB Drive": 19.99, "Tablet": 499.99}

rows = []
start_date = datetime(2024, 1, 1)
for i in range(1, 501):
    product = random.choice(products)
    order_date = start_date + timedelta(days=random.randint(0, 364))
    quantity = random.randint(1, 5)
    rows.append({
        "order_id": i,
        "order_date": order_date.strftime("%Y-%m-%d"),
        "customer_id": f"CUST-{random.randint(1, 50):03d}",
        "product": product,
        "category": categories[product],
        "unit_price": prices[product],
        "quantity": quantity,
        "total_amount": round(prices[product] * quantity, 2)
    })

df_raw = pd.DataFrame(rows)
df_raw.to_csv("sales_data.csv", index=False)
print(f"Generated {len(df_raw)} rows")
print(df_raw.head())

# Cell 5: Read and explore data
df = pd.read_csv("sales_data.csv")
print(df.info())
print(df.describe())
print(f"\nNull values:\n{df.isnull().sum()}")
print(f"\nUnique customers: {df['customer_id'].nunique()}")
print(f"Date range: {df['order_date'].min()} to {df['order_date'].max()}")

# Cell 6: Transform data
df["order_date"] = pd.to_datetime(df["order_date"])
df["order_id"] = df["order_id"].astype(int)
df["quantity"] = df["quantity"].astype(int)
df["unit_price"] = df["unit_price"].astype(float)
df["total_amount"] = df["total_amount"].astype(float)
df = df.drop_duplicates(subset=["order_id"])
print(f"Cleaned rows: {len(df)}")
print(df.dtypes)

# Cell 7: Connect and load into PostgreSQL
engine = create_engine("postgresql+psycopg2://postgres:your-password-here@localhost:5432/sales_analytics")

df.to_sql("sales_data", engine, if_exists="replace", index=False)

with engine.connect() as conn:
    result = conn.execute(text("SELECT COUNT(*) FROM sales_data;"))
    print(f"Rows loaded: {result.fetchone()[0]}")

# Cell 8: Create analytics views
with engine.connect() as conn:
    conn.execute(text("""
        CREATE OR REPLACE VIEW monthly_revenue AS
        SELECT
            DATE_TRUNC('month', order_date) AS month,
            SUM(total_amount) AS revenue,
            COUNT(order_id) AS total_orders
        FROM sales_data
        GROUP BY DATE_TRUNC('month', order_date)
        ORDER BY month;
    """))

    conn.execute(text("""
        CREATE OR REPLACE VIEW top_products AS
        SELECT
            product,
            category,
            SUM(quantity) AS units_sold,
            SUM(total_amount) AS total_revenue
        FROM sales_data
        GROUP BY product, category
        ORDER BY total_revenue DESC;
    """))
    conn.commit()
    print("Views created successfully")

# Cell 9: Query and validate
df_monthly = pd.read_sql("SELECT * FROM monthly_revenue", engine)
print("Monthly Revenue:")
print(df_monthly)

df_products = pd.read_sql("SELECT * FROM top_products", engine)
print("\nTop Products by Revenue:")
print(df_products)

# Cell 10: Validation check
with engine.connect() as conn:
    total_revenue = conn.execute(text("SELECT SUM(total_amount) FROM sales_data;")).fetchone()[0]
    view_revenue = conn.execute(text("SELECT SUM(revenue) FROM monthly_revenue;")).fetchone()[0]
    print(f"Table total: ${total_revenue:,.2f}")
    print(f"View total:  ${view_revenue:,.2f}")
    print(f"Match: {total_revenue == view_revenue}")

Your data is clean, loaded, and validated. Next up, you'll connect Power BI Desktop directly to your PostgreSQL database and build an interactive dashboard from these views.

Connect Power BI and Build Your Dashboard

Your ETL pipeline is validated and your PostgreSQL database is loaded with clean sales data. The hard work of data engineering is done.

Now it's time to turn that data into business insights. You'll connect Power BI Desktop directly to your PostgreSQL database and build an interactive dashboard with charts, cards, and filters.

In this step, get ready to:
  • Connect Power BI Desktop to your PostgreSQL database.
  • Build a line chart, bar chart, and card visual from your views.
  • Add a category slicer for interactive filtering.
Connect Power BI Desktop to PostgreSQL

Power BI Desktop connects directly to your local PostgreSQL instance. It ships with the Npgsql provider bundled in, so no additional driver installation is needed.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • On the Home tab in the ribbon, click Get Data.
  • In the Get Data dialog, select Database on the left.
  • Select PostgreSQL database and click Connect.
  • Enter localhost in the Server field.
  • Enter sales_analytics in the Database field.
  • Click OK.
  • On the left side of the credentials dialog, select Database as the authentication type.
  • Enter your PostgreSQL username (default is postgres) and your password.
  • Click Connect.
  • In the Navigator window, check the boxes next to sales_data, monthly_revenue, and top_products.
  • Click Load.

Why Database authentication?

Power BI does not support Windows Authentication for PostgreSQL connections. You must use Database (username/password) authentication to connect.

After loading, you should see sales_data, monthly_revenue, and top_products listed in the Data pane on the right side of Power BI Desktop.

Connection refused?

Make sure your PostgreSQL server is running on localhost:5432. Check that the username and password match what you used in your JupyterLab notebook.

If you see a firewall warning, allow Power BI Desktop through your system firewall.

help me connect Power BI Desktop to my local PostgreSQL database

Build the dashboard visuals

You'll create three visuals that show different perspectives on your sales data. Each visual pulls from the views and table you loaded.

Start with a line chart showing revenue over time:

  • Click an empty area on the report canvas to make sure nothing is selected.
  • In the Visualizations pane, click the Line chart icon.
  • In the Data pane on the right, expand monthly_revenue.
  • Drag month to the X-axis well in the Visualizations pane.
  • Drag revenue to the Y-axis well.

You should see a line chart with monthly revenue plotted across the year 2024.

Next, create a bar chart ranking products by revenue:

  • Click an empty area on the canvas to deselect the line chart.
  • In the Visualizations pane, click the Clustered bar chart icon.
  • In the Data pane, expand top_products.
  • Drag product to the Y-axis well.
  • Drag total_revenue to the X-axis well.
  • To sort the bars descending by revenue, click More options (...) in the top-right corner of the bar chart visual.
  • Select Sort axis, then select total_revenue.
  • Click More options (...) again, select Sort axis, then select Sort descending.

Your bar chart now shows products ranked from highest to lowest revenue.

Finally, add a card visual showing total revenue:

  • Click an empty area on the canvas.
  • In the Visualizations pane, click the Card visual icon.
  • In the Data pane, expand top_products.
  • Drag total_revenue into the card's field well.
  • The card should display Sum of total_revenue as a single large number.

What do these visuals show?

The line chart reveals monthly revenue trends across 2024. The bar chart ranks all eight products by total revenue so you can spot top sellers. The card gives a single headline number for total revenue across all products.

Each visual pulls from the SQL views and table you created earlier. Power BI queries your PostgreSQL database every time you refresh.

Add a slicer for interactivity

A slicer is an on-canvas filter that lets report viewers segment data by a specific value. You'll add one that filters every visual by product category.

  • Click an empty area on the canvas to deselect any visual.
  • In the Visualizations pane, click the Slicer icon.
  • In the Data pane, expand sales_data.
  • Drag category into the slicer's field well.
  • Click different categories in the slicer (e.g. Electronics, Accessories) and watch all three visuals update dynamically.

How does the slicer filter other visuals?

By default, slicers on a report page filter all other visuals on that page. When you select a category, Power BI filters the underlying data to only rows matching that category. The line chart, bar chart, and card all update instantly.

  • Save your report by clicking File in the menu bar, then Save As.
  • Name the file sales_dashboard.pbix and save it to your Desktop.

Your dashboard should now show a line chart with monthly revenue trends, a bar chart ranking products by revenue, a total revenue card, and a category slicer that filters all visuals when clicked.

Visuals not updating when you click the slicer?

Make sure the slicer uses the category field from sales_data, not from one of the views. The slicer needs to share a relationship with the other tables to filter them.

If visuals still don't respond, check that you haven't accidentally disabled interactions. Select the slicer, go to the Format tab in the ribbon, and click Edit interactions to verify filters are enabled for each visual.

help me fix my Power BI slicer so it filters all visuals on the page

Secret mission

Add Customer Segmentation with RFM Analysis

Your dashboard shows what products sell and when revenue peaks. But which customers are your best? Use advanced SQL window functions to score every customer on Recency, Frequency, and Monetary value, then segment them into actionable groups like Champions and At Risk.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • PostgreSQL database sales_analytics (table and views).
  • JupyterLab notebook etl_pipeline.ipynb.
  • Generated CSV file sales_data.csv.
  • Power BI report sales_dashboard.pbix.
  • Python libraries installed via pip (pandas, sqlalchemy, psycopg2-binary).

Keep everything running

No action needed. Choose this if you're still actively building or plan to extend the dashboard further.

  • Your PostgreSQL database will remain available whenever the PostgreSQL service is running.
  • Your Power BI report will refresh from the live database each time you open it.
  • The pip packages (pandas, sqlalchemy, psycopg2-binary) will remain installed in your Python environment.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay on disk for when you return.

  • Close the JupyterLab browser tab and shut down the Jupyter server from your terminal (press Ctrl+C in the terminal where it is running).
  • Close Power BI Desktop. Your sales_dashboard.pbix file is already saved.
  • Your PostgreSQL database will remain on disk. When you come back, just reconnect from JupyterLab or Power BI.

Delete - I don't want to use this again

Remove all project resources and start fresh. This cannot be undone.

  • Drop the PostgreSQL database by opening your PostgreSQL client (psql) and running:
  • Connect to the default postgres database first (you cannot drop a database while connected to it).
\c postgres
DROP DATABASE sales_analytics;
  • Delete your local project files. On macOS or Linux, run:
rm etl_pipeline.ipynb sales_data.csv sales_dashboard.pbix
  • On Windows, run:
del etl_pipeline.ipynb, sales_data.csv, sales_dashboard.pbix
  • Uninstall the Python libraries by running this in your terminal:
pip uninstall pandas sqlalchemy psycopg2-binary -y

Nice Work!

Nice Work!

Nice work! You just built a complete data analyst workflow from raw data to live business dashboard.

You've learned how to:

  • Build a Python ETL pipeline using pandas, SQLAlchemy, and psycopg2 that extracts, transforms, and loads sales data.
  • Design a PostgreSQL database schema with a source table and analytics views.
  • Connect Power BI Desktop directly to your local PostgreSQL database and create an interactive dashboard with line charts, bar charts, cards, and slicers.
  • (Secret Mission) Implement RFM customer segmentation using advanced SQL window functions and visualize customer segments in Power BI.

Ready to quiz yourself?