---
name: validate-dlt-connector-data
description: Validate that a migrated dlt connector loads the same data as the original EWAH connector. Use when comparing row counts, schemas, and records post-migration, running audit_compare.py, investigating data mismatches, or adjusting dbt base models for schema/column differences between EWAH and dlt.
disable-model-invocation: true
---

# Validate Connector Data

Compare data loaded by EWAH and dlt after migrating a connector to ensure the migration is correct.

## Context

After migrating an EWAH connector to dlt, both loaders should produce equivalent data. This skill uses a Python audit script that compares row counts, column schemas, date ranges, and record-level data between the two schemas. The script generates a discrepancy report and SQL queries for manual investigation.

Schema naming convention:

- EWAH schema: `ewah_<connector>` (e.g., `ewah_facebook`)
- DLT schema: `<connector>` (e.g., `facebook`)

## Prerequisites

- Both EWAH and dlt have loaded data to the same Postgres database
- The connector's `.dlt/secrets.toml` is configured with database credentials
- The audit script (`${CLAUDE_SKILL_DIR}/scripts/audit_compare.py`) is available

## Steps

1. Ensure the database host in `secrets.toml` is set to `localhost` (the audit script runs on the host, not in Docker).

   ```bash
   grep "^host = " connectors/<connector_name>/.dlt/secrets.toml
   ```

   Switch to localhost if needed:

   ```bash
   sed -i 's/^host = "host.docker.internal"/host = "localhost"/' \
     connectors/<connector_name>/.dlt/secrets.toml
   ```

2. Check table names in both schemas to identify any naming differences.

   ```bash
   PGPASSWORD=dev12345_ psql -h localhost -p 5434 -U dlt -d postgres -c "
   SELECT table_schema, table_name
   FROM information_schema.tables
   WHERE table_schema IN ('ewah_<connector>', '<connector>')
   ORDER BY table_schema, table_name;"
   ```

3. Run the comparison script. Use colon notation when EWAH and dlt table names differ.

   ```bash
   uv run --with psycopg2-binary ${CLAUDE_SKILL_DIR}/scripts/audit_compare.py \
     --connector <connector_name> \
     --tables <ewah_table>:<dlt_table>
   ```

4. Review the generated output files:

   | File | Purpose |
   |------|---------|
   | `migration-docs/<connector>/DISCREPANCY_REPORT.md` | Full report with actionable items |
   | `migration-docs/<connector>/audit_queries.sql` | SQL for manual investigation |

5. If records don't match 100%, investigate the mismatch patterns.

   | Pattern | Likely Cause | Action |
   |---------|--------------|--------|
   | Same PK, same date, small value differences | Timing difference (today's data) | Accept as expected |
   | Same PK, different dates | Date range or filter difference | Check extraction params |
   | Different PKs entirely | Missing records | Check extraction filters |
   | All mismatches on today's date | API metrics updated between runs | Exclude today from comparison |

6. Run a manual mismatch query for deeper investigation if needed.

   ```sql
   WITH ewah_data AS (
       SELECT "<pk_column>"::text AS pk, "<date_column>"::text AS date_col,
              "<metric>"::text AS metric
       FROM "ewah_<connector>"."<table>"
   ),
   dlt_data AS (
       SELECT "<pk_column>"::text AS pk, "<date_column>"::text AS date_col,
              "<metric>"::text AS metric
       FROM "<connector>"."<table>"
   ),
   ewah_only AS (SELECT *, 'ewah' as source FROM ewah_data EXCEPT SELECT *, 'ewah' FROM dlt_data),
   dlt_only AS (SELECT *, 'dlt' as source FROM dlt_data EXCEPT SELECT *, 'dlt' FROM ewah_data)
   SELECT * FROM (SELECT * FROM ewah_only UNION ALL SELECT * FROM dlt_only) x
   ORDER BY pk, date_col, source DESC LIMIT 20;
   ```

7. Document findings in the discrepancy report: root cause of mismatches, accepted differences, and issues requiring fixes.

8. Add a **"Recommended dbt base model adjustments"** section at the end of the discrepancy report. This section must always be included — it tells the dbt developer exactly what to change when switching source references from the EWAH schema to the dlt schema. Cover:

   - **Schema and table name changes** — e.g. `{{ source('raw_personio', 'timeoffs') }}` becomes `{{ source('personio', 'absences') }}`
   - **Date/timestamp column changes** — EWAH typically stores dates as `text`, requiring explicit casts (`::timestamp`, `TO_TIMESTAMP()`, etc.) in staging models. dlt stores them as `timestamp with time zone`, so text-parsing logic can be removed. Note: `::date` on a `timestamptz` column is still valid and useful.
   - **Numeric column changes** — EWAH stores numbers as `text`, requiring `::numeric` or `::int` casts. dlt types them properly (`bigint`, `double precision`).
   - **Split-type columns** — dlt stores mixed-type fields (e.g. a field that is sometimes integer, sometimes float) in separate columns: `col` (bigint) and `col__v_double` (double precision). Staging models must `COALESCE(col, col__v_double)` to reconstruct the original value.
   - **Before/after SQL examples** — show the actual EWAH base model pattern and the equivalent dlt base model pattern for each affected table.
   - **General pattern table** — summarize the mapping of EWAH patterns to dlt replacements.

   Example structure:

   ```markdown
   ## Recommended dbt base model adjustments

   ### `<table_name>`

   **Date columns no longer need text parsing.**

   \```sql
   -- EWAH base model (before)
   SELECT
       id,
       start_date::date AS start_date,
       ...
   FROM {{ source('raw_<connector>', '<ewah_table>') }}

   -- dlt base model (after)
   SELECT
       id,
       start_date::date AS start_date,  -- already timestamptz, no text parsing needed
       ...
   FROM {{ source('<connector>', '<dlt_table>') }}
   \```

   ### General pattern for all tables

   | EWAH pattern | dlt replacement | Reason |
   |--------------|-----------------|--------|
   | `col::timestamp` on text column | `col` (already timestamptz) | dlt types dates properly |
   | `TO_TIMESTAMP(col, 'format')` | `col` | No text parsing needed |
   | `col::numeric` on text column | `col` (already bigint/numeric) | dlt types numbers properly |
   | Split-type columns (`col` + `col__v_double`) | `COALESCE(col, col__v_double)` | dlt stores mixed types separately |
   | `{{ source('raw_<connector>', 'table') }}` | `{{ source('<connector>', 'table') }}` | Different schema name |
   ```

9. Commit the validation report.

   ```bash
   git add migration-docs/<connector>/DISCREPANCY_REPORT.md migration-docs/<connector>/audit_queries.sql
   git commit -m "docs(<connector>): add data validation report"
   ```

## Validation

- [ ] secrets.toml host set to `localhost`
- [ ] Table names verified in both schemas
- [ ] Audit script ran successfully
- [ ] Discrepancy report generated
- [ ] All mismatches investigated
- [ ] Root cause identified (timing, filters, transforms)
- [ ] dbt base model adjustments section included in report
- [ ] Report committed to version control

## Examples

**Timing differences are expected:**

```
pk                 | date_col   | metric | source
120241820930050271 | 2026-02-01 | 15035  | ewah    <- EWAH ran later
120241820930050271 | 2026-02-01 | 14807  | dlt     <- DLT ran earlier
```

Both connectors are correct -- metrics changed between extraction times.