MapCraft walkthrough

This walkthrough picks up where the StructScan walkthrough left off: a freshly-created Project (Customer orders ingest, lane Warehouse) with an auto-generated source schema and the customer_orders.csv profile attached. By the end of this page you will have:

• Uploaded a target schema (order_canonical).
• Accepted AI-suggested mappings in bulk.
• Drawn a remaining mapping through a lookup table.
• Written a natural-language transformation rule and reviewed the generated Python / SQL / JSONata.
• Saved Version 1.
• Compiled the DAG that FlowBridge will run.

You'll need

• The Customer orders ingest Project open at /mapcraft/{projectId}.
• The target schema JSON from the next section (or any equivalent canonical Order schema).

---

Step 1 — Verify the source schema

When you land on the project, switch to the Table view. The source column already shows seven fields, populated from StructScan:

Source path	Type	Nullable	From profile
order_id	STRING	no	StructScan
customer_email	STRING	yes (2 nulls)	StructScan
order_date	DATE	no (⚠ mixed-format)	StructScan
country	STRING	no (⚠ mixed-representation)	StructScan
total_amount	NUMBER	no (⚠ negative-value)	StructScan
currency	STRING	yes (1 null)	StructScan
status	ENUM	no	StructScan

You can rename source paths here if your downstream conventions differ (customer_email → email, etc.). For this walkthrough, leave them as-is.

---

Step 2 — Upload the target schema

Click Upload target schema in the toolbar. Choose JSON and paste the following.

order_canonical.json (click to expand)

order_canonical.json

{
  "name": "order_canonical",
  "version": "1.0.0",
  "fields": [
    { "path": "order_id",       "type": "STRING", "required": true,  "description": "Unique order identifier" },
    { "path": "customer_email", "type": "STRING", "required": false, "format": "email" },
    { "path": "order_date",     "type": "DATE",   "required": true,  "format": "iso8601" },
    { "path": "country_code",   "type": "STRING", "required": true,  "format": "iso3166-alpha2" },
    { "path": "total_amount",   "type": "NUMBER", "required": true,  "minimum": 0 },
    { "path": "currency",       "type": "STRING", "required": true,  "format": "iso4217" },
    { "path": "status",         "type": "STRING", "required": true,  "enum": ["paid","pending","refunded"] }
  ]
}

Click Upload. MapCraft parses the JSON into a Schema (role: TARGET) with seven canonical fields. The Table view now shows source on the left and target on the right; every target row starts at UNMAPPED.

---

Step 3 — Run Auto-map

Click Auto-map in the toolbar. MapCraft compares the source and target schemas (and consults the source profile) and produces a batch of AI_SUGGESTED MappingFields.

How confidence works

Each suggestion gets a 0.0 – 1.0 confidence score. The score combines name similarity, type compatibility, and sample-value evidence. Anything ≥ 0.9 is usually safe to bulk-accept; 0.7 – 0.9 deserves a glance; below 0.7 you should review.

Expected results for this project:

Target	Suggested source	Confidence	Notes
order_id	order_id	1.00	Exact match.
customer_email	customer_email	0.99	Exact match.
order_date	order_date	0.85	Same name; format will need a rule.
country_code	country	0.62	Name differs; values differ; lookup needed.
total_amount	total_amount	0.97	Negative-value flag noted.
currency	currency	0.99	Exact match.
status	status	0.95	Enum subset check passes.

---

Step 4 — Bulk-accept the high-confidence suggestions

Switch to the Table view if you are not already there. Sort by confidence descending. Select every row with confidence ≥ 0.85:

• order_id, customer_email, order_date, total_amount, currency, status.

Click Bulk update → Set status to ACCEPTED. Six fields move from AI_SUGGESTED to ACCEPTED. The Project status auto-advances from DRAFT to MAPPING.

One field remains unresolved: country_code. Its 0.62 confidence is too low to accept blindly because the source has mixed representations.

---

Step 5 — Wire country_code through a lookup

Switch to the Canvas view. The unresolved country_code target shows a dashed outline. Drag a line from source country to target country_code. A side panel opens: Choose how to transform this field. Click Add lookup.

5a. Create the lookup table

A modal opens. Fill it in as follows:

Name:             country_iso2
Key column:       country_name
Value column:     country_code

Then add rows. The fastest way is to paste the CSV below.

CSV

country_iso2 (CSV)

country_name,country_code
US,US
USA,US
United States,US
GB,GB
United Kingdom,GB
DE,DE
Germany,DE
FR,FR
France,FR
Netherlands,NL
Finland,FI
Switzerland,CH
India,IN

JSON

country_iso2 (JSON)

{
  "name": "country_iso2",
  "key_column": "country_name",
  "value_column": "country_code",
  "entries": [
    { "country_name": "US",             "country_code": "US" },
    { "country_name": "USA",            "country_code": "US" },
    { "country_name": "United States",  "country_code": "US" },
    { "country_name": "GB",             "country_code": "GB" },
    { "country_name": "United Kingdom", "country_code": "GB" },
    { "country_name": "DE",             "country_code": "DE" },
    { "country_name": "Germany",        "country_code": "DE" },
    { "country_name": "FR",             "country_code": "FR" },
    { "country_name": "France",         "country_code": "FR" },
    { "country_name": "Netherlands",    "country_code": "NL" },
    { "country_name": "Finland",        "country_code": "FI" },
    { "country_name": "Switzerland",    "country_code": "CH" },
    { "country_name": "India",          "country_code": "IN" }
  ]
}

Click Save. The lookup is stored on the Project and shows entry_count: 13.

5b. Bind the lookup to the mapping

Back in the side panel, the new country_iso2 lookup appears in the dropdown. Select it. The mapping line on the canvas turns solid, and the country_code field moves to MANUAL status (you wired it deliberately, without taking an AI suggestion).

---

Step 6 — Write a transformation rule for order_date

order_date is ACCEPTED but the underlying values still come in three formats. Click the field in the Table view, then Add rule.

The rules editor accepts a name and a natural-language description.

Rule name:    Normalise order_date to ISO 8601 UTC
Description:  Parse mixed US (MM/DD/YYYY), ISO (YYYY-MM-DD),
              and DD-Mon-YYYY date formats and emit ISO 8601
              dates in UTC.

Click Generate. MapCraft generates code in three languages.

Python

generated by MapCraft

from datetime import datetime
from typing import Optional

_FORMATS = ("%Y-%m-%d", "%m/%d/%Y", "%d-%b-%Y")

def normalise_order_date(value: str) -> Optional[str]:
    """Parse mixed US / ISO / DD-Mon-YYYY date formats and emit ISO 8601 UTC."""
    if value is None or value == "":
        return None
    for fmt in _FORMATS:
        try:
            return datetime.strptime(value, fmt).date().isoformat()
        except ValueError:
            continue
    raise ValueError(f"Unrecognised date format: {value!r}")

SQL (Postgres)

generated by MapCraft

-- Normalise order_date to ISO 8601
-- Tries ISO, US, and DD-Mon-YYYY in order; raises if none match.
COALESCE(
  to_date(NULLIF(order_date, ''), 'YYYY-MM-DD'),
  to_date(NULLIF(order_date, ''), 'MM/DD/YYYY'),
  to_date(NULLIF(order_date, ''), 'DD-Mon-YYYY')
)

JSONata

generated by MapCraft

(
  $iso := $match(order_date, /^\d{4}-\d{2}-\d{2}$/);
  $us  := $match(order_date, /^(\d{2})\/(\d{2})\/(\d{4})$/);
  $mon := $match(order_date, /^(\d{2})-([A-Za-z]{3})-(\d{4})$/);
  $iso ? order_date
  : $us  ? $us[0].groups[2] & "-" & $us[0].groups[0] & "-" & $us[0].groups[1]
  : $mon ? $mon[0].groups[2] & "-" & $lookup({"Jan":"01","Feb":"02","Mar":"03","Apr":"04","May":"05","Jun":"06","Jul":"07","Aug":"08","Sep":"09","Oct":"10","Nov":"11","Dec":"12"}, $mon[0].groups[1]) & "-" & $mon[0].groups[0]
  : $error("Unrecognised date format: " & order_date)
)

Generated code is reviewable

The three languages are not the same code translated word-for-word. They are three implementations of the same rule, each optimised for its target. You can edit any of them by hand; MapCraft will keep your edits unless you re-generate.

Click Save rule and attach it to the order_date MappingField.

---

Step 7 — Save Version 1

Click Save version in the toolbar.

Version number:   1
Change summary:   Initial mapping. Auto-mapped 6/7, country via
                  lookup, order_date via rule.

This snapshots the Project — fields, rules, lookups — into the Version table. You can diff or restore this version at any time.

---

Step 8 — Compile the DAG

Switch to the DAG view. Click Compile from mappings. MapCraft reads every ACCEPTED or MANUAL MappingField, plus the attached rules and lookups, and builds a DAGDocument.

For this project, the compiled DAG looks like:

The transform node is where the rule and the lookup do their work. Click it to see the previewable SQL.

SQL preview for the transform node

transform · orders_clean

SELECT
  src.order_id                                                AS order_id,
  src.customer_email                                          AS customer_email,
  COALESCE(
    to_date(NULLIF(src.order_date, ''), 'YYYY-MM-DD'),
    to_date(NULLIF(src.order_date, ''), 'MM/DD/YYYY'),
    to_date(NULLIF(src.order_date, ''), 'DD-Mon-YYYY')
  )                                                           AS order_date,
  lk.country_code                                             AS country_code,
  src.total_amount                                            AS total_amount,
  src.currency                                                AS currency,
  src.status                                                  AS status
FROM   customer_orders_raw   src
LEFT JOIN country_iso2_lookup lk
       ON lk.country_name = src.country
WHERE  src.total_amount >= 0;   -- target requires non-negative

Two things worth noticing

1. The WHERE total_amount >= 0 clause comes from the target schema's "minimum": 0 constraint — not from a rule you wrote. The compiler enforces target constraints automatically.
2. The lookup is rendered as a LEFT JOIN against a generated country_iso2_lookup table — not as an inline CASE — so you can re-use the same lookup in other projects without copy-pasting.

---

Step 9 (optional) — Export

If you want the Project as a portable artifact, the toolbar's Export menu has two choices.

Project config (JSON)

Downloads a single JSON file matching the export_config.schema.json contract. Includes the schemas, mappings, rules, lookups, and the compiled DAG. Round-trippable: you can import this into another DataChord workspace.

Python module

Downloads a standalone Python module that implements the transform end to end. Takes the source CSV path and writes the canonical JSON to stdout. Useful when a downstream team wants to run the transform outside DataChord.

---

What you produced

• One Project in status MAPPING (about to become REVIEW once you hand it off).
• A target Schema (order_canonical) and a source Schema (auto from StructScan).
• Seven MappingFields — six ACCEPTED, one MANUAL (country_code).
• One LookupTable (country_iso2, 13 entries).
• One TransformationRule (Normalise order_date to ISO 8601 UTC) with Python / SQL / JSONata implementations.
• One Version (#1) and one compiled DAGDocument.

Continue: FlowBridge walkthrough →