The Comments Column That Keeps Getting Cut Off
The support analytics team pulls ticket data from a vendor portal that drops 14 CSVs a week into a shared folder: one file per region plus a combined file, all named tickets_<region>_<yyyymmdd>.csv. A single Input Data tool with a wildcard reads everything. Last month the dashboard started showing comment text that stops mid-sentence on roughly 3% of tickets. Separately, the EMEA file gained a new column, escalation_tier, and the wildcard now fails on some runs. And ticket_id comes through as a number in files where every ID is numeric, but as text in the APAC file where a few IDs carry a suffix, which breaks the downstream Union.
1. The truncation hits only a small minority of rows. Explain why that pattern points at the reader rather than the source system, and give two remedies with their trade-offs.
Read the worked answer
Why the pattern matters. If the portal were truncating, every comment would be affected equally - the column would have a hard ceiling visible across all rows, and the distribution of word counts would show a spike at that ceiling. Truncation on only ~3% of rows is the signature of a width inferred from a sample: the reader looks at some rows, decides the comment field is 254 characters wide, and silently clips the rare long value.
Confirm it first. Add a LENGTH([comment]) formula and Summarize Max; compare against the same measure computed from the raw file (open one affected file in a text editor and count). Also read the Results pane messages - size/truncation warnings appear there but are easy to scroll past.
Two remedies.
- Raise the max field length in the Input Data tool's options (or set it to unlimited). Cheap, one setting, no new tools. Cost: it applies to every string field in the file, so memory per row grows even where it isn't needed, and you must re-verify that no downstream Select, Auto Field or Join is assuming the old width.
- Impose a schema: a Text Input holding the intended field names and types, fed to a Dynamic Input tool that swaps in each file path. Locks the contract, survives a file that happens to be all short comments, but adds a template to maintain and another thing to update when the vendor changes a column.
Either way, add a guard: a Filter on LENGTH([comment]) >= 254 piped to a Message tool will tell you the day the ceiling is breached again, instead of a dashboard reader noticing.
2. The wildcard input errors out when EMEA's new column appears. What is your fix, and why not simply delete the column from the EMEA file each week?
Read the worked answer
Why the wildcard breaks. A wildcard read treats the set of files as one logical table. When one file's field list diverges, the tool has no instruction for reconciling them and the read fails.
Why manual deletion is the wrong answer. It is a person-shaped dependency: it breaks the week someone is on leave, it leaves no audit trail of what the vendor sent, and it invites the same fire drill the next time the vendor adds resolution_hours. It also makes the workflow non-reproducible - the historical files on disk no longer match what the workflow consumed.
The fix. Replace the single wildcard with a Directory tool (filtered to tickets_*.csv) feeding a batch macro that contains the Input Data tool, a Select tool that standardises field names, order and types, and then a Union after the macro. Each file is read independently, so schema drift in one region stops being a global failure, and Union (unlike a wildcard read) reconciles differing field sets. Inside the macro, add escalation_tier to the Select if present and let it come through as null for regions that don't send it.
Interim option if the macro can't ship this week: ask the vendor to hold the schema, and in the meantime read the EMEA file with a second, separate Input tool and Union it with the other regions - explicitly, in one place, with a comment explaining why.
3. `ticket_id` types differ by file. How do you make the Union stable, and what is the risk of fixing the type only at the end of the workflow?
Read the worked answer
Fix at ingestion, not at the end. Cast ticket_id to V_String in a Select tool immediately after each read (inside the batch macro from the previous task, so it is one place), sized above the longest observed ID - measure it with LENGTH + Summarize rather than guessing, because a too-small V_String truncates just like the comment field did.
Why the end is too late. If a file is read as numeric, 1042-A is already null by the time anything downstream sees it. No later Formula can restore characters that never made it into the stream. Worse, the damage is silent: joins on ticket_id against a text-keyed table simply return fewer matches, and the row count looks plausible.
The hidden trap with wildcards. In a wildcard read, the first file encountered tends to drive the field types. So the type of ticket_id can change from run to run depending on file order or on whether that day's batch happened to include a suffixed ID. That is a nondeterministic workflow, which is a much harder bug to chase than a missing column. The batch-macro structure removes the ambiguity because every file gets the same Select.
Verification. After the Union, run a Summarize Count on ticket_id and compare it to the sum of row counts of the source files; any shortfall is records lost to a type mismatch or a failed read.