Recommended handling of Dev/Test/Production ETLs?
I'm thinking about the best way to fit Kettle into our development process, and I'd love to hear how other people are using it. We maintain separate environments for development, testing, and production - separate application servers, separate databases. Each developer has local instances, and there's a separate development instance that our coninuous integration system deploys new builds to. Then there's a pre-production environment for testing, and finally the actual production environment.
In my ideal world, I'd be able to develop an ETL in Spoon against my local dev database instance. Once it's working, I'd be able to save that ETL to pre-production, and run it with Chef - and I'd like the ETL to automagically run against the test database without modification, simply by nature of the fact that it's running on the test server and not my workstation.
Our web applications already work this way: they don't actually contain database connection info, they just expect to be able to look up their data sources via JNDI, and we can then configure our various Tomcat instances to supply data sources to the correct databases, so our apps magically point to the write database by nature of the environment they are deployed on.
Is there a good way to do this with Kettle? I think this has been brought up recently, but I didn't understood the explination. I noticed that in certain places Kettle allows the use of environment variable substitution. If such substitution was allowed in database connections, I think that would be an acceptable solution. And if I could define a database connection by supplying the path to a Java properties file, that would be even better =)
How are some of you other Kettle users addressing this need right now?
RE: Recommended handling of Dev/Test/Production ETLs?