Deloitte SQL Interview: Scalable Financial Reporting Schema Guide

by

Deloitte: How Do You Design a Scalable SQL Schema for Financial Reporting With Daily, Monthly & Quarterly Aggregations?

This question is frequently asked in Deloitte SQL Developer, Data Engineer, and BI Architect interviews. Deloitte’s financial analytics projects deal with extremely large datasets, multiple reporting cycles, regulatory requirements, and complex aggregations across daily, monthly, quarterly, and yearly periods.

Designing a scalable SQL schema for financial reporting requires deep understanding of data modeling, partitioning strategies, fact table design, date dimensions, indexing, materialized views, and incremental data loads. This guide covers all these areas in the way Deloitte expects candidates to answer.

Related Reading: You may also like our detailed guide on resolving circular dependency issues in Power BI (TCS Interview) .

For reference on SQL data warehouse standards, you can also visit Snowflake’s official data modeling guides.

1. Why Deloitte Focuses on Financial Aggregation Modeling

Deloitte handles auditing, taxation, corporate finance, mergers, risk, and advisory for global enterprises. Their financial reporting systems often need to:

  • Store 5–15 years of historical transaction data
  • Aggregate data across multiple hierarchies
  • Support high-frequency ETL loads
  • Produce dashboards for compliance & regulatory audits
  • Handle billions of rows

A well-designed SQL schema ensures:

  • Fast query performance
  • Low storage cost
  • Easy creation of KPIs
  • Accurate reporting
  • Smooth integration with BI tools like Power BI

2. Understand the Core Financial Reporting Requirements

Deloitte interviewers expect candidates to think like a financial analyst. Before designing the schema, understand reporting needs:

Common Financial Metrics:

  • Revenue, Cost, Profit
  • Operating Expense (OPEX)
  • Capital Expenditure (CAPEX)
  • EBITDA, EBIT, Net Profit
  • Taxation Adjustments
  • Cash Flow Metrics

Time-Based Requirements:

  • Daily transaction-based reports
  • Month-end closing reports
  • Quarterly performance evaluations
  • Year-end financial statements

Therefore, the schema must efficiently support all of these granularities.

3. Designing the Fact Table for Financial Transactions

The fact table is the backbone of the financial reporting schema.

Recommended Fact Table Structure:


CREATE TABLE FactFinancials (
    TransactionID BIGINT,
    AccountID INT,
    CostCenterID INT,
    DepartmentID INT,
    DateID INT,
    TransactionAmount DECIMAL(18,2),
    TransactionType VARCHAR(50),
    CreatedDate DATETIME,
    ModifiedDate DATETIME
);

Key Points:

  • Use surrogate keys for all dimensions
  • Store only numeric measures in fact tables
  • Partition the fact table by date (for faster reads)
  • Ensure proper indexing for AccountID, DateID, CostCenterID

4. Designing Dimension Tables for Financial Hierarchies

4.1 Date Dimension (Most Important)

A date dimension helps support:

  • Daily aggregations
  • Monthly aggregations
  • Quarterly & Yearly summaries
  • Financial calendar variations (4-4-5, etc.)

CREATE TABLE DimDate (
    DateID INT PRIMARY KEY,
    FullDate DATE,
    Year INT,
    Quarter INT,
    Month INT,
    MonthName VARCHAR(20),
    Day INT,
    IsWeekEnd BIT,
    FiscalYear INT,
    FiscalQuarter INT
);

4.2 Account Dimension

Used to categorize financial transactions.


CREATE TABLE DimAccount (
    AccountID INT PRIMARY KEY,
    AccountName VARCHAR(100),
    AccountType VARCHAR(50),
    GLCode VARCHAR(50)
);

4.3 Cost Center and Department Dimensions


CREATE TABLE DimCostCenter (
    CostCenterID INT PRIMARY KEY,
    CostCenterName VARCHAR(100),
    Manager VARCHAR(100),
    Region VARCHAR(50)
);

5. Designing Aggregation Tables (Daily, Monthly, Quarterly)

5.1 Daily Aggregation Table


CREATE MATERIALIZED VIEW mv_DailyFinancials AS
SELECT
    DateID,
    AccountID,
    SUM(TransactionAmount) AS DailyAmount
FROM FactFinancials
GROUP BY DateID, AccountID;

5.2 Monthly Aggregation Table


CREATE MATERIALIZED VIEW mv_MonthlyFinancials AS
SELECT
    d.Year,
    d.Month,
    f.AccountID,
    SUM(f.TransactionAmount) AS MonthlyAmount
FROM FactFinancials f
JOIN DimDate d ON f.DateID = d.DateID
GROUP BY d.Year, d.Month, f.AccountID;

5.3 Quarterly Aggregation Table


CREATE MATERIALIZED_VIEW mv_QuarterlyFinancials AS
SELECT
    d.Year,
    d.Quarter,
    f.AccountID,
    SUM(f.TransactionAmount) AS QuarterlyAmount
FROM FactFinancials f
JOIN DimDate d ON f.DateID = d.DateID
GROUP BY d.Year, d.Quarter, f.AccountID;

6. Partitioning Strategy (Required for Billion-Row Tables)

Deloitte typically partitions FactFinancials on:

  • DateID
  • FiscalYear
  • Quarter

PARTITION BY (FiscalYear, Quarter)

7. Indexing Strategy

Index on:

  • DateID
  • AccountID
  • CostCenterID
  • TransactionType

8. Short Interview-Friendly Answer

“I design a scalable financial reporting schema using a star schema with a partitioned FactFinancials table and denormalized dimensions for Date, Account, Cost Center, and Department. I use materialized views for daily, monthly, and quarterly summaries. I apply surrogate keys, proper indexing, and optimized SQL joins. This approach delivers fast performance for large Deloitte-level workloads.”

9. Conclusion

Designing a scalable SQL schema for financial reporting is essential for Deloitte projects. Using star schema, aggregations, materialized views, date dimensions, and proper indexing ensures fast and accurate reporting across daily, monthly, and quarterly levels.

Leave a comment