未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

warning 适用边界与维护说明

本文适用于银行ODS表结构、ETL流程与性能设计讨论，不是完整可部署脚本；本次仅作全文结构审阅，未作外部事实核验或Oracle环境执行验证。当前增量表的范围分区DDL未列出分区，交易流水表以TIMESTAMP列作为间隔分区键的定义仍不符合Oracle间隔分区键限制，性能分区示例中的省略号仅表示结构示意。客户清洗依赖未提供定义及装载方式的ODS_CUST_INFO_TMP，且SELECT DISTINCT不能保证每个cust_id只有一条源记录，执行MERGE前仍需明确同一客户多条记录的取舍规则；其INSERT分支已完整列出目标列和值，不再存在省略号缺陷。抽取仅展示前一天交易日期窗口，源目标字段映射、重跑幂等、迟到数据及源端更新删除处理均未实现。原始数据保留与清洗加工应分层理解，现有cust_id单列主键不能直接保存多版本SCD历史；位图索引须结合写入并发和装载方式评估，不宜直接套用于高并发写入场景。

Oracle银行数仓ODS层设计

📋 概述

ODS（Operational Data Store）层是银行数据仓库的基础数据层，负责从各个源系统抽取原始数据，保持数据的原貌，为后续的数据加工提供基础。

🏗️ ODS层设计原则

1. 数据完整性

保持源系统数据结构
不做业务逻辑处理
保留所有字段信息

2. 增量与全量策略

-- 增量抽取示例
CREATE TABLE ODS_ACCOUNT_TRANS_INC (
    trans_id      VARCHAR2(32),
    account_no    VARCHAR2(20),
    trans_date    DATE,
    trans_amount  NUMBER(18,2),
    trans_type    VARCHAR2(10),
    etl_date      DATE DEFAULT SYSDATE
) PARTITION BY RANGE (trans_date);

-- 全量抽取示例
CREATE TABLE ODS_CUSTOMER_INFO_FULL (
    cust_id       VARCHAR2(20),
    cust_name     VARCHAR2(100),
    id_no         VARCHAR2(18),
    phone_no      VARCHAR2(20),
    create_date   DATE,
    update_date   DATE,
    etl_date      DATE DEFAULT SYSDATE
);

📊 典型表结构设计

1. 客户信息表

CREATE TABLE ODS_CUST_INFO (
    cust_id          VARCHAR2(20) PRIMARY KEY,
    cust_name        VARCHAR2(100),
    cust_type        VARCHAR2(2),    -- 01:个人 02:企业
    id_type          VARCHAR2(2),
    id_no            VARCHAR2(30),
    gender           VARCHAR2(1),
    birth_date       DATE,
    phone_no         VARCHAR2(20),
    email            VARCHAR2(100),
    address          VARCHAR2(200),
    source_system    VARCHAR2(20),
    etl_date         DATE,
    start_date       DATE,
    end_date         DATE
);

2. 账户信息表

CREATE TABLE ODS_ACCOUNT_INFO (
    account_no       VARCHAR2(30) PRIMARY KEY,
    cust_id          VARCHAR2(20),
    account_type     VARCHAR2(10),
    product_code     VARCHAR2(20),
    open_date        DATE,
    close_date       DATE,
    status           VARCHAR2(2),
    balance          NUMBER(18,2),
    currency         VARCHAR2(3),
    branch_code      VARCHAR2(10),
    source_system    VARCHAR2(20),
    etl_date         DATE
);

3. 交易流水表

CREATE TABLE ODS_TRANS_DETAIL (
    trans_id         VARCHAR2(32) PRIMARY KEY,
    account_no       VARCHAR2(30),
    trans_date       TIMESTAMP,
    trans_amount     NUMBER(18,2),
    trans_type       VARCHAR2(10),
    debit_credit     VARCHAR2(1),    -- D:借 C:贷
    balance_after    NUMBER(18,2),
    channel          VARCHAR2(10),
    teller_no        VARCHAR2(10),
    source_system    VARCHAR2(20),
    etl_date         DATE
) PARTITION BY RANGE (trans_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
    PARTITION p_init VALUES LESS THAN (DATE '2024-01-01')
);

🔄 ETL处理流程

1. 数据抽取

-- 使用DBLINK抽取源系统数据
CREATE DATABASE LINK core_system
CONNECT TO source_user IDENTIFIED BY password
USING 'CORE_DB';

-- 抽取示例
INSERT INTO ODS_ACCOUNT_TRANS_INC
SELECT * FROM account_trans@core_system
WHERE trans_date >= TRUNC(SYSDATE) - 1
  AND trans_date < TRUNC(SYSDATE);

2. 数据清洗

-- 基础数据清洗
MERGE INTO ODS_CUST_INFO tgt
USING (
    SELECT DISTINCT
        cust_id,
        TRIM(cust_name) as cust_name,
        UPPER(id_no) as id_no,
        REGEXP_REPLACE(phone_no, '[^0-9]', '') as phone_no
    FROM ODS_CUST_INFO_TMP
) src
ON (tgt.cust_id = src.cust_id)
WHEN MATCHED THEN
    UPDATE SET 
        tgt.cust_name = src.cust_name,
        tgt.id_no = src.id_no,
        tgt.phone_no = src.phone_no,
        tgt.etl_date = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (cust_id, cust_name, id_no, phone_no, etl_date)
    VALUES (src.cust_id, src.cust_name, src.id_no, src.phone_no, SYSDATE);

📈 性能优化

1. 分区策略

-- 按日期范围分区
CREATE TABLE ODS_TRANS_DETAIL_PART (
    ...
) PARTITION BY RANGE (trans_date)
(
    PARTITION p_202401 VALUES LESS THAN (DATE '2024-02-01'),
    PARTITION p_202402 VALUES LESS THAN (DATE '2024-03-01'),
    ...
);

2. 索引设计

-- 创建必要的索引
CREATE INDEX idx_trans_date ON ODS_TRANS_DETAIL(trans_date);
CREATE INDEX idx_account_no ON ODS_TRANS_DETAIL(account_no);
CREATE BITMAP INDEX idx_trans_type ON ODS_TRANS_DETAIL(trans_type);

🔗 相关文档

💡 最佳实践

数据校验：每次ETL后进行数据量和关键指标校验
历史数据保留：使用SCD（缓慢变化维）管理历史变化
异常处理：建立完善的异常数据处理机制
监控告警：实时监控ETL执行状态和数据质量

ODS层是数据仓库的基础，其设计质量直接影响整个数仓的数据质量