Load government contract data and keep warehouse updates repeatable.

To load government contract data into Snowflake, define the row key, stage one identified delivery, validate it, and merge only newer observations into your current table. Keep a receipt for each delivery so a dashboard row can be traced back to its source file.

This guide includes a SQL worksheet and small CSV fixtures from two real public federal-award releases. You will load two award summaries, apply a later delivery containing three, then replay both deliveries. One selected award's reported obligated balance changes from $55,000 to $27,500. A correct load preserves the newer value when the older file is replayed.

The source files and local Python reference checks were verified while preparing this guide. The SQL has not been executed in a Snowflake account. The download includes the queries and expected results for checking it in your own sandbox; the local checks do not substitute for that execution.

Start with one row per award

“Government contract data” can mean opportunity notices, award summaries, contract actions or supplier relationships. These have different keys and update rules. Joining them before declaring their grain makes duplicate counts and misleading spending totals easy to produce.

Our example uses the WebTruffle public U.S. federal contract awards dataset, derived from USAspending. The logical key is (source, award_id), with one prime award summary per source-generated award identifier. The source namespace prevents an identifier from another feed being treated as the same record.

| Field | Warehouse type | Meaning in this example | | --- | --- | --- | | source, award_id | VARCHAR | Together identify the award summary | | recipient_name | VARCHAR | Reported recipient name; no company resolution implied | | total_obligated_amount_usd | NUMBER(38,2) | Cumulative obligated balance reported for this award | | source_url | VARCHAR | Link to the underlying award page | | batch_id | NUMBER | Approved delivery order: 1, then 2 |

The amount is a balance, not an individual obligation action. Appending both editions and summing them would count multiple observations of the same award. Even the difference between these two balances should not be presented as a complete period-spending measure. Use transaction-level data when that is the analytical question.

For the larger public dataset, field meanings and Python loading workflow, see load U.S. federal contract awards with Python. This article concentrates on Snowflake staging and update behavior.

Inspect two pinned releases

The fixtures select five observations from the August 25 release and August 26 release. The original CSVs contain 17,945 and 17,920 rows respectively. Both full-file SHA-256 hashes were checked against the existing pinned history recipe before selecting these cases.

| Fixture | Selected rows | Purpose | | --- | --- | --- | | batch-1.csv | 2 | Establish the initial current table | | batch-2.csv | 3 | Change one balance, retain one balance, introduce one award | | expected-current.csv | 3 | Expected state after both deliveries and their replays |

The changed award is CONT_AWD_12024B26M0522_12C2_12024B24T7051_12C2. Its balance is $55,000 in the first edition and $27,500 in the second. That is an observed file difference; the example does not infer the legal or operational reason for it.

These are deliberately selected cases, not a representative procurement sample. Each CSV is a six-column projection of the original 66-column file. provenance.json records the original URLs and hashes, sample hashes, counts, selection rule and source terms.

Our batch numbers express the reviewed order of these two editions. They are not source modification timestamps. For another feed, establish how corrections, backfills and same-day reissues are ordered before using a numeric sequence in a merge.

Load the sample in Snowflake

Select an existing database, schema and warehouse in a Snowsight SQL worksheet. Your role needs permission to create the temporary tables and views used by the example. Run the downloaded worksheet.sql in one session, outside any existing transaction. It creates session-scoped demo objects and embeds the exact CSV fixture values as SQL inserts, so no external stage or API credentials are needed.

The worksheet contains setup, four batch applications and verification queries. Its setup resets the temporary demo tables; run setup once, not between deliveries. For step-by-step inspection, the same statements are separated into 01-setup.sql, 02-apply.sql and 03-verify.sql.

The raw staging table deliberately holds text:

CREATE OR REPLACE TEMP TABLE wt_sf_raw (
  batch_id VARCHAR,
  source VARCHAR,
  award_id VARCHAR,
  recipient_name VARCHAR,
  total_obligated_amount_usd VARCHAR,
  source_url VARCHAR
);

Validation checks required fields, duplicate keys, expected row counts and amount syntax before promotion. The amount contract allows signed values with up to two decimal places and rejects missing, malformed or out-of-range amounts. It does not silently turn unknown values into zero or round extra decimal places.

wt_sf_receipts holds delivery provenance. wt_sf_current holds the latest observed award summaries. wt_sf_applied records each accepted batch once. The last table is an acceptance ledger, not a full history of failed and successful execution attempts.

Merge only a validated batch

A MERGE statement alone does not establish uniqueness. Snowflake documents that duplicate source rows can produce multiple inserts when the key is absent from the target. The example rejects repeated source keys and duplicate target keys before merging, alongside enabling ERROR_ON_NONDETERMINISTIC_MERGE. Snowflake MERGE reference.

The central update rule is simple:

MERGE INTO wt_sf_current t
USING (
  SELECT * FROM wt_sf_typed
  WHERE batch_id = $batch_to_apply
) s
ON t.source = s.source AND t.award_id = s.award_id
WHEN MATCHED AND s.batch_id > t.batch_id THEN UPDATE SET
  recipient_name = s.recipient_name,
  total_obligated_amount_usd = s.total_obligated_amount_usd,
  source_url = s.source_url,
  batch_id = s.batch_id
WHEN NOT MATCHED THEN INSERT
  (source, award_id, recipient_name,
   total_obligated_amount_usd, source_url, batch_id)
VALUES
  (s.source, s.award_id, s.recipient_name,
   s.total_obligated_amount_usd, s.source_url, s.batch_id);

Run this through the complete downloaded apply block, which includes validation and receipt recording. A same-batch row with conflicting current values raises an error. A newer batch advances the row's provenance even when its projected business fields are unchanged.

The apply block wraps validation, promotion and acceptance recording in a transaction and rolls back on an exception. Setup DDL is outside that transaction because Snowflake DDL can implicitly commit an active transaction. Transaction rules and exception handling.

This design assumes immutable verified deliveries and one serial writer. Comparing to the current row cannot detect every rewritten historical batch after a newer batch has arrived. In production, retain immutable file receipts and reject changed bytes under an already accepted delivery identity before staging.

Check the update and replay results

The complete worksheet applies batches in the order 1, 2, 2, 1. If you use the separate files, set the batch before running each complete apply block:

SET batch_to_apply = 1;
-- Run 02-apply.sql, then repeat with 2, 2 and 1.

| Step | Expected current awards | Changed award balance | | --- | --- | --- | | First delivery | 2 | $55,000 | | Later delivery | 3 | $27,500 | | Later delivery again | 3 | $27,500 | | Older delivery again | 3 | $27,500 |

The final verification runs differences in both directions between the current table and the expected second batch. Both queries should return zero rows, with three current awards and two accepted batches. Inspect the provenance join too: all three selected current rows should point to batch 2.

For a local check of the fixtures and intended policy, run python3 -B check_example.py. Eight checks cover source-sample receipts, the real balance decrease, expected state, replays, duplicate new keys, conflicting same-version values, invalid amounts and missing keys. These execute Python reference logic, not Snowflake SQL.

There is intentionally no deletion on absence. These source releases are rolling extracts; a missing award is not evidence of cancellation or removal. An old delivery may add a previously unseen key, but cannot overwrite a key already observed in a newer delivery. The result is latest-observed state across accepted deliveries.

Replace inline staging with a file delivery

For scheduled procurement data, replace the five inline fixture rows with a controlled file ingestion step. Keep the same separation between landing a delivery and publishing its validated rows.

Before loading, verify the file hash, header, schema version, expected count and delivery identity. Land through an internal or external Snowflake stage, use explicit column mapping, and choose ON_ERROR='ABORT_STATEMENT' so a parsing error does not quietly produce a partial accepted batch. Snowflake's COPY INTO table reference documents the loading and error-handling options.

Do not feed the original 66-column CSV into this six-column schema. Build and version the projection first, or expand staging to match the original header. Preserve identifier strings, distinguish missing amounts from zero, and retain the raw asset alongside the receipt.

Move to persistent tables through a reviewed migration, then add serial scheduling, failure reporting and an explicit backfill procedure. Record the actual load time separately from source publication and edition dates. The general delivery architecture is covered in how to build a custom data feed.

Compare a native Snowflake data product

Before building a file pipeline, check whether a native data product already answers your question. Snowflake Public Data offers public datasets through Marketplace with a unified schema and automatic updates. That can reduce ingestion work when its coverage and model fit. Snowflake Public Data concepts.

For a “SAM.gov Snowflake” requirement, first specify whether you need opportunity notices, award summaries or contract actions. Verify the available product's actual tables, historical depth, update lag, identifiers, access terms and region in your account. A procurement-related listing does not establish that it contains all three grains.

Choose a native product when its existing schema and coverage satisfy your checks. Choose a file or managed delivery when you need a particular cross-source projection, acceptance contract or delivery schedule. This guide does not claim a WebTruffle Snowflake Marketplace listing or verified availability of a specific SAM.gov table.

Scope a scheduled procurement feed

Run the public example first. Then define the production requirement in terms a data engineer can verify: jurisdictions, record grain, stable keys, fields, history, delivery cadence, correction ordering and acceptance failures.

A useful initial request is: “We need award summaries in Snowflake, keyed by source and award ID, with daily deliveries, immutable receipts and a documented backfill policy.” Add your actual coverage and freshness requirements. This gives a pilot a concrete success condition beyond whether a CSV loads once.

Frequently asked questions