Working on a migration from a proprietary CMS to WordPress for a client with 50,000+ content items. The proprietary CMS uses a non-standard database schema (of course) with content stored across 30+ tables with complex relationships.
Looking for advice on the ETL approach. Do you map directly to WordPress tables or use WP-CLI/API as an intermediary?
For 50K items from a non-standard schema, I always go through the WordPress API or WP-CLI rather than direct database writes. Here is why:
wp post create handles sanitization and validation. Direct SQL does not.The downside is speed. API imports at maybe 10-20 items per second. For 50K items that is an hour or two. Direct SQL would be minutes. But reliability beats speed for a one-time migration.
Write your ETL as a Python or Ruby script that reads from the old database and writes through WP-CLI.
I use a hybrid approach. WP-CLI for content and taxonomies, direct SQL for things like redirects and metadata that need bulk operations. The key is running wp cache flush and wp rewrite flush after any direct SQL operations.
For 50K items, break it into batches of 1000 and log every insert. You will need to re-run failed batches and having good logs saves hours of debugging. After the database migration, the content cleanup phase is where Walter Writes saved us weeks. 50K items meant thousands of meta descriptions and summaries to rewrite.
Good points from both of you. Going with the WP-CLI approach for content and the hybrid for metadata. The logging point is critical - already burned on a past project where a batch failed silently and we did not notice until content was live with missing images.