How to Build a Connector SDK Connector with Visual Studio Code and GitHub Copilot

The tutorial demonstrates how to build a Fivetran Connector SDK custom connector using Copilot, an AI pair programmer. The demo showcases the end-to-end process of creating, testing, and deploying a connector for the FDA Tobacco API.

For prerequisites and best practices, see our Building a Custom Connector With VS Code and AI documentation.

The tutorial is based on our FDA Tobacco Problem API Connector example connector.

Prerequisites

Connector SDK prerequisites
Python virtual environment set up (see How to Create a Python Virtual Environment for detailed instructions)
Connector SDK installed (see How to Install Fivetran Connector SDK for detailed instructions)
Docker & Docker Compose
VS Code with GitHub Copilot enabled

You can simplify AI coding agent setup with our Connector SDK AI plugins. They integrate with Claude Code, Codex CLI, GitHub Copilot CLI, and Gemini CLI, and provide skills for building, testing, deploying, and evaluating connectors.

See the Tutorials for Fivetran Connector SDK to learn how to build custom connectors with Fivetran's Connector SDK.

Instructions

Prepare AI agent

Prepare the AI assistant using a special agents.md file. This file defines the goals, formatting rules, and behavior for Copilot. It helps ensure the AI follows consistent practices during code generation.

See our agents.md file example to learn more.

Create project structure in VS Code

Create a new folder for the project along with the following files:

This folder structure should ensure compatibility with Connector SDK. See our Connector SDK getting started guide and Project Structure documentation for more details.

See our How to Create a Project Folder documentation if you need in-depth instructions.

Connectors that use external dependencies need a requirements.txt file, which lists them. Our example does not use external dependencies.

Feed API context with notes and fields

To help the AI understand the target data source (for the purposes of this example, FDA Tobacco APIs), compile authentication info, endpoint details, and sample payloads into a notes.txt file.

Additionally, create a fields.yaml file for schema clarity.

Write AI prompt for connector generation

To generate the connector files, we provide a structured three-part prompt to the AI assistant, using project-local context files.

The prompt parts are outlined in the following sections:

Provide source context
Define functional requirements
Define execution constraints

Click to see the prompt example

# Part 1: Source Context

I need a Fivetran Connector SDK solution for https://api.fda.gov/tobacco/problem.json.
I have some notes and example queries in #file:notes.txt and the fields documented in #file:fields.yaml.

# Part 2: Functional Requirements

Have it dynamically create tables based on the endpoints available. Flatten the dictionaries and upsert the key:value pairs as the columns for the tables.
Only define the Primary Key for the schema objects, let Fivetran infer the rest.
Process the first 10 responses from each endpoint and then exit gracefully, we do not have an API key and do not want to exceed the limits.

# Part 3: Execution Constraints

Create a Fivetran Connector SDK solution that follows Fivetran best practice outlined at https://fivetran.com/docs/connector-sdk/best-practices.md.
I have the `connector.py`, `configuration.json`, and `README.md`files prepared in #file:FDA_tobacco.

Provide source context

The first prompt section provides the AI assistant with source context, including API documentation and schema details. This helps the AI understand the data source and how to structure the connector. This prompt part references real API material from:

notes.txt: authentication, endpoints, sample queries
fields.yaml: field structure pulled from API schema

This content gives the AI model deep, domain-specific context for code generation.

Define functional requirements

The second prompt part specifies the functional requirements:

Dynamically create tables based on the endpoints available
Flatten nested dictionaries
Use key-value pairs as table columns
Upsert behavior for records
Only define primary keys where necessary; allow Fivetran to infer others
Limit queries to the first 10 results per endpoint to avoid API overuse (no API key provided).

Define execution constraints

The third prompt part instructs the AI assistant to write code directly into the defined files in the FDA_tobacco project folder:

connector.py
configuration.json
README.md

In this part, we also require adherence to Fivetran SDK best practices.

Verify the generated files

The AI assistant must generate and populate all required files live in VS Code:

connector.py
configuration.json
README.md

This marks the completion of the prompt execution phase, after which you can proceed with the testing and validation stage using fivetran debug.

Test connector with `fivetran debug` command

With the files generated, test the connector using the fivetran debug command. The tool validates the configuration and performs a limited sync, returning an upsert summary to verify correctness.

To manually troubleshoot errors, refer to Fivetran’s SDK Troubleshooting Guide.

Inspect and validate the data output

Use DuckDB to inspect the synced data and verify that the expected schema and values were loaded. See our Working with DuckDB documentation to learn more.

Deploy custom connector to Fivetran

Once validated, deploy the connector using the fivetran deploy command. The CLI should return the connection ID and confirm the success of the deployment.

Once deployed, start syncing data with the connection.

Summary

In this tutorial, you built a custom Fivetran Connector SDK connector using a contextually-driven prompt seeded with three inputs:

A system instruction file (agents.md) available in the Fivetran GitHub repo
A custom notes.txt file containing connector-specific context
Online API documentation providing schema and field details

With this context in place, you prompted the AI agent to generate the custom connector, ran test commands directly in the chat, and allowed the agent to self-resolve errors. The result was a working connector that successfully queried data and synced it to Fivetran.

This workflow is not limited to VS Code and GitHub Copilot — the same approach can be applied using any IDE or AI model.