Regulatory lists\u2014like chemical substance inventories and restriction lists\u2014are notoriously messy. They are full of multi-value fields (like semi-colon-separated CAS numbers), irregular date formats, and unpredictable schema drift.<\/p>\n
To bring order to this chaos, I established a strict architectural foundation inside Snowflake before even touching the automation layer.<\/p>\n
1. Hardening the Ingestion Layer<\/p>\n
We designed a custom Snowflake file format to handle the nuances of incoming UTF-8 encoded files, ensuring that a single malformed row wouldn’t bring the entire pipeline to a screeching halt.<\/p>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.1-300x184.png\")
<\/a>Image<\/p><\/div>\n
2. Schema-on-Read with Variadic Data<\/p>\n
Instead of forcing raw, unstable file structures directly into rigid relational columns, we used Snowflake’s OBJECT_CONSTRUCT to ingest the data as a structured JSON payload. This shielded our staging tables from sudden, unexpected column changes.<\/p>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.2.png\")
<\/a>Image<\/p><\/div>\n
Why this matters:<\/p>\n
\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OBJECT_CONSTRUCT<\/strong> standardizes downstream consumption into flexible key-value pairs, making schema drift a non-issue.<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON_ERROR = ‘CONTINUE’<\/strong> guarantees pipeline resilience, isolating bad records without killing the entire ingestion batch.<\/p>\n Regulatory transformations require highly specific mapping rules, like parsing conditional values or handling complex string logic. Embedding this directly inside execution scripts is a recipe for code duplication and maintenance nightmares.<\/p>\n Instead, we encapsulated the mapping logic into a centralized User-Defined Function (UDF) in Snowflake. This gave us a few massive advantages:<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 One Source of Truth: All regulatory mapping rules live in a single, easily auditable repository.<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Reusability: The exact same mapping logic applies to both historical processing and real-time ingestion.<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Cleaner Pipelines: It keeps our primary SQL ingestion syntax modular and incredibly easy to maintain.<\/p>\n While the database foundation was solid, we still had to solve the human problem: how do non-technical business stakeholders upload new lists without touching Azure Storage or running SQL commands?<\/p>\n We solved this by building an interactive internal web application using Streamlit.<\/p>\n Image<\/p><\/div>\n Why Streamlit Changed the Game<\/strong><\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Familiar, Intuitive UI: Users simply drag and drop their updated Excel or CSV regulatory lists right into the browser.<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Embedded Automation: The moment a file is uploaded, the Streamlit app interacts with Snowflake behind the scenes via native Python connectors to run the staging copy and execute the transformation queries.<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Bulletproof Auditing: To prevent accidental data overwrites, we implemented an IS_LATEST flagging strategy. Every upload generates a unique FILE_VERSION_ID and captures an INGESTED_AT timestamp, ensuring we never lose historical lineage.<\/p>\n Regulatory data is highly sensitive and constantly requested by downstream enterprise applications. To protect it, we avoided letting third-party tools query our physical base tables directly. Instead, we built a Secure View Layer that handles both data parsing and access control.<\/p>\n Image<\/p><\/div>\n The Engineering Behind the View<\/p>\n 1.\u00a0\u00a0\u00a0\u00a0 Dynamic Flattening: We used LATERAL FLATTEN to split multi-CAS number strings (e.g., 123-45-6; 789-10-1) into clean, distinct rows on the fly.<\/p>\n 2.\u00a0\u00a0\u00a0\u00a0 Regex Cleansing: A strict REGEXP_LIKE filter guarantees that only syntactically valid CAS numbers are exposed to downstream systems.<\/p>\n 3.\u00a0\u00a0\u00a0\u00a0 Data Security: Because it\u2019s a Secure View, Snowflake prevents external consumers from analyzing the underlying base tables, ensuring tight data governance.<\/p>\n To transform this warehouse asset into an agile data service, we wrapped the Snowflake Secure View in a lightweight, asynchronous FastAPI application.<\/p>\n Secure Connection Engineering<\/strong><\/p>\n To keep credentials secure, we decoupled our configuration from the application logic entirely by using an isolated .env environment file.<\/p>\n Here is the clean implementation pattern we utilized to connect safely to Snowflake:<\/p>\n Image<\/p><\/div>\n The Production API Endpoints<\/strong><\/p>\n With our database connection handled, we exposed real-time endpoint paths to deliver data dynamically to external teams.<\/p>\n Image<\/p><\/div>\n Moving a pipeline from manual execution to automated, local code always uncovers a few environmental quirks. During development, we hit a couple of classic infrastructure roadblocks:<\/p>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Account Identifier Subtleties: Snowflake’s account identifier format can be deceptive. A simple typo like company.azure-westeurope instead of the correct, cloud-provider-specific format company.west-europe.azure will result in frustrating network timeouts. If you run into this, verify your settings natively using:<\/p>\n Image<\/p><\/div>\n \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Network & Firewall Whitelisting: Local API setups require strict coordination with IT security policies. When connecting over enterprise VPNs, you have to ensure your public IP routing matches the client’s Snowflake Network Policies and that traffic can seamlessly traverse port 443.<\/p>\n By eliminating the manual data-shuffling middleman and wrapping our data platform inside user-friendly web frameworks and secure APIs, we completely changed how the business interacts with this data:<\/p>\n Building modern data pipelines isn’t just about moving data from point A to point B\u2014it’s about creating clean, secure, and accessible entry points that empower both business users and downstream software. By combining Snowflake, Streamlit, and FastAPI, we took a slow, painful chore and turned it into a robust, hands-off enterprise asset.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Every data engineer knows the dread of a process that looks perfect on paper but relies entirely on manual intervention. Not long ago, I was managing a critical regulatory compliance data pipeline for a client. The raw ingredients were complex, multi-format regulatory list updates provided in Excel and CSV files. The destination was Snowflake, where […]<\/p>\n","protected":false},"author":1789,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":0},"categories":[6194],"tags":[1358,5236],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/79942"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/1789"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=79942"}],"version-history":[{"count":2,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/79942\/revisions"}],"predecessor-version":[{"id":80019,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/79942\/revisions\/80019"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=79942"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=79942"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=79942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Centralizing the Logic: Why I Used UDFs<\/strong><\/h2>\n
Enter Streamlit: Giving Control Back to the Business<\/h2>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.3.png\")
<\/a>Data Modernization: Secure Views and API Readiness<\/h2>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.4.png\")
<\/a>Exposing Data Safely via FastAPI<\/h2>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.5.png\")
<\/a>![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.6.png\")
<\/a>Lessons from the Field: Troubleshooting Infrastructure<\/strong><\/h2>\n
![\"Image\"](\"https:\/\/www.tothenew.com\/blog\/wp-ttn-blog\/uploads\/2026\/06\/blog.7.png\")
<\/a>The Value Delivered<\/h1>\n
\n\n
\n Before: Manual Process<\/strong><\/td>\n After: Modern Automated Stack<\/strong><\/td>\n<\/tr>\n \n 1. Hand-carried file uploads took hours and introduced human error.
\n2. Inconsistent transformation scripts caused frequent schema discrepancies.
\n3. Overwriting older files erased critical historical audit trails.
\n4. Monolithic access patterns exposed raw base tables to consumers.<\/td>\n1. Streamlit App allows self-service uploads with instant, automatic processing.
\n2. Snowflake UDFs enforce central, standardized mapping logic.
\n3. Audit Versioning (IS_LATEST) preserves permanent tracking and history.
\n4. FastAPI & Secure Views expose data safely via production-ready REST endpoints.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n