Laboratory 2: join TXT and Excel into a regional summary
This example combines 1,000 synthetic pipe-delimited transactions with a 1,000-row Excel customer catalog. It filters approved transactions, joins customers to regions and produces a four-row CSV summary. The expected result is calculated by an independent oracle instead of reusing the engine's Join or Aggregate implementations.
Download the existing synthetic fixture package, edition 6.0.2 and extract it. The fixture release is independent of the Dova 7 UI release. Use an authorized DovaETL source checkout and its required Python dependencies. No customer transactions, payment provider or external connection is involved in the isolated exercise.
1. Review both inputs
transform_workflows/inputs/transactions.txt contains customer_id, amount and status. Every fifth transaction is REJECTED; the others are APPROVED. Identifiers and amounts initially arrive as text.
transform_workflows/inputs/customers.xlsx contains a Customers sheet with a header and 1,000 rows in columns A–C: customer_id, customer_name and region. Its identifier is numeric, and text contains deliberate accent and spacing variations. The expected CSV contains exactly sales_region and approved_total.
2. Inspect the component order
| Component | Required behavior |
|---|---|
| TXT Convert | Convert amount to numeric amount_number. |
| Excel Clean | Trim/collapse spaces, remove accents and uppercase customer/region text. |
| Join | Inner join by integer customer_id; TXT is left, Excel is right; hash strategy and nulls unequal. |
| Filter | Keep APPROVED transactions. |
| Aggregate | Group by region and sum amount_number into total_amount. |
| Sort | Order total descending. |
| Rename | Produce sales_region and approved_total. |
| Select | Retain exactly those two fields. |
The two input branches let you diagnose parsing before blaming the join. Numeric key normalization matters because one identifier comes from text and the other from an Excel cell. Duplicate customer keys can multiply joined records and inflate an aggregate.
3. Execute the isolated verification
Run from the extracted fixture directory:
python .\run_pocs.py --repo C:\path\DovaETL --report .\verification.json
The wrapper verifies both laboratories. It checks fixture integrity, validates definitions, resolves the source_txt and source_xlsx bindings, and verifies the reported 1,000 rows for the Customers sheet. Network and persistent application-storage access are forbidden by the runner. The optional JSON report records synthetic evidence only.
4. Accept the result
Confirm four rows and byte-for-byte equality with the independent oracle and expected/regional_approved_totals.csv. The oracle builds a region lookup by integer identifier, skips rejected transactions, sums by normalized region and sorts. Inspect exactly two output fields, UTF-8 encoding, header and LF line endings.
The sample's number representation does not establish a universal accounting or rounding policy. For a real financial process, define precision, scale, currency and rounding separately before implementing the rule.
5. Move into a laboratory Workflow
Publish the Mapping, then configure both source bindings in the Session. A single uploaded file does not replace the two sources. In the CLI, use the multi-source command shape from CLI and API with actual task and node identifiers. Publish the Workflow, run it manually and compare its output in the same way.
For too few regions, inspect unmatched keys and join type. For inflated totals, inspect lookup uniqueness. For regions split by accents or spaces, inspect Excel cleanup. For additional output fields, inspect Rename, Select and the target schema. Remote file access, worker capability and authorization need their own acceptance checks.