Architecture Blueprints
Most articles on this blog build something on Snowflake. Snowflake is where I prove them, not what they're about. Underneath each article is an architecture that solves a problem every database and data platform has: an AI agent that should act as the employee who asked, a key the cloud provider should never hold, data that must not leave the building. This page puts those architectures first.
How to read a blueprint. Each one starts with the problem and the pattern, without product names. Then the building blocks it needs: if your database or platform has them, the pattern works there too. To make that concrete, each blueprint shows the same building blocks in an ordinary database, PostgreSQL, unless the pattern genuinely needs a data platform. "Re-check when you move it" names the parts that were tied to the platform I built it on. Below that are the articles with the build, step by step and with code; the ones marked tested report a real run.
Identity & access
AI agents that act as the signed-in employee
The problem. The quick way to connect an AI chat tool or app to company data is one shared technical account. That account sees everything, so the AI sees everything, and the audit trail says "the bot did it".
The pattern. The app passes each user's own token from the company identity provider through to the data platform. Every query runs in that user's session, so the same row and column rules apply as everywhere else, and the audit log names the person.
What it needs from a platform
- The company identity provider issuing per-user OAuth or OpenID Connect tokens
- A data platform that accepts an external identity provider's tokens and maps their claims to a user and a role
- Row and column policies keyed on the session user, not on the app that connects
- An agent or tool layer (for example an MCP server) that runs in the caller's session, not its own
- An audit log that records the user, the role and the policies applied
The same building blocks in an ordinary database, for example PostgreSQL. PostgreSQL 18 can accept OAuth bearer tokens from your identity provider through a validator module, row-level security keys rows on current_user, and the pgaudit extension records who ran what.
Re-check when you move it. Whether search and retrieval services filter per user (in the tested build they don't, so search is split per access domain), how shared agents with a pre-selected connection behave, and how roles are derived from token claims.
The build, on Snowflake
- No Service Accounts: AI Agents That Act as the Signed-In Employee, Built on Langdock and Snowflake tested
- From SAML Login to Snowflake as the User: Entra ID Passthrough Without the Secondary-Role Trap tested
- MCP Access with Your Own Identity Provider: External OAuth on Snowflake Managed MCP Servers tested
- Credential Vending for AI Agents: a Snowflake OAuth and PAT Toolkit for MCP Authentication tested
AI agent security
Five controls that keep AI agents in bounds
The problem. Agents get credentials, call tools and read data faster than any review process can follow. Blocking them doesn't work, and letting them run with a person's full permissions doesn't either.
The pattern. Put the controls in the data platform, not in each agent: a distinct identity for the agent, scoped sessions, policies that know when an agent is acting, governed inference and egress, and an audit trail that attributes every read to both the agent and the person behind it.
What it needs from a platform
- A way to tell agent sessions from human ones (a token claim, a session flag or a separate agent identity)
- Row and column policies that can take that into account
- Session scoping, so a role or scope limit applies per session rather than per user
- Control over where models run and which ones may be used, plus network egress rules
- Audit and usage history that records the agent as well as the user
The same building blocks in an ordinary database, for example PostgreSQL. Agents get their own roles, SET ROLE drops a session to a narrower one, a session setting marks agent sessions, row-level security policies read that setting, and pgaudit records both.
Re-check when you move it. Marking agent sessions and limiting scope per session are newer features and differ most between platforms. Where they're missing, a gateway or a separate agent identity does the same job.
The build, on Snowflake
- Keep Shipping: How to Say Yes to AI Agents Without Losing Control
- Containing AI Agents: A Defense-in-Depth Architecture for Production
- Governing AI Inference in the Data Cloud: A Security Architecture Guide for EMEA Enterprises
- Governing AI Inference Inside the Data Platform: a Snowflake Cortex AI Audit and Region Guide
- Federal Zero Trust Architecture Blueprint: One Control Plane for Data and AI, on Snowflake
AI agent security
One policy engine for every agent
The problem. Every agent framework brings its own permission model, and the data platform has another. The rules drift apart, and nobody can answer "who decided this agent could read that?"
The pattern. An external policy engine makes the decision, and the data platform enforces it as a short-lived grant bound to the agent's session. A bypassed gateway or an engine that is offline fails closed.
What it needs from a platform
- A policy engine with a standard decision API, such as Cedar behind AuthZEN
- A gateway that asks for a decision before each tool call
- The user's and the agent's identity carried into the data platform session
- Policies in the platform that read the decision, for example a grants table checked by row and column policies
- Fail-closed behaviour when the engine can't be reached
The same building blocks in an ordinary database, for example PostgreSQL. The policy engine writes short-lived decisions into a table keyed on the session (pg_backend_pid()), and row-level security policies only return rows with a fresh decision: no decision, no rows.
Re-check when you move it. Binding a decision to one session needs a session identifier that policies can read, and a way to recognise agent sessions.
The build, on Snowflake
AI agent security
A policy gateway between AI agents and models
The problem. Coding agents and assistants talk straight to model APIs. Prompts carrying secrets or customer data leave the company, and there is no single place to decide what may be sent or to see what was.
The pattern. Every agent goes through one gateway that speaks the API the agent expects, forwards to whichever model backend is allowed, checks the prompt against policy before it leaves and logs the call.
What it needs from a platform
- A gateway that speaks the API your agents already use
- Model backends you're allowed to use, such as in-platform, sovereign-cloud or local models
- Prompt checks before the call for secrets, personal data and injection patterns
- Per-user authentication at the gateway and a log of every call
- Egress rules, so agents can't go around it
On other platforms. The gateway sits in front of the model backends, so it doesn’t depend on the database underneath at all.
Re-check when you move it. Model names and token exchange differ per backend. The gateway keeps that difference in one adapter per backend.
The build, on Snowflake Cortex and three other model backends
Encryption & sovereignty
Keep the data, the key or the switch
The problem. Regulators and works councils ask what happens if the cloud provider is compelled to hand over data. "It's encrypted at rest" doesn't answer that if the provider also holds the key.
The pattern. Keep one of three things on your side: the data (only tokens or ciphertext go to the cloud), the key (decryption goes through a key service you run), or the switch (you can cut access in a way the provider can't override). Policies decide per user whether a query sees plaintext.
What it needs from a platform
- Built-in encryption functions, or user-defined functions that can run crypto libraries
- A controlled way to call a key service you operate
- Masking policies that decrypt or tokenise per role
- Trustworthy user context on every call to the key service, so each decryption is logged on your side
- For "keep the data", a tokenisation or crypto proxy on premises in front of the cloud
The same building blocks in an ordinary database, for example PostgreSQL. pgcrypto encrypts and decrypts AES inside the database, a PL/Python function can fetch the key from a KMS you run, views or security-definer functions decide per role who sees plaintext, and custom aggregates can carry homomorphic sums.
Re-check when you move it. How the platform passes user context to external calls, and whether it caches function results, decide how strong the audit is. Homomorphic ciphertexts must match the library build the platform runs.
The build, on Snowflake
- The Data, the Key, or the Switch: Customer-Held Controls for Data Sovereignty, Built on Snowflake tested
- AES-CBC Column Encryption with Keys You Control: Four Key Management Strategies on Snowflake tested
- Column-Level Encryption with Your Own Key: Postgres as an On-Premise Crypto Proxy for Snowflake
- Python Camouflage: Format-Preserving Encryption with FF3-1 and Tag-Based Masking, on Snowflake
- Computing on Data the Platform Can't Read: Homomorphic Encryption in Python UDFs on Snowflake tested
Encryption & sovereignty
Query data that never leaves your premises
The problem. Some data may not be copied to the cloud at all, yet analysts and AI need to query it next to everything else. Opening an inbound firewall port for the cloud is a non-starter.
The pattern. An encrypted tunnel joins a container running inside the cloud platform with the owner's network, without opening an inbound firewall port to the data. Queries reach the on-premises tables through it, nothing is stored in the cloud, and closing the tunnel ends access.
What it needs from a platform
- Containers that run inside the data platform, with egress allowed only to named hosts
- An encrypted tunnel the data owner controls and can close, with no inbound port into the data network
- A query engine on premises over an open table format such as Apache Iceberg
- A way to call the container from SQL
- A secret store for the tunnel keys
The same building blocks in an ordinary database, for example PostgreSQL. Foreign data wrappers such as postgres_fdw query remote tables in place, and the connection can run through the same kind of tunnel.
Re-check when you move it. Result size limits and caching of container function results, and how the platform authenticates calls into the container.
The build, on Snowflake
Encryption & sovereignty
Encrypted file drops, decrypted where the data lands
The problem. Partners send PGP-encrypted files. The usual answer is a file transfer server that decrypts them, which means one more machine holding a private key and plaintext.
The pattern. Encrypted files land directly in the platform's storage. A sandboxed function decrypts them with a private key kept in protected platform storage, and the result is loaded from there. No server in between ever holds the key or the plaintext.
What it needs from a platform
- Platform storage that partner files can land in directly
- Sandboxed functions that can read stored files and run a PGP library
- Protected storage for the private key that only those functions can read, with no network egress
- A load step for the decrypted files (scheduled if partners deliver regularly) and an audit trail of every run
The same building blocks in an ordinary database, for example PostgreSQL. pgcrypto decrypts OpenPGP natively (pgp_pub_decrypt), so the database itself can be where partner files are decrypted.
Re-check when you move it. How functions read files from storage. The tested build patched the PGP library for it.
The build, on Snowflake
Security operations
The data platform as your security data lake
The problem. SIEM licences charge by volume, so most logs are dropped or kept for a few days. The data platform is cheaper storage, but it isn't wired into the security operations centre.
The pattern. Keep fast detection in the SIEM and land the full log volume in the data platform. Detections run in SQL on a schedule and raise incidents in the SIEM or XDR, AI triages them, and AI agent activity is exported as one more telemetry source.
What it needs from a platform
- Streaming or batch ingestion, ideally OpenTelemetry-compatible
- Scheduled SQL detections
- Outbound webhooks, or controlled egress to the SIEM's ingestion API
- Audit history of logins, grants, queries and agent activity
- Optional: in-platform AI for triage, and federated queries from the SIEM into the lake
The same building blocks in an ordinary database, for example PostgreSQL. pgaudit and connection logging write the audit trail, file_fdw reads the CSV server log as a table, the pg_cron extension schedules the detection SQL, and the http extension can post findings to the SIEM.
Re-check when you move it. Audit view names and agent trace schemas are platform-specific, so the detection SQL needs porting. The routing pattern doesn't.
The build, on Snowflake
- A Fully Automated SOC on the Data Platform: Snowflake XDR, Run by Cortex AI in One Account tested
- A Hybrid Security Data Lake with Federated Queries, Built with Splunk and Snowflake tested
- Let the Data Platform Raise Security Incidents in Your SIEM Automatically: a Snowflake Build tested
- Scheduled SQL Alerts to Your SIEM: Snowflake Webhook Notifications for Splunk and Sentinel
- Closing the AI Agent Blind Spot in Your SIEM: Cortex Agents in Microsoft Sentinel tested
- OpenTelemetry Logs, Traces and Metrics into an Iceberg Security Data Lake, Built on Snowflake tested
- Security Operations AI Skill: A Cybersecurity Data Lake in SQL, Built on Snowflake tested
Platforms & infrastructure
Run your own software inside the data platform's boundary
The problem. Every tool that needs the data, such as an integration engine, an IDE or a telemetry receiver, usually lives outside the platform, with its own credentials, network path and copy of the data.
The pattern. Run it as a container inside the platform's security boundary. It inherits the platform's identity and network controls, reaches the data without exported credentials, and reaches the outside world only through egress rules you approve.
What it needs from a platform
- A managed container runtime inside the data platform
- Workload identity, so the container gets a short-lived platform token instead of a stored password
- Egress allowlists for named external hosts
- Ingress endpoints that authenticate against platform roles
- Persistent volumes, and container logs flowing into the platform's own telemetry
On other platforms. This one needs a platform that runs customer containers: look for a managed container runtime with workload identity, egress allowlists and authenticated ingress.
Re-check when you move it. This blueprint depends most on the platform. Ingress behaviour, token mounting and volumes differ, and not every data platform runs customer containers.
The build, on Snowflake
Platforms & infrastructure
An open lakehouse you own
The problem. Analytics usually means renting a platform. For small teams, sovereign projects or a second copy of critical data, that's expensive and creates lock-in.
The pattern. Tables in an open format on ordinary object storage, the catalog in a database you run, exposed through the standard Iceberg REST catalog API, so any engine can read the same tables, including the large data platforms.
What it needs from a platform
- S3-compatible object storage
- An open table format and a catalog database
- The Iceberg REST catalog API in front of it
- Access control in two layers, catalog grants and storage credentials
- Query engines that speak Iceberg REST
The same building blocks in an ordinary database, for example PostgreSQL. DuckLake can keep its catalog in PostgreSQL, so an ordinary PostgreSQL server can sit at the heart of the lakehouse.
Re-check when you move it. Iceberg v3 support differs between engines, so check the features you rely on.
The build, on DuckDB and DuckLake
Why blueprints, and why a real platform as the proof#
An architecture diagram is cheap. A build that runs is not: it finds the token claim that’s missing, the policy that doesn’t apply to search results, the object that doesn’t replicate. So each blueprint here was built on one real platform, and the articles keep those findings in.
The patterns themselves rest on open standards (OAuth 2.0, OpenID Connect, SAML, SCIM, MCP, Apache Iceberg, OpenTelemetry) and on building blocks most serious databases and data platforms have in some form: row and column policies, encryption functions, an audit log, scheduled SQL, controlled egress. That’s why they carry over, even if not to every platform in every detail.
Personal blog. Snowflake-related articles explore the art of the possible and are not official Snowflake solutions or endorsed by Snowflake unless stated. Opinions are my own. Content is meant as educational inspiration, not production guidance.