Analistable

How to combine datasets in Amazon QuickSight

By the Analistable team · Updated · 5 min read

Open the dataset in QuickSight's data prep editor, choose Add data, and pick another table, file or existing dataset. QuickSight draws a join between them; click the join icon to set the join type (inner, left, right or full outer) and the key fields, then Apply. Keys must have compatible data types, and joins across different data sources need the result imported into SPICE.

Part of our guide: How to merge data in Power BI, Power Query and Tableau

Where combining happens in QuickSight

In Amazon QuickSight you combine data while preparing a dataset, before any analysis is built. The dataset becomes the single table that visuals use. The join editor is built for joins rather than appends, so stacking rows from several files is usually done upstream, for example in the database, in an S3 manifest pointing at several files of the same layout, or with a SQL query using UNION ALL as a custom SQL source.

AWS changes the QuickSight interface regularly, and some features depend on edition and region, so treat labels below as a guide and check the current AWS documentation for limits.

Join two datasets step by step

  1. From the QuickSight start page, open Datasets, choose the main dataset and select Edit dataset.
  2. In the data prep editor, choose Add data and select another table from the same source, a different data source, or an existing dataset.
  3. QuickSight adds the new table to the join diagram and links it with a join icon. Click the icon.
  4. Choose the join type and, under Join clauses, pick the field from each side. Add more clauses for multi-column keys.
  5. Choose Apply, check the preview, then Save & publish.

Join types and what they keep

Orders (1,200 rows) joined to Customers (950 rows) on customer_id
Join typeKeepsRows (example)
InnerOnly orders with a matching customer1150
LeftAll orders; customer fields empty when unmatched1200
RightAll customers; order fields empty when unmatched1210
Full outerEverything from both1260

The numbers work like this: 1,150 orders match a customer and 50 do not. The 1,150 matched orders belong to 890 customers, so 60 customers have no orders. Right = 1,150 + 60 = 1,210, and full outer = 1,150 + 50 + 60 = 1,260. Each customer with several orders appears once per order, which is why a right join returns more rows than the 950 customers.

Always write down the row count of each input and the expected row count of the output before joining. If one customer can have many orders, any join from customers to orders returns one row per order, not per customer.

Common problems

  • Data types differ. A key that is a string in one dataset and an integer in the other can't be joined. Change the field type in data prep, or cast it in custom SQL.
  • Duplicate rows. If the right table has repeated keys, every match is returned, so sums of order amounts grow. Deduplicate or aggregate the lookup table first.
  • Cross-source joins. Joining a table from one source to another (say, Athena and an uploaded CSV) requires importing to SPICE, because no single database can run the query. SPICE capacity and dataset size limits then apply.
  • Calculated keys. If you need a composite or cleaned key, creating it in the source or in custom SQL is the most reliable approach; check the current documentation for what data prep allows as a join clause.
  • Nulls in keys. Rows with null keys never match, so they disappear in an inner join.

Alternatives to joining in QuickSight

If the data lives in one database, a view or custom SQL that does the join is often clearer and easier to test. For files, cleaning and joining before upload avoids type surprises. The same join logic in SQL looks like this:

SELECT o.order_id, o.order_date, o.amount, c.segment, c.region
FROM orders o
LEFT JOIN customers c
  ON TRIM(UPPER(o.customer_id)) = TRIM(UPPER(c.customer_id));

Analistable runs this kind of query in your browser over spreadsheets and CSVs you load, writing the SQL from a plain-English question. It is an analysis tool, not a dashboard service like QuickSight. For join concepts in other BI tools see joining data sources in Tableau and merging tables in Power BI, and for SQL detail see joining multiple tables in SQL. The Power BI, Power Query and Tableau guide ties them together.

Check the joined dataset before building visuals

Once the dataset is saved, build one quick table visual before any charts: count of rows, distinct count of the key, and sum of the main measure. Compare those with the source tables.

Check figures for the Orders + Customers dataset (left join)
CheckOrders sourceJoined dataset
Rows12001200
Distinct order_id12001200
Sum of amount£184,300£184,300
Rows with empty segment-50

Equal rows and sums mean the join did not duplicate anything. The 50 rows with an empty segment are the orders without a matching customer, matching the inner join result of 1,150. If the joined sum were higher, a key in the customers table would be duplicated; if rows were lower, the join type would be inner rather than left.

Write these numbers in the dataset description so the next person who edits the join can repeat the check.

When you need the same join in several analyses, build it once as a dataset and reuse it rather than repeating the join. Newer QuickSight versions also let you use an existing dataset as the source for another, which helps when one team owns a clean customer table that others join to. Check the current AWS documentation for how refresh schedules and permissions carry over between datasets built this way.

Frequently asked questions

Can QuickSight join data from two different sources?
Yes, but the joined dataset must be imported into SPICE rather than queried directly, because no single source can run the join.
Can I union or append datasets in QuickSight?
The join editor is built for joins, so the usual approach is to stack the data upstream, for example with UNION ALL in custom SQL or an S3 manifest listing files of the same layout.
Why did my row count increase after a join?
The table you joined has more than one row for some keys, so each match is returned. Aggregate or deduplicate it before joining.

Related guides