How do we set up Dev/Prod separation and Git-based CI/CD in Keboola to satisfy SOX-style change controls?

SOX-style controls demand more than “we have a dev project and a prod project.” Auditors want determinism: every change must be traceable from Git commit to production run, with clear separation of duties and the ability to roll back fast if something breaks.

In Keboola, you can get there without building your own DevOps layer. Dev/Prod separation, branching, and Git-based CI/CD are built into the platform, and you can wire them into your existing controls rather than fighting them.

Below is a practical blueprint I’d use with a finance or risk team that needs SOX-grade change management.

---

The control model in Keboola: how it maps to SOX expectations

Before jumping into “click here, click there,” it helps to map Keboola concepts to the control language auditors use.

• Projects → Environments and scope boundaries
  • Each Keboola Project is a self-contained workspace with its own storage, configurations, and processing pipelines.
  • This isolation is the first control boundary: no accidental cross-contamination of data or jobs between critical and non-critical workloads.
• Branches (Dev/Prod Mode) → Controlled sandboxes
  • Within a project, you create development branches that are full copies of your pipelines, using production data without risking production workloads.
  • Branches give you repeatable, testable change tracks instead of ad‑hoc experimentation on prod.
• Versioning + Git integration → Evidence of change control
  • Every configuration in Keboola is versioned by default; you can compare and roll back to earlier states.
  • Git integration and CLI let you enforce reviews, approvals, and automated tests before changes hit production.
• Execution logs & metadata → Audit trail
  • Keboola traces every execution, every table, every user.
  • These logs and lineage form the backbone of your SOX evidence: who changed what, when, why, and what ran as a result.

From a SOX perspective, the goal is to prove:

1. Changes are developed and tested in an isolated environment.
2. Promotion to production is controlled, reviewed, and approved.
3. Every production change is traceable back to a request, a change, and a reviewer.
4. You can roll back or re‑run deterministically when needed.

Keboola’s Dev/Prod separation and Git-based CI/CD are designed to satisfy exactly this.

---

Step 1: Decide your environment strategy (one project vs multi‑project)

You have two main patterns that both work with SOX‑style controls:

Option A: Single project with Dev/Prod branches (most common)

Use one Keboola project and treat:

• main (or prod) branch as production
• Feature branches (e.g., feature/new-allocation-rule) as development environments

When this fits:

• One legal entity or one “system of record” where a single, governed environment is acceptable.
• Strong need to reuse components, storage, and configurations without duplication.
• You already have a Git-based change management process and want Keboola to plug into it.

How SOX maps:

• Environment separation is logical (by branch) but enforced inside Keboola.
• Promotion is controlled through merge + deployment approvals.

Option B: Separate projects for Dev / QA / Prod (stricter isolation)

Use:

• Project A – Dev
• Project B – QA/UAT (optional)
• Project C – Prod

Migrate configurations across projects via the Keboola CLI and Git.

When this fits:

• Regulated institutions with hard separation policies (“no dev access to production systems”).
• Multiple teams with different access needs (e.g., external vendor in Dev, internal-only in Prod).

How SOX maps:

• Environment separation is physical (projects), plus logical (branches).
• Strongest story for auditors: separate credentials, storage, and access policies per environment.

If you’re starting fresh and want to move quickly while staying compliant, I’d begin with Option A and reserve multi‑project for the most critical financial reporting flows.

---

Step 2: Set up roles, access, and “separation of duties”

SOX cares who can:

• Develop and change logic
• Approve changes
• Deploy to production
• Operate and monitor production

In Keboola, use project roles and permissions to make this explicit.

Suggested role split

• Developers / Data Engineers
  • Can create and edit components, transformations, Flows.
  • Full access to development branches.
  • Read-only (or no) access to production branch configuration.
• Approvers / Owners (often finance or risk lead)
  • Read access to Dev branches.
  • Ability to approve and merge pull requests in Git.
  • No need to access low-level credentials.
• Ops / Platform Admins
  • Manage projects, credentials, and integration with your IdP and SIEM.
  • Control who can create new projects and integrations.
• Auditors (internal / external)
  • Read-only access or scheduled exports of logs, lineage, and Git history as evidence.

Ensure:

• Production API tokens are scoped and controlled by Ops.
• Developers do not hold the keys that can change production credentials or run ad‑hoc jobs in Prod without going through the CI/CD pipeline.

---

Step 3: Enable Dev/Prod mode and branching in Keboola

In your main Keboola project:

1. Create a production branch

   • Use the default main or rename to prod to match your enterprise naming.
   • This is the branch wired to production schedules and delivery targets.

2. Create development branches

   • For each change or feature, create a new branch (e.g., feature/new-revenue-classification).
   • Keboola provisions the branch instantly; no tickets, no waiting.
   • Branches reference the same storage and data, so you can test with real data while keeping production executions isolated.

3. Build and modify in the dev branch

   • Add/update transformations (SQL or Python workspaces).
   • Adjust Flows, mappings, or component configurations.
   • Run everything in the dev branch; production jobs remain untouched.

4. Validate outputs in dev

   • Write results into separate test tables (e.g., finance.gl_accounting_test).
   • Compare to production outputs with SQL checks or reconciliation reports.
   • Collaborate with finance/risk stakeholders directly in this branch until they sign off.

This pattern gives you full development freedom with zero risk to production.

---

Step 4: Connect Keboola to Git for version control and reviews

Keboola is versioned by default, but Git brings it into your company’s standard control framework.

4.1 Initialize the repository

1. Create a Git repo for your Keboola project (per project, per domain, or per team – choose a standard).
2. Use the Keboola CLI (kbc init) from your local machine or a CI runner to:
   • Connect the repo to your Keboola project.
   • Pull configurations (components, Flows, transformations) into versioned files.

4.2 Define your branching strategy in Git

Align Keboola branches with Git branches where possible. Typical pattern:

• main (or prod)
  Mirrors the configuration of the production branch in Keboola. Only CI/CD updates this.
• Feature branches (feature/*)
  Correspond to Keboola development branches, or at minimum to change sets that will be deployed.

Workflow:

1. Developer pulls the latest main from Git.
2. Developer creates a Git feature branch and a matching Keboola Dev branch.
3. Work occurs in the Keboola dev branch.
4. Developer pulls configs via CLI (kbc pull) into the Git branch.
5. Developer commits and pushes changes; opens a pull request.

4.3 Enforce review and approval rules

In Git (GitHub/GitLab/Bitbucket/Azure DevOps):

• Require at least one reviewer for PRs impacting production pipelines.
• Require status checks (CI pipeline success) before merge.
• Optionally, require approvals from a different role than the author (for separation of duties).

This review process, backed by Git history and Keboola’s versioning, is your SOX change‑approval backbone.

---

Step 5: Implement Git-based CI/CD for deterministic deployments

Now you’ve got Dev branches and Git; the missing piece is a CI/CD pipeline that deploys changes into Keboola production in a controlled, auditable way.

5.1 CI pipeline: validate before you deploy

When a PR is opened or updated:

1. Syntax and schema checks

   • Use kbc diff (or your own scripts) to compare configs vs main.
   • Run automated SQL linting or Python linters for transformations.
   • Optionally hit a dedicated test branch or test project in Keboola and run a full execution.

2. Data/test validation

   • Execute a subset of Flows in a Keboola dev branch.
   • Run reconciliation queries (e.g., totals by GL account, trial balance integrity).
   • Fail the CI job if validation queries don’t match expected tolerances.

All these steps become CI checks that must pass before anyone can merge the PR.

5.2 CD pipeline: controlled promotion to production

On merge to main (or prod) branch in Git:

1. Deploy configuration to Keboola production branch

   • Use the CLI in your CD job:
     • kbc sync or similar to push the repository state into Keboola’s production branch.
   • Use production-scoped tokens stored in your CI/CD secret store, not on dev laptops.

2. Run smoke tests in Prod

   • Trigger a small production run (e.g., a subset of data, or read‑only checks) to confirm the pipeline wiring is healthy.
   • Log success/failure, job IDs, and timestamps.

3. Update documentation & metadata

   • Optionally, synchronize change details (PR link, Jira ticket, approver) into Keboola’s metadata or your CMDB.
   • This gives auditors an end‑to‑end trace: Ticket → PR → CI checks → Deployment job → Keboola job IDs.

Because infrastructure is auto‑provisioned by Keboola, you don’t need to script cluster creation, scaling, or env setup. CI/CD pipelines stay lean and focused on governed configuration deployment.

---

Step 6: Use Keboola’s version control to support rollback & impact analysis

SOX expects you to recover quickly and explain impact when something goes wrong.

Keboola helps in two ways:

1. Platform-level versioning

   • Every change in Keboola is versioned by default.
   • You can:
     • Compare configuration versions across time.
     • Roll back a component, transformation, or Flow to a previous state.
   • This is particularly useful when emergency fixes are applied inside Keboola and need to be reconciled back to Git afterward.

2. AI-assisted impact analysis (optional)

   • Keboola’s AI support can help summarize what changed between versions and where it propagates downstream.
   • This is not a free pass around review, but it accelerates risk assessment before deploying.

For SOX, pair this with:

• A formal rollback playbook: who can trigger, what criteria, how you restore, and how you validate.
• CI/CD pipelines that can re-deploy the previous Git tag to Keboola prod branch.

---

Step 7: Build the audit trail: logs, lineage, and evidence

To convince auditors, you need more than “trust us, we use Git.”

Keboola’s active metadata and logging provide the evidence:

• Execution logs

  • For each job: who triggered it, when, with which configuration version.
  • Include these job IDs and timestamps in your change tickets or PR summaries.

• Lineage

  • Track tables and data products created by each transformation and Flow.
  • Show how a change in a transformation propagates into financial statements or board reports.

• Project isolation

  • Demonstrate that sensitive financial/reporting data lives in a governed project, separate from exploratory or local processing.

• External SIEM integration

  • Export security events and key operational logs to tools like Splunk, Datadog, or ELK.
  • This gives Security and Internal Audit a familiar place to monitor access, failures, and anomalies.

Together with Git logs and CI/CD logs, you get a complete story:

• Request / ticket
• PR + code review
• Automated tests and approvals
• Deployment job
• Production execution
• Resulting tables and reports

That’s the SOX‑style chain of custody you want.

---

Example: End-to-end SOX-compliant change in practice

To make it concrete, here’s how a typical change to a revenue recognition rule might flow:

1. Initiation
   • Finance raises a ticket: “Update revenue recognition for prepaid contracts.”
2. Development
   • Data engineer creates a Keboola branch feature/prepaid-revenue-rule.
   • Updates SQL transformation in that branch; writes outputs to _test tables.
   • Runs the Flow in the dev branch, shares test outputs with finance.
3. Testing & sign-off
   • Finance validates totals and sample contracts.
   • Engineer pulls changes via CLI, commits to Git, and opens a PR referencing the ticket.
4. Review & CI
   • CI pipeline runs SQL checks and integration tests in the dev branch.
   • A second engineer and a finance approver review and approve the PR.
5. Deployment
   • Merge to main triggers CD pipeline.
   • CLI pushes new configuration to Keboola production branch.
   • A smoke run is executed; Activity Center shows jobs completed successfully.
6. Evidence
   • Jira ticket links to PR, CI logs, and Keboola job IDs.
   • Keboola’s version history and lineage show exactly which pipelines and tables changed.

If an auditor asks “who changed the revenue accrual logic on March 10?”, you can show:

• The ticket,
• The Git diff,
• CI checks,
• Approvals,
• And the specific Keboola jobs that ran after deployment.

---

Putting it all together

To set up Dev/Prod separation and Git-based CI/CD in Keboola that satisfies SOX‑style change controls, focus on:

1. Clear environment boundaries
   • Use projects and branches to separate Dev and Prod.
2. Role-based access and separation of duties
   • Developers build; approvers review; CI/CD deploys; auditors read.
3. Branch-based development with real data, zero prod risk
   • Dev branches for experimentation and validation.
4. Git as the change authority
   • PRs, reviews, and CI checks become mandatory gates.
5. Deterministic deployment via CLI + CI/CD
   • Only pipelines can update production; no manual, untracked changes.
6. Comprehensive audit trail and rollback
   • Version history, lineage, execution logs, and external SIEM integration.

Done this way, Keboola gives you the speed of modern data development—no DevOps tickets, no infrastructure friction—inside a governance framework that stands up in a SOX or internal audit review.

Get Started