Snowflake 'events' table is empty, but s3 buckets has raw and transformed events

For engineers Troubleshooting
1

Hey!

I’ve set up snowplow on aws using this guide

when I send a test event it does not land in the snowflake atomic.events table as expected. I see the events being collected in s3 buckets (in the ’ snowplow-terraform-sample-bucket’) under /bad, /raw, / enriched/ and /transformed/good folders but nothing is in the table.

When I analyze the cloudwatch logs ’ /aws/ec2/sp-snowflake-loader-server’ I see messages like this:
‘…INFO Loader: Total 120 messages received, 0 loaded; Loader is in Idle state; Last state update at 2022-08-04 11:03:41.667’

the question is, why, while it reads the messages, it does not load anything? it seems strange. do you guys have any clues?

2

Hi @pavvell ,

Welcome to Snowplow community !

It is strange any of the message isn’t loaded. Can you see any error log in the cloudwatch ?

3

hi @enes_aldemir! yes, just sent another event and I see the error now, it seems some access was not granted correctly or at all:

INFO Load: Loading s3://snowplow-terraform-sample-bucket/transformed/good/run=2022-08-04-12-50-00/
INFO Load: COPY events FROM s3://snowplow-terraform-sample-bucket/transformed/good/run=2022-08-04-12-50-00/
ERROR com.snowplowanalytics.snowplow.rdbloader: Transaction aborted. Sleeping for 30 seconds for the first time. Caught exception: net.snowflake.client.jdbc.SnowflakeSQLException: Failure using stage area. Cause: [Access Denied (Status Code: 403; Error Code: AccessDenied)]

It seems that s3 integration is required on the snowflake side - Option 1: Configuring a Snowflake Storage Integration to Access Amazon S3 — Snowflake Documentation I’ll try it.

4

Hmm, it is strange. All the required setup should be configured by Snowflake Terraform Module. Could you confirm you’ve followed Step 2 (Snowflake only): Run Snowflake terraform module in here ?

2 Likes
5

@enes_aldemir great, thanks to pointing out to the module! I did run it earlier, but I’ve changed the bucket name in aws/pipeline/snowflake.terraform.tfvars to another name cause the one provided in the template was obviously taken. and I did not think to change it in the snowflake/terraform.tfvars afterwards.

but now I did, re-run it, and everything works! thank you for pointing to the module, otherwise I would be digging into snowflake docs for hours may be :slight_smile:

2 Likes