Data Usage Patterns: Who Uses What at Each Layer

A common mistake in data engineering is building a data platform without understanding who will use it and how. The Medallion Architecture (Bronze → Silver → Gold) is not just a storage pattern — it's a usage pattern. Each layer serves different consumers with different query patterns, latency requirements, and quality guarantees.

The Medallion Architecture Layers

Bronze Layer: Raw Data

What It Contains

• Raw data as-is from source systems
• No transformations applied
• Full history preserved
• Schema-on-read (schema applied at query time)

Who Uses It

• Data Engineers — Debugging pipeline issues, validating data quality
• Data Scientists — Exploring raw data for feature engineering
• Compliance Officers — Auditing raw data for regulatory requirements

Query Patterns

-- Data Engineer: Debugging a pipeline issue
SELECT * FROM bronze.orders 
WHERE ingestion_date = '2024-01-15'
AND raw_payload LIKE '%error%'
LIMIT 100;

-- Data Scientist: Exploring raw clickstream data
SELECT raw_payload
FROM bronze.clickstream
WHERE event_date = '2024-01-15'
LIMIT 1000;

Characteristics

• Latency tolerance: Minutes to hours (not time-sensitive)
• Data quality: None (raw, unvalidated)
• Retention: Long-term (7+ years for compliance)
• Access control: Restricted to authorized engineers and auditors
• Cost: Low (cheap storage, infrequent queries)

SLA

• Freshness: Ingestion latency (minutes to hours)
• Availability: Best effort (not SLA-critical)
• Accuracy: Raw data, no quality guarantees

Silver Layer: Curated Data

What It Contains

• Cleaned, validated, deduplicated data
• Schema applied (schema-on-write)
• Conformed dimensions
• Basic quality checks passed

Who Uses It

• Data Scientists — Feature engineering, model training
• Data Analysts — Ad-hoc analysis, exploration
• ML Engineers — Feature stores, training data
• Data Engineers — Building Gold layer aggregations

Query Patterns

-- Data Scientist: Building features for ML
SELECT 
    customer_id,
    COUNT(DISTINCT order_id) as total_orders,
    SUM(amount) as total_spend,
    AVG(amount) as avg_order_value,
    DATEDIFF('day', MIN(order_date), MAX(order_date)) as customer_tenure
FROM silver.orders
WHERE order_date >= '2023-01-01'
GROUP BY 1;

-- Data Analyst: Ad-hoc analysis
SELECT 
    p.category,
    p.brand,
    SUM(o.amount) as revenue,
    COUNT(DISTINCT o.customer_id) as unique_customers
FROM silver.orders o
JOIN silver.products p ON o.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 1, 2
ORDER BY 3 DESC;

Characteristics

• Latency tolerance: Seconds to minutes
• Data quality: Validated, deduplicated
• Retention: Medium-term (1-3 years)
• Access control: Domain-based access control
• Cost: Medium (query-optimized storage)

SLA

• Freshness: Within 1 hour of source update
• Availability: 99.5% uptime
• Accuracy: Validated against quality rules

Gold Layer: Business-Ready Data

What It Contains

• Aggregated, business-ready data
• Pre-computed metrics and KPIs
• Conformed across domains
• Dashboard-ready

Who Uses It

• BI Analysts — Building dashboards and reports
• Executives — Strategic decision-making
• Business Users — Self-service analytics
• Finance — Financial reporting and compliance

Query Patterns

-- BI Analyst: Dashboard query
SELECT 
    date,
    category,
    revenue,
    units,
    unique_customers,
    avg_order_value
FROM gold.daily_sales_summary
WHERE date >= CURRENT_DATE - 30
ORDER BY date;

-- Executive: Monthly P&L
SELECT 
    month,
    SUM(revenue) as total_revenue,
    SUM(cost) as total_cost,
    SUM(revenue) - SUM(cost) as profit,
    (SUM(revenue) - SUM(cost)) / SUM(revenue) * 100 as margin_pct
FROM gold.monthly_financial_summary
WHERE year = 2024
GROUP BY 1
ORDER BY 1;

Characteristics

• Latency tolerance: Sub-second (dashboard SLA)
• Data quality: Business-validated, reconciled
• Retention: Long-term (5+ years)
• Access control: Role-based access control
• Cost: High (query-optimized, high concurrency)

SLA

• Freshness: Within 24 hours (daily refresh)
• Availability: 99.9% uptime (production dashboards)
• Accuracy: Reconciled against source systems

Consumer-to-Layer Mapping

Query Pattern Analysis by Layer

Bronze Query Patterns

• Full table scans — No indexes, raw data exploration
• Regex/JSON parsing — Extracting fields from raw payloads
• Sampling — TABLESAMPLE BERNOULLI (1) for quick exploration
• Time-based filtering — Filter by ingestion timestamp

Silver Query Patterns

• Joins — Combining fact and dimension tables
• Aggregations — GROUP BY with business logic
• Window functions — Rankings, running totals, time-series
• Deduplication — DISTINCT, ROW_NUMBER for dedup

Gold Query Patterns

• Simple aggregations — Pre-computed, dashboard-ready
• Filtering — Date ranges, categories, segments
• Drill-down — Year → Quarter → Month → Day
• Comparison — YoY, MoM, target vs actual

Access Control by Layer

-- Lake Formation: Layer-based access control
-- Bronze: Only data engineers
GRANT SELECT ON TABLE bronze.orders TO ROLE 'data-engineer';

-- Silver: Domain analysts
GRANT SELECT ON TABLE silver.orders TO ROLE 'marketing-analyst';
GRANT SELECT ON TABLE silver.customers TO ROLE 'marketing-analyst';

-- Gold: All analysts and executives
GRANT SELECT ON ALL TABLES IN SCHEMA gold TO ROLE 'bi-analyst';
GRANT SELECT ON ALL TABLES IN SCHEMA gold TO ROLE 'executive';

Cost Attribution by Layer

Understanding cost per layer helps optimize spending:

Cost Optimization Strategies:

• Bronze: Use S3 Glacier for long-term retention
• Silver: Partition by date, use Parquet for compression
• Gold: Pre-aggregate to reduce query volume, use materialized views

SLA Framework by Layer

Best Practices

1. Document layer ownership — Each layer should have a clear owner
2. Define SLAs per layer — Set freshness, availability, and accuracy targets
3. Implement access control — Restrict Bronze to engineers, Gold to business users
4. Track query patterns — Monitor which layers get the most queries
5. Attribute costs — Track storage and compute costs per layer
6. Optimize for consumers — Pre-aggregate Gold for dashboards, keep Silver flexible for analysts
7. Implement data contracts — Define quality guarantees per layer
8. Monitor layer health — Track freshness, quality, and SLA compliance per layer
9. Automate layer promotion — Use Airflow/dbt to promote data from Bronze → Silver → Gold
10. Version layer schemas — Use schema registries for backward compatibility

Knowledge Check

Key Takeaways

• Each Medallion layer serves different consumers with different needs
• Bronze: Data engineers debug, compliance auditors verify
• Silver: Data scientists explore, analysts analyze, ML engineers build features
• Gold: BI dashboards, executive reports, business self-service
• SLAs, access control, and cost vary significantly by layer
• Understanding consumer-to-layer mapping is critical for platform design

See Also

• Data Lake Architecture
• Data Mart Concepts
• Data Serving Patterns
• Data Governance & Catalog
• Performance Optimization