SQL & Contract Data Modeling

The contract data model

A working contract-intelligence data model has roughly six core entities. Different platforms name them differently; the shapes are the same.

-- Suppliers — canonical entities (after dedup / normalization)
suppliers (
  supplier_id, canonical_name, parent_supplier_id,
  country, tax_id, industry, first_seen_at
)

-- Documents — every ingested file
documents (
  document_id, customer_id, source_uri, file_hash,
  doc_class, ingested_at, page_count
)

-- Agreements — logical contracts. One agreement = many documents
-- (MSA + amendments + SOWs all roll up to one agreement)
agreements (
  agreement_id, customer_id, supplier_id,
  agreement_type, effective_date, current_term_end,
  parent_agreement_id, status
)

-- Document → agreement linkage (M:N)
document_agreement_links (
  document_id, agreement_id, role  -- master | amendment | sow | exhibit
)

-- Obligations — extracted commitments. One agreement → many obligations
obligations (
  obligation_id, agreement_id, obligation_type,
  party_obligated, value, currency, due_at, status,
  confidence, extracted_from_document_id, extracted_at
)

-- Spend — actuals from invoices / POs joined to agreements (sometimes)
spend (
  spend_id, customer_id, supplier_id, agreement_id,
  invoice_id, amount, currency, accrued_at,
  spend_category
)

Key modeling decisions:

• Document vs agreement. A document is the physical PDF; an agreement is the logical commitment that may be made of many documents. Most analytical questions are about agreements; debugging is at document level.
• Supplier canonicalization. "Acme Corp," "ACME Corporation," "Acme Corp Inc.," "Acme" might all be the same supplier. The canonical_name in the suppliers table is the resolved version; raw extractions point to the canonical via supplier_id.
• Parent-supplier hierarchy. Acme Cloud and Acme Datacenter are subsidiaries of Acme Holdings. Roll-up queries need the parent.
• Obligation status. "Pending," "fulfilled," "overdue," "waived." Status changes over time — sometimes need temporal modeling.

Entity resolution

The hardest data-modeling problem in contract intelligence. Two of your customer's contracts both reference "Acme Corp" — are those the same supplier? Probably. But maybe one is "Acme Corp (UK)" and the other is "Acme Corp (US)," which for tax purposes are distinct entities.

Techniques

• Exact match on tax_id (best when present), otherwise canonical_name. Resolves the easy 60%.
• Fuzzy match on name (Levenshtein, Jaro-Winkler, embedding similarity). Resolves another 25% with confidence.
• Customer-supplied mapping. The customer's procurement team often has an internal supplier master. Use it.
• Manual review. The hard residual goes to HITL or to the customer's data team.

The model

Don't merge silently. Store raw extracted names + resolved canonical IDs, with confidence. If a merge turns out to be wrong, you can re-resolve without re-extracting.

supplier_name_resolutions (
  raw_supplier_name,        -- what was in the document
  resolved_supplier_id,     -- canonical
  resolution_method,        -- exact_tax_id | fuzzy_name | customer_map | manual
  confidence,
  resolved_at,
  resolved_by               -- model_version or analyst_id
)

Obligations & renewals

The renewal query

"What's renewing in the next 90 days?" — the single most-asked question in contract intelligence.

SELECT
  a.agreement_id,
  a.supplier_id,
  s.canonical_name,
  a.current_term_end,
  a.current_term_end - CURRENT_DATE AS days_until_renewal,
  -- Find the renewal-clause obligation
  o.value AS renewal_notice_days,
  a.current_term_end - INTERVAL '1 day' * COALESCE(o.value::INT, 60) AS notice_deadline
FROM agreements a
JOIN suppliers s ON s.supplier_id = a.supplier_id
LEFT JOIN obligations o
  ON o.agreement_id = a.agreement_id
 AND o.obligation_type = 'renewal_notice'
WHERE a.status = 'active'
  AND a.current_term_end BETWEEN CURRENT_DATE
                              AND CURRENT_DATE + INTERVAL '90 days'
ORDER BY days_until_renewal;

The senior touch: notice_deadline (when the customer has to give renewal notice) is often more actionable than the renewal date itself. Customers who miss the notice window are locked in for another term.

The auto-renew trap

Many agreements auto-renew unless cancelled. Your data model needs to capture:

• Auto-renew yes/no
• Renewal term length (often differs from initial term)
• Notice period required to cancel
• Whether notice has been given (often manually tracked elsewhere)

Spend rollups

"How much are we spending with Supplier X?" sounds simple. It is not.

The traps

• Supplier hierarchy. Roll up subsidiaries to the parent or not? Depends on the customer's procurement org.
• Currency. Multi-currency totals require FX rates at a specific date. Which date? Invoice date? Accrual date?
• Time alignment. Fiscal year vs calendar year. Customer's fiscal year might end in July.
• Off-contract spend. Spend on a supplier without a corresponding agreement — often the most interesting line item.
• Pass-through vs primary. A staffing agency invoices for contractors; is that "spend with the staffing agency" or "spend on contractor labor"?

The spend-by-supplier query

WITH supplier_hierarchy AS (
  -- Recursive: find the root parent for each supplier
  SELECT supplier_id, supplier_id AS root_id FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, h.root_id
  FROM suppliers s JOIN supplier_hierarchy h ON s.parent_supplier_id = h.supplier_id
),
spend_in_fy AS (
  SELECT
    sp.supplier_id,
    sp.amount * fx.rate_to_usd AS amount_usd
  FROM spend sp
  JOIN fx_rates fx
    ON fx.from_currency = sp.currency
   AND fx.rate_date = DATE_TRUNC('month', sp.accrued_at)
  WHERE sp.accrued_at >= '2025-07-01'  -- customer FY start
    AND sp.accrued_at <  '2026-07-01'
)
SELECT
  parent.canonical_name AS parent_supplier,
  SUM(sf.amount_usd) AS spend_usd_fy
FROM spend_in_fy sf
JOIN supplier_hierarchy h ON h.supplier_id = sf.supplier_id
JOIN suppliers parent ON parent.supplier_id = h.root_id
GROUP BY parent.canonical_name
ORDER BY spend_usd_fy DESC;

SQL patterns you'll write a hundred times

• "Latest extraction per document" — partition by document_id, order by extracted_at desc, take rank=1.
• "Documents that should belong to an agreement but don't" — anti-join, useful for finding orphaned amendments.
• "Agreements without a parent MSA" — SOWs floating without a master.
• "Obligations missing required fields" — quality check.
• "Spend with no matching agreement" — off-contract spend, often the customer's biggest discovery.
• "Agreements expiring in next N days, segmented by notice-window status" — the renewal pipeline.

Each of these is the kind of question a customer's procurement analyst will want to ask. Write them as dbt models with named columns; their analysts will reuse and extend them.

Fuzzy joins

Joining contracts (with extracted supplier names) to the customer's existing supplier master (cleaner names) is fuzzy by nature.

Approach

-- Requires pg_trgm extension
SELECT
  e.raw_supplier_name,
  s.canonical_name,
  similarity(e.raw_supplier_name, s.canonical_name) AS sim
FROM extracted_supplier_names e
CROSS JOIN LATERAL (
  SELECT canonical_name
  FROM suppliers
  ORDER BY canonical_name <-> e.raw_supplier_name
  LIMIT 5
) s
WHERE similarity(e.raw_supplier_name, s.canonical_name) > 0.65;

At BigQuery / Snowflake scale, prefer pre-computed embeddings + vector similarity, or LLM-as-judge on a candidate set generated by trigram or n-gram.

Interview probes

✓ Check yourself

Answer from memory before opening each one.

1. The customer's legal team counts 9,000 contracts and your table shows 14,000 rows. Without any error in either number, what explains it, and which table answers their question?

   Show answer

   Documents are physical files, while an agreement is the logical commitment, and master agreements, amendments and statements of work all roll up to one. Count agreements for their question and use documents for debugging. See the contract data model.

2. Three months in you discover that "Acme Corp (UK)" and "Acme Corp (US)" were merged by mistake. What did the design store that makes undoing the merge cheap?

   Show answer

   The raw extracted name, the resolved supplier identifier, the method used, a confidence and who or what resolved it. You re-resolve from the raw names without re-extracting any document. See entity resolution.

3. A report says an agreement renews in 45 days, and the extracted notice period is 60 days. What should the customer be told? What does the query assume when no notice obligation was extracted?

   Show answer

   The notice deadline has already passed, so they are probably locked into another term. The query falls back to 60 days when nothing was extracted, which is a silent assumption; flag those rows instead of treating the default as fact. See obligations and renewals. The matching practice problem is SQL over contract data.

4. Before a spend-by-parent-supplier number is trusted, name three decisions to settle with the customer.

   Show answer

   Whether subsidiaries roll up to the parent, which date sets the exchange rate, and which fiscal year applies. Pass-through spend, such as a staffing agency invoicing for contractors, is a fourth. Each changes the total. See spend rollups.

5. A $20,000 invoice is dated 3 September for a supplier whose only agreement ended on 31 August. On-contract or off-contract, and what does the join need to see?

   Show answer

   Off-contract, because no agreement covered that date. The join must compare the spend date with the agreement's effective and end dates, not just match on supplier. This case is usually preventable, so report it separately from suppliers with no agreement at all. See SQL patterns you'll write a hundred times.

6. You must fuzzy-match 20 million extracted names against a 300,000-row master. What changes from the trigram query in the chapter?

   Show answer

   Comparing every name to every candidate does not scale. Generate candidates cheaply, with trigram or n-gram buckets or precomputed embeddings, then score the short list, using a model as judge for the ambiguous ones. Send the residue to review. See fuzzy joins.

Takeaway

• Model documents and agreements separately, because analytics asks about agreements and debugging happens on documents.
• Entity resolution layers tax identifier, fuzzy name, customer-supplied mapping and manual review, and stores raw name, resolved identifier, method and confidence so merges can be undone.
• The notice deadline matters more than the renewal date, and auto-renewal needs a flag, term length, notice period and whether notice was given.
• Spend rollups depend on four choices: supplier hierarchy, exchange rate date, fiscal year and pass-through handling.
• Off-contract spend needs an agreement active at the date of the spend, and is often the customer's biggest discovery.
• Write recurring queries as named dbt models so the customer's analysts can reuse and extend them.

Next is getting this data into the customer's warehouse, systems and dashboards. → Warehouse & Integration

Further reading

• pg_trgm — PostgreSQL documentation, accessed 2026. Similarity functions, the % and distance operators, and index support for fuzzy joins.
• WITH Queries (Common Table Expressions) — PostgreSQL documentation, accessed 2026. Recursive queries for supplier hierarchies, including the cycle caveat.
• Window Functions — PostgreSQL documentation, accessed 2026. Latest-per-group patterns and ranking.
• How we structure our dbt projects — dbt Labs, accessed 2026. Staging, intermediate and mart layers for models customers will extend.
• Splink — UK Ministry of Justice analytical services, accessed 2026. Probabilistic record linkage at scale, for the entity resolution problem.