Schema Management

Fivetran automatically builds and updates destination schemas, tables, and columns as new data arrives, so explicitly declaring a schema in your Connector SDK code is optional; connectors work out of the box without it.

We recommend defining primary keys for each table in the schema so Fivetran can de-duplicate and apply updates to specific rows. If you don’t declare a primary key for a table, Fivetran generates an internal unique surrogate primary key. Choose the approach that best fits your connector’s row identity and update behavior, as well as your downstream analytics requirements.

You may also declare a schema to control data types for downstream analytics, or to ensure columns appear in your destination immediately, even if they are sparsely populated. By default, Fivetran creates a column only when it receives a non-null value, which means that columns with sparse data take longer to appear because data must first arrive.

This guide explains what schema management is and how to implement it in your connector.

Understanding schema

In the context of the Connector SDK, a schema is a named grouping of table definitions that you declare directly or retrieve dynamically in your Connector SDK code, along with any tables and columns your connector actually sends at runtime. The table structure includes:

• Schema namespaces: Optional namespaces that group tables, such as SALES and SUPPORT. If you don't provide a schema name, we use the connection name as the schema name.
• Tables: Collection of rows, such as USERS or ORDERS.
• Columns and their data types: Fields inside each row, like id, created_at, or email, along with their data types, such as STRING, INTEGER, or TIMESTAMP.
• Keys: Columns that identify and relate records.

Schema design

Think of schema design as a contract between your connector and Fivetran about how data should land in the destination based on your analytics requirements. In your Connector SDK code, you can optionally define this contract in schema() by declaring schemas for deterministic row identity, and only the column types that require fixed typing or immediate visibility. Implement the contract by structuring your rows to match the declared tables and columns, and sending the rows to Fivetran.

You don’t need to list every column in your schema declaration — Fivetran can infer columns and types from the data you send. Schema inference is especially valuable for sources with dynamic or evolving schemas, and is a great fit for prototypes or connectors syncing unknown or rapidly growing data models.

In your schema design, consider table relationships as well. Foreign keys make joins and downstream lineage easier, but the Connector SDK doesn't support declaring or enforcing them. To simplify downstream joins, include relationship columns, such as user_id, in child tables and document how tables relate.

If your source provides metadata, such as an endpoint listing fields, database metadata, or third-party schema-discovery methods, you can generate your schema dynamically at runtime. This lets your connector handle fully dynamic schemas while still specifying schema names, table names, and primary keys.

Implementing schema

Implement your schema design in the connector.py code:

1. In schema(), optionally declare schemas and their tables, primary keys, and the column data types you need precise control over for downstream analytics.

   If your data extraction uses a metadata endpoint, database introspection, or other schema discovery methods, you can fetch the schema dynamically.

2. In update(), shape records to match your schema and send them to Fivetran using the supported operations. For tables you declared in a schema, pass the matching schema argument in these operations.

Here's an example of the schema() function that sets the primary key for the USERS table and specifies data types for four of its columns:

def schema(configuration: dict):
    return [{
        "table": "users",
        "primary_key": ["id"],
        "columns": {
            "id": "STRING",
            "email": "STRING",
            "created_at": "UTC_DATETIME",
            "custom_attribute": "STRING"
        }
    }]

Here's an example of a matching upsert() operation that sends a row to the same table. Notice that the row includes two columns, city and name, that aren't declared in schema(). Fivetran infers these columns and their data types from the row data and adds them to the destination table automatically.

def update(configuration: dict, state: dict):
    op.upsert(
        table="users",
        data={
            "id": "1",
            "email": "alice@example.com",
            "created_at": "2025-01-10T12:34:56Z",
            "custom_attribute": "pro",
            "city": "Austin",
            "name": "Alice"
        }
    )

Declaring multiple schemas

You can declare multiple schemas so a single connection can sync tables from multiple source schemas. You don't need a connection per schema.

• Declare schemas using the schema key in each table definition returned by schema(). The schema key is optional. If you omit it, we write the table to the destination schema named after the connection. If you provide it, each source schema maps to its own destination schema.

  • You can mix table definitions with and without schema. We write the tables without schema to the destination schema named after the connection, alongside your declared schemas. We reject duplicate combinations of schema and table name.
  • The schema value must be a non-empty string. You can't declare a schema with the same name as the connection, in any letter case. We reject it with an error.
  • Two schemas can each have a table with the same name, with different columns and primary keys.
  • When using Fivetran naming, we prefix each destination schema with the connection name, for example analytics_sales. With source naming, the destination schema matches your declared name exactly. For more information, see naming conventions.
  • With Fivetran naming, schema names that differ only by letter case, such as Sales and sales, normalize to the same destination schema. Tables with the same name merge, and the dashboard shows a duplicate table warning.

  def schema(configuration: dict):
      return [
          {
              "schema": "sales",
              "table": "orders",
              "primary_key": ["id"],
              "columns": {"id": "LONG", "amount": "DECIMAL"}
          },
          {
              "schema": "support",
              "table": "tickets",
              "primary_key": ["id"],
              "columns": {"id": "LONG", "status": "STRING"}
          }
      ]
  

• Pass the schema argument by name to the upsert(), update(), delete(), and truncate() operations, using the same schema you declared for the table. If you omit it, we write the data to the destination schema named after the connection. If you pass a schema you didn't declare, we create the schema and the table and infer the column types:

  def update(configuration: dict, state: dict):
      op.upsert(schema="sales", table="orders", data={"id": 123, "amount": "42.50"})
      op.update(schema="support", table="tickets", modified={"id": 456, "status": "closed"})
      op.checkpoint(state)
  

Defining primary keys in your schema

Primary keys are fields or a combination of fields that uniquely identify each row in a table. To optimize the uploading of your data to your destination, we recommend that you use the schema() method to declare the primary key for each of your tables. However, the use of

If a single field isn't sufficient, for example, when a row is uniquely defined by both a user and a timestamp, you can use a composite primary key that combines multiple fields into one unique identifier.

Here's an example of a composite primary key declaration:

"primary_key": ["user_id", "event_timestamp"]

Defining data types

Fivetran recommends reviewing your source fields for inconsistent data types. Explicitly declaring data types in your Connector SDK schema ensures consistent values reach your destination. If a column can contain inconsistent values, for example, sometimes a string, sometimes a number, and you want to avoid passing that variation downstream, declare its data type in schema().

The Connector SDK supports specific data types, including STRING, UTC_DATETIME, LONG, and DECIMAL. For more information, see the full list of supported types.

The specified types example demonstrates how to declare column data types in schema().

Tips

• Begin by creating a fully working connector with a small schema, such as one to three tables. Building in smaller iterations lets you deliver value early and makes testing your connector easier.
• Avoid using reserved system column names.

Validating schema

The schemas, tables, and columns that appear in your destination are determined by both your schema declaration and how you send data in your update() function. If you send data to a table name not defined in your schema, Fivetran creates a new table in your destination. Similarly, if you make a typo in a schema, table, or column name when sending operations, your connector doesn't break, but the data may end up in an unexpected schema, table, or column.

Because of this, it’s important to test your schema and data loading early and often in development. After writing your initial logic, run a small sync using fivetran debug, then inspect the debug destination (files/warehouse.db) to verify that the schemas, tables, and column names match your expectations. If you declared schema names, tables appear in tester_<schema> with Fivetran naming or <schema> with source naming. For more information, see Local testing. If the results aren't what you intended, review your schema() definition, and the schema, table, and column names used in the operations inside update().

Early validation ensures your data structure is correct and helps catch issues before they reach production.

Handling an evolving schema

Schemas often evolve. For example, if you have a working connector, your source schema may grow — new tables and columns might be added, data types could change, or tables and columns may be removed or renamed. When your connector passes these changes through to Fivetran, we automatically manage these changes based on the destination. The following table describes what Fivetran does in each scenario when we first receive data in a new schema.

You can also design your connector code to respond differently to schema changes — for example, by ignoring new tables or columns, logging a message, or throwing a runtime error to stop the sync. If your analytics requirements call for it, you can also selectively control what data you send to Fivetran.

Schema design examples

You can find practical schema design patterns directly in the official Fivetran Connector SDK repository.

• Static schema: The template connector is the best starting point for beginners. It shows a static schema defined in schema(), with explicit table, primary key, and column declarations. Use this pattern when your source schema is stable and known in advance.
• Parent and child tables: The Data handling patterns example illustrates handling nested or repeating data. The parent table holds the main entity, while child tables represent repeated fields. Child tables reference the parent using the parent's primary key such as company_id. Use this approach for sources with lists or breakdowns. Other examples that demonstrate this pattern include Toast source and Multiple tables with cursors.
• Dynamic schema: Database connectors (for example, Hive or JDBC) discover tables and columns at runtime. Schema is generated dynamically, making this pattern ideal for sources with account-specific or user-defined schemas. Examples to explore include Schema from database, Tracking tables, and Yugabyte.
• Multiple schemas: The Multiple schema example shows a single connector that declares tables in more than one source schema and passes the matching schema argument in its operations. Use this pattern when your source, such as a database, groups tables into schemas.
• Minimal or inferred schema: Some simple API or file connectors rely on schema inference, omitting or minimizing schema(). This is useful for prototypes, but beware of duplicates and unstable columns if the record shape changes. Examples to explore include Unspecified types and SQL Server.

These open-source examples are the fastest way to build intuition for how schema decisions affect real data in the destination. Experiment with small samples, review the resulting tables, and adjust your schema to fit your needs.