The user has built a workable system, but "workable" is not the same as sustainable. The current process, pulling data from Snowflake into a master Excel file, cross-referencing with lookup tables that live only in that file, then copy-pasting filtered results into separate plant workbooks, is fragile, manual, and prone to the exact kind of errors that undermine trust in financial reporting. The user already knows this. The real issue is not whether Access or Excel can handle the job; it is whether the architecture treats the data as a shared, maintainable resource or as a series of handoffs that depend on one person and one open file.
Option 2, the Excel-only approach, has a hidden flaw that the user has already spotted but not fully resolved. The master Excel file would need to import the full Snowflake history to serve as a reference for the lookup tables. That means either the file stays open continuously (creating a single point of failure and a file-size nightmare) or it gets refreshed on a schedule, which introduces latency and dependency. Excel, even with Power Query, is not built to be a persistent query broker for a database of Snowflake's scale. The row limit is a real constraint, but the deeper problem is that Excel was designed as a personal productivity tool, not as a middleware layer for multi-site reporting. The moment you need to maintain lookup tables that multiple people can update without breaking the chain, you are asking Excel to be something it was never meant to be.
Option 1, the Access approach, is the more honest solution. Access is built to handle relational lookups, parameterized queries, and concurrent access patterns that Excel struggles with. The user's concern about maintainability is valid, Access has a steeper learning curve, but the trade-off is a system that does not collapse when one file is left open or when a lookup table needs a new row. More importantly, Access can be set up so that the categorizing tables live in the database itself, not in a separate Excel file that must be opened and refreshed. That means the plant workbooks can pull filtered, categorized data directly from Snowflake via Access, eliminating the copy-paste step entirely. The maintenance burden shifts from "don't break the master file" to "update a table in Access," which is a simpler, more recoverable failure mode.
The practical takeaway is this: stop treating Snowflake as a source that must be funneled through a single Excel bottleneck. Build the categorizing tables into Access, connect each plant workbook directly to Access via Power Query, and let the database handle the filtering and joining. It will take more upfront work, but it will remove the manual copy-paste step, reduce the risk of stale or corrupted data, and make the system maintainable by someone who understands Access basics. The user's instinct to rethink the workflow from the beginning is correct. The next step is to commit to a tool that matches the complexity of the problem, not the one that feels most familiar.