A Snowflake integration for agentic B2B products that need canonical context, incremental updates, and enterprise grade security boundaries. ## "You need a warehouse integration" AI agents are being asked to qualify accounts, route tickets, draft approvals, and trigger actions that change operational systems. Once an agent is allowed to act, the biggest risk becomes incorrect context, not imperfect language generation. An agent that reads the wrong value will automate the wrong outcome at scale. Enterprises already know this failure mode because their definitions are messy by default. Even simple questions like “what is ARR” can have multiple correct answers depending on the use case. Humans can reconcile nuance and settle on the right definition for a decision. Agents cannot negotiate meaning in the middle of execution, so they need explicit truth contracts. Warehouses and lakehouses increasingly hold those truth contracts in practice. Teams centralize data into Snowflake; then curate governed models, [gold tables](https://www.snowflake.com/en/developers/guides/declarative-pipelines-using-dynamic-iceberg-tablesmd/), and reviewed metric definitions. That makes Snowflake a natural source for agentic workflows since governance, lineage, and access control are already present. If your AI agent cannot read canonical context safely, it cannot be trusted with real actions. You need a warehouse integration that is repeatable for onboarding, scoped for least privilege, and predictable for operations. ## What we shipped Ampersand’s Snowflake Connector lets customers expose an approved dataset from Snowflake using SQL they control. Ampersand creates a Dynamic Table in an isolated integration schema from that SQL query. Ampersand creates a Stream on the Dynamic Table to capture incremental changes over time. Ampersand delivers data to your [destination](https://docs.withampersand.com/destinations/overview) (webhook, kinesis, etc), using scheduled incremental delivery or on demand reads. This connector is intentionally read-focused and supports backfill and incremental updates using Snowflake primitives. Optional proxy mode is available for authenticated Snowflake REST API calls. A first-class Subscribe Action across warehouses is a roadmap item. ## Core primitives: Trigger Read API vs incremental changes vs writeback ### Trigger Read API Trigger Read is the on-demand way to fetch data for a custom time window. When you do not configure a schedule, you can call Trigger Read with custom time periods. When you set only a Since value, Trigger Read returns pending data from the Stream. When you set both Since and Until, Trigger Read returns rows from the Dynamic Table starting at a specific point in time. ### Scheduled incremental changes from Streams When you configure a schedule, Ampersand reads pending data from the Stream regularly and delivers only incremental changes. This keeps agent context fresh without running repeated full refresh jobs across large tables. The schedule determines how frequently the Stream is asked for changes and delivered to your destination. Dynamic Table refresh cadence, also called target lag, influences how quickly changes become available for delivery, and very frequent settings can increase warehouse consumption. Many teams describe this as subscription style delivery because changes arrive incrementally. We are not labeling this as a Subscribe Action today, because the implementation is scheduled incremental reads from Streams. A first class Subscribe Action with consistent behavior across Snowflake, BigQuery, and Databricks is on our roadmap. ### Proxy mode writeback, when customers explicitly want it Proxy mode enables authenticated and direct calls to the customer’s Snowflake instance using Snowflake’s REST API. If an agent needs to publish scores, enrichments, or curated tables, proxy mode provides good resource management. ## Use case matrix ### Trigger Read API examples - An onboarding agent can backfill accounts and contacts for enrichment and matching. - A compliance agent can pull historical changes to produce an audit trail for regulated workflows. ### Scheduled incremental delivery examples - A routing agent can trigger workflows when qualified rows appear in a governed table. - A monitoring agent can react when status fields change and launch scoped actions. ### Proxy mode examples - A scoring agent can publish prediction scores into a customer-controlled warehouse table. - An enrichment agent can write results through REST calls using proxy mode boundaries. ## Anonymized case study A hyper growing AI workflow company builds agents that automate revenue and operations tasks. Their enterprise customers keep canonical data in Snowflake with strict governance and review. The company needed agent workflows that were correct, explainable, and safe to operate. ### Before Their agents pulled context from operational APIs that were incomplete and semantically inconsistent. Their pipelines relied on full refresh patterns that increased cost and latency. Security reviews stalled because privileges were broad and credential handling varied by customer. When something went wrong, root cause analysis was slow because truth was spread across systems. ### After Each customer ran a one time Snowflake setup script using a high privilege role. The script created a dedicated minimal role, a small warehouse for sync queries, and an isolated integration database and schema. The script created a service user with RSA key pair authentication and read only access on the source data referenced by the approved query. The customer’s data team provided an approved SQL query per workflow, including a timestamp column for incremental reading and a primary key for consistency across deliveries. Illustrative outcomes that are typical for this pattern were clear and operational. Onboarding time often moved from weeks of bespoke work to days of repeatable setup. Agent failures tied to stale context declined because the agent started from governed warehouse truth. Illustrative metrics for typical deployments included onboarding setup dropping from roughly two weeks to under one week. Illustrative metrics for typical deployments also included reducing delivered rows by fifty to ninety percent compared with nightly full refresh jobs. ## How it works ### Dynamic Tables The customer defines a SQL query that represents the dataset the agent should consume. Ampersand materializes that query as a Dynamic Table inside the integration schema. Target lag controls refresh cadence, and more frequent refreshes can increase warehouse consumption. ### Streams Ampersand creates a Stream on the Dynamic Table to track inserts, updates, and deletes. Ampersand reads from the Stream to capture incremental changes since the last consumption. ### Scheduled incremental sync Ampersand reads pending data from the Stream on a schedule and delivers only changes since the last sync. Schedules are recommended to be less than a day, because some Snowflake tiers may clean up resources like Streams when they are not read regularly. If you do not configure a schedule, you can use Trigger Read with custom time windows instead. ## Architecture overview ``` Customer Snowflake Account ┌──────────────────────────────────────────────────────────────────────┐ │ Customer owned canonical models, views, and curated truth tables │ │ └─ Read only grants to a dedicated minimal integration role │ │ │ │ Isolated integration database and schema for agent connectivity │ │ ├─ Dynamic Table materialized from approved customer SQL query │ │ └─ Stream tracking incremental changes on that Dynamic Table │ │ │ │ Small warehouse dedicated to sync query workloads │ │ Service user using RSA key pair authentication without passwords │ └──────────────────────────────────────────────────────────────────────┘ │ │ scheduled Stream reads or Trigger Read requests ▼ Ampersand Sync Service │ │ incremental deliveries to your destination ▼ Webhook endpoint inside your agent runtime infrastructure │ │ optional proxy mode for authenticated REST calls ▼ Snowflake REST API using approved connection context ``` ## Security and permissions ### Least privilege setup on the customer side Customers run a one-time setup script using a high privilege role like ACCOUNTADMIN. The script creates a dedicated role with minimal permissions for integration access. The script creates a small warehouse for sync queries with bounded cost visibility. The script creates a separate integration database and schema, isolated from source data. The script creates a service user authenticated with RSA key pair credentials, which avoids password-based service accounts. The script grants read-only access to the source data referenced by the approved query. ### Dedicated role and separate integration schema A dedicated role makes audit trails and revocation straightforward for security teams. A separate integration schema keeps Dynamic Tables and Streams visible and manageable. ### RSA key pair authentication RSA key pair authentication reduces secret sprawl and aligns with enterprise expectations. Passwordless authentication also simplifies rotation practices for teams managing machine identities. ## Technical examples ### a) amp.yaml example ```yaml specVersion: 1.0.0 integrations: - name: snowflake-data-sync provider: snowflake proxy: enabled: true read: objects: - objectName: contacts destination: myWebhookDestination optionalFieldsAuto: all requiredFields: - mapToName: contact_id prompt: Which field do you want to use as the contact ID? schedule: "*/60 * * * *" ``` ### b) curl example showing connections:generate ```bash curl --location \ "https://api.withampersand.com/v1/projects/{{projectIdOrName}}/connections:generate" \ --header "X-Api-Key: {{apiKey}}" \ --header "Content-Type: application/json" \ --data '{ "provider": "snowflake", "providerWorkspaceRef": "paxvusd-rm12481", "groupName": "Acme", "groupRef": "acme.io", "consumerName": "Joe", "consumerRef": "joe@acme.io", "customAuth": { "privateKey": "{{base64EncodedPrivateKey}}" }, "warehouse": "AMPERSAND_SYNC_WH", "database": "AMPERSAND_INTEGRATION_DB", "schema": "AMPERSAND_INTEGRATION_SCHEMA", "user": "AMPERSAND_SVC_USER", "role": "AMPERSAND_SYNC_ROLE", "query": "SELECT CONTACT_ID, EMAIL, UPDATED_AT FROM ANALYTICS.CRM_CONTACTS", "timestampColumn": "UPDATED_AT" }' ``` ## Getting started - Step 1: Create an Ampersand org and project, then generate an API key. - Step 2: Ask your customer to run the Snowflake setup script once with ACCOUNTADMIN access. - Step 3: Agree on an approved SQL query, a primary key, and a timestamp column. - Step 4: Define the integration in amp.yaml and map objectName values to your workflow. - Step 5: Generate the connection using connections:generate with customer and Snowflake details. - Step 6: Configure a schedule for incremental deliveries, or use Trigger Read for on demand reads. - Step 7: Validate webhook deliveries in staging before enabling production agent workflows. ## FAQ ### Security review expectations for customer Snowflake access and agent workflows Security teams want explicit scope, auditable artifacts, and clear rollback process. The connector uses a dedicated minimal role and an isolated integration schema. The service user uses RSA key pair authentication, which avoids password-based secrets. Read access is limited to the source data referenced by the approved SQL query. ### Performance and cost considerations for Snowflake refresh cadence and delivery schedules Customers control the warehouse size used for sync queries, which bounds spend. Scheduled incremental deliveries reduce the need for repeated work compared to full-refresh patterns. Query complexity and refresh cadence still determine overall warehouse consumption. Very frequent target lag settings can increase warehouse cost quickly. ### Handling schema changes safely when customer models evolve over time The approved SQL query is the contract that defines what the agent consumes. Customers can evolve underlying tables while keeping query output stable. When output shape must change, treat it like an API version change. ### Field mapping behavior across customers with different Snowflake schemas Field mapping is optional and can be configured during installation and connection setup. Mapping aligns warehouse column names with your product’s schema. ### Data residency and where Dynamic Tables and Streams actually live Dynamic Tables and Streams live inside the customer’s Snowflake account by design. Data leaves Snowflake only when delivered to your destination, such as a webhook. ### Roadmap coverage for BigQuery and AWS Redshift warehouses and Databricks lakehouse deployments Many teams want the same agent truth contract across Snowflake, BigQuery, and Databricks. We are treating cross-platform Subscribe Action semantics as a roadmap item. ## Conclusion If your agent is going to take action, it needs canonical context and safe boundaries. Snowflake is increasingly where enterprises encode truth for the entire organization. Ampersand’s Snowflake Connector gives you a least privilege, passwordless, incremental path into that truth. Next steps: - Talk to us if you want an architecture review for your agent workflows. - Get access when you are ready to onboard warehouse native agent integrations.
Back to articles