Report ยท Chapter 7

Star schema

The feature table loaded into DuckDB as one fact and four dimensions.

Source: data/processed/rfi.duckdb 6 checks passed 4 October 2026
The open concrete frame of a building under a crane, with a city skyline beyond.

Section 1Database load

07_load_rfi_duckdb.py read data/processed/rfi_features.csv and ran sql/01_create_star_schema.sql. It wrote data/processed/rfi.duckdb. The feature CSV was not changed. A later run replaces that database file.

TableRowsGrain
fact_rfi107One row per RFI, RFI-001 to RFI-107
dim_date9One row per received date
dim_party16One row per party name
dim_discipline10One row per discipline
dim_issue_type10One row per issue type

Section 2Quality checks

sql/02_quality_checks.sql wrote data/processed/validation/sql_quality_checks.csv. All 6 checks passed.

CheckResultDetail
fact_row_countPASS107 rows
duplicate_rfi_idPASS0 duplicated identifiers
rfi_id_sequencePASSRFI-001 to RFI-107
missing_dimension_keysPASS0 facts missing a dimension key
blank_questionPASS0 blank
invalid_statusPASS0 outside Answered and Open

Section 3Dimensions

Received dates run from 1 September 2026 to 21 September 2026. Each date_key is the date written as YYYYMMDD.

Date receivedDate key
2026-09-0120260901
2026-09-0220260902
2026-09-0420260904
2026-09-1020260910
2026-09-1420260914
2026-09-1520260915
2026-09-1620260916
2026-09-1720260917
2026-09-2120260921

The 16 parties are the distinct submitting and tasked names together: AE, AE/JBMDL, AE/JBMDL/PM, AE/PM, CT, CT/PM, Carbon Recall Chattanooga, DAVID BOLAND, INC, Dobco, Inc., JBMDL, JBMDL/AE, PM, Richard Group LCC, Schweitzer Engineering Laboratories, Inc., USACE DB, and Willdan.

The 10 disciplines are Architectural, Civil, Controls / SCADA, Cybersecurity, Electrical, General, Mechanical, Other, Quality / Inspection, and Structural.

The 10 issue types are Compliance Requirement, Constructability, Design Clarification, Drawing Conflict, Inspection / Quality, Interface Coordination, Material Requirement, Missing Information, Scope Gap, and Specification Clarification.

Section 4Relationships

Each fact row points at exactly one date, one submitting party, one tasked party, one discipline, and one issue type. Submitting and tasked parties share dim_party.

Fact columnDimensionMeaning
date_keydim_dateDate the RFI was received
from_party_keydim_partyParty that submitted the RFI
tasked_party_keydim_partyParty tasked to answer the RFI
discipline_keydim_disciplineDerived engineering discipline
issue_type_keydim_issue_typeDerived issue type

Question, response, notes, status, document-update flags, and word counts stay on fact_rfi.

Section 5Diagrams

Star schema with fact_rfi in the center, linked to dim_date, dim_party, dim_discipline, and dim_issue_type.
fact_rfi at the center, with one link to each dimension.
Entity diagram listing the columns of dim_date, dim_party, dim_discipline, dim_issue_type, and fact_rfi.
Columns and keys for the fact and the four dimensions.