Skip to main content

BigQuery + Caching Example

This example shows how to expose BigQuery data as a fast, cost-effective API using flAPI's caching layer.

Scenario​

Problem:

  • Marketing team needs a dashboard API
  • Data in BigQuery (100GB table)
  • 10,000 API calls per day
  • Direct queries cost $500/day = $15,000/month

Solution:

  • Cache aggregated data in DuckDB
  • Refresh hourly
  • Cost: $1.20/day = $36/month
  • Savings: 99.76%

Architecture Flow​

Complete Setup​

1. Configuration​

flapi.yaml:

project_name: marketing-api
project_description: Cached BigQuery marketing data

template:
  path: './sqls'

connections:
  bigquery-marketing:
    init: |
      INSTALL 'bigquery';
      LOAD 'bigquery';
    properties:
      project_id: 'my-project-id'
      credentials_path: '${GOOGLE_APPLICATION_CREDENTIALS}'

duckdb:
  db_path: ./flapi_cache.db
  access_mode: READ_WRITE

2. Endpoint Configuration​

sqls/campaigns.yaml:

url-path: /campaigns/

# Enable caching
cache:
  enabled: true
  table: campaigns_cache
  schema: analytics
  schedule: 60m  # Refresh every hour
  template_file: campaigns_cache.sql

# API parameters
request:
  - field-name: country
    field-in: query
    description: Filter by country code (e.g., US, DE)
    required: false
    validators:
      - type: string
        pattern: '^[A-Z]{2}$'
  
  - field-name: campaign_type
    field-in: query
    description: Campaign type (email, social, search)
    required: false
    validators:
      - type: string
        enum: ['email', 'social', 'search', 'display']

template-source: campaigns.sql
connection:
  - bigquery-marketing

3. Cache Template​

sqls/campaigns_cache.sql:

-- This runs every hour to refresh the cache
-- Queries BigQuery once, saves to DuckDB

CREATE OR REPLACE TABLE analytics.campaigns_cache AS
SELECT 
    campaign_id,
    campaign_type,
    country,
    campaign_name,
    SUM(clicks) as total_clicks,
    SUM(conversions) as total_conversions,
    SUM(revenue) as total_revenue,
    ROUND(SUM(revenue) / SUM(clicks), 2) as revenue_per_click,
    MAX(last_updated) as last_updated
FROM bigquery_scan('my-project.marketing.campaigns')
WHERE date >= CURRENT_DATE - INTERVAL '30 days'
  AND active = true
GROUP BY 1, 2, 3, 4

4. API Template​

sqls/campaigns.sql:

-- This serves API requests from the cache (fast!)
-- No BigQuery cost per request

SELECT
    campaign_id,
    campaign_type,
    country,
    campaign_name,
    total_clicks,
    total_conversions,
    total_revenue,
    revenue_per_click,
    last_updated
FROM analytics.campaigns_cache
WHERE 1=1
{{#params.country}}
  AND country = '{{{params.country}}}'
{{/params.country}}
{{#params.campaign_type}}
  AND campaign_type = '{{{params.campaign_type}}}'
{{/params.campaign_type}}
ORDER BY total_revenue DESC
LIMIT 100

Running the Example​

Start flAPI​

# Set credentials
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json

# Start server
$ ./flapi -c flapi.yaml

✓ Loaded 1 endpoints
✓ Cache scheduled: campaigns (60m)
✓ Server listening on :8080

Test the API​

# All campaigns
$ curl http://localhost:8080/campaigns/

# US campaigns only
$ curl "http://localhost:8080/campaigns/?country=US"

# Social campaigns in Germany
$ curl "http://localhost:8080/campaigns/?country=DE&campaign_type=social"

Response:

{
  "data": [
    {
      "campaign_id": "c_12345",
      "campaign_type": "social",
      "country": "US",
      "campaign_name": "Summer Sale 2024",
      "total_clicks": 150000,
      "total_conversions": 1200,
      "total_revenue": 45000.00,
      "revenue_per_click": 0.30,
      "last_updated": "2024-01-15 10:30:00"
    }
  ]
}

Cost Analysis​

Without flAPI​

10,000 API calls/day × $0.05 = $500/day

Monthly cost: $15,000
Response time: 2-5 seconds

With flAPI​

Cache refresh: 24 times/day × $0.05 = $1.20/day
API serving: FREE (from cache)

Monthly cost: $36
Response time: 1-50ms

Savings: $14,964/month (99.76%)
Speed: 1000-10,000x faster

Monitoring Cache​

Check Cache Status​

$ flapii cache status

campaigns_cache:
  Rows: 12,450
  Size: 2.3 MB
  Last refresh: 5 minutes ago
  API calls since refresh: 1,234

Manual Refresh​

$ flapii cache refresh campaigns_cache

Refreshing cache: campaigns_cache
Source: BigQuery
✓ Query executed (2.3s)
✓ 12,450 rows loaded
✓ Cache updated

Next Steps​

🍪 Cookie Settings