I would still be looking to do all that within an RDBMS.

storing millions of rows and hundreds of columns in 2d array if possible

Sure it's possible - if you have enough memory. I just don't see why you would do that when a database is the right tool for the job. There is nothing you have said here to suggest to me that it isn't.

so how would one go about joining tables from multiple data sources without database links and without temp tables...

Perl arrays and hashtables support nesting (e.g. hash-of-hash-of-array-of-hash etc.), mixed "data types" and slices (a convenient form of indexing). There is also PDL if your data is numeric. Additionally, there is Inline::Python if you want to inline python code you already have (untested by me).

storing millions of rows and hundreds of columns in 2d array if possible

well that's illegal in some states and some of us Perl-ers still work on Spectrums, :)

Update: There's also DBIx::Array which offers this:

foreach my $row ($dbx->sqlarrayhash($sql, @bind)) {
  do_something($row->{"id"}, $row->{"column"});
}
[download]

And there's DBIx::Class as a long-term investment.

bw, bliako

Thank you! this seems like a start. While I would try to get as much as done in SQL way, there are some things like transformations, transposes, joins I really wish I could do it in Perl. Is there a package that would do cross-tab or pivot table in Perl? I found Data::Pivot and would love to test out others when I get a chance...

I have data from more than one database and needed to bring them over to drop, rename, perform calculations, joins and export it as flat file

That sounds like a use for a Temporary Table in the RDMS to me...

can you please elaborate, I'd like to learn from you