# Joins

Joins combine records from two or more datasets using a condition that defines how the records relate.

Cloud concept. Updated 2026-10-29. Page: https://blowstack.com/tools/cloud-concepts-hub/joins

> A join connects related data, such as orders and customer profiles, to produce a dataset suitable for analysis or further processing. Correct joins require compatible keys, appropriate matching rules, and an understanding of how unmatched records and duplicate keys affect the result.

Practice exams covering Joins: [DEA-C01 AWS Certified Data Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate), [MLA-C02 (beta) AWS Certified Machine Learning Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-machine-learning-engineer-associate).

## 6 key aspects of Joins

Reliable joins depend on correct matching rules, deliberate row-preservation behavior, validated cardinality, and execution strategies appropriate to data size and timing.

1. **Explicit matching conditions**: Join predicates define relationships using one or more keys, ranges, or other conditions. Normalize identifiers and data types before matching records from independently managed sources.
2. **Control over unmatched records**: Inner joins exclude unmatched records, while outer joins preserve records from one or both inputs. Choose the behavior according to business requirements, such as retaining orders whose customer profiles have not arrived.
3. **Cardinality and data grain**: Repeated keys can multiply output rows and inflate downstream aggregates. Establish what each input row represents and validate expected one-to-one, one-to-many, or many-to-many relationships.
4. **Null and filter semantics**: Ordinary SQL equality does not match null keys, and filters applied after an outer join can remove preserved rows. Test null handling and predicate placement explicitly rather than assuming all unmatched records remain.
5. **Distributed execution costs**: Large joins can require substantial network transfer, memory, and temporary storage. Reduce input size early and evaluate shuffle, broadcast, and data-distribution choices using execution plans and runtime metrics.
6. **Temporal correctness**: Historical enrichment may require effective-date conditions rather than matching only a business key. Streaming joins also require deliberate treatment of event time, late arrivals, and retained state.

## Cloud services for Joins

A common integration pipeline uses AWS Glue or Amazon EMR to normalize keys and join raw datasets before storing curated results in Amazon S3. Amazon Athena can query and validate those results without loading them into a warehouse, while Amazon Redshift supports repeated analytical joins and ELT over warehouse data. Amazon Managed Service for Apache Flink addresses continuous enrichment and stream-to-stream matching. These services are alternatives or complementary processing layers, not mandatory stages; choose where joins execute based on data location, freshness requirements, expected reuse, transfer costs, and operational needs.

- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue ETL jobs can join data from multiple sources using Spark DataFrames, Spark SQL, or supported DynamicFrame transformations. Use them to normalize keys, enrich records, and write integrated datasets, while monitoring shuffle volume and skew.
- **[Amazon Athena](https://blowstack.com/tools/cloud-services-hub/amazon-athena)**: Amazon Athena supports SQL joins over data in Amazon S3 and can query supported external sources through federated query connectors. Filter and project inputs early, and account for scan volume, connector behavior, and memory requirements.
- **[Amazon Redshift](https://blowstack.com/tools/cloud-services-hub/amazon-redshift)**: Amazon Redshift performs warehouse joins for analytics and ELT, including queries involving supported external data. Table distribution choices, statistics, and query plans help determine whether joins require expensive data redistribution.
- **[Amazon EMR](https://blowstack.com/tools/cloud-services-hub/amazon-emr)**: Amazon EMR runs frameworks such as Apache Spark that implement distributed joins across large datasets. Engineers can tune broadcast thresholds, partitioning, and skew-handling strategies to suit workload characteristics.
- **[Amazon Managed Service for Apache Flink](https://blowstack.com/tools/cloud-services-hub/amazon-managed-service-for-apache-flink)**: Amazon Managed Service for Apache Flink supports streaming joins using Flink APIs and SQL. Depending on the join type, event-time constraints, watermarks, and state management determine how records are matched and how late data is handled.

## Joins exam questions

Real practice exam questions on Joins, with the correct answer and its explanation.

### Question 1 (MLA-C02 (beta))

**Question:**

A manufacturer uses an AWS Glue Spark job to combine equipment readings in Amazon S3 with calibration settings extracted from a maintenance database. Each reading has a globally unique reading_id, a site_id, and a device_id. Device IDs are unique only within a site, and different sites reuse the same device IDs with different calibration settings. The calibration dataset contains exactly one row per (site_id, device_id) pair. All join-key fields are non-null and have consistent data types and formats.

Some readings have no corresponding calibration settings, and some calibration settings belong to devices that have no readings. The ML training dataset must contain exactly one row per reading, enriched only with that device's calibration settings from the same site when available. Missing calibration attributes must remain null, and calibration settings without readings must not create output rows.

Which join configuration meets these requirements?

**Correct answer:** Use a left outer join with readings as the left input and calibration settings as the right input, matching on both site_id and device_id.

**Explanation:** The composite key prevents cross-site matches. Because calibration settings are unique on that key, each reading matches at most one calibration row. The left outer join preserves every reading, supplies null calibration attributes for unmatched readings, and excludes calibration-only rows.

### Question 2 (DEA-C01)

**Question:**

A company stores customer accounts in Salesforce and support incidents in ServiceNow. Both systems expose a shared customer account identifier, and each Salesforce account has a unique identifier. Some incidents have no matching Salesforce account, and some accounts have no incidents. A data engineer must create a daily dataset in Amazon S3 that contains exactly one row for every incident, enriched with account attributes when a match exists. Unmatched incidents must have null account attributes, and accounts without incidents must not produce rows. The company wants managed SaaS connectors without custom API integration code. Which solution meets these requirements?

**Correct answer:** Configure separate scheduled Amazon AppFlow flows to transfer Salesforce accounts and ServiceNow incidents to S3. Run an AWS Glue job that performs a left outer join with incidents on the left and accounts on the right, using the shared account identifier.

**Explanation:** AppFlow provides managed connectors to extract data from the two SaaS sources into S3. AWS Glue can then join the datasets. A left outer join preserves every incident and supplies null account attributes for unmatched incidents. The unique account identifier prevents multiple account matches from multiplying incident rows.

## Practice Joins in a full exam

Questions on Joins in each practice exam, with every answer option and an explanation for each:

- [DEA-C01](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate): 4 questions
- [MLA-C02 (beta)](https://blowstack.com/practice-exams/aws-certified-machine-learning-engineer-associate): 1 question

## What is Joins

Joins integrate data by matching records according to a relationship, usually expressed through shared keys. For example, an order dataset might contain customer\_id while a customer dataset contains customer\_id, region, and account\_type. Joining them makes it possible to calculate revenue by customer region without storing customer attributes in every original order record. The inputs can originate from databases, object storage, applications, or event streams, provided the processing engine can access and interpret them.

The join type determines which records survive. An inner join returns matching combinations only. A left outer join preserves every record from the left input and supplies null values for right-side columns when no match exists. A right outer join reverses that preservation rule, while a full outer join preserves unmatched records from both inputs. A cross join produces every possible pairing and can create an unexpectedly large result. Semi joins retain left-side records that have a match without adding right-side columns; anti joins retain left-side records that have no match.

Before joining data from different sources, normalize key types and formats. A customer identifier stored as a number in one source and a padded string in another may require explicit conversion and business rules. Leading zeros, case differences, whitespace, and incompatible timestamp conventions can cause incorrect matches. In ordinary SQL equality comparisons, null does not match null; use an engine-supported null-safe comparison only when that behavior reflects the intended relationship.

Understand the grain of each input: what one row represents. If one customer has ten orders, joining customers to orders normally produces ten matched rows for that customer. If the customer input also contains three rows for the same key, those ten orders can produce thirty rows. This multiplication is valid join behavior, not automatic deduplication. Validate uniqueness, select the appropriate historical version, or aggregate to the intended grain before calculating totals. For slowly changing customer dimensions, the join may need both the customer key and the order timestamp to identify the version effective when the order occurred.

In cloud pipelines, joins can run during ETL before loading a destination, during ELT inside a warehouse, or at query time across accessible sources. Distributed joins often shuffle records across workers so matching keys meet on the same worker. Broadcasting a sufficiently small input can avoid shuffling the larger input, but consumes memory on each receiving worker. Filter unnecessary records, select only required columns, maintain useful statistics, and inspect execution plans. Highly frequent keys can create skew, where a few workers process disproportionate amounts of data.

Predicate placement also affects correctness. After a left join, a WHERE condition requiring a right-side value removes unmatched rows and can effectively produce inner-join behavior. Place conditions in the join predicate when they should restrict matches while preserving left-side records. A join is also different from UNION, which combines result rows rather than matching related records.

Streaming joins introduce timing and state considerations. Two unbounded streams usually require time constraints and state-retention policies to keep processing manageable. Late events may miss their matching window, so define event-time behavior and reconciliation requirements. For any pipeline, test matched and unmatched counts, duplicate-key rates, output cardinality, and business totals before publishing the integrated dataset.

## Learn Joins in lessons

2 short lessons on Joins in 2 certification courses.

- MLA-C02 (beta): Joins: matching records across datasets (Premium), https://app.blowstack.com/lessons/joins-matching-records-across-datasets-mla-c02
- DEA-C01: Joins: matching records across sources (Premium), https://app.blowstack.com/lessons/joins-matching-records-across-sources-dea-c01
