How I migrated streaming sensor data from a simulated on-premise database into a cloud warehouse in near real time and everything that broke along the way.
Many organizations still run operational systems on on-premises databases, but they need real-time analytics in the cloud. Running analytical queries directly against production databases impacts application performance, making it unsuitable for reporting and dashboards.
The goal of this project was to build a scalable pipeline that continuously replicates IoT sensor data from PostgreSQL into Snowflake with minimal latency while keeping the operational database isolated from analytical workloads.
Solution Overview
The pipeline simulates GPS-enabled IoT devices sending location updates through AWS IoT Core. Events are streamed into Kafka, stored in PostgreSQL, captured using Debezium CDC, and delivered into Snowflake for analytics.
The final architecture includes:
- AWS IoT Core for device ingestion
- AWS Lambda for event processing
- Apache Kafka as the streaming backbone
- PostgreSQL as the operational database
- Debezium for Change Data Capture
- Snowflake as the cloud data warehouse
- dbt for Medallion transformations
- Streamlit for real-time dashboards
Why CDC Instead of Polling?
One of the biggest design decisions was choosing Change Data Capture (CDC) instead of scheduled database polling.
Traditional polling:
- Adds unnecessary database load
- Requires timestamp tracking
Debezium reads PostgreSQL's Write-Ahead Log (WAL), allowing every insert, update, and delete to be streamed with minimal overhead. This makes CDC a much more reliable solution for operational databases.
Engineering Challenges
Building the pipeline involved several real-world challenges:
- Converting schema-less JSON into Kafka Connect-compatible messages
- Handling Debezium's decimal and timestamp formats
- Managing connector failures caused by malformed events
- Configuring Snowflake roles and permissions correctly
- Recovering Kafka Connect state after connector corruption
- Maintaining reliable connectivity between AWS services and self-hosted infrastructure
Each issue improved my understanding of distributed systems and highlighted the importance of monitoring, schema management, and fault isolation.
Production Improvements
Although the project is fully functional, a production deployment would include:
- Amazon MSK instead of self-managed Kafka
- Amazon RDS for PostgreSQL
- Dead Letter Queues (DLQs)
- CloudWatch or Prometheus monitoring
- Infrastructure as Code using Terraform
- Secrets Manager for credentials
- Multi-AZ deployment and disaster recovery
- Auto Scaling for streaming components
Key Takeaways
This project reinforced several important engineering principles:
- CDC is significantly more efficient than polling for real-time synchronization.
- Streaming platforms like Kafka decouple systems and improve resilience.
- Data quality should always be validated before transformations.
- Production systems require observability, fault tolerance, and least-privilege security from the beginning.
Final Thoughts
This project helped me better understand how modern organizations move operational data into cloud analytics platforms without impacting production systems. More importantly, it demonstrated how technologies like Kafka, Debezium, Snowflake, and AWS services work together to build scalable, event-driven data platforms.
The implementation reflects the same architectural patterns used in production environments, while also highlighting the practical challenges encountered during development and the lessons learned from solving them.
Loading article