AWS Builder Center
Build a Serverless Billing Reconciliation Pipeline with Amazon Athena and Amazon Quick

Build a Serverless Billing Reconciliation Pipeline with Amazon Athena and Amazon Quick

Transform manual billing pre-validation from hours of repetitive work into an automated, intelligent pipeline that detects duplicates, validates file integrity, and lets analysts query millions of transactions in natural language.

In enterprise environments that process hundreds of thousands — or even millions — of billing transactions monthly, pre-validation is the critical gate between raw data and accurate invoicing. Yet for many organizations, this step remains stubbornly manual: analysts move files, count records, cross-reference systems, and visually scan for anomalies — all before a single invoice is generated.
The cost is real. A missed duplicate transaction or an undetected date anomaly can cascade into incorrect invoices worth hundreds of thousands of dollars. Meanwhile, skilled analysts spend hours each day on repetitive validation tasks that leave little room for the higher value work they were hired to do.
This post describes how a fictitious financial company "Any Financials" — processing millions of transactions per month across multiple fulfillment systems — can use Amazon Quick to transform their billing pre-validation workflow from a manual, error-prone process into an automated, intelligent pipeline that reducing daily effort from hours to minutes.

The Business Problem

On a continuous basis throughout the month, billing operations teams must validate transaction files generated from multiple fulfillment systems before they flow into downstream billing processing.
The challenges were significant:
  • Volume and complexity: Hundreds of billing files generated across multiple fulfillment centers, containing millions of transaction records per month
  • Manual file movement: Analysts had to manually collect and organize pipe-delimited transaction files arriving from different systems
  • Record count validation: Each file contains a header record declaring the expected transaction count — someone had to verify this matched the actual number of detail records
  • Content integrity checks: Detecting duplicate transaction IDs (particularly those >15 days apart, indicating data errors vs. legitimate invoice adjustments), identifying transactions with dates outside the expected billing window, and verifying structural consistency across files
  • High stakes: A single undetected billing error — for example, 10,000 duplicate transactions at $25 each — translates to a $250,000 invoicing impact for downstream customers
  • Time pressure: All validation had to complete within a tight window before multiple daily billing processing, leaving no margin for error or re-work
The net result: analysts were spending significant daily effort on repetitive, rule-based validation instead of exception handling, root cause analysis, and process improvement — the work that moves the needle.

Solution Architecture

The solution uses a fully serverless pipeline built on four AWS services. Raw billing files land in Amazon S3 as pipe-delimited text from multiple fulfillment centers. Amazon Athena queries these files in place using an external table and SQL views that parse headers and transactions without moving any data. The structured results feed into Amazon Quick Sight via SPICE for interactive dashboards covering KPIs, volume trends, and duplicate detection. Finally, Chat Agent capability in Amazon Quick enables billing analysts to ask natural-language questions, such as "Are there any duplicate transactions?" or "What is the total bill by service type?" — without writing SQL queries explicitly. The entire pipeline requires no ETL jobs, no servers, and no database maintenance.

Figure 1: Serverless Billing Reconciliation Pipeline Architecture
Key design decisions:
  • Flat S3 structure (no partitioning) — simplifies deployment for datasets under 10 GB
  • No Parquet conversion — Athena queries text files directly, trading some performance for operational simplicity
  • View-based parsing — a single SQL view handles all logic for splitting headers from transactions and joining them

The Billing Data

File Format and Structure

Each billing file is a plain-text, pipe-delimited file (.txt) containing two record types:
  • Header record (prefix 01): Contains file metadata — creation timestamp, transaction count, originating fulfillment system, and filename
  • Transaction records (prefix 02): Individual billing events with transaction ID, timestamps, service type, rated amount, and processing status
Record Type
Prefix
Fields
Header
01
record_type
Transaction
02
record_type

Sample Data

Below is an excerpt from a typical billing file:
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

The header declares 43 transaction records in this file. Service types include VOICE, DATA, and SMS. Status values indicate processing state: COMPLETED (normal), ADJUSTED (re-billed), and PENDING (awaiting processing).

S3 Storage Layout

Files are stored flat in a single S3 prefix — no date partitioning required for this deployment:
s3://<your-bucket>/reconciliation/
├── BILLING_TXNS_ATLANTA_20260502_1400.txt
├── BILLING_TXNS_CHICAGO_20260506_0900.txt
├── BILLING_TXNS_DALLAS_20260501_1100.txt
├── BILLING_TXNS_DENVER_20260502_1100.txt
├── BILLING_TXNS_NEWYORK_20260508_1100.txt
└── BILLING_TXNS_SEATTLE_20260503_1400.txt

The naming convention encodes the fulfillment center, date, and hour window:  BILLING_TXNS_{CENTER}_{YYYYMMDD}_{HHMM}.txt 

Deploying the Athena Layer

Three SQL statements create the entire query infrastructure. Run them sequentially in the Athena query editor.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS billing_reconciliation;


Step 2: Create the Raw External Table

This table reads each line from the S3 files as a single STRING column. It serves as the raw input layer.
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'
);


Step 3: Create the Parsed Transactions View

This is the core solution. The view separates header and transaction records, parses pipe-delimited fields and joins them using the  $path  pseudo-column that Athena provides for each row.
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 of this view, you can immediately query structured billing data with standard SQL.

Connecting Quick Sight to the Data

Grant S3 Access

Amazon Quick Sight needs permission to read from your S3 bucket. Navigate to:
  • Amazon Quick Console > Manage Account > Permissions > AWS resources
  • Select your S3 bucket from the list, or add it manually

Figure 2: Quick - Manage Account - Permissions Figure

Figure 3: Select S3 buckets for Quick Sight access
Note: If the S3 policy was modified outside Quick Sight (for example, via Terraform or CloudFormation), you may see an error preventing bucket selection. In that case, edit the IAM policy directly: IAM Console > Policies > AWSQuickSightS3Policy > add your bucket ARN to the Resource array.

Create the Athena Dataset

Create a new dataset in Quick Sight connected to the Athena view:
  • Data source: Athena
  • Workgroup: default (or your custom workgroup)
  • Catalog: AwsDataCatalog
  • Database: billing_reconciliation
  • Table/View: billing_transactions

Configure SPICE Import

Select SPICE as the import mode for optimal dashboard performance. SPICE caches the data in memory, enabling sub-second query responses for visualizations and natural language queries.
  • Import mode: SPICE (recommended)
  • Refresh schedule: Set to daily or on-demand based on file upload frequency

Figure 4: Athena Dataset

Figure 5: Configure SPICE import mode

Enriching the Dataset for AI

Before building dashboards or enabling natural language queries, enrich the dataset with calculated fields, column descriptions, and AI-specific instructions.

Calculated Fields

The raw timestamp strings need to be parsed into proper date fields for time-series analysis. Create two calculated fields in the dataset editor:
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'")

The key detail: wrap the literal T and Z characters in single quotes, so the parser treats them as literals, not format tokens.

Column Descriptions

Add descriptions to critical columns in the dataset editor (Dataset > OUTPUT > select column > update Description). These descriptions help Amazon Q understand the business meaning of each field:
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 (e.g., DALLAS-FULFILL-02).
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).

Figure 6: Adding column descriptions in the dataset editor

Dataset Instructions

Add dataset-level instructions that tell Amazon Q about the business logic for duplicate detection. In the dataset editor, navigate to the Instructions panel and enter:
"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 (invoice corrections, late charges). Duplicates 15 or more days apart are anomalies that require investigation. The service types are VOICE, DATA, and SMS. Status ADJUSTED typically indicates a re-billed or modified transaction."

Figure 7: Dataset instructions panel for Amazon Q context

Building the Dashboard

With the dataset enriched, create a Quick Sight analysis with visuals that surface reconciliation insights immediately.

KPI Tiles (Top Row)

  • Total Transactions (COUNT of all rows)
  • Total Billed Amount (SUM of amount)
  • Unique Files (COUNT DISTINCT filename)
  • Duplicate Rate (percentage of transaction_ids appearing more than once)

Volume and Revenue

  • Bar chart: Transaction count by fulfillment_system_name
  • Donut chart: Transaction count by service_type
  • Line chart: Daily transaction volume over transaction_date

Status and Duplicates

  • Donut chart: Distribution of status (COMPLETED, ADJUSTED, PENDING)
  • Table visual: Duplicate transactions showing transaction_id, occurrence count, fulfillment center, and days apart
  • Conditional formatting: Highlight rows where days_apart >= 15 in red

Figure 8: Executive summary KPIs

Figure 9: Volume and revenue breakdown by center and service type

Natural Language Reconciliation with Amazon Quick

With the dataset configured and Dashboard Q&A enabled, your billing operations team can run reconciliation checks using natural language — no SQL expertise required.

Enabling Dashboard Q&A

Chat Agent feature in Amazon Quick, allows users to ask questions directly from dashboards. Enable it in the dashboard settings and point it to the billing_transactions dataset.

Sample Reconciliation Prompts

Here are proven prompts for common reconciliation tasks:
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
Point to the Quick Sight dataset (select Specific Data under chat > select and Add the billing_transactions dataset) and ask your question.

Figure 10: Natural language query for file validation

Tips for Better Results

  • Use column descriptions and dataset instructions to provide Amazon Q with additional business context
  • Keep prompts specific — "transactions over $1 from Dallas this week" outperforms vague queries
  • For complex custom business logic, use the Amazon Quick's custom chat agent capability to build an agent with domain-specific knowledge

Conclusion and Next Steps

In this post, we deployed a complete billing reconciliation pipeline using four AWS services — no servers to manage, no ETL jobs to schedule, and no database to maintain. The pipeline:
  • Reads raw billing files directly from S3 using Athena external tables
  • Parses and structures data through a SQL view (zero data movement)
  • Surfaces insights through Quick Sight dashboards with automated duplicate detection
  • Enables natural language reconciliation checks via chat agents

Production Considerations

For production deployments processing larger datasets (10 GB or more), consider these enhancements:
  • Parquet conversion via AWS Glue ETL — columnar format significantly reduces scan costs and improves query performance compared to raw text files
  • Date/hour partitioning — enables partition pruning so Athena scans only relevant time ranges
  • Partition indexes — reduces query planning overhead for tables with many partitions
  • Incremental SPICE refresh — loads only new data instead of full dataset reload
  • Amazon EventBridge automation — triggers SPICE refresh automatically when new files land in S3

The Reusable Pattern

This solution demonstrates a repeatable architecture for any organization dealing with high-volume file-based validation workflows:
  • Amazon Quick can automate repetitive, rule-based workflows beyond traditional BI use cases — billing validation is just one example. Any process that involves file ingestion, structural validation, and exception detection can follow this pattern.
  • Combining file automation, dashboards, and agentic AI creates a powerful end-to-end solution — each layer serves a purpose: automation handles the mechanical work; dashboards provide visibility, and AI provides accessibility.
  • Natural language querying reduces the technical barrier — analysts don't need SQL skills or BI tool expertise to interact with their billing data. They ask questions the way they ask a colleague.

Who Should Consider This Approach?

  • Billing operations teams processing high volumes of transaction files from multiple sources
  • Finance organizations with manual pre-validation or reconciliation workflows
  • Any team spending significant analyst time on repetitive file-based checks before downstream processing

Further Reading

About the Authors


Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article