Use Case and Problem
Enterprise billing operations teams processing hundreds of thousands of transactions monthly face a persistent challenge: validating transaction files before they flow into downstream invoicing. Analysts manually collect pipe-delimited files from multiple fulfillment systems, verify record counts, detect duplicate transaction IDs, and check for date anomalies — all under tight processing windows. A single undetected duplicate (for example, 10,000 duplicate transactions at $25 each) translates to a $250,000 invoicing impact.
This article shows how to build a fully serverless billing reconciliation pipeline using Amazon S3, Amazon Athena, Amazon Quick Sight, and Chat Agent capability in Amazon Quick — reducing daily validation effort from hours to minutes with zero infrastructure to manage.
How to Solve the Problem
Prerequisites
- An AWS account with access to Amazon S3, Amazon Athena, and Amazon Quick Sight
- An S3 bucket to store billing transaction files
- Quick Sight Enterprise edition (required for Chat Agent features)
Solution Architecture
The pipeline uses four AWS services in sequence:
| Service | Role |
|---|---|
| Amazon S3 | Stores raw billing transaction files from fulfillment systems |
| Amazon Athena | SQL engine that reads S3 files directly via external tables and views |
| Amazon Quick Sight | Imports structured data into SPICE for dashboards |
| Amazon Quick (Chat Agent) | Enables natural language queries for ad-hoc reconciliation |
Key design decisions:
- Flat S3 structure (no partitioning) — simplifies deployment for datasets under 10 GB
- No Parquet conversion — Athena queries text files directly, trading performance for simplicity
- View-based parsing — a single SQL view handles header/transaction splitting and joining
Step 1: Prepare the Billing Data
Each billing file is a pipe-delimited .txt file with two record types:
| Record Type | Prefix | Fields |
|---|---|---|
| Header | 01 | record_type | file_created_datetime | total_transaction_count | fulfillment_system_name | filename |
| Transaction | 02 | record_type | transaction_id | header_link | fulfillment_system_name | transaction_datetime | service_type | duration | amount | currency | status |
Sample data:
01|2026-05-01T11:00:10Z|43|DALLAS-FULFILL-02|BILLING_TXNS_DALLAS_20260501_1100
02|TXN-20260501-1101-DAL|BILLING_TXNS_DALLAS_20260501_1100|DALLAS-FULFILL-02|2026-05-01T10:19:50Z|VOICE|00:28:46|0.219|USD|COMPLETED
02|TXN-20260501-1102-DAL|BILLING_TXNS_DALLAS_20260501_1100|DALLAS-FULFILL-02|2026-05-01T10:30:16Z|VOICE|00:30:41|0.198|USD|ADJUSTED
02|TXN-20260501-1103-DAL|BILLING_TXNS_DALLAS_20260501_1100|DALLAS-FULFILL-02|2026-05-01T10:14:46Z|DATA|00:00:00|3.986|USD|PENDING
Upload files to a flat S3 prefix using the naming convention BILLING_TXNS_{CENTER}_{YYYYMMDD}_{HHMM}.txt:
s3://<your-bucket>/reconciliation/
Step 2: Deploy the Athena Layer
Run these three SQL statements sequentially in the Athena query editor.
Create the database:
CREATE DATABASE IF NOT EXISTS billing_reconciliation;
Create the raw external table:
CREATE EXTERNAL TABLE IF NOT EXISTS billing_reconciliation.billing_raw (
line STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim' = '\n',
'serialization.format' = '\n'
)
STORED AS TEXTFILE
LOCATION 's3://<your-bucket>/reconciliation/'
TBLPROPERTIES (
'skip.header.line.count' = '0'
);
Create the parsed transactions view:
CREATE OR REPLACE VIEW billing_reconciliation.billing_transactions AS
WITH raw_with_meta AS (
SELECT
line,
SUBSTR(line, 1, 2) AS record_type,
"$path" AS source_file
FROM billing_reconciliation.billing_raw
WHERE line IS NOT NULL AND LENGTH(TRIM(line)) > 0
),
headers AS (
SELECT source_file,
SPLIT_PART(line, '|', 5) AS filename,
SPLIT_PART(line, '|', 2) AS file_created_datetime,
CAST(SPLIT_PART(line, '|', 3) AS INTEGER) AS total_transaction_count,
SPLIT_PART(line, '|', 4) AS fulfillment_system_name
FROM raw_with_meta WHERE record_type = '01'
),
transactions AS (
SELECT source_file,
SPLIT_PART(line, '|', 2) AS transaction_id,
SPLIT_PART(line, '|', 3) AS header_link,
SPLIT_PART(line, '|', 4) AS fulfillment_system_name,
SPLIT_PART(line, '|', 5) AS transaction_datetime,
SPLIT_PART(line, '|', 6) AS service_type,
SPLIT_PART(line, '|', 7) AS duration,
CAST(SPLIT_PART(line, '|', 8) AS DOUBLE) AS amount,
SPLIT_PART(line, '|', 9) AS currency,
SPLIT_PART(line, '|', 10) AS status
FROM raw_with_meta WHERE record_type = '02'
)
SELECT
h.filename,
h.file_created_datetime,
h.total_transaction_count,
h.fulfillment_system_name,
t.transaction_id,
t.transaction_datetime,
t.service_type,
t.duration,
t.amount,
t.currency,
t.status
FROM transactions t
INNER JOIN headers h ON t.source_file = h.source_file;
After creation, you can immediately query structured billing data with standard SQL — no ETL required.
Step 3: Connect Quick Sight to the Data
-
Grant S3 access: Quick Sight Console → Manage Account → Permissions → AWS Resources → select your S3 bucket
-
Create Athena dataset: Data source: Athena → Catalog: AwsDataCatalog → Database:
billing_reconciliation→ View:billing_transactions -
Configure SPICE import: Select SPICE for sub-second dashboard performance. Set refresh to daily or on-demand.
Note: If the S3 policy was modified outside Quick Sight (e.g., via Terraform), edit the IAM policy directly: IAM Console → Policies → AWSQuickSightS3Policy → add your bucket ARN to the Resource array.
Step 4: Enrich the Dataset for AI
Calculated fields — parse timestamps for time-series analysis:
transaction_date: parseDate(transaction_datetime, "yyyy-MM-dd'T'HH:mm:ss'Z'")
file_created_date: parseDate(file_created_datetime, "yyyy-MM-dd'T'HH:mm:ss'Z'")
Column descriptions — help the Chat Agent understand business meaning:
| Column | Description |
|---|---|
| transaction_id | Unique identifier for each billing transaction. Same ID in multiple files indicates a duplicate. |
| filename | Unique file identifier. Each file contains transactions from one fulfillment center for one hour window. |
| fulfillment_system_name | Regional processing center that originated the transactions. |
| status | COMPLETED = normal, ADJUSTED = re-billed/modified, PENDING = awaiting processing. |
| amount | Rated charge in USD for the transaction. |
| service_type | Type of billable service: VOICE (calls), DATA (bandwidth), SMS (messages). |
Dataset instructions — add to the Instructions panel:
“Each file has one header and multiple transaction records. The filename is unique per file. A transaction_id can appear in multiple files — when it does, it indicates a duplicate transaction. Duplicates within 14 days are normal business adjustments. Duplicates 15 or more days apart are anomalies requiring investigation.”
Step 5: Build the Dashboard
Create a Quick Sight analysis with these visual groups:
KPI tiles: Total Transactions, Total Billed Amount, Unique Files, Duplicate Rate
Volume & Revenue: Bar chart (count by fulfillment center), Donut (by service type), Line chart (daily volume)
Status & Duplicates: Status distribution donut, Duplicate transactions table with conditional formatting (red for days_apart ≥ 15)
Step 6: Enable Natural Language Reconciliation
Enable Chat Agent on the dashboard and point it to the billing_transactions dataset. Your team can now run reconciliation checks in plain language:
| Reconciliation Task | Natural Language Prompt |
|---|---|
| File count validation | How many files did we receive from each fulfillment center? |
| Duplicate detection | Which transaction IDs appear in more than one file? |
| Revenue summary | What is the total billed amount by service type? |
| Status audit | Show me all ADJUSTED transactions over $1.00 |
| Volume anomalies | Which day had the highest transaction volume? |
| Center comparison | Compare average transaction amounts across fulfillment centers |
Tips for better results:
- Use column descriptions and dataset instructions to provide business context
- Keep prompts specific — “transactions over $1 from Dallas this week” outperforms vague queries
- For complex custom logic, build a dedicated Chat Agent with domain-specific knowledge
Conclusion
This walkthrough deployed a complete billing reconciliation pipeline — from raw S3 files to natural language queries — using four AWS services with zero servers, no ETL jobs, and no database maintenance. The pattern is reusable for any high-volume, file-based validation workflow where analysts need automated checks and self-service access to their data.
For production deployments (10 GB+), consider adding Parquet conversion via AWS Glue, date/hour partitioning, and EventBridge-triggered SPICE refresh.
Further reading:
Author Bio
Nitish Damodar Chaudhari is a Sr. Solutions Architect at AWS, focused on helping enterprise customers accelerate cloud adoption and unlock business value through data and AI.
Deepak Singh is a Sr. GenAI/ML Specialist Solutions Architect at AWS, helping customers design intelligent data pipelines and AI-powered analytics solutions.