Order Fact Table – Data Profiling Analysis Results (Template + Illustrative Example)
Scope and grain
- Assumed grain: one row per order (order header). If your fact grain is order-line, adjust metrics that rely on totals and referential checks to the product dimension accordingly.
- Intended use: accuracy, completeness, consistency, and integrity assessment to inform cleansing, validation rules, and monitoring.
Table overview (replace illustrative values with actuals)
- Row count: [Example] 1,238,417
- Coverage window (order_date): [Example] 2023-01-01 to 2025-09-20
- Primary key: order_id
- Uniqueness: [Example] 99.997% unique
- Duplicate order_ids: [Example] 32
- Freshness (max(updated_at) vs extraction): [Example] 1h 42m
Column-level profiling (core fields)
- order_id
- Type: string/integer (non-nullable)
- Distinct count: [Example] 1,238,385
- Null rate: [Example] 0.000%
- Duplicates: [Example] 32 rows across 16 ids
- Notes: Enforce not null + uniqueness at source; quarantine duplicate keys.
- order_date (date/timestamp)
- Min/Max: [Example] 2023-01-01 / 2025-09-20
- Null rate: [Example] 0.000%
- Future-dated (> current_date): [Example] 0.06%
- Invalid dates/timezones: [Example] 0.00% invalid parse; mixed TZ flags detected
- Seasonality: [Example] weekend share 27%; end-of-month spikes present
- customer_id (FK to dim_customer)
- Null rate: [Example] 0.42%
- Distinct count: [Example] 214,903
- Orphan rate vs dim_customer: [Example] 0.18%
- Notes: Backfill anonymous/guest strategy or surrogate for nulls; address orphans via late-arriving dimension handling.
- currency_code (ISO 4217)
- Cardinality: [Example] 5 (USD, EUR, GBP, CAD, AUD)
- Null rate: [Example] 0.00%
- Invalid values: [Example] 0.04% (case issues: 'usd')
- Notes: Standardize to uppercase; enforce against dim_currency.
- order_status (enumerated)
- Allowed set (example): Pending, Completed, Cancelled, Refunded, Partially_Refunded
- Null rate: [Example] 0.00%
- Top distribution: [Example] Completed 82.5%; Pending 9.8%; Cancelled 5.6%; Refunded 2.1%
- Invalid statuses: [Example] 0.03% (legacy codes)
- Notes: Map legacy to canonical set; add check constraint or validation UDF.
- sales_channel (enumerated)
- Values: [Example] Web, App, Store, Marketplace
- Null rate: [Example] 0.11%
- Distribution: [Example] Web 56%, App 22%, Store 18%, Marketplace 4%
- Notes: Normalize spelling/casing; handle unknowns explicitly.
- subtotal_amount (numeric)
- Null rate: [Example] 0.01%
- Min/Max: [Example] 0.00 / 12,300.00
- P50/P95: [Example] 72.10 / 420.00
- Negative values: [Example] 0.00%
- Currency consistency: [Example] 100% aligned to currency_code
- Notes: Validate precision/scale; non-negative expectation except adjustments per policy.
- discount_amount (numeric; sign convention must be confirmed)
- Null rate: [Example] 0.03%
- Min/Max: [Example] 0.00 / 2,000.00 (stored as positive discount to be subtracted)
- P50/P95: [Example] 0.00 / 20.00
- Unexpected sign: [Example] 0.07% negative values
- Notes: Enforce consistent sign; reconcile with pricing engine rules.
- tax_amount (numeric)
- Null rate: [Example] 0.02%
- Min/Max: [Example] 0.00 / 1,100.00
- Negative values: [Example] 0.01% (likely tax reversals)
- Notes: Negative taxes should align with refund/cancel events.
- shipping_amount (numeric)
- Null rate: [Example] 0.05%
- Min/Max: [Example] 0.00 / 400.00
- Zero with shipped status: [Example] 3.9% (free shipping or missing fees)
- Notes: Cross-check with shipping method and promo flags.
- total_amount (numeric; order grand total)
- Null rate: [Example] 0.01%
- Min/Max: [Example] -2,500.00 / 12,499.00
- P50/P95: [Example] 79.99 / 459.00
- Negative totals: [Example] 0.12% (refunds); 413 rows negative with non-refund status
- Notes: Enforce consistency with status and calculation rule.
- payment_method (categorical)
- Null rate: [Example] 2.30%
- Top values: [Example] Visa 39%, Mastercard 28%, PayPal 18%, Amex 8%, COD 3%, Other 4%
- Incoherent with status (e.g., captured but pending): [Example] 0.09%
- Notes: Validate against payment provider codes; ensure PCI-safe tokenization fields only.
- created_at / updated_at (timestamps)
- Null rates: [Example] created_at 0.00%; updated_at 0.02%
- updated_at >= created_at: [Example] 96.8% (3.2% violations; clock skew or ingest issues)
- Staleness: [Example] 7.4% not updated > 90 days while still Pending
- Notes: Normalize timezones; enforce monotonic update constraint where applicable.
Cross-field consistency checks
Calculation coherence (define per business rule)
- Expected rule (example): total_amount ≈ subtotal_amount − discount_amount + tax_amount + shipping_amount
- Tolerance (epsilon): [Example] 0.01 currency units
- Result: [Example] 98.9% within tolerance; 1.1% mismatches
- Root causes (observed): [Example] rounding, missing shipping after partial refund, discount applied post-tax in some sources
- Action: Standardize calculation order and rounding precision; compute canonical totals in ETL.
Status-to-amount coherence
- Completed: total_amount > 0.00 — [Example] 99.6% pass
- Cancelled (no fulfillment): total_amount = 0.00 — [Example] 92.3% pass
- Refunded: total_amount <= 0.00 or separate refund fact — [Example] 88.1% pass
- Action: Encode explicit monetary state model (authorization, capture, refund) and align status semantics.
Temporal coherence
- order_date within [created_at − 1d, created_at + 1d]: [Example] 99.2% pass
- updated_at present for state changes: [Example] 94.7% pass
- Action: Recompute order_date from event stream or enforce event-sourced derivation.
Referential integrity (to dimensions)
- customer_id in dim_customer: [Example] 99.82% (0.18% orphan)
- currency_code in dim_currency: [Example] 99.96%
- sales_channel in dim_channel: [Example] 99.89%
- date keys resolvable in dim_date: [Example] 99.94%
- Action: Late-arriving dimension handling, conformance mappings, and reject/quarantine policies.
Outliers and anomaly signals
- Total amount outliers (> P95 × 10): [Example] 27 orders; investigate high-value promotions or currency scaling errors.
- Negative subtotal or tax: [Example] 0.02% combined; likely corrective entries; confirm policy.
- Daily order volume anomalies (7-day z-score > 3): [Example] 2 spikes (marketing campaigns) and 1 dip (ETL delay).
- Duplicate order_id with differing amounts: [Example] 11 cases; deduplicate by latest updated_at or source_of_truth.
Data quality risks identified (examples)
- Inconsistent total computation across sources leads to 1.1% mismatches.
- Status semantics not aligned to monetary state (refund/cancel), producing negative totals with non-refund statuses.
- Small but material orphan FK rate on customer_id (0.18%).
- Timestamp incoherence (3.2%) likely due to timezone/clock issues.
- Casing/format issues in currency_code and categorical fields.
Recommended cleansing and validation rules
- Enforce order_id uniqueness; reject duplicates or retain highest updated_at per id.
- Standardize calculation: compute canonical_total with fixed order and rounding; compare to source totals and flag discrepancies > epsilon.
- Enforce non-negative constraints for subtotal, tax, shipping; define and document discount sign convention; correct records violating the chosen convention.
- Status-amount rule set (examples):
- Completed: total_amount > 0 and payment_captured = true
- Cancelled: total_amount = 0 and fulfillment_state = none
- Refunded: total_amount <= 0 or attach linked refund records
- Referential integrity checks on load; quarantine or delayed-load for late dimensions.
- Normalize enumerations (uppercase currency_code, trim spaces, canonical status/channel values).
- Time normalization to UTC; enforce updated_at >= created_at.
Monitoring KPIs and thresholds (set alerts)
- PK duplicates: target 0; warn > 0, critical > 10/day
- FK orphan rate (customer_id): target < 0.10%; warn ≥ 0.25%
- Null rate by monetary fields: target 0; warn ≥ 0.01%
- Total calculation mismatch rate: target < 0.50%; warn ≥ 1.00%
- Negative totals with non-refund status: target 0; critical ≥ 5/day
- Future-dated orders: target 0; warn ≥ 0.01%
- Data freshness: max(updated_at) lag < 2h; warn ≥ 4h
Notes on interpretation
- All numeric “Example” values are illustrative to show expected outputs and typical ranges. Replace with computed metrics from your dataset.
- Confirm business rules for total calculation, discount sign, refund handling, and status semantics before enforcing rules.
If you provide a schema sample and row extracts, I can replace the illustrative figures with precise metrics and produce a finalized profiling report.
