Snowflake to HeliosDB Migration Guide¶

Version: 1.0 Last Updated: January 2026 Compatibility Target: Snowflake (All Editions)

Table of Contents¶

Introduction
Compatibility Overview
Pre-Migration Assessment
Conceptual Mapping
Connection String Migration
Data Type Mapping
Virtual Warehouse Migration
Time Travel Migration
VARIANT Semi-Structured Data Migration
FLATTEN Function Migration
COPY INTO Migration
Stage and Storage Integration
Application Connectivity
SQL Function Mapping
Known Limitations
Performance Considerations
Post-Migration Validation
Common Issues and Troubleshooting
Rollback Procedures

1. Introduction¶

1.1 Why Migrate from Snowflake to HeliosDB?¶

Organizations choose to migrate from Snowflake to HeliosDB for several strategic reasons:

Cost Optimization¶

Cost Factor	Snowflake	HeliosDB	Benefit
Compute Credits	Per-second billing	Fixed resource allocation	Predictable costs
Storage	Separated pricing	Unified pricing	Simplified billing
Data Transfer	Egress charges	Included	70-90% reduction
Multi-cluster Warehouse	Premium charges	Included	Included by default
Time Travel (90 days)	Additional storage cost	Included	No additional charge
Fail-safe	Additional storage cost	Configurable	Flexible retention

Typical Annual Savings: 40-70% for data-intensive workloads

Multi-Model Capabilities¶

HeliosDB extends Snowflake's capabilities with native multi-model support:

Relational SQL: Full Snowflake SQL compatibility (90%+)
Document/JSON: MongoDB-compatible document storage alongside VARIANT
Key-Value: Redis-compatible operations for caching
Time-Series: Native time-series optimizations
Graph: Cypher query language support
Vector: AI/ML embeddings with similarity search

Architecture Advantages¶

Capability	Snowflake	HeliosDB
Deployment	Cloud-only (SaaS)	Cloud + On-premises
Data Sovereignty	Limited regions	Full control
Multi-Protocol	SQL only	9+ protocols
Real-time Streaming	Streams/Tasks	Native CDC + Kafka
ML/AI Integration	External tools	Native in-database
HTAP	Separate warehouse	Unified workload

1.2 Migration Goals¶

This guide helps you achieve:

Zero data loss during migration with full VARIANT support
Minimal application changes due to 90%+ SQL compatibility
Preserved Time Travel functionality with identical syntax
Seamless semi-structured data migration with full FLATTEN support
Improved cost efficiency with predictable resource pricing

2. Compatibility Overview¶

2.1 Snowflake Protocol Support in HeliosDB¶

HeliosDB implements Snowflake SQL API compatibility for seamless integration:

Category	Coverage	Notes
SQL API	90%+	Full statement execution
Authentication	Supported	Key pair, password
Time Travel	100%	AT/BEFORE syntax fully supported
VARIANT	100%	Full semi-structured support
FLATTEN	100%	All modes supported
Virtual Warehouses	100%	All operations supported
COPY INTO	100%	All file formats

2.2 Supported Snowflake Features¶

Fully Supported (Direct Migration)¶

DDL: CREATE DATABASE/SCHEMA/TABLE/VIEW, ALTER, DROP
DML: SELECT, INSERT, UPDATE, DELETE, MERGE
Transactions: COMMIT, ROLLBACK, BEGIN/END TRANSACTION
Time Travel: AT(TIMESTAMP), AT(OFFSET), AT(STATEMENT), BEFORE, UNDROP
Semi-Structured: VARIANT, OBJECT, ARRAY data types
Functions: PARSE_JSON, FLATTEN, OBJECT_CONSTRUCT, ARRAY_AGG
Warehouses: CREATE/ALTER/DROP WAREHOUSE, suspend/resume
Data Loading: COPY INTO from all supported formats

Partially Supported (Requires Modification)¶

Snowflake Feature	HeliosDB Support	Migration Notes
GEOGRAPHY type	Full support	Uses PostGIS-compatible functions
External Tables	Stage-based access	Configure storage integration
Materialized Views	Full support	Same refresh semantics
User-Defined Functions (SQL)	Supported	UDF syntax compatible

Not Supported (Requires Redesign)¶

Snowflake Feature	HeliosDB Alternative	Migration Approach
Streams	HeliosDB CDC	Use Change Data Capture
Tasks	HeliosDB Scheduler	Use DBMS_SCHEDULER or cron
Pipes	COPY INTO with scheduling	Batch or streaming ingestion
Dynamic Tables	Materialized Views	Use MVs with refresh
Snowpark (Python/Java/Scala)	Native SQL + UDFs	Rewrite as SQL UDFs
External Functions	HTTP/REST integration	Use HeliosDB REST API

2.3 SQL Compatibility Matrix¶

SQL Feature	Snowflake	HeliosDB	Notes
CTEs (WITH clause)	Yes	Yes	Identical syntax
Window Functions	Yes	Yes	All functions supported
PIVOT/UNPIVOT	Yes	Yes	Same syntax
QUALIFY	Yes	Yes	Filter window results
SAMPLE/TABLESAMPLE	Yes	Yes	Row sampling
MATCH_RECOGNIZE	Limited	No	Use window functions
Recursive CTEs	Yes	Yes	Same syntax
LATERAL joins	Yes	Yes	Same syntax

3. Pre-Migration Assessment¶

3.1 Snowflake Environment Inventory¶

Before migration, catalog your Snowflake environment:

Database and Schema Inventory¶

-- List all databases
SHOW DATABASES;

-- List all schemas in a database
SHOW SCHEMAS IN DATABASE my_database;

-- Get database sizes
SELECT
    DATABASE_NAME,
    DATABASE_OWNER,
    CREATED,
    LAST_ALTERED,
    COMMENT
FROM INFORMATION_SCHEMA.DATABASES
ORDER BY DATABASE_NAME;

-- Table inventory with sizes
SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE,
    ROW_COUNT,
    BYTES,
    RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')
ORDER BY BYTES DESC NULLS LAST;

VARIANT Column Analysis¶

-- Find all VARIANT columns
SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('VARIANT', 'OBJECT', 'ARRAY')
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;

-- Sample VARIANT structures (for migration planning)
SELECT
    column_name,
    TYPEOF(column_name) as variant_type,
    COUNT(*) as occurrences
FROM my_variant_table
GROUP BY 1, 2;

3.2 Virtual Warehouse Analysis¶

-- Warehouse inventory
SHOW WAREHOUSES;

-- Warehouse usage patterns
SELECT
    WAREHOUSE_NAME,
    START_TIME,
    END_TIME,
    WAREHOUSE_SIZE,
    CREDITS_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

-- Query patterns by warehouse
SELECT
    WAREHOUSE_NAME,
    COUNT(*) as query_count,
    AVG(EXECUTION_TIME) as avg_execution_ms,
    SUM(CREDITS_USED_CLOUD_SERVICES) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY WAREHOUSE_NAME
ORDER BY query_count DESC;

3.3 Time Travel Usage Analysis¶

-- Check Time Travel retention settings
SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE RETENTION_TIME > 0
ORDER BY RETENTION_TIME DESC;

-- Find Time Travel queries in history
SELECT
    QUERY_TEXT,
    START_TIME,
    EXECUTION_STATUS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT ILIKE '%AT(TIMESTAMP%'
   OR QUERY_TEXT ILIKE '%AT(OFFSET%'
   OR QUERY_TEXT ILIKE '%BEFORE(STATEMENT%'
LIMIT 100;

3.4 Stream and Task Inventory (Requires Redesign)¶

-- List all streams
SHOW STREAMS;

-- Stream details
SELECT
    STREAM_CATALOG,
    STREAM_SCHEMA,
    STREAM_NAME,
    TABLE_NAME,
    TYPE,
    STALE
FROM INFORMATION_SCHEMA.STREAMS;

-- List all tasks
SHOW TASKS;

-- Task details
SELECT
    NAME,
    DATABASE_NAME,
    SCHEMA_NAME,
    OWNER,
    SCHEDULE,
    STATE
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE SCHEDULED_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP());

3.5 Migration Complexity Assessment¶

Calculate your migration complexity score:

Factor	Points	Your Score
Databases < 5	1
Databases 5-20	2
Databases > 20	3
VARIANT columns < 10	1
VARIANT columns 10-50	2
VARIANT columns > 50	3
No Streams/Tasks	0
Uses Streams/Tasks	3
No Snowpark usage	0
Uses Snowpark	4
Data size < 100GB	1
Data size 100GB-1TB	2
Data size > 1TB	3

Total Score Interpretation: - 1-5: Simple migration (1-2 weeks) - 6-10: Medium complexity (2-4 weeks) - 11+: Complex migration (4+ weeks)

4. Conceptual Mapping¶

4.1 Snowflake to HeliosDB Object Mapping¶

Snowflake Concept	HeliosDB Equivalent	Notes
Account	Cluster/Instance	HeliosDB instance
Virtual Warehouse	Compute Pool	Resource allocation
Database	Database	Direct mapping
Schema	Schema	Direct mapping
Table	Table	Full compatibility
View	View	Full compatibility
Materialized View	Materialized View	Same functionality
Stage	Storage Mount	File access
File Format	File Format	Same concept
Sequence	Sequence	Compatible syntax
User	User	Direct mapping
Role	Role	Direct mapping
Resource Monitor	Resource Limits	Different implementation

4.2 Warehouse Size to Compute Pool Mapping¶

Snowflake Size	Credits/Hour	HeliosDB Compute Pool	Recommended Memory
X-Small	1	xs-pool	8GB
Small	2	small-pool	16GB
Medium	4	medium-pool	32GB
Large	8	large-pool	64GB
X-Large	16	xlarge-pool	128GB
2X-Large	32	2xlarge-pool	256GB
3X-Large	64	3xlarge-pool	512GB
4X-Large	128	4xlarge-pool	1TB

4.3 Storage Tier Mapping¶

Snowflake Storage	HeliosDB Storage	Configuration
Active storage	Hot tier	Default storage
Time Travel	MVCC + retention	Configurable retention
Fail-safe	Archive tier	Optional configuration
External stage (S3)	S3 storage mount	Native S3 access
External stage (Azure)	Azure storage mount	Native Azure access
External stage (GCS)	GCS storage mount	Native GCS access

5. Connection String Migration¶

5.1 Connection Parameter Mapping¶

Snowflake Parameter	HeliosDB Parameter	Notes
`account`	`account`	Use "heliosdb" or custom
`user`	`user`	Same concept
`password`	`password`	Same concept
`warehouse`	`warehouse`	Maps to compute pool
`database`	`database`	Same concept
`schema`	`schema`	Same concept
`role`	`role`	Same concept
`host`	`host`	HeliosDB server hostname
`port`	`port`	Default: 443

5.2 Connection String Examples¶

Before (Snowflake)¶

# Standard connection
snowflake://user:password@account.snowflakecomputing.com:443/database/schema?warehouse=COMPUTE_WH

# With full parameters
snowflake://my_user:my_password@xy12345.us-east-1.snowflakecomputing.com:443/MY_DATABASE/PUBLIC?warehouse=ANALYTICS_WH&role=ANALYST

After (HeliosDB)¶

# Standard connection
snowflake://user:password@heliosdb.example.com:443/database/schema?warehouse=COMPUTE_WH&account=heliosdb

# With full parameters
snowflake://my_user:my_password@heliosdb.example.com:443/MY_DATABASE/PUBLIC?warehouse=ANALYTICS_WH&role=ANALYST&account=heliosdb

5.3 Python Connection Migration¶

Snowflake (Original)¶

import snowflake.connector

# Snowflake connection
conn = snowflake.connector.connect(
    account="xy12345.us-east-1",
    user="my_user",
    password="my_password",
    warehouse="COMPUTE_WH",
    database="MY_DATABASE",
    schema="PUBLIC",
    role="ANALYST"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users LIMIT 10")
rows = cursor.fetchall()
conn.close()

HeliosDB (Migrated)¶

import snowflake.connector

# HeliosDB connection - only host/account changes
conn = snowflake.connector.connect(
    host="heliosdb.example.com",  # Changed: HeliosDB host
    port=443,
    account="heliosdb",           # Changed: HeliosDB account identifier
    user="my_user",
    password="my_password",
    warehouse="COMPUTE_WH",       # Same warehouse name
    database="MY_DATABASE",       # Same database
    schema="PUBLIC",              # Same schema
    role="ANALYST"                # Same role
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users LIMIT 10")  # Same SQL
rows = cursor.fetchall()
conn.close()

5.4 JavaScript/Node.js Connection Migration¶

Snowflake (Original)¶

const snowflake = require('snowflake-sdk');

const connection = snowflake.createConnection({
    account: 'xy12345.us-east-1',
    username: 'my_user',
    password: 'my_password',
    warehouse: 'COMPUTE_WH',
    database: 'MY_DATABASE',
    schema: 'PUBLIC'
});

connection.connect((err, conn) => {
    if (err) {
        console.error('Connection failed:', err);
        return;
    }
    console.log('Connected to Snowflake');
});

HeliosDB (Migrated)¶

const snowflake = require('snowflake-sdk');

const connection = snowflake.createConnection({
    host: 'heliosdb.example.com',  // Added: HeliosDB host
    port: 443,                      // Added: Port
    account: 'heliosdb',            // Changed: HeliosDB account
    username: 'my_user',
    password: 'my_password',
    warehouse: 'COMPUTE_WH',        // Same warehouse
    database: 'MY_DATABASE',        // Same database
    schema: 'PUBLIC'                // Same schema
});

connection.connect((err, conn) => {
    if (err) {
        console.error('Connection failed:', err);
        return;
    }
    console.log('Connected to HeliosDB');
});

5.5 Environment Variable Migration¶

Snowflake Environment Variables¶

# Snowflake
export SNOWFLAKE_ACCOUNT=xy12345.us-east-1
export SNOWFLAKE_USER=my_user
export SNOWFLAKE_PASSWORD=my_password
export SNOWFLAKE_WAREHOUSE=COMPUTE_WH
export SNOWFLAKE_DATABASE=MY_DATABASE
export SNOWFLAKE_SCHEMA=PUBLIC

HeliosDB Environment Variables¶

# HeliosDB - compatible variable names
export SNOWFLAKE_HOST=heliosdb.example.com
export SNOWFLAKE_PORT=443
export SNOWFLAKE_ACCOUNT=heliosdb
export SNOWFLAKE_USER=my_user
export SNOWFLAKE_PASSWORD=my_password
export SNOWFLAKE_WAREHOUSE=COMPUTE_WH
export SNOWFLAKE_DATABASE=MY_DATABASE
export SNOWFLAKE_SCHEMA=PUBLIC

6. Data Type Mapping¶

6.1 Complete Data Type Mapping¶

Snowflake Type	HeliosDB Type	Notes
`NUMBER`	`NUMBER`	Full precision support
`NUMBER(p,s)`	`NUMBER(p,s)`	Identical
`DECIMAL`	`DECIMAL`	Alias for NUMBER
`NUMERIC`	`NUMERIC`	Alias for NUMBER
`INT`	`INT`	INTEGER alias
`INTEGER`	`INTEGER`	Same
`BIGINT`	`BIGINT`	Same
`SMALLINT`	`SMALLINT`	Same
`TINYINT`	`TINYINT`	Same
`BYTEINT`	`TINYINT`	Maps to TINYINT
`FLOAT`	`FLOAT`	Double precision
`FLOAT4`	`FLOAT4`	Single precision
`FLOAT8`	`FLOAT8`	Double precision
`DOUBLE`	`DOUBLE`	Same
`DOUBLE PRECISION`	`DOUBLE PRECISION`	Same
`REAL`	`REAL`	Same
`VARCHAR`	`VARCHAR`	Variable character
`VARCHAR(n)`	`VARCHAR(n)`	With length
`CHAR`	`CHAR`	Fixed character
`CHAR(n)`	`CHAR(n)`	With length
`STRING`	`VARCHAR`	Maps to VARCHAR
`TEXT`	`TEXT`	Unlimited text
`BINARY`	`BINARY`	Binary data
`BINARY(n)`	`BINARY(n)`	With length
`VARBINARY`	`VARBINARY`	Variable binary
`BOOLEAN`	`BOOLEAN`	Same
`DATE`	`DATE`	Same
`TIME`	`TIME`	Same
`TIME(p)`	`TIME(p)`	With precision
`TIMESTAMP`	`TIMESTAMP`	Default NTZ behavior
`TIMESTAMP_NTZ`	`TIMESTAMP_NTZ`	No timezone
`TIMESTAMP_LTZ`	`TIMESTAMP_LTZ`	Local timezone
`TIMESTAMP_TZ`	`TIMESTAMP_TZ`	With timezone
`VARIANT`	`VARIANT`	Full support
`OBJECT`	`OBJECT`	JSON object
`ARRAY`	`ARRAY`	JSON array
`GEOGRAPHY`	`GEOGRAPHY`	Geospatial
`GEOMETRY`	`GEOMETRY`	Geospatial

6.2 NUMBER Type Migration¶

-- Snowflake NUMBER types
CREATE TABLE snowflake_numbers (
    id NUMBER(38,0),              -- Integer
    price NUMBER(10,2),           -- Decimal
    quantity NUMBER,              -- Default precision
    rate NUMBER(5,4),             -- High precision decimal
    big_number NUMBER(38,0)       -- Max precision
);

-- HeliosDB (identical syntax)
CREATE TABLE heliosdb_numbers (
    id NUMBER(38,0),              -- Integer
    price NUMBER(10,2),           -- Decimal
    quantity NUMBER,              -- Default precision
    rate NUMBER(5,4),             -- High precision decimal
    big_number NUMBER(38,0)       -- Max precision
);

6.3 Timestamp Type Migration¶

-- Snowflake timestamp types
CREATE TABLE snowflake_timestamps (
    created_at TIMESTAMP_NTZ,     -- No timezone
    updated_at TIMESTAMP_LTZ,     -- Local timezone
    event_time TIMESTAMP_TZ,      -- With timezone
    log_time TIMESTAMP            -- Default (NTZ)
);

-- HeliosDB (identical syntax)
CREATE TABLE heliosdb_timestamps (
    created_at TIMESTAMP_NTZ,     -- No timezone
    updated_at TIMESTAMP_LTZ,     -- Local timezone
    event_time TIMESTAMP_TZ,      -- With timezone
    log_time TIMESTAMP            -- Default (NTZ)
);

-- Timestamp operations work identically
SELECT
    created_at,
    CONVERT_TIMEZONE('UTC', 'America/New_York', created_at) as eastern_time,
    DATEADD(day, 7, created_at) as week_later,
    DATEDIFF(hour, created_at, CURRENT_TIMESTAMP()) as hours_ago
FROM timestamps_table;

6.4 Semi-Structured Type Migration¶

-- Snowflake semi-structured types
CREATE TABLE snowflake_json (
    id INT,
    data VARIANT,                 -- Any JSON value
    metadata OBJECT,              -- JSON object
    tags ARRAY                    -- JSON array
);

-- HeliosDB (identical syntax)
CREATE TABLE heliosdb_json (
    id INT,
    data VARIANT,                 -- Any JSON value
    metadata OBJECT,              -- JSON object
    tags ARRAY                    -- JSON array
);

-- Insert works identically
INSERT INTO heliosdb_json (id, data, metadata, tags)
VALUES (
    1,
    PARSE_JSON('{"name": "Alice", "age": 30}'),
    OBJECT_CONSTRUCT('source', 'web', 'version', '2.0'),
    ARRAY_CONSTRUCT('premium', 'active', 'verified')
);

7. Virtual Warehouse Migration¶

7.1 Warehouse Creation Migration¶

Snowflake Warehouse¶

-- Snowflake warehouse creation
CREATE WAREHOUSE analytics_wh WITH
    WAREHOUSE_SIZE = 'LARGE'
    WAREHOUSE_TYPE = 'STANDARD'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 4
    SCALING_POLICY = 'STANDARD'
    INITIALLY_SUSPENDED = TRUE
    COMMENT = 'Analytics workload warehouse';

HeliosDB Warehouse (Identical)¶

-- HeliosDB warehouse creation (same syntax)
CREATE WAREHOUSE analytics_wh WITH
    WAREHOUSE_SIZE = 'LARGE'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 4
    SCALING_POLICY = 'STANDARD'
    INITIALLY_SUSPENDED = TRUE;

7.2 Warehouse Management Operations¶

-- All operations work identically in HeliosDB

-- Resize warehouse
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'XLARGE';

-- Suspend warehouse
ALTER WAREHOUSE analytics_wh SUSPEND;

-- Resume warehouse
ALTER WAREHOUSE analytics_wh RESUME;

-- Modify auto-suspend
ALTER WAREHOUSE analytics_wh SET AUTO_SUSPEND = 600;

-- Scale cluster count
ALTER WAREHOUSE analytics_wh SET
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 8;

-- Drop warehouse
DROP WAREHOUSE IF EXISTS temp_wh;

-- Show warehouse status
SHOW WAREHOUSES;
SHOW WAREHOUSES LIKE 'ANALYTICS%';

-- Switch warehouse context
USE WAREHOUSE analytics_wh;

7.3 Warehouse Size Equivalence¶

Snowflake Size	HeliosDB Compute Allocation
X-Small	1 vCPU, 8GB RAM
Small	2 vCPUs, 16GB RAM
Medium	4 vCPUs, 32GB RAM
Large	8 vCPUs, 64GB RAM
X-Large	16 vCPUs, 128GB RAM
2X-Large	32 vCPUs, 256GB RAM
3X-Large	64 vCPUs, 512GB RAM
4X-Large	128 vCPUs, 1TB RAM

7.4 Multi-Cluster Warehouse Migration¶

-- Snowflake multi-cluster warehouse
CREATE WAREHOUSE scaling_wh WITH
    WAREHOUSE_SIZE = 'MEDIUM'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 10
    SCALING_POLICY = 'STANDARD';

-- HeliosDB multi-cluster warehouse (same syntax)
CREATE WAREHOUSE scaling_wh WITH
    WAREHOUSE_SIZE = 'MEDIUM'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 10
    SCALING_POLICY = 'STANDARD';

-- Economy scaling policy
CREATE WAREHOUSE economy_wh WITH
    WAREHOUSE_SIZE = 'SMALL'
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 4
    SCALING_POLICY = 'ECONOMY';

8. Time Travel Migration¶

8.1 Time Travel Syntax Compatibility¶

HeliosDB supports the exact same Time Travel syntax as Snowflake:

AT(TIMESTAMP) Queries¶

-- Snowflake Time Travel
SELECT * FROM orders
AT(TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP_NTZ);

-- HeliosDB Time Travel (identical)
SELECT * FROM orders
AT(TIMESTAMP => '2024-01-15 10:30:00'::TIMESTAMP_NTZ);

-- Using SYSTIMESTAMP
SELECT * FROM orders
AT(TIMESTAMP => DATEADD(hours, -24, CURRENT_TIMESTAMP()));

-- HeliosDB equivalent
SELECT * FROM orders
AT(TIMESTAMP => DATEADD(hours, -24, CURRENT_TIMESTAMP()));

AT(OFFSET) Queries¶

-- Snowflake: Query data from 1 hour ago
SELECT * FROM inventory
AT(OFFSET => -3600);

-- HeliosDB: Identical syntax
SELECT * FROM inventory
AT(OFFSET => -3600);

-- Query from 1 day ago
SELECT * FROM daily_metrics
AT(OFFSET => -86400);

AT(STATEMENT) and BEFORE(STATEMENT) Queries¶

-- Get query ID from query history
-- Snowflake
SELECT query_id FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE QUERY_TEXT LIKE 'UPDATE orders%'
ORDER BY START_TIME DESC
LIMIT 1;

-- Query data at specific statement
SELECT * FROM orders
AT(STATEMENT => '01a1b2c3-d4e5-f6a7-b8c9-d0e1f2a3b4c5');

-- Query data before specific statement
SELECT * FROM orders
BEFORE(STATEMENT => '01a1b2c3-d4e5-f6a7-b8c9-d0e1f2a3b4c5');

-- HeliosDB: Both syntaxes work identically
SELECT * FROM orders
AT(STATEMENT => 'statement-id');

SELECT * FROM orders
BEFORE(STATEMENT => 'statement-id');

8.2 CHANGES Clause Migration¶

-- Snowflake: Track changes
SELECT *
FROM orders
CHANGES(INFORMATION => DEFAULT)
AT(TIMESTAMP => '2024-01-01 00:00:00')
END(TIMESTAMP => '2024-01-02 00:00:00');

-- HeliosDB: Same syntax
SELECT *
FROM orders
CHANGES(INFORMATION => DEFAULT)
AT(TIMESTAMP => '2024-01-01 00:00:00')
END(TIMESTAMP => '2024-01-02 00:00:00');

-- With append-only information
SELECT *
FROM events
CHANGES(INFORMATION => APPEND_ONLY)
AT(TIMESTAMP => DATEADD(day, -1, CURRENT_TIMESTAMP()))
END(TIMESTAMP => CURRENT_TIMESTAMP());

8.3 UNDROP Migration¶

-- Snowflake: Undrop table
DROP TABLE orders;
UNDROP TABLE orders;

-- HeliosDB: Same syntax
DROP TABLE orders;
UNDROP TABLE orders;

-- Undrop schema
DROP SCHEMA analytics;
UNDROP SCHEMA analytics;

-- Undrop database
DROP DATABASE warehouse_db;
UNDROP DATABASE warehouse_db;

8.4 Clone with Time Travel¶

-- Snowflake: Clone at historical point
CREATE TABLE orders_backup CLONE orders
AT(TIMESTAMP => '2024-01-15 00:00:00');

-- HeliosDB: Same syntax
CREATE TABLE orders_backup CLONE orders
AT(TIMESTAMP => '2024-01-15 00:00:00');

-- Clone at offset
CREATE TABLE inventory_snapshot CLONE inventory
AT(OFFSET => -86400);  -- 24 hours ago

-- Clone schema
CREATE SCHEMA analytics_backup CLONE analytics
AT(TIMESTAMP => '2024-01-01 00:00:00');

8.5 Time Travel Configuration¶

-- Snowflake: Set retention period
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- HeliosDB: Same syntax
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- Check current retention
SHOW TABLES LIKE 'orders';

-- Create table with specific retention
CREATE TABLE audit_log (
    id INT,
    action VARCHAR(100),
    timestamp TIMESTAMP
)
DATA_RETENTION_TIME_IN_DAYS = 90;

9. VARIANT Semi-Structured Data Migration¶

9.1 VARIANT Column Operations¶

-- Create table with VARIANT
CREATE TABLE events (
    id INT,
    event_time TIMESTAMP,
    event_data VARIANT
);

-- Insert JSON data
INSERT INTO events (id, event_time, event_data)
VALUES (
    1,
    CURRENT_TIMESTAMP(),
    PARSE_JSON('{
        "user_id": 12345,
        "action": "purchase",
        "items": [
            {"product_id": 101, "name": "Widget", "price": 29.99},
            {"product_id": 102, "name": "Gadget", "price": 49.99}
        ],
        "metadata": {
            "browser": "Chrome",
            "platform": "Windows"
        }
    }')
);

-- Query VARIANT data (works identically in HeliosDB)
SELECT
    id,
    event_data:user_id::INT as user_id,
    event_data:action::STRING as action,
    event_data:items[0].name::STRING as first_item,
    event_data:metadata.browser::STRING as browser
FROM events;

9.2 Path Notation¶

-- Snowflake path notation
SELECT
    data:level1:level2:field::STRING as nested_value,
    data:array[0].property::NUMBER as array_value,
    data:"special-key"::STRING as special_key  -- Keys with special chars
FROM json_table;

-- HeliosDB path notation (identical)
SELECT
    data:level1:level2:field::STRING as nested_value,
    data:array[0].property::NUMBER as array_value,
    data:"special-key"::STRING as special_key
FROM json_table;

9.3 Type Casting from VARIANT¶

-- All type casts work identically
SELECT
    data:id::INT as id_int,
    data:id::STRING as id_string,
    data:price::FLOAT as price_float,
    data:price::NUMBER(10,2) as price_decimal,
    data:active::BOOLEAN as is_active,
    data:timestamp::TIMESTAMP as event_timestamp,
    data:date::DATE as event_date,
    data:nested::VARIANT as nested_variant,
    data:items::ARRAY as items_array,
    data:config::OBJECT as config_object
FROM variant_table;

9.4 VARIANT Functions¶

-- PARSE_JSON
SELECT PARSE_JSON('{"name": "Alice", "age": 30}') as json_data;

-- TO_JSON
SELECT TO_JSON(OBJECT_CONSTRUCT('name', 'Alice', 'age', 30)) as json_string;

-- TRY_PARSE_JSON (safe parsing)
SELECT TRY_PARSE_JSON(potentially_invalid_json) as parsed;

-- TO_VARIANT
SELECT TO_VARIANT(123) as variant_number;
SELECT TO_VARIANT('hello') as variant_string;

-- GET and GET_PATH
SELECT
    GET(data, 'field') as value1,
    GET_PATH(data, 'level1.level2.field') as value2
FROM json_table;

-- TYPEOF
SELECT
    TYPEOF(data:number_field),    -- Returns 'INTEGER' or 'DECIMAL'
    TYPEOF(data:string_field),    -- Returns 'VARCHAR'
    TYPEOF(data:array_field),     -- Returns 'ARRAY'
    TYPEOF(data:object_field)     -- Returns 'OBJECT'
FROM variant_table;

9.5 OBJECT Functions¶

-- OBJECT_CONSTRUCT
SELECT OBJECT_CONSTRUCT(
    'id', user_id,
    'name', user_name,
    'email', email,
    'metadata', OBJECT_CONSTRUCT(
        'created', created_at,
        'updated', updated_at
    )
) as user_json
FROM users;

-- OBJECT_CONSTRUCT_KEEP_NULL (preserve NULL values)
SELECT OBJECT_CONSTRUCT_KEEP_NULL(
    'field1', value1,
    'field2', NULL,
    'field3', value3
);

-- OBJECT_INSERT
SELECT OBJECT_INSERT(existing_object, 'new_key', 'new_value') as updated;

-- OBJECT_DELETE
SELECT OBJECT_DELETE(existing_object, 'key_to_remove') as updated;

-- OBJECT_KEYS
SELECT OBJECT_KEYS(data) as keys FROM json_table;

9.6 ARRAY Functions¶

-- ARRAY_CONSTRUCT
SELECT ARRAY_CONSTRUCT(1, 2, 3, 4, 5) as numbers;
SELECT ARRAY_CONSTRUCT('a', 'b', 'c') as letters;

-- ARRAY_AGG
SELECT
    category,
    ARRAY_AGG(product_name) as products,
    ARRAY_AGG(DISTINCT product_name) as unique_products
FROM products
GROUP BY category;

-- ARRAY_SIZE
SELECT ARRAY_SIZE(data:items) as item_count FROM orders;

-- ARRAY_CONTAINS
SELECT *
FROM users
WHERE ARRAY_CONTAINS('admin'::VARIANT, data:roles);

-- ARRAY_APPEND / ARRAY_PREPEND
SELECT
    ARRAY_APPEND(existing_array, 'new_element') as appended,
    ARRAY_PREPEND(existing_array, 'first_element') as prepended;

-- ARRAY_CAT (concatenate arrays)
SELECT ARRAY_CAT(array1, array2) as combined;

-- ARRAY_COMPACT (remove NULLs)
SELECT ARRAY_COMPACT(array_with_nulls) as cleaned;

-- ARRAY_DISTINCT
SELECT ARRAY_DISTINCT(array_with_duplicates) as unique_values;

-- ARRAY_INTERSECTION
SELECT ARRAY_INTERSECTION(array1, array2) as common_elements;

-- ARRAY_SLICE
SELECT ARRAY_SLICE(large_array, 0, 10) as first_ten;

10. FLATTEN Function Migration¶

10.1 Basic FLATTEN¶

-- Snowflake FLATTEN
SELECT
    o.id as order_id,
    o.customer_id,
    f.value:product_id::INT as product_id,
    f.value:name::STRING as product_name,
    f.value:price::FLOAT as price,
    f.value:quantity::INT as quantity
FROM orders o,
LATERAL FLATTEN(input => o.items) f;

-- HeliosDB FLATTEN (identical syntax)
SELECT
    o.id as order_id,
    o.customer_id,
    f.value:product_id::INT as product_id,
    f.value:name::STRING as product_name,
    f.value:price::FLOAT as price,
    f.value:quantity::INT as quantity
FROM orders o,
LATERAL FLATTEN(input => o.items) f;

10.2 FLATTEN Output Columns¶

Both Snowflake and HeliosDB FLATTEN return the same columns:

Column	Description	Example
`SEQ`	Sequence number	1
`KEY`	Key (for objects)	"name"
`PATH`	Full path to element	"[0].items[1]"
`INDEX`	Array index	0, 1, 2...
`VALUE`	The element value	{"id": 1, ...}
`THIS`	The input expression	Original array

-- Access all FLATTEN output columns
SELECT
    f.seq,
    f.key,
    f.path,
    f.index,
    f.value,
    f.this
FROM json_table t,
LATERAL FLATTEN(input => t.data:items) f;

10.3 FLATTEN with PATH Parameter¶

-- Flatten nested structure at specific path
SELECT
    t.id,
    f.value:sku::STRING as sku,
    f.value:quantity::INT as qty
FROM orders t,
LATERAL FLATTEN(input => t.data, path => 'order.line_items') f;

-- HeliosDB equivalent (same syntax)
SELECT
    t.id,
    f.value:sku::STRING as sku,
    f.value:quantity::INT as qty
FROM orders t,
LATERAL FLATTEN(input => t.data, path => 'order.line_items') f;

10.4 FLATTEN with OUTER¶

-- OUTER preserves rows with empty/null arrays
SELECT
    o.id,
    o.customer_id,
    f.value:name::STRING as item_name
FROM orders o,
LATERAL FLATTEN(input => o.items, outer => true) f;

-- Without OUTER, orders with no items are excluded
-- With OUTER, they appear with NULL for flattened columns

10.5 FLATTEN with MODE¶

-- MODE = 'ARRAY' (default) - flatten arrays only
SELECT f.value
FROM data,
LATERAL FLATTEN(input => data:array_field, mode => 'ARRAY') f;

-- MODE = 'OBJECT' - flatten object keys
SELECT f.key, f.value
FROM data,
LATERAL FLATTEN(input => data:object_field, mode => 'OBJECT') f;

-- MODE = 'BOTH' - flatten both arrays and objects
SELECT f.key, f.value
FROM data,
LATERAL FLATTEN(input => data:mixed_field, mode => 'BOTH') f;

10.6 Recursive FLATTEN¶

-- Recursively flatten nested structures
SELECT
    f.path as full_path,
    f.key as field_name,
    f.value as field_value,
    TYPEOF(f.value) as value_type
FROM config_table c,
LATERAL FLATTEN(input => c.config, recursive => true) f
WHERE TYPEOF(f.value) NOT IN ('OBJECT', 'ARRAY');

-- Useful for flattening deeply nested JSON
-- Returns all leaf values with their full paths

10.7 Multiple FLATTEN Operations¶

-- Flatten multiple arrays
SELECT
    o.id as order_id,
    items.value:name::STRING as item_name,
    tags.value::STRING as tag
FROM orders o,
LATERAL FLATTEN(input => o.data:items) items,
LATERAL FLATTEN(input => items.value:tags) tags;

-- Cross-product of all items with all their tags

10.8 FLATTEN with Aggregation¶

-- Aggregate over flattened data
SELECT
    o.customer_id,
    COUNT(DISTINCT f.value:product_id) as unique_products,
    SUM(f.value:price::FLOAT * f.value:quantity::INT) as total_value
FROM orders o,
LATERAL FLATTEN(input => o.items) f
GROUP BY o.customer_id;

11. COPY INTO Migration¶

11.1 Basic COPY INTO Syntax¶

-- Snowflake COPY INTO
COPY INTO my_table
FROM @my_stage/data/
FILE_FORMAT = (TYPE = 'JSON')
ON_ERROR = 'CONTINUE';

-- HeliosDB COPY INTO (identical)
COPY INTO my_table
FROM @my_stage/data/
FILE_FORMAT = (TYPE = 'JSON')
ON_ERROR = 'CONTINUE';

11.2 File Format Specifications¶

CSV Format¶

-- Snowflake CSV
COPY INTO my_table
FROM @stage/csv_files/
FILE_FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    NULL_IF = ('NULL', 'null', '')
    EMPTY_FIELD_AS_NULL = TRUE
    COMPRESSION = 'GZIP'
);

-- HeliosDB CSV (same syntax)
COPY INTO my_table
FROM @stage/csv_files/
FILE_FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    RECORD_DELIMITER = '\n'
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    NULL_IF = ('NULL', 'null', '')
    EMPTY_FIELD_AS_NULL = TRUE
    COMPRESSION = 'GZIP'
);

JSON Format¶

-- JSON file format
COPY INTO my_table
FROM @stage/json_files/
FILE_FORMAT = (
    TYPE = 'JSON'
    STRIP_OUTER_ARRAY = TRUE
    STRIP_NULL_VALUES = FALSE
    COMPRESSION = 'AUTO'
);

Parquet Format¶

-- Parquet file format
COPY INTO my_table
FROM @stage/parquet_files/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Avro Format¶

-- Avro file format
COPY INTO my_table
FROM @stage/avro_files/
FILE_FORMAT = (TYPE = 'AVRO');

ORC Format¶

-- ORC file format
COPY INTO my_table
FROM @stage/orc_files/
FILE_FORMAT = (TYPE = 'ORC');

11.3 COPY INTO from Cloud Storage¶

Amazon S3¶

-- Snowflake S3 access
COPY INTO my_table
FROM 's3://my-bucket/data/path/'
CREDENTIALS = (
    AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
    AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
)
FILE_FORMAT = (TYPE = 'PARQUET')
PATTERN = '.*\.parquet';

-- HeliosDB S3 access (same syntax)
COPY INTO my_table
FROM 's3://my-bucket/data/path/'
CREDENTIALS = (
    AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
    AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
)
FILE_FORMAT = (TYPE = 'PARQUET')
PATTERN = '.*\.parquet';

Azure Blob Storage¶

-- Azure Blob access
COPY INTO my_table
FROM 'azure://myaccount.blob.core.windows.net/mycontainer/path/'
CREDENTIALS = (AZURE_SAS_TOKEN = '?sv=2020-08-04&ss=b&srt=sco...')
FILE_FORMAT = (TYPE = 'JSON');

Google Cloud Storage¶

-- GCS access
COPY INTO my_table
FROM 'gcs://my-bucket/path/'
CREDENTIALS = (GCS_CREDENTIALS = '...')
FILE_FORMAT = (TYPE = 'CSV');

11.4 COPY INTO with Error Handling¶

-- Continue on errors
COPY INTO my_table
FROM @stage/data/
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'CONTINUE';

-- Skip entire file on error
COPY INTO my_table
FROM @stage/data/
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'SKIP_FILE';

-- Skip file after N errors
COPY INTO my_table
FROM @stage/data/
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'SKIP_FILE_3';  -- Skip after 3 errors

-- Abort on first error (default)
COPY INTO my_table
FROM @stage/data/
FILE_FORMAT = (TYPE = 'CSV')
ON_ERROR = 'ABORT_STATEMENT';

11.5 COPY INTO for Export¶

-- Export to stage
COPY INTO @my_stage/export/
FROM my_table
FILE_FORMAT = (TYPE = 'PARQUET')
HEADER = TRUE
OVERWRITE = TRUE;

-- Export with query
COPY INTO @my_stage/filtered_export/
FROM (
    SELECT id, name, created_at
    FROM my_table
    WHERE created_at >= '2024-01-01'
)
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'GZIP');

-- Partitioned export
COPY INTO @my_stage/partitioned/
FROM my_table
PARTITION BY (date_column)
FILE_FORMAT = (TYPE = 'PARQUET')
MAX_FILE_SIZE = 104857600;  -- 100MB per file

11.6 Named File Formats¶

-- Create file format
CREATE FILE FORMAT my_csv_format
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    SKIP_HEADER = 1
    NULL_IF = ('NULL', 'null');

-- Use named file format
COPY INTO my_table
FROM @stage/data/
FILE_FORMAT = my_csv_format;

-- Alter file format
ALTER FILE FORMAT my_csv_format SET SKIP_HEADER = 2;

-- Drop file format
DROP FILE FORMAT my_csv_format;

12. Stage and Storage Integration¶

12.1 Internal Stage Migration¶

-- Snowflake internal stage
CREATE STAGE my_internal_stage
    FILE_FORMAT = (TYPE = 'JSON');

-- HeliosDB internal stage (same syntax)
CREATE STAGE my_internal_stage
    FILE_FORMAT = (TYPE = 'JSON');

-- Stage with directory table
CREATE STAGE my_stage
    DIRECTORY = (ENABLE = TRUE);

-- List stage contents
LIST @my_internal_stage;
LIST @my_internal_stage/subdir/;
LIST @my_internal_stage PATTERN = '.*\.json';

12.2 External Stage Migration¶

S3 External Stage¶

-- Snowflake S3 stage
CREATE STAGE s3_stage
    URL = 's3://my-bucket/path/'
    CREDENTIALS = (
        AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
        AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
    )
    FILE_FORMAT = (TYPE = 'PARQUET');

-- HeliosDB S3 stage (same syntax)
CREATE STAGE s3_stage
    URL = 's3://my-bucket/path/'
    CREDENTIALS = (
        AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
        AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
    )
    FILE_FORMAT = (TYPE = 'PARQUET');

Azure External Stage¶

-- Azure Blob stage
CREATE STAGE azure_stage
    URL = 'azure://myaccount.blob.core.windows.net/mycontainer/'
    CREDENTIALS = (AZURE_SAS_TOKEN = '?sv=2020-08-04...')
    FILE_FORMAT = (TYPE = 'CSV');

GCS External Stage¶

-- GCS stage
CREATE STAGE gcs_stage
    URL = 'gcs://my-bucket/path/'
    CREDENTIALS = (GCS_CREDENTIALS = '...')
    FILE_FORMAT = (TYPE = 'JSON');

12.3 Storage Integration Migration¶

-- Snowflake storage integration
CREATE STORAGE INTEGRATION my_s3_integration
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = 'S3'
    ENABLED = TRUE
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-role'
    STORAGE_ALLOWED_LOCATIONS = ('s3://bucket1/', 's3://bucket2/');

-- HeliosDB storage integration (same syntax)
CREATE STORAGE INTEGRATION my_s3_integration
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = 'S3'
    ENABLED = TRUE
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-role'
    STORAGE_ALLOWED_LOCATIONS = ('s3://bucket1/', 's3://bucket2/');

-- Use with stage
CREATE STAGE integrated_stage
    URL = 's3://bucket1/data/'
    STORAGE_INTEGRATION = my_s3_integration
    FILE_FORMAT = (TYPE = 'PARQUET');

12.4 PUT and GET Commands¶

-- Upload file to stage
PUT file:///local/path/data.csv @my_stage/upload/;

-- Upload with options
PUT file:///local/path/*.csv @my_stage/
    PARALLEL = 4
    AUTO_COMPRESS = TRUE;

-- Download from stage
GET @my_stage/data.csv file:///local/download/;

-- Download with pattern
GET @my_stage/ file:///local/download/
    PATTERN = '.*\.parquet';

12.5 Table Stage and User Stage¶

-- Table stage (automatic per table)
-- Access with @%table_name
COPY INTO my_table
FROM @%my_table/incoming/
FILE_FORMAT = (TYPE = 'CSV');

-- User stage (automatic per user)
-- Access with @~
PUT file:///local/data.csv @~/uploads/;
LIST @~;

-- Copy from user stage
COPY INTO my_table
FROM @~/uploads/
FILE_FORMAT = (TYPE = 'CSV');

13. Application Connectivity¶

13.1 Python (snowflake-connector-python)¶

Basic Connection and Query¶

import snowflake.connector
from snowflake.connector import DictCursor

# HeliosDB connection
conn = snowflake.connector.connect(
    host="heliosdb.example.com",
    port=443,
    account="heliosdb",
    user="my_user",
    password="my_password",
    warehouse="COMPUTE_WH",
    database="ANALYTICS",
    schema="PUBLIC"
)

# Basic query
cursor = conn.cursor()
cursor.execute("SELECT * FROM users LIMIT 10")
for row in cursor.fetchall():
    print(row)

# Using DictCursor for named columns
cursor = conn.cursor(DictCursor)
cursor.execute("SELECT id, name, email FROM users")
for row in cursor.fetchall():
    print(f"User: {row['name']}, Email: {row['email']}")

cursor.close()
conn.close()

Time Travel Query¶

from datetime import datetime, timedelta

# Connect to HeliosDB
conn = snowflake.connector.connect(
    host="heliosdb.example.com",
    account="heliosdb",
    user="my_user",
    password="my_password"
)

cursor = conn.cursor()

# Time travel to yesterday
yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
cursor.execute(f"""
    SELECT COUNT(*) as order_count
    FROM orders
    AT(TIMESTAMP => '{yesterday}'::TIMESTAMP)
""")
result = cursor.fetchone()
print(f"Orders yesterday: {result[0]}")

# Time travel with offset
cursor.execute("""
    SELECT *
    FROM inventory
    AT(OFFSET => -3600)
    WHERE product_id = 101
""")
for row in cursor.fetchall():
    print(row)

VARIANT and FLATTEN Query¶

# Query VARIANT data
cursor.execute("""
    SELECT
        id,
        data:user_id::INT as user_id,
        data:action::STRING as action,
        data:metadata.browser::STRING as browser
    FROM events
    WHERE data:action::STRING = 'purchase'
""")

for row in cursor.fetchall():
    print(f"Event {row[0]}: User {row[1]} - {row[2]} via {row[3]}")

# FLATTEN query
cursor.execute("""
    SELECT
        o.id as order_id,
        f.value:name::STRING as product,
        f.value:price::FLOAT as price
    FROM orders o,
    LATERAL FLATTEN(input => o.items) f
""")

for row in cursor.fetchall():
    print(f"Order {row[0]}: {row[1]} - ${row[2]:.2f}")

Batch Operations¶

# Batch insert
data = [
    (1, 'Alice', 'alice@example.com'),
    (2, 'Bob', 'bob@example.com'),
    (3, 'Charlie', 'charlie@example.com')
]

cursor.executemany(
    "INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
    data
)
conn.commit()

13.2 JavaScript/Node.js (snowflake-sdk)¶

Basic Connection¶

const snowflake = require('snowflake-sdk');

// Create connection
const connection = snowflake.createConnection({
    host: 'heliosdb.example.com',
    port: 443,
    account: 'heliosdb',
    username: 'my_user',
    password: 'my_password',
    warehouse: 'COMPUTE_WH',
    database: 'ANALYTICS',
    schema: 'PUBLIC'
});

// Connect
connection.connect((err, conn) => {
    if (err) {
        console.error('Connection failed:', err);
        return;
    }
    console.log('Connected to HeliosDB with ID:', conn.getId());
});

Promise-Based Wrapper¶

const snowflake = require('snowflake-sdk');

class HeliosDBClient {
    constructor(config) {
        this.connection = snowflake.createConnection({
            host: config.host || 'localhost',
            port: config.port || 443,
            account: config.account || 'heliosdb',
            username: config.user,
            password: config.password,
            warehouse: config.warehouse,
            database: config.database,
            schema: config.schema
        });
    }

    async connect() {
        return new Promise((resolve, reject) => {
            this.connection.connect((err, conn) => {
                if (err) reject(err);
                else resolve(conn);
            });
        });
    }

    async execute(sql, binds = []) {
        return new Promise((resolve, reject) => {
            this.connection.execute({
                sqlText: sql,
                binds: binds,
                complete: (err, stmt, rows) => {
                    if (err) reject(err);
                    else resolve({ stmt, rows });
                }
            });
        });
    }

    async close() {
        return new Promise((resolve, reject) => {
            this.connection.destroy((err, conn) => {
                if (err) reject(err);
                else resolve(conn);
            });
        });
    }
}

// Usage
async function main() {
    const client = new HeliosDBClient({
        host: 'heliosdb.example.com',
        user: 'my_user',
        password: 'my_password',
        warehouse: 'COMPUTE_WH',
        database: 'ANALYTICS'
    });

    await client.connect();

    // Time travel query
    const { rows } = await client.execute(`
        SELECT * FROM orders
        AT(OFFSET => -3600)
        LIMIT 10
    `);
    console.log('Historical orders:', rows);

    // FLATTEN query
    const { rows: items } = await client.execute(`
        SELECT
            o.id,
            f.value:name::STRING as product
        FROM orders o,
        LATERAL FLATTEN(input => o.items) f
    `);
    console.log('Order items:', items);

    await client.close();
}

main().catch(console.error);

13.3 Java (JDBC)¶

import net.snowflake.client.jdbc.SnowflakeBasicDataSource;
import java.sql.*;

public class HeliosDBExample {
    public static void main(String[] args) throws SQLException {
        // Configure data source
        SnowflakeBasicDataSource ds = new SnowflakeBasicDataSource();
        ds.setServerName("heliosdb.example.com");
        ds.setPortNumber(443);
        ds.setAccount("heliosdb");
        ds.setUser("my_user");
        ds.setPassword("my_password");
        ds.setWarehouse("COMPUTE_WH");
        ds.setDatabaseName("ANALYTICS");
        ds.setSchema("PUBLIC");

        // Connect and query
        try (Connection conn = ds.getConnection()) {
            // Time travel query
            String timeTravelSql = """
                SELECT * FROM orders
                AT(TIMESTAMP => DATEADD(hour, -1, CURRENT_TIMESTAMP()))
                LIMIT 10
            """;

            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(timeTravelSql)) {
                while (rs.next()) {
                    System.out.println("Order ID: " + rs.getInt("id"));
                }
            }

            // VARIANT query
            String variantSql = """
                SELECT
                    id,
                    data:user_id::INT as user_id,
                    data:action::STRING as action
                FROM events
            """;

            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(variantSql)) {
                while (rs.next()) {
                    System.out.println("User: " + rs.getInt("user_id") +
                                       ", Action: " + rs.getString("action"));
                }
            }
        }
    }
}

13.4 Go (gosnowflake)¶

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/snowflakedb/gosnowflake"
)

func main() {
    // Connection string for HeliosDB
    dsn := fmt.Sprintf(
        "%s:%s@%s:%d/%s/%s?warehouse=%s&account=%s",
        "my_user",
        "my_password",
        "heliosdb.example.com",
        443,
        "ANALYTICS",
        "PUBLIC",
        "COMPUTE_WH",
        "heliosdb",
    )

    db, err := sql.Open("snowflake", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Time travel query
    rows, err := db.Query(`
        SELECT id, customer_id, total
        FROM orders
        AT(OFFSET => -3600)
        LIMIT 10
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id, customerID int
        var total float64
        if err := rows.Scan(&id, &customerID, &total); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Order %d: Customer %d, Total $%.2f\n", id, customerID, total)
    }
}

14. SQL Function Mapping¶

14.1 Semi-Structured Functions¶

Snowflake Function	HeliosDB Support	Notes
`PARSE_JSON(expr)`	Supported	Parse JSON string
`TRY_PARSE_JSON(expr)`	Supported	Safe JSON parse
`TO_JSON(variant)`	Supported	Convert to JSON string
`TO_VARIANT(expr)`	Supported	Convert to VARIANT
`OBJECT_CONSTRUCT(k, v, ...)`	Supported	Build object
`OBJECT_CONSTRUCT_KEEP_NULL(...)`	Supported	Build with NULLs
`OBJECT_DELETE(obj, key)`	Supported	Remove key
`OBJECT_INSERT(obj, k, v)`	Supported	Add key
`OBJECT_KEYS(obj)`	Supported	Get keys array
`OBJECT_AGG(key, value)`	Supported	Aggregate to object
`ARRAY_CONSTRUCT(...)`	Supported	Build array
`ARRAY_AGG(expr)`	Supported	Aggregate to array
`ARRAY_SIZE(array)`	Supported	Array length
`ARRAY_CONTAINS(val, array)`	Supported	Check membership
`ARRAY_APPEND(array, val)`	Supported	Append element
`ARRAY_CAT(arr1, arr2)`	Supported	Concatenate
`ARRAY_COMPACT(array)`	Supported	Remove NULLs
`ARRAY_DISTINCT(array)`	Supported	Unique values
`ARRAY_INTERSECTION(arr1, arr2)`	Supported	Common elements
`ARRAY_SLICE(arr, from, to)`	Supported	Get slice
`GET(variant, key)`	Supported	Get by key
`GET_PATH(variant, path)`	Supported	Get by path
`TYPEOF(variant)`	Supported	Get type
`FLATTEN(...)`	Supported	Unnest arrays

14.2 String Functions¶

Snowflake Function	HeliosDB Support	Notes
`CONCAT(s1, s2, ...)`	Supported	Concatenate
`CONCAT_WS(sep, s1, s2, ...)`	Supported	Concat with separator
`CONTAINS(s, sub)`	Supported	Check substring
`STARTSWITH(s, prefix)`	Supported	Check prefix
`ENDSWITH(s, suffix)`	Supported	Check suffix
`SPLIT(s, sep)`	Supported	Split to array
`SPLIT_PART(s, sep, n)`	Supported	Get nth part
`LISTAGG(col, sep)`	Supported	Aggregate strings
`TRIM/LTRIM/RTRIM(s)`	Supported	Remove whitespace
`UPPER/LOWER(s)`	Supported	Case conversion
`LENGTH/LEN(s)`	Supported	String length
`SUBSTR/SUBSTRING(s, start, len)`	Supported	Extract substring
`REPLACE(s, from, to)`	Supported	Replace substring
`REGEXP_REPLACE(s, pat, rep)`	Supported	Regex replace
`REGEXP_SUBSTR(s, pat)`	Supported	Regex extract
`REGEXP_COUNT(s, pat)`	Supported	Regex count
`REGEXP_LIKE(s, pat)`	Supported	Regex match
`RPAD/LPAD(s, len, pad)`	Supported	Pad string
`REVERSE(s)`	Supported	Reverse string
`INITCAP(s)`	Supported	Title case

14.3 Date and Time Functions¶

Snowflake Function	HeliosDB Support	Notes
`CURRENT_DATE()`	Supported	Current date
`CURRENT_TIME()`	Supported	Current time
`CURRENT_TIMESTAMP()`	Supported	Current timestamp
`DATEADD(part, n, date)`	Supported	Add interval
`DATEDIFF(part, d1, d2)`	Supported	Date difference
`DATE_PART(part, date)`	Supported	Extract part
`DATE_TRUNC(part, date)`	Supported	Truncate date
`DAYNAME(date)`	Supported	Day name
`MONTHNAME(date)`	Supported	Month name
`YEAR/MONTH/DAY(date)`	Supported	Extract component
`HOUR/MINUTE/SECOND(ts)`	Supported	Extract component
`WEEK(date)`	Supported	Week number
`QUARTER(date)`	Supported	Quarter number
`TO_DATE(expr)`	Supported	Convert to date
`TO_TIME(expr)`	Supported	Convert to time
`TO_TIMESTAMP(expr)`	Supported	Convert to timestamp
`TO_TIMESTAMP_NTZ(expr)`	Supported	No timezone
`TO_TIMESTAMP_LTZ(expr)`	Supported	Local timezone
`TO_TIMESTAMP_TZ(expr)`	Supported	With timezone
`CONVERT_TIMEZONE(from, to, ts)`	Supported	Timezone conversion
`LAST_DAY(date)`	Supported	Last day of month
`ADD_MONTHS(date, n)`	Supported	Add months
`MONTHS_BETWEEN(d1, d2)`	Supported	Month difference

14.4 Numeric Functions¶

Snowflake Function	HeliosDB Support	Notes
`ABS(n)`	Supported	Absolute value
`CEIL/CEILING(n)`	Supported	Round up
`FLOOR(n)`	Supported	Round down
`ROUND(n, scale)`	Supported	Round
`TRUNC/TRUNCATE(n, scale)`	Supported	Truncate
`MOD(n, m)`	Supported	Modulo
`POWER(base, exp)`	Supported	Exponentiation
`SQRT(n)`	Supported	Square root
`EXP(n)`	Supported	Exponential
`LN/LOG(n)`	Supported	Natural log
`LOG(base, n)`	Supported	Log base
`SIGN(n)`	Supported	Sign (-1, 0, 1)
`RANDOM()`	Supported	Random number
`UNIFORM(min, max, gen)`	Supported	Uniform random
`GREATEST(v1, v2, ...)`	Supported	Maximum
`LEAST(v1, v2, ...)`	Supported	Minimum

14.5 Conditional Functions¶

Snowflake Function	HeliosDB Support	Notes
`CASE WHEN ... END`	Supported	Conditional
`IFF(cond, true, false)`	Supported	Inline IF
`IFNULL(expr, default)`	Supported	NULL handling
`NVL(expr, default)`	Supported	NULL handling
`NVL2(expr, not_null, null)`	Supported	NULL handling
`NULLIF(e1, e2)`	Supported	NULL if equal
`COALESCE(e1, e2, ...)`	Supported	First non-NULL
`ZEROIFNULL(expr)`	Supported	Zero if NULL
`NULLIFZERO(expr)`	Supported	NULL if zero
`DECODE(expr, v1, r1, ...)`	Supported	Value mapping
`TRY_CAST(expr AS type)`	Supported	Safe cast
`TRY_TO_NUMBER(expr)`	Supported	Safe to number
`TRY_TO_DATE(expr)`	Supported	Safe to date

14.6 Aggregate Functions¶

Snowflake Function	HeliosDB Support	Notes
`COUNT(*)`	Supported	Row count
`COUNT(DISTINCT col)`	Supported	Distinct count
`SUM(col)`	Supported	Sum
`AVG(col)`	Supported	Average
`MIN(col)`	Supported	Minimum
`MAX(col)`	Supported	Maximum
`MEDIAN(col)`	Supported	Median
`MODE(col)`	Supported	Mode
`STDDEV(col)`	Supported	Standard deviation
`VARIANCE(col)`	Supported	Variance
`LISTAGG(col, sep)`	Supported	String aggregation
`ARRAY_AGG(col)`	Supported	Array aggregation
`OBJECT_AGG(key, value)`	Supported	Object aggregation
`APPROX_COUNT_DISTINCT(col)`	Supported	Approximate distinct
`APPROX_PERCENTILE(col, p)`	Supported	Approximate percentile
`HLL(col)`	Supported	HyperLogLog

14.7 Window Functions¶

Snowflake Function	HeliosDB Support	Notes
`ROW_NUMBER()`	Supported	Row numbering
`RANK()`	Supported	Ranking with gaps
`DENSE_RANK()`	Supported	Ranking no gaps
`NTILE(n)`	Supported	Bucket distribution
`LAG(col, n, default)`	Supported	Previous row value
`LEAD(col, n, default)`	Supported	Next row value
`FIRST_VALUE(col)`	Supported	First in window
`LAST_VALUE(col)`	Supported	Last in window
`NTH_VALUE(col, n)`	Supported	Nth in window
`PERCENT_RANK()`	Supported	Percent rank
`CUME_DIST()`	Supported	Cumulative distribution
`SUM() OVER (...)`	Supported	Running sum
`AVG() OVER (...)`	Supported	Running average

15. Known Limitations¶

15.1 Unsupported Features¶

Snowflake Feature	Status	Alternative in HeliosDB
Streams	Not supported	Use CDC (Change Data Capture) tables
Tasks	Not supported	Use DBMS_SCHEDULER or external scheduler
Pipes	Not supported	Use COPY INTO with scheduled jobs
Dynamic Tables	Not supported	Use Materialized Views with refresh
Snowpark	Not supported	Use SQL UDFs or external processing
External Functions	Limited	Use HeliosDB REST API
Java/Scala UDFs	Not supported	Use SQL UDFs
Python UDFs	Not supported	Use SQL UDFs
Stored Procedures (JavaScript)	Limited	Use SQL stored procedures
Data Sharing	Not supported	Use cross-database access
Secure Views	Supported	Same syntax
Row Access Policies	Supported	Same syntax
Tags	Partial	Basic tagging supported
Data Classification	Not supported	Manual classification
Search Optimization	Not supported	Use standard indexes

15.2 Stream Migration Alternatives¶

-- Snowflake Stream approach
CREATE STREAM orders_stream ON TABLE orders;

SELECT * FROM orders_stream;  -- Get changes

-- HeliosDB CDC approach
-- Enable CDC on table
ALTER TABLE orders ENABLE CHANGE_TRACKING;

-- Query changes using Time Travel
SELECT *
FROM orders
CHANGES(INFORMATION => DEFAULT)
AT(TIMESTAMP => DATEADD(minute, -5, CURRENT_TIMESTAMP()))
END(TIMESTAMP => CURRENT_TIMESTAMP());

-- Or create a CDC table
CREATE TABLE orders_cdc AS
SELECT *, CURRENT_TIMESTAMP() as captured_at
FROM orders
WHERE 1=0;

-- Scheduled capture using triggers or external job

15.3 Task Migration Alternatives¶

-- Snowflake Task
CREATE TASK hourly_aggregation
    WAREHOUSE = compute_wh
    SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
    INSERT INTO hourly_stats
    SELECT DATE_TRUNC('hour', created_at), COUNT(*)
    FROM orders
    WHERE created_at >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
    GROUP BY 1;

-- HeliosDB alternative using scheduler
-- Option 1: DBMS_SCHEDULER (Oracle-compatible)
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'HOURLY_AGGREGATION',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            BEGIN
                INSERT INTO hourly_stats
                SELECT DATE_TRUNC(''hour'', created_at), COUNT(*)
                FROM orders
                WHERE created_at >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
                GROUP BY 1;
                COMMIT;
            END;
        ',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=HOURLY; BYMINUTE=0',
        enabled         => TRUE
    );
END;

-- Option 2: External cron job calling HeliosDB
-- */60 * * * * /usr/bin/heliosdb-cli -c "INSERT INTO hourly_stats..."

15.4 Pipe Migration Alternatives¶

-- Snowflake Pipe (continuous ingestion)
CREATE PIPE my_pipe
    AUTO_INGEST = TRUE
    AS COPY INTO my_table FROM @my_stage;

-- HeliosDB alternative: Scheduled COPY
-- Create a job that runs periodically
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'INGEST_FROM_STAGE',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            BEGIN
                EXECUTE IMMEDIATE ''
                    COPY INTO my_table
                    FROM @my_stage/incoming/
                    FILE_FORMAT = (TYPE = ''''JSON'''')
                    ON_ERROR = ''''CONTINUE''''
                '';
                COMMIT;
            END;
        ',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled         => TRUE
    );
END;

-- Or use HeliosDB streaming ingestion
-- (Native Kafka integration available)

15.5 Dynamic Table Migration¶

-- Snowflake Dynamic Table
CREATE DYNAMIC TABLE order_summary
    TARGET_LAG = '1 minute'
    WAREHOUSE = compute_wh
AS
    SELECT
        DATE_TRUNC('hour', order_time) as hour,
        COUNT(*) as order_count,
        SUM(total) as total_revenue
    FROM orders
    GROUP BY 1;

-- HeliosDB alternative: Materialized View with scheduled refresh
CREATE MATERIALIZED VIEW order_summary AS
    SELECT
        DATE_TRUNC('hour', order_time) as hour,
        COUNT(*) as order_count,
        SUM(total) as total_revenue
    FROM orders
    GROUP BY 1;

-- Schedule refresh
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'REFRESH_ORDER_SUMMARY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN REFRESH MATERIALIZED VIEW order_summary; END;',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
        enabled         => TRUE
    );
END;

16. Performance Considerations¶

16.1 Query Optimization¶

-- HeliosDB automatic query optimization
-- No changes needed for most queries

-- Enable parallel query execution
ALTER SESSION SET PARALLEL_QUERY = TRUE;

-- Check query plan
EXPLAIN
SELECT
    customer_id,
    SUM(total) as total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

-- Detailed execution analysis
EXPLAIN ANALYZE
SELECT
    customer_id,
    SUM(total) as total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

16.2 Clustering and Indexes¶

-- Snowflake clustering keys
ALTER TABLE orders CLUSTER BY (customer_id, order_date);

-- HeliosDB equivalent (same syntax supported)
ALTER TABLE orders CLUSTER BY (customer_id, order_date);

-- Additionally, HeliosDB supports traditional indexes
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Composite index
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

16.3 Statistics Collection¶

-- HeliosDB statistics update
ANALYZE orders;

-- Analyze specific columns
ANALYZE orders(customer_id, order_date, total);

-- Scheduled statistics update
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'NIGHTLY_ANALYZE',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN ANALYZE; END;',
        repeat_interval => 'FREQ=DAILY; BYHOUR=2',
        enabled         => TRUE
    );
END;

16.4 Result Caching¶

-- HeliosDB query result caching (automatic)
-- Results are cached based on query patterns

-- Check cache status
SHOW PARAMETER RESULT_CACHE;

-- Force cache bypass for testing
SELECT /*+ NO_RESULT_CACHE */ *
FROM orders
WHERE customer_id = 123;

16.5 Warehouse Sizing Guidelines¶

Workload Type	Snowflake Size	HeliosDB Recommendation
Light OLTP	X-Small	xs-pool with 8GB RAM
Standard OLTP	Small	small-pool with 16GB RAM
Mixed workload	Medium	medium-pool with 32GB RAM
Analytics	Large	large-pool with 64GB RAM
Heavy analytics	X-Large	xlarge-pool with 128GB RAM
Data science	2X-Large+	2xlarge-pool+ with 256GB+ RAM

16.6 Performance Monitoring¶

-- Query history analysis
SELECT
    query_text,
    execution_time,
    bytes_scanned,
    rows_returned
FROM INFORMATION_SCHEMA.QUERY_HISTORY
WHERE execution_time > 60000  -- Over 60 seconds
ORDER BY execution_time DESC
LIMIT 20;

-- Resource usage
SELECT
    warehouse_name,
    AVG(execution_time) as avg_time,
    MAX(execution_time) as max_time,
    SUM(bytes_scanned) as total_bytes
FROM INFORMATION_SCHEMA.QUERY_HISTORY
GROUP BY warehouse_name;

17. Post-Migration Validation¶

17.1 Data Integrity Validation¶

Row Count Verification¶

-- Generate row count comparison queries
SELECT
    'SELECT ''' || TABLE_NAME || ''' as table_name, COUNT(*) as row_count FROM ' ||
    TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME || ';' as validation_query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_TYPE = 'BASE TABLE';

-- Run on both Snowflake and HeliosDB, compare results

Checksum Validation¶

-- Column-level checksum
SELECT
    SUM(HASH(id, customer_id, order_date, total)) as table_checksum
FROM orders;

-- Run identical query on both systems and compare

17.2 VARIANT Data Validation¶

-- Verify VARIANT columns migrated correctly
SELECT
    id,
    data:key1::STRING as key1,
    data:key2::NUMBER as key2,
    TYPEOF(data:nested_array) as nested_type
FROM variant_table
LIMIT 100;

-- Compare structure analysis
SELECT
    TYPEOF(data) as data_type,
    COUNT(*) as occurrences
FROM variant_table
GROUP BY 1;

17.3 Time Travel Validation¶

-- Verify Time Travel works
SELECT COUNT(*)
FROM orders
AT(OFFSET => -3600);

-- Verify retention is preserved
SHOW TABLES LIKE 'orders';
-- Check DATA_RETENTION_TIME_IN_DAYS column

17.4 Query Result Validation¶

-- Create validation queries
-- Run on both Snowflake and HeliosDB

-- Simple aggregation
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as total_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

-- FLATTEN validation
SELECT
    COUNT(*) as total_items,
    SUM(f.value:price::FLOAT) as total_value
FROM orders o,
LATERAL FLATTEN(input => o.items) f;

-- Window function validation
SELECT
    customer_id,
    order_date,
    total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_num,
    SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders
WHERE customer_id = 1234;

17.5 Application Validation Checklist¶

Category	Validation Item	Status
Connection	Application connects successfully	[ ]
Connection	Connection pooling works	[ ]
Queries	Basic SELECT queries work	[ ]
Queries	VARIANT queries return correct data	[ ]
Queries	FLATTEN operations work	[ ]
Queries	Time Travel queries work	[ ]
DML	INSERT operations work	[ ]
DML	UPDATE operations work	[ ]
DML	DELETE operations work	[ ]
DDL	CREATE TABLE works	[ ]
DDL	ALTER TABLE works	[ ]
Warehouse	Warehouse suspend/resume works	[ ]
Data Loading	COPY INTO works	[ ]
Performance	Query times are acceptable	[ ]
Performance	Batch operations perform well	[ ]

18. Common Issues and Troubleshooting¶

18.1 Connection Issues¶

Issue	Cause	Solution
Connection refused	Server not running	Check HeliosDB status
Authentication failed	Wrong credentials	Verify username/password
SSL/TLS error	Certificate issue	Verify SSL configuration
Warehouse not found	Warehouse doesn't exist	Create warehouse or check name
Timeout	Long-running query	Increase timeout or optimize query

Debug Connection¶

import snowflake.connector
import logging

# Enable debug logging
logging.basicConfig(level=logging.DEBUG)

try:
    conn = snowflake.connector.connect(
        host="heliosdb.example.com",
        port=443,
        account="heliosdb",
        user="my_user",
        password="my_password"
    )
    print("Connection successful")
except Exception as e:
    print(f"Connection failed: {e}")

18.2 SQL Compatibility Issues¶

QUALIFY Clause Issues¶

-- If QUALIFY causes issues (should work in HeliosDB)
-- Original
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
)
QUALIFY rn = 1;

-- Alternative if needed
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) sub
WHERE rn = 1;

MATCH_RECOGNIZE Issues¶

-- MATCH_RECOGNIZE is not supported
-- Use window functions instead

-- Snowflake MATCH_RECOGNIZE
SELECT *
FROM events
MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES ...
);

-- HeliosDB alternative using window functions
WITH lagged AS (
    SELECT *,
        LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) as prev_event,
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) as prev_time
    FROM events
)
SELECT *
FROM lagged
WHERE event_type = 'purchase' AND prev_event = 'view';

18.3 VARIANT Issues¶

Path Notation Differences¶

-- Verify path notation works correctly
SELECT
    data:simple_key,                    -- Basic key
    data:nested.key,                    -- Nested with dot
    data:nested:key,                    -- Nested with colon (Snowflake style)
    data:"key-with-dashes",             -- Special characters
    data:array[0],                      -- Array access
    data:array[0].nested                -- Combined
FROM variant_table;

Type Casting Issues¶

-- If type casting fails, use TRY_CAST
SELECT
    TRY_CAST(data:id AS INT) as id,
    TRY_CAST(data:amount AS FLOAT) as amount,
    TRY_CAST(data:date AS DATE) as date_value
FROM variant_table;

18.4 Time Travel Issues¶

Issue	Cause	Solution
Data too old	Retention exceeded	Increase retention period
Statement ID not found	ID expired or invalid	Use timestamp instead
UNDROP fails	Object already exists	Rename existing object first

-- Check if data is available for Time Travel
SELECT
    TABLE_NAME,
    RETENTION_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'orders';

-- If UNDROP fails due to existing object
ALTER TABLE orders RENAME TO orders_temp;
UNDROP TABLE orders;
-- Compare and merge if needed

18.5 Performance Issues¶

-- Identify slow queries
SELECT
    query_text,
    execution_time,
    bytes_scanned,
    compilation_time
FROM INFORMATION_SCHEMA.QUERY_HISTORY
WHERE execution_time > 30000
ORDER BY execution_time DESC;

-- Check for missing indexes
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;
-- Look for table scans that should be index scans

-- Create index if needed
CREATE INDEX idx_orders_customer ON orders(customer_id);

19. Rollback Procedures¶

19.1 Pre-Migration Rollback Preparation¶

Before migration, ensure rollback capability:

-- Create backup of critical tables in Snowflake
CREATE TABLE orders_backup CLONE orders;
CREATE TABLE customers_backup CLONE customers;

-- Document warehouse configurations
SHOW WAREHOUSES;

-- Export DDL for recreation
SELECT GET_DDL('TABLE', 'orders');
SELECT GET_DDL('WAREHOUSE', 'COMPUTE_WH');

19.2 Rollback Steps¶

Step 1: Stop Application Traffic to HeliosDB¶

# Update load balancer or DNS to stop traffic to HeliosDB
# Update application connection strings back to Snowflake

Step 2: Export Any New Data from HeliosDB¶

-- Export any data created in HeliosDB during migration period
COPY INTO @export_stage/new_orders/
FROM (
    SELECT * FROM orders
    WHERE created_at > '2024-01-15 00:00:00'  -- Migration start time
)
FILE_FORMAT = (TYPE = 'PARQUET');

Step 3: Restore Snowflake Connection¶

# Revert to Snowflake connection
conn = snowflake.connector.connect(
    account="xy12345.us-east-1",          # Back to Snowflake
    user="my_user",
    password="my_password",
    warehouse="COMPUTE_WH",
    database="MY_DATABASE"
)

Step 4: Import New Data to Snowflake (if needed)¶

-- Import any data created during HeliosDB period
COPY INTO orders
FROM @import_stage/new_orders/
FILE_FORMAT = (TYPE = 'PARQUET');

19.3 Rollback Checklist¶

Step	Action	Status
1	Stop HeliosDB writes	[ ]
2	Verify Snowflake connectivity	[ ]
3	Update connection strings	[ ]
4	Test application connectivity	[ ]
5	Sync any delta data	[ ]
6	Validate data integrity	[ ]
7	Resume Snowflake operations	[ ]
8	Notify stakeholders	[ ]

19.4 Rollback Decision Criteria¶

Consider rollback if: - Data integrity issues that cannot be resolved within SLA - Performance degradation >50% on critical queries - Critical features not working (VARIANT queries, Time Travel) - Application functionality failures affecting users

Do NOT rollback for: - Minor performance differences (optimize instead) - Non-critical feature differences - Issues with clear workarounds - Learning curve adjustments

Appendix A: Quick Reference Card¶

Essential Commands¶

-- Connect using Snowflake connector
-- Only change: host and account parameters

-- Time Travel
SELECT * FROM table AT(TIMESTAMP => '...');
SELECT * FROM table AT(OFFSET => -3600);

-- VARIANT access
SELECT data:field::TYPE FROM table;

-- FLATTEN
SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f;

-- Warehouse management
CREATE WAREHOUSE wh WITH WAREHOUSE_SIZE = 'MEDIUM';
ALTER WAREHOUSE wh SUSPEND;
ALTER WAREHOUSE wh RESUME;

-- COPY INTO
COPY INTO table FROM @stage FILE_FORMAT = (TYPE = '...');

Migration Summary¶

Component	Effort	Notes
Connection strings	Low	Change host/account only
SQL queries	Low	90%+ compatible
VARIANT data	Low	Identical syntax
Time Travel	Low	Identical syntax
Virtual Warehouses	Low	Same operations
Streams	High	Redesign to CDC
Tasks	Medium	Use scheduler
Snowpark	High	Rewrite to SQL

Appendix B: Migration Timeline Template¶

Week	Phase	Activities
1	Assessment	Inventory Snowflake environment, identify complexity
2	Planning	Design migration strategy, identify unsupported features
3	Setup	Provision HeliosDB, configure warehouses
4-5	Schema Migration	Export DDL, create objects in HeliosDB
6-8	Data Migration	COPY INTO data, verify VARIANT migration
9	Testing	Validate SQL compatibility, Time Travel, FLATTEN
10	Application Update	Update connection strings, test applications
11	Cutover	Switch production to HeliosDB
12	Stabilization	Monitor, optimize, resolve issues

Need Help?

Documentation: docs.heliosdb.io
Community: community.heliosdb.io
Support: support@heliosdb.io

Document Version History:

Version	Date	Changes
1.0	January 2026	Initial release