Ensuring Data Warehouse Reliability: A Practical Guide to Data Lineage and Testing Methods

1. Data Lineage Overview

Yozan's Data Report Center provides over 30 pages, 100+ reports and 400+ metrics, supporting merchants in data‑driven store operations. The underlying data pipeline runs thousands of daily tasks across application service, data gateway, storage, and warehouse layers. The warehouse and application layers are the most change‑prone and risk‑heavy, requiring focused quality controls.

2. Data Layer Testing

2.1 Overview

Data‑layer quality is evaluated on three dimensions: timeliness, completeness, and accuracy.

2.2 Timeliness

Timeliness ensures data is produced on schedule, focusing on scheduling time, task priority, and data deadline. The deadline is the strict latest production time. Two strategies are used:

Monitor offline task completion via the Yozan job platform; missed deadlines trigger email, WeChat or phone alerts.

Check table or partition row counts via an automated interface; a zero result indicates no data output.

Additional alerts monitor repeated failures or retries, supplementing deadline alerts.

2.3 Completeness

Completeness checks whether data is “full”. Two perspectives are used:

Data not excessive : verify total rows, uniqueness of primary keys, and absence of duplicate records.

Data not missing : ensure important fields (primary keys, enums, dates) are non‑null.

Tests are split into table‑level and field‑level checks.

Table‑level: compare total row count or size over time; abnormal changes indicate issues.

Partition‑level: compare daily partition sizes; large deviations flag problems.

Field‑level checks include:

Uniqueness (e.g.,

select count(order_no), count(distinct order_no) from ods.xx_order

).

Non‑null validation (e.g., select count(*) from ods.xx_order where order_no is null).

Enum value validation (e.g., select shop_type from ods.xx_order group by shop_type).

Data format validation such as date strings.

These checks are packaged into the “Data Shape Tool” with universal rules (primary‑key uniqueness) and type‑specific rules (numeric, string, enum, date) graded by severity.

2.4 Accuracy

Accuracy testing is more exploratory and includes:

Self‑check : simple rules like numeric metrics > 0 or ratios between 0‑1 (e.g.,

select count(*) from dw.dws_xx_order where pay_price<0</code>).</li><li><strong>Horizontal table comparison</strong> (within the same table): verify logical relationships, e.g., order count ≥ distinct buyer count.</li><li><strong>Horizontal cross‑table comparison</strong>: compare identical fields across similar tables (e.g., Hive payment tables) or across storage systems (Kylin vs MySQL).</li><li><strong>Vertical comparison</strong>: ensure upstream and downstream tables (e.g., detail vs aggregate) produce consistent results.</li><li><strong>Code review</strong>: clarify metric definitions, verify join types, filter conditions, aggregation functions (SUM vs COUNT(DISTINCT)), and insert semantics (overwrite vs append, column order).</li></ul><h2>3. Application Layer Testing</h2><p>Beyond standard front‑end and API tests, data‑application testing adds:</p><ul><li><strong>Degradation strategy</strong>: decide whether to show a “blue bar” indicating pending offline data (current time + 2 h).</li><li>Handle division‑by‑zero cases in ratio metrics; front‑end displays “‑”.</li></ul><h2>4. Future Plans</h2><p>The current data‑comparison tool lacks support for SQL functions, covering only ~50 % of manual tests. Roadmap includes adding SUM, COUNT, MAX, MIN to raise coverage to > 75 % and integrating shape checks and comparison into an online inspection service.<p>Static SQL analysis (insert checks, join‑on uniqueness, column order) will be incorporated into a big‑data testing service and shared across business lines.</p>