Databricks Community

Raj_DB · Wednesday

Hi everyone,

I am looking for a method to connect Databricks to Snowflake so I can query and transform the data directly in a notebook. I specifically want to avoid methods that just perform a one-time copy of the tables into Databricks, as I need the data in my notebook to reflect the latest refreshed data in Snowflake.

Can anyone suggest the best architectural approach for this? If you have any sample code on how to establish the connection and query the live data, that would be greatly appreciated!

Thanks in advance.

saurabh18cs · Wednesday

Hi @Raj_DB I would treat this as a more of federated-query problem statement than ingestion.1) lakehouse federation (catalog federtation): https://learn.microsoft.com/en-gb/azure/databricks/query-federation/

you can expose the external source through Unity Catalog and query it with normal Databricks SQL.

This is preferable in an enterprise Unity Catalog environment because connection/governance can be managed centrally instead of every notebook carrying its own Snowflake connection configuration.

df = spark.sql("""

SELECT *

FROM snowflake_catalog.sales.orders

WHERE order_date >= current_date() - INTERVAL 7 DAYS

""")

display(df)

2) snowflake spark connectior (legacy query federation )

https://docs.databricks.com/aws/en/archive/connectors/snowflake

If federation isn't available/suitable in your environment, the Snowflake Spark connector is the more traditional approach.

sfOptions = {

"sfUrl": "<account>.snowflakecomputing.com",

"sfUser": "...",

"sfDatabase": "MY_DB",

"sfSchema": "MY_SCHEMA",

"sfWarehouse": "MY_WH"

}

df = (

spark.read

.format("snowflake")

.options(**sfOptions)

.option("dbtable", "CUSTOMERS")

.load()

)

Harshad_B · Wednesday

@Raj_DB, Databricks has native Lakeflow connector to connect with Snowflake and ingest data. But it is not a recommended solution if your requirement is to only read data and not have it physically ingested in a catalog. If data is read-only then consider using Federation.

Source that can reply you decide -

Read and write data from Snowflake, archived and Experimental (the page with the disclaimer): https://docs.databricks.com/aws/en/archive/connectors/snowflake
What is Lakehouse Federation? https://docs.databricks.com/aws/en/query-federation/ . What is query federation? https://docs.databricks.com/aws/en/query-federation/database-federation . Run federated queries on Snowflake: https://docs.databricks.com/aws/en/query-federation/snowflake
What is Lakeflow Connect? (Federation versus Lakeflow decision rule): https://docs.databricks.com/aws/en/ingestion/overview . Managed connectors in Lakeflow Connect: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/
Query-based connectors overview: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/query-based-overview . Create a query-based ingestion pipeline: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/query-based-pipeline
Configuring Snowflake for Spark in Databricks (Snowflake docs): https://docs.snowflake.com/en/user-guide/spark-connector-databricks

Thanks,
Harshad

AbhilashNagilla · Wednesday

Hi @Raj_DB , in query federation, "JDBC pushes the Unity Catalog query down to the external database", so each notebook query reads Snowflake's current data. To set up @saurabh18cs saurabh18cs's snowflake_catalog example with PEM private key auth:

Generate an unencrypted private key with -nocrypt and register the public key on the Snowflake user:

 openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
 openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Store the user name and private key (on one line, without header or footer lines) in a secret scope, then create the connection:

 CREATE CONNECTION snowflake_conn TYPE snowflake
 OPTIONS (
   host '<account-identifier>.snowflakecomputing.com',
   port '443',
   sfWarehouse '<warehouse>',
   user secret('<scope>', '<user-secret-name>'),
   pem_private_key secret('<scope>', '<pem-secret-name>')
 );

Create the foreign catalog:

 CREATE FOREIGN CATALOG snowflake_catalog USING CONNECTION snowflake_conn
 OPTIONS (database '<snowflake_database>');

Aravind_Reddy · Wednesday

Hello @Raj_DB

you can use the below code snippet

from pyspark.sql import SparkSession
import pandas as pd

def read_from_snowflake_ELC(query):
# Initialize Spark session
spark = SparkSession.builder \
.appName("SnowflakeQueryExample") \
.getOrCreate()

# Snowflake connection options
options = {
"sfURL": "XXXXX", # Replace with your Snowflake account URL
"sfUser": "XXXXX", #your snowflake user account
"sfPassword": "XXXXX", #your snowflake password
"sfDatabase": "DAAS", #your snowflake database
"sfSchema": "XXXXX", #your snowflake schema
"sfWarehouse": "XXXXX", #your snowflake warehouse

}

# Execute the query and load the result into a Spark DataFrame
snowflake_table = (spark.read
.format("snowflake")
.options(**options)
.option("query", query)
.load()
)

# Convert Spark DataFrame to Pandas DataFrame

return snowflake_table

query = (f''' select * from dbo.table_A ''')

reading data into a dataframe:-
df = read_from_snowflake(query)

Jen_Evangelista · Thursday

HI, @Raj_DB, Lakehouse Federation with a Snowflake connection and a foreign catalog fits this requirement.

After configuring those objects, you can query from a notebook:

df = spark.sql("""
SELECT *
FROM snowflake_catalog.sales.orders
WHERE order_date >= current_date() - INTERVAL 7 DAYS
""")
display(df)

Replace the catalog, schema, and table names with your own.

For query federation, the query is pushed to Snowflake rather than reading a previously copied Delta table. However, an already displayed notebook result will not refresh automatically: rerun the query to retrieve updated data.

Check network access, Unity Catalog permissions, and supported compute before setting up the connection.

https://docs.databricks.com/aws/en/query-federation/snowflake

balajij8 · Thursday

Lakehouse Federation is the right way to query live Snowflake data directly from a notebook. Every query runs live against Snowflake with no data copy and hence the results are fresh. More details here

CREATE CONNECTION conn_snow TYPE snowflake
OPTIONS (
  host 'snow.snowflakecomputing.com',
  port '443',
  sfWarehouse 'snowwh',
  user secret('secretscope', 'key'),
  password secret('secretscope', 'key')
);

CREATE FOREIGN CATALOG snowflake_catalog
USING CONNECTION conn_snow
OPTIONS (database 'snowdb');

You can query the tables using below in notebook

SELECT * FROM snowflake_catalog.schema.table

You may use catalog federation if the snowflake data is stored as Apache Iceberg tables. When the tables are managed as Apache Iceberg, Databricks can read directly from cloud storage using external locations and authorized paths. It bypasses snowflake compute and is faster than JDBC. It will not work for normal Snowflake tables. More details here

You can avoid the old Snowflake Spark connectors. Also avoid manual JDBC connections as it bypasses Unity Catalog governance

Louis_Frolio · Friday

Hello @Raj_DB, I took a look at both internal and external documentation and here is what I found.

The thread already has the right answer: Lakehouse Federation. @saurabh18cs, @Harshad_B, @AbhilashNagilla, @Aravind_Reddy, @Jen_Evangelista and @balajij8 all pointed there, so I'll confirm it and fill in a few gaps.

Why they are right.

You create a Unity Catalog connection to Snowflake and a foreign catalog that mirrors your Snowflake database. Nothing is copied. Each query is pushed down to Snowflake over JDBC and runs against whatever is in the table at that moment, and Databricks doesn't use its result or disk cache for federated queries, so there's no stale layer in between.

Jen's point still stands: a displayed cell output is static, so rerun it for fresh data. The DataFrame itself isn't a snapshot either. Spark is lazy, so every action (display(), count(), write) hits Snowflake again. If you need one consistent copy for the rest of a run, cache it or write it to Delta first.

Setup (Abhilash and balajij8 already posted the DDL)

Pick an auth method: Snowflake OAuth, Entra ID, Okta, OAuth access token, PEM private key, or basic auth. PEM key-pair is a good default for notebook and pipeline use. Keep credentials in a secret scope.
CREATE CONNECTION ... TYPE snowflake with host, port 443, warehouse, user and credential.
CREATE FOREIGN CATALOG snowflake_catalog USING CONNECTION snowflake_conn OPTIONS (database '<db>');
Grant USE CATALOG, USE SCHEMA and SELECT in Unity Catalog. On the Snowflake side the role needs USAGE on the database and schema plus SELECT on the tables. Missing Snowflake grants are the usual reason a foreign catalog shows up empty.

Compute: DBR 13.3 LTS or above in standard or dedicated access mode, or a pro/serverless SQL warehouse, with network access to Snowflake.

Notebook side

df = spark.sql("""
    SELECT region, SUM(amount) AS total
    FROM snowflake_catalog.sales.orders
    WHERE order_date >= current_date() - INTERVAL 7 DAYS
    GROUP BY region
""")
display(df)   # this is when Snowflake actually gets hit

# optional: land a snapshot in Delta for the rest of the notebook
df.write.mode("overwrite").saveAsTable("my_catalog.scratch.weekly_orders")

Things that cause "challenges"

Filters, projections, joins, aggregates, sorting, LIMIT/OFFSET and some window functions push down to Snowflake. Anything else is finished in Databricks after the rows cross the wire. Filter and aggregate early, and use EXPLAIN FORMATTED or the query profile to see the SQL Snowflake actually received.
Each foreign table scan returns to one executor over a single stream, so pulling a whole large table and filtering in Databricks can OOM that executor.
Every query wakes your Snowflake warehouse, so you pay Snowflake credits plus Databricks compute. Fine for interactive work; size the warehouse and its auto-suspend accordingly.
Foreign catalogs are read-only. Results get written to Delta on the Databricks side.
Table and schema names are lowercased in Unity Catalog.
A table with a column type Unity Catalog doesn't support (Snowflake VECTOR, for example) can't be queried at all. Create a Snowflake view that excludes the column and query that.

Regarding other approaches

balajij8's Iceberg point: catalog federation only applies to Snowflake Managed Iceberg tables, but you don't have to choose. Configure the foreign catalog with authorized paths and a storage location and Databricks reads Iceberg tables straight from cloud storage, then falls back to JDBC for standard tables and views. That needs DBR 16.4 LTS or above, or a SQL warehouse on 2025.16 or above.

Aravind's Spark connector snippet works, and the connector still ships in Databricks Runtime, but Databricks moved its page to the archive and marked it experimental and untested, with Lakehouse Federation recommended instead. It's also outside Unity Catalog governance. Where it still earns a spot is writing back to Snowflake, since federation is read-only. If you use it, drop the SparkSession.builder block (the spark session already exists in a notebook) and read sfPassword from dbutils.secrets.

Harshad's Lakeflow Connect pointer: if "live" ever turns into "refreshed on a schedule," a materialized view over the foreign table or a Lakeflow Connect query-based ingestion pipeline through the same foreign catalog both reuse the connection you've already built.

Takeaway: set up the connection and foreign catalog once, query it like any other Unity Catalog table, and know that every action re-reads Snowflake. That's exactly the behavior you asked for.

References: