Delta lake federated table not working as expected

[code_vibe]

I’m facing an issue while working with federated Redshift tables in Databricks, and I’m hoping someone here can help me out.

I have a source table(material) in Redshift that I’m querying through the Delta lake federation in Databricks. when I run the query on the federated table, I’m seeing incorrect results, where as the same query gives the expected result when it is run against the same table stored as Delta table in catalog and also gives expected result when the same query run on the source table (Material) in Redshift  directly via Data grip.

Here’s what I’ve tried so far:

(I) I created a Delta table in Databricks, also named material, by reading data from the federated Redshift table and loaded it into the Databricks catalog.
When I run the same query on the Delta table (material), I get the expected, correct output.
(ii) Additionally, when I run the same query in Redshift directly using Datagrip, I get the expected result as well.
(iii) However, when I run the same query on the federated table, it gives incorrect results.

Has anyone faced a similar issue or does anyone know why this discrepancy is occurring between the federated table and the Delta table?

Would really appreciate any suggestions or insights.

Issue Details:
Expected Behavior:

select * from material where material is not null and ((material_group = 'X0450' and material_description like '%FILT%') OR material_group in ('X0500','X0600')))

The query should not return the material "YYYY" because its product group 'X0400' is not included in the filter conditions (BMATLGRP = 'X0450' or BMATLGRP IN ('X0030', 'X0040', 'X0050')).

This behavior is correctly observed when querying the Delta Lake table in the Databricks catalog and the Redshift table directly.

Observed Behavior:

The federated table query in Databricks incorrectly returns the material "YYYY" with product group 'X0400'

Query:
1. Query on Federated Table (Incorrect Results):

Result:

The query returns the material "YYYY" with product group 'X0400', which is incorrect.

When the same above query run on top of Delta lake table and in Redshift directly the query does not return the material "YYYY", which is correct.

I even checked the execution plan of the query in Databricks the predicate pushdown is not happening, I am not sure does  the issue is because of this.

Questions:
1) Has anyone encountered similar issues with federated tables returning inconsistent results compared to source systems?

2) Are there any known limitations or configurations for federated tables that could cause this behavior?

[Brahmareddy]

Hi Code_Vide,

How are you doing today?, As per my understanding, It looks like the issue might be due to predicate pushdown not happening when querying the federated Redshift table in Databricks. Predicate pushdown helps filter data at the source (Redshift) before bringing it into Databricks, improving performance and accuracy. Since the same query works fine on both the Redshift table directly and the Delta table in Databricks, but not on the federated table, it suggests that the filtering conditions are not being properly applied when querying Redshift through federation. You can try explicitly forcing predicate pushdown using hints (if supported) or checking Databricks' query execution logs to see how the query is being processed. Also, reviewing the Databricks-to-Redshift connection settings, including any known limitations with predicate pushdown in Databricks federation, might help. If possible, try rewriting the query to see if it affects the pushdown behavior, or test with simpler conditions to confirm where the issue lies.

Regards,

Brahma