16M Rows, Precisely Orchestrated

Freshness is a chain, not a timestamp.

A table’s “last updated” time only says when that table ran. It does not prove every input was ready. Picture a report that joins posts, daily performance, creators, campaign membership, and comments. The report may refresh at 9:30. If creator data is still on yesterday’s load, the finished table holds two moments in time — and nobody can see that from a single green badge.

That is the data-arrival problem. Data does not move through a warehouse as one package. Each source has its own delivery time. Each model has its own run time. A downstream table is truly ready only when the parents it needs have finished a valid refresh.

A schedule and a dependency answer different questions.

A schedule answers, “When should we try?” A dependency answers, “What must succeed before this can start?” A reliable pipeline usually needs both. Here, a schedule wakes the root task four times a day. No child uses a guessed minute offset. Children start only after Snowflake records their parent as successful.

That does not magically know whether an outside vendor delivered correct data. You still need a freshness check or event trigger when arrival itself must be proven. What the graph guarantees is internal order: once the root begins, no required child can race ahead of the warehouse table it depends on.

A DAG makes order part of the system.

The structure is a directed acyclic graph, or DAG — a map of work with arrows that never loop back. Directed means every arrow has a start and an end: “run B after A succeeds.” Acyclic means the arrows cannot form a circle. In this pipeline, each node is a refresh task and each arrow means “start only after this parent succeeds.”

The no-loop rule matters. If the creator table waits for the reporting table while the reporting table waits for creators, neither can start. A DAG makes that impossible by design. It also gives the team a visible answer to a basic question: if this table changes, what else must run before the report is ready?

The graph is also a data contract.

An arrow should mean more than “this usually runs first.” It should represent a real data dependency. The creator table waits for the channel table because creators are built from the accounts they own. Performance waits for observation history because it selects and shapes those observations. The final reporting mart — the prepared table dashboards read — waits for performance, creators, and campaign–post relationships because it needs all three to build a complete row.

Dynamic tables define the result; tasks own the run.

A Snowflake dynamic table stores the result of a query and refreshes that stored result when asked. You declare what the table should contain instead of writing a long series of insert and update statements. That is useful for reporting: humans read prepared rows rather than rebuilding full history in every dashboard query.

Snowflake can also let tables refresh themselves to hit a freshness goal (target lag). Intermediate tables can even let a downstream consumer drive their timing. That model is powerful when you want Snowflake to manage the whole pipeline. This project needed something different: explicit control over each step so order and failure could never be ambiguous.

So the materialized objects use a disabled self-scheduler. Snowflake tasks — small jobs that run SQL on a schedule or after a parent — then call a refresh on each dynamic table in the declared order. Two jobs stay separate: the dynamic table owns the SQL and stored result; the task graph owns timing, order, and failure flow.

Choose one owner for refresh timing.

Mixing “table refreshes itself” with an external task graph creates two control planes. A table may refresh while the graph is still working, or a manual refresh may change the timing downstream consumers expect. The design avoids that ambiguity. The task graph is the only scheduler for this layer.

One root cascades the reporting pipeline.

The main graph begins with a diagnostics table that settles the core set of posts the rest of the stack will use. That job is the only scheduled root in the core lane. It runs at 03:00, 09:00, 15:00, and 21:00 UTC — after expected vendor drops — but every child still waits on completion, not on more clocks.

After diagnostics succeeds, the post table refreshes. That post table is the shared gate for four branches: observation history, channels, comments, and the campaign–post bridge (which posts belong to which campaigns). Observation performance waits for observation history. Creator waits for channel. The final campaign performance mart waits for observation performance, creator, and the bridge.

In SQL, Snowflake expresses these arrows with AFTER. A child may name more than one predecessor. Snowflake’s task-graph model is the same root-and-child idea: by default a new graph run does not overlap an unfinished run before it, so two half-finished refreshes do not race each other.

Parallel work is safe only after the shared gate.

A DAG does not mean everything runs one table at a time. That would be correct but wasteful. Once the post table succeeds, branches that do not depend on each other can run together. Observation history, channel, comment, and bridge all read the ready post layer, so Snowflake can use the warehouse for parallel work.

The join point matters as much as the split. The mart has three parents, so it waits until every required branch has finished. That wait is a fan-in. A fan-out saves time by running independent work together. A fan-in restores consistency before publication — the report does not go out half-built.

Optional data should not block the core product.

An extra Instagram performance source has its own small two-task graph. It does not block the official mart. Its enriched table refreshes only after that optional source succeeds. Useful enrichment can fail without taking the core reporting product down with it.

The same idea applies to campaign hierarchy and media valuation, which use a separate hourly root. Not every table that is eventually joined belongs in one giant graph. Put a dependency on the critical path only when the final product truly cannot be correct without it.

A failed parent should stop stale data from moving forward.

Failure is part of orchestration. If observation history fails, performance does not run and the mart does not run. If channel fails, creator and the mart stop. The previous successful dynamic-table results remain available, so a failed refresh does not erase the last good report.

That is safer than letting downstream tasks run anyway. A dashboard showing an older, known snapshot is usually easier to explain than a new table built from a broken mixture of old and new parents. The pipeline chooses consistency over the appearance of motion.

Snowflake exposes task state, start time, completion time, errors, and graph history. Dynamic tables also expose refresh history and lag. Operators can trace a late or failed upstream table instead of treating the final report as the first place to investigate.

Operators start the root, not every table.

A manual recovery should follow the same graph as a scheduled run. The operator executes the root task once. Enabled children follow their declared dependencies. Running every child by hand at the same time would bypass the order the system was built to protect.

Deployment follows the graph in reverse. Scheduled roots are suspended before changes. Old tasks are dropped from leaves back to the root so dependencies can be removed safely. New tasks are created from the root outward, then the whole dependent graph is enabled. That is not ceremony. It prevents a half-replaced graph from running during a release.

Monitor freshness at the product boundary.

A green task means the SQL completed. It does not prove the source values were correct, or that an outside feed arrived on time. Good operations therefore check both execution and data: task failures, refresh duration, row counts, maximum source timestamps, key coverage, and the age of the final mart. The report boundary is where those signals become one service-level check.