Data Factory Copy Activity is failing for Hubspot data source

Hi @Peter Blatchley
Welcome to the Microsoft Q&A and thank you for posting your questions here.
As pr over observation root cause is still source-side type inference: HubSpot marks amount numeric, but some rows send amount as an empty/dirty string, so the source driver attempts “” → numeric and fails before the write phase. Any non-numeric string (empty/whitespace, “NA”, currency symbols, thousands of separators, etc.) can trigger the cast error on the source step.

What you can do (pick one)

1. Keep the run green and capture rejects Copy activity → Settings → Fault tolerance → enable “Skip incompatible rows” and log error rows to ADLS (errorDataSettings). You’ll get clean data in the sink plus a reject file to triage the bad amount values.
2. Clean the values before casting (recommended) Add a Mapping Data Flow (or a small post-copy cleaning step) and normalize amount to a safe decimal:

amount_clean =
iif(
  isNull(amount) || trim(toString(amount))=='' ||
  toString(amount) in ('NA','N/A','NaN','NULL','null','—','-'),
  null(),
  toDecimal(
    replace(
      replaceRegex(toString(amount), '[^0-9\\-\\.,]', ''),  -- drop currency/symbols
      ',', ''                                              -- drop thousands separator
    )
  )
)

Sink amount_clean as DECIMAL with explicit precision/scale.

3. “Stringify then cast” ingestion (driver-agnostic) If you need to avoid the HubSpot driver’s typing altogether, pull the same endpoint via the generic REST connector (JSON) and land raw strings to staging. In a second step (data flow or SQL), cast to DECIMAL using the expression above.

Extra checks & corner cases to watch

• Values that are only spaces/tabs, “0 ” with trailing space, or negatives in parentheses like “(123.45)”.
  • If using regex, strip parentheses for negatives first: replaceRegex(amount,'[()]','') before cast.
• Locale commas “1.234,56” vs “1,234.56” — normalize before toDecimal.
  • Confirm your regex pattern matches your locale rules; [^\d\-\.,] works for en-US.
• Mixed shapes across pages (missing property vs empty string).
• Oversized numeric exceeding your DECIMAL precision/scale (treat as NULL or widen the target).

If you’re on the older connector, consider upgrading; newer versions map “number” to Decimal and change a few options.

If you’re still blocked, please share required details in private message:

• One redacted failing payload (showing amount exactly as received),
• Run ID, connector version, a screenshot of Copy → Source settings and more
• Desired DECIMAL precision/scale in the sink. I’ll suggest the exact expression/precision based on that. Reference link:
  • https://free.blessedness.top/en-us/azure/data-factory/connector-hubspot?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider&tabs=data-factory#dataset-properties
  • https://free.blessedness.top/en-us/azure/data-factory/connector-hubspot?source=recommendations&tabs=data-factory