Pipeline Builder for DD Data Ingestion: Connecting Bank-Internal Systems to the Ontology Layer

Foundry Ontology Design for Counterparty Risk Investigations — Article 001 in this sub-series — established that the ontology layer is the abstraction that insulates the analytical workflow from upstream schema churn. The ingestion layer is where that insulation is engineered. Pipeline Builder is Foundry’s primary surface for source-to-ontology projection; the design decisions taken inside the pipeline determine whether the ontology remains accurate as source systems change underneath it — and source systems will change, repeatedly, over the life of any DD engagement. Vendors release new versions. Schemas drift. Data-classification policies update. Acquired entities bring their own data stacks. The pipeline either absorbs each change without disrupting downstream Workshop applications and AIP-driven analyses, or it propagates the disruption upward and the institution rediscovers the same stitching problem the ontology was supposed to solve.

This article walks the production-grade pipeline patterns for DD ingestion. The four-layer bronze / silver / gold / ontology-projection pattern that the data-engineering literature predates Foundry but that Pipeline Builder makes low-friction at scale. The source-system connector choices and their throughput implications. The conformance and entity-resolution discipline that decides whether a counterparty appears once in the gold layer or three times under three transliterations. The idempotency requirement that makes pipelines re-runnable without data corruption. The schema-drift policy that determines whether an upstream change breaks the pipeline immediately, silently degrades downstream queries, or is correctly absorbed into the conformance layer. And the audit-metadata columns on every gold-layer object that regulators will examine when reconstructing engagement decisions years later.

The four-layer ingestion pattern

The bronze / silver / gold framing predates Foundry — it appears in the data-warehouse literature from Kimball and Inmon onward and in the more recent lakehouse-architecture work — but Pipeline Builder’s no-code-to-low-code surface makes the discipline low-friction to implement at scale across heterogeneous source systems. Four layers, each with a distinct responsibility.

Bronze is the raw source extract. Whatever the source system sent, in whatever format it sent it, with the schema the source system had at extract time. Bronze datasets are typically partitioned by extract date and retained for 60-180 days for re-derivation. Their purpose is forensic: if the gold-layer ontology turns out to be wrong, the institution must be able to walk back to the bronze data and re-derive correctly. Bronze is also where schema-drift events are first visible — a new field appearing, an existing field’s data type changing, a categorical value falling outside the enumerated set.

Silver is cleaned, conformed, and deduplicated. Field names are standardized to the institution’s canonical vocabulary. Categorical values are conformed to enumerated sets (ISO 3166-1 alpha-2 country codes, the institution’s risk-rating taxonomy, the standard PEP-status enumeration). Duplicate records are resolved through the institution’s entity-resolution rules. Data-classification tags (TIER_2 / TIER_3 / TIER_4 per the institution’s data_classification.yaml-equivalent policy) are applied. Silver datasets are the substrate against which the gold-layer transforms run.

Gold is ontology-ready. Each record in a gold dataset corresponds to one ontology object instance — one Counterparty, one Person, one Transaction. Gold datasets carry the audit metadata that downstream Workshop applications and AIP functions need: the pipeline_run_id that produced the record, the source_extract_date of the underlying bronze data, the transform_version that was active when the record was produced. Gold is where the ontology layer actually materializes — Foundry projects gold-dataset rows into ontology object instances via the Ontology Manager’s property-mapping configuration.

Ontology projection is the final mapping step that registers gold-dataset rows as ontology object instances. The projection is configured through the Ontology Manager UI in Foundry, not through Pipeline Builder directly. From the data engineer’s perspective, gold-layer dataset shape and audit-metadata discipline determine whether projection succeeds cleanly; from the analyst’s perspective, what the ontology surfaces is what gold delivered, with whatever delays and discrepancies the pipeline introduces.

The cleanest implementations treat each layer as independently materializable. Bronze runs on a fixed schedule against source-system endpoints. Silver runs when bronze updates. Gold runs when silver updates. Each layer’s failure is contained and re-derivable from upstream layers; the institution does not lose the ability to reconstruct an earlier ontology state by re-running gold against historical bronze.

Why this pattern matters: a published example

The four-layer pattern abstracts to architecture, but its operational value is most visible against a published case where the ingestion-and-resolution discipline was either present or absent. The DOJ resolution of the Danske Bank Estonia matter is one such case. The DOJ resolution publicly described approximately \$160 billion in non-resident-portfolio flows through U.S. correspondent accounts and a guilty plea by Danske Bank A/S with forfeiture exceeding \$2 billion (U.S. Department of Justice press release, December 13, 2022).

A practitioner ingestion pattern for a similar matter — whether built inside Foundry Pipeline Builder or any equivalent platform — would touch nine source-system categories before the analyst opens a single Workshop card: customer-master records from the bank core; KYC and customer-due-diligence files; beneficial-ownership records; account-opening records and signature cards; transaction-monitoring alerts and disposition history; wire-transfer records and correspondent-bank messages (SWIFT MT103 / pacs.008); internal audit findings; whistleblower or escalation references where institutionally permissioned; and the regulatory-correspondence trail (consent orders, MRA / MRIA letters) the institution receives from its supervisors.

The silver layer would resolve shell-company names across transliterations and corporate-form variations, register beneficial owners against their UBO disclosures, and reconcile account relationships across the source systems’ overlapping identifiers. The gold layer would carry the six ontology object types the investigator actually opens — Counterparty, BeneficialOwner, Account, Transaction, Alert, ControlFinding — each row with the pipeline_run_id and source_extract_date that let the institution reconstruct what it knew about the counterparty on any historical date.

Three questions that ingestion discipline makes answerable, and that absence of ingestion discipline tends to make unanswerable:

1. Which non-resident customers shared beneficial owners, addresses, directors, or correspondent-bank pathways across the portfolio?
2. Which high-risk customers continued to process large U.S.-dollar flows after KYC refresh windows lapsed or after specific alerts were dispositioned without remediation?
3. Which counterparty-master fields (legal name, jurisdiction, risk rating, PEP status) changed in the weeks after a control finding or whistleblower escalation surfaced internally?

The same four-layer / silver-conformance / gold-audit pattern applies symmetrically to financial-misstatement engagements (the SEC’s EPS Initiative and the WorldCom and Kraft Heinz matters) and to anti-corruption work (DOJ resolutions in Odebrecht / Braskem and 1MDB) — different source categories (general-ledger / journal-entry / disclosure-support; vendor-master / third-party intermediary / payment-run / political-exposure), same architecture — which is why the conformance, idempotency, and audit-metadata discipline described below is not optional.

Source-system connectors

Foundry Pipeline Builder supports the standard connector patterns: JDBC for relational warehouses, REST for vendor APIs, file-drop for batch CSV / Parquet drops, streaming for Kafka-style continuous ingestion. The choice between them is dictated by the source system’s interface, not by Pipeline Builder’s preference, and the operational implications differ.

JDBC connectors are appropriate for transactional databases and data-warehouse pull sources (typical DD targets: core-banking counterparty and account tables, general-ledger and journal-entry tables, procurement master tables, ERP close-management data). Throughput scales with the source system’s query capacity and Foundry’s parallel-extract configuration. Authentication is typically service-account-based with credential rotation handled at the institutional secrets-management layer (not in the Foundry-side pipeline configuration). The connector’s parallelism setting interacts with the source system’s connection-pool limits in ways that need explicit coordination with the source system’s DBA team; running a 32-parallel extract against a source system tuned for 16-connection workloads is a denial-of-service against the institution’s own bank core.

REST connectors are the right choice for vendor APIs (typical: sanctions screening, adverse-media surveillance, KYC verification, beneficial-ownership disclosure feeds, PEP and politically-exposed-person registries, government entity registries). Rate-limit handling is the operational center of REST-connector design. Most regulatory-data vendors enforce per-second or per-minute call quotas; Pipeline Builder’s connector configuration must respect those quotas through explicit throttling. Authentication varies widely (API keys, OAuth client credentials, mutual TLS); the institutional secrets-management posture should constrain each connector to the minimum-necessary credential scope.

File-drop connectors are common for batch interchanges where the source system writes a CSV or Parquet file to a shared SFTP / S3 location on a schedule (typical: daily SFTP drops from KYC platforms, monthly counterparty-master refreshes from external vendors, ad-hoc investigation-export drops from case-management systems). Pipeline Builder picks up the file on arrival and ingests. The operational concerns are file-arrival monitoring (what happens if the source system doesn’t deliver), file-format validation (what happens if the schema drifted), and idempotency on re-delivery (what happens if the same file arrives twice).

Streaming connectors are appropriate for high-volume transaction sources where real-time freshness matters (typical: real-time wire and payment feeds, transaction-monitoring alert streams, fraud-decision event streams, case-management state-change events). Foundry supports Kafka-style streaming ingestion; the implementation requires more institutional engineering than batch sources because the failure semantics differ — a streaming pipeline that drops messages between checkpoints produces a partial bronze layer that downstream consumers may not detect.

For an illustrative DD ingestion deployment, three generic source-system categories are common: a core banking system (referred to here as Core Banking System A, not based on any specific institution’s actual stack), a KYC platform (KYC Platform B), and a CRM / counterparty master (CRM / Counterparty Master C). Each category may be served by different vendors at different institutions; the connector pattern is the same.

Conformance and entity resolution

The single most operationally significant decision in the silver layer is how to resolve the same counterparty across multiple source systems. A typical bank-side ingestion sees the same entity represented under different identifiers, different transliterations of its legal name, different jurisdictional codes (full-name vs. ISO 3166-1 alpha-2), and different counterparty-type classifications. Without an entity-resolution rule set, the gold-layer Counterparty ontology object becomes three instances of the same entity, the relationship graph fragments, and the analyst opening the Workshop card sees an incomplete picture.

The conformance discipline is mechanical: standardize jurisdictional codes to a single representation (ISO 3166-1 alpha-2 is the typical choice for international DD workflows); standardize legal-name canonicalization (lowercase, trim, normalize Unicode); standardize categorical values to enumerated sets (risk_rating to {low, medium, high, critical}, pep_status to {none, domestic, foreign, international_org}). These transforms can be implemented inside Pipeline Builder’s no-code surface for the common cases and pushed down to Code Repository transforms (Code Repositories in Foundry, Article 006 in this sub-series) for the cases requiring custom logic.

Entity resolution is harder. The standard approach combines two layers. Deterministic matching uses high-confidence identifiers as short-circuit resolvers: LEI (ISO 17442); tax identifier (EIN, VAT-ID, or jurisdiction equivalent); jurisdiction-specific corporate-registration number; account number (for account-to-counterparty resolution); government-issued person identifier (where institutionally permissioned); internal customer identifier (where the institution’s CIF discipline is reliable). If two source records share a deterministic identifier, they are the same counterparty — no further analysis required.

Probabilistic matching uses scored string-similarity when no deterministic identifier resolves. Two algorithms appear most often in DD work: Jaro-Winkler (forgiving of character transpositions — “Smith” vs “Smtih” scores high, around 0.97) and Levenshtein (counts the minimum edits to make one string match another — “ACME LLC” vs “ACME L.L.C.” scores around 0.91 normalized). The composite score combines: legal-name similarity (Jaro-Winkler or Levenshtein on canonicalized strings); address similarity post-normalization; jurisdiction exact-match; incorporation-date proximity within a configurable day window; director / officer overlap; beneficial-owner overlap; phone / email / domain overlap; counterparty-type exact-match.

A concrete walk-through. Two source records — “Acme Holdings Z LLC” (Core Banking System A, jurisdiction ZZ, incorporated 2018-03-12) and “ACME HOLDINGS (Z) LIMITED” (KYC Platform B, jurisdiction ZZ, incorporated 2018-03-14) — share no deterministic identifier. After canonicalization (lowercase, trim punctuation, normalize Unicode), legal-name Jaro-Winkler scores 0.91. Jurisdiction is an exact ZZ-ZZ match (1.0). Incorporation date is 2 days apart (proximity score ~0.95 against a 30-day window). Counterparty-type matches. Address fields are missing in source A. The composite, weighted per the YAML below, lands at 0.88 — above the 0.85 auto-resolve threshold. The two records merge into one silver-layer entity, SILV-1001, which appears in the worked-example output.

The composite output is not binary. Production rule sets emit three bands — auto-resolve above the high-confidence threshold (above the example’s 0.85); manual-review queue for the ambiguity band (typically 0.70-0.85) where false-merge cost outweighs human-adjudication latency; and do-not-resolve for low-confidence matches that stay as separate silver entities until further evidence emerges. In a typical DD engagement, the manual-review queue is staffed by analysts in the same team that adjudicates KYC and transaction-monitoring exceptions, not by a separate group; queue depth, disposition-time, and confirmed-merge-versus-reject ratios are operational metrics that the engagement supervisor reviews weekly.

The rule set ships with the institution’s entity_resolution_rules.yaml and is versioned alongside the pipeline; rule changes affect every gold-layer counterparty record produced after the change and require a back-fill against historical silver data to apply the new rules to prior periods.

The companion repository’s entity_resolution_rules.yaml documents the rule set used in the worked example. The structure is illustrative pseudo-YAML for inline reference; the parseable full version lives in the companion repo:

entity_resolution:
  deterministic_match:
    priority_order:
      - lei                       # ISO 17442 Legal Entity Identifier
      - ein                       # US Employer Identification Number
      - registration_number       # jurisdiction-specific
    behavior_on_match: short_circuit_resolution

  probabilistic_match:
    composite_threshold: 0.85
    scoring_weights:
      legal_name_jaro_winkler: 0.50
      jurisdiction_exact: 0.20
      incorporation_date_proximity_days_log: 0.15
      address_jaro_winkler: 0.10
      counterparty_type_exact: 0.05

  review_flag_band:
    composite_score_range: [0.70, 0.85]
    behavior: write_to_silver_with_review_flag

Production institutions tune the composite-threshold and scoring-weight parameters against their own counterparty corpus and validate against a labeled holdout set before deployment. The companion repo’s rule set is illustrative — the published validation metrics (precision 0.94, recall 0.91 on a labeled holdout) reflect the worked example, not any production institution’s actual data.

Idempotency and incremental ingestion

A pipeline that produces the same gold output on every re-run regardless of the source data’s update cadence is idempotent. A pipeline that produces different output on each re-run (because of timestamp-of-now references, random sampling without seeds, non-deterministic ordering) is not. Idempotency is the precondition for re-derivation, back-fills, and the engineering practice of running the pipeline in production and in a staging environment simultaneously to verify correctness.

The two practical idempotency disciplines for Foundry pipelines:

Use explicit pipeline-run timestamps, not current_timestamp inside transforms. When a transform needs a “now” value (for example, to compute “trailing 90 days” against a bronze dataset), the value should come from the pipeline-run context rather than the transform’s evaluation moment. Pipeline Builder exposes the run-context timestamp; transforms should consume it explicitly.

Use watermarked incremental extraction, not full-refresh, for high-volume sources. A watermark is just a bookmark — the timestamp (or sequence number) of the most recent record successfully pulled from the source system on the previous run. The current run uses that bookmark to ask the source system for only the records that have appeared since (anything with a source-timestamp greater than the watermark), appends them to the bronze dataset, and updates the bookmark for the next run. Incremental extraction is faster, less load on the source system, and re-runnable without duplicating records. A full-refresh, by contrast, pulls every record every run and discards the prior bronze — appropriate for very small reference tables (sanctions lists, country-code dimensions) but a poor fit for transaction histories.

For DD ingestion specifically, the audit-metadata columns (pipeline_run_id, source_extract_date) on every gold-layer record serve a second purpose beyond regulatory documentation: they enable the institution to identify exactly which records came from which run, which simplifies debugging when an ingestion error is suspected and back-fill operations when the error is confirmed.

A non-idempotent pipeline is more than a code-quality smell — it makes the institution unable to answer the regulator’s “what did the institution know about counterparty Y at time T” question with anything more than the institution’s current state, which is exactly the lineage gap that supervisory examinations and engagement-record reconstructions are designed to test.

In practical engagement terms, the engagement team records the pipeline_run_id on the substantive-testing lead schedule alongside the conclusions drawn from the data in question. When the auditor (or examiner, or counsel reconstructing the record years later) asks how the analyst arrived at a conclusion, the answer routes from the workpaper to the run identifier to the bronze extract to the source system — and PCAOB AS 2401 (consideration of fraud in a financial-statement audit) and AS 1215 (audit documentation) are satisfied because the path is traceable.

Schema-drift handling

Source systems change their schemas. New fields appear. Existing fields’ data types change. Categorical enumerations expand. The pipeline’s response to drift is an explicit institutional policy decision, not an engineering accident.

Two policies are defensible and one is dangerous.

Fail-on-unmapped (defensible). When a bronze-layer extract contains a field the silver-layer transform does not recognize, the pipeline fails the run, alerts the on-call data engineer, and waits for explicit acknowledgment before processing the new field. This policy treats schema drift as a control event that warrants human review. It is the appropriate default for regulated DD ingestion: the institution wants the data engineer to know that a new field appeared, the analyst to decide whether it carries data-classification implications, and the auditor to inspect the conformance and gold-layer transforms before the new field flows downstream.

Log-and-continue (defensible in narrow contexts). When a bronze-layer extract contains an unrecognized field, the pipeline logs the event, drops the field from silver, and continues. This policy avoids ingestion-pipeline outages but loses information silently — the dropped field may have been the data point the next investigator needed. Defensible for low-criticality auxiliary data sources where pipeline uptime outweighs information completeness; not defensible for regulator-relevant DD sources.

Silent absorb (dangerous). When a bronze-layer extract contains an unrecognized field, the pipeline absorbs the field into a generic “additional_data” blob, makes it queryable by analysts who happen to know to look for it, and otherwise behaves as if no change occurred. This pattern is occasionally suggested for “flexibility”; it is the wrong default because it removes the institutional signal that something changed upstream — the kind of signal that distinguishes a controlled DD ingestion from one that drifts into incoherence over years.

The companion repository’s pipeline template uses fail-on-unmapped by default with explicit per-field overrides where log-and-continue is institutionally appropriate.

For the engagement team, the practitioner action is unambiguous: when a fail-on-unmapped alert fires, the engagement memo records (a) the new field, (b) the data-classification determination, (c) the analyst’s review of whether the field affects any open engagement conclusion, and (d) the explicit approval to ingest. The audit trail is the alert + the memo, not the silent absorption.

Audit metadata on every gold object

Every gold-layer record carries audit-metadata columns that are not present in source data and are added by the silver→gold transforms. A sample gold-layer record (illustrative pseudo-YAML; the parseable CSV equivalent ships in the companion repository’s synthetic_data/gold_counterparty_ontology.csv):

# illustrative single gold-layer Counterparty record
counterparty_id: SILV-1001
legal_name: acme holdings (z) llc
jurisdiction: ZZ
risk_rating: medium
last_kyc_refresh: 2026-02-14T00:00:00Z

# audit-metadata columns (added by silver→gold transform)
pipeline_run_id: prod-2026-05-11T03:00:00Z-run-4821
source_extract_date: 2026-05-11T02:14:00Z
transform_version: silver_to_gold@v2.1
computed_at: 2026-05-11T03:18:42Z

The four audit-metadata columns are:

• pipeline_run_id — unique identifier for the pipeline run that produced this record
• source_extract_date — timestamp of the bronze-layer extract this record derives from
• transform_version — semantic version of the silver→gold transform that produced this record
• computed_at — timestamp of the gold-layer transform’s execution

These columns serve four purposes. First, regulatory documentation: when a regulator asks how the institution knew X about Counterparty Y at time T, the analyst traces the answer from the AuditEntry (Foundry Actions Framework for Audit-Trail Discipline, Article 005 in this sub-series) to the ontology object to the gold-dataset row to the bronze extract to the source system, and each step is explicitly timestamped. Second, debugging: when an investigator reports that the Workshop card shows incorrect information, the data engineer identifies exactly which transform-version + run produced the offending record. Third, back-fill management: when a transform-version bug is identified, the engineering team identifies exactly which records need re-derivation. Fourth, model-risk-management discipline: any composite scoring or AIP-augmented analysis downstream of the ontology should record the gold-record transform_version it consumed, so subsequent re-runs against different transform versions are distinguishable.

The audit-metadata convention is consistent with the Federal Reserve’s SR 11-7 model-documentation expectations and SOX §404 internal-controls evidence requirements (PCAOB AS 2201 on the auditor’s evaluation of internal control). It is not by itself sufficient for regulatory compliance — compliance is fact-specific and depends on the institution’s overall control environment — but its absence is a control gap regulators consistently identify, and the auditor flags it during walkthrough and substantive testing.

For the engagement workpapers, the practitioner action is concrete: every conclusion drawn from an ontology object carries the pipeline_run_id and transform_version that produced it, recorded on the lead schedule. If the engagement team later refreshes the pipeline or the transform changes version, the workpaper still resolves to the exact state of the data as of the conclusion date — which is what PCAOB AS 1215 audit-documentation discipline and the supervisor’s review-note discipline require.

A worked three-source ingestion

The companion repository ships an illustrative three-source ingestion pipeline at articles/002_pipeline_builder_dd_data_ingestion/pipeline_template.yaml. The pipeline ingests synthetic data from three generic source systems:

• Core Banking System A — JDBC connector, daily incremental extract on counterparty records, watermark on last_modified_timestamp
• KYC Platform B — REST connector, weekly full refresh on KYC document metadata, rate-limited to 5 requests/second per vendor SLA
• CRM / Counterparty Master C — file-drop connector, daily SFTP delivery of counterparty-master records, fail-on-unmapped schema-drift policy

The bronze layer materializes three separate datasets, one per source. The silver layer’s silver_counterparties_resolved.csv (synthetic data in the companion repo’s synthetic_data/) shows the entity-resolution output: the three source systems’ 16 overlapping counterparty records resolve to six distinct silver-layer entities, with the resolution rules shipped in entity_resolution_rules.yaml. The gold layer’s gold_counterparty_ontology.csv shows the six gold-layer Counterparty records, each carrying pipeline_run_id, source_extract_date, and transform_version audit-metadata columns. The transform that materializes silver to gold is in transforms/silver_to_gold_transform.py (illustrative PySpark; production institutions would deploy this through Foundry Code Repositories, per Code Repositories in Foundry, Article 006 in this sub-series).

The worked example deliberately uses generic source-system categories rather than naming specific bank-core / KYC / CRM vendors. Real institutions running this pattern apply the connector and transform discipline to whichever vendors their stack actually uses.

Where Pipeline Builder hits its limits

Pipeline Builder handles the 80% of DD ingestion transforms that are expressible as filter / join / aggregate / project / type-cast operations on well-structured source data. The remaining 20% requires Foundry Code Repositories (Python or PySpark in a versioned project; see Code Repositories in Foundry, Article 006 in this sub-series). Concretely, the cases where Pipeline Builder’s no-code surface becomes the bottleneck:

• Complex windowing across long time periods (e.g., rolling 365-day transaction-volume aggregations with per-counterparty rank computations) — Pipeline Builder’s window functions are limited; PySpark’s Window API is the production answer
• Custom string parsing for jurisdictional-code variants or transliterated legal names (e.g., Cyrillic-to-Latin transliteration for Russian-language counterparty records, Arabic-script handling for Gulf entities) — beyond simple find-and-replace, the parsing logic belongs in a Code Repository
• Machine-learning inference embedded mid-pipeline (e.g., a sanctions-screening model scoring each counterparty record before silver-layer materialization) — runs through Foundry’s ML deployment surface invoked from a Code Repository transform
• Non-standard joins on array-typed columns or hierarchical structures — Pipeline Builder’s join operators are first-order; multi-level joins require PySpark
• Performance-tuned transforms at production scale where partition-pruning and broadcast-hint discipline make the difference between a 5-minute pipeline run and a 50-minute one — Code Repository’s PySpark surface exposes the tuning levers Pipeline Builder abstracts away
• Graph-style entity resolution — when resolution rules need to operate over a graph (a beneficial-ownership chain three hops deep, a co-director network across counterparties, an address-overlap cluster joining seemingly unrelated entities) rather than pair-wise feature comparison, the logic belongs in a Code Repository transform with a graph-traversal library (NetworkX, GraphFrames)
• Custom external-API integration — when a vendor API requires multi-step OAuth flows with response-token persistence, request-batching with backoff-and-retry, or response parsing of nested JSON / XML structures beyond Pipeline Builder’s REST-connector surface, the integration belongs in a Code Repository transform
• Unit-tested transforms with CI/CD discipline — Pipeline Builder is tested through preview-and-promote; Code Repository transforms are tested through pytest fixtures, golden-record comparisons, and continuous-integration pipelines that prevent schema-change-induced silent regressions. For DD ingestion where the cost of silent failure is high (a misclassified counterparty propagating downstream to alerts, decisions, and engagement memoranda), Code Repository testing is the load-bearing control

Code Repositories in Foundry, Article 006 in this sub-series, walks the Code Repository pattern in detail — when to leave Pipeline Builder’s no-code surface, how to structure the Python project, the testing harness that prevents schema-change-induced silent failures, and the CI/CD discipline that keeps Code Repository transforms from regressing.

For the 80% of DD ingestion that stays inside Pipeline Builder’s envelope, the four-layer bronze / silver / gold / ontology-projection pattern combined with strict idempotency, fail-on-unmapped schema-drift handling, and audit-metadata discipline on every gold-layer record produces an ingestion architecture that survives the schema churn that source systems will inevitably impose over the life of a multi-year DD engagement.

Practitioner controls checklist

The architecture above is ready for production DD ingestion only when the following controls are present and demonstrable. Absent any item, the institution should not yet run the pipeline against regulated-engagement data.

1. Bronze retention sufficient for re-derivation (typically 60-180 days; longer for multi-year lookbacks).
2. Silver conformance rules documented in code and versioned in source control.
3. Entity-resolution thresholds tuned against a labeled holdout set and reviewed at least annually.
4. Manual-review queue instrumented and staffed for ambiguous matches; queue depth and disposition-time tracked.
5. Gold-layer audit metadata on every record (pipeline_run_id, source_extract_date, transform_version, computed_at) — no exceptions.
6. Schema-drift policy set to fail-on-unmapped for regulator-relevant sources; silent absorb prohibited.
7. Watermarks for incremental extraction on high-volume sources; full-refresh exists for back-fills but is not the default.
8. Stable source keys for deduplication; never derive primary keys from row-position or extract-order.
9. Transform versioning with semantic-version discipline; material output changes require an explicit version bump and a documented back-fill plan.
10. Access controls aligned to data classification, with per-layer controls where source-data sensitivity warrants.
11. Exception handling on every connector for source-system unavailability, partial extracts, and malformed records; failed extracts surface as alerts, not silent gaps.
12. Lineage from ontology object back to source-system row reconstructable in under an hour by any authorized analyst, not dependent on tribal knowledge.
13. Code Repository handoff documented for the cases where Pipeline Builder is the bottleneck (the eight triggers enumerated above).
14. Operating procedures documented for pipeline failure, schema-change events, back-fill operations, and source-system replacement; reviewed at least semi-annually and after every material control finding.

The checklist deliberately leaves item-specific thresholds to the institution’s engagement profile and supervisory posture. What it requires is that each item be answered explicitly, in writing, before the pipeline serves regulated work.

---

Authority:

Palantir Foundry documentation:

• Foundry Documentation — Pipeline Builder (no-code transforms, connector configuration, schedules, expectations, schema-drift handling)
• Foundry Documentation — Ontology (property mappings, gold-to-ontology projection)
• Foundry Documentation — Code Repositories (PySpark patterns; bridge to Code Repositories in Foundry, Article 006 in this sub-series)

Data-engineering literature:

• Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit (3rd ed.). Wiley.
• Inmon, W.H. (2005). Building the Data Warehouse (4th ed.). Wiley.
• Armbrust, M., Ghodsi, A., Xin, R., & Zaharia, M. (2021). “Lakehouse: A New Generation of Open Platforms That Unify Data Warehousing and Advanced Analytics.” CIDR 2021.
• Karau, H., & Warren, R. (2017). High Performance Spark. O’Reilly. (For the production-tuning patterns that motivate the Code-Repository handoff.)

Entity-resolution + data-quality:

• Christen, P. (2012). Data Matching: Concepts and Techniques for Record Linkage, Entity Resolution, and Duplicate Detection. Springer.
• ISO 3166-1 (country codes) and ISO 4217 (currency codes) — for the conformance vocabulary.

Regulatory / control framework:

• Federal Reserve, SR 11-7 — Guidance on Model Risk Management (audit-metadata discipline aligns with §V documentation expectations).
• OCC Bulletin 2011-12 — Sound Practices for Model Risk Management.
• SOX §404 — Management Assessment of Internal Controls (data-lineage evidence requirements).
• FFIEC, BSA/AML Examination Manual (CDD and EDD data-lineage expectations).

Published prosecutions referenced:

• U.S. Department of Justice, Danske Bank Pleads Guilty to Fraud Conspiracy and Agrees to Forfeit Over \$2 Billion (Office of Public Affairs press release, December 13, 2022) — source for the non-resident-portfolio-flow and forfeiture figures cited above; figures hedged with “publicly described” language.
• WorldCom (SEC special-investigative-committee report on the false / unsupported entries), Kraft Heinz (SEC procurement internal-controls action and related AAERs), Odebrecht / Braskem (DOJ global resolution, December 2016), and 1MDB (DOJ forfeiture and related filings) — named symmetrically as published examples of the same four-layer pattern applied to financial-misstatement and anti-corruption work; no figures or charges cited beyond the public record.

Word count: ~3,400 words (within v2 target of 3,300-3,500 after surgical merges from v2 second-source draft)

Reproducible artifacts (companion DD Tech Lab repository at https://github.com/noahrgreen/dd-tech-lab-companion):

• articles/002_pipeline_builder_dd_data_ingestion/pipeline_template.yaml — full four-layer bronze/silver/gold ingestion template with conformance, idempotency, and schema-drift policy declarations (verified parseable via yaml.safe_load)
• articles/002_pipeline_builder_dd_data_ingestion/entity_resolution_rules.yaml — deterministic-first, probabilistic-fallback entity-resolution rule set with composite-score thresholds (verified parseable via yaml.safe_load)
• articles/002_pipeline_builder_dd_data_ingestion/synthetic_data/ — three bronze-layer CSVs (5 + 5 + 6 records, one per generic source system), the silver-layer resolved CSV (6 entities), and the gold-layer ontology-ready CSV (6 records with the four audit-metadata columns)
• articles/002_pipeline_builder_dd_data_ingestion/transforms/silver_to_gold_transform.py — illustrative PySpark transform documenting the gold-layer audit-metadata pattern and Foundry Code Repository deployment structure
• articles/002_pipeline_builder_dd_data_ingestion/README.md — bundle navigation and reproduction steps

All artifacts exist at the public GitHub URL at this article’s release and are directly browsable. The two YAML files parse cleanly via yaml.safe_load (verified by the author and re-runnable by any reader against the live repository); the five CSV files load via pandas.read_csv with the row counts specified above; the PySpark transform is internally coherent and documents the structure runnable equivalents would have in an actual Foundry Code Repository deployment. Code Repositories in Foundry, Article 006 in this sub-series, walks the Code Repository pattern for the cases where Pipeline Builder’s no-code surface is the bottleneck.

FINAL v2 — Prepared by Noah Green CPA CFE — 2026-05-17