How auditing a legacy Bash/AWK extraction script uncovered a silent data failure, boosting the reach of billing notifications by +18.7% overnight.
Every month, Entel sends millions of billing and collection notifications (SMS, IVR, Mail, App). However, the overall reach was mysteriously underperforming, stagnating around ~80-84%. The business side assumed it was simply an issue with outdated contact information or unreachable devices.
The reality was hidden deep in the legacy code. A silent failure in the extraction pipeline was indiscriminately dropping nearly a fifth of the customer base before the notifications were even dispatched or cross-referenced. Customers were not being notified, leading to involuntary early arrears and saturated support channels.
"Our customers change their phone numbers or emails constantly. There is nothing we can do to improve the hit rate."
"The extraction script drops any National ID (RUT) that doesn't parse as a perfect integer or fails on joins. We are deleting our own data."
Before data reaches a clean cloud warehouse or a polished dashboard, it often exists as gigabytes of raw, tab-separated .txt logs generated by core legacy systems. To find the missing ~18%, I audited the AWK and sed scripts responsible for preparing the daily flat files (e.g., NOTIFICACIONES_DIARIAS.txt).
The Bug: The Chilean National ID (RUT) ends in a verification digit, which can be a number from 0-9 or the letter 'K'. The legacy parsing logic used functions that either failed when encountering the non-numeric 'K' or retained it in a way that broke downstream JOIN operations against pure-integer customer tables. This systemic error marginalized any ID ending in that character and caused widespread format mismatches.
# The corrected AWK pipeline to process and format the raw .txt logs
# 1. Cleans leading zeros: sub(/^0*/,"")
# 2. Dynamically truncates the verification digit (r=substr($1, 0, length($1)-1))
# to ensure 100% of IDs pass as clean integers for downstream JOINs.
# 3. Formats dates and normalizes channels (SMS, IVR, MAIL) to boolean flags
awk -F "\t" -v OFS="|" "{
del=\"-\";
sub(/^0*/,\"\");
r=substr(\$1, 0, length(\$1)-1);
y=substr(\$4,length(\$4)-3,4);
m=\"00\"substr(\$4,4,2)+0;
d=substr(\$4,1,2);
dd=substr(\$9,1,2);
mm=substr(\$9,4,3);
yy=20substr(\$9,length(\$9)-1,3);
print y,m,d,del,\$1,\$2,\$3,\$5,\$6,\$7,\$8,\$9,yy,dd,mm,r
}" NOTIFICACIONES_DIARIAS_MARZO.txt | \
awk -F "|" -v OFS="|" "{
print 202303, \$16, \$6, \$7, \$1\$4\$2\$4\$3, \$8, \$9, \$10, \$11,
\$13\$4\$15\$4\$14,
gsub(/SMS/,\"1\"),
gsub(/IVR/,\"1\"),
gsub(/MAIL/,\"1\"),
gsub(/APP/,\"1\"),
gsub(/WSP/,\"1\"),
gsub(/RECOVERY/,\"1\")
}" | \
sed "s/ene/01/g;s/feb/02/g;s/mar/03/g;s/abr/04/g;s/may/05/g;s/jun/06/g;
s/jul/07/g;s/ago/08/g;s/sep/09/g;s/oct/10/g;s/nov/11/g;s/dic/12/g;" \
> notificaciones_bi_202303.csvThe notification system wasn't static — it was actively expanding. When the AWK pipeline was first written, Entel only sent notifications via IVR and SMS. Over 2022, the team rolled out APP push (February), MAIL (June), and WhatsApp (April) — each requiring the same parsed flat file as input. The RUT-K bug silently propagated to every new channel because they all consumed the same upstream extraction.
Every new channel inherited the same blind spot. When APP launched in Feb 2022, it consumed the same parsed file — so ~24% of accounts were already missing from day one. When MAIL launched in June, the same gap followed. The bug wasn't in any single channel's logic; it was in the shared upstream extraction that fed all of them.
An instant +18.7% gain overall. Fixing the string parsing logic instantly restored the missing data flow across all billing cycles (vencimientos). In April 2023, the total volume jumped from ~10M to ~11.9M notifications (+1.87 million clients recovered), directly translating to higher on-time collections and reduced call center load. Notice how the peaks in specific cycles (e.g., the 28th) also normalized due to grouping adjustments.
JOIN operations) during the ingestion phase isn't bureaucracy; it's prevention.
substr($1, 0, length($1)-1) in the AWK script propagated the correction to IVR, SMS, APP, MAIL and WSP simultaneously. This is the argument for keeping transformations at the extraction layer, not duplicating them per channel.
AWK and sed remain invaluable tools for transforming gigabytes of logs ultra-fast at the OS level.
wc -l comparison between input and output would have caught the 24% drop on day one.
sed pipeline converted "ene/feb/mar..." to "01/02/03..." — a brittle approach that breaks on uppercase or abbreviated variants. A locale-aware date parser would have been more robust, but in a legacy BAT/AWK context, the sed chain was the pragmatic fix.
"Data engineering is not just building pipelines; it's defending them from the ground up."
This case demonstrates the ability to dig into legacy scripts, debug low-level data extraction pipelines, and deliver massive, quantifiable business value through rigorous auditing.
Contact Javier