Data Loading, Unloading, and Connectivity · Topic 6 of 7

Snowflake Drivers and Connectors

Know the Snowflake drivers (JDBC, ODBC, Python, Go, .NET, Node.js, PHP) and connectors for Kafka and Spark, plus the SQL API and native connectors.

  • 5 min
In a nutshell
  • Drivers: JDBC, ODBC, Python connector, Go, .NET, Node.js, PHP PDO.
  • SQL API = REST over HTTPS for SQL statements, no driver.
  • Kafka connector runs in Kafka Connect and loads through Snowpipe or Snowpipe Streaming.

What it is

Applications and tools reach Snowflake through drivers and connectors. A driver lets a program written in a given language connect, run SQL and fetch results. A connector moves data between Snowflake and another platform, such as Apache Kafka or Apache Spark, or brings in data from a SaaS application.

The exam asks you to match a tool or language to the right option, and to know which connector loads data in which way.

BI toolTableau, Power BIODBC or JDBCSnowflakeCloud Servicesruns onVirtual warehousePython appSnowflake Connector for PythonSnowflakeKafka topicKafka connectorSnowpipe or Snowpipe StreamingloadsTable

How it works

Drivers

DriverLanguage or useTypical client
JDBC DriverJavaJava applications, many BI and ETL tools
ODBC DriverAny ODBC clientExcel, BI tools, C and C++ programs
Snowflake Connector for PythonPython (implements the Python DB API)Scripts, notebooks, Airflow jobs
Go Snowflake DriverGoGo services
.NET DriverC# and other .NET languagesWindows and .NET applications
Node.js DriverJavaScript on Node.jsWeb back ends
PHP PDO Driver for SnowflakePHPPHP web applications

All drivers authenticate the same way Snowflake does elsewhere: password with MFA, key pair authentication, OAuth, or external browser single sign-on, depending on the driver and account settings. Every query still runs on a virtual warehouse chosen by the session.

Exam tip

The Snowflake Connector for Python is named a "connector" but sits in the drivers family. Questions that ask how a Python script runs SQL against Snowflake point to it; questions about DataFrame code that runs inside Snowflake point to Snowpark.

Other ways to connect

  • SQL API: a REST API that submits SQL statements over HTTPS and returns results as JSON, with no driver to install. It suits serverless functions and languages without a driver.
  • Snowflake CLI: the command line tool for running SQL and managing objects and apps from a terminal or CI pipeline.
  • Snowpark: libraries for Python, Java and Scala that build DataFrame operations which execute inside Snowflake.
  • Snowsight: the browser interface, which needs no driver at all.

Connectors

ConnectorWhat it doesLoad mechanism
Snowflake Connector for KafkaReads Kafka topics and writes them into Snowflake tablesSnowpipe (files buffered from topic messages) or Snowpipe Streaming (rows, lower latency)
Snowflake Connector for SparkLets Spark read Snowflake tables as DataFrames and write DataFrames backUses Snowflake stages and COPY under the hood, with query pushdown to Snowflake
Native connectorsSnowflake built connectors for SaaS applications and databases, installed as Snowflake Native Apps from Snowflake MarketplacePull data from the source into tables on a schedule
Partner toolsETL, ELT and BI tools from Snowflake partners, many available through Partner ConnectEach tool's own method, usually through a driver

The Kafka connector in more detail

The Kafka connector runs in a Kafka Connect cluster, outside Snowflake. Each topic maps to a table. With the Snowpipe ingestion method, it writes topic messages to files in an internal stage and calls Snowpipe to load them. With Snowpipe Streaming, it sends rows directly through channels, with no staged files.

Kafka ConnectconnectorSnowpipe methodInternal stagetemp filespipeTableRECORD_CONTENT, RECORD_METADATAKafka ConnectconnectorSnowpipe Streaming methodChannelsrowsTableseconds latency

By default each row holds two VARIANT columns: RECORD_CONTENT (the message) and RECORD_METADATA (topic, partition, offset and other details). Schema detection and evolution, when enabled, map message fields to separate columns instead.

The Spark connector in more detail

The Spark connector supports query pushdown: filters, projections and aggregates in Spark code are translated into SQL and run in Snowflake, so less data moves to the Spark cluster. It is the choice when an existing Spark pipeline, for example on Databricks or Amazon EMR, must read from or write to Snowflake.

Watch out

Do not confuse the Spark connector with Snowpark. The Spark connector runs your code on a Spark cluster and exchanges data with Snowflake. Snowpark runs DataFrame code inside Snowflake on a virtual warehouse, with no Spark cluster.

Picking the right option

ScenarioAnswer
A BI tool needs a live connectionODBC or JDBC driver, depending on the tool
A Python script loads a CSV and runs SQLSnowflake Connector for Python (PUT and COPY INTO)
An app with no driver must run SQL over HTTPSSQL API
Kafka events must land in Snowflake within secondsKafka connector with Snowpipe Streaming
A Spark job must read Snowflake data with pushdownSnowflake Connector for Spark
SaaS application data must be loaded with minimal codeA native connector from Snowflake Marketplace or a partner ETL tool

Common mistakes

Picking Snowpark for a Spark cluster. Snowpark is not a Spark connector; it runs inside Snowflake.

Thinking connectors use their own compute. Data loaded by the Kafka connector uses serverless Snowpipe or Snowpipe Streaming. Queries pushed down by the Spark connector run on the warehouse of the Spark connection.

Treating the SQL API as a driver. It is a REST API with no library to install, not one of the language drivers.

Assuming the Kafka connector creates flat columns by default. Without schema detection it loads RECORD_CONTENT and RECORD_METADATA VARIANT columns.

What to remember for the exam

  • Drivers: JDBC, ODBC, Python connector, Go, .NET, Node.js, PHP PDO.
  • SQL API = REST over HTTPS for SQL statements, no driver.
  • Kafka connector runs in Kafka Connect and loads through Snowpipe or Snowpipe Streaming.
  • Spark connector exchanges DataFrames with Snowflake and pushes queries down to Snowflake.
  • Native connectors install as Snowflake Native Apps and pull SaaS data into tables.

Check yourself

  • Which driver does a Java application use to connect to Snowflake?

    The JDBC Driver.

  • What two columns does the Kafka connector create in a target table by default?

    RECORD_CONTENT for the message and RECORD_METADATA for topic, partition, offset and other details.

  • What does query pushdown in the Spark connector do?

    It translates Spark operations into SQL that runs in Snowflake, so less data is moved to the Spark cluster.

Topic 6 of 7 in Data Loading, Unloading, and Connectivity

Test yourself on Data Loading, Unloading, and Connectivity

The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.