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 connection in Magic Folder Mode does not have the Collapse all or Expand all button enabled on your connection dashboard.

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

System columns

Fivetran adds the following columns for all tables:

Primary key used for file process and load

The following configuration options enable Fivetran know how you'd like to update the files in your destination.

NOTE: You can't modify your primary key once the initial sync is successful.

Naming

You can assign custom schema and table names while building your connection. 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 remains in your destination, and we do not update the _fivetran_synced column for that file. The column will continue to show the last time we successfully synced the file before it was deleted.

• 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

While configuring your connection, 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 connections 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. Every file we find in the source will be interpreted as the file type you select, so ensure that 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. Fivetran supports the infer file type option only for the .csv, .tsv, .json, .avro, .parquet, or .log files. This option does not support syncing Excel or XML file types.

Unstructured file replication Private Preview

Files

Files are stored in an object storage linked with a destination. To maintain consistency, the folder structure from the source, including the parent folder, is replicated in the object storage without any modification. To learn more about the exact storage location of unstructured files, see our Unstructured File Replication documentation.

IMPORTANT: We do not support renaming folders and moving files between folders.

Metadata

The metadata for each file is maintained in the schema and table of a particular destination. The following columns capture the metadata for each file:

• _fivetran_file_path: Stores relative source path and reference to the respective file’s location in the destination.
• url: Stores source URL linking to the file’s original location.

For more information, see our ERD.

Changes in the source

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 configuration for highly specific cases.