agentsclimarketplace

Crispdm phase 3 data preparation

Skill elkhouryrafik-boop/crisp-for-data-codex/crispdm-phase-3-data-preparation

CRISP-DM as 7 sequential agent skills for OpenAI Codex CLI — data-pipeline projects, no frontend. Codex port of crisp-for-data + earn-the-data.

Install
npx -y skills add elkhouryrafik-boop/crisp-for-data-codex --skill crispdm-phase-3-data-preparation

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

2 things to look at

  • 27 days oldThe repository was created 27 days ago. New is not bad, but a brand new repository carrying a familiar-sounding name is the shape a typosquat arrives in, and there has been no time for anyone else to find a problem with it.
  • 0 stars0 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

What its author says it does

Copied from the file, not written here

Use when the student has finished Phase 2 (Data Understanding) and is about to start cutting, cleaning, deriving, joining, or writing files in service of a modeling step. This is the BIGGEST phase in CRISP-DM — empirically 40–80% of total project effort per the surveys (Press 2016; Dasu & Johnson 2003; Anaconda 2020) — and the one the course grades on, because the teacher will explicitly assess how each processing decision was made and why. The skill walks all five canonical generic tasks (Chapman et al. 2000): Select → Clean → Construct → Integrate → Format, with one Socratic dialogue and one decision-log entry per choice. Every decision is logged in a copy-pasteable Markdown table, every transformation is reversible-by-default (raw is immutable; cleaned outputs are new artifacts; rejected rows go to a `rejected/` partition with a `reason_code`, never `dropna()`), and Phase 3 exits by handing Phase 4 a complete YAML data contract — schema, units, CRS, resolution, freshness, quality SLOs, lineage pointer, sensitivity profile, and a one-command rebuild. Triggers on "Phase 3", "data preparation", "clean the data", "feature engineering", "build the pipeline", "select clean construct integrate format", "data wrangling", "join the datasets", "merge these layers", "modeling-ready dataset", "should I impute / drop / merge", "we have raw layers we need one map", "build the priority map", or any request to go from raw vetted sources into a single modeling-ready artifact. Do NOT invoke for pure exploration (Phase 2), for model selection / training / tuning (Phase 4), or for UI work (out of scope for this course).

SKILL.md

52.5 KB, as published. Nobody here has run it

CRISP-DM Phase 3 — Data Preparation

This is the heaviest phase in the course. Phase 3 produces a versioned, documented, reproducible modeling-ready dataset plus the decision log that justifies every transformation between raw input and that artifact. The teacher grades how each processing decision was made; the skill is engineered so that — by the time you exit — every choice is logged, every alternative is named, every cleaning step is reversible, and a downstream modeler can rebuild the artifact from raw inputs with one command.

The standard (Chapman et al., 2000, p. 27) defines Phase 3 as "all activities to construct the final dataset… from the initial raw data" and warns that "data preparation tasks are likely to be performed multiple times, and not in any prescribed order" — Phase 3 is iterative-by-design. This skill imposes a disciplined trajectory through the five generic tasks while preserving the iteration the standard expects.

The anti-pattern this skill exists to prevent

Open notebook → df = df.dropna() → df = pd.merge(a, b) → df.to_csv("final.csv") → present.

That sequence produces three silent failures the course will not tolerate:

  1. Silent row-drops. Rows vanish with no log entry, no rejected/ partition, no reason code. A reviewer asking "why is your dataset 12,847 rows and not 15,000?" cannot get an answer in fewer than three queries. Krishnan et al. (2016) showed that dropping records before training produces systematically biased models even when the dropped records are themselves "dirty" — the missingness mechanism is information you destroy when you filter.

  2. Irreversible cleaning. The raw value is overwritten in place. There is no raw_diameter_cm to roll back to when the cap-at-300 rule turns out to be wrong. The pipeline cannot be re-run with a relaxed threshold without re-downloading from the source.

  3. Hidden join scale-mismatch. A 30 m raster joined to a 1 m vector, published as 1 m. The output looks fine in QGIS because of on-the-fly reprojection. The methodological claim — fine-grained spatial inference — is unsupportable. This is the cardinal sin of spatial pipelines; the project's own deep-research integrity review flagged it as a critical finding.

This skill blocks all three by partitioning rather than dropping, writing new artifacts rather than overwriting, and enforcing the 2× resolution rule at integration time.

Hard precondition — Phase 2 handoff artifacts must exist

Phase 3 cannot legitimately begin until Phase 2 has produced, at minimum:

  • phase-2/data-inventory.md — the candidates considered, the rubric scores, the inclusion/exclusion reasoning, the decision unit at the top.
  • phase-2/data-sheets/<source>.md — one 8-section data sheet per recommended source (Gebru et al., 2021, plus Limitations).
  • phase-2/profiling-plan.md — the 8-cell profiling checklist per recommended dataset.
  • phase-2/brief-revisit.md — the answer to "does the question still hold given what the data actually is?"

If any of these is missing, stop and route the user to the earn-the-data skill (or to a CRISP-DM Phase 2 companion skill). Phase 3 without Phase 2 produces a notebook accident dressed in pipeline clothes.

Additionally, the user must have executed the profiling plan from phase-2/profiling-plan.md. The 8 cells (shape/dtypes, missingness pattern, numeric summaries, categorical summaries, spatial coverage, temporal coverage, cross-field consistency, bridging methods) are the empirical ground for every Phase 3 decision. If the user has the plan but has not run it, ask them to run it before continuing — Phase 3 decisions made without profiling are guesses with a decision-log entry attached.

Required inputs

Before starting, confirm or solicit the six items below. If any is missing, stop and ask — Phase 3 without a target spec is a notebook accident.

  1. Phase-1 business question, restated in one sentence. "We want to produce X for Y, so that Z." This is the criterion every selection rationale must answer to.
  2. Phase-2 data inventory — list of sources with format, CRS (if spatial), resolution (spatial / temporal / spectral), time range, license, known issues, and rubric scores from earn-the-data.
  3. Target output spec — what does the next phase (modeling) need? A single table? A raster stack? A spatial join? At what scale? At what cadence? With what schema?
  4. Compute environment — pandas-in-a-notebook, dbt + Parquet on DuckDB, PostGIS, Dagster + DuckDB, Databricks + Delta, Spark, GeoPandas + GeoParquet. The skill is tool-agnostic but the user must declare the tool, because every exit check (idempotency, schema validation, lineage) is operationalized differently per tool.
  5. Repo layout and naming convention — where do raw / cleaned / integrated / published artifacts live, how are they versioned, what is the glossary.md for column names.
  6. Canonical CRS and integration resolution — for spatial work, the project's chosen CRS (ADR-001) and the integration-scale resolution implied by the 2× rule over the coarsest input. If not declared yet, declare them as ADRs before any cleaning step.

Specialist lenses

Codex runs single-agent — there is no sub-agent spawning here. Where the original workflow routed a step to a specialist, adopt that specialist's lens inline: reason through the step as that expert would before writing the artifact. For an independent second opinion (e.g. Phase 5 verification), run the step in a fresh Codex session primed with the specialist role instead.

Curated for Phase 3:

  • Data Engineer — ETL/ELT design, integration, schema alignment, orchestration (Airflow/Prefect/Dagster), dbt/Beam declarative transforms
  • AI Data Remediation Engineer — cleaning rules, missing-data strategy, anomaly handling with deterministic fix logic
  • Database Optimizer — join performance + index strategy on integrated outputs
  • Backend Architect — pipeline architecture, data contracts, lineage

Sub-step 3.1 — SELECT DATA

Chapman et al. (2000, p. 28) require "the rationale for inclusion/exclusion" as the deliverable. The pipeline-architect translation: selection is a specification, not an implementation detail.

actions

  1. Inventory every source as a row in the selection log. One source = one row. If the user has one big all_data.csv file, decompose it conceptually into its logical sources — they will have different licenses, vintages, and quality scores.
  2. Per source, decide IN or OUT against a written inclusion criterion. Common criteria: relevance to the Phase-1 question, license compatibility, spatial/temporal coverage, rubric score from earn-the-data, redundancy with another retained source, ethical risk.
  3. Per retained source, decide which columns are IN or OUT. Drop columns are justified individually — irrelevant, redundant, low quality, restricted licensing, ethical risk. Every dropped column has a one-line reason.
  4. Per retained source, define row-level filters (temporal cut, spatial cut, quality cut, sensor-recalibration cut). Each filter has a rationale and a quantified row-count impact (X kept, Y rejected).
  5. Implement filters as code that emits two outputs: the kept rows and the rejected rows. Never delete; always partition. Rejected rows go to a rejected/ partition with a reason_code column. This makes the filter reversible (relax the threshold, re-run, no re-download) and makes sensitivity analysis cheap.
  6. Sampling, if needed: declare the sampling method (simple random, stratified, spatial-stratified per Wang et al. 2012, temporal-windowed, cluster) and the seed. Justify the choice against statistical-bias concerns. For spatial data, the default is spatial-stratified — naïve random sampling violates Tobler's First Law and biases estimates through spatial autocorrelation.
  7. Geospatial selection gotchas to check explicitly: bounding boxes that cross the antimeridian silently drop half the world (use polygon intersection); administrative-boundary epochs change (joining 2024 census tracts to 2010 admin geometries drops boundary records); tile-based reads without overlap miss features that straddle tile edges (always buffer reads by ≥ one feature radius).

Socratic prompts

  • "What is the cost if you keep this row and it turns out to be junk?"
  • "What is the cost if you drop this row and it turns out to matter?"
  • "If a reviewer asks 'why is your dataset 12,847 rows and not 15,000?', can you point to one log entry per missing 2,153 rows?"
  • "Is this filter reversible? If we relax it next week, can we re-run without re-downloading?"
  • "Did you sample? If yes, why is your sample unbiased for the question? For spatial data, did you stratify?"
  • "Are you filtering by bounding box on international data? Does your bounding box cross the antimeridian?"
  • "Which epoch of administrative boundaries are you using? Does it match the epoch of the data you're joining?"

decision-log template (selection)

Copy-paste this Markdown table into your project's decisions.md and add one row per choice.

| dec_id  | source                    | scope                                        | keep? | rationale                                                  | rows in | rows out | filter spec                              | reviewer | date       |
|---------|---------------------------|----------------------------------------------|-------|------------------------------------------------------------|---------|----------|------------------------------------------|----------|------------|
| SEL-001 | OpenData BCN tree inv.    | full file                                    | YES   | core layer for host-tree density                           | 198,243 | 0        | —                                        | RE       | 2026-05-26 |
| SEL-002 | OpenData BCN tree inv.    | `species` column                             | YES   | needed for mycorrhizal-host filter                         | —       | —        | normalize against GBIF backbone          | RE       | 2026-05-26 |
| SEL-003 | OpenData BCN tree inv.    | rows where `planting_date < 1990`            | NO    | data quality before 1990 unreliable per source metadata    | 198,243 | 31,402   | `planting_date >= '1990-01-01'`          | RE       | 2026-05-26 |
| SEL-004 | OSM impervious surfaces   | rows outside Barcelona muni boundary         | NO    | out of scope per Phase-1                                   | —       | —        | `ST_Within(geom, muni_2024)`             | RE       | 2026-05-26 |
| SEL-005 | LUCAS soil raster         | cells outside muni + 500 m buffer            | NO    | buffer protects edge-effect at integration                 | —       | —        | mask by `muni_buffered`                  | RE       | 2026-05-26 |

Field semantics:

  • dec_id is stable (SEL-NNN); never renumber after a row lands in a commit.
  • rows in / rows out are quantified; — is only valid for column-level decisions.
  • filter spec is executable code or a precise predicate, never prose.

exit check for 3.1

  • Every retained source has a written inclusion rationale.
  • Every dropped column has a written exclusion rationale.
  • The total row count in the kept/ partition + the rejected/ partition equals the raw row count. No rows are unaccounted for.
  • Filters are executable code committed to the repo, not described in prose only.
  • If you sampled, the seed is recorded and the sampling design is justified against known biases.
  • Geospatial: no bounding box crosses the antimeridian without polygon-intersection fallback; admin-boundary epochs are matched across joined sources.

Sub-step 3.2 — CLEAN DATA

Chapman et al. (2000, p. 28) require "raising the data quality to the level required by the selected analysis techniques" and a Data Cleaning Report documenting what was changed and why. This is where most of the 40–80% of project effort lives.

actions

  1. Profile every retained source if Phase 2's profiling plan was not yet executed: row count, dtypes, null counts per column, unique counts, numeric summaries (min/max/mean/median/p1/p99), sample of unique values per categorical column, geometry validity rate per spatial layer, CRS per spatial layer, sample of raw rows.
  2. Classify each quality issue along Rahm & Do's (2000) axes: schema vs. instance, single-source vs. multi-source. Multi-source issues defer to Step 3.4 (Integrate). Note Chu et al.'s (2016) extension: distinguish qualitative (rule/constraint-based) from quantitative (statistical/outlier-based) cleaning, and Ilyas & Chu's (2019) four sub-problems: error detection, error repair, value imputation, deduplication.
  3. For missing values, classify the mechanism (MCAR / MAR / MNAR per Rubin 1976; Little & Rubin 2019). The classification is a judgment about the world, not about the data — it requires domain knowledge. Then choose treatment per the decision tree in references/clean.md.
  4. For outliers, detect with one or more methods (≥3σ, ≥1.5×IQR per Tukey 1977, isolation forest, domain-specific bounds, density-based per Aggarwal 2017) but never silently remove. Flag first; decide treatment in a logged step. Detection is automated; treatment is logged.
  5. For duplicates, distinguish exact (byte-identical, removable by hash) from near (entity-resolution problem — defer to 3.4 if it requires cross-source matching). Beware: deduplication run before integration often collapses correct records from different sources that should be linked, not merged.
  6. For type and encoding, enforce types at ingest with a schema validator (Pandera, Great Expectations, dbt tests). Set Unicode normalization to NFC (Davis & Whistler 2024 TR15). Set date locale explicitly — 31/02/2021 is a silent error in US locale and a hard error in EU locale. Number locales (1,234.56 vs 1.234,56) silently invert.
  7. For geospatial layers, run ST_IsValid or geom.is_valid on every layer before any join; repair with make_valid (not buffer(0) — that lies for non-simple polygons per Ramsey 2008); reproject to the project's canonical CRS at ingest (see ADR-001); store source CRS in the cleaning report; never re-project repeatedly — floating-point error accumulates (Atkinson & Curran 1997).
  8. Emit a Data Cleaning Report (Markdown + JSON sidecar) per source, with: issues identified, treatment chosen, mechanism assumption (for missingness), rows affected, residual quality concerns.

Socratic prompts

  • "Is this missingness because the value didn't exist, wasn't measured, or was deleted? Each implies a different fix."
  • "If you impute with the mean, you're betting MCAR. Is that bet defensible against your domain knowledge?"
  • "Why is this an outlier — sensor error, real rare event, or a different unit? The treatment depends on the answer."
  • "If you re-run this cleaning step tomorrow, will it produce bit-identical output? If not, where is the non-determinism?"
  • "Did you check geometries for validity before the join, or did the join silently drop them?"
  • "What CRS is each layer in? Did you cast them all to the project canonical CRS, or are you relying on on-the-fly reprojection?"
  • "How many times has this layer been reprojected? Each reprojection accumulates floating-point error."
  • "Are your date strings ambiguous (03/04/2024 is April 3 in EU, March 4 in US)? Have you forced ISO 8601?"

decision-log template (cleaning)

Copy-paste:

| dec_id  | dataset         | issue                                            | decision                                       | rationale                                                                            | mechanism (if NA) | rows affected | reversible?                          | reviewer | date       |
|---------|-----------------|--------------------------------------------------|------------------------------------------------|--------------------------------------------------------------------------------------|-------------------|---------------|--------------------------------------|----------|------------|
| CLN-001 | tree_inventory  | 12% missing `species`                            | flag + drop to `rejected/`                     | listwise deletion under assumed MAR conditional on `district`; alt: MICE rejected, too few predictors | MAR               | 23,789        | YES (rejected partition)             | RE       | 2026-05-26 |
| CLN-002 | tree_inventory  | 0.4% `diameter_cm` > 300                         | flag + cap at 300, retain `raw_diameter_cm`    | physically implausible for street trees per arboricultural guidance; cap rather than drop | —                 | 793           | YES (raw retained in new column)     | RE       | 2026-05-26 |
| CLN-003 | OSM impervious  | 1.2% invalid geometries (self-intersections)     | repair with `ST_MakeValid`; retain `raw_geom`  | OGC SF compliance per Herring 2011; required for downstream `ST_Intersects`          | —                 | 2,408         | YES (raw_geom retained)              | RE       | 2026-05-26 |
| CLN-004 | soil_raster     | NoData over urban areas (~35%)                   | leave as NoData; do not interpolate            | NoData carries information about urban impermeability; imputing smuggles a model into the data | MNAR              | 0             | YES                                  | RE       | 2026-05-26 |
| CLN-005 | tree_inventory  | mixed date formats `DD/MM/YYYY` and `YYYY-MM-DD` | normalize to ISO 8601 with `dayfirst=True`     | unambiguous; international standard; source confirmed as European                    | —                 | all           | YES                                  | RE       | 2026-05-26 |
| CLN-006 | all spatial     | mixed CRSs (4326, 25831, 3857)                   | reproject all to EPSG:25831 at ingest          | canonical project CRS per ADR-001; ETRS89/UTM31N is the Catalan official grid        | —                 | all           | YES (sources unchanged)              | RE       | 2026-05-26 |
| CLN-007 | tree_inventory  | species names inconsistent (`Quercus ilex` vs `Q. ilex` vs `quercus ilex`) | normalize against GBIF backbone v2024 | canonical taxonomy; reproducible against a published vocabulary                      | —                 | 174,454       | YES (raw_species retained)           | RE       | 2026-05-26 |
| CLN-008 | tree_inventory  | exact duplicate rows (re-ingestion artifacts)    | drop after SHA-256 of all columns              | byte-identical; no information loss                                                  | —                 | 1,243         | YES (rejected partition)             | RE       | 2026-05-26 |

exit check for 3.2

  • Every issue surfaced during profiling has a log entry.
  • For each missing-value treatment, the assumed mechanism (MCAR / MAR / MNAR) is named.
  • No row was modified in place; every modification has either a new column (raw_X preserved) or a new artifact.
  • All spatial layers have is_valid rate = 100% and are in the canonical CRS.
  • The cleaning code is deterministic given the same inputs and seed.
  • A Data Cleaning Report (Markdown + JSON) exists per source.
  • Schema-on-write validation passes (Pandera / Great Expectations / dbt tests).

Sub-step 3.3 — CONSTRUCT DATA

Chapman et al. (2000, p. 29): "constructive data preparation operations such as the production of derived attributes, entire new records, or transformed values for existing attributes." In modern parlance, feature engineering.

actions

  1. List the constructions required by the modeling step: derived attributes, aggregations, normalizations, composite indices. Each one is a named feature with a documented purpose.
  2. For each derived attribute, write the formula. The formula is code; the documentation cites the source of the formula (paper, standard, domain rule). BMI = weight / height² is a derived attribute; distance_to_nearest_park from a spatial join is a derived attribute. Every formula has an input lineage and a unit.
  3. For aggregations — especially zonal statistics in spatial work (mean elevation per neighborhood, sum impervious area per parcel, max NDVI per polygon) — produce multiple summaries (mean, median, sum, count, percentile) where theory does not pre-determine the choice. Defer the modeling-relevant choice to Phase 4; do not pre-commit at construction time.
  4. For normalization and standardization, use a Pipeline object whose fit happens on training data only and whose transform applies identically to test/inference data. Fit-on-full-data is the canonical leakage anti-pattern (Kapoor & Narayanan 2023). Common scalers: min-max (bounds [0,1], bad with outliers), z-score (centers/scales, assumes symmetry), robust (median + IQR, outlier-tolerant), Box-Cox / Yeo-Johnson (skew correction).
  5. For composite indices, follow the OECD/JRC ten-step methodology (Nardo et al. 2008): theoretical framework → data selection → imputation → multivariate analysis → normalization → weighting → aggregation → uncertainty/sensitivity analysis → linking → visualization. The last three are routinely skipped and are where defensibility lives.
  6. Sensitivity analysis on weighting choices is mandatory, not optional. Saltelli et al. (2008) give the methods: Sobol indices for global sensitivity, Morris screening for cheap variance-based screening. Emit not one composite index but a distribution of composite indices under perturbed weights, and report rank stability (e.g., Kendall's tau between baseline and perturbed rankings).
  7. Apply Steegen et al.'s (2016) multiverse principle to construction choices: where a construction decision is a hidden hyperparameter, emit a small grid of alternatives and expose downstream rank/score stability as a quality metric.
  8. Version every construction. A new weight set = a new artifact version (barrier_priority_v1 → _v2), not an overwrite.

Socratic prompts

  • "If you weight the inputs differently, does the answer change? By how much? If a 10% perturbation flips the top-10 rankings, your index is fragile and the modeling phase will inherit that fragility."
  • "Where did this formula come from? Cite a paper, a standard, or a documented domain rule. 'It seemed reasonable' is not a rationale."
  • "What's the unit of this derived attribute? If you can't say, you don't have a derived attribute — you have a number."
  • "Is this feature using information that wouldn't be available at inference time? If yes, you have a leakage bug; Kapoor & Narayanan (2023) catalog this as endemic in ML-based science."
  • "For your zonal statistic, why mean and not median? Or did you compute both and defer the choice?"
  • "If your scaler is fit(df).transform(df), you have leaked test-set statistics into training. Refactor as a Pipeline."

decision-log template (construction)

Copy-paste:

| dec_id  | feature                  | formula                                                          | inputs                                                    | unit              | alternatives considered                            | sensitivity result                                            | reviewer | date       |
|---------|--------------------------|------------------------------------------------------------------|-----------------------------------------------------------|-------------------|----------------------------------------------------|---------------------------------------------------------------|----------|------------|
| CON-001 | `host_density_50m`       | count of mycorrhizal-host trees per 50 m cell                    | `tree_inventory.species ∈ FungalRoot v2.0 list`           | count             | basal-area-weighted variant                        | rank correlation ρ=0.91 between count and BA variants         | RE       | 2026-05-26 |
| CON-002 | `imperv_frac_50m`        | fraction of cell area covered by impervious polygons             | OSM impervious surfaces v2024-Q1                          | dimensionless [0,1] | —                                                  | —                                                             | RE       | 2026-05-26 |
| CON-003 | `soil_suitability_50m`   | weighted sum of normalized pH (40%), OC (35%), texture (25%)     | LUCAS topsoil raster v2018                                | dimensionless [0,1] | unweighted mean                                    | weights ADR-003; Sobol S1 [0.31, 0.42, 0.27]                  | RE       | 2026-05-26 |
| CON-004 | `barrier_priority_v1`    | composite of CON-001..003 with weights (0.45, 0.30, 0.25)        | CON-001, CON-002, CON-003                                 | dimensionless [0,1] | equal weights; expert-elicited weights             | rank-stability Kendall τ=0.84 under ±10% weight perturbation  | RE       | 2026-05-26 |
| CON-005 | `priority_zscore`        | z-score of `barrier_priority_v1` fit on training cells           | CON-004 (training cells only)                             | z-score           | min-max                                            | scaler serialized as `scaler.joblib`; train-only fit          | RE       | 2026-05-26 |

exit check for 3.3

  • Every derived feature has a formula, an input lineage, a citation if formula is non-trivial, and a unit.
  • Composite indices have a weighting ADR and a sensitivity-analysis log with a rank-stability metric.
  • No fit-then-leak pattern: all scalers / encoders are serialized as pipeline objects and fit on training data only.
  • Feature names are snake_case, descriptive, and unit-suffixed where applicable.
  • A construction multiverse (alternatives + sensitivity) is logged where the choice is a hyperparameter.

Sub-step 3.4 — INTEGRATE DATA

Chapman et al. (2000, p. 29): "methods whereby information is combined from multiple tables or records to create new records or values." This is the most error-rich generic task — almost every famous pipeline disaster lives here.

actions

  1. For each planned join, declare the cardinality: 1:1, 1:N, or N:N. If N:N, stop and re-design — N:N is almost always a bug. If you genuinely need an N:N relationship, materialize it as an explicit association table with its own primary key.
  2. Pre-join, snapshot row counts. Post-join, assert the expected row count. A test that fails this assertion is a release blocker, not a warning. assert df_joined.shape[0] == df_left.shape[0] for a declared 1:1 join.
  3. For spatial joins, declare the predicate explicitly (intersects, contains, within, nearest) and the boundary convention (open vs. closed). Test on a known edge case (a point exactly on a polygon boundary, a polygon spanning two tiles). Different tools default differently; pin it.
  4. For fuzzy / entity-resolution joins, use a Fellegi–Sunter-based tool (Fellegi & Sunter 1969; modern implementation: Splink — Linacre, Kennedy & Tilling 2022). Declare the threshold; produce a precision/recall estimate on a labeled validation sample (Christen 2012; Binette & Steorts 2022).
  5. Reconcile units, locales, time zones, and CRSs at ingest, never at the join. Joining on misaligned units silently produces wrong answers (NASA Mars Climate Orbiter, 1999). Store units in the schema (column suffix convention temperature_c, area_m2); convert at ingest.
  6. Apply the 2× resolution rule. Integration output is no finer than 2× the coarsest input resolution. If finest input is 1 m and coarsest is 30 m, integrate at 60 m, not 1 m. This derives from Nyquist–Shannon sampling theory applied to spatial sampling (Atkinson & Curran 1997); skipping it produces visually-plausible but methodologically false fine-grained outputs. Document the chosen integration resolution as an ADR.
  7. Temporal alignment: reconcile sampling rates (downsample with explicit aggregation; upsample with explicit interpolation); store all times in UTC and convert to local only at presentation; log source time zone in the data dictionary; check reporting-lag mismatches across sources (joining "as of today" silently mixes epochs if source A is real-time and source B has 30-day lag).
  8. Declare a conflict-resolution policy before the join: trust hierarchy (official cadastre overrides OSM), recency (newest wins), voting/consensus (modal value), or flag-and-defer (conflict=True, modeler decides). Conflicts go to a conflicts/ audit table. Never resolve conflicts silently.

Socratic prompts

  • "If your join doubles the row count, was that expected? If not, where's the duplicate key?"
  • "What happens to a feature that falls exactly on the boundary of two zones? Two assignments? Zero? One arbitrary one? Did you test this?"
  • "Your finest raster is 1 m. Your coarsest is 30 m. Why is your output 1 m? Is it really 1 m, or is it 30 m pretending to be 1 m?"
  • "When two sources disagree, who wins? Is that a documented policy or an accident?"
  • "Are the two sources reporting the same epoch? If one has a 30-day lag, joining them 'as of today' silently mixes data from different points in time."
  • "Are units harmonized? Is temperature Celsius in source A and Kelvin in source B? Is area m² in one and km² in the other?"
  • "Did you assert post-join row count? If you assume a 1:1 and got 1:1.07, that 7% is a bug you have to explain."

decision-log template (integration)

Copy-paste:

| dec_id  | join                           | type            | predicate / key                                | cardinality (expected → actual)     | conflict policy                                  | rows in  | rows out                | reviewer | date       |
|---------|--------------------------------|-----------------|------------------------------------------------|-------------------------------------|--------------------------------------------------|----------|-------------------------|----------|------------|
| INT-001 | trees ⋈ districts              | spatial         | `ST_Within` (closed boundary)                  | 1:1 → 1:1                           | —                                                | 174,454  | 174,454                 | RE       | 2026-05-26 |
| INT-002 | host_density ⋈ soil_suitability | raster align    | resample to 50 m EPSG:25831 via bilinear       | grid:grid                           | —                                                | —        | —                       | RE       | 2026-05-26 |
| INT-003 | trees ⋈ OSM tree points        | fuzzy spatial   | within 5 m AND species match                   | 1:1 → 1:1.07 (7% multi-match)       | municipal wins; OSM logged to `conflicts/`       | 174,454  | 174,454 (12,213 confl.) | RE       | 2026-05-26 |
| INT-004 | all rasters → 50 m grid        | resolution rule | 2× rule (coarsest input = 25 m → 50 m output)  | grid:grid                           | ADR-002 fixes 50 m as canonical integration cell | —        | —                       | RE       | 2026-05-26 |
| INT-005 | sensor_hourly ⋈ census_annual  | temporal        | aggregate hourly→annual (mean) before join     | 8760:1 → 1:1                        | —                                                | 8,760    | 1                       | RE       | 2026-05-26 |

exit check for 3.4

  • All joins have asserted cardinalities; tests fail loudly on mismatch.
  • All conflicts have a logged resolution policy and are audited to conflicts/.
  • Integration output resolution is no finer than 2× the coarsest input; ADR exists for the chosen integration cell.
  • CRSs, units, time zones are uniform across the integrated artifact; conversions happened at ingest, not at join.
  • No silent row inflation or deflation; row-accounting reconciles raw → kept → rejected → conflict-deferred.

Sub-step 3.5 — FORMAT DATA

Chapman et al. (2000, p. 29): "primarily syntactic modifications made to the data that do not change its meaning" — reorderings, retypings, file-format conversions to suit the modeling tool. In 2000 this was minor; in modern pipeline practice it is consequential because format choice determines reproducibility, performance, and interoperability.

actions

  1. Choose the target format based on access pattern, not familiarity. See the decision matrix in references/format.md:
    • Tabular analytical → Apache Parquet (or GeoParquet v1.1 for vector geospatial; OGC standard, geometry types native in Parquet 2.11 since March 2025).
    • Multi-dim arrays → Zarr (cloud-native, chunked, parallel reads from object storage; Miles et al. 2020) or NetCDF if downstream tools demand it.
    • Desktop-GIS round-tripping → GeoPackage (OGC standard, replaces Shapefile; multiple layers, proper types, Unicode, long field names — all areas where Shapefile is broken).
    • Streaming → Apache Avro or Arrow IPC.
    • Human-readable export → CSV with explicit UTF-8 encoding and locale (acceptable only for small reference tables; never as pipeline-internal format).
  2. Enforce naming conventions: snake_case, no spaces, no special characters, unit-suffixed where applicable (area_m2, temperature_c, population_count), source-prefixed where ambiguous (census_pop_2020, osm_pop_2020). Pin a glossary.md in the repo. Geometry column: geom or geometry, pick one project-wide.
  3. Embed metadata in the file, never in a sidecar that can be lost. Parquet footer carries schema and column statistics; GeoParquet metadata block carries CRS; GeoPackage stores CRS and extents in SQLite metadata tables; NetCDF/Zarr use CF Conventions (Eaton et al. 2024) for attribute vocabulary.
  4. Reshape to tidy form (Wickham 2014): each variable a column, each observation a row, each type of observational unit a table. Wide-to-long pivots happen here, not in modeling. Tidy data is the modeling-ready layout regardless of whether it is the storage-optimal layout.
  5. Ship a datasheet (Gebru et al. 2021) or data card (Pushkarna, Zaldivar & Kjartansson 2022) with every published artifact: motivation, composition, collection, preprocessing, uses, distribution, maintenance.
  6. Mint a persistent identifier: Zenodo DOI for archived course deliverables; internal UUID for pipeline-internal artifacts. Meet FAIR (Wilkinson et al. 2016): Findable, Accessible, Interoperable, Reusable.

Socratic prompts

  • "If someone three years from now finds this file on a hard drive with no other context, can they tell what's in it, what the CRS is, what the units are, and how it was made?"
  • "Why CSV? If the answer is 'it's easy to open in Excel', that's not an engineering rationale."
  • "Is your column name self-documenting? pop is not; census_pop_2020_count is."
  • "Does your file declare its schema, or does it rely on the reader to guess?"
  • "If you ship the geometry column as geom here and geometry in the next artifact, you have a future bug."
  • "Did you embed metadata in the file (Parquet footer, GeoPackage tables, NetCDF attrs) or only in a sidecar that can be lost?"

decision-log template (format)

Copy-paste:

| dec_id  | artifact                          | format       | rationale                                                                       | metadata embedded?                                  | datasheet? | identifier   | reviewer | date       |
|---------|-----------------------------------|--------------|---------------------------------------------------------------------------------|-----------------------------------------------------|------------|--------------|----------|------------|
| FMT-001 | `barrier_priority_v1_50m.parquet` | GeoParquet   | cloud-native, columnar, queryable from DuckDB / Sedona / GeoPandas              | YES (CRS in geo metadata block; schema in footer)   | YES        | Zenodo DOI   | RE       | 2026-05-26 |
| FMT-002 | `barrier_priority_v1_50m.gpkg`    | GeoPackage   | desktop-GIS round-trip for collaborators on QGIS                                | YES (`gpkg_metadata` SQLite table)                  | shared     | —            | RE       | 2026-05-26 |
| FMT-003 | `decisions.md`                    | Markdown CSV | human-readable; reviewed in PRs; copy-pasteable to thesis appendix              | header row, self-describing                         | —          | repo path    | RE       | 2026-05-26 |
| FMT-004 | `cleaning_report.json`            | JSON         | machine-readable companion to per-source cleaning reports                       | JSON schema versioned in `schemas/cleaning.json`    | —          | repo path    | RE       | 2026-05-26 |
| FMT-005 | `rejected/*.parquet`              | Parquet      | partitioned audit; same schema as kept + `reason_code` column                   | YES                                                 | —          | repo path    | RE       | 2026-05-26 |

exit check for 3.5

  • Format is justified, not defaulted (no Shapefile, no naked CSV for pipeline-internal artifacts).
  • Names follow the convention; glossary.md is in the repo.
  • Metadata travels with the file (in-file, not sidecar-only).
  • Datasheet exists and answers all seven Gebru et al. categories plus Limitations.
  • Artifact has a stable identifier (DOI for published, UUID for internal).
  • Tidy form: each variable a column, each observation a row, each unit a table.

Phase-level exit criteria

Phase 3 is complete only when all of the following are true. Treat this as a CI gate.

  • Reproducibility: rebuild from raw inputs with one command (make rebuild, dagster materialize, dbt build). Output is bit-identical (or, where non-determinism is intentional, identical up to the declared seed).
  • Lineage: for any value in the final artifact, the inputs and transformations that produced it can be retrieved in one query (Dagster asset graph, dbt DAG, OpenLineage event log).
  • Row accounting: raw row count = kept + rejected + conflict-deferred. No rows are unaccounted for.
  • Schema validation: schema-on-write enforced; the artifact passes its own contract; CI fails on schema drift.
  • Geometry validity: 100% of geometries are valid (ST_IsValid); CRS uniform and explicit (no on-the-fly reprojection assumed).
  • Unit consistency: every numeric column has a declared unit and the values are in that unit.
  • No leakage: scalers/encoders fit on train only; features do not use future-only information (Kapoor & Narayanan 2023).
  • Sensitivity declared: weight choices and threshold choices have a sensitivity-analysis log with a stability metric.
  • Datasheet shipped: all seven Gebru et al. (2021) categories answered, plus Limitations.
  • Decision log complete: every transformation has a SEL/CLN/CON/INT/FMT log entry.
  • ADRs filed: load-bearing choices (canonical CRS, integration resolution, composite-index weights, conflict policy, format) have ADRs.
  • Anti-pattern audit: none of the anti-patterns below is present.
  • Data contract: the YAML below exists, is versioned in the repo, and CI validates the artifact against it.

If any item is missing, the Phase-3 deliverable is incomplete regardless of how good the artifact looks.

Anti-patterns (call these out and stop the user)

In order of severity for graduate-seminar pipelines:

  1. Silent row drop. df = df.dropna() with no log entry. → Stop. Always log + partition to rejected/ with reason_code.
  2. In-place overwrite of raw data. Cleaned values written back to the source column. → Stop. Raw is immutable; cleaned outputs are new columns or new artifacts (raw_diameter_cm retained alongside cleaned diameter_cm).
  3. fillna(mean) without naming the mechanism. Implicitly assumes MCAR. → Stop. State MCAR/MAR/MNAR; if MCAR, justify; if MAR, use proper multiple imputation (MICE per Van Buuren 2018); if MNAR, flag and add sensitivity bracket.
  4. CRS implicit or mixed. Layer in WGS84 overlaid on UTM "looks fine" in QGIS due to on-the-fly reprojection; integration is silently wrong. → Stop. Declare and validate CRS at ingest; reproject everything to the canonical CRS.
  5. Joining at the finest resolution available. 30 m raster joined to 1 m vector and published as 1 m. → Stop. Apply the 2× rule; output resolution = 2× coarsest input.
  6. N:N join without an association table. Cartesian explosion, silent double-counting. → Stop. Re-design as 1:N + N:1 via an explicit association entity.
  7. Fit-on-full-data scalers. Test-set statistics leak into training. → Stop. Refactor as a Pipeline (scikit-learn) or equivalent; fit on train only; serialize for inference.
  8. Shapefile as the canonical output. Truncated field names, no Unicode, no CRS in geometry, multiple sidecar files that can drift. → Stop. Use GeoPackage (desktop round-trip) or GeoParquet (analytical).
  9. Composite index with one set of weights and no sensitivity analysis. → Stop. Run a Sobol or Morris perturbation; report rank stability; emit a multiverse per Steegen et al. 2016.
  10. Conflict resolution that is "whichever loaded last". → Stop. Declare a policy (trust hierarchy, recency, voting, flag-and-defer) before the join; audit conflicts to conflicts/.
  11. pd.read_csv ingest into pd.to_csv egress with no schema enforcement. → Stop. Use Parquet (or GeoParquet) with a schema; enforce on write with Pandera / Great Expectations / dbt tests.
  12. No data contract for Phase 4. → Stop. Phase 3 is not done. Emit the YAML contract below.
  13. Repeated reprojection. Each reprojection accumulates floating-point error. → Stop. Store data in canonical CRS; reproject only at output.
  14. Date locale ambiguity. 03/04/2024 silently means different dates in US vs EU. → Stop. Force ISO 8601 at ingest; declare source locale in the cleaning report.
  15. Hand-written pandas script with no DAG orchestrator. Passes for an exploration; fails as a pipeline (no retries, no lineage, no idempotency). → Stop. Wrap in Airflow / Dagster / Prefect / dbt; idempotency is not optional.

Handoff to Phase 4 — the data contract

Phase 4 (Modeling) cannot begin until the Phase-3 output meets a data contract the modeling step commits against. Emit the YAML below verbatim into phase-3/data-contract.yaml; CI must validate the artifact against it; modeling code reads it as the authoritative source of schema, units, and SLOs.

# phase-3/data-contract.yaml
# CRISP-DM Phase 3 → Phase 4 handoff contract
# Producer: data-engineering pipeline
# Consumer: modeling step
# Version: 1.0.0 (semver; bump on breaking schema change)

contract_version: 1.0.0
artifact:
  name: barrier_priority_v1_50m
  format: GeoParquet              # one of: Parquet | GeoParquet | Zarr | GeoPackage
  location: s3://group4/published/barrier_priority/v1/barrier_priority_v1_50m.parquet
  mirror:                          # optional secondary format for round-tripping
    format: GeoPackage
    location: s3://group4/published/barrier_priority/v1/barrier_priority_v1_50m.gpkg
  bytes_approx: 47_300_000
  rows_approx: 41_237              # number of 50 m cells inside muni + buffer

schema:
  cell_id:
    type: int64
    nullable: false
    unique: true
    description: stable integer id of the 50 m integration cell
  geometry:
    type: geometry
    geometry_type: Polygon
    crs: EPSG:25831                # ETRS89 / UTM zone 31N (Catalonia canonical)
    validity_pct: 100
    nullable: false
  host_density:
    type: float64
    unit: count                    # count of host trees in the cell
    range: [0, 200]
    nullable: false
    derived_from: [tree_inventory_v2024]
  imperv_frac:
    type: float64
    unit: dimensionless
    range: [0, 1]
    nullable: false
    derived_from: [osm_impervious_v2024_q1]
  soil_suit:
    type: float64
    unit: dimensionless
    range: [0, 1]
    nullable: false
    derived_from: [lucas_topsoil_v2018]
  priority_score:
    type: float64
    unit: dimensionless
    range: [0, 1]
    nullable: false
    derived_from: [host_density, imperv_frac, soil_suit]
    formula_ref: CON-004
  _ingested_at:
    type: timestamp
    timezone: UTC
    nullable: false
  _source_version:
    type: string
    nullable: false
    description: semver of the upstream pipeline run

resolution:
  spatial_m: 50                    # integration cell size
  rule: "2x coarsest input (LUCAS topsoil @ 25 m → 50 m output)"
  adr_ref: ADR-002
  finer_resolution_unsupported: true

crs:
  canonical: EPSG:25831
  source_crses_seen: [EPSG:4326, EPSG:25831, EPSG:3857]
  reprojection_policy: "ingest-time only; never reprojected in pipeline interior"
  adr_ref: ADR-001

freshness:
  produced_at: 2026-05-26T14:32:00Z
  valid_from: 2026-05-26
  valid_until: 2027-05-26          # contract void after this; trigger rebuild
  upstream_refresh_cadence: quarterly

quality_slos:
  missingness_max_pct: 0           # 0 = no nulls allowed in published columns
  geometry_validity_pct: 100
  duplicate_cell_id_count: 0
  row_count_min: 40_000
  row_count_max: 42_000
  schema_drift_tolerance: 0        # CI fails on any schema change

lineage:
  pipeline: dagster://group4/barrier_priority
  decision_log: phase-3/decisions.md
  adr_index: phase-3/adrs/INDEX.md
  upstream_artifacts:
    - phase-2/data-inventory.md
    - phase-2/data-sheets/tree-inventory.md
    - phase-2/data-sheets/osm-impervious.md
    - phase-2/data-sheets/lucas-topsoil.md

provenance:
  raw_sources:
    - name: OpenData BCN tree inventory
      url: https://opendata-ajuntament.barcelona.cat/...
      vintage: 2024-01
      license: CC-BY-4.0
      retrieved_at: 2026-05-01
    - name: OSM impervious surfaces (BCN extract)
      url: https://download.geofabrik.de/...
      vintage: 2024-Q1
      license: ODbL-1.0
      retrieved_at: 2026-05-02
    - name: LUCAS topsoil raster
      url: https://esdac.jrc.ec.europa.eu/...
      vintage: 2018
      license: EU-open-data
      retrieved_at: 2026-05-03

sensitivity:
  composite_weights:
    adr_ref: ADR-003
    method: Sobol global sensitivity (Saltelli et al. 2008)
    rank_stability_kendall_tau: 0.84
    perturbation: "±10% on each weight, n=1024 samples"
  threshold_choices:
    - decision_id: CLN-002
      threshold: 300 cm diameter cap
      stability: "0.4% of rows affected; downstream priority_score unchanged within 0.5%"

datasheet:
  path: phase-3/datasheets/barrier_priority_v1_50m.md
  template: "Gebru et al. 2021 + Limitations"
  sections_complete: [motivation, composition, collection, preprocessing, uses, distribution, maintenance, limitations]

identifier:
  type: DOI
  value: 10.5281/zenodo.XXXXXXX    # minted on Zenodo at publication
  internal_uuid: 6f1c2a3e-5b8d-4c2f-9a1e-7d3b4c5e6f70

rebuild:
  command: "make rebuild"
  alt_commands:
    - "dagster asset materialize --select barrier_priority_v1_50m"
    - "dbt build --select barrier_priority_v1_50m"
  expected_runtime_min: 18
  determinism: "bit-identical given pinned upstream versions and seed=20260526"

contacts:
  data_owner: [email protected]
  technical_owner: [email protected]
  on_call: "see runbook in phase-3/RUNBOOK.md"

If any field is missing or any SLO fails, Phase 3 is not done. Hold the line — letting Phase 4 start against an incomplete contract destroys the ability to attribute downstream failures to data versus model.

References

Aggarwal, C. C. (2017). Outlier Analysis (2nd ed.). Springer.

Akidau, T., et al. (2015). The Dataflow Model. VLDB, 8(12), 1792–1803.

Atkinson, P. M., & Curran, P. J. (1997). Choosing an appropriate spatial resolution for remote sensing investigations. Photogrammetric Engineering and Remote Sensing, 63(12), 1345–1351.

Binette, O., & Steorts, R. C. (2022). (Almost) all of entity resolution. Science Advances, 8(12), eabi8021.

Chapman, P., Clinton, J., Kerber, R., Khabaza, T., Reinartz, T., Shearer, C., & Wirth, R. (2000). CRISP-DM 1.0: Step-by-step data mining guide. SPSS Inc.

Christen, P. (2012). Data Matching. Springer.

Chu, X., Ilyas, I. F., Krishnan, S., & Wang, J. (2016). Data Cleaning: Overview and Emerging Challenges. SIGMOD, 2201–2206.

Cochran, W. G. (1977). Sampling Techniques (3rd ed.). Wiley.

Dasu, T., & Johnson, T. (2003). Exploratory Data Mining and Data Cleaning. Wiley.

Davis, M., & Whistler, K. (2024). Unicode Normalization Forms. Unicode TR15.

Eaton, B., et al. (2024). NetCDF Climate and Forecast (CF) Metadata Conventions, v1.11.

Fellegi, I. P., & Sunter, A. B. (1969). A theory for record linkage. JASA, 64(328), 1183–1210.

Gebru, T., et al. (2021). Datasheets for datasets. CACM, 64(12), 86–92.

Herring, J. R. (Ed.). (2011). OpenGIS Simple Feature Access — Part 1. OGC.

Ilyas, I. F., & Chu, X. (2019). Data Cleaning. ACM Books.

Kandel, S., Paepcke, A., Hellerstein, J., & Heer, J. (2011). Wrangler. CHI, 3363–3372.

Kapoor, S., & Narayanan, A. (2023). Leakage and the reproducibility crisis in ML-based science. Patterns, 4(9), 100804.

Krishnan, S., Wang, J., Wu, E., Franklin, M. J., & Goldberg, K. (2016). ActiveClean. VLDB, 9(12), 948–959.

Kuhn, M., & Johnson, K. (2019). Feature Engineering and Selection. CRC.

Linacre, R., Kennedy, P., & Tilling, K. (2022). Splink. JOSS, 7(80), 4324.

Little, R. J. A., & Rubin, D. B. (2019). Statistical Analysis with Missing Data (3rd ed.). Wiley.

Martínez-Plumed, F., et al. (2021). CRISP-DM Twenty Years Later. IEEE TKDE, 33(8), 3048–3061.

Miles, A., et al. (2020). Zarr. Zenodo.

Nardo, M., Saisana, M., Saltelli, A., Tarantola, S., Hoffman, A., & Giovannini, E. (2008). Handbook on Constructing Composite Indicators. OECD / JRC.

Nygard, M. (2011). Documenting Architecture Decisions.

Polyzotis, N., Roy, S., Whang, S. E., & Zinkevich, M. (2018). Data Lifecycle Challenges in Production ML. SIGMOD Record, 47(2), 17–28.

Press, G. (2016, March 23). Cleaning Big Data. Forbes.

Pushkarna, M., Zaldivar, A., & Kjartansson, O. (2022). Data Cards. FAccT, 1776–1826.

Rahm, E., & Do, H. H. (2000). Data Cleaning: Problems and Current Approaches. IEEE Data Eng. Bulletin, 23(4), 3–13.

Ramsey, P. (2008). PostGIS in Action. OSGeo Journal.

Rubin, D. B. (1976). Inference and missing data. Biometrika, 63(3), 581–592.

Saltelli, A., Ratto, M., Andres, T., Campolongo, F., Cariboni, J., Gatelli, D., Saisana, M., & Tarantola, S. (2008). Global Sensitivity Analysis: The Primer. Wiley.

Schröer, C., Kruse, F., & Gómez, J. M. (2021). A Systematic Literature Review on Applying CRISP-DM. Procedia CS, 181, 526–534.

Sculley, D., et al. (2015). Hidden Technical Debt in ML Systems. NeurIPS, 28, 2503–2511.

Soudzilovskaia, N. A., et al. (2020). FungalRoot. New Phytologist, 227(3), 955–966.

Steegen, S., Tuerlinckx, F., Gelman, A., & Vanpaemel, W. (2016). Increasing transparency through a multiverse analysis. PPS, 11(5), 702–712.

Tukey, J. W. (1977). Exploratory Data Analysis. Addison-Wesley.

Van Buuren, S. (2018). Flexible Imputation of Missing Data (2nd ed.). CRC.

Vohra, D. (2016). Apache Parquet. In Practical Hadoop Ecosystem (pp. 325–335). Apress.

Wang, J.-F., Stein, A., Gao, B.-B., & Ge, Y. (2012). A review of spatial sampling. Spatial Statistics, 2, 1–14.

Wickham, H. (2014). Tidy Data. JSS, 59(10), 1–23.

Wilkinson, M. D., et al. (2016). FAIR Guiding Principles. Scientific Data, 3, 160018.

Woodie, A. (2020, July 6). Data Prep Still Dominates Data Scientists' Time. BigDATAwire.

Keep looking

Skills are one crate of 328,083. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.