Foreign Catalog Table - Missing Most Rows

[jruhl]

I have a table pulled from Snowflake via a foreign catalog that's only returning 100 rows in Databricks even though there's billions of records in the source Snowflake table. What steps should I take to diagnose the limitation of rows?

[Ashwin_DSA]

Hi @jruhl,

When you say it's only returning 100 rows, how are you accessing the data? Are you previewing the data or querying it?

You may want to first confirm whether you are hitting an actual federation limit, or whether you are just seeing a small result set due to how the table is being queried or previewed.

Can you... 

• Run SELECT COUNT(*) against the foreign table to confirm whether Databricks can see the full row count.
• Run SELECT * ... LIMIT 1000 and compare that with whatever path is only showing 100 rows.
• Run EXPLAIN on the query to see whether a LIMIT is being pushed down unexpectedly.

For Snowflake foreign catalogs, Databricks supports two access paths... query federation and catalog federation. Query federation pushes the query down to Snowflake over JDBC, while catalog federation reads eligible Snowflake-managed Iceberg tables directly from object storage.

If this is a Snowflake foreign catalog specifically, you can also check the path the table is actually using. In Databricks, Snowflake foreign tables that are read through catalog federation show up as Iceberg source format, while tables read through query federation show up as Snowflake source format. That distinction matters because catalog federation can fall back to JDBC if the table does not meet the direct-access requirements.

If the table is supposed to use catalog federation, I would verify the foreign catalog configuration as well. Databricks documents that Snowflake catalog federation depends on correctly configured authorized paths, and the foreign catalog cannot query tables outside those paths. The same doc also notes that some Snowflake Iceberg tables fall back to query federation if they have incompatible locations, unsupported schemes, or metadata layout issues.

I would also confirm that the Snowflake role used by the connection has the required privileges. Databricks calls out USAGE on the database and schema plus SELECT on the relevant tables as required, and missing Snowflake grants are a common cause of incomplete or missing objects in the federated catalog.

If metadata might be stale, try refreshing the foreign object metadata as documented here: REFRESH FOREIGN. Databricks also notes that refreshing foreign Snowflake tables can be necessary after data or metadata changes in the source system.

Hope this helps.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.