Technical Assessment: FY 2024 FRPP Public Dataset FY2024

Dataset Expectations (Requirements)

• REQ-EXP-01: Demonstrate familiarity with the FRPP MS public dataset and its scope limitations.
• REQ-EXP-02: Account for large, wide tabular data and geospatial attributes (lat/long, state, county).
• REQ-EXP-03: Use AWS-native services for ingest, cataloging, and query (S3 Glue Athena).
• REQ-EXP-04: Explain data access, licensing, and publication details for public distribution.
• REQ-EXP-05: Provide a scalable system design narrative for public real property analytics.

Dataset Overview

The Federal Real Property Profile Management System (FRPP MS) is the federal government’s centralized inventory of real property under the custody and control of executive branch agencies. Due to national security and other authorized exclusions, some FRPP data is not available to the public.

Downloads & Resources

• FRPP_Public_Dataset_FY24_07022025.xlsx (EXCEL)
• frpp_public_dataset_fy24_07022025.xlsx
• Data.json Metadata (Metadata Source)

Source: Harvested from GSA JSON sample csv data.

Scenario

You are supporting a public data analytics team that needs a minimal FRPP data lake for exploration, reporting, and future GenAI-assisted analysis. Your task is to propose a clean architecture and build a small, working subset using the FY 2024 FRPP Public Dataset.

Objectives (User Stories)

• US-OBJ-01: As a data engineer, I want the FRPP dataset ingested into S3 and cataloged in Glue so that it is queryable at scale.
• US-OBJ-02: As an analyst, I want Athena queries for utilization, asset status, and agency rollups so that I can answer oversight questions quickly.
• US-OBJ-03: As a platform owner, I want a lightweight API that can summarize FRPP results and optionally call an LLM so that downstream tools can reuse it.
• US-OBJ-04: As a reviewer, I want documentation of assumptions, data exclusions, and tradeoffs so that the analysis is transparent.

Scope

Type	Requirement	Description	Expectation Fit
Required	US-REQ-01	Ingest the FRPP Public Dataset (Excel/CSV) into S3 so it is available in the data lake. Cost details 💰	REQ-EXP-03
Required	US-REQ-02	Catalog FRPP data in Glue and query it in Athena, including a curated view for utilization and asset status.	REQ-EXP-02, REQ-EXP-03
Required	US-REQ-03	Provide a minimal API (Lambda or container) that returns FRPP summaries by agency, state, or utilization.	REQ-EXP-03
Required	US-REQ-04	Produce summary metrics (counts, square feet, replacement value) by agency, bureau, and state.	REQ-EXP-02
Required	US-REQ-05	Document public exclusions, missing values, and data quality handling assumptions.	REQ-EXP-01, REQ-EXP-04
Required	US-REQ-06	Provide an optional narrative summary using Amazon Bedrock (or an LLM) based on query output.	REQ-EXP-03, REQ-EXP-05
Required	US-REQ-07	Publish dataset metadata (field list, update frequency, and licensing) in the README.	REQ-EXP-04
Optional	REQ-OPT-01	Provide an OpenSearch index for metadata search.	REQ-EXP-03
Optional	REQ-OPT-02	Add a geospatial summary (e.g., CBSA or state rollups) or a map-friendly export.	REQ-EXP-02
Optional	REQ-OPT-03	Provide a simple UI or CLI client for FRPP queries.	REQ-EXP-03
Optional	REQ-OPT-04	Add CI checks or unit tests.	REQ-EXP-05

FRPP Fields (FY 2024)

Field list (CSV header) from the public metadata sample:

Reporting Agency, Reporting Agency Code, Reporting Bureau, Reporting Bureau Code, Real Property Unique Identifier, Using Agency, Using Agency Code, Using Bureau, Using Bureau Code, US/Foreign, Country Code, Country Name, State Code, State Name, County Code, County Name, City Code, City Name, Zip Code, Core-based Statistical Area, Core-based Statistical Area Code, Street Address, Installation Id, Installation Name, Sub Installation Id, Latitude, Longitude, Legal Interest Indicator, Legal Interest Code, Real Property Type, Real Property Type Code, Real Property Use, Real Property Use Code, Utilization, Utilization Code, Asset Status, Asset Status Code, Historical Status, Historical Status Code, Field Office, Field Office Code, Field Office Collocation, Field Office Collocation Code, Acres, Actual Sales Price, Age of Property, Reason Cannot Currently Be Disposed, Reason Cannot Currently Be Disposed Code, Cannot Currently Be Disposed of Date, CFO Agency, Congressional District, Consecutive Years Underutilized, Date Start of Underutilized, Determination to Dispose Date, Determine # of Contractors, Determine Number of Fed Contractors Code, Number of Federal Contractors, Determine # of Employees, Determine Number of Fed Employees Code, Number of Federal Employees, Disposition Date, Disposition Method, Disposition Method Code, Estimated Future Capital Expenditures, Estimated NPV (as of 6/11/2024), Excess Date, FASTA Disposal Exclusion, FASTA Disposal Exclusion Code, FASTA Disposal Exclusion Reason, FASTA Disposal Exclusion Reason Code, FiscalYear, Historical Capital Expenditures, Lease Annual Rent to Lessor, Leased Annual Maintenance Costs, Leased Annual Operations Costs, Lease Authority, Lease Authority Code, Lease Expiration Date, Lease Occupancy Date, Lease Start Date, Net Proceeds, Number of Days Excess, Number of Days Surplus, Number of Days Underutilized, Outgrant Indicator, Owned and Otherwise Managed Annual Operations Cost, Owned and Otherwise Managed Annual Maintenance Cost, Repair Needs, Replacement Value, Condition Index, Report of Excess Accepted Date, Report of Excess Submitted Date, Reduce the Footprint, Square Feet (Buildings), Square Feet Unit of Measure, SquareFeet Unit Of Measure Code, Structural Units, Surplus Declaration Date, Sustainability Status, Sustainability Code, Sustainability System Used Code, Sustainability System Used, Sustainability Square Footage, Sustainability Assessment Date, Unit Of Measure, Unit Of Measure Code, Year Asset Reported Underutilized, Year of Construction, Outgrant Indicator Value, Asset Height Range, Asset Height, Asset Height Range Above Mean Sea Level, Asset Height Above Mean Sea Level, Elevation Above Mean Sea Level, FOIA Exemption, FOIA Exemption Code, Statutory Citation

Sample Rows (Abridged)

COMMERCE,13,NATIONAL OCEANIC AND ATMOSPHERIC ADMINISTRATION.,14,AKM13201,COMMERCE,13,NATIONAL OCEANIC AND ATMOSPHERIC ADMINISTRATION.,14,UNITED STATES,840,UNITED STATES,02,ALASKA,020,ANCHORAGE,0710,ELMENDORF AFB,99506,,0,,5771000,NWS ANCHORAGE AK (ELMENDORF) CAMPUS INSTALLATION,001624,61.2601000,-149.6901000,Owned,G,Building,35,Laboratories,74,Unutilized,5,Report of Excess Accepted,C,Not Evaluated,5,,,,,,67,,true,AL,5,9/30/2020,,Yes,Y,0,Yes,Y,0,,,$0.00,,5/9/2018,Yes,Y,On Military Installation,MIL,2024,$0.00,,,,,,,,2,584,,1,709,N,$7,296.00,$0.00,,$808,630.00,,5/9/2018,,N,1,824,Gross Square Feet,G,,,,,,,,2020,1958,No,Height > 0 feet and <= 30 feet above ground level,,>319.70441 and <=349.70441,,319.70441,No FOIA exemption,10,

Suggested Architecture (Requirements)

• REQ-ARC-01: Use S3 for raw FRPP files and curated outputs.
• REQ-ARC-02: Use Glue for cataloging and optional ETL.
• REQ-ARC-03: Use Athena for SQL analytics and rollups.
• REQ-ARC-04: Provide an API endpoint via Lambda or a container with API Gateway.
• REQ-ARC-05: Use Bedrock (or an alternative LLM) for optional narrative summaries.
• REQ-ARC-06: Include OpenSearch for metadata search or explain why it is omitted.

API Requirements

• REQ-API-01: The system must expose a POST /frpp/query endpoint.
• REQ-API-02: The request body must accept agency (optional), state (optional), utilization (optional), and limit (optional).
• REQ-API-03: The response must include a summary, rows, sources, and status.
• REQ-API-04: If an LLM summary is enabled, include model and promptVersion in the response.

Example Request

{
  "agency": "COMMERCE",
  "state": "AK",
  "utilization": "Unutilized",
  "limit": 10
}

Example Response

{
  "summary": {
    "count": 3,
    "totalSquareFeet": 11966,
    "totalReplacementValue": 2565221
  },
  "rows": [
    {"realPropertyId": "AKM13201", "state": "AK", "utilization": "Unutilized", "squareFeet": 1824}
  ],
  "sources": ["s3://.../frpp_public_dataset_fy24_07022025.csv", "athena://query/123"],
  "status": "ok"
}

Optional LLM Summary Expectations (Requirements)

1. REQ-LLM-01: Run an Athena query based on filters or fixed templates.
2. REQ-LLM-02: Summarize result rows in a compact context block.
3. REQ-LLM-03: Call the LLM with a prompt that includes instructions, context, and the query intent.
4. REQ-LLM-04: If the LLM call fails, return a fallback response using raw data or a rule-based template.
5. REQ-LLM-05: Maintain prompt versions and document how they are selected.

System Design Exercise (Requirements)

• REQ-SYS-01: Provide a system design diagram or narrative for scalable FRPP analytics.
• REQ-SYS-02: Explain data flow, security boundaries, and operational concerns.
• REQ-SYS-03: Identify key tradeoffs and future scaling steps.

Evaluation Criteria (Requirements)

• REQ-EVAL-01: Explain architecture choices and tradeoffs clearly.
• REQ-EVAL-02: Show a clean ingestion, cataloging, and query flow.
• REQ-EVAL-03: Provide a simple, usable API interface with good defaults.
• REQ-EVAL-04: Demonstrate FRPP-specific analytics (utilization, asset status, agency rollups).
• REQ-EVAL-05: Include a README with setup, run steps, and assumptions.
• REQ-EVAL-06: Demonstrate awareness of metadata search and dataset licensing.

Submission (Requirements)

• REQ-SUB-01: Provide a public or private repo link.
• REQ-SUB-02: Include a concise README with setup and demo steps.
• REQ-SUB-03: Record a 3 to 5 minute video walkthrough or schedule a live demo.