Optional: Enriching with an LLM
This page covers two sync directions between your pipeline and the application database:
analytics schema. Ownership and contracts are in Cross-Track Collaboration. This page is the implementation checklist.Read the outbound section when you wire the publish task in your DAG. Read the inbound sections only after outbound publish works in prod.
Outbound sync is required for the demo and a passing grade. Users read what it writes, so this task is more demanding than inbound copy.
It runs in your DAG after dbt build and tests on the mart the backend reads.
<aside> 📘 Recap from Week 10: tests on the mart the backend reads are the same generic tests from dbt Tests. A published table with no tests on its keys is the most common demo failure.
</aside>
Five properties make the difference between a sync that survives a demo and one that does not:
It publishes atomically. Load into a staging table, then staging table swap (load to staging, then swap in one transaction) inside a single transaction. Someone refreshing the page during your sync must see either yesterday's data or today's, never an empty table.
It is safe to re-run. Running it twice must leave the same number of rows. Airflow retries tasks, so this is not optional.
It runs on a schedule. After dbt build, with retries, unattended. If the only green run happened because someone pressed a button ten minutes before the demo, it does not count.
It fails loudly. A failed sync must fail the task and leave the previous good data in place. The worst outcome is a green pipeline serving stale numbers, because nobody investigates a green pipeline.
It is sized honestly. Replacing the whole table each run is simple and correct while your marts are small. Say in your README how many rows you handle and why that choice fits. When it stops fitting, you will know, because the run time will tell you.
<aside> 💡 The step from your platform into the application database has a name in industry: reverse ETL. Products exist that do only this. Saying that in an interview, and explaining why you built it yourself, is worth more than the code.
</aside>
Read this part after your team has a real external source ingested and a full end-to-end pipeline green in production: external fetch, dbt marts, outbound publish to analytics, and a scheduled Airflow run without anyone pressing a button.
Until that bar is met, do not start inbound sync. Teams that copy app tables before outbound publish works spend weeks debugging two directions at once.
By the end of the inbound sections, you should know when inbound sync is worth adding, what to agree with the backend in week 1 even if you build it later, and how to implement it without PII leaks.
Add inbound sync only when all of these are true:
dbt build and outbound publish run green in prod on that sourceanalytics in the live appIf your demo story works from the external feed only, skip inbound sync and say so in the README.
You do not have to build inbound in week 1. You should still agree the usage contract with the backend: which app tables you may read later, which columns stay off limits, and whether app_events will exist.
Ask for an append-only app_events table early if you might model behaviour later. It only records events after the table exists, so a table created in week four gives you one week of history.
flowchart LR
EXT["External<br/>data source"]
subgraph data["Data Track"]
RAWAPP[("raw_app<br/>copy of app tables")]
PIPE["Ingestion<br/>and dbt marts"]
DEDB[("Data platform<br/>Databricks catalog")]
end
subgraph backend["Backend Track"]
API["REST API<br/>Spring Boot"]
APPDB[("Application database<br/>app schema")]
ANA[("analytics schema<br/>you write, they read")]
end
subgraph frontend["Frontend Track"]
UI["Web application"]
end
EXT --> PIPE
RAWAPP --> PIPE
PIPE --> DEDB
DEDB -->|"outbound sync"| ANA
ANA --> API
API <--> APPDB
UI <--> API
APPDB -->|"inbound sync"| RAWAPP
classDef de fill:#e8f4ea,stroke:#4a8055
classDef be fill:#e8eef7,stroke:#4a6080
classDef fe fill:#f7f0e8,stroke:#806a4a
class PIPE,DEDB,RAWAPP de
class API,APPDB,ANA be
class UI fe
The inbound sync (app tables copied into your raw_app layer) runs from the application database back into your Databricks catalog. It is the mirror of outbound publish: outbound sends marts to analytics; inbound copies selected app tables into raw_app for dbt.
The backend grants a read-only role for it:
-- Backend runs this once. This role can read the app, and change nothing.
CREATE ROLE analytics_reader LOGIN PASSWORD 'a-different-one';
GRANT USAGE ON SCHEMA app TO analytics_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO analytics_reader;
Two roles, two jobs. analytics_user writes marts and reads nothing else. analytics_reader reads application tables and writes nothing at all.
Land the application's rows in a schema called raw_app on your platform, and treat it as a dbt source like the external feed. Do not build analysis tables inside the application database.
| Where | Schema | Direction | Written by |
|---|---|---|---|
| Your data platform | raw_app |
In: copies of application tables | Your inbound sync |
| Application database | analytics |
Out: your published marts | Your outbound sync |
Application tables tell you what a user's saved items are now. They do not tell you when they saved them unless the backend records events. Copying only state blocks questions like "how did usage change over the project."
Deletes disappear quietly. If a user removes a saved item, incremental loads may miss the delete unless the backend soft-deletes or you reload small tables in full. Write down which approach you use.
Copy no personal data. Select only the columns you need. Hash user ids rather than carrying email or name into the warehouse.
Inbound sync is simpler than outbound: nothing serves those rows directly to end users.
analytics_reader role onlyWire it into the DAG after dbt build only when you have models that read raw_app. Many teams add it as a fourth task once outbound has been stable for a week.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 https://hackyourfuture.net/
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.