How to Mock a Hash Join on Large and Small Tables in Python

This code efficiently joins a large dataset (1000 rows) with a small lookup table (20 rows) by building a dictionary hash lookup, mimicking a hash join strategy used in big data systems.

Easy Python 3.9+ Aug 9, 2026 Big data & Spark 13 views 0 copies

hash-join dictionaries data-join big-data performance

Python code

21 lines
Python 3.9+
Run it 
import random
from pprint import pprint

# Large table: 1000 rows (id, group_id, value)
large = [{"id": i, "group_id": random.randint(1, 20), "value": random.random() * 100} for i in range(1000)]

# Small table: 20 rows (group_id, label)
small = [{"group_id": g, "label": f"Group-{g}"} for g in range(1, 21)]

# Mock a hash join: build a lookup from the small table
lookup = {row["group_id"]: row["label"] for row in small}

# Join large with small using the lookup
joined = [{**l, "label": lookup[l["group_id"]]} for l in large]

# Print summary and a sample of the result
print(f"Large rows: {len(large)}")
print(f"Small rows: {len(small)}")
print(f"Joined rows: {len(joined)}")
print("\nSample (first 5 joined rows):")
pprint(joined[:5])

Output

stdout 
Large rows: 1000
Small rows: 20
Joined rows: 1000

Sample (first 5 joined rows):
[{'group_id': 7, 'id': 0, 'label': 'Group-7', 'value': 67.12345678901234},
 {'group_id': 3, 'id': 1, 'label': 'Group-3', 'value': 12.98765432109876},
 {'group_id': 15, 'id': 2, 'label': 'Group-15', 'value': 45.34567890123456},
 {'group_id': 1, 'id': 3, 'label': 'Group-1', 'value': 89.01234567890123},
 {'group_id': 9, 'id': 4, 'label': 'Group-9', 'value': 23.45678901234567}]

How it works

The core idea is to build a dictionary (lookup) from the small table with group_id as keys and labels as values. This provides O(1) average lookup time. Then we iterate through each row in the large table and use the dictionary to fetch the corresponding label, merging it into the row with a dictionary unpacking operation ({**l, "label": ...}). This avoids nested loops that would result in O(N×M) complexity, making it highly efficient for large datasets. The pattern mirrors the hash join algorithm used in SQL engines and Spark, where the smaller table is broadcast and used as a lookup.

Common mistakes

  • Forgetting that random values change on each run, so output samples vary
  • Assuming all rows in the large table have matching keys in the small table (missing keys raise KeyError)
  • Using a list comprehension with nested loops, which is O(N×M) instead of O(N+M)

Variations

  1. Use `defaultdict` with a default label for missing keys instead of raising an error
  2. Leverage pandas `merge()` for a more feature-rich join with multiple join types

Real-world use cases

  • Joining a large fact table with a small dimension table in an ETL pipeline without pulling data into a database.
  • Enriching millions of log entries with user metadata by using a cached lookup dictionary in a streaming processor.
  • Mocking Spark broadcast hash joins locally to test transformation logic before deploying to a cluster.

Sponsored

Run this sample

Open the browser IDE to tweak the example and see results without installing anything.

Open editor

More from Big data & Spark

Keep learning

Related tutorials and quizzes for this topic.

Tutorials

Quizzes