What Does a Data Engineer Do When a Dataset Breaks?

What does a data engineer do when a bike-share analyst opens a report and yesterday's trip count is missing? The engineer traces the number back through the table, the code that built it, and the files that supplied the trip records. The fix might be as simple as waiting for a late file, or it might require changing how the code reads timestamps. The job is to make sure people can use the data and tell when it isn't ready.
What does a data engineer do with a new file?
Imagine a bike-share system that records a trip when a rider returns a bike. Each record in this example has a ride ID, a start time, an end time, and a starting station. The operator exports those records to files. An analyst wants a table showing how many trips started at each station each day.
The engineer starts by checking how the files are made. Does each export contain only new rides, or can it include rides sent before? If a ride starts before midnight and ends the next morning, which file contains it? A report grouped by start date cannot assume that every ride in a file started on the same day.
An automated import can save each file unchanged before doing any cleanup. It can also record the file name, arrival time, and import status. Keeping that original copy lets the engineer compare a suspicious value with what the source actually sent. It also makes it possible to rebuild the output after fixing a mistake without asking the operator to send the whole file again.
Checks between the raw file and the report
After importing the file, the engineer loads its rows into a staging table, a work area where data can be checked before anyone relies on it. One check looks at the file itself: are all the required columns present? Another looks at values: can the start and end times be read, and does any ride end before it starts?
Some checks depend on what a field means. If each ride ID is supposed to identify one trip, two identical rows with the same ID should not both count. But if two rows share an ID and disagree on the starting station, deleting whichever row appears second could hide a correction or a source error. The engineer needs a rule for handling that conflict, not just a command that removes duplicates.
Next, the engineer uses SQL to group valid records by start date and station. The published table might have three columns: ride_date, station_id, and trip_count. The engineer checks that each date and station pair appears only once and that the counts add up to the number of accepted trips. If rows were set aside, the import record should say how many and why. A table that adds up can still be incomplete.
The engineer also documents what the table counts. For example, it counts recorded, completed rides by the local date they started. It does not count attempts to rent a bike. A missing station and date combination is not automatically evidence of zero rides, especially if the source has not finished sending records for that day.
A failed job is part of the work
Suppose the operator renames the start time column and the next import stops. The report still shows the last successful update. A useful error message identifies the missing column and the affected file. The engineer checks the raw file and confirms what the new field means with the source owner. A new name might be harmless, but the replacement could also report a different time zone or a different event.
Once the engineer has confirmed the meaning, they update the import code and test it on both an older file and the new one. Then they rerun the failed import. The rerun should not add a second copy of trips already loaded. One way to prevent that is to rebuild the affected portion of the output from checked records and replace the old portion only after the new one passes validation.
Not every problem stops a job. If an export arrives with far fewer records than usual, the code may still run and produce a plausible table. The engineer can flag that drop for review without assuming it is an error. A real change in ridership and a partial export can look similar in the final counts. A visible last-successful-update time also helps the analyst tell stale data from a quiet day.
Build a small pipeline with public data and SQL
Download one recent monthly file from Citi Bike's public system data page. Unzip it, inspect the CSV header, and start with one file and a small sample of rows. Use a local database such as SQLite. The SQLite command-line guide explains how to import a CSV file. Keep the original file and write down which rows you used so you can repeat your test.
- Import the sample into a raw table. Record its row count and check which columns contain the ride ID, start time, and station ID. Use the actual header rather than assuming it matches the example above.
- Make a table called
clean_rideswith one row per accepted ride. Check for repeated ride IDs and missing station IDs. Save a count of the rows you set aside. Deriveride_datefrom the start timestamp after checking its format, and document how you interpret the date. - Run the query below. Pick one station and date, count its matching rows in
clean_rides, and compare that count with the result. Then check that the sum of all groups equals the total number of rows inclean_rides.
SELECT ride_date, station_id, COUNT(*) AS trip_count
FROM clean_rides
GROUP BY ride_date, station_id;
For a recovery test, copy the CSV and remove the start time column from the copy. Make your import instructions or script check for the required header before loading any rows. Confirm that the broken copy fails without replacing the last good result. Run the original sample twice and verify that the counts stay the same. If they double, look for an import step that appends rows instead of rebuilding the sample.
If you want more practice breaking a problem into checks, use the ICSC learning resources or the ICSC training and practice problems. In your project README, leave a short note for the next person who runs the import: where the file came from, which rows the table counts, and what to check first if the update fails.
Frequently asked questions
Is data engineering the same as data analysis?
No. A data engineer builds and maintains the processes that deliver usable data. An analyst uses that data to answer questions, though the work can overlap.
Can I start with just SQL and a CSV file?
Yes. Import a public CSV into SQLite, check the rows, and use SQL to build a grouped table. You can add automation after you can repeat the steps by hand.
Why keep the original file after cleaning it?
The original lets you check what the source actually sent and rerun the process if you discover a mistake in your cleanup rules.



