Good — noted constraints

You chose: strict security / corporate VPC requirements and cost-sensitivity. Below is a concise recommendation and then a focused question to tailor the exact setup.

Recommendation summary (short):

Prefer SQS-based auto-ingest only if your security posture allows SQS integration and you can manage VPC egress to AWS endpoints. It offers low-latency and minimal custom orchestration but requires careful IAM, network, and encryption setup.
If strict VPC/no-public-access is required, consider REST API push from an internal component running in your VPC (e.g., a Lambda with VPC access or an ECS task) to call Snowpipe's REST API — gives tighter control but increases operational work.
To minimize cost: avoid continuous compute; use Snowpipe (micro-billed) rather than always-on warehouses for ingestion, and tune file batching (file size & frequency) to balance latency vs cost.
Network & security best-practices: VPC endpoints (Gateway/Interface), TLS with client certs where supported, restricted IAM roles for S3/SQS access, SSE-S3 or SSE-KMS for S3, and Snowflake object tagging + masking for downstream data access control.

Now a focused question so I can give exact steps (IAM policies, SQS config, Snowflake objects, and example CLI/SQL):

Learning: Snowflake