Hello! Can anyone suggest which is an efficient way to get a list of mailchimp subscribers (email + subscription date) into a BQ table. The idea is to use the email_activity table and get the a list of distinct email addresses, but I am not sure if I am importing too much data to just get a list. (besides, I usually prefer to use the bigquery dts destination connection to avoid to have too many tables, but performance wise which would be better?)

Hey denis, Recent strategy for us is to place the response in GCS then query from there. This is actually the method the BigQuery denormalized uses (with GCS staging) to be more efficient. However they do an avro conversion before staging the load. In your case with schema being straightforward (you're only after on top-level column) ; would suggest trying this method. As long as you define table as 'external' not 'native' you can continuously query. You can set a pretty short lifecycle policy if you don't want to query too many responses, or just define your table path as the most recent response.

I will try, thank you! I usually send everything straight to BQ but I will try this route. (EDIT) but in the connection stage (from source to destination) is there any way to just select the columns / tables that I need? would that improve performances?

@Denis hard for me to say without seeing the configuration live - but certainly you can select down to the

email_activity

and only run that. Again the reason I made the suggestion I did was it should hypothetically need less compute resources for retrieval and loading, as you're basically matching the data format to how you're received it from mailchimp. Even if you were to 'select down to just the

list_id

,

email_address

- this is yet an additional process step and would likely increase the job runtime. I'm a fan of this approach with my GKE deployment because I'm seeing tangibly shorter job runtimes and reduced compute costs - I'm effectively offloading as much compute I can to BigQuery.

@Zach Brak thank you thank you!