Overview
A consumer beauty and personal care brand needed to bring its Walmart and Amazon supplier and performance data into a single AWS RDS PostgreSQL database. Walmart's data was split across a standard supplier API and three separate reporting portals (Retail Link, Scintilla, and Supplier One), each with its own authentication, file formats, and update cadence.
NeenOpal designed a hybrid ETL platform with two ingestion paths running in parallel: an orchestrated API pipeline for the data Walmart exposes directly, and an AI-driven report retrieval pipeline for the data that fell outside the scope of the client's existing API access. Both paths converge on a shared Glue transformation layer, a common S3 staging area, and a single PostgreSQL database, with a separate one-time Spark job bringing in Amazon Vendor Central's historical data. The result is a single, config-driven system rather than a set of one-off integrations.
- 15
- hrs/wk Manual Portal Downloads Eliminated
- 90%
- Reduction in Time to Onboard a New Report
- 99%
- Load Reliability via Upsert Pattern
- 1
- Source of Truth Replacing Two Disconnected Workflows
Customer Challenges
Before engaging NeenOpal, the client's Walmart and Amazon reporting data had no common integration point, no staging layer, and no repeatable load process, so every report was a manual download followed by ad hoc handling.
No Single Integration Point
Walmart's standard supplier API covers only a subset of reporting data (purchase orders, item catalog). The remaining wide range of reports, including Purchase Orders, OTIF, ASN, Disputes, Forecast, Store Sales & Inventory, and Deductions, exist only as exports behind three separately authenticated portals, each with its own UI, session handling, and export mechanics.
Heterogeneous Cadences, Formats, and Schemas
Reports across the three portals run on different cadences (daily, weekly, monthly) and arrive as CSV or Excel exports with inconsistent structure: non-standard workbook states, embedded summary and grand-total rows, and duplicate column headers that require positional disambiguation rather than name-based mapping.
No Staging Layer or Repeatable Load Process
There was no S3 staging convention, no event-driven trigger, and no transformation layer in place. Every file was a manual download followed by ad hoc handling, with no schema validation step to catch drift before it reached a database.
Amazon Vendor Central Required a Separate, One-Time Migration
Amazon Vendor Central data (catalog, sales, traffic, demographics, market basket, and repeat-purchase reporting) had no relationship to the Walmart pipeline and needed its own ingestion path into the same target database, without duplicating the Walmart-specific logic.
Automating Multi-Portal Retail Reporting
Solutions
NeenOpal built two ingestion paths that share a common downstream architecture, plus a separate historical load job for Amazon.
Path 1: Orchestrated API Ingestion
AWS Step Functions orchestrates scheduled pulls from the Walmart Supplier API using OAuth2 client-credentials authentication. Raw JSON responses land in an S3 Bronze layer, after which a Glue job flattens nested structures, deduplicates records, and loads the result into RDS PostgreSQL using an upsert pattern keyed on natural identifiers, so re-runs never create duplicate rows.
Path 2: AI-Driven Report Retrieval
For data that fell outside the scope of the client's existing API access, NeenOpal built a Claude AI skill that navigates each portal's UI to trigger and download the correct report on its scheduled cadence, and validates the resulting file's structure against a live schema definition in report_config.json before uploading it to a report-specific prefix in the S3 Bronze layer. The retrieval work is split across six parallel subagents spanning the three portals, so one slow or failing download never blocks the rest of the batch; each subagent independently retries and resolves its own click failures.
Event-Driven, Config-Driven Transformation
An S3 PUT event triggers a Lambda function the moment any file lands, which starts a Glue job. The job auto-detects file type from binary content rather than trusting the file extension, applies column cleaning and type casting defined per report in report_config.json, resolves duplicate column names positionally, and strips embedded summary rows before loading into PostgreSQL. Because behaviour is entirely config-driven, onboarding a new report is a JSON entry, not a code change, in either the AI retrieval skill, the Lambda, or the Glue job.
Load-Mode Control and Failure Handling
Control tables track each entity's load mode (FULL or INCREMENTAL) and can be flipped with a single UPDATE statement, with no redeployment required. Any failure in either pipeline, whether an API timeout, a portal login failure, or a schema mismatch, publishes an SNS notification containing the report name, file, and error detail so the team can respond before it affects downstream reporting.
Amazon Vendor Central: A Separate Historical Load
A dedicated Lambda and Glue/Spark job loads Amazon Vendor Central's catalog, sales, traffic, demographics, market basket, and repeat-purchase data into the same PostgreSQL database, landing in the same analytics schema and exposed through the same Silver/Gold view pattern used for Walmart. This keeps both retailers queryable from one place without merging their underlying tables into a single joined schema.
Shared Infrastructure Across Both Pipelines
Both Walmart pipelines share the same supporting services. Amazon S3 holds the Bronze layer for raw landing, partitioned by source and report, while AWS Glue handles transformation, type casting, and loading into Amazon RDS PostgreSQL as the target analytics database. AWS Secrets Manager and Parameter Store hold credentials and runtime configuration, Amazon CloudWatch and EventBridge handle scheduling and monitoring, and Amazon SNS carries failure alerting.
Services
Claude
AWS Step Functions
AWS Lambda
AWS Glue
Amazon S3
Amazon RDS
Amazon EventBridge
Walmart Supplier API
Walmart Retail Link
Walmart Scintilla
Walmart Supplier One
Benefits
Fully Automated Report Retrieval Across Three Portals
Eliminated manual logins and downloads across Walmart Retail Link, Scintilla, and Supplier One. Reports are now retrieved, validated, and uploaded automatically on their correct cadence.
Centralised Reporting Across Walmart and Amazon
Consolidated data from Walmart's standard API, AI-retrieved portal reports, and Amazon Vendor Central data into a single PostgreSQL database, giving the business one place to query performance across both retailers instead of switching between portals.
Immediate, Actionable Failure Alerts
SNS notifications carry enough detail (report, file, error) that the team can diagnose and resolve most failures without digging through logs first.
Operational Control Without Redeployment
Load mode (FULL vs. INCREMENTAL) is controlled through a database table, not application code, so the team can reset or replay a load without a deployment cycle.
Two Ingestion Paths, One Architecture
API-based and AI-driven ingestion share the same S3 Bronze layer, Glue transformation logic, and PostgreSQL target, so the team maintains one architecture instead of two disconnected systems.
Config-Driven Onboarding
New reports, from either pipeline, are onboarded through report_config.json alone. No changes to the AI skill, Lambda, or Glue job code are needed, which keeps the marginal engineering cost of a new report close to zero.
Resilience Built Into the Ingestion Layer
Parallel subagents, per-report retries, upsert-based loads, and schema validation against a live config mean transient failures (a slow portal, a malformed export) are contained and do not require manual intervention to recover from.
Conclusion
NeenOpal delivered a production-grade, event-driven ETL platform that treats AI-based report retrieval as a peer to traditional API ingestion, not a workaround. Config-driven onboarding, upsert-based loads, parallelised retrieval, and automated failure alerting give the client a system that scales to new reports and new retailers without repeated re-engineering.
FAQ
Common questions about AI-augmented ETL and multi-portal retail reporting
What is AI-driven report retrieval, and when is it needed instead of an API?
AI-driven report retrieval uses an AI agent to navigate a supplier portal's user interface, trigger the correct report, and download it on schedule, in place of a programmatic API call. It becomes necessary when a retailer's API covers only part of its reporting surface and the remaining reports exist only as portal exports behind separate logins. Engineered properly, with schema validation, retries, and alerting, it behaves as a first-class ingestion path rather than a brittle workaround.
How does an event-driven ETL pipeline on AWS work?
Rather than running transformations on a fixed timetable and hoping the data has arrived, an event-driven pipeline reacts the moment a file lands. An S3 PUT event triggers a Lambda function, which starts a Glue job that cleans, casts, and loads the data into the target database. This removes the lag between arrival and processing, and it means a late or early file is handled correctly without anyone adjusting a schedule.
What does config-driven onboarding mean for adding a new report?
Config-driven means the behaviour of the pipeline is described in a configuration file rather than written into code. Column cleaning rules, type casting, schema definitions, and retrieval cadence all live in a single JSON file, so adding a new report is a new entry in that file rather than a change to the AI retrieval skill, the Lambda, or the Glue job. The marginal engineering cost of each additional report stays close to zero.
Authors
Manan KumarData Analystin
Case studies
Related case studies
Transforming Retail Operations with Forecasting & BI on AWS QuickSight
A leading Sri Lanka-based retail chain was looking to take its first step towards Business Intelligence (BI) and data-driven decision-making. Operating across multiple locations, they faced inefficiencies in inventory planning, vehicle utilization, and order fulfillment. With no centralized data repository or analytics framework, they lacked visibility into operational bottlenecks and customer demand trends. To optimize their retail operations, they partnered with NeenOpal to implement a data warehousing solution integrated with predictive forecasting and an interactive BI dashboard using Amazon QuickSight.
Read case study
Building a Multi-Capability Retail Customer Support Assistant on Amazon Connect
Our client, a large retail group selling consumer goods, approached NeenOpal for a customer support assistant for its website, along the lines of the conversational shopping assistants the major online marketplaces now offer. The brief arrived as an exhaustive list of capabilities the assistant had to cover, spanning product research, cart building, order tracking, warranty and complaint handling, post-purchase care, and escalation to a human agent. NeenOpal built the assistant as a working proof of concept on Amazon Connect, the first time the team had taken Connect into a customer engagement. A conversational layer handles the dialogue, a governed tool layer gives the assistant controlled access to customer and product information, verified sign-in gates anything account specific, and a handover path passes the full conversation to a live agent when a person is needed. The build went from brief to working demonstration in four weeks.
Read case study
Seamless Migration from Snowflake to Google BigQuery
Our client, a leading US solar services provider enhanced their data ecosystem by switching from Snowflake to Google BigQuery for improved scalability, performance, and centralized access. Over six months, 2000+ tables and 100+ procedures were migrated, overcoming challenges such as text handling and tooling limits. Robust ETL workflows and validations provided a smooth, timely transition.
Read case study
Automating Signed Credit Application PDFs with HubSpot Webhooks and AWS Serverless
Our client, a managed technology services provider, partnered with NeenOpal to automate the business credit application its customers complete when applying for credit terms. Until then the whole process ran over email. The team sent the customer a Word form as an attachment, the customer filled in their details and emailed it back, and someone then filed the completed form against the right account in Salesforce. Every application involved a round of back and forth correspondence, manual handling at both ends, and a record that existed only as a document attached to an account. NeenOpal designed and delivered HubSpot form to PDF automation on AWS, a serverless document automation pipeline that replaced the email exchange with a single link on the client's website. A customer opens the form, enters their details, and signs, and a branded PDF is generated automatically, emailed to the address captured on the form and to the client's own team, and archived in encrypted Amazon S3, while the submission itself is recorded field by field in HubSpot. Nobody sends a form out, chases it back, or files it by hand.
Read case study
Tableau Dashboard Development for a US Insurance Organization: 4 Data Sources Unified
Our client, an insurance organization with field teams spread across every US state, partnered with NeenOpal to replace a manual, spreadsheet-driven reporting process with a consolidated Tableau dashboard environment governed by row-level security. Operational data lived in four separate systems covering Airtable, Salesforce, Google Analytics 4, and Google Search Console, and bringing them together meant exporting and stitching the data by hand, which left leadership without a single reliable view of performance. NeenOpal consolidated all four sources into an Azure SQL Server layer, reviewed and corrected the data quality issues that surfaced along the way, and delivered three Tableau dashboards across seven screens that refresh automatically every day. Row-level security was implemented across all fifty states and the independent districts, driven by an entitlement model the client maintains through a single Google Sheet, so every representative sees the states they are responsible for and nothing more.
Read case study
Unifying Multi-Region Sales Pipeline Analytics with Power BI and Power Query
Our client, a global healthcare company with commercial operations across Africa and Western Europe, partnered with NeenOpal to build a unified sales pipeline dashboard spanning more than six regional teams across two continents. Pipeline data was managed in isolation by each region, reported in inconsistent formats, and pulled from different systems including Salesforce and SAP, leaving leadership without a consolidated view of opportunities, orders, and revenue. NeenOpal designed and delivered a centralized sales pipeline dashboard in Power BI, powered by an automated Excel and Power Query ETL layer, that standardizes data from every region into one live view. The solution tracks each opportunity from lead to shipped order, benchmarks performance against multiple internal targets, forecasts revenue from existing customers, and lets non-technical users refresh the entire report in one to two clicks.
Read case study