Data Warehouse Maintenance

Data Warehouse Maintenance

Indian enterprises that commissioned data warehouses during the post-Budget 2024-25 analytics wave are now confronting the less-discussed half of the project: keeping the warehouse trustworthy over a multi-year life. Disciplined maintenance — spanning data quality checks, ETL monitoring, schema drift control, query performance tuning, cost governance and DPDP-aligned security — is what separates a defensible single source of truth from an expensive asset that no auditor, tax team or board committee will confidently rely on for material decisions.

Why Maintenance Is a Compliance-Grade Obligation in FY 2026-27

Tax authorities and statutory auditors are significantly more data-literate in 2026 than they were five years ago. The Income Tax Department's Annual Information Statement (AIS) and Taxpayer Information Summary (TIS) now aggregate data from more than forty third-party sources. GSTN cross-checks purchase registers against GSTR-2B in near-real time. SEBI's Integrated Surveillance Department pulls transaction data from multiple custodians simultaneously. Against this backdrop, your data warehouse is no longer merely a business intelligence tool — it is part of the audit trail.

The Companies Act, 2013 requires books of account to be maintained for eight years from the end of the relevant financial year (Section 128). SA 315 (Revised 2021), the auditing standard on understanding the entity and its environment, requires auditors to assess the reliability of the IT systems — including data warehouses — that produce financial data used in the statements. If your warehouse feeds the GST reconciliation workbook your CFO signs off on, or drives the intercompany elimination in the consolidation model, the statutory auditor will test whether the warehouse actually produces accurate and complete outputs.

The Directors' Responsibility Statement under Section 134(5) of the Companies Act certifies that adequate accounting controls exist. A warehouse that silently produces duplicated rows, broken dimension joins, or stale e-invoice data is a control failure, not a technical inconvenience. Frame your maintenance budget accordingly.

The Five Pillars of Warehouse Maintenance

Think of your programme as five concurrent workstreams, each needing a named owner, a written procedure, and a measurable KPI.

Pillar 1 — Data Quality

Data quality has five testable dimensions: completeness (are all expected rows present?), accuracy (do warehouse totals match source ledgers?), validity (do GSTINs pass the check-digit algorithm?), timeliness (did the GSTR-2B load land by the 14th as expected?), and uniqueness (are Invoice Reference Numbers — IRNs — duplicated?). For each dimension, define a threshold, automate a test, and route failures to an incident queue before any downstream report runs.

Pillar 2 — ETL Reliability and Monitoring

Extract, Transform, Load (ETL) pipelines degrade silently. API rate limits change, GSTN JSON schemas drift, and cloud infrastructure occasionally throttles overnight batch jobs. Instrument every pipeline with: job start/end timestamps, row counts extracted versus loaded, a schema hash of the source response, and a heartbeat alert if a scheduled load does not begin within an agreed window.

Pillar 3 — Query Performance

Fact tables in a mid-size enterprise grow at tens of millions of rows per quarter. Without partitioning by invoice date and clustering by GSTIN or cost centre, a single GSTR-9 reconciliation query can scan the entire table rather than a targeted partition — unnecessarily running up cloud costs and degrading response time for every concurrent user on the system.

Pillar 4 — Cost Governance

Cloud warehouse cost scales with storage volume and query-slot consumption. Both grow silently unless you tag, measure and constrain them. On a platform like BigQuery, un-governed query costs can double within a single financial year as new dashboards proliferate. Treat warehouse cost as a maintenance KPI that is reviewed on the same cadence as data quality scores.

Pillar 5 — Security, Access Control and DPDP Compliance

Under the Digital Personal Data Protection Act, 2023, and the DPDP Rules notified in 2025, personal data held in a warehouse — PAN numbers, Aadhaar identifiers (masked to last four digits under UIDAI guidelines), mobile numbers, email addresses — must have a documented lawful basis for processing, column-level masking for roles that do not require the raw value, tamper-evident access logs, and a documented retention-and-erasure schedule. These controls carry civil penalty exposure as notified under the Act; they are not optional hygiene.

Routine Maintenance Calendar: What to Do and When

Every Business Day

• Ingestion SLA check. Confirm that the prior day's GSTR-1 / IRP feed, ERP journal-entry extract, and bank-reconciliation file landed within the agreed window. Anything missing by 09:00 should open an incident automatically.
• Row-count delta check. Compare today's loaded row count against the 30-day rolling average for that source. A deviation of more than 20% in either direction indicates a source outage, a runaway duplicate, or a broken filter.
• Failed-job triage. Every ETL job that failed overnight must be root-cause analysed and either successfully re-run or escalated before the business day's first reporting run begins.

Every Week

• Query performance regression review. Pull the top-twenty longest-running queries from the prior seven days. Flag any query that slowed by more than 30% week-on-week. Investigate whether a partition has grown beyond its optimal size, a cluster key has drifted, or a materialised view has gone stale.
• Schema-drift alert review. Check whether any schema-hash mismatch fired against a GSTN, MCA V3 or ERP source during the week. If a new field appeared or an existing field changed data type, assess downstream impact and update the ingestion contract before the next production load.
• IRN uniqueness sweep. Run a uniqueness check on Invoice Reference Numbers in the purchase register. Duplicated IRNs are one of the most common silent data-quality failures and one of the most damaging, because they directly inflate ITC claims.

Every Month

• Ledger reconciliation. Compare the warehouse's aggregate purchase and sales totals for the prior month against the corresponding GSTR-3B figures. Any discrepancy exceeding Rs. 10,000 should be investigated and resolved before the next GSTR-3B filing date.
• Dimension hierarchy refresh. Vendor master, employee master and cost-centre hierarchies change constantly. Confirm that no GSTIN in your supplier dimension has moved from active to cancelled status in the GSTN database without a corresponding flag in the warehouse.
• Cost review. Pull the ten most expensive query patterns and the ten costliest datasets by storage. Assign a business owner to any orphaned dataset consuming cost with no active dashboard or report user.
• Access-delta review. Compare the current access list against the prior month. Any new role assignment or deletion that cannot be reconciled to a joiner-mover-leaver HR event must be investigated.

Every Quarter

• Full access audit. Reconcile all service-account and human-user access against the minimum-privilege principle. Rotate all service-account secrets and API keys. Log the rotation date, rotated-by name, and next-due date in your ISMS (Information Security Management System) register.
• DR drill. Execute the documented disaster-recovery procedure against a non-production replica. Measure the actual Recovery Time Objective (RTO) and Recovery Point Objective (RPO) achieved against the documented targets. Record the outcome formally — this is an auditor-ready control that many organisations promise but never actually test.
• DPDP retention sweep. Identify any personal-data dataset that has exceeded its documented retention period. Delete or anonymise on schedule. Record the deletion in your data-processing register.

Every Financial Year

• Retention policy rebaseline. Align retention schedules to the applicable statutory minima:
• GST records: six years from the due date of the annual return for the relevant year (Section 36 of the CGST Act, 2017)
• Income Tax: six assessment years as a practical safe harbour
• Companies Act: eight years from the end of the relevant financial year (Section 128)
• DPDP personal data: retain only as long as the stated purpose requires; delete on expiry or on receipt of a valid erasure request
• Platform commitment review. Renegotiate cloud reserved-capacity commitments based on actual slot consumption versus contracted capacity. Over-provisioned commitments are a direct and avoidable drain on the maintenance budget.

Managing Schema Drift from GST, MCA V3 and ERP Sources

Schema drift — the silent change in the structure of an upstream data source — is the single most common cause of undetected data corruption in Indian enterprise warehouses. GSTN has revised its e-invoice JSON schema multiple times since IRN generation became mandatory for larger taxpayers. The MCA V3 portal changed form structures during its migration from V2. ERPs release quarterly patches that rename columns, change data types, or introduce new mandatory fields.

How to detect drift before it corrupts production. Before every ingestion job, compute a cryptographic hash of the source schema — field names, data types, field order, and nullable flags. Compare it against the prior run's hash stored in a control table. Any mismatch fires an alert to the data engineering team before a single row is written to the target.

How to respond. Never let a schema-drifted payload land in the main fact table. Route the quarantined batch to a staging zone with a _quarantine suffix, assess the change, update the transformation logic, run regression tests against the prior month's data, and only then release to production. The cost of one corrupted quarterly fact table — detected during the GSTR-9/9C sign-off — vastly exceeds the cost of a one-day delay in ingestion.

GST-specific risk. If the IRP API response adds a new field linking the IRN to a revised e-way bill and your pipeline silently drops that field, freight-cost analysis and e-way bill reconciliation will be wrong for the entire quarter before anyone notices. The field was "optional" in the schema — but downstream, it was not.

ETL Monitoring: Catching Failures Before They Become Auditor Findings

Good ETL monitoring in 2026 is not a dashboard you check once a week. It is an active alerting system with defined response SLAs and documented runbooks.

Instrument these signals for every pipeline:

1. Job execution status — success, partial success, or failed
2. Rows extracted from source vs. rows successfully loaded to target
3. Rows rejected, with rejection reason code (schema mismatch, duplicate key, constraint violation)
4. Wall-clock duration vs. 30-day p90 baseline
5. Source API response time and HTTP error-rate over the run window

Illustrative alert thresholds:

• Job failed → immediate alert, on-call notification within five minutes
• Rejection rate >1% of loaded rows → alert within 30 minutes
• Job duration >150% of the 30-day p90 → alert within 60 minutes, investigate before the next dependent job starts

Runbooks are not optional. Every alert type must have a corresponding runbook entry that tells the on-call engineer: (a) the most likely cause, (b) how to confirm it in two minutes, and (c) the fix or escalation path. Without runbooks, alert fatigue sets in rapidly, and genuinely critical failures begin to be silently acknowledged and ignored.

DPDP-Aligned Governance Inside the Warehouse

The Digital Personal Data Protection Act, 2023, and the rules notified in 2025 create specific, enforceable obligations for any Indian organisation that stores personal data in a warehouse — regardless of whether the data originated in HR, CRM, e-commerce or loan origination systems.

Mandatory controls you must have in place:

1. Data inventory. Maintain a register of every table or dataset in the warehouse that contains personal data, the lawful basis for holding it, the data-principal category (employee, customer, vendor), and the documented retention period.
2. Purpose limitation. If customer PAN was collected for TDS deduction under Section 194Q of the Income-tax Act, 1961, it cannot be repurposed for a marketing analytics model without a distinct lawful basis. Enforce purpose boundaries at the column-tag level in your warehouse catalogue — not just in a policy document.
3. Column-level masking. Any role that does not require the raw value of PAN, Aadhaar or mobile number should receive a masked token (for example, ABCDE1234F → XXXXX1234F). Implement this in platform-native column-level security and test it as part of the quarterly access audit.
4. Breach-notification readiness. The Act requires notification to the Data Protection Board within the prescribed period following discovery of a personal-data breach. Your warehouse's access logs and data-lineage records are the primary evidence base for a breach investigation. They must be complete, tamper-evident, and retained for the prescribed period — and they must be regularly tested, not assumed to be working.
5. Tested erasure procedure. If a data principal submits a valid erasure request, you must locate and delete (or anonymise) every record referencing that individual across all warehouse tables, historical snapshots, and backup copies. Without a maintained data catalogue and a rehearsed erasure runbook, this is operationally impossible once the warehouse grows beyond a handful of datasets.

Worked Example: What Maintenance Failure Actually Costs

Consider a mid-size manufacturer with annual GST turnover of Rs. 120 crore. During FY 2025-26, their ETL pipeline for the purchase register had a deduplication bug that ran silently from April to September 2025 — six full months. The bug caused every purchase invoice received via the GSTR-2B download to be loaded twice into the fact table.

The financial exposure:

A weekly IRN uniqueness sweep and a monthly ledger reconciliation — both standard maintenance controls requiring no additional tooling beyond a scheduled SQL query — would have surfaced the duplication within days of it first occurring. The entire remediation cost was avoidable.

The additional risk that does not appear in the table: once an excess ITC claim appears on the GST portal, it can attract scrutiny under Section 61 (scrutiny of returns) or Section 73/74 (determination of tax not paid), even if the error was inadvertent and proactively reversed.

Common Mistakes and How to Fix Them

1. The "built and done" budget trap. Most Indian projects allocate a capital budget for warehouse construction and then provide no recurring maintenance allocation. When the build team rolls off, maintenance falls to whoever is available. Fix: include a maintenance cost line — typically 15–20% of the year-one build cost per subsequent year — in every business case from the outset.

2. No schema contract with source systems. Pipelines that simply "accept whatever comes from the API" will corrupt data silently when GSTN, MCA V3 or the ERP vendor updates their schema. Fix: define, document and version-control a schema contract for every source feed. Automate schema-hash comparison as a pre-ingestion gate.

3. Shared, un-rotated service accounts. A single service account used by five pipelines, with credentials unchanged for eighteen months, is a single point of compromise and a routine finding in IT audits. Fix: one service account per pipeline; rotate credentials on a quarterly schedule; store secrets in a vault, never in a config file or a code repository.

4. Ignoring cold-partition storage cost. Partitions from FY 2022-23 are queried at most a few times a year during historical analysis but are stored at the same active-tier cost as current-quarter data. Fix: automate the migration of partitions older than twelve months to a cold or archive storage tier. On most enterprise cloud platforms, this reduces storage cost by 60–80% with no change in query access — only query latency on those cold partitions increases.

5. No named business owner per dataset. When the engineer who built a pipeline departs, the dataset becomes an orphan — consuming storage cost, potentially containing stale data, and lacking anyone accountable for its accuracy or DPDP compliance. Fix: every dataset must have a named business owner recorded in the data catalogue. Ownership is a control. Fuzzy ownership is the root cause of most maintenance failures you will encounter in practice.

6. DR procedure tested once, never again. DR drills are scheduled in the annual calendar and then quietly deprioritised when the quarter turns busy. Fix: book a mandatory half-day for a partial DR test each quarter. Keep the full annual drill, but quarterly partial tests ensure the runbook stays current as the warehouse evolves.

Key Takeaways

• Maintenance is a compliance obligation, not optional housekeeping. Section 128 of the Companies Act, SA 315 (Revised 2021), and the DPDP Act, 2023 all place direct obligations on the reliability and governance of your data warehouse.
• Run a tiered maintenance calendar: daily ingestion SLA checks, weekly performance and schema-drift reviews, monthly ledger reconciliations, quarterly access audits and DR tests, annual retention rebaselings.
• Schema drift is the silent killer. Contract-test every source feed before ingestion and quarantine drifted payloads. Never let an unvalidated payload land in a production fact table.
• The financial cost of skipping maintenance compounds rapidly. A six-month deduplication bug at a mid-size manufacturer can generate Rs. 2+ crore in interest, penalties and professional fees — all avoidable with two routine SQL checks.
• DPDP compliance inside the warehouse is non-negotiable. Maintain a personal-data inventory, enforce column-level masking by role, and have a tested erasure procedure ready before a data-principal request forces the issue under deadline.
• Treat cost as a maintenance KPI. Tag every dataset, review the top-ten cost drivers monthly, and aggressively move cold partitions to archive tiers. Unmanaged warehouse cost is one of the most predictable and preventable cost-overrun categories in modern IT operations.
• Ownership is a control. Every dataset needs a named business owner accountable for accuracy, cost, and DPDP compliance. Maintenance fails not because the tools are missing but because accountability is diffuse.