Nightly KDW Updates
KDW (the KFS Data Warehouse) loads data from dozens of tables in KFS (i.e., pulls set-up as well as transactional data), Monday through Friday, after the normal weeknight batch processes.
KDW updates have a load schedule similar to all other production data mart loads. (Note: at Cornell we are somewhat imprecise with terminology; for this posting, let’s just use the terms “data mart” and “data warehouse” interchangeably.) That is, they are automatically triggered near the end of the batch window, generally after 9 PM, but can fluctuate based upon sequencing and timing of the nightly batch jobs.
The source of the data from which KDW loads data into the data mart is a result of a nightly process called ETL—extract, transform and load—in which data is first extracted from the source systems, then manipulated (i.e., transformed) before being loaded into KDW. Generally, this is a process that takes several hours to run. In the “pre-production” phase, it has been taking 2 hours, but will continue to increase as the amount of data grows.
To avoid further downtime of KFS (i.e., to allow it to be up and available to continue writing data) and lower the risk of introducing errors into the data in the nightly KDW ETL process, KDW ETL runs against a static snapshot, a read-only copy of KFS data frozen at a point in time. The snapshot of the data keeps it from changing so that you don’t end up with data that is orphaned or out of sync, which would likely be the case if you were to grab data at different points in time from a “live” system.
So the weeknight process in general is that we shut down KFS so that people are not entering data online while we are running batch, the batch processes run, then we snapshot the data. When the snapshot is complete, we then bring up the KFS system and “kick off” the KDW load. That way, if the data mart load fails for some reason—let’s say, because of running out of temporary disk space, or some network interruption (two of the top three most common problems, the third being bad data)—we just start the KDW load part of it over again.
Stephanie Herrick
KFS ID&R Tech Lead
Comments
Leave a Reply
Recent Comments