r/dataengineering

I had this conversation with one of our account manager but they seem to give me so many random answers on how it can be used.

Here are my doubts,

1. How do you define one semantic view, is it based on multiple ideas coming together into a unified view or multiple ideas into multiple views
2. After the change in orchestration, Agent tends to write its own SQL definition for the questions asked, how is it utilising the semantic view in this case?
3. Following up on the second question, how do verified queries work together. I have noticed it recognises the verified query but doesn’t want to use the definition which is correct.
4. Which model is being used for the agent to answer business-related questions?
5. Is it better to bring your own LLM and use cortex analysts ability to write code and let the LLM answer?

Let me know if you have found anything useful or good practice while building these views and agents. I want to understand it better.

Senior analytics engineer - spent last 5 years in FAANG mostly doing BI/DE work across small and large teams, and since last year i'm mostly doing fullstack for small internal multiservices app (data/stream intensive). WLB is ok but no career progression in current role.

Using AI tools extensively - both for internal purposes as well as for minor system integrations here and there, as well as maintaining knowledge repos enabling better collaboration using AI. Got cloud certifications, but no tool-specific certifications. Spent 2 years doing internships/ or unrelated corporate jobs before that.

I assume no career growth in my current role, but WLB is good and now I mostly work on software stuff (though i am still much much stronger in DE part), the only concerning thing is stagnation and comfort zone. Been contemplating a lot between:

- Rest & vest? ( I am fine with that)

- Sr. DE in scale-ups/startups?

- Attempt downgrading to some AI-company for a mid-DE role?

- Try to pivot internally to PMT / TPM job families? (Exciting, but concerned about the skill gap)

- Try to pivot internally into higher scope but more stress role? (No excited about that)

- Try to pivot into a more AI-parallel role? (Exciting, but I assume knowledge gap)

I want to get a better understanding of the market conditions? How realistic/unrealistic are these assuming some time to prepare? How hard is it to get interviews these days? How code-intensive vs design/architectural vs behavioral are they?

I am trying to assess the best approach to branching and releases, as we will soon be moving to Dataform and have the chance to improve things with our general development process. But I don’t have a lot of experience here.

Currently, we have 3 projects (dev, staging, and prod) in BigQuery. Each with its own associated branch in GitHub. People merge their feature code to dev, then eventually all changes are merged to staging and finally prod. GitHub actions deploys the code.

But I’m wondering if this can be simplified. My thoughts are:

- 1 protected branch - main (I don’t see the need for 3 branches)

- Devs work from the latest commit of main when developing in the dev project.

- The staging project is also based on the latest commit from main. This is where user acceptance testing happens.

- Prod is based on a git tag on main. When ready to release to prod, a new tag is created, and our Dataform production release configuration is updated with this new tag (still assessing whether this release config in Dataform can be updated automatically via a GitHub action when a new release tag is created, or if we would need to update it manually)

- For hotfixes, we branch from the latest prod tag and merge back into it with the fix. Then cherry picked onto main.

Just wondering if what I’m suggesting makes sense, or if I’m trying to simplify too much?

I've started working with dbt and a cloud warehouse, where I'm thinking of moving from auto-incremented keys in favour of surrogate hash keys, both because auto-increment keys is not as much of a thing in cloud warehouses and also because hash keys are idempotent and much simpler to manage.

One thing that I'm not sure of yet is this: If the surrogate key is a hash of the business key, should I self-hash the foreign key references in the fact table builds, or should I do the usual pattern of left join on dims + coalesce to sentinel value if not found?

I can see the pros and cons of both methods.

Some pros:

• Fewer risks of fan-out in case of misshapen data (should be caught by dbt tests, but still)
• Increased parallelism because of fewer dependencies
• Simpler lineage
• Late-arriving dims, early-arriving facts are not an issue
• If the dimension happens to be massive, this can save compute time, therefore cutting costs.

Some drawbacks of self-hash:

• Reduced impact analysis clarity through the lineage because of no lookups. This can also be diminished via dbt docs
• You must ensure that you hash the same way (logical ordering and also natural keys formatting). A left-join lookup is likely easier to catch in case of a missed lookup (idk).
• Not exactly a drawback, but you probably want to left-join if you apply SCD2.
• No clear way to ensure proper fallback to the sentinel value if that is something used in the analytics layer