Using Claude in VS Code to Build a Custom Connector With Fivetran’s Connector SDK

Create a dedicated project folder and confirm local tools are ready.

Project layout example:

FDA_drug_vscode_claude/
  agents.md               # system instructions pasted from the public repo
  connector.py            # added later by Claude
  configuration.json      # added later by Claude
  requirements.txt        # added later by Claude
  README.md               # added later by Claude
  notes.txt               # your API context: auth, endpoints, examples

For more details, see our Project Structure best practices documentation.

Suggested checks before you start prompting:

• Run fivetran --version in the VS Code terminal to confirm Connector SDK is on the path.
• Run python --version to confirm your interpreter is available to the terminal inside VS Code.
• If your source needs credentials, export an environment variable for the API key using your shell’s syntax. See our environment variable documentation for more details.

1. Set up the instruction system Copilot will use to guide Claude.

   i. Click the Copilot Chat gear icon, choose Instructions, and create a new instruction file named agents.md.

   ii. Paste in the content of ai_and_connector_sdk/agents.md from the Fivetran public repository.

   iii. Add the template connector file template_example_connector/connector.py as an additional instruction reference.

2. Create a project folder and add a context file named notes.txt that includes:

   • Authentication notes and whether an API key is optional
   • API overview, endpoint names, base URLs, and any query parameters
   • Example requests and sample response snippets from the OpenFDA Drug API

These files provide the AI model with concrete API context and a reference implementation for Connector SDK conventions.

Author a three-part prompt in Copilot Chat using Edit with Copilot.

1. Source and intent:
   • Target the OpenFDA Drug API endpoints to replicate.
   • Point the model to #file:notes.txt for concrete query examples and response shapes.
2. Functional behavior and constraints:
   • Upsert only the first 10 results per endpoint by default to conserve quota during testing.
   • Exit gracefully after a short run and include clear comments and strategic logging.
   • Default to no API key required, while supporting an optional key via the configuration file.
   • Ensure all configuration values are strings and define only primary keys so Fivetran can infer the rest.
   • Allow the model to test queries first to validate endpoint behavior before writing code.
3. Instruction references and output location:
   • Follow best practices from #file:agents.md.
   • Use the template structure from the added connector.py reference.
   • Write generated files into the current project folder.

Optional prompt you can adapt:

Create a Fivetran Connector SDK solution for the OpenFDA Drug API. Use the endpoint details and examples in `#file:notes.txt`. Default to no API key, but allow an optional key in `configuration.json`. Upsert the first 10 records per endpoint, then exit gracefully with logging and comments. Define only primary keys; all config values should be strings. Follow the guidance in `#file:agents.md` and mirror the structure of the template connector. Write files into this folder: `connector.py`, `configuration.json`, `requirements.txt`, `README.md`.

1. Make sure the Agent is enabled and Claude Sonnet 3.7 model is selected.
2. Ask Copilot to create the full set of SDK files. Claude typically proposes a plan and then creates files in sequence. Files to expect:
   • connector.py containing discover and sync logic, request helpers, logging
   • requirements.txt listing fivetran_sdk and HTTP dependencies
   • configuration.json with string-typed config values, plus optional API key
   • README.md summarizing what the connector does and how to run it
3. Accept the proposed edits in VS Code and confirm the files appear in the project folder. If any file is missing, ask Copilot to add it explicitly.

From the integrated terminal in VS Code, ask Copilot to execute:

fivetran debug connector.py --config configuration.json

See the Troubleshooting section for common errors encountered during debug and how to resolve them. Rerun until the summary shows a small test batch of upserts, a checkpoint, and any required schema change events.

Typical first-run issues and how Copilot addresses them:

• Date range errors causing empty or 404 responses. Copilot adjusts the lookback logic in code or config and retries.
• Field mismatches or unexpected shapes. Copilot updates the schema mapping and record emit logic.
• Pagination quirks. Copilot inspects response metadata and amends the loop conditions.

Rerun until the summary shows a small test batch of upserts, a checkpoint, and any required schema change events.

Ask Copilot to open and review the warehouse.db produced by the debug run. Have it show representative rows from each replicated table, summarize row counts and key fields, and call out any endpoints that are not yet represented.

If Copilot identifies gaps, use its observations to drive the next editing pass in connector.py and configuration.json.

If a specific endpoint such as drug shortages fails to replicate:

• Provide example requests and field notes for that endpoint from the API documentation.
• Ask Copilot to test a request to confirm the correct query shape, then update the connector code accordingly.
• Claude will run the debug command again and confirm that the table for that endpoint now appears and contains rows.

Repeat this loop until all endpoints of interest are covered and validated locally.

1. Ask Copilot to verify the connector's readiness by querying the local warehouse.db for each replicated table. Request the following:
   • A list of all tables created by the debug run
   • Row counts per table and identification of primary keys
   • Sample rows per table to confirm expected fields
2. If Copilot encounters an error while previewing a table (for example, selecting a non-existent column in a table), have it correct the query and, if needed, update connector.py logic for that endpoint. Copilot re-runs fivetran debug when fixes are applied, then re-checks the tables until all targeted endpoints are present and populated.

1. Request Copilot to deploy the connector to Fivetran using the following command:

   fivetran deploy connector.py
   

2. Provide the prompts in the terminal when asked:
   • Destination name such as csg_auto_test
   • Connection name such as FDA drug video
   • API key (stored as an environment variable)
   • Configuration path, which is the configuration.json in your project root
3. When deployment succeeds, follow the returned link to the new connector in the Fivetran dashboard.
4. Open the new connector page and start the initial sync.

• Project contains the four generated files plus notes.txt and agents.md
• Debug run completes with a small test batch and emits consistent schema
• DuckDB review shows tables and keys populated for each endpoint
• Deployment to the intended destination succeeds and the connection link opens
• Initial sync completes without errors in the Fivetran dashboard

• You configured Copilot in VS Code with instruction files and API context
• Created a structured three‑part prompt aligned to the OpenFDA Drug API.
• Generated the connector files: connector.py, configuration.json, requirements.txt, README.md.
• Ran fivetran debug, resolve errors, and iterated to a clean test run.
• Inspected the local warehouse.db with DuckDB and verified tables, keys, and row counts.
• Closed gaps for any failing endpoints by enriching notes.txt and refining connector.py.
• Prepared the configuration for deployment and validated string‑typed values.
• Deployed the connector with fivetran deploy and opened it in the Fivetran dashboard.
• Started the initial sync and confirmed the schema and data in the destination.

The issues below are drawn from the VS Code flow in the video transcript. Use them to keep the main steps clean while still capturing practical fixes.

Empty results or 404 errors after first debug run

• Symptom: responses are empty or the API returns 404.
• Likely cause: date range or query filters are too strict for the selected endpoint.
• Fix: loosen or remove the initial lookback window in configuration.json, or adjust query parameters in connector.py. Keep the test limit at 10.
• Verify: rerun fivetran debug and confirm rows are emitted.

Field mismatches or schema errors

• Symptom: KeyError, missing field, or schema change events that repeat.
• Likely cause: response shape differs by endpoint or across pages.
• Fix: use safe accessors (for example, dict.get), flatten nested objects consistently, and declare only primary keys. Bump the local schema version if needed so changes are applied cleanly.
• Verify: debug shows stable upserts without repeated schema changes.

Pagination only retrieves one page or duplicates rows

• Symptom: only a small subset of records appears, or duplicates are emitted.
• Likely cause: missing next-page handling, incorrect offset/limit logic, or not honoring continuation tokens.
• Fix: inspect response metadata for next links or cursors; update the loop to request subsequent pages; deduplicate by primary key when emitting.
• Verify: upsert counts increase across pages and duplicates disappear.

Endpoint table missing

• Symptom: an expected table is not created in warehouse.db.
• Likely cause: endpoint not included in configuration.json or not wired in connector.py.
• Fix: add the endpoint to config and map it to a handler in connector.py; include it in the discover schema.
• Verify: rerun debug and confirm the table appears with rows.

Wrong working directory during CLI commands

• Symptom: CLI cannot find connector.py or configuration.json.
• Likely cause: running commands from a parent folder.
• Fix: change directory to the project root before running CLI.
• Verify: CLI starts and references the correct files.

Missing or unset API key when needed

• Symptom: HTTP 401 or 403.
• Likely cause: API requires a key, but the environment variable is unset.
• Fix: export the key as an environment variable and read it in configuration.json. See the environment variable guide.
• Verify: authenticated requests succeed and records are emitted.

Rate limiting or HTTP 429

• Symptom: intermittent failures with 429 status.
• Likely cause: too many requests during testing.
• Fix: keep the test limit at 10, add small sleeps/backoff, and respect server rate limits.
• Verify: stable runs without 429 after adjustments.

warehouse.db not created

• Symptom: no local database after debug.
• Likely cause: debug run failed early or the output path is incorrect.
• Fix: fix the failing error first; ensure the default output location is used or configured correctly.
• Verify: file appears in the project and opens in DuckDB.

DuckDB cannot open the file

• Symptom: error opening warehouse.db.
• Likely cause: DuckDB not installed or wrong path.
• Fix: install DuckDB and provide the correct path to the db file; re-run debug if necessary.
• Verify: tables list successfully and rows are queryable.

Deployment prompts fail or are incomplete

• Symptom: deploy exits asking for destination, connection name, or config path.
• Likely cause: missing inputs.
• Fix: provide destination name, a clear connection name, and the configuration.json path.
• Verify: CLI returns a connector URL and the page opens in Fivetran.

Agent edits the wrong folder

• Symptom: files are created or modified outside the project.
• Likely cause: workspace scope confusion.
• Fix: pin the project folder, point the agent to local files with #file: references, and confirm paths before accepting edits.
• Verify: expected files update in the intended directory.