B2B Sales Pipeline Dashboard

Connect Power BI to Snowflake and build a live B2B sales analytics dashboard.

Introduction

30 Second Summary

Every B2B sales team lives and dies by its pipeline. Knowing which deals will close, which reps are crushing it, and where revenue is leaking determines whether a company hits its quarterly targets.

In this project, you will build an interactive sales pipeline dashboard by connecting Power BI Desktop to a Snowflake cloud data warehouse loaded with real B2B sales data. You will load a multi-table dataset, write analytical SQL queries, model relationships, and create a dashboard a VP of Sales could actually use.

What You'll Build

An interactive Power BI dashboard connected live to Snowflake, analyzing deal win rates, team performance, product revenue, and pipeline health from a 4-table B2B sales dataset modeled as a star schema.

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

  • A Snowflake data warehouse with 4 tables loaded via the Snowsight web UI, proving you can work with cloud data platforms.
  • SQL queries that calculate win rates, deal velocity, and revenue by segment, turning raw data into actionable insights.
  • A multi-page Power BI dashboard with KPI cards, trend lines, slicers, drill-through, and a written business recommendation.
  • Secret Mission: DAX calculated measures for weighted pipeline value and average deal cycle time, plus a forecasting visual.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY) and familiar with the Power BI Desktop interface. You do not need prior experience with Snowflake.

Set Up Snowflake and Download the Dataset

Before you can analyze sales data in Power BI, you need somewhere to store it. A cloud data warehouse like Snowflake separates storage from compute, so you can load millions of rows and query them without worrying about local hardware limits.

In this step, you will create a Snowflake trial account, set up a database and warehouse inside it, and download the dataset you will analyze throughout this project.

In this step, get ready to:
  • Sign up for a Snowflake free trial account.
  • Create a database, schema, and compute warehouse in Snowsight.
  • Download the CRM Sales Opportunities dataset from Maven Analytics.
Sign up for a Snowflake free trial

Snowflake offers a 30-day free trial with $400 in credits. No credit card is required.

  • Open your browser and navigate to signup.snowflake.com.
  • Fill in your first name, last name, and email address.
  • For Why are you signing up?, select Personal learning and development.
  • Click Continue.
  • On the next page, select Standard for your Snowflake Edition.
  • Choose a cloud provider (AWS or Azure) and a region close to your location.
  • Click Get Started.
  • Check your email inbox for a verification link from Snowflake, then click the link to activate your account.
  • Set your username and password when prompted.

What is Snowflake?

Snowflake is a cloud data warehouse. Unlike a traditional database running on your own machine, Snowflake runs entirely in the cloud and bills you only for the compute time you actually use.

The "warehouse" in Snowflake is specifically the compute engine that runs your queries. You can pause it when you are not using it so you do not burn credits.

Standard edition is plenty

Standard edition includes everything you need for this project. Higher editions like Enterprise consume credits faster for features you will not use here.

After activation, Snowflake logs you into Snowsight, its web-based interface. You should see the Snowsight home page with a navigation menu on the left.

Create your database, schema, and warehouse

Snowflake organizes data into databases (top-level containers), schemas (folders inside a database), and tables. You also need a warehouse (compute engine) to run queries.

  • In the Snowsight navigation menu on the left, click Projects, then click Worksheets.
  • Click the + Worksheet button in the top right to create a new SQL worksheet.
  • Set up your project environment by pasting and running this SQL:
-- Create a dedicated database for this project
CREATE DATABASE sales_analytics;

-- Create a schema to hold our raw CSV data
CREATE SCHEMA sales_analytics.raw;

-- Create a compute warehouse (auto-suspends after 60s of inactivity)
CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
  • Click the Run All button (or press Ctrl+Shift+Enter) to execute all three statements.

What does this SQL do?

  • CREATE DATABASE sales_analytics creates a top-level container for all the tables in this project.
  • CREATE SCHEMA sales_analytics.raw creates a folder called raw inside that database. This is where you will load the CSV data.
  • CREATE WAREHOUSE analytics_wh spins up an extra-small compute engine. AUTO_SUSPEND = 60 means it shuts down after 60 seconds of inactivity, saving credits. AUTO_RESUME = TRUE means it restarts automatically when you run your next query.
  • Verify by checking the results panel below the worksheet. You should see a success message for each statement.
  • In the left sidebar, expand Data and confirm you see SALES_ANALYTICS listed as a database with RAW as a schema inside it.

Seeing an error?

  • If you see a permissions error, make sure you are logged in with the ACCOUNTADMIN role. Click your username in the bottom left corner of Snowsight and switch your role to ACCOUNTADMIN.
  • If you see "Object already exists", that is fine. It means the resource was already created from a previous run.

Help me troubleshoot my Snowflake SQL worksheet error.

✔️ Awesome, I've got everything!

Great. Make sure your worksheet is saved. You will come back to it in the next step.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run in your worksheet:

-- Create a dedicated database for this project
CREATE DATABASE sales_analytics;

-- Create a schema to hold our raw CSV data
CREATE SCHEMA sales_analytics.raw;

-- Create a compute warehouse (auto-suspends after 60s of inactivity)
CREATE WAREHOUSE analytics_wh WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
Download the CRM Sales Opportunities dataset

The dataset comes from Maven Analytics, a free data education platform. It contains B2B sales pipeline data from a fictitious computer hardware company, split across 4 CSV files.

  • Open mavenanalytics.io/data-playground in a new browser tab.
  • Use the search bar or scroll to find CRM Sales Opportunities.
  • Click the dataset card to open its detail page.
  • Click Download. If prompted, sign up for a free Maven Analytics account (no payment required) or log in.
  • Unzip the downloaded file. You should see 4 CSV files: accounts.csv, products.csv, sales_teams.csv, and sales_pipeline.csv.

What is in this dataset?

The dataset models a real-world B2B sales pipeline with 4 related tables:

  • accounts contains customer companies with their sector, revenue, and employee count.
  • products lists the 7 hardware products with their series and price.
  • sales_teams maps each sales agent to a manager and regional office.
  • sales_pipeline is the fact table with approximately 8,800 deal records including stage, dates, and close values.
  • Confirm all 4 files are present by opening the unzipped folder.
  • Keep this folder somewhere easy to find. You will upload these files to Snowflake in the next step.

Can't find the dataset?

The Maven Analytics data playground page lists many datasets. Search for "CRM Sales Opportunities" using the search bar at the top of the page. The dataset card shows "Multiple tables, 8,800 records, 18 fields."

Help me find and download the CRM Sales Opportunities dataset from Maven Analytics.

Your cloud data warehouse is ready and your dataset is downloaded. Next up, you will load those 4 CSV files into Snowflake and verify the data landed correctly.

Load Data into Snowflake

Your Snowflake database and warehouse are ready, but the tables inside are empty. Before you can analyze the sales pipeline, you need to define the table structures and fill them with the Maven CRM dataset.

In this step, you will create four tables that match the CSV columns, upload each file through the Snowsight UI, and verify the data landed correctly.

In this step, get ready to:
  • Create 4 target tables in the sales_analytics.raw schema.
  • Upload all 4 CSV files using the Snowsight data loading wizard.
  • Verify row counts and spot-check the loaded data.
Create the target tables

Before uploading CSV files, Snowflake needs empty tables with the correct column definitions to receive the data. Each table maps to one of the four CSV files you downloaded.

  • Switch back to your SQL worksheet in Snowsight.
  • Create the accounts table by running this SQL:
-- Create the accounts dimension table
CREATE TABLE sales_analytics.raw.accounts (
    account VARCHAR,
    sector VARCHAR,
    year_established INTEGER,
    revenue FLOAT,
    employees INTEGER,
    office_location VARCHAR,
    subsidiary_of VARCHAR
);

What does this SQL do?

This creates a table called accounts in the sales_analytics.raw schema. Each column matches a field in the accounts.csv file. VARCHAR handles text, INTEGER handles whole numbers, and FLOAT handles decimals like revenue figures.

  • You should see a "Table ACCOUNTS successfully created" message in the results pane.
  • Create the products table by running this SQL:
-- Create the products dimension table
CREATE TABLE sales_analytics.raw.products (
    product VARCHAR,
    series VARCHAR,
    sales_price INTEGER
);
  • Create the sales_teams table by running this SQL:
-- Create the sales teams dimension table
CREATE TABLE sales_analytics.raw.sales_teams (
    sales_agent VARCHAR,
    manager VARCHAR,
    regional_office VARCHAR
);
  • Create the sales_pipeline fact table by running this SQL:
-- Create the sales pipeline fact table (the main transactional data)
CREATE TABLE sales_analytics.raw.sales_pipeline (
    opportunity_id VARCHAR,
    sales_agent VARCHAR,
    product VARCHAR,
    account VARCHAR,
    deal_stage VARCHAR,
    engage_date DATE,
    close_date DATE,
    close_value INTEGER
);

Why is sales_pipeline the most important table?

In a star schema, the fact table holds the transactional records. The sales_pipeline table is your fact table because it stores individual deal events.

The other three tables (accounts, products, sales_teams) are dimension tables. They describe the "who" and "what" around each deal. Later, you will connect these tables together in Power BI to form the star schema.

  • Confirm all four tables were created successfully. You should see a success message after each statement.

Seeing an error when creating tables?

  • Make sure your worksheet context is set to the sales_analytics database and analytics_wh warehouse. Check the dropdowns at the top of your worksheet.
  • If you see "Object already exists", you may have run the statement twice. That is fine. The table is already there.
  • Help me debug my CREATE TABLE error in Snowflake.
Upload CSV files using the Snowsight UI

Now that the empty tables exist, you will upload each CSV file through the Snowsight data loading wizard. You will repeat this process for all four files.

  • In the left navigation menu, select Ingestion.
  • Select Add Data.
  • Select Load data into a Table. The Load Data into Table dialog appears.
  • Browse to accounts.csv on your computer and select it.
  • Set the database to SALES_ANALYTICS, the schema to RAW, and the table to ACCOUNTS.
  • Select Next.
  • On the file format settings, set the file type to CSV and set Header lines to skip to 1 (this tells Snowflake the first row is column names, not data).
  • Select Load.

Snowsight displays the number of rows successfully inserted into the table.

What just happened?

Behind the scenes, Snowsight uploaded your CSV to an internal stage (a temporary holding area in Snowflake's cloud storage), then ran a COPY INTO command to move the data from the stage into your table. The UI automates what would otherwise be a multi-step SQL process.

  • Repeat the same process for the remaining three files. For each one:
  • Go back to Ingestion > Add Data > Load data into a Table.
  • Upload products.csv into the PRODUCTS table.
  • Upload sales_teams.csv into the SALES_TEAMS table.
  • Upload sales_pipeline.csv into the SALES_PIPELINE table.
  • For each file, make sure you set the file type to CSV and Header lines to skip to 1 before clicking Load.

Load failing or showing 0 rows?

  • Double-check that you selected the correct table for each CSV. The table name should match the file name (e.g., products.csv goes into the PRODUCTS table).
  • Make sure Header lines to skip is set to 1. If set to 0, Snowflake tries to insert the header row as data and may fail on type mismatches.
  • Confirm your warehouse analytics_wh is selected in the wizard. Without a warehouse, the load cannot execute.
  • Help me troubleshoot my Snowsight data load.
Verify the data load

Loading data without verification is risky. A quick count and spot-check confirms everything arrived intact.

  • Return to your SQL worksheet and run the following row count queries:
-- Verify row counts for all four tables
SELECT COUNT(*) FROM sales_analytics.raw.accounts;
SELECT COUNT(*) FROM sales_analytics.raw.products;
SELECT COUNT(*) FROM sales_analytics.raw.sales_teams;
SELECT COUNT(*) FROM sales_analytics.raw.sales_pipeline;

What should I see?

Each query returns a single number showing how many rows the table contains. The sales_pipeline table should have approximately 8,800 rows. The other tables will be smaller (dimension tables typically have fewer records than the fact table).

  • Spot-check a few rows from the largest table by running:
-- Spot-check the fact table to confirm data looks correct
SELECT * FROM sales_analytics.raw.sales_pipeline LIMIT 5;
  • Confirm that you see 5 rows of data with values in all columns (opportunity_id, sales_agent, product, account, deal_stage, engage_date, close_date, close_value).

Why spot-check?

Row counts tell you the data arrived, but a spot-check tells you it arrived correctly. You are looking for real values in each column rather than NULLs or garbled text. If dates look like dates and names look like names, your schema matched the CSV structure.

Seeing NULL values or missing data?

  • If most columns show NULL, you may have skipped the header-skip setting during upload. Drop the table, recreate it, and reload the CSV with Header lines to skip set to 1.
  • If dates appear as NULL but other columns are fine, the date format in the CSV may not match Snowflake's default. Re-upload and check that the file format options include the correct date format.
  • Help me fix NULL values in my Snowflake table after loading a CSV.

✔️ Awesome, I've got everything!

Great. Make sure all four tables show non-zero row counts and your spot-check returned real data.

ⓧ I'd like to double check the full code

Here are all the SQL statements you ran in this step. You can copy and re-run them in your worksheet to confirm everything is in place.

-- Create the accounts dimension table
CREATE TABLE sales_analytics.raw.accounts (
    account VARCHAR,
    sector VARCHAR,
    year_established INTEGER,
    revenue FLOAT,
    employees INTEGER,
    office_location VARCHAR,
    subsidiary_of VARCHAR
);

-- Create the products dimension table
CREATE TABLE sales_analytics.raw.products (
    product VARCHAR,
    series VARCHAR,
    sales_price INTEGER
);

-- Create the sales teams dimension table
CREATE TABLE sales_analytics.raw.sales_teams (
    sales_agent VARCHAR,
    manager VARCHAR,
    regional_office VARCHAR
);

-- Create the sales pipeline fact table (the main transactional data)
CREATE TABLE sales_analytics.raw.sales_pipeline (
    opportunity_id VARCHAR,
    sales_agent VARCHAR,
    product VARCHAR,
    account VARCHAR,
    deal_stage VARCHAR,
    engage_date DATE,
    close_date DATE,
    close_value INTEGER
);

-- Verify row counts for all four tables
SELECT COUNT(*) FROM sales_analytics.raw.accounts;
SELECT COUNT(*) FROM sales_analytics.raw.products;
SELECT COUNT(*) FROM sales_analytics.raw.sales_teams;
SELECT COUNT(*) FROM sales_analytics.raw.sales_pipeline;

-- Spot-check the fact table to confirm data looks correct
SELECT * FROM sales_analytics.raw.sales_pipeline LIMIT 5;

Your data warehouse is loaded and verified. Next up, you will write SQL queries to analyze win rates, deal velocity, and team performance across this pipeline data.

Analyze the Pipeline with SQL

Your data warehouse is loaded and ready to query. Before connecting Power BI, you need to understand what stories the data tells.

In this step, you will write SQL queries directly in Snowsight to uncover win rates, deal velocity, and top performers. These insights will guide every visual you build in the dashboard later.

In this step, get ready to:
  • Calculate overall and segmented win rates using SQL.
  • Measure average deal cycle time by product and quarter.
  • Rank managers by total revenue to identify top performers.
Calculate the overall win rate and break it down by segment

Win rate is the percentage of closed deals that were won. A deal is "closed" when its deal_stage is either Won or Lost. Knowing win rate overall and by segment reveals where the sales team converts best.

  • In your Snowsight SQL worksheet, paste the following query to calculate the overall win rate:
-- Overall win rate across all closed deals
SELECT 
    COUNT(CASE WHEN deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline
WHERE deal_stage IN ('Won', 'Lost');

What does this query do?

The CASE WHEN expression counts only rows where the deal was won. Dividing that by the total count of closed deals (Won + Lost) gives the win rate as a percentage.

The WHERE clause filters out deals still in progress (like Prospecting or Negotiation) so only truly closed outcomes are measured.

  • Click the blue Run button (or press Ctrl+Enter) to execute the query.
  • You should see a single row with the overall win rate percentage.

Now break the win rate down by product. This JOIN connects each deal to the products table so you can group by product name.

  • Add this query below your first one in the same worksheet:
-- Win rate broken down by product
SELECT 
    sp.product,
    COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.products p ON sp.product = p.product
WHERE sp.deal_stage IN ('Won', 'Lost')
GROUP BY sp.product
ORDER BY win_rate_pct DESC;

What does the JOIN do here?

The JOIN links sales_pipeline to the products dimension table on the shared product column. This is a star schema pattern: the fact table (sales_pipeline) connects outward to dimension tables for richer grouping.

  • Run the query. You should see one row per product, sorted from highest to lowest win rate.

Finally, break it down by regional office. This requires joining to the sales_teams table.

  • Add this query below the previous ones:
-- Win rate broken down by regional office
SELECT 
    st.regional_office,
    COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.sales_teams st ON sp.sales_agent = st.sales_agent
WHERE sp.deal_stage IN ('Won', 'Lost')
GROUP BY st.regional_office
ORDER BY win_rate_pct DESC;
  • Run the query. Note which regional office has the highest win rate.

Getting zero rows or an error?

Make sure your WHERE clause uses 'Won' and 'Lost' with exact capitalization. The values in deal_stage are case-sensitive strings.

If you see a "does not exist" error, confirm the fully qualified table name includes the database and schema: sales_analytics.raw.sales_pipeline.

Help me debug my win rate query in Snowflake

Calculate average deal cycle time

Deal cycle time measures how many days it takes to close a deal from first engagement. Shorter cycles mean faster revenue. Grouping by product and quarter shows whether certain products sell faster and whether velocity is improving over time.

  • Add this query to your worksheet to calculate average cycle time for won deals:
-- Average deal cycle time in days, grouped by product and quarter
SELECT 
    sp.product,
    EXTRACT(YEAR FROM sp.close_date) AS close_year,
    EXTRACT(QUARTER FROM sp.close_date) AS close_quarter,
    ROUND(AVG(DATEDIFF(day, sp.engage_date, sp.close_date)), 1) AS avg_cycle_days
FROM sales_analytics.raw.sales_pipeline sp
WHERE sp.deal_stage = 'Won'
    AND sp.close_date IS NOT NULL
    AND sp.engage_date IS NOT NULL
GROUP BY sp.product, close_year, close_quarter
ORDER BY sp.product, close_year, close_quarter;

What does this query do?

The DATEDIFF(day, engage_date, close_date) function returns the number of days between when a deal was first engaged and when it closed. Wrapping it in AVG() gives the average across all won deals in each group.

The EXTRACT functions pull the year and quarter from close_date so you can see how cycle time trends over time.

  • Run the query. You should see rows grouped by product and quarter, each showing an average number of days.
  • Look for the product with the shortest average cycle time. This is the fastest-selling product in the pipeline.

Seeing NULL values in the results?

Some deals may have NULL dates if they were never fully closed. The WHERE clause filters these out with IS NOT NULL checks. If you still see NULLs, make sure both conditions are present.

Help me fix NULL values in my cycle time query

Write a revenue summary with manager rankings

A ranked revenue summary lets leadership see which managers drive the most closed revenue. The RANK() window function assigns a position based on total revenue without collapsing the result set.

  • Add this final query to your worksheet:
-- Total revenue by manager with ranking
SELECT 
    st.manager,
    SUM(sp.close_value) AS total_revenue,
    COUNT(*) AS deals_won,
    RANK() OVER (ORDER BY SUM(sp.close_value) DESC) AS revenue_rank
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.sales_teams st ON sp.sales_agent = st.sales_agent
WHERE sp.deal_stage = 'Won'
GROUP BY st.manager
ORDER BY revenue_rank;

What does RANK() do?

The RANK() OVER (ORDER BY SUM(sp.close_value) DESC) expression assigns rank 1 to the manager with the highest total revenue, rank 2 to the next, and so on. If two managers tie, they share the same rank.

This is a window function. It calculates across the entire result set without reducing it to a single row like a regular aggregate would.

  • Run the query. You should see each manager listed with their total revenue, number of deals won, and their rank.
  • Note the rank-1 manager. This is your top performer by revenue.

Query running but warehouse is suspended?

Your analytics_wh warehouse has AUTO_RESUME = TRUE, so it should start automatically when you run a query. If it does not resume, click the warehouse selector in the top-right of the worksheet and confirm analytics_wh is selected.

Help me resume my Snowflake warehouse

✔️ Awesome, I've got everything!

Great. Make sure all three queries are saved in your worksheet. You will reference these insights when building visuals in Power BI.

ⓧ I'd like to double check the full code

Here are all three queries together. Confirm your worksheet matches:

-- Overall win rate across all closed deals
SELECT 
    COUNT(CASE WHEN deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline
WHERE deal_stage IN ('Won', 'Lost');

-- Win rate broken down by product
SELECT 
    sp.product,
    COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.products p ON sp.product = p.product
WHERE sp.deal_stage IN ('Won', 'Lost')
GROUP BY sp.product
ORDER BY win_rate_pct DESC;

-- Win rate broken down by regional office
SELECT 
    st.regional_office,
    COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) AS won_deals,
    COUNT(*) AS total_closed_deals,
    ROUND(COUNT(CASE WHEN sp.deal_stage = 'Won' THEN 1 END) * 100.0 / COUNT(*), 2) AS win_rate_pct
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.sales_teams st ON sp.sales_agent = st.sales_agent
WHERE sp.deal_stage IN ('Won', 'Lost')
GROUP BY st.regional_office
ORDER BY win_rate_pct DESC;

-- Average deal cycle time in days, grouped by product and quarter
SELECT 
    sp.product,
    EXTRACT(YEAR FROM sp.close_date) AS close_year,
    EXTRACT(QUARTER FROM sp.close_date) AS close_quarter,
    ROUND(AVG(DATEDIFF(day, sp.engage_date, sp.close_date)), 1) AS avg_cycle_days
FROM sales_analytics.raw.sales_pipeline sp
WHERE sp.deal_stage = 'Won'
    AND sp.close_date IS NOT NULL
    AND sp.engage_date IS NOT NULL
GROUP BY sp.product, close_year, close_quarter
ORDER BY sp.product, close_year, close_quarter;

-- Total revenue by manager with ranking
SELECT 
    st.manager,
    SUM(sp.close_value) AS total_revenue,
    COUNT(*) AS deals_won,
    RANK() OVER (ORDER BY SUM(sp.close_value) DESC) AS revenue_rank
FROM sales_analytics.raw.sales_pipeline sp
JOIN sales_analytics.raw.sales_teams st ON sp.sales_agent = st.sales_agent
WHERE sp.deal_stage = 'Won'
GROUP BY st.manager
ORDER BY revenue_rank;

You now have a clear picture of the pipeline: which products win most, which regions close fastest, and who drives the most revenue. Next up, you will connect Power BI directly to Snowflake and turn these numbers into an interactive dashboard.

Connect Power BI to Snowflake and Build the Data Model

You have raw data in Snowflake and SQL queries that surface key insights. Now it is time to bring that data into Power BI Desktop so you can visualize it interactively.

In this step, you will connect Power BI directly to your Snowflake warehouse, load all four tables, and arrange them into a star schema. A star schema places your fact table (sales_pipeline) at the center and connects it to dimension tables (accounts, products, sales_teams). This structure makes Power BI's visuals faster and your DAX calculations simpler.

In this step, get ready to:
  • Connect Power BI Desktop to your Snowflake warehouse.
  • Build a star schema with proper table relationships.
  • Create a Date table for time intelligence.
Connect Power BI Desktop to Snowflake

Power BI has a native Snowflake connector that lets you pull data directly from your cloud warehouse without exporting CSVs. You need your Snowflake account URL and your login credentials.

Find your Snowflake account URL
  • Switch back to Snowsight in your browser.
  • Click your account name in the bottom-left corner to open the user menu.
  • Select Connect a tool to Snowflake.
  • In the Account Details dialog that appears, copy the value in the Account/Server URL field.
  • Keep this value handy. It looks something like orgname-account_name.snowflakecomputing.com.

What is this account URL?

The account URL uniquely identifies your Snowflake instance on the internet. Power BI uses it to know which Snowflake server to connect to. Every Snowflake account has a URL in this format.

Open the Snowflake connector in Power BI
  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • In Power BI Desktop, click Get Data in the Home ribbon.
  • In the Get Data window, select Database from the left panel.
  • Select Snowflake from the list, then click Connect.
Enter your Snowflake connection details
  • In the Server field, paste your Snowflake account URL that you copied from Snowsight (e.g. orgname-account_name.snowflakecomputing.com).
  • In the Warehouse field, enter analytics_wh.
  • Leave the other optional fields blank and click OK.
  • When prompted for credentials, select Database on the left side of the authentication dialog.
  • Enter your Snowflake username and password (the same ones you use to log into Snowsight).
  • Click Connect.

Why Database authentication?

Power BI supports several authentication methods for Snowflake, including Microsoft Entra ID for single sign-on. For a trial account, username and password (Database authentication) is the simplest option that works out of the box.

Select and load all four tables
  • In the Navigator window, expand SALES_ANALYTICS, then expand RAW.
  • Check the boxes next to all four tables: ACCOUNTS, PRODUCTS, SALES_PIPELINE, and SALES_TEAMS.
  • Click Load.

Power BI imports the data from Snowflake. You should see all four tables appear in the Fields pane on the right side of the screen.

Tables not appearing in the Navigator?

Make sure you entered analytics_wh as the warehouse name. If the warehouse is suspended, Power BI may time out. Switch back to Snowsight and run any query to wake the warehouse (it auto-resumes), then retry the connection in Power BI.

Double-check that your Server URL matches the value from the Account/Server URL field in Snowsight exactly.

Help me troubleshoot my Power BI to Snowflake connection.

Build the star schema in Model view

A star schema organizes your data model so that the fact table (the one with transactional rows) sits at the center, connected outward to dimension tables. This design makes filters flow correctly through your visuals.

  • Click the Model view icon in the left sidebar of Power BI Desktop (it looks like three connected boxes).
  • You should see your four tables displayed as boxes. Power BI may have auto-detected some relationships. If so, delete any auto-created relationships by right-clicking the line between tables and selecting Delete. You will create them correctly from scratch.
Create the three dimension relationships
  • Drag the account field from the SALES_PIPELINE table onto the account field in the ACCOUNTS table.
  • In the relationship dialog that appears, confirm the cardinality is set to Many to one (*:1) and the cross filter direction is Single.
  • Click OK.
  • Drag the product field from SALES_PIPELINE onto the product field in PRODUCTS. Confirm Many to one and Single direction, then click OK.
  • Drag the sales_agent field from SALES_PIPELINE onto the sales_agent field in SALES_TEAMS. Confirm Many to one and Single direction, then click OK.

Your Model view should now show SALES_PIPELINE at the center with lines connecting outward to the three dimension tables.

Why many-to-one with single direction?

Each deal in sales_pipeline references one account, one product, and one sales agent. That is the many-to-one relationship.

Single direction means filters flow from the dimension table into the fact table, not the reverse. This is the standard star schema pattern that keeps your model predictable and ensures slicers on dimension columns (like region or product series) correctly filter the fact rows.

Seeing a many-to-many warning?

If Power BI warns about many-to-many cardinality, it means the column you dragged has duplicate values on both sides. Make sure you are dragging FROM SALES_PIPELINE (many side) TO the dimension table (one side). The dimension table's key column should contain unique values.

Help me fix a many-to-many relationship warning in Power BI.

Create a Date table for time intelligence

Power BI's time intelligence functions (year-over-year comparisons, running totals, monthly trends) require a dedicated Date table. You will create one using DAX that automatically covers the date range in your data.

  • Switch to Table view by clicking the grid icon in the left sidebar.
  • In the Home ribbon, click New table in the Calculations group.
  • In the formula bar that appears at the top, paste the following DAX formula:
DateTable =
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q")
)
  • Press Enter to create the table.

What does this DAX formula do?

CALENDARAUTO() scans your data model and generates a contiguous list of dates covering the full range of date values it finds. It automatically picks up the earliest and latest dates from columns like engage_date and close_date.

ADDCOLUMNS extends each row with calculated fields: the year number, month number, month name, and quarter label. These columns give you slicing and grouping options in your visuals.

Mark it as the official Date table
  • In the Fields pane on the right, right-click DateTable.
  • Select Mark as date table, then click Mark as date table again in the submenu.
  • In the dialog that appears, select Date from the dropdown as the date column, then click OK.

Why mark it as a date table?

Marking the table tells Power BI to use it for all time intelligence calculations instead of the auto-generated hidden date hierarchies. This gives you full control over how dates are grouped and filtered in your visuals.

Connect the Date table to sales_pipeline
  • Switch back to Model view using the icon in the left sidebar.
  • Drag the Date field from DateTable onto the close_date field in SALES_PIPELINE.
  • Confirm the cardinality is One to many (1:*) (from DateTable to SALES_PIPELINE) and direction is Single.
  • Click OK.

Your Model view should now show SALES_PIPELINE at the center connected to four tables: ACCOUNTS, PRODUCTS, SALES_TEAMS, and DateTable. This is your completed star schema.

Why connect on close_date?

Connecting the Date table to close_date means your time-based visuals (monthly trends, quarterly summaries) will slice deals by when they closed. This is the most common axis for revenue and win-rate analysis.

Relationship not creating?

If Power BI rejects the relationship, check that the close_date column in SALES_PIPELINE has a Date or DateTime data type. Select the column in Table view, then in the Column tools ribbon, change Data type to Date.

Help me connect my Date table to the fact table.

Your data model is solid. Next up, you will build the interactive dashboard with KPI cards, trend charts, and drill-through pages that bring this star schema to life.

Build the Interactive Dashboard

Your star schema is wired up and ready to power real visuals. The hard work of modeling pays off now because every chart you build can slice cleanly across products, regions, and managers without messy joins.

In this step, you'll build a multi-page dashboard that a VP of Sales could use to track quarterly performance. You'll create KPI cards, trend charts, a cross-tabulation matrix, and drill-through navigation that lets users click into deal-level detail.

In this step, get ready to:
  • Create a KPI page with revenue, win rate, pipeline, and deal size cards plus slicers.
  • Build a Performance page with bar charts, trend lines, and a conditional-formatted matrix.
  • Add drill-through interactivity and a written business recommendation.
Create the KPI page with card visuals and slicers

KPI cards give stakeholders an instant pulse-check on the business. You'll create four DAX measures that power these cards, then add slicers so users can filter everything by region or time period.

  • In Power BI Desktop, double-click the page tab labelled Page 1 at the bottom of the canvas.
  • Type KPI and press Enter to rename it.
  • Create your first DAX measure by right-clicking the sales_pipeline table in the Data pane and selecting New Measure.
  • Paste the following formula into the formula bar and press Enter:
Total Revenue = 
    CALCULATE(
        SUM(sales_pipeline[close_value]),
        sales_pipeline[deal_stage] = "Won"
    )
  • Create a second measure on the same table using New Measure:
Win Rate = 
    DIVIDE(
        CALCULATE(
            COUNTROWS(sales_pipeline),
            sales_pipeline[deal_stage] = "Won"
        ),
        CALCULATE(
            COUNTROWS(sales_pipeline),
            sales_pipeline[deal_stage] IN {"Won", "Lost"}
        )
    )
  • Create a third measure:
Total Deals in Pipeline = 
    CALCULATE(
        COUNTROWS(sales_pipeline),
        NOT(sales_pipeline[deal_stage] IN {"Won", "Lost"})
    )
  • Create a fourth measure:
Average Deal Size = 
    CALCULATE(
        AVERAGE(sales_pipeline[close_value]),
        sales_pipeline[deal_stage] = "Won"
    )

What do these measures do?

Total Revenue sums the close_value column but only for deals that were Won. Win Rate divides Won deals by total closed deals (Won + Lost), giving you a percentage.

Total Deals in Pipeline counts all opportunities that haven't closed yet. Average Deal Size shows the mean value of Won deals. Together these four numbers tell a complete pipeline story.

  • In the Visualizations pane, click the Card visual icon (it looks like a single number with a label).
  • Drag Total Revenue from the Data pane into the card.
  • Repeat for the other three measures: add a new Card visual for each of Win Rate, Total Deals in Pipeline, and Average Deal Size.
  • Arrange all four cards across the top of the page.

Now add slicers so users can filter the entire page by region and time.

  • Click the Slicer visual icon in the Visualizations pane.
  • Drag regional_office from the sales_teams table into the slicer.
  • Add a second Slicer visual and drag a date hierarchy field (quarter or year) from your Date table into it.
  • Position the slicers near the top or side of the page where they are easy to reach.

Click a value in the regional_office slicer. All four cards should update instantly to reflect only that region's numbers.

Cards showing blank or zero?

Make sure your measure references sales_pipeline[deal_stage] exactly as the column is named. DAX is case-insensitive for column names but the table reference must match what appears in the Data pane.

If the slicer doesn't filter the cards, verify that the relationship between sales_teams and sales_pipeline is active in the Model view. The filter should flow from the dimension to the fact table.

Still stuck? Help me debug why my DAX card measure is showing blank in Power BI.

Build the Performance page with charts and a matrix

The Performance page shows trends and comparisons. A bar chart highlights which managers drive the most revenue. A line chart reveals monthly patterns. A matrix cross-tabulates win rate by product and region so you can spot opportunities at a glance.

  • Click the + icon at the bottom of the canvas to create a new page.
  • Double-click the new page tab, type Performance, and press Enter.

Start with a revenue-by-manager bar chart.

  • In the Visualizations pane, select the Clustered bar chart icon.
  • Drag manager from sales_teams into the Y-axis well.
  • Drag the Total Revenue measure into the X-axis well.
  • Resize the visual to fill roughly half the page width.

Next, add a monthly trend line.

  • Select the Line chart icon in the Visualizations pane.
  • Drag a month-level date field from your Date table into the X-axis.
  • Drag opportunity_id from sales_pipeline into the Y-axis (it will default to Count).
  • This shows the count of deals closed each month, revealing seasonal patterns.

Finally, add the matrix visual for a win rate cross-tabulation.

  • Select the Matrix icon in the Visualizations pane.
  • Drag product from the products table into Rows.
  • Drag regional_office from sales_teams into Columns.
  • Drag the Win Rate measure into Values.

Apply conditional formatting to make high and low win rates visually obvious.

  • With the matrix selected, find Win Rate in the Values well of the Visualizations pane.
  • Click the down-arrow next to Win Rate and select Conditional formatting, then Background color.
  • In the dialog, set Format style to Rules.
  • Add a rule that colors cells green when the value is above 0.5, yellow between 0.3 and 0.5, and red below 0.3.
  • Click OK to apply.

Why use a matrix with conditional formatting?

A matrix is the Power BI equivalent of a pivot table. By placing products on rows and regions on columns, you create a heat map of win rates. Conditional formatting turns raw numbers into a colour-coded signal that a stakeholder can scan in seconds.

Your Performance page should now show the bar chart, line chart, and colour-coded matrix side by side.

Matrix showing blank cells?

Blank cells mean there are no closed deals for that product-region combination. This is expected for some intersections. If the entire matrix is blank, check that your Win Rate measure references the correct table and column names.

Need help? Why is my Power BI matrix showing all blanks for my Win Rate measure?

Add drill-through and a written recommendation

Drill-through lets users right-click a data point on the Performance page and jump to a detail page filtered to that specific manager or product. This turns your dashboard from a static report into an interactive exploration tool.

  • Click the + icon at the bottom of the canvas to create a third page.
  • Double-click the tab, type Deal Detail, and press Enter.

Configure this page as a drill-through destination.

  • With the Deal Detail page active, look at the Visualizations pane and expand the Drill through filters well.
  • From the Data pane, drag manager from sales_teams into the Drill through well.
  • Also drag product from products into the same Drill through well.

What does the Drill through well do?

Any field you add to the Drill through well becomes a filter key. When a user right-clicks a bar on the Performance page (for a specific manager), Power BI navigates to the Deal Detail page and filters everything on that page to show only that manager's deals. A Back button is automatically added so users can return.

  • Add a Table visual to the Deal Detail page.
  • Drag these fields into the table: opportunity_id, account, product, deal_stage, engage_date, close_date, and close_value.
  • This gives the drill-through user a row-level view of every deal for the selected manager or product.

Test the drill-through by navigating back to the Performance page.

  • On the Performance page, right-click any bar in the revenue-by-manager chart.
  • Select Drill through, then click Deal Detail.
  • The Deal Detail page opens, filtered to that manager's opportunities.
  • Click the Back arrow button (top-left of the page) to return to Performance.

Now add a text box with your one-paragraph business recommendation.

  • Navigate to the KPI page (or whichever page you want the recommendation to appear on).
  • In the ribbon, click Insert, then select Text box.
  • Write a one-paragraph recommendation based on what you observed in the data. For example: "GTX Plus Pro has the highest win rate but accounts for a small share of the pipeline. The sales team should prioritize qualifying more GTX Plus Pro opportunities to increase overall conversion."
  • Position the text box at the bottom of the page where it acts as a summary.

Why include a written recommendation?

Dashboards show the "what". A written recommendation shows the "so what". This is the skill that separates analysts from dashboard builders. It proves you can translate data into a specific, actionable business decision.

  • Save your Power BI file by pressing Ctrl+S.
  • Click through each page tab (KPI, Performance, Deal Detail) and test the slicers and drill-through to confirm everything responds correctly.

Your dashboard should now have 3 pages. The KPI page updates when you select a region. The Performance page shows manager rankings and product-region win rates. The Deal Detail page shows individual opportunities when you drill through.

Drill through option not appearing?

The Drill through option only appears in the right-click menu if the field you right-clicked exists in the Drill through well on the target page. Make sure manager or product is in the Drill through well on the Deal Detail page, and that the source visual contains that same field.

Still not working? Help me figure out why drill through isn't appearing in my Power BI right-click menu.

Secret mission

DAX Measures and Forecasting

Your dashboard already tells the story of what happened. But what if you could predict what's coming next? In this mission, you'll create advanced DAX measures for weighted pipeline value and average deal cycle time, then add a forecasting visual that projects next quarter's revenue trend.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Your Snowflake warehouse is set to auto-suspend after 60 seconds of inactivity, so it will not consume credits while idle. Your $400 in trial credits will easily last the full 30-day trial with this workload.

Cost warning

Snowflake credits are only consumed while the analytics_wh warehouse is actively running queries. With AUTO_SUSPEND = 60, the warehouse shuts down after one minute of inactivity. Your trial expires automatically after 30 days with no charge.

Resources you used:

  • Snowflake sales_analytics database (includes the raw schema and all 4 tables).
  • Snowflake analytics_wh warehouse (XSMALL, auto-suspend enabled).
  • Downloaded CSV files from Maven Analytics (4 files on your computer).
  • Power BI .pbix dashboard file (saved locally).

Keep everything running

No action needed. Choose this if you are still actively building or exploring the dashboard.

  • The analytics_wh warehouse auto-suspends after 60 seconds of inactivity and will not consume credits while idle.
  • Your $400 in free trial credits will last the full 30-day trial period with this workload.
  • The trial account is automatically suspended by Snowflake after 30 days.

Pause - I'll come back to this later

Manually suspend the warehouse to guarantee zero credit consumption until you return.

  • Open a SQL worksheet in Snowsight and run this command to suspend the warehouse:
ALTER WAREHOUSE analytics_wh SUSPEND;
  • Your database, schema, and tables remain intact and cost nothing while stored.
  • When you return, the warehouse will automatically resume when you run your next query (thanks to AUTO_RESUME = TRUE).
  • Keep your Power BI .pbix file and CSV files on your computer for when you come back.

Delete - I don't want to use this again

Remove all project resources from Snowflake and your local machine.

  • Open a SQL worksheet in Snowsight and run these commands to drop the database and warehouse:
DROP DATABASE sales_analytics;
DROP WAREHOUSE analytics_wh;

What does this do?

DROP DATABASE removes the sales_analytics database along with the raw schema and all 4 tables inside it. DROP WAREHOUSE removes the compute resource entirely.

  • Delete the 4 CSV files (accounts.csv, products.csv, sales_teams.csv, sales_pipeline.csv) from your Downloads folder.
  • Delete the Power BI .pbix file from wherever you saved it on your computer.
  • Your Snowflake trial account will be automatically suspended after 30 days if you take no further action.

Nice Work!

Nice Work!

You just built a production-grade sales pipeline analytics dashboard powered by a cloud data warehouse. A VP of Sales could use what you created to track quarterly performance, compare team results, and make data-driven decisions.

Here's what you learned:

  • Built a cloud data warehouse in Snowflake and loaded a multi-table B2B sales dataset using the Snowsight web UI.
  • Wrote SQL aggregation queries to analyze win rates, deal velocity, and team performance across products and regions.
  • Connected Power BI Desktop directly to Snowflake, modeled data in a star schema, and built a multi-page interactive dashboard with KPIs, drill-through, and a written business recommendation.
  • Secret Mission: Created DAX calculated measures for weighted pipeline value and average deal cycle time, then added a forecast visual projecting next quarter's revenue.

Ready to quiz yourself?