Why you should profile a warehouse's live schema, row counts, and cardinality with T-SQL before writing a single data agent instruction.

By the time you're ready to write data source instructions, it's tempting to work from memory: you know this warehouse, you built half of it. That's exactly the assumption worth checking before you write anything down for the agent to rely on, because "I know this schema" and "this is what the schema actually contains right now" drift apart faster than anyone expects, especially on a warehouse that's been in production for a year with three people touching it.

Why profiling comes before instructions, not after

Every later part of this series (business terms, joins, time intelligence, example queries) is you writing down claims about the data: this column means X, these tables join on Y, dates in this table only go back to Z. Every one of those claims is either grounded in something you actually queried, or it's a guess wearing the confidence of a fact. Profiling is the step where you replace guesses with queries, and it's cheap: an hour of SELECT statements against INFORMATION_SCHEMA catches problems that would otherwise surface as a data agent confidently returning a wrong number.

Start with the schema itself

INFORMATION_SCHEMA is standard T-SQL and works the same way here as anywhere else. Two views cover most of what you need to start:

SELECT table_schema, table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
ORDER BY table_schema, table_name;

SELECT table_name, column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'FactSales'
ORDER BY ordinal_position;

This alone answers questions people usually guess at: which tables actually exist, what schema they live in, what's nullable, and what the real data types are. varchar columns holding what should be numbers, or a datetime2 column you assumed was just a date, show up here before they show up as a confusing agent answer three weeks from now.

It's also the fastest way to catch a mismatch between your mental model and the current state of the warehouse: a table someone renamed, a column someone added last sprint, or a table you thought was still in use that's actually been superseded by a newer one nobody dropped. None of that is visible from memory. All of it is visible from two SELECT statements.

Row counts and freshness, per table

Once you know what tables exist, get a row count and a date range for anything with a date column. There's no universal shortcut here that's safe to assume across every engine and configuration, so the reliable version is also the boring version: query each table directly.

SELECT COUNT(*) AS row_count
FROM FactSales;

SELECT
    MIN(order_date) AS earliest_date,
    MAX(order_date) AS latest_date
FROM FactSales;

Run this across every fact table you're planning to expose. You're looking for two kinds of surprises: tables with far fewer rows than you expected (a load that's been silently failing), and date ranges that don't start or end where you assumed (a table that only has two years of history, or one that stopped updating four months ago). Both are the kind of thing a data agent will cheerfully compute a confident, wrong answer against, because a query engine has no way to know your data is stale, only that it ran successfully.

Cardinality: the check that catches join problems early

Cardinality, how many distinct values a column has relative to its row count, is the profiling step people skip most often and need most. A quick pass:

SELECT
       COUNT(*) AS total_rows,
    COUNT(DISTINCT customer_id) AS distinct_customers,
    COUNT(DISTINCT order_id) AS distinct_orders
FROM FactSales;

If distinct_orders is meaningfully lower than total_rows on a table you assumed was grain-level-one-row-per-order, you've just found a fan-out waiting to happen. That's the exact failure mode covered in Part 12: a join that silently multiplies rows, and every measure built on top of it, by however many duplicate rows exist per key. Catching it here, with a COUNT(DISTINCT ...), costs one query. Catching it after the agent is live costs a very awkward conversation about why last quarter's revenue suddenly doubled.

Diagram (flow)

·         Row counts +\ndate ranges per table  →  Cardinality checks\n(COUNT DISTINCT vs COUNT *

·         Cardinality checks\n(COUNT DISTINCT vs COUNT *  →  Grain matches\nwhat you assumed?

·         Grain matches\nwhat you assumed?  →  Fix the model, or document\nthe real grain honestly   [No]

·         Grain matches\nwhat you assumed?  →  Write data source\ninstructions in Part 5   [Yes]

Null rates deserve their own check

A column being nullable in INFORMATION_SCHEMA.COLUMNS tells you it's allowed to contain NULL. It doesn't tell you how often it actually does, and that gap matters a lot once an agent starts aggregating the column.

SELECT
   COUNT(*) AS total_rows,
   COUNT(discount_pct) AS non_null_rows,
   COUNT(*) - COUNT(discount_pct) AS null_rows
FROM FactSales;

A column that's NULL for 40% of rows behaves very differently in an AVG() than one that's NULL for 1%, since AVG() silently ignores NULLs rather than treating them as zero. If a business term in Part 6 is going to be defined as an average or a rate over a column like this, you want to know the null rate before you write that definition, not after someone asks why the average discount looks suspiciously generous.

A minimal profiling checklist

·         Every table you plan to expose has a known row count, not an assumed one.

·         Every table with a date column has a checked MIN/MAX, not an assumed range.

·         Every table's assumed grain (one row per what?) is confirmed with a COUNT(DISTINCT ...) check, not just a table or column name that sounds right.

·         Any column you plan to describe as a key, a flag, or a category has had its actual distinct values checked at least once (SELECT DISTINCT status FROM ...), since a "status" column with five undocumented values you've never seen is a common surprise.

·         Anything that surprised you during profiling is written down somewhere, even informally, before you move on. You will not remember it three parts from now.

Ground it in reality, not in your mental model

The uncomfortable trade-off here is time: profiling properly takes real effort on a warehouse with dozens of tables, and it's tempting to profile only the two or three tables you're sure you'll need first. That's a reasonable scope decision, as long as it's a decision and not an accident. Profile the tables you're actually going to describe to the agent in Part 5, thoroughly, rather than spreading a thin, incomplete pass across everything in the warehouse.

Key takeaways

·         Profiling replaces assumptions about the schema with queries you actually ran, and every later part of this series depends on those queries being right.

·         INFORMATION_SCHEMA.TABLES and .COLUMNS are the starting point for what actually exists, not what you remember building.

·         Row counts and date ranges catch stale or silently-failing loads before the agent surfaces them as a confident, wrong answer.

·         Cardinality checks (COUNT(DISTINCT ...) versus COUNT(*)) catch grain mismatches and fan-out joins while they're a one-query fix, not a production incident.

·         Scope profiling to the tables you'll actually describe to the agent next, and do that scope thoroughly rather than everything shallowly.

What's next

Next in the series: Writing a Data Source Description That Routes Correctly. With a profiled, ground-truthed schema in hand, we'll write the description that tells a multi-source agent when this warehouse is the right place to look.