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-SmallUsing 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 secondsRather than resizing the warehouse, we started with Snowflake Query History.
The baseline revealed:
| Operation | Queries | Total Time |
|---|---|---|
| INSERT | 124 | 93.56 sec |
| SELECT | 39 | 9.88 sec |
| SHOW | 5 | 2.45 sec |
| CREATE TABLE | 1 | 1.03 sec |
| DESCRIBE | 1 | 0.41 sec |
| USE | 2 | 0.31 sec |
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 timesThis 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 tableA 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:
| Stage | Execution Time |
|---|---|
| CSV read | 0.175 sec |
| DataFrame preparation | 0.069 sec |
| Bulk load | 5.827 sec |
| Stage check | 0.181 sec |
| Table creation | 1.308 sec |
| Cleanup | 0.282 sec |
| Row verification | 0.194 sec |
| Total | 8.038 sec |
The change was substantial:
Before
~120 seconds
124 INSERT operations
↓
After
~8 seconds
Bulk ingestionThat 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:
| Stage | Execution Time |
|---|---|
| Original count | 0.174 sec |
| Raw column processing | 0.714 sec |
| Final cleaning | 1.405 sec |
| Preview fetch | 0.257 sec |
| Final row count | 0.101 sec |
| Total | 2.682 sec |
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,041Application-level profiling showed:
| Stage | Execution Time |
|---|---|
| Matching procedure | 54.207 sec |
| Final output to Pandas | 1.575 sec |
| Relationship fetch | 0.166 sec |
| Snapshot | 1.881 sec |
| Run-log insert | 0.995 sec |
| Python processing | 2.075 sec |
| Results page | 7.219 sec |
| Total | 61.463 sec |
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:
| Procedure Stage | Execution Time |
|---|---|
| Input setup | 1.633 sec |
| Column mapping | 6.645 sec |
| Normalization | 2.068 sec |
| Company search | 42.927 sec |
| DBA search | 0 sec |
| Scoring | 2.369 sec |
| Cleanup | 1.210 sec |
| Count | 0.572 sec |
| Total | 57.424 sec |
The bottleneck was now isolated:
SEARCH_COMPANY_SECONDS = 42.927Company 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,797That 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 → MICROSOFTthe 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 rowsversus:
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/rankingThe 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 keysFor example:
123,777 input rows
120,000 distinct search keyswould suggest limited benefit.
Whereas:
123,777 input rows
50,000 distinct search keyswould 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 = 3That 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 afterThis 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:
| Metric | Before | After |
|---|---|---|
| Input rows | 123,777 | 123,777 |
| Warehouse | X-Small | X-Small |
| Import processing | ~120 sec | ~8 sec |
| INSERT pattern | ~124 INSERTs | Bulk load |
| Approx. improvement | — | ~93% |
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 rowsThe 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.
