> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradime.io/llms.txt
> Use this file to discover all available pages before exploring further.

# MongoDB schema drift healer

> Build a DinoAI agent that heals dbt™ failures from MongoDB schema drift by inspecting raw VARIANT data, patching the staging model, and opening a fix PR.

The MongoDB schema drift healer is a DinoAI agent for pipelines that land MongoDB collections in Snowflake (via Airbyte or similar) and flatten them with dbt™ staging models. When a document's shape drifts, a renamed field, a flat value moved into a nested object, a type change, the staging model starts returning NULLs or failing outright. This agent inspects the raw VARIANT column to discover the new shape, patches the model with COALESCE-style fallbacks so both old and new documents keep working, and opens a PR with a full drift report.

<Note>
  **Prerequisites**

  * A Snowflake connection with read access to the raw landed collections (the agent samples the `_AIRBYTE_DATA` VARIANT column via `run_sql_query`).
  * A [MongoDB connection](/products/dino-ai/tools-and-features/warehouse-tool/mongodb-tools) (optional but recommended: lets the agent verify the drift against the source collection, not just the landed copy).
  * [Slack connected](/integrations/slack) if you trigger it through Bolt self-healing (the healing session threads into the configured channel).
  * Familiarity with [programmable agents](/products/dino-ai/programmable-agents/index) and [Bolt self-healing](/guides/orchestrate-data-pipelines/set-up-self-healing).

  Estimated time: 20 minutes.
</Note>

## Steps

<Steps>
  <Step title="Create the agent">
    In the **Agent** app, open **Agents**, select **New agent**, and choose **Start from scratch**. Fill in the builder fields below, leave **Model** on **Auto**, then select **Deploy** and choose **Open a pull request**: self-healing selects agents from `.dinoai/agents/*.yml` on the schedule's git branch, so the definition must be merged there. See [Build an agent in the UI](/guides/programmable-agents/build-in-the-agent-ui) for a tour of the builder.

    **Name**: `mongo-dbt-healer`

    **Role**:

    ```text theme={"system"}
    Mongo-to-dbt Schema Drift Healer. When the dbt pipeline fails because
    the upstream MongoDB collection's shape has drifted after landing in
    Snowflake, identify the new shape, patch the dbt model to handle both
    old and new shapes, and open a PR.
    ```

    **Goal**:

    ```text theme={"system"}
    The trigger message contains a dbt failure log — typically a not_null
    test failure on a staging model that flattens a MongoDB VARIANT column
    (e.g. stg_mongo__orders), or a Database Error referencing a path that
    no longer exists in the raw documents (e.g.
    invalid identifier '_AIRBYTE_DATA:PRICE' after a rename in Mongo).

    1. Parse the trigger message and extract:
         - the failing model path (e.g. models/staging/stg_mongo__orders.sql)
         - the failing test or compiled SQL fragment
         - the column(s) that came back NULL or unresolved

    2. Read the failing model AND its YAML tests
       (models/staging/_stg_mongo__orders.yml or similar).

    3. Inspect the raw Snowflake VARIANT to discover the new document
       shape. Run, against the source the failing model SELECTs from:
         - SELECT _AIRBYTE_DATA FROM <source> LIMIT 5; for both recent
           (top by _AIRBYTE_EMITTED_AT DESC) and oldest rows — capture the
           full JSON of each.
         - SELECT TYPEOF(_AIRBYTE_DATA:<field>) ... for every field the
           model references AND for any new top-level keys you see in the
           recent samples.
         - A count split: how many rows match the v1 shape vs the new
           shape (COUNT_IF(_AIRBYTE_DATA:<old_field> IS NOT NULL) vs
           COUNT_IF(_AIRBYTE_DATA:<new_field> IS NOT NULL)).

    4. Build a drift report — for every column in the failing model:
         - v1 path / v2 path / change type (renamed, nested, type-changed,
           dropped, added)
         - count of v1 docs vs v2 docs
         - 2 example before/after JSON snippets

    5. Patch the dbt model. Use COALESCE / nested lookups so BOTH the v1
       and v2 documents produce a non-null row. General shapes:
         - rename: coalesce(_airbyte_data:<old_field>::<type>,
           _airbyte_data:<new_field>::<type>) as <column>
         - flat→nested object: coalesce(_airbyte_data:<old_field>::<type>,
           _airbyte_data:<parent>.<new_field>::<type>) as <column>
         - flat→array element: pick the relevant array element when the v2
           shape moves a previously-flat field into an array
           (_airbyte_data:<array>[<index>].<field>::<type>)
       For genuinely new fields with no v1 equivalent, add new columns
       rather than overwriting existing ones, and document them in the
       YAML. Never silently drop a column a downstream model may use —
       keep the old column name even if the underlying path changed.

    6. Update the staging model's YAML so column descriptions reflect
       "v1 OR v2" sources, and so any test that no longer applies to the
       v2 shape is loosened (e.g. drop accepted_values if v2 introduces
       a new enum value) — but only if the data confirms the change.

    7. Open ONE PR on branch heal/mongo-drift/<collection>-<yyyymmdd>.
       PR description must include:
         - the original dbt error verbatim
         - the drift report from step 4
         - the patched SQL + YAML diff
         - a "Manual follow-ups" checklist: re-run the failing model,
           verify the not_null tests pass against the mixed v1/v2 data.
    ```

    **Backstory**:

    ```text theme={"system"}
    You act immediately — no confirmation prompts, no "shall I proceed?".
    If something is ambiguous, state your assumption and continue. The
    live raw Snowflake table is the source of truth for the current
    document shape, not the dbt model. Always prefer surgical, additive
    edits — the goal is a model that supports BOTH the old and new
    shapes during the transition, not a "clean v2-only" rewrite.

    You only heal ONE failing model per run. If the trigger message
    reports failures across multiple models, pick the most-upstream one
    (typically the staging model) and let the downstream failures resolve
    themselves on the next run after the fix merges. Bundling multiple
    model fixes into one PR is forbidden.
    ```

    **Allowed tools** (everything else is refused, see the [Tools Reference](/products/dino-ai/programmable-agents/tools-reference)):

    * `read_file`, `write_file`, `replace_in_file`, `search_files_and_directories`, `ripgrep_search`
    * `run_sql_query`
    * `run_terminal_command`
    * `list_bolt_schedules`, `get_bolt_run_logs`
    * `list_mongodb_collections`, `mongodb_collection_schema`, `mongodb_count`, `mongodb_aggregate`, `mongodb_find`

    <Info>
      The [MongoDB tools](/products/dino-ai/tools-and-features/warehouse-tool/mongodb-tools) are all read-only. They let the agent confirm the drift at the source (is the rename real in Mongo, or an ingestion artifact?) before patching the model. No Slack tool is needed when triggered by self-healing, because the healing session already threads into the configured channel.
    </Info>
  </Step>

  <Step title="Enable self-healing on the schedule">
    Point the Bolt schedule that runs your MongoDB staging models at this agent, from the UI or as YAML. See [Set up self-healing](/guides/orchestrate-data-pipelines/set-up-self-healing) for the walkthrough:

    ```yaml title="paradime_schedules.yml" theme={"system"}
    self_healing:
      enabled: true
      slack_channel: '#data-alerts'
      agent_name: mongo-dbt-healer
    ```

    <Warning>
      `self_healing.slack_channel` must also appear in this schedule's `notifications.slack_channels`, or the deployer rejects it. The agent threads into the existing failure notification, so that notification has to exist.
    </Warning>
  </Step>

  <Step title="Watch the first heal">
    The next time a MongoDB shape change breaks the schedule, open the failure thread in your self-healing channel. You'll see the session parse the error, sample the raw VARIANT data (recent vs oldest rows), post the drift report, and link the fix PR.
  </Step>
</Steps>

<Check>
  On a drift failure, the agent opens one PR on a `heal/mongo-drift/<collection>-<yyyymmdd>` branch containing the COALESCE-patched staging model, updated YAML, and a drift report quoting the original error, per-column change types, v1/v2 document counts, and before/after JSON samples. Both old and new documents produce non-null rows after the patch. Review the drift report, merge, and retry the Bolt run.
</Check>

## How it works

Schema drift in a document store rarely arrives as a clean cutover: for days or weeks the landed table contains a mix of v1 and v2 documents. That is why the agent's core rule is **additive patching**: every changed field becomes a `COALESCE(old_path, new_path)` so the model works across the transition, and downstream column names never change even when the underlying path did. The count split in the drift report (how many rows match each shape) tells the reviewer when it will be safe to remove the v1 fallback later.

```text theme={"system"}
1. Parse the failure → failing model, test, NULL columns
2. Read the model SQL + YAML tests
3. Sample _AIRBYTE_DATA: recent rows vs oldest rows, TYPEOF per field,
   COUNT_IF split of v1-shaped vs v2-shaped documents
4. Drift report: per column, v1 path / v2 path / change type / counts /
   before-after JSON
5. Patch: COALESCE fallbacks, keep old column names, add (never replace)
   new columns
6. YAML: "v1 OR v2" descriptions, loosen tests only if the data confirms
7. One PR, one model, with the drift report in the description
```

## Next steps

<CardGroup cols={2}>
  <Card title="Bolt pipeline healer" href="/guides/programmable-agents/bolt-pipeline-healer" icon="workflow">
    The general-purpose healer for failed dbt™ runs.
  </Card>

  <Card title="MongoDB schema drift triager" href="/guides/programmable-agents/mongodb-schema-drift-triager" icon="ticket">
    The Jira-driven variant: planned drift changes as tickets.
  </Card>

  <Card title="Set up self-healing" href="/guides/orchestrate-data-pipelines/set-up-self-healing" icon="shield">
    Enable self-healing on a Bolt schedule.
  </Card>

  <Card title="MongoDB tools reference" href="/products/dino-ai/tools-and-features/warehouse-tool/mongodb-tools" icon="database">
    The read-only MongoDB tools this agent uses.
  </Card>
</CardGroup>


## Related topics

- [MongoDB schema drift triager](/guides/programmable-agents/mongodb-schema-drift-triager.md)
- [MongoDB](/integrations/mongodb.md)
- [MongoDB Tools](/products/dino-ai/tools-and-features/warehouse-tool/mongodb-tools.md)
- [Integrations](/integrations/index.md)
- [Tools Reference](/products/dino-ai/programmable-agents/tools-reference.md)