I guess the question would be, can Oracle Database see live Teradata tables?
I've found following link https://forums.oracle.com/thread/1091742 that uses DB link to connect to Teradata. can you (or anyone else) please comment if this is possible
If you can use db links, then yes, APEX will work.. However, using DBLinks with APEX is not the best way to work..
Accessing data through db links introduces performance issues, more so when the remote database is non-Oracle. Whether these issues are important to you is difficult to say and depend on the dynamics of how you are accessing the remote data. For example, if you are doing the occasional lookup on remote data, then performance may be acceptable, but if you intend to use Apex as the front end for a complete reporting solution based on remote data, then performance will more than likely be an issue.
I would suggest that maybe the best way forward is to suck it and see. Set up your db link to the remote data and see if the application performs reasonably, if not then there are still some options available that are easier than the ETL type solution you are currently using, for example, possibly using materialized views.