What's the Deal With Data Engineers Anyway?

tags.

\ While scraping can be useful, it’s often inefficient and unreliable:

• Some websites use JavaScript to load content dynamically, making it harder to extract.
• Others block scraping attempts using security mechanisms like CAPTCHA.

APIs

In the previous step, we retrieved the longitude and latitude for the cities where the company operates. This data is crucial for making requests to APIs, which are designed to provide structured and reliable data access. Let me explain:

\ An API (Application Programming Interface) is a tool that allows computers or programs to communicate with each other. For instance, I used the OpenWeather API to fetch real-time weather data and the Aerodatabox API for airport and flight details.

\ However, just like accessing a central water supply, using an API often comes with a cost. OpenWeather offers a limited number of free daily requests, while Aerodatabox requires a subscription for extended use. Additionally, APIs use an API Key — a unique code that authenticates requests to ensure that only authorized users can access the service.

\ Of course, many APIs can work directly with city names instead of requiring latitude and longitude. However, it’s valuable to explore multiple approaches and understand how geographic coordinates are used, isn’t it?

\ By making polite requests to the API with our location data, we receive structured responses, like weather forecasts or flight information, ready for analysis.

\

Data Transformation

The next step in ETL is Transformation — cleaning and organizing raw data into a usable format.

Common tasks include:

• Removing unnecessary columns.
• Formatting data for consistency.
• Handling missing values.

\ The most interesting transformation in my project was converting latitude/longitude data from DMS format (e.g., 52°31′12″N) to decimal format (e.g., 52.52000). This made the data compatible with APIs. I wrote a Python function to handle this conversion. In simple terms, it’s like attaching a filter to your tap to get cleaner, better-tasting water.

Data Loading

The final step is Loading the cleaned data into a relational database for easy access and analysis.

\ Why not use Excel or CSV files?

Excel is like having a separate water container for every tap, washing machine, and shower — sufficient for small needs but disconnected and inefficient as your system grows. MySQL, on the other hand, is like a modern plumbing system that delivers water to every part of the house seamlessly and reliably. It’s scalable, efficient, and keeps everything linked together.

\ In this project, I used a MySQL database with tables for cities, population, weather, airports, and flights. After defining the schema and relationships between tables using SQL, I wrote Python functions to insert data using SQLAlchemy and pandas. Now, the database snapshot looks well-organized and ready for use.

Automation

So far, I’ve executed the pipeline manually using Python, collecting all the necessary data into the database. Again, it is a significant piece of programming code that executes sequentially and performs various operations, such as creating a connection, retrieving data, or pushing a table to an SQL database.

\ Key Pipeline Function:

def extracting_and_loading_data(list_of_cities): connection_string = create_connection_string() cities_df = get_cities_data(list_of_cities) cities_to_db = transform_cities_df(cities_df) push_unique_data_to_db(cities_to_db, 'cities', connection_string) cities_from_sql = extract_from_db('cities', connection_string) population_to_db = transform_population(cities_df, cities_from_sql) push_unique_data_to_db(population_to_db, 'population', connection_string) weather_df = get_weather_data(cities_from_sql) push_data_to_db(weather_df, 'weather', connection_string) airports_df = get_airports_data(cities_from_sql) airports_to_db = transform_airports_df(airports_df) push_unique_data_to_db(airports_to_db, 'airports', connection_string) airports_from_sql = extract_from_db('airports', connection_string) flights_df = get_flights_data(airports_from_sql) push_data_to_db(flights_df, 'flights', connection_string) return "Data has been updated" extracting_and_loading_data(["Berlin", "Hamburg", "Munich", "Cologne", "Stuttgart", "Leipzig", "Dortmund", "Vienna"])

\ But automation is essential for maintaining up-to-date data. While some information (e.g., city population) rarely changes, weather and flight data need regular updates. To automate the pipeline:

1. Scheduling: Use a job scheduler (e.g., cron) to run the Python script daily.
2. Notifications: Send alerts via Slack, Telegram or email if the pipeline succeeds or fails.

\ Once these steps are complete, monitoring the system and fixing issues as they arise will ensure the process remains smooth.

Conclusion

This project showcases the foundational role of a data engineer in building a reliable data pipeline, much like setting up the plumbing in a house. From extracting data through web scraping and APIs to transforming it into usable formats, and finally loading it into a relational database, each step ensures that the “utilities” (data) flow smoothly for analysts and decision-makers.

\ We explored how to collect weather and flight data to help an e-scooter company predict usage patterns and respond to challenges like weather changes or tourist activity. The pipeline automates these processes, ensuring the data is updated daily and accessible to everyone in the company.

\ This isn’t the end of the journey — it’s the beginning. The groundwork laid by data engineers allows data analysts and scientists to dive deeper, uncover insights, and build predictive models. Together, these roles drive data-driven decision-making.

\ If you’re curious about the technical details or want to explore the code behind this project, feel free to visit my GitHub repository here. There, you’ll find all the scripts, functions, and documentation to help you dive deeper into the project.

\ If I’ve managed to make these concepts clear and relatable, feel free to share your thoughts or ask questions — I’d love to know if this article has inspired you to explore the world of data engineering!