Odds Importer: Building a Mission-Critical Data Pipeline

The Odds Importer represents one of the most challenging and successful infrastructure projects I've architected—transforming a chaotic collection of fragile scripts into a robust, observable, and scalable platform processing tens of millions of odds updates daily with 99.99% uptime.

The Crisis

In 2017, our sports betting platforms depended on a collection of hastily-written scripts that:

• Failed frequently with no monitoring or alerting
• Processed data inconsistently leading to inaccurate odds
• Couldn't handle scale as we added more sportsbooks
• Lacked observability making debugging nearly impossible
• Required manual intervention multiple times daily

The business impact was severe: revenue loss from stale odds, customer complaints about inaccurate data, and engineering team burnout from constant firefighting.

The Transformation

From Chaos to Architecture

Rebuilt the entire system with enterprise-grade practices:

• Ruby on Rails as the core orchestration framework
• Elasticsearch for real-time search and analytics
• Node.js microservices for high-throughput data processing
• PostgreSQL and SQL Server for reliable data persistence
• Apache Kafka for event streaming and data distribution
• Docker containerization for consistent deployments
• AWS infrastructure for scalability and reliability

Observability First

Implemented comprehensive monitoring from day one:

• Real-time dashboards showing system health and performance
• Automated alerting for anomalies and failures
• Distributed tracing for end-to-end request monitoring
• Performance metrics tracking latency and throughput
• Business metrics monitoring data freshness and accuracy

Technical Architecture

Core Infrastructure Components

The system is built around four critical components:

SQL Server Database (Single Source of Truth)

• Centralized data store containing all normalized odds and betting data
• Master repository for historical data and current market states
• Performance optimized for high-volume write operations
• Reliable foundation ensuring data consistency across all systems

Advision Pipeline Downloader

• Multi-source ingestion downloading feeds from all sportsbook types
• Rate-limited requests respecting individual API constraints
• Fault-tolerant downloads with retry logic and error handling
• Format-agnostic collection handling XML, JSON, CSV, and custom formats

Advision Pipeline Importer

• Data transformation engine normalizing disparate feed formats
• Business logic processor applying validation and enrichment rules
• High-throughput inserter efficiently writing processed data to SQL Server
• Quality assurance ensuring data integrity before database commits

Real-Time SQL Server ↔ PostgreSQL Synchronization

• Live data replication keeping PostgreSQL current with SQL Server changes
• Backend API optimization providing fast read access for odds applications
• Minimal latency sync ensuring near-instantaneous data availability
• Bidirectional updates maintaining consistency across both databases

Data Flow Architecture

Key Innovations

Intelligent Rate Limiting

Developed sophisticated API management:

• Per-sportsbook rate limiting respecting individual API constraints
• Adaptive throttling responding to API performance changes
• Priority queuing ensuring critical updates process first
• Burst handling managing sudden data volume spikes

Performance Optimization

Achieved remarkable scale improvements:

• Parallel processing handling multiple sportsbooks simultaneously
• Intelligent caching reducing redundant API calls
• Batch optimization grouping updates for efficient processing

Operational Excellence

Deployment Strategy

Implemented zero-downtime deployment practices:

• Blue-green deployments for seamless service updates
• Automated rollbacks for quick recovery from issues
• Environment parity ensuring consistency across development, staging, and production

Monitoring and Alerting

Created comprehensive observability:

• Technical metrics: Latency, throughput, and error rates
• Infrastructure monitoring: CPU, memory, and network performance
• Custom dashboards for different stakeholder needs (engineering, business, operations)

Documentation and Runbooks

Established operational procedures:

• Architecture documentation for system understanding
• Runbook procedures for common operational tasks
• Incident response guides for quick problem resolution
• Onboarding materials for new team members

Remarkable Results

Reliability Transformation

Achieved extraordinary uptime and performance:

• 99.99% uptime from previous ~90% availability
• 50M+ daily updates processed reliably
• Sub-second latency for critical odds updates
• Zero data loss through robust error handling and recovery

Technical Challenges Overcome

API Integration Complexity

Managing relationships with 50+ sportsbook APIs:

• Authentication variations handling OAuth, API keys, and custom auth
• Rate limit diversity respecting different throttling strategies
• Data format inconsistencies normalizing varied response structures
• Reliability differences adapting to varying API quality levels

Scale and Performance

Handling massive data volumes efficiently:

• Memory optimization processing large datasets within resource constraints
• Database performance optimizing queries for high-throughput operations
• Network efficiency minimizing bandwidth usage while maximizing throughput
• Resource allocation balancing processing power across different data sources

Lessons Learned

Technical Architecture Insights

Distributed System Trade-offs: The initial importer architecture was designed as a distributed system with separate services for downloading and processing data. While this provided good separation of concerns, it created unexpected cost challenges:

• Data transfer costs: Communication between services across AWS zones became extremely expensive at scale
• Redis storage overhead: Storing massive amounts of JSON data in Redis for inter-service communication required oversized instances
• Unnecessary complexity: The distributed approach added operational overhead without proportional benefits

Architecture Redesign Learnings: If rebuilding today, I would consolidate into a single service architecture:

• Single process approach: Download, process, and ingest all within one service to eliminate inter-service data transfer
• Direct database writes: Skip Redis intermediate storage and write directly to SQL Server
• Massive cost reduction: Eliminate cross-zone data transfer fees and reduce Redis infrastructure needs
• Simplified operations: Fewer moving parts means easier debugging and maintenance

Monitoring vs. Distribution: The original motivation for service separation was visibility into potential problems. However, the real solution was better observability:

• Enhanced telemetry: Comprehensive metrics and logging within a single service provides the same visibility
• Better monitoring: Proper instrumentation eliminates the need for service separation for debugging
• Operational simplicity: One well-monitored service beats multiple loosely-coupled services

Key Technical Takeaways

• Premature optimization: Don't distribute until you actually need to
• Cost awareness: Consider data transfer costs early in AWS architecture decisions
• Observability first: Good metrics solve more problems than service separation
• Simplicity wins: The simplest architecture that meets requirements is usually the best

The Odds Importer project represents more than technical achievement—it demonstrates how thoughtful architecture, operational excellence, and team collaboration can transform critical business infrastructure from a liability into a competitive advantage. The platform continues to reliably serve millions of users daily, enabling the success of multiple sports betting platforms while providing a foundation for future innovation.