You ingest and reconstruct multiple unrelated CSV exports or a PostgreSQL dump from an acquired legacy system by first mapping the dump's underlying schema and relationships, then building a staged pipeline that loads raw files or tables as-is, reconstructs relationships through keys, and only then applies business logic to produce clean, usable records. This guide is for data analysts and engineers handling data migration after a company acquisition, when the source system hands over a folder of disconnected CSVs or a raw database dump instead of clean, documented tables. After reading, you'll be able to turn a messy export into a reliable ingestion pipeline instead of a one-time manual reconstruction effort.
Acquired systems rarely export cleanly. What you typically get is either a batch of CSV files with no clear relationship documented between them, or a full PostgreSQL dump where the schema reflects years of undocumented changes. Both cases need the same underlying approach: understand the structure before you try to use the data.
The Problem
When a company is acquired, the standard export from their legacy system is often a set of unrelated CSV files, one per table or report, with no shared documentation on how they connect. Alternatively, it's a raw PostgreSQL dump of the entire database, including tables, orphaned records, and years of schema drift with no data dictionary. Either way, someone has to reverse-engineer the relationships before the data is usable, and doing that manually with a SQL client against a dump, one query at a time, doesn't scale if you're absorbing more than one acquired system.
What You'll Need
- The full set of CSV exports or the PostgreSQL dump file, plus any documentation the source team can provide (even partial)
- A staging environment (a scratch database or schema) to load raw data into before transformation
- A pipeline tool that can connect to PostgreSQL and ingest CSVs from cloud storage or SFTP, such as Integrate.io
- Time budgeted specifically for schema discovery; this step is frequently underestimated
How to Reconstruct a Legacy Export: Step-by-Step
Step 1: Inventory Every File or Table Before Touching the Data
Before writing any transformation logic, get a complete list of what you actually received. Migrations that skip this step routinely discover missing tables mid-project.
What to do:
- List every CSV file by name and row count, or every table in the PostgreSQL dump with row counts
- Note file sizes and last-modified dates as a rough signal of which tables are actively used versus stale
- Flag any file or table with an unclear name for a follow-up question to whoever provided the export
- Confirm you have the complete export, not a partial one, before starting reconstruction work
Output of this step: A complete inventory of every file or table in the export, with row counts and initial notes on likely purpose.
Step 2: Load Everything Into Staging As-Is
Resist the urge to clean or transform data on the way in. Load raw first, so you always have an unmodified copy to check against later.
What to do:
- Load every CSV file into its own staging table, preserving original column names and types
- If working from a PostgreSQL dump, restore it into an isolated staging database rather than a shared one
- Do not apply any transformations, joins, or cleanup at this stage
- Confirm row counts in staging match the source inventory from Step 1 exactly
Output of this step: A staging environment containing an unmodified, one-to-one copy of every source file or table.
Where Integrate.io helps: Integrate.io connects directly to PostgreSQL and to CSV files in S3, SFTP, or cloud storage, so this raw load step can run as a scheduled pipeline rather than a manual import script, and it preserves the original schema by default until you explicitly configure a transformation.
Step 3: Reverse-Engineer the Relationships Between Tables
This is the step that turns disconnected files into a usable relational structure. Without documentation, this means inferring keys from the data itself.
What to do:
- Look for shared ID columns across tables (customer_id, account_id) even if named inconsistently
- Check for foreign-key-like patterns even where no formal constraint exists in the source
- Sample join candidates and manually verify the relationship makes sense (do the row counts and cardinality match what you'd expect?)
- Document each discovered relationship as you confirm it, building a data dictionary the source system never had
Output of this step: A documented map of table relationships, confirmed against actual data rather than assumed from naming conventions alone.
Step 4: Identify and Isolate Orphaned or Inconsistent Records
Legacy systems accumulate records that don't cleanly fit the current relational model: deleted-but-not-purged rows, duplicate keys from old migrations, or records missing a parent reference.
What to do:
- Run referential checks: does every child record have a valid parent based on the relationships from Step 3?
- Quantify how many records fail each check, not just whether any fail
- Route orphaned or inconsistent records to a separate review table rather than dropping them silently
- Decide with a stakeholder whether orphaned records need manual reconciliation or can be safely excluded
Output of this step: A clear count of clean versus orphaned records per table, with orphaned records isolated for review rather than lost or silently merged.
Step 5: Apply Transformation and Standardization Rules
Only after the structure is understood and validated should you apply business logic: standardizing formats, deduplicating, and mapping to your own schema.
What to do:
- Standardize inconsistent formats (dates, phone numbers, state names) now that you know which fields need it
- Deduplicate records using the keys confirmed in Step 3, not assumed keys
- Map source fields to your target schema, documenting any field that doesn't have a clean equivalent
- Re-run row count and spot checks against the staging copy to confirm nothing was lost in transformation
Output of this step: A transformed dataset that matches your target schema, traceable back to the original staged copy for auditing.
Where Integrate.io helps: Integrate.io's AI Transformation component can apply plain-language standardization rules across inconsistent legacy formats, for example normalizing several different date or phone number conventions found across years of undocumented schema changes, without writing a separate conditional rule for every variant found.
Step 6: Document the Reconstructed Schema for the Next Acquisition
If this isn't the only legacy system your company will absorb, the reconstruction work you just did has value beyond this one migration.
What to do:
- Publish the data dictionary and relationship map you built in Step 3 somewhere the next team can find it
- Note which parts of the reconstruction required manual judgment versus automated inference
- Template the staging-to-transformation pipeline structure so it can be reused with a new source's specific field names swapped in
- Flag any lessons about the acquired company's system that would speed up the next similar migration
Output of this step: A documented, reusable reconstruction process, reducing the discovery time needed for the next acquired legacy system.
Common Mistakes to Avoid
- Applying transformations directly on load instead of staging raw data first, which removes your ability to check against an unmodified source copy when something looks wrong later.
- Assuming column names imply relationships without verifying with actual data; legacy systems frequently have misleading or inconsistent naming.
- Dropping orphaned records silently instead of isolating and reviewing them; some of those records represent real, if messy, business history.
- Underestimating the time needed for schema discovery and jumping straight to transformation, which usually means redoing work once a missed relationship surfaces.
- Treating a PostgreSQL dump restore as equivalent to understanding the schema; restoring the data doesn't tell you what any of it means.
- Not documenting the reconstruction process, which forces the next legacy migration to start from zero instead of building on what was already learned.
Conclusion
Ingesting and reconstructing multiple unrelated CSV exports or a PostgreSQL dump from an acquired legacy system comes down to loading everything raw into staging first, reverse-engineering the relationships from the actual data rather than assumed naming, isolating anything that doesn't fit cleanly, and only then applying transformation logic. Skipping straight to cleanup is what turns a one-week reconstruction into a month of rework. Platforms like Integrate.io support this staged approach directly, connecting to PostgreSQL and file-based sources while preserving raw schema until you're ready to transform, and applying plain-language standardization rules once the structure is confirmed. With a documented, reusable reconstruction process in place, absorbing the next acquired system's data becomes faster each time instead of starting the discovery work over from scratch.