Skip to main content

Operations Runbook

Day-to-day and monthly procedures for maintaining Konsolidat.

Monthly Close Procedure​

1. Sync D365 Data​

Trigger an Airbyte sync to pull the latest GL entries and reference data.

From Excel Task Pane: Click "Trigger Pipeline" button.

From CLI:

# Via Airbyte API
curl -X POST http://localhost:8000/api/v1/connections/sync \
  -H "Content-Type: application/json" \
  -d '{"connectionId": "YOUR_CONNECTION_ID"}'

2. Run dbt Build​

After Airbyte sync completes:

cd /path/to/konsolidat/dbt_project
dbt build

This will:

  1. Build all 85 models (staging → bronze → silver → gold)
  2. Run all 150+ tests
  3. Report any failures

3. Verify Test Results​

Check for test failures:

dbt test

Critical tests for month-end:

  • assert_trial_balance_balances — debits = credits per entity/period
  • assert_silver_gl_debit_credit_balance — GL-level balance check
  • assert_spread_sums_to_annual — budget integrity
  • assert_ic_elimination_nets_zero — IC elimination check
  • assert_fctb_entity_layer_ties — consolidation integrity

4. Refresh Excel Reports​

In Excel: run EPM_ClearCache then EPM_RefreshAll (or Ctrl+Shift+R per sheet).

5. Review Consolidation​

Check consolidated trial balance for:

  • CTA amounts (non-zero when FX rates differ)
  • IC eliminations (should net to zero)
  • Top-side journals (balanced)
  • NCI split (correct ownership percentages)

Common dbt Commands​

CommandDescription
dbt buildFull build: run models + tests
dbt runRun models only (no tests)
dbt testRun tests only
dbt seedLoad/reload seed CSVs
dbt run --select gold_trial_balance+Run one model and downstream
dbt run --select tag:goldRun all gold models
dbt test --select gold_trial_balanceTest one model
dbt build --full-refreshDrop and rebuild all tables
dbt debugVerify dbt configuration and connectivity

Seed Management​

Updating Reference Data​

  1. Edit the CSV file in dbt_project/seeds/
  2. Run dbt seed to reload
  3. Run dbt build to rebuild dependent models

Key Seeds​

SeedWhen to Update
consolidation_groups.csvNew entity, ownership change
allocation_rules.csvNew allocation rule
allocation_drivers_*.csvMonthly driver values
budget_annual_input.csvAnnual budget cycle
spread_profiles.csvNew spread pattern
consolidation_adjustments.csvTop-side journals
ic_elimination_rules.csvNew IC patterns
entity_fiscal_calendars.csvNew entity or calendar change

ClickHouse Maintenance​

Check Database Sizes​

SELECT database, formatReadableSize(sum(bytes_on_disk)) as size
FROM system.parts
WHERE active
GROUP BY database
ORDER BY sum(bytes_on_disk) DESC

Check Table Row Counts​

SELECT database, table, formatReadableQuantity(sum(rows)) as rows
FROM system.parts
WHERE active AND database LIKE 'epm_%'
GROUP BY database, table
ORDER BY database, table

Optimize Tables (After Large Loads)​

OPTIMIZE TABLE epm_gold.gold_trial_balance FINAL

Check Running Queries​

SELECT query_id, elapsed, query
FROM system.processes
ORDER BY elapsed DESC

Frappe Maintenance​

Backup​

cd ~/frappe-bench
bench --site konsolidat.local backup

Backups are saved to ~/frappe-bench/sites/konsolidat.local/private/backups/.

Clear Cache​

bench --site konsolidat.local clear-cache

Restart Workers​

bench restart

Check Logs​

# Frappe web log
tail -f ~/frappe-bench/logs/web.log

# Worker log
tail -f ~/frappe-bench/logs/worker.log

# Frappe error log
tail -f ~/frappe-bench/logs/frappe.log

Troubleshooting Quick Checks​

IssueCheck
API returning errorstail ~/frappe-bench/logs/frappe.log
ClickHouse downdocker ps — check health status
dbt test failuredbt test --select test_name — read assertion
Stale dataCheck Airbyte sync status, re-trigger if needed
Slow queriesSELECT * FROM system.query_log ORDER BY query_duration_ms DESC LIMIT 10

Scheduled Tasks​

TaskFrequencyMethod
Airbyte D365 syncDaily / on-demandAirbyte scheduler or API
dbt buildAfter sync completesCron or Pipeline Run
Frappe backupDailybench backup via cron
ClickHouse backupWeeklyclickhouse-backup or volume snapshot
Log rotationWeeklyOS logrotate

Next Steps​