Data Profiling & Mapping
You cannot model data you don't understand. Profiling is the one-time investigation that turns "here's a CSV/table" into "here's exactly what's in it and how it should be shaped." Do this before drawing a single ER box.
Where this sits (boundaries)
- vs. [[data-reliability]] (data-quality): profiling is exploratory, run once to understand the data. Data-quality is recurring, run every load to guard it. Same checks, different lifecycle stage.
- vs. [[data-modeling]] (schema-design): profiling discovers the actual data; schema-design chooses the model (star/SCD/grain) using what you found here.
- Reuse the code:
utils/quality.py and utils/keys.py already implement most of these checks — call them, don't re-implement.
The profiling checklist — run all of it
Structure
Completeness
Cardinality & keys
Distributions & ranges
Relationships
Source-to-target mapping
Once profiled, map each source field to its intended target. This table is a deliverable and feeds modeling directly.
| Source field |
Type (actual) |
Null% |
Target field |
Target type |
Transform |
Notes |
cust_id |
string |
0% |
customer_id |
INT64 |
cast, validate FK |
primary key |
signup_dt |
string |
2% |
signup_date |
DATE |
parse MM/DD/YYYY |
2% unparseable → quarantine |
stat |
string |
0% |
status |
STRING |
lowercase, map codes |
values: A/I/P → active/inactive/pending |
ER diagram
With keys and relationships known, draw the ER model. Prefer text-based (Mermaid) so it lives in the repo and diffs cleanly:
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_LINE : contains
PRODUCT ||--o{ ORDER_LINE : "appears in"
CUSTOMER {
int customer_id PK
string email
date signup_date
}
ORDER {
int order_id PK
int customer_id FK
timestamp order_ts
}
Capture per entity: primary key, foreign keys, grain, and cardinality of each relationship (||--o{ = one-to-many, optional).
Output & hand-off
A profiling pass produces three artifacts: the profile report (the checklist findings), the source-to-target map, and the ER diagram. These feed:
- Choosing the model → [[data-modeling]] (schema-design)
- Standing up recurring checks from what you found → [[data-reliability]] (data-quality)
- Architecture sizing → [[data-architecture]]
See the lifecycle overview in [[data-lifecycle]].
1---2name: data-profiling3description: Profile and map raw data BEFORE designing a schema. One-time exploratory analysis to learn the true shape of a dataset — row/column counts, null rates, cardinality, value distributions, ranges, data types, candidate keys, duplicates, referential relationships — then a source-to-target field mapping and an ER diagram. Use this skill whenever the user has data in hand and needs to understand it before modeling, is about to design a schema, asks "what does this data actually look like", needs to find the primary/composite key of an unfamiliar table, or must map source fields to a target model. This is exploratory and one-time; ongoing production validation is data-quality's job, and modeling patterns are schema-design's job.4---56# Data Profiling & Mapping78You cannot model data you don't understand. Profiling is the **one-time investigation** that turns "here's a CSV/table" into "here's exactly what's in it and how it should be shaped." Do this before drawing a single ER box.910## Where this sits (boundaries)1112- **vs. [[data-reliability]] (data-quality):** profiling is exploratory, run once to *understand* the data. Data-quality is recurring, run every load to *guard* it. Same checks, different lifecycle stage.13- **vs. [[data-modeling]] (schema-design):** profiling *discovers the actual data*; schema-design *chooses the model* (star/SCD/grain) using what you found here.14- **Reuse the code:** `utils/quality.py` and `utils/keys.py` already implement most of these checks — call them, don't re-implement.1516## The profiling checklist — run all of it1718### Structure19- [ ] Row count and column count20- [ ] Column names and declared vs. actual data types (strings hiding numbers/dates?)21- [ ] Encoding, delimiter, header presence for files2223### Completeness24- [ ] Null / empty-string / sentinel (`-1`, `9999`, `"N/A"`, `"unknown"`) rate per column25- [ ] Columns that are entirely null or entirely constant (drop candidates)2627### Cardinality & keys28- [ ] Distinct count per column29- [ ] Candidate unique key — single column, or composite (`utils/keys.find_unique_key`)30- [ ] Duplicate rows on the candidate key (`utils/keys.get_duplicate_rows`)3132### Distributions & ranges33- [ ] Min / max / typical range for numerics and dates (catch impossible values: negative age, future timestamps)34- [ ] Top-N frequency for categoricals (spot typos, casing inconsistency, unexpected categories)35- [ ] Outliers worth flagging before they distort models3637### Relationships38- [ ] Foreign-key candidates: which columns join to which other tables, and do the values actually match (orphan rate)?39- [ ] Grain: what does one row represent? (one order? one order-line? one order-per-day snapshot?)4041## Source-to-target mapping4243Once profiled, map each source field to its intended target. This table is a deliverable and feeds modeling directly.4445| Source field | Type (actual) | Null% | Target field | Target type | Transform | Notes |46|---|---|---|---|---|---|---|47| `cust_id` | string | 0% | `customer_id` | INT64 | cast, validate FK | primary key |48| `signup_dt` | string | 2% | `signup_date` | DATE | parse `MM/DD/YYYY` | 2% unparseable → quarantine |49| `stat` | string | 0% | `status` | STRING | lowercase, map codes | values: A/I/P → active/inactive/pending |5051## ER diagram5253With keys and relationships known, draw the ER model. Prefer text-based (Mermaid) so it lives in the repo and diffs cleanly:5455```mermaid56erDiagram57 CUSTOMER ||--o{ ORDER : places58 ORDER ||--|{ ORDER_LINE : contains59 PRODUCT ||--o{ ORDER_LINE : "appears in"60 CUSTOMER {61 int customer_id PK62 string email63 date signup_date64 }65 ORDER {66 int order_id PK67 int customer_id FK68 timestamp order_ts69 }70```7172Capture per entity: primary key, foreign keys, grain, and cardinality of each relationship (`||--o{` = one-to-many, optional).7374## Output & hand-off7576A profiling pass produces three artifacts: the **profile report** (the checklist findings), the **source-to-target map**, and the **ER diagram**. These feed:77- Choosing the model → [[data-modeling]] (schema-design)78- Standing up recurring checks from what you found → [[data-reliability]] (data-quality)79- Architecture sizing → [[data-architecture]]8081See the lifecycle overview in [[data-lifecycle]].
Methasit-Pun