I am a relatively new BI Manager. When I inherited this setup, the data engineers were using the AWS CDK to deploy materialized views and views in our BI Postgres database which we use as a data warehouse. From my research, the BI team should be using dbt to manage these data objects. When I asked the CTO about this, he said he'd rather not introduce new tools (dbt and AirFlow) when the current setup is working. For what I know, the CDK deployment via github can be automated for views, but not for materialized views because materialized views need to be manually dropped and created. I don't have enough knowledge to tell whether the current setup is 'working' or not. Would dbt + AirFlow work better? How could I articulate the advantages of dbt over the AWS CDK?

Not sure if relevant, but we also plan to switch to a data warehouse system (Snowflake, Redshift etc.) at some point.

Edited: figured additional information on the current setup would be helpful. I typed this out in an hour or two with the help of an LLM with an MCP server connected to our GitHub repository:

Our BI layer is a dedicated schema (let's call that BI_schema) in our production Aurora PostgreSQL reporting database, alongside dozens of source schemas. It holds the objects behind dashboards, scheduled reports, and ad hoc SQL access. We run two environments, Dev and Prod, and SQL changes are tested in Dev before they are deployed to Prod. We’re also building out Redshift (ingestion via DMS and zero-ETL) and I am evaluating dbt as the transformation layer. I’d like your assessment of whether our current use of CDK for deploying these objects is appropriate.

What’s in the production BI schema

• A few dozen materialized views (MVs) in the production BI schema, some very wide one-big-table style
• A bunch of views, including thin wrapper views. The pattern is MV, then a wrapper view, then downstream MVs and views. A wrapper can be repointed to a new MV version with CREATE OR REPLACE VIEW, which avoids a downstream rebuild
• Supporting objects: functions, indexes, roles, and grants
• Everything lives in one schema today. I plan to split it into staging, intermediate, and marts layers once we transition to a data warehouse platform

How deployment is supposed to work

• SQL files live in GitHub in three places:
  • Folder 1 holds one-shot, numbered changes, applied once each in numeric order
  • Folder 2 holds re-runnable library objects (views, functions, procedures, tables, schemas)
  • Folder 3 holds reference SQL that nothing deploys, which is where the MV code and the index-setting code live
• A pull request runs CI checks (convention validation, unit tests, linting, cdk synth). Nothing deploys from a PR
• Merging to main triggers a CDK-defined AWS CodePipeline. The Dev stage deploys automatically, and the Prod stage waits behind a manual approval, so Dev and Prod can be several changes apart
• Each stage uploads Folder 1 and Folder 2 to that environment’s S3 bucket with the CDK BucketDeployment construct. The upload triggers a Lambda
• The Lambda is a custom Python runner that does three things:
  • It applies pending migrations first and stops at the first failure
  • It then applies library objects in filename order, continuing past failures and re-applying any file whose checksum has changed
  • It records every file (name, checksum, object type, status, error) in a tracking table in the target schema
• To change an existing object, you add a new file with a higher number. There are no rollback scripts, so mistakes are corrected by rolling forward
• Access control (roles and grants) is applied by a SQL procedure that each deployed file calls at the end
• Policy requires all database changes to go through this path
• CDK provisions the machinery (pipeline, bucket, Lambda, IAM, VPC). The SQL itself isn’t modeled as CDK constructs, so CDK acts as both infrastructure-as-code and the host of the migration runner

Where the process breaks down

1. Materialized views are deployed by hand

Postgres has CREATE OR REPLACE VIEW but nothing equivalent for MVs. Changing an MV’s definition requires a drop and recreate, which can’t be expressed in a pipeline that applies files idempotently. A re-run CREATE MATERIALIZED VIEW fails, a DROP in the file would run on every deploy, and dropping without CASCADE is blocked by dependents. Recreating also means repopulating, which is slow for the wide MVs. So MV code is committed and reviewed in PRs, but only in the reference folder, and a data engineer then applies it by hand. The consequences are drift between the repo and the database, dependence on one person’s knowledge, and ordering failures. One view deployment failed because the MV it reads from had not been created yet. Views deploy normally.

2. Dependency management is manual. Objects are chained: MVs feed wrapper views, which feed other MVs. Changing an upstream MV means dropping every dependent object, then recreating them all in dependency order and repopulating the MVs. Our largest base MV has 25 downstream objects (12 MVs and 13 views) in Prod, at least four levels deep, so one change can mean rebuilding two dozen objects, and the rebuild is repeated in each environment. The pipeline deploys in filename order, not dependency order, so this sequence is run by hand. The git history shows commits on MV code under about 25 tickets in the last 6 months, roughly four to five a month. That count mixes new MVs with edits to existing ones, and I haven’t separated them.

3. Refresh scheduling has gaps. A control table drives MV refreshes through a stored procedure registered with pg_cron. None of these three pieces is in the repository, so they are neither version-controlled nor deployed. In Prod, 35 of 36 MVs have an enabled daily schedule and all are refreshing. One MV has no schedule, and four MVs have duplicate control rows, some with conflicting refresh hours. Duplicate rows have already caused a stalled refresh. Refresh order isn’t guaranteed, and exclusive locks are held across the whole batch. Again, someone else (a data engineer) manages this refresh schedule somewhere else, and because I don't see his code, I can't tell where things go wrong.

4. Pipeline reliability.

• The Prod tracking table holds 20 rows in a failed state: 14 views, 2 procedures, 2 tables, 1 schema, and 1 migration. 16 of the 20 are the same error, a duplicate-key collision in the tracking table itself. Reading the Lambda code, this looks structural. A file recorded as failed can’t later be recorded as succeeded, because the success insert collides with the existing failed row and the whole attempt is rolled back. The other five failures were a view whose MV didn’t exist yet, a view that already existed, a missing schema, a drop blocked by dependents, and a concurrent-update error inside the access-control procedure, which points to the Lambda’s concurrency problem. 12 of the 13 failed view objects exist in Prod anyway, so the failed status doesn’t mean they are missing
• Some reference SQL is run by hand and lives in a folder the pipeline doesn’t deploy
• One or two people understand the whole system (I certainly don't. The CTO probably does; I doubt any of the data engineers does)

5. Scalability.

With all these issues, scaling the BI layer sounds intimidating. I don't want to be in the situation where someone needs to manually drop and recreate dozens of BI objects because of one upstream change and then I have to validate all of them. We constantly learn new things about the business - partly because of prior poor documentation, and it feels like just updating the BI objects could be one person's full-time job if we scale to hundreds of objects in the future.

Where I'd like to go

• Access control (roles, schema-level grants, default privileges) stays in the existing CDK pipeline, because that is the security boundary
• Transformation logic (the MVs and views) moves to dbt, which handles dependency ordering and MV rebuilds, adds testing, and lets the BI team manage per-model grants
• We’d split the single BI schema into staging, intermediate, and marts layers as part of that move
• Orchestration for the Redshift side is undecided (MWAA versus EventBridge plus Fargate)

What I’d like your opinion on

1. Is CDK plus an S3-triggered Lambda a reasonable way to deploy SQL objects, or is it a migration framework assembled from the wrong parts?
2. Which of the problems above come from the CDK approach itself, and which would exist under any file-based runner? My view is that the manual MV step comes from MVs not being replaceable, something that I believe that dbt can resolve.
3. Would you keep the custom Lambda’s tracking design (name-keyed, re-applies on checksum change, failed rows block a later success), or replace it?
4. Does the proposed split (CDK for access control, dbt for transformations) look sound, or does it create new coupling?
5. How would you handle MV rebuilds, dependency ordering, and refresh scheduling?
6. What am I not seeing that the CTO is?

I have been working for 5 years on the analytical side of things and I'm yet to find a company where our work actually makes a difference. I know that there are some very specific businesses like financial trading where profit is actually made using data, but for most corporate BI projects this is not the case.

Companies have huge budgets for their data teams to support business decisions, yet most of our output is never used to make a difference. Business seems to care more about a KPI 'looking good' than being actually correct, if they care at all.

I have worked in very different environments but this was all the same everywhere.

I am now working in digital advertising which is supposed to be heavily data driven and we produce more than 30 different KPIs yet advertisers don't seem to care and just spend their yearly advertising budgets anyway.

When I was at uni, 'big data' was hyped as the holy grail, and I was naive enough to think that my work would actually end up in board meetings and that we would be one of the most important departments in the company.

Can someone more experienced tell me why this is?

We're a construction company using Viewpoint Spectrum as our primary data source. Currently, most reporting is done through Power BI, SQL, and Claude, with approximately 20-40 report consumers and a small number of report developers (2-5).

One challenge is that Spectrum database access is restricted through IP whitelisting, making direct access difficult for field users. Additionally, with Claude being use a lot I believe it's important to have real data modeling and etc. We're evaluating different architectures for reporting, governance, and future AI use cases.

I looked at data warehouses and other solutions be they seem to be very expensive and hard to justify. I have also seen people recommend having your own server where something like MongoDB or Apache Superset is hosted. There are too many solutions so I would like some recommendations.