What have you tried? Are both of these queries running slowly? Are there other operations which have become a problem? Since you have a live system, rather than speculating about what might be causing a problem, you can add some profiling to be sure.
Your first query can be answered by an index on ( Hour, PuzzleScore ), in that order. Your second would require adding Iip; since it should have excellent cardinality, I'd put it first: ( Iip, Hour, PuzzleScore ). Check with EXPLAIN to confirm whether the indices are actually used; depending on row counts and cardinality, there may be no benefit.
Since you're querying on Hour either way, I'd consider clustering on that field (in conjunction with a unique field or combination of fields to ensure uniqueness, of course).
Caveat: I mostly work in SQL-Server, I'm no expert on MySQL.
There is a forum discussion which discusses this briefly, but quite well IMO: Tek Tips - Using a data warehouse as a source system
Some points for consideration:
Duplication of effort / Single source of the truth
Will the operational system need to apply the same logic to the source data that the data warehouse is already performing?
Read-only source
Does the operational system expect to write changes/updates back to the warehouse?
Timely information
Is the operational system happy with the latency of the warehouse? (Generally T-1)
Service-level agreement
What is the impact to the operational system if there is an outage to the warehouse? In my experience warehouses, inherently, have a lower priority than transactional systems and may have, for example, up to 24 hours to become available (for querying) and up to 4 days for the ETL to be restored, running, and for the warehouse to be up to date.
If the operational system is internal and non-critical, this may be acceptable. If it is customer-facing, and retrieving FX rates from the warehouse for pricing, probably not.
I think the quote from that forum post that sums it up nicely is:
The business drives the technical solution with their requirements.
Your role would be to provide the facts to the business. If the business is willing to accept the risks and proceed with using the data warehouse as a source for an operational system, then I suggest you get that in writing and triplicate.
A "best-of-both-worlds" solution would be for the warehouse to publish the data once processed for the operational system to consume. The data could be extracted to a file or replicated to another/the-operational-system's database. This assumes that your warehouse is not "real-time".
I must admit that I get the heeby-jeebies whenever someone suggests connecting an operational system to our warehouse. Within our environment, we made the architectural decision that we would not control how users could consume the data, provided it does not unfairly impact our ETL processes or other users. An operational system becomes another "user query", and as such we provide the same level of service wrt to availability and accuracy as we do to Joe Bloggs, the junior analyst in Finance.
If a user requires a higher level of service, then we provide the data (via FTP'd files) rather than the user pulling the data (via queries/direct access). This assists in impact analysis for future changes because the extracts are visible within our ETL tool/suite.
Best Answer
We have a similar situation as the one you described, except that these copied tables are only used for ETL (by select queries). I'll describe how we approached it, but you have to evaluate if it works for your environment (see Jon Seigel's comment at your question).
The concept is straightforward:
This approach works for us, drop/recreate is faster than loading into indexed tables; well in the case of a columnstore index you don't have many other options anyway apart from partitioning & using partition switch, but we're not doing that for these tables (EDW fact tables are a different story).