fakesnow

The fakesnow server accepts any username, password, and account combination via HTTP.

Connection Details:

• user: fake
• password: snow
• account: fakesnow
• host: <server_host>
• port: <server_port>
• protocol: http

Snowflake CLI Configuration: Add the following to your config.toml:

[connections.fakesnow]
host = "localhost"
port = <port_number>
account = "fakesnow"
user = "fake"
password = "snow"
protocol = "http"

Note: You MUST include protocol = "http" for compatibility. Use the connection with snow sql -c fakesnow ....

Java (JDBC) Connection: Use the following URL format: jdbc:snowflake://<host>:<port>/?ssl=off&account=fakesnow&db=<database>&schema=<schema>

Important for Java:

1. Keep account in the URL (required for local hosts).
2. Run the JVM with --add-opens=java.base/java.nio=ALL-UNNAMED to prevent ExceptionInInitializerError with Arrow.

[connections.fakesnow]
host = "localhost"
port = <port number from server startup>
account = "fakesnow"
user = "fake"
password = "snow"
protocol = "http"

Fakesnow provides built-in fixtures for pytest. To use them, add fakesnow.fixtures to your pytest_plugins in conftest.py.

Standard Patching (Autouse):

import pytest
from typing import Iterator

pytest_plugins = "fakesnow.fixtures"

@pytest.fixture(scope="session", autouse=True)
def setup(_fakesnow_session: None) -> Iterator[None]:
    yield

Patching specific modules:

import pytest
import fakesnow

pytest_plugins = "fakesnow.fixtures"

@pytest.fixture(scope="session", autouse=True)
def _fakesnow_session() -> Iterator[None]:
    with fakesnow.patch("mymodule.write_pandas"):
        yield

Using the fakesnow_server fixture:

import snowflake.connector

def test_with_server(fakesnow_server: dict):
    # fakesnow_server contains connection kwargs (host, port, etc.)
    with snowflake.connector.connect(**fakesnow_server) as conn:
        conn.cursor().execute("SELECT 1")
        assert conn.cursor().fetchone() == (1,)

The fakesnow server implements a subset of the Snowflake SQL API using Starlette. It manages user sessions via tokens and provides endpoints for authentication, query execution, and session management.

Key API Endpoints

• POST /session/v1/login-request: Authenticates a user and returns a session token.
• POST /session: Manages session lifecycle (can be used to delete a session via ?delete query parameter).
• POST /queries/v1/query-request: Executes SQL statements (supports describeOnly and various binding types).
• POST /queries/v1/abort-request: Aborts a running query.
• GET /monitoring/queries/{sfqid}: Checks the status of a specific query ID.

Session Management

Sessions are identified by a token passed in the Authorization header. The server supports two database modes:

1. Shared In-Memory: By default, connections share a single in-memory database.
2. Isolated In-Memory: If the login request includes FAKESNOW_DB_PATH: ":isolated:", a new, isolated in-memory database is created for that connection, ensuring no table sharing with other connections.

To start a session, send a POST request to /session/v1/login-request. The request body must contain session parameters like AUTOCOMMIT and optionally FAKESNOW_DB_PATH to control database isolation.

Request Body Structure:

{
  "data": {
    "SESSION_PARAMETERS": {
      "AUTOCOMMIT": true,
      "nop_regexes": [],
      "FAKESNOW_DB_PATH": ":isolated:"
    }
  }
}

Response: A successful response returns a token which must be used in the Authorization header for subsequent requests, and a serverVersion used by clients to determine compatibility.

{
  "data": {
    "token": "<session_token>",
    "serverVersion": "<version>",
    "parameters": [
      {"name": "AUTOCOMMIT", "value": true},
      {"name": "CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY", "value": 3600},
      {"name": "CLIENT_STAGE_ARRAY_BINDING_THRESHOLD", "value": 0}
    ],
    "sessionInfo": {
      "databaseName": "<db>",
      "schemaName": "<schema>"
    }
  },
  "success": true
}

When using fakesnow with DuckDB, note the following behaviors for current_timestamp and type casting:

• current_timestamp returns a TIMESTAMP WITH TIME ZONE.
• Casting current_timestamp to TIMESTAMP removes the timezone.
• Using AT TIME ZONE 'UTC' converts the timestamp to a TIMESTAMP (without timezone).
• Limitation: Direct casting from TIMESTAMP WITH TIME ZONE to TIMESTAMP(9) results in a Conversion Error: Unimplemented type for cast. To achieve high precision without the error, cast to TIMESTAMP first, then to TIMESTAMP(9).

select current_timestamp;                                    -- TIMESTAMP WITH TIME ZONE
select current_timestamp::TIMESTAMP;                         -- TIMESTAMP
select current_timestamp::TIMESTAMP::TIMESTAMP(9);           -- TIMESTAMP_NS
select current_timestamp AT TIME ZONE 'UTC';                 -- TIMESTAMP
select (current_timestamp AT TIME ZONE 'UTC')::TIMESTAMP(9); -- TIMESTAMP_NS

-- This will fail:
select current_timestamp::TIMESTAMP(9);                      -- Error: Conversion Error

select current_timestamp;                                    -- 2023-06-24 17:06:31.483+10 (TIMESTAMP WITH TIME ZONE)
select current_timestamp::TIMESTAMP;                         -- 2023-06-24 07:06:31.483 (TIMESTAMP)
select current_timestamp::TIMESTAMP::TIMESTAMP(9);           -- 2023-06-24 07:06:31.483 (TIMESTAMP_NS)
select current_timestamp AT TIME ZONE 'UTC';                 -- 2023-06-24 07:06:31.483 (TIMESTAMP)
select (current_timestamp AT TIME ZONE 'UTC')::TIMESTAMP(9); -- 2023-06-24 07:06:31.483 (TIMESTAMP_NS)
select current_timestamp::TIMESTAMP(9);                      -- Error: Conversion Error: Unimplemented type for cast (TIMESTAMP WITH TIME ZONE -> TIMESTAMP_NS)

When using fakesnow with Postgres, current_timestamp and its casts behave as follows:

• current_timestamp returns a TIMESTAMP WITH TIME ZONE.
• Casting to TIMESTAMP results in a TIMESTAMP WITHOUT TIME ZONE.
• Using AT TIME ZONE 'UTC' also results in a TIMESTAMP WITHOUT TIME ZONE.
• Unlike DuckDB, direct casting to TIMESTAMP(9) is supported and returns a TIMESTAMP WITHOUT TIME ZONE.

select current_timestamp;                                    -- TIMESTAMP WITH TIME ZONE
select current_timestamp::TIMESTAMP;                         -- TIMESTAMP WITHOUT TIME ZONE
select current_timestamp::TIMESTAMP::TIMESTAMP(9);           -- TIMESTAMP WITHOUT TIME ZONE
select current_timestamp AT TIME ZONE 'UTC';                 -- TIMESTAMP WITHOUT TIME ZONE
select (current_timestamp AT TIME ZONE 'UTC')::TIMESTAMP(9); -- TIMESTAMP WITHOUT TIME ZONE
select current_timestamp::TIMESTAMP(9);                      -- TIMESTAMP WITHOUT TIME ZONE

select current_timestamp;                                    -- 2023-06-24 07:06:31.483+00 (TIMESTAMP WITH TIME ZONE)
select current_timestamp::TIMESTAMP;                         -- 2023-06-24 07:06:31.483 (TIMESTAMP WITHOUT TIME ZONE)
select current_timestamp::TIMESTAMP::TIMESTAMP(9);           -- 2023-06-24 07:06:31.483 (TIMESTAMP WITHOUT TIME ZONE)
select current_timestamp AT TIME ZONE 'UTC';                 -- 2023-06-24 07:06:31.483 (TIMESTAMP WITHOUT TIME ZONE)
select (current_timestamp AT TIME ZONE 'UTC')::TIMESTAMP(9); -- 2023-06-24 07:06:31.483 (TIMESTAMP WITHOUT TIME ZONE)
select current_timestamp::TIMESTAMP(9);                      -- 2023-06-24 07:06:31.483 (TIMESTAMP WITHOUT TIME ZONE)