WhereFour Silver Layer Line-Item Granularity Findings (ENG-191)

Findings

The WhereFour DLT source (apps/dagster/erp_pipeline/implementations/wherefour_sync_orchestrator.py) defines a silver table wherefour_orders via wherefour_orders_hints() in utils/dlt_wherefour_hints.py. This table contains only flattened order header fields (e.g. id, customer_id, order_number, order_date, total, etc.).

No table currently materializes individual order line items (quantity, unit_price, sku, customer_id per line). The existing pipeline does not create a wherefour_order_lines (or similar) Iceberg table, nor does it apply hints for such a table.

Sample SQL against existing silver tables

SELECT * FROM erp_data_native_v2.wherefour_orders
WHERE connection_id = '093e4161-3310-4559-ab5e-24723d93a6aa'
  AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
  AND customer_id = 'CLEVELAND_KITCHEN';

Result: Returns one row per order, without per‑line quantity or unit_price columns.

Conclusion

• Line‑item granularity is missing in the current WhereFour silver layer.
• To satisfy the downstream Hono endpoint, we must extend the DLT source to emit a wherefour_order_lines table (or add line‑item columns to wherefour_orders).

Scope Estimate for Extension

Task	Effort (person‑days)
Add wherefour_order_lines_hints() with appropriate columns (order_id, line_number, sku, quantity, unit_price, etc.)	0.5
Implement sync_order_lines method in WhereFourSyncOrchestrator (fetch from API, map rows)	1.0
Update downstream materializations / Iceberg merge logic	0.5
Add tests for new table and validate query example	0.5
Documentation and CI verification	0.25
Total	2.75

Recommendation

Proceed to Ticket 4 (Hono endpoint) using the current wherefour_orders data (will need to handle missing line‑item fields) or prioritize extending the silver layer (Ticket 2) to include line items before implementing the endpoint.

---

Generated by Qwen Code for Linear ticket ENG‑191.