Obviously, snowflake has revolutionized cloud data warehousing for years. Consequently, the demands for streamlined data ingestion grew significantly. When it comes to the snowflake openflow tutorial, understanding this new paradigm is absolutely essential. Snowflake Openflow launched in 2025. It targets complex data pipeline management natively. This groundbreaking tool promises to simplify data engineering tasks dramatically.
To illustrate, previously, data engineers relied heavily on external ETL tools for pipeline orchestration. However, these external tools added immense complexity and significant cost overhead easily. Furthermore, managing separate batch and streaming systems was always inefficient. Snowflake Openflow changes this entire challenging landscape completely.
Additionally, this new Snowflake service simplifies modern data integration dramatically. Therefore, data engineers can focus on transformation logic, not infrastructure management. You must learn Openflow now to stay competitive in the rapidly evolving modern data stack. A good snowflake openflow tutorial starts right here.
The Evolution of Snowflake Openflow Tutorial and Why It Matters Now
Second, initially, Snowflake users often needed custom solutions for sophisticated real-time data ingestion needs. Consequently, many data teams utilized expensive third-party streaming engines unnecessarily. Snowflake recognized this critical friction point early on during its 2024 planning stages. The goal was full, internal pipeline ownership.
To illustrate, openflow, unveiled spectacularly at Snowflake Summit 2025, addresses all these integration issues directly. Moreover, it successfully unifies both traditional batch and real-time ingestion capabilities seamlessly within the platform. This essential consolidation reduces architectural complexity immediately and meaningfully.
Therefore, data engineers need comprehensive, structured guidance immediately, hence this detailed snowflake openflow tutorial guide. Openflow significantly reduces reliance on those costly external ETL tools we mentioned. Ultimately, this unified approach simplifies governance and lowers total operational costs substantially over time.
How Snowflake Openflow Tutorial Actually Works Under the Hood
However, essentially, Openflow operates as a native, declarative control plane within the core Snowflake architecture. Furthermore, it skillfully leverages the existing Virtual Warehouse compute structure for processing power. Data pipelines are defined quickly using intuitive declarative configuration files, typically YAML format.
Specifically, the robust Openflow system handles resource scaling automatically based on the detected load requirements. Therefore, engineers completely avoid tedious manual provisioning and scaling tasks forever. Openflow ensures strict transactional consistency across all ingestion types, whether batch or streaming.
Consequently, data moves incredibly efficiently from various source systems directly into your target Snowflake environment. This tight, native integration ensures maximum performance and minimal latency during transfers. To fully utilize its immense power, mastering the underlying concepts provided in this comprehensive snowflake openflow tutorial is crucial.
Building Your First Snowflake Openflow Tutorial Solution
Firstly, you must clearly define your desired data sources and transformation targets. Openflow configurations usually reside in specific YAML definition files within a stage. Furthermore, these files precisely specify polling intervals, source connection details, and transformation logic steps.
You must register your newly created pipeline within the active Snowflake environment. Use the simple CREATE OPENFLOW PIPELINE command directly in your worksheet. This command immediately initiates the internal, highly sophisticated orchestration engine. Learning the syntax through a dedicated snowflake openflow tutorial accelerates your initial deployment.
Consequently, the pipeline engine begins monitoring source systems instantly for new data availability. Data is securely staged and then loaded following your defined rules precisely and quickly. Here is a basic configuration definition example for a simple batch pipeline setup.
Once the definition is successfully deployed, you must monitor its execution status continuously. The native Snowflake UI provides rich, intuitive monitoring dashboards easily accessible to all users. This crucial hands-on deployment process is detailed within every reliable snowflake openflow tutorial.
Advanced Snowflake Openflow Tutorial Techniques That Actually Work
Advanced Openflow users frequently integrate their pipelines tightly with existing dbt projects. Therefore, you can fully utilize complex existing dbt models for highly sophisticated transformations seamlessly. Openflow can trigger dbt runs automatically upon successful upstream data ingestion completion.
Furthermore, consider implementing conditional routing logic within specific pipelines for optimization. This sophisticated technique allows different incoming data streams to follow separate, optimized processing paths easily. Use Snowflake Stream objects as internal, transactionally consistent checkpoints very effectively.
Initially, focus rigorously on developing idempotent pipeline designs for maximum reliability and stability. Consequently, reprocessing failures or handling late-arriving data becomes straightforward and incredibly fast to manage. Every robust snowflake openflow tutorial stresses this crucial architectural principle heavily.
CDC Integration: Utilize change data capture (CDC) features to ensure only differential changes are processed efficiently.
What I Wish I Knew Before Using Snowflake Openflow Tutorial
I initially underestimated the vital importance of proper resource tagging for visibility and cost control. Therefore, cost management proved surprisingly difficult and confusing at first glance. Always tag your Openflow workloads meticulously using descriptive tags for accurate tracking and billing analysis.
Furthermore, understand that certain core Openflow configurations are designed to be immutable after successful deployment. Consequently, making small, seemingly minor changes might require a full pipeline redeployment frequently. Plan your initial configuration and schema carefully to minimize this rework later on.
Another crucial lesson involves properly defining comprehensive error handling mechanisms deeply within the pipeline code. You must define clear failure states and automated notification procedures quickly and effectively. This specific snowflake openflow tutorial emphasizes careful planning over rapid, untested deployment strategies.
Making Snowflake Openflow Tutorial 10x Faster
Achieving significant performance gains often comes from optimizing the underlying compute resources utilized. Therefore, select the precise warehouse size that is appropriate for your expected ingestion volume. Never oversize your compute for small, frequent, low-volume loads unnecessarily.
Moreover, utilize powerful Snowpipe Streaming alongside Openflow for handling very high-throughput real-time data ingestion needs. Openflow effectively manages the pipeline state, orchestration, and transformation layers easily. This combination provides both high speed and reliable control.
Consider optimizing your transformation SQL embedded within the pipeline steps themselves. Use features like clustered tables and materialized views aggressively for achieving blazing fast lookups. By applying these specific tuning concepts, your subsequent snowflake openflow tutorial practices will be significantly more performant and cost-effective.
-- Adjust the Warehouse size for a specific running pipeline
ALTER OPENFLOW PIPELINE my_realtime_pipeline
SET WAREHOUSE = 'OPENFLOW_WH_MEDIUM';
-- Optimization for transformation layer
CREATE MATERIALIZED VIEW mv_customer_lookup AS
SELECT customer_id, region FROM CUSTOMERS_DIM WHERE region = 'EAST'
CLUSTER BY (customer_id);
Observability Strategies for Snowflake Openflow Tutorial
Achieving strong observability is absolutely paramount for maintaining reliable data pipelines efficiently. Consequently, Openflow provides powerful native views for accessing detailed metrics and historical logging immediately. Use the standard INFORMATION_SCHEMA diligently for auditing performance metrics thoroughly and accurately.
Furthermore, set up custom alerts based on crucial latency metrics or defined failure thresholds. Snowflake Task history provides excellent, detailed lineage tracing capabilities easily accessible through SQL queries. Integrate these mission-critical alerts with external monitoring systems like Datadog or PagerDuty if necessary.
You must rigorously define clear Service Level Agreements (SLAs) for all your production Openflow pipelines immediately. Therefore, monitoring ingestion latency and error rates becomes a critical, daily operational activity. This final section of the snowflake openflow tutorial focuses intensely on achieving true operational excellence.
-- Querying the status of the Openflow pipeline execution
SELECT
pipeline_name,
execution_start_time,
execution_status,
rows_processed
FROM
TABLE(INFORMATION_SCHEMA.OPENFLOW_PIPELINE_HISTORY(
'MY_FIRST_OPENFLOW',
date_range_start => DATEADD(HOUR, -24, CURRENT_TIMESTAMP()))
);
This comprehensive snowflake openflow tutorial guide prepares you for tackling complex Openflow challenges immediately. Master these robust concepts and revolutionize your entire data integration strategy starting today. Openflow represents a massive leap forward for data engineers globally.
Data Engineers today face immense pressure to deliver speed and efficiency. Optimizing snowflake performance is no longer a luxury; it is a fundamental requirement. Furthermore, mastering these concepts separates efficient teams from those struggling with runaway cloud costs. In this comprehensive handbook, we provide the 2025 deep dive into modern Snowflake optimization. Additionally, you will discover actionable SQL tuning techniques. Consequently, your data pipelines will operate faster and cheaper. Let us begin this detailed technical exploration.
Why Snowflake Performance Matters for Modern Teams
Cloud expenditure remains a chief concern for executive teams. Poorly optimized queries directly translate into high compute consumption. Therefore, understanding resource utilization is crucial for data engineering success. Furthermore, slow queries erode user trust in the data platform itself. A delayed dashboard means slower business decisions. Consequently, the organization loses competitive advantage quickly. We must treat optimization as a core engineering responsibility. Indeed, efficiency drives innovation in the modern data stack. Moreover, excellent snowflake performance directly impacts the bottom line. Teams must prioritize cost efficiency alongside speed. In fact, these two goals are inextricably linked.
The Hidden Cost of Inefficiency
Many organizations adopt the “set it and forget it” mentality. They run overly large warehouses for simple tasks. However, this approach leads to significant waste. Snowflake bills based purely on compute time utilized. Furthermore, inefficient SQL forces the warehouse to work harder and longer. Therefore, engineers must actively monitor usage patterns constantly. For instance, a complex query running hourly might cost thousands monthly. Additionally, fixing that query could save 80% of the compute time instantly. We advocate for proactive monitoring and continuous tuning. Consequently, teams maintain predictable and stable budgets. Clearly, performance tuning is a direct exercise in financial management.
Understanding Snowflake Performance Architecture
Achieving optimal snowflake performance requires understanding its unique architecture. Snowflake separates storage and compute resources completely. This separation offers incredible scalability and flexibility. Moreover, it introduces specific optimization challenges. The Virtual Warehouse handles all query execution. Conversely, the Cloud Services layer manages metadata and optimization. Therefore, tuning often involves balancing these two layers effectively. We must leverage the underlying structure for best results.
Snowflake stores data in immutable micro-partitions. These partitions are typically 50 MB to 500 MB in size. Furthermore, Snowflake automatically tracks metadata about the data within each partition. This metadata includes minimum and maximum values for columns.
Consequently, the query optimizer uses this information efficiently. It employs a technique called pruning. Pruning allows Snowflake to skip reading unnecessary data partitions instantly. For instance, if you query data for January, Snowflake only scans partitions containing January data. Moreover, effective pruning is the single most important factor for fast query execution. Therefore, good data layout is non-negotiable.
The Query Optimizer’s Role
The Cloud Services layer houses the sophisticated query optimizer. This optimizer analyzes the SQL statement before execution. Additionally, it determines the most efficient execution plan possible. It considers factors like available micro-partition data and join order. Furthermore, it decides which parts of the query can be executed in parallel. Therefore, writing clear, standard SQL helps the optimizer immensely. However, sometimes the optimizer needs assistance. We use tools like the EXPLAIN plan to inspect its choices. Subsequently, we adjust SQL or data structure based on the plan’s feedback.
Setting Up Optimal Snowflake Performance: A Deep Dive into Warehouse Costs
Warehouse sizing is the most critical factor affecting immediate cost and speed. Snowflake uses T-shirt sizes (XS, S, M, L, XL, etc.) for warehouses. Importantly, doubling the size doubles the computing power. Consequently, doubling the size also doubles the credits consumed per hour. Therefore, selecting the correct size requires careful calculation.
Right-Sizing Your Compute
Engineers often default to larger warehouses “just in case.” However, this practice wastes significant funds immediately. We must align the warehouse size with the workload complexity. For instance, small ETL jobs or dashboard queries often fit perfectly on an XS or S warehouse. Conversely, massive data ingestion or complex machine learning training might require an L or XL. Furthermore, remember that larger warehouses reduce latency only up to a certain point. Subsequently, data spillover or poor query design becomes the bottleneck. We recommend starting small and scaling up only when necessary. Clearly, monitoring warehouse saturation helps guide this decision.
Auto-Suspend and Auto-Resume Features
The auto-suspend feature is mandatory for cost control. This setting automatically pauses the warehouse after a period of inactivity. Consequently, the organization stops accruing compute costs instantly. Furthermore, we recommend setting the auto-suspend timer aggressively low. Five to ten minutes is usually ideal for interactive workloads. Conversely, ETL pipelines should use the auto-suspend feature immediately upon completion. We must ensure queries execute and then relinquish the resources quickly. Additionally, auto-resume ensures seamless operation when new queries arrive. Therefore, proper configuration prevents idle spending entirely.
Leveraging Multi-Cluster Warehouses
Multi-cluster warehouses solve concurrency challenges elegantly. A single warehouse cluster struggles under high simultaneous load. Consequently, users experience query queuing and delays. However, a multi-cluster warehouse automatically spins up additional clusters. This action handles the extra load immediately. We set minimum and maximum cluster counts based on expected concurrency. Furthermore, we select the scaling policy carefully. For instance, the “Economy” mode saves costs but might delay peak demand queries slightly. Conversely, the “Standard” mode provides immediate scaling but at a higher potential cost. Therefore, we must balance user experience against the financial constraints.
Advanced SQL Tuning for Maximum Throughput
SQL optimization is paramount for achieving best-in-class snowflake performance. Even with perfect warehouse configuration, bad SQL will fail. We focus intensely on reducing the volume of data scanned and processed. This approach yields the greatest performance gains instantly.
Effective Use of Clustering Keys
Snowflake automatically clusters data upon ingestion. However, the initial clustering might not align with common query patterns. We define clustering keys on very large tables (multi-terabyte) frequently accessed. Furthermore, clustering keys organize data physically on disk based on the specified columns. Consequently, the system prunes irrelevant micro-partitions even more efficiently. For instance, if users always filter by customer_id and transaction_date, these columns should form the key. We monitor the clustering depth metric regularly. Additionally, we use the ALTER TABLE RECLUSTER command only when necessary. Indeed, reclustering consumes credits, so we must use it judiciously.
Materialized Views vs. Standard Views
Materialized views (MVs) pre-compute and store the results of complex queries. They drastically reduce latency for repetitive, costly aggregations. For instance, daily sales reports often benefit from MVs immediately. However, MVs incur maintenance costs; Snowflake automatically refreshes them when the underlying data changes. Consequently, frequent updates on the base tables increase MV maintenance time and cost. Therefore, we reserve MVs for static, large datasets where the read-to-write ratio is extremely high. Conversely, standard views simply store the query definition. Standard views require no maintenance but execute the underlying query every time.
Avoiding Anti-Patterns: Joins and Subqueries
Inefficient joins are notorious performance killers. We must always use explicit INNER JOIN or LEFT JOIN syntax. Furthermore, we must avoid Cartesian joins entirely; these joins multiply rows exponentially and crash performance. Additionally, we ensure the join columns are of compatible data types. Mismatched types prevent the optimizer from using efficient hash joins. Moreover, correlated subqueries significantly slow down execution. Correlated subqueries execute once per row of the outer query. Therefore, we often rewrite correlated subqueries as standard joins or window functions.
Common Mistakes and Performance Bottlenecks
In fact, window functions often provide cleaner, faster solutions for aggregation problems.Even experienced Data Engineers make common mistakes in Snowflake environments. Recognizing these pitfalls allows for proactive prevention. We must enforce coding standards to minimize these errors.
The Dangers of Full Table Scans
A full table scan means the query reads every single micro-partition. This action completely bypasses the pruning mechanism. Consequently, query time and compute cost skyrocket immediately. Full scans usually occur when filters use functions on columns. For instance, filtering on TO_DATE(date_column) prevents pruning. The optimizer cannot use the raw metadata efficiently. Therefore, we must move the function application to the literal value instead. We write date_column = ‘2025-01-01’::DATE instead of wrapping the column in a function. Furthermore, missing WHERE clauses also trigger full scans.
Managing Data Spillover
Obviously, defining restrictive filters is essential for efficient querying. Data spillover occurs when the working set of data exceeds the memory available in the virtual warehouse. Snowflake handles this by spilling data to local disk and then to remote storage. However, I/O operations drastically slow down processing time. Consequently, queries that spill heavily are extremely expensive and slow. We identify spillover through the Query Profile analysis tool. Therefore, two primary solutions exist: increasing the warehouse size temporarily, or rewriting the query. For instance, large sorts or complex aggregations often cause spillover. Furthermore, we optimize the query to minimize sorting or aggregation steps.
Ignoring the Query Profile
Indeed, rewriting is always preferable to simply throwing more compute power at the problem.The Query Profile is the most important tool for snowflake performance tuning. It provides a visual breakdown of query execution. Furthermore, it shows exactly where time is spent: in scanning, joining, or sorting. Many engineers simply look at the total execution time. However, ignoring the profile means ignoring the root cause of the delay. We actively teach teams how to interpret the profile. Look for high percentages in “Local Disk I/O” or “Remote Disk I/O” (spillover). Additionally, look for disproportionate time spent on specific join nodes. Subsequently, address the identified bottleneck directly.
Production Best Practices and Monitoring
Clearly, consistent profile review drives continuous improvement. Optimization is not a one-time event; it is a continuous process. Production environments require robust monitoring and governance. We establish clear standards for resource usage and query complexity.
Implementing Resource Monitors
This proactive stance ensures long-term efficiency. Resource monitors prevent unexpected spending spikes efficiently. They allow Data Engineers to set credit limits per virtual warehouse or account. Furthermore, they define actions to take when limits are approached. For instance, we can set up notifications at 75% usage. Subsequently, we suspend the warehouse completely at 100% usage. Therefore, resource monitors act as a crucial safety net for budget control. We recommend setting monthly or daily limits based on workload predictability. Additionally, review the limits quarterly to account for growth.
Using Query Tagging
Indeed, preventative measures save significant money. Query tagging provides invaluable visibility into usage patterns. We tag queries based on their origin: ETL, BI tool, ad-hoc analysis, etc. Furthermore, this metadata allows for precise cost allocation and performance analysis. For instance, we can easily identify which BI dashboard consumes the most credits. Consequently, we prioritize the tuning efforts where they deliver the highest ROI. We enforce tagging standards through automated pipelines. Therefore, all executed SQL must carry relevant context information.
This practice helps us manage overall snowflake performance effectively. Ingestion methods significantly impact the final data layout and query speed. We recommend using the COPY INTO command for bulk loading. Furthermore, always load files in optimally sized batches. Smaller, more numerous files lead to metadata overhead. Conversely, extremely large files hinder parallel processing and micro-partitioning efficiency. We aim for file sizes between 100 MB and 250 MB. Additionally, use the VALIDATE option during loading for error checking. Subsequently, ensure data is loaded in the order it will typically be queried. This improves initial clustering and pruning performance immediately.
Conclusion: Sustaining Superior Snowflake Performance
Thus, efficient ingestion sets the stage for fast retrieval. Mastering snowflake performance is an ongoing journey for any modern Data Engineer. We covered architectural fundamentals and advanced SQL tuning techniques. Furthermore, we emphasized the critical link between cost control and efficiency. Continuous monitoring and proactive optimization are essential practices. Therefore, integrate Query Profile reviews into your standard deployment workflow. Additionally, regularly right-size your warehouses based on observed usage. Consequently, your organization will benefit from faster insights and lower cloud expenditure. We encourage you to apply these 2025 best practices immediately. Indeed, stellar performance is achievable with discipline and expertise.
Run dbt Core Directly in Snowflake Without Infrastructure
Snowflake native dbt integration announced at Summit 2025 eliminates the need for separate containers or VMs to run dbt Core. Data teams can now execute dbt transformations directly within Snowflake, with built-in lineage tracking, logging, and job scheduling through Snowsight. This breakthrough simplifies data pipeline architecture and reduces operational overhead significantly.
For years, running dbt meant managing separate infrastructure—deploying containers, configuring CI/CD pipelines, and maintaining compute resources outside your data warehouse. The Snowflake native dbt integration changes everything by bringing dbt Core execution inside Snowflake’s secure environment.
What Is Snowflake Native dbt Integration?
Snowflake native dbt integration allows data teams to run dbt Core transformations directly within Snowflake without external orchestration tools. The integration provides a managed environment where dbt projects execute using Snowflake’s compute resources, with full visibility through Snowsight.
Key Benefits
The native integration delivers:
Zero infrastructure management – No containers, VMs, or separate compute
Built-in lineage tracking – Automatic data flow visualization
Native job scheduling – Schedule dbt runs using Snowflake Tasks
Integrated logging – Debug pipelines directly in Snowsight
No licensing costs – dbt Core runs free within Snowflake
Organizations using Snowflake Dynamic Tables can now complement those automated refreshes with sophisticated dbt transformations, creating comprehensive data pipeline solutions entirely within the Snowflake ecosystem.
How Native dbt Integration Works
Execution Architecture
When you deploy a dbt project to Snowflake native dbt integration, the platform:
Stores project files in Snowflake’s internal stage
Compiles dbt models using Snowflake’s compute
Executes SQL transformations against your data
Captures lineage automatically for all dependencies
Logs results to Snowsight for debugging
Similar to how real-time data pipeline architectures require proper orchestration, dbt projects benefit from Snowflake’s native task scheduling and dependency management.
-- Create a dbt job in Snowflake
CREATE OR REPLACE TASK run_dbt_models
WAREHOUSE = transform_wh
SCHEDULE = 'USING CRON 0 2 * * * America/Los_Angeles'
AS
CALL DBT.RUN_DBT_PROJECT('my_analytics_project');
-- Enable the task
ALTER TASK run_dbt_models RESUME;
Setting Up Native dbt Integration
Prerequisites
Before deploying dbt projects natively:
Snowflake account with ACCOUNTADMIN or appropriate role
Existing dbt project with proper structure
Git repository containing dbt code (optional but recommended)
Step-by-Step Implementation
1: Prepare Your dbt Project
Ensure your project follows standard dbt structure:
Improved security (execution stays within Snowflake perimeter)
Better integration with Snowflake features
Cost Considerations
Compute Consumption
Snowflake native dbt integration uses standard warehouse compute:
Charged per second of active execution
Auto-suspend reduces idle costs
Share warehouses across multiple jobs for efficiency
Comparison with External Solutions
Aspect
External dbt
Native dbt Integration
Infrastructure
EC2/VM costs
Only Snowflake compute
Maintenance
Manual updates
Managed by Snowflake
Licensing
dbt Cloud fees
Free (dbt Core)
Integration
External APIs
Native Snowflake
Organizations using automation strategies across their data stack can consolidate tools and reduce total cost of ownership.
Real-World Use Cases
Use Case 1: Financial Services Reporting
A fintech company moved 200+ dbt models from AWS containers to Snowflake native dbt integration, achieving:
60% reduction in infrastructure costs
40% faster transformation execution
Zero downtime migrations using blue-green deployment
Use Case 2: E-commerce Analytics
An online retailer consolidated their data pipeline by combining native dbt with Dynamic Tables:
dbt handles complex business logic transformations
Dynamic Tables maintain real-time aggregations
Both execute entirely within Snowflake
Use Case 3: Healthcare Data Warehousing
A healthcare provider simplified compliance by keeping all transformations inside Snowflake’s secure perimeter:
HIPAA compliance maintained without data egress
Audit logs automatically captured
PHI never leaves Snowflake environment
Advanced Features
Git Integration
Connect dbt projects directly to repositories:
CREATE GIT REPOSITORY dbt_repo
ORIGIN = 'https://github.com/myorg/dbt-project.git'
API_INTEGRATION = github_integration;
-- Run dbt from specific branch
CALL run_dbt_from_git('dbt_repo', 'production');
Testing and Validation
Native integration supports full dbt testing:
Schema tests validate data structure
Data tests check business rules
Custom tests enforce specific requirements
Multi-Environment Support
Manage dev, staging, and production through Snowflake databases:
sql
-- Development environment
USE DATABASE dev_analytics;
CALL run_dbt('dev_project');
-- Production environment
USE DATABASE prod_analytics;
CALL run_dbt('prod_project');
Troubleshooting Common Issues
Issue 1: Slow Model Compilation
Solution: Pre-compile dbt projects and cache results:
sql
-- Cache compiled SQL for faster execution
ALTER TASK dbt_refresh SET
SUSPEND_TASK_AFTER_NUM_FAILURES = 3;
Issue 2: Dependency Conflicts
Solution: Use Snowflake’s Python environment isolation:
Snowflake plans to enhance native dbt integration with:
Visual dbt model builder for low-code transformations
Automatic optimization suggestions using AI
Enhanced collaboration features for team workflows
Deeper integration with Snowflake’s AI capabilities
Organizations exploring autonomous AI agents in other platforms will find similar intelligence coming to dbt optimization.
Conclusion: Simplified Data Transformation
Snowflake native dbt integration represents a significant evolution in data transformation architecture. By eliminating external infrastructure and bringing dbt Core inside Snowflake, data teams achieve simplified operations, reduced costs, and enhanced security.
The integration is production-ready today, with thousands of organizations already migrating their dbt workloads. Teams should evaluate their current dbt architecture and plan migrations to take advantage of this native capability.
Start with non-critical projects, validate performance, and progressively move production workloads. The combination of zero infrastructure overhead, built-in observability, and seamless Snowflake integration makes native dbt integration the future of transformation pipelines.
The era of AI in CRM is here, and its name is Salesforce Copilot. It’s more than just a chatbot that answers questions; in fact,it’s an intelligent assistant designed to take action. But its true power is unlocked when you teach it to perform custom tasks specific to your business.
Ultimately, this guide will walk you through the entire process of building your very first custom Salesforce Copilot action. We’ll create a practical tool that allows a user to summarize a complex support Case and post that summary to Chatter with a single command.
Understanding the Core Concepts of Salesforce Copilot
First, What is a Copilot Action?
A Copilot Action is, in essence, a custom skill you give to your Salesforce Copilot. It connects a user’s natural language request to a specific automation built on the Salesforce platform, usually using a Salesforce Flow.
To see how this works, think of the following sequence:
1. To begin, a user gives a command like, “Summarize this case for me and share an update.”
2. Salesforce Copilot then immediately recognizes the user’s intent.
3. This recognition subsequently triggers the specific Copilot Action you built.
4. Finally, the Flow connected to that action runs all the necessary logic, such as calling Apex, getting the summary, and posting the result to Chatter.
Our Project Goal: The Automated Case Summary Action
Our goal is to build a Salesforce Copilot action that can be triggered from a Case record page. To achieve this, our action will perform three key steps:
1. It will read the details of the current Case.
2. Next, the action will use AI to generate a concise summary.
3. Lastly, it will post that summary to the Case’s Chatter feed for team visibility.
Although you can do a lot in Flow, complex logic is often best handled in Apex. Therefore, we’ll start by creating a simple Apex method that takes a Case ID and returns its Subject and Description, which the Flow can then call.
The CaseSummarizer Apex Class
// Apex Class: CaseSummarizer
public with sharing class CaseSummarizer {
// Invocable Method allows this to be called from a Flow
@InvocableMethod(label='Get Case Details for Summary' description='Returns the subject and description of a given Case ID.')
public static List<CaseDetails> getCaseDetails(List<Id> caseIds) {
Id caseId = caseIds[0]; // We only expect one ID
Case thisCase = [SELECT Subject, Description FROM Case WHERE Id = :caseId LIMIT 1];
// Prepare the output for the Flow
CaseDetails details = new CaseDetails();
details.caseSubject = thisCase.Subject;
details.caseDescription = thisCase.Description;
return new List<CaseDetails>{ details };
}
// A wrapper class to hold the output variables for the Flow
public class CaseDetails {
@InvocableVariable(label='Case Subject' description='The subject of the case')
public String caseSubject;
@InvocableVariable(label='Case Description' description='The description of the case')
public String caseDescription;
}
}
After creating the Apex logic, we’ll build an Autolaunched Flow that orchestrates the entire process from start to finish.
Flow Configuration
Go to Setup > Flows and create a new Autolaunched Flow.
For this purpose, define an input variable: recordId (Text, Available for Input). This, in turn, will receive the Case ID.
Add an Action element: Call the getCaseDetails Apex method we just created, passing the recordId as the caseIds input.
Store the outputs: Store the caseSubject and caseDescription in new variables within the Flow.
Add a “Post to Chatter” Action:
Message: This is where we bring in AI. We’ll use a Prompt Template here soon, but for now, you can put placeholder text like {!caseSubject}.
Target Name or ID: Set this to {!recordId} to post on the current Case record.
Save and activate the Flow (e.g., as “Post Case Summary to Chatter”).
Step 3: Teaching the AI with a Prompt Template
Furthermore, this step tells the LLM how to generate the summary.
Prompt Builder Setup
Go to Setup > Prompt Builder.
Create a new Prompt Template.
For the prompt, write instructions for the AI. Specifically, use merge fields to bring in your Flow variables.
You are a helpful support team assistant.
Based on the following Case details, write a concise, bulleted summary to be shared with the internal team on Chatter.
Case Subject: {!caseSubject}
Case Description: {!caseDescription}
Summary:
4. Save the prompt (e.g., “Case Summary Prompt”).
Step 4: Connecting Everything with a Copilot Action
Now, this is the crucial step where we tie everything together.
Action Creation
Go to Setup > Copilot Actions.
Click New Action.
Select Salesforce Flow as the action type and choose the Flow you created (“Post Case Summary to Chatter”).
Instead of using a plain text value for the “Message” in your Post to Chatter action, select your “Case Summary Prompt” template.
Follow the prompts to define the language and behavior. For instance, for the prompt, you can use something like: “Summarize the current case and post it to Chatter.”
Activate the Action.
Step 5: Putting Your Copilot Action to the Test
Finally, navigate to any Case record. Open the Salesforce Copilot panel and type your command: “Summarize this case for me.”
Once you issue the command, the magic happens. Specifically, the Copilot will understand your intent, trigger the action, run the Flow, call the Apex, generate the summary using the Prompt Template, and post the final result directly to the Chatter feed on that Case.
Conclusion: The Future of CRM is Action-Oriented
In conclusion, you have successfully built a custom skill for your Salesforce Copilot. This represents a monumental shift from passive data entry to proactive, AI-driven automation. Indeed, by combining the power of Flow, Apex, and the Prompt Builder, you can create sophisticated agents that understand your business and work alongside your team to drive incredible efficiency.
The age of AI chatbots is evolving into the era of AI doers. Instead of just answering questions, modern AI can now execute tasks, interact with systems, and solve multi-step problems. At the forefront of this revolution on the Databricks platform is the Mosaic AI Agent Framework.
This guide will walk you through building your first Databricks AI Agent—a powerful assistant that can understand natural language, inspect your data, and execute Spark SQL queries for you, all powered by the latest GPT-5 model.
What is a Databricks AI Agent?
A Databricks AI Agent is an autonomous system you create using the Mosaic AI Agent Framework. It leverages a powerful Large Language Model (LLM) as its “brain” to reason and make decisions. You equip this brain with a set of “tools” (custom Python functions) that allow it to interact with the Databricks environment.
The agent works in a loop:
Reason: Based on your goal, the LLM decides which tool is needed.
Act: The agent executes the chosen Python function.
Observe: It analyzes the result of that function.
Repeat: It continues this process until it has achieved the final objective.
Our Project: The “Data Analyst” Agent
We will build an agent whose goal is to answer data questions from a non-technical user. To do this, it will need two primary tools:
A tool to get the schema of a table (get_table_schema).
A tool to execute a Spark SQL query and return the result (run_spark_sql).
Let’s start building in a Databricks Notebook.
Step 1: Setting Up Your Tools (Python Functions)
An agent’s capabilities are defined by its tools. In Databricks, these are simply Python functions. Let’s define the two functions our agent needs to do its job.
# Tool #1: A function to get the DDL schema of a table
def get_table_schema(table_name: str) -> str:
"""
Returns the DDL schema for a given Spark table name.
This helps the agent understand the table structure before writing a query.
"""
try:
ddl_result = spark.sql(f"SHOW CREATE TABLE {table_name}").first()[0]
return ddl_result
except Exception as e:
return f"Error: Could not retrieve schema for table {table_name}. Reason: {e}"
# Tool #2: A function to execute a Spark SQL query and return the result as a string
def run_spark_sql(query: str) -> str:
"""
Executes a Spark SQL query and returns the result.
This is the agent's primary tool for interacting with data.
"""
try:
result_df = spark.sql(query)
# Convert the result to a string format for the LLM to understand
return result_df.toPandas().to_string()
except Exception as e:
return f"Error: Failed to execute query. Reason: {e}"
Step 2: Assembling Your Databricks AI Agent
With our tools defined, we can now use the Mosaic AI Agent Framework to create our agent. This involves importing the Agent class, providing our tools, and selecting an LLM from Model Serving.
For this example, we’ll use the newly available openai/gpt-5model endpoint.
from databricks_agents import Agent
# Define the instructions for the agent's "brain"
# This prompt guides the agent on how to behave and use its tools
agent_instructions = """
You are a world-class data analyst. Your goal is to answer user questions by querying data in Spark.
Here is your plan:
1. First, you MUST use the `get_table_schema` tool to understand the columns of the table the user mentions. Do not guess column names.
2. After you have the schema, formulate a Spark SQL query to answer the user's question.
3. Execute the query using the `run_spark_sql` tool.
4. Finally, analyze the result from the query and provide a clear, natural language answer to the user. Do not just return the raw data table. Summarize the findings.
"""
# Create the agent instance
data_analyst_agent = Agent(
model="endpoints:/openai-gpt-5", # Using a Databricks Model Serving endpoint for GPT-5
tools=[get_table_schema, run_spark_sql],
instructions=agent_instructions
)
Step 3: Interacting with Your Agent
Your Databricks AI Agent is now ready. You can interact with it using the .run() method, providing your question as the input.
Let’s use the common samples.nyctaxi.trips table.
# Let's ask our new agent a question
user_question = "What were the average trip distances for trips paid with cash vs. credit card? Use the samples.nyctaxi.trips table."
# Run the agent and get the final answer
final_answer = data_analyst_agent.run(user_question)
print(final_answer)
What Happens Behind the Scenes:
Reason: The agent reads your prompt. It knows it needs to find average trip distances from the samples.nyctaxi.trips table but first needs the schema. It decides to use the get_table_schema tool.
Act: It calls get_table_schema('samples.nyctaxi.trips').
Observe: It receives the table schema and sees columns like trip_distance and payment_type.
Reason: Now it has the schema. It formulates a Spark SQL query like SELECT payment_type, AVG(trip_distance) FROM samples.nyctaxi.trips GROUP BY payment_type. It decides to use the run_spark_sql tool.
Act: It calls run_spark_sql(...) with the generated query.
Observe: It receives the query result as a string (e.g., a small table showing payment types and average distances).
Reason: It has the data. Its final instruction is to summarize the findings.
Final Answer: It generates and returns a human-readable response like: “Based on the data, the average trip distance for trips paid with a credit card was 2.95 miles, while cash-paid trips had an average distance of 2.78 miles.”
Conclusion: Your Gateway to Autonomous Data Tasks
Congratulations! You’ve just built a functional Databricks AI Agent. This simple text-to-SQL prototype is just the beginning. By creating more sophisticated tools, you can build agents that perform data quality checks, manage ETL pipelines, or even automate MLOps workflows, all through natural language commands on the Databricks platform.
Autonomous AI Agents That Transform Customer Engagement
Salesforce Agentforce represents the most significant CRM innovation of 2025, marking the shift from generative AI to truly autonomous agents. Unveiled at Dreamforce 2024, Salesforce Agentforce enables businesses to deploy AI agents that work independently, handling customer inquiries, resolving support tickets, and qualifying leads without human intervention. This comprehensive guide explores how enterprises leverage these intelligent agents to revolutionize customer relationships and operational efficiency.
Traditional chatbots require constant supervision and predefined scripts. Salesforce Agentforce changes everything by introducing agents that reason, plan, and execute tasks autonomously across your entire CRM ecosystem.
What Is Salesforce Agentforce?
Salesforce Agentforce is an advanced AI platform that creates autonomous agents capable of performing complex business tasks across sales, service, marketing, and commerce. Unlike traditional automation tools, these agents understand context, make decisions, and take actions based on your company’s data and business rules.
Core Capabilities
The platform enables agents to:
Resolve customer inquiries autonomously across multiple channels
Qualify and prioritize leads using predictive analytics
Generate personalized responses based on customer history
Execute multi-step workflows without human intervention
Learn from interactions to improve performance over time
Real-world impact: Companies using Salesforce Agentforce report 58% success rates on simple tasks and 35% on complex multi-step processes, significantly reducing response times and operational costs.
Key Features of Agentforce AI
xGen Sales Model
The xGen Sales AI model enhances predictive analytics for sales teams. It accurately forecasts revenue, prioritizes high-value leads, and provides intelligent recommendations that help close deals faster. Sales representatives receive real-time guidance on which prospects to contact and what messaging will resonate.
xLAM Service Model
Designed for complex service workflows, xLAM automates ticket resolution, manages customer inquiries, and predicts service disruptions before they escalate. The model analyzes historical patterns to prevent issues proactively rather than reactively addressing complaints.
Agent Builder
The low-code Agent Builder empowers business users to create custom agents without extensive technical knowledge. Using natural language descriptions, teams can define agent behaviors, set guardrails, and deploy solutions in days rather than months.
How Agentforce Works with Data Cloud
Salesforce Agentforce leverages Data Cloud to access unified customer data across all touchpoints. This integration is critical because AI agents need comprehensive context to make informed decisions.
Unified Data Access
Agents retrieve information from:
Customer relationship history
Purchase patterns and preferences
Support interaction logs
Marketing engagement metrics
Real-time behavioral data
Retrieval Augmented Generation (RAG)
The platform uses RAG technology to extract relevant information from multiple internal systems. This ensures agents provide accurate, contextual responses grounded in your organization’s actual data rather than generic outputs.
Why this matters: 80% of enterprise data is unstructured. Data Cloud harmonizes this information, making it accessible to autonomous agents for better decision-making.
Real-World Use Cases
Use Case 1: Autonomous Customer Service
E-commerce companies deploy service agents that handle common inquiries 24/7. When customers ask about order status, return policies, or product recommendations, agents provide instant, accurate responses by accessing order management systems and customer profiles.
Business impact: Reduces support ticket volume by 40-60% while maintaining customer satisfaction scores.
Use Case 2: Intelligent Lead Qualification
Sales agents automatically engage with website visitors, qualify leads based on predefined criteria, and route high-value prospects to human representatives. The agent asks qualifying questions, scores responses, and updates CRM records in real-time.
Business impact: Sales teams focus on ready-to-buy prospects, increasing conversion rates by 25-35%.
Use Case 3: Proactive Service Management
Service agents monitor system health metrics and customer usage patterns. When potential issues are detected, agents automatically create support tickets, notify relevant teams, and even initiate preventive maintenance workflows.
Business impact: Prevents service disruptions, improving customer retention and reducing emergency support costs.
Getting Started with Agentforce
Step 1: Define Your Use Case
Start with a specific, high-volume process that’s currently manual. Common starting points include:
Customer inquiry responses
Lead qualification workflows
Order status updates
Appointment scheduling
Step 2: Prepare Your Data
Ensure Data Cloud has access to relevant information sources:
CRM data (accounts, contacts, opportunities)
Service Cloud data (cases, knowledge articles)
Commerce Cloud data (orders, products, inventory)
External systems (ERP, marketing automation)
Step 3: Build and Train Your Agent
Use Agent Builder to:
Describe agent purpose and scope
Define decision-making rules
Set guardrails and escalation paths
Test with sample scenarios
Deploy to production with monitoring
Step 4: Monitor and Optimize
Track agent performance using built-in analytics:
Task completion rates
Customer satisfaction scores
Escalation frequency
Resolution time metrics
Continuously refine agent instructions based on performance data and user feedback.
Best Practices for Implementation
Start Small and Scale
Begin with a single, well-defined use case. Prove value before expanding to additional processes. This approach builds organizational confidence and allows teams to learn agent management incrementally.
Establish Clear Guardrails
Define when agents should escalate to humans:
Complex negotiations requiring judgment
Sensitive customer situations
Requests outside defined scope
Regulatory compliance scenarios
Maintain Human Oversight
While agents work autonomously, human supervision remains important during early deployments. Review agent decisions, refine instructions, and ensure quality standards are maintained.
Invest in Data Quality
Agent performance depends directly on data accuracy and completeness. Prioritize data cleansing, deduplication, and enrichment initiatives before deploying autonomous agents.
Pricing and Licensing
Salesforce Agentforce pricing follows a conversation-based model:
Charged per customer interaction
Volume discounts available
Enterprise and unlimited editions include base conversations
Additional conversation packs can be purchased
Organizations should evaluate expected interaction volumes and compare costs against manual handling expenses to calculate ROI.
Integration with Existing Salesforce Tools
Einstein AI Integration
Agentforce builds on Einstein AI capabilities, leveraging existing predictive models and analytics. Organizations with Einstein implementations can extend those investments into autonomous agent scenarios.
Slack Integration
Agents operate within Slack channels, enabling teams to monitor agent activities, intervene when necessary, and maintain visibility into customer interactions directly in collaboration tools.
MuleSoft Connectivity
For enterprises with complex system landscapes, MuleSoft provides pre-built connectors that allow agents to interact with external applications, databases, and legacy systems seamlessly.
Future of Autonomous Agents
Multi-Agent Collaboration
The 2025 roadmap includes enhanced multi-agent orchestration where specialized agents collaborate on complex tasks. For example, a sales agent might work with a finance agent to create custom pricing proposals automatically.
Industry-Specific Agents
Salesforce is developing pre-configured agents for specific industries:
Financial Services: Compliance checking and risk assessment
Healthcare: Patient engagement and appointment optimization
Retail: Inventory management and personalized shopping assistance
Manufacturing: Supply chain coordination and quality control
Continuous Learning Capabilities
Future releases will enable agents to learn from every interaction, automatically improving responses and decision-making without manual retraining.
Common Challenges and Solutions
Challenge 1: Trust and Adoption
Solution: Start with low-risk use cases, maintain transparency about agent involvement, and demonstrate value through metrics before expanding scope.
Challenge 2: Data Silos
Solution: Implement Data Cloud to unify information across systems, ensuring agents have comprehensive context for decision-making.
Challenge 3: Over-Automation
Solution: Maintain balanced automation by defining clear escalation paths and preserving human touchpoints for high-value or sensitive interactions.
Conclusion: Embracing Autonomous AI
Salesforce Agentforce represents a fundamental shift in how businesses automate customer engagement. By moving beyond simple chatbots to truly autonomous agents, organizations can scale personalized service while reducing operational costs and improving customer satisfaction.
Success requires thoughtful implementation—starting with well-defined use cases, ensuring data quality, and maintaining appropriate human oversight. Companies that adopt this technology strategically will gain significant competitive advantages in efficiency, responsiveness, and customer experience.
The future of CRM automation is autonomous, intelligent, and available now through Salesforce Agentforce. Organizations ready to embrace this transformation should begin planning their agent strategy today.
When you think of aggregation functions in SQL, SUM(), COUNT(), and AVG() likely come to mind first. These are the workhorses of data analysis, undoubtedly. However, Snowflake, a titan in the data cloud, offers a treasure trove of specialized, unique aggregation functions that often fly under the radar. These functions aren’t just novelties; they are powerful tools that can simplify complex analytical problems and provide insights you might otherwise struggle to extract.
Let’s dive into some of Snowflake’s most potent, yet often overlooked, aggregation capabilities.
1. APPROX_TOP_K (and APPROX_TOP_K_ARRAY): Finding the Most Frequent Items Efficiently
Imagine you have billions of customer transactions and you need to quickly identify the top 10 most purchased products, or the top 5 most active users. A GROUP BY and ORDER BY on such a massive dataset can be resource-intensive. This is where APPROX_TOP_K shines.
This function provides an approximate list of the most frequent values in an expression. While not 100% precise (hence “approximate”), it offers a significantly faster and more resource-efficient way to get high-confidence results, especially on very large datasets.
Example Use Case: Top Products by Sales
Let’s use some sample sales data.
-- Create some sample sales data
CREATE OR REPLACE TABLE sales_data (
sale_id INT,
product_name VARCHAR(50),
customer_id INT
);
INSERT INTO sales_data VALUES
(1, 'Laptop', 101),
(2, 'Mouse', 102),
(3, 'Laptop', 103),
(4, 'Keyboard', 101),
(5, 'Mouse', 104),
(6, 'Laptop', 105),
(7, 'Monitor', 101),
(8, 'Laptop', 102),
(9, 'Mouse', 103),
(10, 'External SSD', 106);
-- Find the top 3 most frequently sold products using APPROX_TOP_K_ARRAY
SELECT APPROX_TOP_K_ARRAY(product_name, 3) AS top_3_products
FROM sales_data;
-- Expected Output:
-- [
-- { "VALUE": "Laptop", "COUNT": 4 },
-- { "VALUE": "Mouse", "COUNT": 3 },
-- { "VALUE": "Keyboard", "COUNT": 1 }
-- ]
APPROX_TOP_K returns a single JSON object, while APPROX_TOP_K_ARRAY returns an array of JSON objects, which is often more convenient for downstream processing.
2. MODE(): Identifying the Most Common Value Directly
Often, you need to find the value that appears most frequently within a group. While you could achieve this with GROUP BY, COUNT(), and QUALIFY ROW_NUMBER(), Snowflake simplifies it with a dedicated MODE() function.
Example Use Case: Most Common Payment Method by Region
Imagine you want to know which payment method is most popular in each sales region.
-- Sample transaction data
CREATE OR REPLACE TABLE transactions (
transaction_id INT,
region VARCHAR(50),
payment_method VARCHAR(50)
);
INSERT INTO transactions VALUES
(1, 'North', 'Credit Card'),
(2, 'North', 'Credit Card'),
(3, 'North', 'PayPal'),
(4, 'South', 'Cash'),
(5, 'South', 'Cash'),
(6, 'South', 'Credit Card'),
(7, 'East', 'Credit Card'),
(8, 'East', 'PayPal'),
(9, 'East', 'PayPal');
-- Find the mode of payment_method for each region
SELECT
region,
MODE(payment_method) AS most_common_payment_method
FROM
transactions
GROUP BY
region;
-- Expected Output:
-- REGION | MOST_COMMON_PAYMENT_METHOD
-- -------|--------------------------
-- North | Credit Card
-- South | Cash
-- East | PayPal
The MODE() function cleanly returns the most frequent non-NULL value. If there’s a tie, it can return any one of the tied values.
3. COLLECT_LIST() and COLLECT_SET(): Aggregating Values into Arrays
These functions are incredibly powerful for denormalization or when you need to gather all related items into a single, iterable structure within a column.
• COLLECT_LIST(): Returns an array of all input values, including duplicates, in an arbitrary order.
• COLLECT_SET(): Returns an array of all distinct input values, also in an arbitrary order.
Example Use Case: Customer Purchase History
You want to see all products a customer has ever purchased, aggregated into a single list.
-- Using the sales_data from above
-- Aggregate all products purchased by each customer
SELECT
customer_id,
COLLECT_LIST(product_name) AS all_products_purchased,
COLLECT_SET(product_name) AS distinct_products_purchased
FROM
sales_data
GROUP BY
customer_id
ORDER BY customer_id;
-- Expected Output (order of items in array may vary):
-- CUSTOMER_ID | ALL_PRODUCTS_PURCHASED | DISTINCT_PRODUCTS_PURCHASED
-- ------------|------------------------|---------------------------
-- 101 | ["Laptop", "Keyboard", "Monitor"] | ["Laptop", "Keyboard", "Monitor"]
-- 102 | ["Mouse", "Laptop"] | ["Mouse", "Laptop"]
-- 103 | ["Laptop", "Mouse"] | ["Laptop", "Mouse"]
-- 104 | ["Mouse"] | ["Mouse"]
-- 105 | ["Laptop"] | ["Laptop"]
-- 106 | ["External SSD"] | ["External SSD"]
These functions are game-changers for building semi-structured data points or preparing data for machine learning features.
4. SKEW() and KURTOSIS(): Advanced Statistical Insights
For data scientists and advanced analysts, understanding the shape of a data distribution is crucial. SKEW() and KURTOSIS() provide direct measures of this.
• SKEW(): Measures the asymmetry of the probability distribution of a real-valued random variable about its mean. A negative skew indicates the tail is on the left, a positive skew on the right.
• KURTOSIS(): Measures the “tailedness” of the probability distribution. High kurtosis means more extreme outliers (heavier tails), while low kurtosis means lighter tails.
Example Use Case: Analyzing Price Distribution
-- Sample product prices
CREATE OR REPLACE TABLE product_prices (
product_id INT,
price_usd DECIMAL(10, 2)
);
INSERT INTO product_prices VALUES
(1, 10.00), (2, 12.50), (3, 11.00), (4, 100.00), (5, 9.50),
(6, 11.20), (7, 10.80), (8, 9.90), (9, 13.00), (10, 10.50);
-- Calculate skewness and kurtosis for product prices
SELECT
SKEW(price_usd) AS price_skewness,
KURTOSIS(price_usd) AS price_kurtosis
FROM
product_prices;
-- Expected Output (values will vary based on data):
-- PRICE_SKEWNESS | PRICE_KURTOSIS
-- ---------------|----------------
-- 2.658... | 6.946...
This clearly shows a positive skew (the price of 100.00 is pulling the average up) and high kurtosis due to that outlier.
Conclusion: Unlock Deeper Insights with Snowflake Unique Aggregations
While the common aggregation functions are essential, mastering these Snowflake unique aggregations can elevate your analytical capabilities significantly. They empower you to solve complex problems more efficiently, prepare data for advanced use cases, and derive insights that might otherwise remain hidden. Don’t let these powerful tools gather dust; integrate them into your data analysis toolkit today.
The world of data is buzzing with the promise of Large Language Models (LLMs), but how do you move them from simple chat interfaces to intelligent actors that can do things? The answer is agents. This guide will show you how to build your very first Snowflake Agent in minutes, creating a powerful assistant that can understand your data and write its own SQL.
A Snowflake Agent is an advanced AI entity, powered by Snowflake Cortex, that you can instruct to complete complex tasks. Unlike a simple LLM call that just provides a text response, an agent can use a set of pre-defined “tools” to interact with its environment, observe the results, and decide on the next best action to achieve its goal.
Reason: The LLM thinks about the goal and decides which tool to use.
Act: It executes the chosen tool (like a SQL function).
Observe: It analyzes the output from the tool.
Repeat: It continues this loop until the final goal is accomplished.
Our Project: The “Text-to-SQL” Agent
We will build a Snowflake Agent with a clear goal: “Given a user’s question in plain English, write a valid SQL query against the correct table.”
To do this, our agent will need two tools:
A tool to look up the schema of a table.
A tool to draft a SQL query based on that schema.
Let’s get started!
Step 1: Create the Tools (SQL Functions)
An agent is only as good as its tools. In Snowflake, these tools are simply User-Defined Functions (UDFs). We’ll create two SQL functions that our agent can call.
First, a function to get the schema of any table. This allows the agent to understand the available columns.
-- Tool #1: A function to describe a table's schema
CREATE OR REPLACE FUNCTION get_table_schema(table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
SELECT GET_DDL('TABLE', table_name);
$$;
Second, we’ll create a function that uses SNOWFLAKE.CORTEX.COMPLETE to draft a SQL query. This function will take the user’s question and the table schema as context.
-- Tool #2: A function to write a SQL query based on a schema and a question
CREATE OR REPLACE FUNCTION write_sql_query(schema VARCHAR, question VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
SELECT SNOWFLAKE.CORTEX.COMPLETE(
'llama3-8b', -- Using a fast and efficient model
CONCAT(
'You are a SQL expert. Based on the following table schema and user question, write a single, valid SQL query. Do not add any explanation, just the code.\n\n',
'Schema:\n', schema, '\n\n',
'User Question:\n', question
)
)
$$;
With our tools ready, we can now assemble the agent itself.
Step 2: Create Your First Snowflake Agent
Creating the agent involves a single, declarative SQL command. Here, we define the agent, grant it access to our functions (tools), and give it its core objective using a prompt.
-- Now, we create the agent and give it the tools
CREATE OR REPLACE AGENT my_first_sql_agent
MODEL = 'llama3-70b' -- Use a powerful model for reasoning
FUNCTIONS = (get_table_schema, write_sql_query)
PROMPT =
$$
You are an expert SQL-writing agent. Your goal is to help users by writing a single SQL query that answers their question.
Here is your plan:
1. First, you MUST use the get_table_schema tool to find the columns of the table the user mentions.
2. Once you have the schema, you MUST use the write_sql_query tool to generate the final SQL.
3. Finally, output the generated SQL query as your final answer. Do not add any extra text or explanation.
$$;
And that’s it. Your Snowflake Agent is now live and ready for instructions.
Step 3: Chat with Your Agent
To interact with your agent, you use the CALL command. The agent will show its thought process as it uses the tools you provided.
Let’s assume you have the SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER table.
-- Let's test our new agent!
CALL my_first_sql_agent('Show me the names of all customers in the USA, table name is SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER');
Expected Agent Thought Process (the output you’ll see):
Thinking: The user wants customer names from the USA and has given me a table name. My first step is to get the schema of that table. I will call get_table_schema.
Observing Output: (The agent now sees the CREATE TABLE statement with columns like C_NAME, C_NATIONKEY, etc., and knows there’s a related NATION table for country names).
Thinking: Okay, I have the schema. The user wants names (C_NAME) where the nation is USA. I have all the information I need to write the query. I will now call write_sql_query.
Calling Tool:write_sql_query('<schema_output>', 'Show me the names of all customers in the USA')
Observing Output:SELECT c.c_name FROM customer c JOIN nation n ON c.c_nationkey = n.n_nationkey WHERE n.n_name = 'USA';
Final Answer: The agent outputs the generated SQL.
Conclusion: From Minutes to Mastery
You’ve just built a functional Snowflake Agent that can reason and act within your data cloud. This simple prototype is just the beginning. Imagine agents that can manage data quality, perform complex transformations, or even administer security, all through natural language commands. Welcome to the future of data interaction.
Revolutionary Declarative Data Pipelines That Transform ETL
In 2025,Snowflake Dynamic Tables have become the most powerful way to build automated data pipelines. This comprehensive guide covers everything from target lag configuration to incremental refresh strategies, with real-world examples showing how dynamic tables eliminate complex orchestration code and transform pipeline creation through simple SQL statements.
For years, building data pipelines meant wrestling with Streams, Tasks, complex scheduling logic, and dependency management. Dynamic tables changed everything. Now data engineers define the end state they want, and Snowflake handles all the orchestration automatically. The impact is remarkable: pipelines that previously required hundreds of lines of procedural code now need just a single CREATE DYNAMIC TABLE statement.
These tables automatically detect changes in base tables, incrementally update results, and maintain freshness targets—all without external orchestration tools. Leading enterprises use them to build production-ready pipelines processing billions of rows daily, achieving both faster development and lower operational costs.
What Are Snowflake Dynamic Tables and Why They Matter
Snowflake Dynamic Tables are specialized tables that automatically maintain query results through intelligent refresh processes. Unlike traditional tables that require manual updates, dynamic tables continuously monitor source data changes and update themselves based on defined freshness requirements.
Core Concept Explained
When you create a Snowflake Dynamic Table, you define a query that transforms data from base tables. Snowflake then takes full responsibility for refreshing the table, managing dependencies, and optimizing the refresh process. This declarative approach represents a fundamental shift from imperative pipeline coding.
The traditional approach:
sql
-- Old way: Manual orchestration with Streams and Tasks
CREATE STREAM sales_stream ON TABLE raw_sales;
CREATE TASK refresh_daily_sales
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('sales_stream')
AS
MERGE INTO daily_sales_summary dst
USING (
SELECT product_id,
DATE_TRUNC('day', sale_date) as day,
SUM(amount) as total_sales
FROM sales_stream
GROUP BY 1, 2
) src
ON dst.product_id = src.product_id
AND dst.day = src.day
WHEN MATCHED THEN UPDATE SET total_sales = src.total_sales
WHEN NOT MATCHED THEN INSERT VALUES (src.product_id, src.day, src.total_sales);
The Snowflake Dynamic Tables approach:
sql
-- New way: Simple declarative definition
CREATE DYNAMIC TABLE daily_sales_summary
TARGET_LAG = '5 minutes'
WAREHOUSE = compute_wh
AS
SELECT product_id,
DATE_TRUNC('day', sale_date) as day,
SUM(amount) as total_sales
FROM raw_sales
GROUP BY 1, 2;
The second approach achieves the same result with 80% less code and zero orchestration logic.
How Automated Refresh Works
Snowflake Dynamic Tables use a sophisticated two-step refresh process:
Step 1: Change Detection Snowflake analyzes the dynamic table’s query and creates a Directed Acyclic Graph (DAG) based on dependencies. Behind the scenes, Snowflake creates lightweight streams on base tables to capture change metadata (only ROW_ID, operation type, and timestamp—minimal storage cost).
Step 2: Incremental Merge Only detected changes are incorporated into the dynamic table. This incremental processing dramatically reduces compute consumption compared to full table refreshes. For queries that support it (most aggregations, joins, and filters), Snowflake automatically uses incremental mode.
Real-world example: A global retailer processes 50 million daily transactions. When 10,000 new orders arrive, their Snowflake Dynamic Table refreshes in seconds by processing only those 10,000 rows—not the entire 50 million row history.
Understanding Target Lag Configuration
Target lag defines how fresh your data needs to be. It’s the maximum acceptable delay between changes in base tables and their reflection in the dynamic table.
Target Lag Options and Trade-offs
sql
-- High freshness (low lag) for real-time dashboards
CREATE DYNAMIC TABLE real_time_metrics
TARGET_LAG = '1 minute'
WAREHOUSE = small_wh
AS SELECT * FROM live_events WHERE event_time > CURRENT_TIMESTAMP - INTERVAL '1 hour';
-- Moderate freshness for hourly reports
CREATE DYNAMIC TABLE hourly_summary
TARGET_LAG = '30 minutes'
WAREHOUSE = medium_wh
AS SELECT DATE_TRUNC('hour', ts) as hour, COUNT(*) FROM events GROUP BY 1;
-- Lower freshness (higher lag) for daily aggregates
CREATE DYNAMIC TABLE daily_rollup
TARGET_LAG = '6 hours'
WAREHOUSE = large_wh
AS SELECT DATE(ts) as day, SUM(revenue) FROM sales GROUP BY 1;
Trade-off considerations:
Lower target lag = More frequent refreshes = Higher compute costs = Fresher data
Higher target lag = Less frequent refreshes = Lower compute costs = Older data
Using DOWNSTREAM Lag for Pipeline DAGs
For pipeline DAGs with multiple Snowflake Dynamic Tables, use TARGET_LAG = DOWNSTREAM:
sql
-- Layer 1: Base transformation
CREATE DYNAMIC TABLE customer_events_cleaned
TARGET_LAG = DOWNSTREAM
WAREHOUSE = compute_wh
AS
SELECT customer_id, event_type, event_time
FROM raw_events
WHERE event_time IS NOT NULL;
-- Layer 2: Aggregation (defines the lag requirement)
CREATE DYNAMIC TABLE customer_daily_summary
TARGET_LAG = '15 minutes'
WAREHOUSE = compute_wh
AS
SELECT customer_id,
DATE(event_time) as day,
COUNT(*) as event_count
FROM customer_events_cleaned
GROUP BY 1, 2;
The upstream table (customer_events_cleaned) automatically inherits the 15-minute lag from its downstream consumer. This ensures the entire pipeline maintains consistent freshness without redundant configuration.
Comparing Dynamic Tables vs Streams and Tasks
Understanding when to use Dynamic Tables versus traditional Streams and Tasks is critical for optimal pipeline architecture.
When to Use Dynamic Tables
Choose Dynamic Tables when:
You need declarative, SQL-only transformations without procedural code
Your pipeline has straightforward dependencies that form a clear DAG
You want automatic incremental processing without manual merge logic
Time-based freshness (target lag) meets your requirements
You prefer Snowflake to automatically manage refresh scheduling
Your transformations involve standard SQL operations (joins, aggregations, filters)
Choose Streams and Tasks when:
You need fine-grained control over exact refresh timing
Your pipeline requires complex conditional logic beyond SQL
You need event-driven triggers from external systems
Your workflow involves cross-database operations or external API calls
You require custom error handling and retry logic
Your processing needs transaction boundaries across multiple steps
-- Complex multi-table join with aggregation
CREATE DYNAMIC TABLE customer_lifetime_value
TARGET_LAG = '1 hour'
WAREHOUSE = compute_wh
AS
SELECT
c.customer_id,
c.customer_name,
COUNT(DISTINCT o.order_id) as total_orders,
SUM(o.order_amount) as lifetime_value,
MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.customer_status = 'active'
GROUP BY 1, 2;
This query would be impossible in a materialized view but works perfectly in Dynamic Tables.
Incremental vs Full Refresh
Dynamic Tables automatically choose between incremental and full refresh modes based on your query patterns.
Understanding Refresh Modes
Incremental refresh (default for most queries):
Processes only changed rows since last refresh
Dramatically reduces compute costs
Works for most aggregations, joins, and filters
Requires deterministic queries
Full refresh (fallback for complex scenarios):
Reprocesses entire dataset on each refresh
Required for non-deterministic functions
Used when change tracking isn’t feasible
Higher compute consumption
sql
-- This uses incremental refresh automatically
CREATE DYNAMIC TABLE sales_by_region
TARGET_LAG = '10 minutes'
WAREHOUSE = compute_wh
AS
SELECT region,
SUM(sales_amount) as total_sales
FROM transactions
WHERE transaction_date >= '2025-01-01'
GROUP BY region;
-- This forces full refresh (non-deterministic function)
CREATE DYNAMIC TABLE random_sample_data
TARGET_LAG = '1 hour'
WAREHOUSE = compute_wh
REFRESH_MODE = FULL -- Explicitly set to FULL
AS
SELECT *
FROM large_dataset
WHERE RANDOM() < 0.01; -- Non-deterministic
Forcing Incremental Mode
You can explicitly force incremental mode for supported queries:
sql
CREATE DYNAMIC TABLE optimized_pipeline
TARGET_LAG = '5 minutes'
WAREHOUSE = compute_wh
REFRESH_MODE = INCREMENTAL -- Explicitly set
AS
SELECT customer_id,
DATE(order_time) as order_date,
COUNT(*) as order_count,
SUM(order_total) as daily_revenue
FROM orders
WHERE order_time > CURRENT_TIMESTAMP - INTERVAL '90 days'
GROUP BY 1, 2;
Production Best Practices
Building reliable production pipelines requires following proven patterns.
Performance Optimization tips
Break down complex transformations:
sql
-- Bad: Single complex dynamic table
CREATE DYNAMIC TABLE complex_report
TARGET_LAG = '15 minutes'
WAREHOUSE = compute_wh
AS
-- 500 lines of complex SQL with multiple CTEs, joins, window functions
...;
-- Good: Multiple simple dynamic tables
CREATE DYNAMIC TABLE cleaned_events
TARGET_LAG = DOWNSTREAM
WAREHOUSE = compute_wh
AS
SELECT customer_id, event_type, CAST(event_time AS TIMESTAMP) as event_time
FROM raw_events
WHERE event_time IS NOT NULL;
CREATE DYNAMIC TABLE enriched_events
TARGET_LAG = DOWNSTREAM
WAREHOUSE = compute_wh
AS
SELECT e.*, c.customer_segment
FROM cleaned_events e
JOIN customers c ON e.customer_id = c.customer_id;
CREATE DYNAMIC TABLE final_report
TARGET_LAG = '15 minutes'
WAREHOUSE = compute_wh
AS
SELECT customer_segment,
DATE(event_time) as day,
COUNT(*) as event_count
FROM enriched_events
GROUP BY 1, 2;
Monitoring and Debugging
Monitor your Tables through Snowsight or SQL:
sql
-- Show all dynamic tables
SHOW DYNAMIC TABLES;
-- Get detailed information about refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('daily_sales_summary'))
ORDER BY data_timestamp DESC
LIMIT 10;
-- Check if dynamic table is using incremental refresh
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY(
'my_dynamic_table'
))
WHERE refresh_action = 'INCREMENTAL';
-- View the DAG for your pipeline-- In Snowsight: Go to Data → Databases → Your Database → Dynamic Tables-- Click on a dynamic table to see the dependency graph visualization
Cost Optimization Strategies
Right-size your warehouse:
sql
-- Small warehouse for simple transformations
CREATE DYNAMIC TABLE lightweight_transform
TARGET_LAG = '10 minutes'
WAREHOUSE = x_small_wh -- Start small
AS SELECT * FROM source WHERE active = TRUE;
-- Large warehouse only for heavy aggregations
CREATE DYNAMIC TABLE heavy_analytics
TARGET_LAG = '1 hour'
WAREHOUSE = large_wh -- Size appropriately
AS
SELECT product_category,
date,
COUNT(DISTINCT customer_id) as unique_customers,
SUM(revenue) as total_revenue
FROM sales_fact
JOIN product_dim USING (product_id)
GROUP BY 1, 2;
Use clustering keys for large tables:
sql
CREATE DYNAMIC TABLE partitioned_sales
TARGET_LAG = '30 minutes'
WAREHOUSE = medium_wh
CLUSTER BY (sale_date, region) -- Improves refresh performance
AS
SELECT sale_date, region, product_id, SUM(amount) as sales
FROM transactions
GROUP BY 1, 2, 3;
Real-World Use Cases
Use Case 1: Real-Time Analytics Dashboard
Scenario: E-commerce company needs up-to-the-minute sales dashboards
sql
-- Real-time order metrics
CREATE DYNAMIC TABLE real_time_order_metrics
TARGET_LAG = '2 minutes'
WAREHOUSE = reporting_wh
AS
SELECT
DATE_TRUNC('minute', order_time) as minute,
COUNT(*) as order_count,
SUM(order_total) as revenue,
AVG(order_total) as avg_order_value
FROM orders
WHERE order_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY 1;
-- Product inventory status
CREATE DYNAMIC TABLE inventory_status
TARGET_LAG = '5 minutes'
WAREHOUSE = operations_wh
AS
SELECT
p.product_id,
p.product_name,
p.stock_quantity,
COALESCE(SUM(o.quantity), 0) as pending_orders,
p.stock_quantity - COALESCE(SUM(o.quantity), 0) as available_stock
FROM products p
LEFT JOIN order_items o ON p.product_id = o.product_id
WHERE o.order_status = 'pending'
GROUP BY 1, 2, 3;
Use Case 2:Change Data Capture Pipelines
Scenario: Financial services company tracks account balance changes
sql
-- Capture all balance changes
CREATE DYNAMIC TABLE account_balance_history
TARGET_LAG = '1 minute'
WAREHOUSE = finance_wh
AS
SELECT
account_id,
transaction_id,
transaction_time,
transaction_amount,
SUM(transaction_amount) OVER (
PARTITION BY account_id
ORDER BY transaction_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_balance
FROM transactions
ORDER BY account_id, transaction_time;
-- Daily account summaries
CREATE DYNAMIC TABLE daily_account_summary
TARGET_LAG = '15 minutes'
WAREHOUSE = finance_wh
AS
SELECT
account_id,
DATE(transaction_time) as summary_date,
MIN(running_balance) as min_balance,
MAX(running_balance) as max_balance,
COUNT(*) as transaction_count
FROM account_balance_history
GROUP BY 1, 2;
Use Case 3: Slowly Changing Dimensions
Scenario: Type 2 SCD implementation for customer dimension
sql
-- Customer SCD Type 2 with dynamic table
CREATE DYNAMIC TABLE customer_dimension_scd2
TARGET_LAG = '10 minutes'
WAREHOUSE = etl_wh
AS
WITH numbered_changes AS (
SELECT
customer_id,
customer_name,
customer_address,
customer_segment,
update_timestamp,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY update_timestamp
) as version_number
FROM customer_changes_stream
)
SELECT
customer_id,
version_number,
customer_name,
customer_address,
customer_segment,
update_timestamp as valid_from,
LEAD(update_timestamp) OVER (
PARTITION BY customer_id
ORDER BY update_timestamp
) as valid_to,
CASE
WHEN LEAD(update_timestamp) OVER (
PARTITION BY customer_id
ORDER BY update_timestamp
) IS NULL THEN TRUE
ELSE FALSE
END as is_current
FROM numbered_changes;
Use Case 4:Multi-Layer Data Mart Architecture
Scenario: Building a star schema data mart with automated refresh
sql
-- Bronze layer: Data cleaning
CREATE DYNAMIC TABLE bronze_sales
TARGET_LAG = DOWNSTREAM
WAREHOUSE = etl_wh
AS
SELECT
CAST(sale_id AS NUMBER) as sale_id,
CAST(sale_date AS DATE) as sale_date,
CAST(customer_id AS NUMBER) as customer_id,
CAST(product_id AS NUMBER) as product_id,
CAST(quantity AS NUMBER) as quantity,
CAST(unit_price AS DECIMAL(10,2)) as unit_price
FROM raw_sales
WHERE sale_id IS NOT NULL;
-- Silver layer: Business logic
CREATE DYNAMIC TABLE silver_sales_enriched
TARGET_LAG = DOWNSTREAM
WAREHOUSE = transform_wh
AS
SELECT
s.*,
s.quantity * s.unit_price as total_amount,
c.customer_segment,
p.product_category,
p.product_subcategory
FROM bronze_sales s
JOIN dim_customer c ON s.customer_id = c.customer_id
JOIN dim_product p ON s.product_id = p.product_id;
-- Gold layer: Analytics-ready
CREATE DYNAMIC TABLE gold_sales_summary
TARGET_LAG = '15 minutes'
WAREHOUSE = analytics_wh
AS
SELECT
sale_date,
customer_segment,
product_category,
COUNT(DISTINCT sale_id) as transaction_count,
SUM(total_amount) as revenue,
AVG(total_amount) as avg_transaction_value
FROM silver_sales_enriched
GROUP BY 1, 2, 3;
New features in 2025
Immutability Constraints
New in 2025: Lock specific rows while allowing incremental updates to others
sql
CREATE DYNAMIC TABLE sales_with_closed_periods
TARGET_LAG = '30 minutes'
WAREHOUSE = compute_wh
IMMUTABLE WHERE (sale_date < '2025-01-01') -- Lock historical data
AS
SELECT
sale_date,
region,
SUM(amount) as total_sales
FROM transactions
GROUP BY 1, 2;
This prevents accidental modifications to closed accounting periods while continuing to update current data.
CURRENT_TIMESTAMP Support for incremental mode
New in 2025: Use time-based filters in incremental mode
sql
CREATE DYNAMIC TABLE rolling_30_day_metrics
TARGET_LAG = '10 minutes'
WAREHOUSE = compute_wh
REFRESH_MODE = INCREMENTAL -- Now works with CURRENT_TIMESTAMP
AS
SELECT
customer_id,
COUNT(*) as recent_orders,
SUM(order_total) as recent_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;
Previously, using CURRENT_TIMESTAMP forced full refresh. Now it works with incremental mode.
Backfill from Clone feature
New in 2025: Initialize dynamic tables from historical snapshots
sql
-- Clone existing table with corrected data
CREATE TABLE sales_corrected CLONE sales_with_errors;
-- Apply corrections
UPDATE sales_corrected SET amount = amount * 1.1 WHERE region = 'APAC';
-- Create dynamic table using corrected data as baseline
CREATE DYNAMIC TABLE sales_summary
BACKFILL FROM sales_corrected
IMMUTABLE WHERE (sale_date < '2025-01-01')
TARGET_LAG = '15 minutes'
WAREHOUSE = compute_wh
AS
SELECT sale_date, region, SUM(amount) as total_sales
FROM sales
GROUP BY 1, 2;
Advanced Patterns and Techniques
Pattern 1: Handling Late-Arriving Data
Handle records that arrive out of order:
sql
CREATE DYNAMIC TABLE ordered_events
TARGET_LAG = '30 minutes'
WAREHOUSE = compute_wh
AS
SELECT
event_id,
event_time,
customer_id,
event_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY event_time, event_id
) as sequence_number
FROM raw_events
WHERE event_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY customer_id, event_time;
Pattern 2: Using window Functions for cumulative calculations
Build cumulative calculations automatically:
sql
CREATE DYNAMIC TABLE customer_cumulative_spend
TARGET_LAG = '20 minutes'
WAREHOUSE = analytics_wh
AS
SELECT
customer_id,
order_date,
order_amount,
SUM(order_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as lifetime_value,
COUNT(*) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as order_count
FROM orders;
Pattern 3: Automated Data Quality Checks
Automate data validation:
sql
CREATE DYNAMIC TABLE data_quality_metrics
TARGET_LAG = '10 minutes'
WAREHOUSE = monitoring_wh
AS
SELECT
'customers' as table_name,
CURRENT_TIMESTAMP as check_time,
COUNT(*) as total_rows,
COUNT(DISTINCT customer_id) as unique_ids,
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) as missing_emails,
SUM(CASE WHEN LENGTH(phone) < 10 THEN 1 ELSE 0 END) as invalid_phones,
MAX(updated_at) as last_update
FROM customers
UNION ALL
SELECT
'orders' as table_name,
CURRENT_TIMESTAMP as check_time,
COUNT(*) as total_rows,
COUNT(DISTINCT order_id) as unique_ids,
SUM(CASE WHEN order_amount <= 0 THEN 1 ELSE 0 END) as invalid_amounts,
SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) as orphaned_orders,
MAX(order_date) as last_update
FROM orders;
Troubleshooting Common Issues
Issue 1: Tables Not Refreshing
Problem: Dynamic table shows “suspended” status
Solution:
sql
-- Check for errors in refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('my_table'))
WHERE state = 'FAILED'
ORDER BY data_timestamp DESC;
-- Resume the dynamic table
ALTER DYNAMIC TABLE my_table RESUME;
-- Check dependencies
SHOW DYNAMIC TABLES LIKE 'my_table';
Issue 2: Using Full Refresh Instead of Incremental
Problem: Query should support incremental but uses full refresh
Complex nested queries: Simplify or break into multiple dynamic tables
Masking policies on base tables: Consider alternative security approaches
LATERAL FLATTEN: May force full refresh for complex nested structures
sql
-- Check current refresh mode
SELECT refresh_mode
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY('my_table'))
LIMIT 1;
-- If full refresh is required, optimize for performance
ALTER DYNAMIC TABLE my_table SET WAREHOUSE = larger_warehouse;
Issue 3: High compute Costs
Problem: Unexpected credit consumption
Solutions:
sql
-- 1. Analyze compute usage
SELECT
name,
warehouse_name,
SUM(credits_used) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY 1, 2
ORDER BY total_credits DESC;
-- 2. Increase target lag to reduce refresh frequency
ALTER DYNAMIC TABLE expensive_table
SET TARGET_LAG = '30 minutes'; -- Was '5 minutes'-- 3. Use smaller warehouse
ALTER DYNAMIC TABLE expensive_table
SET WAREHOUSE = small_wh; -- Was large_wh-- 4. Check if incremental is being used-- If not, optimize query to support incremental processing
Migration from Streams and Tasks
Converting existing Stream/Task pipelines to Dynamic Tables:
Before (Streams and Tasks):
sql
-- Stream to capture changes
CREATE STREAM order_changes ON TABLE raw_orders;
-- Task to process stream
CREATE TASK process_orders
WAREHOUSE = compute_wh
SCHEDULE = '10 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('order_changes')
AS
INSERT INTO processed_orders
SELECT
order_id,
customer_id,
order_date,
order_total,
CASE
WHEN order_total > 1000 THEN 'high_value'
WHEN order_total > 100 THEN 'medium_value'
ELSE 'low_value'
END as value_tier
FROM order_changes
WHERE METADATA$ACTION = 'INSERT';
ALTER TASK process_orders RESUME;
After (Snowflake Dynamic Tables):
sql
CREATE DYNAMIC TABLE processed_orders
TARGET_LAG = '10 minutes'
WAREHOUSE = compute_wh
AS
SELECT
order_id,
customer_id,
order_date,
order_total,
CASE
WHEN order_total > 1000 THEN 'high_value'
WHEN order_total > 100 THEN 'medium_value'
ELSE 'low_value'
END as value_tier
FROM raw_orders;
Benefits of migration:
75% less code to maintain
Automatic dependency management
No manual stream/task orchestration
Automatic incremental processing
Built-in monitoring and observability
Snowflake Dynamic Tables: Comparison with Other Platforms
Feature
Snowflake Dynamic Tables
dbt Incremental Models
Databricks Delta Live Tables
Setup complexity
Low (native Snowflake)
Medium (external tool)
Medium (Databricks-specific)
Automatic orchestration
Yes
No (requires scheduler)
Yes
Incremental processing
Automatic
Manual configuration
Automatic
Query language
SQL
SQL + Jinja
SQL + Python
Dependency management
Automatic DAG
Manual ref() functions
Automatic DAG
Cost optimization
Automatic warehouse sizing
Manual
Automatic cluster sizing
Monitoring
Built-in Snowsight
dbt Cloud or custom
Databricks UI
Multi-cloud
AWS, Azure, GCP
Any Snowflake account
Databricks only
Conclusion: The Future of Data Pipeline develoment
Snowflake Dynamic Tables represent a paradigm shift in data pipeline development. By eliminating complex orchestration code and automating refresh management, they allow data teams to focus on business logic rather than infrastructure.
Key transformations enabled:
80% reduction in pipeline code complexity
Zero orchestration maintenance overhead
Automatic incremental processing without manual merge logic
Self-managing dependencies through intelligent DAG analysis
Built-in monitoring and observability
Cost optimization through intelligent refresh scheduling
As data freshness requirements increase and pipeline complexity grows, dynamic tables provide the declarative approach needed to build scalable, maintainable data infrastructure.
Start with simple use cases, measure performance, and progressively migrate complex pipelines. The investment in learning this technology pays dividends in reduced maintenance burden and faster feature delivery.
Snowflake Hybrid Tables: Is This the End of the ETL Era?
For decades, the data world has been split in two. On one side, you have transactional (OLTP) databases—the fast, row-based engines that power our applications. On the other hand, you have analytical (OLAP) databases like Snowflake—the powerful, columnar engines that fuel our business intelligence. Traditionally, the bridge between them has been a slow, complex, and costly process called ETL. But what if that bridge could disappear entirely? Ultimately, this is the promise of Snowflake Hybrid Tables, and it’s a revolution in the making.
What Are Snowflake Hybrid Tables? The Best of Both Worlds
In essence, Snowflake Hybrid Tables are a new table type, powered by a groundbreaking workload engine called Unistore. Specifically, they are designed to handle both fast, single-row operations (like an UPDATE from an application) and massive analytical scans (like a SUM across millions of rows) on a single data source.
To illustrate, think of it this way:
The Traditional Approach: You have a PostgreSQL database for your e-commerce app and a separate Snowflake warehouse for your sales analytics. Consequently, every night, an ETL job copies data from one to the other.
The Hybrid Table Approach: Your e-commerce app and your sales dashboard both run on the same table within Snowflake. As a result, the data is always live.
This is possible because Unistore combines a row-based storage engine (for transactional speed) with Snowflake’s traditional columnar engine (for analytical performance), thereby giving you a unified experience.
Why This Changes Everything: Key Benefits
Adopting Snowflake Hybrid Tables isn’t just a technical upgrade; it’s a strategic advantage that simplifies your entire data stack.
Analyze Live Transactional Data: The most significant benefit. Imagine running a sales-per-minute dashboard that is 100% accurate, or a fraud detection model that works on transactions the second they happen. No more waiting 24 hours for data to refresh.
Dramatically Simplified Architecture: You can eliminate entire components from your data stack. Say goodbye to separate transactional databases, complex Debezium/CDC pipelines, and the orchestration jobs (like Airflow) needed to manage them.
Build Apps Directly on Snowflake: Developers can now build, deploy, and scale data-intensive applications on the same platform where the data is analyzed, reducing development friction and time-to-market.
Unified Governance and Security: With all your data in one place, you can apply a single set of security rules, masking policies, and governance controls. No more trying to keep policies in sync across multiple systems.
Practical Guide: Your First Snowflake Hybrid Table
Let’s see this in action with a simple inventory management example.
First, creating a Hybrid Table is straightforward. The key differences are the HYBRID keyword and the requirement for a PRIMARY KEY, which is crucial for fast transactional lookups.
Step 1: Create the Hybrid Table
-- Create a hybrid table to store live product inventory
CREATE OR REPLACE HYBRID TABLE product_inventory (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
stock_level INT,
last_updated_timestamp TIMESTAMP_LTZ
);
Notice the PRIMARY KEY is enforced and indexed for performance.
Step 2: Perform a Transactional Update
Imagine a customer buys a product. Your application can now run a fast, single-row UPDATE directly against Snowflake.
-- A customer just bought product #123
UPDATE product_inventory
SET stock_level = stock_level - 1,
last_updated_timestamp = CURRENT_TIMESTAMP()
WHERE product_id = 123;
This operation is optimized for speed using the row-based storage engine.
Step 3: Run a Real-Time Analytical Query
Simultaneously, your BI dashboard can run a heavy analytical query to calculate the total value of all inventory.
-- The analytics team wants to know the total stock level right NOW
SELECT
SUM(stock_level) AS total_inventory_units
FROM
product_inventory;
This query uses Snowflake’s powerful columnar engine to scan the stock_level column efficiently across millions of rows.
Is It a Fit for You? Key Considerations
While incredibly powerful, Snowflake Hybrid Tables are not meant to replace every high-throughput OLTP database (like those used for stock trading). They are ideal for:
“Stateful” application backends: Storing user profiles, session states, or application settings.
Systems of record: Managing core business data like customers, products, and orders where real-time analytics is critical.
Data serving layers: Powering APIs that need fast key-value lookups.
Conclusion: A New Architectural Standard
Snowflake Hybrid Tables represent a fundamental shift, moving us from a world of fragmented data silos to a unified platform for both action and analysis. By erasing the line between transactional and analytical workloads, Snowflake is not just simplifying architecture—it’s paving the way for a new generation of data-driven applications that are smarter, faster, and built on truly live data. The era of nightly ETL batches is numbered.
