0

Our data warehouse is currently loaded with a traditional daily ETL batch job, but we're looking to soon implement a few stars in real-time (fed by streaming Kafka messages, FWIW). So only a couple fact tables and dimensions will be in real-time, while the rest will remain loaded daily. I was wondering how others have dealt with the issue of a real-time fact table with FKs into daily batch-loaded dimensions.

The scenario we have is our business is pushing for our Sales Workflow star schema to be in real-time so we can run intraday workflow analytics, but there are keys in the fact table, like into customers and sales reps (and several others), that'll still be populated daily. Without turning the entire DW in real-time, what are some best practices for solving the "delayed" key problem?

0

One possible solution is to create missing dimension members solely based on what is available in the fact data, and then merge (aka upsert) the full dimension data as it arrives later.

For this to work, the attributes of your dimensions will need to be able to accommodate ‘unknown’.

  • We'd considered that approach, but ran into issues because our data team is running analytics on those daily batch dimensions, and inserting new, sparsely populated records in real-time to be upserted later had unintended consequences with their reporting. – romanpilot Oct 3 '17 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.