Data Pipeline Architecture

VirtuousAI uses a medallion architecture to progressively refine your data from raw extracts to analytics-ready tables. This pattern ensures data quality, enables auditing, and supports incremental updates.

The Three Layers

Bronze: Raw Data

The bronze layer stores exactly what came from your data sources — no transformations, no cleaning.

Why keep raw data?

• Audit trail — You can always see what was originally extracted
• Reprocessing — Re-run transforms if logic changes
• Debugging — Investigate issues by examining source data

Silver: Cleaned Data

The silver layer contains deduplicated, typed, consistently named data. Each source gets its own staging tables.

What silver provides:

• One row per business entity (deduplicated)
• Correct data types (decimals, timestamps)
• Consistent naming conventions
• Still source-scoped (not yet unified)

Gold: Business Models

The gold layer contains dimensional models ready for analytics. Data from multiple sources is unified here.

What gold provides:

• Unified entities across sources
• Pre-computed aggregations
• Star schema for fast queries
• Business terminology (not source system names)

Why This Architecture?

Problem 1: Duplicate Records

APIs often return the same record multiple times (pagination issues, incremental syncs). Bronze keeps all of them; silver deduplicates.

Problem 2: Inconsistent Types

Price might be a string in one API response and a number in another. Silver casts everything to consistent types.

Problem 3: Nested Structures

API responses often have deeply nested JSON. Silver flattens these into queryable columns.

Problem 4: Cross-Source Analysis

Customers appear in Shopify, Klaviyo, and your email system. Gold unifies them into a single dim_customer.

Transformations

Data moves between layers via duckdb_transform actions. These execute SQL transformations using DuckDB.

Transform Modes

Example Transform

A typical bronze-to-silver transform:

SELECT
  id as customer_id,
  email,
  CAST(total_spent AS DECIMAL(18,2)) as total_spent,
  created_at as created_at_utc
FROM bronze_customers
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY id
  ORDER BY updated_at DESC
) = 1

This query:

1. Renames fields for consistency
2. Casts monetary values to decimal
3. Deduplicates by keeping only the latest version of each customer

Storage and Format

Why Delta Lake for silver/gold?

• ACID transactions
• Schema evolution
• Time travel (query historical versions)
• Efficient MERGE operations

Next Steps

• Learn about Data Modeling for gold layer design
• See Actions for transform configuration
• Try Data Extraction to populate bronze