intellimetrics Learning
Building Trustworthy Data Products Module 2 · Know What You Were Given

Profile Before You Promise

Last reviewed · content updated

Intermediate

What you'll learn

~22 min
  • Run a profiling pass that answers the six questions that decide whether a table can support your metric
  • Detect the failure modes an agent-generated profile routinely misses, starting with sampling
  • Produce a written profile artifact you can hand to a source-system owner as evidence

Six questions, before any promise

Profiling is looking at what the data actually contains rather than what it is supposed to contain. Six questions decide whether a table can support the metric you defined in Module 1:

  1. Grain — is one row really one of what you think? Test it: count rows, count distinct on the columns you believe form the key. If they differ, your grain is wrong and every aggregate you build will double-count.
  2. Completeness over time — plot row counts by day or month. Gaps, cliffs, and step changes are where a source stopped sending, a system was replaced, or a backfill overwrote history.
  3. Null rate per column — and specifically, whether nulls cluster in time. A column that is 4% null overall and 100% null before 2021 is a column that did not exist before 2021.
  4. Cardinality — how many distinct values, and does that match reality? Meridian has 1,847 distribution circuits. A circuit_id column with 2,300 distinct values contains something that is not a circuit.
  5. Units and ranges — minimum, maximum, and a handful of examples. This is where you find kilowatts mixed with megawatts, and the negative durations that mean somebody’s timestamps ran backwards.
  6. Time zone and boundary behavior — what time zone are the timestamps in, is it consistent, and what happens at daylight-saving transitions. This one is skipped almost universally and is responsible for a large share of off-by-one-day errors.

The prompt

Profile the table [name] for use as the source of a monthly,
one-row-per-circuit metric.


Produce, as SQL I can read before running:
1) row count, and distinct count on (circuit_id, month) so I can
   confirm grain
2) row counts by month for the full history, so I can see gaps
   and step changes
3) null rate per column, AND null rate per column by year so I can
   see when a column started being populated
4) distinct count and top 20 values for every id-like column
5) min, max, and 5 sample values for every numeric and date column
6) the declared time zone of each timestamp column, and counts of
   rows falling in the daylight-saving transition hours


Run against the FULL table, not a sample. If any query would be
expensive, tell me which and why before running it.

That last paragraph is the important one, and the next section explains why.

What the agent will get wrong

An AI CLI is genuinely excellent at this task. It writes profiling SQL faster and more thoroughly than most people will by hand. It also has four characteristic failure modes, and they are consistent enough to check for every time.

It samples without saying so

Left to its own devices, an agent frequently adds a LIMIT or a TABLESAMPLE to keep queries cheap — and then reports the result as if it described the table. A 0.2% null rate measured on the first 10,000 rows of a table sorted by insertion date tells you about 2019 and nothing about now.

Check: every profile number should be traceable to a query with no LIMIT in it. Ask directly: “which of these numbers came from a sample?”

It reports the average and hides the distribution

“Null rate 4%” is a fact that conceals the useful fact. Nulls concentrated in a date range mean a column was added. Nulls concentrated in one circuit mean a device is broken. The overall rate tells you neither.

Check: insist on the by-period breakdown, which is why it is a separate item in the prompt above.

It accepts the column name as the column meaning

Ask an agent to profile service_start_date and it will produce date statistics and a sentence about service start dates. It has no way to know the column holds conversion dates. It will not flag that 340,000 customers apparently started service on 2019-04-01.

Check: look at the min/max and the top values yourself. A suspicious cluster on a single date is the signature of a migration, not of a business event.

It smooths over the thing you most need to see

Agents are trained toward helpful summaries. A profile that ends “the table appears clean and suitable for your analysis” is the highest-risk output in this lesson, because it is a conclusion the agent is not positioned to draw and you are inclined to accept.

Check: ask for it the other way round — “list every anomaly, oddity, and thing you would want a human to look at, and do not offer a fitness conclusion.”

⚠The pattern behind all four

Each failure is the agent producing something that looks like an answer where it does not have one. That is the shape of the risk throughout this training, and profiling is where you learn to recognize it cheaply — a wrong profile costs you an afternoon, while the same mistake in Module 3 costs you a wrong number in front of a supervisor.

The profile is an artifact, not an activity

Write the results down. A profile you did and did not record is a profile you will redo in four months, and more importantly it is the evidence you need when the answer is “the source system sent bad values.”

PROFILE - circuit_faults (curated), run 2026-08-25, full table


GRAIN         3.24M rows, 3.19M distinct (circuit_id, event_ts)
              -> 51k duplicate (circuit, timestamp) pairs. NOT unique.
              Investigated: duplicates carry different crew_id. Grain is
              really one row per circuit-event-CREW, not per event.
COMPLETENESS  continuous 2016-01 to present EXCEPT 2021-03 through
              2021-05: row counts drop 60%. Source system migration.
              Confirmed with ops. Period is unusable for trend work.
NULLS         cause_code 11% overall; 100% null before 2018-07 (column
              added). fault_duration 3%, clustered in 4 circuits whose
              telemetry is known bad.
CARDINALITY   circuit_id has 2,104 distinct against 1,847 real circuits.
              257 extras are decommissioned circuits still in history -
              correct, but must be excluded by the population rule.
UNITS         fault_duration in SECONDS despite being named _minutes in
              the source system's own documentation. Verified against
              three known incidents.
TIME ZONE     event_ts is UTC. Local reporting is America/Chicago.
              Month boundaries will shift 5-6 hours if not converted.


OPEN WITH SOURCE OWNER: the _minutes / seconds naming conflict.

Everything in that block came out of the six questions. Two of the findings — the real grain and the seconds-not-minutes unit — would each have silently produced a wrong metric, and neither would have been caught by any platform quality check, because the pipeline is transporting them faithfully.

Stop and escalate when the profile implicates the source itself — the seconds-named-minutes conflict is the source owner’s to fix or formally acknowledge, with your profile artifact attached as evidence. Patching it silently in your transform leaves every other consumer reading it wrong.

KNOWLEDGE CHECK

An agent's profile reports circuit_id has 1,850 distinct values, close to the 1,847 real circuits. What should you do?

Key takeaway

Six questions decide whether a table can carry your metric: grain, completeness over time, null rate by period, cardinality against reality, units and ranges, and time-zone behavior. An agent writes the profiling SQL better than you will by hand, and reliably fails in four ways — silent sampling, averages that hide distributions, trusting column names, and offering a fitness conclusion it cannot support. Record the profile as an artifact: it is what you redo otherwise, and it is the evidence that turns “the source is wrong” from a complaint into a conversation. Lesson 2.3 asks the same skeptical question about the catalog itself.

Search lessons