The Union That Doubled Revenue

Symptom 1 - revenue doubled

Each file contains a summary row whose Revenue equals that file's total. Six files means six summary rows whose Revenue sums to the entire dataset. Union detail + summary and SUM(Revenue) counts the data twice: once in detail, once in total. That is the "almost exactly double" - the word almost is the giveaway, since rounding in the summary rows breaks exactness. The summary rows also pollute every dimension (a Store called "Total" now appears in store counts and in any store-level ranking).

Symptom 2 - Discount null for 60% of rows

Discount is text in Jan–Mar and numeric in Apr–Jun. On union, Tableau resolves the column to a single type: it takes the numeric type from Apr–Jun and coerces "5%" and "n/a" to NULL - or it takes string and then every SUM(Discount) silently ignores everything. Either way, three of six months lose their discount values. "n/a" is a third category again - it is not zero, and coercing it to zero understates discount.

Symptom 3 - Channel null for Jan–Mar

Schema drift. Union matches columns by name; a column that does not exist in a file yields NULL for those rows, not an error. January genuinely may not have had a Channel attribute - but nobody has checked whether it is a missing column or a missing value, and the two need different handling.

Fix order

1. Filter out the summary rows first. In Prep, add a Clean step removing rows where Store = 'Total' (and add a guard for 'TOTAL'/'Total ' variants - UPPER(TRIM())). Do this before any aggregation, or every downstream number is wrong and you will chase ghosts.
2. Fix Discount next. Decide the business rule: is "n/a" a zero discount or an unknown? Parse "5%" to 0.05 with a calculated field, map "n/a" explicitly, and cast the whole column to a numeric type in one step. Do not rely on implicit coercion.
3. Handle Channel last. Determine whether pre-April orders genuinely had no channel or whether the column was added mid-year. If a channel applies retrospectively, backfill from a reference table; if not, leave NULL and document it, so the NULL is understood rather than filtered away silently.

Then reconcile: compare SUM(Revenue) in the output against Finance's reported total before publishing anything.

Design principles: assume the schema will drift again, and make drift visible

1. Union by wildcard, not by hand. Point the union at the folder with a wildcard pattern so a new monthly file is picked up automatically, and explicitly enable "include filename" so every row carries its source file. Drop by hand-added unions - they silently miss files.
2. Guard rail 1: summary-row removal. A Clean step filtering on the store label, applied inside the flow, not left to whoever builds the chart. Better still, ask Finance to stop emitting summary rows; if they will not, defend against them.
3. Guard rail 2: explicit schema, enforced. After the union, add a step that outputs a fixed column set with fixed types - every expected field present, cast, and renamed to a canonical name. Anything unexpected is dropped or flagged rather than flowing through. This is what converts a silent NULL into a visible decision.
4. Guard rail 3: a type-check branch. Split the flow: one branch continues to the output, a parallel branch counts NULLs and distinct values in Discount and Channel per source file. Publish that branch as a data-quality report. Schema drift then appears as a number, not as a broken dashboard three weeks later.
5. Guard rail 4: row-count and reconciliation assertion. Compare output row count against the sum of source row counts minus expected summary rows; compare SUM(Revenue) against the prior month plus this month's delta. Write the assertion into a validation step so it fails the flow, not the report.
6. Parameterise the folder path. Hard-coded paths are why nobody dares move the flow.
7. Document it in the flow itself - step names, a comment on the summary-row filter, and a note on the Discount parsing rule, plus a short written handover. The absence of documentation is half of why this took a rebuild.

Do not attempt to solve schema drift with a giant calculated field per column. It hides the problem in one place and is unmaintainable within two quarters.

Three gates: source, flow output, published data source.

Gate 1 - source profile (inside Prep, before the union)

• Row count and distinct file name per input; a missing monthly file should be an error, not a smaller dataset.
• Detect summary rows: count rows where the store label is not a real store, per file. Expect 1 each; anything else means a format change.
• Column inventory per file: which fields exist, what data type. Diff against the previous month. This is how Channel's arrival would have been caught in April instead of in the review.

Gate 2 - flow output (assertions that fail the run)

• Reconciliation: SUM(Revenue) output vs Finance's reported total, tolerance set explicitly (e.g. ±0.1%). This is the check that would have caught the doubling on day one.
• Row count: output rows = sum of input rows − 6 summary rows. A mismatch means the filter or the union changed behaviour.
• Null profiling: % NULL by column, by month. Discount at 60% should have been a hard stop; set thresholds and alert on breach.
• Duplicate detection: distinct Order ID vs row count - catches a re-sent file, which the wildcard union will happily double-count.
• Grain check: confirm one row per order line; if the grain changed, every downstream ratio is suspect.

Gate 3 - published data source (post-publish, ongoing)

• A small validation sheet in the workbook: total revenue, total rows, % null discount, latest month present. Anyone can glance at it and see whether the numbers are plausible.
• Certify the data source with a named owner and a refresh SLA, so downstream authors use the governed version rather than rebuilding their own union from the same folder - which is how you end up with two dashboards that disagree.
• Refresh-failure alerting to a monitored channel; a flow that fails silently is a flow that reports last month's numbers as this month's.

The governing principle: reconcile to an external number (Finance's total) rather than to another internal view. Internal consistency checks all passed while revenue was double.