What It Actually Takes to Import Data Into a Live Database
Multiple data providers, a domain specific taxonomy, and a rollback plan which caught me off-guard after the migration completed.
I was asked to add a couple thousand books to a live catalog serving a production website. While it seems simple enough on paper to be ticked off my to-do list within an afternoon with some clever scripting, it's just hitting some APIs, inserting rows and done. I thought so too until in practice, it took me 4 providers, a genre taxonomy rewrite, and a rollback plan that failed its own assumptions before even a single new book was added to production.
Trusting the Data
Sourcing the data for a couple thousand books sounded like a straightforward affair. Google Cloud offered a Books API, but no data on historical book popularity trends. NYTimes offered a historical weekly best-seller list going back to 2017, but some information was missing, and OpenLibrary provided monthly data dumps of their entire catalog, but the goal was not to become a mirror of OpenLibrary.
With all the constraints considered, and the fact that a very similar pipeline will also feed an on-demand path for user imports (more on this later), I needed a robust mechanism of fetching arbitrary books, either through stable identifiers (ISBN) or through semantics like the Title and the name of the author.
Considering these constraints, I decided to use the NYTimes historical popularity data as the criteria for seeding the database with popularly read books.
Hardcover, another book reading service which offers a GraphQL API was also a consideration for selecting the most popular books, and I also downloaded around 1 lakh books from their API ranked on the basis of their popularity, although, while performing a sanity check, the top work which showed up was Harry Potter & The Philosopher's Stone, in Spanish. This made me question the relevance of the popularity data on the platform, and I put the collected data aside for later use. I also learned about how a single work (like Harry Potter & The Philosopher's Stone), may have multiple editions (paperback, hardcover, Spanish etc.) and all of them also have different ISBNs.
With all 4 providers accounted for, a quick clarifications on how things stand as of now. As of writing this, the pipeline has sourced over 5,000 books from the NYT, enriched roughly 3,500 of them and 2,000 books are pushed to production. Hardcover's 100,000 books are still touching untouched, waiting for a later pass once the initial seeding stage is done. Quality first, volume later.
External APIs are Someone Else's Database
Using four providers meant four different opinions about what a books metadata should look like. Some providers had description missing, and some duplicated across languages. Author names could show up in a variety of ways depending on the source. Publishers were sometimes absent entirely. Page counts occasionally came back as zero, or as strings instead of numbers.
None of these was a bug from any providers end, that's just what happens when data is aggregated from publishers, libraries and crowd sourced catalogs which were never meant to be an authoritative source of truth or be inter-compatible. The importer had to assume every field could be wrong, missing, or malformed, and handle each case explicitly instead of trusting the shape of the response.
A few of the cleanup steps which had to be done before anything touched the database were:
normalize_isbn(raw) -> strip hyphens, validate checksum, prefer ISBN-13
coalesce_author(record) -> fallback chain across providers, else "Unknown"
sanitize_page_count(n) -> reject non-positive or non-numeric values
resolve_language(record) -> default to English if unspecified
This was just defensive programming applied to a domain where the API returning valid JSON is completely unrelated to the data actually being usable.
Genre Normalization
The genre problem was more interesting than expected. Google Books and most other providers report categories using BISAC codes, a publishing-industry taxonomy that doesn't map cleanly onto how the platform organizes books for readers. A BISAC category like BODY, MIND & SPIRIT doesn't mean anything to a reader browsing by genre. It needed to become something like Mythology & Spirituality. TRUE CRIME became Thriller & Mystery. While some mappings were obvious, others required me to consult the business team to clarify the mappings.
I was initially planning to let any genres outside the defined ones default to the Others genre, I decided to log anything the importer didn't recognize during dry runs and left it for manual review. A surprising number of "missing" records were the same genres expressed differently, such as Young Adult Fiction instead of simply Fiction.
After this step, the number of genres went down from around a dozen to a couple of really obscure ones, which could then be cleanly sorted into the Others bucket.
Trusting Book Identity
The same book can show up from four different providers with four different ISBNs, due to the whole works, titles and editions receiving different ISBNs and no obvious way to tell a script "these are the same book." Right now, the importer relies on ISBN-13 matching, both within the platform's own catalog and when merging records across providers.
This is also the exact problem the Goodreads TBR import feature has to solve in real time. A user uploads their export, and for every book not already on the platform, the system fetches it on demand from Google Books, falling back to OpenLibrary if needed. A confidence score then decides whether to add the book automatically or ask the user to confirm the match themselves. The bulk importer gets to be patient and let a human review its dry-run report before committing anything. The TBR import doesn't get that luxury, it has to decide instantly whether "Dune" from a user's shelf and "Dune" already in the database are the same book, with no dry run and no second chance to ask later, only the user standing there waiting for an answer.
Title and author fuzzy matching, to catch the cases ISBN matching misses, is still future work.
Trusting the Importer
Sourcing clean data was one half of the story, actually importing it into a production database which had real data of users, while ensuring the foreign key and unique constraints, across various tables like books, authors and book_authors were respected was the real challenge.
Due to this, the import script was optimized for confidence, rather than speed. Some of the safety measures implemented in the importer are mentioned below:
Each row got its own savepoint. If a single record failed due to any malformed field, that one row could be rolled back individually and logged to the console instead of taking down all the other books successfully imported before it.
A dry run was implemented, in which, instead of a simulation which could miss out on real database quirks, a real DB transaction was opened, in which actual authors, books were added, genres mapped, and printed detailed statistics reporting what it did. Then the entire session was rolled back. The dry run exercised the whole import path without leaving any visible change in the database, while simultaneously allowing me to analyze what all changes would persist in the DB in the actual run.
The detailed statistics at the end of the import mattered more than the import itself, as it gave a quick overview of the commonly asked questions to determine if it was safe to run for real. Things like:
How many books would be inserted
The number of rows before and after the insert
How many duplicates were found
How many, and which genres came back unmapped.
Any uncaught exceptions (were included in log lines)
Working with a production DB
While I initially planned to directly open a new connection and transaction on the production database while it was serving traffic, since it was only writing data to the rows which would only be read by general traffic, I ultimately chose the conservative route.
I took a dump of the database, stopped accepting new connections on the backend, ran the import, and had the dump to recover from in case things went horribly wrong.
What I did not realize while doing so though, was that not accepting new HTTP requests did not mean that the DB traffic from the application dies down. Background workers were running, scheduled tasks were firing, and analytics jobs could be running. A restore from before the import would have thrown away every write from these processes made in the meantime, which had nothing to do with the import itself.
Google Books also added a lesson in a similar category, albeit in a smaller way. It has excellent metadata, the best ISBN coverage out of any provider discussed here, but it caps usage at 1k requests a day, and the workflow for requesting a quota increase is broken. This is why enrichment happens daily instead of all at once, and the architecture doesn't assume that any single provider will always be available on demand. I am currently sticking to Google Books since they have the best quality data, and the import schedule doesn't have any hard deadlines.
Whats left
A few things which are left unfinished:
Title and author fuzzy matching, extending the confidence score logic already running in the TBR importer to work with the bulk import pipeline.
A proper maintenance mode which actually pauses the application, not just HTTP traffic.
Enriching the bulk data from Hardcover once we have seeded the higher quality data.
Lessons Learned
Importing books wasn't difficult. Designing a process that gave me confidence before touching production was.
None of the real lessons here were about Python or PostgreSQL or Google Books specifically. They were about trust: trusting the data enough to clean it instead of assuming it, trusting the importer enough to let a dry run answer questions before a single row committed, and trusting production enough to know that stopping traffic isn't the same as stopping the system.
Production changes deserve rehearsals.