chenwu/REFACTOR-MA
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
REFACTOR-MA/validation/chpa/02_dws/validate_01_02_refactor.sql
T

1627 lines
44 KiB
SQL
Raw Permalink Blame History

-- Databricks notebook source
-- MAGIC %md
-- MAGIC # CHPA 01/02 refactor validation (DWD/DWS)
-- MAGIC
-- MAGIC Run this notebook AFTER all `sql/chpa/01_dwd/` and `sql/chpa/02_dws/` jobs
-- MAGIC finish and BEFORE migrating downstream consumers.
-- MAGIC
-- MAGIC **Snapshot assumption:** the legacy CHPA 01/02 jobs and the new RE 01_dwd/02_dws
-- MAGIC jobs must have run on the same source snapshot (same DWD sources, same config
-- MAGIC tables, same fact files). The legacy tables below are kept as validation
-- MAGIC baselines until migration completes.
-- MAGIC
-- MAGIC **Hard gates:** row counts and bidirectional `EXCEPT ALL` on explicit business
-- MAGIC columns (regenerated `ETL_*_DT` timestamps are excluded) for every
-- MAGIC logic-equivalent old/new pair. Any failed check raises an error at the end.
-- MAGIC
-- MAGIC **Intentional soft areas** (quality metrics only, no hard failure):
-- MAGIC
-- MAGIC 1. **Sales fact province (Part 2) rows** -- the province mapping was rewritten:
-- MAGIC legacy normalized province names from `dm.dm_td_geography` (CONCAT suffixes);
-- MAGIC the new fact joins `dwd_gnd_pharbers_prov_fact.PROVINCE_C` directly to
-- MAGIC `dws.dws_ext_td_ims_geo.PROVINCE_C` (layer fix). Exact equality is only
-- MAGIC guaranteed when geo coverage is identical, so only the national (CHT) rows
-- MAGIC are hard-gated; province rows are reported via match-rate and delta metrics.
-- MAGIC 2. **Market** -- the legacy MERGE could abort on multi-match
-- MAGIC (`DELTA_MULTIPLE_SOURCE_ROW_MATCHING`) and `ROW_NUMBER()` KC tie-breaks are
-- MAGIC engine-defined; equivalence is expected in every non-error case but is not
-- MAGIC guaranteed, so market is metrics-only.
-- MAGIC 3. **ETL timestamps** -- regenerated by each run; reported as informational
-- MAGIC metrics only.
-- MAGIC
-- MAGIC Target initialization is documented in each build script: most use `LIKE` the
-- MAGIC verified baseline, explicit narrowed contracts use zero-row CTAS type inference,
-- MAGIC and the stateful pack-YM table uses `DEEP CLONE` to retain older history.
-- COMMAND ----------
-- Manufacturer-corporation schema contract: the legacy dwd_ims_td_manufacturer_corp
-- carries T1.* plus 3 derived columns; only the columns referenced by live
-- consumers are known locally. Confirm the 9-column contract below before relying
-- on the manufacturer-corporation gate (the DWS build script documents this
-- UNKNOWN-SCHEMA caveat).
DESCRIBE TABLE dws.dws_ext_td_ims_manufacturer_corporation;
-- COMMAND ----------
DESCRIBE TABLE dwd.dwd_ims_td_manufacturer_corp;
-- COMMAND ----------
-- Manufacturer-corporation: known columns only (6 source + 3 derived).
CREATE OR REPLACE TEMP VIEW validation_old_manufacturer_corp AS
SELECT
Manufacturer_ID,
Manufacturer_CODE,
Manufacturer_Abbr,
Manufacturer_Name,
ManufacturerType_ID,
Corporation_Code,
CORP_MASTER_CODE,
CORP_ABBR,
CORP_DES
FROM dwd.dwd_ims_td_manufacturer_corp;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_manufacturer_corp AS
SELECT
Manufacturer_ID,
Manufacturer_CODE,
Manufacturer_Abbr,
Manufacturer_Name,
ManufacturerType_ID,
Corporation_Code,
CORP_MASTER_CODE,
CORP_ABBR,
CORP_DES
FROM dws.dws_ext_td_ims_manufacturer_corporation;
-- COMMAND ----------
-- Pack property: 31 business columns (no timestamps in the contract).
CREATE OR REPLACE TEMP VIEW validation_old_pack_property AS
SELECT
PACK_COD,
PACK_DES,
STGH_DES,
PACK_LCH,
PROD_COD,
CMPS_COD,
CMPS_DES,
ATC1_COD,
ATC2_COD,
ATC3_COD,
ATC4_COD,
APP1_COD,
APP2_COD,
APP3_COD,
BIO_DESC,
GENE_ORIG_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_Entry_Date,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
VBP_DESC,
MANU_COD,
MANU_DES,
MNFL_COD,
MNFL_DES,
CORP_COD,
CORP_DES,
BrandType
FROM dwd.dwd_ims_td_pack_property;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_pack_property AS
SELECT
PACK_COD,
PACK_DES,
STGH_DES,
PACK_LCH,
PROD_COD,
CMPS_COD,
CMPS_DES,
ATC1_COD,
ATC2_COD,
ATC3_COD,
ATC4_COD,
APP1_COD,
APP2_COD,
APP3_COD,
BIO_DESC,
GENE_ORIG_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_Entry_Date,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
VBP_DESC,
MANU_COD,
MANU_DES,
MNFL_COD,
MNFL_DES,
CORP_COD,
CORP_DES,
BrandType
FROM dws.dws_ext_td_ims_pack_property;
-- COMMAND ----------
-- Geo: 9 business columns. ETL_INSERT_DT/ETL_UPDATE_DT are copied from staging by
-- both jobs; they are compared as informational metrics only (see timestamp cell).
CREATE OR REPLACE TEMP VIEW validation_old_geo AS
SELECT
AUDIT_COD,
AUDIT_DES,
AUDIT_DES_C,
AUDIT_TYPE,
CITY_TIER,
AZ_CITY_TIER,
PROVINCE,
PROVINCE_C,
REGIONCENTER
FROM dws.dws_ims_td_geo;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_geo AS
SELECT
AUDIT_COD,
AUDIT_DES,
AUDIT_DES_C,
AUDIT_TYPE,
CITY_TIER,
AZ_CITY_TIER,
PROVINCE,
PROVINCE_C,
REGIONCENTER
FROM dws.dws_ext_td_ims_geo;
-- COMMAND ----------
-- Corporation CN: 3 business columns (ETL timestamps regenerated).
CREATE OR REPLACE TEMP VIEW validation_old_corp_cn AS
SELECT
CORP_COD,
CORP_DES,
CORP_DES_C
FROM dws.dws_ims_td_corp_cn;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_corp_cn AS
SELECT
CORP_COD,
CORP_DES,
CORP_DES_C
FROM dws.dws_ext_td_ims_corporation_cn;
-- COMMAND ----------
-- Manufacturer CN: 3 business columns (ETL timestamps regenerated).
CREATE OR REPLACE TEMP VIEW validation_old_manu_cn AS
SELECT
MANU_COD,
MANU_DES,
MANU_DES_C
FROM dws.dws_ims_td_manu_cn;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_manu_cn AS
SELECT
MANU_COD,
MANU_DES,
MANU_DES_C
FROM dws.dws_ext_td_ims_manufacturer_cn;
-- COMMAND ----------
-- Product CN: 5 business columns incl. RANK_TYPE (declarative CASE replaces the
-- legacy tmp list + UPDATE; equivalence chains through the multi-manufacturer check).
CREATE OR REPLACE TEMP VIEW validation_old_prod_cn AS
SELECT
PROD_COD,
PROD_DES,
PROD_DES_C,
CMPS_DES_C,
RANK_TYPE
FROM dws.dws_ims_td_prod_cn;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_prod_cn AS
SELECT
PROD_COD,
PROD_DES,
PROD_DES_C,
CMPS_DES_C,
RANK_TYPE
FROM dws.dws_ext_td_ims_product_cn;
-- COMMAND ----------
-- ATC CN: 12 business columns (legacy ATC2_CODe/ATC3_CODe/ATC4_CODe typo'd names
-- resolve case-insensitively to ATC2_CODE/ATC3_CODE/ATC4_CODE).
CREATE OR REPLACE TEMP VIEW validation_old_atc_cn AS
SELECT
ATC1_COD,
ATC1_DES,
ATC1_DES_C,
ATC2_COD,
ATC2_DES,
ATC2_DES_C,
ATC3_COD,
ATC3_DES,
ATC3_DES_C,
ATC4_COD,
ATC4_DES,
ATC4_DES_C
FROM dws.dws_ims_td_atc_cn;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_atc_cn AS
SELECT
ATC1_COD,
ATC1_DES,
ATC1_DES_C,
ATC2_COD,
ATC2_DES,
ATC2_DES_C,
ATC3_COD,
ATC3_DES,
ATC3_DES_C,
ATC4_COD,
ATC4_DES,
ATC4_DES_C
FROM dws.dws_ext_td_ims_atc_cn;
-- COMMAND ----------
-- NFC CN: 9 business columns.
CREATE OR REPLACE TEMP VIEW validation_old_nfc_cn AS
SELECT
APP1_COD,
APP1_DES,
APP1_DES_C,
APP2_COD,
APP2_DES,
APP2_DES_C,
APP3_COD,
APP3_DES,
APP3_DES_C
FROM dws.dws_ims_td_nfc_cn;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_nfc_cn AS
SELECT
APP1_COD,
APP1_DES,
APP1_DES_C,
APP2_COD,
APP2_DES,
APP2_DES_C,
APP3_COD,
APP3_DES,
APP3_DES_C
FROM dws.dws_ext_td_ims_nfc_cn;
-- COMMAND ----------
-- Market-TA: legacy SELECT * is replaced by an explicit MARKET/TA projection; if the
-- source map carries additional columns they are intentionally not compared.
CREATE OR REPLACE TEMP VIEW validation_old_market_ta AS
SELECT
MARKET,
TA
FROM dws.dws_ims_td_market_ta;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_market_ta AS
SELECT
MARKET,
TA
FROM dws.dws_ext_td_ims_market_ta;
-- COMMAND ----------
-- Product multi-manufacturer list: legacy tmp staging vs new DWS flag list (PROD_COD only).
CREATE OR REPLACE TEMP VIEW validation_old_prod_tmp AS
SELECT
PROD_COD
FROM tmp.tmp_ims_td_prod_tmp;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_prod_multi AS
SELECT
PROD_COD
FROM dws.dws_ext_td_ims_product_multi_manufacturer;
-- COMMAND ----------
-- Pack-YM rolling snapshot: 3 business columns (ym, pack_id, pack_code).
CREATE OR REPLACE TEMP VIEW validation_old_pack_ym AS
SELECT
ym,
pack_id,
pack_code
FROM dws.dws_ims_td_pack_ym;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_pack_ym AS
SELECT
ym,
pack_id,
pack_code
FROM dws.dws_ext_td_ims_pack_ym;
-- COMMAND ----------
-- Promoted sales fact (full): 9 business columns, no timestamps.
CREATE OR REPLACE TEMP VIEW validation_old_fact AS
SELECT
YM,
AUDIT_COD,
PACK_COD,
MTH00LC,
MTH00LCLY,
MTH00CN,
MTH00CNLY,
MTH00UN,
MTH00UNLY
FROM tmp.tmp_ims_tf_fact_sales;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_fact AS
SELECT
YM,
AUDIT_COD,
PACK_COD,
MTH00LC,
MTH00LCLY,
MTH00CN,
MTH00CNLY,
MTH00UN,
MTH00UNLY
FROM dws.dws_ext_tf_ims_chpa_sales;
-- COMMAND ----------
-- National (CHT) rows only: logic-equivalent by construction (same join keys, same
-- asymmetric YM>=202201 window, same yearmont_range cap); hard-gated below.
CREATE OR REPLACE TEMP VIEW validation_old_fact_cht AS
SELECT
YM,
AUDIT_COD,
PACK_COD,
MTH00LC,
MTH00LCLY,
MTH00CN,
MTH00CNLY,
MTH00UN,
MTH00UNLY
FROM validation_old_fact
WHERE AUDIT_COD = 'CHT';
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_fact_cht AS
SELECT
YM,
AUDIT_COD,
PACK_COD,
MTH00LC,
MTH00LCLY,
MTH00CN,
MTH00CNLY,
MTH00UN,
MTH00UNLY
FROM validation_new_fact
WHERE AUDIT_COD = 'CHT';
-- COMMAND ----------
-- Date dimension: 7 business columns (chained on fact equivalence; the YM range and
-- DATE_FLAG max come from the fact, which is validated above).
CREATE OR REPLACE TEMP VIEW validation_old_date AS
SELECT
YM,
YEAR,
MONTH,
QUARTER,
YQ,
DATE_FLAG,
HALF_YEAR
FROM dws.dws_ims_td_date;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_date AS
SELECT
YM,
YEAR,
MONTH,
QUARTER,
YQ,
DATE_FLAG,
HALF_YEAR
FROM dws.dws_ext_td_ims_date;
-- COMMAND ----------
-- Market: 35 business columns. Metrics-only area (see header), view kept for the
-- soft EXCEPT ALL comparison in the market quality cell.
CREATE OR REPLACE TEMP VIEW validation_old_market AS
SELECT
market,
PACK_COD,
PACK_DES,
STGH_DES,
PACK_LCH,
PROD_COD,
CMPS_COD,
CMPS_DES,
ATC1_COD,
ATC2_COD,
ATC3_COD,
ATC4_COD,
APP1_COD,
APP2_COD,
APP3_COD,
BIO_DESC,
GENE_ORIG_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_Entry_Date,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
VBP_DESC,
MANU_COD,
MANU_DES,
MNFL_COD,
MNFL_DES,
CORP_COD,
CORP_DES,
BrandType,
bu,
Market_Ratio,
Key_Competitor
FROM dws.dws_ims_td_market;
-- COMMAND ----------
CREATE OR REPLACE TEMP VIEW validation_new_market AS
SELECT
market,
PACK_COD,
PACK_DES,
STGH_DES,
PACK_LCH,
PROD_COD,
CMPS_COD,
CMPS_DES,
ATC1_COD,
ATC2_COD,
ATC3_COD,
ATC4_COD,
APP1_COD,
APP2_COD,
APP3_COD,
BIO_DESC,
GENE_ORIG_DESC,
ETH_OTC_DESC,
NRDL_DESC,
NRDL_Entry_Date,
EDL_DESC,
TCM_DESC,
PAED_DESC,
GQCE_DESC,
VBP_DESC,
MANU_COD,
MANU_DES,
MNFL_COD,
MNFL_DES,
CORP_COD,
CORP_DES,
BrandType,
bu,
Market_Ratio,
Key_Competitor
FROM dws.dws_ext_td_ims_market;
-- COMMAND ----------
-- Hard compatibility checks: row counts + bidirectional EXCEPT ALL per area.
-- Every row must end with passed = true; the final cell raises on any failure.
CREATE OR REPLACE TEMP VIEW validation_01_02_compatibility_checks AS
WITH compatibility_checks AS (
SELECT
'manufacturer_corporation' AS area,
'manu_corp_row_count' AS check_name,
(SELECT COUNT(*) FROM validation_new_manufacturer_corp) AS actual_value,
(SELECT COUNT(*) FROM validation_old_manufacturer_corp) AS expected_value
UNION ALL
SELECT
'manufacturer_corporation',
'manu_corp_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_manufacturer_corp
EXCEPT ALL
SELECT * FROM validation_new_manufacturer_corp
) AS differences
),
0
UNION ALL
SELECT
'manufacturer_corporation',
'manu_corp_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_manufacturer_corp
EXCEPT ALL
SELECT * FROM validation_old_manufacturer_corp
) AS differences
),
0
UNION ALL
SELECT
'pack_property',
'pack_property_row_count',
(SELECT COUNT(*) FROM validation_new_pack_property),
(SELECT COUNT(*) FROM validation_old_pack_property)
UNION ALL
SELECT
'pack_property',
'pack_property_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_pack_property
EXCEPT ALL
SELECT * FROM validation_new_pack_property
) AS differences
),
0
UNION ALL
SELECT
'pack_property',
'pack_property_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_pack_property
EXCEPT ALL
SELECT * FROM validation_old_pack_property
) AS differences
),
0
UNION ALL
SELECT
'geo',
'geo_row_count',
(SELECT COUNT(*) FROM validation_new_geo),
(SELECT COUNT(*) FROM validation_old_geo)
UNION ALL
SELECT
'geo',
'geo_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_geo
EXCEPT ALL
SELECT * FROM validation_new_geo
) AS differences
),
0
UNION ALL
SELECT
'geo',
'geo_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_geo
EXCEPT ALL
SELECT * FROM validation_old_geo
) AS differences
),
0
UNION ALL
SELECT
'corporation_cn',
'corp_cn_row_count',
(SELECT COUNT(*) FROM validation_new_corp_cn),
(SELECT COUNT(*) FROM validation_old_corp_cn)
UNION ALL
SELECT
'corporation_cn',
'corp_cn_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_corp_cn
EXCEPT ALL
SELECT * FROM validation_new_corp_cn
) AS differences
),
0
UNION ALL
SELECT
'corporation_cn',
'corp_cn_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_corp_cn
EXCEPT ALL
SELECT * FROM validation_old_corp_cn
) AS differences
),
0
UNION ALL
SELECT
'manufacturer_cn',
'manu_cn_row_count',
(SELECT COUNT(*) FROM validation_new_manu_cn),
(SELECT COUNT(*) FROM validation_old_manu_cn)
UNION ALL
SELECT
'manufacturer_cn',
'manu_cn_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_manu_cn
EXCEPT ALL
SELECT * FROM validation_new_manu_cn
) AS differences
),
0
UNION ALL
SELECT
'manufacturer_cn',
'manu_cn_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_manu_cn
EXCEPT ALL
SELECT * FROM validation_old_manu_cn
) AS differences
),
0
UNION ALL
SELECT
'product_cn',
'prod_cn_row_count',
(SELECT COUNT(*) FROM validation_new_prod_cn),
(SELECT COUNT(*) FROM validation_old_prod_cn)
UNION ALL
SELECT
'product_cn',
'prod_cn_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_prod_cn
EXCEPT ALL
SELECT * FROM validation_new_prod_cn
) AS differences
),
0
UNION ALL
SELECT
'product_cn',
'prod_cn_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_prod_cn
EXCEPT ALL
SELECT * FROM validation_old_prod_cn
) AS differences
),
0
UNION ALL
SELECT
'atc_cn',
'atc_cn_row_count',
(SELECT COUNT(*) FROM validation_new_atc_cn),
(SELECT COUNT(*) FROM validation_old_atc_cn)
UNION ALL
SELECT
'atc_cn',
'atc_cn_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_atc_cn
EXCEPT ALL
SELECT * FROM validation_new_atc_cn
) AS differences
),
0
UNION ALL
SELECT
'atc_cn',
'atc_cn_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_atc_cn
EXCEPT ALL
SELECT * FROM validation_old_atc_cn
) AS differences
),
0
UNION ALL
SELECT
'nfc_cn',
'nfc_cn_row_count',
(SELECT COUNT(*) FROM validation_new_nfc_cn),
(SELECT COUNT(*) FROM validation_old_nfc_cn)
UNION ALL
SELECT
'nfc_cn',
'nfc_cn_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_nfc_cn
EXCEPT ALL
SELECT * FROM validation_new_nfc_cn
) AS differences
),
0
UNION ALL
SELECT
'nfc_cn',
'nfc_cn_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_nfc_cn
EXCEPT ALL
SELECT * FROM validation_old_nfc_cn
) AS differences
),
0
UNION ALL
SELECT
'market_ta',
'market_ta_row_count',
(SELECT COUNT(*) FROM validation_new_market_ta),
(SELECT COUNT(*) FROM validation_old_market_ta)
UNION ALL
SELECT
'market_ta',
'market_ta_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_market_ta
EXCEPT ALL
SELECT * FROM validation_new_market_ta
) AS differences
),
0
UNION ALL
SELECT
'market_ta',
'market_ta_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_market_ta
EXCEPT ALL
SELECT * FROM validation_old_market_ta
) AS differences
),
0
UNION ALL
SELECT
'product_multi_manufacturer',
'prod_multi_row_count',
(SELECT COUNT(*) FROM validation_new_prod_multi),
(SELECT COUNT(*) FROM validation_old_prod_tmp)
UNION ALL
SELECT
'product_multi_manufacturer',
'prod_multi_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_prod_tmp
EXCEPT ALL
SELECT * FROM validation_new_prod_multi
) AS differences
),
0
UNION ALL
SELECT
'product_multi_manufacturer',
'prod_multi_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_prod_multi
EXCEPT ALL
SELECT * FROM validation_old_prod_tmp
) AS differences
),
0
UNION ALL
SELECT
'pack_ym',
'pack_ym_row_count',
(SELECT COUNT(*) FROM validation_new_pack_ym),
(SELECT COUNT(*) FROM validation_old_pack_ym)
UNION ALL
SELECT
'pack_ym',
'pack_ym_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_pack_ym
EXCEPT ALL
SELECT * FROM validation_new_pack_ym
) AS differences
),
0
UNION ALL
SELECT
'pack_ym',
'pack_ym_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_pack_ym
EXCEPT ALL
SELECT * FROM validation_old_pack_ym
) AS differences
),
0
UNION ALL
SELECT
'sales_fact_cht',
'fact_cht_row_count',
(SELECT COUNT(*) FROM validation_new_fact_cht),
(SELECT COUNT(*) FROM validation_old_fact_cht)
UNION ALL
SELECT
'sales_fact_cht',
'fact_cht_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_fact_cht
EXCEPT ALL
SELECT * FROM validation_new_fact_cht
) AS differences
),
0
UNION ALL
SELECT
'sales_fact_cht',
'fact_cht_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_fact_cht
EXCEPT ALL
SELECT * FROM validation_old_fact_cht
) AS differences
),
0
UNION ALL
SELECT
'date',
'date_row_count',
(SELECT COUNT(*) FROM validation_new_date),
(SELECT COUNT(*) FROM validation_old_date)
UNION ALL
SELECT
'date',
'date_legacy_minus_new',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_date
EXCEPT ALL
SELECT * FROM validation_new_date
) AS differences
),
0
UNION ALL
SELECT
'date',
'date_new_minus_legacy',
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_date
EXCEPT ALL
SELECT * FROM validation_old_date
) AS differences
),
0
UNION ALL
SELECT
'framework',
'except_all_null_semantics',
(
SELECT COUNT(*)
FROM (
SELECT CAST(NULL AS STRING) AS nullable_value
EXCEPT ALL
SELECT CAST(NULL AS STRING) AS nullable_value
) AS differences
),
0
)
SELECT
area,
check_name,
actual_value,
expected_value,
actual_value = expected_value AS passed
FROM compatibility_checks;
-- COMMAND ----------
SELECT
area,
check_name,
actual_value,
expected_value,
passed
FROM validation_01_02_compatibility_checks
ORDER BY area, check_name;
-- COMMAND ----------
-- Grain quality metrics on the NEW outputs (duplicate business keys / null required
-- keys). Review metrics, not hard failures.
WITH duplicated_keys AS (
SELECT
'pack_property' AS metric_name,
(
SELECT COUNT(*)
FROM (
SELECT PACK_COD
FROM validation_new_pack_property
GROUP BY PACK_COD
HAVING COUNT(*) > 1
) AS duplicates
) AS metric_value
UNION ALL
SELECT
'manu_corp_duplicate_manufacturer_id',
(
SELECT COUNT(*)
FROM (
SELECT Manufacturer_ID
FROM validation_new_manufacturer_corp
GROUP BY Manufacturer_ID
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'geo_duplicate_audit_cod',
(
SELECT COUNT(*)
FROM (
SELECT AUDIT_COD
FROM validation_new_geo
GROUP BY AUDIT_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'corp_cn_duplicate_corp_cod',
(
SELECT COUNT(*)
FROM (
SELECT CORP_COD
FROM validation_new_corp_cn
GROUP BY CORP_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'manu_cn_duplicate_manu_cod',
(
SELECT COUNT(*)
FROM (
SELECT MANU_COD
FROM validation_new_manu_cn
GROUP BY MANU_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'prod_cn_duplicate_prod_cod',
(
SELECT COUNT(*)
FROM (
SELECT PROD_COD
FROM validation_new_prod_cn
GROUP BY PROD_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'atc_cn_duplicate_path',
(
SELECT COUNT(*)
FROM (
SELECT ATC1_COD, ATC2_COD, ATC3_COD, ATC4_COD
FROM validation_new_atc_cn
GROUP BY ATC1_COD, ATC2_COD, ATC3_COD, ATC4_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'nfc_cn_duplicate_path',
(
SELECT COUNT(*)
FROM (
SELECT APP1_COD, APP2_COD, APP3_COD
FROM validation_new_nfc_cn
GROUP BY APP1_COD, APP2_COD, APP3_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'market_ta_duplicate_market_ta',
(
SELECT COUNT(*)
FROM (
SELECT MARKET, TA
FROM validation_new_market_ta
GROUP BY MARKET, TA
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'prod_multi_duplicate_prod_cod',
(
SELECT COUNT(*)
FROM (
SELECT PROD_COD
FROM validation_new_prod_multi
GROUP BY PROD_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'pack_ym_duplicate_ym_pack_id',
(
SELECT COUNT(*)
FROM (
SELECT ym, pack_id
FROM validation_new_pack_ym
GROUP BY ym, pack_id
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'fact_duplicate_ym_audit_pack',
(
SELECT COUNT(*)
FROM (
SELECT YM, AUDIT_COD, PACK_COD
FROM validation_new_fact
GROUP BY YM, AUDIT_COD, PACK_COD
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'date_duplicate_ym',
(
SELECT COUNT(*)
FROM (
SELECT YM
FROM validation_new_date
GROUP BY YM
HAVING COUNT(*) > 1
) AS duplicates
)
UNION ALL
SELECT
'market_multi_kc_per_market_pack_prod',
(
SELECT COUNT(*)
FROM (
SELECT market, PACK_COD, PROD_COD
FROM validation_new_market
GROUP BY market, PACK_COD, PROD_COD
HAVING COUNT(DISTINCT Key_Competitor) > 1
) AS duplicates
)
)
SELECT
metric_name,
metric_value
FROM duplicated_keys
UNION ALL
SELECT
'pack_property_null_pack_cod',
COUNT_IF(PACK_COD IS NULL)
FROM validation_new_pack_property
UNION ALL
SELECT
'manu_corp_null_manufacturer_id',
COUNT_IF(Manufacturer_ID IS NULL)
FROM validation_new_manufacturer_corp
UNION ALL
SELECT
'geo_null_audit_cod',
COUNT_IF(AUDIT_COD IS NULL)
FROM validation_new_geo
UNION ALL
SELECT
'corp_cn_null_corp_cod',
COUNT_IF(CORP_COD IS NULL)
FROM validation_new_corp_cn
UNION ALL
SELECT
'manu_cn_null_manu_cod',
COUNT_IF(MANU_COD IS NULL)
FROM validation_new_manu_cn
UNION ALL
SELECT
'prod_cn_null_prod_cod',
COUNT_IF(PROD_COD IS NULL)
FROM validation_new_prod_cn
UNION ALL
SELECT
'atc_cn_null_atc1_cod',
COUNT_IF(ATC1_COD IS NULL)
FROM validation_new_atc_cn
UNION ALL
SELECT
'nfc_cn_null_app1_cod',
COUNT_IF(APP1_COD IS NULL)
FROM validation_new_nfc_cn
UNION ALL
SELECT
'market_ta_null_market',
COUNT_IF(MARKET IS NULL)
FROM validation_new_market_ta
UNION ALL
SELECT
'market_ta_null_ta',
COUNT_IF(TA IS NULL)
FROM validation_new_market_ta
UNION ALL
SELECT
'pack_ym_null_ym',
COUNT_IF(ym IS NULL)
FROM validation_new_pack_ym
UNION ALL
SELECT
'pack_ym_null_pack_id',
COUNT_IF(pack_id IS NULL)
FROM validation_new_pack_ym
UNION ALL
SELECT
'pack_ym_null_pack_code',
COUNT_IF(pack_code IS NULL)
FROM validation_new_pack_ym
UNION ALL
SELECT
'fact_null_pack_cod',
COUNT_IF(PACK_COD IS NULL)
FROM validation_new_fact
UNION ALL
SELECT
'fact_null_audit_cod',
COUNT_IF(AUDIT_COD IS NULL)
FROM validation_new_fact
UNION ALL
SELECT
'date_null_ym',
COUNT_IF(YM IS NULL)
FROM validation_new_date
UNION ALL
SELECT
'market_null_market',
COUNT_IF(market IS NULL)
FROM validation_new_market
UNION ALL
SELECT
'market_null_pack_cod',
COUNT_IF(PACK_COD IS NULL)
FROM validation_new_market
ORDER BY metric_name;
-- COMMAND ----------
-- Geo province match-rate: how much of the Pharbers province fact is covered by the
-- new dws_ext_td_ims_geo PROVINCE_C mapping (the layer fix replaced the legacy
-- dm.dm_td_geography CONCAT normalization). Unmatched provinces produce NULL
-- AUDIT_COD in the fact and must be reviewed.
SELECT
(SELECT COUNT(DISTINCT province_c) FROM dwd.dwd_gnd_pharbers_prov_fact) AS total_distinct_provinces,
(
SELECT COUNT(*)
FROM (
SELECT DISTINCT province_c
FROM dwd.dwd_gnd_pharbers_prov_fact
WHERE province_c IN (SELECT province_c FROM validation_new_geo)
) AS matched
) AS matched_distinct_provinces,
(
SELECT COUNT(*)
FROM (
SELECT DISTINCT province_c
FROM dwd.dwd_gnd_pharbers_prov_fact
WHERE province_c IN (SELECT province_c FROM validation_new_geo)
) AS matched
) / NULLIF(
(SELECT COUNT(DISTINCT province_c) FROM dwd.dwd_gnd_pharbers_prov_fact),
0
) AS province_match_rate,
(SELECT COUNT(*) FROM dwd.dwd_gnd_pharbers_prov_fact) AS total_fact_rows,
(
SELECT COUNT(*)
FROM dwd.dwd_gnd_pharbers_prov_fact
WHERE province_c IN (SELECT province_c FROM validation_new_geo)
) AS matched_fact_rows,
(
SELECT COUNT(*)
FROM dwd.dwd_gnd_pharbers_prov_fact
WHERE province_c IN (SELECT province_c FROM validation_new_geo)
) / NULLIF(
(SELECT COUNT(*) FROM dwd.dwd_gnd_pharbers_prov_fact),
0
) AS row_match_rate;
-- COMMAND ----------
-- Unmatched provinces (present in the fact but missing from dws_ext_td_ims_geo):
-- these rows become NULL AUDIT_COD groups in the promoted fact. Review list.
SELECT
province_c
FROM (
SELECT DISTINCT province_c
FROM dwd.dwd_gnd_pharbers_prov_fact
EXCEPT ALL
SELECT province_c
FROM validation_new_geo
) AS unmatched
ORDER BY province_c
LIMIT 50;
-- COMMAND ----------
-- Sales fact province (Part 2) metrics. NOT hard-gated: the province mapping was
-- rewritten (geo layer fix), so per-row equality is not guaranteed. Large deltas or
-- a jump in NULL AUDIT_COD rows indicate a geo coverage regression to review.
SELECT
(SELECT COUNT(*) FROM validation_old_fact WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT') AS old_province_rows,
(SELECT COUNT(*) FROM validation_new_fact WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT') AS new_province_rows,
(SELECT COUNT_IF(AUDIT_COD IS NULL) FROM validation_old_fact) AS old_null_audit_rows,
(SELECT COUNT_IF(AUDIT_COD IS NULL) FROM validation_new_fact) AS new_null_audit_rows,
(SELECT COUNT_IF(PACK_COD IS NULL) FROM validation_old_fact) AS old_null_pack_rows,
(SELECT COUNT_IF(PACK_COD IS NULL) FROM validation_new_fact) AS new_null_pack_rows,
(SELECT COUNT(*) FROM validation_old_fact) AS old_fact_total_rows,
(SELECT COUNT(*) FROM validation_new_fact) AS new_fact_total_rows,
(SELECT SUM(MTH00UN) FROM validation_old_fact WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT') AS old_province_mth00un,
(SELECT SUM(MTH00UN) FROM validation_new_fact WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT') AS new_province_mth00un,
(
SELECT SUM(MTH00UN)
FROM validation_new_fact
WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT'
) - (
SELECT SUM(MTH00UN)
FROM validation_old_fact
WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT'
) AS province_mth00un_delta,
(
SELECT SUM(MTH00LC)
FROM validation_new_fact
WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT'
) - (
SELECT SUM(MTH00LC)
FROM validation_old_fact
WHERE AUDIT_COD IS NULL OR AUDIT_COD <> 'CHT'
) AS province_mth00lc_delta;
-- COMMAND ----------
-- Pack-YM rolling window metrics: the recent 5-year window (ym + 500 > max ym) is
-- refreshed every run; older rows are retained unchanged. Compare window extents.
SELECT
(SELECT MAX(ym) FROM validation_old_pack_ym) AS old_max_ym,
(SELECT MAX(ym) FROM validation_new_pack_ym) AS new_max_ym,
(
SELECT COUNT(*)
FROM validation_old_pack_ym
WHERE ym + 500 > (SELECT MAX(ym) FROM validation_old_pack_ym)
) AS old_window_rows,
(
SELECT COUNT(*)
FROM validation_new_pack_ym
WHERE ym + 500 > (SELECT MAX(ym) FROM validation_new_pack_ym)
) AS new_window_rows,
(
SELECT COUNT(*)
FROM validation_old_pack_ym
WHERE ym + 500 <= (SELECT MAX(ym) FROM validation_old_pack_ym)
) AS old_retained_rows,
(
SELECT COUNT(*)
FROM validation_new_pack_ym
WHERE ym + 500 <= (SELECT MAX(ym) FROM validation_new_pack_ym)
) AS new_retained_rows,
(SELECT COUNT(DISTINCT ym) FROM validation_old_pack_ym) AS old_distinct_ym,
(SELECT COUNT(DISTINCT ym) FROM validation_new_pack_ym) AS new_distinct_ym;
-- COMMAND ----------
-- Date dimension metrics (hard EXCEPT ALL already gates equality).
SELECT
(SELECT COUNT(*) FROM validation_old_date) AS old_rows,
(SELECT COUNT(*) FROM validation_new_date) AS new_rows,
(SELECT MAX(YM) FROM validation_old_date) AS old_max_ym,
(SELECT MAX(YM) FROM validation_new_date) AS new_max_ym,
(SELECT COUNT_IF(DATE_FLAG = 'R') FROM validation_old_date) AS old_report_month_rows,
(SELECT COUNT_IF(DATE_FLAG = 'R') FROM validation_new_date) AS new_report_month_rows;
-- COMMAND ----------
-- Market quality metrics. NOT hard-gated: the legacy MERGE could abort on multi-match
-- and KC ROW_NUMBER tie-breaks are engine-defined; the anti-join rewrite matches the
-- MERGE result in every non-error case. The soft EXCEPT ALL counts below must be 0
-- for a clean run -- treat any difference as a review item, not an automatic failure.
SELECT
(SELECT COUNT(*) FROM validation_old_market) AS old_rows,
(SELECT COUNT(*) FROM validation_new_market) AS new_rows,
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_old_market
EXCEPT ALL
SELECT * FROM validation_new_market
) AS differences
) AS market_legacy_minus_new,
(
SELECT COUNT(*)
FROM (
SELECT * FROM validation_new_market
EXCEPT ALL
SELECT * FROM validation_old_market
) AS differences
) AS market_new_minus_legacy,
(SELECT COUNT_IF(Market_Ratio = '1') FROM validation_old_market) AS old_ratio_one_rows,
(SELECT COUNT_IF(Market_Ratio = '1') FROM validation_new_market) AS new_ratio_one_rows,
(SELECT COUNT_IF(Key_Competitor = 'Others') FROM validation_old_market) AS old_kc_others_rows,
(SELECT COUNT_IF(Key_Competitor = 'Others') FROM validation_new_market) AS new_kc_others_rows;
-- COMMAND ----------
-- ETL timestamp metrics (informational only): ETL_INSERT_DT/ETL_UPDATE_DT are
-- regenerated by each run, so differences are expected except for geo, which copies
-- the staging timestamps unchanged. Counts rows whose ETL_INSERT_DT differs between
-- the old baseline and the new table on the business key.
SELECT
'geo' AS table_name,
'etl_insert_mismatch_rows' AS metric_name,
(
SELECT COUNT(*)
FROM dws.dws_ims_td_geo AS old_t
JOIN dws.dws_ext_td_ims_geo AS new_t
ON old_t.AUDIT_COD = new_t.AUDIT_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
) AS metric_value
UNION ALL
SELECT
'corporation_cn',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_corp_cn AS old_t
JOIN dws.dws_ext_td_ims_corporation_cn AS new_t
ON old_t.CORP_COD = new_t.CORP_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'manufacturer_cn',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_manu_cn AS old_t
JOIN dws.dws_ext_td_ims_manufacturer_cn AS new_t
ON old_t.MANU_COD = new_t.MANU_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'product_cn',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_prod_cn AS old_t
JOIN dws.dws_ext_td_ims_product_cn AS new_t
ON old_t.PROD_COD = new_t.PROD_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'atc_cn',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_atc_cn AS old_t
JOIN dws.dws_ext_td_ims_atc_cn AS new_t
ON old_t.ATC1_COD = new_t.ATC1_COD
AND old_t.ATC2_COD = new_t.ATC2_COD
AND old_t.ATC3_COD = new_t.ATC3_COD
AND old_t.ATC4_COD = new_t.ATC4_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'nfc_cn',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_nfc_cn AS old_t
JOIN dws.dws_ext_td_ims_nfc_cn AS new_t
ON old_t.APP1_COD = new_t.APP1_COD
AND old_t.APP2_COD = new_t.APP2_COD
AND old_t.APP3_COD = new_t.APP3_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'date',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_date AS old_t
JOIN dws.dws_ext_td_ims_date AS new_t
ON old_t.YM = new_t.YM
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'pack_ym',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_pack_ym AS old_t
JOIN dws.dws_ext_td_ims_pack_ym AS new_t
ON old_t.ym = new_t.ym
AND old_t.pack_id = new_t.pack_id
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
UNION ALL
SELECT
'market',
'etl_insert_mismatch_rows',
(
SELECT COUNT(*)
FROM dws.dws_ims_td_market AS old_t
JOIN dws.dws_ext_td_ims_market AS new_t
ON old_t.market = new_t.market
AND old_t.PACK_COD = new_t.PACK_COD
AND old_t.PROD_COD = new_t.PROD_COD
WHERE NOT (old_t.ETL_INSERT_DT = new_t.ETL_INSERT_DT)
)
ORDER BY table_name;
-- COMMAND ----------
-- Block downstream migration when any hard compatibility check fails.
SELECT IF(
(SELECT COUNT_IF(NOT passed) FROM validation_01_02_compatibility_checks) = 0,
'All 01/02 refactor compatibility checks passed.',
raise_error(
CONCAT(
'01/02 refactor compatibility validation failed: ',
CAST((SELECT COUNT_IF(NOT passed) FROM validation_01_02_compatibility_checks) AS STRING),
' check(s) did not pass.'
)
)
) AS validation_result;
-- COMMAND ----------
-- Coverage report: hard checks per area (all must show failed = 0).
SELECT
area,
COUNT(*) AS checks,
COUNT_IF(passed) AS passed,
COUNT_IF(NOT passed) AS failed
FROM validation_01_02_compatibility_checks
GROUP BY area
ORDER BY area;
-- COMMAND ----------
-- MAGIC %md
-- MAGIC # Coverage and caveats
-- MAGIC
-- MAGIC ## Hard-gated areas (row count + bidirectional EXCEPT ALL, ETL timestamps excluded)
-- MAGIC
-- MAGIC | Area | Old baseline | New target | Business columns |
-- MAGIC | --- | --- | --- | --- |
-- MAGIC | manufacturer-corporation | dwd.dwd_ims_td_manufacturer_corp | dws.dws_ext_td_ims_manufacturer_corporation | 9 known columns |
-- MAGIC | pack property | dwd.dwd_ims_td_pack_property | dws.dws_ext_td_ims_pack_property | 31 |
-- MAGIC | geo | dws.dws_ims_td_geo | dws.dws_ext_td_ims_geo | 9 business + ETL as metric |
-- MAGIC | corporation CN | dws.dws_ims_td_corp_cn | dws.dws_ext_td_ims_corporation_cn | 3 |
-- MAGIC | manufacturer CN | dws.dws_ims_td_manu_cn | dws.dws_ext_td_ims_manufacturer_cn | 3 |
-- MAGIC | product CN (incl. RANK_TYPE) | dws.dws_ims_td_prod_cn | dws.dws_ext_td_ims_product_cn | 5 |
-- MAGIC | ATC CN | dws.dws_ims_td_atc_cn | dws.dws_ext_td_ims_atc_cn | 12 |
-- MAGIC | NFC CN | dws.dws_ims_td_nfc_cn | dws.dws_ext_td_ims_nfc_cn | 9 |
-- MAGIC | market-TA | dws.dws_ims_td_market_ta | dws.dws_ext_td_ims_market_ta | 2 |
-- MAGIC | product multi-manufacturer | tmp.tmp_ims_td_prod_tmp | dws.dws_ext_td_ims_product_multi_manufacturer | 1 |
-- MAGIC | pack-YM rolling window | dws.dws_ims_td_pack_ym | dws.dws_ext_td_ims_pack_ym | 3 |
-- MAGIC | sales fact (national CHT rows) | tmp.tmp_ims_tf_fact_sales | dws.dws_ext_tf_ims_chpa_sales | 9 |
-- MAGIC | date | dws.dws_ims_td_date | dws.dws_ext_td_ims_date | 7 |
-- MAGIC
-- MAGIC ## Metrics-only areas (documented reasons, no hard failure)
-- MAGIC
-- MAGIC - **Sales fact province rows**: geo layer fix changed the province join from
-- MAGIC `dm.dm_td_geography` CONCAT normalization to a direct
-- MAGIC `dws.dws_ext_td_ims_geo.PROVINCE_C` match. Province match-rate, NULL AUDIT_COD
-- MAGIC rows and value deltas are reported; a low match-rate or a large delta must be
-- MAGIC reviewed before migration.
-- MAGIC - **Market**: legacy MERGE multi-match failure modes and engine-defined
-- MAGIC `ROW_NUMBER()` KC tie-breaks mean equality is expected but not guaranteed; row
-- MAGIC counts, soft EXCEPT ALL counts, Market_Ratio = '1' share and KC 'Others' share
-- MAGIC are reported for review.
-- MAGIC - **ETL timestamps**: regenerated every run; mismatch counts are informational.
-- MAGIC - **01_dwd in-place jobs** (code padding, market config refresh, time-window
-- MAGIC defaults, ManufacturerType_ID fix, Pharbers ingest) write the same physical
-- MAGIC tables as the legacy CHPA 01 jobs, so no old/new comparison applies; their
-- MAGIC effect is covered transitively through the DWS outputs above.
-- MAGIC
-- MAGIC ## Known limitations
-- MAGIC
-- MAGIC - Manufacturer-corporation compares only the 9 columns referenced by live
-- MAGIC consumers; the legacy `T1.*` may carry additional columns not compared
-- MAGIC (UNKNOWN-SCHEMA caveat -- DESCRIBE the source before go-live).
-- MAGIC - market-TA compares only MARKET/TA; legacy `SELECT *` may carry extra source
-- MAGIC columns that are intentionally not carried into the new table.
-- MAGIC - All comparisons assume the old and new jobs ran on the same source snapshot;
-- MAGIC rerun the full old/new chain on the same snapshot before trusting the gate.
-- MAGIC - `EXCEPT ALL` NULL semantics are asserted by the framework guard; if that check
-- MAGIC fails, this engine does not compare NULLs as equal and the bidirectional
-- MAGIC checks cannot be trusted.
Reference in New Issue View Git Blame Copy Permalink