Hi all, I've got a huge table in Postgres partitioned by fiscal quarters that I'm trying to move into Snowflake. I've been able to successfully run Meltano to copy one partition over, and now I'm trying to figure out the following: • I'd like to have one pipeline per partition, and in general have each pipeline select a specific subset of entities (Postgres tables). So far I've been able to select entities in the CLI and I see how I can set this in yml at the extractor level, but how do I select entities at a pipeline level? • My Postgres instance doesn't have WAL turned on - if I don't do that, are there limitations/concerns I should be aware of about using xmin full table replication? • How might I improve performance? Bottlenecks I'm aware of that I can tweak are 1) Postgres CPU/memory, 2)

itersize

in

tap-postgres

seems to be hardcoded, 3) Snowflake

batch_size

, 4) Snowflake warehouse size. Any tips greatly appreciated!

Hi @dave_lyons!

how do I select entities at a pipeline level?

Do you mean at runtime, or using the

schedules

feature? In the first case you can use environment variables like

TAP_POSTGRES__SELECT='["public-my_table.*","public-another_table.*"]' meltano elt tap-postgres target-snowflake

In the second case, you define values in the

env

object: https://meltano.com/docs/project.html#schedules

@edgar_ramirez_mondragon I ran

full table

and not only does the select query include a

xmin::text::bigint

column, but when the job pipe broke and I restarted, it resumed based on

where age(xmin::xid) <= age('569696045'::xid)

. That's what I was referring.

that is correct. the present focus in on the CLI experience and UI will gain more focus on 2022 H1

@edgar_ramirez_mondragon any tips on how to speed things up?

So these are not specific 1. Bump batch size as much as you can. This may require increasing the available memory for

meltano elt ...

. 2. Disable unnesting in the target and leave those transformations to the data warehouse. 3. Create one extractor for each table (using different

select

filters) with plugin inheritance

re: batch size, I'm hitting what looks like a Snowflake, not a memory, limitation: https://meltano.slack.com/archives/CMN8HELB0/p1634154018290500?thread_ts=1634116216.278500&cid=CMN8HELB0

Ah, the meltano variant unnests objects into multiple columns but it doesn't unnest arrays so performance in that sense shouldn't be a concern

@edgar_ramirez_mondragon that makes sense. I'm left with trying to make initial migrations of large tables (600mil rows, +100GB) go faster. Warehouse size doesn't seem to help, batch size seems to be capped because the Snowflake connector uses UPSERTs and not COPY,

itersize

is hardcoded at 20000 in

tap-postgres

, and CPU/memory usage on Postgres, Snowflake, and even my local machine is nowhere near exhausted so that's not the bottleneck. What else can I do?

So with the current limitations, to squeeze performance another option is to write csvs to s3 and have something like snowpipe automatically COPY those files for you

Like the loader is actually S3 CSVs?

Yup

hmmm. We are a GCP shop, in the UI I see BigQuery but not GCS.

@dave_lyons I see. Indeed target-s3-csv is not listed in the hub, but pipelinewise's should be a good option. For GCS support, it could be forked

I’m looking into a similar migration/pipeline into Snowflake. @dave_lyons did you end up figuring something out?

@matt_cooley I’ve got it working but those initial loads are still bears and I haven’t figured out the best tweaks. Happy to share more, I had to step away from it the last couple of weeks so its not top of mind.