Focal Revenue
How MEV Built a Serverless Hotel Data Pipeline with AWS Lambda and Step Functions
Client:
Focal Revenue
Focus:
Serverless data ingestion / File conversion / Workflow orchestration / PMS data normalization
Technologies:
Python / AWS Lambda / AWS Step Functions / Amazon EventBridge / Amazon S3 / Amazon Athena / AWS Glue Data Catalog / Apache Parquet / Amazon RDS for PostgreSQL
[ about the client/ ]
Focal Revenue’s analytics product depended on hotel data received from multiple property management systems.
The sources differed in structure, delivery schedule, and file format, so the platform needed a repeatable way to convert and normalize each data stream before it could be used for reporting.
What Needed to Be Done
Focal Revenue needed to process hotel data arriving through APIs, SFTP, email reports, and S3 uploads. Depending on the integration, incoming files could include XML, CSV, JSON, XLSX, TXT, or zipped DBF exports.
Each source required its own preparation and transformation rules, but the output had to match the common format expected by the analytics database.
MEV needed to separate ingestion, conversion, querying, normalization, database writes, and snapshot creation into a pipeline that could run according to individual property schedules and expose failures at each processing stage.
Each source required its own preparation and transformation rules, but the output had to match the common format expected by the analytics database.
MEV needed to separate ingestion, conversion, querying, normalization, database writes, and snapshot creation into a pipeline that could run according to individual property schedules and expose failures at each processing stage.
Why It Mattered
A single, tightly coupled ETL process would become harder to extend as Focal Revenue added properties and PMS integrations.
Differences between sources included:
- how files were delivered
- which files were required for a build
- how frequently data arrived
- how reservations and allotments were represented
- which source-specific normalization rules had to be applied
Without a structured orchestration layer, adding an integration could affect unrelated processing logic. Failed builds would also be harder to locate and recover because ingestion, transformation, and database operations would be handled as one process.
What MEV Built
MEV built a serverless ingestion and normalization pipeline using AWS Lambda, Amazon EventBridge, AWS Step Functions, Amazon S3, Amazon Athena, and the AWS Glue Data Catalog.
Amazon S3 stored incoming and processed files. S3 events triggered Lambda functions that mapped the source to the relevant hotel, detected the integration and file type, and converted files into Parquet or CSV where required.
Amazon EventBridge initiated property-specific processing according to configured schedules. AWS Step Functions selected the appropriate integration workflow and coordinated choice, wait, iteration, sequential, and parallel processing paths.
Athena queried processed files in S3 using property-specific tables registered in the AWS Glue Data Catalog. Python functions then applied common transformation rules together with logic required for the individual PMS.
Normalized records were written to the analytics database, followed by the creation of reporting snapshots.
Amazon S3 stored incoming and processed files. S3 events triggered Lambda functions that mapped the source to the relevant hotel, detected the integration and file type, and converted files into Parquet or CSV where required.
Amazon EventBridge initiated property-specific processing according to configured schedules. AWS Step Functions selected the appropriate integration workflow and coordinated choice, wait, iteration, sequential, and parallel processing paths.
Athena queried processed files in S3 using property-specific tables registered in the AWS Glue Data Catalog. Python functions then applied common transformation rules together with logic required for the individual PMS.
Normalized records were written to the analytics database, followed by the creation of reporting snapshots.
Technical Flow
01:
Receive and store source data
Hotel data arrived through APIs, SFTP servers, email processed through AWS SES, and direct S3 uploads. Incoming files were placed in source-specific S3 locations.
02:
Detect the hotel and file format
An S3 event triggered AWS Lambda. The function mapped the incoming source to the correct hotel identifier and determined which PMS integration and file-processing rules applied.
03:
Convert files for downstream processing
Lambda functions converted source files into Parquet or CSV where required. Parquet became the standard for newer integrations, while existing CSV and JSON streams could continue without unnecessary conversion.
Processed files were saved to a target S3 bucket.
Processed files were saved to a target S3 bucket.
04:
Start the property-specific workflow
Amazon EventBridge triggered a Lambda function according to the schedule configured for the hotel and integration.
The function selected and started the corresponding AWS Step Functions workflow.
The function selected and started the corresponding AWS Step Functions workflow.
05:
Query and normalize the data
Amazon Athena queried the processed files in S3 using metadata from the AWS Glue Data Catalog.
Python functions retrieved the query results and applied:
- rules shared by all integrations
- PMS-specific transformations
- reservation and allotment processing logic
- validation required by the common application format
- use the normalized data for property and portfolio reporting
Normalized output was saved back to S3 before the final database steps.
06:
Write records and create snapshots
A common ingestion function wrote the normalized rows to the SQL analytics database. The final workflow step generated snapshots used for reporting and historical analysis.
The database functions expected a precise common structure, so source-specific differences had already been resolved earlier in the pipeline.
The database functions expected a precise common structure, so source-specific differences had already been resolved earlier in the pipeline.
07:
Monitor individual processing steps
Step Functions exposed the status of each workflow state instead of treating the entire pipeline as a single job.
CloudWatch logs, Slack alerts, source-file reports, data-validation reports, and failed-task queues helped the team identify whether a problem occurred during ingestion, conversion, normalization, or database processing.
CloudWatch logs, Slack alerts, source-file reports, data-validation reports, and failed-task queues helped the team identify whether a problem occurred during ingestion, conversion, normalization, or database processing.
Outcome
Focal Revenue gained a repeatable serverless architecture for moving source-specific hotel data into its analytics platform.
The pipeline made it possible to:
- add PMS-specific workflows while retaining common processing steps
- convert legacy file formats into analytics-ready Parquet or CSV
- schedule builds separately for each property and integration
- coordinate conditional and parallel processing through Step Functions
- query S3 data without first loading every intermediate file into a database
- write consistently structured records into the analytics database
- isolate and investigate failed processing stages
- generate daily snapshots for reporting and historical analysis
This data-processing foundation supported the wider platform as it expanded from its early hotel network to about 400 hotels across 15 management groups. The client reported that system performance and integrations kept pace as the platform grew.
This case is part of the full case study
MEV Built Focal Revenue’s Hotel Revenue Intelligence Platform[ portfolio/ ]
