materialized-view

You are helping the user create and manage materialized views using Apache DataFusion.

Input: $@

A materialized view in this context is a SQL query whose results are persisted as a Parquet file, registered as an external table, and tracked for refresh. This is a lightweight, file-based approach — no database server needed.

Step 1 — Check datafusion-cli is installed

command -v datafusion-cli

If not found, delegate to /datafusion-skills:install-datafusion and then continue.

Step 2 — Resolve state directory

Look for an existing state file:

STATE_DIR=""
test -f .datafusion-skills/state.sql && STATE_DIR=".datafusion-skills"
PROJECT_ROOT="$(git rev-parse --show-toplevel 2>/dev/null || echo "$PWD")"
PROJECT_ID="$(echo "$PROJECT_ROOT" | tr '/' '-')"
test -f "$HOME/.datafusion-skills/$PROJECT_ID/state.sql" && STATE_DIR="$HOME/.datafusion-skills/$PROJECT_ID"

If no state directory exists, create one. Ask the user:

Where would you like to store DataFusion session state?

1. In the project directory (.datafusion-skills/) — colocated with the project
2. In your home directory (~/.datafusion-skills/<project-id>/) — keeps the repo clean

Create the chosen directory and initialize state.sql if it doesn't exist:

mkdir -p "$STATE_DIR"
touch "$STATE_DIR/state.sql"

Also create the materialized views directory and manifest:

mkdir -p "$STATE_DIR/views"
test -f "$STATE_DIR/views/manifest.json" || echo '{"views":{}}' > "$STATE_DIR/views/manifest.json"

Step 3 — Parse the command

Determine what the user wants:

• create <SQL or natural language> → create a new materialized view
• refresh <name> → refresh an existing materialized view
• status → show status of all materialized views
• list → list all materialized views
• drop <name> → remove a materialized view

If the input is just SQL or a natural language description, treat it as create.

Command: CREATE

3a — Generate SQL if needed

If the input is natural language, generate SQL. Use the session state to understand available tables:

datafusion-cli --file "$STATE_DIR/state.sql" -c "SHOW TABLES;" 2>/dev/null

3b — Derive a view name

Generate a descriptive snake_case name from the SQL (e.g., daily_trades_by_symbol). Ask the user to confirm or rename.

3c — Validate the query

Test that the SQL runs successfully:

datafusion-cli --file "$STATE_DIR/state.sql" -c "
$SQL LIMIT 1;
"

If it fails, diagnose the error and fix the SQL.

3d — Materialize as Parquet

datafusion-cli --file "$STATE_DIR/state.sql" -c "
COPY ($SQL) TO '$STATE_DIR/views/<view_name>.parquet' STORED AS PARQUET;
"

3e — Register as external table

Append to the state file so the view is available in future sessions:

cat >> "$STATE_DIR/state.sql" <<SQL
-- Materialized view: <view_name>
-- Source SQL: $SQL
CREATE EXTERNAL TABLE IF NOT EXISTS <view_name> STORED AS PARQUET LOCATION '$STATE_DIR/views/<view_name>.parquet';
SQL

3f — Update the manifest

Update $STATE_DIR/views/manifest.json to track the view:

python3 -c "
import json, datetime
manifest_path = '$STATE_DIR/views/manifest.json'
with open(manifest_path) as f:
    manifest = json.load(f)
manifest['views']['<view_name>'] = {
    'sql': '''$SQL''',
    'parquet_path': '$STATE_DIR/views/<view_name>.parquet',
    'created_at': datetime.datetime.now().isoformat(),
    'last_refreshed': datetime.datetime.now().isoformat(),
    'source_tables': []  # extracted from SQL
}
with open(manifest_path, 'w') as f:
    json.dump(manifest, f, indent=2)
"

3g — Verify

datafusion-cli --file "$STATE_DIR/state.sql" -c "
SELECT COUNT(*) AS row_count FROM <view_name>;
DESCRIBE <view_name>;
"

Report success with row count and schema.

Command: REFRESH

Refresh a specific view

Read the view's SQL from the manifest:

python3 -c "
import json
with open('$STATE_DIR/views/manifest.json') as f:
    manifest = json.load(f)
view = manifest['views'].get('<view_name>')
if view:
    print(view['sql'])
else:
    print('ERROR: view not found')
"

Then re-materialize:

datafusion-cli --file "$STATE_DIR/state.sql" -c "
COPY ($SQL) TO '$STATE_DIR/views/<view_name>.parquet' STORED AS PARQUET;
"

Update the last_refreshed timestamp in the manifest.

Report the new row count.

Command: STATUS

Show the status of all materialized views:

python3 -c "
import json, os, datetime
with open('$STATE_DIR/views/manifest.json') as f:
    manifest = json.load(f)
for name, view in manifest['views'].items():
    parquet = view['parquet_path']
    size = os.path.getsize(parquet) if os.path.exists(parquet) else 0
    size_mb = size / (1024 * 1024)
    print(f\"{name}:\")
    print(f\"  SQL: {view['sql'][:80]}...\")
    print(f\"  Last refreshed: {view['last_refreshed']}\")
    print(f\"  File size: {size_mb:.2f} MB\")
    print(f\"  Path: {parquet}\")
    print()
"

Also verify each view is queryable:

datafusion-cli --file "$STATE_DIR/state.sql" -c "SELECT COUNT(*) AS row_count FROM <view_name>;"

Command: LIST

python3 -c "
import json
with open('$STATE_DIR/views/manifest.json') as f:
    manifest = json.load(f)
for name in sorted(manifest['views']):
    view = manifest['views'][name]
    print(f\"  {name} — refreshed {view['last_refreshed'][:10]}\")
"

Command: DROP

Remove a materialized view:

1. Delete the Parquet file
2. Remove from manifest.json
3. Remove the CREATE EXTERNAL TABLE line from state.sql

rm -f "$STATE_DIR/views/<view_name>.parquet"
python3 -c "
import json
with open('$STATE_DIR/views/manifest.json') as f:
    manifest = json.load(f)
manifest['views'].pop('<view_name>', None)
with open('$STATE_DIR/views/manifest.json', 'w') as f:
    json.dump(manifest, f, indent=2)
"

Remove the corresponding lines from state.sql:

sed -i '' '/-- Materialized view: <view_name>/,/^$/d' "$STATE_DIR/state.sql"

Error handling

• Query fails → check if source tables exist, suggest /datafusion-skills:create-table to register them
• Disk space → warn if the Parquet file is > 1GB
• Manifest corrupt → regenerate from existing Parquet files in the views directory

Suggest next steps

After creating a view:

Your materialized view <view_name> is now available as a table. Query it with /datafusion-skills:query SELECT * FROM <view_name> LIMIT 10. To refresh it when source data changes, run /datafusion-skills:materialized-view refresh <view_name>.