How can we find the backing dataset for a table which is being queried by using Foundry Postgres SQL service in slate?
Edit: We do have a way to find sync information from Details Tab in a dataset. I want to reverse enginner this. To find a way to get dataset details using foundry sync table name as i don't know the main dataset.
The metadata related to the mapping from a dataset RID to a postgres table is stored in the postgate.dataset
table, however only the administrator account has permissions to query the postgate
schema. If you've "lost" the source dataset and can't find it by looking at the possible input datasets in the Dataset preview, you can reach out to your Foundry Support contact to get further help.
The general best practice is to manually add the relevant datasets into the Dataset tab in the Slate app - I normally do this even if I'm primarily using the Platform tab to read from the object layer instead of from Postgres.
The reason this isn't automatically inferred is because Slate doesn't actually introspect your query and you can write queries that dynamically choose which table to query at runtime.