Enabling New Record Insert from Streamline to Snowflake

This guide explains how to configure Snowflake tables for INSERT operations in Streamline when your tables use AUTOINCREMENT primary keys.

Key Points

• ⚠️ INSERT operations are disabled by default for tables with AUTOINCREMENT primary keys

• ✅ UPDATE operations work without any special configuration (no Correlation ID needed)

• 🔧 This guide shows how to enable INSERTs by adding a Correlation ID column

• ⏱️ Setup takes 5-10 minutes per table

• 📊 Minimal impact: Only adds one VARCHAR(36) column to your table

💡 Quick Check: If you only need to UPDATE existing records (not create new ones), you can stop reading now—you don't need a Correlation ID!

Understanding the Problem

Snowflake's AUTOINCREMENT columns have a unique limitation that affects data integration platforms like Streamline. When inserting new records into tables with autoincrement primary keys, Snowflake does not provide a reliable way to retrieve the generated ID value.

Why This Matters

When Streamline inserts data into your Snowflake tables, it needs to know the primary key values of inserted records to:

• Build relationships between parent and child tables

• Track changes in the dataset

• Maintain data integrity across related tables

Example Scenario:

-- You have two related tables
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_name VARCHAR(255)
);

CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER,  -- Foreign key to accounts
    contact_name VARCHAR(255),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

-- Streamline needs to:
-- 1. Insert into accounts → Get the generated account_id (e.g., 12345)
-- 2. Insert into contacts using that account_id → Link the contact to account 12345

The Technical Limitation

Unlike other databases, Snowflake's AUTOINCREMENT feature:

• ❌ Does not support RETURNING clauses

• ❌ Does not expose the backing sequence

• ❌ Does not provide LAST_INSERT_ID() function

• ❌ Returns empty results from JDBC's getGeneratedKeys()

• ❌ Time Travel workarounds are unreliable in concurrent environments

Reference: Snowflake Community Discussion on AUTOINCREMENT Limitations

What is a Correlation ID?

A Correlation ID is a special column you add to your Snowflake table that Streamline uses to retrieve the auto-generated primary key after an INSERT operation.

ℹ️ Note: Correlation ID is ONLY needed for INSERT operations. If you only plan to UPDATE existing records, you do not need a Correlation ID column.

How It Works

1. Streamline generates a unique UUID (e.g., a7f3c8e1-4b2d-4c9a-8f1e-9d6b5a2c3f4e)

2. Inserts the record with this UUID in your Correlation ID column

3. Queries the table using the UUID to find the newly inserted record

4. Retrieves the auto-generated primary key from the query result

5. Uses that primary key for relationships and tracking

Analogy

Think of the Correlation ID as a "tracking number" that helps Streamline find the exact record it just inserted, similar to how you track a package with a tracking number.

When Do You Need This?

✅ You NEED a Correlation ID when:

Your table meets BOTH of these conditions:

1. Primary key uses AUTOINCREMENT

2.  

   CREATE TABLE my_table (
       id INTEGER PRIMARY KEY AUTOINCREMENT,  -- Uses AUTOINCREMENT
       name VARCHAR(255)
   );

3. You want to perform INSERT operations (create new records) from Streamline

🔍 How to Know: In Streamline's Deliver data interface, if INSERT operations are disabled for your table, it means the table has an AUTOINCREMENT primary key without a Correlation ID column. Follow the setup steps in this guide to enable INSERT operations.

Common Use Cases:

• Creating new records in tables with autoincrement primary keys

• Parent-child table relationships (e.g., Accounts → Contacts)

• Multi-table datasets requiring inserts

• Tables where you don't control the primary key generation

Step-by-Step Setup Guide

Prerequisites

• Access to your Snowflake account with ALTER TABLE privileges

• Knowledge of which tables will receive data from Streamline

• Basic understanding of SQL

Step 1: Add Correlation ID Column to Your Snowflake Table

For each table with AUTOINCREMENT primary keys that will receive data from Streamline, add a Correlation ID column.

1.1 Choose a Column Name

Pick a descriptive name for your Correlation ID column. We recommend:

• STREAMLINE_CORRELATION_ID

• INTEGRATION_CORRELATION_ID

• SYNC_CORRELATION_ID

1.2 Add the Column

-- Connect to your Snowflake account
USE DATABASE your_database_name;
USE SCHEMA your_schema_name;

-- Add the Correlation ID column
-- Note: VARCHAR(36) is used because Snowflake does not have a native UUID data type
-- UUIDs are stored as 36-character strings (e.g., 'a7f3c8e1-4b2d-4c9a-8f1e-9d6b5a2c3f4e')
ALTER TABLE your_table_name
ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);

Example:

-- For an accounts table
USE DATABASE SALES_DB;
USE SCHEMA PUBLIC;
ALTER TABLE accounts
ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);
-- For a contacts table (if it also has AUTOINCREMENT)
ALTER TABLE contacts
ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);

1.3 Create an Index (Recommended for Performance)

Since Streamline will query this column after every insert, adding an index significantly improves performance:

-- Create an index on the Correlation ID column
CREATE INDEX idx_correlation_id
ON your_table_name(STREAMLINE_CORRELATION_ID);

Example:

CREATE INDEX idx_accounts_correlation_id
ON accounts(STREAMLINE_CORRELATION_ID);

CREATE INDEX idx_contacts_correlation_id
ON contacts(STREAMLINE_CORRELATION_ID);

1.4 Verify the Column Was Added

-- Check the table structure
DESCRIBE TABLE your_table_name;

-- You should see your new column listed

Step 2: Configure Snowflake Integration in Streamline

Now that your Snowflake tables are prepared, configure the integration in Streamline.

2.1 Navigate to Integrations

1. Log in to Streamline

2. Go to Integrations > Data Sources

3. Click Add New Integration

4. Select Snowflake

2.2 Enter Connection Details

Provide your Snowflake connection information:

⚠️ Important: The Correlation ID Field Name must match exactly the column name you created in Step 1 (case-sensitive in Snowflake).

2.3 Save and Test Connection

1. Click Test Connection to verify connectivity

2. If successful, click Save Integration

Step 3: Update Existing Integration (If Applicable)

⚠️ For Customers with Existing Snowflake Integrations

If you already have a Snowflake data source/integration configured in Streamline and want to add Correlation ID support:

3.1 Add the Correlation ID Column

First, complete Step 1 to add the Correlation ID column to your Snowflake table(s).

3.2 Update Your Integration in Streamline

You need to update your existing Snowflake integration to include the Correlation ID field name.

🚧 IMPORTANT WARNING: Editing existing data sources is not currently available in Streamline. Until this feature is implemented, you will need to recreate your data source with the Correlation ID configuration.

To recreate your data source:

1. Document your current settings:

   • Note all connection details (Host, Username, Warehouse, etc.)

   • Note which tables/entities are being used

   • Note any workflows or delivery steps using this integration

2. Remove the old integration:

   • Go to Integrations > Data Sources

   • Find your Snowflake integration

   • Delete/remove it

3. Create a new integration:

   • Follow Step 2 above

   • Use the same connection details as before

   • Add the Correlation ID Field Name this time

4. Reconfigure workflows:

   • Update any workflows that were using the old integration

   • Point them to the new integration

5. Test thoroughly:

   • Test that data is flowing correctly

   • Verify INSERT operations are now enabled

💡 Future Update: The ability to edit existing data sources will be added in a future Streamline release, eliminating the need to recreate integrations.

Step 4: Use Deliver Data in Streamline

Once configured, deliver data works automatically!

4.1 Select Your Dataset

1. Navigate to your Workflow

2. Select the Entity (Dataset) that corresponds to your Snowflake table

3. Add a Deliver data step

4.2 Automatic Handling

When Streamline performs an insert operation:

1. ✅ Automatically generates a unique UUID for the Correlation ID

2. ✅ Inserts the record with the UUID in your Correlation ID column

3. ✅ Retrieves the auto-generated primary key using the UUID

4. ✅ Handles relationships between parent and child tables seamlessly

You don't need to do anything else! The system handles everything behind the scenes.

Scenarios That Don't Require Correlation ID

✅ You DON'T need a Correlation ID when:

Your table uses one of these alternative primary key strategies:

Scenario 1: Explicit Sequence with Default Expression

-- Create a sequence
CREATE SEQUENCE account_id_seq START 1 INCREMENT 1 ORDER;

-- Use it as default value
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY DEFAULT account_id_seq.NEXTVAL,
    account_name VARCHAR(255)
);

Why it works: Streamline can directly access and use the sequence to generate IDs before inserting.

Scenario 2: UUID-Based Primary Keys

CREATE TABLE accounts (
    account_id VARCHAR(36) PRIMARY KEY DEFAULT UUID_STRING(),
    account_name VARCHAR(255)
);

Why it works: Streamline can generate UUIDs on the client side before inserting.

Scenario 3: Update-Only Operations

If you're only updating existing records (not inserting new ones), you don't need a Correlation ID regardless of your primary key type.

Example:

-- Update operations work fine without Correlation ID
UPDATE accounts
SET account_name = 'New Name'
WHERE account_id = 12345;

In Streamline: If your workflow only updates existing records and never creates new ones, you can skip the Correlation ID setup entirely.

Scenario 4: Read-Only Operations

If you're only reading data from Snowflake (no inserts/updates), you don't need a Correlation ID regardless of your primary key type.

Best Practices

1. Consistent Naming

Use the same Correlation ID column name across all your Snowflake tables for consistency:

-- Good: Same name everywhere
ALTER TABLE accounts ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);
ALTER TABLE contacts ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);
ALTER TABLE orders ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);

2. Always Add an Index

Performance degrades without an index, especially for large tables:

CREATE INDEX idx_accounts_correlation_id ON accounts(STREAMLINE_CORRELATION_ID);

3. Don't Populate Existing Records

The Correlation ID should remain NULL for existing records. Only Streamline populates this field during insert operations.

4. Document Your Tables

Add a comment to document the purpose of the Correlation ID column:

COMMENT ON COLUMN accounts.STREAMLINE_CORRELATION_ID IS
'Used by Streamline for data integration. Managed automatically. Do not modify.';

5. Consider Future Tables

When creating new tables that will integrate with Streamline, consider using explicit sequences or UUID primary keys instead of AUTOINCREMENT to avoid needing a Correlation ID:

-- Option A: Explicit Sequence
CREATE SEQUENCE account_id_seq START 1 INCREMENT 1;
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY DEFAULT account_id_seq.NEXTVAL,
    account_name VARCHAR(255)
);

-- Option B: UUID Primary Key
CREATE TABLE accounts (
    account_id VARCHAR(36) PRIMARY KEY DEFAULT UUID_STRING(),
    account_name VARCHAR(255)
);

Frequently Asked Questions

Q: I already have a Snowflake integration in Streamline. What do I do?

A: You'll need to add the Correlation ID column to your Snowflake table, then recreate your integration in Streamline with the Correlation ID field configured. See Step 3: Update Existing Integration for detailed instructions.

⚠️ Note: Editing existing integrations is not currently available. You must recreate the integration until this feature is implemented.

Q: Does the Correlation ID affect my existing data?

A: No. Existing records will have NULL in the Correlation ID column. Only new records inserted by Streamline will have values populated.

Q: Can I use the Correlation ID for my own purposes?

A: No. The Correlation ID column is exclusively managed by Streamline. Do not read from, write to, or depend on this column in your own applications.

Q: What if I have multiple Streamline integrations to the same table?

A: Use the same Correlation ID column for all integrations. Each insert operation generates a unique UUID, so there's no conflict.

Q: Can I delete or modify Correlation ID values?

A: Once inserted, you should not modify Correlation ID values. However, you can safely delete old Correlation ID values if needed (set to NULL) for records that were successfully inserted and no longer need tracking.

Q: Why can't I insert records into my Snowflake table in Streamline?

A: If INSERT operations are disabled in Streamline's Deliver data interface, your table has an AUTOINCREMENT primary key without a Correlation ID column configured. By default, tables with autoincrement primary keys are not "insertable" in Streamline until you complete the Correlation ID setup. Follow the steps in this guide to enable INSERT operations.

Q: I only need to UPDATE records, do I still need a Correlation ID?

A: No! Correlation ID is only required for INSERT operations. UPDATE operations work perfectly fine without a Correlation ID, regardless of whether your table has an AUTOINCREMENT primary key.

Q: Why use VARCHAR(36) instead of a UUID data type?

A: Snowflake does not have a native UUID data type. UUIDs in Snowflake are stored as VARCHAR strings. The standard UUID format is 36 characters long (e.g., a7f3c8e1-4b2d-4c9a-8f1e-9d6b5a2c3f4e), which is why we use VARCHAR(36). This is consistent with how Snowflake's built-in UUID_STRING() function works.

Reference: Snowflake UUID_STRING() Documentation

Q: What's the storage overhead?

A: Minimal. A VARCHAR(36) column stores a UUID string (36 characters). For example:

• 1 million rows = ~36 MB of storage (uncompressed)

• Snowflake's compression typically reduces this significantly

References

Snowflake Documentation

• AUTOINCREMENT Clause

• CREATE SEQUENCE

• UUID_STRING() Function

• ALTER TABLE - Add Column

• CREATE INDEX

Community Discussions

• Get Identity of Row Inserted in Snowflake

• Snowflake AUTOINCREMENT Limitations

Understanding Insert Availability in Streamline

Default Behavior for Autoincrement Tables

Important: By default, tables with AUTOINCREMENT primary keys are not insertable in Streamline. When you view such tables in the Deliver data interface:

• ✅ UPDATE operations are available

• ❌ INSERT operations are disabled

• ℹ️ A message may indicate that inserts are not supported

Enabling Insert Operations

To enable INSERT operations for autoincrement tables:

1. ✅ Add a Correlation ID column to your Snowflake table

2. ✅ Configure the Correlation ID field name in your Streamline integration

3. ✅ Refresh your data catalog in Streamline

4. ✅ INSERT operations will become available

Once configured, inserts work automatically without any additional steps!

Support

If you encounter issues not covered in this guide:

1. Check Insert Availability: Verify if INSERT operations are enabled for your table in Streamline's Deliver data interface

2. Check Streamline Logs: Look for error messages related to your Snowflake integration

3. Verify Configuration: Ensure the Correlation ID column name matches exactly in both Snowflake and Streamline

4. Refresh Data Catalog: After adding the Correlation ID column, refresh your data catalog in Streamline

5. Test Simple Insert: Try inserting a single record to isolate the problem

6. Contact Support: Reach out to Streamline support with:

   • Your Snowflake table structure (DESCRIBE TABLE)

   • Streamline integration configuration

   • Error messages or logs

Quick Reference Card

✅ Checklist for Setting Up Correlation ID

For New Integrations:

For Existing Integrations:

📋 Quick SQL Script Template

-- Replace placeholders with your actual values
USE DATABASE <your_database>;
USE SCHEMA <your_schema>;

-- Add Correlation ID column
ALTER TABLE <your_table>
ADD COLUMN STREAMLINE_CORRELATION_ID VARCHAR(36);

-- Add index for performance
CREATE INDEX idx_<table>_correlation_id
ON <your_table>(STREAMLINE_CORRELATION_ID);

-- Add documentation comment
COMMENT ON COLUMN <your_table>.STREAMLINE_CORRELATION_ID IS
'Used by Streamline for data integration. Managed automatically. Do not modify.';

-- Verify
DESCRIBE TABLE <your_table>;

Known Limitations

Editing Existing Data Sources

Current Status: Editing existing Snowflake data sources/integrations is not currently available in Streamline.

Impact: If you have an existing Snowflake integration and want to add Correlation ID support, you must recreate the integration.

Workaround: See Step 3: Update Existing Integration for detailed instructions on how to safely recreate your integration.

Planned Enhancement: The ability to edit existing data sources will be added in a future release. This will allow you to:

• Add or update the Correlation ID field name

• Modify other connection settings

• Avoid recreating integrations

Timeline: To be determined. Check Streamline release notes for updates.