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

# Building a Synthetic Dataset with a Workflow

> Create a dataset, fill it with generated fake rows, sample it, and map it to Rosetta Stone in one workflow that leaves the same data behind on every run

A [synthetic dataset](https://docs.narrative.io/reference/glossary.md#synthetic-dataset) is a dataset filled with generated rows that stand in for real records. This guide shows how to build one with a single [workflow](https://docs.narrative.io/concepts/workflows/workflow-orchestration.md). The workflow creates the dataset, fills it with 500 fake customers of a fictional retailer, Summit & Pine Outfitters, takes a sample, and maps the columns to [Rosetta Stone](https://docs.narrative.io/concepts/rosetta-stone/how-it-works.md). Every run leaves the same rows behind.

Use this pattern when you need data that looks real without touching real data, such as:

* A demo of a product flow, so the audience sees mapped, queryable data
* A partner trial, before the partner has shared any data of their own
* A test of an audience builder, a connector, or a downstream workflow

## Prerequisites

* An API key with `read` and `write` access to `workflows`, or the [Data Collaboration MCP Server](https://docs.narrative.io/guides/mcp/connecting-an-ai-assistant.md) connected to your AI assistant
* The ID of the [data plane](https://docs.narrative.io/reference/glossary.md#data-plane) the dataset belongs on, and the ID of a [compute pool](https://docs.narrative.io/reference/glossary.md#compute-pool) on that data plane
* Familiarity with the workflow format in [Automating Multi-Step Pipelines](https://docs.narrative.io/guides/workflows/workflow-orchestration.md)

## How the rows are generated

The generator is a single NQL `SELECT` that reads no source table. It builds a sequence of row numbers, then works out every column from the row number.

### A sequence with no source table

Three `VALUES` lists of digits, joined with `CROSS JOIN`, produce every combination of a units digit, a tens digit, and a hundreds digit: 10 × 10 × 5 = 500 rows. The expression `h.d * 100 + t.d * 10 + u.d + 1` turns each combination into a row number `n` from 1 to 500:

```sql theme={null}
SELECT h.d * 100 + t.d * 10 + u.d + 1 AS n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS u(d)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS t(d)
CROSS JOIN (VALUES (0), (1), (2), (3), (4)) AS h(d)
```

To change the row count, change the hundreds list. `(0), (1)` gives 200 rows, and adding a fourth list of digits gives thousands.

### Columns worked out from the row number

Each column is a function of `n`, built from `MOD`, `CASE`, and `LPAD`:

* `LPAD(CAST(n AS VARCHAR), 4, '0')` gives a zero-padded key such as `0042`, which feeds the customer ID and the email address.
* `MOD(n - 1, 5)` picks one of five places, and a `CASE` turns the place into a state, a ZIP code prefix, and an area code.
* `MOD(n * 61, 100)` gives a roll from 0 to 99 that spreads customers across loyalty tiers: 45% bronze, 30% silver, 17% gold, 8% platinum.
* `MOD(n * 97, 540)` spreads purchase dates over the 540 days before a fixed date.

Nothing in the query is random, and the dates count back from a fixed timestamp rather than `CURRENT_TIMESTAMP`. A rerun therefore produces the same rows. The only column that changes is `_nio_last_modified_at`, which Narrative sets on every write.

### Identifiers that are fake by construction

Every identifier comes from a range reserved for fiction, so none of them belongs to a real person:

| Identifier | Generated value | Why it's fake |
| - | - | - |
| Email | `customer0001@example.com` to `customer0500@example.com` | `example.com` is reserved for documentation and can't receive mail |
| Phone | `+14155550100` to `+12065550199` | 555-0100 through 555-0199 is the range North American numbering reserves for fiction |

The generator hashes both with `SHA2(..., 256)` in NQL, so the dataset holds the same shape of identifier a real customer file would. The hash has to be taken over the form the Rosetta Stone attribute expects, or it won't join:

| Column | Attribute | Input to the hash |
| - | - | - |
| `email_sha256` | `sha256_hashed_email` | The normalized email: lowercase, no surrounding whitespace. The generated addresses are already in that form. |
| `phone_sha256` | `sha256_hashed_phone_number` | The number in E.164 format, including the leading `+`, such as `+14155550100` |

The phone hash in particular is easy to get wrong. A hash of `14155550100` (no `+`) or `4155550100` (no country code) belongs to a different attribute and won't join against `sha256_hashed_phone_number`. For the normalization rules in general, see [Hashing PII for Upload](https://docs.narrative.io/guides/ingestion/hashing-pii.md).

## Step 1: Look up the attribute IDs

Mappings name attributes by ID. Look each ID up with `GET /attributes` rather than copying it from this page:

```bash theme={null}
curl "https://api.narrative.io/attributes?q=sha256_hashed_phone_number" \
  -H "Authorization: Bearer $NIO_API_TOKEN"
```

Repeat for each attribute you map. Check the attribute's description before you use it: it says what input the attribute expects. This guide maps these attributes:

| Column | Attribute name | ID in the example |
| - | - | - |
| `email_sha256` | `sha256_hashed_email` | `76` |
| `phone_sha256` | `sha256_hashed_phone_number` | `259` |
| `country` | `iso_3166_1_country` | `4` |
| `state` | `state` | `297` |
| `postal_code` | `postal_code` | `298` |
| `age` | `age` | `8` |

See [`GET /attributes`](https://docs.narrative.io/api-reference/attributes/get-attributes) for the other search parameters.

## Step 2: Validate the generator query

Validate the `SELECT` on its own before you put it in a workflow. Validation parses and type-checks the query without reading data or starting a job, so a typo shows up in seconds, not halfway through a workflow run.

Send the query to `POST /nql/validate`, or ask your AI assistant to run [`narrative_nql_validate`](https://docs.narrative.io/reference/integrations/mcp-server#narrative_nql_validate) on it:

```sql theme={null}
SELECT
  CONCAT('c', base.pad) AS customer_id,
  SHA2(CONCAT('customer', base.pad, '@example.com'), 256) AS email_sha256,
  SHA2(CONCAT('+1',
    CASE base.place WHEN 0 THEN '415' WHEN 1 THEN '212' WHEN 2 THEN '512' WHEN 3 THEN '312' ELSE '206' END,
    '55501', LPAD(CAST(CAST(FLOOR((base.n - 1) / 5) AS INTEGER) AS VARCHAR), 2, '0')), 256) AS phone_sha256,
  CASE MOD(base.n * 7, 4) WHEN 0 THEN 'Avery' WHEN 1 THEN 'Blake' WHEN 2 THEN 'Casey' ELSE 'Dana' END AS first_name,
  CASE MOD(base.n * 11, 3) WHEN 0 THEN 'Alder' WHEN 1 THEN 'Birch' ELSE 'Cedar' END AS last_name,
  'US' AS country,
  CASE base.place WHEN 0 THEN 'CA' WHEN 1 THEN 'NY' WHEN 2 THEN 'TX' WHEN 3 THEN 'IL' ELSE 'WA' END AS state,
  CONCAT(
    CASE base.place WHEN 0 THEN '941' WHEN 1 THEN '100' WHEN 2 THEN '787' WHEN 3 THEN '606' ELSE '981' END,
    LPAD(CAST(MOD(base.n * 29, 100) AS VARCHAR), 2, '0')) AS postal_code,
  19 + MOD(base.n * 37, 60) AS age,
  CASE WHEN base.roll < 45 THEN 'bronze' WHEN base.roll < 75 THEN 'silver' WHEN base.roll < 92 THEN 'gold' ELSE 'platinum' END AS loyalty_tier,
  TIMESTAMPADD(DAY, -(1 + MOD(base.n * 97, 540)), TIMESTAMP '2026-11-18 09:00:00') AS last_purchase_at
FROM (
  SELECT
    seq.n,
    LPAD(CAST(seq.n AS VARCHAR), 4, '0') AS pad,
    MOD(seq.n - 1, 5) AS place,
    MOD(seq.n * 61, 100) AS roll
  FROM (
    SELECT h.d * 100 + t.d * 10 + u.d + 1 AS n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS u(d)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS t(d)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4)) AS h(d)
  ) AS seq
) AS base
```

The five area codes and the 100 numbers from `0100` to `0199` give 500 distinct phone numbers, one for each row. If you add rows, add area codes and widen `MOD(seq.n - 1, 5)` and `FLOOR((base.n - 1) / 5)` to match, or the phone numbers repeat.

## Step 3: Write the workflow

The workflow runs five tasks in order:

| Task | Call | What it does |
| - | - | - |
| `createCrm` | [`CreateDatasetIfNotExists`](https://docs.narrative.io/reference/workflows/tasks#createdatasetifnotexists) | Creates the empty dataset from an explicit schema. On a rerun, it returns the existing dataset unchanged. |
| `clearCrm` | [`ExecuteDml`](https://docs.narrative.io/reference/workflows/tasks#executedml) | Deletes the rows a previous run wrote |
| `fillCrm` | `ExecuteDml` | Inserts the 500 generated rows with `INSERT INTO … SELECT` |
| `sampleCrm` | [`CreateDatasetSample`](https://docs.narrative.io/reference/workflows/tasks#createdatasetsample) | Captures a sample of the new rows |
| `mapCrm` | [`CreateRosettaStoneMappingsIfNotExist`](https://docs.narrative.io/reference/workflows/tasks#createrosettastonemappingsifnotexist) | Maps the hashed identifiers and the location and age columns to Rosetta Stone attributes |

Save this as `demo-sample-crm.yaml`, replacing `<DATA_PLANE_ID>` and `<COMPUTE_POOL_ID>` with your own IDs:

```yaml theme={null}
document:
  dsl: '1.0.0'
  namespace: demo
  name: demo-sample-crm
  version: '1.0.0'
do:
  - createCrm:
      call: CreateDatasetIfNotExists
      with:
        datasetName: demo_crm_customers
        displayName: 'Sample CRM: Summit & Pine Outfitters customers'
        description: '500 fake customers of a fictional retailer. Emails use example.com and phones use 555-01xx, so they match nobody.'
        tags: [sample_data]
        dataPlaneId: <DATA_PLANE_ID>
        computePoolId: <COMPUTE_POOL_ID>
        schema:
          type: object
          required: [customer_id, email_sha256, phone_sha256, country, state, postal_code]
          properties:
            customer_id: { type: string }
            email_sha256: { type: string }
            phone_sha256: { type: string }
            first_name: { type: string }
            last_name: { type: string }
            country: { type: string }
            state: { type: string }
            postal_code: { type: string }
            age: { type: long }
            loyalty_tier: { type: string }
            last_purchase_at: { type: timestamptz }
          file_config: { type: json }
  - clearCrm:
      call: ExecuteDml
      with:
        computePoolId: <COMPUTE_POOL_ID>
        nql: |
          DELETE FROM company_data.demo_crm_customers
          WHERE company_data.demo_crm_customers.customer_id LIKE 'c%'
  - fillCrm:
      call: ExecuteDml
      with:
        computePoolId: <COMPUTE_POOL_ID>
        nql: |
          INSERT INTO company_data.demo_crm_customers (customer_id, email_sha256, phone_sha256, first_name, last_name,
            country, state, postal_code, age, loyalty_tier, last_purchase_at)
          SELECT
            CONCAT('c', base.pad) AS customer_id,
            SHA2(CONCAT('customer', base.pad, '@example.com'), 256) AS email_sha256,
            SHA2(CONCAT('+1',
              CASE base.place WHEN 0 THEN '415' WHEN 1 THEN '212' WHEN 2 THEN '512' WHEN 3 THEN '312' ELSE '206' END,
              '55501', LPAD(CAST(CAST(FLOOR((base.n - 1) / 5) AS INTEGER) AS VARCHAR), 2, '0')), 256) AS phone_sha256,
            CASE MOD(base.n * 7, 4) WHEN 0 THEN 'Avery' WHEN 1 THEN 'Blake' WHEN 2 THEN 'Casey' ELSE 'Dana' END AS first_name,
            CASE MOD(base.n * 11, 3) WHEN 0 THEN 'Alder' WHEN 1 THEN 'Birch' ELSE 'Cedar' END AS last_name,
            'US' AS country,
            CASE base.place WHEN 0 THEN 'CA' WHEN 1 THEN 'NY' WHEN 2 THEN 'TX' WHEN 3 THEN 'IL' ELSE 'WA' END AS state,
            CONCAT(
              CASE base.place WHEN 0 THEN '941' WHEN 1 THEN '100' WHEN 2 THEN '787' WHEN 3 THEN '606' ELSE '981' END,
              LPAD(CAST(MOD(base.n * 29, 100) AS VARCHAR), 2, '0')) AS postal_code,
            19 + MOD(base.n * 37, 60) AS age,
            CASE WHEN base.roll < 45 THEN 'bronze' WHEN base.roll < 75 THEN 'silver' WHEN base.roll < 92 THEN 'gold' ELSE 'platinum' END AS loyalty_tier,
            TIMESTAMPADD(DAY, -(1 + MOD(base.n * 97, 540)), TIMESTAMP '2026-11-18 09:00:00') AS last_purchase_at
          FROM (
            SELECT
              seq.n,
              LPAD(CAST(seq.n AS VARCHAR), 4, '0') AS pad,
              MOD(seq.n - 1, 5) AS place,
              MOD(seq.n * 61, 100) AS roll
            FROM (
              SELECT h.d * 100 + t.d * 10 + u.d + 1 AS n
              FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS u(d)
              CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS t(d)
              CROSS JOIN (VALUES (0), (1), (2), (3), (4)) AS h(d)
            ) AS seq
          ) AS base
  - sampleCrm:
      call: CreateDatasetSample
      with:
        datasetName: demo_crm_customers
        computePoolId: <COMPUTE_POOL_ID>
  - mapCrm:
      call: CreateRosettaStoneMappingsIfNotExist
      with:
        datasetName: demo_crm_customers
        mappings:
          - attributeId: 76
            mapping:
              type: object_mapping
              propertyMappings:
                - path: value
                  expression: email_sha256
          - attributeId: 259
            mapping:
              type: object_mapping
              propertyMappings:
                - path: value
                  expression: phone_sha256
          - attributeId: 4
            mapping:
              type: value_mapping
              expression: country
          - attributeId: 297
            mapping:
              type: value_mapping
              expression: state
          - attributeId: 298
            mapping:
              type: value_mapping
              expression: postal_code
          - attributeId: 8
            mapping:
              type: value_mapping
              expression: age
```

### Why the tasks run in this order

**The clear step comes before the insert.** `INSERT` appends, so without `clearCrm` a second run would leave 1,000 rows, and a third would leave 1,500. With the clear step first, every run leaves the same 500 rows.

**The delete is keyed on a prefix.** NQL rejects a `DELETE` that has no `WHERE` clause, with the error `DELETE can not be used without a WHERE condition`. Every generated `customer_id` starts with `c`, so `LIKE 'c%'` matches every row the generator wrote. Pick a prefix that no row you want to keep would share.

**The sample comes before the mappings.** A new mapping is checked against the dataset's sample, and without one the mapping fails with `no sample available`. See [Mapping Schemas](https://docs.narrative.io/guides/rosetta-stone/mapping-schemas.md). `sampleCrm` captures the freshly inserted rows, so `mapCrm` has rows to check its mappings against.

**The mapping task tolerates reruns.** When a mapping already exists, `CreateRosettaStoneMappingsIfNotExist` reports it under `conflictMappings` and leaves it unchanged. It doesn't fail the task.

Each hashed identifier maps to the `value` property of an object attribute, and the mappings set nothing else. Rosetta Stone derives a `hashed_email` mapping from `sha256_hashed_email` on its own, with `type` filled in. For when to map to an attribute that derives others instead of to a derived one, see [Mapping Schemas](https://docs.narrative.io/guides/rosetta-stone/mapping-schemas.md).

## Step 4: Build a second dataset from the first

Once the customer dataset exists, `INSERT INTO … SELECT … WHERE` can build other datasets from it, such as an audience segment. The source must be a dataset or view your company owns. See [INSERT](https://docs.narrative.io/nql/commands/insert.md#source-restrictions-for-the-select-form).

Append these tasks to the `do` list to build a segment of high-value customers who bought in the last six months:

```yaml theme={null}
  - createSegments:
      call: CreateDatasetIfNotExists
      with:
        datasetName: demo_crm_segments
        displayName: 'Sample segments'
        description: 'Segments built from the sample CRM.'
        tags: [sample_data]
        dataPlaneId: <DATA_PLANE_ID>
        computePoolId: <COMPUTE_POOL_ID>
        schema:
          type: object
          required: [segment_name, customer_id, email_sha256, phone_sha256]
          properties:
            segment_name: { type: string }
            customer_id: { type: string }
            email_sha256: { type: string }
            phone_sha256: { type: string }
          file_config: { type: json }
  - clearSegments:
      call: ExecuteDml
      with:
        computePoolId: <COMPUTE_POOL_ID>
        nql: |
          DELETE FROM company_data.demo_crm_segments
          WHERE company_data.demo_crm_segments.customer_id LIKE 'c%'
  - fillHighValue:
      call: ExecuteDml
      with:
        computePoolId: <COMPUTE_POOL_ID>
        nql: |
          INSERT INTO company_data.demo_crm_segments (segment_name, customer_id, email_sha256, phone_sha256)
          SELECT
            'High-value loyalists' AS segment_name,
            crm.customer_id,
            crm.email_sha256,
            crm.phone_sha256
          FROM company_data.demo_crm_customers AS crm
          WHERE crm.loyalty_tier IN ('gold', 'platinum')
            AND crm.last_purchase_at >= TIMESTAMP '2026-05-22 00:00:00'
  - sampleSegments:
      call: CreateDatasetSample
      with:
        datasetName: demo_crm_segments
        computePoolId: <COMPUTE_POOL_ID>
  - mapSegments:
      call: CreateRosettaStoneMappingsIfNotExist
      with:
        datasetName: demo_crm_segments
        mappings:
          - attributeId: 76
            mapping:
              type: object_mapping
              propertyMappings:
                - path: value
                  expression: email_sha256
          - attributeId: 259
            mapping:
              type: object_mapping
              propertyMappings:
                - path: value
                  expression: phone_sha256
```

With the generator above, the segment holds 39 customers. It follows the same create, clear, fill, sample, and map order as the customer dataset. To add another segment to the same dataset, add another `ExecuteDml` insert with a different `segment_name` and `WHERE` clause before `sampleSegments`.

## Step 5: Create and run the workflow

The workflow's YAML goes in the `specification` field of a JSON request body. Set `trigger_immediately` to start the first run as soon as the workflow is created:

```bash theme={null}
jq -n \
  --rawfile spec demo-sample-crm.yaml \
  --arg dataPlane "<DATA_PLANE_ID>" \
  '{specification: $spec, data_plane_id: $dataPlane, trigger_immediately: true}' \
| curl -X POST https://api.narrative.io/workflows \
  -H "Authorization: Bearer $NIO_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d @-
```

The response's `id` is the workflow ID, and because the request triggered a run, it also carries a `run_id`. See [Create a workflow](https://docs.narrative.io/api-reference/workflows/create-a-workflow.md) for the full request and response.

With the MCP server, ask your assistant to call [`narrative_workflows_create`](https://docs.narrative.io/reference/integrations/mcp-server#narrative_workflows_create) with the YAML as `specification`, your data plane ID as `data_plane_id`, and `trigger_immediately: true`.

To refresh the data later, trigger the same workflow again rather than creating a new one. A workflow name can only be used by one workflow, so a second create with the same specification is rejected. See [Trigger a workflow](https://docs.narrative.io/api-reference/workflows/trigger-a-workflow.md), or use `narrative_workflows_trigger`.

## Step 6: Follow the run

Check the run's status until it reaches `completed` or `failed`:

```bash theme={null}
curl https://api.narrative.io/workflows/{workflow_id}/runs \
  -H "Authorization: Bearer $NIO_API_TOKEN"
```

Each task enqueues its own job. List the run's jobs to see which task is running and what each finished task returned:

```bash theme={null}
curl "https://api.narrative.io/jobs?workflow_run_id={run_id}" \
  -H "Authorization: Bearer $NIO_API_TOKEN"
```

With the MCP server, the same steps are [`narrative_workflow_runs_list`](https://docs.narrative.io/reference/integrations/mcp-server#workflow-tools) and [`narrative_jobs_search`](https://docs.narrative.io/reference/integrations/mcp-server#narrative_jobs_search) with `workflow_run_id`.

The sample job's result reports `row_count`, the number of rows the sample captured. A sample holds up to 1,000 rows, so for a 500-row dataset it should read `500`. After the run completes, the dataset's mappings list the six attributes from `mapCrm`, along with the `hashed_email` mapping Rosetta Stone derives from `sha256_hashed_email`.

## Limitations

> [!WARNING]
>
> **Fake identifiers match nothing.** No identity graph or partner dataset contains these hashes, so match, expansion, and enrichment results against a synthetic dataset come back empty. Use it to show how a flow works, not what it returns.

* **Runs stop at the first failure.** Tasks run one at a time, and a failed task skips everything after it. If `fillCrm` fails, `clearCrm` has already emptied the dataset, and it stays empty until a run succeeds. See [Handling errors](https://docs.narrative.io/guides/workflows/workflow-orchestration.md#handling-errors).
* **The dataset's settings are fixed when it's created.** When the dataset already exists, `CreateDatasetIfNotExists` returns it unchanged, so a change to `tags`, `description`, or `schema` in the YAML has no effect on a rerun. No workflow task changes the tags of an existing dataset. To change them, edit the dataset in the app or with [Update a dataset](https://docs.narrative.io/api-reference/datasets/update-a-dataset.md).

## Related content

> [card] [Automating Multi-Step Pipelines](https://docs.narrative.io/guides/workflows/workflow-orchestration.md)
>
> Create, trigger, schedule, and monitor workflows

> [card] [Task Reference](https://docs.narrative.io/reference/workflows/tasks.md)
>
> Parameters and output for every workflow task

> [card] [Workflow Specification Syntax](https://docs.narrative.io/reference/workflows/specification-syntax.md)
>
> The YAML format for documents, tasks, and exports

> [card] [Mapping Schemas](https://docs.narrative.io/guides/rosetta-stone/mapping-schemas.md)
>
> How mappings connect dataset columns to Rosetta Stone attributes


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.