Server mode cannot run a stage/PUT/COPY/MERGE bulk-load pipeline

[arnaldojvg]

Context

We run fakesnow in server mode (docker) as a local Snowflake emulator for the acceptance/integration tests of a Postgres → Snowflake data-sync service. The service uses the classic bulk-load pattern: create a stage and a named file format, PUT a gzipped CSV, COPY INTO a staging table, then MERGE into the target. Table snapshots additionally use table stages (@db.schema.%table).

Verified against fakesnow 0.11.15, every step of that pipeline fails:

Problems

1. CREATE OR REPLACE STAGE / CREATE STAGE IF NOT EXISTS are not honored — re-creating an existing stage always fails with Object '<name>' already exists (errno 2002), so any job that recreates its stage per run cannot execute twice.

2. CREATE FILE FORMAT crashes the server — fakesnow/checks.py _missing_qualifiers raises AssertionError: Unexpected parent kind: FILE FORMAT, returning HTTP 500. The snowflake connector then retries the 500 in a loop, so the client hangs for ~10 minutes instead of failing fast.

3. COPY INTO ... FILE_FORMAT = (FORMAT_NAME = '<name>') is not supported — fails with FILE_FORMAT without TYPE is not currently implemented (follows from 2: named formats don't exist).

4. Common CSV copy options are not implemented — EMPTY_FIELD_AS_NULL, NULL_IF, ESCAPE_UNENCLOSED_FIELD='NONE' fail with "not currently implemented", although they map naturally onto duckdb read_csv defaults.

5. PUT with an unquoted file URL fails to parse — snowflake-sqlalchemy sends PUT file:///path/data.csv.gz @STAGE AUTO_COMPRESS=FALSE (unquoted); sqlglot only recognizes exp.Put with a quoted source, so the statement falls through untransformed to duckdb and dies with Parser Error: syntax error at or near "PUT".

6. Server-mode PUT writes into the client's filesystem — the server returns stageInfo.locationType = LOCAL_FS with a server-local path, so the connector writes the file on the client machine. When client and server are different containers (the normal docker setup), a subsequent COPY INTO finds no files and silently loads 0 rows.

7. Table stages (@db.schema.%table) are not supported for PUT/COPY INTO.

8. Multi-clause MERGE returns NULL counts — with WHEN MATCHED ... UPDATE + WHEN NOT MATCHED ... INSERT, a clause that affects 0 rows yields None instead of 0 in the result row ("number of rows inserted"/"number of rows updated"); snowflake-connector crashes client-side with TypeError: int() argument must be ... not 'NoneType'. Real Snowflake always returns integers.

Repro (condensed)

CREATE OR REPLACE STAGE MY_STAGE FILE_FORMAT = (TYPE = 'CSV');           -- fails on 2nd run (1)
CREATE OR REPLACE FILE FORMAT MY_FMT TYPE='CSV' SKIP_HEADER=1;           -- HTTP 500 (2)
PUT file:///tmp/data.csv.gz @MY_STAGE AUTO_COMPRESS=FALSE;               -- parser error (5), wrong filesystem (6)
COPY INTO STAGING FROM @MY_STAGE/data.csv.gz
  FILE_FORMAT = (FORMAT_NAME = 'MY_FMT');                                -- not implemented (3)
COPY INTO STAGING FROM @MY_STAGE/data.csv.gz
  FILE_FORMAT = (TYPE='CSV' EMPTY_FIELD_AS_NULL=TRUE NULL_IF=(''));      -- not implemented (4)
PUT file:///tmp/snap.csv.gz @DB.SCH.%SNAP_T AUTO_COMPRESS=FALSE;         -- not supported (7)
MERGE INTO T USING S ON ... WHEN MATCHED THEN UPDATE ...
  WHEN NOT MATCHED THEN INSERT ...;                                      -- None counts (8)

PR #400 implements all of the above.

🤖 Generated with Claude Code