Import (Connectors)

Pull data from an external source into your tables — or let a source push into them. Eight connectors ship today — HubSpot, Stripe, Salesforce, ServiceTitan, Commercient, SQL Server, the ODBC Agent, and Inbound Webhooks (see Inbound Webhooks) — behind a common seam (more plug in over time); each imports every object the key can read (discovered from the account, not a fixed list): HubSpot CRM objects (contacts, companies, deals, tickets, products, quotes, engagements, custom objects), Stripe resources (customers, charges, payment_intents, invoices, subscriptions, products, prices, balance_transactions), Salesforce SObjects (Account, Contact, Lead, Opportunity, Case, … plus custom __c objects, with incremental via LastModifiedDate), ServiceTitan resources (customers, locations, contacts, jobs, projects, appointments, job types, invoices, payments, estimates, technicians, business units, employees, memberships — incremental via modifiedOnOrAfter), — for Commercient — the tenant’s own dbo.SF_* tables synced locally over T-SQL (no external API; incremental via each table’s rowversion/TimeStamp, server-side MERGE with no rows round-tripping through the app), — for SQL Server — any SQL Server database this platform can reach on the network (paste a connection string, stored encrypted; tables/views discovered from the catalog with exact column types; needs a single-column PK or an id column; a rowversion column enables cheap incremental), or — for the ODBC Agent — a push source: a small portable agent you install NEXT TO any ODBC-reachable database (Oracle, MySQL, Access, old ERPs) that pushes data outbound over HTTPS, so no inbound access to the source network is needed (see Agent setup below), or — for Inbound Webhooks — a receiver: the platform gives you a URL, the sending system POSTs events to it, and each event becomes rows in a hook_<entity> table (no key to paste, and nothing to Run — see Inbound Webhooks). It’s the Import page under Schema Builder (/import) — distinct from Import Data (file upload).

Flow: (1) Connect — pick the source and paste its key: a HubSpot private-app token (pat-…), a Stripe restricted read-only key (rk_live_…/rk_test_…), or — for Salesforce — a Connected App via JWT Bearer (Consumer Key + username + certificate private key; recommended, no password stored) or the deprecated username-password flow (Consumer Key/Secret + username + password + security token; Salesforce disables it by default and is phasing it out), with the Login URL selecting production (login.salesforce.com) or a sandbox (test.salesforce.com) and the org instance learned automatically at sign-in; or — for ServiceTitan — the OAuth2 API application’s four values (Client ID, Client Secret, App Key, Tenant ID — created in ServiceTitan under Settings → Integrations → API Application Access) plus an environment choice (production or the integration sandbox); tokens are short-lived and refreshed automatically, and tables land as st_<entity> (or st_<middle>_<entity>); or — for Commercient — no key at all (it’s a local source that reads this tenant’s own dbo.SF_* tables; set the PTID to the tenant slug); or — for SQL Server — the source connection string itself (use a read-only login; TrustServerCertificate defaults on for LAN servers); or — for the ODBC Agent — the source’s ODBC connection entered as key/value fields (Driver, Server, Database, UID, PWD, plus driver-specific extras; a paste box fills the fields from a full string), stored encrypted at rest and fetched by the agent over TLS at startup — it never sits in a plaintext file and local overrides are not supported (source and target tables are two halves of the same connection record, so an agent can never be cross-wired to the wrong source); on Edit every setting is visible and alterable except the password, which never leaves the server (blank keeps the stored one).

Agent setup (ODBC connections): the connection card carries a panel to download the agent (portable self-contained exes — 64-bit + 32-bit for legacy ODBC drivers; no .NET install; zip has just the exes + README) and download a ready-made config — using the Agent API key stored (encrypted) on the connection with one click, or a pasted key (Settings → API Keys); it generates appsettings.json with this tenant’s REST endpoint — taken from the URL you’re on — slug, connection id, and PTID; the API key is written encrypted, and on its first run the agent re-protects it with machine-bound Windows encryption (DPAPI), so the config file is useless if copied to another machine.

There’s also Create setup link (requires the stored key): a single-use, expiring URL you send to the customer’s IT person — no Data Lake login needed; it shows a confirm page (so mail scanners can’t consume it) and downloads one bundle (both exes + README + the ready-made config). The link works exactly once, defaults to a 7-day expiry, and unused links can be revoked.

Once an agent has checked in, the panel also shows its telemetry — installed version (against the latest published, highlighted when behind), environment (machine, OS, bitness), IP address, and last seen — and an Update agent button: it flags the connection, the agent sees the flag on its next check-in, downloads the published package, swaps its own exe and exits, and the next scheduled firing runs the new build (the flag clears itself when the agent reports the new version).

Key requirement: the agent’s API key must carry the dedicated DataLake Agent permission (agent.sync, Access Management — off by default) or be an owner key — fetching the server-stored source connection string requires it, and since the string lives only server-side, a plain admin (data.ingest) key is not enough to run the agent.

Best practice: a dedicated agent user granted only agent.sync — its key runs the whole push flow and nothing else. Easiest install path: Create setup link, open it in a browser on the source server, save and unzip the bundle, then double-click DataLakeAgent.exe (or DataLakeAgent-x86.exe on a 32-bit server) and approve the Windows elevation (UAC) prompt — the bundle’s appsettings.json is already configured, so there’s nothing to edit, and double-clicking offers to install the scheduled task for you. That task is a Windows Scheduled Task named Commercient Data Lake \ Data Hub <slug> <ptid> firing about once a minute (one tick: check for a pushed update → claim due work → push → exit; below-normal priority, ~half a core only while actively pushing, zero footprint between firings; uninstall-task + delete the folder removes everything). The agent reports its catalog (the discovered entity list) once when it starts and again only when someone presses Re-discover — there is no longer an hourly catalog report. When any entity is set to Change tracking streaming, the agent promotes itself to a resident process that streams changes continuously (roughly every couple of seconds) instead of exiting between firings. For a headless/scripted install, run DataLakeAgent.exe install-task 5 from an elevated prompt instead. Multiple agents share one server — one folder per connection, each with its own config, task name, and single-instance guard, so agents for different connections run in parallel.

Within a minute of the first run the agent’s tables appear in the entity list (system/catalog schemas — sys, INFORMATION_SCHEMA and friends — are never offered). Entities sync on the key the driver reports — single or composite — and the key columns keep their real names: the target gets the same primary key as the source. When none of the synced columns is named id, the target also gets a computed, uniquely-indexed id column holding the row’s URL-ready API extension (KeyName/value — or K1/v1/K2/v2 for composite keys), the same convention CRM Pro tables use, so every row stays addressable by a single id. Keyless views need a column named id.

Each agent entity carries a Limits & watermark link under its target table (it summarizes state once set, e.g. “Limits: cutoff · wm: ModifiedDate”) opening a popup with: a data cutoff (WHERE) — appended to the source read as AND (…) in the source database’s SQL dialect, e.g. InvoiceDate >= '2024-01-01', so old history is never pulled (rows already synced that later fall outside the filter are only removed by a Full-mirror run) — and a Watermark column picked from the entity’s actual columns (a modified-date or ever-increasing numeric) that enables cheap incremental; blank = incremental re-reads everything each run (still correct — the upsert only writes real changes).

Moving or restoring databases. The per-table sync cursor (change-tracking version or incremental watermark) lives in the Data Lake tenant database alongside the data it tracks, so it travels with a move/detach and a backup/restore: restore a 2-day-old Data Lake backup and each table’s cursor rolls back 2 days too, and the agent automatically re-pulls that window on its next run (re-ingesting is idempotent, so nothing is duplicated). This is authoritative — the agent follows the Data Lake’s cursor even when it moves backward, so a restore can never leave a table silently short. Two safety cases fall back to a full re-baseline (a one-off complete re-pull) instead of resuming: the restore reached further back than the source’s change-tracking retention window, or the source database itself was restored to an older point than the cursor. Change-tracking sources with column tracking enabled also skip re-pulling rows that changed only on columns this entity doesn’t sync (a note records when this suppression is active); the cursor still advances, and a later change to a synced column pulls the row normally.

For agent connections, Run/Resync queue the request and the entity shows a live “sent to agent — waiting for its next poll…” status until the agent claims it (typically within its poll interval), then the normal phase/row-count progress takes over — the panel refreshes itself, including for scheduled and agent-initiated runs; Test shows when the agent last checked in.

Streaming catch-up chips (Change-tracking entities). When an agent entity streams changes, its row shows a small status chip reflecting how far behind the live source it is (the agent pushes this backlog to the server on every ~2-second tick, so it’s near-real-time):

• current (green) — streaming is at the source’s current change-tracking version; nothing pending.
• catching up — N rows behind (amber) — streaming is working through a backlog of N change rows; it will converge on its own.
• drain retrying (amber) — the agent hit a transient error and is retrying by itself; the entity is still syncing and no action is needed (this is self-healing, not a failure).
• parked — full sync needed (amber, terminal) — streaming has stopped for this entity and won’t catch up until you press Resync, usually because the source’s change-tracking history expired while the agent was offline longer than the retention window. This is the one chip that needs you to act.

Re-discover pending badge. On an agent connection, pressing Re-discover shows a re-discovery pending — requested <time ago> badge next to the button until the agent re-reports its catalog on its next poll (usually within a minute), when the badge clears itself. (The agent reports its catalog at startup and on Re-discover only — there’s no periodic re-report — so this badge is how you know the request is in flight.)

Streaming health. For a streaming connection a status line shows the agent’s live streaming state: live (with the time of the last drain), starting…, refused (with the reason), or faulted — retrying in Ns. If the agent predates streaming support it reads “no status reported yet”.

Secrets are encrypted at rest in the tenant DB, never returned, with a Test that confirms it authenticates (or, for Commercient, that the local DB is reachable) and counts readable objects. Two optional free-text fields: a table-name middle term namespaces this connection’s tables (blank → a provider’s connections share tables <prefix>_<entity>, e.g. stripe_customers; set → separate <prefix>_<middle>_<entity>, e.g. hs_148779116_contacts) and is fixed at creation (changing it would orphan the tables already created); and a Provider Tenant ID (PTID) is just an editable label for the source account (metadata, does not affect table names).

Every connection has a Write mode: Master (default — the source wins; local edits to synced columns are overwritten when the source row changes, and the sync safely pauses concurrency-protection triggers inside its merge transaction) or Editor (the connector behaves like any other editor: rows edited in the Data Lake since their last sync keep the Lake’s version, the skipped source changes are reported on the run as amber notes — not failures — and deletes are always soft; per-row sync anchors are seeded when you switch, so the first editor run doesn’t false-conflict). Use Editor for bi-directional flows where both the source system and the Data Lake accept edits.

Conflicts console. Editor-mode conflicts are surfaced in the UI, not only in run notes. Each connection’s Import page has a Conflicts section, and a global Sync Conflicts page (linked in the sidebar with an unresolved-count badge) groups every connection’s conflicts — including file-editor ingests, which group under File uploads. Each conflict carries a kind badge (source change skipped or source delete skipped), a side-by-side Lake (kept) vs Source (skipped) view of the row, and a Resolve button. Resolving a conflict acknowledges the current source value — that same difference won’t re-notify on later runs, and it re-raises only if the source value changes again.

These conflicts are persisted and deduped: a standing conflict re-detected by a no-watermark full re-stage only bumps its seen-counter — run notes and warning emails fire only for new conflicts or ones whose source value changed; conflicts auto-resolve when values converge, are queryable (Lake values vs skipped source values, per row) via GET /api/ddl/import/connections/{id}/conflicts, and acknowledgeable via its /resolve action. Tables with Change Logging enabled also record each skipped source change as a special dlo_op='C' entry in the shadow table (and in Master mode, ordinary change logging captures every overwritten value — enable it for overwrite forensics).

Connections can be edited (display name, PTID, region, write mode, error-notification emails — and for ODBC connections the source’s key/value settings and the stored Agent API key) and the key rotated (paste a new one, or leave blank to keep the current) without losing entity selections or run history — via the card’s Edit button or PUT /api/ddl/import/connections/{id}.

A connection can also carry notification emails (comma/semicolon-separated): those addresses are emailed whenever a run for the connection fails (the error) or completes with data warnings (the run’s notes — e.g. values that couldn’t be parsed as the column’s type and were stored as NULL, or a new source column that was auto-added); clean runs send nothing. Subjects and bodies are stamped with the tenant so operators watching several accounts can tell sources apart.

(2) Select entities — per object set mode (incremental default = only new/changed records since the last run, tracked by a watermark — HubSpot by last-modified date, Commercient by each table’s rowversion/TimeStamp column, Stripe via the Events API so it catches updates too — invoiceitems, like invoices, syncs incrementally through this same events feed, so its updates and deletes are captured (needs the Stripe key’s Events Read permission — without it, incremental gracefully falls back to inserts-only and the connection panel shows an orange warning that updates aren’t being captured (the warning clears automatically once a run succeeds with Events Read granted); events retain ~30 days, so idle-longer connections should run a Full mirror to catch up); the watermark keeps a small safety overlap so late/in-flight records aren’t missed — the overlap is per-connection editable (Watermark overlap, in seconds, on the connection form; blank = the platform default of 300s) for sources with more clock drift / eventual-consistency lag; full = mirror, removing rows no longer present), target table (defaults from prefix + PTID), and an optional property subset (all describable by default — Stripe, having no schema API, seeds its field list from common fields plus a sampled record); Save. The list is auto-discovered, but you can also Add entity by name (an input on the Entities panel) for anything discovery doesn’t surface — an extra Stripe resource (e.g. payouts, refunds) or a HubSpot object/custom object the key can read but can’t enumerate; it probes that the key can actually read that name (clear error if not), then drops it in as an unsaved row to configure + Save like any other.

(3) Run / Run All / Resync — background syncs that stream pages from the source through the ingest pipeline without buffering the whole pull (Run = configured mode; Resync = one-off full re-pull that ignores the incremental watermark — to rebuild a table or catch up after Stripe’s ~30-day event window, after which incremental resumes from the newest record); the table is created if missing and rows are upserted on id (full mode also deletes).

Column types are schema-driven where the provider declares them (HubSpot/Salesforce booleans → BIT, numbers → DECIMAL; strings → NVARCHAR(MAX) so a longer value in a later batch can’t overflow) and inferred from values otherwise; if the source later grows a new property, the column is added to the table automatically (nullable) rather than dropped, with a note on the run. Unparseable cell values are lenient — a non-key value that isn’t valid for its column’s type becomes NULL instead of failing the run, counted per column and shown as an amber warning on the run (and emailed, see notification emails above). Stripe’s hosted_invoice_url/invoice_pdf are deliberately never imported (Stripe rotates the signed token in those URLs on every fetch — they’d make every invoice look changed each run).

Commercient runs entirely server-side — a single T-SQL MERGE from dbo.SF_* into the schema, so no rows leave the database; the target defaults to cm_<object> (the SF_ERP_<erp>_[Clone_] boilerplate stripped, e.g. dbo.SF_ERP_Salesforce_Clone_ArCustomer+ → cm_ArCustomer+), and unusual names (e.g. a trailing +) are handled end-to-end. The source’s TimeStamp rowversion drives incremental but is not copied; the target gets its own auto-managed TimeStamp rowversion (never written), like other tables.

A live Run history shows the hub, rows pulled, insert/update/delete counts, and the full error on a failure.

Traffic graph. Each connection card also shows a Rows synced per day chart (last 30 days, UTC) built from a durable daily rollup, so it survives run-history trimming. Bars show rows synced (with insert/update/delete split and run counts in the hover tooltip) and mark days that had failures/errors (red) or warnings (amber); an empty range reads “No connector activity in the last 30 days.”

While a run is in flight the per-entity status shows live progress — the current phase (queued -> starting -> reading -> staging -> merging; queued means it’s waiting for a run slot, not stuck), records retrieved so far, and elapsed seconds (no percentage: streaming pulls and Stripe’s APIs expose no upstream total, so records-retrieved is the honest signal). The first incremental run is a full pull; later runs fetch only the delta. An entity with no records in the source completes as a clean no-op (0 rows; the table isn’t created until there’s data) — not a failure, so empty/scheduled objects don’t pile up errors.

Concurrent runs are bounded two ways — platform-wide (default 4) and per connection (default 3), so one connection’s big resync queues on itself and can’t starve other connections’ syncs; over a cap, runs show queued and start as slots free. Long pulls hold no database locks (only the final brief merge is transactional), so a slow sync never blocks reads of the target table. Run history is kept bounded automatically (newest ~500 runs per connection, configurable) and a Clear history action purges it on demand (an in-progress run is preserved).

Sync worker slots. At the top of the Import page a small slots strip shows where this tenant’s connector pulls run: an API (this node) row is always present, and — if the tenant has a dedicated sync worker provisioned — a Worker <machine> row (with its version and last heartbeat). Each row shows three pools — sync, verify, and resync — as used/cap, and appends (+N queued) in amber when work is waiting on a pool (sustained queueing is the signal to raise worker threads). A dedicated worker offloads heavy syncs from the API request threads; tenants served centrally show just the API-node row.

(4) Schedule — each entity has a Schedule dropdown (Manual only / every 15 min / hourly / every 6 hours / daily); pick an interval and Save, and a background runner syncs it automatically on that cadence (first scheduled run starts within a couple of minutes), using the same mode/table and appearing in Run history alongside manual runs. Gated by data.ingest (Admin role). Deleting a connection removes its selections + run history but keeps the imported tables. Managed via REST under /api/ddl/import/*; not exposed as an MCP tool.

Re-discover (button on the Entities panel) re-probes the source with the connection’s current credentials and refreshes the entity list — for every connector. Use it when the source gains objects the connection can now see (a new source table, a new HubSpot/Salesforce object). Stripe discovery probes Stripe’s whole listable catalog and keeps only the resources the key can actually read (unreadable ones are hidden, not errored), so elevating the Stripe key’s permissions (e.g. credit-notes, billing/credit_grants) and re-discovering makes those resources appear.

Sync modes — what each writes. Each entity’s Mode is one of: Incremental (default — inserts + updates records changed since the last run, tracked by a watermark; fast/light); Full mirror (re-pulls everything and makes the table an exact copy of the source, removing rows no longer present); or Change tracking (a cheap incremental that also captures deletes by reading SQL Server’s built-in Change Tracking on the source table). Incremental’s delete behavior depends on the provider: Stripe deletes (via *.deleted events — see below); the ODBC Agent deletes only when the entity runs in Change tracking mode; every other provider never deletes in incremental — use Full mirror to purge source-removed rows.

Stripe deletes (incremental). Stripe is the only provider whose Incremental mode removes rows: its Events API emits *.deleted events (e.g. customer.deleted), so an incremental run collects those ids and, after merging inserts/updates, removes the matching rows by key — a real DELETE, or a soft dl_deleted = 1 when the target table carries that column (e.g. an Editor-mode connection). This needs the Stripe key’s Events Read permission (the same one incremental updates rely on). A Full mirror run reconciles removals via its whole-table diff and needs no delete events.

Change tracking mode (SQL Server agent sources). Offered — in the Sync-modes reference and the per-entity Mode dropdown — only on an ODBC Agent connection whose stored source string is a native SQL Server connection string (no Driver=/Dsn= key); it is hidden for every other connector (Stripe, HubSpot, Salesforce, SQL Server pull, Commercient) and for agent connections whose source string is a classic ODBC/DSN string. CT mode preconditions error only for CT-mode entities (an entity’s Incremental or Full-mirror siblings on the same connection are unaffected) with an actionable message when: the source isn’t a SQL Server agent source; the source string is ODBC/DSN rather than native (“Change tracking mode requires a Microsoft SQL Server source (native provider)… Store a native SQL Server connection string (no Driver=/Dsn= key)…”); or Change Tracking isn’t enabled on the customer’s source. The last is the customer’s own action — the run reports the exact T-SQL: at the DB level ALTER DATABASE … SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON) and per table ALTER TABLE … ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) (the table must have a primary key). If the agent is offline longer than the retention window its saved cursor ages out, so the next run auto-re-baselines with a full pull (reconciling deletes missed during the gap) and resumes cheap CT from there.

Row-count checks (destination parity). Every completed run records the destination table’s live row count (excluding soft-deleted rows) — shown as a Dest rows column in Run history. Full mirror runs also run a parity check comparing source vs destination counts with the run’s read-cutoff and entity filter applied to both sides (apples-to-apples); a mismatch doesn’t fail the run but surfaces as a warning note (“source has N rows but destination has M … this mirror may be incomplete”), a ⚠ mismatch badge in Run history, and an email if the connection has Email-on-warning on. HTTP sources (Stripe/HubSpot/Salesforce) have no cheap SELECT COUNT(*), so their runs skip the comparison and note “source row count is unavailable” (the destination count is still recorded); the parity check is most useful on SQL Server / ODBC-agent full-mirror runs.

Verify (integrity check). Each connector-pull entity has a Verify button that runs a read-only full-snapshot comparison of the Lake copy against the live source — it answers “is the Lake a perfect mirror of the source right now?” without writing anything. The report buckets the differences: matched, missing in Lake (in the source, not the target), extra in Lake (in the target, not the source), and value mismatches (same key, different values). Differences that are expected are counted separately so they don’t read as corruption: soft-delete tombstones (rows the mirror removed) and, in Editor mode, local edits that intentionally kept the Lake’s value. Verify and sync can’t run at the same time on one entity, and the result lands in Run history as a verify run. Verify is available on connector-pull connections; agent-push (ODBC) connections aren’t supported yet, and it doesn’t exist at all for a push source such as an inbound webhook (nothing can be asked for a full snapshot of a feed that only exists because a sender chose to send it). For Commercient connections the whole comparison now runs inside SQL Server — the source dbo.SF_* table is diffed against the Lake table directly, with no snapshot table staged and no rows travelling through the application — so a verify of 65,000 wide rows finishes in seconds rather than minutes. REST: POST /api/ddl/import/connections/{id}/entities/{entity}/verify; over the admin control plane it’s the verify_entity_integrity tool.

What a connection supports (capabilities). Each connection reports its own capability flags — push-based, uses the agent, needs a credential, source can be enumerated, mirror, verify, change tracking — and the Import page reads those rather than guessing from the provider name. That’s why the buttons differ per card: a push-based connection hides Run, Resync and Run All (with the hint that rows arrive when the sender posts), the Verify button and Full mirror mode only appear where the source can actually be enumerated, and the agent panel and ODBC settings grid only appear for agent connections.