FATCA data dictionary — structuring sources→targets, transformations & versioning

Author: Alexander Fölsche, CPA (US), Wirtschaftsprüfer (Germany), Swiss Licensed Audit Expert

A tight data dictionary keeps FATCA reporting predictable and debuggable. This guide shows how to structure source→target mappings, document transformations and validations, manage lineage and ownership, and version-lock each filing year.

Scope: Applicable to Form 8966 and local IGA portal schemas. Use this structure alongside your validation summary, submission receipts and correction evidence.

1) What a reviewer expects to see

  • One table per filing year and schema version, for example 2025-IGA-CH-v1.2 or 2025-IRS-8966-v1.0.
  • Every target field listed with data type, null policy, validation rules and owner.
  • Source lineage, including system, table, column, extraction logic and transformations.
  • Change log with rationale, approver and effective date.
  • Examples and edge cases for tricky fields such as country, TIN, GIIN, FX and reporting thresholds.

2) Recommended columns for the data dictionary

Column Purpose / examples
Target Field Exact field name in Form 8966 or the local portal, such as ReportingFIName or AccountNumber.
Target Type / Length String(70), Integer, Decimal(18,2), Date(YYYY-MM-DD).
Null Policy Required, optional or conditionally required, with the relevant rule noted.
Source(s) System.Table.Column, multiple sources if federated, join keys and filters.
Transformation Trim, upper, ISO-3166 mapping, TIN normalization, FX conversion with source and rate date.
Validation Rules Regex checks, allowed lists, country rules and cross-field checks, such as GIIN required if status = X.
Owner / Steward Role or named function accountable for field quality.
Examples & Edge Cases Two to three realistic examples, tricky cases and expected outputs.

3) Good transformation notes — copy-paste patterns

COUNTRY_CODE := ISO3166_ALPHA2(trim(upper(Source.CountryName)))
```

GIIN := upper(trim(Source.GIIN))  // validate: <6 alnum>.<5 digits>.<2 alnum>.<3 digits>
TIN_STORE := digits_only(Source.TIN)
TIN_DISPLAY := fmt_tin(TIN_STORE, type)
ACCOUNT_BALANCE_USD := round(Source.Balance * FX_RATE("ECB", ReportDate, Source.Currency, "USD"), 2)
REPORTABLE_FLAG := IF(BALANCE >= THRESHOLD(country=model), "Y", "N")
```

4) Versioning and change control

  1. Freeze per year: keep one dictionary version for each filing year and portal schema; avoid silent edits after sign-off.
  2. Change log: document date, field, old→new, reason, approver and impact, including whether a re-run is required.
  3. Reference builds: store the exact export job version or hash that produced the filing.
  4. Cross-links: validation summary and submission receipts should reference the same dictionary version.

5) Example rows for typical fields

Target Field Sources Transformation Validation Owner
AccountNumber Core.Accounts.AcctNo Left-pad to 12; remove spaces Unique per year; not null IT / Data
EntityTIN KYC.Tax.TIN, KYC.Tax.TINType digits_only; display per type Exactly 9 digits; type in {SSN, ITIN, EIN} Tax Ops
EntityGIIN GIIN.Master.GIIN Upper; pattern check; monthly match evidence path Format and match result logged Tax Ops
AccountBalanceUSD DW.Balances.Amount, Currency ECB rate at year-end; round to 2 decimals; store FX source/date Non-negative; FX source/date present Finance

6) Governance and evidence

  • Owner map: each field has a steward; issues route to the owner with ETA.
  • Validation summary: counts by rule, pass/fail status and maker-checker sign-offs.
  • Dossier links: dictionary PDF/CSV, validation summary, receipts and corrections log should share the same version ID.
  • Retention: store the dictionary and exports under your records schedule, for example 7 years.
Data dictionary starter template
Template with target fields, lineage, transformations, validations, ownership and change log.

Related reading