[BUG]: Snowflake profiler exhausts client memory on large accounts — extract steps buffer the full result set and materialize it three times

[vladsagot]

Is there an existing issue for this?

• I have searched the existing issues

Category of Bug / Issue

Profiler bug

Current Behavior

On a high-volume Snowflake account, execute-database-profiler exhausts client memory and the host process dies. On a VDI this took the whole session host down rather than producing a Python traceback.

The Snowflake extract has no streaming anywhere in its path. A single step's result set is fully buffered and then materialized two more times before it reaches disk:

1. Unbounded query. query_history.sql selects every query in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY over a 90-day window — ~30 columns, no LIMIT, plus an ORDER BY START_TIME DESC.

2. Full buffer into Python objects. _BaseConnector.fetch calls result.fetchall() — no fetchmany, no cursor iteration, no server-side paging:

   # src/databricks/labs/lakebridge/connections/database_manager.py
   with Session(self.engine) as session, session.begin():
       result = session.execute(text(query))
       return FetchResult(list(result.keys()), result.fetchall())

3. Second copy into pandas. FetchResult.to_df() copies the whole thing again:

   def to_df(self) -> pd.DataFrame:
       return pd.DataFrame.from_records(self.rows, columns=list(self.columns))

4. Third materialization into DuckDB. PipelineClass._save_to_db then runs CREATE TABLE {step} AS SELECT * FROM _result_frame (or INSERT INTO ... SELECT *) against the frame.

Steps 2–4 are all live simultaneously, so peak RSS is roughly 2–3x the decoded result set. ACCOUNT_USAGE.QUERY_HISTORY on a busy account is easily tens of millions of rows over 90 days, and as Python row objects that is many GB before pandas takes its copy.

This is not specific to Snowflake — _BaseConnector.fetch is shared by the Oracle and Teradata connectors, and the MSSQL and Redshift connectors have their own fetchall() implementations with the same shape. Snowflake just hits it first because ACCOUNT_USAGE is the largest default extract.

Expected Behavior

A profiler extract should complete in bounded memory regardless of source-account size. Memory use should be a function of batch size, not result-set size.

Steps To Reproduce

1. Configure the Snowflake profiler against an account with a high query volume (enough that 90 days of ACCOUNT_USAGE.QUERY_HISTORY is in the tens of millions of rows).
2. Run databricks labs lakebridge execute-database-profiler.
3. The query_history step grows unboundedly in RSS and the process is killed by the OS / the host runs out of memory.

Relevant log output or Exception details

No Python traceback was captured — the host ran out of memory and the session
terminated before the interpreter could unwind.

Proposed approach

Sketching this rather than prescribing it, since it touches the shared pipeline framework:

• Add a batched fetch path to DatabaseConnector (e.g. fetch_batches(query, size) -> Iterator[FetchResult]) backed by cursor.fetchmany() / SQLAlchemy's yield_per, keeping the existing fetch() for small steps.
• Have _execute_sql_step / _save_to_db consume batches and append to DuckDB per batch inside a single transaction, so nothing larger than one batch is resident.
• Prefer CREATE TABLE ... AS SELECT from the first batch, then INSERT subsequent batches, dropping the intermediate pandas copy where possible — DuckDB can ingest Arrow or record batches directly.
• Consider a configurable batch size with a conservative default.

Related: #2486 (configurable lookback window). That issue would reduce how often this is hit, but not fix it — the failure is a function of rows returned, not of the window, so a large enough account fails at 30 days too. The two are complementary.

Logs Confirmation

• I ran the command line with --debug
• I have attached the lsp-server.log under USER_HOME/.databricks/labs/remorph-transpilers/<converter_name>/lib/lsp-server.log

Sample Query

-- src/databricks/labs/lakebridge/resources/assessments/snowflake/query_history.sql
SELECT QUERY_ID, QUERY_TYPE, DATABASE_NAME, ... -- ~30 columns
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -90, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

Operating System

Windows

Version

latest via Databricks CLI

[asnare]

Memory handling for Snowflake is dramatically improved in #2610, narrowly targeted pending testing against other source systems, with results now handled as batches. The query-history is also no longer sorted; that was convenient for eye-balling but that's insufficient to justify the overhead.