> For the complete documentation index, see [llms.txt](https://documentation.dnanexus.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://documentation.dnanexus.com/developer/ingesting-data/data-model-loader/appending-data-with-data-model-loader.md).

# Appending Data with Data Model Loader

Append rows and coding entries to an existing Apollo dataset.

Use Data Model Loader (DML) with `mode=append` and an existing Dataset record (`dataset_id`) to add rows or coding entries to that dataset without creating a replacement Dataset record. DML reads the schema from the existing Dataset descriptor, so it does not add or change fields, update the descriptor, or recreate dashboards. Without an existing Dataset record, `mode=append` stages data in an existing database and creates a new Dataset record when the staged data is finalized. To compare the two append workflows with other ingestion options, see [Choose a mode](https://documentation.dnanexus.com/developer/ingesting-data/data-model-loader.md#choose-a-mode).

## Check append prerequisites

Before you run an append, confirm that:

* The target dataset and database come from an append-capable DML `create` or `overwrite` load, which automatically adds the `dx_ingested_at` field to every entity table and creates the `dx_codings` table.
* The target database is in the `open` state.
* The project has **Delete Access** set to **Contributors & Admins** so the run can drop temporary tables and rebuild updated final tables.

{% hint style="warning" %}
Datasets and databases created with older versions of Data Model Loader that do not include `dx_ingested_at` and `dx_codings` cannot be appended to. To use `mode=append`, first re-ingest the dataset with `mode=create` or `mode=overwrite`.
{% endhint %}

## Create an append-enabled dataset

For the initial load, provide the data dictionary, the CSV files for the entities you are loading, and the coding CSV if the dataset contains coded fields:

```shell
dx run data_model_loader_v2 \
  -i mode=create \
  -i output_dataset_name=phenotypic_data \
  -i database_name=phenotypic_db \
  -i dictionary_csv=file-xxxx \
  -i data_csvs=file-yyyy
```

If you use a coding file, add `-i codings_csv=file-zzzz`. For multiple entity files, repeat `-i data_csvs=file-...` for each file. Record the returned Dataset record (`record-xxxx`) and `database_name` for later appends.

## Append data rows

Provide the existing Dataset record, target database name, and `output_dataset_name`, along with `data_csvs` for the entities receiving new rows. `output_dataset_name` remains a required app input even though append mode returns the existing Dataset record unchanged, while `dictionary_csv` is optional because the existing Dataset descriptor supplies the schema.

```shell
dx run data_model_loader_v2 \
  -i mode=append \
  -i output_dataset_name=phenotypic_data \
  -i dataset_id=record-xxxx \
  -i database_name=phenotypic_db \
  -i data_csvs=file-aaaa
```

In a multi-entity dataset, pass `-i data_csvs=file-...` only for the entities receiving new rows. The run processes and rebuilds only the entity tables that receive appended rows, leaving other entity tables untouched. You can include `-i codings_csv=file-bbbb` in the same run, or omit `data_csvs` altogether when appending only coding entries.

### Handle duplicates and validation

Provide either a delta file containing only new rows or a complete file containing previously ingested rows and new rows. A delta file reduces the amount of input data that the run reads and validates, though finalization still rebuilds each updated entity table.

Pass `-i on_duplicate=<mode>` and `-i do_validate=<boolean>` to control how an append run handles primary-key collisions and value validation:

* **`on_duplicate`** - Controls how the run handles an incoming row whose primary key already exists in the target table. Set it to `error` (the default) to fail the run when a key already exists, or `skip` to ignore existing keys and append only new keys. This input applies only when appending to an existing Dataset.
* **`do_validate`** - Controls whether the run validates incoming values against the Dataset schema, data types, codings, and within-file primary-key uniqueness before loading. It defaults to `true`.

| `on_duplicate` | `do_validate` | Incoming key already exists in target table                                    | New key repeats within incoming CSV                                                                                         |
| -------------- | ------------- | ------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------- |
| `error`        | `true`        | The run fails with error `2010`.                                               | The run fails duplicate-key validation (`2010`).                                                                            |
| `skip`         | `true`        | The run skips the row, leaves the existing row unchanged, and counts the skip. | The run fails validation (`2010`) for a repeated new key, and skips a repeated key that already exists in the target table. |
| `skip`         | `false`       | The run skips the row and counts the skip.                                     | The run does not check for duplicates within the file and can append the row twice.                                         |
| `error`        | `false`       | The run fails with error `2010`.                                               | The run does not check for duplicates within the file and can append the row twice.                                         |

When you re-upload a complete file or retry a batch, pass `-i on_duplicate=skip -i do_validate=true`. Reserve `do_validate=false` for trusted, pre-validated data.

For entities without a primary key, DML cannot identify duplicate rows and appends every incoming row on every run. Append mode cannot delete rows. To remove duplicates, re-ingest the affected table with `mode=overwrite`.

## Append coding entries

Codings are stored in the database's `dx_codings` table rather than in the Dataset descriptor. Before loading coded fields, the run reads existing codings from `dx_codings`, validates the union of existing and incoming entries, adds new `(coding_name, code)` pairs, and skips identical existing rows. Both a delta coding file (smaller to maintain) and a complete recurring coding file (safe when a pipeline exports the full coding table) are idempotent.

For example, to append a new `visit_status,3` coding pair using the standard coding CSV columns:

```csv
coding_name,code,meaning,parent_code,display_order,concept
visit_status,3,Resolved,,3,
```

Run `mode=append` with `codings_csv` (with or without `data_csvs`):

```shell
dx run data_model_loader_v2 \
  -i mode=append \
  -i output_dataset_name=phenotypic_data \
  -i dataset_id=record-xxxx \
  -i database_name=phenotypic_db \
  -i codings_csv=file-eeee
```

If an incoming row reuses an existing `(coding_name, code)` pair with a changed `meaning`, `parent_code`, `display_order`, or `concept`, the append fails with coding conflict error `166` (even when `do_validate=false`). An incoming `parent_code` can reference an existing code in `dx_codings` or another code in the same incoming file. Missing parents (`105`) and duplicate meanings within a coding name (`140`) fail validation before any new coding rows are written. To look up coding error codes and resolutions, see [Common Errors](https://documentation.dnanexus.com/developer/ingesting-data/data-model-loader/troubleshooting.md#common-errors).

## Review append results

### Check the append summary

An append returns the existing Dataset record, the database ID, and these append-specific outputs:

| Output           | Description                                                                                                                                                                            |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `append_summary` | JSON totals for `total_rows_appended`, `total_rows_skipped`, `total_codings_added`, `total_codings_skipped`, and `ingested_at`.                                                        |
| `append_report`  | A JSON file with the totals and the full per-table and per-coding breakdown (`per_table_appended`, nonzero `per_table_skipped`, `per_coding_added`, and nonzero `per_coding_skipped`). |

Each append also updates the dataset-level `lastIngestedAt` value in the database details, stored in epoch milliseconds. Cohort Browser displays it as **Last Ingested** in the **Dataset Info** panel, opened from the info icon in the dataset header, and uses it to refresh cached codings after an append.

### Query rows by append batch

DML writes the row-level `dx_ingested_at` value in epoch seconds to every row appended in a batch. It matches `ingested_at` in `append_summary`. Because `dx_ingested_at` has no Cohort Browser display folder (`folder_path`), it does not appear as a selectable field in the Cohort Browser UI.

To inspect row counts by append batch in [JupyterLab](https://documentation.dnanexus.com/user/jupyter-notebooks.md) or with [Spark SQL Runner](https://documentation.dnanexus.com/user/spark/example-applications/spark-sql-runner.md), query `<unique_database_name>.<entity_table>`. To find the unique database name (`database_xxxx__<database_name>`), run `dx describe <database_id>`:

```sql
SELECT dx_ingested_at, COUNT(*) AS rows_in_batch
FROM <unique_database_name>.<entity_table>
GROUP BY dx_ingested_at
ORDER BY dx_ingested_at;
```

To resolve append or permission errors, see [Troubleshooting](https://documentation.dnanexus.com/developer/ingesting-data/data-model-loader/troubleshooting.md#append-and-permission-errors).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://documentation.dnanexus.com/developer/ingesting-data/data-model-loader/appending-data-with-data-model-loader.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
