
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_110002|TXN-20260501-1101-DAL|BILLING_TXNS_DALLAS_20260501_1100|DALLAS-FULFILL-02|2026-05-01T10:19:50Z|VOICE|00:28:46|0.219|USD|COMPLETED02|TXN-20260501-1102-DAL|BILLING_TXNS_DALLAS_20260501_1100|DALLAS-FULFILL-02|2026-05-01T10:30:16Z|VOICE|00:30:41|0.198|USD|ADJUSTED02|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 TEXTFILELOCATION '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 ASWITH 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.statusFROM transactions tINNER 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

Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article