Schema Analyzer

Produce a complete schema analysis and comparison document for a data migration. This is the first deliverable in a migration engagement. The output drives field mapping, transformation design, and complexity estimation for the rest of the project.

Source Schema Extraction

Extract the schema from the source system using the method appropriate for the system type:

SQL Server Source

-- Extract full table schema
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    tp.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS Precision,
    c.scale AS Scale,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    dc.definition AS DefaultValue,
    ep.value AS ColumnDescription
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id 
    AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
WHERE t.type = 'U'
ORDER BY t.name, c.column_id;

-- Extract foreign key relationships
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ParentTable,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
    OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY ParentTable, ParentColumn;

Dataverse Source

Use the Dataverse Web API to extract entity metadata:

GET [org-url]/api/data/v9.2/EntityDefinitions?$select=LogicalName,DisplayName,Description
GET [org-url]/api/data/v9.2/EntityDefinitions(LogicalName='account')/Attributes?$select=LogicalName,AttributeType,RequiredLevel,DisplayName

SharePoint List Source

Export list schema via PowerShell:

Connect-PnPOnline -Url $siteUrl -Interactive
$list = Get-PnPList -Identity "ListName"
$fields = Get-PnPField -List "ListName"
$fields | Select-Object InternalName, TypeAsString, Required, MaxLength, DefaultValue |
  Export-Csv ".\sharepoint-schema.csv" -NoTypeInformation

CSV / Excel Source

Document the schema by inspection:

• Column headers (as-is from the file)
• Inferred data type (text, number, date, boolean)
• Presence of null/empty values (sample 100 rows)
• Distinct value count (for potential code/lookup fields)
• File encoding and delimiter

Source Schema Documentation

For each entity/table being migrated, produce:

Entity: [Table/List name] Record count: [Approximate count] Primary key: [Column name and type] System of record: [System name]

Column Name	Data Type	Length / Precision	Nullable	Default	Foreign Key	Sample Values	Notes
ClientID	int	—	No	Identity	—	10042, 10043	Auto-increment
LastName	varchar	100	No	—	—	"Martinez"
FirstName	varchar	100	No	—	—	"Elena"
SSN	char	11	Yes	—	—	"--***"	MUST be masked in migration
DateOfBirth	date	—	Yes	—	—	1975-03-14
State	char	2	No	—	—	"CA", "TX"	2-letter USPS code
AgentCode	varchar	20	Yes	—	→ Agents.AgentCode	"AG-001"
StatusCode	char	1	No	'A'	—	"A", "I", "D"	A=Active, I=Inactive, D=Deceased
CreateDate	datetime	—	No	GetDate()	—		Server default — not user-controlled
Notes	nvarchar	4000	Yes	—	—		Free text

Target Schema Documentation

Document the destination system's schema for the same entities:

Entity: [Destination entity name] System: [Destination system name] Write method: REST API / SQL INSERT / Dataverse upsert / SharePoint list item

Field Name	Data Type	Required	Max Length	Validation	Default	Notes
Id	GUID	No (auto)	—	System-assigned	—	Do not populate
LastName	string	Yes	150	Not blank	—
FirstName	string	Yes	150	Not blank	—
TaxId	string	No	20	Encrypted at rest	—	Destination for SSN — encrypted
BirthDate	date	No	—	Must be in past	—
StateCode	string	Yes	2	Must be valid US state	—	Same format as source
AgentId	GUID	No	—	Must exist in Agent entity	—	Resolved from AgentCode
Status	string	Yes	20	Enum: Active, Inactive, Deceased	Active
Remarks	string	No	5000			Larger than source — no truncation needed
CreatedOn	datetime	No (auto)	—	System-assigned	—
ExternalId	string	No	100		—	Populate with source ClientID for traceability

Schema Comparison

Produce a side-by-side comparison identifying all differences:

Coverage Analysis

Source fields with no destination mapping (document each — confirm discard is intentional):

Source Field	Reason Not Mapped	Action Required
CreateDate	Destination uses system-assigned CreatedOn	Confirm with client — no action
[field]	[reason]	[action]

Destination required fields with no source (must resolve before migration can proceed):

Destination Field	Required	Available Source?	Resolution
[field]	Yes	No	Derive from [other source field] / Apply default value "[value]" / Block migration until source provides data

Data Type Mismatches

Source Field	Source Type	Destination Field	Destination Type	Conversion Required
ClientID (int)	int	ExternalId	string(100)	Cast integer to string
DateOfBirth (date)	date	BirthDate	date	Direct mapping — same type
StatusCode (char 1)	char(1)	Status	string(20)	Lookup table translation
Notes (nvarchar 4000)	nvarchar(4000)	Remarks	string(5000)	Direct mapping — destination wider
SSN (char 11)	char(11)	TaxId	string(20) — encrypted	Encryption at rest handled by destination API

Field Length Mismatches

Source Field	Source Length	Destination Field	Destination Length	Risk
LastName	varchar(100)	LastName	string(150)	None — destination wider
[if source wider]				Truncation required — document

Relationship Mapping

Source Relationship	Source FK	Destination Relationship	Resolution
Client → Agent (via AgentCode)	Clients.AgentCode → Agents.AgentCode	Client → Agent (via AgentId GUID)	Build AgentCode → AgentId lookup table; migrate Agents first
Policy → Client (via ClientID int)	Policies.ClientID → Clients.ClientID	Policy → Client (via ClientId GUID)	Build ClientID → new GUID mapping table during client migration

Business Logic Identification

Document any business logic embedded in the source system that must be replicated in migration:

Logic Type	Description	Source Location	Migration Action
Calculated field	AnnualPremium = MonthlyPremium × 12	Not stored; always calculated	Replicate calculation in migration transform
Status derivation	Policy status "Expired" when ExpirationDate < today	SQL view logic	Apply same logic in WHERE clause of migration query
Lookup/decode	StatusCode 'A' = Active, 'I' = Inactive	Application code (not in DB)	Build lookup table, confirm with client
Cascade	Client delete cascades to Policy records	FK with CASCADE	Test: verify destination handles orphan records

Migration Complexity Scoring

Rate each entity/table by migration complexity:

Rating	Criteria
Simple	Direct field copy with type conversion only. No lookups, no derived fields, no business logic.
Medium	Requires lookup table translation OR derived fields OR data cleansing on < 5 fields.
Complex	Business logic replication, major data model restructuring, many-to-many relationship resolution, or data cleansing on > 5 fields.

Entity complexity table:

Entity	Records	Complexity	Rationale
Clients	12,450	Medium	Status code lookup, SSN encryption, AgentCode → GUID resolution
Policies	38,200	Complex	Multiple relationship resolutions, status logic, term calculation
Claims	4,100	Medium	Status lookup, client GUID resolution
Agents	85	Simple	Direct mapping, minimal transformation
Attachments	28,000	Complex	Binary file migration, path restructuring, SharePoint upload

Output Format

Deliver as:

1. Source schema extraction method and queries used
2. Source schema documentation (one table per entity)
3. Target schema documentation (one table per entity)
4. Schema comparison tables:
   • Unmapped source fields
   • Destination required fields with no source
   • Data type mismatches
   • Field length mismatches
   • Relationship mapping
5. Business logic catalog
6. Complexity scoring table with rationale
7. Open questions (items requiring client confirmation before field mapping proceeds)
8. Next steps (recommended order: data profiling → field mapping → transformation design → test migration)