> 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.

# Data validation patterns

> Add Data Check tasks, quality gates, quarantine Views and reconciliation checks to Mammoth pipelines, and keep audit evidence for regulated reports.

A dashboard that shows wrong numbers is worse than no dashboard. Validation patterns in Mammoth catch bad data before it reaches a consumer, either by halting the pipeline and alerting, or by flagging records so analysts know which rows to distrust. This guide is for data engineers, QA analysts, and operations managers. It covers gate and flag validation, Data Check tasks, financial reconciliation, quarantine Views, audit evidence, and pre-production testing.

![Source data passes transform steps to a Data Check step; passing rows go to the View, failing rows are quarantined.](https://docs.mammoth.io/api/v1/images/20260805_052244_validation-quality-gate-flow1.jpg)

## Two validation philosophies

**Gate validation**: the pipeline stops if data quality checks fail. Downstream consumers never see bad data because it never reaches them. The cost: analysts have no new data until the issue is resolved.

**Flag validation**: the pipeline continues, but each row has a quality flag (`Valid`, `Warning`, `Invalid`). Consumers see all data, and can filter by quality flag. The cost: analysts might accidentally include flagged rows in their analysis.

**When to use which:**

- Use **gate validation** for financial data, regulated reports, or any output where bad data could cause decisions or legal consequences
- Use **flag validation** for operational data where it's acceptable to analyze partial datasets, or when you want to surface data quality issues as a metric rather than a blocker

## Gate validation with the Data Check task

The **Data Check** pipeline task is built for this. Add it directly to your pipeline rather than bolting checks onto the automation that runs it. A single Data Check task can configure multiple checks: row count, null percentage, value range, unique values, or a **Custom prompt** check (a rule you describe in plain language, which Mammoth turns into SQL), each with its own condition and tolerance. The task has one **fail action**: **Flag and continue** (the pipeline runs normally and the failure is flagged) or **Pause the pipeline** (gate validation: the pipeline stays paused until the data is fixed or approved). Use **Notify on failure** to pick recipients so a failure actually reaches someone.

### Row count check

The simplest and most important check: does the output have a reasonable number of rows?

1. Add a **Data Check** task after your transformation tasks
2. Add a **Row count** check: `> 0` (catches empty output from upstream failure)
3. For time-series data, use a tolerance-based row count check against the previous run to catch unexpected drops (e.g. more than 10% below the prior run)

### Null rate check

For columns that must be populated, add a **Null percentage** check on that column directly in the Data Check step (for example, `Amount` null percentage `< 5`). You do not need to precompute the rate with separate tasks.

### Range check

For numeric columns with known valid ranges, add a **Value range** check in the Data Check task (for example, `Amount` between `0` and `10000000`). Set the fail action to **Flag and continue** to flag the failure for review, or **Pause the pipeline** if any out-of-range value should stop the pipeline.

### Referential integrity check

Verify that foreign keys actually exist in the reference table:

1. **Lookup** the foreign key against the reference table. Unmatched rows resolve to `NULL` in the new lookup column
2. Add a **Data Check** task with a **Custom prompt** check verifying the count of unmatched rows is zero

## Financial reconciliation pattern

For finance and accounting data, reconciliation is the cornerstone of data integrity: totals must match the source system at every transformation stage.

### Control total pattern

1. Extract the control total from the source system (total debits, total credits, row count), often available in a summary report
2. After each major transformation stage, compute the same total in Mammoth
3. Compare: `ControlTotal_Source = ControlTotal_After_Transform`

Example for GL consolidation:

```plaintext
Source GL export: 47,832 rows, total debits = $4,823,441.20
After entity normalization: 47,832 rows, total debits = $4,823,441.20 ✓
After currency conversion: 47,832 rows, total debits = $4,823,441.20 ✓ (USD base)
After inter-company elimination: 46,204 rows, total = $4,318,220.15 ✓ (expected reduction)
```

### Double-entry check

For double-entry accounting data:

1. **Group & Pivot**: `SUM(Debits)`, `SUM(Credits)`
2. **Math Function**: `Imbalance = ABS(SUM(Debits) - SUM(Credits))`
3. **Data Check** task, **Custom prompt** check: `Imbalance < 0.01` (allow for rounding but not structural imbalance), fail action **Pause the pipeline**

### Period-over-period quick check

For monthly financial data, large unexpected swings are a data quality signal (not always, but always worth checking):

1. **Window function**: `LAG(Total, 1)` for previous period total
2. **Math Function**: `MoMChange = ABS(Total - PrevTotal) / PrevTotal * 100`
3. Flag rows where `MoMChange > 50` for manual review. This is a quarantine flag, not a gate

## Quarantine pattern

Instead of halting on bad data, route invalid rows to a separate dataset:

1. Create two Views on the same Dataset, each with its own **Conditional Filter**:

   - Main View: keeps `QualityFlag = 'Valid'`; this is the output consumers read
   - Quarantine View: keeps `QualityFlag` of `Warning` or `Invalid`; export or retain it for review

2. A data steward reviews the quarantine dataset and either:

   - Fixes the source and reruns
   - Manually corrects rows and re-merges to the main View
   - Documents the issue and accepts the partial dataset

The quarantine pattern keeps the main View clean while preserving the bad data for investigation. "Quarantine" here is only a naming convention for the second View. Mammoth has no built-in quarantine mode.

![A Conditional Filter sends Valid rows to the main View and Warning or Invalid rows to a dated quarantine dataset.](https://docs.mammoth.io/api/v1/images/20260805_052318_validation-quarantine-branching1.jpg)

## Audit-ready pipeline design

For regulated environments (SOX, HIPAA, Basel III), validation must leave evidence:

### Document every check

Every Data Check task should have a description that answers:

- What is being checked
- What the threshold is
- Why that threshold was chosen
- Who approved the threshold

Example: `"Row count must be ≥ 44,000. Threshold based on minimum expected daily transaction volume from Finance SLA agreement (Jan 2025). Approved by Controller."`

### Preserve check results

1. Each Data Check task shows its status (passed, failed, bypassed, or not run) and run details, including the result of every check, in the pipeline
2. Mammoth does not write check results to a dataset for you. If your auditors need a historical record of actual values and thresholds, keep your own audit log (for example, a dataset you append to on each review)
3. Export that audit log monthly as evidence for regulatory review

### Activity Log as audit evidence

Mammoth's **Activity Log** captures every user action and every pipeline execution automatically. Exporting it requires an Enterprise plan. For SOX audits:

1. Export the Activity Log for the relevant period
2. The log shows: who triggered each pipeline and when. Pair it with your own audit log for the actual check results (values, pass/fail, thresholds), which the Activity Log does not record
3. Together, these give you documented evidence of data preparation activities. Confirm with your auditors that this meets your requirements

## Testing pipelines before production

Before making a new pipeline production:

1. **Test with synthetic edge cases:** deliberately include rows with nulls, out-of-range values, and mismatched keys, and verify the validation catches them
2. **Test with last period's data:** run the pipeline against the previous period where you know the expected output, and compare results
3. **Test failure paths:** manually trigger a quality gate failure and verify the alert fires and the downstream View is not updated

Document the test results in the pipeline description with the date and outcome. This demonstrates due diligence for future audits.

---
Source: https://docs.mammoth.io/guides/advanced/validation.md · Updated: 2026-10-13