I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there a preferred type of technology. For example, is it more effective to a Data Warehouse (like Snowflake) or in some other MPP system like Spark?

To make the problem more concrete I created a hypothetical problem similar to my actual problem. Assume I have a table that looks like this: enter image description here

I am working on some logic that requires account pairs that have the same name. To find pairs of accounts with the same account I can easily do something like this:

FROM accounts as account1
JOIN accounts as account2 ON account1.name = account2.name AND account1.acount_id != account2.acount_id

The problem I am facing is due to the amount of data I am processing. There are roughly ~2 trillion records I am trying to self JOIN on. Obviously, this will take some time and some pretty serious compute. I have run a similar query in Snowflake using XL and 3XL warehouses but after several hours of running, I canceled them. I am hoping there is a more cost-effective or time-efficient way.

Has anyone had success with massive JOINs? Are there any other tricks I could deploy? What tool did you find the most effective?

  • This isn't really a problem solvable by changing the tool, rather it's one that is by change in process, architecture, or hardware. For example, hashing the name, indexing that hash, and pre-staging the materialized results are techniques to help you solve this problem. Those are all techniques generally applicable to most database systems. After properly architecting your system and improving your process, it just becomes a numbers game with the amount of data you have. E.g. 1 terabyte of data = 1 terabyte of data, no matter what system you use to store it.
    – J.D.
    Commented Feb 25, 2022 at 0:04
  • @J.D. Actually there are important architectural supports for large joins in some database systems that are simply not available in others. This is a valid question. Commented Nov 15, 2022 at 2:09
  • @WestCoastProjects In all modern mainstream database systems, and I'm sure also in Snowflake, employ pretty much the same techniques for handling joins with large datasets. In fact, even the novelty join methodology you mentioned in your answer uses essentially the same standard algorithms under the hood as the typical physical join operators of other systems. I never said OP's question was invalid, rather offered one way to help solve his problem architecturally.
    – J.D.
    Commented Nov 15, 2022 at 2:24
  • J.D. Please provide evidence that any of the other systems support the specific approach mentioned in my answer. I would be more than glad to learn that others do: but making assumptions ("I'm sure") does not cut it. Even getting spark 's support to work is arduous and it does explicitly support it. Commented Nov 15, 2022 at 2:31
  • @WestCoastProjects I didn't say the specific approach is exactly available among other systems but under the hood of the operation you referenced is either a Hash or Merge Join of sorts which are natively implemented in all modern mainstream relational database systems. While I'm sure the exact operation you speak of has been fine tuned for specific use cases to improve performance by a degree, it's nothing revolutionary, else it'd be the standard. I say this as someone who has worked with and joined tables in the 10s of billions, on modest hardware, regularly.
    – J.D.
    Commented Nov 15, 2022 at 2:39

1 Answer 1


I am not sure whether exporting such amount of data would be faster/cheaper.

Instead of self-join I would try sorting or grouping:

GROUP by name


WITH cte as (
    select name, row_number() over (order by id partition by name) as rn
select name from cte where rn > 1;

And choose the biggest warehouse. The smaller warehouse the more spilling to disk, which significantly slows down the execution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.