Hi everyone,
I’m working with data pipelines where source systems are expected to provide tables that match a required column specification. In reality, the upstream schema often drifts — for example, columns get renamed, new fields appear, or some expected fields are missing. This causes our Bronze → Silver transforms to break, and at the moment we end up manually editing nodes to reconcile the changes.
What I’d like to achieve is a translation layer that can:
• Detect or handle schema drift in Bronze datasets,
• Map source tables into a stable Silver schema that always aligns with the required specification,
• Reduce or eliminate the need for manual edits whenever providers change their schema.
I’m interested in best practices or patterns others have used in Foundry to solve this, whether through Pipeline Builder features, Python transforms, multi-output patterns, or other design approaches.
I’m also open to experimenting with LLM support. One idea would be to feed the LLM node a description of the target schema (a list of required fields with definitions and expected types), along with the source column names and maybe a small sample of values. The model could then propose mappings from source to target fields, possibly with simple transform suggestions (e.g. cast to date). These would still be validated and enforced by deterministic transforms, but the mapping suggestions themselves could save time.
Has anyone tried something similar or found a robust approach for this kind of schema translation in Foundry?