Skip to content

Latest commit

 

History

History
798 lines (690 loc) · 32 KB

reporting-database.md

File metadata and controls

798 lines (690 loc) · 32 KB
title Reporting Database

Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved. SPDX-License-Identifier: MIT-0

Reporting Database

The GenAI IDP Accelerator includes a comprehensive reporting database that captures detailed metrics about document processing. This database is implemented as AWS Glue tables over Amazon S3 data in Parquet format, making it queryable through Amazon Athena for analytics and reporting purposes.

Table of Contents

Evaluation Tables

The evaluation tables store metrics and results from comparing extracted document data against baseline (ground truth) data. These tables provide insights into the accuracy and performance of the document processing system.

Document Evaluations

The document_evaluations table contains document-level evaluation metrics:

Column Type Description
document_id string Unique identifier for the document
input_key string S3 key of the input document
evaluation_date timestamp When the evaluation was performed
accuracy double Overall accuracy score (0-1)
precision double Precision score (0-1)
recall double Recall score (0-1)
f1_score double F1 score (0-1)
false_alarm_rate double False alarm rate (0-1)
false_discovery_rate double False discovery rate (0-1)
execution_time double Time taken to evaluate (seconds)
config_version string Configuration profile used for processing

This table is partitioned by date (YYYY-MM-DD format).

Section Evaluations

The section_evaluations table contains section-level evaluation metrics:

Column Type Description
document_id string Unique identifier for the document
section_id string Identifier for the section
section_type string Type/class of the section
accuracy double Section accuracy score (0-1)
precision double Section precision score (0-1)
recall double Section recall score (0-1)
f1_score double Section F1 score (0-1)
false_alarm_rate double Section false alarm rate (0-1)
false_discovery_rate double Section false discovery rate (0-1)
evaluation_date timestamp When the evaluation was performed
config_version string Configuration profile used for processing

This table is partitioned by date (YYYY-MM-DD format).

Attribute Evaluations

The attribute_evaluations table contains attribute-level evaluation metrics:

Column Type Description
document_id string Unique identifier for the document
section_id string Identifier for the section
section_type string Type/class of the section
attribute_name string Name of the attribute
expected string Expected (ground truth) value
actual string Actual extracted value
matched boolean Whether the values matched
score double Match score (0-1)
reason string Explanation for the match result
evaluation_method string Method used for comparison
confidence string Confidence score from extraction
confidence_threshold string Confidence threshold used
evaluation_date timestamp When the evaluation was performed
config_version string Configuration profile used for processing

This table is partitioned by date (YYYY-MM-DD format).

Rule Validation Tables

The rule validation tables store business rule validation results showing compliance and policy adherence for processed documents.

Rule Validation Summary

The rule_validation_summary table contains document-level rule validation results:

Column Type Description
document_id string Unique identifier for the document
input_key string S3 key of the input document
validation_date timestamp When the validation was performed
overall_status string Overall validation status (COMPLETE, FAILED, etc.)
total_policy_types int Number of policy types evaluated
total_rules int Total number of rules evaluated
pass_count int Number of rules that passed
fail_count int Number of rules that failed
information_not_found_count int Number of rules where information was not found

This table is partitioned by date (YYYY-MM-DD format).

Rule Validation Details

The rule_validation_details table contains individual rule validation results:

Column Type Description
document_id string Unique identifier for the document
policy_type string Category/type of the policy class
rule string Description of the specific rule being validated
recommendation string Validation result (Pass, Fail, Information Not Found)
reasoning string Explanation for the recommendation
supporting_pages string JSON array of page numbers supporting the validation
validation_date timestamp When the validation was performed

This table is partitioned by date (YYYY-MM-DD format).

Metering Table

The metering table captures detailed usage metrics and cost information for each document processing operation:

Rows are per run. Each processing run of a document writes the usage of that run only. A run that resubmits a stored document (Process Changes, finishing or skipping a review, idp-cli rerun-inference or the SDK rerun) reports the stages it ran and none of the earlier runs' usage, so summing a document's rows over all runs gives its total usage without counting any run twice. The document's tracking record (the Estimated Cost in the Web UI) keeps the cumulative total of every run. Documents reprocessed before this behaviour was introduced keep the figures they were saved with: their rows are not rewritten.

Column Type Description
document_id string Unique identifier for the document
context string Processing context (OCR, Classification, Extraction, etc.)
service_api string Specific API or model used (e.g., textract/analyze_document, bedrock/claude-3)
unit string Unit of measurement (pages, inputTokens, outputTokens, etc.)
value double Quantity of the unit consumed
number_of_pages int Number of pages in the document
unit_cost double Cost per unit in USD (e.g., cost per token, cost per page)
estimated_cost double Calculated total cost in USD (value × unit_cost)
timestamp timestamp Document COMPLETION time — when the workflow ended and the row was written. Same value as the partition.
initial_event_time timestamp Original queue time — when the document was first enqueued. Preserved for consumers who need queue-time semantics.
config_version string Configuration profile used for processing

Partitioned by date + hour (both string; YYYY-MM-DD and HH). Partition values reflect completion time (write time), not queue time. WHERE date = 'X' means "docs completed on X", NOT "docs queued on X" — filter on initial_event_time explicitly when you need queue-time semantics. Add AND hour = 'HH' to partition-prune queries scoped to a specific hour.

Rollup tables (populated by the scheduled DataMartRollupFunction)

For aggregate cost/volume queries over wider ranges, consumers should read from the pre-aggregated Athena tables — a full-day scan of raw metering is ~10× more expensive than the equivalent daily rollup query:

Table Grain Written by Use for
metering_hourly hour × config_version × service_api × unit Hourly rollup Lambda, at N+1:05 UTC for hour N Ranges of 2h to 24h — cost per service/unit
metering_daily day × config_version × service_api × unit Daily rollup Lambda, at D+1 00:15 UTC for day D Ranges of > 24h — cost per service/unit
metering_docs_hourly hour × config_version Hourly rollup Lambda, alongside metering_hourly Ranges of 2h to 24h — doc counts and pages
metering_docs_daily day × config_version Daily rollup Lambda, alongside metering_daily Ranges of > 24h — doc counts and pages
control_plane_hourly hour × function_name × component × bedrock_model Same rollup Lambda, from CloudWatch metrics Per-Lambda control-plane cost attribution
data_plane_lambda_hourly hour × function_name × component Same rollup Lambda, from CloudWatch AWS/Lambda metrics for idp:plane=data Lambdas Per-Lambda data-plane compute cost — Lambda GB-seconds only (Bedrock/Textract API costs are already in metering_hourly per-doc, omitted here to avoid double-count)

Aggregate columns on metering_hourly / metering_daily (cost per service/unit): sum_value, sum_cost.

Aggregate columns on metering_docs_hourly / metering_docs_daily (doc-grain volume/pages/runs): n_docs, sum_pages, n_runs. number_of_pages is stamped identically on every metering row per document, so aggregating it at the (service_api, unit) grain of metering_hourly would fan the page count out by the number of rows a doc touches; the metering_docs_* tables aggregate via a MAX(number_of_pages) GROUP BY document_id subquery to collapse the fan-out.

Naming note: on metering_docs_hourly, n_docs is COUNT(DISTINCT document_id) within that hour. On metering_docs_daily, n_docs = SUM(hourly n_docs), i.e. a "doc-hours" count (a document processed across two hours counts twice). For strict cross-day unique-doc counts, query raw metering with COUNT(DISTINCT document_id).

n_runs counts document runs — one run is one (document_id, timestamp) pair in raw metering, so reprocessing a document raises n_runs without raising n_docs. Unlike n_docs it is additive: a run falls in exactly one hour, so SUM(n_runs) is exact at either grain and over any range it fully covers.

⚠️ n_runs is NULL for partitions older than the data-mart migration window, and SUM skips NULLs — so a query reaching past that boundary returns a number covering only part of the range, with nothing marking it short. Select COUNT(*) and COUNT("n_runs") alongside any SUM("n_runs") and treat them differing as "partially recorded". The column carries the same warning as its Glue comment, so it is visible from the catalog too. See docs/reporting-sql-layer.md for the measurements behind both claims.

See docs/reporting-sql-layer.md for the consumer tier picker (<2h → raw, 2-24h → hourly, >24h → daily), the tagging model that drives control-plane discovery, and the automated migration path (a CFN custom resource) for the new hour partition key.

Cost Calculation and Pricing

The metering table now includes automated cost calculation capabilities:

  • unit_cost: Retrieved from pricing configuration for each service_api/unit combination

  • estimated_cost: Automatically calculated as value × unit_cost for each record

  • Dynamic Pricing: Costs are loaded from configuration and cached for performance

  • Unpriced services: When the pricing configuration has no entry for a service_api, unit_cost and estimated_cost are written as NULL (not 0.0), and a warning naming the service is logged. SUM() ignores NULLs, so totals are unaffected, but the gap can be found rather than being read as free usage:

    SELECT DISTINCT service_api, unit
    FROM metering
    WHERE unit_cost IS NULL;   -- add pricing entries for anything listed here

    A unit absent from an entry that exists is a genuine 0.0 only when it is declared non-chargeable for that service, or when its metered count is 0 (a count of zero costs nothing at any rate). The declaration lives in one place, idp_common.metering_units, and each entry records the authority behind it. The basis on which that set was populated is that a unit appearing in no priced entry of AWS's published price list is treated as non-chargeable: Bedrock's totalTokens and requests are there because no Bedrock usagetype names a total token count, and on-demand model inference is priced in tokens, images or video and never per request.

    ⚠️ That is an accepted assumption, not something the price list states. A price list catalogues published SKUs; it does not say what AWS bills. So a unit AWS charges for and publishes no dimension for is treated as free here and billed at $0.00, and that risk is accepted rather than eliminated. It is bounded in one partial, measured way: every token unit the shipped Bedrock pricing prices does appear as a published Bedrock dimension, so on that service every unit known to be chargeable is listed. The non-Bedrock units this project meters — pages, documents, gb_seconds, and Lambda requests — were not checked against their own services' price lists.

    The two prompt-cache units are decided per model rather than per service, because that is the level at which the question has an answer: AWS prices a cache read at $0.625 per million tokens on Amazon Nova Premier and publishes no cache dimension at all for Claude 3 Haiku, in any of the seventeen regions it serves it from. Eleven shipped Bedrock entries omit at least one of the two cache rates, and each omission now records, per unit, which price-list model it was read against and whether AWS publishes a non-zero rate for the dimension. Where AWS publishes none, the omission is a true 0.0. Where AWS does publish a rate, the unit is written as NULL and appears in the query above — the omission is justified only by the model being unable to report the unit on this code path, and a count that nevertheless turns up is a fact worth seeing rather than a zero. A cache unit omitted from a Bedrock entry that has no recorded basis — a pricing row added later, or your own edited pricing table — is NULL for the same reason.

    ⚠️ The web UI's document cost table does not draw this distinction yet. It carries its own port of the cost lookup and still shows $0.00 for a unit a pricing entry omits, so a row this table records as NULL appears there as free with no mark. Treat the metering table as the authority on which of the two a $0.00 is.

    Any other unit an entry omits is written as NULL, not 0.0, and appears in the query above. That matters because Bedrock's usage block grows: a numeric field a future model returns reaches the metering table automatically, and if its absence from your pricing configuration were read as "free" it would be billed at $0.00 with nothing to distinguish it from a unit AWS does not charge for. If you see a unit listed by that query, add its rate to the pricing configuration for that model.

Pricing Configuration Format

Pricing data is loaded from the system configuration in the following format:

pricing:
  - name: "bedrock/us.anthropic.claude-3-sonnet-20240229-v1:0"
    units:
      - name: "inputTokens"
        price: "3.0e-6"    # $0.000003 per input token
      - name: "outputTokens"
        price: "1.5e-5"    # $0.000015 per output token
  - name: "textract/analyze_document"
    units:
      - name: "pages"
        price: "0.0015"    # $0.0015 per page

Cost Calculation Process

  1. Service Matching: The pricing key is matched exactly against service_api, then against progressively shorter /-delimited suffixes of it (longest match wins)
  2. Unit Matching: The unit name is matched exactly within that entry — there is no fuzzy or substring matching in either step, so one model can never be priced off another model's row
  3. Cost Calculation: estimated_cost = value × unit_cost
  4. Caching: Pricing data is cached to avoid repeated configuration lookups

Note on cache-read pricing. Prompt-cache reads are billed at a fraction of fresh input tokens (about 0.1x for Claude, 0.25x for Nova), so pricing.yaml carries a separate cacheReadInputTokens rate per model. Releases before this fix resolved pricing by substring, which could bind a cacheReadInputTokens lookup to a row's inputTokens rate and overstate the cost of cache reads by up to 10x. This only happened for a model id that had no exact pricing entry of its own, or whose entry omitted cacheReadInputTokens; a model with a complete entry was always priced correctly. Among shipped, selectable models the live case was the EU Nova 2 Lite service tiers (eu.amazon.nova-2-lite-v1:0:flex and :priority), which had no pricing rows and so billed cache reads at the EU base-tier fresh-input rate — 8x their real price. If your reported costs came from an earlier release and you used one of those, re-run the report for corrected numbers.

The metering table is particularly valuable for:

  • Cost analysis and allocation - Track spending by document type, service, or time period
  • Usage pattern identification - Analyze consumption patterns across different models
  • Resource optimization - Identify cost-effective processing approaches
  • Performance benchmarking - Compare cost efficiency across different document types and sizes
  • Budget monitoring - Track actual costs against budgets and forecasts

Document Sections Tables

The document sections tables store the actual extracted data from document sections in a structured format suitable for analytics. These tables are automatically created when new section types are encountered during document processing, eliminating the need for manual table creation.

Automatic Table Creation

When a document is processed and a new section type (classification) is detected, the system automatically:

  1. Creates a new Glue table for that section type (e.g., document_sections_invoice, document_sections_receipt, document_sections_w2)
  2. Configures the table with appropriate schema based on the extracted data
  3. Sets up partition projection for efficient date-based queries
  4. Updates the table schema if new fields are detected in subsequent documents

Important: Section type names are normalized to lowercase for consistency with case-sensitive S3 paths. For example, a section classified as "W2" will create a table named document_sections_w2 with data stored in document_sections/w2/.

Dynamic Section Tables

Document sections are stored in dynamically created tables based on the section classification. Each section type gets its own table with the following characteristics:

Common Metadata Columns:

Column Type Description
section_id string Unique identifier for the section
document_id string Unique identifier for the document
section_classification string Type/class of the section
section_confidence double Confidence score for the section classification
timestamp timestamp When the document was processed
config_version string Configuration profile used for processing

Dynamic Data Columns: The remaining columns are dynamically inferred from the JSON extraction results and vary by section type. Common patterns include:

  • Nested JSON objects are flattened using dot notation (e.g., customer.name, customer.address.street)
  • Arrays are converted to JSON strings
  • Primitive values (strings, numbers, booleans) are preserved as their native types

Partitioning: Each section type table is partitioned by date (YYYY-MM-DD format) for efficient querying.

File Organization:

document_sections/
├── invoice/
│   └── date=2024-01-15/
│       ├── doc-123_section_1.parquet
│       └── doc-456_section_3.parquet
├── receipt/
│   └── date=2024-01-15/
│       └── doc-789_section_2.parquet
└── bank_statement/
    └── date=2024-01-15/
        └── doc-abc_section_1.parquet

Prompt-cache efficiency per class

Every section that made a Bedrock extraction call carries metadata.prompt_cache (see configuration.md), so each document_sections_<class> table has the columns "metadata.prompt_cache.state" (caching | write-only | never-cached | disabled | no-cache-point | no-cache-data), "metadata.prompt_cache.cache_point_sent", "metadata.prompt_cache.read_share", "metadata.prompt_cache.input_tokens", "metadata.prompt_cache.cache_read_input_tokens", "metadata.prompt_cache.cache_write_input_tokens", "metadata.prompt_cache.requests" and "metadata.prompt_cache.min_cacheable_prefix_tokens". Column names contain dots, so quote them, and the flattener stores every leaf as a string (model_ids as a JSON list), so cast before aggregating. A class that is never-cached across most documents has a prompt prefix below its model's minimum; a class that is mostly write-only arrives less than once per 5-minute TTL.

SELECT "metadata.prompt_cache.state"                                            AS cache_state,
       COUNT(*)                                                                  AS sections,
       ROUND(AVG(CAST("metadata.prompt_cache.read_share" AS double)), 3)         AS avg_read_share,
       SUM(CAST("metadata.prompt_cache.cache_write_input_tokens" AS bigint))     AS tokens_written,
       SUM(CAST("metadata.prompt_cache.cache_read_input_tokens" AS bigint))      AS tokens_read
FROM document_sections_invoice
WHERE date >= '2026-09-01'
GROUP BY 1
ORDER BY sections DESC;

Crawler Configuration

The AWS Glue Crawler automatically discovers new section types and creates corresponding tables. The crawler can be configured to run:

  • Manually (on-demand)
  • Every 15 minutes
  • Every hour
  • Daily (default)

This ensures that new section types are automatically available for querying without manual intervention.

Using the Reporting Database with Athena

Amazon Athena provides a serverless query service to analyze data directly in Amazon S3. The reporting database tables are automatically registered in the AWS Glue Data Catalog, making them immediately available for querying in Athena.

To use the reporting database with Athena:

  1. Open the Amazon Athena console
  2. Select the database named after your stack (e.g., idp_reporting)
  3. Start querying the tables using standard SQL

Sample Queries

Here are some example queries to get you started.

Semantic note: in metering, WHERE date = 'X' filters on completion time. Every sample below that filters metering by date is asking "docs completed in this range", not "docs queued". For queue-time semantics, filter on the initial_event_time column instead.

Cross-table date divergence — read this before joining tables by date. metering partitions on completion time (write time); the sibling reporting tables evaluation_metrics/* and document_sections_* still partition on queue time (initial_event_time). A document processed across midnight lands in a different partition depending on which table you look at. If you're joining metering with any of those tables, either: (1) filter each side by its own partition and accept the day-boundary skew, (2) join on document_id alone and filter downstream by a single semantic (either metering.timestamp or metering.initial_event_time), or (3) prefer daily/weekly aggregates where the skew averages out.

Performance note: for wide date ranges (>24h), prefer metering_daily / metering_hourly over raw metering — same shape of query, but KB-scale scan instead of GB-scale. See Reporting SQL Layer for the tier picker.

Overall accuracy by document type:

SELECT 
  section_type, 
  AVG(accuracy) as avg_accuracy, 
  COUNT(*) as document_count
FROM 
  section_evaluations
GROUP BY 
  section_type
ORDER BY 
  avg_accuracy DESC;

Token usage by model:

SELECT 
  service_api, 
  SUM(CASE WHEN unit = 'inputTokens' THEN value ELSE 0 END) as total_input_tokens,
  SUM(CASE WHEN unit = 'outputTokens' THEN value ELSE 0 END) as total_output_tokens,
  SUM(CASE WHEN unit = 'totalTokens' THEN value ELSE 0 END) as total_tokens,
  COUNT(DISTINCT document_id) as document_count
FROM 
  metering
WHERE 
  context = 'Extraction'
GROUP BY 
  service_api
ORDER BY 
  total_tokens DESC;

Extraction confidence vs. accuracy:

SELECT 
  CASE 
    WHEN CAST(confidence AS double) < 0.7 THEN 'Low (<0.7)'
    WHEN CAST(confidence AS double) < 0.9 THEN 'Medium (0.7-0.9)'
    ELSE 'High (>0.9)'
  END as confidence_band,
  AVG(CASE WHEN matched THEN 1.0 ELSE 0.0 END) as accuracy,
  COUNT(*) as attribute_count
FROM 
  attribute_evaluations
WHERE 
  confidence IS NOT NULL
GROUP BY 
  CASE 
    WHEN CAST(confidence AS double) < 0.7 THEN 'Low (<0.7)'
    WHEN CAST(confidence AS double) < 0.9 THEN 'Medium (0.7-0.9)'
    ELSE 'High (>0.9)'
  END
ORDER BY 
  confidence_band;

Token usage per page by document type:

SELECT 
  se.section_type,
  AVG(m.value / m.number_of_pages) as avg_tokens_per_page
FROM 
  metering m
JOIN 
  section_evaluations se ON m.document_id = se.document_id
WHERE 
  m.unit = 'totalTokens'
  AND m.number_of_pages > 0
GROUP BY 
  se.section_type
ORDER BY 
  avg_tokens_per_page DESC;

Document sections analysis by type:

-- Query invoice sections for customer analysis
SELECT 
  document_id,
  section_id,
  "customer.name" as customer_name,
  "customer.address.city" as customer_city,
  "total_amount" as invoice_total,
  date
FROM 
  invoice
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY 
  date DESC;

Section processing volume by date:

-- Count sections processed by type and date
SELECT 
  date,
  section_classification,
  COUNT(*) as section_count,
  COUNT(DISTINCT document_id) as document_count
FROM (
  SELECT date, section_classification, document_id FROM invoice
  UNION ALL
  SELECT date, section_classification, document_id FROM receipt
  UNION ALL
  SELECT date, section_classification, document_id FROM bank_statement
)
GROUP BY 
  date, section_classification
ORDER BY 
  date DESC, section_count DESC;

Date range queries with new partition structure:

-- Efficient date range query using single date partition
SELECT 
  COUNT(*) as total_documents,
  AVG(accuracy) as avg_accuracy
FROM 
  document_evaluations
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31';

-- Monthly aggregation
SELECT 
  SUBSTR(date, 1, 7) as month,
  COUNT(*) as document_count,
  AVG(accuracy) as avg_accuracy
FROM 
  document_evaluations
WHERE 
  date >= '2024-01-01'
GROUP BY 
  SUBSTR(date, 1, 7)
ORDER BY 
  month;

Cost analysis queries:

-- Total estimated costs by service API
SELECT 
  service_api,
  SUM(estimated_cost) as total_cost,
  AVG(estimated_cost) as avg_cost_per_operation,
  COUNT(*) as operation_count,
  COUNT(DISTINCT document_id) as document_count
FROM 
  metering
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  service_api
ORDER BY 
  total_cost DESC;

-- Cost per page analysis by document type
--
-- Note the COALESCE and the unpriced_rows column. `estimated_cost` is NULL for a
-- service with no pricing entry, and SUM() skips NULLs while the denominator counts
-- those rows' pages regardless — so a plain SUM(estimated_cost)/SUM(number_of_pages)
-- silently understates cost per page whenever any row is unpriced. COALESCE makes
-- the assumption explicit (treat unpriced as $0) and unpriced_rows tells you whether
-- that assumption cost you anything: if it is not 0, the figure is a lower bound.
SELECT 
  se.section_type,
  SUM(COALESCE(m.estimated_cost, 0)) / SUM(m.number_of_pages) as cost_per_page,
  SUM(COALESCE(m.estimated_cost, 0)) as total_cost,
  SUM(m.number_of_pages) as total_pages,
  COUNT_IF(m.unit_cost IS NULL) as unpriced_rows,
  COUNT(DISTINCT m.document_id) as document_count
FROM 
  metering m
JOIN 
  section_evaluations se ON m.document_id = se.document_id
WHERE 
  m.number_of_pages > 0
  AND m.date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  se.section_type
ORDER BY 
  cost_per_page DESC;

-- Daily cost trends
SELECT 
  date,
  SUM(estimated_cost) as daily_cost,
  COUNT(DISTINCT document_id) as documents_processed,
  SUM(estimated_cost) / COUNT(DISTINCT document_id) as avg_cost_per_document
FROM 
  metering
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  date
ORDER BY 
  date;

-- Most expensive documents
SELECT 
  document_id,
  SUM(estimated_cost) as total_document_cost,
  SUM(value) as total_units_consumed,
  COUNT(*) as operations_count,
  MAX(number_of_pages) as page_count
FROM 
  metering
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  document_id
ORDER BY 
  total_document_cost DESC
LIMIT 10;

-- Cost efficiency by model (cost per token)
--
-- Same NULL caveat as the cost-per-page query above: without COALESCE the numerator
-- drops unpriced rows while the denominator keeps their token counts, so a model with
-- no pricing entry appears cheap rather than unknown. unpriced_rows > 0 means the
-- ratio is a lower bound.
SELECT 
  service_api,
  SUM(COALESCE(estimated_cost, 0)) / SUM(value) as cost_per_token,
  SUM(COALESCE(estimated_cost, 0)) as total_cost,
  SUM(value) as total_tokens,
  COUNT_IF(unit_cost IS NULL) as unpriced_rows,
  COUNT(DISTINCT document_id) as document_count
FROM 
  metering
WHERE 
  unit IN ('inputTokens', 'outputTokens', 'totalTokens')
  AND date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  service_api
ORDER BY 
  cost_per_token ASC;

-- Cost breakdown by processing context
SELECT 
  context,
  SUM(estimated_cost) as total_cost,
  COUNT(DISTINCT document_id) as document_count,
  SUM(estimated_cost) / COUNT(DISTINCT document_id) as avg_cost_per_document
FROM 
  metering
WHERE 
  date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  context
ORDER BY 
  total_cost DESC;

-- Cost analysis by configuration profile
SELECT 
  config_version,
  COUNT(DISTINCT document_id) as document_count,
  SUM(estimated_cost) as total_cost,
  AVG(estimated_cost) as avg_cost_per_record
FROM 
  metering
WHERE 
  date >= '2024-01-01'
GROUP BY 
  config_version
ORDER BY 
  total_cost DESC;

Configuration profile analysis:

-- Compare accuracy across configuration profiles
SELECT 
  config_version,
  AVG(accuracy) as avg_accuracy,
  AVG(f1_score) as avg_f1_score,
  COUNT(DISTINCT document_id) as document_count
FROM 
  document_evaluations
WHERE 
  date >= '2024-01-01'
GROUP BY 
  config_version
ORDER BY 
  avg_f1_score DESC;

-- Filter documents by configuration profile
SELECT 
  document_id,
  section_classification,
  timestamp
FROM 
  document_sections_w2
WHERE 
  config_version = 'Production'
  AND date >= '2024-01-01'
LIMIT 100;

-- Cost vs quality by configuration profile
SELECT 
  m.config_version,
  AVG(e.weighted_overall_score) as avg_quality,
  SUM(m.estimated_cost) as total_cost,
  COUNT(DISTINCT m.document_id) as document_count
FROM 
  document_evaluations e
JOIN 
  metering m ON e.document_id = m.document_id AND e.config_version = m.config_version
WHERE 
  e.date >= '2024-01-01'
GROUP BY 
  m.config_version
ORDER BY 
  avg_quality DESC;

Creating Dashboards

For more advanced visualization and dashboarding:

  1. Use Amazon QuickSight to connect to your Athena queries
  2. Create interactive dashboards to monitor:
    • Extraction accuracy over time
    • Cost trends by document type
    • Performance metrics by model
    • Resource utilization patterns

You can also export query results to CSV or other formats for use with external business intelligence tools.