Snowflake performance optimization is often approached by looking at warehouse size first. A slow workload appears, the warehouse is resized, performance improves, and the problem appears solved.

But additional compute can hide an inefficient workload rather than fix it.

In this case study, we used Snowflake Query History, Query Profile, application-level instrumentation, and stored-procedure profiling to identify where time was actually being spent in a data-processing application.

The first optimization reduced a data import from approximately two minutes to eight seconds without increasing warehouse size. A second investigation then isolated a Cortex Search operation responsible for roughly 75% of an account-matching procedure’s execution time.

The important part wasn’t any single optimization technique. It was the process:

Measure → isolate → understand → optimize → verify.

The Test Workload

The application processes customer datasets inside Snowflake and includes ingestion, data cleaning, and account-matching workflows.

For performance testing, we kept the workload consistent:

Input rows:       123,777
Input columns:    5
Warehouse size:   X-Small

Using the same dataset, warehouse, and application settings was important because it gave us a reliable baseline for comparing changes.

Optimization 1: Fixing the Data Import

The original import took approximately:

~120 seconds

Rather than resizing the warehouse, we started with Snowflake Query History.

The baseline revealed:

The dominant pattern was immediately visible:

124 INSERT statements were consuming approximately 94 seconds.

The application was splitting the DataFrame into batches of roughly 1,000 rows and repeatedly issuing SQL INSERT operations.

Conceptually:

123,777 rows
      ↓
~1,000-row batches
      ↓
INSERT
INSERT
INSERT
...
~124 times

This was primarily an ingestion architecture problem, not evidence that the X-Small warehouse lacked sufficient compute.

Replacing Repeated INSERTs with Snowpark Bulk Loading

The row-batch INSERT implementation was replaced with Snowpark’s write_pandas() bulk-loading path.

The revised flow became:

Pandas DataFrame
      ↓
Snowpark write_pandas()
      ↓
App-owned staging table
      ↓
CTAS into destination
      ↓
Drop staging table

A simplified version of the loading operation:

session.write_pandas(
    df,
    table_name=stage_table,
    database=current_database,
    schema="CORE",
    auto_create_table=True,
    overwrite=True,
    quote_identifiers=True
)

The final destination table could then be created server-side:

CREATE OR REPLACE TABLE destination_table AS
SELECT *
FROM staging_table;

Using an application-owned staging area was also important in the Native App architecture because it avoided requiring additional stage-creation privileges in the consumer’s destination schema.

Import Performance After Optimization

Application-level instrumentation showed:

The change was substantial:

Before
~120 seconds
124 INSERT operations

        ↓

After
~8 seconds
Bulk ingestion

That is approximately a 93% reduction in application processing time for the tested import.

More importantly, the improvement was achieved without increasing warehouse size.

Optimization Lesson: Architecture Before Warehouse Size

A warehouse resize might have improved the original workload somewhat, but it would not have removed the fundamental inefficiency of issuing more than 100 separate INSERT operations.

This leads to a useful optimization principle:

Before adding compute, determine whether the application is asking Snowflake to perform unnecessary work.

Warehouse sizing remains an important optimization tool, but it should be evaluated after understanding the execution pattern.

Profiling the Clean-and-Save Workflow

After improving ingestion, we moved to the next workflow rather than assuming it was also slow.

Instrumentation produced:

At approximately 2.7 seconds, this was not a meaningful optimization target compared with other application operations.

This is an important part of performance engineering: knowing what not to optimize.

Optimization 2: Profiling Account Match Back

The next workflow was significantly more expensive.

Account Match Back performs candidate discovery and scoring against an account search index.

For the same workload:

Input rows:       123,777
Candidate rows:   618,797
Final rows:       106,041

Application-level profiling showed:

This immediately eliminated one possible assumption.

With more than 100,000 final rows, moving results into Pandas might appear suspicious. In practice, to_pandas() accounted for only approximately 1.6 seconds.

The real bottleneck was inside the matching procedure.

Profiling Inside the Stored Procedure

We instrumented the matching procedure itself.

The result:

The bottleneck was now isolated:

SEARCH_COMPANY_SECONDS = 42.927

Company search represented approximately 75% of the matching procedure’s execution time.

At this point, optimizing scoring, Python processing, or result serialization would have had relatively little impact.

Inside the Cortex Search Operation

The expensive stage uses Snowflake Cortex Search through a lateral batch-search pattern:

CREATE OR REPLACE TABLE core._ACCT_MB_CAND_C AS
SELECT
    p._MB_ROW_ID AS input_row_id,
    p.ic_q,
    s."RECORD_ID" AS crm_record_id,
    s."COMPANY" AS crm_company,
    s."COMPANY_CLEAN" AS crm_company_clean,
    s."METADATA$RANK"::INTEGER AS search_rank
FROM core._ACCT_MB_PREP p,
LATERAL CORTEX_SEARCH_BATCH(
    service_name => '<db>.CORE.ACCT_SEARCH_SVC',
    query        => p.ic_q,
    limit        => <TOP_K>
) s;

The workload operates at input-row grain. For each prepared row, its normalized company-name value is supplied to CORTEX_SEARCH_BATCH, and the resulting candidates are materialized into an intermediate candidate table.

The test used:

Input rows:            123,777
TOP_K:                 5
Generated candidates:  618,797

That is effectively five candidates per input row.

Query Profile Provided Another Important Signal

The corresponding expensive operation showed a profile dominated by:

Remote disk I/O:   87.3%
Synchronization:   10.7%
Processing:         0.8%

The candidate-generation operation also wrote approximately 618,797 rows.

That profile is materially different from a workload dominated by warehouse CPU processing.

Simply scaling the warehouse therefore wasn’t the first optimization we wanted to test.

Instead, we investigated how the search workload itself was constructed.

Identifying Redundant Cortex Search Work

The investigation revealed an important property of the current implementation.

The prepared input remains at original input-row grain.

If multiple rows normalize to the same search value, for example:

Row 1 → MICROSOFT
Row 2 → APPLE
Row 3 → MICROSOFT
Row 4 → GOOGLE
Row 5 → MICROSOFT

the same normalized query can participate in the Cortex Search operation multiple times.

The implementation currently performs no deduplication of ic_q before candidate search.

That creates a potential optimization opportunity:

Current

123,777 input rows
        ↓
Normalize
        ↓
Cortex Search at input-row grain
        ↓
618,797 candidate rows

versus:

Potential architecture

123,777 input rows
        ↓
Normalize
        ↓
Distinct search keys
        ↓
Cortex Search
        ↓
Candidate set per distinct key
        ↓
Join candidates back to input-row grain
        ↓
Existing scoring/ranking

The objective is not to reduce TOP_K or alter matching thresholds. It is to determine whether identical search requests can be evaluated once and safely reused across matching input rows.

Measure the Opportunity Before Implementing It

We deliberately did not implement deduplication immediately.

First, the potential reduction needs to be quantified.

A representative analysis is:

WITH prepped AS (
    SELECT
        _MB_ROW_ID,
        CLEAN_COMPANY_NAME(company_name) AS ic_q
    FROM account_matchback_input
)

SELECT
    COUNT(*) AS input_rows,
    COUNT(DISTINCT ic_q) AS distinct_search_queries,
    COUNT(*) - COUNT(DISTINCT ic_q) AS potentially_redundant_searches
FROM prepped;

The critical comparison is:

Total input rows
vs.
Distinct normalized search keys

For example:

123,777 input rows
120,000 distinct search keys

would suggest limited benefit.

Whereas:

123,777 input rows
50,000 distinct search keys

would indicate a substantially larger opportunity to reduce Cortex Search probes.

The actual performance improvement should still be benchmarked rather than inferred directly from the percentage reduction because search-service batching and execution behavior may not scale linearly.

Why We Didn’t Reduce TOP_K

Another obvious optimization would be:

TOP_K = 5
      ↓
TOP_K = 3

That would reduce candidate volume.

But it also changes the semantics of the matching process by reducing candidate recall.

Performance tuning shouldn’t silently change business behavior.

The same caution applies to narrowing the search index with additional filtering. The current search service contains both company and DBA representations, and changing search scope could affect which candidates are discoverable.

Performance-only changes should be separated from changes to matching strategy.

The Optimization Methodology

The most valuable result from this work was a repeatable methodology.

Establish baseline
        ↓
Inspect Query History
        ↓
Identify expensive operations
        ↓
Inspect Query Profile
        ↓
Add application-level timing
        ↓
Instrument expensive procedures
        ↓
Isolate the dominant stage
        ↓
Understand its execution architecture
        ↓
Quantify the optimization opportunity
        ↓
Change one thing
        ↓
Re-run the same workload
        ↓
Compare before and after

This approach prevented several premature optimizations.

We didn’t resize the warehouse simply because ingestion was slow.

We didn’t optimize Clean + Save because it was already completing in approximately 2.7 seconds.

We didn’t optimize to_pandas() based on the assumption that transferring 100,000+ rows must be expensive.

And we haven’t yet changed Cortex Search simply because duplicate search keys appear possible.

Each decision follows measurement.

Results So Far

The ingestion optimization already produced a clear result:

The Account Match Back investigation has isolated the next opportunity:

Total workflow
61.46 sec
   ↓
Matching procedure
~54–57 sec
   ↓
Company search
42.93 sec
   ↓
123,777 search probes
   ↓
618,797 candidate rows

The next benchmark will determine how much of that search workload can be eliminated through search-key deduplication without affecting candidate quality.

Conclusion

Snowflake optimization is not synonymous with warehouse resizing.

Warehouse size matters, but so do ingestion architecture, query patterns, intermediate materialization, external/service-bound operations, application behavior, and unnecessary repetition.

In this case, changing the ingestion architecture reduced processing from approximately:

120 seconds → 8 seconds

on the same X-Small warehouse.

For the second workload, progressive instrumentation narrowed a roughly 61-second workflow down to a single Cortex Search stage consuming approximately 43 seconds.

That is the value of measurement-driven optimization.

Don’t start with:

How much more compute does this workload need?

Start with:

What work is Snowflake actually spending time doing, and does all of that work need to happen?

Then optimize the dominant operation, benchmark it under the same conditions, and let the measurements determine the next step.

Waqar Khan Head of Engineering

Waqar leads software engineering at DataTools Pro, overseeing the platform, integrations, and APIs. With over 12 years of consulting experience and 100+ projects completed prior to joining full-time in 2023, he specializes in Salesforce, Snowflake, workflow automation, pipelines, and API integrations, turning complex business and technical requirements into scalable solutions. He currently leads R&D across agentic automation, headless Salesforce, and specialized migration services.

See Full Bio

Salesforce Snowflake OpenAI