Michele Andrighetti

Category: Data & SQL

  • Snapshots and data audit: a practical pattern

    DATA AUDIT · HISTORY · SQL

    When a value changes over time, the current state is often not enough. To understand what actually happened, you need to reconstruct previous snapshots and put them in order.

    Start from the right identifier

    The first problem is distinguishing the identifier of the current record from the key that represents the logical history of the object. A useful audit query starts from that relationship and rebuilds all the connected versions.

    Order before interpreting

    Snapshots become readable only when they are ordered in time. In many cases the comparison between one row and the previous one reveals the exact point where a status, type or amount changed.

    Bring the main record and its details together

    The main table is often not enough for analysis. Payments, titles or other details need to sit next to the snapshots while the origin of each field remains clear. Consistent aliases and explicit names reduce interpretation errors.

    From query to tool

    When the same investigation is repeated often, it is worth turning it into a small tool: the user selects the identifiers, the system builds the query and returns a workbook already formatted to make the history visible.

    Audit means making change visible, not simply extracting more rows.

  • Excel as a front-end for Oracle: when it makes sense

    EXCEL · ORACLE · DATA INTERFACES

    Excel is often criticised when it sits close to a database, but the problem is rarely Excel itself. The real question is whether the workbook is acting as an interface over governed data or quietly becoming a second data system.

    When Excel is a good interface

    There are many situations where the database should remain the source of truth while Excel provides the operational surface. Users may need filters, a compact form, an export, a comparison between records, or a controlled way to launch a query without writing SQL.

    In this role Excel is not replacing Oracle. It is reducing the distance between the database and the person who needs to inspect or operate on a small part of it.

    The boundary matters

    The design starts to become fragile when the workbook stores copies of data that are treated as authoritative, embeds business rules in many formulas and macros, or requires users to synchronise changes manually. At that point the interface is no longer thin: it has become a parallel application with weak governance.

    I prefer a clear boundary. Oracle owns persistent data and relational logic. SQL retrieves the dataset that is needed. Power Query can shape it. VBA can coordinate interaction. Excel presents the result and collects only the parameters that belong to the user’s task.

    What I try to keep out of the workbook

    • Large duplicated datasets maintained by hand.
    • Credentials embedded in formulas or VBA.
    • Critical business rules repeated in several workbooks.
    • Write operations that bypass validation or audit.
    • Logic that cannot be explained without opening the VBA editor.

    A useful pattern

    A small workbook can work well when the flow is explicit: configuration → parameters → query → transformation → control → output. Each layer has one responsibility and can be changed independently. The workbook remains understandable because the difficult parts are not scattered across cells.

    This is especially useful for diagnostic tools and operational controls. The interface can be close to the user while the data remains where it belongs.

    When I would stop using Excel

    I would move away from Excel when many concurrent users need to update the same data, when permissions become complex, when the workflow requires server-side execution, or when the workbook itself has become a deployment problem. Those are signs that the interface is carrying more responsibility than it should.

    Excel is a good front-end when it makes the database easier to use without pretending to be the database.