Analyzing Data Across Multiple Files

You have three files. orders.csv has order IDs, customer IDs, product names, and amounts. customers.csv has customer IDs, names, and tier levels. products.csv has product names, categories, and margins.

The question you need to answer: "What's the total revenue by customer tier for each product category?" No single file has all the columns. You need to combine them.

In a traditional workflow, you'd load everything into a database or write a Python script. In ExploreMyData, you load all three files as tabs, join them together, and build your analysis on the combined result. Everything stays in the browser.

Step 1: Load all your files

Open ExploreMyData and load your first file. Then load the second and third. Each file gets its own tab at the top of the workspace. You can switch between tabs to preview each file independently.

With all three files loaded, you can see:

• orders: 10,000 rows of order_id, customer_id, product_name, amount, order_date
• customers: 500 rows of customer_id, name, email, tier, signup_date
• products: 50 rows of product_name, category, margin_pct, supplier

Step 2: Join orders with customers

On the orders tab, click the green + in the Pipeline panel and select Join from the Data group. The panel lets you pick the other table and the join columns.

1. Select customers as the table to join with.
2. Set the join column: customer_id from orders matches customer_id from customers.
3. Choose the join type from the Inner / Left / Right / Full switch. For this analysis, Left makes sense: keep all orders, even if a customer record is missing.
4. Click Apply.

The result is a new view with all the order columns plus the customer columns (name, email, tier, signup_date). The pipeline now shows the join as a step. Under the hood, it's running:

SELECT "__mammoth_l".*, "__mammoth_r".*
FROM "orders" AS "__mammoth_l"
LEFT JOIN "customers" AS "__mammoth_r"
  ON "__mammoth_l"."customer_id" = "__mammoth_r"."customer_id"

Step 3: Join the result with products

Now that orders and customers are combined, you can add the product data. Add another Join step:

1. Select products as the table to join with.
2. Set the join column: product_name from the current view matches product_name from products.
3. LEFT JOIN again.
4. Apply.

The pipeline now has two join steps. The view contains columns from all three files: order details, customer tier, and product category. You're ready to analyze.

Step 4: Build the analysis

With the combined data, answering "total revenue by customer tier and product category" is straightforward. Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group:

1. Group by: tier and category
2. Add one aggregation: SUM on amount
3. Apply.

The step runs an ordinary group-by against the output of the two joins above it, which you refer to as pipeline_output if you ever rewrite it as a SQL Query step:

SELECT "tier", "category", SUM("amount") AS "amount_sum"
FROM pipeline_output
GROUP BY "tier", "category"

Note the output column name. Group & Aggregate names its results <column>_<function>, so SUM on amount gives you amount_sum. There is no alias box in the panel. If you want it called total_revenue, add a Rename Columns step after it.

Reach for Pivot instead when you want the categories spread across columns rather than down rows. Here we want one row per tier and category pair, which is a group-by, not a pivot.

The pipeline now has three steps: join customers, join products, group and aggregate. Each step is visible, editable, and deletable.

To see the biggest numbers first, click the amount_sum header in the grid. That sorts what you're looking at without adding a step. If you want the ordering baked into the result, use Limit Rows with its "Sort by" and "Sort direction" fields, Top / Bottom Rows, or a SQL Query step with an ORDER BY. There is no standalone Sort operation.

How joins work in the pipeline

A join is just another pipeline step. It combines two tables into one view. Every operation after the join works on the combined data. This means you can:

• Filter the joined data (e.g., only "premium" tier customers)
• Add calculated columns using fields from both tables (e.g., amount * margin_pct)
• Aggregate across dimensions that came from different files
• Chain multiple joins to bring in three, four, or more files

If you delete a join step, subsequent steps that reference columns from the joined table will break. The pipeline will show an error, and you can fix it by re-adding the join or adjusting the downstream steps.

Multi-file tabs vs. multi-view tabs

There's an important distinction. Each loaded file gets its own tab. You can apply independent pipelines to each file. When you join, the result appears in the current tab's pipeline.

This means you can clean each file independently before joining. For example:

1. On the customers tab: trim whitespace from names, fill missing tier values with "standard".
2. On the products tab: lowercase all category names for consistency.
3. On the orders tab: filter out cancelled orders, then join with the cleaned customers and products.

The join references the other file's current state, including any pipeline steps applied to it. Clean first, then join.

Tips for multi-file analysis

• Check join keys first. Before joining, switch to each tab and look at the join column. Are the values consistent? Does one file use "US" and the other "United States"? Clean that before joining.
• Watch for duplicate columns. If both files have a column called "name", the join will create two columns. Use Select Columns to drop the one you don't need.
• Start with the largest table. Join the smaller lookup tables into the main fact table, not the other way around. It's more intuitive and easier to verify.
• Use LEFT JOIN by default. It preserves all rows from your main table even if there's no match in the lookup table. INNER JOIN drops unmatched rows, which can silently lose data.

Load your files and start joining →