First off, is there any point to doing a dynamic simulation at a large/realistic scale to get more realism? Example, huge explosion, mile radius, giving you a source that is approximately 3,218 meters across, thus having you make a huge smoke container. Is this any better than doing it with a source 3, 30, or even 300 meters across? If so, what should I watch out for when lighting and rendering such a large scene? Or just what should I watch out for in general?

Houdini is in general unit agnostic.

It seems like you can use what a project requires or personal preference.

When working with large scales camera clipping planes are a bit wonky and you will have to adjust things accordingly. Same goes for lights if they have any near/far clipping.

At super large or small scales bad things can happen due to numeric precision, but I think sidefx has addressed most of these issues on the rendering side by using double floats for things like camera matrices. Not sure what DOPs will do at those scales.

DOPs does have a setting for units that defaults for length in meters and mass in Kg. It's under the Main Menu->Edit->Preferences->Hip File Options.

Something that often happens to me is I will develop a little test around the origin in meters, but then find out that things are either in CM or US units (feet/inches/etc). I tend to leave the setup alone if its producing nice results and use conversions via global scale parameters and/or parenting hierarchies, rather than rework the entire setup.