I Tried Out Whether Leading Zeros Disappear When Exporting a Snowflake VARCHAR Column to Amazon S3 as CSV with Fivetran Activations

This is Kawabata.

In this article, I export a Snowflake table with a VARCHAR column containing leading zeros to S3 using Fivetran Activations. I check the file contents in three formats: CSV / TSV / Parquet.

https://fivetran.com/docs/activations/destinations/available-destinations/s3

https://fivetran.com/docs/activations/sources/available-sources/snowflake

Overview of S3 output in Fivetran Activations

Fivetran Activations is a reverse ETL feature that sends data warehouse tables or SQL results to SaaS applications or storage. Here, Snowflake is used as the activation source and Amazon S3 as the activation destination.

The S3 destination supports five output formats.

The official documentation explains that values containing the configured delimiter are enclosed in double quotes. I will check how values containing quotes or line breaks are handled in the actual output. You can use variables such as %yyyy %m %d in file names.

The official pages for the Snowflake source and S3 destination that I referred to did not include a column type mapping table. The response from Fivetran Support (November 5, 2026) is as follows.

• Activations does not infer types from the contents of values. It uses the declared type in Snowflake's column metadata
• DATE, TIME, BINARY, VARIANT, OBJECT, ARRAY, and GEOGRAPHY become String. A warning "Unsupported column type will be treated as text" appears
• Parquet output preserves leading zeros in strings

The CSV format itself has no rule for stripping leading zeros. If leading zeros disappear, it happens either when Activations writes the file or when the software that opens the file reads it. In this article, I check the former by viewing the file directly as text.

https://www.rfc-editor.org/rfc/rfc4180

Prerequisites

• A Fivetran account where Activations is available
• A Snowflake account (this article uses an Enterprise trial account, AWS ap-northeast-1)
• A service user for Activations to connect to Snowflake. In this article it is created with TYPE = SERVICE, so it cannot log in with a password and uses key-pair authentication
• An S3 bucket, and an access key (or IAM role) for an IAM user that can write to the bucket

Choose Basic for the Sync Engine. Basic works with read permissions only and does not require creating a CENSUS database on the Snowflake side. If you choose Advanced, write permission on the CENSUS database is required.

Preparation

Snowflake side: test table and service user

I create a table that has a string column with leading zeros alongside a column holding the same values as numbers. I also include strings containing delimiters and quotes, NULL, empty strings, and DATE / TIMESTAMP / BOOLEAN / VARIANT, so I can see how each is represented in CSV in one go.

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS ACTIVATIONS_DEMO;
CREATE WAREHOUSE IF NOT EXISTS ACTIVATIONS_WH
  WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

CREATE OR REPLACE TABLE ACTIVATIONS_DEMO.PUBLIC.LEADING_ZERO_TEST (
  ID               NUMBER(38,0) NOT NULL,
  POSTAL_CODE      VARCHAR(7),      -- 7 digits with leading zeros (string)
  POSTAL_CODE_NUM  NUMBER(7,0),     -- the same value held as a number (for comparison)
  EMP_CODE         VARCHAR(5),      -- code with leading zeros and mixed letters
  NOTE             VARCHAR(100),    -- string containing delimiters and quotes
  AMOUNT           NUMBER(10,2),
  EVENT_DATE       DATE,
  EVENT_TS         TIMESTAMP_NTZ,
  IS_ACTIVE        BOOLEAN,
  ATTRS            VARIANT
);

INSERT INTO ACTIVATIONS_DEMO.PUBLIC.LEADING_ZERO_TEST
SELECT 1, '0600001', 600001,  '00042', 'Sapporo',       1234.50, '2026-11-01'::DATE, '2026-11-01 09:00:00'::TIMESTAMP_NTZ, TRUE,  OBJECT_CONSTRUCT('zip', '0600001')
UNION ALL
SELECT 2, '1000001', 1000001, 'A0042', 'Hello, world',  0.10,    '2026-11-02'::DATE, '2026-11-02 18:30:15'::TIMESTAMP_NTZ, FALSE, OBJECT_CONSTRUCT('zip', '1000001')
UNION ALL
SELECT 3, '0010000', 10000,   '00100', 'say "hi"',      NULL,    NULL,               NULL,                                 NULL,  NULL
UNION ALL
SELECT 4, NULL,      NULL,    '',      '',              100.00,  '2026-11-04'::DATE, '2026-11-04 00:00:00'::TIMESTAMP_NTZ, TRUE,  OBJECT_CONSTRUCT_KEEP_NULL('zip', NULL)
UNION ALL
SELECT 5, '0000000', 0,       '00000', 'all zeros',     0.00,    '2026-11-05'::DATE, '2026-11-05 23:59:59'::TIMESTAMP_NTZ, FALSE, OBJECT_CONSTRUCT();

CREATE ROLE IF NOT EXISTS ACTIVATIONS_ROLE;
GRANT ROLE ACTIVATIONS_ROLE TO ROLE SYSADMIN;
GRANT USAGE, OPERATE, MONITOR ON WAREHOUSE ACTIVATIONS_WH TO ROLE ACTIVATIONS_ROLE;
GRANT USAGE ON DATABASE ACTIVATIONS_DEMO TO ROLE ACTIVATIONS_ROLE;
GRANT USAGE ON SCHEMA ACTIVATIONS_DEMO.PUBLIC TO ROLE ACTIVATIONS_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA ACTIVATIONS_DEMO.PUBLIC TO ROLE ACTIVATIONS_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ACTIVATIONS_DEMO.PUBLIC TO ROLE ACTIVATIONS_ROLE;

CREATE USER IF NOT EXISTS ACTIVATIONS_USER
  TYPE = SERVICE
  DEFAULT_ROLE = ACTIVATIONS_ROLE
  DEFAULT_WAREHOUSE = ACTIVATIONS_WH;
GRANT ROLE ACTIVATIONS_ROLE TO USER ACTIVATIONS_USER;

Generating a key pair and registering the public key

The Snowflake source in Activations recommends key-pair authentication. I create the keys with the openssl included in Git for Windows.

& "C:\Program Files\Git\usr\bin\openssl.exe" genrsa 2048 | & "C:\Program Files\Git\usr\bin\openssl.exe" pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
& "C:\Program Files\Git\usr\bin\openssl.exe" rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Register the body of rsa_key.pub, excluding the BEGIN / END lines, to the service user.

ALTER USER ACTIVATIONS_USER SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
DESC USER ACTIVATIONS_USER;  -- If RSA_PUBLIC_KEY_FP has a value, the key is registered

S3 side: bucket and IAM user

I create one bucket for testing and issue an access key for an IAM user for writing. The required permissions follow the example in Fivetran's documentation.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:AbortMultipartUpload",
        "s3:ListMultipartUploadParts",
        "s3:ListBucketMultipartUploads"
      ],
      "Resource": [
        "arn:aws:s3:::<バケット名>",
        "arn:aws:s3:::<バケット名>/*"
      ]
    }
  ]
}

Trying it out

Registering Snowflake as an activation source

In the Fivetran console, select New Source under Sources in Activations and register Snowflake.

• Snowflake Account Name: the beginning of the connection URL (<organization name>-<account name>)
• Query Execution Warehouse: ACTIVATIONS_WH
• User: ACTIVATIONS_USER
• Role: ACTIVATIONS_ROLE
• Authentication: Key-pair. Paste the contents of rsa_key.p8 including the header and footer
• Sync Engine: Basic

Registering S3 as an activation destination

Select Amazon S3 under Destinations, enter the Access Key ID, Secret Key, Bucket Name, and AWS Region, and save. The connection test checks permissions such as s3:PutObject.

Creating and running a sync with CSV

In Add activation, select Snowflake as the source and S3 as the destination, and create the activation sync.

• Source: ACTIVATIONS_DEMO.PUBLIC.LEADING_ZERO_TEST
• Sync behavior: Update or Create
• Sync key: ID
• Field mappings: Sync All Columns
• File Path: csv/leading_zero_test_%yyyy%m%d.csv
• Advanced Configuration: File format is CSV, with a header row

The field mapping screen shows the type Activations assigned to each column. Confirm that VARCHAR columns are String, NUMBER(38,0) is Integer, and VARIANT is String.

It seems that data types can also be converted manually.

The setup is complete.

Checking the contents of the CSV

Download the file from S3 and display it as-is as text.

aws s3 cp s3://<バケット名>/csv/ C:\work\ --recursive
Get-Content C:\work\leading_zero_test_20261106_full.csv

unique_id,ID,POSTAL_CODE,POSTAL_CODE_NUM,EMP_CODE,NOTE,AMOUNT,EVENT_DATE,EVENT_TS,IS_ACTIVE,ATTRS
1,1,0600001,600001,00042,Sapporo,1234.5,2026-11-01,2026-11-01T09:00:00.000+00:00,true,"{
  ""zip"": ""0600001""
}"
2,2,1000001,1000001,A0042,"Hello, world",0.1,2026-11-02,2026-11-02T18:30:15.000+00:00,false,"{
  ""zip"": ""1000001""
}"
3,3,0010000,10000,00100,"say ""hi""",,,,,
4,4,,,"","",100.0,2026-11-04,2026-11-04T00:00:00.000+00:00,true,"{
  ""zip"": null
}"
5,5,0000000,0,00000,all zeros,0.0,2026-11-05,2026-11-05T23:59:59.000+00:00,false,{}

Here is what can be read from this.

• The leading zeros in POSTAL_CODE and EMP_CODE remained in all rows. The values are 0600001 / 0010000 / 0000000 and 00042 / 00100 / 00000.
• A unique_id column is added at the beginning. It holds the value of ID, which was specified as the sync key
• Values containing commas, quotes, or line breaks are enclosed in double quotes, and inner " characters are doubled to "" ("Hello, world", "say ""hi"""). Values with no special characters, like {}, are not enclosed
• NULL is an empty field, and an empty string is "". The two can be distinguished
• NUMBER(10,2) loses trailing zeros (1234.50 → 1234.5, 0.00 → 0.0)
• DATE is 2026-11-01 and TIMESTAMP_NTZ is 2026-11-01T09:00:00.000+00:00, with three millisecond digits and +00:00 appended. BOOLEAN is true / false
• VARIANT becomes formatted JSON with line breaks and 2-space indentation, and the whole value is enclosed in quotes. One CSV record spans multiple lines

Since the leading zeros remain in the CSV text, if the zeros disappear, it happens in the process on the side that reads the file.

Exporting as TSV

I duplicate the sync, change the File Path to tsv/leading_zero_test_%yyyy%m%d.tsv and the File format to TSV, and run it. The download and verification steps are the same as for CSV.

This is the full text of the TSV output on November 7, 2026. The tab delimiters look the same as spaces, so I have pasted it as-is.

unique_id	ID	POSTAL_CODE	POSTAL_CODE_NUM	EMP_CODE	NOTE	AMOUNT	EVENT_DATE	EVENT_TS	IS_ACTIVE	ATTRS
1	1	0600001	600001	00042	Sapporo	1234.5	2026-11-01	2026-11-01T09:00:00.000+00:00	true	"{
  ""zip"": ""0600001""
}"
2	2	1000001	1000001	A0042	Hello, world	0.1	2026-11-02	2026-11-02T18:30:15.000+00:00	false	"{
  ""zip"": ""1000001""
}"
3	3	0010000	10000	00100	"say ""hi"""					
4	4			""	""	100.0	2026-11-04	2026-11-04T00:00:00.000+00:00	true	"{
  ""zip"": null
}"
5	5	0000000	0	00000	all zeros	0.0	2026-11-05	2026-11-05T23:59:59.000+00:00	false	{}

There is only one difference from CSV. Hello, world is not enclosed. Since the delimiter is a tab, the comma is no longer a special character. say "hi" and the VARIANT JSON are enclosed just as in CSV, because they contain quotes and line breaks.

The leading zeros, the unique_id column, the representation of NULL and empty strings, and the formats of decimals, dates/times, and BOOLEAN were the same as in CSV.

Exporting as Parquet

I change the File Path to parquet/leading_zero_test_%yyyy%m%d.parquet and the File format to Parquet, and run it. Parquet cannot be read as text, so I put it in a Snowflake internal stage to inspect the contents. The output of INFER_SCHEMA shows the data types as interpreted by Snowflake, not the type names used inside Activations. The values are viewed with $1.

aws s3 cp s3://<バケット名>/parquet/ C:\work\ --recursive
snow stage copy "C:\work\leading_zero_test_20261106_full.parquet" "@ACTIVATIONS_DEMO.PUBLIC.OUTPUT_STAGE" -c trial --overwrite

CREATE OR REPLACE FILE FORMAT ACTIVATIONS_DEMO.PUBLIC.FF_PARQUET TYPE = PARQUET;

-- Column types of the output Parquet as interpreted by Snowflake
SELECT COLUMN_NAME, TYPE, NULLABLE
FROM TABLE(INFER_SCHEMA(
  LOCATION    => '@ACTIVATIONS_DEMO.PUBLIC.OUTPUT_STAGE/leading_zero_test_20261106_full.parquet',
  FILE_FORMAT => 'ACTIVATIONS_DEMO.PUBLIC.FF_PARQUET'));

-- Values
SELECT $1
FROM @ACTIVATIONS_DEMO.PUBLIC.OUTPUT_STAGE/leading_zero_test_20261106_full.parquet
  (FILE_FORMAT => 'ACTIVATIONS_DEMO.PUBLIC.FF_PARQUET');

This is the result of reading the Parquet output on November 7, 2026 with INFER_SCHEMA. The column order is the order within the file (Source Order).

These are the $1 values. Snowsight displays VARIANT keys in alphabetical order and omits keys that are NULL. I show the representative rows 1, 3, and 4.

{
  "AMOUNT": 1234.5,
  "ATTRS": "{\n  \"zip\": \"0600001\"\n}",
  "EMP_CODE": "00042",
  "EVENT_DATE": "2026-11-01",
  "EVENT_TS": "2026-11-01 09:00:00.000",
  "ID": 1,
  "IS_ACTIVE": true,
  "NOTE": "Sapporo",
  "POSTAL_CODE": "0600001",
  "POSTAL_CODE_NUM": 600001,
  "unique_id": "1"
}
{
  "EMP_CODE": "00100",
  "ID": 3,
  "NOTE": "say \"hi\"",
  "POSTAL_CODE": "0010000",
  "POSTAL_CODE_NUM": 10000,
  "unique_id": "3"
}
{
  "AMOUNT": 100,
  "ATTRS": "{\n  \"zip\": null\n}",
  "EMP_CODE": "",
  "EVENT_DATE": "2026-11-04",
  "EVENT_TS": "2026-11-04 00:00:00.000",
  "ID": 4,
  "IS_ACTIVE": true,
  "NOTE": "",
  "unique_id": "4"
}

Rows 2 and 5 have the same shape, and POSTAL_CODE was "1000001" and "0000000", and EMP_CODE was "A0042" and "00000".

Here is what can be read from this.

• POSTAL_CODE and EMP_CODE became TEXT columns, and the leading zeros remained in all rows. This matches the support response that "Parquet preserves leading zeros"
• unique_id is TEXT, and its values are strings like "1". In CSV / TSV it could not be distinguished from a number, but in Parquet you can tell it is a string
• Snowflake interpreted AMOUNT as NUMBER(38, 10). The values are displayed as 1234.5 / 100 / 0, with no trailing zeros
• EVENT_DATE is TEXT ("2026-11-01"), and EVENT_TS is TIMESTAMP_NTZ (2026-11-01 09:00:00.000). Unlike CSV / TSV, +00:00 is not appended
• IS_ACTIVE is BOOLEAN and ATTRS is TEXT. The contents of ATTRS are the same formatted JSON with line breaks as in CSV, stored as a string
• NULL is null and an empty string is "", so the two can be distinguished

Summary of results

The types of the Parquet columns are those interpreted by Snowflake's INFER_SCHEMA.

Limitations and notes

• How Activations handles column types is based on the Fivetran Support response as of November 5, 2026. I could not confirm a mapping table on the official Snowflake source / S3 destination pages that I referred to. The behavior may change
• With numeric types, leading zeros and digit counts as a code are not preserved. For columns that need leading zeros, prepare them as strings in the source Snowflake. For values that have already become numbers, converting them to VARCHAR does not restore the original leading zeros
• Switching from the Basic Sync Engine to Advanced is coming soon, and you cannot go back from Advanced to Basic (as stated in the documentation)

Conclusion

I checked how leading zeros are handled when exporting a Snowflake VARCHAR column to S3 with Fivetran Activations, in CSV / TSV / Parquet.
Since the handling of types is not described in the documentation, it is safest to verify it on a real environment before reflecting it in your design.

I hope this article is helpful to someone!