+1 (726) 227-3027
ETL Advisors

Loading Snowflake with Talend: Bulk, Stage, and ELT Pushdown

Snowflake is the most common target we see in Talend estates built or refreshed since 2020, and the most common performance complaint is "the load takes hours." It nearly always comes from treating Snowflake like an OLTP database. This tutorial shows the patterns that work.

Why row-by-row inserts are the wrong tool

Snowflake is a columnar, micro-partitioned warehouse. A tDBOutput configured for Snowflake with Insert action sends batches of INSERT statements. Each one:

  • Acquires a warehouse compute slot
  • Writes a new micro-partition (Snowflake does not update files in place)
  • Invalidates result caches on the table

Ten million rows inserted in batches of 10,000 is a thousand small DML operations, each creating tiny partitions that Snowflake later has to re-cluster. You pay for the warehouse the whole time, and the table ends up fragmented. The right pattern is: stage files, then COPY INTO once.

Pattern 1: Talend's Snowflake bulk components

Talend Studio ships Snowflake-specific components (see the Snowflake component reference). The ones that matter for loading:

  • tSnowflakeConnection – account, warehouse, database, schema, role, and authentication. Use key-pair authentication for service accounts; password auth is being phased out for programmatic access.
  • tSnowflakeOutputBulk – writes the incoming flow to files (CSV, gzip by default) and uploads them to a stage. Works with internal stages (@~, @%table, or a named stage) and external S3/Azure/GCS stages.
  • tSnowflakeBulkExec – runs COPY INTO from the stage into the target table, with the file format, ON_ERROR behavior, and purge options.
  • tSnowflakeOutputBulkExec – the two above combined in one component; fine for simple loads.
  • tSnowflakeRow – executes arbitrary SQL. This is what you use for pushdown (Pattern 2).

A minimal bulk load job

tDBInput (source) --Main--> tMap --Main--> tSnowflakeOutputBulk (stage: @%CUSTOMER_STG, file prefix: customers_)
   --OnSubjobOk--> tSnowflakeBulkExec (table: CUSTOMER_STG, stage: @%CUSTOMER_STG, purge: true)
   --OnSubjobOk--> tSnowflakeRow (MERGE into CUSTOMER)

Key settings on tSnowflakeOutputBulk:

  • File format: CSV with FIELD_OPTIONALLY_ENCLOSED_BY = '"' and gzip compression. Parquet is supported and is better for wide tables with typed columns.
  • Max file size: keep files in the 100 to 250 MB compressed range. Snowflake loads files in parallel; one giant file uses one thread.
  • Number of files: let the component split; a load of dozens of mid-size files is much faster than one file.

Key settings on tSnowflakeBulkExec:

  • ON_ERROR: ABORT_STATEMENT for strict loads, CONTINUE with a reject review when the source is dirty. Check COPY_HISTORY afterward.
  • PURGE: TRUE unless you need the staged files for forensics.
  • Load to a staging table, never directly into the reporting table; merge in Pattern 2.

This single change (batch inserts to stage + copy) has taken loads from hours to minutes on every engagement where we applied it.

Pattern 2: ELT pushdown with MERGE

Once the data is in CUSTOMER_STG, let Snowflake do the set-based work. A tSnowflakeRow after the bulk load runs:

MERGE INTO analytics.customer AS t
USING analytics.customer_stg AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND (
       t.name        IS DISTINCT FROM s.name
    OR t.email       IS DISTINCT FROM s.email
    OR t.updated_at  <  s.updated_at
) THEN UPDATE SET
    name = s.name,
    email = s.email,
    updated_at = s.updated_at,
    load_batch_id = s.load_batch_id
WHEN NOT MATCHED THEN INSERT (customer_id, name, email, updated_at, load_batch_id)
  VALUES (s.customer_id, s.name, s.email, s.updated_at, s.load_batch_id);

The IS DISTINCT FROM guards avoid rewriting rows that did not change, which keeps micro-partitions stable and Time Travel storage small.

Other pushdown patterns that belong in tSnowflakeRow rather than tMap:

  • Deduplication: QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1
  • Type 2 SCD: a MERGE that closes the current row and inserts the new version, or a dbt snapshot if dbt is in the stack
  • Aggregations and joins across large tables
  • Semi-structured flattening: load JSON into a VARIANT column with COPY INTO, then LATERAL FLATTEN in SQL instead of tExtractJSONFields over millions of records

Talend remains the orchestrator and the extractor; Snowflake does the transformation. That division is the subject of our ETL vs ELT in 2026 post.

Cost pitfalls

  • Per-row inserts (above). Also applies to tDBOutput Update and Insert or update actions; replace them with stage + MERGE.
  • Warehouse left running. Set AUTO_SUSPEND to 60 seconds on load warehouses; a Talend job that opens a connection and idles in a tSleep keeps the meter running.
  • Oversized warehouse for the copy. COPY INTO parallelizes by file count. An X-Small warehouse with 40 files usually beats a Large with one file.
  • Lookups in tMap against Snowflake. A tMap lookup that fires a query per row is the insert problem in reverse. Load the lookup into memory once, or push the join down.
  • No clustering strategy on very large tables. Not a Talend issue, but loads that interleave old and new dates defeat natural clustering; load in date order when you can.

Validation queries we run after every load

Put these in a tSnowflakeRow (or tSnowflakeInput feeding a tAssert) at the end of the job; fail the job if they do not pass.

-- 1. Did COPY load what we staged?
SELECT file_name, status, rows_parsed, rows_loaded, error_count
FROM TABLE(information_schema.copy_history(
  table_name => 'ANALYTICS.CUSTOMER_STG',
  start_time => DATEADD(hour, -1, CURRENT_TIMESTAMP())));

-- 2. Row counts: staging vs. source count passed in from Talend
SELECT COUNT(*) AS stg_rows FROM analytics.customer_stg
WHERE load_batch_id = '${batch_id}';

-- 3. Key integrity
SELECT customer_id, COUNT(*) FROM analytics.customer
GROUP BY 1 HAVING COUNT(*) > 1;

-- 4. Freshness
SELECT MAX(updated_at) FROM analytics.customer;

Compare stg_rows to the source row count captured in globalMap from the extract (((Integer) globalMap.get("tDBInput_1_NB_LINE"))) and fail if they differ. A load that "succeeds" with fewer rows than it read is the most expensive kind of silent failure.

Summary

Stage files and COPY INTO; never insert row by row. Land in a staging table, then MERGE and transform in Snowflake SQL. Keep the warehouse small and auto-suspended, and verify every load with COPY_HISTORY, row counts, and key checks. Snowflake's own bulk-loading guide and the COPY INTO reference cover the options in depth.

Slow Snowflake loads from Talend? Contact us; this is usually a one-week fix.