The Medallion Architecture (Bronze/Silver/Gold) is widely adopted across modern data platforms. However, applying a generic version of this model to asset management data often results in systems that appear technically correct on paper but fail under operational pressure.

Investment data behaves differently. It carries requirements that most standard Medallion designs do not account for, such as:

• Bi-temporal tracking (as-of vs. knowledge date)
• Complex instrument hierarchies
• Multi-source reconciliation
• Long-term regulatory lineage under SEC Rule 17a-4

In practical terms, this means your architecture should organize data, preserve history, resolve conflicts, and support auditability over time. This blog walks through how we design Medallion Architectures in Snowflake, specifically for USA-based asset management use cases.

1. Bronze Layer of Medallion Architecture: Immutable Source Capture.

Specific to asset management, the Bronze layer of the Medallion Architecture in Snowflake is beyond data ingestion. This is the foundation for an audit. To handle these requirements correctly, the architecture starts with how data is captured at the source, without losing any context or history.

Design Principles for Asset Management Bronze:

• Append-only ingestion: Every file, API response, and feed delivery is stored exactly as received, with load timestamps and source identifiers. No updates or deletions.
• Full payload preservation: Raw data is stored with headers, metadata, and even malformed records. This ensures you can answer questions like “what data was available at the time of a decision?”
• Source-partitioned storage: Each source (Bloomberg, Geneva, prime brokers) maintains its own structure. This reflects real-world differences in formats, schedules, and SLAs.
• Bi-temporal timestamps: Every record includes both business_date and load_timestamp, enabling accurate point-in-time reconstruction.

Snowflake-Specific Bronze Implementation: We rely on native capabilities in Snowflake to make this layer efficient and reliable;

• External tables on Azure Data Lake Storage Gen2 to avoid unnecessary data duplication.
• Snowpipe for automated, event-driven ingestion with consistent delivery.
• VARIANT columns for handling semi-structured data (JSON/XML feeds).
• Time Travel configured for extended retention, supported by Fail-Safe for recovery.

2. Silver Layer of Medallion Architecture: Conformed & Validated.

This is where the complexity of investment data is addressed through data standardization, reconciliation, and validation.

• Entity Resolution: A single instrument can appear with multiple identifiers across systems – CUSIP, SEDOL, ISIN, and internal IDs. However, the Silver layer resolves this through a centralized Security Master model that maps all identifiers to a single canonical entity with a defined hierarchy.
• Position Reconciliation Pattern: Positions typically exist across three systems: OMS, accounting platforms (like Geneva or Eagle), and prime brokers. The Silver layer aligns these through:
  • Start-of-day reconciliation across systems.
  • Transaction-level matching using trade and settlement details.
  • Break classification into timing differences, operational issues, or material discrepancies.
• Data Quality Framework: Data quality in this layer directly impacts NAV and reporting accuracy. We implement three levels of validation, namely:
  • Completeness: Ensuring all expected data is received and populated.
  • Accuracy: Verifying prices, quantities, and cash flows against expected ranges.
  • Consistency: Cross-validating calculations and reconciliation outputs.

3. Gold Layer in Medallion Architecture: Analytics-Ready Models.

The Gold layer of Snowflake’s Medallion Architecture delivers structured, consumption-ready datasets aligned to business use cases.

• Portfolio Analytics Gold: Designed for portfolio managers and risk teams.
  • Pre-aggregated views by fund, sector, geography, and instrument.
  • Calculated metrics like duration, yield, DV01, securities returns, and spreads.
  • Performance attribution across multiple dimensions.
  • Near real-time updates using Snowflake Streams and Tasks.
• Regulatory Reporting Gold: Built for compliance and regulatory submissions.
  • Form PF datasets with AUM, leverage, and exposure metrics.
  • 13F filings with holdings, CUSIP, and valuations.
  • Form ADV reporting for firm-level disclosures.
  • End-to-end lineage tracing back to Bronze data.
• Client Reporting Gold: Supports investor reporting and communication.
  • NAV history across multiple time horizons.
  • Capital activity tracking (commitments, contributions, distributions).
  • Performance metrics like IRR, TVPI, DPI, RVPI.
  • Exposure summaries across sectors and geographies.

NOTE: For Performance Optimization, use clustering keys in Snowflake aligned to query patterns:

1. Portfolio Analytics: (fund_id, as_of_date).
2. Regulatory Reporting: (filing_period, fund_id).
3. Client Reporting: (investor_id, fund_id, report_date).

This significantly improves performance, especially for large datasets. 

dbt Implementation Patterns

As a best practice, we structure the medallion architecture using dbt with clear conventions:

1. Bronze: Source definitions with freshness checks, no transformations.
2. Silver: Incremental models with merge strategies for evolving dimensions.
3. Gold: Mix of full-refresh and incremental models based on data volume.
4. Macros: Standardized financial calculations (yield, duration, attribution).
5. Testing: Custom schema validations for reconciliation, NAV checks, and cashflow balance.

With the core data platform in place, the next step is to enable consumption through analytics and reporting tools such as Microsoft Fabric.

The Microsoft Fabric Integration Layer

If your organization is using Microsoft Fabric alongside Snowflake, then Fabric acts as the consumption layer:

• Fabric Lakehouse connected to Snowflake Gold datasets
• Fabric Data Factory orchestrating pipelines end-to-end
• Power BI semantic models for reporting
• Fabric Notebooks for advanced analytics using Python and Spark

Conclusion

The Medallion Architecture is not a fixed template. Especially in asset management, each layer must be designed around bi-temporal data, reconciliation needs, regulatory traceability, and complex instrument structures. Across implementations at firms managing $10B to $150B in AUM, the pattern remains consistent:

• Bronze as an immutable audit trail
• Silver as a reconciliation and validation layer
• Gold as domain-specific, analytics-ready datasets

And the tools (like Snowflake) are already capable. The difference just comes from how intentionally the architecture is designed around the realities of investment data.

At UBTI, we work with asset management firms to design and implement Snowflake-based Medallion Architectures tailored to investment data, covering ingestion, reconciliation logic, regulatory lineage, and performance optimization. Speak with our team to evaluate your current data platform and understand specific techniques to make it production-ready and regulator-ready.