4.5 Connectors, Drivers, and Snowsight Loading

Connectivity: Clients, Drivers, and Connectors

Snowflake exposes a single SQL endpoint that many clients reach through purpose-built libraries. The exam expects you to recognize each one's role.

SnowSQL is the workhorse CLI: it is the standard place to run PUT (upload to a stage) and GET (download), neither of which works in the Snowsight worksheet. It supports variables, scripts (-f file.sql), and connection profiles, making it suited to automated and scheduled loads.

The Spark connector lets a Spark job read from or write to Snowflake; it pushes down filters and projections into Snowflake and moves bulk data through an internal or external stage rather than row-by-row. The Kafka connector subscribes to topics and lands records into Snowflake — historically via Snowpipe (micro-batch through internal stages) and now also via Snowpipe Streaming for lower latency, typically writing each topic's data into a table with RECORD_CONTENT and RECORD_METADATA VARIANT columns.

Loading in Snowsight and Choosing the Right Path

Snowsight web loading

Snowsight (the modern web UI) includes a Load Data into Table wizard reached from a database table's actions. It lets you pick local files (up to a small per-file size limit, e.g. ~250 MB and a handful of files) and a file format, then runs the staging and COPY INTO for you behind the scenes — convenient for ad-hoc or one-time loads without installing a client. Because the browser cannot run PUT, Snowsight uploads through this managed wizard instead. For repeatable or large-volume pipelines, use SnowSQL scripting, a driver, or Snowpipe.

Picking the right ingestion method

Use this decision guide, which mirrors how scenario questions are framed:

• Periodic large batches on a schedule → bulk COPY INTO on a sized warehouse.
• Files arriving continuously in cloud storage → Snowpipe auto-ingest (event notifications).
• App-driven file notifications / Kafka → Snowpipe REST API or the Kafka connector.
• Sub-second row streaming, no files → Snowpipe Streaming.
• Query files in place without loading → external table over a stage.
• One-off small upload, no tooling → Snowsight Load Data wizard.

Readiness check

You are ready for this 18% domain when you can, without notes: name the six load formats and the three unload formats; list the four stage types and their prefixes (@~, @%t, @my_stage, external); contrast bulk COPY (your warehouse, ABORT_STATEMENT) with Snowpipe (serverless, SKIP_FILE); recall the 100-250 MB compressed sizing rule; and write a FLATTEN query against a VARIANT column. If any of those is shaky, drill it before test day.

Authentication and Driver Behavior

Every connector authenticates to the same account URL (account_identifier.snowflakecomputing.com). Recognize the supported authentication methods, since connectivity questions often hinge on them:

• Username/password — basic, often combined with MFA.
• Key pair (JWT) — a 2048-bit RSA key registered on the user; preferred for service accounts and Snowpipe REST, because the insertFiles call signs a JWT rather than sending a password.
• OAuth / external browser SSO — federated identity for interactive users.

The Python connector can return results as pandas DataFrames (fetch_pandas_all) and write them efficiently with write_pandas, which stages Parquet behind the scenes and runs COPY — a fast bulk-load path from Python. JDBC/ODBC drivers are what BI tools (Tableau, Power BI, Looker) and ETL tools use, and they transparently page large result sets through internal stages for performance. The Go, .NET, and Node.js drivers expose the same SQL surface for their ecosystems.

Result and query behavior shared across drivers

All drivers benefit from Snowflake's result cache (identical query within 24 hours returns instantly without compute) and stream large results through chunked stage files rather than one big payload. For ingestion specifically, drivers that need to upload files call PUT under the hood (the same primitive SnowSQL exposes), then COPY INTO. Knowing that the driver, SnowSQL, and write_pandas all converge on PUT + COPY INTO ties the whole chapter together: stages are the universal hand-off point, COPY INTO is the universal loader, and the connector is just the transport. '

Quick reference for connector questions

When a stem names a specific tool, match it to its role: SnowSQL = CLI with PUT/GET and scripting; Python connector = programmatic loads and pandas; JDBC/ODBC = BI and ETL tool connectivity; Spark connector = big-data pushdown; Kafka connector = streaming ingestion; Snowsight = browser worksheets plus the small-file Load Data wizard. Drivers do not change what loading does — they only change who initiates the PUT and COPY. Authentication choice (password, key-pair JWT, or OAuth/SSO) is independent of the loading method, but key-pair is the expected answer for unattended service accounts and the Snowpipe REST API.

With these mappings memorized, the connectivity slice of this 18% domain becomes a matching exercise rather than a memory test.