Ingestion / Reverse-ETL & Product Analytics

GA4 records every interaction as one named event carrying key-value parameters, so a pageview is simply the event page_view. Universal Analytics collected typed hits — pageview, event, transaction, timing — and treated pageviews and sessions as first-class objects.

event_params is a repeated record of a key plus a value struct holding string_value, int_value, float_value and double_value. You pick the matching key with a scalar subquery over UNNEST and read whichever sub-field the parameter's type populates.

user_pseudo_id is a device- and browser-scoped identifier derived from the client id, present on every event and reset when storage is cleared. user_id is the identifier you explicitly set, present only on events collected after you set it.

GA4 can rewrite a daily export table after it first appears, because late-arriving events are folded in and the day's data is finalized in place. A pipeline that loads each table once and marks the date done silently keeps the first, incomplete version.

They are different datasets. GA4 reports add modelled data, apply thresholding and collapse high-cardinality dimensions into an other row, while the BigQuery export contains only observed events with no modelling and no thresholds — so exact reconciliation is not achievable.

An event property describes one occurrence — the plan clicked, the search term — and is stored on that event. A user property describes the person and is stamped onto their profile, applying to events from then on.

insert_id is a client-supplied unique key on an Amplitude event. Amplitude drops a later event carrying an insert_id it has already seen within its recent deduplication window, so a retried upload does not double-count.

Because the browser's device ID stays mapped to the user who just logged out. reset() clears the user ID and issues a new device ID, so the next visitor's anonymous events start a fresh identity instead of being attributed to the previous person.

Usually three causes stack up: client-side events never reached Amplitude, the funnel enforces a conversion window and a step order the SQL ignores, and the two systems count different entities — Amplitude counts resolved users, the query counts rows or raw user ids.

N-Day retention counts a user as retained on day N only if they returned exactly on day N. Unbounded retention counts them if they returned on day N or any day after, so its curve is always the higher, smoother one.

Event properties are frozen onto one event when it fires. Profile properties live on the user and get overwritten, so reports read their current value. Super properties are stored in the client and auto-attached to every later event.

Mixpanel tags pre-login activity with a device-generated distinct_id. Calling identify with your application's user id links that device id to the user id, so anonymous and logged-in events resolve to one user in reports.

Give every event a deterministic $insert_id derived from its source row, not a fresh random value per attempt. Mixpanel deduplicates on that identifier, so a retry sending the identical payload is dropped instead of counted twice.

You buy fast self-serve funnels and retention that product managers can run unaided, and you give up SQL joins to your modelled business data, pay on event volume, and take on a second copy of user data to govern and delete.

Group Analytics counts accounts rather than individuals. With a group key such as company id on the events, funnels and retention report how many organisations converted or came back, not how many seats did.

Heap's browser snippet automatically records user interactions — clicks, input changes, form submissions and pageviews — with each element's DOM path, visible text, link target and page URL, plus session and user context. No per-event tracking call is written.

Because Heap already captured and retained the raw interactions. An event definition is a matching rule applied over that stored data, not a new collection instruction, so saving it classifies past interactions as well as future ones.

Most likely the definition's selector no longer matches the rendered markup — a CSS refactor, generated class names, a DOM restructure or copy change broke the rule. Autocapture kept recording the clicks; they simply stopped being classified as that event.

Choose Heap's autocapture when questions arrive faster than releases and nobody can predict what to instrument — early product, small engineering capacity, exploratory analysis. Accept a noisy corpus, definitions coupled to markup, a wider privacy surface, and semantics living in a vendor UI.

Because the synced event is derived from Heap's retained raw interactions via an editable definition. Change the rule and past interactions are reclassified, so the warehouse copy is restated for dates you already loaded and reported on.

Segment's identify call records who a user is: a userId plus traits such as email or plan. track records what a user did: an event name plus properties describing that one action.

A cloud-mode Segment destination receives events from Segment's servers, so Segment can filter, transform, retry and replay them. A device-mode destination gets the vendor's own SDK bundled into the client and receives events directly from the browser or app.

Segment's client library assigns an anonymousId and stores it locally; the first identify call sends that anonymousId together with the userId, letting downstream tools stitch the two. Skipping analytics.reset on logout or omitting the anonymousId on server calls breaks the link.

Route through Segment when the set of downstream tools changes often, when several teams need one agreed schema, and when you want history replayed into tools added later. Instrument directly when the destination set is small, stable and client-dependent.

A Segment Protocols tracking plan declares the allowed events and their property types; by default a non-conforming event is still delivered and recorded as a violation. Only the source's schema controls turn that into dropping the event or stripping the offending property.

Census is a reverse-ETL tool: it reads modeled tables out of your warehouse and writes them into operational SaaS apps like Salesforce or HubSpot. ETL fills the warehouse; reverse ETL pushes its answers back into the tools people work in.

Update or Create only ever inserts or updates records present in the model. Mirror also removes or archives destination records that have dropped out of the model, so the destination ends up matching the model exactly — including its absences.

Decide ownership field by field. A reverse-ETL sync is a blind last-writer-wins job with no merge, so the fix is to unmap fields humans own, keep warehouse-owned fields separate from human-editable ones, and ingest CRM edits back into the warehouse if the model must respect them.

It stores bookkeeping tables recording what each sync already sent, so it can diff the model each run and push only changed rows. The cost is warehouse compute and storage per sync, and a state reset that resends every row into a rate-limited destination.

A Hightouch model is a warehouse query, table or dbt model with a unique primary key. A sync binds that model to one destination object, maps its columns onto destination fields, and writes the rows into a SaaS tool on a schedule.

Read the sync run's row-level errors first; they usually name a destination validation failure or a rate-limit response. Then cut volume: find out why the diff marked so many rows changed, filter the model down, and batch or reschedule the writes.

Insert only creates new destination records, Update only changes records that already exist, Upsert does both, and Mirror additionally removes destination records for rows that dropped out of the Hightouch model. Which modes are offered depends on the destination.

Buy when destinations are many and their APIs churn, non-engineers must own mappings, and per-row error reporting matters. Build when one or two stable destinations, strict data-residency rules or a punishing cost curve justify permanently owning diffing, retries, rate limiting and observability.

Customer Studio lets non-engineers assemble audiences visually on top of a governed parent model and its related event models. The resulting audience becomes a sync source, so campaign segments reuse warehouse definitions instead of new hand-written SQL each time.

Log-based capture reads the database's own replication log, so it sees every committed insert, update and delete in commit order. Query-based capture repeatedly polls the tables, so it only sees rows that still exist at poll time.

A transaction log records only changes, and only recent ones. Rows untouched since the log's retention window began appear nowhere in it, so the pipeline must read the table once to establish a baseline.

Both read the full contents of the captured tables on first start. With initial the connector then switches to streaming the transaction log and follows it forever; with initial_only it shuts down once the snapshot finishes and never streams.

A poll returns rows that exist now and match its filter. A deleted row matches nothing, so it is never reported; several updates between two polls return only the newest version. Polling observes current state, never history.

A CDC pipeline redelivers events after any restart, so a replayed insert collides on the key and a replayed relative update double-counts. Writing each event's after-image as an upsert keyed on the primary key makes redelivery a no-op.

A _SUCCESS marker is written only after every data file in a batch has finished uploading, so its presence is the producer's promise that the drop is complete and safe to read. Its contents are irrelevant.

A full-refresh extract reads every source row each run and replaces the target. An incremental extract reads only rows changed since a stored watermark and merges them: far cheaper, but it must handle boundary rows, deletes and reruns itself.

Schema drift is the source changing shape without warning: columns added, dropped, renamed, retyped or reordered. Loads written against yesterday's shape then fail on a cast, shift positionally, or silently drop the new field.

Because updated_at is stamped when a row is written, not when its transaction commits, so a row can become visible only after the extract has already moved its watermark past that timestamp. Boundary ties and clock skew lose rows the same way.

File ingestion is at-least-once by nature, so make replay harmless: track processed objects by key plus content hash in the same transaction as the load, or make the load replace a partition rather than append to it, or upsert on a deterministic row key.

An Expectation Suite is a named collection of declarative assertions about one dataset - not-null, unique, between, matches-regex. Validating it against a batch of data returns a Validation Result with per-expectation pass/fail plus counts and samples of the offending values.

A quarantine table keeps each rejected row plus the reason it failed, so good data still lands while bad data stays visible and replayable. Dropping destroys the evidence silently; failing the whole load punishes every valid row for a few defects.

Keep the contract spec in the producer's repository and add a CI job that diffs the proposed spec against the released one, classifies each change as compatible or breaking, and fails the pull request on a breaking change unless a major version bump and consumer sign-off accompany it.

Fail the load when the defect makes the whole batch untrustworthy: an unrecognisable schema, a row count or control total that disagrees with the manifest, duplicate keys that would corrupt a merge, or a reject rate far above baseline. Independent per-row defects belong in quarantine.

Read the returned result's success flag and raise from the task - Great Expectations reports, it does not enforce. Its actions store results and rebuild Data Docs whether validation passed or failed, so nothing stops on its own.

An Airbyte source implements spec, check, discover and read. spec returns the JSON-Schema config form, check validates credentials, discover returns the catalog of streams and their schemas, and read emits records and state messages on stdout.

Airbyte pairs a read mode with a write mode. Full Refresh Overwrite replaces the table each sync; Full Refresh Append re-appends everything; Incremental Append adds only records past the cursor; Incremental Append + Deduped keeps one current row per primary key.

The stream declares a cursor field, then emits STATE messages during read holding the highest cursor value whose records have already been emitted. The platform persists the last STATE it saw and hands it back as the next run's input, so the connector resumes from there.

It needs a cursor field to decide what is new and a primary key to collapse it. Airbyte appends the incremental records to a raw table, then rebuilds a final table holding the highest-cursor row per primary key.

Use the low-code declarative manifest when the source is a plain REST API with JSON responses, standard auth and standard pagination. Drop to the Python CDK when the source is not HTTP, the response needs real parsing, or the request logic is stateful.

Once. Fivetran bills Monthly Active Rows: a distinct primary key that is inserted, updated or deleted during a calendar month counts as one active row no matter how many times it changed, so raising sync frequency alone does not multiply the bill.

Fivetran owns the connector itself — source API and schema changes, incremental state, retries, backfills and target table DDL. You supply credentials and a destination; the vendor maintains the extraction code you would otherwise write and patch.

They set how much new source structure a connector propagates automatically. Allow All admits new tables and new columns, Allow Columns admits new columns only in already-synced tables, and Block All admits neither until someone enables them explicitly.

When the consumption bill outgrows the engineering cost it replaces, when most sources are custom rather than catalog connectors, or when residency, network or latency constraints the hosted service cannot meet start driving the architecture.

History Mode stops overwriting rows in place. Each change writes a new versioned row with validity columns — _fivetran_active, _fivetran_start, _fivetran_end — so the table keeps every past state instead of only the current one.