Anyone have experience with data lakes

[Brad Burlingham]

I'm hoping to setup api from all of our dealerships into a datalake so that we can create our own reporting. Does anyone have any suggestions on vendors or overall advice?

First stage is to use it to develop marketing dashboards to evaluate trends, performance, kpi etc. next step would be to use it with a cdp to develop audiences etc.

 

[CarlAutofusion]

Check out snowflake.com. It can get expensive if you're running a ton of stuff through it but its' pretty good and they have an API if you need to do anything custom. It can also look for patterns and trends in the data.

 

[Ryan Everson]

Snowflake is still the industry leader by far, but ClickHouse is quickly stealing market share as a more innovative solution:
Fast Open-Source OLAP DBMS - ClickHouse

Getting marketing data like GA4 into a data warehouse can sometimes be easier with Google BigQuery, but tools like Supermetrics can help load that data into Snowflake as well.

For reporting, I would highly suggest looking into Sigma. We’re in the process of moving off Tableau and Google Data / Looker Studio and over to Sigma:
Sigma. Unified AI apps and analytics

But honestly, the most challenging piece is going to be setting up the pipelines to extract data out of your DMS, CRM, etc because as I’m sure you already know, automotive still tends to be a pretty walled off garden.

 

[CarlAutofusion]

Ryan is right that the most time-consuming/expensive part will be getting the data pipelines set up. Some vendors won't do it unless there's a bit of arm twisting. Others will charge for the access.

 

[Bill Hoerr]

I went through this process previously at my 4 rooftop group. I documented the process and put it in the DR Resources tab here as well as GitHub with a more detailed architectural breakdown.

We used:

• CDK (DMS): The Data Your Way export tool for daily pipeline cadence. This was super helpful.
• DriveCentric (CRM): They offered direct API connection
• Infrastructure: AWS API Gateway, S3 storage, Glue for dedup/cleaning, and Redshift for storage
• Reporting: Data Studio
• Marketing Activation: Segment CDP

What I'd do differently now:

• I'd go with BigQuery/Google Cloud over Redshift/AWS for the native integrations with GA4 and Data Studio as Ryan mentioned ^
• Supermetrics (they recently released a marketing intelligence platform for activation) or Airbyte if you want more technical control
• Evidence.dev for reporting. They have NLP built-in so non-technical folks can build reports too and permission controls if you wanted to create reports per department.

Depending on the DMS and CRM, the pipeline will be the largest obstacle. But DriveCentric and CDK had these export tools which made it ALOT easier to get things started. And as always, get comfortable with the data dictionary provided

It was a fun and beneficial project for our group and I hope some of this helps you in your quest!

 

[djbailey121]

I'm hoping to setup api from all of our dealerships into a datalake so that we can create our own reporting. Does anyone have any suggestions on vendors or overall advice?

First stage is to use it to develop marketing dashboards to evaluate trends, performance, kpi etc. next step would be to use it with a cdp to develop audiences etc.

Every multi-rooftop dealer group seems to eventually build a data lake, and every one hits the same set of problems. Once you figure out the pipeline, the next one is usually that “one customer” is actually six records scattered across DMS, CRM, and service at five different stores. Rules-based dedup catches the easy ones and drops a good bit of the rest. The fix is resolving identity by meaning to match even the records that don't have many common identifiers. Unify the person first. Then your dashboards and campaigns tell the truth.

 

[djbailey121]

I went through this process previously at my 4 rooftop group. I documented the process and put it in the DR Resources tab here as well as GitHub with a more detailed architectural breakdown.

We used:

• CDK (DMS): The Data Your Way export tool for daily pipeline cadence. This was super helpful.
• DriveCentric (CRM): They offered direct API connection
• Infrastructure: AWS API Gateway, S3 storage, Glue for dedup/cleaning, and Redshift for storage
• Reporting: Data Studio
• Marketing Activation: Segment CDP

What I'd do differently now:

• I'd go with BigQuery/Google Cloud over Redshift/AWS for the native integrations with GA4 and Data Studio as Ryan mentioned ^
• Supermetrics (they recently released a marketing intelligence platform for activation) or Airbyte if you want more technical control
• Evidence.dev for reporting. They have NLP built-in so non-technical folks can build reports too and permission controls if you wanted to create reports per department.

Depending on the DMS and CRM, the pipeline will be the largest obstacle. But DriveCentric and CDK had these export tools which made it ALOT easier to get things started. And as always, get comfortable with the data dictionary provided

It was a fun and beneficial project for our group and I hope some of this helps you in your quest!

Sharp breakdown Bill. You've nailed the pipeline, and I'd just add the part that plays a big role in whether or not the whole thing works.

It doesn't matter much whether you land on Databricks, Snowflake, BigQuery, or AWS, they're all capable. What actually makes or breaks it is the identity spine and the outside data you append to it, run through a pipeline built to fit the solution rather than forcing the solution into the tool.

That's where the dedup step you mentioned really carries weight. We're seeing this firsthand on a project with Tekion right now, reconciling records across their ecosystem. We’ve found generally that Glue/rules-based matching gets the easy ones but misses ~half the true matches, and the ones it drops are usually the high-value repeat customers. Resolve identity by meaning first, then append (vehicle/VIN, equity, demographics) so you're activating against a real person who's been placed in the broader market rather than just what's in your own system.

 

[Bill Hoerr]

Sharp breakdown Bill. You've nailed the pipeline, and I'd just add the part that plays a big role in whether or not the whole thing works.

It doesn't matter much whether you land on Databricks, Snowflake, BigQuery, or AWS, they're all capable. What actually makes or breaks it is the identity spine and the outside data you append to it, run through a pipeline built to fit the solution rather than forcing the solution into the tool.

That's where the dedup step you mentioned really carries weight. We're seeing this firsthand on a project with Tekion right now, reconciling records across their ecosystem. We’ve found generally that Glue/rules-based matching gets the easy ones but misses ~half the true matches, and the ones it drops are usually the high-value repeat customers. Resolve identity by meaning first, then append (vehicle/VIN, equity, demographics) so you're activating against a real person who's been placed in the broader market rather than just what's in your own system.

Agreed! The id resolution took about 2-3 months to pin down and was handled downstream via Segment CDP with a PK string across customer id, vin, lead id, deal id, etc. to ensure LTV values and vin assigned were accurate against the first defined record.

So relied on Glue for clear dedup logic but then had downstream mapping using DMS/CRM and non DMS/CRM sources to append any new/modified change to shoppers. Hardest part was keeping with 3rd party tool tracking coverage!