Daily federal award releases reconciled into persistent history.

Daily federal contract award releases are safe to append as an immutable observation ledger. They are not safe to append and aggregate as though every row were a different award or a new financial action. Preserve release membership, deduplicate normalized content states, and select one latest-observed row per award_id before summing cumulative balances.

The four WebTruffle releases available at this article's August 31, 2026 research cutoff contain 101,389 award-release rows but only 82,743 unique awards. They include 18,646 repeated appearances. Summing total_obligated_amount_usd across every appearance produces $202,845,310,918.86; selecting the latest observed row for each award produces $173,612,813,504.62. The $29,232,497,414.24 difference is older repeated balance—not additional spending.

There is a second, less obvious failure. Of 864 published updated observations, 333—38.54%—were not present in the immediately preceding public release. Comparing only today's CSV with yesterday's CSV cannot recover those transitions. The defensible comparison is today's award state against the last state ever observed for that award_id.

The downloadable Python and DuckDB recipe pins all four manifests and award files, checks the stable 66-field schema, reconstructs every published change_type, creates a latest-observed view, measures the cumulative-balance trap, and exports deterministic evidence with a provenance receipt.

Combine federal contract award releases: the short answer

Use this sequence:

1. Pin every release manifest by tag, byte count, and independently recorded SHA-256.
2. Verify each manifest before trusting the award-file hash it declares.
3. Require the same schema version and exact 66-field award header in every release.
4. Load all source fields as text and add manifest-level release metadata separately.
5. Retain one immutable release-membership row for every award appearance.
6. Key award identity by the published award_id, not PIID, recipient name, or a homemade composite.
7. Compare each appearance with the award's persistent last-observed state—even when it skipped one or more releases.
8. Treat (award_id, content_hash) as a published normalized content state, while retaining every release in which that state appeared.
9. Build a latest-observed view with one deterministic row per award_id; do not call it a complete current inventory.
10. Aggregate cumulative obligations, outlays, current value, or potential value only after selecting the intended award grain.
11. Inspect changed fields as well as amounts. In this corpus, 541 of 864 updates leave cumulative obligations unchanged.
12. Use USAspending transaction history when the question is about individual actions, action dates, or signed transaction obligations.
13. Export release checks, state reconciliation, overlap, amount checks, field-change counts, review rows, latest-observed states, and input/output hashes.

Start with the free US federal contract awards dataset when you need one published rolling edition. Use the single-release Python guide for verification, award-supplier joins, field presence, and changed-window analysis inside that edition. Use the USAspending API guide when you need source integration or transaction-level history. This guide owns the separate problem of maintaining state across editions.

Pin the four-release corpus

This worked example uses every public release in the repository at the August 31 research cutoff:

• August 25 (manifest): source last_modified_date window August 23–25;
• August 26 (manifest): window August 24–26;
• August 27 (manifest): window August 25–27; and
• August 29 (manifest): window August 27–29.

Each source window is a three-day inclusive rolling window, so the same award can appear in adjacent editions. Deduplicate by award_id for award-grain analysis: a release occurrence is not a new contract award. Observed state history is not a transaction ledger, and a cumulative balance is not daily spend. Missing remains unknown, not zero; absence does not prove deletion, termination, or closure. Latest-observed is not a complete inventory. Supplier totals must not be summed across releases.

These are deliberately overlapping three-day changed-record editions. Each row is the current USAspending prime-award summary observed for an award selected by source last_modified_date inside that edition's window. The row is not a transaction and the release is not a full active-contract snapshot.

The releases were generated on August 26, 27, 28, and 30 respectively. Keep edition_date, manifest generated_at, source coverage window, and download time as separate fields. A tag is a locator; the independently recorded byte count and digest identify the artifact expected by this workflow.

Verify each manifest before its award file

The trust order is root first, declarations second:

independently pinned manifest bytes + SHA-256
    → verified manifest declarations
        → verified schema and award CSV bytes + SHA-256
            → structural and semantic checks
                → analytical views and exports

The pinned inputs are:

All four manifests declare schema version 1.0. Their schema.json asset has SHA-256 12d2718f6f057d6a64dc09574652cfdb17625f7ab8434d6424f58013db73f1f6, and the award product retains the same ordered 66 fields. The recipe rejects an unexpected manifest, asset size, digest, schema version, header, row count, duplicate award key inside an edition, or release-level change-count mismatch.

Load source columns as VARCHAR. Award IDs, PIIDs, codes, hashes, dates, decimal strings, blanks, and source labels should not be silently coerced because a reader guessed their types. Cast one measure only when a query needs arithmetic.

Separate release membership, content state, and latest observed state

One flattened append cannot answer all history questions. Preserve at least these grains:

• Release: one verified manifest, its edition, source window, generation time, schema, source health, and file identities.
• Release membership: one appearance of one award_id in one release. Its natural key is (edition_date, award_id) for this corpus.
• Award content state: one distinct publisher-defined normalized state, keyed by (award_id, content_hash) and linked to every release membership where it appeared.
• Latest observed award state: one deterministic selected membership per award_id, ordered by release chronology. It is a derived view, not source evidence to overwrite history.
• Transaction: one USAspending award action with its own ID, action date, modification number, action type, description, and signed federal_action_obligation. This is a separate upstream grain.

The four releases reconcile as:

101,389 release-membership rows
 83,607 distinct award-content states
 82,743 latest-observed award states

The gaps are meaningful. There are 17,782 repeated memberships whose content hash matches the award's previous observed state, plus 864 memberships whose normalized content changed. Fifteen awards changed more than once, so 864 updated observations belong to 849 award identities.

Keep these tables or equivalent event-stream entities separately. If a consumer only wants the selected state, publish the view with a receipt that identifies the releases and selection policy used to derive it.

Why append and sum fails

Appending is correct for an observation ledger. The error begins when a release-membership table is queried as though it were an award table.

The same award summary can reappear because overlapping source windows select it again or because USAspending reports a later modification. The row's total_obligated_amount_usd is the award holder's accumulated balance at that snapshot. It is not the obligation action for the edition date.

For the exact four-release corpus:

sum over all 101,389 release rows          $202,845,310,918.86
sum over 82,743 latest-observed awards     $173,612,813,504.62
older repeated balances included in append  $29,232,497,414.24

The appended result is 16.84% greater than the latest-observed result. Neither number is total US federal procurement, fiscal-year spending, daily spending, or cash paid. They describe cumulative obligations on two explicitly different populations inside a bounded changed-record corpus. The comparison demonstrates a grain error; it does not estimate missing government spending.

The safe SQL pattern selects first and aggregates second:

WITH ranked AS (
  SELECT
    *,
    row_number() OVER (
      PARTITION BY award_id
      ORDER BY edition_date DESC, release_generated_at DESC
    ) AS state_rank
  FROM award_release_membership
)
SELECT
  count(*) AS awards,
  sum(try_cast(total_obligated_amount_usd AS DECIMAL(38, 2))) AS cumulative_obligations
FROM ranked
WHERE state_rank = 1;

SUM ignores nulls. Report the measure's nonblank and successfully parsed counts beside the total; never turn missing amounts into zero merely to make an aggregate easier.

Compare with persistent state, not only the previous file

A two-file diff asks, “Was this key in the immediately previous release?” A history tracker must ask, “What was the last accepted state for this key?” Those are different questions when releases are rolling changed-record windows.

Of the 864 updated observations:

• all 81 August 26 updates were present in the August 25 file;
• 166 of 365 August 27 updates were present in August 26, while 199 were not; and
• 284 of 418 August 29 updates were present in August 27, while 134 were not.

Overall, 333 updates skipped at least one public release membership. A left join to only the previous CSV cannot recover their prior content hash. Depending on implementation, it may misclassify the row as new or fail to produce a transition at all.

Use a persistent state table keyed by award_id:

for each verified release in chronological order:
    for each award row:
        prior = persistent_state[award_id]
        if prior does not exist: reconstructed_change = new
        else if prior.content_hash == row.content_hash: reconstructed_change = unchanged
        else: reconstructed_change = updated

        append immutable release membership
        register (award_id, content_hash) state if not already known
        replace persistent_state[award_id] with this accepted row

Do not remove a state merely because an award is absent from the next rolling release. The later file contains awards modified inside its own window, not a complete list of active awards. Absence means no membership in that edition under its successful published selection, not deleted, inactive, completed, terminated, or missing upstream.

Reconstruct new, unchanged, and updated

The persistent algorithm exactly reproduces the publisher's change_type across all 101,389 rows:

82,743 new first observations
17,782 unchanged repeated-content observations
   864 updated content observations
-------
101,389 release memberships

That equation is a strong acceptance test. It establishes that the release ordering, identity key, last-state lookup, and published content hashes were interpreted consistently for this corpus. It does not prove the upstream record is factually correct or complete.

Store both the publisher's change_type and the reconstructed value. A mismatch should quarantine the run. Do not silently recalculate a different label, because that would hide a changed publisher contract, an out-of-order load, a duplicate edition, a corrupted input, or a bug in the state machine.

Use deterministic release ordering. Here, edition_date is unique across the four inputs, and manifest generated_at is retained as a secondary checkpoint. In a production system, reject duplicate edition identities unless a versioned replacement policy explicitly resolves them.

Deduplicate content states without erasing history

content_hash is a publisher-defined digest of normalized award content. It is useful for recognizing that two release memberships carried the same normalized state. It is not an award identifier, a source-file hash, or evidence that the underlying federal claim is true.

The content-state table can retain:

award_id
content_hash
state_first_edition
state_last_edition
state_first_generated_at
state_last_generated_at
membership_count
the 66-field source state or a permitted immutable payload reference

For this corpus, 101,389 memberships collapse to 83,607 distinct (award_id, content_hash) states. The reduction is analytical, not destructive: membership rows still prove which state appeared in which release.

Do not deduplicate globally by content_hash alone. Two different awards can theoretically share identical normalized field content while remaining different source identities. Do not create award identity from PIID alone either; the published generated award_id is the product's award-grain key and encodes source identity more safely than a locally guessed composite.

Build a latest observed view, not a current inventory

The latest-observed view picks the most recent accepted membership for each award:

CREATE OR REPLACE VIEW award_latest_observed AS
SELECT * EXCLUDE (state_rank)
FROM (
  SELECT
    *,
    row_number() OVER (
      PARTITION BY award_id
      ORDER BY edition_date DESC, release_generated_at DESC
    ) AS state_rank
  FROM award_release_membership
)
WHERE state_rank = 1;

Name the view for what it is. It contains the latest state observed within these four releases for 82,743 awards selected by their rolling last_modified_date windows and the dataset's April 4, 2022 base-action cutoff. It is not:

• every federal contract award;
• every currently active award;
• every award modified since April 2022;
• a fiscal-year transaction ledger;
• a deletion-aware current snapshot; or
• complete recent DoD and USACE activity.

USAspending documents that ordinary procurement data are generally published within several days of an action, while Department of Defense and U.S. Army Corps of Engineers procurement data are publicly delayed by 90 days. Apply those source caveats to every “latest” label. (USAspending About the Data)

Sum cumulative obligations once

The release manifest defines total_obligated_amount_usd as the award holder's accumulated federal obligations at the source snapshot. USAspending separately defines an obligation as a binding agreement that may result in spending immediately or in the future, while an outlay is an actual payment. (USAspending data sources)

Three rules follow:

1. Across releases: select one intended state per award before summing its cumulative balance.
2. Across relationship rows: aggregate the award table before joining a one-to-many relationship that repeats award measures.
3. Across time: do not subtract two cumulative summaries and call the result a specific transaction without retrieving the action history.

A later cumulative obligation can rise, fall through deobligation, remain unchanged, or be corrected. The net difference between two accepted summaries is a useful review signal. It is not automatically one action, one action date, or one causal event.

Keep these measures separate:

• total_obligated_amount_usd: accumulated legal commitments reported for the award;
• total_outlay_amount_usd: accumulated payments reported for the award;
• current_total_value_of_award_usd: source current award value; and
• potential_total_value_of_award_usd: possible or ceiling value, not promised spending.

All are award-summary balances. None becomes a flow because it appeared in a daily file.

Inspect fields, not only amounts

An amount-only alert would miss most content updates in the corpus. Of 864 updated observations, 541—62.62%—leave total_obligated_amount_usd unchanged.

Selected field-change counts are:

These counts overlap: one transition can change several fields. A content hash tells you that normalized content differs; a field diff identifies what changed; neither explains why. Route review by decision impact—financial, timing, supplier, agency, classification, competition, set-aside, location, or descriptive change—and keep the prior and current values together.

Use transactions to explain award actions

The award CSV intentionally excludes transactions. When the question is “Which action changed this award?” use the official transaction grain.

USAspending's POST /api/v2/transactions/ endpoint accepts the generated award_id and returns transaction IDs, action dates, action types, modification numbers, descriptions, and federal_action_obligation, with pagination up to 5,000 results per page. The official contract says generated award identifiers are preferred because they are effectively permanent. (USAspending transaction endpoint)

Retrieve and reconcile transaction pages when you need to:

• attribute a net summary change to one or more actions;
• measure obligations by action date or fiscal period;
• distinguish a new award action from a modification;
• preserve signed deobligations; or
• explain why an award's cumulative balance changed.

Do not replace the award history with transactions. Award summaries answer latest-holder-state questions; transactions answer action-flow questions. Store both with their keys and reconcile the signed transaction history to the cumulative summary under a documented cutoff and tolerance.

For source collection, archive files, request partitioning, pagination, and transaction retrieval, continue with the USAspending API guide.

Rebuild supplier views from the selected award grain

Each public edition also publishes supplier and award-supplier products. Do not append edition-level supplier rollups and then sum them. Supplier totals inherit the same repeated-award problem, and supplier membership can change as award summaries change.

Choose the analytical question first:

• Latest-observed supplier view: join or derive supplier attributes from the latest-observed award states, then aggregate each award_id once.
• Supplier content history: preserve supplier observations and their source-scoped identity changes separately.
• Award-recipient relationship history: keep one relationship observation per release and award, without adding award measures across repeated memberships.

Use supplier_id for the dataset's normalized supplier grain and retain its identifier scheme. Use award_id for award-grain joins. Keep UEI, CAGE, PIID, and recipient name as scoped attributes. Names can vary or collide; PIIDs are not globally sufficient award identities.

The companion recipe focuses on the awards CSV because that is enough to prove cross-release state and cumulative-balance semantics. Extend it to supplier products only after pinning their manifests, schemas, hashes, and grain checks with the same discipline.

Export reconciliation evidence and provenance

The recipe writes these outputs:

Write the provenance receipt last, after every other output has been closed and hashed. Record at least:

• script name and version;
• Python and DuckDB versions;
• run timestamp and input cutoff;
• every release tag, source window, manifest URL, byte count, and SHA-256;
• every award CSV URL, byte count, SHA-256, and row count;
• schema version and ordered header hash or exact header;
• persistent-state and latest-selection policies;
• review limit and sort order;
• all acceptance equations; and
• each output's rows, bytes, and SHA-256.

Re-running the same code with the same verified inputs and flags should produce byte-identical CSVs and the same analytical content. If the receipt includes a runtime timestamp, distinguish that nondeterministic envelope field from the deterministic evidence fields and output hashes.

Turn the worked example into an incremental state machine

A production tracker should not redownload all history forever. Preserve immutable release evidence and maintain transactional state:

1. Discover a candidate release, but do not trust a moving latest alias as evidence.
2. Refuse an edition already accepted unless a controlled replacement workflow is active.
3. Download the manifest under byte and time limits; verify its independently registered digest.
4. Verify the schema, source health, award file, declared rows, exact header, and unique in-release award IDs.
5. Stage every row as text with its release foreign key.
6. For each award_id, read the last accepted state under a transactionally consistent snapshot.
7. Reconstruct new, unchanged, or updated; require agreement with the published label.
8. Insert immutable release membership and any new (award_id, content_hash) state.
9. Advance latest-observed pointers only after every row and release-level equation passes.
10. Commit the release, state updates, output metadata, and checkpoint atomically—or use an idempotent sequence whose replay cannot duplicate membership.
11. Publish derived outputs from the accepted checkpoint, never from a partially loaded edition.
12. Retain rollback metadata without deleting the immutable evidence that explained a prior delivery.

Concurrency matters. Two workers must not accept the same edition or update one award's state out of order. Use an edition uniqueness constraint and a transaction, lock, compare-and-swap version, or equivalent serializable mechanism appropriate to the storage system.

Late or corrected releases need an explicit policy. Never silently insert an older edition after newer state and recalculate history without versioning the result. Quarantine it, determine the intended chronological and supersession semantics, then publish a new derived-history version with a visible reason.

Limitations and interpretation boundaries

• The corpus contains four public changed-record releases available at the August 31, 2026 cutoff. It is not enough history to establish seasonality, source reliability, or a complete longitudinal federal-contract database.
• Each edition is selected by an overlapping three-day USAspending last_modified_date window. It is not a full active-award snapshot and not a transaction file.
• The latest-observed view contains awards seen in this corpus. An award absent from a later release has not been shown to be deleted, inactive, completed, cancelled, or terminated.
• content_hash distinguishes publisher-normalized content states. It does not prove source truth, legal reuse, completeness, or causal business events.
• change_type=updated means normalized summary content changed relative to persistent prior state. It does not mean obligations changed; 541 of 864 updates here did not change cumulative obligations.
• Cumulative obligations, outlays, current value, and potential value are different award-summary measures. They are not edition-date flows and must not be added across repeated states.
• A difference between summary balances is a net observation. Retrieve transaction history before attributing the difference to actions or dates.
• The dataset excludes transactions, IDVs, subawards, opportunities, and awards before its documented base-action cutoff. It does not support government-wide or fiscal-year totals by itself.
• USAspending source coverage and reporting rules change over time. Recent DoD and USACE procurement is subject to a documented 90-day public delay.
• Missing values remain unknown. A matching release hash proves file identity, not upstream completeness or factual accuracy.

Federal contract award history checklist

• [ ] Every accepted release has a verified independent manifest digest.
• [ ] Every schema and award file matches its declared bytes and SHA-256.
• [ ] The exact 66-field header and one-row-per-award_id in-release grain pass.
• [ ] Source fields remain text until a named query casts them.
• [ ] Release, membership, content-state, latest-observed, and transaction grains remain separate.
• [ ] State comparison uses the last observed award state, not only the immediately previous file.
• [ ] Published and reconstructed change_type values reconcile exactly.
• [ ] Every release row is retained even when its content state repeats.
• [ ] Latest-observed selection is deterministic and labeled with its corpus cutoff.
• [ ] Cumulative amounts are aggregated once per selected award state.
• [ ] Null and parse coverage accompanies every financial aggregate.
• [ ] Field changes are inspected even when obligations remain unchanged.
• [ ] Supplier totals are rebuilt from the selected award grain.
• [ ] Transaction history is used for individual award-action questions.
• [ ] Outputs include row counts, sort rules, input identities, hashes, and limitations.
• [ ] Release acceptance and state advancement are atomic or safely idempotent.

Frequently asked questions