Implementation details

Monthly Treasury Statement (MTS)

Status: collection, lakehouse ingestion, queries, and export tools are implemented. Dashboard publication is a separate activity.

Publisher: U.S. Treasury, Bureau of the Fiscal Service. The pipeline uses the Fiscal Data API endpoints mts_table_1 through mts_table_9 under https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/mts/. See the source overview and individual table guides for subject matter and field definitions.

Data flow

1. Treasury Fiscal Data API → Python downloader running in Lambda or locally.
2. Downloader → nine staged JSON files and one manifest per report month.
3. Staged files in Amazon S3 → AWS Glue validation and type conversion → nine Iceberg tables in Amazon S3 Tables.
4. Amazon Athena → CSV exports or a JSON Lines bundle → optional local PostgreSQL analysis.
5. A separately published Tableau Public workbook → embedded dashboard on the website.

The export and local database tools support analysis. The repository does not establish which export or database revision feeds the published Tableau workbook.

1. Collect and stage a report

The downloader selects a month-end record_date and requests each of the nine tables with that exact date filter. It requests up to 1,000 rows per page, follows pagination up to a 20-page limit per table, and checks that every row belongs to the selected report and the combined count matches the API's reported total. Empty or changing results fail the download.

Latest-report mode discovers the newest date from Table 1 and requires it to match the previous calendar month's end. Historical mode accepts inclusive start and end dates and processes the month ends in that range, oldest first.

Rows are combined into one JSON object per table, retaining source labels, types, and formats and adding the report date and endpoint URL. JSON is serialized with sorted keys and compact spacing. These are repackaged source records, rather than original HTTP response files.

mts/2026/08/
  mts_table_1.json
  ...
  mts_table_9.json
  manifest.json

Each manifest lists the nine file keys, SHA-256 hashes, and row counts. In S3 mode the manifest is written after the table files; identical content is skipped on reruns. The local mode writes the same report structure to disk. The hashes identify the serialized staged files, not the original API responses.

2. Validate before loading

The Glue job discovers manifests beneath a selected mts, mts/YYYY, or mts/YYYY/MM prefix and processes them in chronological key order. It loads existing staged months; it does not download missing ones.

• Require the expected manifest path and exactly the nine table files for the report month.
• Verify staged bytes against manifest hashes, then check nonempty row counts against both the manifest and payload metadata.
• Check report dates, every row's column set, and declared metadata types against explicit schemas reviewed against the August 2026 feed.
• Validate every value conversion and check all target schemas before replacing any table's report data.

Validation runs for all nine source tables before the report's data writes. Unexpected source or target schemas stop the job for explicit review. A source object larger than 32 MiB is rejected; processing holds one report's payloads in driver memory.

3. Convert types and record provenance

Ingestion does not aggregate rows, recalculate Treasury totals or net amounts, adjust for inflation, scale amounts, or join other datasets.

Each row also receives source_bucket, source_key, source_version_id, source_sha256, source_url, manifest_key, manifest_version_id, batch_id, transform_version, and ingested_at. The batch ID is the manifest's SHA-256 hash, transformation version is currently 1, and ingestion time is recorded in UTC. Version IDs are captured when available.

4. Store reports and apply corrections

The job creates missing Iceberg format v2 tables named mts_table_1 through mts_table_9, in the configurable silver_mts namespace by default. Each table is partitioned by record_date.

For each table, the job atomically replaces the selected report date and checks the resulting row count. This prevents duplicate reports on reruns and removes rows absent from a corrected report. Other report dates remain intact. Rerunning unchanged data still creates new table snapshots and ingestion timestamps.

There is no transaction across all nine lakehouse tables. A failure can leave a partially updated report or completed earlier months. Wait for the entire job to succeed before exporting or consuming the report. After resolving a failure, rerun the affected month or range; avoid concurrent writers during ingestion.

The staging bucket is private, encrypted, and versioned in the supplied template. Superseded object versions expire after 90 days. The downloader supplies hashes but does not pin file version IDs in its manifest. Replaced source files may therefore prevent replay from an old manifest, even though the loader records the versions it actually reads.

5. Query, export, and analyze

Athena queries the lakehouse through its catalog integration and configured workgroup. Query results are stored separately from the source tables in a private S3 results bucket. Queries do not write their results back into the lakehouse.

• Named CSV exports: the optional Glue export job queries all nine tables, selecting source fields and lineage without filtering, joining, or aggregation. It waits for all queries to succeed before copying the results to named CSV files. Copies are sequential, so publication of the nine files is not atomic.
• Local CSV workflow: Athena export scripts can copy CSV results into a new run folder. The local download helper fetches all nine files before invoking the combined PostgreSQL loader. The SQL loader replaces all nine destination tables in one transaction using client-side \copy; CSV column order must match its schema.
• JSON Lines bundle workflow: a separate tool exports selected tables into an offline bundle with schemas, row counts, checksums, and query IDs. Its PostgreSQL importer validates the bundle and destination schemas, then replaces the selected tables in one transaction. Decimals remain exact and missing values retain their distinction from text.

Each Athena query reads its own table snapshot. These exports do not guarantee a common snapshot across nine tables; keep ingestion idle while exporting and use only completed exports.

The Net Income dashboard embeds a separately published Tableau Public visualization of Table 4 net receipts. Workbook calculations, filters, joins, formatting, and refresh history are not available in this repository for verification. The ingestion rules documented above do not certify the workbook's transformations.

6. Refresh behavior and interpretation

The staging template defaults to a configurable EventBridge download schedule on the 20th of each month at 18:00 UTC. Glue ingestion is manually invoked, and exports and dashboard publication are separate steps. A successful scheduled download does not establish that the website's dashboard has refreshed.

The table guides describe an August 2026 reference snapshot. Check dashboard coverage independently. Report dates, ingestion timestamps, and publication dates describe different events.

MTS contains headings, detail lines, subtotals, totals, and multiple period measures. Summing across hierarchy levels can double-count amounts; summing successive fiscal-year-to-date values can repeatedly count the same activity. Choose the intended period, measure, and hierarchy level using the table guides. The implemented pipeline does not link spending to legislation or establish policy causation.

Future data sources

Census data is a possible future source; no Census pipeline is implemented or described here. No specific Census dataset, endpoint, geographic coverage, or update schedule has been selected for this page.

As sources are added, each will receive its own section documenting the publisher and selected dataset, coverage and refresh schedule, collection process, validation rules, transformations, provenance, correction handling, and downstream uses. Source-specific limitations will be documented alongside those rules.

Any future analysis combining sources will also need to explain its join keys, geographic and time alignment, units, and treatment of unmatched records. MTS processing rules should not be assumed to apply to another source.

Shared website delivery

The website consists of static HTML and CSS. The hosting template serves an existing private S3 bucket through CloudFront using Origin Access Control and redirects viewer HTTP requests to HTTPS. Data processing and website publishing are separate operations; loading a table does not regenerate these pages or publish a Tableau workbook.

Explore government data sources · About the project