I should clarify, step 1 in most pipelines is pulling data out of the sources and replicating it in Snowflake. Then a multi-source join is a normal ANSI SQL join on literal tables in different schemas of the same database, not "tables".
(Some call this model "ETLT", where the first ETL part is just moving data from APIs or other databases into a shared db, and the extra "T" joining that data across sources or otherwise organizing it in useful ways.)
(Some call this model "ETLT", where the first ETL part is just moving data from APIs or other databases into a shared db, and the extra "T" joining that data across sources or otherwise organizing it in useful ways.)