What causes convolutional neural networks to be somewhat translation invariant is the max pooling. Each neuron has a receptive field in the original image. For example, if you have two convolutional layers with stride 1 and one 2x2 max pooling step in between,
That is, input image --> C3x3/1 --> M2x2/2 --> C3x3/1 --> output feature map,
then each neuron in the output feature map sees 8x8 patches in the original image, i.e. has a 8x8 receptive field. That neuron gets excited by stuff that happens anywhere in this 8x8 region (borderignoring border effects not withstanding) because the spatial information was lost in the max pooling step. If you add more max pooling steps to the network you will increase this receptive field.
Typically, in the last few layers, densely connected layers are used, which combine the information from the different receptive fields. There, different regions of the image are connected with different weights, so it does matter where the information came from.
For example, in a face recognition software you might want to abstract the information a bit through max pooling, but not too much, because the information how the different image components (eyes, nose etc.) are spatially related is important.
Or, expanding on the example you gave. Imagine you were to train a network with images of cats and dogs in which the animals only ever appear in the upper left corner. Furthermore, you design the network such that the receptive field of your last feature map before the fully connected layer is a quarter of the input image. Then the classifier would not be able to recognise a cat or a dog in the lower right corner. The weights in the fully connected layer connecting to that part of the image would never have learned anything.
Lastly, you can make your network so deep that the receptive fields of the last layer before the fully connected layers, covers the whole image. In that case, anything in the input image can excite any neuron in the last feature map.