source-profiler

name	source-profiler
description	Data source profiling for Qlik development. When MCP or direct database access is available, provides query templates and procedures to assess source schemas, field types, cardinality, null rates, sample values, and data quality indicators. When live access is unavailable, generates a structured Source Profile Template for manual completion. Includes source architecture classification — annotating tables with architectural role, key structure, mutability, incremental load pattern, and consumption implications (dimensional warehouse, OLTP, Data Vault 2.0, flat files). Load when profiling source schemas before data-model design, assessing source data quality, classifying source architecture, or filling in a Source Profile Template by hand.
user-invocable	false

Source Profiling for Data-Driven Architecture

Before designing a Qlik data model, you must understand the source data: what tables exist, what fields are in each table, what data types they use, how many distinct values exist in each field, whether null values are present, and what the source system's architecture pattern is (dimensional warehouse, normalized OLTP, Data Vault 2.0, flat files, etc.). Building a data model on incorrect assumptions about cardinality, key uniqueness, or data types produces models that fail on reload or produce silent data errors.

Source profiling supports a two-path design:

• Path A (MCP Available): Use MCP database connections to run profiling queries directly against the source, automatically discovering schema metadata, cardinality, null rates, and sample values.
• Path B (MCP Unavailable): Generate a structured Source Profile Template (see profile-template.md) for the developer to fill from their database tools (SQL Server Management Studio, pgAdmin, DBeaver, etc.).

Both paths produce the same standardized Source Profile Report, which is the input for Qlik data model design.

The skill also includes source architecture classification, which annotates each table with its architectural role (Dimension, Fact, Lookup, Hub, Link, Satellite, etc.) and consumption implications (how Qlik should load and associate this table).

Two-Path Profiling Workflow

Path A: MCP Available

When the project has MCP database connections configured:

1. Identify available data connections from the Project Specification (requirements gathering output). Each connection has a name, type (ODBC, REST, etc.), and target (server, database, API endpoint).

2. For each connection, run schema discovery queries (provided below by database type) to list all tables and their fields.

3. For each table, run column-level profiling queries to assess:

   • Field name and data type (exactly as stored in source)
   • Cardinality (distinct value count)
   • Null rate (percentage of null values)
   • Min/Max values (for numeric and date fields)
   • Sample values (3-5 representative values)

4. Classify each table's architectural role (see Section 3 below) based on schema inspection: Is it a dimension (relatively static, has keys)? A fact (event records, larger row count)? A lookup table? A Data Vault hub, link, or satellite?

5. Compile into Source Profile Report using the standardized format defined in Section 6.

6. Validate for completeness and present to developer for confirmation.

Path B: MCP Unavailable

When MCP connections are not available:

1. Generate the Source Profile Template (from profile-template.md in this skill directory) and provide it to the developer.

2. Provide clear instructions for the developer to fill the template using their own database tools:

   • Row counts: Run SELECT COUNT(*) FROM [table_name]
   • Cardinality: Run SELECT COUNT(DISTINCT [column_name]) FROM [table_name]
   • Sample values: Run SELECT DISTINCT [column_name] FROM [table_name] LIMIT 5
   • Data types: Query the database system catalog (information_schema for SQL databases)

3. Developer completes the template and returns it.

4. Validate the completed template for completeness (all required fields filled, sample values are realistic, no missing tables).

5. The completed template IS the Source Profile Report. No further transformation is needed.

Column-Level Profiling Detail

For each column in a table, capture:

1. Column Metadata

• Column name — Exactly as it appears in the source system (e.g., product_id, ProductName, CATEGORY_CODE)
• Data type — Source system data type: VARCHAR(50), INT, DATETIME, DECIMAL(10,2), etc.
• Nullable — Whether the column allows NULL values (yes/no)

2. Cardinality and Uniqueness

• Cardinality — Approximate distinct value count. Range is fine (e.g., "~500 distinct products" vs. "~5M distinct transactions").
• Null rate — Percentage of rows where the column is NULL. Note: COUNT(DISTINCT col) does not include NULLs in any standard database. Report distinct-value count and null rate as two independent measures.
• Uniqueness — If this column is or could be a key, note whether values are unique (no duplicates). Check for composite key opportunities (e.g., does [order_id, line_number] form a unique key?).

3. Value Ranges and Sample Values

• Min/Max — For numeric fields, the minimum and maximum values. For date fields, the earliest and latest dates.
• Sample values — 3-5 representative values. For categorical fields, include common values. For numeric fields, show the range. Example: ['Active', 'Inactive', 'Pending'] or [100, 5000, 99999].

4. Pattern Notes

• String-encoded nulls — Fields containing string values that represent missing data: 'null', 'NaN', 'none', 'n/a', '[null]'. These are data quality issues.
• Mixed data types — Columns that contain both numeric and string values. Qlik will attempt type coercion, often incorrectly.
• Encoding artifacts — Non-printable characters, leading/trailing spaces, bracket artifacts ([, ]) that appear in string values.
• Date format inconsistencies — Different date formats in the same column (some records YYYY-MM-DD, others MM/DD/YYYY).

MCP Profiling Queries by Database Type

When MCP connections are available, use these query templates to profile sources. Adapt field and table names to match the source.

SQL Server / Azure SQL

Schema Discovery (list all tables and columns):

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'  -- Adjust schema name as needed
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Column-Level Profiling (run once per table; SQL Server / Azure SQL):

-- Statistics: one row per column
SELECT
    '[ColumnName]' AS column_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT [ColumnName]) AS cardinality,
    CAST(100.0 * SUM(CASE WHEN [ColumnName] IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5,2)) AS null_percent,
    CAST(MIN([ColumnName]) AS VARCHAR(50)) AS min_value,
    CAST(MAX([ColumnName]) AS VARCHAR(50)) AS max_value
FROM [TableName];

-- Sample values: STRING_AGG does NOT accept DISTINCT inside the function,
-- so de-duplicate in a subquery first.
SELECT STRING_AGG(CAST(sv AS VARCHAR(50)), ', ') WITHIN GROUP (ORDER BY sv) AS sample_values
FROM (
    SELECT DISTINCT TOP 5 [ColumnName] AS sv
    FROM [TableName]
    WHERE [ColumnName] IS NOT NULL
    ORDER BY [ColumnName]
) d;

PostgreSQL

Schema Discovery:

SELECT
    table_name,
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'  -- Adjust schema name as needed
ORDER BY table_name, ordinal_position;

Column-Level Profiling (PostgreSQL):

-- Statistics: one row per column
SELECT
    'ColumnName' AS column_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT "ColumnName") AS cardinality,
    ROUND(100.0 * SUM(CASE WHEN "ColumnName" IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_percent,
    MIN("ColumnName"::TEXT) AS min_value,
    MAX("ColumnName"::TEXT) AS max_value
FROM "TableName";

-- Sample values: limit the row count INSIDE a subquery so STRING_AGG
-- aggregates at most 5 distinct values. LIMIT at the outer level of an
-- aggregate-only query is a no-op (single-row result).
SELECT STRING_AGG(sv::TEXT, ', ' ORDER BY sv::TEXT) AS sample_values
FROM (
    SELECT DISTINCT "ColumnName" AS sv
    FROM "TableName"
    WHERE "ColumnName" IS NOT NULL
    ORDER BY "ColumnName"
    LIMIT 5
) d;

MySQL

Schema Discovery:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'  -- Adjust database name as needed
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Column-Level Profiling (MySQL 8.0+):

-- Statistics: one row per column
SELECT
    'ColumnName' AS column_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT ColumnName) AS cardinality,
    ROUND(100.0 * SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_percent,
    MIN(CAST(ColumnName AS CHAR)) AS min_value,
    MAX(CAST(ColumnName AS CHAR)) AS max_value
FROM TableName;

-- Sample values: GROUP_CONCAT does NOT accept LIMIT inside the function
-- in MySQL 8.0; limit the input row count via a subquery instead.
-- The result is also truncated by the session variable group_concat_max_len
-- (default 1024). Raise it if needed: SET SESSION group_concat_max_len = 4096;
SELECT GROUP_CONCAT(CAST(sv AS CHAR) ORDER BY sv SEPARATOR ', ') AS sample_values
FROM (
    SELECT DISTINCT ColumnName AS sv
    FROM TableName
    WHERE ColumnName IS NOT NULL
    ORDER BY ColumnName
    LIMIT 5
) d;

Generic ANSI SQL Fallback

When the above database-specific queries don't work, use this generic approach (may be slower, works on most databases):

SELECT
    'ColumnName' AS column_name,
    COUNT(*) AS row_count,
    COUNT(DISTINCT ColumnName) AS cardinality,
    ROUND(100.0 * SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_percent,
    CAST(MIN(ColumnName) AS VARCHAR(50)) AS min_value,
    CAST(MAX(ColumnName) AS VARCHAR(50)) AS max_value
FROM TableName;

Table-Level Profiling

For each table, capture:

• Table name — Exactly as it appears in the source system
• Row count — Current record count. Use SELECT COUNT(*) FROM [table]
• Row count growth — If available from audit tables or logs: rows added per day/week/month. Helps size QVDs and predict reload times.
• Primary key — Which column(s) form the unique key. Confirm uniqueness with SELECT COUNT(*), COUNT(DISTINCT key_columns) FROM table. If counts differ, the key is not unique.
• Key uniqueness status — "Confirmed unique", "Violations found", "Composite key required", or "No key"
• Timestamp fields — Which columns indicate record creation (created_at, insert_date), modification (updated_at, modified_date), or soft-delete (is_deleted, deleted_date)?
• Refresh pattern — How is this table updated in the source? Full refresh every load, append-only (inserts only), insert/update (upsert), insert/update/delete (with soft-delete flag)?

Source Architecture Classification

This is the skill's unique contribution beyond standard column-level profiling. Each table's architectural role determines HOW Qlik should consume it (key strategy, incremental load approach, association rules).

See architecture-classification.md for the detailed classification decision tree, per-role consumption guide, and architecture type signatures.

For each table, classify:

1. Architectural Role — What is this table's purpose in the source system? Options: Dimension, Fact, Lookup, Staging, Hub (Data Vault), Link (Data Vault), Satellite (Data Vault), Bridge, Snapshot, Aggregate, Reference
2. Key Structure — What identifies a unique record? Options: Natural key, Surrogate key, Composite key, Hash key, No key
3. Mutability — How does the table change over time? Options: Immutable (append-only), Mutable (updated in place), SCD Type 1 (attributes overwritten), SCD Type 2 (versioned with effective dates), Temporal (bi-temporal with bi-temporal timestamps)
4. Incremental Load Pattern — How should Qlik incrementally load this table? Options: Full refresh, Insert-by-timestamp, Insert/Update-by-timestamp, Dual-timestamp (for SCD2), File-level detection (flat files), Not applicable
5. Consumption Implication — One-sentence guidance for how Qlik should join and aggregate this table. Example: "Surrogate key dimension with SCD Type 2 versioning. Use dual-timestamp incremental load. Join on surrogate key; filter for is_current=true when modeling current state."

Cross-Table Profiling

When MCP is available, validate relationships between tables:

Referential Integrity Checks

For fact tables with foreign keys pointing to dimensions, verify:

• Do all foreign key values in the fact table exist as primary keys in the dimension? Example: Does every product_id in fact_orders exist in dim_product?
• Run: SELECT COUNT(DISTINCT fk_column) FROM fact_table WHERE fk_column NOT IN (SELECT pk_column FROM dim_table)
• If count > 0, orphaned records exist.

Field Name Collisions

Scan for field names that appear in multiple tables but are NOT intended as associations:

• Example: Both Orders and Returns tables have a Status field, but they're different status domains (order status vs. return status). In Qlik, identical field names create associations. These should be prefixed in the DataModel layer: Order.Status, Return.Status.
• This is a design decision, not a data quality issue. Surface it for the data architect's awareness.

Relationship Mapping

For key-linked tables, identify the relationship cardinality:

• One-to-one: product_id in dim_product is unique; product_id in a product_details table is also unique.
• One-to-many: customer_id in dim_customer is unique; customer_id in fact_orders appears many times.
• Many-to-many: Order IDs and Product IDs are linked through a order_line_items table.

Data Quality Indicators

During profiling, flag these data quality issues. They don't block profiling but inform the data architect's decisions:

String-Encoded Nulls

Fields containing string values like 'null', 'NaN', 'none', 'n/a' to represent missing data. These appear as valid values to Qlik, polluting filter panes. Flag for Qlik-side NullAsValue or source-side data cleaning.

Mixed Data Types

Columns with both numeric and string values. Qlik attempts type coercion, often incorrectly. Flag for explicit casting in Qlik load script.

Encoding Artifacts

Non-printable characters, leading/trailing spaces, bracket artifacts. Flag for data cleaning in Transform layer.

Sparse Fields

Fields populated for less than 10% of records. Decide in Qlik: Should this be a separate dimension table (join only when populated) or handled via NullAsValue?

Duplicate Key Candidates

If a table is supposed to have a unique primary key but the profiling query shows duplicates (cardinality < row_count), flag it. The data architect may need to create a composite key or investigate whether the table is actually append-only with key reuse.

Source Profile Report Format

This is the standardized output format produced by both Path A (MCP) and Path B (manual template). It is the input for data model design.

# Source Profile Report

**Completion Date:** [date]
**Profiling Method:** [MCP queries | Manual template completion]

## Source Systems

### [Source System Name]

**Connection Details:**
- Type: [ODBC | REST | Folder | Other]
- Target: [Server, database, endpoint]
- Sample connection string: [example]

**Overall Architecture:** [Dimensional Warehouse | Normalized OLTP | Data Vault 2.0 | Flat Files/CSV | API | Other]

**Tables Profiled:** [count]

---

#### [Table Name]

**Metadata:**
- Row Count: [N]
- Primary Key: [field(s)]
- Key Uniqueness: [Confirmed | Violations found]
- Refresh Pattern: [Full refresh | Insert-by-timestamp | Insert/Update-by-timestamp | Dual-timestamp | File-level]

**Architectural Classification:**
- Role: [Dimension | Fact | Lookup | Hub | Link | Satellite | ...]
- Key Structure: [Natural key | Surrogate key | Composite key | Hash key]
- Mutability: [Immutable | Mutable | SCD Type 1 | SCD Type 2 | Temporal]
- Incremental Load Pattern: [Full refresh | Insert-by-timestamp | ...]

**Consumption Implication:**
[One sentence describing how Qlik should load and join this table]

**Column Inventory:**

| Column Name | Data Type | Cardinality | Null % | Min | Max | Sample Values | Notes |
|---|---|---|---|---|---|---|---|
| [name] | [type] | [count or ~range] | [%] | [value] | [value] | [val1, val2, val3] | [Any data quality flags] |

**Data Quality Flags:**
- [Any issues found: string-encoded nulls, mixed types, orphaned records, etc.]

**Cross-Table Notes:**
- [Referential integrity status]
- [Field name collision notes]
- [Relationship cardinality to other tables]

---

## Summary

[1-2 paragraph summary of overall source architecture, key tables, profiling completeness, and any significant data quality concerns]

Supporting Documents

For source architecture classification decision logic and detailed role-specific consumption guidance, read architecture-classification.md in this skill directory.

For the structured profiling template used when MCP is unavailable, read profile-template.md in this skill directory.