Dynamic Tables

Key Characteristics

• ▶Automatically refresh only changed portions of data
• ▶Track source data modifications since last refresh
• ▶Compute only affected results, significantly reducing cost
• ▶Maintain freshness through incremental updates
• ▶Support complex upstream dependencies and transformations
• ▶Enable cost-effective refresh of large materialized datasets

Why It Matters

• ▶Dramatically reduces compute costs for large-scale materialized views
• ▶Enables overnight refresh of massive data warehouses
• ▶Reduces latency between source data changes and analytics freshness
• ▶Simplifies ETL development through declarative refresh logic
• ▶Scales to terabyte-scale datasets with manageable costs
• ▶Combines benefits of materialized views with real-time freshness

Example

`
-- Traditional materialized view (full refresh)
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT 
  DATE(order_date) as sale_date,
  product_id,
  SUM(order_value) as daily_revenue,
  COUNT(*) as transaction_count
FROM orders
GROUP BY DATE(order_date), product_id;

-- Refresh scans entire orders table (10 years = billions of rows)
REFRESH MATERIALIZED VIEW mv_daily_sales;
-- Cost: scan 1B rows, group, aggregate
-- Time: 30 minutes

-- Dynamic table (incremental refresh)
CREATE DYNAMIC TABLE dt_daily_sales
TARGET LAG = 1 DAY
WAREHOUSE = compute_wh
AS
SELECT 
  DATE(order_date) as sale_date,
  product_id,
  SUM(order_value) as daily_revenue,
  COUNT(*) as transaction_count
FROM orders
GROUP BY DATE(order_date), product_id;

-- System automatically:
-- 1. Identifies changed orders since last refresh
-- 2. Recomputes daily summaries for changed dates only
-- 3. Updates materialized results incrementally
-- Cost: scan 100K changed rows + merge
-- Time: 2 minutes
`

Coginiti Perspective

CoginitiScript's incremental publication provides capabilities comparable to dynamic tables but with cross-platform portability. While Snowflake's dynamic tables are platform-specific, CoginitiScript's append and merge strategies work across any supported platform. The publication.Incremental() function lets the same block handle both initial load and incremental refresh, and the analytics catalog governs the refresh logic with version control and code review, adding a governance layer that platform-native dynamic tables lack.

Related Concepts