Services / Intelligence
Data platforms where the numbers agree
We build warehouses, lakehouses and streaming pipelines with the things that make data trustworthy: medallion layering, dbt transformations in version control, orchestration with real dependency management, data contracts with freshness SLAs, quality tests that block promotion, and lineage from dashboard back to source column. Most engagements start because two reports disagree and nobody can prove which is right.
- One metric definition, in code, read by every dashboard
- Bronze layer immutable so any transformation error is reprocessable
- Freshness and quality SLAs published, and breaches reported
- Query-level cost attribution from the first week
At a glance
- Warehouses
- Snowflake, BigQuery, Redshift, Databricks
- Transformation
- dbt Core and dbt Cloud
- Orchestration
- Airflow, Dagster, Step Functions
- Streaming and CDC
- Kafka, MSK, Kinesis, Debezium
- First useful dataset
- 6 – 8 weeks from kick-off
- Typical warehouse saving
- 30 – 50 percent on ungoverned estates
- Enquiries
- nitesh@redcubical.com
Architecture
Medallion and lakehouse architecture
Three layers with distinct contracts. The discipline is that no layer skips the one before it, and no dashboard ever queries Bronze.
What each layer is for
Bronze holds raw source data, immutable and append-only, with ingestion metadata. Silver holds cleaned, conformed, deduplicated records with types enforced and business keys resolved. Gold holds modelled dimensional and aggregate tables that analysts consume. The point is reprocessability: when a transformation is found to be wrong, you rebuild Silver and Gold from Bronze rather than asking a source system for history it may no longer hold.
| Layer | Contains | Rules we enforce | Who reads it |
|---|---|---|---|
| Bronze (raw) | Source data exactly as received, plus ingestion timestamp, source system, batch identifier and file or offset reference | Append-only. No transformation, no deduplication, no type coercion. Retained for the full reprocessing window, typically 13 months minimum | Data engineers only. Never a dashboard |
| Silver (conformed) | Cleaned, typed, deduplicated records. Business keys resolved, reference data joined, late-arriving records handled, soft deletes applied | Idempotent rebuild from Bronze. Quality tests must pass before promotion. Schema changes go through the contract process | Data engineers, advanced analysts, machine learning feature pipelines |
| Gold (modelled) | Dimensional models with conformed dimensions and fact tables, plus denormalised wide tables and pre-aggregates for BI | Every metric defined exactly once. Built only from Silver. Documented and owned by a named person | Analysts, BI tools, executives, downstream applications and APIs |
Lakehouse rather than a classic warehouse where you need open table formats — Iceberg or Delta — for schema evolution, time travel and engine independence. That matters when Spark, SQL and machine learning workloads all read the same tables, and when you want the option to change query engine later without re-landing the data.
Decision
Batch or streaming?
Batch versus streaming
Choose streaming only when a decision must be made within seconds of an event, or when data volume makes batch windows unworkable. Otherwise choose batch or micro-batch. Streaming costs two to four times more to build and considerably more to operate, because late data, exactly-once semantics and stateful recovery are genuinely difficult. A fifteen-minute micro-batch meets most stated real-time requirements.
| Factor | Batch (hourly to daily) | Micro-batch (1 – 15 minutes) | Streaming (sub-second to seconds) |
|---|---|---|---|
| Right for | Financial reporting, regulatory returns, historical analysis, most executive dashboards | Operational dashboards, near-real-time inventory, hourly reconciliation | Fraud scoring, real-time alerting, live personalisation, IoT telemetry |
| Build effort | Baseline | 1.2 to 1.5 times baseline | 2 to 4 times baseline |
| Operational burden | Low. A failed run is re-run | Moderate. Backlogs need monitoring | High. Consumer lag, state size, rebalancing and checkpoint recovery all need active ownership |
| Late-arriving data | Trivial. The next run picks it up | Manageable with short watermarks | Hard. Watermarks, allowed lateness and side outputs must all be designed deliberately |
| Correction after a bug | Simple. Reprocess the window from Bronze | Simple to moderate | Difficult. Usually needs a parallel batch reprocessing path, which means maintaining two implementations of the same logic |
| Typical technology | dbt on the warehouse, orchestrated by Airflow or Dagster | dbt incremental models on a short schedule, Snowpipe, Kinesis Firehose | Kafka or Kinesis with Flink, Spark Structured Streaming or Kafka Streams |
| Cost profile | Predictable, scheduled compute | Slightly higher, more frequent warehouse wake-ups | Always-on compute plus state storage. Highest fixed cost |
The question we ask when a client requests real-time: what decision changes, and who makes it, in the window between now and the next batch? If the answer is that a person looks at a dashboard during office hours, a fifteen-minute micro-batch is the correct architecture and it costs a fraction of the alternative.
Platforms
Which warehouse wins, and when
| Platform | Strongest at | Cost model | Watch out for | Choose it when |
|---|---|---|---|---|
| Snowflake | Ease of operation, workload isolation through separate virtual warehouses, secure data sharing across organisations | Per-second credit consumption by warehouse size, plus storage | Idle warehouses with long auto-suspend windows, and per-team warehouse sprawl with no attribution | Mixed-skill team, multiple clouds, or data sharing with partners and customers matters |
| Google BigQuery | Serverless scanning with nothing to size, strong geospatial and ML integration, generous streaming ingestion | Per-terabyte scanned on demand, or slot-based capacity commitments | A single unpartitioned full-table scan in a scheduled query can cost more than the rest of the month combined | You are on Google Cloud and want zero cluster management |
| Amazon Redshift | Tight AWS integration, Spectrum querying over S3, predictable provisioned cost, RA3 managed storage | Provisioned node hours, or serverless RPU-seconds | Distribution and sort key choices are consequential and awkward to change later; concurrency needs deliberate management | You are deep in AWS, want commitment discounts, and your analytics estate is SQL-centred |
| Databricks | Spark workloads, machine learning lifecycle, streaming, and Delta Lake with open table format and time travel | DBU consumption by compute type, plus underlying cloud compute | Requires more engineering maturity than the others; a SQL-only analyst team will find it heavier than needed | Substantial Spark, ML or streaming work sits alongside SQL reporting |
| PostgreSQL with columnar extensions | Small estates, low cost, no new platform to learn or govern | Instance hours | Falls over somewhere between 100 GB and 1 TB of analytical data depending on query shape | Under roughly 200 GB with modest concurrency. Genuinely the right answer more often than vendors suggest |
For standard SQL analytics, all four major platforms are good enough that platform choice is not the thing that will determine your success. Modelling discipline, metric governance and orchestration reliability are. We have seen excellent platforms on every one of them and unusable ones on every one of them.
Toolchain
Transformation, orchestration and change data capture
How the toolchain divides
dbt owns transformation: SQL models in version control, dependencies inferred from references, tests alongside models, and documentation with lineage generated from the code rather than maintained separately. Airflow or Dagster owns orchestration: ingestion, dbt invocation, reverse ETL and downstream triggers. Keeping the two separate is what keeps transformation logic reviewable in pull requests.
How we use dbt
- Layered project structure mirroring medallion: staging models one-to-one with sources, intermediate models for reusable logic, marts for the Gold layer.
- Incremental models for anything large, with a defined unique key and a late-arrival lookback window. Full refreshes are scheduled deliberately, never as the default.
- Tests as first-class artefacts. Not null, unique, accepted values, relationships, plus singular tests for business invariants such as "no order line without a parent order".
- Exposures declaring which dashboards depend on which models, so impact is visible before a change is merged.
- A semantic layer or metrics definitions so revenue is defined once and every consumer inherits it.
- Slim continuous integration building and testing only modified models and their downstream dependants against a production clone.
Airflow or Dagster
- Airflow where the team already runs it, where the operator ecosystem matters, or where a managed service such as MWAA or Cloud Composer is the mandated path. Mature, well understood, easy to hire for.
- Dagster where the pipeline is best expressed as assets rather than tasks. Asset lineage, freshness policies and typed inputs and outputs make data quality and staleness first-class concerns rather than things you monitor separately.
- Step Functions for simple AWS-native sequences where a full orchestrator is unnecessary overhead.
Our default for a new platform is Dagster, because asset-based orchestration matches how data teams actually reason about their work. Our default for an existing Airflow shop is Airflow, because migrating a working orchestrator delivers no business value.
Change data capture with Debezium
Ingestion is the other half of the toolchain, and the choice between log-based capture and a scheduled incremental pull sets what the rest of the platform can promise.
Why log-based CDC rather than incremental queries
Debezium reads the database transaction log and emits every insert, update and delete as an event. Unlike a timestamp-based incremental pull it captures deletes and intermediate states, and it puts no query load on the source database. We use it when the source is a production transactional database we must not slow down, and when deletes and full change history matter.
Where CDC earns its complexity
- Deletes are captured. A timestamp-based pull silently keeps deleted rows forever, which is how a warehouse ends up with more active customers than the source system has.
- No query load on the source. Reading the write-ahead log or binlog costs the production database almost nothing compared with repeated range scans.
- Full change history. Every intermediate state is available, which is what makes accurate slowly changing dimensions possible.
- Near-real-time by default without polling, and no reliance on the source having a trustworthy updated-at column. Many do not.
- Consistent ordering per key so a row's state can be rebuilt deterministically.
What it costs you
- Source database configuration. Logical replication or binlog access, a replication slot, and privileges a DBA must grant. This is often the longest-lead item in the whole project.
- Replication slot risk. A stalled consumer means the source retains write-ahead log segments indefinitely and can fill its disk. This needs monitoring and alerting from day one, and it is the failure mode we see most often.
- Kafka or Kinesis to operate, or a managed equivalent, with the schema registry and retention decisions that come with it.
- Snapshot plus stream complexity. The initial snapshot and the ongoing stream must be stitched without gaps or duplicates.
- Schema evolution handling. A source DDL change becomes a downstream event your consumers must tolerate.
For a nightly report from a source with a reliable updated-at column and no hard deletes, a scheduled incremental extract is the correct choice and CDC is over-engineering.
Trust
Data contracts, freshness SLAs and quality testing
What a data contract contains
A data contract is a versioned agreement between producer and consumers covering schema, semantics, freshness, allowed null and duplicate rates, and a deprecation notice period. It is enforced in the producer's pipeline, so a breaking change fails their build rather than quietly breaking a dashboard. That inversion — quality owned upstream, not cleaned downstream — is the whole point.
| Test category | Example | Runs where | On failure |
|---|---|---|---|
| Schema conformance | Expected columns present, types unchanged, no unexpected additions in a strict contract | Producer pipeline and at ingestion | Block the producer build. Alert both producer and consumer owners |
| Uniqueness and referential integrity | Primary key unique, every fact row resolves to an existing dimension key | dbt test after Silver build | Block promotion to Gold. Gold keeps serving the previous good build |
| Null and completeness thresholds | Customer email null rate under 2 percent, order total never null | dbt test after Silver build | Warn below threshold, block above it. Thresholds agreed with the business, not guessed |
| Freshness | Orders table contains data no more than 90 minutes old during business hours | Continuous freshness monitor, Dagster freshness policy or dbt source freshness | Alert the owner. Stale marker shown on affected dashboards so nobody reads yesterday as today |
| Volume anomaly | Daily row count within 3 standard deviations of the trailing 30-day mean | Post-load check | Alert and hold promotion pending human review. Catches partial loads, which quiet failures produce |
| Business invariants | Ledger debits equal credits. No negative inventory. Refunds never exceed the original payment | dbt singular tests on Gold | Block publication. These are the tests that catch real logic errors |
| Reconciliation to source | Warehouse revenue for last month equals the finance system to within a defined tolerance | Scheduled reconciliation job | Alert with the variance and its breakdown. Reviewed monthly with the business owner |
| Distribution drift | Category mix or average order value shifts beyond an expected band | Scheduled profiling | Informational alert. Often a genuine business change, sometimes an upstream defect |
Freshness SLAs are published, not implied: for each Gold dataset, an expected freshness during business hours, an owner, and an escalation path. A breach is reported rather than absorbed. Absorbed breaches are how a business quietly learns to distrust its own dashboards.
Modelling & governance
Dimensional models or wide tables?
How we model the Gold layer
Both, at different layers. Dimensional modelling with conformed dimensions forms the core of Gold, because it handles slowly changing attributes correctly and prevents the same dimension being defined five different ways. Denormalised wide tables are then built on top of those dimensions for specific analytical uses and BI performance. Wide tables alone give you fast queries and, within a year, six definitions of "active customer".
Dimensional core
- Conformed dimensions shared across fact tables, so customer means the same thing in the sales mart and the support mart.
- Type 2 history where attribute change matters. Reporting a historic sale against a customer's current region is a common and expensive error.
- Explicit fact grain, documented. Ambiguous grain is the root cause of most double-counting.
- Surrogate keys so a source system key change does not ripple through the warehouse.
- Accumulating snapshot facts for process pipelines such as order to cash, where milestone dates are the analysis.
Wide tables on top
- One wide table per analytical domain, built from the dimensional core so definitions are inherited rather than reinvented.
- Pre-joined and pre-aggregated at the grain the BI tool actually queries, which removes runtime joins and their cost.
- Partitioned and clustered on the columns users genuinely filter on, verified from query history rather than assumed.
- Rebuilt incrementally from the dimensional layer, never hand-maintained.
- Documented with a named owner and a listed set of dashboards that depend on it.
Modern columnar engines make the storage duplication cheap. The reason to keep the dimensional core is governance, not storage efficiency.
Lineage, catalogue and cost control
Modelling decides whether the numbers are right. Governance and cost control decide whether anyone can find them, trust them and afford to query them.
Governance and lineage
- Column-level lineage from dashboard back to source column, generated from dbt metadata rather than drawn by hand. Hand-drawn lineage diagrams are out of date the week after they are made.
- A catalogue people actually use. Table and column descriptions, owner, freshness, and the dashboards downstream. Descriptions written in dbt so they live with the code.
- Data classification per column: public, internal, confidential, personal data. Drives masking policies and access grants.
- Role-based access at the schema and column level, with dynamic masking on personal data so analysts can join on a customer without reading their contact details.
- Retention and erasure. Retention policy per dataset, and a documented mechanism to honour erasure requests including in Bronze, which is the layer people forget.
- Change management. Model changes reviewed in pull requests, with exposures showing which dashboards are affected before merge.
Warehouse cost control
- Query-level attribution first. Cost by warehouse, user, dbt model and dashboard. Without attribution you are guessing at which optimisation matters.
- Partitioning and clustering on real filter patterns, taken from query history. This is usually the single largest saving on BigQuery and Snowflake alike.
- Incremental models replacing full refreshes. A nightly full rebuild of a large fact table is often the biggest line item in the bill.
- Auto-suspend at 60 seconds and right-sized warehouses. Idle compute is pure waste and takes minutes to fix.
- Materialised aggregates for dashboard tiles that would otherwise scan the same data every refresh, for every viewer.
- Dashboard audit. Retire what nobody opens. We routinely find 30 to 50 percent of dashboards have had no viewer in 90 days while still refreshing hourly.
- Resource monitors and query timeouts so one runaway analytical query cannot produce a five-figure surprise.
- Cost per report and per active user reported monthly, which is the number that tells you whether the platform is getting more or less efficient.
Diagnosis
Common symptoms, root causes and fixes
The complaints that bring clients to us, and what is usually behind them.
| Symptom | Usual root cause | The fix |
|---|---|---|
| Two reports show different numbers for the same metric | The metric is defined twice, in two tools, with different date grain or filters. Both are locally correct | A single semantic layer with one versioned definition per metric. Every dashboard reads from it; none define their own |
| The warehouse has more active customers than the source system | Timestamp-based incremental loads never captured deletes, so deleted rows persist forever | Log-based CDC, or a periodic full reconciliation with soft-delete handling in Silver |
| Yesterday's number changed overnight | Late-arriving records with no defined lookback window, or a non-idempotent incremental model | Explicit lookback window, idempotent merge logic, and a published restatement policy so changes are expected rather than alarming |
| A dashboard shows stale data and nobody noticed | No freshness monitoring. A pipeline failed silently or a source stopped sending | Freshness SLA per dataset, alerting on breach, and a visible staleness indicator on the dashboard itself |
| The monthly warehouse bill doubled without more users | Usually a small number of scheduled queries doing full scans, or a warehouse left running with a long auto-suspend | Query-level cost attribution, partitioning on real filters, incremental models, auto-suspend at 60 seconds |
| Revenue in the warehouse does not match the finance system | Different treatment of refunds, cancellations, currency conversion date or fiscal period boundary | Scheduled reconciliation job with variance breakdown, and an agreed written definition signed off by finance |
| Analysts extract to spreadsheets and work there | The warehouse does not have what they need at the grain they need it, so they route around it | Interview the analysts, add the missing Gold models, then measure whether extraction volume falls |
| A pipeline change broke three dashboards nobody knew depended on it | No lineage and no exposure declarations, so impact is invisible before merge | dbt exposures plus column-level lineage, surfaced in the pull request as a required check |
| A dimension change rewrote historical figures | Type 1 dimension overwriting attributes that should have been historised | Type 2 slowly changing dimensions where attribute change is analytically material, decided per attribute rather than per table |
| Orchestration DAG takes six hours and nobody knows why | Sequential tasks with no parallelism, full refreshes on large models, and transformation logic embedded in operators | Move transformation into dbt so dependencies are inferred and parallelised, make large models incremental, profile the critical path |
| Duplicate rows appear intermittently | At-least-once delivery with no deduplication key, or a retried batch reloading the same file | Idempotent merges on a defined business key, plus processed-file tracking at ingestion |
| Nobody trusts the platform, so decisions are made on instinct | Accumulated small failures never visibly resolved. Trust is lost gradually and regained deliberately | Publish quality and freshness SLAs, report breaches openly, fix the top three complaints first and show the result |
Answers
Data engineering and analytics questions
Why do two of our reports show different numbers?
Almost always because the same metric is defined twice. Two teams wrote their own logic — different date grain, different filter on cancelled records, different handling of refunds — and both are locally correct. The fix is a single semantic layer where each metric is defined once, in version-controlled code, with every dashboard reading from it. Reconciling the two reports without fixing the definition means they diverge again within a quarter.
What is medallion architecture?
A three-layer pattern. Bronze holds raw ingested data, immutable and append-only, exactly as the source sent it. Silver holds cleaned, conformed, deduplicated data with types enforced and business keys resolved. Gold holds the modelled, aggregated tables that analysts and dashboards use. The value is that reprocessing is always possible: if a transformation was wrong, you rebuild Silver and Gold from Bronze rather than asking the source system for history it may no longer have.
Should we choose Snowflake, BigQuery, Redshift or Databricks?
Snowflake for a mixed-skill team wanting the least operational overhead and strong data sharing. BigQuery when you are on Google Cloud and want serverless scanning with no cluster to manage. Redshift when you are deep in AWS and want tight integration with your existing estate and commitment discounts. Databricks when substantial workloads are Spark, machine learning or streaming rather than SQL reporting. There is no wrong answer among these four for standard analytics; the deciding factors are your team's existing skills and your cloud position.
Do we need streaming, or is batch enough?
Batch is enough far more often than people expect. Streaming is justified when a decision must be made within seconds — fraud scoring, operational alerting, live inventory — or when the data volume makes batch windows unworkable. It costs two to four times more to build and considerably more to operate, because late-arriving data, exactly-once semantics and stateful window recovery are genuinely hard. A fifteen-minute micro-batch satisfies most "real-time" requirements we are handed.
What is a data contract?
A versioned agreement between a data producer and its consumers covering schema, semantics, freshness, allowed null rates and a deprecation notice period. It is enforced in the producer's pipeline, so a breaking change fails their build rather than silently breaking a downstream dashboard on a Monday morning. Contracts turn data quality from a downstream cleanup activity into an upstream engineering responsibility, which is the only version that holds.
How do you control warehouse costs?
Query-level attribution first, so you know which workloads and which teams generate the spend. Then the standard levers: partitioning and clustering on real filter patterns, incremental models rather than full refreshes, right-sized warehouses with aggressive auto-suspend, result caching, materialised aggregates for repeated dashboard queries, and retiring dashboards nobody opens. On estates with no prior governance we typically find 30 to 50 percent, and the largest single item is usually a small number of scheduled queries doing full scans hourly for a dashboard with three users.
How long does it take to build a warehouse from scratch?
A first useful gold-layer dataset in six to eight weeks: ingestion for two or three key sources, a Silver layer, a modelled Gold layer, orchestration and tests. A complete platform covering ten or more sources with governance, lineage and a semantic layer runs four to eight months. The pace is set by source system access and by how long it takes your business to agree metric definitions, not by engineering throughput.
Should we use dimensional models or wide tables?
Both, at different layers. Dimensional modelling with conformed dimensions in the core Gold layer, because it handles slowly changing attributes correctly and prevents the same dimension being defined differently in five places. Then denormalised wide tables built on top of those dimensions for specific analytical use cases and BI performance. The mistake is starting with wide tables only: you get fast queries and, within a year, six definitions of "active customer".
Start with a data platform assessment
Three weeks, fixed fee. We map your sources, trace lineage on the metrics people argue about, audit quality and freshness, analyse warehouse spend by query, and produce a costed remediation plan. The lineage map and cost analysis are yours regardless.