PM here from Snowflake Postgres.

I was the chief product office of the Crunchy Data team that was acquired just before Summit over a year ago. The top ask from most early customers was easing getting their Postgres data into Snowflake. We were already hard at work on it from day one, and even some of the foundations of it with pg_lake date back to over 3 years go.

Today that capability to mirror your data from Postgres to Snowflake is going GA. The blog post has more details, but happy to field any questions folks may have about how it works, the engineering behind it, or just Postgres in general.

Blog post with more details at: https://www.snowflake.com/en/blog/data-mirroring-ga-snowflake-postgres/

My company currently has JSON data landing in a "staging" table in Snowflake nightly at 11 pm. There's dynamic tables that transform the data from a variant column into a structured table. Currently we have the dynamic tables set up on an hourly lag even though data only comes in once a day. The reason being is there are multiple scheduled tasks that refers to these dynamic tables after 11 pm and we do not want the lag to be such a wide range incase the dynamic tables have not refreshed, but these tasks run and will not capture the latest data in the "staging" tables.

Should I change the set up? Is it costing the company Snowflake credits by having the dynamic table checked hourly even though we know data will only come in once a day? Is there no way to schedule a refresh on a specific time for a dynamic table?

I have JSON files in an external stage (S3) and I'm trying to load them into Snowflake first in a landing table and then eventually into a dimensional model in a target schema.

I just learned that I can't run COPY INTO statements with fancy SELECT queries that include a lateral function, since that's what I need to parse out the JSON file. I started to just build a basic query that SELECTs the data from the JSON file and just insert it into a table by doing essentially this:

SELECT 
    mycolumns
FROM
@mydata.my_stage_name/ALL/
, LATERAL FLATTEN(input => $1, outer => true) as data

Are there any better options that I could use or at least try out before I fully commit to that option?