Build a reproducible data-preparation workflow

Cleaning one file is a task. Receiving the same export every week, applying the same corrections, merging it with another source and converting the result is a workflow.

The difference matters because you stop thinking about isolated tools and start defining an ordered chain of transformations.

Start from the expected result

Write down the target before choosing operations. For example:

“I receive three CSV exports. I need to remove blank rows, align their columns, merge them, deduplicate on customer_id and produce one final file.”

That sentence already reveals most of the workflow.

A typical preparation chain

Import files
→ inspect structure
→ clean values
→ align columns
→ merge tables
→ remove duplicates
→ filter unnecessary rows
→ convert to the target format
→ validate the result

The exact order varies, but every step should have a reason.

Clean early

Whitespace in a key can prevent two records from matching. A mismatched header can create a duplicate column during a merge. A duplicate row can distort later statistics.

That is why Clean Excel and CSV files is often a sensible first transformation after import.

Reduce data before expensive steps

If only one region is needed, filter early rather than carrying a million unnecessary rows through every later operation. The same applies to unused columns.

See Filter, sort, select and rename CSV columns for the individual operations.

Merge only after schemas are aligned

Combining files before harmonizing headers can create separate columns for the same concept. Prepare each source first, then merge.

The Excel and CSV Merger covers occasional jobs. Compatible table transformations can also be reused inside the Workspace.

Compare at useful checkpoints

Comparison can be used as a before/after control or to review a new export before replacing an old one. The Excel and CSV Comparator matches records using a key rather than relying on row positions.

Convert late unless there is a reason not to

Changing format too early can introduce unnecessary constraints. Keep the current representation while it supports the transformations you need, then convert when the destination requires it.

The guide Convert CSV, Excel, JSON and TSV explains the main trade-offs.

Standalone tool or pipeline?

A one-off operation is usually fastest in a dedicated tool. A pipeline becomes more useful when:

• the same sequence returns regularly;
• several transformations must stay in a fixed order;
• many files need the same logic;
• the result of one step should feed the next automatically.

In the Workspace, compatible modules use Bethemesh’s shared transformation engines. The tool catalog and the pipeline-compatible transformation catalog are not identical, so only supported modules should be assumed to work in a pipeline.

Make the workflow verifiable

Automation should include simple checks: row counts, expected headers, empty-key counts, duplicates removed and final output format.

Keep both the original source and a validated output when the data matters. This separation between source, transformations and result makes the process easier to replay, audit and improve.