> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dqlabs.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# What Prizm Collects from SQL Server
> Complete field-level breakdown of every metadata object, quality metric, and signal Prizm extracts from SQL Server across all platform jobs.
Prizm runs the following platform jobs against each SQL Server connector. Each job has a defined type, execution flow, and purpose. Select a job below to see the details.
| | |
| :------------ | :---------------------------------------------------------------------- |
| **Type** | CONTEXT |
| **Execution** | Runs immediately after source is configured — first job in the pipeline |
**What this job does:** Extracts databases, schemas, tables, views, stored procedures, and functions from SQL Server system catalog views. This is the foundation job — all subsequent jobs depend on it completing successfully.
| Object | Fields Collected | SQL Server Source |
| :--------------------- | :---------------------------------------------------------------------------------------------- | :------------------------------------------------ |
| **Database** | Name, created date, owner | `INFORMATION_SCHEMA` / `sys.databases` |
| **Schema** | Name, owner | `INFORMATION_SCHEMA.SCHEMATA` |
| **Table** | Name, type, create date, modify date | `INFORMATION_SCHEMA.TABLES` / `sys.tables` |
| **View** | Name, definition | `INFORMATION_SCHEMA.VIEWS` / `sys.views` |
| **Column** | Name, data type, max length, precision, scale, is\_nullable, is\_identity, ordinal position | `INFORMATION_SCHEMA.COLUMNS` / `sys.columns` |
| **Stored Procedure** | Name, definition | `INFORMATION_SCHEMA.ROUTINES` / `sys.sql_modules` |
| **Function** | Name, definition | `INFORMATION_SCHEMA.ROUTINES` |
| **Index** | Name, is\_primary\_key, is\_unique | `sys.indexes` |
| **Foreign Key** | FK name, parent table, parent column, referenced table, referenced column, delete/update action | `sys.foreign_keys`, `sys.foreign_key_columns` |
| **Check Constraint** | Definition | `sys.check_constraints` |
| **Default Constraint** | Definition | `sys.default_constraints` |
| **Extended Property** | `MS_Description` values for tables and columns | `sys.extended_properties` |
Prizm reads existing `MS_Description` extended properties and imports them as asset and attribute descriptions in the Prizm catalog. These are the descriptions written by SSMS or deployment scripts and visible in SQL Server Object Explorer.
| | |
| :------------ | :------------------------------- |
| **Type** | CONTEXT / TRUST |
| **Execution** | Starts after TECHNICAL completes |
**What this job does:** Extracts volume, freshness, and schema signals for table assets. Serves a dual purpose — feeds the Prizm catalog (CONTEXT) and the alerting and anomaly detection pipeline (TRUST). It is the source of all time-series observability signals in Prizm.
| Signal | Source | Description |
| :---------------- | :----------------------------------------------- | :------------------------------------------------- |
| **Row Count** | `sys.partitions` (WHERE index\_id IN (0,1)) | Row count trend; anomaly alerts on drops or spikes |
| **Table Size** | `sys.allocation_units` (total\_pages × 8 × 1024) | Physical storage size in bytes |
| **Freshness** | `sys.dm_db_index_usage_stats.last_user_update` | Time since last write operation; SLA breach alerts |
| **Schema Change** | `sys.columns` diff between runs | Column additions, renames, type changes, removals |
Anomaly detection uses adaptive forecasting models (EWMA, AutoReg, Prophet, SARIMAX) selected automatically based on each metric's historical pattern. Thresholds are recalculated after every run. A minimum of 5 historical values is required before alerts begin firing.
**Volume query (SQL Server):**
```sql theme={null}
WITH tablelist AS (
SELECT T.TABLE_SCHEMA, T.TABLE_NAME, COUNT(COLUMN_NAME) AS ColumnCnt
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON T.TABLE_CATALOG = C.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE UPPER(T.TABLE_NAME) = UPPER('')
AND UPPER(T.TABLE_SCHEMA) = UPPER('')
GROUP BY T.TABLE_SCHEMA, T.TABLE_NAME
),
TableRecCnt AS (
SELECT SCHEMA_NAME(obj.schema_id) AS Table_Schema, obj.name AS TableName,
SUM(p.rows) AS RecordCount,
SUM(a.total_pages) * 8 * 1024 AS TABLE_SIZE
FROM sys.objects AS obj
INNER JOIN sys.partitions AS p ON obj.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE UPPER(SCHEMA_NAME(obj.schema_id)) = UPPER('')
AND UPPER(obj.name) = UPPER('')
AND p.index_id < 2
GROUP BY obj.schema_id, obj.name
)
SELECT r.RecordCount AS ROW_COUNT, t.ColumnCnt AS COLUMN_COUNT, r.TABLE_SIZE
FROM tablelist t
INNER JOIN TableRecCnt r ON t.TABLE_SCHEMA = r.Table_Schema AND t.TABLE_NAME = r.TableName
```
**Freshness query (SQL Server):**
```sql theme={null}
SELECT DATEDIFF(second,
CONVERT(datetimeoffset, dmb.last_user_update),
CONVERT(datetimeoffset, GETDATE())) AS freshness
FROM sys.dm_db_index_usage_stats dmb
JOIN sys.tables tbl ON dmb.object_id = tbl.object_id
WHERE OBJECT_NAME(dmb.object_id) = ''
AND tbl.schema_id = SCHEMA_ID('')
AND dmb.last_user_update IS NOT NULL
```
| | |
| :------------ | :------------------------------------------- |
| **Type** | CONTEXT |
| **Execution** | Runs in parallel after OPERATIONAL completes |
**What this job does:** Extracts lineage using SQL Server system catalog views to identify object dependencies and data flow. Lineage is derived from three sources: foreign key constraints (table-to-table), `INFORMATION_SCHEMA.VIEW_COLUMN_USAGE` (view-to-table), and `sys.dm_sql_referenced_entities` (stored procedure and view object dependencies).
| Source | Lineage Type | Method |
| :-------------------- | :------------------------ | :----------------------------------------------------- |
| **FK constraints** | Table → Table | `sys.foreign_keys` + `sys.foreign_key_columns` |
| **Views** | View → Table/Column | `sys.dm_sql_referenced_entities` — no parsing required |
| **Stored Procedures** | Procedure → Table/View | `sys.dm_sql_referenced_entities` |
| **Synonyms** | Cross-database references | `sys.synonyms` |
| Direction | What Prizm Shows |
| :------------- | :------------------------------------------------------ |
| **Upstream** | Tables and views this asset reads from |
| **Downstream** | Tables, views, and procedures that depend on this asset |
**Table-to-table lineage (FK-based):**
```sql theme={null}
SELECT fk.name AS relationship_name,
SCHEMA_NAME(tp.schema_id) AS parent_schema,
OBJECT_NAME(fkc.parent_object_id) AS parent_table,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS fk_column,
SCHEMA_NAME(tr.schema_id) AS referenced_schema,
OBJECT_NAME(fkc.referenced_object_id) AS referenced_table,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS pk_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
```
**Forward lineage — all objects referenced by a view or stored procedure:**
```sql theme={null}
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS source_schema,
o.name AS source_object,
o.type_desc AS source_type,
d.referenced_schema_name,
d.referenced_entity_name,
d.referenced_minor_name AS referenced_column
FROM sys.objects o
CROSS APPLY sys.dm_sql_referenced_entities(
SCHEMA_NAME(o.schema_id) + '.' + o.name, 'OBJECT'
) d
WHERE o.type IN ('V', 'P', 'FN', 'IF', 'TF')
AND o.is_ms_shipped = 0
```
| | |
| :------------ | :------------------------------------------- |
| **Type** | CONTEXT |
| **Execution** | Runs in parallel after OPERATIONAL completes |
**What this job does:** Extracts query usage, session activity, and performance statistics. Powers the Usage and Performance views in the Prizm asset detail page. Requires `VIEW SERVER STATE` on the master database.
| Category | Metrics | Source |
| :------------------------ | :---------------------------------------------------------- | :------------------------------------------------- |
| **Query Performance** | Execution count, total/average execution time, query text | `sys.dm_exec_query_stats` + `sys.dm_exec_sql_text` |
| **Session Activity** | Active session count, peak concurrency | `sys.dm_exec_sessions` |
| **Query Count per Asset** | Number of queries referencing each table in the past N days | `sys.dm_exec_query_stats` filtered by schema.table |
**Query count per table:**
```sql theme={null}
SELECT COUNT(*) AS QUERY_COUNT
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
WHERE st.text LIKE '%.%'
AND s.creation_time >= DATEADD(DD, -, GETDATE())
```
| | |
| :------------ | :----------------------------------------------- |
| **Type** | TRUST |
| **Execution** | Triggered after PROFILE RECOMMENDATION completes |
**What this job does:** Executes profiling jobs and stores results. Can also be triggered manually from the asset page at any time.
**Table-level metrics:**
| Metric | Description |
| :---------------------- | :------------------------------------------------ |
| **Row Count** | Total rows at profile time |
| **Duplicate Row Count** | Rows that are exact duplicates of another row |
| **Completeness Score** | % of columns with non-null values across all rows |
**Column-level metrics:**
| Metric | Applicable Types | Description |
| :---------------------- | :--------------------- | :--------------------------------------------- |
| **Null Rate** | All | % of null values |
| **Distinct Count** | All | Number of unique non-null values (cardinality) |
| **Uniqueness Rate** | All | % of values appearing exactly once |
| **Min / Max** | Numeric, Date/Datetime | Observed range of values |
| **Mean** | Numeric | Arithmetic mean |
| **Standard Deviation** | Numeric | Statistical spread |
| **Top N Values** | String, Boolean | Most frequent values and their counts |
| **Pattern Conformance** | String | % matching an expected regex format |
## Table-Level Metadata Mapping
| SQL Server Field | Source View | Prizm Asset Field |
| :-------------------------------- | :----------------------------------------- | :----------------------------------------------------------------------------- |
| `t.name` | `sys.tables` / `sys.views` | `asset_name` |
| `s.name` (schema) | `sys.schemas` | domain mapping |
| `t.type_desc` | `sys.tables` | `asset_type`: USER\_TABLE / VIEW / SYSTEM\_TABLE |
| `p.name` (owner) | `sys.database_principals` | `data_owner` (maps to RBAC in Prizm) |
| `ep.value` (MS\_Description) | `sys.extended_properties` | `asset_description` |
| `t.create_date` / `t.modify_date` | `sys.tables` | `asset_created_date` / `last_modified_date` |
| `p.rows` (row count) | `sys.partitions` WHERE `index_id IN (0,1)` | `row_count` |
| `i.is_primary_key` / `is_unique` | `sys.indexes` | `primary_key_flag` / `has_unique_constraint` |
| `PRIZM_*` extended properties | `sys.fn_listextendedproperty` | `custom_metadata` — Prizm reads back any previously written quality properties |
## Column-Level Metadata Mapping
| Column Field | Source | Prizm Attribute Field |
| :----------------------------------------- | :------------------------ | :------------------------------------------------------------------------------------ |
| `c.name` | `sys.columns` | `attribute_name` |
| `ty.name` (data type) | `sys.types` | `data_type` (int, nvarchar, datetime2, decimal, etc.) |
| `c.max_length` / `c.precision` / `c.scale` | `sys.columns` | `type_size` — used to validate data fits declared constraints |
| `c.is_nullable` | `sys.columns` | `nullable_flag` — auto-generates NOT NULL quality rule when 0 (false) |
| `c.is_identity` | `sys.columns` | `is_identity_column` — used to auto-generate uniqueness and sequence continuity rules |
| `c.column_id` | `sys.columns` | `column_ordinal` — schema drift detection |
| `dc.definition` (default) | `sys.default_constraints` | `default_value` — used to detect unexpected NULL patterns |
| `ck.definition` (check constraint) | `sys.check_constraints` | `check_constraint_def` — auto-creates range / allowed-values quality rules |
| `ep.value` (MS\_Description) | `sys.extended_properties` | `attribute_description` — reads existing column documentation from SSMS descriptions |
## Supported Data Types
| Category | SQL Server Types |
| :------- | :----------------------------------------------------------------- |
| Integer | `INT`, `SMALLINT`, `TINYINT`, `BIGINT` |
| Numeric | `DECIMAL`, `NUMERIC`, `FLOAT`, `REAL`, `MONEY`, `SMALLMONEY` |
| Text | `VARCHAR`, `NVARCHAR`, `CHAR`, `NCHAR`, `TEXT`, `NTEXT` |
| Date | `DATE` |
| Datetime | `DATETIME`, `DATETIME2`, `SMALLDATETIME`, `DATETIMEOFFSET`, `TIME` |
| Binary | `BINARY`, `VARBINARY`, `IMAGE` |
| Other | `BIT`, `UNIQUEIDENTIFIER`, `XML` |
## What You See in Prizm
Once Prizm completes its initial catalog and observability runs, every SQL Server table and view gets a unified asset detail page.
| Section | What Prizm Shows |
| :------------------- | :--------------------------------------------------------------------------------------------------------------------- |
| **Quality Score** | Overall data quality percentage computed across all active metrics |
| **Status** | Review state and criticality badge |
| **Key Metrics** | VOLUME (row count), SCHEMA (column count), FRESHNESS (time since last update), METRICS (total quality metrics defined) |
| **Description** | Imported from `MS_Description` or AI-generated |
| **Semantic Context** | Domain, Application, Product, Tag, and Terms classifications |
| **Owners** | Business owner, Technical owner, and Steward contacts |
The Attribute tab lists every column Prizm has cataloged with columns: **NAME, DATA TYPE, SCORE, ALERT, ISSUE, METRIC COUNT, STATUS**.
Clicking any attribute opens its column profile with views across completeness, uniqueness, character distribution, patterns, and frequency.
The Metric tab lists all quality metrics defined on the asset, filterable by category: Distribution, Custom, Frequency, Pattern, Statistics, Availability, Conditional.
The Lineage tab renders an interactive directed graph of all upstream sources (tables this asset reads from via FK or view dependencies) and downstream consumers (tables, views, and procedures that depend on this asset).
## Next Steps
Review prerequisites and configure the connector.
Common questions about what Prizm collects and why.