Skip to main content

​
Introduction

Snowflake offers different types of tables that fundamentally affect how data is stored, accessed, and processed. For Elementum, choosing between Standard and Hybrid tables forms the essential foundation that shapes all workflow data operations and transaction processing capabilities. When Elementum processes business-critical workflows, every data write, state change, and query is ultimately influenced by the underlying table structure supporting these operations.
The choice between Snowflake Standard tables and Hybrid tables is critical for optimizing both performance and cost for the platform’s various data needs. For the purposes of this guide, we refer to any table not explicitly created as a Hybrid table as a “Standard table.”
The fundamental rule for Elementum implementations:
  • Standard Tables: Suitable for tables that are primarily read-only
  • Hybrid Tables: Use for any table with high read/write activity (transactional data)
This choice directly impacts:
  • Query performance for time-sensitive operational workflows
  • Storage costs for large datasets with varying access patterns
  • Data freshness requirements for real-time decision making
  • System responsiveness for end users
  • Optimization of compute resources across the organization

​
Quick Reference: Table Type Selection for Elementum

Standard Tables

Data Pattern: Primarily read-only dataExamples:
  • Reference data
  • Configuration tables with infrequent updates
  • Historical archives
  • Lookup tables
  • Analytical datasets accessed for reporting
    • Benefits: Simplified management, consistent performance for analytical queries, lower maintenance overhead

    Hybrid Tables

    Data Pattern: High read/write transactional dataExamples:
  • Active workflow instances
  • Transaction records
  • Status tracking tables
  • Data Exchange Tables
  • Any table requiring frequent updates or inserts
    • Benefits: Significantly faster performance for transactional workloads, improved concurrency, reduced latency

    ​
    Understanding Table Types in the Elementum Context

    ​
    Standard Tables

    Standard tables in Snowflake store data entirely in the persistent storage layer, providing consistent performance characteristics regardless of data age or access patterns.
    • Data is stored in micro-partitions in cloud storage with metadata cached for query optimization
    • Offers uniform performance across all data
    • Reference data, configuration data, and other read-primarily datasets
    For Elementum implementations, Standard tables work well for:
    • Master data that workflows reference (customers, products, locations)
    • Business rules and validation criteria used by workflows
    • Lookup tables providing decision parameters for workflow steps
    • Mapping and translation tables for data standardization
    • Configuration parameters that inform workflow behavior

    ​
    Hybrid Tables

    Hybrid tables combine both local (in compute layer) and remote storage (in cloud storage) to optimize for performance and cost, particularly for transactional data with frequent access.
    • Frequently accessed “hot” data is stored in the compute layer for ultra-fast access
    • Delivers significantly faster performance for transactional operations
    • Any table experiencing frequent reads and writes
    Hybrid tables are essential for Elementum’s core transactional components:
    • Active workflow instance tables
    • Transaction processing tables
    • State tracking and status tables
    • Event logging with frequent updates
    • Any table supporting real-time operations

    ​
    Performance Benefits Comparison

    Choosing the right table type for different data workloads in Elementum provides significant performance advantages.

    Standard Tables (Read-Primarily Data)

    Query Performance:
  • Reliable performance for analytical queries
  • Simplified query planning and optimization
  • Consistent execution times for reports and dashboards
    • Maintenance Efficiency:
  • Less complexity for tables with infrequent changes
  • Reduced need for monitoring and optimization
  • Simplified backup and recovery processes

    • Hybrid Tables (Transactional Data)

    Write Operations:
  • Up to 10x faster inserts and updates for hot data
  • Reduced latency for transaction processing
  • Better handling of concurrent write operations
    • Read Performance:
  • Near-instant access to recently written data
  • Reduced query latency for operational workflows
  • Better cache utilization for frequently accessed records
    • Concurrency:
  • Better handling of simultaneous read/write operations
  • Reduced contention for actively used workflow data
  • Improved scalability for high-traffic implementations
    • These performance benefits directly translate to more responsive workflows, faster state transitions, and improved user experience for Elementum implementations.

    ​
    Cost Considerations

    Choosing the right table type impacts not just performance but also cost.

    Hybrid Tables

    Costs:
  • Higher storage costs for hot data (stored in both compute and storage layers)
  • Lower compute costs due to reduced query processing time
    • Value Proposition:
  • Better value for transactional tables where performance is critical

    • Standard Tables

    Costs:
  • Lower overall storage costs
  • More predictable cost model
    • Value Proposition:
  • Better value for read-primarily data
    • For Elementum’s implementations, the cost/performance tradeoff typically favors hybrid tables for core transaction processing, as the performance benefits outweigh the modest increase in storage costs.

    ​
    Transitioning from Standard to Hybrid Tables

    Snowflake does not currently support direct conversion of an existing standard table to a hybrid table. Unlike other table property changes, changing the table type requires creating a new table and migrating the data.

    ​
    Post-Migration Optimization

    After migration to a hybrid table:
    1

    Configure Indexes

    Configure indexes for optimal query performance - hybrid tables use automatic and search optimization indexes rather than clustering keys
    2

    Monitor Performance

    Monitor performance to confirm improvement justifies the migration effort
    3

    Adjust Virtual Warehouse Sizing

    Adjust virtual warehouse sizing as hybrid tables may allow for more efficient compute utilization
    For technical implementation details, refer to Snowflake’s documentation on Creating and Using Hybrid Tables.

    ​
    Conclusion

    Key Takeaways for Elementum as a Transactional Workflow Platform

    1. Use Hybrid Tables for all transactional data with frequent reads and writes to maximize performance, responsiveness, and user experience.
    2. Keep Standard Tables for read-primarily data to maintain cost efficiency and simplified management.
    3. Monitor usage patterns and be prepared to migrate tables from standard to hybrid as your Elementum implementation evolves and usage patterns change.
    By following these guidelines, Elementum implementations can achieve optimal performance for transaction processing while maintaining cost efficiency across the entire data platform.
    ⌘I