Tag: Apache Parquet

In this post, I create a structured data knowledge base using Amazon Bedrock and test it by allowing it to query a database directly. Here’s what worked, what didn’t, and what it cost.

Introduction

In my last post, I built an unstructured Bedrock knowledge base using an S3-backed vector store. I now want to see how Bedrock behaves when it can use structured, relational data instead.

A Bedrock structured knowledge base doesn’t use embeddings or vector search. Instead, it relies on the database schema. Bedrock generates SQL queries from natural language prompts, executes them directly against the data source and then formats the results into a user response.

While researching November’s post, I checked the query engine options for a Bedrock structured data knowledge base out of curiosity. I was greeted by a choice of…Redshift:

So fair enough – Redshift it is!

In this post, I will first load curated Parquet data into Redshift Serverless via S3. Next, I’ll create a Bedrock structured data knowledge base and synchronise it with my data.

Then I’ll pose increasingly challenging questions to the knowledge base, ranging from simple counts to complex aggregations. I’ll assess Bedrock’s strengths, analyse any misconceptions, and examine how prompt phrasing can affect results. Finally, I’ll review the costs associated with both Bedrock and Redshift.

To manage expectations, this post is essentially a POC. As such, the following are out of scope:

• Benchmarking SQL or model performance.
• Evaluating general model accuracy.
• Production hardening.

Now, let’s get some data into AWS.

Data Preparation

In this section, I’ll create a minimal data pipeline using Parquet, S3 and Redshift Serverless.

Dataset Context

Let’s begin by talking about the data.

The data is sourced from my personal iTunes library and represents a snapshot of track-level metadata at a specific time. Each row corresponds to a single track, with columns capturing both standard attributes and user events.

Some columns mentioned later should be explicitly defined:

• mtg_artist – The artist associated with the track.
• mtg_publisher – The record label or publisher associated with the track.
• my_rating – A personal track rating assigned in iTunes, on a fixed numeric scale.
• plays – The total number of times each track was played on iTunes at the time of the snapshot.

File Creation

This process starts by creating a Parquet file. Parquet has been an industry standard for a while now, has many benefits and is ideal for this use case.

Now – full disclosure: this process is currently a bit hacky and uses an ad-hoc local Python script. I will write a proper ETL process as part of Project Wolfie. For now, it’s a combination of pandas .join and .to_parquet functions.

As for the data itself, I did mention it briefly in my February 2025 and May 2025 posts, but admittedly not in much depth. Project Wolfie’s Roadmap does include data dictionaries for all data sources, so they will exist – they just don’t currently. That said, the absence of data dictionaries won’t significantly impact this post, as my main focus is on how the Bedrock knowledge base interacts with the structured data.

S3 Setup

Next, let’s create an S3 bucket. I’ve created a temporary new bedrock-test general-purpose bucket for this post with default config. I then created an iTunesTest folder and uploaded my curated_data.parquet file. The names of the folders and buckets aren’t mega important here; what matters most is knowing the S3 path, as I’ll need it soon.

With the S3 bucket taken care of, it’s time to sort out Redshift. But which Redshift?

Redshift Provisioned Vs Redshift Serverless

There are currently two Redshift offerings, each with its own implementation.

Redshift Provisioned is the original deployment model, in which users create and manage a fixed-size cluster with specific node types. Capacity is chosen upfront, and the cluster is always available.

Redshift Provisioned is ideal for steady workloads that need consistent performance and precise control. However, it requires capacity planning and incurs charges even when not in use, making it less suitable for small or irregular workloads.

Redshift Serverless removes the need for cluster management by automatically adjusting compute capacity in response to demand. It mostly behaves like a standard Redshift endpoint, while AWS oversees the underlying infrastructure.

Redshift Serverless is ideal for low-volume or infrequent workloads, as you only pay for the compute power you consume and don’t need to manage capacity. However, it offers less control than provisioned clusters, and costs may exceed those of provisioned clusters if usage goes beyond a certain threshold.

Here, I’m using Redshift Serverless because:

• Intermittent Workload: The data will be queried infrequently, so an always-on cluster is unnecessary.
• Operational Simplicity: I want to focus on the AI POC, not on managing and sizing infrastructure.
• Free Trial: Redshift Serverless offers a free trial for new users. It’d be rude not to…

Decision made. Next, let’s configure Redshift Serverless.

Redshift Serverless Setup

One of Redshift’s Serverless perks is its simple setup. Instead of explaining this myself, I’ll refer to two AWS videos. The first focuses mainly on the settings:

And the second explores Redshift’s Query Editor:

Here, I use the defaults again. I end up with a workgroup for controlling resources, scaling and execution prioritisation, and a namespace for holding tables, workgroups, and other database resources:

Next, I need to put my Parquet data into a Redshift table. I’ll be using Redshift’s COPY command for this, but as the target table must already exist in the database, I need to write a Data Definition Language (DDL) command first.

To make the process as painless as possible, I write a quick pandas Python script to get the curated_data.parquet file schema:

import pandas as pd

# Use .read_parquet to capture the data in a DataFrame
df = pd.read_parquet("curated_data.parquet")

# Use .dtypes to get the schema
print(df.dtypes)

import pandas as pd

# Use .read_parquet to capture the data in a DataFrame
df = pd.read_parquet("curated_data.parquet")

# Use .dtypes to get the schema
print(df.dtypes)

Name object
Artist object
Album object
Work object
Genre object
Size int64
Time int64
...

Name object
Artist object
Album object
Work object
Genre object
Size int64
Time int64
...

And then pass the output to Amazon Q Developer to write a DDL statement for Redshift:

Convert this Parquet schema into a Redshift DDL statement:

Name object
Artist object
Album object
Work object
Genre object
Size int64
Time int64

…

Here's the Redshift DDL statement for your Parquet schema:

CREATE TABLE itunes_data(
    Name VARCHAR(500),
    Artist VARCHAR(500),
    Album VARCHAR(500),
    Work VARCHAR(500),
    Genre VARCHAR(100),
    Size BIGINT,
    Time BIGINT
    ...

Key mappings:

int32 → INTEGER
int64 → BIGINT
float64 → DOUBLE PRECISION
datetime64 → TIMESTAMP
object → VARCHAR(500) (adjust size as needed for your data)

Here's the Redshift DDL statement for your Parquet schema:

CREATE TABLE itunes_data(
    Name VARCHAR(500),
    Artist VARCHAR(500),
    Album VARCHAR(500),
    Work VARCHAR(500),
    Genre VARCHAR(100),
    Size BIGINT,
    Time BIGINT
    ...

Key mappings:

int32 → INTEGER
int64 → BIGINT
float64 → DOUBLE PRECISION
datetime64 → TIMESTAMP
object → VARCHAR(500) (adjust size as needed for your data)

I then run this script in Redshift to create an empty itunes_data table. COPY now has everything it needs to put the data into Redshift:

COPY dev.public.itunes_data
FROM 's3://bedrock-test/iTunesTest/curated_data.parquet'
IAM_ROLE 'arn:aws:iam::XXXXXXXXXX:role/service-role/AmazonRedshift-CommandsAccessRole-20251225T125455'
PARQUET

COPY dev.public.itunes_data
FROM 's3://bedrock-test/iTunesTest/curated_data.parquet'
IAM_ROLE 'arn:aws:iam::XXXXXXXXXX:role/service-role/AmazonRedshift-CommandsAccessRole-20251225T125455'
PARQUET

In this command:

• COPY is the name of the target Redshift table.
• FROM is the path to the S3 objects containing my data.
• IAM_ROLE is the method the cluster uses to authenticate with, in this case, S3. This role was created during the Redshift Serverless default setup process.
• PARQUET tells Redshift what format the data is in.

Full documentation is at the COPY from Amazon S3 page. The end result is a Redshift table containing the Parquet data:

I have now prepared my data. Now I can start on the knowledge base!

Knowledge Base Build

In this section, I’ll build a Bedrock structured data knowledge base. Firstly, I’ll set up my query engine and permissions. Next, I’ll create a Bedrock knowledge base and connect it to my Redshift structured data store. Finally, I’ll synchronise the Rdshift structured data store with the Bedrock knowledge base to enable querying.

Knowledge Base Setup

Configuring a Bedrock structured data knowledge base starts out very similarly to my recent unstructured one, albeit with one noticeable difference at the start:

This leads to the Query Engine selection screen shown earlier. After selecting Redshift and either creating or selecting an IAM service role for Bedrock, I must then configure the connection options for my chosen Redshift offering:

The next step identifies where the structured data is stored. In this case, my itunes_data table is located in the dev database’s pubic schema, so I select dev from the database list:

I must now give authentication information to connect to my Redshift database. I can choose between the IAM role created in the previous step and AWS Secrets Manager credentials for authentication.

With this completed, the knowledge base has been created. The next step is the synchronisation process.

Data Store Sync

Currently, Bedrock has visibility into the schema but not into the data itself. As with an unstructured data knowledge base, I must synchronise the Redshift structured data with my Bedrock knowledge base to make the data queryable:

If the credentials are incorrect or lack proper database permissions, the sync will fail:

Bedrock logs issues for review. Typically, IAM updates and/or SQL grants are needed to resolve them.. Here, my Bedrock Execution Role needed access to the public Redshift schema:

GRANT USAGE ON SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT USAGE ON SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

With this done, my sync succeeds and I can start testing my knowledge base.

Knowledge Base Testing

In this section, I will evaluate the Bedrock knowledge base by asking increasingly complex queries. I will start with simple retrievals and then introduce complexity and analytical challenges through ranking, filtering, and aggregation. My goal is to assess comprehension, schema understanding and SQL correctness.

Bedrock isn’t just suggesting SQL here. Instead, it generates and executes SQL directly in Redshift using the knowledge base’s execution role. The queries presented are part of an active execution path rather than just illustrative pseudocode. If the SQL generated is invalid or poorly structured, the query will fail just as it would if I ran it myself.

For each query, I will provide the question I submitted to Bedrock, the SQL that Bedrock generated, and a screenshot of the results. The SQL here is formatted by the excellent CodeBeautify SQL Formatter.

Initial Config

Before I can give queries to the knowledge base, I must configure two settings. Firstly, I must identify the type of response I want:

• Retrieval Only: Bedrock provides the results of executing an SQL query.
• Retrieval and Generation: Bedrock generates a response based on the outcome of executing the SQL query.
• Generate SQL Query: Bedrock transforms the query into SQL.

I’ll use Retrieval and Generation since it returns both the response and the generated SQL. I also need to select a model for Bedrock to use for response generation. In this case, Nova Lite is a suitable option:

The knowledge base can now respond to queries.

Simple Queries

Let’s start by asking about the data as a whole:

How many tracks are in the collection?

Bedrock handles this easily. It generates a straightforward COUNT(*) query and gets the correct number:

SELECT 
  COUNT(*) AS "total_tracks" 
FROM 
  public.itunes_data;

SELECT 
  COUNT(*) AS "total_tracks" 
FROM 
  public.itunes_data;

Next, let’s dig a little deeper. This time I ask about a specific record label:

How many tracks are produced by Anjunabeats?

This should be in the hundreds, but Bedrock returns a far different number:

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  artist = 'Anjunabeats';

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  artist = 'Anjunabeats';

This isn’t a problem with Bedrock. Rather, this is ambiguity within my data. The first Anjunabeats release was Anjunabeats – Volume One, meaning this track and its remixes have both an artist and a publisher of Anjunabeats.

Here, Bedrock interprets ‘produced’ in the artist sense and queries on the artist column. Instead, it should use the mtg-publisher column, but it has no way to know that. In response, I change the prompt to use ‘published’ instead of ‘produced’ and try again.

How many tracks are published by Anjunabeats?

This time, Bedrock uses my signposting and gets the correct answer by querying mtg-publisher:

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats';

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats';

So the first issue I encountered wasn’t related to SQL at all; it was the ambiguity in how Bedrock interpreted my prompt. There are more effective ways to address this ambiguity that I will explore later in this section.

Multi-Constraint Queries

Now let’s try some more demanding questions. I’m still asking about Anjunabeats releases, but I’m also now asking about track ratings:

What are the highest-rated tracks published by Anjunabeats?

Bedrock correctly selects the name and my_rating columns, and also decides to order the results despite not being asked to – nice touch!

Unfortunately, Bedrock then slips back into old habits and, despite my prompt continuing to use published, assumes I’m asking about Anjunabeats – Volume One again and queries one of the artist columns:

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

So Bedrock tells me about the various Volume One remixes I own:

Ok – let’s try changing the prompt. Now, I’m asking firstly about the record label, and then the ratings:

What are the tracks published by Anjunabeats with the highest rating?

This time Bedrock queries on mtg-publisher and the results are correct:

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

My collection has many top-rated Anjunabeats tracks, so let’s narrow it down by asking about play counts:

Tell me about the tracks published by Anjunabeats with the highest rating. I want to know which tracks have the most plays. Give me the artist, title and year.

Bedrock modifies its query. The artist, title and year columns are all correctly selected, and it makes sense to remove my_rating since the tracks I’m asking about all have the same rating value. The query also now has i aliases – not much use for this query but helpful for joins and window functions.

Unfortunately, although I asked for tracks in the plural, Bedrock returns only 1 result:

SELECT 
  i.artist, 
  i.name, 
  i.year 
FROM 
  public.itunes_data i 
WHERE 
  i."mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  i.my_rating DESC, 
  i.plays DESC 
LIMIT 1;

SELECT 
  i.artist, 
  i.name, 
  i.year 
FROM 
  public.itunes_data i 
WHERE 
  i."mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  i.my_rating DESC, 
  i.plays DESC 
LIMIT 1;

Why? No idea. To prevent this from happening again, I request five tracks along with some additional fields.

Tell me about the tracks published by Anjunabeats with the highest rating. I want to know the 5 tracks with the most plays. Give me the artist, title, year, initial key and BPM for each track.

Bedrock responds by…talking about Volume One again:

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "plays" DESC 
LIMIT 5;

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "plays" DESC 
LIMIT 5;

This is definitely something to consider for the future. Anjunabeats isn’t the only Artist/Publisher combo in this dataset! And while I love me some Volume One, it’s not this query’s focus.

So I spell it out in the prompt’s first line:

Tell me about the tracks where the publisher is Anjunabeats. I want to know the 5 tracks with the highest rating and the most plays. Give me the artist, title, year, initial key, BPM and play count for each track.

Success!

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm", 
  "plays" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC, 
  "plays" DESC 
LIMIT 5;

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm", 
  "plays" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC, 
  "plays" DESC 
LIMIT 5;

(If these play counts seem low for two-decade-old tracks, all I can say is that record decks don’t collect metadata – Ed)

Complex Query

Let’s see how far we can go! For the last test, I started with a fairly hefty prompt:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– An overall total of tracks

– Totals for each initial key, ordered by initial key

– Totals for each decade (eg 2000 to 2009, 2010 to 2019 etc)

And, well, I broke it:

Bedrock gave it a good go, but the SQL it sent to Redshift was invalid:

SELECT 
  COUNT(*) AS "Total Tracks", 
  COUNT(DISTINCT "mtg-initialkey") AS "Total Tracks with Initial Key", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' THEN 1 END
  ) AS "Total Anjunabeats Tracks", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 END
  ) AS "Total Anjunabeats Tracks with Rating", 
  (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats' 
      AND my_rating IS NOT NULL
  ) AS "Highest Rated Anjunabeats Track", 
  SUM(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 ELSE 0 END
  ) OVER (
    PARTITION BY FLOOR("mtg-year" / 10)
  ) AS "Totals by Decade" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating IS NOT NULL 
ORDER BY 
  "mtg-initialkey";

SELECT 
  COUNT(*) AS "Total Tracks", 
  COUNT(DISTINCT "mtg-initialkey") AS "Total Tracks with Initial Key", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' THEN 1 END
  ) AS "Total Anjunabeats Tracks", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 END
  ) AS "Total Anjunabeats Tracks with Rating", 
  (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats' 
      AND my_rating IS NOT NULL
  ) AS "Highest Rated Anjunabeats Track", 
  SUM(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 ELSE 0 END
  ) OVER (
    PARTITION BY FLOOR("mtg-year" / 10)
  ) AS "Totals by Decade" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating IS NOT NULL 
ORDER BY 
  "mtg-initialkey";

Redshift query execution failed with error: 
ERROR: column "itunes_data."mtg-publisher"" must appear 
in the GROUP BY clause or be used in an aggregate function.

Redshift query execution failed with error: 
ERROR: column "itunes_data."mtg-publisher"" must appear 
in the GROUP BY clause or be used in an aggregate function.

Fair enough. I would probably break this into multiple queries as well. Let’s ease the pressure a bit by splitting the prompt.

Complex Query: Totals

Firstly, let’s request the overall total of tracks published by Anjunabeats with the highest rating:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– An overall total of tracks

This is a COUNT(*) like the initial query. Bedrock also adds a subquery to get the Anjunabeats MAX(my_rating) value instead of the dataset MAX(my_rating) value:

SELECT 
  COUNT(*) AS "Total Tracks" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  );

SELECT 
  COUNT(*) AS "Total Tracks" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  );

This is sensible. While the MAX(my_rating) value is 100 for both the Anjunabeats record label and the whole dataset, this isn’t true for other labels in the dataset. This version of the query is easier to reuse.

The answer is correct, and I have my baseline value for the other queries – 44 tracks:

You might think 44/539 is a small number. But I don’t hand out top ratings lightly!

Complex Query: Initial Key Ordering

Now let’s try the second part of the prompt that introduces key aggregation:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each initial key, ordered by initial key

And, uh…

This response feels like a placeholder. I repeated the same query multiple times, and each time I got the same result. I’ll revisit this later.

Complex Query: Decade Grouping

Finally, let’s try the last part of the initial prompt that introduces decade aggregation:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each decade (eg 2000 to 2009, 2010 to 2019 etc)

Bedrock creates a new calculated column for this, using an interesting method to calculate decades that I hadn’t considered! For example, take 2005:

• 2005 / 10 = 200.5
• FLOOR(200.5) = 200
• 200 * 10 = 2000

Much better than the CASE statement I was expecting:

SELECT 
  FLOOR("mtg-year" / 10) * 10 AS decade, 
  COUNT(*) AS total_tracks 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  FLOOR("mtg-year" / 10) * 10 
ORDER BY 
  decade;

SELECT 
  FLOOR("mtg-year" / 10) * 10 AS decade, 
  COUNT(*) AS total_tracks 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  FLOOR("mtg-year" / 10) * 10 
ORDER BY 
  decade;

Bedrock confirms what I already knew – that the 2000s were a great time and I miss them terribly:

Also, as in the first query, these results add up to 44.

Complex Query: Initial Key Ordering Retry

Let’s go back to that failing query from earlier. How about I just ask for the totals and drop the ordering requirement:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key

Bedrock apparently has no issues with this:

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey";

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey";

But this doesn’t add up to 44! This time I was curious to see what data Bedrock was getting, so I added an ORDER BY clause to the query and ran it in Redshift:

So the SQL is valid, and the data is correct and equals 44. The issue here is that Bedrock is summarising the results. In an attempt to stop this, I asked Bedrock to order the results by totals like I had just done:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key, ordered by totals

Bedrock’s SQL is fine again:

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

But this response loses precision after the Gm and Bm keys. Bedrock correctly says 19 keys are in the full list, but is vague beyond this:

In one final attempt to get the kind of response I’m after, I tell Bedrock not to summarise the data:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key, ordered by totals. do not summarise the data

Bedrock runs the same query as last time…

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

…and kind of gives me what I want. Bedrock doesn’t give me all of the keys with 2 tracks and gives me none of the keys with 1 track:

I decided that was far enough. This is now crossing into prompt engineering, and the whole point of all this is to test the knowledge base’s ability to use structured data. In that respect, these tests have been successful.

But could I have done things differently?

Future Improvements

I want to end this section by holding my hands up. While Bedrock struggled with some of the tasks I gave it, there were several ways I could have made the process easier for it. As this experience was mainly a proof-of-concept and a learning opportunity, I didn’t implement those measures here.

So instead of concluding with what Bedrock couldn’t achieve, I will outline how I’ll improve future Bedrock implementations as Project Wolfie develops. The following list is not exhaustive – there are definitely unknown unknowns here!

Improved Data Schema: The Parquet file containing my data was created ad hoc, resulting in an unrefined schema with duplicate data and unclear column names. Validation and normalisation processes were also minimal. The Wolfie Data Pipeline will generate a much-improved dataset, which will aid Bedrock’s understanding.

Supportive Descriptions: When creating Bedrock knowledge bases, table and column descriptions, usage notes and additional attributes can be included. These enhance SQL query generation by providing extra context and information about table and column structures. For example, I could have given mtg-publisher a description of ‘record label’ and that might have solved the Volume One issue.

Curated Queries: I can provide predefined examples of questions and answers for the knowledge base, where the questions are natural language queries and the answers are corresponding SQL queries. This gives Bedrock more context for generating SQL. For example, a possible solution to the Anjunabeats confusion could have been:

Q: Tell me about the tracks where the publisher is Anjunabeats.
A: SELECT * FROM public.itunes_data WHERE "mtg-publisher" = 'Anjunabeats'

Q: Tell me about the tracks where the publisher is Anjunabeats.
A: SELECT * FROM public.itunes_data WHERE "mtg-publisher" = 'Anjunabeats'

All things to try out in future versions! Next, let’s talk about costs.

Costs Analysis

This section covers the costs incurred during one week of experimentation in eu-west-1. All figures shown in screenshots are actual charges as reported by AWS. Firstly I’ll examine Bedrock’s costs, followed by Redshift’s.

This account is within my AWS Organisation, and so has access to my Community Builder credits. Some costs will therefore appear as zero until I filter out these credits.

Bedrock Costs

Looking first at Bedrock, my December 2025 bill shows 26 GenerateSQL-StructuredRetrieve requests, totalling USD 0.05:

At the billing level, Bedrock displays a total cost of zero. This indicates how AWS credits are shown, appearing as ‘No region’ credit lines. This information will be relevant shortly!

Now, let’s go to Cost Explorer and adjust the cost granularity. Here are the daily Bedrock expenses for my AWS data sandbox account:

And this is a table showing the costs filtered by API:

Minor rounding artefacts appear at this scale – I’m assuming the 25 December costs are USD 0.00999 and one of the other days is USD 2.00111 or something similar.

Overall, Bedrock costs were low, visible, and easy to reason about. But something is missing…

Redshift Costs

Despite Redshift being central to the architecture, no Redshift charges appear in the billing console during this period. Not even with the debit/credit method shown above. This raises an important question: how exactly does the free trial work?

Well, it turns out that this is by design:

Amazon Redshift Serverless offers a free trial. If you participate in the free trial, you can view the free trial credit balance in the Redshift console, and check free trial usage in the SYS_SERVERLESS_USAGE system view. Note that billing details for free trial usage does not appear in the billing console. You can only view usage in the billing console after the free trial ends.

Billing for Amazon Redshift Serverless

I am NOT a fan of this! I love Cost Explorer for its visibility; everyone with access can view, slice and aggregate the data, quickly identifying trends and hotspots. Not being able to do this with the Redshift Serverless trial feels like a misstep. Surely part of the trial involves understanding Redshift costs like and alongside other AWS services, in order to avoid a big financial shock when the trial ends! Having them separate isn’t great.

Anyway.

There are ways to monitor the costs of a Redshift Serverless trial, but they’re not as intuitive as Cost Explorer. The most obvious way to see how many credits have been used is through the Free Trial widget on the Redshift Serverless dashboard.

Redshift Serverless is billed using Redshift Processing Units (RPUs). An RPU represents a bundle of compute, memory, and networking capacity, and charges are primarily driven by:

• The workgroup’s configured Base Capacity.
• The duration this capacity remains active.

The widget shows USD 56.98 used – higher than I had expected! The billing page mentions SYS_SERVERLESS_USAGE so let’s see what that has to offer:

SELECT
  * 
FROM
  SYS_SERVERLESS_USAGE

SELECT
  * 
FROM
  SYS_SERVERLESS_USAGE

Of particular interest here is the charged_seconds column: the accumulated RPU seconds charged between the start_time and end_time. Handily, the Redshift docs include a query returning aggregated daily costs:

SELECT
  TRUNC(start_time) "Day",
  (
    sum(charged_seconds)/ 3600 :: DOUBLE PRECISION
  ) * 0.387 as cost_incurred 
FROM
  SYS_SERVERLESS_USAGE
GROUP BY
  1 
ORDER BY
  1

SELECT
  TRUNC(start_time) "Day",
  (
    sum(charged_seconds)/ 3600 :: DOUBLE PRECISION
  ) * 0.387 as cost_incurred 
FROM
  SYS_SERVERLESS_USAGE
GROUP BY
  1 
ORDER BY
  1

That’s… actually more than the widget ($58.62), but I can understand the widget having some lag. So what’s driving these costs?

Redshift Cost Review

Firstly, my workgroup is massively overprovisioned, as the Base Capacity was using the default 128 RPU. Great for performance, but uses a ton of resources and is total overkill for my use case!

Fortunately, Base Capacity can be changed. In Considerations and limitations for Amazon Redshift Serverless capacity, AWS states that configurations with 4 base RPU support managed storage capacity of up to 32 TB. And I have… 2.7GB of total storage used.

I clearly don’t need 128 RPU, so I reduce my Base Capacity to 4 RPUs:

While this change led to longer query execution times, expenses quickly stabilised and stopped rising.

Additionally, the Redshift Serverless usage shown is not limited to the Bedrock processes mentioned in this post. During the same time, I was also trying out different features in the Redshift environment, such as:

• Executing queries manually using my IAM user to explore and test various features of Redshift Serverless.
• Creating and testing two separate Bedrock knowledge bases (both visible in the Redshift console).
• Performing ad-hoc exploratory analysis via the Redshift query editor.

Redshift monitors and reports on this activity in the console:

My exploratory use of Redshift prolonged workgroup activity, consumed additional RPU and increased costs. As I reduced my exploratory use, the Redshift costs began to align more closely with my expectations.

Summary

In this post, I created a structured data knowledge base using Amazon Bedrock and tested it by allowing it to query a database directly.

Setup Thoughts

Firstly, I was impressed with the respective Redshift and Bedrock setup processes. Although there were some challenges, they primarily involved ensuring services communicated effectively and were resolved quickly. And while some default settings were more helpful than others, they at least provided a starting point for making informed configuration decisions.

Bedrock’s costing is transparent, so I could also make informed cost decisions. And while I’m a big fan of the Redshift Serverless free trial, I wish it was trackable within Cost Explorer. In the meantime, I’ll keep a close eye on SYS_SERVERLESS_USAGE!

Testing Thoughts

Testing the knowledge base was straightforward, and I quickly understood how Bedrock interacted with Redshift. Seeing the actual queries being executed not only helps optimise the knowledge base but also informs potential architectural changes to the database.

The results emphasise the importance of data structure and quality. I’ve worked with data for almost a decade now, and have seen many sales pitches claiming to remove the need for SQL, data modelling or normalisation. The reality is that, especially in the current AI era, well-structured and properly modelled data and an understanding of how to query it effectively remain crucial.

As I’ve shown here, without these data controls, an AI attempting to use such data is likely to produce incorrect, inconsistent or inaccurate outputs. While simple queries and explicit prompts can help mitigate these issues, it is more beneficial to start with clean, well-structured data.

A knowledge base that can access clean, normalised data stores lets users focus on interpreting answers rather than rephrasing questions. This will be a key consideration for the architecture of the Project Wolfie data pipelines.

Finally, I’ll leave you with an AWS demo video in which Lester Sim creates his own Bedrock structured data knowledge base:

Like this post? Click the button below for links to contact, socials, projects and sessions:

Thanks for reading ~~^~~