No matter how you approach it, your problem requires a "creative" solution, worthy of a developer note for subsequent devs/maintainers to use. First, some background, followed by a note, followed by an easy and I think reasonable solution at the end <-- tl;dr.

As Zyava pointed out, translation is subject to the module doing the translation. Templates render in block instances, and block instances have a module_name property which is used when invoking translation; ref Mage_Core_Block_Abstract::__() :

public function __()
{
    $args = func_get_args();
    $expr = new Mage_Core_Model_Translate_Expr(array_shift($args), $this->getModuleName());
    array_unshift($args, $expr);
    return Mage::app()->getTranslator()->translate($args);
}

The module_name property is (normally) derived on demand and based on class name (ref. ::getModuleName()):

public function getModuleName()
{
    $module = $this->getData('module_name');
    if (is_null($module)) {
        $class = get_class($this);
        $module = substr($class, 0, strpos($class, '_Block'));
        $this->setData('module_name', $module);
    }
    return $module;
}

So, if the module_name property is already set, that module translation applies. For extant blocks from core layout, this property can be set via layout XML; for example:

<default>
    <action block="root" method="setModuleName">
        <name>Your_Module</name>
    </action>
</default>

Voilà! Your module CSV owns the translation for that instance. This could be an approach. Of course, there is still the sticky situation of other modules' translation being applied via module-specific helper in block instances (including template files of course), and it's always true for layout XML translations. Also, this approach will break the Disable Modules Output behavior, which uses the module_name param.

Solution

As it turns out, it's possible to specify multiple translation files for a module. It's not done in the core (each module only declares one .csv file), but the functionality is there in Mage_Core_Model_Translate:

public function getModulesConfig()
{
    if (!Mage::getConfig()->getNode($this->getConfig(self::CONFIG_KEY_AREA).'/translate/modules')) {
        return array();
    }

    $config = Mage::getConfig()->getNode($this->getConfig(self::CONFIG_KEY_AREA).'/translate/modules')->children();
    if (!$config) {
        return array();
    }
    return $config;
}

and

protected function _loadModuleTranslation($moduleName, $files, $forceReload=false)
{
    foreach ($files as $file) {
        $file = $this->_getModuleFilePath($moduleName, $file);
        $this->_addData($this->_getFileData($file), $moduleName, $forceReload);
    }
    return $this;
}

Because the files' contents are merged (I tested), it's possible to specify only those strings which you want to override in your custom CSV(s). For example, if you want to translate the Additional Information string on the product page (translated by the Mage_Catalog module), the following would work:

app/locale/Custom.csv:

"Additional Information","More Info, Dude"

In your module config - which should <depends /> on Mage_Catalog to ensure its contents merge after - the following will cause the Custom.csv translation pairs to merge on top of the original:

<frontend>
    <translate>
        <modules>
            <Mage_Catalog>
                <files>
                    <additional>Custom.csv</additional>
                </files>
            </Mage_Catalog>
        </modules>
    </translate>
</frontend>

The nice thing about this approach is that you can collect your overriding core translations in one file.

Try this.

To sum, specify an additional translation file under the core module's translation XPath and place your overriding CSV pairs in there.

<frontend>
    <translate>
        <modules>
            <Mage_Sales>
                <files>
                    <mr_storms_wicked_translations>Mage_Sales_Custom.csv</mr_storms_wicked_translations>
                </files>
            </Mage_Sales>
        </modules>
    </translate>
</frontend>