Review Foreign Key Processing during ingestion.

[ulixius9]

trafficstars

While ingesting foreign key constraint we do ES lookup to find the referred table's FQN https://github.com/open-metadata/OpenMetadata/blob/b2ce491ff1c2e473222b006a9e1c1915ed537e87/ingestion/src/metadata/ingestion/source/database/common_db_source.py#L539

because we do not get the database name of the FK from sqlalchemy dialect. This may lead into multiple issues, it may produce incorrect versioning in case the ES did not populate, also the FK information only gets reflected after the 2nd run.

To solve this we can modify/patch the SQA dialects, and grab the information about the database as well, for example snowflake stores the fk db name, once we have all the ingredients to generate FQN we will not have to make a call to ES, and FK can reflect from the first run itself.

[harshsoni2024]

I will work on this.