For a migration of that scale, I’d lean toward storing metadata in database tables rather than YAML files. It’s easier to query, update, and integrate with orchestration tools, especially when you have 500 tables. YAML works fine for small projects, but it gets messy fast at enterprise scale.
For orchestration, a generic parameterized workflow is usually the way to go. Instead of creating 500 separate pipelines, have one dynamic pipeline that loops through your metadata and processes each table. You can still parallelize tasks for performance, but this approach keeps things maintainable and flexible.