Back to Projects
CompletedData

GHG Emissions Analysis Dashboard

Interactive Databricks AI/BI dashboard analyzing greenhouse gas emissions across U.S. counties and states with SQL data transformation, geospatial mapping, and per-capita normalization.

Project Summary

I built an interactive dashboard in Databricks (Free Edition) to analyze greenhouse gas (GHG) emissions data across U.S. counties and states, covering the full analyst workflow inside the platform: dataset exploration via the Catalog, query development in the SQL Editor, and final visualization in an AI/BI Dashboard.

The goal was to answer a real analytical question: which regions emit the most GHG in absolute terms, which have the highest emissions relative to population, and how are emissions distributed geographically across the country.

Live Interactive Dashboard

Below is the live embedded Databricks AI/BI Dashboard:

Stack and Tools

  • Databricks Catalog: dataset discovery and access (emissions.default.emissions_data table).
  • SQL Editor: query development for aggregation, normalization, and ranking logic.
  • AI/BI Dashboards: map, bar chart, and table visualizations backed by SQL datasets.
  • Serverless SQL Warehouse: on-demand compute engine for query execution.

Workflow

1. Data exploration via Catalog

The source table emissions_data contains county-level records with GHG emissions (in metric tons of CO2e, stored as a comma-formatted string), population, state abbreviation, county/state name, and geographic coordinates. Because the emissions column ships as text with thousands separators, most queries needed a REPLACE + CAST step before any numeric operation.

2. SQL queries behind each visualization

Location data (map visualization) — pulls raw coordinates and emissions values to plot every county as a point on a map, color- or size-encoded by emissions volume:

SELECT 
    latitude,
    longitude,
    `GHG emissions mtons CO2e` as emissions
FROM 
    emissions.default.emissions_data

Emissions per person — normalizes emissions by population to surface counties with disproportionately high per-capita output rather than just large absolute totals:

SELECT 
    county_state_name,
    population,
    CAST(REPLACE(`GHG emissions mtons CO2e`, ',', '') AS DOUBLE) / population AS emissions_per_person
FROM 
    emissions_data
WHERE 
    population > 0 AND `GHG emissions mtons CO2e` IS NOT NULL
ORDER BY
    emissions_per_person DESC

Total emissions per state — aggregates county-level records up to the state level and ranks the top 10 emitting states:

SELECT 
    state_abbr,
    SUM(CAST(REPLACE(`GHG emissions mtons CO2e`, ',', '') AS DOUBLE)) AS total_emissions
FROM 
    emissions_data
GROUP BY
    state_abbr
ORDER BY
    total_emissions DESC
LIMIT 10

Top counties by total emissions — a ranked table of the 10 highest-emitting counties in absolute terms, paired with their population for context:

SELECT 
    county_state_name,
    population,
    CAST(
        REPLACE(`GHG emissions mtons CO2e`, ',', '') 
        AS DOUBLE) AS total_emissions
    FROM
        emissions_data
    ORDER BY total_emissions DESC
    LIMIT 10

3. Dashboard construction

With the four datasets defined, I built a dashboard combining geographic and ranked views:

  • Map — every county plotted by latitude/longitude, encoded by emissions volume, for an at-a-glance view of hotspots across the country.
  • Bar chart — "Total emissions per state" — top 10 states by absolute GHG output, useful for state-level policy or resource-allocation conversations.
  • Table — "Top counties by total emissions" — the 10 highest-emitting counties with population shown alongside, so raw scale isn't misread as inefficiency.
  • Table/chart — "Emissions per person" — the per-capita ranking, which tells a very different story than the absolute totals: some smaller counties post extreme per-person values despite modest total emissions.

4. Key analytical insight: absolute vs. per-capita framing

The most important design decision in this dashboard was including both an absolute ranking (total emissions per state/county) and a normalized one (emissions per person). Looking only at totals biases the story toward large, populous regions — but the per-capita view exposes small counties (often driven by industrial or agricultural activity) with outsized environmental impact relative to their population. Presenting both together avoids a misleading single-axis narrative, which is a common pitfall in environmental data storytelling.

Result

A geospatial and comparative dashboard combining a location-based map, top-10 rankings by state and county, and a per-capita emissions view — all derived from a single emissions_data table using four purpose-built SQL queries. The dashboard supports both a macro view (which states emit the most) and a granular view (which counties are disproportionately impactful).

Technical Learnings

  • Handling dirty numeric data stored as formatted strings (REPLACE + CAST) before performing aggregations or arithmetic in Databricks SQL.
  • Designing complementary metrics (absolute vs. per-capita) to prevent a dashboard from telling only half the story.
  • Building a map visualization directly from raw latitude/longitude columns without external GIS tooling.
  • Revisiting the iframe embedding constraints when publishing the dashboard to an external site — Databricks AI/BI embeds require the hosting domain to be explicitly allow-listed in workspace security settings, which affects local development (localhost) differently than production.

Next Steps

A natural extension would be joining this emissions dataset against economic or industrial-activity data to test whether per-capita outliers correlate with specific sectors, and adding a time dimension if historical emissions data becomes available.