Automating Swap Attribute Enrichment & Geneva Ingestion

Architecture overview

In this post, we’ll walk through an automated architectural pattern to ingest daily sparse swap masters, dynamically enrich them using an equity security master baseline, and automatically pipe the finalized records directly into your accounting system.

The pipeline breaks down into three distinct operational layers:

1. The Ingestion Layer: Automating the retrieval of EOD data files from external endpoints (SFTP/S3).
2. The Transformation Layer (The Enriched View): Executing a deterministic left-join via a virtualized database view to inherit crucial risk and classification metrics.
3. The Ingestion/Loading Layer: Transmitting the structurally complete data records back to the core accounting system.

[ Counterparty / PB S3 ] 
       │
       ▼ (SFTP/S3 Sync)
[ ref.swap_security_master ] ──┐
                               ├──> [ ref.v_swap_security_master_enriched ] ──> [ Geneva Loader ]
[ ref.equity_master ] ─────────┘

Part 1: Orchestrating the Daily Automation Workflow

Using a visual workflow engine, we establish a resilient graph to handle data ingestion and exception routing. This ensures that the engineering team is only alerted if an upstream data provider fails to deliver.

1. Ingesting the Sparse Swap Master

In modern financial technology, managing derivative data at scale presents a persistent challenge: completeness. Unlike standard cash equities, over-the-counter (OTC) and cleared equity swaps often arrive from prime brokers and counterparties with sparse or missing underlying metadata.

Each evening, the prime broker drops a daily snapshot. We configure an automated FTP/S3 Download task to capture the file:

• Source Pattern: swap_security_master/swap_security_master_YYYYMMDD.csv
• Target Destination: ref.swap_security_master

2. Safeguarding the Pipeline with Conditional Logic

Data feeds break frequently. To ensure high availability, the workflow implements explicit branch logic:

• On Success: The file is parsed and bulk-loaded into the relational staging environment.
• On Failure: A dedicated failure path initiates an instantaneous alert component (Notify failed FTP download), dispatching telemetry to the operations desk via Slack or email if a file is corrupted, malformed, or missing.

Part 2: Building the Enrichment Engine via Virtualized Views

Once the raw ref.swap_security_master and reference ref.equity_master tables are securely localized, we create a logical view layer to handle the attribute inheritance.

Using the natural language configuration interface, we instruct the data platform to generate a zero-copy virtual view:

"Can you build a view that enriches the swap security master with the attributes that are missing from the equity master? Join swap on underlying_id to equity security_id, and pull in issuer, sub_industry, issue_country, and country_of_risk_exposure. Keep all the swap columns, just add those four. Call it v_swap_security_master_enriched"

The SQL Under the Hood

The system instantiates this view programmatically, utilizing a strict Left Join to preserve the integrity of the total swap population—even if a specific underlying equity identifier experiences a reference mismatch:

SQL

CREATE VIEW ref.v_swap_security_master_enriched AS 
SELECT 
    swaps.*,
    equity.issuer AS underlying_issuer,
    equity.sub_industry AS underlying_sub_industry,
    equity.issue_country AS underlying_issue_country,
    equity.country_of_risk_exposure AS underlying_country_of_risk
FROM ref.swap_security_master swaps
LEFT JOIN ref.equity_master equity 
    ON swaps.underlying_id = equity.security_id;

By leveraging a relational view rather than a physical table rewrite, we ensure that any mid-day corrections to the equity master are instantly and dynamically reflected across the swap portfolio without running manual refresh scripts.

Part 3: Automated Synchronization to Geneva

An enriched security master is only as valuable as its availability in your production accounting engine. The final phase of our daily workflow serializes the data from our newly created view into a format consumable by your accounting system.

To ensure accurate downstream risk management, compliance filtering, and portfolio accounting, data operations teams must systematically backfill these gaps.

1. Attaching the Loader Component

Following a successful data load, the workflow triggers a final Loader task execution block.

2. Field Mapping & Schema Normalization

The loader reads from ref.v_swap_security_master_enriched and maps our freshly inherited attributes straight into native fields or customized User Defined Fields (UDFs):

• underlying_issuer -> Standard issuer header
• underlying_country_of_risk -> Country of Risk UDF
• underlying_sub_industry -> Industry Classification Code

3. Execution & Validation

Your accounting repository is dynamically updated every single night with verified, complete data. This significantly reduces downstream accounting breaks, mitigates manual patching by operational analysts, and delivers clean exposure reports prior to market open.

Putting this all together

Using the steps above, I have an end to end process that does the following at the end of every business day:

• Extracts sec master data from my Accounting System
• Backfills swap sec master data from underlying security sec master, so you don't have to redo the work.
• Pushes updated sec master data with repaired fields to enhance data not only in CoSet, but also in your Accounting System

All of the above happens on schedule, with no code and no manual intervention.