Files

Primary keys

For Magic Folder Mode, Fivetran adds a single column as a primary key for each table.

Sync limitations

Magic Folder Mode has the following sync limitations:

• We do not sync files inside nested folders in your configured folder.

• We do not sync empty folders or unsupported file types.

• We don't sync empty worksheets. We don't sync an Excel worksheet if the entire first row of the worksheet is empty. We don't sync a column if its first row is empty.

• We don't sync worksheets with pivot tables.

You can find more information on the individual connector pages.

Schema view

The Schema tab of a connector in Magic Folder Mode does not have the Collapse all or Expand all button enabled on your connector dashboard.

If you haven't uploaded any supported files, the Schema tab of a connector in Magic Folder Mode will be empty.

Primary keys

Fivetran adds the below three columns for all tables. We add the _file and _line columns as composite primary keys for both append_file and upsert_file configurations. However, we include the _modified column as a composite primary key only when you set the Modified File Merge option to append_file.

NOTE: For the Email connector, the _modified column contains the timestamp when we receive an email containing a file.

Naming

You can assign custom schema and table names while building your connector. Column names are generated based on the uploaded files. For a CSV, there will be one column for each entry in the first line. For a JSON file, there will be one column for each top-level field in the JSON object.

If you change the name of a file in your bucket, all data in that file will be re-imported, which may result in duplicate values within that table.

Excluding source data

Inclusion and exclusion of source data in file storage connections are done by specifying the folder path. The connection will sync all files under a certain folder path, even if they are themselves in nested subfolders of the specified folder. Excluding fields from source files is not supported.

NOTE: If we find the _fivetran_synced, _fivetran_id, and _fivetran_active columns in the source files, we ignore these columns during the sync because we use the values of the corresponding system columns that Fivetran generates in your destination.

Updating data

After the initial load of historical data, Fivetran only pulls incremental updates of any new or modified files from the source, adding an extra column to your tables called _fivetran_synced (TIMESTAMP) that indicates the start time of the job that synced this row.

We do not recommend manually changing the contents of your files while using the upsert_file primary key configuration. Doing so will result in the following behaviors:

• As the connection has file-level granularity, if you modify a file, we update the table in the destination to match the contents of the file.
• Adding rows to a CSV that has already been synced will change the last modified date, which will then trigger a re-sync of the contents of that file.
• As we use the file name and line numbers as primary keys, whatever you change in your file will be represented in the file in the destination, but you may lose the previous version of the file that corresponded to the old record.

Deleted data

We handle the data deleted at the source based on the last modified dates of the files, and treat the deleted files, columns, and rows differently.

• Files: If you delete a file in your source, it no longer exists, so it no longer has a last modified date and the deletion is not reflected in your destination. Therefore, the data from the deleted file still remains in your destination.

• Columns: If you delete a column in your source, it results in a change in the last modified date. The value of the deleted columns are synced as null in the destination.

• Rows: If you delete a row in your source, it results in a change in the last modified date. If you use upsert_file as the primary key during configuration, then the rows are deleted from your destination.

Configuration options

Using the connector configuration options, you can select subsets of your folders, certain types of files, and more to sync only the files you need in your destination. Setting up multiple connectors targeted at the same source, but with different options, can allow you to slice and dice a source any way you'd like.

Folder path (optional)

This folder path is used to specify a portion of the bucket in which you'd like Fivetran to look for files. Any files under the specified folder and all of its nested subfolders will be examined for files we can upload. If no prefix is supplied, we'll look through the entire bucket for files to sync.

File pattern (optional)

The file pattern is a regular expression that we use to decide whether to sync certain files. It applies to everything under the prefix. For instance, suppose under the prefix logs you had three folders: 2017, 2016, and errors. Using the pattern \d\d\d\d/.*, you could exclude all the files in the errors folder, because \d\d\d\d only applies to the folders whose name consists of four consecutive digits, and because .* after / applies to any files in these folders. If you're not sure what regular expression to use, you can leave this field blank, and we'll sync everything under the prefix.

If you're feeling particularly bold, you can learn to write your own regex using Tutorialspoint's Java - Regular Expressions documentation and test it out using the Rubular tool.

TIP: To sync files having file names with a specific string irrespective of the case, use the ?i modifier in your regex. For example, if you want to match all the files with a 'customer' string in the file name, specify the file pattern as (?i).*customer.*.

File type

The file type is used to let Fivetran know that even files without a file extension ought to be parsed as this file type. For example, if you have an automated CSV output system that saves files without a .csv extension, you can specify the CSV type and we will sync them correctly as CSVs. Selecting "infer" will let Fivetran infer from a file's extension (.csv, .tsv, .json, .avro, or .log) what to sync. If you do choose a file type, every file we examine will be interpreted as the file type you select, so make sure everything Fivetran syncs has the same file type!
Fivetran also supports syncing xml files to the destination. Fivetran loads your XML data into the _data column without flattening it.

JSON Delivery Mode (optional)

This option is available for JSON or JSONL file types. Fivetran uses this option to choose how to handle your JSON data. Fivetran has two values for this feature, Packed and Unpacked. If you select Packed, Fivetran loads all the JSON data into the _data column without flattening it, or if you select Unpacked, Fivetran flattens one level of columns and infers their data types.

Compression (optional)

The compression format is used to let Fivetran know that even files without a compression extension should be decompressed using the selected compression format. For example, if you have an automated CSV output system that GZIPs files to save space, but saves them without a .gzip extension, you can set this field to GZIP. The connection will then decompress every file that it examines using GZIP. If all of your compressed files are correctly marked with a matching compression extension (.bz2, .gz, .gzip, .tar, or .zip), you can select "infer".

Error handling (optional)

The error handling option enables you to choose how to handle errors in your files. If you select skip, we ignore any improperly formatted data within a file, allowing you to sync only valid data. However, if you select fail, we fail the sync with an error on finding any improperly formatted data. Regardless of the option you choose, you will receive a notification on your dashboard if we encounter any errors.

Advanced configuration options

You can use the advanced connector configuration options for highly specific cases.