# Magic Etl CLI

> Magic ETL dataflows via community-domo-cli — list, get-definition, create, update, run, execution status; JSON DAG actions, transforms, joins. Use when automating dataflows with the community Domo CLI end-to-end. For REST/Java-CLI–first flows or mixed API patterns, use magic-etl instead.

- Skill: `stahura/magic-etl-cli` (Agent Skill)
- Install (CLI): `npx skillmds@latest add stahura/magic-etl-cli`
- Raw SKILL.md: https://api.skillmd.com/api/skills/stahura/magic-etl-cli/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: stahura (https://skillmd.com/u/stahura)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/stahura/magic-etl-cli

---


# Creating Magic ETL Dataflows Programmatically in Domo

## Overview

Magic ETL dataflows in Domo can be created, updated, and executed entirely through the `community-domo-cli`. The dataflow is defined as a JSON document describing a directed acyclic graph (DAG) of actions — input nodes, transforms, and output nodes.

**Use `community-domo-cli` for all dataflow operations.**

| Operation | CLI command |
|-----------|-------------|
| List dataflows | `community-domo-cli dataflows list` |
| Get full dataflow definition | `community-domo-cli dataflows get-definition <ID>` |
| Run a dataflow | `community-domo-cli -y dataflows run <ID>` |
| Check execution status | `community-domo-cli dataflows executions <ID> --limit 1` |
| **Create** a dataflow | `community-domo-cli -y dataflows create --body-file <FILE>` |
| **Update** a dataflow | `community-domo-cli -y dataflows update <ID> --body-file <FILE>` |
| **Rename/enable** | `community-domo-cli -y dataflows update <ID> --body-file <FILE>` |

**Important:** All operations use `community-domo-cli`, which handles auth automatically. The created dataflow is not in DRAFT state and can be executed immediately — no UI save required.

> **Mutating commands need `-y` (or `--yes`).** Create, update, and run prompt `Execute mutating action? [y/N]`. Without `-y`, the CLI aborts when stdin is not a TTY (scripts, Python `subprocess`, CI). Always pass `-y` for automation.

> **Never search for user IDs.** `responsibleUserId` is optional — omit it entirely. Never call `community-domo-cli users list` or any users endpoint to find a user ID for dataflow creation.

## CLI Commands for Dataflows

### List All Magic ETL Dataflows

```bash
community-domo-cli dataflows list
```

### Get a Dataflow Definition

```bash
community-domo-cli dataflows get-definition <DATAFLOW_ID>
```

Returns the full JSON definition including all actions, inputs, outputs, and GUI positions via `GET /dataprocessing/v1/dataflows/{id}`. This is the best way to understand the JSON structure — fetch an existing dataflow and study it. Always use this before issuing an update.

### Run a Dataflow

```bash
community-domo-cli -y dataflows run <DATAFLOW_ID>
```

### List Dataflow Executions

```bash
community-domo-cli dataflows executions <DATAFLOW_ID> --limit <LIMIT>
```

### Check a Specific Execution

```bash
community-domo-cli dataflows execution-get <DATAFLOW_ID> <EXECUTION_ID>
```

Returns execution state (`SUCCESS`, `FAILED_DATA_FLOW`, `CREATED`, `RUNNING`), row counts, errors, etc.

## Creating a Dataflow via the CLI

### Create

```bash
community-domo-cli -y dataflows create --body-file <FILE>
```

The body **must** include `"databaseType": "MAGIC"`. The created dataflow can be executed immediately — see Gotcha #7 and #11 for details.

### Updating an Existing Dataflow

```bash
community-domo-cli -y dataflows update <DATAFLOW_ID> --body-file <FILE>
```

Always fetch the full definition with `get-definition` first, modify it, then pass it via `--body-file`. Include the complete definition — PUT replaces the entire dataflow.

## Dataflow JSON Structure

### Top-Level Fields

```json
{
  "name": "My Magic ETL",
  "databaseType": "MAGIC",
  "responsibleUserId": 149955692,
  "draft": false,
  "enabled": true,
  "runState": "ENABLED",
  "engineProperties": {
    "kettle.mode": "STRICT"
  },
  "inputs": [ ... ],
  "outputs": [ ... ],
  "actions": [ ... ]
}
```

| Field | Description |
|---|---|
| `name` | Display name of the dataflow |
| `databaseType` | Must be `"MAGIC"` for Magic ETL |
| `responsibleUserId` | Numeric user ID of the owner |
| `draft` | `false` for a published dataflow |
| `enabled` | `true` to allow execution |
| `runState` | `"ENABLED"` to allow scheduled runs |
| `engineProperties` | `{"kettle.mode": "STRICT"}` is standard |
| `inputs` | Array of input dataset references |
| `outputs` | Array of output dataset references |
| `actions` | Array of action nodes (the DAG) |

> **Never search for user IDs.** `responsibleUserId` is optional — omit it entirely. Never call `community-domo-cli users list` or any users endpoint to find a user ID for dataflow creation.

### Top-Level `gui` Field (Canvas Layout & Sections)

The `gui` field controls the visual canvas layout, including colored **Section** zones that group related tiles. When `useGraphUI: true`, the canvas `elements` array controls tile positioning (overriding action-level `gui.x`/`gui.y`).

```json
{
  "gui": {
    "version": "1.0",
    "canvases": {
      "default": {
        "canvasSettings": {
          "coarserGrid": false,
          "hideCoarserGridPopUp": false,
          "backgroundVariant": "None"
        },
        "elements": [
          { "type": "Section", ... },
          { "type": "Tile", ... }
        ],
        "disabledActions": []
      }
    },
    "useGraphUI": true
  }
}
```

#### Section Elements (Colored Zone Backgrounds)

Sections are colored rectangular zones that visually group related tiles on the canvas:

```json
{
  "id": "a-unique-uuid",
  "type": "Section",
  "x": 56,
  "y": 72,
  "width": 1320,
  "height": 288,
  "name": "Work Orders Denormalization",
  "backgroundColor": "var(--colorChartBlue6)"
}
```

| Field | Description |
|---|---|
| `id` | Unique UUID for the section |
| `type` | Must be `"Section"` |
| `x`, `y` | Absolute position on the canvas (top-left corner) |
| `width`, `height` | Size of the colored zone in pixels |
| `name` | Label displayed in the section header |
| `backgroundColor` | CSS variable for the zone color (see table below) |

#### Available Section Background Colors

| CSS Variable | Color | Suggested Use |
|---|---|---|
| `var(--colorChartBlue6)` | Light blue | Input/staging pipelines |
| `var(--colorChartGreen6)` | Light green | Quality/validation pipelines |
| `var(--colorChartPurple6)` | Light purple | Output/publishing pipelines |
| `var(--colorChartOrange6)` | Light orange | Transform/enrichment pipelines |
| `var(--colorChartRed6)` | Light red | Filter/exclusion pipelines |
| `var(--colorChartYellow6)` | Light yellow | Shared/dimension tables |

The `6` suffix indicates the lightest shade — ideal for section backgrounds so tile labels remain readable.

#### Tile Elements (Action Positions)

Each action node has a corresponding `Tile` element in the canvas. Tiles can be **parented** inside a Section, in which case their `x`/`y` are **relative to the Section's position**:

```json
{
  "id": "LoadFromVault-work_orders",
  "type": "Tile",
  "x": 72,
  "y": 56,
  "parentId": "section-uuid-here",
  "color": null,
  "colorSource": null
}
```

| Field | Description |
|---|---|
| `id` | Must match the action's `id` field |
| `type` | Must be `"Tile"` |
| `x`, `y` | Position — **absolute** if no `parentId`, **relative to parent Section** if `parentId` is set |
| `parentId` | UUID of the parent Section element (omit for unparented tiles) |
| `color` | Optional integer color code for the tile icon |

**Reparenting formula:** When moving a tile into a section, convert absolute to relative coordinates:
- `relativeX = absoluteX - section.x`
- `relativeY = absoluteY - section.y`

Tiles without `parentId` render at absolute canvas coordinates and float outside any section.

#### ALWAYS Add Sections When Creating Dataflows

When building a dataflow with multiple logical branches or processing stages, **always add Section elements** to organize the visual layout. This is a best practice that makes dataflows immediately understandable. Group tiles by:
- **Pipeline branch** (e.g., each fact table's join chain gets its own section)
- **Processing stage** (e.g., "Input/Staging", "Transforms", "Output")
- **Shared resources** (e.g., dimension tables used across branches)

Assign a distinct color to each section for visual differentiation.

### Inputs Array

Each input references a Domo dataset that feeds into the dataflow:

```json
"inputs": [
  {
    "dataSourceId": "422efbf4-6c96-4576-907b-eacae8379d5d",
    "executeFlowWhenUpdated": false,
    "dataSourceName": "RAW | SFDC | Accounts",
    "onlyLoadNewVersions": false,
    "recentVersionCutoffMs": 0
  }
]
```

| Field | Description |
|---|---|
| `dataSourceId` | UUID of the input dataset |
| `dataSourceName` | Display name (for readability) |
| `executeFlowWhenUpdated` | `true` to auto-trigger the dataflow when this input updates |
| `onlyLoadNewVersions` | `true` to only process new data versions |

### Outputs Array

For a **new** dataflow where the output dataset doesn't exist yet:

```json
"outputs": [
  {
    "dataSourceId": null,
    "dataSourceName": "SFDC | Account Summary",
    "versionChainType": "REPLACE"
  }
]
```

Setting `dataSourceId` to `null` tells Domo to create a new dataset on first run. After the first successful run, Domo assigns a UUID.

For an **existing** output dataset:

```json
"outputs": [
  {
    "dataSourceId": "8fe448eb-231d-4ff5-95f1-86db64336e96",
    "dataSourceName": "SFDC | Account Opportunity Summary",
    "versionChainType": "REPLACE"
  }
]
```

`versionChainType`: `"REPLACE"` replaces all data each run. Other options include `"APPEND"`.

## Action Types

Actions are the nodes in the DAG. Each action has a unique `id`, a `type`, and references its upstream dependencies via `dependsOn`.

Valid types: `LoadFromVault`, `PublishToVault`, `MergeJoin`, `GroupBy`, `WindowAction`, `Filter`, `ExpressionEvaluator`, `SQL`

NOT valid (will fail with `DP-0069`): `SaveToVault`, `SelectValues`, `RenameColumns`

### Common Action Fields

Every action has these fields:

```json
{
  "type": "ActionType",
  "id": "unique-action-id",
  "name": "Human-readable name",
  "dependsOn": ["upstream-action-id-1", "upstream-action-id-2"],
  "settings": {
    "preferredDatabaseEntityType": "TEMP_VIEW"
  },
  "gui": {
    "x": 128,
    "y": 128,
    "color": null,
    "colorSource": null,
    "sampleJson": null
  },
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `id` | Unique identifier for this action node. Can be any string, but convention is `TypeName-uuid` |
| `dependsOn` | Array of action IDs that must complete before this one runs |
| `gui.x` / `gui.y` | Position in the visual canvas. When `useGraphUI: true`, the top-level `gui.canvases.default.elements` array takes precedence — set both to stay consistent |
| `settings.preferredDatabaseEntityType` | Always `"TEMP_VIEW"` |
| `tables` | Always `[{}]` |

### LoadFromVault (Input Node)

Loads data from a Domo dataset into the dataflow.

```json
{
  "type": "LoadFromVault",
  "id": "LoadFromVault-accounts",
  "name": "RAW | SFDC | Accounts",
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 128, "y": 128, "color": null, "colorSource": null, "sampleJson": null},
  "previewRowLimit": 10000,
  "propagateAi": false,
  "filterPolicy": "LEGACY",
  "dataSourceId": "422efbf4-6c96-4576-907b-eacae8379d5d",
  "sourceType": "AUTO",
  "executeFlowWhenUpdated": false,
  "pseudoDataSource": false,
  "truncateTextColumns": false,
  "truncateRows": false,
  "onlyLoadNewVersions": false,
  "recentVersionCutoffMs": 0,
  "tables": [{}]
}
```

Key fields:
- `dataSourceId`: UUID of the dataset to load
- `name`: Should match the dataset name for clarity
- No `dependsOn` — this is a root node

### MergeJoin (Join)

Joins two upstream actions on specified keys.

```json
{
  "type": "MergeJoin",
  "id": "MergeJoin-accounts-opps",
  "name": "Join Accounts & Opp Summary",
  "dependsOn": ["LoadFromVault-accounts", "GroupBy-opp-summary"],
  "disabled": false,
  "removeByDefault": false,
  "notes": [],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 576, "y": 320, "color": null, "colorSource": null, "sampleJson": null},
  "previewRowLimit": null,
  "joinType": "LEFT OUTER",
  "relationshipType": "MTM",
  "step1": "LoadFromVault-accounts",
  "step2": "GroupBy-opp-summary",
  "keys1": ["Id"],
  "keys2": ["AccountId"],
  "on": null,
  "schemaModification1": [],
  "schemaModification2": [
    {"name": "AccountId", "rename": "Opp_AccountId", "remove": true}
  ]
}
```

| Field | Description |
|---|---|
| `joinType` | `"LEFT OUTER"`, `"INNER"`, `"RIGHT OUTER"`, `"FULL OUTER"` |
| `relationshipType` | **`"MTM"`** — use this abbreviation, NOT `"MANY_TO_MANY"` (causes validation errors) |
| `step1` | Action ID for the left side of the join |
| `step2` | Action ID for the right side of the join |
| `keys1` | Array of column names from step1 to join on |
| `keys2` | Array of column names from step2 to join on (matched positionally with keys1) |
| `schemaModification1` | Rename or remove columns from step1 (left side) |
| `schemaModification2` | Rename or remove columns from step2 (right side) to avoid name collisions |
| `dependsOn` | Must include both `step1` and `step2` action IDs |

**Note:** Keys can be asymmetric — `keys1: ["fldWorkCodeID"]` / `keys2: ["ID"]`. Domo matches by array position, not by column name.

#### schemaModification1 / schemaModification2

When two datasets share column names (e.g., both have `Id`, `Name`, `Description`), use `schemaModification2` to rename or remove the conflicting columns from the right-side (step2) dataset:

```json
"schemaModification2": [
  {"name": "Description", "rename": "Opportunities.Description", "remove": false},
  {"name": "Id", "rename": "Opportunities.Id", "remove": false},
  {"name": "Name", "rename": "Opportunities.Name", "remove": false},
  {"name": "AccountId", "rename": "Opp_AccountId", "remove": true}
]
```

- `remove: true` — drops the column entirely from the output
- `remove: false` — keeps the column but renames it to the `rename` value

### GroupBy (Aggregation)

Aggregates data by grouping columns and applying aggregation functions.

```json
{
  "type": "GroupBy",
  "id": "GroupBy-opp-summary",
  "name": "Summarize Opportunities per Account",
  "dependsOn": ["LoadFromVault-opportunities"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 352, "y": 512, "color": null, "colorSource": null, "sampleJson": null},
  "input": "LoadFromVault-opportunities",
  "addLineNumber": false,
  "giveBackRow": false,
  "allRows": false,
  "groups": [
    {"name": "AccountId"}
  ],
  "fields": [
    {"name": "Opportunity Count", "source": "Id", "type": "COUNT_ALL", "valuefield": null, "expression": null, "settings": null},
    {"name": "Total Amount", "source": "Amount", "type": "SUM", "valuefield": null, "expression": null, "settings": null},
    {"name": "Avg Amount", "source": "Amount", "type": "AVERAGE", "valuefield": null, "expression": null, "settings": null},
    {"name": "Total Expected Revenue", "source": "ExpectedRevenue", "type": "SUM", "valuefield": null, "expression": null, "settings": null},
    {"name": "Avg Probability", "source": "Probability", "type": "AVERAGE", "valuefield": null, "expression": null, "settings": null}
  ],
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `input` | Action ID of the upstream data |
| `groups` | Array of columns to group by. Each entry: `{"name": "ColumnName"}` |
| `fields` | Array of aggregation definitions |

#### Aggregation Field Definition

```json
{
  "name": "Total Amount",
  "source": "Amount",
  "type": "SUM",
  "valuefield": null,
  "expression": null,
  "settings": null
}
```

| Field | Description |
|---|---|
| `name` | Output column name for the aggregated value |
| `source` | Source column name to aggregate |
| `type` | Aggregation function (see below) |

#### Aggregation Types

| Type | Description | Confirmed |
|---|---|---|
| `SUM` | Sum of values | Yes |
| `AVERAGE` | Mean of values | Yes |
| `COUNT_ALL` | Count of all rows (including nulls) | Yes |
| `COUNT_DISTINCT` | Count of distinct values | |
| `MIN` | Minimum value | Yes |
| `MAX` | Maximum value | |
| `FIRST` | First value in group | |
| `LAST` | Last value in group | |
| `CONCAT_COMMA` | Concatenate values with comma separator | |

**Important:** The API uses `MIN` and `MAX`, not `MINIMUM` or `MAXIMUM`. Using `MINIMUM` returns a validation error: `"The value is invalid for expected type... AggregateType"`. This was discovered through testing.

#### Expression-Based Aggregations (Advanced)

Instead of using `source` + `type`, you can use full SQL aggregation expressions. Set `source`, `type`, and `valuefield` to `null` and put the full expression in `expression`:

```json
{
  "name": "Total Hours",
  "source": null,
  "type": null,
  "valuefield": null,
  "expression": "SUM(IFNULL(`Hours`,0))",
  "settings": null
}
```

**CRITICAL:** The `expression` must be a **full SQL aggregation expression** — NOT just the function name.
- `"expression": "SUM(IFNULL(\`Hours\`,0))"` with `"source": null` — CORRECT
- `"expression": "SUM"` with `"valuefield": "Hours"` — WRONG (treats "SUM" as a column name)

This allows embedding complex logic like CASE WHEN directly in the GroupBy without needing a pre-GroupBy ExpressionEvaluator:

```json
{
  "name": "LTM_Revenue",
  "source": null, "type": null, "valuefield": null,
  "expression": "SUM(CASE WHEN `Date` >= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 MONTH) THEN IFNULL(`Revenue`,0) ELSE 0 END)",
  "settings": null
}
```

### WindowAction (Rank & Window)

Applies window functions — ranking, row numbering, lag/lead offsets — partitioned by group columns and ordered by sort columns. This is the programmatic equivalent of the "Rank & Window" tile in the Magic ETL UI.

```json
{
  "type": "WindowAction",
  "id": "WindowAction-first-order",
  "name": "First Order Date per Customer",
  "dependsOn": ["LoadFromVault-finance"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 352, "y": 128, "color": null, "colorSource": null, "sampleJson": null},
  "input": "LoadFromVault-finance",
  "groupRules": [
    {"column": "customer", "caseSensitive": false}
  ],
  "orderRules": [
    {"column": "date_ymd", "caseSensitive": false, "ascending": true}
  ],
  "additions": [
    {
      "name": "Customer Order Rank",
      "operation": {
        "type": "RANKING",
        "operationType": "ROW_NUMBER",
        "column": null,
        "defaultValue": null,
        "amount": null
      }
    }
  ],
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `input` | Action ID of the upstream data |
| `groupRules` | Partition columns — the window function is applied within each group |
| `orderRules` | Sort order within each partition |
| `additions` | Array of window function definitions to add as new columns |

#### groupRules

Defines the `PARTITION BY` columns:

```json
"groupRules": [
  {"column": "customer", "caseSensitive": false},
  {"column": "region", "caseSensitive": false}
]
```

Each entry partitions the data by that column. Multiple entries create a composite partition key.

#### orderRules

Defines the `ORDER BY` within each partition:

```json
"orderRules": [
  {"column": "date_ymd", "caseSensitive": false, "ascending": true}
]
```

| Field | Description |
|---|---|
| `column` | Column to sort by |
| `caseSensitive` | Whether string sorting is case-sensitive |
| `ascending` | `true` for ASC, `false` for DESC |

Multiple orderRules create a composite sort key.

#### additions (Window Function Definitions)

Each addition creates a new column with the result of a window function:

```json
{
  "name": "Output Column Name",
  "operation": {
    "type": "RANKING",
    "operationType": "ROW_NUMBER",
    "column": null,
    "defaultValue": null,
    "amount": null
  }
}
```

#### Window Operation Types

**Ranking operations** (`type: "RANKING"`):

| operationType | Description | column | amount |
|---|---|---|---|
| `ROW_NUMBER` | Sequential number within partition (1, 2, 3...) | `null` | `null` |
| `RANK` | Rank with gaps for ties (1, 2, 2, 4...) | `null` | `null` |
| `DENSE_RANK` | Rank without gaps for ties (1, 2, 2, 3...) | `null` | `null` |

**Offset operations** (`type: "OFFSET"`):

| operationType | Description | column | amount |
|---|---|---|---|
| `LAG` | Value from N rows before in the partition | Source column name | Number of rows to look back |
| `LEAD` | Value from N rows ahead in the partition | Source column name | Number of rows to look ahead |

Example — LAG to get previous day's revenue:

```json
{
  "name": "Previous Day Revenue",
  "operation": {
    "type": "OFFSET",
    "operationType": "LAG",
    "column": "revenue",
    "defaultValue": null,
    "amount": 1
  }
}
```

Example — ROW_NUMBER to rank orders per customer:

```json
{
  "name": "Customer Order Rank",
  "operation": {
    "type": "RANKING",
    "operationType": "ROW_NUMBER",
    "column": null,
    "defaultValue": null,
    "amount": null
  }
}
```

#### Combining WindowAction with GroupBy

A common pattern is to use `WindowAction` for row-level ranking, then `GroupBy` for aggregation. For example, to find the first order date per customer:

1. **WindowAction**: Partition by `customer`, order by `date_ymd` ASC, add `ROW_NUMBER`
2. **GroupBy**: Group by `customer`, use `MIN` on `date_ymd` to get the first order date, plus `SUM`/`AVERAGE` on numeric columns

The `WindowAction` passes through all original columns plus the new additions, so downstream actions have access to everything.

### Filter (Row Filtering)

Filters rows based on column conditions. This is the programmatic equivalent of the "Filter Rows" tile in the Magic ETL UI.

```json
{
  "type": "Filter",
  "id": "Filter-exclude-florida",
  "name": "Exclude Florida",
  "dependsOn": ["LoadFromVault-sales"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 352, "y": 256, "color": null, "colorSource": null, "sampleJson": null},
  "input": "LoadFromVault-sales",
  "filterList": [
    {
      "leftField": "state",
      "rightField": null,
      "rightValue": {"value": "Florida", "type": "STRING"},
      "rightExpr": "'Florida'",
      "operator": "NE",
      "expression": null,
      "andFilterList": []
    }
  ],
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `input` | Action ID of the upstream data |
| `filterList` | Array of filter conditions (combined with OR logic) |
| `andFilterList` | Nested array within a filter for AND logic |

#### Filter Condition Fields

| Field | Description |
|---|---|
| `leftField` | Column name to filter on |
| `operator` | Comparison operator (see table below) |
| `rightValue` | Typed value object: `{"value": "Florida", "type": "STRING"}` |
| `rightExpr` | String expression of the value, wrapped in single quotes for strings: `"'Florida'"` |
| `rightField` | Column name to compare against (for column-to-column comparisons, otherwise `null`) |

**Important:** For string comparisons, `rightValue` must be a typed object with `value` and `type` fields, and `rightExpr` must wrap the value in single quotes. Using a plain string for `rightValue` returns a `VALIDATION-DE` error.

#### Expression-Based Filters (Alternative)

Instead of using `leftField`/`operator`/`rightValue`, you can use a raw SQL `expression` for complex conditions:

```json
{
  "leftField": null,
  "rightField": null,
  "rightValue": null,
  "rightExpr": null,
  "operator": null,
  "expression": "IFNULL(TRIM(`column_name`),'') <> ''",
  "andFilterList": []
}
```

This is useful for multi-condition filters or filters with function calls. The expression is a SQL WHERE clause fragment.

#### Filter Operators

| Operator | Description | Confirmed |
|---|---|---|
| `NE` | Not equal | Yes |
| `EQ` | Equal | |
| `LT` | Less than | |
| `GT` | Greater than | |
| `LE` | Less than or equal | |
| `GE` | Greater than or equal | |
| `NN` | Is not null | Yes (from existing dataflow) |
| `NL` | Is null | |
| `NIN` | Not in list | |
| `IN` | In list | |

#### rightValue Type Values

| Type | Use For |
|---|---|
| `STRING` | Text comparisons |
| `NUMERIC` | Number comparisons |
| `DATE` | Date comparisons |

### ExpressionEvaluator (Add Formula)

Adds calculated columns using Domo's expression language. This is the programmatic equivalent of the "Add Formula" tile in the Magic ETL UI.

```json
{
  "type": "ExpressionEvaluator",
  "id": "ExpressionEvaluator-month-trunc",
  "name": "Add Month Column",
  "dependsOn": ["Filter-exclude-florida"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 480, "y": 256, "color": null, "colorSource": null, "sampleJson": null},
  "input": "Filter-exclude-florida",
  "expressions": [
    {
      "expression": "concat(year(`date_ymd`),MONTHNAME(`date_ymd`))",
      "fieldName": "Year_Month",
      "settings": {}
    }
  ],
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `input` | Action ID of the upstream data |
| `expressions` | Array of formula definitions |

#### Expression Definition

```json
{
  "expression": "concat(year(`date_ymd`),MONTHNAME(`date_ymd`))",
  "fieldName": "Year_Month",
  "settings": {}
}
```

| Field | Description |
|---|---|
| `expression` | Domo formula expression. Column names must be wrapped in backticks. |
| `fieldName` | Name of the new output column |
| `settings` | Empty object `{}` |

#### Known Working Functions

| Function | Description | Example |
|---|---|---|
| `year()` | Extract year from date | `year(\`date_ymd\`)` |
| `MONTH()` | Extract month number (1-12) | `MONTH(\`date_ymd\`)` |
| `MONTHNAME()` | Extract month name from date | `MONTHNAME(\`date_ymd\`)` |
| `concat()` | Concatenate strings | `concat(year(\`date_ymd\`), MONTHNAME(\`date_ymd\`))` |
| `DATE()` | Convert to date type | `DATE(\`date_ymd\`)` |
| `DATE_FORMAT()` | Format date as string | `DATE_FORMAT(\`date_ymd\`, 'yyyy-MM-dd')` |
| `LEFT()` | Left substring | `LEFT(\`Date\`, 7)` for year-month from "2025-01-15" |
| `LPAD()` | Left-pad string | `LPAD(MONTH(\`date\`), 2, '0')` |
| `IFNULL()` | Null replacement (2 args only) | `IFNULL(\`col\`, 0)` |
| `COALESCE()` | Null replacement (multi-arg) | `COALESCE(\`a\`, \`b\`, 'default')` |
| `TRIM()` | Remove whitespace | `TRIM(\`col\`)` |
| `CAST()` | Type casting | `CAST(NULL AS DATETIME)` |
| `LAST_DAY()` | Last day of month | `LAST_DAY(\`date\`)` |
| `DATE_SUB()` | Subtract interval | `DATE_SUB(\`date\`, INTERVAL 12 MONTH)` |
| `CONVERT_TZ()` | Timezone conversion | `CONVERT_TZ(CURRENT_TIMESTAMP(),'UTC','US/Eastern')` |
| `CURRENT_TIMESTAMP()` | Current datetime | `CURRENT_TIMESTAMP()` |

**Important:** `TRUNC_MONTH()` is NOT a valid function and returns `"Unknown function: TRUNC_MONTH"`. To truncate dates to month level, use `concat(year(...), MONTHNAME(...))` or similar string-based approaches.

#### Expression Behavior Rules

- Expressions are processed **in order** — later expressions CAN reference fields computed by earlier ones in the same tile
- Adding an expression with the same `fieldName` as an existing column **replaces** that column's value (useful for type casting or reformatting)
- `IFNULL()` takes exactly 2 args; use `COALESCE(a, b, c)` for multi-arg null handling
- `CAST(NULL AS DATETIME)` works for creating null typed columns

Multiple expressions can be added in a single ExpressionEvaluator action — each creates a new column (or replaces an existing one). All original columns are passed through.

### Unique (Deduplicate)

Removes duplicate rows based on specified key columns. This is the programmatic equivalent of the "Remove Duplicates" tile.

```json
{
  "type": "Unique",
  "id": "Unique-dedup",
  "name": "Deduplicate",
  "dependsOn": ["SelectValues-final"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 800, "y": 256, "color": null, "colorSource": null, "sampleJson": null},
  "input": "SelectValues-final",
  "countRows": false,
  "fields": [
    {"name": "ColA", "caseInsensitive": false},
    {"name": "ColB", "caseInsensitive": false}
  ],
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `input` | Action ID of the upstream data |
| `countRows` | `true` to add a count column showing how many duplicates were found |
| `fields` | Array of columns that define uniqueness. Rows with identical values across all listed columns are deduplicated |
| `fields[].caseInsensitive` | `true` for case-insensitive string matching |

**Tip:** Add a Unique tile after joins that might fan out rows (e.g., many-to-many joins).

### SelectValues (Legacy / Not Valid for New Flows)

Selects specific columns and optionally renames them.

```json
{
  "type": "SelectValues",
  "id": "SelectValues-final",
  "name": "Select Final Columns",
  "dependsOn": ["MergeJoin-final"],
  "settings": {"preferredDatabaseEntityType": "TEMP_VIEW"},
  "gui": {"x": 1024, "y": 320, "color": null, "colorSource": null, "sampleJson": null},
  "input": "MergeJoin-final",
  "select": [
    {"name": "Id", "rename": "Account ID"},
    {"name": "Name", "rename": "Account Name"},
    {"name": "Industry"},
    {"name": "Contact Count"},
    {"name": "Total Amount"}
  ],
  "tables": [{}]
}
```

- Only columns listed in `select` are passed through; all others are dropped
- Omit `rename` to keep the original column name

**Alternative format** — uses `fields` instead of `select`. **Only list columns you want to KEEP** (with optional rename). Unlisted columns are dropped. Do NOT add `remove: true` entries — they cause `DP-0003` validation errors.

**Only list columns you want to KEEP** (with optional rename). Unlisted columns are dropped. Do NOT add `remove: true` entries — they cause `DP-0003` validation errors.

```json
"fields": [
  {"name": "ID", "rename": "Dataflow ID"},
  {"name": "Display Name", "rename": "Dataflow Name"},
  {"name": "Link", "rename": "Dataflow Link"}
]
```

The minimal field entry is just `{"name": "...", "rename": "..."}`. Do NOT include `type`, `dateFormat`, `settings`, or `remove` — these cause `DP-0003 Action is improperly configured` errors on the fields format.

### PublishToVault (Output Node)

Writes the final data to a Domo dataset.

```json
{
  "type": "PublishToVault",
  "id": "PublishToVault-my-output",
  "name": "Output Dataset Name",
  "dependsOn": ["last-action-id"],
  "settings": {"preferredDatabaseEntityType": "DYNAMIC_TABLE"},
  "inputs": ["last-action-id"],
  "dataSource": {
    "guid": null,
    "type": "DataFlow",
    "name": "Output Dataset Name",
    "description": "",
    "cloudId": null
  },
  "versionChainType": "REPLACE",
  "schemaSource": "DATAFLOW",
  "partitioned": false,
  "tables": [{}]
}
```

| Field | Description |
|---|---|
| `dependsOn` | Array with the final transform action ID |
| `inputs` | Must include the same final transform action ID |
| `dataSource.type` | `"DataFlow"` |
| `dataSource.name` | Name for the output dataset |
| `dataSource.guid` | `null` for new outputs, existing UUID for existing output dataset |
| `dataSource.cloudId` | `null` for standard dataflow-managed output |
| `versionChainType` | `"REPLACE"` (full replace) or `"APPEND"` |
| `schemaSource` | `"DATAFLOW"` |
| `partitioned` | `false` |
| `tables` | `[{}]` |

For new dataflows, omit `guid` entirely. Domo assigns a real output dataset UUID on the first successful run.

## Complete Example: Account Summary ETL

This example creates a dataflow that:
1. Loads 3 Salesforce datasets (Accounts, Contacts, Opportunities)
2. Aggregates contacts per account (count)
3. Aggregates opportunities per account (count, total amount, avg amount, expected revenue, avg probability)
4. Left joins both aggregations to the accounts table
5. Outputs one row per account with all summary metrics

### Pipeline Diagram

```
LoadFromVault(Accounts) ─────────────────────┐
                                              ├─ MergeJoin(LEFT) ──┐
LoadFromVault(Opportunities)                  │   on Id=AccountId   │
  └─ GroupBy(AccountId) ─────────────────────┘                     │
       COUNT(Id) → Opportunity Count                               ├─ MergeJoin(LEFT) ─── PublishToVault
       SUM(Amount) → Total Amount                                  │   on Id=AccountId
       AVG(Amount) → Avg Amount                                    │
       SUM(ExpectedRevenue) → Total Expected Revenue               │
       AVG(Probability) → Avg Probability                          │
                                                                   │
LoadFromVault(Contacts)                                            │
  └─ GroupBy(AccountId) ──────────────────────────────────────────┘
       COUNT(Id) → Contact Count
```

### Creation Command

```bash
community-domo-cli -y dataflows create --body-file sfdc_account_summary_etl.json
```

### Execution

```bash
community-domo-cli -y dataflows run <DATAFLOW_ID>
```

## Complete Example: Customer Summary with Rank & Window

This example creates a dataflow that:
1. Loads a finance dataset (329,460 rows with customer, date, sales, revenue, cogs)
2. Uses a WindowAction to rank orders per customer by date (ROW_NUMBER)
3. Aggregates per customer: first order date (MIN), SUM and AVERAGE for sales/revenue/cogs, order count

### Pipeline Diagram

```
LoadFromVault(Mododata | Finance)  329,460 rows
  └── WindowAction: ROW_NUMBER partitioned by customer, ordered by date_ymd ASC
       └── GroupBy(customer):
            MIN(date_ymd)  → First Order Date
            SUM(sales)     → Total Sales
            AVG(sales)     → Avg Sales
            SUM(revenue)   → Total Revenue
            AVG(revenue)   → Avg Revenue
            SUM(cogs)      → Total COGS
            AVG(cogs)      → Avg COGS
            COUNT(date_ymd)→ Order Count
              └── PublishToVault  →  104 customer rows
```

### Key JSON Snippets

WindowAction (Rank & Window tile):
```json
{
  "type": "WindowAction",
  "id": "WindowAction-first-order",
  "name": "First Order Date per Customer",
  "dependsOn": ["LoadFromVault-finance"],
  "input": "LoadFromVault-finance",
  "groupRules": [
    {"column": "customer", "caseSensitive": false}
  ],
  "orderRules": [
    {"column": "date_ymd", "caseSensitive": false, "ascending": true}
  ],
  "additions": [
    {
      "name": "Customer Order Rank",
      "operation": {
        "type": "RANKING",
        "operationType": "ROW_NUMBER",
        "column": null,
        "defaultValue": null,
        "amount": null
      }
    }
  ]
}
```

GroupBy with MIN for first order date:
```json
{
  "type": "GroupBy",
  "id": "GroupBy-customer-summary",
  "name": "Customer Aggregations",
  "dependsOn": ["WindowAction-first-order"],
  "input": "WindowAction-first-order",
  "groups": [{"name": "customer"}],
  "fields": [
    {"name": "First Order Date", "source": "date_ymd", "type": "MIN"},
    {"name": "Total Sales", "source": "sales", "type": "SUM"},
    {"name": "Avg Sales", "source": "sales", "type": "AVERAGE"},
    {"name": "Total Revenue", "source": "revenue", "type": "SUM"},
    {"name": "Avg Revenue", "source": "revenue", "type": "AVERAGE"},
    {"name": "Total COGS", "source": "cogs", "type": "SUM"},
    {"name": "Avg COGS", "source": "cogs", "type": "AVERAGE"},
    {"name": "Order Count", "source": "date_ymd", "type": "COUNT_ALL"}
  ]
}
```

### Result

Output dataset: 104 rows (one per customer) with columns: `customer`, `First Order Date`, `Total Sales`, `Avg Sales`, `Total Revenue`, `Avg Revenue`, `Total COGS`, `Avg COGS`, `Order Count`.

## Complete Example: Monthly Dept Metrics with Filter & Formula

This example creates a dataflow that:
1. Loads a sales dataset (481,643 rows)
2. Filters out rows where state = 'Florida'
3. Adds a `Year_Month` column using `concat(year(), MONTHNAME())`
4. Aggregates by Year_Month + department + metric_name: SUM and AVERAGE for revenue, store_revenue, web_revenue, mobile_revenue, total_costs, visits

### Pipeline Diagram

```
LoadFromVault(Mododata | Sales)  481,643 rows
  └── Filter: state != 'Florida'
       └── ExpressionEvaluator: Year_Month = concat(year(date_ymd), MONTHNAME(date_ymd))
            └── GroupBy(Year_Month, department, metric_name):
                 SUM/AVG for revenue, store_revenue, web_revenue,
                 mobile_revenue, total_costs, visits
                   └── PublishToVault  →  14,057 rows
```

### Key JSON Snippets

Filter (exclude a specific value):
```json
{
  "type": "Filter",
  "id": "Filter-exclude-florida",
  "name": "Exclude Florida",
  "dependsOn": ["LoadFromVault-sales"],
  "input": "LoadFromVault-sales",
  "filterList": [
    {
      "leftField": "state",
      "rightField": null,
      "rightValue": {"value": "Florida", "type": "STRING"},
      "rightExpr": "'Florida'",
      "operator": "NE",
      "expression": null,
      "andFilterList": []
    }
  ]
}
```

ExpressionEvaluator (date to year-month string):
```json
{
  "type": "ExpressionEvaluator",
  "id": "ExpressionEvaluator-month-trunc",
  "name": "Add Month Column",
  "dependsOn": ["Filter-exclude-florida"],
  "input": "Filter-exclude-florida",
  "expressions": [
    {
      "expression": "concat(year(`date_ymd`),MONTHNAME(`date_ymd`))",
      "fieldName": "Year_Month",
      "settings": {}
    }
  ]
}
```

### Result

Output: 14,057 rows (1 row per month x department x metric_name, excluding Florida). Granularity reduced from 481K daily rows to 14K monthly aggregated rows.

## ETL Error Codes Reference

When a dataflow execution fails, the error object in `lastExecution.errors[]` contains a `code` field. Use this table to diagnose:

| Code | Category | Meaning | Typical Fix |
|------|----------|---------|-------------|
| `DP-DSNF` | Missing input | Required dataset does not exist | Recreate dataset or remove the LoadFromVault tile |
| `DP-61100` | Missing input | Same as DP-DSNF (alternate code) | Same as above |
| `DP-0001` | Schema mismatch | Column referenced but not found | Upstream schema changed — update column references |
| `DP-0059` | Formula error | Syntax error in expression | Fix formula (e.g., missing `END` on CASE statement) |
| `DP-0118` | Code/model crash | Transform job failure | Check Python script or AI model tile |
| `DP-TGTFR` | Data volume | Batch Text Generation needs >= 100 rows | Ensure input has enough rows |
| `MYSQL-601072` | Join key missing | Key column doesn't exist in table | Update join key column names |
| `DP-0000` | Unknown | No API-level details | Inspect in Domo UI |

### Tracing Errors to Specific Tiles

Error objects contain a `parameters` map with `_actionId` (UUID of the failing tile). Match it against `actions[].id` in the dataflow:

```python
errors = dataflow.get("lastExecution", {}).get("errors", [])
actions_by_id = {a["id"]: a for a in dataflow.get("actions", [])}
for e in errors:
    action_id = e.get("parameters", {}).get("_actionId", "")
    tile = actions_by_id.get(action_id, {})
    print(f'Error: [{e["code"]}] {e["localizedMessage"]}')
    print(f'Tile: "{tile.get("name", "?")}" (type: {tile.get("type", "?")})')
    print(f'Property: {e.get("parameters", {}).get("_propertyPath", "")}')
```

| `_actionId` tile type | What to inspect |
|---|---|
| `ExpressionEvaluator` | `expressions[].expression` — the formula text |
| `LoadFromVault` | `dataSourceId` — the input dataset UUID |
| `MergeJoin` | `keys1` / `keys2` — join key column names |
| `SelectValues` | `fields[].name` — column names being selected |
| `Filter` | `filterList[].leftField` — filter column |
| `PublishToVault` | `dataSource.guid` — output dataset UUID |

## Common Pitfalls

### 1. `DP-DSCF`/`DP-0069` Failures — Wrong Action Type or PublishToVault Structure

`DP-0069` means an illegal action type was used (for example `SaveToVault`, `SelectValues`, `RenameColumns`). `DP-DSCF` indicates output commit failure and is commonly tied to malformed `PublishToVault` payloads.

**Root causes:** unsupported action type or incomplete/incorrect `PublishToVault` shape.

**Fix:** use supported action types and use this `PublishToVault` shape:

```json
{
  "type": "PublishToVault",
  "id": "PublishToVault-my-output",
  "name": "Output Dataset Name",
  "dependsOn": ["last-action-id"],
  "settings": {"preferredDatabaseEntityType": "DYNAMIC_TABLE"},
  "inputs": ["last-action-id"],
  "dataSource": {"guid": null, "type": "DataFlow", "name": "Output Dataset Name", "description": "", "cloudId": null},
  "versi

…(truncated)