Extract Insights and Visualize Data with Databricks SQL

Three modules: analytical SQL, effective visualizations, shareable dashboards

Table of Contents

1. Introduction and training context
2. Mastering Analytical SQL in Databricks

• 2.1 Setting up Your Analytical Workspace
• 2.2 Aggregate Functions for Key Metrics
• 2.3 Conditional logic with CASE, IF and COALESCE
• 2.4 Joining, Filtering, and Combining Datasets

3. Creating Effective Visualizations

• 3.1 From Query to Chart in One Click
• 3.2 Choosing the Right Visual for the Data
• 3.3 Customizing Labels, Aggregations, and Appearance
• 3.4 Adding Interactivity
• 3.5 Saving and Organizing

4. Building Shareable Dashboards

• 4.1 Dashboard Design Fundamentals
• 4.2 Creating Interactive Experiences
• 4.3 Collaboration and Sharing
• 4.4 Automation for Ongoing Value

5. Key SQL Query Summary
6. Key concepts and terminology
7. Good practices and recommendations

1. Introduction and background of the training

This training covers practical skills that are becoming increasingly important in IT and data-driven roles: transforming raw data into insights that are easy to understand and share. In many organizations, data exists in multiple systems, often in formats that do not lend themselves easily to analysis or comparison. Business teams need quick answers to questions like: how is compensation distributed, how does it vary by role or region, and do observed patterns match expectations? Without the right tools, answering these questions can be slow, manual, and error-prone.

Databricks solves this problem by providing a single platform where data can be connected, queried in SQL, and transformed into visual insights — all in one place. To make this concrete, the training is based on a realistic scenario using a global compensation dataset. The goal is not just to run SQL queries, but to understand what the data is telling us: how compensation is distributed, where gaps exist, and how these insights can be clearly communicated.

Each clip in the training constructs a specific piece of the analysis. These pieces ultimately come together into a dashboard that tells a complete compensation story.

Training structure

Dataset used: Global Compensation

The compensation dataset includes the following main columns:

2. Mastering Analytical SQL in Databricks

2.1 Setting up the analytical environment

Home page Databricks SQL

When you open Databricks SQL for the first time, the Home page is the first page that is displayed. It is designed to get started quickly. There are options for:

• Upload data: import new files or sources
• Browse existing datasets: explore what is already available in the workspace
• Connect to multiple data sources: link Databricks to external systems

These options are useful when importing new data into Databricks or exploring what is already available.

The Workspace

The Workspace is the environment where all Databricks assets live. It organizes objects such as notebooks, libraries, dashboards and experiments into folders, making it easier to manage work in one place. The Workspace also provides access to data objects and computational resources — it forms the foundation for everything we build and analyze in Databricks.

Key Point: Although the Workspace contains many different asset types, most of the SQL-centric work is in a separate section just below the navigation.

SQL tools in Databricks

In the SQL section, we find tools specifically designed for working with SQL and analytics:

• SQL Editor: the place where one writes and executes SQL queries against the data. This is where we explore the dataset and calculate the metrics throughout the training.
• Queries: the area where SQL queries are saved and organized so that they can be consulted, reused or shared later.
• Dashboards: the section for combining multiple charts, tables and text blocks to tell a complete story with the data. Dashboards are particularly useful for monitoring trends, comparing metrics, and sharing insights with others.
• SQL Warehouse: the computational resource that executes SQL queries. The choice of a warehouse affects performance and the way queries are executed.

Overall architecture

Databricks Workspace
│
├── SQL Editor          → Écriture et exécution des requêtes SQL
├── Queries             → Sauvegarde et organisation des requêtes
├── Dashboards          → Création d'expériences visuelles interactives
├── SQL Warehouse       → Ressource de calcul pour l'exécution des requêtes
│
└── Catalog (Unity Catalog)
    └── workspace
        └── hr (schema)
            └── compensation (table)

Navigate to SQL Editor

There are several ways to access the SQL Editor:

1. Via the Home page: click directly on the link to the SQL Editor
2. Via the Queries section: click on the Queries link and create a new query via the button at the top right or the link in the center of the screen
3. Via the SQL icon in the left navigation bar

Common error: When accessing the SQL Editor, it is possible to receive an error message like “Failed to load query”. This is usually caused by disconnecting from the previous session and the query cannot load. The solution is simple: close the tab and click on the SQL button to start a new query.

Access to tables via the Catalog

To find the compensation table in the Catalog hierarchy:

1. Develop the workspace
2. Expand schema hr
3. Find the compensation table

It is possible to simply drag and drop the table name into the editor to obtain a starting SELECT * query.

2.2 Aggregation functions for key metrics

aggregate functions allow you to summarize large datasets into meaningful business metrics. This is often the first step in any analytical workflow.

Labor counting

The most basic but essential metric: total number of employees. With each row representing an employee, counting the rows gives a quick measure of the size of the workforce.

SELECT COUNT(*) AS total_employees
FROM compensation;

Importance of basic metrics: Even simple metrics like this are important because they provide context for everything that will be calculated next. This result will subsequently become one of the KPI headers of the dashboard.

Average Base Salary Calculation

Averages are commonly used in reporting. It’s important to remember that they can be influenced by outliers — which is why averages are more useful when combined with other metrics rather than viewed alone.

SELECT AVG(base_salary) AS average_base_salary
FROM compensation;

Calculation of average and total bonus

SELECT 
    AVG(bonus) AS average_bonus,
    SUM(bonus) AS total_bonus
FROM compensation;

Calculation of the median salary (Median Salary)

The median represents the midpoint of the data and often gives a more realistic picture of typical compensation, especially in datasets where compensation varies significantly by role.

SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY base_salary) AS median_salary
FROM compensation;

Median vs. Average: The median is less sensitive to outliers than the average. In a compensation dataset, a few very high salaries can raise the average without it reflecting the reality of most employees.

Total base compensation

This metric helps frame the overall scale of compensation and is useful for understanding the scale of the data with which we work.

SELECT SUM(base_salary) AS total_base_compensation
FROM compensation;

Analysis by job title with GROUP BY

GROUP BY allows you to break down metrics by segment and begin to answer more specific business questions, such as how compensation is changing by job title in the organization.

SELECT 
    job_title,
    COUNT(*)          AS employee_count,
    AVG(base_salary)  AS avg_base_salary,
    AVG(bonus)        AS avg_bonus,
    SUM(base_salary)  AS total_base_salary
FROM compensation
GROUP BY job_title
ORDER BY avg_base_salary DESC;

Overview of global metrics

SELECT 
    COUNT(*)                                                    AS total_employees,
    AVG(base_salary)                                           AS average_base_salary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY base_salary)  AS median_salary,
    SUM(base_salary)                                           AS total_base_compensation,
    AVG(bonus)                                                 AS average_bonus,
    SUM(bonus)                                                 AS total_bonus
FROM compensation;

Best practices: When creating a new query in the SQL Editor, use the + icon at the top of the interface or the New Query menu. This opens a blank tab without interfering with existing queries.

2.3 Conditional logic with CASE, IF and COALESCE

This section covers transforming raw data into something easier to understand and analyze. Conditional logic helps group numerical values ​​into meaningful ranges that better align with how companies actually think about compensation.

Creating salary bands with CASE

Instead of working directly with individual salary values, they can be grouped into ranges or bands. This makes the patterns easier to spot and aligns better with how compensation is typically discussed in organizations.

SELECT 
    employee_name,
    job_title,
    department,
    base_salary,
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END AS salary_band
FROM compensation;

This query examines the base_salary and, when it matches the relevant threshold, applies a range: Low, Medium or High.

Classification with IF

The IF function allows you to perform a simple comparison against a benchmark. This type of logic is useful when you want a yes/no style classification, such as determining whether a salary is above or below a defined threshold.

SELECT 
    employee_name,
    job_title,
    base_salary,
    IF(base_salary > 75000, 'Above Benchmark', 'Below Benchmark') AS benchmark_status
FROM compensation;

Usage of IF vs CASE: IF is ideal for simple binary conditions (two possible outcomes). CASE is preferred for multiple conditions or multi-level classifications.

Handling missing values ​​with COALESCE

In actual compensation data, fields like bonuses or allowances are often missing. COALESCE allows null values ​​to be replaced with a default value (like zero) so that calculations remain accurate and consistent.

SELECT 
    employee_name,
    job_title,
    base_salary,
    COALESCE(bonus, 0)       AS bonus_cleaned,
    COALESCE(stock_grant, 0) AS stock_grant_cleaned,
    base_salary + COALESCE(bonus, 0) + COALESCE(stock_grant, 0) AS total_compensation
FROM compensation;

Imperfect real world data: Real world datasets are not perfect. Learning to deal with incomplete data is an essential analytical skill. Using COALESCE ensures that aggregations like SUM or AVG do not return NULL when some values ​​are missing.

Combining conditional logic with aggregation

By grouping employees by salary ranges and calculating counts and averages, we begin to answer more meaningful questions about the distribution of compensation in the organization.

SELECT 
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END                         AS salary_band,
    COUNT(*)                    AS employee_count,
    AVG(base_salary)            AS avg_base_salary,
    AVG(COALESCE(bonus, 0))     AS avg_bonus,
    MIN(base_salary)            AS min_salary,
    MAX(base_salary)            AS max_salary
FROM compensation
GROUP BY 
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END
ORDER BY avg_base_salary;

This script:

1. Create salary bands with CASE
2. Adds KPIs (count, average, min, max)
3. Grouped by the salary bracket itself

Browsing and saving queries

When working in the SQL Editor, it is possible to rename a query directly in the query tab. After updating the name, a popup appears at the top right asking if you want to move the query. Clicking on the Move query link opens the directory folders and allows you to move the query to the chosen folder — a good organizational practice from the start.

2.4 Joins, filters and combining datasets

These techniques allow you to go beyond simple summaries and start answering more complex business questions.

Filtering by salary threshold (WHERE)

Filtering allows you to reduce the dataset to only work with records relevant to the question asked. A common pattern in compensation analysis is to identify high earners or focus on a particular segment of the workforce.

SELECT 
    employee_name,
    job_title,
    department,
    base_salary
FROM compensation
WHERE base_salary > 60000
ORDER BY base_salary DESC;

Validation of columns: If a column does not exist in the dataset, it will be underlined in red in the editor. This is an immediate visual indicator of a syntax or naming error.

JOIN: individual comparison vs. average per position

One of the most powerful analyzes is to calculate the average salary by job title and join that result with individual employee records. This allows each employee’s salary to be compared to the average for their position — a powerful way to add context to individual data points.

SELECT 
    e.employee_name,
    e.job_title,
    e.department,
    e.base_salary,
    avg_by_title.avg_salary_for_title,
    ROUND(e.base_salary - avg_by_title.avg_salary_for_title, 2) AS salary_vs_avg
FROM compensation e
JOIN (
    SELECT 
        job_title,
        AVG(base_salary) AS avg_salary_for_title
    FROM compensation
    GROUP BY job_title
) avg_by_title
    ON e.job_title = avg_by_title.job_title
ORDER BY salary_vs_avg DESC;

In this example, an employee with a salary of $80,000 with an average of $72,000 for their role ends up with a salary_vs_avg of +$8,000, meaning they earn above average for their role.

Note on sensitive data: Including employee names in analytics queries may pose privacy concerns. In production environments, this sensitive information may need to be masked or anonymized depending on organizational policies.

UNION: Combining results from multiple queries

UNION is useful when you want to categorize or segment data and then group these categories into a single output for analysis or reporting.

-- Employés avec salaire élevé
SELECT 
    employee_name,
    job_title,
    base_salary,
    'High Earner' AS category
FROM compensation
WHERE base_salary >= 100000

UNION ALL

-- Employés avec salaire dans la tranche haute-moyenne
SELECT 
    employee_name,
    job_title,
    base_salary,
    'Upper-Mid Earner' AS category
FROM compensation
WHERE base_salary >= 70000 AND base_salary < 100000

UNION ALL

-- Employés avec salaire dans la tranche basse-moyenne ou faible
SELECT 
    employee_name,
    job_title,
    base_salary,
    'Lower Earner' AS category
FROM compensation
WHERE base_salary < 70000

ORDER BY base_salary DESC;

UNION vs UNION ALL: UNION removes duplicates and is slower because it must perform deduplication. UNION ALL keeps all records and performs better when we know that the datasets do not overlap.

Department comparison with advanced filtering

SELECT 
    department,
    COUNT(*)                        AS headcount,
    AVG(base_salary)                AS avg_salary,
    MIN(base_salary)                AS min_salary,
    MAX(base_salary)                AS max_salary,
    AVG(COALESCE(bonus, 0))         AS avg_bonus
FROM compensation
WHERE department IN ('Finance', 'Sales', 'IT', 'HR', 'Marketing')
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC;

3. Creating Effective Visualizations

3.1 From query to graph in one click

Raw query results are powerful, but visuals are what make them instantly understandable. This is where analysis becomes insight. A well-written query is powerful, but a well-designed chart makes that power accessible.

The basic query for visualization

The starting point is a simple aggregation — average salary by department — which produces a single row per department showing the average salary for each. This structure is ideal for visualization because it is clean, predictable, and easy to interpret.

SELECT 
    department,
    AVG(base_salary) AS avg_base_salary
FROM compensation
GROUP BY department
ORDER BY avg_base_salary DESC;

From table to graph: the process

1. Run the query in the SQL Editor
2. Observe the results in the results table at the bottom
3. Click on the + icon next to the Table result
4. Databricks instantly creates a visual from the data — a bar chart by default
5. There is no need for export, external tools, or rewriting of the query

Key moment: The visual is directly controlled by SQL. This shows how quickly you can move from a business question to a clear visual answer when analyzing and visualizing in the same place.

Visual enrichment

Once the chart has been created, it is possible to:

• Add data labels: check the “Show data labels” box in the options
• Sort the visual: go to the Y axis tab and select the sort by value option
• Save the visual: click on the Save button at the bottom
• Save query: return to the SQL Editor and save

More complex query with HAVING

To avoid noisy or misleading results, the HAVING clause can be used to filter job titles with a minimum number of employees.

SELECT 
    job_title,
    COUNT(*)         AS employee_count,
    AVG(base_salary) AS avg_salary
FROM compensation
GROUP BY job_title
HAVING COUNT(*) >= 4
ORDER BY avg_salary DESC;

HAVING vs WHERE: WHERE filters rows before aggregation. HAVING filters groups after aggregation. In this example, HAVING COUNT(*) >= 4 means that we only include positions with at least 4 employees — we cannot use WHERE here because COUNT(*) is not yet calculated at this point.

Management of the default scatter chart

When a query contains multiple numeric metrics, Databricks may offer a scatter chart by default. To obtain a bar chart:

1. Remove the employee_count column from the Y axis in the visual configuration
2. Change chart type to bar chart
3. Select horizontal option
4. Configure Y axis to display job_title (category)
5. Configure the X axis to display avg_salary
6. Sort by value for a clear view

Adding contextual metrics without clutter

employee_count does not need to be plotted as the primary axis, but may be useful as a reference or tooltip when interpreting the results. This important pattern to remember: start with a clear and simple visual, then add additional metrics to support the story rather than distract from it.

3.2 Choosing the right visual for the data

This is where design decisions start to matter. Choosing the right chart for the data is one of the most important parts of data visualization.

Example 1: Horizontal bar chart — Average bonus per position and department

This query analyzes the average bonus by job_title, with department adding an additional layer of context. This allows you to compare similar roles and see how compensation differs depending on where they are in the organization.

SELECT 
    job_title,
    department,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
GROUP BY job_title, department
ORDER BY avg_bonus DESC;

Why a horizontal bar chart?

• Job titles are long and easier to read horizontally
• The length of each bar makes it easy to compare bonus values at a glance
• Using department as a grouping adds analytical depth without cluttering the visual

Visual configuration:

• Sort by decreasing bonus value (Y axis)
• Add data labels so users see the value
• Remove X axis to declutter (data labels replace this axis)
• Clean up Y axis title by removing underscores

Example 2: Pie chart — Distribution of average bonus by location

SELECT 
    location,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
GROUP BY location
ORDER BY avg_bonus DESC;

Why a pie chart? Pie charts are often misused, but they work well to show composition. The key is to keep the number of categories small and meaningful. For a question of proportion – how the bonus budget is distributed according to regions – the pie chart is appropriate.

Visual configuration:

• Change series name for better readability
• Update data labels to display average bonus value (rather than percentage, which doesn’t make sense here)
• Remove decimals for cleanliness
• Order values clockwise from highest to lowest

Analytical result: The lowest bonus is in Global Operations, the highest in Finance.

Example 3: Scatter chart — Relationship between salary and bonus

SELECT 
    job_title,
    AVG(base_salary)            AS avg_salary,
    AVG(COALESCE(bonus, 0))     AS avg_bonus,
    COUNT(*)                    AS employee_count
FROM compensation
GROUP BY job_title
ORDER BY avg_salary DESC;

Why a scatter chart? To compare two numeric metrics and explore their relationship in the context of a job_title, the scatter chart is the ideal choice. Each line of the query represents a job_title and the scatter plot instantly reveals:

• The general pattern: The higher the salary, the greater the bonus
• Clusters: Groupings of positions with similar compensation profiles
• Outliers: Positions that earn more bonuses despite having a similar salary level to others

Visual configuration:

• Update axis titles (remove underscores)
• Do not activate data labels as they would clutter the visual
• Users can see values by hovering over data points

Chart type selection principles

3.3 Customizing labels, aggregations and appearance

Small changes make big differences. The analysis is already done — now it’s about improving the way the insight is presented. Small changes to labels, aggregations, and layout can completely change how a chart is interpreted.

Basic query: Total salary by department

SELECT 
    department,
    SUM(base_salary) AS total_salary
FROM compensation
GROUP BY department
ORDER BY total_salary DESC;

Visual optimizations

1. Reduced number of metrics displayed

If a visual displays two values ​​(for example SUM and AVG), but you only want to show one, you must delete the unwanted value from column Y. Clarity comes before the quantity of information.

2. Horizontal orientation for readability

When category labels are displayed vertically, readability for the end user is compromised. If the labels don’t read left to right, you can imagine the user tilting their head to read them — that’s not the desired experience. The solution is to transform the graph into horizontal bar chart:

1. Change chart type to “Bar chart”
2. Select horizontal option

3. Correction of axis titles

• Y axis displays “department” → capitalize: “Department”
• X axis displays “SUM of salary” → rename to “Total Salary”

4. Customization of colors (branding)

In most cases, you will want to dress the visuals and reports in the colors of the organization. For this:

1. Go to the Colors tab of the visual
2. Click on the existing color → a popup opens
3. Enter the hex value of the custom color
4. Click OK

This is how we begin to create a visual identity for the reports.

5. Addition of data labels

Data labels remove the guesswork and give the end user what they want: the exact number. For example, to know the exact value of the total salary in the Finance department, without data labels we can only guess “more than 800K”. With data labels enabled:

• Check the “Show data labels” box
• Remove decimals for cleanliness

6. Optional removal of the X axis

If data labels are enabled, the X axis becomes redundant. The shape of the visual does not change and users always see the difference between departments. The option to remove the X axis is available, but it may be appropriate to keep it until it is possible to add a title to the visual.

7. Sorting values

The Sort values option, when enabled, sorts the visual alphabetically by department. Disabling it orders the visual by value. Depending on the focus of the visual, you can switch between the two options.

3.4 Adding interactivity

An interactive experience puts data exploration in the hands of the end user. The interactivity begins in the SQL itself.

Dynamic parameters in SQL queries

Instead of hardcoding a filter value, we use a dynamic parameter. The syntax in Databricks SQL is to use a colon : before the parameter name.

SELECT 
    job_title,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
WHERE department = :department_param
GROUP BY job_title
ORDER BY avg_bonus DESC;

Parameter behavior:

• This syntax creates a dynamic placeholder under the script in the interface
• A value is expected before the query can run
• If executed without value → no result returned
• If we enter “Sales” → returns the average bonus per job_title for the Sales department
• If you enter “Finance” → returns the data for Finance
• If you enter “IT” → returns the data for IT

Key concept: Dynamic input replaces hard-coded filters, allowing data to adapt without rewriting the query. When these inputs are connected to the visuals, the impact is immediately visible. Charts are no longer static outputs — they respond to controlled changes in the underlying data.

Parameter conversion to dropdown list

To improve ergonomics, the parameter can be converted into a drop-down list:

1. Click on the Settings icon of the parameter
2. Update the type in “Dropdown”
3. Enter the predefined values for the drop-down list (eg: Sales, Finance, IT, HR, Marketing)
4. Select a default value
5. Click outside the parameters to validate

We now have a predefined list that can be used to dynamically update the results.

Connection of the parameter to a dashboard

Parameterized queries can be integrated into a dashboard so that the parameter controls several visuals at the same time.

Steps:

1. Go to the Dashboard section and click on “Create dashboard”
2. In the dashboard canvas, go to the left and add an SQL query
3. Paste the parameterized query (with :department_param)
4. Run query — it expects a value for the parameter
5. From the toolbar at the bottom, drag a Visualization element onto the canvas
6. In the parameters on the right, configure the axes:

• X axis: avg_bonus or total_salary
• Y axis: job_title

7. From the toolbar, drag a Filter element onto the canvas
8. Name the filter, go to settings and click on the + next to “Parameters”
9. Select department_param from the list

The dashboard filter is now connected to the query parameter. When a user enters a value in the filter, the visual adapts instantly.

Interactivity architecture: We moved from static logic to dynamic logic. We connected this logic to a dashboard and saw how the parameter can dynamically control the visuals. This is the foundation of interactive reporting — not just displaying data, but designing systems that respond to the user.

3.5 Backup and organization

In growing reporting environments, creation is only half the challenge — maintaining clarity and control becomes just as important.

Where to find queries in Databricks

There are several places to access saved queries:

1. Home page: displays some suggested queries that have recently been worked on
2. Workspace: contains almost all assets. Has a focused search option and filters
3. Recents tab: displays what we have recently worked on, with some filters available
4. SQL Editor: shows open queries and lists previously saved or used queries
5. Queries section: lists all workspace queries, with a search option and filters

Global search

You can use the global search option that appears at the top of most Databricks screens. For example, searching for “salary” returns everything that contains this word and you can filter by asset type (queries only).

Clicking a query in the search results returns to the SQL Editor and displays the query in its latest state.

Organization by folders

Folders introduce structure. Instead of a flat list of requests, folders allow logical grouping by department, project or business function. This is especially important in shared environments where multiple developers contribute.

Creating folders:

• Via the Create button on the right in the Workspace
• Via right click in the Workspace → “Create folder” option

Examples of folders to create:

• HR Reporting — for HR reports
• Module Queries — for training queries

Moving assets:

• Click on the ellipsis (…) to the right of the asset
• Select Move
• In the popup, navigate to the desired location and click Move

It is also possible to move a query directly from the SQL Editor by clicking on the Move link in the popup that appears after the renaming.

Permission management

Each asset, whether a query or a folder, can be restricted to who can see, modify or manage it via permissions. This protects assets from unauthorized editing and allows the work to remain private until it is ready to be published.

To set permissions:

1. Right-click on an asset
2. Click on Share (Permissions)
3. The window that opens shows the current permissions
4. To add new users, enter the name in the field and grant them permissions: Manage, Edit, Run or View

Professional reporting: Professional reporting is not only about creating insights, it is also about maintaining an environment where these insights can grow securely and sustainably.

4. Building Shareable Dashboards

4.1 Fundamentals of dashboard design

So far, we have built strong visuals and made them interactive. We now move from building components to designing experiments. A well-designed dashboard doesn’t just display data — it guides attention.

Fundamental Design Principles

A professional dashboard is based on three principles:

1. Visual hierarchy: predetermine what should be seen first, second and third. Headline KPIs provide immediate insight, primary visuals highlight key analytical relationships, and secondary charts provide additional breakdown and context.

2. Structured layout: use grids and alignment to create order rather than chaos. Nothing should compete for attention — this clarity is intentional.

3. Spacing and balance: Ensure the dashboard looks intentional rather than cluttered. Together, these principles reduce cognitive load and improve clarity for the end user.

The flow of a well-designed dashboard

A well-designed dashboard flows from top to bottom:

1. Summary → KPIs headline at the top
2. Core analysis → Primary visuals in the middle
3. Supporting breakdowns → Context graphs at the bottom

Construction of the dashboard dataset

For this dashboard, we use the complete remuneration dataset:

SELECT * FROM compensation;

The aggregation functions available in the dashboard will be used to create the visuals directly — without the need to pre-aggregate in SQL.

Adding and configuring visuals

Visual 1: Average salary by department (Bar chart)

1. From the toolbar at the bottom, drag a Visualization element onto the canvas
2. Configure in the right panel:

• X axis: base_salary with aggregation = Average
• Y axis: department

3. Order from highest to lowest
4. Add data labels
5. Delete X axis title and values
6. Delete Y axis title
7. Add a descriptive main title

Visual 2: Total salary by division (Bar chart)

1. Configure the X axis: base_salary with aggregation = SUM
2. Configure Y axis: division
3. Order by value descending
4. Apply the same decluttering amendments
5. Add a descriptive title

Visual 3: KPI — Average bonus (Counter)

1. Add a new visual and change the visualization type to Counter
2. Select bonus in the Value option with aggregation = Average
3. Format the counter to display the currency value
4. Resize the visual to align with other KPI cards

Visual 4: KPI — Median salary (Counter)

-- Pour le KPI médian, une requête séparée peut être nécessaire
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY base_salary) AS median_salary
FROM compensation;

Visual 5: KPI — Average stock grant (Counter)

1. Copy and paste an existing KPI (right click → Copy, then right click → Paste)
2. Change value for stock_grant with aggregation = Average
3. Update format and title

Final dashboard layout

Three row structure:

Application of styling:

• Consistent Colors: Use a simple, intentional palette. Too many colors can create unnecessary visual noise, whereas a consistent palette helps the viewer focus on the data itself.
• Alignment: KPIs side by side at the top, separated by the dashboard title, allow users to quickly scan and compare headline metrics
• Size hierarchy: primary visuals are slightly larger than others, signaling their importance and primary analysis character
• Branding: apply organizational colors across visuals to maintain consistency. When colors align with familiar organizational themes, reports appear more integrated with the tools and websites people already use

Publication of the dashboard

1. Click on the Publish button at the top right
2. A query sharing option appears — this option allows dashboard users to run queries via your own permissions
3. Continue to Publish
4. Share directly with other users if desired
5. Use the View published button to see the dashboard in live state

Layout Improvement: Design improvements often come from small adjustments — aligning edges, balancing empty or dark space, making sure elements are evenly distributed on the canvas. The underlying data has not changed, but the way it is presented has been transformed.

4.2 Creating interactive experiences

One of the biggest advantages of modern dashboards is interactivity. Instead of creating multiple static reports, a single dashboard can allow users to explore different perspectives of the same data.

Cross-highlighting

Even without adding filters, many visuals already automatically interact with each other. By selecting a category in a chart, other visuals respond by highlighting related data. This is called cross-highlighting.

Advantages of cross-highlighting:

• Allows users to explore relationships between visuals without changing the underlying query
• Instead of seeing each chart in isolation, the dashboard starts to behave like a connected analytical environment

Global filters

While cross-highlighting works directly in visuals, dashboards often benefit from broader controls allowing users to change the entire view of a report. This is where Global filters come in.

A Global filter applies to multiple visuals at once, allowing users to focus on a specific segment of their data.

Added a global filter:

1. Click on the Edit button
2. Click on the + icon to add a Global filter
3. Use the menu on the right to add the location field
4. Configure the filter title (eg: “Location”)
5. Save

When you select a location, each visual on the dashboard updates instantly to reflect this selection. The same dashboard can thus answer several questions without requiring several reports.

Combined filters for multidimensional exploration

By introducing a second filter (e.g. division), users can explore compensation insights according to different functional areas of the organization.

Advantages of combined filters:

• Users can explore data by both location and division
• Filters respond intelligently to each other: when you select a value in one filter, the options available in the other filter automatically adjust based on the remaining data

Benchmarking example:

• Select “Global Technology” as division
• Location filter updates to only show locations in this division
• Compare New York vs San Francisco:
• San Francisco: 13 employees, average salary $60K, average bonus $2.23K
• New York: 12 employees, average salary $68K, average bonus $2.83K

This is a great example of how interactivity and responsive filtering enable quick comparisons, keeping the analysis focused and precise.

Drill-through (navigation to details)

Filters allow users to adjust the data they see, but sometimes they also need to be able to drill down to finer levels of detail. This is where the drill-through functionality becomes valuable.

Drill-through allows users to move from a high-level visual to a more detailed report focused on selected data.

Drill-through configuration:

1. Create a new page in the dashboard — call this page “Details”
2. Add and format a title at the top of the page
3. Add a Table type visual covering the entire width of the page
4. Add detail columns to the table:

• employee_name
• job_title
• department
• base_salary
• bonus
• stock_grant

The main dashboard page focuses on summaries and diagrams, while this page provides the underlying records behind these insights.

Using drill-through:

1. On the main visual, right click on an element (e.g.: a bar, a point)
2. Select the option “Drill to” → “Details”
3. The selected context is automatically moved to the details page
4. The page only displays data from the selected category

Layered approach: High-level views for exploration, supported by detailed views for in-depth investigations. This is a common and powerful pattern in dashboard design.

4.3 Collaboration and sharing

Dashboards are rarely used in isolation. In most environments, they are shared across teams to support decision-making and collaboration.

Sharing a dashboard

Sharing with other users can be initiated from multiple places in the Databricks workspace — all behaving the same and providing the same screen for applying these settings. In practice, we generally use the Share option at the top right of the dashboard.

The four levels of permissions

When sharing a dashboard, it is important to assign the correct access level depending on how the dashboard will be used.

Sharing process

1. Click on the Share option at the top right
2. In the field, type the name of a user or select “All workspace users”
3. Assign the appropriate level of permissions
4. Optional: check the box to send an email to new users
5. Click on Add

At any time, you can access the sharing screen again to modify the permissions granted.

Effective Collaboration: Collaboration works best when everyone understands their role in the reporting environment. This ensures consistency, protects structure and supports collaboration between teams — it’s what makes dashboards truly effective in a real-world environment.

4.4 Automation for Sustainable Value

Dashboards are more valuable when they stay up to date. Rather than manually updating reports, modern platforms allow you to automate refresh processes to keep data current over time. Automation transforms one-off output dashboards into continuous reporting tools.

Configuring a scheduled refresh

1. Click on the Schedule option at the top right of the dashboard
2. Give the schedule a clear name (e.g. “Hourly”)
3. Configure the frequency: every hour, at 0 minutes after each hour
4. Select interval: every 1, 2 or more hours as needed
5. Choose to apply a filter:

• Default: no filter applied
• Currently applied filter: for example, a filter on a specific city like “Chicago”

Good naming practice: If a filter is applied, it is beneficial to add this filter to the schedule name (eg: “Hourly - Chicago”) for greater clarity.

Configuring destination notifications

Notification destinations allow you to send notifications to various locations in the organization. To configure one:

1. Go to Settings (icon at the top right of the workspace)
2. Select the Notifications option on the left
3. Click on Manage notification destinations
4. Click on Add destination

Available destination types:

• Email: sending an email to one or more addresses
• Webhook: integration with external systems via HTTP
• Microsoft Teams: notification in a Teams channel
• And other options depending on the workspace configuration

Creating an Email destination:

1. Select “Email” as type
2. Give a name to the destination
3. Add the destination email address
4. Click on Create

Finalization of the schedule with subscribers

Once the destination notification has been created:

1. Return to the dashboard and reopen the Schedule menu
2. Apply the same schedule settings
3. In the Subscription option, the created Email destination is now available
4. Under Advanced settings, select the SQL warehouse to use (or leave as default)
5. Configure a custom email subject if desired
6. Click on Create → the schedule is now active

Management of existing schedules

After creating a schedule, it can be modified at any time:

1. Click on the ellipsis (…) of the schedule
2. Update necessary details

Edit options:

• Adjust timing
• Update recipients
• Create additional schedules for different use cases

This flexibility allows dashboards to adapt to changing reporting needs.

Long-term value: Automation ensures that dashboards remain useful long after they are first created. By keeping data up to date and delivering consistency to users, we reduce manual effort and increase reliability. This allows dashboards to move from one-off analysis tools to continuous sources of insight that support regular decision-making.

5. Key SQL Query Summary

Here is a complete compilation of all the important SQL queries covered in the training.

5.1 Exploration of the dataset

-- Afficher toutes les données du dataset de rémunération
SELECT * FROM compensation;

5.2 Basic Metrics

-- Taille de la main-d'œuvre
SELECT COUNT(*) AS total_employees
FROM compensation;

-- Salaire de base moyen
SELECT AVG(base_salary) AS average_base_salary
FROM compensation;

-- Bonus moyen et total
SELECT 
    AVG(bonus) AS average_bonus,
    SUM(bonus) AS total_bonus
FROM compensation;

-- Salaire médian
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY base_salary) AS median_salary
FROM compensation;

-- Rémunération totale de base
SELECT SUM(base_salary) AS total_base_compensation
FROM compensation;

-- Vue globale combinée
SELECT 
    COUNT(*)                                                    AS total_employees,
    AVG(base_salary)                                           AS average_base_salary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY base_salary)  AS median_salary,
    SUM(base_salary)                                           AS total_base_compensation,
    AVG(COALESCE(bonus, 0))                                    AS average_bonus,
    SUM(COALESCE(bonus, 0))                                    AS total_bonus
FROM compensation;

5.3 Aggregation by dimension

-- Métriques par intitulé de poste
SELECT 
    job_title,
    COUNT(*)          AS employee_count,
    AVG(base_salary)  AS avg_base_salary,
    AVG(COALESCE(bonus, 0)) AS avg_bonus,
    SUM(base_salary)  AS total_base_salary
FROM compensation
GROUP BY job_title
ORDER BY avg_base_salary DESC;

-- Métriques par département avec filtre de taille
SELECT 
    department,
    COUNT(*)              AS headcount,
    AVG(base_salary)      AS avg_salary,
    MIN(base_salary)      AS min_salary,
    MAX(base_salary)      AS max_salary,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC;

-- Salaire moyen par département (pour visualisation)
SELECT 
    department,
    AVG(base_salary) AS avg_base_salary
FROM compensation
GROUP BY department
ORDER BY avg_base_salary DESC;

-- Bonus moyen par poste et département
SELECT 
    job_title,
    department,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
GROUP BY job_title, department
ORDER BY avg_bonus DESC;

-- Bonus moyen par localisation
SELECT 
    location,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM compensation
GROUP BY location
ORDER BY avg_bonus DESC;

-- Relation salaire/bonus par poste (pour scatter chart)
SELECT 
    job_title,
    AVG(base_salary)             AS avg_salary,
    AVG(COALESCE(bonus, 0))      AS avg_bonus,
    COUNT(*)                     AS employee_count
FROM compensation
GROUP BY job_title
ORDER BY avg_salary DESC;

-- Salaire total par département
SELECT 
    department,
    SUM(base_salary) AS total_salary
FROM compensation
GROUP BY department
ORDER BY total_salary DESC;

5.4 Conditional logic

-- Salary bands avec CASE
SELECT 
    employee_name,
    job_title,
    department,
    base_salary,
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END AS salary_band
FROM compensation;

-- Classification benchmark avec IF
SELECT 
    employee_name,
    job_title,
    base_salary,
    IF(base_salary > 75000, 'Above Benchmark', 'Below Benchmark') AS benchmark_status
FROM compensation;

-- Gestion des nulls avec COALESCE
SELECT 
    employee_name,
    job_title,
    base_salary,
    COALESCE(bonus, 0)       AS bonus_cleaned,
    COALESCE(stock_grant, 0) AS stock_grant_cleaned,
    base_salary + COALESCE(bonus, 0) + COALESCE(stock_grant, 0) AS total_compensation
FROM compensation;

-- Agrégation par salary band (CASE + GROUP BY)
SELECT 
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END                         AS salary_band,
    COUNT(*)                    AS employee_count,
    AVG(base_salary)            AS avg_base_salary,
    AVG(COALESCE(bonus, 0))     AS avg_bonus,
    MIN(base_salary)            AS min_salary,
    MAX(base_salary)            AS max_salary
FROM compensation
GROUP BY 
    CASE 
        WHEN base_salary < 50000  THEN 'Low'
        WHEN base_salary < 100000 THEN 'Medium'
        ELSE 'High'
    END
ORDER BY avg_base_salary;

5.5 Filtering and joins

-- Filtre par seuil salarial
SELECT 
    employee_name,
    job_title,
    department,
    base_salary
FROM compensation
WHERE base_salary > 60000
ORDER BY base_salary DESC;

-- JOIN : comparaison individuelle vs moyenne par poste
SELECT 
    e.employee_name,
    e.job_title,
    e.department,
    e.base_salary,
    avg_by_title.avg_salary_for_title,
    ROUND(e.base_salary - avg_by_title.avg_salary_for_title, 2) AS salary_vs_avg
FROM compensation e
JOIN (
    SELECT 
        job_title,
        AVG(base_salary) AS avg_salary_for_title
    FROM compensation
    GROUP BY job_title
) avg_by_title
    ON e.job_title = avg_by_title.job_title
ORDER BY salary_vs_avg DESC;

-- UNION : segmentation des employés par tranche salariale
SELECT 
    employee_name,
    job_title,
    base_salary,
    'High Earner' AS category
FROM compensation
WHERE base_salary >= 100000

UNION ALL

SELECT 
    employee_name,
    job_title,
    base_salary,
    'Upper-Mid Earner' AS category
FROM compensation
WHERE base_salary >= 70000 AND base_salary < 100000

UNION ALL

SELECT 
    employee_name,
    job_title,
    base_salary,
    'Lower Earner' AS category
FROM compensation
WHERE base_salary < 70000

ORDER BY base_salary DESC;

5.6 Parameterized queries

-- Requête avec paramètre dynamique pour le département
SELECT 
    job_title,
    AVG(COALESCE(bonus, 0)) AS avg_bonus,
    SUM(base_salary)        AS total_salary
FROM compensation
WHERE department = :department_param
GROUP BY job_title
ORDER BY avg_bonus DESC;

Parameter syntax: In Databricks SQL, a dynamic parameter is defined using :parameter_name. This syntax creates an interactive placeholder below the script in the interface, where a value must be entered before execution.

6. Key concepts and terminology

Databricks and environment

SQL and Data Analysis

Visualization and dashboard

Permissions and governance

7. Good practices and recommendations

In analytical SQL

1. Always validate columns before running: Databricks SQL underlines non-existent columns in red — check for these errors before running a query.

2. Use COALESCE for calculations involving nulls: Without COALESCE, a null value in a column like bonus will propagate null in any arithmetic or aggregation calculation.

3. Associate the median to the average: The average can be distorted by extreme values ​​(very high management salaries). The median gives a more representative picture of typical compensation.

4. Organize queries into folders from the start: Creating a logical folder structure (by project, department, or function) from the start avoids clutter as the analytics environment grows.

5. Save and name queries descriptively: Clear names like compensation_avg_by_department rather than query1 facilitate reuse and collaboration.

6. Use HAVING for filtered aggregations: Do not confuse WHERE (before aggregation) and HAVING (after aggregation). To filter on COUNT(*), only HAVING is valid.

7. Use dynamic parameters rather than hardcoded values: Replace fixed values ​​in WHERE with :parameter_name parameters to make queries reusable.

In visualization

8. Match the type of graph to the question asked:

• Category comparison → horizontal bar chart
• Composition/proportion → pie chart (with few categories)
• Relationship between two numeric metrics → scatter chart
• Key unique value → counter

9. Orient bar charts horizontally with long labels: Vertical labels forcing the user to tilt the head impair readability. The horizontal bar chart solves this problem.

10. Enable data labels to remove guesswork: Without data labels, users can only estimate values. Data labels give the exact value immediately.

11. Use consistent colors for branding: A simple color palette aligned with the organization’s visual identity makes reports more trustworthy and increases adoption.

12. Don’t overload visuals: Start with a simple, clear visual, then add additional metrics only if they support the story — not if they distract from it.

13. Remove underscores from axis titles: Axis titles like base_salary should be renamed to “Base Salary” for professional presentation.

In dashboard design

14. Apply a clear visual hierarchy: KPIs headline at the top → primary analysis in the middle → secondary breakdowns at the bottom. Users need to know what to look at first.

15. Use size and position to signal importance: Primary visuals (key analysis) should be slightly larger than others to signal importance.

16. Maintain balance and spacing: Dashboards should look intentional rather than cluttered. Consistent spacing and careful alignment reduce cognitive load.

17. Remove redundant axes if data labels are enabled: If the values ​​are already readable on the graph, the corresponding axis can be deleted to declutter.

18. Test interactivity before publishing: Verify that cross-highlighting, global filters and drill-through work correctly before sharing with users.

In collaboration and governance

19. Assign permissions to the lowest level necessary: Most users only need Can View or Can Run. Reserve Can Edit for developers and Can Manage for dashboard owners.

20. Configure refresh schedules for production dashboards: A dashboard whose data is not up to date quickly loses its value. Automate refreshes with a frequency appropriate to the business context.

21. Use notification destinations to alert subscribers: Configure email or Teams notifications so that stakeholders are informed of new available data.

22. Document the structure of queries and folders: In multiple teams, documenting the organization of assets facilitates collaboration and reduces duplication.

Documentation generated on 2026-06-20

Search Terms

extract · insights · visualize · data · databricks · sql · azure · spark · engineering · analytics · dashboard · salary · average · chart · queries · query · bonus · global · metrics · aggregation · analytical · calculation · combining · conditional