Skip to main content

Explore MotherDuck Retail Analytics

How to use this explorer

Move between stages with the arrows or the numbered stage list. Each stage shows the input it receives on the left and the output it produces on the right, with the lines that changed marked. The configuration that makes the change sits below the comparison, and the final stage shows the complete pipeline.

The 5 stages in order

  1. Generate POS transactions
    Synthesize a point-of-sale event with items, totals, and a payment method on a 100ms timer.
  2. Enrich with store metadata
    Look up the region, format, city, and size for the store and derive time and basket features.
  3. Validate and flag transactions
    Apply the anomaly checks and attach the flags, an anomaly bit, and a quality score.
  4. Batch and encode to Parquet
    Flatten items to JSON text, batch 1000 events or 10 seconds, group by store region, and encode each region group as a separate Parquet object.
  5. Write partitioned Parquet to S3
    Upload each Parquet batch under region and date partitions that DuckLake can discover. Delivery has not been exercised.

Interactive pipeline

MOTHERDUCK RETAIL REFERENCE

Synthetic POS events run through expanso-edge

Stage 1 of 5

Generate POS transactions

Synthesize a point-of-sale event with items, totals, and a payment method on a 100ms timer.

94HighlightsAuthored emphasis only—not a computed diff.
Copy & download

Input

[generate input: one synthetic POS event every 100ms]

Output

Authored highlight: [Authored highlight: {Authored highlight: "employee_id": "EMP-2505",Authored highlight: "items": [Authored highlight: {Authored highlight: "category": "frozen",Authored highlight: "name": "Item 6",Authored highlight: "qty": 2,Authored highlight: "sku": "SKU-65505",Authored highlight: "unit_price": 24.68},Authored highlight: {Authored highlight: "category": "dairy",Authored highlight: "name": "Item 153",Authored highlight: "qty": 1,Authored highlight: "sku": "SKU-53152",Authored highlight: "unit_price": 23.99},Authored highlight: {Authored highlight: "category": "household",Authored highlight: "name": "Item 328",Authored highlight: "qty": 4,Authored highlight: "sku": "SKU-45827",Authored highlight: "unit_price": 3.91},Authored highlight: {Authored highlight: "category": "meat",Authored highlight: "name": "Item 295",Authored highlight: "qty": 3,Authored highlight: "sku": "SKU-67794",Authored highlight: "unit_price": 8.42},Authored highlight: {Authored highlight: "category": "dairy",Authored highlight: "name": "Item 203",Authored highlight: "qty": 3,Authored highlight: "sku": "SKU-36202",Authored highlight: "unit_price": 7.13},Authored highlight: {Authored highlight: "category": "bakery",Authored highlight: "name": "Item 64",Authored highlight: "qty": 4,Authored highlight: "sku": "SKU-46063",Authored highlight: "unit_price": 38.24}],Authored highlight: "payment_method": "gift_card",Authored highlight: "store_id": 6,Authored highlight: "subtotal": 288.6,Authored highlight: "tax_amount": 25.25,Authored highlight: "tax_rate": 0.0875,Authored highlight: "terminal_id": 6,Authored highlight: "timestamp": "2026-10-05T17:39:21.255967-07:00",Authored highlight: "total_amount": 313.85,Authored highlight: "txn_id": "5032b55b-4e75-4b30-9543-fe5930de4419",Authored highlight: "type": "sale"},Authored highlight: {Authored highlight: "employee_id": "EMP-8152",Authored highlight: "items": [Authored highlight: {Authored highlight: "category": "bakery",Authored highlight: "name": "Item 154",Authored highlight: "qty": 2,Authored highlight: "sku": "SKU-40153",Authored highlight: "unit_price": 36.49}],Authored highlight: "payment_method": "card",Authored highlight: "store_id": 3,Authored highlight: "subtotal": 72.98,Authored highlight: "tax_amount": 6.39,Authored highlight: "tax_rate": 0.0875,Authored highlight: "terminal_id": 3,Authored highlight: "timestamp": "2026-10-05T17:39:21.355365-07:00",Authored highlight: "total_amount": 79.37,Authored highlight: "txn_id": "b44d442c-8839-4cb0-b885-a76d573def6b",Authored highlight: "type": "sale"},Authored highlight: {Authored highlight: "employee_id": "EMP-9827",Authored highlight: "items": [Authored highlight: {Authored highlight: "category": "beverage",Authored highlight: "name": "Item 457",Authored highlight: "qty": 1,Authored highlight: "sku": "SKU-82456",Authored highlight: "unit_price": 41.21},Authored highlight: {Authored highlight: "category": "electronics",Authored highlight: "name": "Item 430",Authored highlight: "qty": 2,Authored highlight: "sku": "SKU-74929",Authored highlight: "unit_price": 19.17}],Authored highlight: "payment_method": "card",Authored highlight: "store_id": 28,Authored highlight: "subtotal": 79.55,Authored highlight: "tax_amount": 6.96,Authored highlight: "tax_rate": 0.0875,Authored highlight: "terminal_id": 8,Authored highlight: "timestamp": "2026-10-05T17:39:21.454515-07:00",Authored highlight: "total_amount": 86.51,Authored highlight: "txn_id": "9be5e1c9-1347-4787-84b8-5249eb4ed8bf",Authored highlight: "type": "sale"}]
Stage configuration01-generate-pos-transactions.yaml
input:
  generate:
    interval: 100ms
    mapping: |
      root.txn_id = uuid_v4()
      root.store_id = random_int(min: 1, max: 50)
      root.terminal_id = random_int(min: 1, max: 10)
      root.timestamp = now()

      let roll = random_int(min: 1, max: 100)
      root.type = match {
        $roll <= 85 => "sale",
        $roll <= 95 => "return",
        _ => "exchange"
      }

      root.payment_method = ["card", "card", "card", "cash", "mobile", "gift_card"].index(random_int(min: 0, max: 5))
      root.employee_id = "EMP-" + random_int(min: 1000, max: 9999).string()

      let categories = ["grocery", "produce", "dairy", "bakery", "meat",
                         "frozen", "beverage", "household", "personal_care", "electronics"]
      let item_count = random_int(min: 1, max: 6)
      root.items = range(0, $item_count).map_each(
        {
          "sku": "SKU-" + random_int(min: 10000, max: 99999).string(),
          "name": "Item " + random_int(min: 1, max: 500).string(),
          "category": $categories.index(random_int(min: 0, max: 9)),
          "qty": random_int(min: 1, max: 4),
          "unit_price": (random_int(min: 99, max: 4999).number() / 100)
        }
      )

      root.subtotal = (root.items.map_each(this.qty * this.unit_price).sum() * 100).round() / 100
      root.tax_rate = 0.0875
      root.tax_amount = (root.subtotal * root.tax_rate * 100).round() / 100
      root.total_amount = ((root.subtotal + root.tax_amount) * 100).round() / 100

      root = if root.type == "return" {
        root.assign({
          "subtotal": -root.subtotal,
          "tax_amount": -root.tax_amount,
          "total_amount": -root.total_amount
        })
      }