We have been in contact with Sean via email, so I am posting the answers here, should anyone else run into the same scenario.
Our API works on the database level, but you should make sure you do not mix in a LINQ statement, which executes the query and works with the data in application memory. The methods are .NET static extension methods which amplify ObjectQuery class, since it implements IQueryable interface. The ObjectQuery is IQueryable out-of-the-box, but supports only basic syntax. When you use something not-supported, you will get fallback to enumerable. If you want to use LINQ, we recommend to call Enumerable methods instead of Queryable.
Using CustomTableItemProvider is the best method of accessing data in CustomTable, but that may not be the best option for your scenario. Kentico allows you to define Classes and class fields the same way as you do with Custom Tables using Custom modules, and then generate ready to use ClassInfo and ClassProvider code. This approach has the benefit of using our Abstract provider class that we use for our classes, so it natively uses hashtables to cache data, and will be able to use the Fake class to fake test data. This object will behave like any other Kentico InfoObject or Provider. The only downside is that you will need to define UI for your custom module, but that can be done from the UI using the predefined UI Page templates for listing and editing.
The DataEngine API uses hashtables to store object information and they can use ID, CodeName or GUID as key. If the Kentico class you work with has these hashtables enabled, any method that retrieves a single object based these fields will use hashtable entry if available. Most built-in Kentico classes use these hashtables, but some objects may have it turned off to conserve memory. This usually only happens for infrequently used objects, or if the class is missing that particular field (i.e. GUID). For example the CustomTableItemProvider has ID and GUID hashtables turned on, so as long as you retrieve single object this way it will come from cache if it is there. Other object may have more aggressive caching turned on. For example the SKUInfoProvider will retrieve all SKUs and store it in hashtable the first time it is used to retrieve one.
The hashtables are not used when loading more than one object, or when using SQL conditions in the DataEngine API. These are executed against the database. If you would like to cache results of such queries, for example in your web parts, you will need to do so using CacheHelper.
The Fake class that you tried to use is meant to substitute ProviderObject of our AbstractProvider Class with a copy that has fake data loaded. Since your custom class is not a Kentico provider it has no ProviderObject property, and therefore it has no use for you. You would need to write the logic that returns fake data directly into your provider class.