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.

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

community-domo-cli dataflows list

Get a Dataflow Definition

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

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

List Dataflow Executions

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

Check a Specific Execution

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

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

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

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

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

{
  "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:

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

Available Section Background Colors

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:

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

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:

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

Outputs Array

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

"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:

"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:

{
  "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": [{}]
}

LoadFromVault (Input Node)

Loads data from a Domo dataset into the dataflow.

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

{
  "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}
  ]
}

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:

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

{
  "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": [{}]
}

Aggregation Field Definition

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

Aggregation Types

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:

{
  "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:

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

{
  "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": [{}]
}

groupRules

Defines the PARTITION BY columns:

"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:

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

Multiple orderRules create a composite sort key.

additions (Window Function Definitions)

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

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

Window Operation Types

Ranking operations (type: "RANKING"):

Offset operations (type: "OFFSET"):

Example — LAG to get previous day's revenue:

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

Example — ROW_NUMBER to rank orders per customer:

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

{
  "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": [{}]
}

Filter Condition Fields

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:

{
  "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

rightValue Type Values

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.

{
  "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": [{}]
}

Expression Definition

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

Known Working Functions

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.

{
  "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": [{}]
}

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.

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

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

{
  "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": [{}]
}

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

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

Execution

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):

{
  "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:

{
  "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):

{
  "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):

{
  "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:

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:

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", "")}')

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:

{
  "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)