I need to write a loop for our ETL process in order to aid in development/testing. We'd like to separate out Extract and Transform steps, but we want to keep them in the same app to reduce maintenance. (Side note, if you have thoughts on a better ETL process, please let me know. We're all pretty new to this.)
Because some of our large datasets can take 30-40 minutes to load from a database, we don't want to load data every time we load. The only two methods I'm aware of is Incremental Loading and using a Loop to determine with parts of the script get loaded. And since I'm not sure which is a better practice, I thought I'd come here.
TO THE QUESTION
I think I would like for the loop to do the following: Check for a variable (vLightSwitch) and see if it's On or Off. If it's On run our Extract section of script (which stores the raw data into QVDs that are accessed by the Transform phase) then carry on to load the rest of the script. If vLightSwitch is off, skip the Extract portion of script and go straight to Transform. So the Transform and store statements will always run, but the Extract section will only run if the vLightSwitch is turned On.
I'm not familiar with writing loop syntax at all so I don't know the best way to do this.
Thanks for the help!
(and let me know if there's a better way to manage the ETL process)