Citizen Developer ????????? BigQuery Scheduled Query ?????????????????????
Citizen Developer ????????? business users ??????????????????????????? professional developers ?????????????????????????????????????????? applications, reports, data pipelines ??????????????????????????????????????? ?????????????????? low-code/no-code tools BigQuery Scheduled Query ??????????????????????????????????????????????????? citizen developers ????????????????????????????????? data transformations ??????????????????????????????????????? SQL ????????????????????????????????????????????? Python ???????????? manage infrastructure
???????????? Citizen Developer ??????????????? ?????? bottleneck ????????? data team (??????????????????????????? engineer ??????????????? pipeline), Business users ?????????????????? data ?????????????????? (domain knowledge), ???????????????????????? (??????????????? report ???????????????????????????????????? ???????????????????????????????????????), ???????????????????????????????????? (????????????????????? hire engineers ???????????????), Innovation ????????? front-line (?????????????????????????????????????????????????????????????????????)
BigQuery ????????????????????????????????? citizen developers ??????????????? SQL ???????????????????????????????????????????????????????????????????????????????????????, ????????????????????? manage servers (serverless), Scheduled Queries ?????? automation ????????????, Connected Sheets ????????? BigQuery ????????? Google Sheets, Looker Studio ??????????????? dashboard ????????????????????? code
????????????????????? BigQuery ?????????????????? Citizen Developers
Setup environment ???????????????????????????????????????????????? citizen developers
# === Citizen Developer Environment Setup ===
# 1. Terraform: Create sandbox datasets
cat > citizen_dev_setup.tf << 'EOF'
# Sandbox dataset for citizen developers
resource "google_bigquery_dataset" "citizen_sandbox" {
dataset_id = "citizen_sandbox"
friendly_name = "Citizen Developer Sandbox"
description = "Safe environment for citizen developers to explore data"
location = "asia-southeast1"
default_table_expiration_ms = 2592000000 # 30 days auto-delete
labels = {
environment = "sandbox"
team = "citizen-dev"
}
}
# Production-ready dataset (curated by data team)
resource "google_bigquery_dataset" "analytics_gold" {
dataset_id = "analytics_gold"
friendly_name = "Analytics Gold Layer"
description = "Curated business-ready tables"
location = "asia-southeast1"
}
# IAM: Read-only access to gold, full access to sandbox
resource "google_bigquery_dataset_iam_member" "gold_reader" {
dataset_id = google_bigquery_dataset.analytics_gold.dataset_id
role = "roles/bigquery.dataViewer"
member = "group:citizen-developers@company.com"
}
resource "google_bigquery_dataset_iam_member" "sandbox_editor" {
dataset_id = google_bigquery_dataset.citizen_sandbox.dataset_id
role = "roles/bigquery.dataEditor"
member = "group:citizen-developers@company.com"
}
# Custom role with limited permissions
resource "google_project_iam_custom_role" "citizen_developer" {
role_id = "citizenDeveloper"
title = "Citizen Developer"
description = "Limited BigQuery access for citizen developers"
permissions = [
"bigquery.jobs.create",
"bigquery.tables.getData",
"bigquery.tables.list",
"bigquery.tables.create",
"bigquery.savedqueries.create",
"bigquery.savedqueries.list",
"bigquery.transfers.get",
]
}
# Cost controls: Custom quota
resource "google_bigquery_reservation" "citizen_quota" {
name = "citizen-dev-quota"
location = "asia-southeast1"
# Limit to 100 slots (prevent runaway queries)
slot_capacity = 100
edition = "STANDARD"
}
EOF
# 2. Create template scheduled queries
cat > templates/daily_summary.sql << 'SQL'
-- Template: Daily Sales Summary
-- Instructions: Replace {YOUR_DATE_COLUMN} and {YOUR_TABLE}
-- Schedule: every day 08:00
CREATE OR REPLACE TABLE citizen_sandbox.daily_summary AS
SELECT
DATE(order_date) AS report_date,
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM analytics_gold.orders
WHERE DATE(order_date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY report_date;
SQL
echo "Citizen developer environment configured"??????????????? Data Pipeline ???????????? SQL
???????????????????????? data pipelines ????????? citizen developers ????????????????????????
# === Citizen Developer SQL Pipelines ===
# 1. Weekly KPI Report (scheduled: every monday 07:00)
cat > pipelines/weekly_kpi.sql << 'SQL'
-- Weekly KPI Dashboard Data
-- Owner: Marketing Team
-- Schedule: Every Monday 07:00
-- Destination: citizen_sandbox.weekly_kpis
CREATE OR REPLACE TABLE citizen_sandbox.weekly_kpis AS
WITH weekly_data AS (
SELECT
DATE_TRUNC(order_date, WEEK) AS week_start,
product_category,
COUNT(*) AS orders,
SUM(amount) AS revenue,
COUNT(DISTINCT customer_id) AS customers,
AVG(amount) AS avg_order
FROM analytics_gold.orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY week_start, product_category
),
prev_week AS (
SELECT * FROM weekly_data
WHERE week_start = DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), WEEK)
),
two_weeks_ago AS (
SELECT * FROM weekly_data
WHERE week_start = DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK), WEEK)
)
SELECT
p.week_start,
p.product_category,
p.orders,
p.revenue,
p.customers,
p.avg_order,
-- Week-over-week change
ROUND(SAFE_DIVIDE(p.revenue - t.revenue, t.revenue) * 100, 1) AS revenue_wow_pct,
ROUND(SAFE_DIVIDE(p.customers - t.customers, t.customers) * 100, 1) AS customers_wow_pct
FROM prev_week p
LEFT JOIN two_weeks_ago t
ON p.product_category = t.product_category
ORDER BY p.revenue DESC;
SQL
# 2. Customer Segmentation (scheduled: every day 06:00)
cat > pipelines/customer_segments.sql << 'SQL'
-- RFM Customer Segmentation
-- Owner: CRM Team
CREATE OR REPLACE TABLE citizen_sandbox.customer_segments AS
WITH rfm AS (
SELECT
customer_id,
DATE_DIFF(CURRENT_DATE(), MAX(DATE(order_date)), DAY) AS recency_days,
COUNT(*) AS frequency,
SUM(amount) AS monetary
FROM analytics_gold.orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY customer_id
),
scored AS (
SELECT
*,
NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
NTILE(5) OVER (ORDER BY frequency) AS f_score,
NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm
)
SELECT
*,
CONCAT(CAST(r_score AS STRING), CAST(f_score AS STRING), CAST(m_score AS STRING)) AS rfm_score,
CASE
WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
ELSE 'Others'
END AS segment
FROM scored;
SQL
# 3. Inventory Alert (scheduled: every 6 hours)
cat > pipelines/inventory_alert.sql << 'SQL'
-- Low Inventory Alert
-- Owner: Operations Team
CREATE OR REPLACE TABLE citizen_sandbox.inventory_alerts AS
SELECT
p.product_id,
p.product_name,
p.current_stock,
p.reorder_level,
COALESCE(s.avg_daily_sales, 0) AS avg_daily_sales,
CASE
WHEN p.current_stock = 0 THEN 'OUT_OF_STOCK'
WHEN p.current_stock <= p.reorder_level THEN 'LOW_STOCK'
WHEN p.current_stock <= p.reorder_level * 2 THEN 'WARNING'
ELSE 'OK'
END AS stock_status,
SAFE_DIVIDE(p.current_stock, NULLIF(s.avg_daily_sales, 0)) AS days_of_stock,
CURRENT_TIMESTAMP() AS checked_at
FROM analytics_gold.products p
LEFT JOIN (
SELECT product_id, AVG(quantity) AS avg_daily_sales
FROM analytics_gold.daily_sales
WHERE sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY product_id
) s ON p.product_id = s.product_id
WHERE p.current_stock <= p.reorder_level * 2
ORDER BY days_of_stock ASC;
SQL
echo "Citizen developer pipelines ready"Self-Service Analytics
?????????????????????????????? self-service ?????????????????? citizen developers
#!/usr/bin/env python3
# self_service.py ??? Self-Service Analytics Platform
import json
import logging
from typing import Dict, List
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("selfservice")
class SelfServicePlatform:
def __init__(self):
pass
def tools_catalog(self):
return {
"bigquery_console": {
"skill_level": "Beginner",
"description": "??????????????? SQL queries ?????? browser",
"use_cases": ["Ad-hoc analysis", "Create scheduled queries", "Explore data"],
"learning_time": "2-4 ?????????????????????",
},
"connected_sheets": {
"skill_level": "Beginner",
"description": "????????? BigQuery data ?????? Google Sheets",
"use_cases": ["Pivot tables on big data", "Auto-refresh reports", "Familiar interface"],
"learning_time": "1-2 ?????????????????????",
"limitation": "10,000 rows extract limit",
},
"looker_studio": {
"skill_level": "Beginner-Intermediate",
"description": "??????????????? dashboard ???????????? drag-and-drop",
"use_cases": ["KPI dashboards", "Scheduled email reports", "Sharing with stakeholders"],
"learning_time": "4-8 ?????????????????????",
"cost": "?????????",
},
"dataform": {
"skill_level": "Intermediate",
"description": "SQL-based data pipeline ?????? BigQuery",
"use_cases": ["Data transformation", "Dependencies management", "Data quality tests"],
"learning_time": "1-2 ?????????",
"cost": "????????? (built-in BigQuery)",
},
"appsheet": {
"skill_level": "Beginner",
"description": "??????????????? app ????????? data ?????????????????????????????? code",
"use_cases": ["Data entry forms", "Mobile apps", "Workflow automation"],
"learning_time": "4-8 ?????????????????????",
},
}
def training_program(self):
return {
"week_1": {
"topic": "SQL Fundamentals",
"content": ["SELECT, WHERE, GROUP BY", "JOIN types", "Aggregate functions", "Date functions"],
"outcome": "Query data ????????? BigQuery ?????????",
},
"week_2": {
"topic": "BigQuery Specific Features",
"content": ["Partitioned tables", "Scheduled queries", "Connected Sheets", "Cost awareness"],
"outcome": "??????????????? scheduled query ?????????",
},
"week_3": {
"topic": "Data Visualization",
"content": ["Looker Studio basics", "Chart types", "Filters and date ranges", "Sharing"],
"outcome": "??????????????? dashboard ?????????",
},
"week_4": {
"topic": "Advanced Patterns",
"content": ["Window functions", "CTEs", "Data quality checks", "Best practices"],
"outcome": "??????????????? complete data pipeline ?????????",
},
}
platform = SelfServicePlatform()
tools = platform.tools_catalog()
print("Self-Service Analytics Tools:")
for name, info in tools.items():
print(f"\n {name} ({info['skill_level']}):")
print(f" {info['description']}")
print(f" Learning: {info['learning_time']}")
training = platform.training_program()
print(f"\nTraining Program (4 weeks):")
for week, info in training.items():
print(f" {week}: {info['topic']} ??? {info['outcome']}")Governance ????????? Guardrails
????????????????????????????????????????????? citizen developers
# === Governance Guardrails ===
cat > governance_guardrails.yaml << 'EOF'
citizen_developer_guardrails:
cost_controls:
max_bytes_per_query: "10 TB (prevent SELECT * on huge tables)"
query_timeout: "300 seconds"
daily_quota: "$50/user/day"
monthly_budget: "$500/team/month"
alerts: "Alert at 80% budget consumption"
implementation: |
-- BigQuery custom quota via Reservations
-- Or use org policy constraints
-- Monitor via INFORMATION_SCHEMA.JOBS
data_access:
principle: "Least privilege + sandbox"
read_only: ["analytics_gold (curated tables)"]
read_write: ["citizen_sandbox (personal/team tables)"]
no_access: ["raw data", "PII tables", "production pipelines"]
row_level_security: "Filter by department/region"
column_masking: "Mask PII columns (email, phone)"
quality_controls:
naming_convention:
tables: "{team}_{purpose}_{frequency}"
examples: ["marketing_weekly_kpi", "ops_daily_inventory"]
documentation: "????????? scheduled query ?????????????????? comment ??????????????????"
review_process: "Query ????????? affect production ???????????? review ????????? data team"
testing: "????????? --dry_run ???????????? run ????????????"
anti_patterns:
- pattern: "SELECT * FROM large_table"
prevention: "Training + query validator"
- pattern: "Cross join without filter"
prevention: "Query complexity limit"
- pattern: "Writing to production tables"
prevention: "IAM: no write access to production"
- pattern: "Sharing PII data in reports"
prevention: "Column-level security + DLP scanning"
EOF
python3 -c "
import yaml
with open('governance_guardrails.yaml') as f:
data = yaml.safe_load(f)
g = data['citizen_developer_guardrails']
print('Governance Guardrails:')
print(f'\nCost Controls:')
for k, v in g['cost_controls'].items():
if k != 'implementation':
print(f' {k}: {v}')
print(f'\nData Access:')
print(f' Read-only: {g[\"data_access\"][\"read_only\"]}')
print(f' Read-write: {g[\"data_access\"][\"read_write\"]}')
print(f' No access: {g[\"data_access\"][\"no_access\"]}')
print(f'\nAnti-patterns to prevent:')
for ap in g['anti_patterns'][:3]:
print(f' {ap[\"pattern\"]} ??? {ap[\"prevention\"]}')
"
echo "Governance guardrails configured"Training ????????? Best Practices
?????????????????? training citizen developers
#!/usr/bin/env python3
# citizen_dev_guide.py ??? Citizen Developer Guide
import json
import logging
from typing import Dict, List
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("guide")
class CitizenDevGuide:
def __init__(self):
pass
def sql_cheat_sheet(self):
return {
"basic_queries": [
{"pattern": "Count and Sum", "sql": "SELECT COUNT(*), SUM(amount) FROM orders WHERE date = CURRENT_DATE()"},
{"pattern": "Group By", "sql": "SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY COUNT(*) DESC"},
{"pattern": "Date Filter", "sql": "SELECT * FROM orders WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-06-30'"},
],
"intermediate": [
{"pattern": "Join Tables", "sql": "SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id"},
{"pattern": "Window Function", "sql": "SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank FROM products"},
{"pattern": "CTE", "sql": "WITH monthly AS (SELECT DATE_TRUNC(date, MONTH) AS month, SUM(amount) AS total FROM orders GROUP BY 1) SELECT * FROM monthly"},
],
"bigquery_specific": [
{"pattern": "Approximate Count", "sql": "SELECT APPROX_COUNT_DISTINCT(user_id) FROM events"},
{"pattern": "Unnest Array", "sql": "SELECT * FROM table, UNNEST(tags) AS tag"},
{"pattern": "Scheduled Query Param", "sql": "WHERE DATE(created_at) = @run_date"},
],
}
def success_metrics(self):
return {
"adoption": {
"active_citizen_devs": 45,
"scheduled_queries_created": 120,
"dashboards_created": 35,
"time_saved_hours_month": 200,
},
"quality": {
"queries_passing_review": "92%",
"avg_query_cost": "$0.12",
"data_freshness_sla_met": "98%",
},
"business_impact": {
"reports_self_served": "75% (was 20%)",
"data_team_ticket_reduction": "60%",
"time_to_insight": "2 hours (was 2 weeks)",
},
}
guide = CitizenDevGuide()
cheat = guide.sql_cheat_sheet()
print("SQL Cheat Sheet for Citizen Developers:")
for level, queries in cheat.items():
print(f"\n {level}:")
for q in queries:
print(f" {q['pattern']}: {q['sql'][:60]}...")
metrics = guide.success_metrics()
print(f"\nSuccess Metrics:")
for category, data in metrics.items():
print(f" {category}:")
for k, v in data.items():
print(f" {k}: {v}")FAQ ??????????????????????????????????????????
Q: Citizen Developer ????????????????????? data team ?????????????????????????
A: ????????? ?????????????????????????????? Citizen developers ?????????????????? low-value tasks (simple reports, ad-hoc queries) ????????? data team ???????????????????????? high-value (complex pipelines, ML models, architecture) ??????????????? data team ?????????????????????????????? "?????? report" ???????????? "??????????????? platform, governance, mentoring" Data team ????????????????????????????????????????????? Complex ETL pipelines, Data modeling ????????? architecture, Security ????????? governance, Performance optimization, ML/AI workloads ???????????????????????? ????????????????????????????????? citizen developer program ?????? data team ?????? productivity ????????????????????? 40-60% ?????????????????????????????????????????? report ???????????????
Q: ????????????????????? citizen developer ?????? query ???????????????????????????????
A: ???????????????????????? Cost quota ???????????? daily/monthly budget ????????? user/team ???????????? BigQuery Reservations ???????????? custom monitoring, Query validator ????????? --dry_run ???????????? run ?????????????????? estimated bytes, Table restrictions ????????? access ??????????????? curated tables (partitioned, clustered) ?????????????????? raw data, Training ?????????????????? SELECT ??????????????? columns ?????????????????????????????? ?????????????????? SELECT *, Monitoring alert ??????????????? query scan > threshold, Sandbox tables ???????????? expiration auto-delete ????????????????????? storage costs ???????????? ??????????????? ??????????????? culture ????????? cost-aware ??????????????????????????? technical controls
Q: ?????????????????????????????? Citizen Developer?
A: ?????????????????????????????? Business analysts ?????????????????? Excel/Sheets ???????????????????????????, Marketing/Sales ????????????????????? report KPIs, Operations ????????????????????? monitor inventory/logistics, Finance ????????????????????? financial reporting, Product managers ????????????????????? analyze user behavior ??????????????????????????? ???????????? data ????????? analytical thinking, ?????????????????????????????? SQL basics (2-4 ?????????????????????), ?????? domain knowledge ????????? data team ???????????????, ????????????????????? insights ???????????? (?????????????????????????????????????????????????????????) ???????????????????????? ???????????????????????????????????? complex ML models, ?????????????????????????????? real-time processing, ????????????????????????????????????????????? sensitive PII data ??????????????????
Q: ??????????????? Citizen Developer Program ??????????????????????
A: ????????????????????? ??????????????????????????? ??????????????? 5-10 ???????????????????????????????????????????????? teams, ??????????????? sandbox environment ?????????????????????????????? (read-only production, write sandbox), Training 4 ????????????????????? (SQL basics ??? BigQuery ??? Visualization ??? Best practices), Assign mentor ????????? data team 1:5 ratio, ??????????????????????????? use cases ??????????????? (weekly report, KPI dashboard), ??????????????? adoption, quality, business impact, ???????????? ??????????????? pilot ?????????????????? ???????????????????????????????????????????????? ??????????????? Executive sponsor ?????????????????????????????????, Data team buy-in (?????????????????? threat ????????????????????? partner), Clear governance (??????????????????????????? ????????????????????????????????????), Continuous support (office hours, Slack channel, documentation)
