ENHANCEMENT: Add CreateIfMissing() to Cases().WriteTables()

[improveTheWorld]

ENHANCEMENT: CreateIfMissing for Cases().WriteTables()

Summary

The Cases().WriteTables() API in Snowflake does not support CreateIfMissing(), unlike the standard WriteTable() API. This forces users to manually create destination tables before using the Cases pattern.

Current Behavior

WriteTable (Works ✅)

await orders
    .WriteTable("NEW_TABLE")
    .CreateIfMissing()  // ✅ Supported - table auto-created
    .ExecuteAsync();

WriteTables with Cases (Does NOT work ❌)

await orders
    .Cases(o => o.Amount > 10000)
    .WriteTables("HIGH_VALUE", "NORMAL")
    // .CreateIfMissing()  ❌ NOT AVAILABLE
    .ExecuteAsync();  // Fails if tables don't exist

Error Message

Snowflake.Data.Client.SnowflakeDbException: 
SQL compilation error: Object 'HIGH_VALUE' does not exist or not authorized.

Affected Components

• DataFlow.Snowflake v1.2.0 (SnowflakeCategorizedWriteOperation)
• Not affected: Spark (doesn't have equivalent API)

Root Cause

Location: Write.Snowflake.cs - SnowflakeCategorizedWriteOperation class

The WriteTable() API returns a SnowflakeWriteOperation that has a CreateIfMissing() method. This method generates CREATE TABLE IF NOT EXISTS SQL before writing.

The WriteTables() API returns a SnowflakeCategorizedWriteOperation which lacks this method. The class doesn't have the logic to create multiple tables based on the schema.

API Comparison

Feature	WriteTable	WriteTables (Cases)
CreateIfMissing()	✅ Supported	❌ NOT Supported
Auto-create table	Yes	No
Multiple targets	No	Yes

Reproduction Steps

using DataFlow.Snowflake;

var context = Snowflake.Connect("connection-string");
var orders = context.Read.Table<Order>("ORDERS");

// DROP tables to simulate fresh state
await context.ExecuteAsync("DROP TABLE IF EXISTS HIGH_VALUE_ORDERS");
await context.ExecuteAsync("DROP TABLE IF EXISTS NORMAL_ORDERS");

// This FAILS because tables don't exist:
await orders
    .Cases(o => o.Amount > 10000)
    .WriteTables("HIGH_VALUE_ORDERS", "NORMAL_ORDERS")
    .ExecuteAsync();  // Throws: Object does not exist

Current Workarounds

Workaround 1: Pre-create tables manually

await context.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS HIGH_VALUE_ORDERS (
        id INT, amount DOUBLE, customer_name STRING
    )");
await context.ExecuteAsync(@"
    CREATE TABLE IF NOT EXISTS NORMAL_ORDERS (
        id INT, amount DOUBLE, customer_name STRING
    )");

// Now WriteTables works
await orders
    .Cases(o => o.Amount > 10000)
    .WriteTables("HIGH_VALUE_ORDERS", "NORMAL_ORDERS")
    .ExecuteAsync();

Workaround 2: Use separate WriteTable calls

var highValue = orders.Where(o => o.Amount > 10000);
var normal = orders.Where(o => o.Amount <= 10000);

await highValue.WriteTable("HIGH_VALUE_ORDERS").CreateIfMissing().ExecuteAsync();
await normal.WriteTable("NORMAL_ORDERS").CreateIfMissing().ExecuteAsync();

Proposed Fix

Add CreateIfMissing() to SnowflakeCategorizedWriteOperation:

public class SnowflakeCategorizedWriteOperation<T>
{
    private bool _createIfMissing = false;
    
    public SnowflakeCategorizedWriteOperation<T> CreateIfMissing()
    {
        _createIfMissing = true;
        return this;
    }
    
    public async Task ExecuteAsync()
    {
        if (_createIfMissing)
        {
            foreach (var tableName in _tableNames)
            {
                var createSql = GenerateCreateTableSql<T>(tableName);
                await _context.ExecuteAsync(createSql);
            }
        }
        
        // Existing write logic...
    }
}

Impact

• Severity: LOW
• Frequency: Low (Cases pattern is advanced feature)
• User Impact: Requires manual table creation for Cases pattern

Labels

enhancement, snowflake, cases-pattern, write-api