Firebase Realtime Database to Amazon Redshift: How to Move Your Data
Move Firebase Realtime Database into Amazon Redshift with Airbyte. Choosing the node path, why a Google source needs an S3 bucket, and modelling nested data.
Moving Firebase Realtime Database into Amazon Redshift puts application data where it can be joined and aggregated. Realtime Database is built for syncing state to clients rather than for analysis, and anything shaped like how many, grouped by, or joined to needs the data on separate compute.
This guide covers the managed path with Airbyte. Two things shape the build: Realtime Database is one deep JSON tree rather than a set of tables, and Redshift cannot be loaded without a staging bucket, which puts an AWS prerequisite in front of a Google source.
Firebase Realtime Database to Amazon Redshift at a glance:
Why move data from Firebase to Amazon Redshift?
Two situations account for most of these pipelines.
The first is analysis your application database cannot support. Realtime Database answers queries by path and offers little beyond that, so aggregating across users or joining to anything else needs a warehouse.
The second is where your analytical estate already sits. A mobile app on Firebase and a business running on AWS is a common combination, and replicating into Redshift puts app data beside the commercial data already there rather than adding a second warehouse.
What do you need before you start?
One decision shapes the data and one is an AWS prerequisite you cannot skip:
A decision about which node to sync. Realtime Database is one deeply nested JSON tree, and the node path you give the connector determines what each record contains. Choosing the wrong level produces either enormous records or fragments that mean nothing on their own.
An S3 staging bucket and IAM permissions. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command, which is Redshift's own recommended approach. You need put, get, delete and list on the bucket, in the same region as the cluster.
A Google service account with read access. Along with your database name. Create one for the pipeline rather than reusing something your application uses, so its access is separately revocable.
Redshift entities created in advance. A database, a schema and a user able to write data and manage staging operations. Airbyte writes into two schemas, the one you nominate and a raw schema it manages, so tell whoever reviews your Redshift objects that the second is expected.
Note also that an SSH tunnel covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.
How do you build a Firebase to Redshift pipeline in Airbyte?
Step 1: Choose the node path
Pick the node whose children are the entities your analysts will count, so each child becomes a row. Ask your application developers what lives under it too, because a schemaless tree carries no documentation and they are the only source of truth about what a field means.
Step 2: Configure the Firebase source
Click Sources in the left navigation, then New Source, and select Firebase Realtime Database, following adding a source. Supply the database name, your service account credentials and the node path you decided on.
Step 3: Configure the Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default, and can be retained if you need them for debugging.
Step 4: Create the connection and plan for removals
Click Connections, then New connection, select your stream and a sync mode. There is no change data capture here, so a record deleted in Firebase stays in Redshift until something removes it. Decide what that should mean before anyone counts rows.
Think about your sort and distribution keys once the tables exist, because on Redshift those choices affect query performance considerably more than anything in the pipeline does.
Why does a Google source need an AWS bucket?
Because the requirement comes from the destination rather than the source. Redshift is designed to be loaded in bulk from S3, so the connector writes files with a manifest and then issues a COPY command pointing at them. That is Redshift's own recommended path and there is no direct insert alternative.
So a pipeline from a Google product acquires an AWS prerequisite, which surprises teams who assumed the Firebase side was the only setup work. Budget for a bucket in the same region as your cluster, IAM credentials with four permissions on it, and whatever approval your organisation requires for new storage.
It also affects your network design. An SSH tunnel covers the SQL connection to the cluster and not the staging traffic, which reaches S3 over HTTPS regardless. If your security model assumes all traffic passes through a bastion, that needs revisiting before somebody discovers it in a review.
How should you model a JSON tree in Redshift?
Deliberately, because Redshift is the least forgiving destination in this series for semi-structured data. It offers a SUPER type for holding it, and the ergonomics are thinner than a lakehouse or BigQuery provides, so leaving everything nested and hoping analysts cope is not a plan.
Build a modelled layer extracting the fields people query into typed columns, and treat the landed table as staging. That is the same advice as elsewhere, and here it is closer to mandatory, because the alternative is every analyst writing path expressions against a structure nobody documented.
Then set sort and distribution keys on that modelled table. Firebase data usually has a natural time dimension and a natural entity key, and choosing those deliberately is what turns a working pipeline into one people enjoy querying.
Frequently asked questions
Do I need an S3 bucket to load into Redshift?
Yes. The destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. There is no direct insert path, even though the source is a Google product.
How do I decide what a row should be?
By the node path you give the connector. Pick the node whose children are the entities you want to count, so each child becomes one record.
Are deletions in Firebase reflected in Redshift?
No. There is no change data capture, so use a deleted flag in your application and filter on it, or rebuild the table periodically.
Why is there an extra schema in my cluster?
Airbyte writes to your target schema and to a raw data schema it manages to improve reliability. Both are expected.
Can I do this without writing code?
The Airbyte setup is UI-driven. You will want SQL extracting the nested structure into typed columns, which on this destination is closer to a requirement than a nicety.
Get your Firebase data into Amazon Redshift
Choose the node path deliberately since it defines your rows, set up the staging bucket early because a Google source needing AWS storage surprises people, build a typed layer rather than leaving everything nested, and decide what a deletion should mean before anyone counts rows.
Airbyte's connector catalog includes 600+ pre-built connectors, so application data can reach a warehouse and a search index at once. For the same source in a warehouse that handles nesting more easily, see Firebase Realtime Database to BigQuery, and for another source landing in Redshift, MySQL to Amazon Redshift.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.