I want to automate the source tables reading from snowflake to S3 through Snaplogic pipeleine dynamically. There might be additional tables added or additional schemas added in snowflake. What could be the best approach for this scenario
Dynamic Snowflake → S3 Extraction Approach This is a common pattern, and SnapLogic has good building blocks for it. Here's a solid architecture to handle dynamically growing tables/schemas without needing to hardcode or manually update pipelines every time something changes. Recommended Approach: Metadata-Driven Pipeline 1. Discover tables/schemas dynamically Instead of hardcoding table names, query Snowflake's metadata views at runtime: - Use a Snowflake Execute Snap to query INFORMATION_SCHEMA.TABLES (or SHOW TABLES IN SCHEMA ...) to get the current list of tables/schemas. - This becomes your "driver" list — anything new that shows up here gets picked up automatically on the next run. 2. Iterate over the discovered list - Feed the list of tables into a Pipeline Execute Snap (child pipeline pattern), passing each table/schema name as a pipeline parameter. - The child pipeline does the actual extraction logic (read from Snowflake table → write to S3), parameterized so it works for any table. 3. Child pipeline (generic extractor) - Use a Snowflake Select/Execute Snap with a dynamically built query (e.g., SELECT * FROM $schema.$table) using expressions. - Pass the output to a File Writer / S3 Snap, where the target S3 key/path is also built dynamically (e.g., s3://bucket/$schema/$table/$date.csv or .parquet). - This way, one child pipeline handles unlimited tables without modification. 4. Handle schema drift (new/changed columns) - Since you're reading dynamically, avoid strict schema mapping in a Mapper if possible — let the data pass through generically (JSON/CSV with headers) so new columns don't break anything. - If you need enforced structure, consider writing in a self-describing format like Parquet or JSON, which tolerates schema evolution better than fixed-width CSV. 5. Orchestration - Trigger the parent (discovery) pipeline on a schedule (Scheduled Task) or via an Ultra/Triggered Task depending on your latency needs. - Optionally maintain a control table (in Snowflake or a simple file) to track which tables have already been processed, last extraction timestamp, watermark columns, etc., for incremental loads. 6. Error handling & scalability - Use error pipelines/catch snaps so one failing table doesn't stop the whole batch. - Consider the Pipeline Execute Snap's "reuse count" and parallel execution settings to process multiple tables concurrently for performance. --- Summary of the Pattern
[Discover Schemas/Tables via INFORMATION_SCHEMA]
│
▼
[Pipeline Execute - loop per table] ──► [Generic Extractor Pipeline]
│
[Snowflake Select] → [S3 Writer]
This gives you a self-adjusting pipeline: new tables/schemas added in Snowflake are automatically picked up on the next scheduled run, with no manual pipeline changes required. --- Would you like me to actually generate this pipeline for you (the metadata discovery + dynamic extraction pattern), or are you looking for more guidance/design discussion first?
It would be easier to use the Snowflake - Table List snap to get all the table names rather than manually querying them from the tables table.