How do I add a variable to $node from a module? I would like to perform a lookup on $node->uid from a module, fetch a field from the user profile and then attach that field to $node for use in template files.

I can do this within a theme preprocess method, but my understanding is that adding data (and performing queries) the "Drupal Way" means doing it in a module.

I guess I'm asking what hook I want to target. (Please note that this module did not implement the content type I want to alter.)
Try hook_node_load(). With this hook you can add info to a node.