TIL: How Snowflake, DBT, and Databricks Foreign Catalogs Work Together

So, I’ve been looking into ways to share data across different platforms, and one setup that really caught my eye involved DBT, Snowflake, and what they call “foreign catalogs” in Databricks. It’s not the only way to do things, of course – there’s also data replication, using Iceberg tables, or CDC streams – but this particular pattern just felt… neat.

Basically, one of the teams we work with has their data setup like this: they use DBT to shape their data within Snowflake. Then, they make that Snowflake database available as a foreign catalog in Databricks. The cool part is that when we run a query against that foreign catalog in Databricks, the actual work gets sent back to Snowflake to be done. No moving data around, no making copies, it just runs where the data already is.

The Architecture Explained

Let’s break down how this architecture flows.

┌─────────────────────────────────────────────────────────────┐
│                    TIL: Data Flow Architecture               │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐     │
│  │  Source     │    │  Snowflake  │    │    DBT      │     │
│  │  Data       │───▶│  DWH        │───▶│  Models     │     │
│  └─────────────┘    └─────────────┘    └─────────────┘     │
│                           │                     │           │
│                           ▼                     ▼           │
│                    ┌─────────────────────────────────┐      │
│                    │   Database (Foreign Catalog)    │      │
│                    └─────────────────────────────────┘      │
│                               │                             │
│                               ▼                             │
│                    ┌─────────────────────────────────┐      │
│                    │      Databricks Workspace       │      │
│                    │   (Query Pushdown to Snowflake) │      │
│                    └─────────────────────────────────┘      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

First, data comes into Snowflake, which is acting as our main data warehouse.

Next, DBT comes into play, cleaning up and organizing that raw data into structured models.

Then, that Snowflake database gets exposed to Databricks. This is done through Unity Catalog, making it appear as a foreign catalog.

Finally, when you query this foreign catalog from Databricks, the query is actually executed on Snowflake.

Pseudo Code Examples

Here’s a peek at what that looks like in practice.

With DBT, you might have a model like this, written in SQL, that aggregates order amounts:

SELECT
  order_id,
  customer_id,
  order_date,
  SUM(amount) as total_amount
FROM {{ ref('stg_orders') }}
GROUP BY 1, 2, 3

And then, inside Databricks, you’d query it through the foreign catalog, maybe using Python and SQL:

df = spark.sql("""
  SELECT *
  FROM foreign_catalog.sales.orders
  WHERE order_date >= '2026-01-01'
""")

When you run that Databricks query, it’s actually being processed by Snowflake.

Why This Pattern Matters

Now, why is this pattern significant? Like I said, it’s just one approach among many, but it’s a strong contender if you want to use Snowflake’s processing power for your transformations and then bring Databricks in for machine learning or deeper analysis. It tackles a few issues pretty effectively.

No data duplication - For starters, you avoid duplicating data. The data lives in one place, Snowflake, but you can access it from multiple tools. No need to copy it back and forth.

Leverage platform strengths - It also lets you play to each platform’s strengths. Snowflake handles the heavy lifting of transformations, and Databricks is there for your ML tasks or advanced analytics.

Single source of truth - This setup also helps establish a single source of truth. All your business logic is defined in the DBT models, so everyone is looking at the same, consistent numbers.

Cost efficiency - And from a cost perspective, you’re not paying extra for storage just to move data around. Queries run where the data resides, which tends to be more efficient.

DBT Basics & Use Cases

A quick note on DBT itself: it’s a tool that lets you transform data using SQL, helping teams build reliable data models. It’s become quite popular for modern data workflows. It offers things like version control for your models, automatic testing, and ways to track data lineage. But DBT isn’t just for this specific Snowflake-Databricks pattern; it’s useful for building data mesh architectures across different platforms, creating data quality checks, implementing CI/CD for data pipelines, and even working with streaming data. Personally, this discovery has me intrigued—I’m planning to dive deeper into DBT and explore how it can fit into more of my work. There are plenty of resources out there if you want to join me.

Want to learn more? Check out the DBT Learning Catalog for hands-on courses and tutorials.

Key Takeaways

So, the main takeaway here is that building modern data systems isn’t really about picking just one tool. It’s about connecting the right tools together smartly. In this case, DBT transforms data in Snowflake, foreign catalogs make it accessible in Databricks, and query pushdown keeps things efficient. This approach fits alongside other strategies like using Iceberg tables or setting up data replication. If you’re thinking about how to share data across different environments, this pattern is definitely worth a look. It’s straightforward, helps manage costs, and cuts down on the hassle of duplicated data.