TPC-DI EQ5

TPC-DI benchmark · all TPC-DI queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  'Data Lineage and Audit Trail Validation' AS validation_name,
  batch_id,
  total_records_processed,
  records_with_audit_trail,
  audit_coverage_pct,
  batch_consistency_score,
  CASE
    WHEN audit_coverage_pct >= 95.0 AND batch_consistency_score >= 90.0
    THEN 'PASS'
    WHEN audit_coverage_pct >= 95.0
    THEN 'CONSISTENCY ISSUES'
    ELSE 'AUDIT TRAIL INCOMPLETE'
  END AS audit_status
FROM (
  SELECT
    batch_summary.batch_id,
    batch_summary.total_records_processed,
    batch_summary.records_with_audit_trail,
    batch_summary.records_with_audit_trail / batch_summary.total_records_processed * 100 AS audit_coverage_pct,
    100 /* Calculate consistency score based on batch ID consistency across related tables */ - (
      batch_inconsistencies.inconsistent_records / batch_summary.total_records_processed * 100
    ) AS batch_consistency_score
  FROM (
    SELECT
      1 AS batch_id,
      (
        SELECT
          COUNT(*)
        FROM DimCustomer
        WHERE
          BatchID = 1
      ) + (
        SELECT
          COUNT(*)
        FROM DimAccount
        WHERE
          BatchID = 1
      ) + (
        SELECT
          COUNT(*)
        FROM FactTrade
        WHERE
          BatchID = 1
      ) AS total_records_processed,
      (
        SELECT
          COUNT(*)
        FROM DimCustomer
        WHERE
          BatchID = 1 AND BatchID IS NOT NULL
      ) + (
        SELECT
          COUNT(*)
        FROM DimAccount
        WHERE
          BatchID = 1 AND BatchID IS NOT NULL
      ) + (
        SELECT
          COUNT(*)
        FROM FactTrade
        WHERE
          BatchID = 1 AND BatchID IS NOT NULL
      ) AS records_with_audit_trail
  ) AS batch_summary
  CROSS JOIN (
    SELECT
      (
        SELECT
          COUNT(*)
        FROM FactTrade AS ft
        LEFT JOIN DimCustomer AS dc
          ON ft.SK_CustomerID = dc.SK_CustomerID
        WHERE
          ft.BatchID <> dc.BatchID AND dc.IsCurrent IS TRUE
      ) /* Count records with inconsistent batch references */ + (
        SELECT
          COUNT(*)
        FROM FactTrade AS ft
        LEFT JOIN DimAccount AS da
          ON ft.SK_AccountID = da.SK_AccountID
        WHERE
          ft.BatchID <> da.BatchID AND da.IsCurrent IS TRUE
      ) AS inconsistent_records
  ) AS batch_inconsistencies
  UNION ALL
  SELECT
    batch_summary.batch_id,
    batch_summary.total_records_processed,
    batch_summary.records_with_audit_trail,
    batch_summary.records_with_audit_trail / batch_summary.total_records_processed * 100 AS audit_coverage_pct,
    100 - (
      batch_inconsistencies.inconsistent_records / batch_summary.total_records_processed * 100
    ) AS batch_consistency_score
  FROM (
    SELECT
      2 AS batch_id,
      (
        SELECT
          COUNT(*)
        FROM DimCustomer
        WHERE
          BatchID = 2
      ) + (
        SELECT
          COUNT(*)
        FROM DimAccount
        WHERE
          BatchID = 2
      ) + (
        SELECT
          COUNT(*)
        FROM FactTrade
        WHERE
          BatchID = 2
      ) AS total_records_processed,
      (
        SELECT
          COUNT(*)
        FROM DimCustomer
        WHERE
          BatchID = 2 AND BatchID IS NOT NULL
      ) + (
        SELECT
          COUNT(*)
        FROM DimAccount
        WHERE
          BatchID = 2 AND BatchID IS NOT NULL
      ) + (
        SELECT
          COUNT(*)
        FROM FactTrade
        WHERE
          BatchID = 2 AND BatchID IS NOT NULL
      ) AS records_with_audit_trail
  ) AS batch_summary
  CROSS JOIN (
    SELECT
      (
        SELECT
          COUNT(*)
        FROM FactTrade AS ft
        LEFT JOIN DimCustomer AS dc
          ON ft.SK_CustomerID = dc.SK_CustomerID
        WHERE
          ft.BatchID = 2 AND ft.BatchID <> dc.BatchID AND dc.IsCurrent IS TRUE
      ) AS inconsistent_records
  ) AS batch_inconsistencies
) AS audit_validation
WHERE
  total_records_processed > 0
ORDER BY
  batch_id

Representative DataFrame

No DataFrame implementation is registered for this query.

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import TPCDI

bench = TPCDI(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['EQ5'])

Source

Query text: benchbox/core/tpcdi/query_etl.py