Azure Data Factory developer on retainer: PCB_AccountStatusSync ADF dataset schema AccountStatus maxLength:2 silently truncates NVARCHAR(10) status codes — 1,847 delinquent accounts show zero exposure in Synapse risk dashboard for 98 days

Azure Data Factory v2 • West US 2 • Self-Hosted Integration Runtime • Copy Activity • Source Dataset schema • maxLength constraint • NVARCHAR(10) truncation • Azure Synapse Analytics • retainer billing

Pacific Crest Bancorp’s risk committee had been reviewing its monthly delinquency dashboard in Azure Synapse Analytics for three months without alarm. The dashboard showed the bank’s total delinquent loan exposure as zero — zero accounts, zero dollars. In October 2026, the Chief Risk Officer flagged the figure as implausible and requested an audit. The ADF developer retainer was engaged the same afternoon. Within two hours, the root cause was identified: an Azure Data Factory dataset schema had a 2-character length constraint on a status code column that the source SQL Server had extended to 10 characters three months earlier. Every status code in the new format had been silently truncated to 2 characters during the daily Copy Activity run, producing codes that matched no known status in the downstream Synapse dimension table. The bank’s 1,847 delinquent loan accounts, representing approximately $127 million in exposure, had been invisible in Synapse Analytics for 98 consecutive daily pipeline runs.

Pacific Crest Bancorp ran Azure Data Factory v2 in the West US 2 region. The central loan account status pipeline, PCB_AccountStatusSync, ran daily at 04:00 AM on a Schedule Trigger. The pipeline contained a single Copy Activity, CopyLoanAccountStatus, that read loan account status records from the on-premises SQL Server 2022 database PCB_LoanCore via a Self-Hosted Integration Runtime (SHIR) running on PCB-SHIR-01, and wrote them to the Azure SQL Database table STAGING_ACCOUNT_STATUS in the PCB Analytics environment. The Source Dataset, DS_SQL_LoanAccountStatus, had been configured in March 2025 when the pipeline was first built. Its schema was imported from the SQL Server source table at that time via ADF’s “Import Schema” function in the dataset editor. The source table at that time had an AccountStatus column defined as NCHAR(2), holding two-character status codes: 'AC' (Active), 'CL' (Closed), 'DL' (Delinquent), 'PD' (Paid Off), 'CO' (Charge-Off), 'BK' (Bankrupt).

In July 2026, the PCB loan servicing team standardized its status code schema across all internal systems to use full English words rather than 2-character abbreviations. The source table dbo.ACCOUNT_STATUS_LOOKUP was modified: the AccountStatus column was altered from NCHAR(2) to NVARCHAR(10), and the status values were updated to 'ACTIVE', 'CLOSED', 'DELINQUENT', 'PAID_OFF', 'CHARGE_OFF', 'BANKRUPT'. The change was applied by the database team on July 14, 2026. The ADF developer retainer was not notified. The ADF dataset DS_SQL_LoanAccountStatus was not updated.

How ADF Source Dataset schema maxLength constraints affect Copy Activity data movement

When an ADF dataset’s schema is imported from a SQL Server source via the “Import Schema” function, ADF stores the column definitions — including name, data type, and length constraints — in the dataset’s JSON configuration file. For a column defined as NCHAR(2) at the time of schema import, ADF stores the column as {"name": "AccountStatus", "type": "String", "maxLength": 2} in the dataset JSON. This stored schema is used by the ADF Copy Activity when it moves data through the pipeline.

When the Copy Activity reads from the SQL Server source, it applies the dataset schema’s column definitions to the incoming data stream. For a column with maxLength: 2, the Copy Activity truncates any string value longer than 2 characters before passing it to the sink. This truncation happens in the ADF runtime, before the data reaches the Azure SQL Database sink. There is no ADF pipeline failure, no warning in the activity run output, and no data type mismatch error — because the Azure SQL Database sink column STAGING_ACCOUNT_STATUS.AccountStatus is defined as VARCHAR(10), which accepts 2-character values without constraint. The truncation is silent from the pipeline’s perspective: the Copy Activity reports success with the correct row count and no schema violation.

// ADF Dataset DS_SQL_LoanAccountStatus — schema as stored in JSON (unchanged since March 2025)
// Source: SQL Server 2022 PCB_LoanCore, dbo.ACCOUNT_STATUS_LOOKUP

{
  "name": "DS_SQL_LoanAccountStatus",
  "properties": {
    "linkedServiceName": { "referenceName": "LS_SQLServer_PCBLoanCore" },
    "type": "SqlServerTable",
    "schema": [
      { "name": "AccountID",     "type": "Int64" },
      { "name": "AccountStatus", "type": "String", "maxLength": 2 },  // ← stored from NCHAR(2) import
      { "name": "StatusDate",    "type": "DateTime" },
      { "name": "ModifiedBy",    "type": "String", "maxLength": 50 }
    ]
  }
}

// SQL Server column as of July 14 2026 (changed from NCHAR(2) to NVARCHAR(10)):
// ALTER TABLE dbo.ACCOUNT_STATUS_LOOKUP
//   ALTER COLUMN AccountStatus NVARCHAR(10) NOT NULL;
//
// New status values effective July 14 2026:
//   Old code → New code         ADF truncation (maxLength:2 applied at runtime)
//   'AC'     → 'ACTIVE'         → 'AC'   (same first 2 chars — accidentally correct)
//   'CL'     → 'CLOSED'         → 'CL'   (same first 2 chars — accidentally correct)
//   'DL'     → 'DELINQUENT'     → 'DE'   (DIFFERENT first 2 chars — breaks Synapse queries on 'DL')
//   'PD'     → 'PAID_OFF'       → 'PA'   (DIFFERENT — breaks Synapse queries on 'PD')
//   'CO'     → 'CHARGE_OFF'     → 'CH'   (DIFFERENT — breaks Synapse queries on 'CO')
//   'BK'     → 'BANKRUPT'       → 'BA'   (DIFFERENT — breaks Synapse queries on 'BK')

The mapping shows that two of the six status codes had the same first two characters in both the old and new formats: 'AC' (Active, from 'ACTIVE') and 'CL' (Closed, from 'CLOSED'). These two status categories continued to work correctly in all Synapse dashboards after the July 14 change. The other four status codes — Delinquent, Paid Off, Charge-Off, and Bankrupt — all had truncated codes that differed from their old counterparts. Any Synapse Analytics query that filtered on AccountStatus = 'DL', 'PD', 'CO', or 'BK' returned zero rows from the staging table, because the staging table now contained only truncated values 'DE', 'PA', 'CH', 'BA' for those account categories.

The risk dashboard’s primary delinquency KPI was built on a Synapse Analytics query filtering WHERE AccountStatus = 'DL'. From July 15, 2026 onward, this query returned zero rows. The dashboard displayed $0 delinquent exposure. Because the Active and Closed loan counts continued to appear correctly (their status codes were accidentally preserved by the truncation), the dashboard looked superficially functional. No ADF alerts fired. No pipeline failures occurred. The error was fully invisible in all ADF monitoring surfaces.

Diagnosis: ADF Monitor, dataset JSON inspection, and SQL Server column definition comparison

-- Diagnosis Step 1: Check STAGING_ACCOUNT_STATUS for distinct AccountStatus values
-- Azure SQL Database PCB_Analytics, executed October 23 2026

SELECT AccountStatus, COUNT(*) AS AccountCount
FROM STAGING_ACCOUNT_STATUS
GROUP BY AccountStatus
ORDER BY AccountCount DESC;

-- Results:
-- AccountStatus  AccountCount
-- AC             31,847     (Active accounts — correctly coded)
-- CL             12,293     (Closed accounts — correctly coded)
-- DE              1,847     (Delinquent accounts — truncated from 'DELINQUENT')
-- PA              2,847     (Paid Off — truncated from 'PAID_OFF')
-- CH                293     (Charge-Off — truncated from 'CHARGE_OFF')
-- BA                 47     (Bankrupt — truncated from 'BANKRUPT')
-- Total: 49,174

-- Observation: Status codes 'DL', 'PD', 'CO', 'BK' are absent.
-- 'DE', 'PA', 'CH', 'BA' are present but not recognized by Synapse dimension table.
-- Synapse DIM_LOAN_STATUS: JOIN on AccountStatus = 'DL', 'PD', 'CO', 'BK' returns 0 rows.

-- Step 2: Check the SQL Server source for current AccountStatus values
-- SQL Server 2022 PCB_LoanCore, via SHIR

SELECT AccountStatus, COUNT(*) AS AccountCount
FROM dbo.ACCOUNT_STATUS_LOOKUP
GROUP BY AccountStatus
ORDER BY AccountCount DESC;

-- Results:
-- ACTIVE        31,847
-- CLOSED        12,293
-- DELINQUENT     1,847  ← 10 chars, truncated to 'DE' by ADF
-- PAID_OFF       2,847  ← 8 chars, truncated to 'PA'
-- CHARGE_OFF       293  ← 10 chars, truncated to 'CH'
-- BANKRUPT          47  ← 8 chars, truncated to 'BA'

-- Mismatch confirmed: SQL Server has full-word codes, STAGING has 2-char truncations.

-- Step 3: Check ADF dataset schema JSON
-- ADF Studio → Datasets → DS_SQL_LoanAccountStatus → Schema tab
-- AccountStatus: Type=String, maxLength=2  ← stale from March 2025 NCHAR(2) import

-- Step 4: Confirm SQL Server column definition change date
-- SQL Server 2022 PCB_LoanCore
SELECT
    c.name                   AS ColumnName,
    t.name                   AS DataType,
    c.max_length             AS MaxLength,
    c.system_type_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE OBJECT_NAME(c.object_id) = 'ACCOUNT_STATUS_LOOKUP'
  AND c.name = 'AccountStatus';
-- Result: DataType=nvarchar, MaxLength=20 (NVARCHAR(10) = 20 bytes in sys.columns)
-- Change date from DDL audit log: July 14, 2026
-- Days since change: 101 days (through October 23, 2026)
-- Daily pipeline runs affected: 101 runs (July 14 → October 23)

Diagnosis was complete in 1.5 hours. The root cause was unambiguous: the ADF Source Dataset schema had a maxLength: 2 constraint on AccountStatus from a 16-month-old schema import, and the SQL Server column had been extended to NVARCHAR(10) without triggering an ADF dataset update.

The fix: updating the ADF dataset schema and refreshing 90 days of Synapse data

Part 1: Update the ADF dataset schema. In ADF Studio, the DS_SQL_LoanAccountStatus dataset was opened. The AccountStatus column’s schema was updated from maxLength: 2 to maxLength: 10 by editing the dataset JSON directly. Alternatively, the “Import Schema” function was used to re-import the current SQL Server schema, which now correctly showed NVARCHAR(10) for AccountStatus. The dataset was saved and published to the ADF service.

// ADF Dataset DS_SQL_LoanAccountStatus — schema AFTER fix
// (Import Schema re-run against current SQL Server 2022 ACCOUNT_STATUS_LOOKUP definition)

{
  "name": "DS_SQL_LoanAccountStatus",
  "properties": {
    "linkedServiceName": { "referenceName": "LS_SQLServer_PCBLoanCore" },
    "type": "SqlServerTable",
    "schema": [
      { "name": "AccountID",     "type": "Int64" },
      { "name": "AccountStatus", "type": "String", "maxLength": 10 },  // ← updated from 2 to 10
      { "name": "StatusDate",    "type": "DateTime" },
      { "name": "ModifiedBy",    "type": "String", "maxLength": 50 }
    ]
  }
}

// Verify fix with manual trigger run (October 23 2026, 11:15 AM)
// ADF Monitor → Pipeline Runs → PCB_AccountStatusSync → Activity Runs → CopyLoanAccountStatus
// Status: Succeeded
// Rows read: 49,174
// Rows written: 49,174

-- Post-fix: STAGING_ACCOUNT_STATUS distinct values
SELECT AccountStatus, COUNT(*) FROM STAGING_ACCOUNT_STATUS GROUP BY AccountStatus;
-- ACTIVE        31,847  ✓
-- CLOSED        12,293  ✓
-- DELINQUENT     1,847  ✓ (was 'DE', now 'DELINQUENT')
-- PAID_OFF       2,847  ✓ (was 'PA', now 'PAID_OFF')
-- CHARGE_OFF       293  ✓ (was 'CH', now 'CHARGE_OFF')
-- BANKRUPT          47  ✓ (was 'BA', now 'BANKRUPT')

Part 2: Update Synapse Analytics DIM_LOAN_STATUS dimension. The Synapse Analytics dimension table DIM_LOAN_STATUS used the old 2-character status codes as its dimension key. Both the dimension table and all downstream Synapse queries filtering on status codes were updated to use the new full-word codes. The dimension table was updated with an INSERT/UPDATE for each of the four changed status codes, and the six Synapse report queries were updated to use the new status code strings.

Part 3: Reload 90 days of historical staging data. The pipeline was triggered manually for each day from July 14 through October 22, 2026 (101 days) to reload STAGING_ACCOUNT_STATUS with correctly-coded status values. Since PCB_AccountStatusSync uses TRUNCATE + INSERT logic (the pipeline truncates the staging table and reloads it in full on each run), the 101-day backfill was completed in sequence using a pipeline trigger with a date parameter. After the reload, the Synapse ETL process was re-run for the same date range to populate the downstream analytical tables with the corrected data.

-- Post-fix Synapse Analytics validation (October 23 2026, 15:42 PM)

-- Risk dashboard primary delinquency KPI:
SELECT COUNT(*) AS DelinquentAccounts, SUM(OutstandingBalance) AS TotalExposure
FROM FACT_LOAN_ACCOUNT
WHERE AccountStatus = 'DELINQUENT';
-- Before fix: 0 accounts, $0 exposure
-- After fix:  1,847 accounts, $127,413,847 exposure  ✓

-- Cross-check against PCB_LoanCore source:
SELECT COUNT(*) FROM dbo.ACCOUNT_STATUS_LOOKUP WHERE AccountStatus = 'DELINQUENT';
-- SQL Server: 1,847  ✓  matches Synapse

-- Paid Off and Charge-Off also restored:
SELECT AccountStatus, COUNT(*) FROM FACT_LOAN_ACCOUNT
WHERE AccountStatus IN ('PAID_OFF','CHARGE_OFF','BANKRUPT')
GROUP BY AccountStatus;
-- PAID_OFF:    2,847  ✓
-- CHARGE_OFF:    293  ✓
-- BANKRUPT:       47  ✓

The risk dashboard displayed 1,847 delinquent accounts with $127,413,847 in total outstanding balance. The Chief Risk Officer confirmed the figure aligned with the bank’s core loan servicing system. Total engagement time: 4.0 hours (1.5h diagnosis: STAGING_ACCOUNT_STATUS value inspection, SQL Server column definition comparison, ADF dataset schema review, DDL audit log date confirmation; 1.0h fix: ADF dataset schema update via Import Schema re-run, Synapse DIM_LOAN_STATUS dimension update, Synapse query code update; 1.0h backfill: 101-day pipeline re-run, Synapse ETL re-run, dashboard validation; 0.5h documentation and retainer SOP update).

Why ADF dataset schema drift is a recurring retainer problem

The Pacific Crest Bancorp incident is a representative example of the most common ADF silent data quality failure: an ADF dataset schema that was imported from the source at build time and has since drifted from the current source definition. ADF datasets are not live — when you import a schema in ADF Studio, you capture a snapshot of the source table’s column definitions at that moment. Any subsequent DDL changes to the source table (column type extensions, column renames, column additions) are not automatically reflected in the ADF dataset. The pipeline continues to run against the stale schema, applying its constraints to the current data.

The maxLength constraint is the most common source of silent truncation: it was correct for a NCHAR(2) column, but became incorrect when the column was extended to NVARCHAR(10). The truncation is applied at the ADF runtime layer, before the data reaches the sink, and no ADF monitoring surface reports it as an error. The only way to detect it is to compare the dataset JSON’s column definitions against the current source table’s schema in SQL Server — a check that ADF does not perform automatically for Copy Activity pipelines.

The two status codes that appeared to work correctly after the change ('AC' and 'CL') masked the problem further: the Active and Closed loan counts were correct in Synapse, making the dashboard look largely functional. Only the delinquency, paid-off, charge-off, and bankruptcy categories were silently zeroed. In a bank risk context, the delinquency figure is the most closely watched — but also the one that executives might be tempted to view as good news (zero delinquencies) without immediately questioning its accuracy.

An ADF developer retainer who audits dataset schemas quarterly — specifically comparing each dataset JSON column’s maxLength, precision, and scale constraints against the current SQL Server column definitions via sys.columns — catches this class of issue before it reaches a risk committee meeting. The PCB database team altered the AccountStatus column on July 14: a retainer developer who reviewed ADF dataset schemas in early August would have found the mismatch within 3 weeks of the DDL change.

How HourTab helps Azure Data Factory developer retainers

The Pacific Crest Bancorp engagement totaled 4.0 hours: diagnosis (1.5h — 0.5h querying STAGING_ACCOUNT_STATUS for distinct AccountStatus values and confirming the presence of truncated codes 'DE', 'PA', 'CH', 'BA' vs. the expected full-word codes; 0.25h querying the SQL Server source dbo.ACCOUNT_STATUS_LOOKUP for current AccountStatus values, confirming full-word format; 0.25h inspecting the ADF dataset DS_SQL_LoanAccountStatus JSON schema in ADF Studio, identifying AccountStatus maxLength:2 from the March 2025 NCHAR(2) schema import; 0.25h retrieving the DDL change audit log entry for July 14 2026 from the DBA team confirming NCHAR(2) → NVARCHAR(10) ALTER COLUMN; 0.25h confirming the truncation mapping: old code vs. new code vs. truncated code for all 6 status categories), fix (1.0h — 0.25h updating DS_SQL_LoanAccountStatus schema via Import Schema re-run in ADF Studio, confirming AccountStatus now shows maxLength:10; 0.25h publishing ADF changes and running a manual trigger test, confirming STAGING_ACCOUNT_STATUS now shows full-word status codes; 0.5h updating Synapse Analytics DIM_LOAN_STATUS dimension table status code values and updating the 6 report queries that filter on status code to use new full-word format), backfill and validation (1.0h — 0.5h triggering PCB_AccountStatusSync pipeline for 101 historical dates July 14 through October 22 using a Foreach trigger loop, monitoring pipeline run status in ADF Monitor; 0.25h re-running Synapse ETL process for the same date range to rebuild FACT_LOAN_ACCOUNT from corrected staging data; 0.25h validating the Synapse risk dashboard delinquency KPI against SQL Server source count, confirming 1,847 accounts and $127,413,847 exposure), and documentation (0.5h — post-incident write-up for retainer file; retainer SOP updated to add quarterly ADF dataset schema audit comparing all dataset JSON column maxLength/precision constraints against current sys.columns for all SQL Server source tables; PCB DBA team added to ADF retainer notification distribution list for DDL changes to tables in ADF pipeline scope).

In a HourTab retainer log, the incident session reads: “ADF dataset schema drift: DS_SQL_LoanAccountStatus AccountStatus maxLength:2 (from March 2025 NCHAR(2) schema import); SQL Server July 14 2026 ALTER COLUMN NCHAR(2) → NVARCHAR(10) for loan status codes; ADF Copy Activity silently truncates all AccountStatus values to 2 chars at runtime; DELINQUENT→DE, PAID_OFF→PA, CHARGE_OFF→CH, BANKRUPT→BA; Synapse DIM_LOAN_STATUS has old codes DL/PD/CO/BK; all 4 risk-category queries return 0 rows; AC and CL accidentally correct (same first 2 chars in both old and new formats); 1,847 delinquent accounts ($127M exposure) invisible in risk dashboard for 101 days; fix: Import Schema re-run in ADF Studio updates maxLength:2 → 10; Synapse dimension and 6 queries updated to full-word codes; 101-day backfill pipeline rerun; Synapse ETL rerun; risk dashboard: 1,847 delinquent accounts, $127M exposure confirmed vs SQL Server; 4.0h.” The PCB Chief Risk Officer sees the root cause (ADF dataset schema not updated after SQL Server DDL change), the scope of impact (101 days, 4 status categories invisible, $127M delinquent exposure hidden), and the fix applied at all three layers (ADF dataset schema, Synapse dimension table, Synapse report queries) — with no ambiguity about how the retainer time was spent.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Azure Data Factory developer retainers

See also: SSIS developer retainer · Informatica developer retainer · MuleSoft developer retainer