> ## Documentation Index
> Fetch the complete documentation index at: https://docs.elementum.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake Table Types

> Essential guide for choosing between Standard and Hybrid tables to optimize Elementum's performance and cost efficiency

## 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.

<Info>
  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."
</Info>

<Warning>
  **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)
</Warning>

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

<CardGroup cols={2}>
  <Card title="Standard Tables" icon="table-list" color="#blue">
    **Data Pattern**: Primarily read-only data

    **Examples**:
    <li>Reference data</li>
    <li>Configuration tables with infrequent updates</li>
    <li>Historical archives</li>
    <li>Lookup tables</li>
    <li>Analytical datasets accessed for reporting</li>

    **Benefits**: Simplified management, consistent performance for analytical queries, lower maintenance overhead
  </Card>

  <Card title="Hybrid Tables" icon="bolt" color="#green">
    **Data Pattern**: High read/write transactional data

    **Examples**:
    <li>Active workflow instances</li>
    <li>Transaction records</li>
    <li>Status tracking tables</li>
    <li>Data Exchange Tables</li>
    <li>Any table requiring frequent updates or inserts</li>

    **Benefits**: Significantly faster performance for transactional workloads, improved concurrency, reduced latency
  </Card>
</CardGroup>

***

## 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

<Accordion title="Ideal Use Cases for Standard Tables in Elementum">
  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
</Accordion>

### 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

<Accordion title="Essential Use Cases for Hybrid Tables in Elementum">
  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
</Accordion>

***

## Performance Benefits Comparison

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

<CardGroup cols={2}>
  <Card title="Standard Tables (Read-Primarily Data)" icon="chart-bar">
    **Query Performance**:
    <li>Reliable performance for analytical queries</li>
    <li>Simplified query planning and optimization</li>
    <li>Consistent execution times for reports and dashboards</li>

    **Maintenance Efficiency**:
    <li>Less complexity for tables with infrequent changes</li>
    <li>Reduced need for monitoring and optimization</li>
    <li>Simplified backup and recovery processes</li>
  </Card>

  <Card title="Hybrid Tables (Transactional Data)" icon="rocket">
    **Write Operations**:
    <li>Up to **10x faster** inserts and updates for hot data</li>
    <li>Reduced latency for transaction processing</li>
    <li>Better handling of concurrent write operations</li>

    **Read Performance**:
    <li>Near-instant access to recently written data</li>
    <li>Reduced query latency for operational workflows</li>
    <li>Better cache utilization for frequently accessed records</li>

    **Concurrency**:
    <li>Better handling of simultaneous read/write operations</li>
    <li>Reduced contention for actively used workflow data</li>
    <li>Improved scalability for high-traffic implementations</li>
  </Card>
</CardGroup>

<Note>
  These performance benefits directly translate to more responsive workflows, faster state transitions, and improved user experience for Elementum implementations.
</Note>

***

## Cost Considerations

<Warning>
  Choosing the right table type impacts not just performance but also cost.
</Warning>

<CardGroup cols={2}>
  <Card title="Hybrid Tables" icon="credit-card">
    **Costs**:
    <li>Higher storage costs for hot data (stored in both compute and storage layers)</li>
    <li>Lower compute costs due to reduced query processing time</li>

    **Value Proposition**:
    <li>Better value for transactional tables where performance is critical</li>
  </Card>

  <Card title="Standard Tables" icon="piggy-bank">
    **Costs**:
    <li>Lower overall storage costs</li>
    <li>More predictable cost model</li>

    **Value Proposition**:
    <li>Better value for read-primarily data</li>
  </Card>
</CardGroup>

<Tip>
  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.
</Tip>

***

## Transitioning from Standard to Hybrid Tables

<Warning>
  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.
</Warning>

### Post-Migration Optimization

After migration to a hybrid table:

<Steps>
  <Step title="Monitor Performance">
    Monitor performance to confirm improvement justifies the migration effort
  </Step>

  <Step title="Adjust Virtual Warehouse Sizing">
    Adjust virtual warehouse sizing as hybrid tables may allow for more efficient compute utilization
  </Step>
</Steps>

<Note>
  Migrating table types from hybrid to standard or standard to hybrid can only be performed by Elementum. Contact [Elementum support](/support/resources) to request a table type migration.
</Note>

<Info>
  For technical implementation details, refer to Snowflake's documentation on [Creating and Using Hybrid Tables](https://docs.snowflake.com/en/user-guide/tables-hybrid).
</Info>

***

## Conclusion

<Card title="Key Takeaways for Elementum as a Transactional Workflow Platform" icon="check-circle">
  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.
</Card>

<Info>
  By following these guidelines, Elementum implementations can achieve optimal performance for transaction processing while maintaining cost efficiency across the entire data platform.
</Info>