金融多源原始数据清洗后，如何维护规范化表与原始表的关联？

Great question—this is a critical problem in financial data systems, where traceability, auditability, and troubleshooting depend entirely on being able to map cleaned, normalized records back to their raw source data. Let’s break down your proposed approach, its tradeoffs, and how to optimize it, plus compare it to other common strategies.

Your Proposed Approach: Independent Link Table #

First off, your idea of a dedicated table with {cleanID, rawTable, rawTableID} is a solid, flexible foundation—this is often called a cross-reference or lineage table. Let’s start with why this works well:

Pros #

• Unlimited scalability: Add new raw data tables without modifying existing schema (no need to add columns to your normalized table or raw tables).
• Clean separation of concerns: Your normalized table stays focused on its core data model, and raw tables don’t get cluttered with foreign keys to the clean layer.
• Centralized lineage: All mapping lives in one place, making it easy to run queries like "show all raw records that contributed to this clean entry" or "find the clean version of this raw row".

Potential Pitfalls (and Fixes) #

Like any approach, it has a few edge cases to address:

• String-based rawTable risk: Typing errors (e.g., raw_stock_price instead of raw_stock_prices) can break lineage. Fix this by using a database enum for rawTable (if your DB supports it, like PostgreSQL) or adding a check constraint to restrict values to valid table names. Example:

  -- PostgreSQL example
  CREATE TYPE raw_source_enum AS ENUM ('raw_stock_prices', 'raw_bond_yields', 'raw_fx_rates');
  ALTER TABLE clean_raw_links ALTER COLUMN rawTable TYPE raw_source_enum;
  

• Data consistency: Ensure that inserting a clean record and its link happens atomically. Wrap both operations in a single transaction in your trigger—if either fails, the entire operation rolls back to avoid orphaned clean records or links.
• Performance at scale: For large datasets, add targeted indexes:
  • A unique index on (cleanID, rawTable, rawTableID) to prevent duplicate links.
  • An index on (rawTable, rawTableID) to quickly find the clean record for a given raw row.
  • An index on cleanID to fetch all raw sources for a clean record.

Alternative Approaches (and When to Use Them) #

Let’s compare your approach to other common strategies to confirm it’s the best fit for your use case:

• Multi-column foreign keys on the normalized table: Adding columns like raw_stock_id, raw_bond_id, etc., to your clean table.
  ❌ Downside: Not scalable—every new raw source requires a schema change, and you’ll end up with lots of NULL values (since a clean record only comes from one raw source). Avoid this if you have more than 2-3 raw tables.

• Add cleanID foreign key to raw tables: Adding a clean_id column to each raw table that references the normalized table.
  ❌ Downside: Works only if one raw record maps to one clean record. If your cleaning process aggregates multiple raw records into one clean entry, this breaks. Also, if raw tables are read-only (e.g., loaded from external systems), you can’t modify their schema.

• JSON/JSONB field on the normalized table: Storing lineage as a JSON object like {"source_table": "raw_stock_prices", "source_id": 123} in your clean table.
  ❌ Downside: Poor query performance for large datasets (JSON fields aren’t as efficient to index as relational columns), and you lose database-level validation (no way to enforce that source_id exists in the specified table).

Final Recommendation #

Your independent link table is the optimal approach for most financial data pipelines, especially when dealing with multiple raw sources and needing strict traceability. The key is to harden it with enums/check constraints, atomic transactions, and targeted indexes to avoid the minor pitfalls.

Here’s a quick example of how your trigger might look (PostgreSQL syntax):

CREATE OR REPLACE FUNCTION link_clean_to_raw()
RETURNS TRIGGER AS $$
BEGIN
  -- Insert link record when a clean record is created
  INSERT INTO clean_raw_links (cleanID, rawTable, rawTableID)
  VALUES (NEW.clean_id, TG_TABLE_NAME::raw_source_enum, NEW.raw_id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to your raw table (repeat for each raw source)
CREATE TRIGGER after_raw_stock_insert
AFTER INSERT ON raw_stock_prices
FOR EACH ROW
EXECUTE FUNCTION link_clean_to_raw();

内容的提问来源于stack exchange，提问作者Sam