How to Build a Data Dashboard (Even if You Just Started Learning SQL)

Dive into building your own data dashboard with this beginner-friendly guide—even if you're just starting with SQL. Using BigQuery, public datasets, and Looker Studio, you'll learn how to easily create an impressive dashboard.

If you're a data analyst—or aiming to become one—learning SQL is essential. SQL, or Structured Query Language, is the language used to pull and analyze data from databases, helping you turn raw numbers into real insights. But if you’re just starting out, coming up with project ideas can be the hardest part. That’s where this guide comes in. You’ll build a data dashboard using Google’s free BigQuery and Looker Studio tools that lets you analyze real crime data from the US city of Chicago.

Whether you're new to SQL or looking to sharpen your skills, the SQL for Data Analysis track on LearnSQL.com is the perfect place to begin. It’s a complete learning path designed for future analysts, covering everything from basic queries to more advanced topics like creating complex reports and using window functions. Each of the four courses included in the track is interactive and practical, helping you learn by doing with real-world examples that prepare you for actual data work. By the end of the track, you’ll have the confidence to write your own queries, analyze real datasets, and build projects like the dashboard we’ll create in this tutorial.

Tools We’ll Use to Create the Dashboard

We will use two tools to create our dashboard: BigQuery and Looker Studio.

BigQuery

BigQuery is a data warehouse offered by Google. A data warehouse is a type of database designed to handle large amounts of data and answer analytical questions.

BigQuery is especially helpful for beginners because you don’t need to install anything; just open your browser and start writing SQL. You can use BigQuery for free while you're learning.

Google offers a free tier with a generous monthly limit. If you’re only running simple queries and exploring data, you’re very unlikely to exceed it. BigQuery also gives you access to free public datasets, including real data on topics like crime, health, and transportation.

Looker Studio

Looker Studio is a Business Intelligence platform offered by Google. It lets you turn data into interactive, shareable dashboards and easy-to-read reports. It’s a great tool for beginners because it’s visual, intuitive, and works in your browser. You can build professional-looking reports with drag-and-drop charts and filters. And you can update your work automatically as your data changes.

Example dashboard in Looker Studio

Looker Studio is free to use (with some limits). It works with a wide range of data sources, including Google Sheets, Google Analytics, and (more importantly for us) BigQuery.

In this article, we’ll connect Looker Studio to BigQuery to build a dashboard using one of BigQuery’s publicly available datasets.

The Chicago Crimes Dataset

For this tutorial, we’ll use the Chicago Crimes dataset that’s publicly available through Google BigQuery’s free public datasets program. It’s published by the City of Chicago and based on information collected by the Chicago Police Department. It’s updated weekly with data from the previous week.

The dataset is ideal for practicing SQL and building your first data dashboard. It includes various crime types you can group and filter, as well as date and time fields to try out time-based queries. Since it’s updated regularly, you’ll be working with fresh, real-world data.

This dataset contains thousands of rows, each representing a reported crime in the city of Chicago. There’s only one table in the dataset, named bigquery-public-data.chicago_crime.crime. Some of the most important columns in the table are:

• date – The date and time the crime occurred (stored as a DATETIME).
• block – The approximate address where the incident happened.
• primary_type – The high-level crime category (e.g. THEFT, BATTERY, ROBBERY).
• description – More specific description of the crime (e.g. SIMPLE, STRONG ARM – NO WEAPON).
• location_description – The type of place where the crime occurred (e.g. STREET, PARKING LOT, CHA HALLWAY).
• arrest – A Boolean (TRUE/FALSE) value indicating whether someone was arrested.
• domestic – A Boolean (TRUE/FALSE) value indicating if it was a domestic-related incident.
• beat, district, ward – Numeric identifiers for police beats, districts, and city wards.
• latitude, longitude – The coordinates of the place where the crime was committed (can be used for mapping).
• year – The year the crime occurred.

Questions to Ask About the Dataset

To build a meaningful dashboard, you need to start with the right questions. You can come up with your own questions to build your dashboard. I’ll share with you the questions and queries I used to build a simple data dashboard to visualize crimes over a given time period.

The questions I used to guide my dashboard were:

• How many crimes were reported in the given period?
• How many arrests were made in the given period?
• What are the top 5 most common crime types reported?
• What are the top 5 crime types that led to arrests?

Before we start writing the queries to answer these questions, let’s explore the dataset.

Explore a Dataset in BigQuery

The first step in creating your dashboard is learning more about your dataset. 

1. Open the BigQuery console at console.cloud.google.com/bigquery.
2. In the left-hand panel, search for bigquery-public-data. Expand it to find chicago_crime, and click on the crime table to view its structure.
3. Click the SQL query button at the top.
4. In the query editor, you can start writing SQL:
   

   SELECT *
   FROM `bigquery-public-data.chicago_crime.crime`
   LIMIT 10;
   

   
   This will return the first 10 rows from the dataset.

From there, you can start exploring your data. Run a few queries to get familiar with the dataset. You can filter by date, group by crime type, or analyze arrests. These are the same techniques you would use in any SQL-based database.

Remember to always enclose the full table name in backticks:  `bigquery-public-data.dataset_name.table_name`.

Build Queries Running Your Dashboard

Now let’s write the queries for our dashboard. To keep things simple, I’ll focus on data from the year 2024, but you can easily change the filters to cover any time period you want.

These queries are just a starting point. Feel free to adjust them or use your own ideas. Dashboards are always built step by step: you start with a basic version, then improve it as you go.

Total Reported Crimes

Our first query is:

SELECT COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024;

This query counts the total number of crimes recorded in the Chicago Crime dataset for the year 2024. We use the COUNT(*) function to return the number of rows and the year column to filter for crimes committed in the year 2024.

Total Arrests

SELECT COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
  AND arrest IS TRUE;

This query is similar to the previous one, except we’re filtering for (and only counting) rows where the arrest column is set to TRUE.

Top 5 Crime Types

SELECT
  primary_type,
  COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
GROUP BY primary_type
ORDER BY COUNT(*) DESC
LIMIT 5;

This query shows the top 5 most common types of crime in 2024. It filters the data for that year, groups the records by crime type (primary_type), counts how many times each type occurred, and then sorts them from most to least frequent. Finally, it limits the result to the top 5.

Top 5 Arrest Types

SELECT
  primary_type,
  COUNT(*)
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year = 2024
AND arrest IS TRUE
GROUP BY primary_type
ORDER BY COUNT(*) DESC
LIMIT 5;

This query finds the top 5 crime types in 2024 that most often led to an arrest. It filters the data to include only crimes from 2024 where an arrest was made, groups them by primary_type, counts how many arrests happened for each type, sorts the results by count in descending order, and returns the top 5.

Connect Looker Studio to BigQuery

We’ll use Looker Studio to build our dashboard. It can connect to many data sources, but it works especially well with those in the Google ecosystem (like BigQuery).

To keep things clean and efficient, we’ll use the Custom Query option. This lets us filter, organize, and shape the data with SQL before it reaches the dashboard.

Start by opening Looker Studio and creating a new blank report. In the Add data to report panel, choose BigQuery as your data source. Then, select Custom Query. Pick your billing project. (This is the project that will be charged for BigQuery usage; if you’re using BigQuery Sandbox, you won’t be charged). Paste your SQL query into the Enter Custom Query field and click Add.

Interestingly, you can’t rename the data source at this stage; Looker Studio will name it automatically. After adding the data source to your report, you can rename it by going to Resource > Manage added data sources, where you can also update the query or rename any fields.

You can add other SQL queries to your dashboard as needed.

Create a Dashboard in Looker Studio

Next, you need to create your visualizations. First, click the Add a chart button. You can choose from a selection of different chart types, like bar chart or line chart.

To set up a chart, you need to select the right data source and define which columns to display. Looker Studio’s chart editor has two main tabs to help you do this. The SETUP tab is where you choose the data source, dimensions, and metrics; this is where you decide what the chart will show. The STYLE tab lets you customize the look of the chart, i.e., by choosing fonts, colors, borders, and titles.

For example, when creating a bar chart, you’ll use the SETUP tab to show crime types and counts and the STYLE tab to adjust how the chart appears.

You can add other charts to your dashboard, such as scorecards that display a single number, line charts to show trends over time, or geo maps to visualize data by location. Each chart can be styled separately, giving you full control over the layout and design to match your preferences or highlight key insights.

Here’s what my final dashboard looks like:

Take Your SQL Skills Further

If you enjoyed building this dashboard, don’t stop here! Try exploring other datasets and building dashboards of your own. The more you practice, the better you’ll get at asking the right questions—and finding answers in the data.

Want to take your skills to the next level? Once again, I recommend our SQL for Data Analysis track. It’s a hands-on learning path designed to help you move beyond the basics and start thinking like a data analyst. You’ll learn how to write more advanced queries, work with real-world datasets, spot trends, compare time periods, segment your data, and prepare data for dashboards and reports. Each course includes interactive exercises, instant feedback, and real SQL practice. With this interactive track, you’re learning by doing rather than just reading.

By the end of the track, you’ll be able to explore datasets with confidence and build dashboards that actually answer important questions. Check it out today!