Using Claude in VS Code to Build a Custom Connector with the Fivetran 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 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 all_things_ai/ai_agents/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.

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.

Before publishing, confirm the following:

• 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

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.