-- 第一步：通过 SQL PUT 将本地 CSV 文件上传到 User Volume
PUT '/path/to/your/doc_ods_loan_book.csv' TO USER VOLUME FILE 'doc_ods_loan_book.csv';
-- 第二步：从 User Volume COPY INTO 表
COPY INTO best_practice_reg_reporting.doc_ods_loan_book
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('doc_ods_loan_book.csv');
也可直接内联插入小批量测试数据（不需要 CSV 文件）：
INSERT INTO best_practice_reg_reporting.doc_ods_loan_book
(loan_id, loan_number, loan_date, maturity_date, amount, applied_amount, interest_rate,
loan_duration_months, monthly_payment, country, age, gender, employment_status,
income_total, debt_to_income, probability_of_default, loss_given_default,
expected_loss, expected_return, rating, status, default_date, current_debt_days,
ead1, ead2, principal_balance, recovery_stage, report_as_of_eod)
VALUES
-- 来自 Bondora 原始数据（真实贷款 ID）
('66AE108B-532B-4BB3-BAB7-0019A46412C1',483449,CAST('2016-03-23' AS DATE),CAST('2020-06-26' AS DATE),
2125.0000,2125.0000,20.97,60,62.0500,'EE',53,1,6,354.00,26.29,
0.06851186,0.580000,0.03965108,0.14114493,'C','Late',CAST('2020-01-14' AS DATE),
552,1251.9800,64.0700,1155.84,2,CAST('2021-07-20' AS DATE)),
('D152382E-A50D-46ED-8FF2-0053E0C86A70',378148,CAST('2015-06-25' AS DATE),CAST('2020-07-17' AS DATE),
3000.0000,3000.0000,17.12,60,84.7500,'EE',50,1,5,900.00,30.58,
0.03079912,0.650000,0.02001943,0.14043561,'B','Late',CAST('2016-06-02' AS DATE),
1918,2730.8400,2370.7700,2436.41,2,CAST('2021-07-20' AS DATE))
-- ... 完整 25 条，均来自 Bondora CSV 实际字段
;
验证行数：
SELECT COUNT(*) AS row_count FROM best_practice_reg_reporting.doc_ods_loan_book;
-- 模拟贷款状态变化（由 Current 变为 Late）
UPDATE best_practice_reg_reporting.doc_ods_loan_book
SET status = 'Late', current_debt_days = 45
WHERE loan_id IN (
'A1234567-0005-4ABC-8000-555555555555',
'A1234567-0010-4ABC-8000-101010101010'
);
查看完整变更历史（DESC HISTORY）
DESC HISTORY best_practice_reg_reporting.doc_ods_loan_book LIMIT 5;
-- 回溯到 UPDATE 之前，确认两笔贷款的原始状态
SELECT loan_id, status, current_debt_days
FROM best_practice_reg_reporting.doc_ods_loan_book
TIMESTAMP AS OF '2026-06-06 23:53:15'
WHERE loan_id IN (
'A1234567-0005-4ABC-8000-555555555555',
'A1234567-0010-4ABC-8000-101010101010'
);
loan_id | status | current_debt_days
----------------------------------------+---------+------------------
A1234567-0005-4ABC-8000-555555555555 | Current | 0
A1234567-0010-4ABC-8000-101010101010 | Current | 5
CREATE TABLE IF NOT EXISTS best_practice_reg_reporting.doc_dwd_loan_std (
loan_id STRING,
loan_date DATE,
maturity_date DATE,
amount DECIMAL(18,4),
interest_rate DECIMAL(8,4),
loan_duration_months INT,
country STRING,
rating STRING,
status STRING,
current_debt_days INT COMMENT 'Days past due DPD',
ifrs9_stage INT COMMENT '1=performing 2=underperforming 3=credit-impaired',
probability_of_default DECIMAL(10,8),
loss_given_default DECIMAL(8,6),
ead DECIMAL(14,4) COMMENT 'Exposure at Default',
ecl_amount DECIMAL(14,4) COMMENT 'ECL = PD x LGD x EAD',
principal_balance DECIMAL(14,4),
default_date DATE,
report_date DATE,
dwd_load_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) COMMENT 'DWD: Regulatory-caliber standardized loan data with IFRS9 stage classification';
IFRS 9 三阶段分类逻辑
INSERT INTO best_practice_reg_reporting.doc_dwd_loan_std
SELECT
loan_id,
loan_date,
maturity_date,
amount,
interest_rate,
loan_duration_months,
country,
rating,
status,
COALESCE(current_debt_days, 0) AS current_debt_days,
-- IFRS 9 三阶段判定规则
CASE
WHEN status = 'Default'
OR COALESCE(current_debt_days, 0) > 90 THEN 3 -- Credit-impaired
WHEN COALESCE(current_debt_days, 0) > 30
OR rating IN ('E', 'F', 'HR')
OR probability_of_default > 0.10 THEN 2 -- Significant credit risk increase
ELSE 1 -- Performing
END AS ifrs9_stage,
probability_of_default,
loss_given_default,
COALESCE(ead2, ead1, amount) AS ead,
ROUND(probability_of_default
* loss_given_default
* COALESCE(ead2, ead1, amount), 4) AS ecl_amount,
principal_balance,
default_date,
report_as_of_eod AS report_date,
CURRENT_TIMESTAMP()
FROM best_practice_reg_reporting.doc_ods_loan_book;
阶段判定说明：
阶段
条件
IFRS 9 含义
本数据集
Stage 1
DPD ≤ 30 天 且 PD ≤ 10% 且 Rating 非 E/F/HR
正常履约
13 笔
Stage 2
DPD 31-90 天 或 PD > 10% 或 Rating ∈ {E,F,HR}
信用风险显著上升
1 笔
Stage 3
DPD > 90 天 或 Status = Default
信用减值
11 笔
验证分布：
SELECT ifrs9_stage, COUNT(*) AS loan_count, ROUND(SUM(ecl_amount), 2) AS total_ecl
FROM best_practice_reg_reporting.doc_dwd_loan_std
GROUP BY ifrs9_stage
ORDER BY ifrs9_stage;
PD（违约概率）和 LGD（违约损失率）是模型敏感参数，不应对 IT 运营人员或外部审计人员完整暴露。
创建脱敏函数
CREATE OR REPLACE FUNCTION best_practice_reg_reporting.mask_sensitive_rate(rate DOUBLE)
RETURNS DOUBLE
AS CASE
WHEN current_user() IN ('privileged_user') THEN rate -- 替换为实际授权用户名
ELSE ROUND(rate, 2)
END;
ALTER TABLE best_practice_reg_reporting.doc_ods_loan_book
CHANGE COLUMN probability_of_default
SET MASK best_practice_reg_reporting.mask_sensitive_rate;
ALTER TABLE best_practice_reg_reporting.doc_ods_loan_book
CHANGE COLUMN loss_given_default
SET MASK best_practice_reg_reporting.mask_sensitive_rate;
脱敏效果验证
SELECT current_user() AS executing_user,
0.06851186 AS raw_pd,
best_practice_reg_reporting.mask_sensitive_rate(0.06851186) AS masked_pd;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_reg_reporting.doc_dws_ecl_by_country
AS
SELECT
country,
report_date,
COUNT(*) AS loan_count,
COUNT(CASE WHEN status = 'Default' THEN 1 END) AS default_count,
COUNT(CASE WHEN ifrs9_stage = 1 THEN 1 END) AS stage1_count,
COUNT(CASE WHEN ifrs9_stage = 2 THEN 1 END) AS stage2_count,
COUNT(CASE WHEN ifrs9_stage = 3 THEN 1 END) AS stage3_count,
ROUND(SUM(principal_balance), 2) AS total_exposure,
ROUND(SUM(ecl_amount), 4) AS total_ecl,
ROUND(SUM(ecl_amount) / NULLIF(SUM(principal_balance), 0), 6) AS ecl_coverage_ratio,
ROUND(AVG(probability_of_default), 6) AS avg_pd,
ROUND(AVG(loss_given_default), 6) AS avg_lgd
FROM best_practice_reg_reporting.doc_dwd_loan_std
GROUP BY country, report_date;
⚠️ 注意：Dynamic Table DDL 中不写
REFRESH INTERVAL
REFRESH INTERVAL
参数。调度刷新通过 Studio Task 管理（见"配置刷新调度"一节），可在任务上附加数据质量规则和监控告警。
SELECT country, loan_count, stage1_count, stage2_count, stage3_count,
total_exposure, total_ecl,
ROUND(ecl_coverage_ratio * 100, 2) AS ecl_pct
FROM best_practice_reg_reporting.doc_dws_ecl_by_country
ORDER BY total_ecl DESC;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_reg_reporting.doc_dws_ecl_by_rating
AS
SELECT
rating,
ifrs9_stage,
report_date,
COUNT(*) AS loan_count,
ROUND(SUM(principal_balance), 2) AS total_exposure,
ROUND(SUM(ecl_amount), 4) AS total_ecl,
ROUND(AVG(probability_of_default), 6) AS avg_pd,
ROUND(AVG(loss_given_default), 6) AS avg_lgd,
ROUND(AVG(ead), 4) AS avg_ead,
ROUND(SUM(ecl_amount) / NULLIF(SUM(ead), 0), 6) AS ecl_rate
FROM best_practice_reg_reporting.doc_dwd_loan_std
GROUP BY rating, ifrs9_stage, report_date;
刷新后查看 ECL 覆盖率分布：
SELECT rating, ifrs9_stage, loan_count,
ROUND(total_exposure, 2) AS exposure,
ROUND(total_ecl, 2) AS ecl,
ROUND(ecl_rate * 100, 4) AS ecl_rate_pct
FROM best_practice_reg_reporting.doc_dws_ecl_by_rating
ORDER BY ifrs9_stage ASC, ecl_rate DESC;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_reg_reporting.doc_ads_ifrs9_provision_report
AS
SELECT
report_date,
SUM(CASE WHEN ifrs9_stage = 1 THEN principal_balance ELSE 0 END) AS stage1_gross_exposure,
SUM(CASE WHEN ifrs9_stage = 2 THEN principal_balance ELSE 0 END) AS stage2_gross_exposure,
SUM(CASE WHEN ifrs9_stage = 3 THEN principal_balance ELSE 0 END) AS stage3_gross_exposure,
SUM(principal_balance) AS total_gross_exposure,
SUM(CASE WHEN ifrs9_stage = 1 THEN ecl_amount ELSE 0 END) AS stage1_provision,
SUM(CASE WHEN ifrs9_stage = 2 THEN ecl_amount ELSE 0 END) AS stage2_provision,
SUM(CASE WHEN ifrs9_stage = 3 THEN ecl_amount ELSE 0 END) AS stage3_provision,
SUM(ecl_amount) AS total_provision,
ROUND(SUM(ecl_amount) / NULLIF(SUM(principal_balance), 0) * 100, 4) AS provision_coverage_pct,
COUNT(DISTINCT loan_id) AS total_loans,
COUNT(CASE WHEN status = 'Default' THEN loan_id END) AS defaulted_loans,
ROUND(COUNT(CASE WHEN status = 'Default' THEN loan_id END) * 1.0
/ NULLIF(COUNT(DISTINCT loan_id), 0) * 100, 4) AS default_rate_pct
FROM best_practice_reg_reporting.doc_dwd_loan_std
GROUP BY report_date;
刷新并查看报表：
REFRESH DYNAMIC TABLE best_practice_reg_reporting.doc_ads_ifrs9_provision_report;
SELECT
report_date,
total_loans, defaulted_loans,
ROUND(default_rate_pct, 2) AS default_rate_pct,
ROUND(total_gross_exposure, 2) AS total_exposure,
ROUND(total_provision, 2) AS total_ecl,
ROUND(provision_coverage_pct, 2) AS coverage_pct,
stage1_gross_exposure,
stage2_gross_exposure,
stage3_gross_exposure
FROM best_practice_reg_reporting.doc_ads_ifrs9_provision_report;
