> AI agents: this is one page from Mammoth Analytics documentation. The index of all pages as Markdown is https://docs.mammoth.io/llms.txt. Append `.md` to any docs URL, or send `Accept: text/markdown`, to get Markdown.

# Multi-source integration

The master data problem: five systems, each with authoritative data on a slice of your business, each with its own schema, naming conventions, and update cadence. The result is a mess of VLOOKUPs, manual reconciliation, and reports that do not agree.

This guide is for data engineers, integration specialists, and analysts. It shows how to build a single, authoritative master dataset in Mammoth that consolidates multiple sources, resolves conflicts, handles schema differences, and stays current automatically.

**What you will build:** A master dataset that ingests from multiple sources, normalizes schemas, deduplicates records, and refreshes on a configurable schedule.

![Salesforce, ERP, Google Sheets, PostgreSQL and CSV/Excel sources feed Mammoth pipelines that output a master dataset.](https://docs.mammoth.io/api/v1/images/20260805_054538_multi-source-architecture1.jpg)

---

## The challenge of integrating multiple sources

Multi-source integration problems compound:

- Source A uses `customer_id`, Source B uses `CustomerID`, Source C uses `cust_no`
- The same company appears as "Acme Corp", "ACME", and "Acme Corporation" across sources
- Sources update at different cadences: CRM hourly, ERP daily, warehouse weekly
- Any single source can have gaps; the integrated view should fill from others

---

## Step 1: Map your sources

Before building, document what you are consolidating. For each source:

| Source | Type | Key field | Update cadence | Schema notes |
| --- | --- | --- | --- | --- |
| Salesforce CRM | Connector | `AccountId` | Scheduled refresh | Account.Name, Industry, Region |
| NetSuite ERP | File upload | `Customer_Code` | Daily export | Different region codes |
| Google Sheets | Connector | `cust_id` | Manual updates | Marketing team maintains |
| Warehouse DB | Connector | `CUST_NO` | Weekly | Most authoritative for revenue |
| Support tickets | Webhook or REST API connector | `customer_id` | As events arrive | Health score data |

This mapping is your integration contract. You will reference it throughout the build.

---

## Step 2: Normalize each source

For each source, build a normalization pipeline that outputs a consistent schema.

**Standard output schema:**

```plaintext
customer_id_canonical   | string  : normalized unique identifier
company_name_canonical  | string  : deduplicated company name
region_canonical        | string  : standardized region (AMER/EMEA/APAC)
industry_canonical      | string  : standardized industry
source_system           | string  : which source this row came from
source_updated_at       | date    : when the source record was last updated
```

**Normalize Salesforce:**

1. Connect Salesforce Account data via **+ New Dataset** → **Connectors** → **Salesforce**
2. Transform:
   - Use **Rename Column** in each column's menu to match the standard schema: `AccountId` → `customer_id_canonical`, `Name` → `company_name_canonical`
   - **Bulk Replace** on `Region` to normalize to AMER/EMEA/APAC
   - **Add column** `source_system`, then **Label & Insert Values** with the constant value `salesforce`

Repeat this normalization pattern for each source, producing one dataset per source with identical schemas.

---

## Step 3: Deduplicate company names

The hardest part of master data: "Acme Corp" and "ACME Corporation" are the same company.

1. Combine all normalized datasets into one (see Step 4 for the pattern)
2. On the consolidated dataset, add a **Generative AI** task
3. Describe: "Standardize the company_name_canonical column. Consolidate variations of the same company into a single canonical form. Use the most complete or official version (for example, prefer 'Acme Corporation' over 'ACME' or 'Acme Corp')."
4. Select `company_name_canonical` as the context column and preview the output
5. Review the output: spot check a sample of rows, and correct any mismatches using **Bulk Replace**

![Mock preview: ten company-name variants consolidated by Generative AI into three canonical companies with total revenue.](https://docs.mammoth.io/api/v1/images/20260805_054603_multi-source-deduplication1.jpg)

> Tip: Generative AI row limit\
Generative AI processes only the first 50,000 rows of a view and writes at most 300 characters per value. For larger datasets, deduplicate in stages, or use **Bulk Replace**, which groups the unique values of a column for you.

---

## Step 4: Consolidate with priority rules

Append all normalized sources, then apply priority rules where sources conflict.

### Consolidate all sources

1. Open your first normalized dataset (e.g., Salesforce)
2. In each additional source's pipeline, add a **Send to dataset** task (**Unify**) pointing to the consolidated dataset, with Update Method **Combine data**
3. Result: all rows from all sources, one per record per source, in a single consolidated dataset

### Resolve conflicts with priority rules

When the same company appears in multiple sources with different values, you need a rule for which source wins.

1. Decide which source owns each field:
   - `revenue`: the warehouse DB (most authoritative)
   - `region`: Salesforce
   - `industry`: Salesforce
   - `health_score`: Support tickets
2. Create one View per owning source using a **Conditional Filter** on `source_system` (for example, `source_system` is `warehouse_db`)
3. Starting from a View with one row per `customer_id_canonical` (use **Remove duplicate rows**), bring in each field from its owning source's View with **Lookup** (one value) or **Join** (several columns) on `customer_id_canonical`

---

## Step 5: Add a freshness indicator

Track which sources contributed to each master record and when they last updated.

1. Add a **Calculate date difference** task: Start Date `source_updated_at`, End Date **Current Time (UTC)**, unit **Days**, new column `DaysSinceUpdate`
2. Add an **AI SQL Query** task: "Add a FreshnessStatus column: 'Stale' if DaysSinceUpdate is greater than 7, otherwise 'Current'"
3. This becomes your master data freshness dashboard

---

## Step 6: Set up multi-source refresh automation

Each source updates on a different schedule. Set up refreshes for each:

1. Open **Automations** in the project sidebar → **New Automation**
2. Create **Dataset Refresh** automations for each connector source (Salesforce, Google Sheets, the warehouse DB)
3. Stagger the schedules, for example Salesforce at 5 AM and the warehouse at 5:30 AM, so the sources are current when downstream tasks run
4. A **Send to dataset** task runs whenever its pipeline runs, so the consolidated dataset updates as each source refreshes. Automations cannot yet wait on one another, so allow enough time between schedules

---

## Step 7: Send the master dataset to downstream destinations

1. Open your master dataset's View in the Data Editor
2. Use **Export** to add a destination
3. Options:
   - **Export** → **Databases** → BigQuery, PostgreSQL, SQL Server, or Redshift: push the master dataset to your data warehouse
   - **Live Link**: an auto-updating URL for BI tools (Tableau, Power BI) or lightweight consumers
   - **Export** → **Tableau Server/Cloud**, SFTP, or REST API Export for other downstream systems

---

## Results to expect

- One authoritative master dataset replaces several separate reports
- Source and update date are tracked on every record
- Business teams can read the master dataset or its dashboards without maintaining the reconciliation themselves

---

## Variations: schema drift and golden records

**Handle schema drift**\
When Source A adds a new column, your normalization pipeline silently excludes it (it is not in the standard schema). Mammoth pauses the update and prompts you when a refresh or new file looks different from what you saved, so review the new columns and update your standard-schema mapping.

**Golden record pattern**\
For true MDM (master data management), the "source wins" priority rule is not enough. Build a golden record by scoring each source field's completeness and recency, then taking the highest-scoring value regardless of source.

---
Source: https://docs.mammoth.io/guides/use-cases/multi-source-integration.md · Updated: 2026-10-13