S3 - Census Docs

🏃‍♀️ Getting started
This guide shows you how to use Census to connect your S3 account to your data warehouse. This type of connection can sync data between the warehouse and your S3 bucket by mirroring data into a CSV file.
Prerequisites
Before you begin, you'll need the following:
Census account: If you don't have this already, start with a free trial.
S3 account: You'll need an S3 bucket that's ready for use, along with credentials for an AWS user that can write to that bucket. See AWS documentation for setting up an S3 bucket. The specific permissions Census may need are covered later in Required permissions.
Have the proper credentials to access to your data source. See our docs for each supported data source for further information:
​Azure Synapse​
​Databricks​
​Elasticsearch​
​Google BigQuery​
​Google Sheets​
​MySQL​
​Postgres​
​Redshift​
​Rockset​
​Snowflake​
​SQL Server​
Step 1: Connect S3
1.Log into Census and navigate to Connections.
2.Click Add Service.
3.Select S3 from the dropdown list.
4.Enter a Name for your destination. This is only for your reference – it can be anything that makes sense to you.
5.Enter authentication details for your AWS account and S3 bucket: Access Key ID, Secret Key, Bucket Name, and AWS Region. Census uses these details to authenticate – they must match your AWS setup. If you would like to use role based permissions instead, see the instructions below.
6.Click Save Connection.
The setup will look something like this: 👇
Step 2: Connect your data warehouse
The steps for connecting your data warehouse will depend on your technology. See the following guides:
​Databricks​
​Google BigQuery​
​Google Sheets​
​Postgres​
​Redshift​
​Rockset​
​Snowflake​
After setting up your warehouse, your Connections page should look something like this: 👇
Connections page with data source and S3 service
Step 3: Create your model
When defining models, you'll write SQL queries to select the data you want to sync. This can be as simple as selecting everything in a specific database table or as complex as creating new calculated values.
1.From inside your Census account, navigate to the Models page.
2.Enter a name for your model. You'll use this to select the model later.
3.Enter your SQL query. If you want to test the query, use the Preview button.
4.Click Save Model.
Basic SQL query for a new model
Step 4: Create your first sync
The sync will move data from your warehouse to your S3 bucket. In this step, you'll define how that will work.
1.From inside your Census account, navigate to the Syncs page and click Add Sync.
2.Under What data do you want to sync?, choose your data warehouse as the Connection and your model as the Source.
3.Under Where do you want to sync data to?, choose the name you assigned in Step 1 (we used S3) as the Connection. Enter the File Path for the CSV file where data will sync. The path can accept variables that will populate when the sync runs. See File Path Variables. Confirm the file path in the Template Preview field.
4.Under How should changes to the source be synced?, Select Update or Create or Mirror
5.Under Which properties should be updated?, choose whether to sync only Selected Properties or Sync All Properties. Syncing all properties will automatically add new properties to the sync if the model or database table changes.
6.To test your sync without actually syncing data, click Run Test and verify the results.
7.Click Next. This will open the Confirm Details page where you can see a recap of your setup.
8.If you want to start a sync immediately, set the Run a sync now? checkbox.
9.Click Create Sync.
When configuring your sync, the page should look something like this: 👇
Example sync setup for S3
Step 5: Confirm the synced data
Once your sync is complete, it's time to check your data. Open your CSV file from the S3 bucket and check that the file was created or updated correctly.
If everything went well, that's it! You've started syncing data from your warehouse to S3! 🥳️​
S3 bucket showing the new CSV file created by the Census sync
And if anything went wrong, contact the Census support team to get some help.
Using role-based permissions
As an alternative to using keys you may opt to grant Census access to a role in your AWS account. This won't provide any additional functionality from Census, but may be preferable for your AWS configuration. This is a multi-step process with parts happening in Census and inside your AWS console.
Step 1: When configuring the S3 destination click the "Use role" checkbox. Provide your bucket and region, but leave access and secret key blank. Click Connect:
Step 2: The automated connection check will run at this point and fail, this is expected.
Step 3: Click the 'Back' button to return to editing the destination. You should now see an 'External ID' input box with a string in it. You will use this string in the following step.
Step 4: Open your AWS Console in a separate tab and browse to the IAM service. Click 'Roles' and 'Create role'.
When creating the role choose 'AWS Account' for Trusted Entity Type and the 'Another AWS Account' radio button.
Ask your Census account representative for the Census AWS account to use.
Check the 'Require external ID' checkbox and enter the External ID string from Step 3.
Finish setting up your Role. Note that it should have the required permissions to access the bucket you are using as your Census destination!
When done, click on your role and copy its ARN. Go back to the tab where you're editing the Census S3 Destination and enter the role ARN.
Click 'Connect'. The tester should re-run and succeed.
File path variables
When defining the File Path for an S3 sync, you can use variables that will be set when the sync runs. This allows you to create and sync to new CSV files in the S3 bucket that reflect the date and time of the sync.
Variable
Description
Example Values
%Y
4-digit year
1997
%y
2-digit year
97
%m
month with zero padding
07, 12
%-m
month without zero padding
7, 12
%d
day with zero padding
03, 23
%-d
day without zero padding
3, 23
%H
24 hour with zero padding
08, 18
%k
24 hour without zero padding
8, 18
%I
12 hour with zero padding
08, 12
%l
12 hour without zero padding
8, 12
%M
minute with zero padding
04, 56
%S
second with zero padding
06, 54
🔄 Supported sync behaviors
Behavior
Supported
Objects
Update or Create
✅
All
Mirror
✅
All
Learn about all of our sync behaviors in Core Concepts.
​Let us know if you want Census to support additional sync behaviors for S3.
💡Things to know
We support syncing files up to 100GB. Files larger than 5GB may require some additional permissions, see 🔐 Required permissions below.
Data arrives in one file to the designated S3 bucket and file path.
Files are written as a CSV with headers.
We highly recommend adding default server-side encryption to your S3 buckets. Census supports syncing to buckets with encryption policies as long as the bucket uses an AWS provided key type like the Amazon S3 key (SSE-S3) or the AWS Key Management Service key (SSE-KMS). If the bucket uses SSE-KMS, make sure the IAM role credentials associated with the S3 connection have access to the AWS KMS key used for encryption. We do not support syncing to buckets using a customer-provided encryption key.
​Contact us if your use cases don't work with these limitations. We plan on addressing at least a few of these in the future!
🔐 Required permissions
For most S3 uploads, the only permission that we require is the s3:PutObject action. 
For files larger than 5GB, Census makes use of S3's Multi-part upload which requires the additional permissions:
s3:AbortMultipartUpload
s3:ListMultipartUploadParts
s3:ListBucketMultipartUploads
For more details on how Multipart Uploads use these permissions, see the S3 documentation.
🚑 Need help connecting to S3?
You can send our support team an email at [email protected] or start a conversation from the in-app chat.