Query a Dataset

In this tutorial you will learn how to use dsgrid query dataset to map a registered dataset to alternate dimensions. The example maps county-level energy data with detailed subsectors to state-level and a coarser subsector grouping in a single query.

This tutorial assumes that:

  • You have a registered dataset in a dsgrid registry.

  • You have populated your ~/.dsgrid.json5 with the registry location.

  • The required dimension mappings are registered (or you will register them).

If you need to create and register a dimension mapping, follow How to Create Dimension Mappings first.

Starting and Target Dimensions

The dataset to be aggregated uses a county geography dimension whose records look like this:

County dimension records (counties.csv)

id

name

state

time_zone

06073

San Diego County

CA

America/Los_Angeles

06075

San Francisco County

CA

America/Los_Angeles

48141

El Paso County

TX

America/Chicago

48201

Harris County

TX

America/Chicago

36001

Albany County

NY

America/New_York

36119

Westchester County

NY

America/New_York

08031

Denver County

CO

America/Denver

08059

Jefferson County

CO

America/Denver

The target state dimension has these records:

State dimension records (states.csv)

id

name

is_conus

census_division

census_region

CA

California

True

pacific

west

CO

Colorado

True

mountain

west

TX

Texas

True

west_south_central

south

NY

New York

True

mid_atlantic

northeast

A many-to-one aggregation mapping connects the two. Each county maps to exactly one state with a fraction of 1 (the full value is assigned to the target):

County-to-state mapping (lookup_county_to_state.csv)

from_id

from_fraction

to_id

06073

1

CA

06075

1

CA

08031

1

CO

08059

1

CO

36001

1

NY

36119

1

NY

48141

1

TX

48201

1

TX

The dataset also has a subsector dimension with detailed building types:

Subsector dimension records (subsectors.csv — first rows)

id

name

FullServiceRestaurant

Full Service Restaurant

Hospital

Hospital

LargeHotel

Large Hotel

LargeOffice

Large Office

SmallOffice

Small Office

Warehouse

Warehouse

A second mapping aggregates all commercial building types into a single commercial_subsectors category:

Subsector mapping (lookup_subsectors_to_commercial_subsectors.csv — first rows)

from_id

to_id

FullServiceRestaurant

commercial_subsectors

Hospital

commercial_subsectors

LargeHotel

commercial_subsectors

LargeOffice

commercial_subsectors

SmallOffice

commercial_subsectors

Warehouse

commercial_subsectors

Step 1: Identify Source and Target Dimensions

Start by inspecting the dataset’s current dimensions:

dsgrid registry datasets list
dsgrid registry datasets dump <dataset-id>

The list command shows all registered datasets. The dump command writes a dataset’s full config (including its dimension references) to a local file so you can inspect it.

Identify which dimensions you want to remap. For this example, we want to:

  1. Aggregate geography from counties to states.

  2. Aggregate subsector from individual building types to a coarser grouping.

Check what target dimensions and mappings are available:

dsgrid registry dimensions list -f "Type == geography"
dsgrid registry dimensions list -f "Type == subsector"

Note the ID (a UUID) and Version of each target dimension.

Step 2: Verify Mappings Exist

Confirm that mappings exist between the dataset’s current dimensions and each target:

dsgrid registry dimension-mappings list

Look for mappings whose From ID matches the dataset’s geography / subsector dimension and whose To ID matches the target. If a mapping does not exist, create and register one using How to Create Dimension Mappings.

Step 3: Generate a Query Template

Use the CLI to generate a query template:

dsgrid query dataset create-query my-state-aggregation my-dataset-id

This creates dataset_query.json5 with the following structure:

{
  name: "my-state-aggregation",
  result: {
    table_format: {
      format_type: "stacked",
    },
    output_format: "parquet",
    sort_columns: [],
    single_output_file: false,
  },
  dataset_id: "my-dataset-id",
  to_dimension_references: [],
  mapping_plan: null,
  time_based_data_adjustment: {
    leap_day_adjustment: "none",
    daylight_saving_adjustment: {
      spring_forward_hour: "none",
      fall_back_hour: "none",
    },
  },
  wrap_time_allowed: false,
}

Leave mapping_plan as null. User-defined mapping plans are not yet supported for dataset queries.

Step 4: Populate Target Dimensions

Edit dataset_query.json5 to add entries to to_dimension_references. Each entry identifies a target dimension by its type, dimension_id, and version. The example below maps two dimensions — geography and subsector — in a single query:

{
  name: "my-state-aggregation",
  dataset_id: "my-dataset-id",
  to_dimension_references: [
    {
      type: "geography",
      dimension_id: "<target-geography-uuid>",
      version: "1.0.0",
    },
    {
      type: "subsector",
      dimension_id: "<target-subsector-uuid>",
      version: "1.0.0",
    },
  ],
  result: {
    table_format: {
      format_type: "stacked",
    },
    output_format: "parquet",
    sort_columns: ["geography", "subsector"],
  },
}

Note

You do not need to specify mapping IDs. dsgrid finds registered mappings between your dataset’s dimensions and the target dimensions automatically.

What happens to dimensions not listed in to_dimension_references?

Dimensions that are not listed in to_dimension_references are kept at their original resolution. They pass through the query unchanged — they are not aggregated or collapsed.

For example, if the dataset has columns geography, sector, subsector, model_year, and end_use, and you only map geography and subsector, the output will still contain sector, model_year, and end_use at their original granularity.

Step 5: Run the Query

dsgrid query dataset run dataset_query.json5 -o query_output/

dsgrid will:

  1. Load the dataset from the registry.

  2. Resolve mappings between the dataset’s dimensions and each target dimension.

  3. Apply the mappings to transform the data.

  4. Write results to query_output/my-state-aggregation/.

Step 6: Inspect the Output

The output directory contains the mapped data:

query_output/
└── my-state-aggregation/
    └── table.parquet

You can inspect the results with Python:

import pandas as pd

df = pd.read_parquet("query_output/my-state-aggregation/table.parquet")
print(df.head())
print(f"Unique geographies: {df['geography'].nunique()}")
print(f"Unique subsectors: {df['subsector'].nunique()}")

For large outputs, query the file with DuckDB (installed with dsgrid) instead of loading it all into memory:

import duckdb

duckdb.sql(
    "SELECT geography, subsector, SUM(value) AS value "
    "FROM 'query_output/my-state-aggregation/table.parquet' "
    "GROUP BY geography, subsector"
).show()

For outputs too large for a single node, use PySpark:

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
df = spark.read.parquet("query_output/my-state-aggregation/table.parquet")
df.show()
df.select("geography").distinct().show()
df.select("subsector").distinct().show()

If you ran the query with the Spark backend, table.parquet is a directory of part files rather than a single file (unless you set single_output_file: true). pandas reads the directory as-is; with DuckDB, use 'query_output/my-state-aggregation/table.parquet/*.parquet'.

Because we mapped counties → states, the geography column now contains state abbreviations (CA, CO, NY, TX, …) instead of FIPS codes. Similarly, subsector now contains the coarser grouping rather than individual building types. All other dimension columns remain at their original resolution.

Next Steps