Mismatch of Columns in databricks vs Athena.

[DavidSzedlak]

We are trying to expose one of our external tables to databricks via unity catalog, but we are having an issue with column mismatch, ie few of our columns are not visible in databricks. Is this a known issue? If so, can anyone best advise me on where to go from here? Many thanks!

[Advika]

Hello @DavidSzedlak!

This could be due to a schema mismatch, which occurs when the data files evolve but the table's metadata isn’t updated. Try running: MSCK REPAIR TABLE <table-name> SYNC METADATA
This should refresh the schema and make the missing columns visible.

[LRALVA]

Hi @DavidSzedlak ,

1.  Unity Catalog caches metadata for performance. If new columns were added to the source table after the initial creation, they may not be reflected.
Run the following command to refresh metadata: ALTER TABLE <catalog>.<schema>.<table_name> REFRESH;

2.  Check Column Mapping in External Table Definition:
    Verify the external table definition in Unity Catalog. Column definitions in Unity Catalog may differ from the         underlying file structure.
    Use the MSCK REPAIR TABLE command if new columns were added to the data source but are missing in the metadata.
Ex: MSCK REPAIR TABLE <catalog>.<schema>.<table_name>;

3.  Review Permissions and Access Control:
Missing columns could also result from insufficient permissions.
Confirm the user/role has the appropriate access rights.
Command to Check Grants: 
Ex:  SHOW GRANTS ON TABLE <catalog>.<schema>.<table_name