Data Quality Profile: One Row Per Column, One Line

Whoever is handed a new feed of orders, from a new supplier system or a migration, checks it before anyone builds a dashboard on it: how many rows, how many blank costs, whether a figure or two sits so far outside the rest that it looks like a misplaced decimal point. That means a run of separate checks, a count here, a null count there, a sort to the top and bottom to eyeball the extremes, each one its own query against the same table. Filter the orders down to one supplier or one week, and every one of those checks describes a population that is no longer on screen, so the audit gets run again from the first step.

Data profiling in JavaScript, built into the grid itself, runs all of that as one line instead. Point a second grid at the orders grid with a profile key naming the columns to check, and it comes back with one row per column: how many rows, how many missing, how many distinct values, the minimum, the maximum, the mean, the median, the quartiles, the standard deviation and the count of outliers. Filter the orders grid the same way, and the profile re-derives from whatever is left, because it reads the orders grid rather than a snapshot taken before anyone touched the filter.

That turns the new-feed check from five separate queries, re-run by hand every time the filter changes, into one config object that stays current on its own. Nobody re-runs the null count to see if it is still right, because the grid that shows it is reading the orders grid live. The figures below come from the published package, not from the prose.

Data profiling in JavaScript

import { createGrid } from '@toclocoinc/lattice-grid';

const orders = [
  { id: 1,  region: 'EMEA',     amount: 420, cost: 310 },
  { id: 2,  region: 'APAC',     amount: 180, cost: 165 },
  { id: 3,  region: 'Americas', amount: 960, cost: 540 },
  { id: 4,  region: 'EMEA',     amount: 310, cost: 410 },
  { id: 5,  region: 'Americas', amount: 540, cost: 300 },
  { id: 6,  region: 'APAC',     amount: 275, cost: 180 },
  { id: 7,  region: 'EMEA',     amount: 890, cost: 505 },
  { id: 8,  region: 'Americas', amount: 150, cost: 210 },
  { id: 9,  region: 'APAC',     amount: 410, cost: 260 },
  { id: 10, region: 'EMEA',     amount: 220, cost: 140 },
  { id: 11, region: 'Americas', amount: 730, cost: 410 },
  { id: 12, region: 'APAC',     amount: 95,  cost: 120 },
  { id: 13, region: 'EMEA',     amount: 605, cost: 350 },
  { id: 14, region: 'Americas', amount: 340, cost: 190 },
  { id: 15, region: 'APAC',     amount: 515, cost: 300 },
  { id: 16, region: 'EMEA',     amount: 175, cost: null },
  { id: 17, region: 'Americas', amount: 820, cost: 460 },
  { id: 18, region: 'APAC',     amount: 260, cost: 150 },
  { id: 19, region: 'EMEA',     amount: 450, cost: 980 },
  { id: 20, region: 'Americas', amount: 390, cost: 215 },
];

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',     title: 'Order' },
    { field: 'region', title: 'Region', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
    { field: 'cost',   title: 'Cost',   type: 'number' },
  ],
  rows: orders,
});

// A second grid, one row per column instead of one row per group.
const profile = createGrid(document.querySelector('#profile'), {
  source: {
    mode: 'derived',
    from: book,
    profile: ['amount', 'cost'],
  },
  columns: [
    { field: 'column',   title: 'Column' },
    { field: 'rows',     title: 'Rows',     type: 'number' },
    { field: 'missing',  title: 'Missing',  type: 'number' },
    { field: 'distinct', title: 'Distinct', type: 'number' },
    { field: 'min',      title: 'Min',      type: 'number' },
    { field: 'max',      title: 'Max',      type: 'number' },
    { field: 'mean',     title: 'Mean',     type: 'number' },
    { field: 'median',   title: 'Median',   type: 'number' },
    { field: 'q1',       title: 'Q1',       type: 'number' },
    { field: 'q3',       title: 'Q3',       type: 'number' },
    { field: 'stddev',   title: 'Deviation', type: 'number' },
    { field: 'outliers', title: 'Outliers', type: 'number' },
  ],
});

Filter the orders grid to one region, and profile recomputes every one of those figures for whatever is left, because cost’s missing count, its outlier count and the rest are read off the orders grid on screen rather than off the twenty rows that happened to be there when the page loaded.

React

import React from 'react';
import { createGrid } from '@toclocoinc/lattice-grid';
import '@toclocoinc/lattice-grid/css';
import { createLatticeGrid } from '@toclocoinc/lattice-grid/modules/react';

// Build the component once, at module scope, not on every render.
const LatticeGrid = createLatticeGrid({ React, createGrid });

function OrdersQualityProfile({ orders }) {
  const [book, setBook] = React.useState(null);

  const orderColumns = [
    { field: 'id',     title: 'Order' },
    { field: 'region', title: 'Region', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
    { field: 'cost',   title: 'Cost',   type: 'number' },
  ];

  const profileColumns = [
    { field: 'column',   title: 'Column' },
    { field: 'rows',     title: 'Rows',     type: 'number' },
    { field: 'missing',  title: 'Missing',  type: 'number' },
    { field: 'distinct', title: 'Distinct', type: 'number' },
    { field: 'min',      title: 'Min',      type: 'number' },
    { field: 'max',      title: 'Max',      type: 'number' },
    { field: 'mean',     title: 'Mean',     type: 'number' },
    { field: 'median',   title: 'Median',   type: 'number' },
    { field: 'q1',       title: 'Q1',       type: 'number' },
    { field: 'q3',       title: 'Q3',       type: 'number' },
    { field: 'stddev',   title: 'Deviation', type: 'number' },
    { field: 'outliers', title: 'Outliers', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={profileColumns}
          source={{
            mode: 'derived',
            from: book,
            profile: ['amount', 'cost'],
          }}
          style={{ height: 120 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the profile’s from needs; the profile panel itself only renders once that instance exists. Filtering the orders grid, from its own header or a quick filter, moves the profile the same way it does in the plain example, because both read the grid’s public API, not a copy of its rows taken once and never revisited.

The options used to profile a grid

What the example actually produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, and printing profile’s rows, gives:

cost is missing on one order and carries one outlier, the 980 against an amount of only 450, far enough past the third quartile of 410 to trip Tukey’s fence. amount has neither: twenty distinct values, nothing missing, nothing outside the range the rest of the column sits in. That is the difference between a column worth building on and one worth a second look, read off two rows instead of chased down with five separate checks.

The derived and chained grids guide covers profile alongside grouping, joins, unnesting and chaining a derived grid from a derived grid. For a dashboard that reads a per-rep profile the same way, alongside the totals and leaderboard it is built from, see the sales dashboard demo.