Accelerating Financial Data Analysis Using Snowflake's Cortex Analyst

Every second, around 22,952 credit card transactions occur across the globe. These data points are being generated in the blink of an eye—each possessing potential insights into consumer behavior, purchasing patterns, and much more. For financial services firms and businesses, this large-scale transactional data offers a powerful opportunity to understand customers, personalize services, and drive loyalty. 

The challenge, however, is quickly extracting actionable insights from this vast data to make informed, timely decisions. With the volume of data growing by the second, businesses need to analyze it at lightning speed. Snowflake’s Cortex Analyst empowers various user personas to easily extract actionable answers with no coding required. Analysts, managers, and executives can now ask complex questions of structured data in everyday language. Whether it's improving fraud detection, refining credit scoring, or adjusting offerings based on customer behavior, Cortex Analyst gives businesses a competitive edge by unlocking the full potential of their data—faster and more effectively.

What is Cortex Analyst?

Cortex Analyst is a fully managed feature of Snowflake Cortex that empowers business users to ask questions in natural language and receive answers directly from structured data in Snowflake—no SQL required. For financial services firms, this means teams can leverage their industry knowledge alongside Cortex Analyst to quickly analyze customer spending patterns, detect fraud, or assess credit risk. Cortex Analyst automatically generates the necessary SQL queries and executes them on Snowflake’s scalable engine. This integration not only enhances performance but also reduces total cost of ownership, making it a cost-effective solution for businesses looking to maximize efficiency and real-time insights.

‍Building a Semantic Model for Customer Credit Card Transactions Analysis with Cortex Analyst

To showcase the power of Cortex Analyst, we created an application that analyzes over one million credit card transactions across the US. A key factor in developing a successful Cortex Analyst application is building a strong semantic model. A semantic model bridges business language and database schemas, enriching data with descriptions, synonyms, and custom SQL expressions to create tailored metrics. It centers on the logical table—a simplified view of a physical table—containing logical columns categorized into dimensions, time dimensions, and measures. These columns can reference physical data or use expressions like aggregations to define custom metrics. In other words, the semantic model provides context to the LLM so it can better understand and interpret your data.

An initial version of the semantic model was created using Snowflake’s Semantic Model Generator, which helps automate model creation from Snowflake tables. It allows users to refine the model iteratively and make real-time adjustments based on the responses. The model for this demo is built on credit card transaction data, which includes transaction amounts, categories, merchant details, and timestamps. It integrates with Snowflake’s privacy and governance features, such as role-based access control (RBAC) and column masking, which help protect sensitive customer data. Once finalized, the semantic model is stored as YAML file and can be accessed through a REST API.

name: TRANSACTIONS_SEMANTIC_MODEL
tables:
  - name: TRANSACTIONS
    description: This table stores information about individual transactions, including
      timestamp, credit card details, merchant information, transaction amount, and customer demographics.
    base_table:
      database: DATABASE
      schema: SCHEMA
      table: TRANSACTIONS
    primary_key:
      columns:
        - FULL_NAME
    dimensions:
      - name: FULL_NAME
        synonyms:
          - 
        description: Full name of the customer or individual involved in the transaction.
        expr: FULL_NAME
        data_type: TEXT
        sample_values:
          - Jennifer Banks
          - Stephanie Gill
          - Margaret Curtis

Snippet of the semantic model for the credit card use case

In the above code snippet of the semantic model, the ‘base_table’ key specifies the data table in Snowflake that the semantic model is built on top of, tying the model to the structured data.  

Additionally, the model also allows users to define custom metrics tailored to their needs. For example, as shown below, a new logic column called CUSTOMER_LIFETIME_VALUE is created. It is calculated as follows:

CUSTOMER LIFETIME VALUE  =Frequency of a customer's trasactions  x  Average amount spent by the customer x Customer Lifespan

Which translates to the following SQL expression:

CUSTOMER LIFETIME VALUE  =COUNTDISTINCT timestamp  x  AVGamount x  DATEDIFF(DAY, MINtimestamp, MAX(timestamp))

The value is calculated using the frequency of a customer’s transactions, the average amount spent by the customer, and the customer’s lifespan. In this case, 'timestamp' and 'amount' are existing columns in the data table.

- name: CUSTOMER_LIFETIME_VALUE
        synonyms:
          - lifetime value
          - CLV
          - clv
        description: Filter down to the customer's FULL_NAME in the query and then
          calculate the expression as the customer's lifetime value
        expr: COUNT(DISTINCT timestamp)*AVG(amount)*DATEDIFF(DAY, MIN(timestamp),
          MAX(timestamp)) / NULLIF(365.0, 0)
        data_type: FLOAT
        sample_values:
          - '289324.17'
          - '70000.35'
          - '10029.62'

The custom logic column, CUSTOMER_LIFETIME_VALUE, is defined as a SQL expression using other logic columns, with descriptions providing instructions for its calculation.

The model also supports predefined filters, giving users the ability to easily apply business rules or specific logic to refine and tailor their results to a given use case.  For instance, in the example below, a filter is used to define the term "millennials" as individuals born between 1981 and 1996. When a user includes the term "millennials" in their query, the model automatically applies this filter, ensuring that the results are based only on customers whose year of birth falls within that range.

filters:
      - name: millennials
        synonyms:
          - millennials
          - millennial
        description: Defines millennials as people born between 1981 and 1996
        expr: EXTRACT(YEAR FROM DOB) BETWEEN 1981 AND 1996

A filter defining the term “millennials” so that any query containing the word “millennials” or its synonyms will be filtered based on the customers’ date of birth per these defined requirements.

‍Leveraging Cortex Analyst for Data-Driven Insights

With the semantic model now in place, it’s time to see how Cortex Analyst can transform natural language questions into valuable insights. Cortex Analyst is accessed via a REST API, which takes in user credentials and the semantic model and returns a response to the user query. In this demo, we extract a token from a session object created by a Python connector for authentication. In production, tokens can be stored securely as secrets in Snowflake. Given below is an example of the code provided by Snowflake to make a call to Cortex Analyst. 

def send_message(prompt: str) -> Dict[str, Any]:
    """Calls the REST API and returns the response."""
    request_body = {
        "messages": [{"role": "user", "content": [{"type": "text", "text": prompt}]}],
        "semantic_model_file": f"@{DATABASE}.{SCHEMA}.{STAGE}/{FILE}",
    }
    resp = requests.post(
        url=f"https://{HOST}/api/v2/cortex/analyst/message",
        json=request_body,
        headers={
            "Authorization": f'Snowflake Token="{st.session_state.CONN.rest.token}"',
            "Content-Type": "application/json",
        },
    )
    request_id = resp.headers.get("X-Snowflake-Request-Id")
    if resp.status_code < 400:
        return {**resp.json(), "request_id": request_id}  # type: ignore[arg-type]
    else:
        raise Exception(
            f"Failed request (id: {request_id}) with status {resp.status_code}: {resp.text}"
        )

To better understand the value that Cortex Analyst can offer, let’s imagine the scenario of a financial analyst working at a bank analyzing customers’ credit card transactions. Using a Streamlit application based on the example provided by Snowflake, the financial analyst poses a question and receives the following response within seconds.

Cortex Analyst not only provides answers to the natural language question but also provides the SQL query it generated to answer the question, enabling users to verify the accuracy of the results.

Now, suppose the financial analyst is interested in exploring seasonal and demographic trends. Given below is an example of such a query for a specific demographic, namely millennials.

In the example above, Cortex Analyst demonstrates that the "millennials" filter in the semantic model is working properly by offering a clear and accurate interpretation of the financial analyst's query. Of note is Cortex Analyst’s interpretation of the week before and the week of Black Friday – as you can imagine there are different interpretations of this request. While this may seem counterintuitive to a human who expects a Monday-to-Sunday week, it highlights an opportunity for the financial analyst to apply their expertise and validate whether the interpretation aligns with the use case. The financial analyst can then modify the semantic model with the corrected interpretation using filters or the Verified Query Repository. This is a powerful example of how Cortex Analyst is a tool that augments analyses in conjunction with the users’ domain expertise. 

Now, the financial analyst is interested in investigating the customer lifetime value, which is defined per customer as the transaction volume, spending on the card, and the length of time the customer has owned the card. To do so, the financial analyst asks the following question: Which customers belong to the top 5% by lifetime value over the entire available time period?

Recall the custom CUSTOMER_LIFETIME_VALUE metric included in the semantic model. The response to this query uses that specific custom metric to identify customers in the top 5% by lifetime value.

The above use case illustrates just one of the many domains that Cortex Analyst can transform. Its value spans across any industry handling structured data. In manufacturing, for example, it can assist monitor production quality and quality control by helping answer questions like, “How many defective units were produced in the last shift?” Similarly, in retail it can offer insights into customer behavior, inventory management, and sales forecasting, enhancing operational efficiency. 

By pairing effortless data access with domain expertise, Cortex Analyst enables users at all levels to ask complex questions and get fast, actionable insights—no coding required. This has the potential to fuel productivity and drive business outcomes with unprecedented speed. You can take it one step further by seamlessly integrating Cortex Analyst with other Cortex features to build powerful AI systems tailored to your needs.

Unlock Faster Insights and Drive Results Today

Leveraging Cortex Analyst to drive insights is a great example of how our team can help your organization enhance business efficiency and productivity. By harnessing advanced AI tools, we enable users to quickly transform data into actionable insights, streamline decision-making and improve transparency. 

We can design and deploy similar solutions tailored to your needs, driving impactful results to keep your business ahead of the competition.

Interested in learning more? Reach out to one of our experts today!

Who are we?

Aimpoint Digital is a market-leading analytics firm at the forefront of solving the most complex business and economic challenges through data and analytical technology. From integrating self-service analytics to implementing AI at scale and modernizing data infrastructure environments, Aimpoint Digital operates across transformative domains to improve the performance of organizations. Connect with our team and get started today.

‍